<?php

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

return new class extends Migration
{
    public function up(): void
    {
        // 기존 트리거 삭제
        DB::unprepared('DROP TRIGGER IF EXISTS trg_construction_site_photos_ai');
        DB::unprepared('DROP TRIGGER IF EXISTS trg_construction_site_photos_au');
        DB::unprepared('DROP TRIGGER IF EXISTS trg_construction_site_photos_ad');

        // INSERT 트리거 재생성 (사진 컬럼 제거)
        DB::unprepared("
            CREATE TRIGGER trg_construction_site_photos_ai AFTER INSERT ON construction_site_photos
            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, changed_columns, tenant_id, actor_id, session_info, db_user, created_at)
                    VALUES ('construction_site_photos', NEW.`id`, 'INSERT', NULL,
                        JSON_OBJECT('id', NEW.`id`, 'tenant_id', NEW.`tenant_id`, 'user_id', NEW.`user_id`, 'site_name', NEW.`site_name`, 'work_date', NEW.`work_date`, 'description', NEW.`description`),
                        NULL, NEW.`tenant_id`, @sam_actor_id, @sam_session_info, CURRENT_USER(), NOW());
                END IF;
            END
        ");

        // UPDATE 트리거 재생성
        DB::unprepared("
            CREATE TRIGGER trg_construction_site_photos_au AFTER UPDATE ON construction_site_photos
            FOR EACH ROW
            BEGIN
                IF @disable_audit_trigger IS NULL OR @disable_audit_trigger != 1 THEN
                    IF NOT (NEW.`id` <=> OLD.`id`) OR NOT (NEW.`tenant_id` <=> OLD.`tenant_id`) OR NOT (NEW.`user_id` <=> OLD.`user_id`) OR NOT (NEW.`site_name` <=> OLD.`site_name`) OR NOT (NEW.`work_date` <=> OLD.`work_date`) OR NOT (NEW.`description` <=> OLD.`description`) 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 ('construction_site_photos', NEW.`id`, 'UPDATE',
                            JSON_OBJECT('id', OLD.`id`, 'tenant_id', OLD.`tenant_id`, 'user_id', OLD.`user_id`, 'site_name', OLD.`site_name`, 'work_date', OLD.`work_date`, 'description', OLD.`description`),
                            JSON_OBJECT('id', NEW.`id`, 'tenant_id', NEW.`tenant_id`, 'user_id', NEW.`user_id`, 'site_name', NEW.`site_name`, 'work_date', NEW.`work_date`, 'description', NEW.`description`),
                            JSON_ARRAY(
                                IF(NOT (NEW.`id` <=> OLD.`id`), 'id', NULL),
                                IF(NOT (NEW.`tenant_id` <=> OLD.`tenant_id`), 'tenant_id', NULL),
                                IF(NOT (NEW.`user_id` <=> OLD.`user_id`), 'user_id', NULL),
                                IF(NOT (NEW.`site_name` <=> OLD.`site_name`), 'site_name', NULL),
                                IF(NOT (NEW.`work_date` <=> OLD.`work_date`), 'work_date', NULL),
                                IF(NOT (NEW.`description` <=> OLD.`description`), 'description', NULL)
                            ),
                            NEW.`tenant_id`, @sam_actor_id, @sam_session_info, CURRENT_USER(), NOW());
                    END IF;
                END IF;
            END
        ");

        // DELETE 트리거 재생성
        DB::unprepared("
            CREATE TRIGGER trg_construction_site_photos_ad AFTER DELETE ON construction_site_photos
            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, changed_columns, tenant_id, actor_id, session_info, db_user, created_at)
                    VALUES ('construction_site_photos', OLD.`id`, 'DELETE',
                        JSON_OBJECT('id', OLD.`id`, 'tenant_id', OLD.`tenant_id`, 'user_id', OLD.`user_id`, 'site_name', OLD.`site_name`, 'work_date', OLD.`work_date`, 'description', OLD.`description`),
                        NULL, NULL, OLD.`tenant_id`, @sam_actor_id, @sam_session_info, CURRENT_USER(), NOW());
                END IF;
            END
        ");
    }

    public function down(): void
    {
        // 롤백 시 트리거 삭제 (원래 마이그레이션의 rollback이 컬럼을 복원하므로 그때 다시 생성해야 함)
        DB::unprepared('DROP TRIGGER IF EXISTS trg_construction_site_photos_ai');
        DB::unprepared('DROP TRIGGER IF EXISTS trg_construction_site_photos_au');
        DB::unprepared('DROP TRIGGER IF EXISTS trg_construction_site_photos_ad');
    }
};
