<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

/**
 * BATCH 4 — T-11
 * ptm_curriculum_blueprints: Single source of truth jam pelajaran per mapel/tingkat/regulasi.
 * Immutable setelah Published (ADR-004). Perubahan = versi baru.
 */
class CreatePtmCurriculumBlueprints extends Migration
{
    public function up(): void
    {
        $this->forge->addField([
            'id'                    => ['type' => 'INT',     'constraint' => 10, 'unsigned' => true, 'auto_increment' => true],
            'regulation_package_id' => ['type' => 'INT',     'constraint' => 10, 'unsigned' => true, 'null' => false],
            'school_subject_id'     => ['type' => 'BIGINT',  'constraint' => 20, 'unsigned' => true, 'null' => false],
            'grade_id'              => ['type' => 'BIGINT',  'constraint' => 20, 'unsigned' => true, 'null' => false],
            'workload_type_id'      => ['type' => 'INT',     'constraint' => 10, 'unsigned' => true, 'null' => false],
            'program_type'          => ['type' => 'ENUM', 'constraint' => ['REGULAR','P5','P2RA','LITERACY','EXTRACURRICULAR'], 'null' => false, 'default' => 'REGULAR'],
            'hours_per_week'        => ['type' => 'TINYINT', 'constraint' => 3, 'unsigned' => true, 'null' => false, 'comment' => 'Jam standar per minggu'],
            'version'               => ['type' => 'TINYINT', 'constraint' => 3, 'unsigned' => true, 'null' => false, 'default' => 1],
            'is_current'            => ['type' => 'TINYINT', 'constraint' => 1, 'null' => false, 'default' => 1],
            'superseded_by'         => ['type' => 'INT',     'constraint' => 10, 'unsigned' => true, 'null' => true],
            'cloned_from'           => ['type' => 'INT',     'constraint' => 10, 'unsigned' => true, 'null' => true],
            'status'                => ['type' => 'ENUM', 'constraint' => ['DRAFT','PUBLISHED','SUPERSEDED','CANCELLED'], 'null' => false, 'default' => 'DRAFT'],
            'notes'                 => ['type' => 'TEXT',    'null' => true],
            'created_by'            => ['type' => 'BIGINT',  'constraint' => 20, 'unsigned' => true, 'null' => false],
            'published_by'          => ['type' => 'BIGINT',  'constraint' => 20, 'unsigned' => true, 'null' => true],
            'published_at'          => ['type' => 'DATETIME', 'null' => true],
            'created_at'            => ['type' => 'DATETIME', 'null' => true],
            'updated_at'            => ['type' => 'DATETIME', 'null' => true],
        ]);

        $this->forge->addKey('id', true);
        $this->forge->addKey(['regulation_package_id', 'status']);
        $this->forge->addKey(['school_subject_id', 'grade_id', 'is_current']);
        $this->forge->addKey('superseded_by');

        $this->forge->addForeignKey('regulation_package_id', 'ptm_regulation_packages', 'id', 'RESTRICT', 'CASCADE');
        $this->forge->addForeignKey('school_subject_id',     'school_subjects',          'id', 'RESTRICT', 'CASCADE');
        $this->forge->addForeignKey('grade_id',              'grades',                   'id', 'RESTRICT', 'CASCADE');
        $this->forge->addForeignKey('workload_type_id',      'ptm_workload_types',        'id', 'RESTRICT', 'CASCADE');
        $this->forge->addForeignKey('created_by',            'users',                    'id', 'RESTRICT', 'CASCADE');
        // published_by nullable — pakai SET NULL agar tidak error saat NULL
        $this->forge->createTable('ptm_curriculum_blueprints', true);

        // Composite natural-key index (nama diberi manual karena >64 karakter jika auto-generated)
        $this->db->query('ALTER TABLE `ptm_curriculum_blueprints`
            ADD INDEX `idx_bp_natural_key` (`regulation_package_id`, `school_subject_id`, `grade_id`, `program_type`, `workload_type_id`, `is_current`),
            ADD CONSTRAINT `fk_ptm_bp_publisher`  FOREIGN KEY (`published_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
            ADD CONSTRAINT `fk_ptm_bp_superseded` FOREIGN KEY (`superseded_by`) REFERENCES `ptm_curriculum_blueprints` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
            ADD CONSTRAINT `fk_ptm_bp_cloned`     FOREIGN KEY (`cloned_from`)   REFERENCES `ptm_curriculum_blueprints` (`id`) ON DELETE SET NULL ON UPDATE CASCADE');
    }

    public function down(): void
    {
        $this->forge->dropTable('ptm_curriculum_blueprints', true);
    }
}
