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

// Only Admin
if ($_SESSION['role'] !== 'admin') die("Access Denied");

echo "<h3>Starting Optimization Process...</h3>";

try {
    $pdo->beginTransaction();

    // --- PHASE 1: ADD INDEXES ---
    // We use a helper to silently ignore errors if index already exists
    function addIndex($pdo, $table, $indexName, $columns) {
        try {
            $pdo->exec("CREATE INDEX $indexName ON $table ($columns)");
            echo "✅ Index '$indexName' added to table '$table'.<br>";
        } catch (Exception $e) {
            echo "ℹ️ Index '$indexName' likely already exists.<br>";
        }
    }

    addIndex($pdo, 'timetable', 'idx_tt_teacher_day', 'teacher_id, day_of_week');
    addIndex($pdo, 'timetable', 'idx_tt_class', 'class_id');
    addIndex($pdo, 'students', 'idx_st_class', 'class_id');
    addIndex($pdo, 'students', 'idx_st_ic', 'ic_number');
    addIndex($pdo, 'student_attendance', 'idx_sa_date_stat', 'date, status');
    addIndex($pdo, 'users', 'idx_u_role', 'role');

    // --- PHASE 3: ADD COLUMNS FOR WRITE-TIME CALCULATION ---
    // We add 'birth_year' to avoid parsing ICs every time we run a report
    // We don't add 'gender' because your table likely already has 'sex' column
    
    // Check if column exists
    $col_check = $pdo->query("SHOW COLUMNS FROM students LIKE 'birth_year'");
    if ($col_check->rowCount() == 0) {
        $pdo->exec("ALTER TABLE students ADD COLUMN birth_year INT(4) DEFAULT NULL AFTER ic_number");
        echo "✅ Column 'birth_year' added to students table.<br>";
    } else {
        echo "ℹ️ Column 'birth_year' already exists.<br>";
    }

    // --- BACKFILL DATA ---
    echo "<hr><strong>Backfilling Data...</strong><br>";
    
    $students = $pdo->query("SELECT id, ic_number FROM students")->fetchAll();
    $count = 0;
    $curr_short_year = intval(date('y'));

    $update_stmt = $pdo->prepare("UPDATE students SET birth_year = ? WHERE id = ?");

    foreach ($students as $s) {
        $ic = preg_replace('/[^0-9]/', '', $s['ic_number']);
        if (strlen($ic) == 12) {
            $yy = substr($ic, 0, 2);
            // Logic: If 85 > 25 -> 1985. If 10 < 25 -> 2010.
            $birth_year = ($yy > $curr_short_year) ? intval("19" . $yy) : intval("20" . $yy);
            $update_stmt->execute([$birth_year, $s['id']]);
            $count++;
        }
    }

    $pdo->commit();
    echo "<br><div style='color:green; font-weight:bold'>Success! Optimized $count student records.</div>";
    echo "<br>You can now delete this file.";

} catch (Exception $e) {
    $pdo->rollBack();
    echo "<div style='color:red'>Error: " . $e->getMessage() . "</div>";
}
?>