<?php

use Wibiesana\Padi\Core\DatabaseManager;

return new class {
    public function up(): void
    {
        $db = DatabaseManager::connection();
        
        $db->exec("CREATE TABLE IF NOT EXISTS student_achievements (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            student_id INT UNSIGNED NOT NULL,
            achievement_name VARCHAR(150) NOT NULL,
            category VARCHAR(50) NOT NULL, -- 'academic' / 'non-academic'
            level VARCHAR(50) NOT NULL, -- 'school', 'district', 'province', 'national', 'international'
            achievement_date DATE NOT NULL,
            notes TEXT NULL,
            certificate_path TEXT NULL, -- stores JSON array of paths
            created_by INT UNSIGNED NULL,
            updated_by INT UNSIGNED NULL,
            created_at DATETIME NULL,
            updated_at DATETIME NULL,
            FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        
        echo "✓ Table student_achievements created successfully\n";
    }

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