<?php
// Filename: admin_statistics.php
require 'config.php';
requireLogin();

// SECURITY
if (!hasRole(['admin', 'principal'])) {
    denyAccess("Access Denied");
}

// --- 1. FETCH METRICS ---
$total_students_reported = $pdo->query("SELECT COUNT(DISTINCT student_id) FROM student_discipline")->fetchColumn();
$total_teachers_reporting = $pdo->query("SELECT COUNT(DISTINCT reported_by) FROM student_discipline")->fetchColumn();
$total_cases = $pdo->query("SELECT COUNT(*) FROM student_discipline")->fetchColumn();
$total_teacher_absences = $pdo->query("SELECT COUNT(*) FROM teacher_absences")->fetchColumn();

// --- 2. FETCH TOP LISTS ---
// 1. Top Students Absent
$top_student_absent = $pdo->query("SELECT s.full_name, c.class_name, COUNT(*) as count FROM student_attendance sa JOIN students s ON sa.student_id = s.id JOIN classes c ON s.class_id = c.id WHERE sa.status = 'absent' GROUP BY sa.student_id ORDER BY count DESC LIMIT 5")->fetchAll();

// 2. Top Students Disciplinary
$top_student_disc = $pdo->query("SELECT s.full_name, c.class_name, COUNT(*) as count FROM student_discipline sd JOIN students s ON sd.student_id = s.id JOIN classes c ON s.class_id = c.id GROUP BY sd.student_id ORDER BY count DESC LIMIT 5")->fetchAll();

// 3. Top Cases
$top_cases = $pdo->query("SELECT infraction, COUNT(*) as count FROM student_discipline GROUP BY infraction ORDER BY count DESC LIMIT 5")->fetchAll();

// 4. Top Reporters
$top_reporters = $pdo->query("SELECT u.full_name, COUNT(*) as count FROM student_discipline sd JOIN users u ON sd.reported_by = u.id GROUP BY sd.reported_by ORDER BY count DESC LIMIT 5")->fetchAll();

// 5. Top Teachers Absent
$top_teacher_absent = $pdo->query("SELECT u.full_name, COUNT(*) as count FROM teacher_absences ta JOIN users u ON ta.user_id = u.id GROUP BY ta.user_id ORDER BY count DESC LIMIT 5")->fetchAll();

// 6. Top Teachers Early
$top_teacher_early = $pdo->query("SELECT u.full_name, COUNT(*) as count FROM attendance_logs al JOIN timetable t ON al.timetable_id = t.id JOIN users u ON al.user_id = u.id WHERE TIME(al.scan_time) < t.start_time GROUP BY al.user_id ORDER BY count DESC LIMIT 5")->fetchAll();

// 7. Top Teachers Late > 20 Mins
$top_teacher_late = $pdo->query("SELECT u.full_name, COUNT(*) as count FROM attendance_logs al JOIN timetable t ON al.timetable_id = t.id JOIN users u ON al.user_id = u.id WHERE TIMESTAMPDIFF(MINUTE, CONCAT(DATE(al.scan_time), ' ', t.start_time), al.scan_time) > 20 GROUP BY al.user_id ORDER BY count DESC LIMIT 5")->fetchAll();

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>School Analytics</title>
    <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.11.1/font/bootstrap-icons.css">
    <style>
        body { background-color: #f4f6f9; font-family: sans-serif; padding-bottom: 80px; }
        
        /* HEADER */
        .class-header-card {
            background: linear-gradient(135deg, #0d6efd 0%, #0a58ca 100%);
            color: white; border-radius: 0 0 20px 20px;
            padding: 25px 20px 30px 20px; margin-bottom: 20px;
        }

        /* STAT CARDS */
        .stat-card {
            background: white; border: 0; border-radius: 15px;
            padding: 15px; height: 100%; box-shadow: 0 2px 10px rgba(0,0,0,0.03);
            display: flex; align-items: center; justify-content: space-between;
        }
        .icon-circle {
            width: 45px; height: 45px; border-radius: 12px;
            display: flex; align-items: center; justify-content: center;
            font-size: 1.2rem; margin-right: 10px;
        }
        .st-val { font-size: 1.5rem; font-weight: 800; line-height: 1; }
        .st-lbl { font-size: 0.7rem; color: #6c757d; text-transform: uppercase; font-weight: 700; margin-top: 4px; }

        /* LISTS */
        .list-card {
            background: white; border-radius: 15px; border: 0;
            box-shadow: 0 2px 8px rgba(0,0,0,0.03); overflow: hidden; height: 100%;
        }
        .list-header { 
            padding: 12px 15px; font-weight: 700; font-size: 0.9rem; 
            border-bottom: 1px solid #f0f0f0; display: flex; align-items: center; 
        }
        .list-item {
            padding: 10px 15px; border-bottom: 1px solid #f8f9fa;
            display: flex; justify-content: space-between; align-items: center;
        }
        .list-item:last-child { border-bottom: none; }
        
        .rank-num { 
            font-size: 0.75rem; color: #adb5bd; width: 20px; font-weight: bold; 
        }
        .item-name { font-size: 0.85rem; font-weight: 600; color: #333; }
        .item-sub { font-size: 0.7rem; color: #999; }
        .count-badge { font-size: 0.75rem; padding: 4px 8px; border-radius: 8px; font-weight: 700; }

        /* Colors */
        .c-red { color: #dc3545; background: #fff5f5; }
        .c-orange { color: #fd7e14; background: #fff4e6; }
        .c-blue { color: #0d6efd; background: #f0f7ff; }
        .c-green { color: #198754; background: #e8f5e9; }
    </style>
</head>
<body class="container-fluid p-0">

    <div class="class-header-card shadow">
        <div class="d-flex justify-content-between align-items-center mb-2">
            <div><small class="text-white-50 fw-bold text-uppercase">Admin Panel</small></div>
            <a href="admin_dashboard.php" class="btn btn-sm btn-outline-light rounded-pill px-3">Back</a>
        </div>
        <div class="d-flex justify-content-between align-items-end">
            <div>
                <h3 class="fw-bold mb-0">Statistics</h3>
                <div class="text-white-50 small">Deep dive into school data.</div>
            </div>
            <button onclick="window.print()" class="btn btn-sm btn-light text-primary fw-bold rounded-pill shadow-sm px-3">
                <i class="bi bi-printer-fill me-1"></i> Print
            </button>
        </div>
    </div>

    <div class="container px-3">
        
        <div class="row g-3 mb-4">
            <div class="col-6 col-md-3">
                <div class="stat-card">
                    <div>
                        <div class="st-val text-danger"><?= $total_students_reported ?></div>
                        <div class="st-lbl">Students Flagged</div>
                    </div>
                    <div class="icon-circle c-red"><i class="bi bi-exclamation-triangle-fill"></i></div>
                </div>
            </div>
            <div class="col-6 col-md-3">
                <div class="stat-card">
                    <div>
                        <div class="st-val text-primary"><?= $total_teachers_reporting ?></div>
                        <div class="st-lbl">Active Reporters</div>
                    </div>
                    <div class="icon-circle c-blue"><i class="bi bi-person-vcard-fill"></i></div>
                </div>
            </div>
            <div class="col-6 col-md-3">
                <div class="stat-card">
                    <div>
                        <div class="st-val text-warning"><?= $total_cases ?></div>
                        <div class="st-lbl">Total Cases</div>
                    </div>
                    <div class="icon-circle c-orange"><i class="bi bi-folder-fill"></i></div>
                </div>
            </div>
            <div class="col-6 col-md-3">
                <div class="stat-card">
                    <div>
                        <div class="st-val text-secondary"><?= $total_teacher_absences ?></div>
                        <div class="st-lbl">Staff Absences</div>
                    </div>
                    <div class="icon-circle bg-light text-secondary"><i class="bi bi-calendar-x-fill"></i></div>
                </div>
            </div>
        </div>

        <h6 class="fw-bold text-secondary text-uppercase small mb-3 ps-1">Student Insights</h6>
        <div class="row g-3 mb-4">
            <div class="col-12 col-md-4">
                <div class="list-card">
                    <div class="list-header text-danger bg-danger bg-opacity-10">
                        <i class="bi bi-person-x-fill me-2"></i> Top Absentees
                    </div>
                    <?php foreach($top_student_absent as $i => $r): ?>
                        <div class="list-item">
                            <div class="d-flex align-items-center">
                                <span class="rank-num"><?= $i+1 ?></span>
                                <div>
                                    <div class="item-name"><?= htmlspecialchars($r['full_name']) ?></div>
                                    <div class="item-sub"><?= htmlspecialchars($r['class_name']) ?></div>
                                </div>
                            </div>
                            <span class="count-badge c-red"><?= $r['count'] ?></span>
                        </div>
                    <?php endforeach; ?>
                    <?php if(empty($top_student_absent)): ?><div class="p-3 text-center small text-muted">No data available.</div><?php endif; ?>
                </div>
            </div>

            <div class="col-12 col-md-4">
                <div class="list-card">
                    <div class="list-header text-warning bg-warning bg-opacity-10">
                        <i class="bi bi-shield-exclamation me-2"></i> Most Reported
                    </div>
                    <?php foreach($top_student_disc as $i => $r): ?>
                        <div class="list-item">
                            <div class="d-flex align-items-center">
                                <span class="rank-num"><?= $i+1 ?></span>
                                <div>
                                    <div class="item-name"><?= htmlspecialchars($r['full_name']) ?></div>
                                    <div class="item-sub"><?= htmlspecialchars($r['class_name']) ?></div>
                                </div>
                            </div>
                            <span class="count-badge c-orange"><?= $r['count'] ?></span>
                        </div>
                    <?php endforeach; ?>
                    <?php if(empty($top_student_disc)): ?><div class="p-3 text-center small text-muted">No data available.</div><?php endif; ?>
                </div>
            </div>

            <div class="col-12 col-md-4">
                <div class="list-card">
                    <div class="list-header text-primary bg-primary bg-opacity-10">
                        <i class="bi bi-bar-chart-fill me-2"></i> Common Issues
                    </div>
                    <?php foreach($top_cases as $i => $r): ?>
                        <div class="list-item">
                            <div class="d-flex align-items-center">
                                <span class="rank-num"><?= $i+1 ?></span>
                                <div class="item-name"><?= htmlspecialchars($r['infraction']) ?></div>
                            </div>
                            <span class="count-badge c-blue"><?= $r['count'] ?></span>
                        </div>
                    <?php endforeach; ?>
                    <?php if(empty($top_cases)): ?><div class="p-3 text-center small text-muted">No data available.</div><?php endif; ?>
                </div>
            </div>
        </div>

        <h6 class="fw-bold text-secondary text-uppercase small mb-3 ps-1">Staff Performance</h6>
        <div class="row g-3">
            
            <div class="col-12 col-md-6 col-lg-3">
                <div class="list-card">
                    <div class="list-header text-success bg-success bg-opacity-10">
                        <i class="bi bi-star-fill me-2"></i> Top Reporters
                    </div>
                    <?php foreach($top_reporters as $i => $r): ?>
                        <div class="list-item">
                            <div class="d-flex align-items-center">
                                <span class="rank-num"><?= $i+1 ?></span>
                                <div class="item-name"><?= htmlspecialchars($r['full_name']) ?></div>
                            </div>
                            <span class="count-badge c-green"><?= $r['count'] ?></span>
                        </div>
                    <?php endforeach; ?>
                </div>
            </div>

            <div class="col-12 col-md-6 col-lg-3">
                <div class="list-card">
                    <div class="list-header text-secondary bg-secondary bg-opacity-10">
                        <i class="bi bi-person-dash-fill me-2"></i> Most Absent
                    </div>
                    <?php foreach($top_teacher_absent as $i => $r): ?>
                        <div class="list-item">
                            <div class="d-flex align-items-center">
                                <span class="rank-num"><?= $i+1 ?></span>
                                <div class="item-name"><?= htmlspecialchars($r['full_name']) ?></div>
                            </div>
                            <span class="count-badge bg-light text-dark border"><?= $r['count'] ?></span>
                        </div>
                    <?php endforeach; ?>
                </div>
            </div>

            <div class="col-12 col-md-6 col-lg-3">
                <div class="list-card">
                    <div class="list-header text-info bg-info bg-opacity-10">
                        <i class="bi bi-clock-fill me-2"></i> Most Punctual
                    </div>
                    <?php foreach($top_teacher_early as $i => $r): ?>
                        <div class="list-item">
                            <div class="d-flex align-items-center">
                                <span class="rank-num"><?= $i+1 ?></span>
                                <div class="item-name"><?= htmlspecialchars($r['full_name']) ?></div>
                            </div>
                            <span class="count-badge bg-info bg-opacity-10 text-dark"><?= $r['count'] ?></span>
                        </div>
                    <?php endforeach; ?>
                </div>
            </div>

            <div class="col-12 col-md-6 col-lg-3">
                <div class="list-card">
                    <div class="list-header text-danger bg-danger bg-opacity-10">
                        <i class="bi bi-alarm-fill me-2"></i> Critically Late (>20m)
                    </div>
                    <?php foreach($top_teacher_late as $i => $r): ?>
                        <div class="list-item">
                            <div class="d-flex align-items-center">
                                <span class="rank-num"><?= $i+1 ?></span>
                                <div class="item-name"><?= htmlspecialchars($r['full_name']) ?></div>
                            </div>
                            <span class="count-badge c-red"><?= $r['count'] ?></span>
                        </div>
                    <?php endforeach; ?>
                    <?php if(empty($top_teacher_late)): ?><div class="p-3 text-center small text-muted">No late records found.</div><?php endif; ?>
                </div>
            </div>

        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>