<?php

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

/**
 * 전체 대상 테이블에 AFTER INSERT/UPDATE/DELETE 트리거를 자동 생성
 *
 * MySQL은 CREATE TRIGGER를 PREPARE/EXECUTE로 실행할 수 없으므로
 * SP 대신 PHP에서 INFORMATION_SCHEMA를 읽고 DDL을 직접 실행한다.
 */
return new class extends Migration
{
    /** 트리거 미적용 테이블 */
    private array $excludeTables = [
        'audit_logs',
        'trigger_audit_logs',
        'personal_access_tokens',
        'sessions',
        'cache',
        'cache_locks',
        'jobs',
        'job_batches',
        'failed_jobs',
        'migrations',
        'password_reset_tokens',
        'api_request_logs',
    ];

    /** 변경 추적 제외 컬럼 */
    private array $excludeColumns = [
        'created_at',
        'updated_at',
        'deleted_at',
        'remember_token',
    ];

    public function up(): void
    {
        $dbName = DB::getDatabaseName();

        // 대상 테이블 조회
        $tables = DB::select("
            SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_SCHEMA = ?
              AND TABLE_TYPE = 'BASE TABLE'
              AND TABLE_NAME NOT LIKE 'telescope\\_%'
            ORDER BY TABLE_NAME
        ", [$dbName]);

        $created = 0;
        $skipped = 0;

        foreach ($tables as $tableRow) {
            $tableName = $tableRow->TABLE_NAME;

            if (in_array($tableName, $this->excludeTables, true)) {
                $skipped++;

                continue;
            }

            try {
                $this->createTriggersForTable($dbName, $tableName);
                $created++;
            } catch (\Throwable $e) {
                // 개별 테이블 실패 시 로그 남기고 계속 진행
                logger()->warning("Audit trigger creation failed for {$tableName}: {$e->getMessage()}");
                $skipped++;
            }
        }

        logger()->info("Audit triggers: {$created} tables processed, {$skipped} skipped");
    }

    public function down(): void
    {
        $dbName = DB::getDatabaseName();

        $triggers = DB::select("
            SELECT TRIGGER_NAME
            FROM INFORMATION_SCHEMA.TRIGGERS
            WHERE TRIGGER_SCHEMA = ?
              AND (TRIGGER_NAME LIKE 'trg\\_%\\_ai'
                OR TRIGGER_NAME LIKE 'trg\\_%\\_au'
                OR TRIGGER_NAME LIKE 'trg\\_%\\_ad')
        ", [$dbName]);

        foreach ($triggers as $trigger) {
            DB::unprepared("DROP TRIGGER IF EXISTS `{$trigger->TRIGGER_NAME}`");
        }
    }

    private function createTriggersForTable(string $dbName, string $tableName): void
    {
        // PK 컬럼 확인
        $pkRow = DB::selectOne("
            SELECT COLUMN_NAME
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_KEY = 'PRI'
            ORDER BY ORDINAL_POSITION LIMIT 1
        ", [$dbName, $tableName]);

        if (! $pkRow) {
            return; // PK 없으면 스킵
        }
        $pk = $pkRow->COLUMN_NAME;

        // 컬럼 목록 (제외 컬럼 필터링)
        $columns = DB::select('
            SELECT COLUMN_NAME
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
            ORDER BY ORDINAL_POSITION
        ', [$dbName, $tableName]);

        $cols = [];
        $hasTenantId = false;
        foreach ($columns as $col) {
            if ($col->COLUMN_NAME === 'tenant_id') {
                $hasTenantId = true;
            }
            if (! in_array($col->COLUMN_NAME, $this->excludeColumns, true)) {
                $cols[] = $col->COLUMN_NAME;
            }
        }

        if (empty($cols)) {
            return;
        }

        // JSON_OBJECT 구문 조립
        $jsonNew = implode(',', array_map(fn ($c) => "'{$c}', NEW.`{$c}`", $cols));
        $jsonOld = implode(',', array_map(fn ($c) => "'{$c}', OLD.`{$c}`", $cols));

        // UPDATE 변경 감지 조건
        $changeCheck = implode(' OR ', array_map(
            fn ($c) => "NOT(OLD.`{$c}` <=> NEW.`{$c}`)",
            $cols
        ));

        // changed_columns 배열 (변경된 컬럼명만)
        $changedCols = implode(',', array_map(
            fn ($c) => "IF(NOT(OLD.`{$c}` <=> NEW.`{$c}`),'{$c}',NULL)",
            $cols
        ));

        $tenantNew = $hasTenantId ? 'NEW.`tenant_id`' : 'NULL';
        $tenantOld = $hasTenantId ? 'OLD.`tenant_id`' : 'NULL';

        // 기존 트리거 삭제
        DB::unprepared("DROP TRIGGER IF EXISTS `trg_{$tableName}_ai`");
        DB::unprepared("DROP TRIGGER IF EXISTS `trg_{$tableName}_au`");
        DB::unprepared("DROP TRIGGER IF EXISTS `trg_{$tableName}_ad`");

        // AFTER INSERT
        DB::unprepared("
            CREATE TRIGGER `trg_{$tableName}_ai` AFTER INSERT ON `{$tableName}`
            FOR EACH ROW
            BEGIN
                IF @disable_audit_trigger IS NULL OR @disable_audit_trigger != 1 THEN
                    INSERT INTO trigger_audit_logs
                        (table_name, row_id, dml_type, old_values, new_values, tenant_id, actor_id, session_info, db_user, created_at)
                    VALUES
                        ('{$tableName}', CAST(NEW.`{$pk}` AS CHAR), 'INSERT', NULL,
                         JSON_OBJECT({$jsonNew}),
                         {$tenantNew}, @sam_actor_id, @sam_session_info, CURRENT_USER(), NOW());
                END IF;
            END
        ");

        // AFTER UPDATE (변경 있을 때만)
        DB::unprepared("
            CREATE TRIGGER `trg_{$tableName}_au` AFTER UPDATE ON `{$tableName}`
            FOR EACH ROW
            BEGIN
                IF @disable_audit_trigger IS NULL OR @disable_audit_trigger != 1 THEN
                    IF {$changeCheck} THEN
                        INSERT INTO trigger_audit_logs
                            (table_name, row_id, dml_type, old_values, new_values, changed_columns, tenant_id, actor_id, session_info, db_user, created_at)
                        VALUES
                            ('{$tableName}', CAST(NEW.`{$pk}` AS CHAR), 'UPDATE',
                             JSON_OBJECT({$jsonOld}),
                             JSON_OBJECT({$jsonNew}),
                             JSON_ARRAY({$changedCols}),
                             {$tenantNew}, @sam_actor_id, @sam_session_info, CURRENT_USER(), NOW());
                    END IF;
                END IF;
            END
        ");

        // AFTER DELETE
        DB::unprepared("
            CREATE TRIGGER `trg_{$tableName}_ad` AFTER DELETE ON `{$tableName}`
            FOR EACH ROW
            BEGIN
                IF @disable_audit_trigger IS NULL OR @disable_audit_trigger != 1 THEN
                    INSERT INTO trigger_audit_logs
                        (table_name, row_id, dml_type, old_values, new_values, tenant_id, actor_id, session_info, db_user, created_at)
                    VALUES
                        ('{$tableName}', CAST(OLD.`{$pk}` AS CHAR), 'DELETE',
                         JSON_OBJECT({$jsonOld}), NULL,
                         {$tenantOld}, @sam_actor_id, @sam_session_info, CURRENT_USER(), NOW());
                END IF;
            END
        ");
    }
};
