<?php
// csr_manager/ranking.php — CSR performance leaderboard
if (session_status() === PHP_SESSION_NONE) session_start();
if (!isset($_SESSION['csr_id'])) { header('Location: ../login.php'); exit(); }

require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../includes/app_helpers.php';
require_once __DIR__ . '/../includes/csr_report_helpers.php';

$db = (new Database())->getConnection();
ensureCsrRoleSchema($db);
ensureCsrTeamTables($db);

$managerId = (int)$_SESSION['csr_id'];
if (getCsrRoleForReport($db, $managerId) !== 'csr_manager') {
    header('Location: dashboard.php'); exit();
}

$start = $_GET['start_date'] ?? date('Y-m-01');
$end   = $_GET['end_date']   ?? date('Y-m-d');
$teamFilter = (int)($_GET['team_id'] ?? 0);

// Validate dates
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $start)) $start = date('Y-m-01');
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $end))   $end   = date('Y-m-d');

// Load all teams
$teamsStmt = $db->query("SELECT id, team_name FROM csr_teams WHERE is_active=1 ORDER BY team_name");
$allTeams  = $teamsStmt->fetchAll(PDO::FETCH_ASSOC);

// Build the leaderboard query
$teamJoin = $teamFilter ? "AND ct.id = $teamFilter" : "";
$rankRows = [];
try {
    // Build date-bound ranking using CTEs / derived tables for correctness + performance
    $teamCondition = $teamFilter ? "AND ct.id = " . (int)$teamFilter : "";
    $stmt = $db->prepare("
        SELECT
            cu.id,
            CONCAT(cu.first_name,' ',cu.last_name) AS agent_name,
            COALESCE(cu.role,'csr')                AS role,
            ct.team_name,
            COALESCE(gh.cnt, 0)       AS gh,
            COALESCE(sched.cnt, 0)    AS schedule,
            COALESCE(att.cnt, 0)      AS attempt,
            0                         AS warnings,
            COALESCE(cancel.cnt, 0)   AS mistakes
        FROM csr_users cu
        JOIN csr_team_members ctm ON ctm.csr_id = cu.id
        JOIN csr_teams ct ON ct.id = ctm.team_id AND ct.is_active = 1 $teamCondition
        -- GH: tickets handled (locked)
        LEFT JOIN (
            SELECT csr_id, COUNT(DISTINCT request_id) AS cnt
            FROM csr_request_locks
            WHERE DATE(locked_at) BETWEEN :s1 AND :e1
            GROUP BY csr_id
        ) gh ON gh.csr_id = cu.id
        -- Schedule: technicians assigned
        LEFT JOIN (
            SELECT assigned_by AS csr_id, COUNT(DISTINCT request_id) AS cnt
            FROM service_assignments
            WHERE DATE(assigned_at) BETWEEN :s2 AND :e2
            GROUP BY assigned_by
        ) sched ON sched.csr_id = cu.id
        -- Attempt: in-progress + completed tickets handled
        LEFT JOIN (
            SELECT crl2.csr_id, COUNT(DISTINCT sr2.id) AS cnt
            FROM csr_request_locks crl2
            JOIN service_requests sr2 ON sr2.id = crl2.request_id
              AND sr2.job_status_id IN (3, 4)
            GROUP BY crl2.csr_id
        ) att ON att.csr_id = cu.id
        -- Mistakes: canceled status changes
        LEFT JOIN (
            SELECT user_id AS csr_id, COUNT(DISTINCT request_id) AS cnt
            FROM request_logs
            WHERE action = 'status_change'
              AND CAST(new_value AS CHAR) = '5'
              AND DATE(created_at) BETWEEN :s3 AND :e3
            GROUP BY user_id
        ) cancel ON cancel.csr_id = cu.id
        WHERE cu.is_active = 1
        GROUP BY cu.id, cu.first_name, cu.last_name, cu.role, ct.team_name,
                 gh.cnt, sched.cnt, att.cnt, cancel.cnt
        ORDER BY gh DESC, schedule DESC
    ");
    $stmt->execute([':s1'=>$start,':e1'=>$end,':s2'=>$start,':e2'=>$end,':s3'=>$start,':e3'=>$end]);
    $rankRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) {
    $rankRows = [];
    // Store error for display
    $rankError = $e->getMessage();
}

$title = 'Ranking';
function h(string $s): string { return htmlspecialchars($s, ENT_QUOTES, 'UTF-8'); }

ob_start();
?>

<div class="d-flex justify-content-between align-items-center flex-wrap gap-2 mb-4">
  <div>
    <h2 style="font-family:'Space Grotesk',system-ui,sans-serif;font-weight:800;margin:0;">
      <i class="fas fa-ranking-star me-2" style="color:var(--cm-yellow);"></i>CSR Ranking
    </h2>
    <small style="color:var(--cm-muted);"><?= h($start) ?> — <?= h($end) ?> · <?= count($rankRows) ?> agents</small>
  </div>
</div>

<!-- Date + team filter -->
<div class="cm-card mb-4">
  <form method="GET" style="display:flex;gap:10px;flex-wrap:wrap;align-items:flex-end;">
    <div>
      <label style="color:var(--cm-muted);font-size:.72rem;font-weight:700;display:block;margin-bottom:4px;">FROM</label>
      <input class="cm-input" type="date" name="start_date" value="<?= h($start) ?>" style="width:150px;">
    </div>
    <div>
      <label style="color:var(--cm-muted);font-size:.72rem;font-weight:700;display:block;margin-bottom:4px;">TO</label>
      <input class="cm-input" type="date" name="end_date" value="<?= h($end) ?>" style="width:150px;">
    </div>
    <div>
      <label style="color:var(--cm-muted);font-size:.72rem;font-weight:700;display:block;margin-bottom:4px;">TEAM</label>
      <select class="cm-select" name="team_id" style="min-width:160px;">
        <option value="0" <?= !$teamFilter?'selected':'' ?>>All Teams</option>
        <?php foreach ($allTeams as $t): ?>
          <option value="<?= (int)$t['id'] ?>" <?= $teamFilter===(int)$t['id']?'selected':'' ?>><?= h($t['team_name']) ?></option>
        <?php endforeach; ?>
      </select>
    </div>
    <button type="submit" class="cm-btn cm-btn--primary"><i class="fas fa-search me-1"></i>Filter</button>
    <a href="ranking.php" class="cm-btn"><i class="fas fa-times me-1"></i>Reset</a>
  </form>
</div>

<!-- Top 3 podium (if enough data) -->
<?php if (count($rankRows) >= 3): ?>
<div style="display:grid;grid-template-columns:1fr 1fr 1fr;gap:16px;margin-bottom:20px;">
  <?php
  $podium = array_slice($rankRows, 0, 3);
  $podiumOrder = [1, 0, 2]; // 2nd, 1st, 3rd visual order on desktop
  $medals = ['🥇','🥈','🥉'];
  $podiumColors = ['oklch(0.82 0.16 85)','oklch(0.72 0.02 260)','oklch(0.72 0.15 45)'];
  foreach ([1,0,2] as $vi => $ri):
    if (!isset($podium[$ri])) continue;
    $p = $podium[$ri];
  ?>
  <div class="cm-card" style="text-align:center;padding:20px;border-color:<?= $podiumColors[$ri] ?>44;<?= $ri===0?'order:-1;':''; ?>">
    <div style="font-size:2rem;margin-bottom:8px;"><?= $medals[$ri] ?></div>
    <div style="font-family:'Space Grotesk',system-ui,sans-serif;font-weight:800;font-size:1rem;"><?= h($p['agent_name']) ?></div>
    <div style="font-size:.72rem;color:var(--cm-muted);margin-bottom:10px;"><?= h($p['team_name']??'—') ?></div>
    <div style="font-family:'Space Grotesk',system-ui,sans-serif;font-size:2rem;font-weight:800;color:<?= $podiumColors[$ri] ?>;"><?= (int)$p['gh'] ?></div>
    <div style="font-size:.64rem;font-weight:700;text-transform:uppercase;letter-spacing:.06em;color:var(--cm-muted);">GH Total</div>
  </div>
  <?php endforeach; ?>
</div>
<?php endif; ?>

<!-- Full leaderboard -->
<div class="cm-card">
  <div class="cm-table-wrap">
    <table class="cm-table">
      <thead>
        <tr>
          <th style="width:40px;">#</th>
          <th>Team</th>
          <th>Agent</th>
          <th>Role</th>
          <th>GH</th>
          <th>Schedule</th>
          <th>Attempt</th>
          <th>Warnings</th>
          <th>Mistakes</th>
        </tr>
      </thead>
      <tbody>
    <?php if (empty($rankRows)): ?>
        <?php if (!empty($rankError)): ?>
        <tr><td colspan="9" style="text-align:center;padding:24px;color:var(--cm-red);">
          <i class="fas fa-exclamation-triangle me-2"></i>Query error — check DB permissions.<br>
          <small style="color:var(--cm-muted);"><?= h($rankError ?? '') ?></small>
        </td></tr>
        <?php else: ?>
        <tr><td colspan="9" style="text-align:center;padding:32px;color:var(--cm-muted);">No ranking data for this period.</td></tr>
        <?php endif; ?>
        <?php else: ?>
        <?php foreach ($rankRows as $rank => $r):
          $roleLabel = match($r['role'] ?? 'csr') {
            'team_leader' => 'Team Leader',
            'csr_manager' => 'CSR Manager',
            default       => 'CSR'
          };
          $rankNum = $rank + 1;
          $medalEmoji = match($rankNum) { 1 => '🥇', 2 => '🥈', 3 => '🥉', default => $rankNum };
        ?>
        <tr>
          <td style="text-align:center;font-weight:800;<?= $rankNum<=3?'font-size:1.1rem;':'' ?>"><?= $medalEmoji ?></td>
          <td style="font-size:.76rem;color:var(--cm-blue);font-weight:700;"><?= h($r['team_name']??'—') ?></td>
          <td><strong><?= h($r['agent_name']) ?></strong></td>
          <td><span class="cm-badge cm-badge--muted" style="font-size:.65rem;"><?= h($roleLabel) ?></span></td>
          <td style="font-weight:700;"><?= (int)$r['gh'] ?></td>
          <td style="color:var(--cm-cyan);"><?= (int)$r['schedule'] ?></td>
          <td style="color:var(--cm-blue);"><?= (int)$r['attempt'] ?></td>
          <td style="color:var(--cm-yellow);"><?= (int)$r['warnings'] ?></td>
          <td style="color:var(--cm-red);"><?= (int)$r['mistakes'] ?></td>
        </tr>
        <?php endforeach; ?>
        <?php endif; ?>
      </tbody>
    </table>
  </div>
</div>

<?php
$content = ob_get_clean();
include __DIR__ . '/layout.php';
?>
