<?php
declare(strict_types=1);

require_once __DIR__ . '/../includes/config.php';
require_once __DIR__ . '/../includes/db.php';
require_once __DIR__ . '/../includes/functions.php';
require_once __DIR__ . '/../includes/admin-auth.php';

$pdo = db();
requireAdmin();

$totalStudents = tableExists($pdo, 'students') ? safeCount($pdo, "SELECT COUNT(*) FROM students") : 0;
$totalCourses = tableExists($pdo, 'core_courses') ? safeCount($pdo, "SELECT COUNT(*) FROM core_courses") : 0;
$totalAttendance = tableExists($pdo, 'core_attendance') ? safeCount($pdo, "SELECT COUNT(*) FROM core_attendance") : 0;
$totalPresent = tableExists($pdo, 'core_attendance') ? safeCount($pdo, "SELECT COUNT(*) FROM core_attendance WHERE status='present'") : 0;
$totalLate = tableExists($pdo, 'core_attendance') ? safeCount($pdo, "SELECT COUNT(*) FROM core_attendance WHERE status='late'") : 0;
$totalAbsent = tableExists($pdo, 'core_attendance') ? safeCount($pdo, "SELECT COUNT(*) FROM core_attendance WHERE status='absent'") : 0;

$attendanceRate = $totalAttendance > 0 ? round(($totalPresent / $totalAttendance) * 100, 2) : 0;

$studentsReport = [];

if (tableExists($pdo, 'students')) {
    $studentsReport = $pdo->query("
        SELECT
            s.id,
            s.full_name,
            s.student_code,
            s.final_level,
            COALESCE(f.final_percentage, 0) AS final_percentage
        FROM students s
        LEFT JOIN core_final_grades f ON f.student_id = s.id
        ORDER BY final_percentage DESC, s.full_name ASC
        LIMIT 20
    ")->fetchAll();
}

$courseReport = [];

if (tableExists($pdo, 'core_courses')) {
    $courseReport = $pdo->query("
        SELECT
            c.id,
            c.title,
            c.level,
            COUNT(cs.id) AS total_students
        FROM core_courses c
        LEFT JOIN core_course_students cs ON cs.course_id = c.id
        GROUP BY c.id
        ORDER BY total_students DESC
        LIMIT 20
    ")->fetchAll();
}

?>

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">

<title>Reports</title>

<link href="https://fonts.googleapis.com/css2?family=Inter:wght@400;500;600;700;800;900&display=swap" rel="stylesheet">

<style>
*{margin:0;padding:0;box-sizing:border-box}
body{font-family:'Inter',sans-serif;background:#f1f5f9;color:#0f172a}
.page{padding:34px}
.topbar{display:flex;justify-content:space-between;align-items:center;margin-bottom:30px}
.page-title{font-size:42px;font-weight:900}
.back-btn{background:#dbeafe;color:#1d4ed8;text-decoration:none;padding:14px 20px;border-radius:18px;font-weight:900}
.hero{background:linear-gradient(135deg,#0f172a,#2563eb);color:#fff;border-radius:34px;padding:36px;margin-bottom:26px}
.hero h2{font-size:40px;font-weight:900;margin-bottom:12px}
.hero p{color:#dbeafe;line-height:2}
.stats{display:grid;grid-template-columns:repeat(auto-fit,minmax(210px,1fr));gap:20px;margin-bottom:26px}
.stat{background:#fff;border-radius:28px;padding:26px;border:1px solid #e2e8f0;box-shadow:0 18px 45px rgba(15,23,42,.05)}
.stat span{display:block;color:#64748b;font-weight:900;font-size:13px;margin-bottom:12px}
.stat h3{font-size:38px;font-weight:900;color:#2563eb}
.grid{display:grid;grid-template-columns:1fr 1fr;gap:26px}
.card{background:#fff;border-radius:34px;padding:30px;border:1px solid #e2e8f0;box-shadow:0 18px 45px rgba(15,23,42,.05)}
.card h2{font-size:30px;margin-bottom:22px;font-weight:900}
.table-wrap{overflow:auto}
table{width:100%;border-collapse:collapse}
th{background:#f8fafc;padding:16px;text-align:left;font-size:13px;text-transform:uppercase;color:#64748b}
td{padding:16px;border-top:1px solid #e2e8f0;font-size:14px}
.badge{display:inline-block;padding:8px 12px;border-radius:999px;background:#dbeafe;color:#1d4ed8;font-weight:900;font-size:12px}
@media(max-width:1000px){.grid{grid-template-columns:1fr}}
</style>
</head>

<body>

<div class="page">

    <div class="topbar">
        <h1 class="page-title">Reports</h1>
        <a href="dashboard.php" class="back-btn">← Dashboard</a>
    </div>

    <div class="hero">
        <h2>SMI Academic Reports</h2>
        <p>
            Overview of students, courses, attendance performance,
            grades, and academic progress inside the Student Gateway.
        </p>
    </div>

    <div class="stats">

        <div class="stat">
            <span>Total Students</span>
            <h3><?php echo $totalStudents; ?></h3>
        </div>

        <div class="stat">
            <span>Total Courses</span>
            <h3><?php echo $totalCourses; ?></h3>
        </div>

        <div class="stat">
            <span>Attendance Rate</span>
            <h3><?php echo $attendanceRate; ?>%</h3>
        </div>

        <div class="stat">
            <span>Present</span>
            <h3><?php echo $totalPresent; ?></h3>
        </div>

        <div class="stat">
            <span>Late</span>
            <h3><?php echo $totalLate; ?></h3>
        </div>

        <div class="stat">
            <span>Absent</span>
            <h3><?php echo $totalAbsent; ?></h3>
        </div>

    </div>

    <div class="grid">

        <div class="card">
            <h2>Top Students</h2>

            <div class="table-wrap">
                <table>
                    <thead>
                        <tr>
                            <th>Student</th>
                            <th>Code</th>
                            <th>Level</th>
                            <th>Final %</th>
                        </tr>
                    </thead>

                    <tbody>
                    <?php if($studentsReport): ?>
                        <?php foreach($studentsReport as $row): ?>
                            <tr>
                                <td><?php echo e($row['full_name']); ?></td>
                                <td><?php echo e($row['student_code'] ?? 'N/A'); ?></td>
                                <td>
                                    <span class="badge">
                                        <?php echo e(ucfirst($row['final_level'] ?? 'N/A')); ?>
                                    </span>
                                </td>
                                <td><strong><?php echo number_format((float)$row['final_percentage'], 2); ?>%</strong></td>
                            </tr>
                        <?php endforeach; ?>
                    <?php else: ?>
                        <tr><td colspan="4">No student data.</td></tr>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>

        <div class="card">
            <h2>Courses Summary</h2>

            <div class="table-wrap">
                <table>
                    <thead>
                        <tr>
                            <th>Course</th>
                            <th>Level</th>
                            <th>Students</th>
                        </tr>
                    </thead>

                    <tbody>
                    <?php if($courseReport): ?>
                        <?php foreach($courseReport as $course): ?>
                            <tr>
                                <td><?php echo e($course['title']); ?></td>
                                <td>
                                    <span class="badge">
                                        <?php echo e(ucfirst($course['level'] ?? 'General')); ?>
                                    </span>
                                </td>
                                <td><strong><?php echo e((string)$course['total_students']); ?></strong></td>
                            </tr>
                        <?php endforeach; ?>
                    <?php else: ?>
                        <tr><td colspan="3">No courses data.</td></tr>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>

    </div>

</div>

</body>
</html>