<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    public function up(): void
    {
        // 파티셔닝은 Schema::create에서 지원하지 않으므로 raw SQL 사용
        DB::statement("
            CREATE TABLE trigger_audit_logs (
                id              BIGINT UNSIGNED AUTO_INCREMENT,
                table_name      VARCHAR(64)     NOT NULL   COMMENT '변경된 테이블명',
                row_id          VARCHAR(64)     NOT NULL   COMMENT '변경된 레코드 PK',
                dml_type        ENUM('INSERT','UPDATE','DELETE') NOT NULL COMMENT 'DML 유형',
                old_values      JSON            DEFAULT NULL COMMENT '변경 전 값 (INSERT시 NULL)',
                new_values      JSON            DEFAULT NULL COMMENT '변경 후 값 (DELETE시 NULL)',
                changed_columns JSON            DEFAULT NULL COMMENT 'UPDATE시 변경된 컬럼 목록',
                tenant_id       BIGINT UNSIGNED DEFAULT NULL COMMENT '테넌트 ID',
                actor_id        BIGINT UNSIGNED DEFAULT NULL COMMENT '사용자 ID (세션변수)',
                session_info    VARCHAR(500)    DEFAULT NULL COMMENT '세션 정보 JSON (IP, UA 등)',
                db_user         VARCHAR(100)    DEFAULT NULL COMMENT 'CURRENT_USER()',
                created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '변경 시각',
                PRIMARY KEY (id, created_at)
            ) ENGINE=InnoDB
              DEFAULT CHARSET=utf8mb4
              COLLATE=utf8mb4_unicode_ci
              COMMENT='DB 트리거 기반 데이터 변경 추적'
              PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
                PARTITION p202601 VALUES LESS THAN (UNIX_TIMESTAMP('2026-02-01')),
                PARTITION p202602 VALUES LESS THAN (UNIX_TIMESTAMP('2026-03-01')),
                PARTITION p202603 VALUES LESS THAN (UNIX_TIMESTAMP('2026-04-01')),
                PARTITION p202604 VALUES LESS THAN (UNIX_TIMESTAMP('2026-05-01')),
                PARTITION p202605 VALUES LESS THAN (UNIX_TIMESTAMP('2026-06-01')),
                PARTITION p202606 VALUES LESS THAN (UNIX_TIMESTAMP('2026-07-01')),
                PARTITION p202607 VALUES LESS THAN (UNIX_TIMESTAMP('2026-08-01')),
                PARTITION p202608 VALUES LESS THAN (UNIX_TIMESTAMP('2026-09-01')),
                PARTITION p202609 VALUES LESS THAN (UNIX_TIMESTAMP('2026-10-01')),
                PARTITION p202610 VALUES LESS THAN (UNIX_TIMESTAMP('2026-11-01')),
                PARTITION p202611 VALUES LESS THAN (UNIX_TIMESTAMP('2026-12-01')),
                PARTITION p202612 VALUES LESS THAN (UNIX_TIMESTAMP('2027-01-01')),
                PARTITION p202701 VALUES LESS THAN (UNIX_TIMESTAMP('2027-02-01')),
                PARTITION p202702 VALUES LESS THAN (UNIX_TIMESTAMP('2027-03-01')),
                PARTITION p_future VALUES LESS THAN MAXVALUE
              )
        ");

        // 조회 성능 인덱스
        DB::statement("
            CREATE INDEX ix_trig_table_row_created
                ON trigger_audit_logs (table_name, row_id, created_at)
        ");

        DB::statement("
            CREATE INDEX ix_trig_tenant_created
                ON trigger_audit_logs (tenant_id, created_at)
        ");

        DB::statement("
            CREATE INDEX ix_trig_dml_created
                ON trigger_audit_logs (dml_type, created_at)
        ");
    }

    public function down(): void
    {
        DB::statement('DROP TABLE IF EXISTS trigger_audit_logs');
    }
};
