<?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
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // NOTE: Ensure this unique index exists once in a separate schema migration:
        // Schema::table('collection_summaries', function (Blueprint $table) {
        //     $table->unique('payment_identity', 'uq_payment_identity');
        // });

        // ===================== AFTER INSERT =====================
        DB::unprepared(<<<'SQL'
        DROP TRIGGER IF EXISTS trg_after_insert_collection;
        CREATE TRIGGER trg_after_insert_collection
        AFTER INSERT ON customer_payment_collections
        FOR EACH ROW
        BEGIN
            DECLARE v_location_id  BIGINT;
            DECLARE v_type_lc      VARCHAR(64);
            DECLARE v_raw          VARCHAR(191);
            DECLARE v_identity     VARCHAR(191);
            DECLARE v_amt          DECIMAL(18,2);

            SET v_type_lc = LOWER(TRIM(NEW.payment_type) COLLATE utf8mb4_0900_ai_ci);
            SET v_raw     = NULLIF(TRIM(NEW.payment_id)   COLLATE utf8mb4_0900_ai_ci, '');
            SET v_amt     = CAST(NEW.payment AS DECIMAL(18,2));

            SELECT w.id INTO v_location_id
              FROM warehouses w
             WHERE w.id = NEW.warehouse_id
             LIMIT 1;
            IF v_location_id IS NULL THEN SET v_location_id = 0; END IF;

            IF v_type_lc = 'cash' THEN
                SET v_identity = 'CASH';
            ELSEIF v_type_lc = 'bank' THEN
                SET v_identity = IF(v_raw IS NULL, 'BANK:DEFAULT',
                                IF((v_raw COLLATE utf8mb4_0900_ai_ci) LIKE ('BANK:%' COLLATE utf8mb4_0900_ai_ci),
                                   v_raw, CONCAT('BANK:', v_raw)));
            ELSEIF v_type_lc IN ('mobile bank','mobile_bank','mobile-bank') THEN
                SET v_identity = IF(v_raw IS NULL, 'MOBILE:DEFAULT',
                                IF((v_raw COLLATE utf8mb4_0900_ai_ci) LIKE ('MOBILE:%' COLLATE utf8mb4_0900_ai_ci),
                                   v_raw, CONCAT('MOBILE:', v_raw)));
            ELSE
                SET v_identity = COALESCE(v_raw, 'UNKNOWN');
            END IF;

            INSERT INTO collection_summaries (
                payment_identity, payment_id, location_id, payment_method, balance, created_at, updated_at
            ) VALUES (
                v_identity, COALESCE(NEW.payment_id, 'CASH'), v_location_id, NEW.payment_type, v_amt, NOW(), NOW()
            )
            ON DUPLICATE KEY UPDATE
                balance        = balance + VALUES(balance),
                payment_method = VALUES(payment_method),
                payment_id     = COALESCE(VALUES(payment_id), 'CASH'),
                updated_at     = NOW();
        END
        SQL);

        // ===================== AFTER UPDATE =====================
        DB::unprepared(<<<'SQL'
        DROP TRIGGER IF EXISTS trg_after_update_collection;
        CREATE TRIGGER trg_after_update_collection
        AFTER UPDATE ON customer_payment_collections
        FOR EACH ROW
        BEGIN
            DECLARE v_loc_old, v_loc_new BIGINT;
            DECLARE v_type_old_lc, v_type_new_lc VARCHAR(64);
            DECLARE v_raw_old, v_raw_new VARCHAR(191);
            DECLARE v_id_old, v_id_new   VARCHAR(191);
            DECLARE v_amt_old, v_amt_new DECIMAL(18,2);
            DECLARE v_delta              DECIMAL(18,2);

            -- OLD
            SET v_type_old_lc = LOWER(TRIM(OLD.payment_type) COLLATE utf8mb4_0900_ai_ci);
            SET v_raw_old     = NULLIF(TRIM(OLD.payment_id)   COLLATE utf8mb4_0900_ai_ci, '');
            SET v_amt_old     = CAST(OLD.payment AS DECIMAL(18,2));
            SELECT w.id INTO v_loc_old FROM warehouses w WHERE w.id = OLD.warehouse_id LIMIT 1;
            IF v_loc_old IS NULL THEN SET v_loc_old = 0; END IF;

            IF v_type_old_lc = 'cash' THEN
                SET v_id_old = 'CASH';
            ELSEIF v_type_old_lc = 'bank' THEN
                SET v_id_old = IF(v_raw_old IS NULL, 'BANK:DEFAULT',
                              IF((v_raw_old COLLATE utf8mb4_0900_ai_ci) LIKE ('BANK:%' COLLATE utf8mb4_0900_ai_ci),
                                 v_raw_old, CONCAT('BANK:', v_raw_old)));
            ELSEIF v_type_old_lc IN ('mobile bank','mobile_bank','mobile-bank') THEN
                SET v_id_old = IF(v_raw_old IS NULL, 'MOBILE:DEFAULT',
                              IF((v_raw_old COLLATE utf8mb4_0900_ai_ci) LIKE ('MOBILE:%' COLLATE utf8mb4_0900_ai_ci),
                                 v_raw_old, CONCAT('MOBILE:', v_raw_old)));
            ELSE
                SET v_id_old = COALESCE(v_raw_old, 'UNKNOWN');
            END IF;

            -- NEW
            SET v_type_new_lc = LOWER(TRIM(NEW.payment_type) COLLATE utf8mb4_0900_ai_ci);
            SET v_raw_new     = NULLIF(TRIM(NEW.payment_id)   COLLATE utf8mb4_0900_ai_ci, '');
            SET v_amt_new     = CAST(NEW.payment AS DECIMAL(18,2));
            SELECT w.id INTO v_loc_new FROM warehouses w WHERE w.id = NEW.warehouse_id LIMIT 1;
            IF v_loc_new IS NULL THEN SET v_loc_new = 0; END IF;

            IF v_type_new_lc = 'cash' THEN
                SET v_id_new = 'CASH';
            ELSEIF v_type_new_lc = 'bank' THEN
                SET v_id_new = IF(v_raw_new IS NULL, 'BANK:DEFAULT',
                              IF((v_raw_new COLLATE utf8mb4_0900_ai_ci) LIKE ('BANK:%' COLLATE utf8mb4_0900_ai_ci),
                                 v_raw_new, CONCAT('BANK:', v_raw_new)));
            ELSEIF v_type_new_lc IN ('mobile bank','mobile_bank','mobile-bank') THEN
                SET v_id_new = IF(v_raw_new IS NULL, 'MOBILE:DEFAULT',
                              IF((v_raw_new COLLATE utf8mb4_0900_ai_ci) LIKE ('MOBILE:%' COLLATE utf8mb4_0900_ai_ci),
                                 v_raw_new, CONCAT('MOBILE:', v_raw_new)));
            ELSE
                SET v_id_new = COALESCE(v_raw_new, 'UNKNOWN');
            END IF;

            IF v_id_old = v_id_new THEN
                SET v_delta = v_amt_new - v_amt_old;
                UPDATE collection_summaries
                   SET balance        = balance + v_delta,
                       payment_method = NEW.payment_type,
                       payment_id     = COALESCE(NEW.payment_id, 'CASH'),
                       updated_at     = NOW()
                 WHERE payment_identity = v_id_new COLLATE utf8mb4_0900_ai_ci;
            ELSE
                UPDATE collection_summaries
                   SET balance    = balance - v_amt_old,
                       updated_at = NOW()
                 WHERE payment_identity = v_id_old COLLATE utf8mb4_0900_ai_ci;

                INSERT INTO collection_summaries (
                    payment_identity, payment_id, location_id, payment_method, balance, created_at, updated_at
                ) VALUES (
                    v_id_new, COALESCE(NEW.payment_id, 'CASH'), v_loc_new, NEW.payment_type, v_amt_new, NOW(), NOW()
                )
                ON DUPLICATE KEY UPDATE
                    balance        = balance + VALUES(balance),
                    payment_method = VALUES(payment_method),
                    payment_id     = COALESCE(VALUES(payment_id), 'CASH'),
                    updated_at     = NOW();
            END IF;
        END
        SQL);

        // ===================== AFTER DELETE =====================
        DB::unprepared(<<<'SQL'
        DROP TRIGGER IF EXISTS trg_after_delete_collection;
        CREATE TRIGGER trg_after_delete_collection
        AFTER DELETE ON customer_payment_collections
        FOR EACH ROW
        BEGIN
            DECLARE v_location_id  BIGINT;
            DECLARE v_type_lc      VARCHAR(64);
            DECLARE v_raw          VARCHAR(191);
            DECLARE v_identity     VARCHAR(191);
            DECLARE v_amt          DECIMAL(18,2);

            SET v_type_lc = LOWER(TRIM(OLD.payment_type) COLLATE utf8mb4_0900_ai_ci);
            SET v_raw     = NULLIF(TRIM(OLD.payment_id)   COLLATE utf8mb4_0900_ai_ci, '');
            SET v_amt     = CAST(OLD.payment AS DECIMAL(18,2));

            SELECT w.id INTO v_location_id
              FROM warehouses w
             WHERE w.id = OLD.warehouse_id
             LIMIT 1;
            IF v_location_id IS NULL THEN SET v_location_id = 0; END IF;

            IF v_type_lc = 'cash' THEN
                SET v_identity = 'CASH';
            ELSEIF v_type_lc = 'bank' THEN
                SET v_identity = IF(v_raw IS NULL, 'BANK:DEFAULT',
                                IF((v_raw COLLATE utf8mb4_0900_ai_ci) LIKE ('BANK:%' COLLATE utf8mb4_0900_ai_ci),
                                   v_raw, CONCAT('BANK:', v_raw)));
            ELSEIF v_type_lc IN ('mobile bank','mobile_bank','mobile-bank') THEN
                SET v_identity = IF(v_raw IS NULL, 'MOBILE:DEFAULT',
                                IF((v_raw COLLATE utf8mb4_0900_ai_ci) LIKE ('MOBILE:%' COLLATE utf8mb4_0900_ai_ci),
                                   v_raw, CONCAT('MOBILE:', v_raw)));
            ELSE
                SET v_identity = COALESCE(v_raw, 'UNKNOWN');
            END IF;

            UPDATE collection_summaries
               SET balance    = balance - v_amt,
                   updated_at = NOW()
             WHERE payment_identity = v_identity COLLATE utf8mb4_0900_ai_ci;
        END
        SQL);
    }


    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        DB::unprepared('DROP TRIGGER IF EXISTS trg_after_insert_collection;');
        DB::unprepared('DROP TRIGGER IF EXISTS trg_after_update_collection;');
        DB::unprepared('DROP TRIGGER IF EXISTS trg_after_delete_collection;');
    }
};
