<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateTahfidzSchema extends Migration
{
    public function up()
    {
        $this->db->query('SET FOREIGN_KEY_CHECKS = 0;');

        // 1. Master data 114 surah Al-Qur'an
        $this->db->query('
            CREATE TABLE IF NOT EXISTS `quran_surahs` (
              `id`          TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
              `number`      TINYINT UNSIGNED NOT NULL,
              `name_arabic` VARCHAR(50)      NOT NULL,
              `name_latin`  VARCHAR(50)      NOT NULL,
              `name_id`     VARCHAR(60)      NOT NULL,
              `total_ayat`  SMALLINT UNSIGNED NOT NULL,
              `juz_start`   TINYINT UNSIGNED NOT NULL,
              `revelation`  ENUM(\'Makkiyah\',\'Madaniyah\') NOT NULL DEFAULT \'Makkiyah\',
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        // 2. Program Tahfidz (KBM / PK / Boarding)
        $this->db->query('
            CREATE TABLE IF NOT EXISTS `tahfidz_programs` (
              `id`                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
              `name`                 VARCHAR(100)    NOT NULL,
              `type`                 ENUM(\'kbm\',\'pk\',\'boarding\') NOT NULL DEFAULT \'kbm\',
              `academic_period_id`   BIGINT UNSIGNED NOT NULL,
              `description`          TEXT            NULL,
              `is_active`            TINYINT(1)      NOT NULL DEFAULT 1,
              `created_by`           BIGINT UNSIGNED NULL,
              `created_at`           DATETIME        NULL,
              `updated_at`           DATETIME        NULL,
              `deleted_at`           DATETIME        NULL,
              PRIMARY KEY (`id`),
              KEY `idx_tahfidz_programs_period` (`academic_period_id`),
              CONSTRAINT `fk_tahfidz_prog_period`
                FOREIGN KEY (`academic_period_id`) REFERENCES `academic_periods` (`id`)
                ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        // 3. Guru pengampu per program
        $this->db->query('
            CREATE TABLE IF NOT EXISTS `tahfidz_program_teachers` (
              `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
              `program_id` BIGINT UNSIGNED NOT NULL,
              `teacher_id` BIGINT UNSIGNED NOT NULL,
              `created_at` DATETIME NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `uq_prog_teacher` (`program_id`, `teacher_id`),
              KEY `idx_prog_teacher_teacher` (`teacher_id`),
              CONSTRAINT `fk_prog_teacher_program`
                FOREIGN KEY (`program_id`) REFERENCES `tahfidz_programs` (`id`)
                ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `fk_prog_teacher_teacher`
                FOREIGN KEY (`teacher_id`) REFERENCES `teachers` (`id`)
                ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        // 4. Siswa terdaftar per program
        $this->db->query('
            CREATE TABLE IF NOT EXISTS `tahfidz_program_students` (
              `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
              `program_id` BIGINT UNSIGNED NOT NULL,
              `student_id` BIGINT UNSIGNED NOT NULL,
              `joined_at`  DATE NULL,
              `created_at` DATETIME NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `uq_prog_student` (`program_id`, `student_id`),
              KEY `idx_prog_student_student` (`student_id`),
              CONSTRAINT `fk_prog_student_program`
                FOREIGN KEY (`program_id`) REFERENCES `tahfidz_programs` (`id`)
                ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `fk_prog_student_student`
                FOREIGN KEY (`student_id`) REFERENCES `students` (`id`)
                ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        // 5. Rekaman setoran harian
        $this->db->query('
            CREATE TABLE IF NOT EXISTS `tahfidz_setoran` (
              `id`           BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
              `program_id`   BIGINT UNSIGNED  NOT NULL,
              `student_id`   BIGINT UNSIGNED  NOT NULL,
              `teacher_id`   BIGINT UNSIGNED  NOT NULL,
              `surah_id`     TINYINT UNSIGNED NOT NULL,
              `ayat_start`   SMALLINT UNSIGNED NOT NULL DEFAULT 1,
              `ayat_end`     SMALLINT UNSIGNED NOT NULL DEFAULT 1,
              `jenis`        ENUM(\'ziyadah\',\'murajaah\') NOT NULL DEFAULT \'ziyadah\',
              `nilai`        TINYINT UNSIGNED NOT NULL DEFAULT 0,
              `catatan`      TEXT NULL,
              `setoran_date` DATE NOT NULL,
              `created_at`   DATETIME NULL,
              `updated_at`   DATETIME NULL,
              PRIMARY KEY (`id`),
              KEY `idx_setoran_program`  (`program_id`),
              KEY `idx_setoran_student`  (`student_id`),
              KEY `idx_setoran_surah`    (`surah_id`),
              KEY `idx_setoran_date`     (`setoran_date`),
              CONSTRAINT `fk_setoran_program`
                FOREIGN KEY (`program_id`) REFERENCES `tahfidz_programs` (`id`)
                ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `fk_setoran_student`
                FOREIGN KEY (`student_id`) REFERENCES `students` (`id`)
                ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `fk_setoran_teacher`
                FOREIGN KEY (`teacher_id`) REFERENCES `teachers` (`id`)
                ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        // 6. Status hafalan per surah per siswa per program
        $this->db->query('
            CREATE TABLE IF NOT EXISTS `tahfidz_hafalan` (
              `id`          BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
              `program_id`  BIGINT UNSIGNED  NOT NULL,
              `student_id`  BIGINT UNSIGNED  NOT NULL,
              `surah_id`    TINYINT UNSIGNED NOT NULL,
              `is_hafal`    TINYINT(1)       NOT NULL DEFAULT 0,
              `hafal_date`  DATE NULL,
              `verified_by` BIGINT UNSIGNED NULL,
              `catatan`     TEXT NULL,
              `created_at`  DATETIME NULL,
              `updated_at`  DATETIME NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `uq_hafalan` (`program_id`, `student_id`, `surah_id`),
              KEY `idx_hafalan_student` (`student_id`),
              CONSTRAINT `fk_hafalan_program`
                FOREIGN KEY (`program_id`) REFERENCES `tahfidz_programs` (`id`)
                ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `fk_hafalan_student`
                FOREIGN KEY (`student_id`) REFERENCES `students` (`id`)
                ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('SET FOREIGN_KEY_CHECKS = 1;');
    }

    public function down()
    {
        $this->db->query('SET FOREIGN_KEY_CHECKS = 0;');
        $this->db->query('DROP TABLE IF EXISTS `tahfidz_hafalan`');
        $this->db->query('DROP TABLE IF EXISTS `tahfidz_setoran`');
        $this->db->query('DROP TABLE IF EXISTS `tahfidz_program_students`');
        $this->db->query('DROP TABLE IF EXISTS `tahfidz_program_teachers`');
        $this->db->query('DROP TABLE IF EXISTS `tahfidz_programs`');
        $this->db->query('DROP TABLE IF EXISTS `quran_surahs`');
        $this->db->query('SET FOREIGN_KEY_CHECKS = 1;');
    }
}
