<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Forge;
use CodeIgniter\Database\Migration;

class CreateAcademyFoundation extends Migration
{
    private readonly array $attributes;

    public function __construct(?Forge $forge = null)
    {
        parent::__construct($forge);

        $this->attributes = $this->db->getPlatform() === 'MySQLi' ? ['ENGINE' => 'InnoDB'] : [];
    }

    public function up(): void
    {
        $this->createRolesTable();
        $this->createUserRolesTable();
        $this->createParentsTable();
        $this->createCoachesTable();
        $this->createCoachAvailabilityTable();
        $this->createAcademyLevelsTable();
        $this->createAcademyGroupsTable();
        $this->createChildrenTable();
        $this->createChildGuardiansTable();
        $this->createChildCoachesTable();
        $this->createEvaluationTypesTable();
        $this->createEvaluationCriteriaTable();
        $this->createEvaluationTypeCriteriaTable();
        $this->createEvaluationRulesTable();
        $this->createEvaluationsTable();
        $this->createEvaluationScoresTable();
        $this->createEvaluationStatusHistoryTable();
        $this->createAttendanceSessionsTable();
        $this->createAttendanceRecordsTable();
        $this->createJourneyEventsTable();
        $this->createNotificationsTable();
        $this->extendSettingsTable();
    }

    public function down(): void
    {
        $this->db->disableForeignKeyChecks();

        $tables = [
            'notifications',
            'journey_events',
            'attendance_records',
            'attendance_sessions',
            'evaluation_status_history',
            'evaluation_scores',
            'evaluations',
            'evaluation_rules',
            'evaluation_type_criteria',
            'evaluation_criteria',
            'evaluation_types',
            'child_coaches',
            'child_guardians',
            'children',
            'academy_groups',
            'academy_levels',
            'coach_availability',
            'coaches',
            'parents',
            'user_roles',
            'roles',
        ];

        foreach ($tables as $table) {
            $this->forge->dropTable($table, true);
        }

        $this->db->enableForeignKeyChecks();
    }

    private function createRolesTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'slug' => ['type' => 'VARCHAR', 'constraint' => 50],
            'name' => ['type' => 'VARCHAR', 'constraint' => 100],
            'description' => ['type' => 'TEXT', 'null' => true],
            'priority' => ['type' => 'INT', 'constraint' => 11, 'default' => 10],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addUniqueKey('slug');
        $this->createTable('roles');
    }

    private function createUserRolesTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'user_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'role_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addUniqueKey(['user_id', 'role_id']);
        $this->forge->addKey('role_id');
        $this->forge->addForeignKey('user_id', 'users', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('role_id', 'roles', 'id', '', 'CASCADE');
        $this->createTable('user_roles');
    }

    private function createParentsTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'user_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'full_name' => ['type' => 'VARCHAR', 'constraint' => 150],
            'phone' => ['type' => 'VARCHAR', 'constraint' => 30, 'null' => true],
            'email' => ['type' => 'VARCHAR', 'constraint' => 120, 'null' => true],
            'relationship_type' => ['type' => 'VARCHAR', 'constraint' => 50, 'default' => 'parent'],
            'status' => ['type' => 'VARCHAR', 'constraint' => 30, 'default' => 'active'],
            'notes' => ['type' => 'TEXT', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addKey('user_id');
        $this->forge->addKey('email');
        $this->forge->addForeignKey('user_id', 'users', 'id', '', 'SET NULL');
        $this->createTable('parents');
    }

    private function createCoachesTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'user_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'full_name' => ['type' => 'VARCHAR', 'constraint' => 150],
            'display_role' => ['type' => 'VARCHAR', 'constraint' => 100],
            'phone' => ['type' => 'VARCHAR', 'constraint' => 30, 'null' => true],
            'email' => ['type' => 'VARCHAR', 'constraint' => 120, 'null' => true],
            'avatar_path' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'is_active' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'notes' => ['type' => 'TEXT', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addUniqueKey('user_id');
        $this->forge->addKey('email');
        $this->forge->addForeignKey('user_id', 'users', 'id', '', 'SET NULL');
        $this->createTable('coaches');
    }

    private function createCoachAvailabilityTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'coach_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'day_of_week' => ['type' => 'TINYINT', 'constraint' => 1],
            'start_time' => ['type' => 'TIME'],
            'end_time' => ['type' => 'TIME'],
            'is_available' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addForeignKey('coach_id', 'coaches', 'id', '', 'CASCADE');
        $this->createTable('coach_availability');
    }

    private function createAcademyLevelsTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'name' => ['type' => 'VARCHAR', 'constraint' => 100],
            'slug' => ['type' => 'VARCHAR', 'constraint' => 100],
            'sort_order' => ['type' => 'INT', 'constraint' => 11, 'default' => 0],
            'color_hex' => ['type' => 'VARCHAR', 'constraint' => 7, 'null' => true],
            'internal_description' => ['type' => 'TEXT', 'null' => true],
            'parent_description' => ['type' => 'TEXT', 'null' => true],
            'benchmark_minimum' => ['type' => 'DECIMAL', 'constraint' => '5,2', 'null' => true],
            'benchmark_target' => ['type' => 'DECIMAL', 'constraint' => '5,2', 'null' => true],
            'promotion_rules' => ['type' => 'TEXT', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addUniqueKey('slug');
        $this->createTable('academy_levels');
    }

    private function createAcademyGroupsTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'name' => ['type' => 'VARCHAR', 'constraint' => 100],
            'code' => ['type' => 'VARCHAR', 'constraint' => 50],
            'academy_level_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'primary_coach_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'schedule_summary' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'status' => ['type' => 'VARCHAR', 'constraint' => 30, 'default' => 'active'],
            'capacity' => ['type' => 'INT', 'constraint' => 11, 'default' => 12],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addUniqueKey('code');
        $this->forge->addForeignKey('academy_level_id', 'academy_levels', 'id', '', 'SET NULL');
        $this->forge->addForeignKey('primary_coach_id', 'coaches', 'id', '', 'SET NULL');
        $this->createTable('academy_groups');
    }

    private function createChildrenTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'first_name' => ['type' => 'VARCHAR', 'constraint' => 100],
            'last_name' => ['type' => 'VARCHAR', 'constraint' => 100],
            'full_name' => ['type' => 'VARCHAR', 'constraint' => 180],
            'birth_date' => ['type' => 'DATE', 'null' => true],
            'gender' => ['type' => 'VARCHAR', 'constraint' => 20, 'null' => true],
            'academy_level_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'academy_group_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'primary_coach_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'primary_guardian_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'first_class_at' => ['type' => 'DATE', 'null' => true],
            'status' => ['type' => 'VARCHAR', 'constraint' => 30, 'default' => 'active'],
            'has_siblings' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
            'notes' => ['type' => 'TEXT', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addKey('academy_level_id');
        $this->forge->addKey('academy_group_id');
        $this->forge->addKey('primary_coach_id');
        $this->forge->addKey('status');
        $this->forge->addForeignKey('academy_level_id', 'academy_levels', 'id', '', 'SET NULL');
        $this->forge->addForeignKey('academy_group_id', 'academy_groups', 'id', '', 'SET NULL');
        $this->forge->addForeignKey('primary_coach_id', 'coaches', 'id', '', 'SET NULL');
        $this->forge->addForeignKey('primary_guardian_id', 'parents', 'id', '', 'SET NULL');
        $this->createTable('children');
    }

    private function createChildGuardiansTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'child_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'parent_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'relationship_type' => ['type' => 'VARCHAR', 'constraint' => 50, 'default' => 'parent'],
            'is_primary' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addUniqueKey(['child_id', 'parent_id']);
        $this->forge->addForeignKey('child_id', 'children', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('parent_id', 'parents', 'id', '', 'CASCADE');
        $this->createTable('child_guardians');
    }

    private function createChildCoachesTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'child_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'coach_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'is_primary' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
            'assigned_at' => ['type' => 'DATETIME', 'null' => true],
            'unassigned_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addUniqueKey(['child_id', 'coach_id']);
        $this->forge->addForeignKey('child_id', 'children', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('coach_id', 'coaches', 'id', '', 'CASCADE');
        $this->createTable('child_coaches');
    }

    private function createEvaluationTypesTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'name' => ['type' => 'VARCHAR', 'constraint' => 100],
            'slug' => ['type' => 'VARCHAR', 'constraint' => 100],
            'description' => ['type' => 'TEXT', 'null' => true],
            'is_quick_check' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
            'affects_promotion' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
            'status' => ['type' => 'VARCHAR', 'constraint' => 30, 'default' => 'active'],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addUniqueKey('slug');
        $this->createTable('evaluation_types');
    }

    private function createEvaluationCriteriaTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'name' => ['type' => 'VARCHAR', 'constraint' => 120],
            'slug' => ['type' => 'VARCHAR', 'constraint' => 120],
            'description' => ['type' => 'TEXT', 'null' => true],
            'is_active' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'default_weight' => ['type' => 'DECIMAL', 'constraint' => '5,2', 'default' => 1],
            'is_critical' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
            'sort_order' => ['type' => 'INT', 'constraint' => 11, 'default' => 0],
            'min_score' => ['type' => 'DECIMAL', 'constraint' => '5,2', 'default' => 1],
            'max_score' => ['type' => 'DECIMAL', 'constraint' => '5,2', 'default' => 5],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addUniqueKey('slug');
        $this->createTable('evaluation_criteria');
    }

    private function createEvaluationTypeCriteriaTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'evaluation_type_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'evaluation_criteria_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'weight' => ['type' => 'DECIMAL', 'constraint' => '5,2', 'default' => 1],
            'is_required' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'sort_order' => ['type' => 'INT', 'constraint' => 11, 'default' => 0],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addUniqueKey(['evaluation_type_id', 'evaluation_criteria_id']);
        $this->forge->addForeignKey('evaluation_type_id', 'evaluation_types', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('evaluation_criteria_id', 'evaluation_criteria', 'id', '', 'CASCADE');
        $this->createTable('evaluation_type_criteria');
    }

    private function createEvaluationRulesTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'name' => ['type' => 'VARCHAR', 'constraint' => 120],
            'scope_type' => ['type' => 'VARCHAR', 'constraint' => 50, 'default' => 'global'],
            'scope_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'almost_ready_threshold' => ['type' => 'DECIMAL', 'constraint' => '5,2', 'default' => 3.5],
            'ready_threshold' => ['type' => 'DECIMAL', 'constraint' => '5,2', 'default' => 4.0],
            'minimum_critical_threshold' => ['type' => 'DECIMAL', 'constraint' => '5,2', 'default' => 3.0],
            'is_active' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'notes' => ['type' => 'TEXT', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addKey(['scope_type', 'scope_id']);
        $this->createTable('evaluation_rules');
    }

    private function createEvaluationsTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'child_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'evaluation_type_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'evaluation_date' => ['type' => 'DATE'],
            'evaluator_user_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'evaluator_coach_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'level_at_time_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'final_score' => ['type' => 'DECIMAL', 'constraint' => '5,2', 'default' => 0],
            'final_status' => ['type' => 'VARCHAR', 'constraint' => 30, 'default' => 'HOLD'],
            'promotion_signal' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
            'notes' => ['type' => 'TEXT', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addKey(['child_id', 'evaluation_date']);
        $this->forge->addKey('final_status');
        $this->forge->addForeignKey('child_id', 'children', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('evaluation_type_id', 'evaluation_types', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('evaluator_user_id', 'users', 'id', '', 'SET NULL');
        $this->forge->addForeignKey('evaluator_coach_id', 'coaches', 'id', '', 'SET NULL');
        $this->forge->addForeignKey('level_at_time_id', 'academy_levels', 'id', '', 'SET NULL');
        $this->createTable('evaluations');
    }

    private function createEvaluationScoresTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'evaluation_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'evaluation_criteria_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'weight' => ['type' => 'DECIMAL', 'constraint' => '5,2', 'default' => 1],
            'score' => ['type' => 'DECIMAL', 'constraint' => '5,2', 'default' => 0],
            'is_critical' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
            'notes' => ['type' => 'TEXT', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addForeignKey('evaluation_id', 'evaluations', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('evaluation_criteria_id', 'evaluation_criteria', 'id', '', 'CASCADE');
        $this->createTable('evaluation_scores');
    }

    private function createEvaluationStatusHistoryTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'evaluation_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'previous_status' => ['type' => 'VARCHAR', 'constraint' => 30, 'null' => true],
            'new_status' => ['type' => 'VARCHAR', 'constraint' => 30],
            'reason' => ['type' => 'TEXT', 'null' => true],
            'changed_by' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addForeignKey('evaluation_id', 'evaluations', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('changed_by', 'users', 'id', '', 'SET NULL');
        $this->createTable('evaluation_status_history');
    }

    private function createAttendanceSessionsTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'academy_group_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'coach_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'session_date' => ['type' => 'DATE'],
            'start_time' => ['type' => 'TIME', 'null' => true],
            'end_time' => ['type' => 'TIME', 'null' => true],
            'status' => ['type' => 'VARCHAR', 'constraint' => 30, 'default' => 'scheduled'],
            'is_cancelled' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
            'cancelled_reason' => ['type' => 'TEXT', 'null' => true],
            'recovery_for_session_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'notes' => ['type' => 'TEXT', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addKey(['academy_group_id', 'session_date']);
        $this->forge->addForeignKey('academy_group_id', 'academy_groups', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('coach_id', 'coaches', 'id', '', 'SET NULL');
        $this->forge->addForeignKey('recovery_for_session_id', 'attendance_sessions', 'id', '', 'SET NULL');
        $this->createTable('attendance_sessions');
    }

    private function createAttendanceRecordsTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'attendance_session_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'child_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'status' => ['type' => 'VARCHAR', 'constraint' => 30, 'default' => 'present'],
            'checked_in_at' => ['type' => 'DATETIME', 'null' => true],
            'notes' => ['type' => 'TEXT', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addUniqueKey(['attendance_session_id', 'child_id']);
        $this->forge->addKey('status');
        $this->forge->addForeignKey('attendance_session_id', 'attendance_sessions', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('child_id', 'children', 'id', '', 'CASCADE');
        $this->createTable('attendance_records');
    }

    private function createJourneyEventsTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'child_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'event_type' => ['type' => 'VARCHAR', 'constraint' => 60],
            'title' => ['type' => 'VARCHAR', 'constraint' => 150],
            'description' => ['type' => 'TEXT', 'null' => true],
            'metadata' => ['type' => 'TEXT', 'null' => true],
            'created_by' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addKey(['child_id', 'created_at']);
        $this->forge->addForeignKey('child_id', 'children', 'id', '', 'CASCADE');
        $this->forge->addForeignKey('created_by', 'users', 'id', '', 'SET NULL');
        $this->createTable('journey_events');
    }

    private function createNotificationsTable(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'user_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'child_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'type' => ['type' => 'VARCHAR', 'constraint' => 50],
            'title' => ['type' => 'VARCHAR', 'constraint' => 150],
            'message' => ['type' => 'TEXT'],
            'status' => ['type' => 'VARCHAR', 'constraint' => 30, 'default' => 'unread'],
            'action_url' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'read_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addKey(['user_id', 'status']);
        $this->forge->addForeignKey('user_id', 'users', 'id', '', 'SET NULL');
        $this->forge->addForeignKey('child_id', 'children', 'id', '', 'SET NULL');
        $this->createTable('notifications');
    }

    private function extendSettingsTable(): void
    {
        $columns = [];

        if (! $this->db->fieldExists('label', 'settings')) {
            $columns['label'] = ['type' => 'VARCHAR', 'constraint' => 150, 'null' => true, 'after' => 'context'];
        }

        if (! $this->db->fieldExists('group_name', 'settings')) {
            $columns['group_name'] = ['type' => 'VARCHAR', 'constraint' => 100, 'default' => 'general', 'after' => 'label'];
        }

        if (! $this->db->fieldExists('description', 'settings')) {
            $columns['description'] = ['type' => 'TEXT', 'null' => true, 'after' => 'group_name'];
        }

        if (! $this->db->fieldExists('is_public', 'settings')) {
            $columns['is_public'] = ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0, 'after' => 'description'];
        }

        if ($columns !== []) {
            $this->forge->addColumn('settings', $columns);
        }
    }

    private function createTable(string $tableName): void
    {
        $this->forge->createTable($tableName, false, $this->attributes);
    }
}
