<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

/**
 * BATCH 6 — T-14
 * ptm_sk_documents: SK resmi PTM. Immutable setelah Published.
 */
class CreatePtmSkDocuments extends Migration
{
    public function up(): void
    {
        $this->forge->addField([
            'id'                    => ['type' => 'BIGINT',  'constraint' => 20, 'unsigned' => true, 'auto_increment' => true],
            'academic_period_id'    => ['type' => 'BIGINT',  'constraint' => 20, 'unsigned' => true, 'null' => false],
            'regulation_package_id' => ['type' => 'INT',     'constraint' => 10, 'unsigned' => true, 'null' => false],
            'revision_number'       => ['type' => 'TINYINT', 'constraint' => 3,  'unsigned' => true, 'null' => false, 'default' => 1],
            'previous_sk_id'        => ['type' => 'BIGINT',  'constraint' => 20, 'unsigned' => true, 'null' => true],
            'status'                => ['type' => 'ENUM', 'constraint' => ['DRAFT','PUBLISHED','SUPERSEDED','CANCELLED'], 'null' => false, 'default' => 'DRAFT'],
            'nomor_sk'              => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'template_config'       => ['type' => 'JSON',    'null' => true],
            'validation_summary'    => ['type' => 'TEXT',    'null' => true],
            'reviewer_user_id'      => ['type' => 'BIGINT',  'constraint' => 20, 'unsigned' => true, 'null' => true],
            'published_by'          => ['type' => 'BIGINT',  'constraint' => 20, 'unsigned' => true, 'null' => true],
            'published_at'          => ['type' => 'DATETIME', 'null' => true],
            'notes'                 => ['type' => 'TEXT',    'null' => true],
            'created_by'            => ['type' => 'BIGINT',  'constraint' => 20, 'unsigned' => true, 'null' => false],
            'created_at'            => ['type' => 'DATETIME', 'null' => true],
            'updated_at'            => ['type' => 'DATETIME', 'null' => true],
        ]);

        $this->forge->addKey('id', true);
        $this->forge->addKey(['academic_period_id', 'status']);
        $this->forge->addKey('previous_sk_id');

        $this->forge->addForeignKey('academic_period_id',    'academic_periods',         'id', 'RESTRICT', 'CASCADE');
        $this->forge->addForeignKey('regulation_package_id', 'ptm_regulation_packages',  'id', 'RESTRICT', 'CASCADE');
        $this->forge->addForeignKey('reviewer_user_id',      'users',                    'id', 'SET NULL',  'CASCADE');
        $this->forge->addForeignKey('published_by',          'users',                    'id', 'SET NULL',  'CASCADE');
        $this->forge->addForeignKey('created_by',            'users',                    'id', 'RESTRICT', 'CASCADE');
        $this->forge->createTable('ptm_sk_documents', true);

        // Self-reference FK
        $this->db->query('ALTER TABLE `ptm_sk_documents`
            ADD CONSTRAINT `fk_ptm_sk_prev` FOREIGN KEY (`previous_sk_id`) REFERENCES `ptm_sk_documents` (`id`) ON DELETE SET NULL ON UPDATE CASCADE');
    }

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