<?php

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

return new class extends Migration
{
    public function up(): void
    {
        DB::statement('DROP VIEW IF EXISTS v_unified_audit');

        // connection charset이 latin1일 수 있으므로 COLLATE를 명시적으로 지정
        $collate = 'utf8mb4_unicode_ci';

        DB::statement("
            CREATE VIEW v_unified_audit AS

            -- Layer 1: Laravel Auditable (앱 레벨 감사)
            SELECT
                CONCAT('app_', id) COLLATE {$collate} AS uid,
                'APP' COLLATE {$collate} AS source,
                tenant_id,
                target_type COLLATE {$collate} AS table_name,
                CAST(target_id AS CHAR(64)) COLLATE {$collate} AS row_id,
                UPPER(action) COLLATE {$collate} AS action,
                `before` AS old_values,
                `after` AS new_values,
                CAST(NULL AS JSON) AS changed_columns,
                actor_id,
                ip COLLATE {$collate} AS ip_address,
                ua COLLATE {$collate} AS user_agent,
                CAST(NULL AS CHAR(100)) COLLATE {$collate} AS db_user,
                created_at
            FROM audit_logs

            UNION ALL

            -- Layer 2: MySQL Trigger Audit (DB 레벨 감사)
            SELECT
                CONCAT('trg_', id) COLLATE {$collate} AS uid,
                'TRIGGER' COLLATE {$collate} AS source,
                tenant_id,
                table_name COLLATE {$collate},
                row_id COLLATE {$collate},
                CAST(dml_type AS CHAR(10)) COLLATE {$collate} AS action,
                old_values,
                new_values,
                changed_columns,
                actor_id,
                CAST(JSON_UNQUOTE(JSON_EXTRACT(session_info, '$.ip')) AS CHAR(45)) COLLATE {$collate} AS ip_address,
                CAST(JSON_UNQUOTE(JSON_EXTRACT(session_info, '$.ua')) AS CHAR(255)) COLLATE {$collate} AS user_agent,
                db_user COLLATE {$collate},
                created_at
            FROM trigger_audit_logs
        ");
    }

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