<?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. Students (Profile)
        Schema::create('students', function (Blueprint $table) {
            $table->id();
            $table->string('ssc_reg_no')->unique(); // Board Registration No (Prime ID)
            $table->string('class_roll')->unique()->nullable(); // System Generated
            $table->string('name');
            
            // Academic Info
            $table->enum('class', ['11', '12'])->default('11');
            $table->tinyInteger('group_code'); // 3=Science, 2=Commerce, 0/1=Humanities
            $table->year('ssc_batch'); // Input Year e.g. 2023
            $table->year('hsc_batch')->nullable(); // Derived Year e.g. 2025
            $table->integer('serial')->nullable(); // Reset per batch+group
            
            // Legacy / Linkers
            $table->foreignId('batch_id')->nullable()->constrained('batches')->nullOnDelete();
            $table->foreignId('academic_version_id')->nullable()->constrained('academic_versions')->nullOnDelete(); 
            
            // Contact
            $table->string('phone')->nullable();
            $table->string('email')->nullable();
            $table->text('address')->nullable();
            $table->string('photo')->nullable();
            
            // Status
            $table->enum('status', ['active', 'inactive', 'transferred', 'graduated', 'alumni'])->default('active');
            
            $table->timestamps();
            $table->softDeletes();
            
            // Indexes for Search
            $table->index(['ssc_reg_no']);
            $table->index(['ssc_batch', 'group_code', 'serial']); // For roll generation check
            $table->index(['class_roll']);
        });

        // 2. Student Subjects (Pivot for assigned subjects, especially 4th subject)
        Schema::create('student_subjects', function (Blueprint $table) {
            $table->id();
            $table->foreignId('student_id')->constrained()->cascadeOnDelete();
            $table->foreignId('subject_id')->constrained()->cascadeOnDelete();
            $table->foreignId('academic_version_id')->constrained('academic_versions'); // Snapshot of version
            
            $table->boolean('is_optional')->default(false); // Flag for 4th subject
            
            $table->timestamps();
            
            $table->unique(['student_id', 'subject_id']);
            $table->index(['student_id', 'is_optional']);
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('student_subjects');
        Schema::dropIfExists('students');
    }
};
