<?php
// admin/results/broadsheet.php
session_start();

$projectRoot = dirname(dirname(__DIR__)); 
require_once $projectRoot . "/config/database.php";

// --- UPDATED SECURITY CHECK ---
// Allow both admin and subadmin roles
if (!isset($_SESSION['role']) || !in_array($_SESSION['role'], ['admin', 'subadmin'])) {
    header("Location: ../../auth/login.php");
    exit();
}

$class_id = $_GET['class_id'] ?? null;
$session = $_GET['session'] ?? '';
$term = $_GET['term'] ?? '';

if (!$class_id || !$session || !$term) {
    die("Error: Missing parameters.");
}

try {
    // 2. FETCH SYSTEM SETTINGS
    $stmtSet = $pdo->query("SELECT * FROM system_settings WHERE id = 1");
    $settings = $stmtSet->fetch(PDO::FETCH_ASSOC);

    // 3. FETCH CLASS INFO
    $stmtClass = $pdo->prepare("SELECT class_name FROM classes WHERE id = ?");
    $stmtClass->execute([$class_id]);
    $className = $stmtClass->fetchColumn();

    // 4. FETCH SUBJECTS (Only those that have results for this selection)
    $stmtSub = $pdo->prepare("SELECT DISTINCT sub.id, sub.subject_name 
                              FROM subjects sub 
                              JOIN results r ON sub.id = r.subject_id 
                              WHERE r.class_id = ? AND r.session = ? AND r.term = ?");
    $stmtSub->execute([$class_id, $session, $term]);
    $subjects = $stmtSub->fetchAll(PDO::FETCH_ASSOC);

    // 5. FETCH SCORES (Approved results only)
    $stmtRes = $pdo->prepare("SELECT s.id as student_id, s.fullname, r.subject_id, r.ca_score, r.exam_score, r.total_score 
                              FROM students s 
                              JOIN results r ON s.id = r.student_id 
                              WHERE r.class_id = ? AND r.session = ? AND r.term = ? AND r.is_approved = 1
                              ORDER BY s.fullname ASC");
    $stmtRes->execute([$class_id, $session, $term]);
    $rawResults = $stmtRes->fetchAll(PDO::FETCH_ASSOC);

    // 6. MAP DATA
    $dataMap = [];
    $totals = [];
    foreach ($rawResults as $row) {
        $sid = $row['student_id'];
        $subid = $row['subject_id'];
        
        $dataMap[$sid][$subid] = [
            'ca' => $row['ca_score'],
            'exam' => $row['exam_score'],
            'total' => $row['total_score']
        ];
        $dataMap[$sid]['fullname'] = $row['fullname'];
        
        if(!isset($totals[$sid])) $totals[$sid] = 0;
        $totals[$sid] += (float)$row['total_score'];
    }

    // Rank by Total (Highest first)
    arsort($totals);
    $positions = [];
    $rank = 1;
    foreach ($totals as $sid => $score) { $positions[$sid] = $rank++; }

} catch (PDOException $e) { die("Database Error: " . $e->getMessage()); }
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Detailed Broadsheet - <?= htmlspecialchars($className) ?></title>
    <style>
        body { font-family: 'Segoe UI', Arial, sans-serif; margin: 0; padding: 10px; background: #f4f4f4; font-size: 11px; }
        .broadsheet-container { background: white; padding: 20px; min-width: 1400px; margin: auto; position: relative; border: 1px solid #000; }
        
        /* Header */
        .header-box { display: flex; align-items: center; justify-content: space-between; border-bottom: 2px solid #000; padding-bottom: 10px; margin-bottom: 15px; }
        .school-info { text-align: center; flex: 1; }
        .school-info h1 { margin: 0; font-size: 22px; }
        .logo { width: 80px; height: 80px; object-fit: contain; }

        /* Table Structure */
        table { width: 100%; border-collapse: collapse; table-layout: fixed; }
        th, td { border: 1px solid #000; padding: 4px; text-align: center; word-wrap: break-word; }
        
        /* Subject Group Headers */
        .sub-header { background: #2c3e50; color: white; font-size: 10px; height: 40px; }
        .score-type { background: #eee; font-size: 9px; font-weight: bold; }
        
        .name-col { text-align: left; width: 180px; font-weight: bold; padding-left: 5px; }
        .sn-col { width: 30px; }
        .summary-col { background: #f9f9f9; font-weight: bold; width: 50px; }

        .official-footer { margin-top: 30px; display: flex; justify-content: space-around; position: relative; }
        .sig-box { border-top: 1px solid #000; width: 200px; text-align: center; padding-top: 5px; font-weight: bold; }
        .stamp-img { position: absolute; bottom: 5px; right: 20%; width: 100px; opacity: 0.6; }

        .no-print { margin-bottom: 10px; display: flex; justify-content: space-between; align-items: center; }
        .btn { padding: 8px 15px; cursor: pointer; border-radius: 4px; text-decoration: none; font-weight: bold; border: none; font-size: 12px; }
        .btn-print { background: #27ae60; color: white; }
        .btn-back { background: #34495e; color: white; }

        @media print {
            .no-print { display: none; }
            body { padding: 0; background: white; }
            .broadsheet-container { border: none; width: 100%; min-width: auto; }
            @page { size: A3 landscape; margin: 0.5cm; }
        }
    </style>
</head>
<body>

<div class="no-print">
    <a href="index.php" class="btn btn-back">⬅ Change Parameters</a>
    <button onclick="window.print()" class="btn btn-print">🖨 Print Detailed Broadsheet</button>
</div>

<div class="broadsheet-container">
    <div class="header-box">
        <img src="../../assets/img/logo.png" class="logo" onerror="this.style.display='none'">
        <div class="school-info">
            <h1><?= strtoupper(htmlspecialchars($settings['school_name'] ?? 'School Name')) ?></h1>
            <p><?= htmlspecialchars($settings['school_address'] ?? '') ?> | <?= htmlspecialchars($settings['school_phone'] ?? '') ?></p>
            <p><strong>DETAILED BROADSHEET: <?= strtoupper($className) ?> (<?= $term ?> - <?= $session ?>)</strong></p>
        </div>
        <div style="width:80px;"></div>
    </div>

    <table>
        <thead>
            <tr>
                <th rowspan="2" class="sn-col">S/N</th>
                <th rowspan="2" class="name-col">STUDENT NAME</th>
                <?php foreach($subjects as $sub): ?>
                    <th colspan="3" class="sub-header"><?= strtoupper($sub['subject_name']) ?></th>
                <?php endforeach; ?>
                <th rowspan="2" class="summary-col">TOTAL</th>
                <th rowspan="2" class="summary-col">AVG</th>
                <th rowspan="2" class="summary-col">POS</th>
            </tr>
            <tr>
                <?php foreach($subjects as $sub): ?>
                    <th class="score-type">CA</th>
                    <th class="score-type">EX</th>
                    <th class="score-type">TOT</th>
                <?php endforeach; ?>
            </tr>
        </thead>
        <tbody>
            <?php 
            $sn = 1; 
            $subCount = count($subjects);
            foreach($positions as $sid => $rank): 
            ?>
            <tr>
                <td><?= $sn++ ?></td>
                <td class="name-col"><?= strtoupper(htmlspecialchars($dataMap[$sid]['fullname'])) ?></td>
                <?php foreach($subjects as $sub): 
                    $s = $dataMap[$sid][$sub['id']] ?? ['ca'=>'-', 'exam'=>'-', 'total'=>'-'];
                ?>
                    <td><?= $s['ca'] ?></td>
                    <td><?= $s['exam'] ?></td>
                    <td style="background:#f2f2f2; font-weight:bold;"><?= $s['total'] ?></td>
                <?php endforeach; ?>
                
                <td class="summary-col"><?= $totals[$sid] ?></td>
                <td class="summary-col">
                    <?= ($subCount > 0) ? number_format($totals[$sid] / $subCount, 1) : '0.0' ?>
                </td>
                <td style="font-weight:bold; font-size:12px;"><?= $rank ?></td>
            </tr>
            <?php endforeach; ?>
            
            <?php if(empty($positions)): ?>
                <tr>
                    <td colspan="<?= ($subCount * 3) + 5 ?>" style="padding:50px; font-size:14px; color:#999;">
                        No approved results found for this selection.
                    </td>
                </tr>
            <?php endif; ?>
        </tbody>
    </table>

    <div class="official-footer">
        <div class="sig-box">FORM TEACHER</div>
        <img src="../../assets/img/stamp.png" class="stamp-img" onerror="this.style.display='none'">
        <div class="sig-box">PRINCIPAL / DATE</div>
    </div>
</div>

</body>
</html>