<?php
session_start();
$projectRoot = dirname(__DIR__);
require_once $projectRoot . '/config/database.php';

// 1. SECURITY: Admin and Sub-Admin only
if (!isset($_SESSION['role']) || !in_array($_SESSION['role'], ['admin', 'subadmin'])) {
    die("Unauthorized access."); 
}

// 2. FETCH SYSTEM SETTINGS (Refresh Session Memory)
try {
    $settingsStmt = $pdo->query("SELECT * FROM system_settings WHERE id = 1");
    $settings = $settingsStmt->fetch(PDO::FETCH_ASSOC);
    
    if ($settings) {
        $_SESSION['active_session'] = $settings['active_session']; 
        $_SESSION['active_term'] = $settings['active_term'];       
    } else {
        die("Error: No data found in system_settings table. Please configure settings first.");
    }
} catch (PDOException $e) {
    die("Database Error: " . $e->getMessage());
}

$active_session = $_SESSION['active_session'];
$active_term    = $_SESSION['active_term'];

$message = "";
$status = "";

// 3. HANDLE THE PUSH ACTION
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['push_scores'])) {
    try {
        $pdo->beginTransaction();

        // Fetch scores from exam_results joined with exams to get academic context
        $stmt = $pdo->query("
            SELECT r.student_id, e.subject_id, e.class_id, r.score 
            FROM exam_results r
            JOIN exams e ON r.exam_id = e.id
            WHERE r.status = 'completed' OR r.status = 'submitted'
        ");
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if ($results) {
            /**
             * PUSH TO 'results' TABLE
             * Column Mapping: r.score -> results.exam_score
             */
            $sql = "INSERT INTO results (student_id, class_id, subject_id, session, term, exam_score) 
                    VALUES (:student_id, :class_id, :subject_id, :session, :term, :score)
                    ON DUPLICATE KEY UPDATE exam_score = VALUES(exam_score)";
            
            $pushStmt = $pdo->prepare($sql);
            $count = 0;

            foreach ($results as $res) {
                $pushStmt->execute([
                    ':student_id' => $res['student_id'],
                    ':class_id'   => $res['class_id'],
                    ':subject_id' => $res['subject_id'],
                    ':session'    => $active_session,
                    ':term'       => $active_term,
                    ':score'      => $res['score']
                ]);
                $count++;
            }

            $pdo->commit();
            $message = "Successfully synchronized $count student scores to the exam_score column!";
            $status = "success";
        } else {
            $message = "No completed CBT results found for the current filter.";
            $status = "info";
        }
    } catch (Exception $e) {
        if ($pdo->inTransaction()) { $pdo->rollBack(); }
        $message = "Sync Error: " . $e->getMessage();
        $status = "error";
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>CBT to Broadsheet Sync</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <style>
        :root { --primary: #2c3e50; --accent: #3498db; --bg: #f8f9fa; }
        body { font-family: 'Inter', 'Segoe UI', sans-serif; background: var(--bg); display: flex; align-items: center; justify-content: center; min-height: 100vh; margin: 0; }
        .card { background: #fff; padding: 40px; border-radius: 16px; box-shadow: 0 10px 30px rgba(0,0,0,0.1); max-width: 450px; width: 90%; text-align: center; border: 1px solid #eee; }
        .icon-header { font-size: 3.5rem; color: var(--accent); margin-bottom: 20px; }
        .alert { padding: 15px; border-radius: 8px; margin-bottom: 25px; font-weight: 600; font-size: 0.9rem; border-left: 5px solid; }
        .alert-success { background: #f0fdf4; color: #166534; border-color: #22c55e; }
        .alert-error { background: #fef2f2; color: #991b1b; border-color: #ef4444; }
        .alert-info { background: #eff6ff; color: #1e40af; border-color: #3b82f6; }
        .info-panel { background: #f1f5f9; padding: 15px; border-radius: 10px; margin-bottom: 30px; display: flex; justify-content: space-around; border: 1px solid #e2e8f0; }
        .info-item { font-size: 0.8rem; color: #64748b; font-weight: 700; text-transform: uppercase; }
        .info-item span { display: block; color: var(--primary); font-size: 1rem; margin-top: 4px; }
        .btn-sync { background: var(--accent); color: white; border: none; padding: 16px 20px; border-radius: 10px; font-size: 1rem; font-weight: 700; cursor: pointer; width: 100%; transition: all 0.3s; display: flex; align-items: center; justify-content: center; gap: 10px; }
        .btn-sync:hover { background: #2980b9; transform: translateY(-2px); box-shadow: 0 5px 15px rgba(52, 152, 219, 0.4); }
        .btn-sync:active { transform: translateY(0); }
        .back-btn { display: inline-block; margin-top: 25px; color: #94a3b8; text-decoration: none; font-size: 0.85rem; font-weight: 500; }
        .back-btn:hover { color: var(--accent); }
    </style>
</head>
<body>

<div class="card">
    <div class="icon-header"><i class="fa-solid fa-file-export"></i></div>
    <h2 style="margin: 0 0 10px 0; color: var(--primary);">Exam Score Sync</h2>
    <p style="color: #64748b; margin-top: 0; font-size: 0.95rem;">Move CBT scores into the main results broadsheet.</p>

    <?php if($message): ?>
        <div class="alert alert-<?= $status ?>"><?= $message ?></div>
    <?php endif; ?>

    <div class="info-panel">
        <div class="info-item">Session <span><?= htmlspecialchars($active_session) ?></span></div>
        <div class="info-item">Term <span><?= htmlspecialchars($active_term) ?></span></div>
    </div>

    

    <form method="POST">
        <button type="submit" name="push_scores" class="btn-sync" onclick="return confirm('This will update the exam_score column for all students in the results table. Continue?')">
            <i class="fa-solid fa-arrows-rotate"></i> Sync CBT to Broadsheet
        </button>
    </form>

    <a href="dashboard.php" class="back-btn"><i class="fa-solid fa-arrow-left"></i> Back to Dashboard</a>
</div>

</body>
</html>