<?php
/* ============================================================================
 *  company/staff_audit.php — diagnose Total Staff vs Staff List mismatch.
 *  Shows every company_staff row LEFT-JOINed to users, flags broken links.
 *  Admin-only. Plain output. Safe to delete after use.
 * ==========================================================================*/
session_start();
if (!isset($_SESSION["user_id"]) || $_SESSION["role"] !== "company_admin") {
    header("Location: ../auth/login.php"); exit;
}
require_once '../config/database.php';
header('Content-Type: text/html; charset=utf-8');
$companyId = (int)$_SESSION["company_id"];

echo "<style>body{font-family:Arial;padding:24px;max-width:1000px;margin:auto;}table{border-collapse:collapse;width:100%;font-size:13px;}th,td{border:1px solid #ccc;padding:7px 9px;text-align:left;}th{background:#0f766e;color:#fff;}tr.bad{background:#fee2e2;}tr.warn{background:#fef3c7;}.tag{font-weight:700;}a.btn{display:inline-block;margin-top:14px;padding:8px 14px;background:#0f766e;color:#fff;text-decoration:none;border-radius:7px;}</style>";

$total = (int)$pdo->query("SELECT COUNT(*) FROM company_staff WHERE company_id={$companyId}")->fetchColumn();
$joined = (int)$pdo->query("SELECT COUNT(*) FROM company_staff cs JOIN users u ON u.id=cs.user_id WHERE cs.company_id={$companyId}")->fetchColumn();

echo "<h2>Staff audit — company #{$companyId}</h2>";
echo "<p><b>company_staff rows:</b> {$total} &nbsp; | &nbsp; <b>rows with a valid user (what the list shows):</b> {$joined} &nbsp; | &nbsp; <b>broken:</b> ".($total-$joined)."</p>";

$rows = $pdo->prepare("
    SELECT cs.id AS cs_id, cs.user_id, cs.staff_code, cs.position, cs.department, cs.joined_date,
           u.id AS u_id, u.name, u.email, u.username, u.status, u.role
    FROM company_staff cs
    LEFT JOIN users u ON u.id = cs.user_id
    WHERE cs.company_id = ?
    ORDER BY cs.id ASC");
$rows->execute([$companyId]);
$rows = $rows->fetchAll(PDO::FETCH_ASSOC);

echo "<table><tr><th>cs.id</th><th>user_id</th><th>Staff code</th><th>users row?</th><th>Name</th><th>Email</th><th>Username</th><th>status</th><th>role</th><th>Diagnosis</th></tr>";
foreach ($rows as $r) {
    $hasUser = $r['u_id'] !== null;
    $diag = ''; $cls = '';
    if (!$hasUser) { $diag = "❌ NO users row for user_id={$r['user_id']} — dropped from list"; $cls='bad'; }
    elseif ($r['status'] !== 'active') { $diag = "⚠️ user status = ".htmlspecialchars($r['status']); $cls='warn'; }
    elseif ($r['role'] !== 'customer') { $diag = "ℹ️ user role = ".htmlspecialchars($r['role']); }
    else { $diag = "✅ OK"; }
    echo "<tr class='$cls'>";
    echo "<td>{$r['cs_id']}</td><td>{$r['user_id']}</td><td>".htmlspecialchars((string)$r['staff_code'])."</td>";
    echo "<td>".($hasUser?'yes':'<b>MISSING</b>')."</td>";
    echo "<td>".htmlspecialchars((string)$r['name'])."</td><td>".htmlspecialchars((string)$r['email'])."</td>";
    echo "<td>".htmlspecialchars((string)$r['username'])."</td><td>".htmlspecialchars((string)$r['status'])."</td>";
    echo "<td>".htmlspecialchars((string)$r['role'])."</td><td>$diag</td></tr>";
}
echo "</table>";

echo "<a class='btn' href='staff.php'>← Back to Staff</a>";