<?php
require_once 'bootstrap.php';

// Create school-specific tables for all approved schools
$db = db();
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Get all approved schools
$schools = $db->query("
    SELECT id, school_name, institution_id 
    FROM schools 
    WHERE status = 'approved'
")->fetchAll(PDO::FETCH_ASSOC);

foreach ($schools as $school) {
    $sanitized = preg_replace('/[^a-zA-Z0-9_]/', '_', $school['school_name']);

    echo "Creating tables for: " . $school['school_name'] . " (" . $sanitized . ")\n";

    // 1. Students table
    $db->exec("
        CREATE TABLE IF NOT EXISTS students_{$sanitized} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id TEXT UNIQUE NOT NULL,
            name TEXT NOT NULL,
            picture BLOB,
            sex TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ");

    // 2. Results table
    $db->exec("
        CREATE TABLE IF NOT EXISTS results_{$sanitized} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id TEXT NOT NULL,
            name TEXT NOT NULL,
            \"1\" REAL DEFAULT 0,
            \"2\" REAL DEFAULT 0,
            \"3\" REAL DEFAULT 0,
            \"4\" REAL DEFAULT 0,
            \"5\" REAL DEFAULT 0,
            \"6\" REAL DEFAULT 0,
            \"7\" REAL DEFAULT 0,
            \"8\" REAL DEFAULT 0
        )
    ");

    // 3. Activated students table
    $db->exec("
        CREATE TABLE IF NOT EXISTS activated_students_{$sanitized} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id TEXT UNIQUE NOT NULL,
            activation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            expiry_date TIMESTAMP
        )
    ");

    // 4. OSCE procedures table
    $db->exec("
        CREATE TABLE IF NOT EXISTS osce_procedures_{$sanitized} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            table_name TEXT UNIQUE
        )
    ");

    // 5. Activation slots table
    $db->exec("
        CREATE TABLE IF NOT EXISTS activation_slot_{$sanitized} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            number INTEGER DEFAULT 0
        )
    ");

    echo "✅ Tables created for " . $school['school_name'] . "\n";
}

echo "\n🎉 Migration complete! All school tables have been created.\n";
