Schema::create('academic_years', function (Blueprint $table) {
            $table->id();

            $table->string('name', 20)
                ->comment('Human readable label e.g. 2025/2026');

            $table->date('start_date')
                ->comment('First day of the academic year');

            $table->date('end_date')
                ->comment('Last day of the academic year');

            $table->boolean('is_active')
                ->default(false)
                ->comment('Only one academic year should be active at a time');

            $table->text('notes')
                ->nullable()
                ->comment('Admin notes about this academic year');

            $table->timestamps();

            // Indexes for common queries
            $table->index('is_active', 'idx_academic_years_active');
            $table->index(['start_date', 'end_date'], 'idx_academic_years_dates');
        });
        Schema::create('assignments', function (Blueprint $table) {
            $table->id();

            $table->foreignId('teacher_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->foreignId('subject_id')
                ->constrained('subjects')
                ->cascadeOnDelete();

            $table->foreignId('class_id')
                ->nullable()
                ->constrained('school_classes')
                ->nullOnDelete()
                ->comment('Null for 1:1 private assignments');

            $table->foreignId('academic_year_id')
                ->constrained('academic_years')
                ->cascadeOnDelete();

            $table->foreignId('academic_term_id')
                ->nullable()
                ->constrained('academic_year_terms')
                ->nullOnDelete();

            $table->foreignId('class_session_id')
                ->nullable()
                ->constrained('class_sessions')
                ->nullOnDelete()
                ->comment('Optional link to the session this assignment was set in');

            $table->string('title', 200);
            $table->text('description')->nullable();
            $table->text('instructions')->nullable();

            $table->enum('type', [
                'homework',
                'classwork',
                'project',
                'essay',
                'practical',
                'other',
            ])->default('homework');

            $table->decimal('max_score', 5, 2)
                ->default(100.00)
                ->comment('Maximum achievable score');

            $table->decimal('passing_score', 5, 2)
                ->default(50.00)
                ->comment('Minimum score to pass');

            $table->datetime('assigned_at')
                ->comment('When the assignment was given to students');

            $table->datetime('due_at')
                ->comment('Submission deadline');

            $table->boolean('allow_late_submission')
                ->default(false);

            $table->boolean('is_published')
                ->default(false)
                ->comment('Students only see published assignments');

            $table->boolean('is_active')->default(true);

            $table->string('attachment_path', 500)
                ->nullable()
                ->comment('Teacher-uploaded assignment sheet');

            $table->timestamps();

            $table->index('teacher_id', 'idx_asgn_teacher');
            $table->index('subject_id', 'idx_asgn_subject');
            $table->index('class_id', 'idx_asgn_class');
            $table->index('academic_year_id', 'idx_asgn_year');
            $table->index('due_at', 'idx_asgn_due');
            $table->index('is_published', 'idx_asgn_published');
            $table->index(
                ['class_id', 'subject_id', 'is_published'],
                'idx_asgn_class_subject_published'
            );
        });
        Schema::create('achievement_milestones', function (Blueprint $table) {
            $table->id();

            $table->foreignId('student_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->string('milestone_key', 100)
                ->comment('Unique key e.g. first_submission, streak_10, gold_tier');

            $table->string('title', 200);

            $table->text('description')->nullable();

            $table->string('icon', 50)->nullable();

            $table->string('color', 7)->nullable();

            $table->unsignedInteger('points_awarded')
                ->default(0)
                ->comment('Bonus points awarded for this milestone');

            $table->timestamp('achieved_at');

            $table->timestamps();

            // Each milestone can only be earned once per student
            $table->unique(
                ['student_id', 'milestone_key'],
                'unique_milestone_per_student'
            );

            $table->index('student_id', 'idx_am_student');
            $table->index('milestone_key', 'idx_am_key');
            $table->index('achieved_at', 'idx_am_achieved');
        });
        Schema::create('exams', function (Blueprint $table) {
            $table->id();

            $table->foreignId('teacher_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->foreignId('subject_id')
                ->constrained('subjects')
                ->cascadeOnDelete();

            $table->foreignId('class_id')
                ->nullable()
                ->constrained('school_classes')
                ->nullOnDelete();

            $table->foreignId('academic_year_id')
                ->constrained('academic_years')
                ->cascadeOnDelete();

            $table->foreignId('academic_term_id')
                ->nullable()
                ->constrained('academic_year_terms')
                ->nullOnDelete();

            $table->foreignId('class_session_id')
                ->nullable()
                ->constrained('class_sessions')
                ->nullOnDelete()
                ->comment('Optional — if exam runs within a session');

            $table->string('title', 200);
            $table->text('description')->nullable();
            $table->text('instructions')->nullable();

            $table->enum('type', [
                'quiz',
                'mid_term',
                'end_term',
                'mock',
                'standardised',
                'other',
            ])->default('mid_term');

            $table->decimal('max_score', 5, 2)->default(100.00);
            $table->decimal('passing_score', 5, 2)->default(50.00);

            $table->unsignedSmallInteger('duration_minutes')
                ->nullable()
                ->comment('Exam duration in minutes — null for take-home');

            $table->datetime('scheduled_at')
                ->comment('When the exam is scheduled to start');

            $table->enum('status', [
                'draft',
                'published',
                'in_progress',
                'completed',
                'cancelled',
            ])->default('draft');

            $table->boolean('is_active')->default(true);

            $table->string('attachment_path', 500)
                ->nullable()
                ->comment('Exam paper upload');

            $table->timestamps();

            $table->index('teacher_id', 'idx_exam_teacher');
            $table->index('subject_id', 'idx_exam_subject');
            $table->index('class_id', 'idx_exam_class');
            $table->index('academic_year_id', 'idx_exam_year');
            $table->index('scheduled_at', 'idx_exam_scheduled');
            $table->index('status', 'idx_exam_status');
            $table->index(
                ['class_id', 'subject_id', 'status'],
                'idx_exam_class_subject_status'
            );
        });
        Schema::create('class_timetables', function (Blueprint $table) {
            $table->id();

            $table->foreignId('class_id')
                ->constrained('school_classes')
                ->cascadeOnDelete();

            $table->foreignId('subject_id')
                ->constrained('subjects')
                ->cascadeOnDelete();

            $table->foreignId('teacher_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->foreignId('academic_year_id')
                ->constrained('academic_years')
                ->cascadeOnDelete();

            $table->foreignId('academic_term_id')
                ->nullable()
                ->constrained('academic_year_terms')
                ->nullOnDelete()
                ->comment('Nullable — null means timetable applies to whole year');

            $table->enum('day_of_week', [
                'monday', 'tuesday', 'wednesday',
                'thursday', 'friday', 'saturday', 'sunday',
            ]);

            $table->time('start_time');
            $table->time('end_time');

            $table->string('room_location', 100)
                ->nullable()
                ->comment('e.g. Room 12, Lab A, Online');

            $table->enum('default_mode', ['online', 'in_person', 'hybrid'])
                ->default('in_person')
                ->comment('Default delivery mode — overridable per session');

            $table->boolean('is_active')
                ->default(true);

            $table->text('notes')->nullable();

            $table->timestamps();

            // Prevent timetable collisions: same class, same day, same start time
            $table->unique(
                ['class_id', 'academic_year_id', 'academic_term_id', 'day_of_week', 'start_time'],
                'unique_class_timetable_slot'
            );

            $table->index('class_id', 'idx_tt_class');
            $table->index('teacher_id', 'idx_tt_teacher');
            $table->index('subject_id', 'idx_tt_subject');
            $table->index('academic_year_id', 'idx_tt_year');
            $table->index('day_of_week', 'idx_tt_day');
            $table->index(
                ['teacher_id', 'day_of_week', 'start_time'],
                'idx_tt_teacher_day_time'
            );
        });
        Schema::create('goal_progress', function (Blueprint $table) {
            $table->id();

            $table->foreignId('goal_id')
                ->constrained('student_goals')
                ->cascadeOnDelete();

            $table->foreignId('logged_by')
                ->constrained('users')
                ->cascadeOnDelete()
                ->comment('Teacher who logged this progress entry');

            $table->unsignedTinyInteger('progress_percentage')
                ->comment('Current progress 0-100%');

            $table->text('notes')
                ->nullable()
                ->comment('Teacher notes on this progress entry');

            $table->timestamp('logged_at');

            $table->timestamps();

            $table->index('goal_id', 'idx_gp_goal');
            $table->index('logged_by', 'idx_gp_logger');
            $table->index('logged_at', 'idx_gp_logged_at');
        });
        Schema::create('activity_feed', function (Blueprint $table) {
            $table->id();
            $table->uuid('uuid')->unique();


            $table->foreignId('user_id')
                ->nullable()
                ->constrained('users')
                ->cascadeOnDelete();

            $table->foreignId('actor_id')
                ->nullable()
                ->constrained('users')
                ->cascadeOnDelete();

            $table->string('event_type', 100)->index();

            // FIXED: proper polymorphic context
            $table->morphs('context');

            $table->json('payload')->nullable();

            $table->timestamps();

            // BI optimization
            $table->index(['user_id', 'event_type']);
            $table->index(['actor_id', 'created_at']);
        });
        Schema::create('zoom_recordings', function (Blueprint $table) {
            $table->id();

            $table->foreignId('class_session_id')
                ->constrained('class_sessions')
                ->cascadeOnDelete();

            $table->string('zoom_recording_id', 100)
                ->unique()
                ->comment('Zoom internal recording UUID');

            $table->string('playback_url', 1000)->nullable();
            $table->string('download_url', 1000)->nullable();
            $table->string('recording_password', 100)->nullable();

            $table->unsignedInteger('duration_seconds')->nullable();
            $table->unsignedBigInteger('file_size_bytes')->nullable();
            $table->string('file_type', 20)->nullable()->comment('MP4, M4A, etc.');

            $table->enum('status', [
                'processing',
                'available',
                'deleted',
                'failed',
            ])->default('processing');

            // Access control
            $table->boolean('accessible_to_students')->default(true);
            $table->boolean('accessible_to_parents')->default(true);

            $table->timestamp('recording_start')->nullable();
            $table->timestamp('recording_end')->nullable();
            $table->timestamp('available_at')->nullable();

            $table->timestamps();

            $table->index('class_session_id', 'idx_zr_session');
            $table->index('status', 'idx_zr_status');
        });
        Schema::create('academic_year_terms', function (Blueprint $table) {
            $table->id();

            $table->foreignId('academic_year_id')
                ->constrained('academic_years')
                ->cascadeOnDelete()
                ->comment('FK to academic_years — term belongs to one year');

            $table->string('name', 50)
                ->comment('e.g. Term 1, Term 2, Term 3');

            $table->unsignedTinyInteger('term_order')
                ->default(1)
                ->comment('Sort order within academic year: 1, 2, 3');

            $table->date('start_date');
            $table->date('end_date');

            $table->boolean('is_active')
                ->default(false)
                ->comment('Only one term per academic year should be active');

            $table->text('notes')
                ->nullable();

            $table->timestamps();

            // Indexes
            $table->index('academic_year_id', 'idx_terms_academic_year');
            $table->index('is_active', 'idx_terms_active');
            $table->index(['academic_year_id', 'term_order'], 'idx_terms_year_order');

            // A term name must be unique within an academic year
            $table->unique(
                ['academic_year_id', 'name'],
                'unique_term_name_per_year'
            );
        });
        Schema::create('message_threads', function (Blueprint $table) {
            $table->id();
            $table->uuid('uuid')->unique();


            $table->unsignedBigInteger('last_message_id')->nullable()->index();

            $table->timestamps();
            $table->softDeletes();

        });
        Schema::create('message_thread_participants', function (Blueprint $table) {
            $table->id();

            $table->foreignId('message_thread_id')
                ->constrained('message_threads')
                ->cascadeOnDelete();

            $table->foreignId('user_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->string('role_snapshot', 50)->nullable();

            $table->timestamp('last_read_at')->nullable();

            $table->timestamps();

            // Prevent duplicate membership
            $table->unique(['message_thread_id', 'user_id']);

            // Fast inbox lookup
            $table->index(['user_id', 'message_thread_id']);
            $table->index(['user_id', 'last_read_at']);
        });
        Schema::create('messages', function (Blueprint $table) {
            $table->id();
            $table->uuid('uuid')->unique();


            $table->foreignId('message_thread_id')
                ->constrained('message_threads')
                ->cascadeOnDelete();

            $table->foreignId('sender_id')
                ->constrained('users');

            $table->string('subject')->nullable();
            $table->longText('body');

            $table->enum('type', ['direct', 'broadcast', 'system'])
                ->default('direct');

            $table->timestamp('sent_at')->nullable();

            $table->timestamps();
            $table->softDeletes();

            $table->index(['message_thread_id', 'created_at']);
            $table->index(['sender_id', 'type']);
        });
        Schema::create('message_recipients', function (Blueprint $table) {
            $table->id();

            $table->foreignId('message_id')
                ->constrained('messages')
                ->cascadeOnDelete();

            $table->foreignId('user_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->timestamp('read_at')->nullable();

            $table->timestamps();

            // Prevent duplicate delivery
            $table->unique(['message_id', 'user_id']);

            // BI + unread optimization
            $table->index(['user_id', 'read_at']);
            $table->index(['message_id', 'user_id']);
        });
        Schema::create('notifications', function (Blueprint $table) {
            $table->id();

            $table->foreignId('user_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->string('type', 100)
                ->comment('e.g. session_created, grade_received, action_item_assigned');

            $table->string('title', 255);
            $table->text('body');

            $table->string('action_url', 500)
                ->nullable()
                ->comment('URL to navigate to when notification is clicked');

            $table->json('data')
                ->nullable()
                ->comment('Extra payload e.g. {session_id: 5, subject: "Math"}');

            $table->boolean('is_read')->default(false);
            $table->timestamp('read_at')->nullable();

            $table->timestamps();

            $table->index(['user_id', 'is_read'], 'idx_notif_user_unread');
            $table->index(['user_id', 'created_at'], 'idx_notif_user_created');
            $table->index('type', 'idx_notif_type');
        });
        Schema::create('announcements', function (Blueprint $table) {
            $table->id();
            $table->uuid('uuid')->unique();


            $table->string('title');
            $table->longText('content');

            $table->timestamp('scheduled_at')->nullable();
            $table->timestamp('published_at')->nullable()->index();

            $table->foreignId('created_by')->constrained('users');

            $table->timestamps();
            $table->softDeletes();

        });
        Schema::create('announcement_targets', function (Blueprint $table) {
            $table->id();

            $table->foreignId('announcement_id')
                ->constrained('announcements')
                ->cascadeOnDelete();

            $table->string('role', 50);

            $table->timestamps();

            $table->unique(['announcement_id', 'role']);

            $table->index(['role']);
        });
        Schema::create('quiz_questions', function (Blueprint $table) {
            $table->id();

            $table->foreignId('exam_id')
                ->nullable()
                ->constrained('exams')
                ->cascadeOnDelete();

            $table->foreignId('assignment_id')
                ->nullable()
                ->constrained('assignments')
                ->cascadeOnDelete();

            $table->foreignId('created_by')
                ->constrained('users')
                ->cascadeOnDelete()
                ->comment('Teacher who created this question');

            $table->enum('question_type', [
                'multiple_choice',
                'true_false',
                'short_answer',
                'numeric',
            ]);

            $table->text('question_text')
                ->comment('The question content');

            $table->text('explanation')
                ->nullable()
                ->comment('Explanation shown to student after grading');

            $table->decimal('points', 5, 2)
                ->default(1.00)
                ->comment('Points awarded for correct answer');

            $table->unsignedSmallInteger('order_index')
                ->default(0)
                ->comment('Display order within the exam/assignment');

            // For numeric questions — the correct numeric answer
            $table->decimal('correct_numeric_answer', 10, 4)
                ->nullable();

            // For numeric questions — acceptable margin of error
            $table->decimal('numeric_tolerance', 10, 4)
                ->nullable()
                ->default(0)
                ->comment('e.g. 0.5 means answer within ±0.5 is correct');

            // For short_answer — the exact correct text (case-insensitive match)
            $table->string('correct_text_answer', 500)
                ->nullable();

            $table->boolean('case_sensitive')
                ->default(false)
                ->comment('For short_answer — whether match is case sensitive');

            $table->boolean('is_active')
                ->default(true);

            $table->timestamps();

            $table->index('exam_id', 'idx_qq_exam');
            $table->index('assignment_id', 'idx_qq_assignment');
            $table->index('created_by', 'idx_qq_creator');
            $table->index('question_type', 'idx_qq_type');
            $table->index('order_index', 'idx_qq_order');
        });
        Schema::create('parent_student', function (Blueprint $table) {
            $table->id();

            $table->foreignId('parent_id')
                ->constrained('users')
                ->cascadeOnDelete()
                ->comment('FK to users — must have Parent role');

            $table->foreignId('student_id')
                ->constrained('users')
                ->cascadeOnDelete()
                ->comment('FK to users — must have Student role');

            $table->enum('relationship_type', [
                'mother',
                'father',
                'guardian',
                'grandparent',
                'sibling',
                'other',
            ])->default('guardian')
                ->comment('Nature of the parental relationship');

            $table->boolean('is_primary_guardian')
                ->default(false)
                ->comment('Only one parent per student receives invoices and primary communications');

            $table->timestamp('created_at')->useCurrent();

            // A parent cannot be linked to the same student twice
            $table->unique(['parent_id', 'student_id'], 'unique_parent_student');

            // Index for student lookup (find all parents of a student)
            $table->index('student_id', 'idx_parent_student_student');

            // Index for parent lookup (find all children of a parent)
            $table->index('parent_id', 'idx_parent_student_parent');
        });
        Schema::create('parent_action_item_logs', function (Blueprint $table) {
            $table->id();

            $table->foreignId('action_item_id')
                ->constrained('parent_action_items')
                ->cascadeOnDelete();

            $table->foreignId('performed_by')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->enum('action', [
                'created',
                'assigned',
                'status_changed',
                'completed',
                'cancelled',
                'updated',
            ]);

            $table->string('from_status', 50)->nullable();
            $table->string('to_status', 50)->nullable();
            $table->text('notes')->nullable();

            $table->timestamp('created_at')->useCurrent();

            $table->index('action_item_id', 'idx_pail_item');
        });
        Schema::create('payments', function (Blueprint $table) {
            $table->id();
            $table->foreignId('invoice_id')->constrained()->restrictOnDelete();
            $table->foreignId('parent_id')->constrained('users')->restrictOnDelete();
            $table->decimal('amount', 10, 2);
            $table->string('currency', 3)->default('GHS');
            $table->enum('payment_method', ['card', 'mobile_money', 'bank_transfer', 'ussd', 'bank', 'cash', 'cheque', 'waiver']);
            $table->enum('status', ['pending', 'processing', 'successful', 'failed', 'cancelled', 'refunded'])->default('pending');
            
            $table->string('paystack_reference', 100)->nullable()->unique();
            $table->string('paystack_access_code', 100)->nullable();
            $table->string('paystack_authorization_code', 100)->nullable();
            $table->string('paystack_channel', 50)->nullable();
            $table->string('gateway_response', 255)->nullable();
            $table->timestamp('gateway_paid_at')->nullable();
            $table->json('paystack_metadata')->nullable();
            
            $table->string('reference_number', 100)->nullable();
            $table->text('notes')->nullable();
            $table->foreignId('recorded_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamp('paid_at')->nullable();
            
            $table->timestamps();
            $table->softDeletes();
            
            $table->index('paystack_reference');
            $table->index(['invoice_id', 'status']);
            $table->index('parent_id');
        });
        Schema::create('community_events', function (Blueprint $table) {
            $table->id();
            $table->uuid('uuid')->unique();
            $table->foreignId('community_channel_id')->constrained('community_channels')->cascadeOnDelete();
            $table->foreignId('class_session_id')->nullable()->constrained('class_sessions')->nullOnDelete(); // Zoom Hook
            $table->string('title');
            $table->text('description')->nullable();
            $table->foreignId('host_id')->constrained('users');
            $table->dateTime('scheduled_date');
            $table->enum('location_type', ['zoom', 'in_person', 'hybrid']);
            $table->enum('status', ['scheduled', 'in_progress', 'completed', 'cancelled'])->default('scheduled');
            $table->timestamps();
            $table->softDeletes();
        });
        Schema::create('community_goals', function (Blueprint $table) {
            $table->id();
            $table->foreignId('community_id')->constrained('communities')->cascadeOnDelete();
            $table->string('title');
            $table->string('target_metric'); // e.g., 'assignments_completed', 'attendance_rate'
            $table->unsignedInteger('target_value');
            $table->unsignedInteger('current_value')->default(0);
            $table->timestamp('expires_at')->nullable();
            $table->boolean('is_completed')->default(false);
            $table->timestamps();
        });
        Schema::create('community_challenges', function (Blueprint $table) {
            $table->id();
            $table->foreignId('community_id')->constrained('communities')->cascadeOnDelete();
            $table->string('title');
            $table->text('description');
            $table->unsignedInteger('reward_points')->default(0);
            $table->timestamp('expires_at')->nullable();
            $table->timestamps();
        });
        Schema::create('community_moderation_actions', function (Blueprint $table) {
            $table->id();
            $table->foreignId('community_id')->constrained('communities')->cascadeOnDelete();
            $table->foreignId('moderator_id')->constrained('users');
            $table->foreignId('target_user_id')->nullable()->constrained('users');
            $table->enum('action_type', ['locked_topic', 'removed_topic', 'muted_user', 'suspended_user']);
            $table->morphs('target'); // Allows targeting a Topic, Reply, or Event
            $table->string('reason');
            $table->timestamps();
        });
        Schema::create('community_polls', function (Blueprint $table) {
            $table->id();
            $table->foreignId('community_topic_id')->constrained('community_topics')->cascadeOnDelete();
            $table->string('question');
            $table->timestamp('expires_at')->nullable();
            $table->timestamps();
        });
        Schema::create('community_poll_options', function(Blueprint $table){
            $table->id();
            $table->foreignId('community_poll_id')->constrained('community_polls')->cascadeOnDelete();
            $table->string('option_text');
        });
        Schema::create('community_poll_votes', function(Blueprint $table){
            $table->id();
            $table->foreignId('community_poll_option_id')->constrained('community_poll_options')->cascadeOnDelete();
            $table->foreignId('user_id')->constrained('users');
            $table->timestamps();
            $table->unique(['community_poll_option_id', 'user_id']);
        });
        Schema::create('parent_action_items', function (Blueprint $table) {
            $table->id();

            $table->foreignId('parent_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->foreignId('student_id')
                ->constrained('users')
                ->cascadeOnDelete()
                ->comment('The student this action item relates to');

            $table->foreignId('teacher_id')
                ->nullable()
                ->constrained('users')
                ->nullOnDelete()
                ->comment('Teacher who assigned this action item');

            $table->foreignId('created_by')
                ->constrained('users')
                ->cascadeOnDelete()
                ->comment('User who created (teacher or admin)');

            $table->string('title', 255);
            $table->text('description');

            $table->date('due_date');

            $table->enum('status', [
                'pending',
                'in_progress',
                'completed',
                'cancelled',
            ])->default('pending');

            $table->timestamp('completed_at')->nullable();

            $table->timestamps();

            $table->index(['parent_id', 'status'], 'idx_pai_parent_status');
            $table->index(['student_id'], 'idx_pai_student');
            $table->index(['teacher_id', 'status'], 'idx_pai_teacher_status');
            $table->index('due_date', 'idx_pai_due_date');
        });
        Schema::create('payroll_transactions', function (Blueprint $table) {
            $table->id();
            $table->uuid('uuid')->unique();
            $table->foreignId('payroll_period_id')->constrained('payroll_periods')->cascadeOnDelete();
            $table->foreignId('payroll_calculation_id')->constrained('payroll_calculations')->cascadeOnDelete();
            
            // Polymorphic Source Tracking (Handles ClassSessions, GoalAchievements, etc.)
            $table->nullableNumericMorphs('source'); 
            
            $table->enum('transaction_type', ['session_earning', 'admin_fee', 'bonus', 'penalty', 'adjustment', 'refund', 'reversal']);
            
            $table->decimal('amount', 12, 2);
            $table->string('currency', 3)->default('GHS');
            $table->json('config_snapshot');
            
            // Phase 11.5 Reversal Ledger
            $table->foreignId('reversed_transaction_id')->nullable()->constrained('payroll_transactions');
            $table->text('reversal_reason')->nullable();
            $table->foreignId('reversed_by')->nullable()->constrained('users');
            $table->dateTime('reversed_at')->nullable();
            
            $table->timestamps();
            
            // Safest Idempotency Directive
            $table->unique(['payroll_calculation_id', 'source_type', 'source_id', 'transaction_type'], 'uniq_calc_source_type');
        });
        Schema::create('school_settings', function (Blueprint $table) {
            $table->id();

            // Identity
            $table->string('school_name')
                ->default('RP Learning Academy')
                ->comment('Displayed in headers, reports, and certificates');

            $table->string('logo_path')
                ->nullable()
                ->comment('Relative path to logo in storage/app/public');

            $table->string('tagline')
                ->nullable()
                ->comment('Short motto displayed under school name');

            // Contact
            $table->string('email')->nullable();
            $table->string('phone', 20)->nullable();
            $table->string('website')->nullable();

            // Address
            $table->string('address_line_1')->nullable();
            $table->string('address_line_2')->nullable();
            $table->string('city', 100)->nullable();
            $table->string('country', 100)
                ->default('Trinidad and Tobago');

            // Localisation
            $table->string('timezone', 50)
                ->default('America/Port_of_Spain')
                ->comment('Used by scheduling and timetable modules');

            $table->string('currency', 10)
                ->default('TTD')
                ->comment('Used by finance and payroll modules');

            $table->string('currency_symbol', 5)
                ->default('$')
                ->comment('Displayed on invoices and receipts');

            // Branding — stored as JSON for flexibility
            $table->json('branding_config')
                ->nullable()
                ->comment('JSON: primary_color, secondary_color, accent_color, font_heading, font_body');

            // Academic defaults
            $table->string('default_country', 100)
                ->default('Trinidad and Tobago')
                ->comment('Pre-fills country field in user profile creation');

            $table->timestamps();
        });
        Schema::create('user_profiles', function (Blueprint $table) {
            $table->id();

            $table->foreignId('user_id')
                ->constrained('users')
                ->cascadeOnDelete()
                ->comment('FK to users table — one profile per user');

            // Personal Information
            $table->date('date_of_birth')
                ->nullable()
                ->comment('Used in certificates and age verification');

            $table->enum('gender', ['male', 'female', 'other', 'prefer_not_to_say'])
                ->nullable();

            // Address
            $table->string('address_line_1')->nullable();
            $table->string('address_line_2')->nullable();
            $table->string('city', 100)->nullable();
            $table->string('country', 100)->nullable()->default('Trinidad and Tobago');

            // Emergency Contact
            $table->string('emergency_contact_name')->nullable();
            $table->string('emergency_contact_phone', 20)->nullable();

            // Professional — Teacher specific
            $table->text('qualifications')
                ->nullable()
                ->comment('[Teacher] Academic qualifications and certifications');

            $table->text('bio')
                ->nullable()
                ->comment('Short professional bio — displayed on teacher/student cards');

            // Academic
            $table->date('joined_date')
                ->nullable()
                ->comment('Date user joined the school — used in certificates');

            $table->timestamps();

            // Indexes
            $table->unique('user_id');
        });
        Schema::create('payroll_engine_versions', function (Blueprint $table) {
            $table->id();
            $table->string('version_number')->unique(); // e.g., v1.0.0-2026
            $table->string('name');
            $table->text('description')->nullable();
            $table->boolean('is_active')->default(false);
            $table->foreignId('created_by')->constrained('users');
            $table->timestamps();
        });
        Schema::create('student_subject_enrollments', function (Blueprint $table) {
            $table->id();

            $table->foreignId('student_id')
                ->constrained('users')
                ->cascadeOnDelete()
                ->comment('FK to users — must have Student role');

            $table->foreignId('subject_id')
                ->constrained('subjects')
                ->cascadeOnDelete()
                ->comment('FK to subjects');

            $table->foreignId('academic_year_id')
                ->constrained('academic_years')
                ->cascadeOnDelete()
                ->comment('FK to academic_years — enrollment is year-scoped');

            $table->enum('status', [
                'active',
                'dropped',
                'completed',
            ])->default('active')
                ->comment('Enrollment lifecycle: active during year, dropped if withdrawn, completed at year end');

            $table->date('enrolled_date')
                ->comment('Date the student was enrolled in this subject');

            $table->date('dropped_date')
                ->nullable()
                ->comment('Date the student dropped — null if still enrolled');

            $table->text('notes')
                ->nullable()
                ->comment('Admin notes on enrollment decisions');

            $table->timestamps();

            // A student cannot be enrolled in the same subject twice in the same year
            $table->unique(
                ['student_id', 'subject_id', 'academic_year_id'],
                'unique_student_subject_enrollment'
            );

            // Performance indexes
            $table->index('student_id', 'idx_sse_student');
            $table->index('subject_id', 'idx_sse_subject');
            $table->index('academic_year_id', 'idx_sse_year');
            $table->index('status', 'idx_sse_status');
            $table->index(
                ['subject_id', 'academic_year_id', 'status'],
                'idx_sse_subject_year_status'
            );
        });
        Schema::create('teacher_availability', function (Blueprint $table) {
            $table->id();

            $table->foreignId('teacher_id')
                ->constrained('users')
                ->cascadeOnDelete()
                ->comment('FK to users — must have Teacher role');

            $table->enum('day_of_week', [
                'monday', 'tuesday', 'wednesday',
                'thursday', 'friday', 'saturday', 'sunday',
            ])->comment('Day this availability applies to');

            $table->time('start_time')
                ->comment('Availability window start');

            $table->time('end_time')
                ->comment('Availability window end');

            $table->boolean('is_active')
                ->default(true)
                ->comment('Can be toggled without deletion');

            $table->text('notes')->nullable();

            $table->timestamps();

            // A teacher cannot have duplicate availability for the same day/time
            $table->unique(
                ['teacher_id', 'day_of_week', 'start_time'],
                'unique_teacher_availability'
            );

            $table->index('teacher_id', 'idx_ta_teacher');
            $table->index('day_of_week', 'idx_ta_day');
        });
        Schema::create('notification_preferences', function (Blueprint $table) {
            $table->id();

            $table->foreignId('user_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->enum('category', [
                'academic_updates',
                'financial_invoices',
                'attendance_warnings',
                'payroll_earnings',
                'messages',
                'session_notifications',
                'system',
            ]);

            $table->boolean('in_app')->default(true);
            $table->boolean('email')->default(true);
            $table->boolean('sms')->default(false);

            $table->timestamps();

            $table->unique(['user_id', 'category'], 'unique_notif_pref_user_category');
        });
        Schema::create('invoices', function (Blueprint $table) {
            $table->id();
            $table->uuid('uuid')->unique();
            $table->foreignId('student_id')->constrained('users')->restrictOnDelete();
            $table->foreignId('parent_id')->constrained('users')->restrictOnDelete();
            $table->foreignId('academic_year_id')->constrained()->restrictOnDelete();
            $table->foreignId('term_id')->constrained('academic_year_terms')->restrictOnDelete();
            $table->string('invoice_number', 20)->unique();
            
            $table->decimal('subtotal', 10, 2)->default(0);
            $table->decimal('discount_amount', 10, 2)->default(0);
            $table->decimal('total_amount', 10, 2);
            $table->decimal('amount_paid', 10, 2)->default(0);
            $table->decimal('balance_remaining', 10, 2);
            
            $table->string('currency', 3)->default('GHS');
            $table->enum('status', ['draft', 'pending', 'partially_paid', 'paid', 'overdue', 'cancelled', 'voided'])->default('draft');
            $table->date('due_date');
            $table->text('notes')->nullable();
            
            $table->timestamp('sent_at')->nullable();
            $table->timestamp('paid_at')->nullable();
            $table->foreignId('created_by')->constrained('users')->restrictOnDelete();
            $table->json('metadata')->nullable();
            
            $table->timestamps();
            $table->softDeletes();
            
            $table->index(['academic_year_id', 'term_id', 'status']);
            $table->index(['parent_id', 'status']);
            $table->index('uuid');
        });
        Schema::create('streaks', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained('users')->cascadeOnDelete();
            $table->string('type', 100); // e.g., 'attendance', 'homework', 'community_participation'
            $table->unsignedInteger('current_streak')->default(0);
            $table->unsignedInteger('longest_streak')->default(0);
            $table->timestamp('last_activity_at')->nullable();
            $table->timestamps();

            $table->unique(['user_id', 'type']);
        });
        Schema::create('reputation_transactions', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained('users')->cascadeOnDelete();
            $table->enum('persona', ['student', 'teacher', 'parent']); // Strict OPS Isolation
            $table->integer('points');
            $table->string('reason');
            $table->morphs('source'); // Binds points to a specific Reply, Attendance log, etc.
            $table->timestamps();

            $table->index(['user_id', 'persona', 'created_at']);
        });
        Schema::create('badges', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('description');
            $table->string('icon_path')->nullable();
            $table->string('criteria_type'); // Hook for OPSEngineService calculation
            $table->timestamps();
        });
        Schema::create('user_badges', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained('users')->cascadeOnDelete();
            $table->foreignId('badge_id')->constrained('badges')->cascadeOnDelete();
            $table->timestamp('awarded_at')->useCurrent();

            $table->unique(['user_id', 'badge_id']);
        });
        Schema::create('leaderboard_snapshots', function (Blueprint $table) {
            $table->id();
            $table->enum('category', ['overall', 'attendance', 'helpfulness']);
            $table->enum('timeframe', ['weekly', 'monthly', 'all_time']);
            $table->date('snapshot_date');
            $table->timestamps();
        });
        Schema::create('leaderboard_entries', function (Blueprint $table) {
            $table->id();
            $table->foreignId('leaderboard_snapshot_id')->constrained('leaderboard_snapshots')->cascadeOnDelete();
            $table->foreignId('user_id')->constrained('users')->cascadeOnDelete();
            $table->unsignedInteger('rank');
            $table->unsignedInteger('score');

            $table->index(['leaderboard_snapshot_id', 'rank']);
        });
        Schema::create('students', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
        });
        Schema::create('specialists', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
        });
        Schema::create('reports', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
        });
        Schema::create('learning_sessions', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
        });
        Schema::create('teacher_compensation_settings', function (Blueprint $table) {
            $table->id();

            $table->foreignId('teacher_id')
                ->unique()
                ->constrained('users')
                ->cascadeOnDelete();

            $table->enum('rate_type', ['per_session', 'per_hour'])
                ->default('per_session');

            $table->decimal('rate_value', 10, 2)
                ->default(0.00)
                ->comment('Rate in GHS per session or per hour');

            $table->boolean('admin_fee_enabled')
                ->default(false)
                ->comment('Whether an admin fee is deducted from this teacher\'s gross');

            $table->decimal('admin_fee_percentage', 5, 2)
                ->default(0.00)
                ->comment('Percentage deducted as admin fee — captured AT payroll execution');

            $table->string('currency', 10)->default('GHS');

            $table->text('notes')->nullable();

            $table->timestamps();

            $table->index('teacher_id', 'idx_tcs_teacher');
        });
        Schema::create('teacher_invitations', function (Blueprint $table) {
            $table->id();

            $table->string('email')->comment('Email address the invitation was sent to');

            $table->foreignId('invited_by')
                ->constrained('users')
                ->cascadeOnDelete()
                ->comment('Admin who sent the invitation');

            $table->string('token', 64)
                ->unique()
                ->comment('Hashed invitation token');

            $table->enum('status', ['pending', 'accepted', 'expired'])
                ->default('pending');

            $table->text('personal_message')
                ->nullable()
                ->comment('Optional message from admin included in invitation email');

            $table->timestamp('expires_at')
                ->comment('Token expires 72 hours after creation');

            $table->timestamp('accepted_at')->nullable();

            $table->foreignId('accepted_by_user_id')
                ->nullable()
                ->constrained('users')
                ->nullOnDelete()
                ->comment('The user account created via this invitation');

            $table->timestamps();

            $table->index('email', 'idx_ti_email');
            $table->index('token', 'idx_ti_token');
            $table->index('status', 'idx_ti_status');
        });
        Schema::create('student_goals', function (Blueprint $table) {
            $table->id();

            $table->foreignId('student_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->foreignId('teacher_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->foreignId('subject_id')
                ->nullable()
                ->constrained('subjects')
                ->nullOnDelete()
                ->comment('Null = general goal not subject-specific');

            $table->foreignId('academic_year_id')
                ->constrained('academic_years')
                ->cascadeOnDelete();

            $table->string('title', 200);

            $table->text('description')->nullable();

            $table->enum('status', [
                'active',
                'achieved',
                'cancelled',
                'on_hold',
            ])->default('active');

            $table->date('target_date')
                ->comment('Deadline for achieving this goal');

            $table->unsignedTinyInteger('target_percentage')
                ->default(100)
                ->comment('Target progress percentage to consider goal achieved');

            $table->text('success_criteria')
                ->nullable()
                ->comment('What does achievement look like?');

            $table->timestamp('achieved_at')->nullable();

            $table->timestamps();

            $table->index('student_id', 'idx_sg_student');
            $table->index('teacher_id', 'idx_sg_teacher');
            $table->index('academic_year_id', 'idx_sg_year');
            $table->index('status', 'idx_sg_status');
            $table->index('target_date', 'idx_sg_target');
            $table->index(
                ['student_id', 'status'],
                'idx_sg_student_status'
            );
        });
        Schema::create('subjects', function (Blueprint $table) {
            $table->id();

            $table->string('name', 100)
                ->comment('Display name e.g. Mathematics');

            $table->string('code', 20)
                ->comment('Short code e.g. MATH101 — unique identifier');

            $table->text('description')
                ->nullable()
                ->comment('Curriculum description shown to students and parents');

            $table->string('color', 7)
                ->default('#0B3A66')
                ->comment('Hex color used for badges and timetable UI');

            $table->string('icon', 50)
                ->nullable()
                ->comment('Optional icon identifier (Font Awesome class or emoji)');

            $table->boolean('is_active')
                ->default(true)
                ->comment('Inactive subjects do not appear in new enrollments');

            $table->timestamps();

            // Subject code must be globally unique
            $table->unique('code', 'unique_subject_code');

            // Index for active subject lookup
            $table->index('is_active', 'idx_subjects_active');

            // Index for name lookup in admin search
            $table->index('name', 'idx_subjects_name');
        });
        Schema::create('student_question_answers', function (Blueprint $table) {
            $table->id();

            $table->foreignId('question_id')
                ->constrained('quiz_questions')
                ->cascadeOnDelete();

            $table->foreignId('student_id')
                ->constrained('users')
                ->cascadeOnDelete();

            // Parent context — one of these will be set
            $table->foreignId('submission_id')
                ->nullable()
                ->constrained('assignment_submissions')
                ->cascadeOnDelete()
                ->comment('Set when question belongs to an assignment');

            $table->foreignId('exam_result_id')
                ->nullable()
                ->constrained('exam_results')
                ->cascadeOnDelete()
                ->comment('Set when question belongs to an exam');

            // Student response fields — one populated based on question_type
            $table->foreignId('selected_option_id')
                ->nullable()
                ->constrained('quiz_question_options')
                ->nullOnDelete()
                ->comment('For multiple_choice and true_false');

            $table->string('text_answer', 500)
                ->nullable()
                ->comment('For short_answer questions');

            $table->decimal('numeric_answer', 10, 4)
                ->nullable()
                ->comment('For numeric questions');

            // Grading outcome
            $table->boolean('is_correct')
                ->nullable()
                ->comment('Null until graded. Auto-set for MCQ/TF/numeric.');

            $table->decimal('points_awarded', 5, 2)
                ->nullable()
                ->comment('Points given for this answer');

            $table->boolean('requires_review')
                ->default(false)
                ->comment('True for short_answer needing teacher review');

            $table->text('teacher_override_note')
                ->nullable()
                ->comment('Teacher note when manually overriding auto-grade');

            $table->timestamp('answered_at')
                ->nullable();

            $table->timestamps();

            // One answer per student per question per submission context
            $table->unique(
                ['question_id', 'student_id', 'submission_id'],
                'unique_answer_per_submission'
            );

            $table->unique(
                ['question_id', 'student_id', 'exam_result_id'],
                'unique_answer_per_exam_result'
            );

            $table->index('question_id', 'idx_sqa_question');
            $table->index('student_id', 'idx_sqa_student');
            $table->index('submission_id', 'idx_sqa_submission');
            $table->index('exam_result_id', 'idx_sqa_exam_result');
            $table->index('is_correct', 'idx_sqa_correct');
            $table->index('requires_review', 'idx_sqa_review');
        });
        Schema::create('class_session_joins', function (Blueprint $table) {
            $table->id();

            $table->foreignId('class_session_id')
                ->constrained('class_sessions')
                ->cascadeOnDelete();

            $table->foreignId('student_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->timestamp('joined_at')
                ->comment('When student clicked [Join Session] in portal');

            $table->timestamp('left_at')
                ->nullable()
                ->comment('When student left — null if still in session');

            $table->unsignedSmallInteger('duration_minutes')
                ->nullable()
                ->comment('Calculated: left_at - joined_at in minutes');

            $table->unsignedSmallInteger('minutes_after_start')
                ->nullable()
                ->comment('How many minutes after session start_time the student joined');

            $table->boolean('auto_marked_present')
                ->default(false)
                ->comment('True once auto-attendance fires for this student in this session');

            $table->string('join_ip', 45)->nullable()->comment('IPv4 or IPv6');
            $table->string('user_agent', 500)->nullable();

            $table->timestamps();

            $table->index('class_session_id', 'idx_csj_session');
            $table->index('student_id', 'idx_csj_student');
            $table->index('joined_at', 'idx_csj_joined');
            $table->index('auto_marked_present', 'idx_csj_auto');
            $table->index(
                ['class_session_id', 'student_id'],
                'idx_csj_session_student'
            );
        });
        Schema::create('class_sessions', function (Blueprint $table) {
            $table->id();

            $table->foreignId('class_id')
                ->nullable()
                ->constrained('school_classes')
                ->nullOnDelete()
                ->comment('Nullable for 1:1 private sessions with no class');

            $table->foreignId('subject_id')
                ->constrained('subjects')
                ->cascadeOnDelete();

            $table->foreignId('teacher_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->foreignId('academic_year_id')
                ->constrained('academic_years')
                ->cascadeOnDelete();

            $table->foreignId('academic_term_id')
                ->nullable()
                ->constrained('academic_year_terms')
                ->nullOnDelete();

            $table->foreignId('timetable_id')
                ->nullable()
                ->constrained('class_timetables')
                ->nullOnDelete()
                ->comment('Source timetable — null for ad-hoc sessions');

            $table->enum('session_type', ['online', 'in_person', 'hybrid'])
                ->default('in_person');

            $table->date('session_date');
            $table->time('start_time');
            $table->time('end_time');

            $table->string('room_location', 100)->nullable();

            $table->string('zoom_link', 500)
                ->nullable()
                ->comment('Zoom meeting URL for online sessions');

            $table->string('zoom_meeting_id', 50)->nullable();
            $table->string('zoom_passcode', 20)->nullable();

            $table->enum('status', [
                'scheduled',
                'in_progress',
                'completed',
                'cancelled',
                'rescheduled',
            ])->default('scheduled');

            $table->text('topic')
                ->nullable()
                ->comment('Lesson topic or learning objective');

            $table->text('teacher_notes')
                ->nullable()
                ->comment('Teacher notes after the session');

            $table->timestamp('started_at')
                ->nullable()
                ->comment('Actual session start timestamp');

            $table->timestamp('ended_at')
                ->nullable()
                ->comment('Actual session end timestamp');

            $table->timestamps();

            // Indexes
            $table->index('session_date', 'idx_cs_date');
            $table->index('class_id', 'idx_cs_class');
            $table->index('subject_id', 'idx_cs_subject');
            $table->index('teacher_id', 'idx_cs_teacher');
            $table->index('academic_year_id', 'idx_cs_year');
            $table->index('academic_term_id', 'idx_cs_term');
            $table->index('status', 'idx_cs_status');
            $table->index('timetable_id', 'idx_cs_timetable');
            $table->index(
                ['teacher_id', 'session_date', 'status'],
                'idx_cs_teacher_date_status'
            );
            $table->index(
                ['class_id', 'session_date'],
                'idx_cs_class_date'
            );
        });
        Schema::create('student_class_enrollments', function (Blueprint $table) {
            $table->id();

            $table->foreignId('student_id')
                ->constrained('users')
                ->cascadeOnDelete()
                ->comment('FK to users — must have Student role');

            $table->foreignId('class_id')
                ->constrained('school_classes')
                ->cascadeOnDelete()
                ->comment('FK to school_classes');

            $table->foreignId('academic_year_id')
                ->constrained('academic_years')
                ->cascadeOnDelete()
                ->comment('FK to academic_years');

            $table->foreignId('academic_term_id')
                ->nullable()
                ->constrained('academic_year_terms')
                ->nullOnDelete()
                ->comment('FK to academic_year_terms — nullable for year-long enrollments');

            $table->enum('status', [
                'active',
                'withdrawn',
                'transferred',
                'completed',
            ])->default('active')
                ->comment('Enrollment lifecycle status');

            $table->date('enrolled_date')
                ->comment('Date the student was placed in this class');

            $table->date('withdrawn_date')
                ->nullable()
                ->comment('Date the student left — null if still enrolled');

            $table->text('notes')
                ->nullable()
                ->comment('Admin notes on transfers or special circumstances');

            $table->timestamps();

            // A student cannot be in the same class twice in the same term
            $table->unique(
                ['student_id', 'class_id', 'academic_year_id', 'academic_term_id'],
                'unique_student_class_enrollment'
            );

            // Performance indexes
            $table->index('student_id', 'idx_sce_student');
            $table->index('class_id', 'idx_sce_class');
            $table->index('academic_year_id', 'idx_sce_year');
            $table->index('academic_term_id', 'idx_sce_term');
            $table->index('status', 'idx_sce_status');
            $table->index(
                ['class_id', 'academic_year_id', 'status'],
                'idx_sce_class_year_status'
            );
        });
        Schema::create('exam_results', function (Blueprint $table) {
            $table->id();

            $table->foreignId('exam_id')
                ->constrained('exams')
                ->cascadeOnDelete();

            $table->foreignId('student_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->foreignId('graded_by')
                ->nullable()
                ->constrained('users')
                ->nullOnDelete();

            $table->enum('status', [
                'absent',
                'present',
                'graded',
                'finalised',
                'appealed',
            ])->default('present');

            $table->decimal('score', 5, 2)
                ->nullable()
                ->comment('Raw score — null until graded');

            $table->decimal('percentage', 5, 2)
                ->nullable()
                ->comment('Calculated: score / max_score * 100');

            $table->string('grade_letter', 5)
                ->nullable()
                ->comment('e.g. A, B+, C — calculated from percentage');

            $table->boolean('is_passing')
                ->nullable();

            $table->text('teacher_remarks')->nullable();

            $table->boolean('is_finalised')
                ->default(false)
                ->comment('Finalised results cannot be edited without audit trail');

            $table->datetime('graded_at')->nullable();
            $table->datetime('finalised_at')->nullable();

            $table->timestamps();

            // One result per student per exam
            $table->unique(
                ['exam_id', 'student_id'],
                'unique_result_per_student'
            );

            $table->index('exam_id', 'idx_er_exam');
            $table->index('student_id', 'idx_er_student');
            $table->index('graded_by', 'idx_er_grader');
            $table->index('status', 'idx_er_status');
            $table->index(
                ['student_id', 'is_finalised'],
                'idx_er_student_finalised'
            );
        });
        Schema::create('payroll_calculations', function (Blueprint $table) {
            $table->id();
            $table->uuid('uuid')->unique();
            $table->foreignId('payroll_period_id')->constrained('payroll_periods')->cascadeOnDelete();
            
            $table->foreignId('teacher_id')->constrained('users')->index();
            $table->enum('calculation_status', ['draft', 'calculated', 'approved', 'locked', 'paid', 'reversed'])->default('draft');
            
            // Financials & BI Optimization
            $table->decimal('gross_amount', 12, 2)->default(0);
            $table->decimal('admin_fee_amount', 12, 2)->default(0);
            $table->decimal('admin_fee_percent', 5, 2)->default(0); 
            $table->decimal('net_amount', 12, 2)->default(0);
            $table->string('currency', 3)->default('GHS');
            $table->integer('session_count')->default(0);
            $table->integer('student_count')->default(0);
            
            // Security, Compliance, & Versioning
            $table->string('payslip_number')->unique()->index();
            $table->string('calculation_hash', 64)->nullable(); 
            $table->string('verification_hash', 64)->nullable(); 
            $table->integer('verification_version')->default(1);
            $table->json('config_snapshot'); // Now enforces currency & exchange_rate
            $table->foreignId('engine_version_id')->constrained('payroll_engine_versions'); 
            $table->dateTime('generated_at');
            
            // Signature Metadata
            $table->dateTime('signed_at')->nullable();
            $table->foreignId('signed_by')->nullable()->constrained('users');
            
            // Disbursement Tracking
            $table->enum('payment_status', ['pending', 'processing', 'completed', 'failed', 'reversed'])->default('pending');
            $table->dateTime('paid_at')->nullable();
            $table->foreignId('paid_by')->nullable()->constrained('users');
            $table->string('payment_reference')->nullable();
            $table->enum('payment_method', ['bank_transfer', 'mobile_money', 'cash', 'cheque', 'other'])->nullable();
            
            // Future-Proofing
            $table->text('notes')->nullable();
            $table->json('metadata')->nullable();
            
            $table->timestamps();
            
            // High-Performance BI Index
            $table->index(['teacher_id', 'calculation_status']);
        });
        Schema::create('policy_acceptances', function (Blueprint $table) {
            $table->id();

            $table->foreignId('user_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->enum('policy_type', [
                'terms_of_service',
                'privacy_policy',
                'payment_responsibility',
                'data_consent',
            ]);

            $table->string('version_accepted', 20)
                ->default('1.0')
                ->comment('Policy version number at time of acceptance');

            $table->string('ip_address', 45)->nullable();
            $table->text('user_agent')->nullable();

            $table->timestamp('accepted_at')->useCurrent();

            $table->index(['user_id', 'policy_type'], 'idx_pa_user_policy');
            $table->index('accepted_at', 'idx_pa_accepted_at');
        });
        Schema::create('payroll_periods', function (Blueprint $table) {
            $table->id(); 
            $table->uuid('uuid')->unique(); 
            
            $table->string('payroll_run_number')->unique()->index(); 
            $table->dateTime('starts_at');
            $table->dateTime('ends_at');
            $table->string('timezone', 50);
            $table->enum('status', ['draft', 'processing', 'pending_approval', 'approved', 'rejected', 'locked', 'paid', 'cancelled'])->default('draft');
            
            // Workflow & Audit
            $table->foreignId('creator_id')->constrained('users');
            $table->foreignId('reviewer_id')->nullable()->constrained('users');
            $table->dateTime('reviewed_at')->nullable();
            
            $table->foreignId('approver_id')->nullable()->constrained('users');
            $table->dateTime('approved_at')->nullable();
            $table->string('approver_signature_hash', 64)->nullable(); 
            
            $table->foreignId('disburser_id')->nullable()->constrained('users');
            $table->dateTime('disbursed_at')->nullable();
            
            $table->dateTime('locked_at')->nullable();
            $table->foreignId('locked_by')->nullable()->constrained('users');
            
            $table->timestamps();
            $table->softDeletes();
        });
        Schema::create('assignment_submissions', function (Blueprint $table) {
            $table->id();

            $table->foreignId('assignment_id')
                ->constrained('assignments')
                ->cascadeOnDelete();

            $table->foreignId('student_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->foreignId('graded_by')
                ->nullable()
                ->constrained('users')
                ->nullOnDelete()
                ->comment('Teacher who graded this submission');

            $table->enum('status', [
                'pending',
                'submitted',
                'late',
                'graded',
                'returned',
                'missing',
            ])->default('pending');

            $table->text('student_notes')
                ->nullable()
                ->comment('Student notes submitted with the assignment');

            $table->string('file_path', 500)
                ->nullable()
                ->comment('Student uploaded submission file');

            $table->string('file_name', 255)->nullable();
            $table->string('file_mime', 100)->nullable();
            $table->unsignedBigInteger('file_size')->nullable();

            $table->decimal('score', 5, 2)
                ->nullable()
                ->comment('Score awarded — null until graded');

            $table->text('teacher_feedback')
                ->nullable()
                ->comment('Teacher written feedback to student');

            $table->boolean('is_passing')
                ->nullable()
                ->comment('Calculated: score >= assignment.passing_score');

            $table->datetime('submitted_at')->nullable();
            $table->datetime('graded_at')->nullable();

            $table->timestamps();

            // One submission record per student per assignment
            $table->unique(
                ['assignment_id', 'student_id'],
                'unique_submission_per_student'
            );

            $table->index('assignment_id', 'idx_sub_assignment');
            $table->index('student_id', 'idx_sub_student');
            $table->index('graded_by', 'idx_sub_grader');
            $table->index('status', 'idx_sub_status');
            $table->index('submitted_at', 'idx_sub_submitted_at');
            $table->index(
                ['student_id', 'status'],
                'idx_sub_student_status'
            );
        });
        Schema::create('student_fee_assignments', function (Blueprint $table) {
            $table->id();
            $table->uuid('uuid')->unique();
            $table->foreignId('student_id')->constrained('users')->restrictOnDelete();
            $table->foreignId('fee_structure_id')->constrained()->restrictOnDelete();
            $table->foreignId('academic_year_id')->constrained()->restrictOnDelete();
            $table->foreignId('term_id')->constrained('academic_year_terms')->restrictOnDelete();
            $table->enum('status', ['active', 'waived', 'cancelled'])->default('active');
            $table->text('waiver_reason')->nullable();
            $table->foreignId('assigned_by')->constrained('users')->restrictOnDelete();
            $table->timestamps();
            $table->softDeletes();
            
            $table->index(['academic_year_id', 'term_id', 'status'], 'sfa_term_status_idx');
        });
        Schema::create('payroll_adjustments', function (Blueprint $table) {
            $table->id();
            $table->uuid('uuid')->unique();
            $table->foreignId('payroll_period_id')->constrained('payroll_periods')->cascadeOnDelete();
            $table->foreignId('teacher_id')->constrained('users');
            
            $table->enum('type', ['bonus', 'manual_bonus', 'manual_deduction', 'penalty', 'refund', 'correction', 'reversal']);
            $table->decimal('amount', 12, 2);
            $table->string('currency', 3)->default('GHS');
            $table->text('reason');
            $table->foreignId('approved_by')->constrained('users');
            
            $table->timestamps();
        });
        Schema::create('student_ops_scores', function (Blueprint $table) {
            $table->id();

            $table->foreignId('student_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->foreignId('academic_year_id')
                ->constrained('academic_years')
                ->cascadeOnDelete();

            $table->foreignId('class_id')
                ->nullable()
                ->constrained('school_classes')
                ->nullOnDelete();

            // Component scores (0-100 each)
            $table->decimal('exam_score', 5, 2)->default(0);
            $table->decimal('assignment_score', 5, 2)->default(0);
            $table->decimal('attendance_score', 5, 2)->default(0);
            $table->decimal('participation_score', 5, 2)->default(0);
            $table->decimal('behavior_score', 5, 2)->default(0);

            // Weighted total
            $table->decimal('total_score', 5, 2)->default(0);

            // Ranking
            $table->unsignedSmallInteger('current_rank')
                ->nullable()
                ->comment('Rank within class');

            $table->unsignedSmallInteger('class_size')
                ->nullable();

            $table->enum('performance_trend', [
                'up',
                'down',
                'stable',
            ])->default('stable');

            $table->decimal('previous_total_score', 5, 2)
                ->nullable()
                ->comment('Previous OPS for trend calculation');

            $table->timestamp('calculated_at')
                ->comment('When this score was last calculated');

            $table->timestamps();

            $table->unique(
                ['student_id', 'academic_year_id'],
                'unique_ops_per_student_year'
            );

            $table->index('student_id', 'idx_ops_student');
            $table->index('academic_year_id', 'idx_ops_year');
            $table->index('class_id', 'idx_ops_class');
            $table->index('total_score', 'idx_ops_score');
            $table->index('current_rank', 'idx_ops_rank');
        });
        Schema::create('school_classes', function (Blueprint $table) {
            $table->id();

            $table->string('name', 100)
                ->comment('Display name e.g. Form 3C');

            $table->string('code', 20)
                ->comment('Short code e.g. F3C — used in timetables and reports');

            $table->unsignedSmallInteger('capacity')
                ->default(25)
                ->comment('Maximum number of students allowed in this class');

            $table->text('description')
                ->nullable()
                ->comment('Optional notes about this class');

            $table->boolean('is_active')
                ->default(true)
                ->comment('Inactive classes cannot receive new enrollments');

            $table->timestamps();

            // Class code must be unique
            $table->unique('code', 'unique_class_code');

            // Indexes
            $table->index('is_active', 'idx_school_classes_active');
            $table->index('name', 'idx_school_classes_name');
        });
        Schema::create('communities', function (Blueprint $table) {
            $table->id();
            $table->uuid('uuid')->unique();
            $table->string('name');
            $table->text('description')->nullable();
            $table->enum('community_type', ['public', 'private', 'course', 'class', 'cohort', 'teacher', 'parent']);
            $table->enum('visibility_type', ['student', 'teacher', 'parent', 'mixed']);
            $table->foreignId('creator_id')->constrained('users');
            $table->timestamps();
            $table->softDeletes();

            $table->index(['community_type', 'visibility_type']);
        });
        Schema::create('community_members', function (Blueprint $table) {
            $table->id();
            $table->foreignId('community_id')->constrained('communities')->cascadeOnDelete();
            $table->foreignId('user_id')->constrained('users')->cascadeOnDelete();
            $table->enum('role', ['owner', 'community_manager', 'moderator', 'teacher', 'assistant_teacher', 'student', 'parent', 'member']);
            $table->timestamp('joined_at')->useCurrent();

            $table->unique(['community_id', 'user_id']);
        });
        Schema::create('community_channels', function (Blueprint $table) {
            $table->id();
            $table->uuid('uuid')->unique();
            $table->foreignId('community_id')->constrained('communities')->cascadeOnDelete();
            $table->string('name', 100);
            $table->string('description', 255)->nullable();
            $table->boolean('is_private')->default(false);
            $table->foreignId('creator_id')->constrained('users');
            $table->timestamps();
            $table->softDeletes();
        });
        Schema::create('community_topics', function (Blueprint $table) {
            $table->id();
            $table->uuid('uuid')->unique();
            $table->foreignId('community_channel_id')->constrained('community_channels')->cascadeOnDelete();
            $table->foreignId('creator_id')->constrained('users');

            // Educational Hooks
            $table->foreignId('assignment_id')->nullable()->constrained('assignments')->nullOnDelete();
            $table->foreignId('exam_id')->nullable()->constrained('exams')->nullOnDelete();

            $table->string('title');
            $table->longText('body');

            // State & Knowledge Base
            $table->boolean('is_pinned')->default(false);
            $table->boolean('is_locked')->default(false);
            $table->boolean('is_solved')->default(false);
            $table->boolean('is_featured')->default(false);
            $table->boolean('is_knowledge_article')->default(false);

            // Performance Metrics
            $table->unsignedInteger('views_count')->default(0);
            $table->unsignedInteger('replies_count')->default(0);
            $table->unsignedInteger('reactions_count')->default(0);

            $table->timestamps();
            $table->softDeletes();

            $table->index(['community_channel_id', 'is_pinned', 'created_at']);
        });
        Schema::create('community_replies', function (Blueprint $table) {
            $table->id();
            $table->uuid('uuid')->unique();
            $table->foreignId('community_topic_id')->constrained('community_topics')->cascadeOnDelete();
            $table->unsignedBigInteger('parent_id')->nullable(); // Nested Replies
            $table->foreignId('creator_id')->constrained('users');
            $table->longText('body');
            $table->boolean('is_accepted_answer')->default(false);
            $table->timestamp('edited_at')->nullable();
            $table->timestamps();
            $table->softDeletes();

            $table->foreign('parent_id')->references('id')->on('community_replies')->cascadeOnDelete();
            $table->index(['community_topic_id', 'parent_id', 'created_at']);
        });
        Schema::create('community_analytics_snapshots', function (Blueprint $table) {
            $table->id();
            $table->foreignId('community_id')->constrained('communities')->cascadeOnDelete();
            $table->date('snapshot_date');
            $table->unsignedInteger('active_users_count')->default(0);
            $table->unsignedInteger('new_topics_count')->default(0);
            $table->unsignedInteger('new_replies_count')->default(0);
            $table->decimal('engagement_rate', 5, 2)->default(0);

            // Defined Health Score: 30% Active Members, 25% Replies, 20% Attendance, 15% Resources, 10% Polls
            $table->unsignedInteger('health_score')->default(0)->comment('Calculated out of 100 based on standard formula');

            $table->timestamps();
            $table->unique(['community_id', 'snapshot_date']);
        });
        Schema::create('intervention_actions', function (Blueprint $table) {
            $table->id();

            $table->foreignId('plan_id')
                ->constrained('intervention_plans')
                ->cascadeOnDelete();

            $table->foreignId('logged_by')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->string('action_title', 200);

            $table->text('action_taken')
                ->comment('Description of what action was taken');

            $table->text('result')
                ->nullable()
                ->comment('Outcome or response to this action');

            $table->enum('action_type', [
                'meeting',
                'parent_contact',
                'extra_support',
                'referral',
                'monitoring',
                'note',
            ])->default('note');

            $table->date('action_date');

            $table->boolean('parent_informed')
                ->default(false)
                ->comment('Whether parent was notified of this action');

            $table->timestamps();

            $table->index('plan_id', 'idx_ia_plan');
            $table->index('logged_by', 'idx_ia_logger');
            $table->index('action_date', 'idx_ia_date');
        });
        Schema::create('teacher_student_assignments', function (Blueprint $table) {
            $table->id();

            $table->foreignId('teacher_id')
                ->constrained('users')
                ->cascadeOnDelete()
                ->comment('FK to users — must have Teacher role');

            $table->foreignId('student_id')
                ->constrained('users')
                ->cascadeOnDelete()
                ->comment('FK to users — must have Student role');

            $table->foreignId('subject_id')
                ->constrained('subjects')
                ->cascadeOnDelete();

            $table->foreignId('academic_year_id')
                ->constrained('academic_years')
                ->cascadeOnDelete();

            $table->enum('status', ['active', 'inactive', 'reassigned'])
                ->default('active')
                ->comment('reassigned = closed due to teacher change');

            $table->date('start_date')
                ->comment('Date this assignment became effective');

            $table->date('end_date')
                ->nullable()
                ->comment('Set when assignment ends — null means ongoing');

            $table->text('notes')->nullable();

            $table->timestamps();

            // One active assignment per student per subject per year
            $table->unique(
                ['student_id', 'subject_id', 'academic_year_id'],
                'unique_student_subject_assignment'
            );

            $table->index('teacher_id', 'idx_tsta_teacher');
            $table->index('student_id', 'idx_tsta_student');
            $table->index('subject_id', 'idx_tsta_subject');
            $table->index('academic_year_id', 'idx_tsta_year');
            $table->index('status', 'idx_tsta_status');
            $table->index(
                ['teacher_id', 'academic_year_id', 'status'],
                'idx_tsta_teacher_year_status'
            );
        });
        Schema::create('student_dashboard_snapshots', function (Blueprint $table) {
            $table->id();

            $table->foreignId('student_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->foreignId('academic_year_id')
                ->constrained('academic_years')
                ->cascadeOnDelete();

            $table->decimal('ops_score', 5, 2)->default(0);
            $table->unsignedSmallInteger('rank')->nullable();
            $table->decimal('attendance_rate', 5, 2)->default(0);
            $table->unsignedInteger('current_streak')->default(0);
            $table->unsignedInteger('total_points')->default(0);

            $table->string('current_tier', 50)
                ->nullable()
                ->comment('e.g. Bronze, Silver, Gold');

            $table->json('cached_data_json')
                ->nullable()
                ->comment('Full pre-computed dashboard payload');

            $table->timestamp('generated_at')
                ->comment('When this snapshot was generated');

            $table->timestamps();

            $table->unique(
                ['student_id', 'academic_year_id'],
                'unique_snapshot_per_student_year'
            );

            $table->index('student_id', 'idx_snap_student');
            $table->index('academic_year_id', 'idx_snap_year');
            $table->index('ops_score', 'idx_snap_ops');
            $table->index('generated_at', 'idx_snap_generated');
        });
        Schema::create('teacher_subject_assignments', function (Blueprint $table) {
            $table->id();

            $table->foreignId('teacher_id')
                ->constrained('users')
                ->cascadeOnDelete()
                ->comment('FK to users — must have Teacher role');

            $table->foreignId('subject_id')
                ->constrained('subjects')
                ->cascadeOnDelete();

            $table->foreignId('class_id')
                ->nullable()
                ->constrained('school_classes')
                ->nullOnDelete()
                ->comment('Nullable — null means 1:1 private teaching, no class');

            $table->foreignId('academic_year_id')
                ->constrained('academic_years')
                ->cascadeOnDelete();

            $table->enum('status', ['active', 'inactive'])
                ->default('active');

            $table->date('assigned_date');

            $table->date('end_date')
                ->nullable()
                ->comment('Set when assignment ends — null means ongoing');

            $table->text('notes')->nullable();

            $table->timestamps();

            // A teacher cannot be assigned the same subject in the same
            // class in the same year twice
            $table->unique(
                ['teacher_id', 'subject_id', 'class_id', 'academic_year_id'],
                'unique_teacher_subject_class_year'
            );

            $table->index('teacher_id', 'idx_tsa_teacher');
            $table->index('subject_id', 'idx_tsa_subject');
            $table->index('class_id', 'idx_tsa_class');
            $table->index('academic_year_id', 'idx_tsa_year');
            $table->index('status', 'idx_tsa_status');
        });
        Schema::create('announcement_reads', function (Blueprint $table) {
            $table->id();
            $table->foreignId('announcement_id')->constrained('announcements')->cascadeOnDelete();
            $table->foreignId('user_id')->constrained('users')->cascadeOnDelete();
            $table->timestamp('read_at')->useCurrent();

            $table->unique(['announcement_id', 'user_id']);
            $table->index(['user_id', 'read_at']);
        });
        Schema::create('credential_delivery_logs', function (Blueprint $table) {
            $table->id();

            $table->foreignId('student_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->foreignId('delivered_by')
                ->constrained('users')
                ->cascadeOnDelete()
                ->comment('Parent or admin who triggered delivery');

            $table->enum('delivery_method', [
                'copied',
                'downloaded_pdf',
                'emailed_parent',
                'emailed_student',
            ]);

            $table->string('recipient_email')->nullable()
                ->comment('Email address used if method is emailed_*');

            $table->timestamp('delivered_at')->useCurrent();

            $table->index('student_id', 'idx_cdl_student');
            $table->index('delivered_by', 'idx_cdl_delivered_by');
        });
        Schema::create('reactions', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained('users')->cascadeOnDelete();
            $table->morphs('reactable'); // Targets Message, Topic, Reply, etc.
            $table->enum('type', ['like', 'love', 'helpful', 'insightful', 'celebrate', 'question', 'agree', 'disagree']);
            $table->timestamps();

            $table->unique(['user_id', 'reactable_type', 'reactable_id', 'type'], 'unique_user_reaction');
        });
        Schema::create('mentions', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained('users')->cascadeOnDelete(); // Mentioned user
            $table->morphs('mentionable'); // Where they were mentioned
            $table->timestamp('read_at')->nullable();
            $table->timestamps();

            $table->index(['user_id', 'read_at']);
        });
        Schema::create('topic_subscriptions', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained('users')->cascadeOnDelete();
            $table->foreignId('community_topic_id')->constrained('community_topics')->cascadeOnDelete();
            $table->timestamps();

            $table->unique(['user_id', 'community_topic_id']);
        });
        Schema::create('intervention_plans', function (Blueprint $table) {
            $table->id();

            $table->foreignId('student_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->foreignId('teacher_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->foreignId('academic_year_id')
                ->constrained('academic_years')
                ->cascadeOnDelete();

            $table->string('title', 200);

            $table->text('reason')
                ->comment('Why this intervention is needed');

            $table->text('plan_description')
                ->nullable()
                ->comment('Overview of the intervention approach');

            $table->enum('status', [
                'active',
                'monitoring',
                'resolved',
                'escalated',
                'closed',
            ])->default('active');

            $table->enum('trigger_type', [
                'low_ops',
                'poor_attendance',
                'behavioral',
                'academic_decline',
                'manual',
            ])->default('manual')
                ->comment('What triggered this intervention');

            $table->decimal('ops_score_at_creation', 5, 2)
                ->nullable()
                ->comment('OPS score when plan was created — for context');

            $table->date('start_date');

            $table->date('review_date')
                ->nullable()
                ->comment('Scheduled review date');

            $table->date('end_date')
                ->nullable();

            $table->text('resolution_notes')
                ->nullable();

            $table->timestamps();

            $table->index('student_id', 'idx_ip_student');
            $table->index('teacher_id', 'idx_ip_teacher');
            $table->index('academic_year_id', 'idx_ip_year');
            $table->index('status', 'idx_ip_status');
            $table->index('start_date', 'idx_ip_start');
            $table->index(
                ['student_id', 'status'],
                'idx_ip_student_status'
            );
        });
        Schema::create('student_points', function (Blueprint $table) {
            $table->id();

            $table->foreignId('student_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->foreignId('awarded_by')
                ->nullable()
                ->constrained('users')
                ->nullOnDelete()
                ->comment('Teacher or system that awarded the points');

            $table->unsignedInteger('points')
                ->comment('Points awarded in this transaction');

            $table->string('reason', 200)
                ->comment('Human-readable reason for award');

            $table->enum('source', [
                'assignment_submission',
                'assignment_pass',
                'exam_pass',
                'attendance',
                'streak_bonus',
                'teacher_award',
                'milestone',
                'system',
            ])->default('system');

            $table->unsignedBigInteger('source_id')
                ->nullable()
                ->comment('ID of the source record e.g. submission_id, exam_result_id');

            $table->timestamps();

            $table->index('student_id', 'idx_sp_student');
            $table->index('awarded_by', 'idx_sp_awarder');
            $table->index('source', 'idx_sp_source');
            $table->index('created_at', 'idx_sp_created');
            $table->index(
                ['student_id', 'source'],
                'idx_sp_student_source'
            );
        });
        Schema::create('resources', function (Blueprint $table) {
            $table->id();

            $table->foreignId('teacher_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->foreignId('subject_id')
                ->constrained('subjects')
                ->cascadeOnDelete();

            $table->foreignId('category_id')
                ->constrained('resource_categories')
                ->cascadeOnDelete();

            $table->foreignId('class_id')
                ->nullable()
                ->constrained('school_classes')
                ->nullOnDelete();

            $table->foreignId('academic_year_id')
                ->nullable()
                ->constrained('academic_years')
                ->nullOnDelete();

            $table->string('title', 200);
            $table->text('description')->nullable();

            $table->enum('type', [
                'document', 'video', 'audio', 'image', 'link', 'other',
            ])->default('document');

            $table->string('file_path', 500)->nullable();
            $table->string('file_name', 255)->nullable();
            $table->string('file_mime', 100)->nullable();
            $table->unsignedBigInteger('file_size')->nullable();
            $table->string('external_url', 500)->nullable();

            $table->boolean('is_published')->default(false);
            $table->boolean('is_active')->default(true);
            $table->unsignedInteger('download_count')->default(0);

            $table->timestamps();

            $table->index('teacher_id', 'idx_res_teacher');
            $table->index('subject_id', 'idx_res_subject');
            $table->index('category_id', 'idx_res_category');
            $table->index('class_id', 'idx_res_class');
            $table->index('academic_year_id', 'idx_res_year');
            $table->index('type', 'idx_res_type');
            $table->index('is_published', 'idx_res_published');
            $table->index(
                ['subject_id', 'is_published', 'is_active'],
                'idx_res_subject_published_active'
            );
        });
        Schema::create('teacher_student_assignment_history', function (Blueprint $table) {
            $table->id();

            $table->foreignId('teacher_id')
                ->constrained('users')
                ->restrictOnDelete()
                ->comment('Original teacher — never cascade, preserve history');

            $table->foreignId('student_id')
                ->constrained('users')
                ->restrictOnDelete()
                ->comment('Student — never cascade, preserve history');

            $table->foreignId('subject_id')
                ->constrained('subjects')
                ->restrictOnDelete()
                ->comment('Subject — never cascade, preserve history');

            $table->foreignId('academic_year_id')
                ->constrained('academic_years')
                ->restrictOnDelete();

            $table->unsignedBigInteger('original_assignment_id')
                ->comment('References teacher_student_assignments.id — soft reference, no FK constraint to allow assignment deletion if needed');

            $table->date('start_date')
                ->comment('When this assignment originally started');

            $table->date('end_date')
                ->comment('When this assignment ended');

            $table->enum('reason', [
                'reassigned',
                'withdrawn',
                'completed',
                'administrative',
                'other',
            ])->default('reassigned')
                ->comment('Why this assignment ended');

            $table->text('notes')
                ->nullable()
                ->comment('Admin notes on the reassignment decision');

            $table->foreignId('closed_by')
                ->nullable()
                ->constrained('users')
                ->nullOnDelete()
                ->comment('Admin user who performed the reassignment');

            // Write-only timestamp — no updated_at
            $table->timestamp('created_at')
                ->useCurrent()
                ->comment('Immutable — set once on write, never changed');

            // Indexes for history lookup
            $table->index('teacher_id', 'idx_tsah_teacher');
            $table->index('student_id', 'idx_tsah_student');
            $table->index('subject_id', 'idx_tsah_subject');
            $table->index('academic_year_id', 'idx_tsah_year');
            $table->index('original_assignment_id', 'idx_tsah_original');
            $table->index(
                ['student_id', 'subject_id', 'academic_year_id'],
                'idx_tsah_student_subject_year'
            );
        });
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
        Schema::create('password_reset_tokens', function (Blueprint $table) {
            $table->string('email')->primary();
            $table->string('token');
            $table->timestamp('created_at')->nullable();
        });
        Schema::create('sessions', function (Blueprint $table) {
            $table->string('id')->primary();
            $table->foreignId('user_id')->nullable()->index();
            $table->string('ip_address', 45)->nullable();
            $table->text('user_agent')->nullable();
            $table->longText('payload');
            $table->integer('last_activity')->index();
        });
        Schema::create('attendance', function (Blueprint $table) {
            $table->id();

            $table->foreignId('student_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->foreignId('teacher_id')
                ->constrained('users')
                ->cascadeOnDelete()
                ->comment('Teacher who logged this attendance record');

            $table->foreignId('class_session_id')
                ->constrained('class_sessions')
                ->cascadeOnDelete();

            $table->foreignId('class_id')
                ->nullable()
                ->constrained('school_classes')
                ->nullOnDelete();

            $table->foreignId('subject_id')
                ->constrained('subjects')
                ->cascadeOnDelete();

            $table->foreignId('academic_year_id')
                ->constrained('academic_years')
                ->cascadeOnDelete();

            $table->foreignId('academic_term_id')
                ->nullable()
                ->constrained('academic_year_terms')
                ->nullOnDelete();

            $table->date('attendance_date')
                ->comment('Denormalised from class_session for fast queries');

            $table->enum('status', [
                'present',
                'absent',
                'late',
                'excused',
            ])->default('present');

            $table->enum('mode', [
                'online',
                'in_person',
            ])->default('in_person')
                ->comment('Mirrors the session delivery mode');

            $table->unsignedSmallInteger('minutes_late')
                ->nullable()
                ->comment('For late arrivals — how many minutes late');

            $table->text('remarks')
                ->nullable()
                ->comment('Teacher notes on this attendance record');

            $table->boolean('is_verified')
                ->default(false)
                ->comment('Admin verified this attendance record');

            $table->timestamps();

            // One attendance record per student per session
            $table->unique(
                ['student_id', 'class_session_id'],
                'unique_attendance_per_session'
            );

            $table->index('student_id', 'idx_att_student');
            $table->index('teacher_id', 'idx_att_teacher');
            $table->index('class_session_id', 'idx_att_session');
            $table->index('class_id', 'idx_att_class');
            $table->index('subject_id', 'idx_att_subject');
            $table->index('academic_year_id', 'idx_att_year');
            $table->index('attendance_date', 'idx_att_date');
            $table->index('status', 'idx_att_status');
            $table->index(
                ['student_id', 'academic_year_id', 'status'],
                'idx_att_student_year_status'
            );
            $table->index(
                ['class_id', 'attendance_date'],
                'idx_att_class_date'
            );
        });
        Schema::create('zoom_webhooks', function (Blueprint $table) {
            $table->id();

            $table->string('zoom_event_id', 100)
                ->unique()
                ->comment('Zoom event UUID — prevents duplicate processing');

            $table->string('event_type', 100)
                ->comment('e.g. session.participant_joined, recording.completed');

            $table->string('zoom_session_name', 100)->nullable();

            $table->json('payload')
                ->comment('Full webhook payload from Zoom');

            $table->enum('status', ['received', 'processed', 'failed', 'ignored'])
                ->default('received');

            $table->text('processing_error')->nullable();
            $table->timestamp('processed_at')->nullable();

            $table->timestamps();

            $table->index('event_type', 'idx_zw_event_type');
            $table->index('zoom_session_name', 'idx_zw_session');
            $table->index('status', 'idx_zw_status');
        });
        Schema::create('student_streaks', function (Blueprint $table) {
            $table->id();

            $table->foreignId('student_id')
                ->constrained('users')
                ->cascadeOnDelete();

            $table->unsignedInteger('current_streak')
                ->default(0)
                ->comment('Current consecutive days of activity');

            $table->unsignedInteger('longest_streak')
                ->default(0)
                ->comment('Historical best streak');

            $table->date('last_activity_date')
                ->nullable()
                ->comment('Date of most recent learning activity');

            $table->timestamps();

            $table->unique('student_id', 'unique_streak_per_student');
            $table->index('current_streak', 'idx_ss_current');
            $table->index('last_activity_date', 'idx_ss_last_activity');
        });