<?php

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

return new class extends Migration
{
    public function up(): void
    {
        // 1. Exam Templates (Reusable Rules)
        Schema::create('exam_templates', function (Blueprint $table) {
            $table->id();
            $table->string('name'); // e.g., "Standard Half Yearly"
            $table->text('description')->nullable();
            
            // JSON Rules: { "grading_method": "gpa_5", "pass_criteria": "all_subjects" }
            $table->json('rules')->nullable(); 
            
            $table->foreignId('academic_version_id')->constrained('academic_versions');
            $table->timestamps();
        });

        // 2. Exams (Actual Scheduled Exams)
        Schema::create('exams', function (Blueprint $table) {
            $table->id();
            $table->string('name'); // e.g., "Test Exam 2024"
            $table->foreignId('batch_id')->constrained('batches');
            $table->enum('class', ['11', '12'])->default('11');
            $table->foreignId('exam_template_id')->constrained('exam_templates');
            
            $table->date('start_date')->nullable();
            $table->date('end_date')->nullable();
            
            $table->enum('status', ['upcoming', 'open_for_entry', 'published', 'archived'])->default('upcoming');
            
            $table->timestamps();
            $table->softDeletes();
            
            $table->index(['batch_id', 'status']);
        });

        // 3. Marks (Raw Entry)
        Schema::create('marks', function (Blueprint $table) {
            $table->id();
            $table->foreignId('student_id')->constrained()->cascadeOnDelete();
            $table->foreignId('exam_id')->constrained()->cascadeOnDelete();
            $table->foreignId('subject_id')->constrained()->cascadeOnDelete();
            
            // Component Marks: {"cq": 45, "mcq": 28, "practical": 25}
            $table->json('marks')->nullable(); 
            
            $table->float('total_obtained')->default(0);
            $table->string('grade')->nullable(); // A+, A, F
            $table->float('gpa')->default(0); // 5.0, 4.0
            $table->boolean('is_absent')->default(false);
            
            $table->timestamps();
            
            $table->unique(['student_id', 'exam_id', 'subject_id']);
            $table->index(['exam_id', 'subject_id']); // For tabulating subject results
        });

        // 4. Final Results (Aggregated Exam Result)
        Schema::create('final_results', function (Blueprint $table) {
            $table->id();
            $table->foreignId('student_id')->constrained()->cascadeOnDelete();
            $table->foreignId('exam_id')->constrained()->cascadeOnDelete();
            
            $table->float('total_marks')->default(0);
            $table->float('gpa')->default(0);
            $table->string('grade')->nullable();
            $table->integer('rank')->nullable();
            
            $table->enum('status', ['passed', 'failed', 'withheld'])->default('passed');
            $table->text('remarks')->nullable(); // Failed subjects list
            
            $table->timestamps();
            
            $table->unique(['student_id', 'exam_id']);
            $table->index(['exam_id', 'gpa']); // For ranking
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('final_results');
        Schema::dropIfExists('marks');
        Schema::dropIfExists('exams');
        Schema::dropIfExists('exam_templates');
    }
};
