<?php
// public/admin/dashboard.php
declare(strict_types=1);

require_once __DIR__ . "/../../config/db.php";
require_once __DIR__ . "/../../config/auth.php";

require_admin();

$u    = current_user();
$uid  = (int)($u['id'] ?? 0);
$name = (string)($u['name'] ?? '');

$today = (new DateTime('today'))->format('Y-m-d');
$startOfMonth = (new DateTime('first day of this month'))->format('Y-m-d');
$startOfYear = (new DateTime('first day of January'))->format('Y-m-d');
$lastMonth = (new DateTime('first day of last month'))->format('Y-m-d');
$nextWeek = (new DateTime($today))->modify('+7 days')->format('Y-m-d');

// ---------- Helpers ----------
function fetchValue(PDO $pdo, string $sql, array $params = []): int {
    $st = $pdo->prepare($sql);
    $st->execute($params);
    return (int)($st->fetchColumn() ?? 0);
}
function fetchAll(PDO $pdo, string $sql, array $params = []): array {
    $st = $pdo->prepare($sql);
    $st->execute($params);
    return $st->fetchAll(PDO::FETCH_ASSOC);
}

// ---------- System Overview KPIs ----------
$kpi = [
    // School Statistics
    'total_schools' => fetchValue($pdo, "SELECT COUNT(*) FROM schools"),
    'total_gps' => fetchValue($pdo, "SELECT COUNT(*) FROM gps"),
    'total_blocks' => fetchValue($pdo, "SELECT COUNT(*) FROM blocks"),
    'total_districts' => fetchValue($pdo, "SELECT COUNT(*) FROM districts"),
    
    // User Statistics
    'total_reps' => fetchValue($pdo, "SELECT COUNT(*) FROM users WHERE role = 'REP'"),
    'active_reps_today' => fetchValue($pdo, "
        SELECT COUNT(DISTINCT visited_by) 
        FROM visits 
        WHERE visit_date = ?
    ", [$today]),
    'inactive_reps' => fetchValue($pdo, "
        SELECT COUNT(*) FROM users 
        WHERE role = 'REP' 
            AND id NOT IN (
                SELECT DISTINCT visited_by 
                FROM visits 
                WHERE visit_date >= DATE_SUB(?, INTERVAL 7 DAY)
            )
    ", [$today]),
    
    // Visit Statistics
    'total_visits' => fetchValue($pdo, "SELECT COUNT(*) FROM visits"),
    'visits_today' => fetchValue($pdo, "SELECT COUNT(*) FROM visits WHERE visit_date = ?", [$today]),
    'visits_this_month' => fetchValue($pdo, "SELECT COUNT(*) FROM visits WHERE visit_date >= ?", [$startOfMonth]),
    'visits_this_year' => fetchValue($pdo, "SELECT COUNT(*) FROM visits WHERE visit_date >= ?", [$startOfYear]),
    
    // Coverage Statistics
    'schools_visited' => fetchValue($pdo, "SELECT COUNT(DISTINCT school_id) FROM visits"),
    'schools_never_visited' => fetchValue($pdo, "
        SELECT COUNT(*) FROM schools 
        WHERE id NOT IN (SELECT DISTINCT school_id FROM visits)
    "),
    
    // Completed Schools (12+ classes)
    'completed_schools' => fetchValue($pdo, "
        SELECT COUNT(DISTINCT v.school_id)
        FROM visits v
        JOIN visit_student_interactions vsi ON vsi.visit_id = v.id
        GROUP BY v.school_id
        HAVING COUNT(DISTINCT vsi.class) >= 12
    "),
    
    // Verification Statistics
    'pending_verifications' => fetchValue($pdo, "
        SELECT COUNT(*) FROM school_verification 
        WHERE status = 'PENDING'
    "),
    'approved_verifications' => fetchValue($pdo, "
        SELECT COUNT(*) FROM school_verification 
        WHERE status = 'APPROVED'
    "),
    'rejected_verifications' => fetchValue($pdo, "
        SELECT COUNT(*) FROM school_verification 
        WHERE status = 'REJECTED'
    "),
    
    // Follow-up Statistics
    'followups_today' => fetchValue($pdo, "
        SELECT COUNT(*) FROM visits 
        WHERE followup_required = 1 
            AND followup_date = ?
    ", [$today]),
    'followups_overdue' => fetchValue($pdo, "
        SELECT COUNT(*) FROM visits 
        WHERE followup_required = 1 
            AND followup_date < ?
            AND followup_date IS NOT NULL
    ", [$today]),
    'followups_week' => fetchValue($pdo, "
        SELECT COUNT(*) FROM visits 
        WHERE followup_required = 1 
            AND followup_date BETWEEN ? AND ?
    ", [$today, $nextWeek]),
    
    // Permission Statistics
    'permission_granted' => fetchValue($pdo, "SELECT COUNT(*) FROM visits WHERE permission_status = 'GRANTED'"),
    'permission_pending' => fetchValue($pdo, "SELECT COUNT(*) FROM visits WHERE permission_status = 'PENDING'"),
    'permission_denied' => fetchValue($pdo, "SELECT COUNT(*) FROM visits WHERE permission_status = 'NOT_ALLOWED'"),
];

// Calculate percentages
$kpi['coverage_pct'] = $kpi['total_schools'] > 0 
    ? round(($kpi['schools_visited'] / $kpi['total_schools']) * 100, 1) 
    : 0;
    
$kpi['completion_pct'] = $kpi['total_schools'] > 0 
    ? round(($kpi['completed_schools'] / $kpi['total_schools']) * 100, 1) 
    : 0;

// Fix for completed_schools - if it returns array, count it properly
if (is_array($kpi['completed_schools'])) {
    $kpi['completed_schools'] = count($kpi['completed_schools']);
}

// ---------- Charts Data ----------
// Daily visits (last 30 days)
$dailyVisits = fetchAll($pdo, "
    SELECT 
        visit_date AS date,
        COUNT(*) AS count,
        COUNT(DISTINCT visited_by) AS unique_reps
    FROM visits
    WHERE visit_date >= DATE_SUB(?, INTERVAL 29 DAY)
    GROUP BY visit_date
    ORDER BY visit_date ASC
", [$today]);

// Rep performance (top 20) - FIXED: Added all non-aggregated columns to GROUP BY
$repPerformance = fetchAll($pdo, "
    SELECT 
        u.id,
        u.name,
        u.email,
        COUNT(DISTINCT v.id) AS total_visits,
        COUNT(DISTINCT v.school_id) AS unique_schools,
        COUNT(DISTINCT CASE WHEN v.visit_date = ? THEN v.id END) AS visits_today,
        COUNT(DISTINCT CASE WHEN v.visit_date >= ? THEN v.id END) AS visits_this_month,
        COUNT(DISTINCT vsi.id) AS total_interactions,
        (
            SELECT COUNT(*) FROM school_verification sv 
            WHERE sv.representative_id = u.id AND sv.status = 'PENDING'
        ) AS pending_verifications
    FROM users u
    LEFT JOIN visits v ON v.visited_by = u.id
    LEFT JOIN visit_student_interactions vsi ON vsi.visit_id = v.id
    WHERE u.role = 'REP'
    GROUP BY u.id, u.name, u.email
    ORDER BY total_visits DESC
    LIMIT 20
", [$today, $startOfMonth]);

// Block-wise coverage - FIXED: Corrected the school relationship through GP
$blockCoverage = fetchAll($pdo, "
    SELECT 
        b.id,
        b.name AS block_name,
        d.name AS district_name,
        COUNT(DISTINCT s.id) AS total_schools,
        COUNT(DISTINCT v.school_id) AS visited_schools,
        ROUND(COUNT(DISTINCT v.school_id) / COUNT(DISTINCT s.id) * 100, 1) AS coverage_pct,
        COUNT(DISTINCT v.id) AS total_visits,
        COUNT(DISTINCT v.visited_by) AS active_reps
    FROM blocks b
    JOIN districts d ON d.id = b.district_id
    LEFT JOIN gps g ON g.block_id = b.id
    LEFT JOIN schools s ON s.gp_id = g.id
    LEFT JOIN visits v ON v.school_id = s.id
    GROUP BY b.id, b.name, d.name
    HAVING total_schools > 0
    ORDER BY coverage_pct ASC
    LIMIT 20
");

// GP-wise performance - FIXED: Correct joins
$gpPerformance = fetchAll($pdo, "
    SELECT 
        g.id,
        g.name AS gp_name,
        b.name AS block_name,
        d.name AS district_name,
        COUNT(DISTINCT s.id) AS total_schools,
        COUNT(DISTINCT v.school_id) AS visited_schools,
        COUNT(DISTINCT v.id) AS total_visits,
        COUNT(DISTINCT v.visited_by) AS assigned_reps,
        COUNT(DISTINCT CASE WHEN v.visit_date >= ? THEN v.school_id END) AS visited_this_month
    FROM gps g
    JOIN blocks b ON b.id = g.block_id
    JOIN districts d ON d.id = b.district_id
    LEFT JOIN schools s ON s.gp_id = g.id
    LEFT JOIN visits v ON v.school_id = s.id
    GROUP BY g.id, g.name, b.name, d.name
    ORDER BY visited_schools DESC
    LIMIT 15
", [$startOfMonth]);

// Pending verifications list - FIXED: Added all joins correctly
$pendingVerifications = fetchAll($pdo, "
    SELECT 
        sv.id,
        sv.requested_at,
        sv.school_id,
        s.name AS school_name,
        g.name AS gp_name,
        b.name AS block_name,
        d.name AS district_name,
        u.name AS rep_name,
        u.email AS rep_email,
        DATEDIFF(?, sv.requested_at) AS days_pending
    FROM school_verification sv
    JOIN schools s ON s.id = sv.school_id
    JOIN gps g ON g.id = s.gp_id
    JOIN blocks b ON b.id = g.block_id
    JOIN districts d ON d.id = b.district_id
    JOIN users u ON u.id = sv.representative_id
    WHERE sv.status = 'PENDING'
    ORDER BY sv.requested_at ASC
    LIMIT 20
", [$today]);

// Recent activities - FIXED: Correct joins
$recentActivities = fetchAll($pdo, "
    (SELECT 
        'visit' AS type,
        v.id,
        v.visit_date AS date,
        v.permission_status,
        s.name AS school_name,
        u.name AS rep_name,
        NULL AS status,
        CONCAT('Visited ', s.name) AS description
    FROM visits v
    JOIN schools s ON s.id = v.school_id
    JOIN users u ON u.id = v.visited_by
    ORDER BY v.visit_date DESC
    LIMIT 10)
    
    UNION ALL
    
    (SELECT 
        'verification' AS type,
        sv.id,
        sv.requested_at AS date,
        NULL AS permission_status,
        s.name AS school_name,
        u.name AS rep_name,
        sv.status,
        CONCAT('Verification ', LOWER(sv.status), ' for ', s.name) AS description
    FROM school_verification sv
    JOIN schools s ON s.id = sv.school_id
    JOIN users u ON u.id = sv.representative_id
    ORDER BY sv.requested_at DESC
    LIMIT 10)
    
    ORDER BY date DESC
    LIMIT 15
");

// Schools never visited - FIXED: Correct joins through GP
$neverVisitedSchools = fetchAll($pdo, "
    SELECT 
        s.id,
        s.name AS school_name,
        s.category,
        g.name AS gp_name,
        b.name AS block_name,
        d.name AS district_name,
        (
            SELECT COUNT(*) FROM gp_assignments 
            WHERE gp_id = s.gp_id AND status = 'ACTIVE'
        ) AS assigned_reps
    FROM schools s
    JOIN gps g ON g.id = s.gp_id
    JOIN blocks b ON b.id = g.block_id
    JOIN districts d ON d.id = b.district_id
    WHERE s.id NOT IN (SELECT DISTINCT school_id FROM visits)
    ORDER BY d.name, b.name, g.name
    LIMIT 15
");

// Follow-ups due - FIXED: Correct joins
$followupsDue = fetchAll($pdo, "
    SELECT 
        v.followup_date,
        s.name AS school_name,
        g.name AS gp_name,
        b.name AS block_name,
        u.name AS rep_name,
        DATEDIFF(v.followup_date, ?) AS days_left
    FROM visits v
    JOIN schools s ON s.id = v.school_id
    JOIN gps g ON g.id = s.gp_id
    JOIN blocks b ON b.id = g.block_id
    JOIN users u ON u.id = v.visited_by
    WHERE v.followup_required = 1 
        AND v.followup_date IS NOT NULL
        AND v.followup_date >= ?
    ORDER BY v.followup_date ASC
    LIMIT 15
", [$today, $today]);

// System health metrics
$systemHealth = [
    'orphan_schools' => fetchValue($pdo, "
        SELECT COUNT(*) FROM schools 
        WHERE gp_id NOT IN (SELECT id FROM gps)
    "),
    'unassigned_gps' => fetchValue($pdo, "
        SELECT COUNT(*) FROM gps 
        WHERE id NOT IN (SELECT DISTINCT gp_id FROM gp_assignments WHERE status = 'ACTIVE')
    "),
    'reps_without_assignments' => fetchValue($pdo, "
        SELECT COUNT(*) FROM users 
        WHERE role = 'REP' 
            AND id NOT IN (SELECT DISTINCT user_id FROM gp_assignments WHERE status = 'ACTIVE')
    "),
    'visits_without_interactions' => fetchValue($pdo, "
        SELECT COUNT(*) FROM visits v
        WHERE NOT EXISTS (
            SELECT 1 FROM visit_student_interactions WHERE visit_id = v.id
        )
    "),
];

// Format chart data
$chartLabels = [];
$chartVisits = [];
$chartUniqueReps = [];

foreach ($dailyVisits as $day) {
    $chartLabels[] = date('d M', strtotime($day['date']));
    $chartVisits[] = (int)$day['count'];
    $chartUniqueReps[] = (int)$day['unique_reps'];
}

$page_title = "Admin Dashboard";
require_once __DIR__ . "/../partials/header.php";
?>

<!-- Bootstrap Icons -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.11.3/font/bootstrap-icons.min.css">

<style>
:root {
    --primary: #4361ee;
    --success: #06d6a0;
    --warning: #ffb703;
    --danger: #ef476f;
    --dark: #073b4c;
    --info: #17a2b8;
    --purple: #6f42c1;
    --orange: #fd7e14;
    --light: #f8f9fa;
    --gray: #6c757d;
}

body {
    background: #f0f2f5;
    font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, Helvetica, Arial, sans-serif;
}

/* Dashboard Layout */
.admin-container {
    max-width: 1600px;
    margin: 0 auto;
    padding: 20px;
}

/* Welcome Header */
.welcome-header {
    background: linear-gradient(135deg, #1e293b, #0f172a);
    color: white;
    border-radius: 24px;
    padding: 30px;
    margin-bottom: 30px;
    box-shadow: 0 20px 40px rgba(0,0,0,0.1);
    position: relative;
    overflow: hidden;
}

.welcome-header::before {
    content: '';
    position: absolute;
    top: -50%;
    right: -50%;
    width: 200%;
    height: 200%;
    background: radial-gradient(circle, rgba(255,255,255,0.1) 0%, transparent 70%);
    animation: rotate 30s linear infinite;
}

@keyframes rotate {
    from { transform: rotate(0deg); }
    to { transform: rotate(360deg); }
}

.welcome-title {
    font-size: 2.2rem;
    font-weight: 700;
    margin-bottom: 10px;
    position: relative;
}

.welcome-subtitle {
    display: flex;
    gap: 30px;
    flex-wrap: wrap;
    opacity: 0.9;
    font-size: 1rem;
    position: relative;
}

.welcome-subtitle i {
    margin-right: 8px;
    color: var(--warning);
}

/* Stats Grid */
.stats-grid {
    display: grid;
    grid-template-columns: repeat(auto-fit, minmax(240px, 1fr));
    gap: 20px;
    margin-bottom: 30px;
}

.stat-card {
    background: white;
    border-radius: 20px;
    padding: 24px;
    box-shadow: 0 4px 12px rgba(0,0,0,0.03);
    border: 1px solid #e9ecef;
    transition: all 0.3s;
    position: relative;
    overflow: hidden;
}

.stat-card:hover {
    transform: translateY(-4px);
    box-shadow: 0 12px 30px rgba(0,0,0,0.08);
}

.stat-card::after {
    content: '';
    position: absolute;
    top: 0;
    right: 0;
    width: 100px;
    height: 100px;
    background: linear-gradient(135deg, transparent 50%, rgba(67, 97, 238, 0.03) 100%);
    border-radius: 50%;
}

.stat-icon {
    width: 56px;
    height: 56px;
    background: linear-gradient(135deg, var(--primary), #3a0ca3);
    border-radius: 16px;
    display: flex;
    align-items: center;
    justify-content: center;
    color: white;
    font-size: 1.8rem;
    margin-bottom: 16px;
    box-shadow: 0 8px 16px rgba(67, 97, 238, 0.2);
}

.stat-value {
    font-size: 2.2rem;
    font-weight: 700;
    color: var(--dark);
    margin-bottom: 4px;
    line-height: 1.2;
}

.stat-label {
    color: var(--gray);
    font-size: 0.9rem;
    display: flex;
    align-items: center;
    gap: 6px;
}

.stat-trend {
    margin-top: 12px;
    font-size: 0.8rem;
    padding: 4px 8px;
    background: var(--light);
    border-radius: 20px;
    display: inline-flex;
    align-items: center;
    gap: 4px;
}

.trend-up { color: var(--success); }
.trend-down { color: var(--danger); }

/* Section Styles */
.section-header {
    display: flex;
    justify-content: space-between;
    align-items: center;
    margin-bottom: 20px;
    flex-wrap: wrap;
    gap: 15px;
}

.section-title {
    display: flex;
    align-items: center;
    gap: 12px;
}

.section-title i {
    width: 40px;
    height: 40px;
    background: white;
    border-radius: 12px;
    display: flex;
    align-items: center;
    justify-content: center;
    color: var(--primary);
    font-size: 1.4rem;
    box-shadow: 0 4px 8px rgba(0,0,0,0.03);
}

.section-title h2 {
    margin: 0;
    font-size: 1.5rem;
    font-weight: 600;
    color: var(--dark);
}

.section-actions {
    display: flex;
    gap: 10px;
}

/* Card Styles */
.dashboard-card {
    background: white;
    border-radius: 24px;
    padding: 24px;
    border: 1px solid #e9ecef;
    margin-bottom: 30px;
    box-shadow: 0 4px 12px rgba(0,0,0,0.02);
}

.chart-container {
    position: relative;
    width: 100%;
    min-height: 350px;
}

/* Table Styles */
.table-responsive {
    border-radius: 16px;
    overflow: auto;
}

.table {
    margin: 0;
}

.table th {
    background: var(--light);
    border-top: none;
    color: var(--gray);
    font-weight: 600;
    font-size: 0.8rem;
    text-transform: uppercase;
    letter-spacing: 0.5px;
    padding: 16px 12px;
}

.table td {
    padding: 16px 12px;
    vertical-align: middle;
    color: var(--dark);
    border-bottom: 1px solid #e9ecef;
}

.table tr:hover td {
    background: rgba(67, 97, 238, 0.02);
}

/* Badge Styles */
.badge-custom {
    padding: 6px 12px;
    border-radius: 30px;
    font-size: 0.75rem;
    font-weight: 600;
    display: inline-flex;
    align-items: center;
    gap: 6px;
}

.badge-success { background: rgba(6, 214, 160, 0.1); color: var(--success); }
.badge-warning { background: rgba(255, 183, 3, 0.1); color: var(--warning); }
.badge-danger { background: rgba(239, 71, 111, 0.1); color: var(--danger); }
.badge-info { background: rgba(23, 162, 184, 0.1); color: var(--info); }
.badge-purple { background: rgba(111, 66, 193, 0.1); color: var(--purple); }
.badge-primary { background: rgba(67, 97, 238, 0.1); color: var(--primary); }

/* Progress Bar */
.progress-thin {
    height: 6px;
    background: var(--light);
    border-radius: 3px;
    overflow: hidden;
    margin-top: 8px;
}

.progress-fill {
    height: 100%;
    background: linear-gradient(90deg, var(--primary), var(--success));
    border-radius: 3px;
    transition: width 0.3s;
}

/* Alert Cards */
.alert-card {
    background: linear-gradient(135deg, #fff5f5, #fff);
    border-left: 4px solid var(--danger);
    border-radius: 16px;
    padding: 16px;
    margin-bottom: 12px;
    display: flex;
    align-items: center;
    gap: 12px;
    box-shadow: 0 2px 8px rgba(0,0,0,0.02);
}

.alert-card.warning {
    background: linear-gradient(135deg, #fff9e6, #fff);
    border-left-color: var(--warning);
}

.alert-card.info {
    background: linear-gradient(135deg, #e6f3ff, #fff);
    border-left-color: var(--info);
}

/* Quick Actions */
.quick-actions-grid {
    display: grid;
    grid-template-columns: repeat(auto-fit, minmax(180px, 1fr));
    gap: 12px;
    margin-bottom: 30px;
}

.quick-action-item {
    background: white;
    border: 1px solid #e9ecef;
    border-radius: 16px;
    padding: 20px;
    text-align: center;
    text-decoration: none;
    color: var(--dark);
    transition: all 0.2s;
}

.quick-action-item:hover {
    background: var(--primary);
    color: white;
    transform: translateY(-2px);
    box-shadow: 0 12px 24px rgba(67, 97, 238, 0.2);
    border-color: transparent;
}

.quick-action-item i {
    font-size: 2rem;
    margin-bottom: 10px;
    display: block;
}

.quick-action-item span {
    font-size: 0.9rem;
    font-weight: 500;
}

/* Mobile Responsive */
@media (max-width: 768px) {
    .admin-container {
        padding: 12px;
    }
    
    .welcome-header {
        padding: 20px;
    }
    
    .welcome-title {
        font-size: 1.6rem;
    }
    
    .welcome-subtitle {
        gap: 15px;
        font-size: 0.9rem;
    }
    
    .stats-grid {
        grid-template-columns: 1fr;
        gap: 12px;
    }
    
    .stat-card {
        padding: 18px;
    }
    
    .stat-value {
        font-size: 1.8rem;
    }
    
    .section-title h2 {
        font-size: 1.2rem;
    }
    
    .dashboard-card {
        padding: 16px;
    }
    
    .chart-container {
        min-height: 250px;
    }
    
    .table td, .table th {
        padding: 12px 8px;
        font-size: 0.85rem;
    }
    
    .quick-actions-grid {
        grid-template-columns: repeat(2, 1fr);
    }
}

/* Tablet */
@media (min-width: 769px) and (max-width: 1024px) {
    .stats-grid {
        grid-template-columns: repeat(2, 1fr);
    }
}
</style>

<div class="admin-container">
    <!-- Welcome Header -->
    <div class="welcome-header">
        <div class="welcome-title">
            👋 Welcome back, <?= htmlspecialchars(explode(' ', $name)[0]) ?>!
        </div>
        <div class="welcome-subtitle">
            <span><i class="bi bi-calendar"></i> <?= date('l, d F Y', strtotime($today)) ?></span>
            <span><i class="bi bi-building"></i> <?= number_format($kpi['total_schools']) ?> Schools</span>
            <span><i class="bi bi-people"></i> <?= $kpi['total_reps'] ?> Reps</span>
            <span><i class="bi bi-check-circle"></i> <?= $kpi['coverage_pct'] ?>% Coverage</span>
        </div>
    </div>

    <!-- Quick Actions -->
    <div class="quick-actions-grid">
        <a href="/school-crm/public/admin/reps.php" class="quick-action-item">
            <i class="bi bi-people"></i>
            <span>Manage Reps</span>
        </a>
        <a href="/school-crm/public/admin/assignments.php" class="quick-action-item">
            <i class="bi bi-diagram-3"></i>
            <span>Assignments</span>
        </a>
        <a href="/school-crm/public/admin/verifications.php" class="quick-action-item">
            <i class="bi bi-check2-circle"></i>
            <span>Verifications</span>
            <?php if ($kpi['pending_verifications'] > 0): ?>
                <span class="badge bg-danger ms-1"><?= $kpi['pending_verifications'] ?></span>
            <?php endif; ?>
        </a>
        <a href="/school-crm/public/admin/reports.php" class="quick-action-item">
            <i class="bi bi-file-text"></i>
            <span>Reports</span>
        </a>
        <a href="/school-crm/public/admin/schools.php" class="quick-action-item">
            <i class="bi bi-building"></i>
            <span>Schools</span>
        </a>
        <a href="/school-crm/public/admin/settings.php" class="quick-action-item">
            <i class="bi bi-gear"></i>
            <span>Settings</span>
        </a>
    </div>

    <!-- Key Statistics -->
    <div class="stats-grid">
        <div class="stat-card">
            <div class="stat-icon" style="background: linear-gradient(135deg, #4361ee, #3a0ca3);">
                <i class="bi bi-building"></i>
            </div>
            <div class="stat-value"><?= number_format($kpi['total_schools']) ?></div>
            <div class="stat-label">Total Schools</div>
            <div class="stat-trend">
                <i class="bi bi-eye"></i> <?= $kpi['schools_visited'] ?> visited
            </div>
            <div class="progress-thin">
                <div class="progress-fill" style="width: <?= $kpi['coverage_pct'] ?>%"></div>
            </div>
        </div>

        <div class="stat-card">
            <div class="stat-icon" style="background: linear-gradient(135deg, #06d6a0, #05b588);">
                <i class="bi bi-people"></i>
            </div>
            <div class="stat-value"><?= $kpi['total_reps'] ?></div>
            <div class="stat-label">Total Representatives</div>
            <div class="stat-trend">
                <i class="bi bi-calendar-check"></i> <?= $kpi['active_reps_today'] ?> active today
            </div>
        </div>

        <div class="stat-card">
            <div class="stat-icon" style="background: linear-gradient(135deg, #ffb703, #faa300);">
                <i class="bi bi-calendar-check"></i>
            </div>
            <div class="stat-value"><?= number_format($kpi['total_visits']) ?></div>
            <div class="stat-label">Total Visits</div>
            <div class="stat-trend">
                <i class="bi bi-graph-up"></i> <?= $kpi['visits_today'] ?> today
            </div>
        </div>

        <div class="stat-card">
            <div class="stat-icon" style="background: linear-gradient(135deg, #ef476f, #d43f62);">
                <i class="bi bi-hourglass-split"></i>
            </div>
            <div class="stat-value"><?= $kpi['pending_verifications'] ?></div>
            <div class="stat-label">Pending Verifications</div>
            <div class="stat-trend <?= $kpi['pending_verifications'] > 0 ? 'trend-up' : '' ?>">
                <i class="bi bi-clock"></i> <?= $kpi['approved_verifications'] ?> approved
            </div>
        </div>
    </div>

    <!-- System Health Alerts -->
    <?php if ($systemHealth['orphan_schools'] > 0 || $systemHealth['unassigned_gps'] > 0 || $kpi['followups_overdue'] > 0): ?>
    <div class="dashboard-card" style="padding: 20px;">
        <div class="section-title" style="margin-bottom: 15px;">
            <i class="bi bi-exclamation-triangle" style="color: var(--warning);"></i>
            <h2>System Alerts</h2>
        </div>
        <div class="row g-3">
            <?php if ($systemHealth['orphan_schools'] > 0): ?>
            <div class="col-12 col-md-4">
                <div class="alert-card warning">
                    <i class="bi bi-building-x" style="font-size: 1.5rem;"></i>
                    <div>
                        <strong><?= $systemHealth['orphan_schools'] ?> Orphan Schools</strong>
                        <div class="small">Not assigned to any GP</div>
                    </div>
                </div>
            </div>
            <?php endif; ?>
            
            <?php if ($systemHealth['unassigned_gps'] > 0): ?>
            <div class="col-12 col-md-4">
                <div class="alert-card warning">
                    <i class="bi bi-diagram-3" style="font-size: 1.5rem;"></i>
                    <div>
                        <strong><?= $systemHealth['unassigned_gps'] ?> Unassigned GPs</strong>
                        <div class="small">No representative assigned</div>
                    </div>
                </div>
            </div>
            <?php endif; ?>
            
            <?php if ($kpi['followups_overdue'] > 0): ?>
            <div class="col-12 col-md-4">
                <div class="alert-card danger">
                    <i class="bi bi-bell" style="font-size: 1.5rem;"></i>
                    <div>
                        <strong><?= $kpi['followups_overdue'] ?> Overdue Follow-ups</strong>
                        <div class="small">Requires immediate attention</div>
                    </div>
                </div>
            </div>
            <?php endif; ?>
        </div>
    </div>
    <?php endif; ?>

    <!-- Charts Row -->
    <div class="row g-3 mb-3">
        <div class="col-12 col-lg-8">
            <div class="dashboard-card">
                <div class="section-header">
                    <div class="section-title">
                        <i class="bi bi-graph-up"></i>
                        <h2>Daily Visits (Last 30 Days)</h2>
                    </div>
                    <div class="section-actions">
                        <span class="badge-custom badge-primary">
                            <i class="bi bi-people"></i> Active Reps
                        </span>
                    </div>
                </div>
                <div class="chart-container">
                    <canvas id="dailyChart"></canvas>
                </div>
            </div>
        </div>

        <div class="col-12 col-lg-4">
            <div class="dashboard-card">
                <div class="section-header">
                    <div class="section-title">
                        <i class="bi bi-pie-chart"></i>
                        <h2>Permission Status</h2>
                    </div>
                </div>
                <div class="chart-container" style="min-height: 300px;">
                    <canvas id="permChart"></canvas>
                </div>
                <div class="row g-2 mt-3">
                    <div class="col-4 text-center">
                        <div class="small text-muted">Granted</div>
                        <div class="fw-bold text-success"><?= $kpi['permission_granted'] ?></div>
                    </div>
                    <div class="col-4 text-center">
                        <div class="small text-muted">Pending</div>
                        <div class="fw-bold text-warning"><?= $kpi['permission_pending'] ?></div>
                    </div>
                    <div class="col-4 text-center">
                        <div class="small text-muted">Denied</div>
                        <div class="fw-bold text-danger"><?= $kpi['permission_denied'] ?></div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <!-- Pending Verifications -->
    <?php if (!empty($pendingVerifications)): ?>
    <div class="dashboard-card">
        <div class="section-header">
            <div class="section-title">
                <i class="bi bi-hourglass-split" style="color: var(--warning);"></i>
                <h2>Pending Verifications</h2>
            </div>
            <div class="section-actions">
                <a href="/school-crm/public/admin/verifications.php" class="btn btn-sm btn-outline-primary">
                    View All <i class="bi bi-arrow-right"></i>
                </a>
            </div>
        </div>
        <div class="table-responsive">
            <table class="table">
                <thead>
                    <tr>
                        <th>Requested</th>
                        <th>School</th>
                        <th>Location</th>
                        <th>Representative</th>
                        <th>Days Pending</th>
                        <th>Action</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($pendingVerifications as $pv): ?>
                    <tr>
                        <td class="text-nowrap"><?= date('d M Y', strtotime($pv['requested_at'])) ?></td>
                        <td>
                            <strong><?= htmlspecialchars($pv['school_name']) ?></strong>
                        </td>
                        <td>
                            <?= htmlspecialchars($pv['gp_name']) ?><br>
                            <small class="text-muted"><?= htmlspecialchars($pv['block_name']) ?>, <?= htmlspecialchars($pv['district_name']) ?></small>
                        </td>
                        <td>
                            <span><?= htmlspecialchars($pv['rep_name']) ?></span><br>
                            <small class="text-muted"><?= htmlspecialchars($pv['rep_email']) ?></small>
                        </td>
                        <td>
                            <span class="badge-custom <?= $pv['days_pending'] > 7 ? 'badge-warning' : 'badge-info' ?>">
                                <?= $pv['days_pending'] ?> days
                            </span>
                        </td>
                        <td>
                            <a href="/school-crm/public/admin/verify.php?id=<?= $pv['id'] ?>" class="btn btn-sm btn-primary">
                                Review
                            </a>
                        </td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
    <?php endif; ?>

    <!-- Two Column Tables -->
    <div class="row g-3 mb-3">
        <!-- Rep Performance -->
        <div class="col-12 col-lg-6">
            <div class="dashboard-card">
                <div class="section-header">
                    <div class="section-title">
                        <i class="bi bi-trophy"></i>
                        <h2>Top Performing Reps</h2>
                    </div>
                    <div class="section-actions">
                        <a href="/school-crm/public/admin/reps.php" class="btn btn-sm btn-outline-primary">
                            View All
                        </a>
                    </div>
                </div>
                <div class="table-responsive">
                    <table class="table">
                        <thead>
                            <tr>
                                <th>Representative</th>
                                <th>Visits</th>
                                <th>Schools</th>
                                <th>This Month</th>
                                <th>Pending</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($repPerformance as $rep): ?>
                            <tr>
                                <td>
                                    <strong><?= htmlspecialchars($rep['name'] ?? 'Unknown') ?></strong>
                                    <small class="d-block text-muted"><?= htmlspecialchars($rep['email'] ?? '') ?></small>
                                </td>
                                <td class="fw-bold"><?= $rep['total_visits'] ?? 0 ?></td>
                                <td><?= $rep['unique_schools'] ?? 0 ?></td>
                                <td>
                                    <span class="badge-custom badge-success">
                                        <?= $rep['visits_this_month'] ?? 0 ?>
                                    </span>
                                </td>
                                <td>
                                    <?php if (($rep['pending_verifications'] ?? 0) > 0): ?>
                                        <span class="badge-custom badge-warning">
                                            <?= $rep['pending_verifications'] ?> pending
                                        </span>
                                    <?php else: ?>
                                        <span class="text-muted">0</span>
                                    <?php endif; ?>
                                </td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

        <!-- GP Performance -->
        <div class="col-12 col-lg-6">
            <div class="dashboard-card">
                <div class="section-header">
                    <div class="section-title">
                        <i class="bi bi-diagram-3"></i>
                        <h2>GP Performance</h2>
                    </div>
                </div>
                <div class="table-responsive">
                    <table class="table">
                        <thead>
                            <tr>
                                <th>GP / Block</th>
                                <th>Schools</th>
                                <th>Visited</th>
                                <th>Visits</th>
                                <th>Coverage</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($gpPerformance as $gp): ?>
                            <tr>
                                <td>
                                    <strong><?= htmlspecialchars($gp['gp_name'] ?? '') ?></strong>
                                    <small class="d-block text-muted">
                                        <?= htmlspecialchars($gp['block_name'] ?? '') ?>, <?= htmlspecialchars($gp['district_name'] ?? '') ?>
                                    </small>
                                </td>
                                <td><?= $gp['total_schools'] ?? 0 ?></td>
                                <td><?= $gp['visited_schools'] ?? 0 ?></td>
                                <td><?= $gp['total_visits'] ?? 0 ?></td>
                                <td>
                                    <?php 
                                    $cov = $gp['total_schools'] > 0 
                                        ? round(($gp['visited_schools'] / $gp['total_schools']) * 100, 1) 
                                        : 0;
                                    ?>
                                    <span class="badge-custom <?= $cov >= 70 ? 'badge-success' : ($cov >= 40 ? 'badge-warning' : 'badge-danger') ?>">
                                        <?= $cov ?>%
                                    </span>
                                </td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <!-- Second Two Column Row -->
    <div class="row g-3 mb-3">
        <!-- Never Visited Schools -->
        <div class="col-12 col-lg-6">
            <div class="dashboard-card">
                <div class="section-header">
                    <div class="section-title">
                        <i class="bi bi-building-x" style="color: var(--danger);"></i>
                        <h2>Schools Never Visited</h2>
                    </div>
                </div>
                <div class="table-responsive">
                    <table class="table">
                        <thead>
                            <tr>
                                <th>School</th>
                                <th>Location</th>
                                <th>Category</th>
                                <th>Assigned Reps</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (empty($neverVisitedSchools)): ?>
                                <tr>
                                    <td colspan="4" class="text-center text-muted py-4">
                                        <i class="bi bi-check-circle text-success"></i> All schools have been visited!
                                    </td>
                                </tr>
                            <?php else: ?>
                                <?php foreach ($neverVisitedSchools as $school): ?>
                                <tr>
                                    <td>
                                        <strong><?= htmlspecialchars($school['school_name'] ?? '') ?></strong>
                                    </td>
                                    <td>
                                        <?= htmlspecialchars($school['gp_name'] ?? '') ?><br>
                                        <small class="text-muted"><?= htmlspecialchars($school['block_name'] ?? '') ?></small>
                                    </td>
                                    <td><?= htmlspecialchars($school['category'] ?? 'N/A') ?></td>
                                    <td>
                                        <span class="badge-custom badge-info">
                                            <?= $school['assigned_reps'] ?? 0 ?> reps
                                        </span>
                                    </td>
                                </tr>
                                <?php endforeach; ?>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

        <!-- Follow-ups Due -->
        <div class="col-12 col-lg-6">
            <div class="dashboard-card">
                <div class="section-header">
                    <div class="section-title">
                        <i class="bi bi-bell" style="color: var(--warning);"></i>
                        <h2>Upcoming Follow-ups</h2>
                    </div>
                </div>
                <div class="table-responsive">
                    <table class="table">
                        <thead>
                            <tr>
                                <th>Due Date</th>
                                <th>School</th>
                                <th>Location</th>
                                <th>Rep</th>
                                <th>Status</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (empty($followupsDue)): ?>
                                <tr>
                                    <td colspan="5" class="text-center text-muted py-4">
                                        <i class="bi bi-calendar-check"></i> No upcoming follow-ups
                                    </td>
                                </tr>
                            <?php else: ?>
                                <?php foreach ($followupsDue as $f): ?>
                                <tr>
                                    <td class="text-nowrap"><?= date('d M', strtotime($f['followup_date'])) ?></td>
                                    <td><?= htmlspecialchars($f['school_name'] ?? '') ?></td>
                                    <td><?= htmlspecialchars($f['gp_name'] ?? '') ?></td>
                                    <td><?= htmlspecialchars($f['rep_name'] ?? '') ?></td>
                                    <td>
                                        <?php if ($f['days_left'] < 0): ?>
                                            <span class="badge-custom badge-danger">Overdue</span>
                                        <?php elseif ($f['days_left'] == 0): ?>
                                            <span class="badge-custom badge-warning">Today</span>
                                        <?php elseif ($f['days_left'] <= 2): ?>
                                            <span class="badge-custom badge-info">Soon</span>
                                        <?php else: ?>
                                            <span class="badge-custom badge-success"><?= $f['days_left'] ?> days</span>
                                        <?php endif; ?>
                                    </td>
                                </tr>
                                <?php endforeach; ?>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <!-- Recent Activities -->
    <div class="dashboard-card">
        <div class="section-header">
            <div class="section-title">
                <i class="bi bi-activity"></i>
                <h2>Recent Activities</h2>
            </div>
        </div>
        <div class="table-responsive">
            <table class="table">
                <thead>
                    <tr>
                        <th>Time</th>
                        <th>Type</th>
                        <th>Description</th>
                        <th>Representative</th>
                        <th>Status</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($recentActivities as $activity): ?>
                    <tr>
                        <td class="text-nowrap"><?= date('d M H:i', strtotime($activity['date'])) ?></td>
                        <td>
                            <?php if ($activity['type'] === 'visit'): ?>
                                <span class="badge-custom badge-primary">
                                    <i class="bi bi-calendar-check"></i> Visit
                                </span>
                            <?php else: ?>
                                <span class="badge-custom badge-info">
                                    <i class="bi bi-check-circle"></i> Verification
                                </span>
                            <?php endif; ?>
                        </td>
                        <td><?= htmlspecialchars($activity['description'] ?? '') ?></td>
                        <td><?= htmlspecialchars($activity['rep_name'] ?? '') ?></td>
                        <td>
                            <?php if ($activity['type'] === 'visit'): ?>
                                <span class="badge-custom <?= $activity['permission_status'] === 'GRANTED' ? 'badge-success' : ($activity['permission_status'] === 'PENDING' ? 'badge-warning' : 'badge-danger') ?>">
                                    <?= $activity['permission_status'] ?? 'N/A' ?>
                                </span>
                            <?php else: ?>
                                <span class="badge-custom <?= $activity['status'] === 'APPROVED' ? 'badge-success' : ($activity['status'] === 'PENDING' ? 'badge-warning' : 'badge-danger') ?>">
                                    <?= $activity['status'] ?? 'N/A' ?>
                                </span>
                            <?php endif; ?>
                        </td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

<!-- Chart.js -->
<script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.1/dist/chart.umd.min.js"></script>
<script>
// Daily visits chart
const dailyCtx = document.getElementById('dailyChart').getContext('2d');
new Chart(dailyCtx, {
    type: 'line',
    data: {
        labels: <?= json_encode($chartLabels, JSON_UNESCAPED_SLASHES) ?>,
        datasets: [
            {
                label: 'Total Visits',
                data: <?= json_encode($chartVisits, JSON_UNESCAPED_SLASHES) ?>,
                borderColor: '#4361ee',
                backgroundColor: 'rgba(67, 97, 238, 0.1)',
                tension: 0.4,
                fill: true,
                yAxisID: 'y',
                pointBackgroundColor: '#4361ee',
                pointBorderColor: '#fff',
                pointBorderWidth: 2,
                pointRadius: 4
            },
            {
                label: 'Active Reps',
                data: <?= json_encode($chartUniqueReps, JSON_UNESCAPED_SLASHES) ?>,
                borderColor: '#06d6a0',
                backgroundColor: 'transparent',
                tension: 0.4,
                borderDash: [5, 5],
                yAxisID: 'y1',
                pointBackgroundColor: '#06d6a0',
                pointBorderColor: '#fff',
                pointBorderWidth: 2,
                pointRadius: 4
            }
        ]
    },
    options: {
        responsive: true,
        maintainAspectRatio: false,
        interaction: {
            mode: 'index',
            intersect: false,
        },
        plugins: {
            legend: {
                position: 'top',
                labels: {
                    usePointStyle: true,
                    boxWidth: 6
                }
            },
            tooltip: {
                backgroundColor: '#1e293b',
                titleColor: '#fff',
                bodyColor: '#fff'
            }
        },
        scales: {
            y: {
                type: 'linear',
                display: true,
                position: 'left',
                beginAtZero: true,
                grid: {
                    color: 'rgba(0,0,0,0.05)'
                },
                title: {
                    display: true,
                    text: 'Total Visits',
                    color: '#4361ee'
                }
            },
            y1: {
                type: 'linear',
                display: true,
                position: 'right',
                beginAtZero: true,
                grid: {
                    drawOnChartArea: false,
                },
                title: {
                    display: true,
                    text: 'Active Reps',
                    color: '#06d6a0'
                }
            },
            x: {
                grid: {
                    display: false
                },
                ticks: {
                    maxRotation: 45,
                    minRotation: 45,
                    maxTicksLimit: 15
                }
            }
        }
    }
});

// Permission chart
const permCtx = document.getElementById('permChart').getContext('2d');
new Chart(permCtx, {
    type: 'doughnut',
    data: {
        labels: ['Granted', 'Pending', 'Not Allowed'],
        datasets: [{
            data: [<?= $kpi['permission_granted'] ?>, <?= $kpi['permission_pending'] ?>, <?= $kpi['permission_denied'] ?>],
            backgroundColor: ['#06d6a0', '#ffb703', '#ef476f'],
            borderWidth: 0
        }]
    },
    options: {
        responsive: true,
        maintainAspectRatio: false,
        plugins: {
            legend: {
                position: 'bottom',
                labels: {
                    padding: 20,
                    font: {
                        size: 12
                    }
                }
            }
        },
        cutout: '65%'
    }
});
</script>

<?php require_once __DIR__ . "/../partials/footer.php"; ?>