<?php
/**
 * EduTrack — migration runner.
 *
 * Applies all SQL migrations in this folder, in order, using the application's
 * own database connection (so it uses the correct live credentials from config).
 * Every migration is idempotent, so this is safe to run repeatedly.
 *
 * Usage:
 *   CLI :  php database/migrations/run_migrations.php
 *   Web :  https://your-site/edutrack/database/migrations/run_migrations.php
 *          (requires a logged-in admin session)
 *
 * NOTE: the column/enum migrations also auto-ensure themselves in code on first
 * use, so a failure on those is usually harmless — but the SBA and template
 * migrations CREATE/SEED data and should be run here.
 */
declare(strict_types=1);

$IS_CLI = (PHP_SAPI === 'cli');

// This file lives at <app-root>/database/migrations/ — load the app config.
require_once dirname(__DIR__, 2) . '/config.php';

// ── Access control (web only; CLI is trusted) ──────────────────────────────
if (!$IS_CLI) {
    if (session_status() === PHP_SESSION_NONE) {
        session_start();
    }
    if (empty($_SESSION['user_id']) || ($_SESSION['role'] ?? '') !== 'admin') {
        http_response_code(403);
        exit('Forbidden — sign in as an administrator to run migrations.');
    }
    header('Content-Type: text/plain; charset=utf-8');
}

$conn = getDBConnection();
if (!$conn instanceof mysqli) {
    $msg = "Database connection failed.\n";
    if ($IS_CLI) { fwrite(STDERR, $msg); } else { echo $msg; }
    exit(1);
}

// Run order matters: tables/seeds first, then column/enum extensions.
$migrations = [
    'restore_missing_autoincrement.sql', // repairs id PK/AUTO_INCREMENT on 24 tables missing it locally
    'sba_components.sql',            // creates sba_component_types, sba_scores + seeds weighting settings
    'exam_templates.sql',            // creates exam_paper_templates + seeds 8 system templates
    'exam_question_types.sql',       // enum += true_false
    'exam_question_structural.sql',  // enum += matching, comprehension; + extra_data column
    'exam_paper_section_config.sql', // exam_papers.section_config column
    'exam_format_profiles.sql',      // creates exam_format_profiles (+ versions) — AI house-style reference
    'timetable_period_type.sql',     // timetable_periods.period_type column + backfill
    'scores_progress_analytics.sql', // scores: class_name, PK/AUTO_INCREMENT, unique key, indexes
    'canteen_module.sql',            // canteen tables (self-heals in code, listed for completeness)

    // ── Add-ons (2026-07) ────────────────────────────────────────────────
    'finance_suite.sql',             // expense_requests/approvals/attachments + event accounts
    'multi_calendar.sql',            // school_terms.programme_level + composite unique key
    'whatsapp_comms.sql',            // whatsapp_log / sessions / pending
    'student_activities.sql',        // student_leadership + student_activities
    'health_records.sql',            // student_health + infirmary_visits
    'payroll.sql',                   // payroll_staff_pay / runs / items
    'biometric_attendance.sql',      // biometric_devices / enrollments / punches

    // ── Bulk fee payment (2026-07) ───────────────────────────────────────
    'bulk_fee_payment.sql',          // fee_structures.term + billing_frequency, backfilled
    'bulk_fee_payment_batches.sql',  // bulk_payment_batches, payments.batch_id, student_fee_credits
    'bulk_fee_payment_online.sql',   // online_payments.weeks_covered
    'bulk_fee_payment_credit.sql',   // payments.payment_method += 'credit'
];

echo "EduTrack migrations — target database: " . (defined('DB_NAME') ? DB_NAME : '(unknown)') . "\n";
echo str_repeat('-', 60) . "\n";

$failures = 0;
foreach ($migrations as $file) {
    $path = __DIR__ . DIRECTORY_SEPARATOR . $file;
    echo str_pad("→ {$file}", 42, ' ');

    if (!is_file($path)) {
        echo "MISSING\n";
        $failures++;
        continue;
    }
    $sql = (string) file_get_contents($path);
    if (trim($sql) === '') {
        echo "EMPTY (skipped)\n";
        continue;
    }

    $ok = true;
    $error = '';
    try {
        if ($conn->multi_query($sql)) {
            do {
                if ($res = $conn->store_result()) {
                    $res->free();
                }
            } while ($conn->more_results() && $conn->next_result());
        }
        if ($conn->errno) {
            $ok = false;
            $error = $conn->error;
        }
    } catch (Throwable $e) {
        $ok = false;
        $error = $e->getMessage();
        // Best-effort: drain any pending result sets so the connection stays usable.
        while ($conn->more_results()) {
            try {
                if (!$conn->next_result()) {
                    break;
                }
                if ($res = $conn->store_result()) {
                    $res->free();
                }
            } catch (Throwable $ignored) {
                break;
            }
        }
    }

    echo $ok ? "OK\n" : "FAILED: {$error}\n";
    if (!$ok) {
        $failures++;
    }
}

echo str_repeat('-', 60) . "\n";
echo $failures === 0
    ? "All migrations applied successfully.\n"
    : "{$failures} migration(s) reported an error — review the lines above.\n";

exit($failures === 0 ? 0 : 1);
