<?php
require_once 'auth.php';
ensureAdminAuth();
require_once '../config/database.php';
require_once '../includes/app_helpers.php';
$database = new Database();
$db = $database->getConnection();
ensureOperationalTables($db);
ensureAnnouncementTables($db);

$start = $_GET['start_date'] ?? date('Y-m-01');
$end = $_GET['end_date'] ?? date('Y-m-d');
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');

$stmt = $db->prepare("SELECT
        cu.id,
        CONCAT(cu.first_name, ' ', cu.last_name) AS csr_name,
        cu.email,
        COALESCE(cu.role, 'csr') AS role,
        COUNT(DISTINCT handled.request_id) AS handled_tickets,
        COUNT(DISTINCT completed.request_id) AS completed_tickets,
        COUNT(DISTINCT canceled.request_id) AS canceled_tickets,
        COUNT(DISTINCT assigned.request_id) AS technician_assigned,
        (COUNT(DISTINCT completed.request_id) * 3 + COUNT(DISTINCT assigned.request_id) * 2 + COUNT(DISTINCT handled.request_id)) AS score
    FROM csr_users cu
    LEFT JOIN (
        SELECT user_id AS csr_id, request_id FROM request_logs WHERE user_role IN ('csr','csr_manager') AND DATE(created_at) BETWEEN :start1 AND :end1
        UNION
        SELECT csr_id, request_id FROM csr_request_locks WHERE DATE(locked_at) BETWEEN :start2 AND :end2
        UNION
        SELECT csr_id, request_id FROM csr_activity WHERE DATE(created_at) BETWEEN :start3 AND :end3
    ) handled ON handled.csr_id = cu.id
    LEFT JOIN request_logs completed ON completed.user_id = cu.id AND completed.user_role IN ('csr','csr_manager') AND completed.action = 'status_change' AND completed.new_value = '4' AND DATE(completed.created_at) BETWEEN :start4 AND :end4
    LEFT JOIN request_logs canceled ON canceled.user_id = cu.id AND canceled.user_role IN ('csr','csr_manager') AND canceled.action = 'status_change' AND canceled.new_value = '5' AND DATE(canceled.created_at) BETWEEN :start5 AND :end5
    LEFT JOIN service_assignments assigned ON assigned.assigned_by = cu.id AND DATE(assigned.assigned_at) BETWEEN :start6 AND :end6
    WHERE cu.is_active = 1
      AND COALESCE(cu.role, 'csr') = 'csr'
    GROUP BY cu.id
    ORDER BY score DESC, handled_tickets DESC, completed_tickets DESC, csr_name ASC");
$stmt->execute([
    ':start1'=>$start, ':end1'=>$end, ':start2'=>$start, ':end2'=>$end, ':start3'=>$start, ':end3'=>$end,
    ':start4'=>$start, ':end4'=>$end, ':start5'=>$start, ':end5'=>$end, ':start6'=>$start, ':end6'=>$end,
]);
$rankings = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>CSR Ranking - Admin</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
<link href="https://fonts.googleapis.com/css2?family=Montserrat:wght@400;500;600;700;800;900&display=swap" rel="stylesheet">
<link rel="stylesheet" href="../assets/theme.css"><link rel="stylesheet" href="../assets/portal-dark.css">
<style>
:root{--bg1:#080240;--bg2:#093349;--card1:#17295e;--card2:#102448;--cyan:#17bdd4;--muted:#aebbd4;--soft:#d7e7ff}*{box-sizing:border-box}body{background:linear-gradient(135deg,var(--bg1),var(--bg2)) fixed!important;color:#fff!important;font-family:Montserrat,sans-serif!important}.sidebar{min-height:100vh;background:rgba(8,2,64,.96);position:fixed;width:250px;border-right:1px solid rgba(96,165,250,.18)}.sidebar-header{padding:24px 18px;text-align:center;border-bottom:1px solid rgba(96,165,250,.18)}.sidebar-header i{color:var(--cyan)}.sidebar-header h4{font-size:1.1rem;font-weight:900}.sidebar-menu{padding:20px 0}.sidebar-menu a{color:#b9dcff!important;padding:13px 20px;display:block;text-decoration:none;border-left:4px solid transparent;font-weight:700;font-size:14px}.sidebar-menu a:hover,.sidebar-menu a.active{background:rgba(56,189,248,.12);color:#fff!important;border-left-color:var(--cyan)}.sidebar-menu i{margin-right:10px;width:20px}.main-content{margin-left:250px;padding:24px}.panel,.stat-card{background:linear-gradient(135deg,var(--card1),var(--card2))!important;border:1px solid rgba(56,189,248,.24)!important;border-radius:18px!important;box-shadow:0 10px 24px rgba(0,0,0,.24);color:#fff!important}.panel{padding:20px;margin-bottom:20px}.panel h2,.panel h4{font-weight:900}.text-muted{color:var(--muted)!important}.form-control{background:rgba(8,20,48,.72)!important;border:1px solid rgba(96,165,250,.34)!important;color:#fff!important;border-radius:10px}.btn-primary{background:linear-gradient(135deg,#1d9bf0,#17bdd4)!important;border:none!important;font-weight:900;border-radius:10px}.table{color:#fff!important}.table th{background:rgba(8,20,48,.58)!important;color:#fff!important;border-color:rgba(96,165,250,.22)!important}.table td{background:rgba(8,20,48,.18)!important;color:var(--soft)!important;border-color:rgba(96,165,250,.20)!important}.rank-badge{display:inline-flex;width:34px;height:34px;align-items:center;justify-content:center;border-radius:50%;background:linear-gradient(135deg,#1d9bf0,#17bdd4);font-weight:900}.badge{border-radius:999px;font-weight:900}
.main-content,
.main-content *{
    color:#ffffff;
}
.panel p,
.panel label,
.panel h2,
.panel h4,
.table td,
.table td strong,
.table td small,
.table th,
.form-label{
    color:#ffffff!important;
}
.table td{
    color:#d7e7ff!important;
}
.table td strong{
    color:#ffffff!important;
}
.table small,
.text-muted{
    color:#aebbd4!important;
}
.form-control,
.form-control:focus{
    color:#ffffff!important;
    background:rgba(8,20,48,.72)!important;
}
.form-control::-webkit-calendar-picker-indicator{
    filter:invert(1);
    opacity:.85;
}
.badge.bg-info{
    background:linear-gradient(135deg,#1d9bf0,#17bdd4)!important;
    color:#ffffff!important;
}
.rank-badge{
    color:#ffffff!important;
}
@media(max-width:768px){.sidebar{position:relative;width:100%;min-height:auto}.main-content{margin-left:0;padding:14px}.table{min-width:850px}}
</style>
    <link rel="stylesheet" href="../assets/ushs-global-theme.css">
    <link rel="stylesheet" href="../assets/admin-sidebar.css">
</head>
<body>
<?php require_once __DIR__ . '/admin_sidebar.php'; render_admin_sidebar(); ?>

    <main class="main-content">
<div class="panel d-flex justify-content-between flex-wrap gap-2 align-items-center"><div><h2><i class="fas fa-ranking-star me-2" style="color:var(--cyan)"></i>CSR Ranking</h2><p class="text-muted mb-0">Rank regular CSRs only by handled tickets, completions, assignments, and score. Team Leaders / Managers are excluded.</p></div></div>
<div class="panel"><form class="row g-3 align-items-end"><div class="col-md-4"><label class="form-label text-white fw-bold">Start Date</label><input type="date" class="form-control" name="start_date" value="<?=h($start)?>"></div><div class="col-md-4"><label class="form-label text-white fw-bold">End Date</label><input type="date" class="form-control" name="end_date" value="<?=h($end)?>"></div><div class="col-md-4"><button class="btn btn-primary w-100"><i class="fas fa-filter me-1"></i>Apply Filter</button></div></form></div>
<div class="panel"><div class="table-responsive"><table class="table table-hover align-middle"><thead><tr><th>Rank</th><th>CSR</th><th>Role</th><th>Handled</th><th>Completed</th><th>Cancelled</th><th>Tech Assigned</th><th>Score</th></tr></thead><tbody><?php foreach($rankings as $i=>$r): ?><tr><td><span class="rank-badge"><?=($i+1)?></span></td><td><strong><?=h($r['csr_name'])?></strong><br><small class="text-muted"><?=h($r['email'])?></small></td><td><span class="badge bg-info"><?=h($r['role']==='csr_manager'?'CSR Manager':'CSR')?></span></td><td><?=number_format((int)$r['handled_tickets'])?></td><td><?=number_format((int)$r['completed_tickets'])?></td><td><?=number_format((int)$r['canceled_tickets'])?></td><td><?=number_format((int)$r['technician_assigned'])?></td><td><strong><?=number_format((int)$r['score'])?></strong></td></tr><?php endforeach; ?><?php if(!$rankings): ?><tr><td colspan="8" class="text-center text-muted py-4">No ranking data found.</td></tr><?php endif; ?></tbody></table></div></div>
</main></body></html>
