<?php

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

return new class extends Migration
{
    public function up(): void
    {
        DB::statement("CREATE TRIGGER delete_payments_for_loan AFTER UPDATE ON sy_loans FOR EACH ROW
        BEGIN
            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
            
            IF NEW.is_active = false THEN
                BEGIN
                    DECLARE err_msg TEXT;

                    GET DIAGNOSTICS CONDITION 1
                        err_msg = MESSAGE_TEXT;

                    INSERT INTO log_triggers(triggername, message, fk_usercreator) VALUES ('delete_payments_for_loan', err_msg, NEW.fk_userupdater);
                END;

                BEGIN
                    UPDATE sy_payments SET is_active = false, fk_userupdater = NEW.fk_userupdater WHERE fk_loan = OLD.id;
                    UPDATE sy_loan_details SET is_active = false, fk_userupdater = NEW.fk_userupdater WHERE fk_loan = OLD.id;
                    UPDATE sy_endorsements SET is_active = false, fk_userupdater = NEW.fk_userupdater WHERE fk_loan = OLD.id;
                    UPDATE sy_guarantees SET is_active = false, fk_userupdater = NEW.fk_userupdater WHERE fk_loan = OLD.id;
                END;
            END IF;
        END;");
    }

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