<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // Add session and status fields to students table
        Schema::table('students', function (Blueprint $table) {
            $table->string('session')->nullable()->after('year');
            $table->enum('current_class', ['11', '12'])->default('11')->after('class');
            $table->enum('status', ['active', 'promoted', 'failed', 'left'])->default('active')->after('current_class');
        });

        // Populate session from year for existing students
        // Example: year=2025 → session="2024-2026"
        DB::statement("UPDATE students SET session = CONCAT(year - 1, '-', year + 1) WHERE session IS NULL");
        
        // Populate current_class from class for existing students
        DB::statement("UPDATE students SET current_class = class WHERE current_class IS NULL OR current_class = ''");

        // Add session field to exams table
        Schema::table('exams', function (Blueprint $table) {
            $table->string('session')->nullable()->after('year');
        });

        // Populate session from year for existing exams
        DB::statement("UPDATE exams SET session = CONCAT(year - 1, '-', year + 1) WHERE session IS NULL");
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('students', function (Blueprint $table) {
            $table->dropColumn(['session', 'current_class', 'status']);
        });

        Schema::table('exams', function (Blueprint $table) {
            $table->dropColumn('session');
        });
    }
};

