<?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 client_changes AFTER UPDATE ON sy_clients FOR EACH ROW
        BEGIN
            DECLARE err_msg TEXT DEFAULT '';
            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
            
            BEGIN
                GET DIAGNOSTICS CONDITION 1
                    err_msg = MESSAGE_TEXT;
                INSERT INTO log_triggers(triggername, message, fk_usercreator)  VALUES ('client_changes', err_msg, NEW.fk_usercreator);
            END;

            BEGIN
                DECLARE changetype VARCHAR(50) DEFAULT '';
                DECLARE oldvalue VARCHAR(255) DEFAULT '';
                DECLARE newvalue VARCHAR(255) DEFAULT '';

                IF NEW.firstname <> OLD.firstname OR NEW.lastname <> OLD.lastname OR NEW.secondlastname <> OLD.secondlastname THEN
                    SET changetype = 'CHANGE.NAME';
                    SET oldvalue = CONCAT(OLD.firstname, ' ', OLD.lastname, ' ', OLD.secondlastname);
                    SET newvalue = CONCAT(NEW.firstname, ' ', NEW.lastname, ' ', NEW.secondlastname);

                    INSERT INTO log_clients(fk_client, type, old_value, new_value, fk_userupdater) VALUES (OLD.id, changetype, oldvalue, newvalue, NEW.fk_usercreator);
                END IF;

                IF NEW.curp <> OLD.curp THEN
                    SET changetype = 'CHANGE.CURP';
                    SET oldvalue = OLD.curp;
                    SET newvalue = NEW.curp;

                    INSERT INTO log_clients(fk_client, type, old_value, new_value, fk_userupdater) VALUES (OLD.id, changetype, oldvalue, newvalue, NEW.fk_usercreator);
                END IF;

                IF NEW.address <> OLD.address THEN
                    SET changetype = 'CHANGE.ADDRESS';
                    SET oldvalue = OLD.address;
                    SET newvalue = NEW.address;

                    INSERT INTO log_clients(fk_client, type, old_value, new_value, fk_userupdater) VALUES (OLD.id, changetype, oldvalue, newvalue, NEW.fk_usercreator);
                END IF;

                IF NEW.phonenumber <> OLD.phonenumber THEN
                    SET changetype = 'CHANGE.PHONENUMBER';
                    SET oldvalue = OLD.phonenumber;
                    SET newvalue = NEW.phonenumber;

                    INSERT INTO log_clients(fk_client, type, old_value, new_value, fk_userupdater) VALUES (OLD.id, changetype, oldvalue, newvalue, NEW.fk_usercreator);
                END IF;
            END;
        END;
    ");
    }

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