<?php

use Wibiesana\Padi\Core\DatabaseManager;

return new class {
    public function up(): void
    {
        $db = DatabaseManager::connection();
        
        // 1. extracurriculars
        $db->exec("CREATE TABLE IF NOT EXISTS extracurriculars (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(150) NOT NULL,
            description TEXT NULL,
            advisor_teacher_id INT UNSIGNED NULL,
            schedule VARCHAR(100) NULL,
            created_by INT UNSIGNED NULL,
            updated_by INT UNSIGNED NULL,
            created_at DATETIME NULL,
            updated_at DATETIME NULL,
            FOREIGN KEY (advisor_teacher_id) REFERENCES teacher(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        
        // 2. extracurricular_members
        $db->exec("CREATE TABLE IF NOT EXISTS extracurricular_members (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            extracurricular_id INT UNSIGNED NOT NULL,
            student_id INT UNSIGNED NOT NULL,
            grade VARCHAR(10) NULL,
            notes TEXT NULL,
            created_by INT UNSIGNED NULL,
            updated_by INT UNSIGNED NULL,
            created_at DATETIME NULL,
            updated_at DATETIME NULL,
            FOREIGN KEY (extracurricular_id) REFERENCES extracurriculars(id) ON DELETE CASCADE,
            FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE,
            UNIQUE KEY uq_member (extracurricular_id, student_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        
        echo "✓ Extracurricular tables created successfully\n";
    }

    public function down(): void
    {
        $db = DatabaseManager::connection();
        $db->exec("DROP TABLE IF EXISTS extracurricular_members");
        $db->exec("DROP TABLE IF EXISTS extracurriculars");
        echo "✓ Extracurricular tables dropped successfully\n";
    }
};
