<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        try {
            DB::statement('ALTER TABLE warehouse_stocks ADD COLUMN conversion_input VARCHAR(255) NULL');
        } catch (\Exception $e) {
            // Column may already exist
        }

        // Drop existing triggers if they exist
        DB::unprepared('DROP TRIGGER IF EXISTS trg_purchase_details_after_insert');
        DB::unprepared('DROP TRIGGER IF EXISTS trg_purchase_details_after_update');
        DB::unprepared('DROP TRIGGER IF EXISTS trg_purchase_details_after_delete');

        // Create trigger for INSERT
        DB::unprepared('
            CREATE TRIGGER trg_purchase_details_after_insert
            AFTER INSERT ON purchase_details
            FOR EACH ROW
            BEGIN
                DECLARE v_warehouse_id BIGINT;
                DECLARE v_qty DECIMAL(20,2);

                SET v_qty = CAST(NEW.quantity AS DECIMAL(20,2)) + IFNULL(NEW.gift_qty, 0);

                SELECT warehouse_id
                INTO v_warehouse_id
                FROM purchase_masters
                WHERE id = NEW.purchase_master_id;

                -- Insert new record if not exists, otherwise update existing
                INSERT INTO warehouse_stocks (
                    warehouse_id,
                    product_id,
                    attribute_id,
                    quantity,
                    unit_price,
                    tp_price,
                    mrp_price,
                    unit_price_total,
                    tp_price_total,
                    mrp_price_total,
                    conversion_input,
                    created_at,
                    updated_at
                ) VALUES (
                    v_warehouse_id,
                    NEW.product_id,
                    NEW.attribute_id,
                    v_qty,
                    CAST(NEW.unit_price AS DECIMAL(20,2)),
                    CAST(NEW.tp_price AS DECIMAL(20,2)),
                    CAST(NEW.mrp_price AS DECIMAL(20,2)),
                    v_qty * CAST(NEW.unit_price AS DECIMAL(20,2)),
                    v_qty * CAST(NEW.tp_price AS DECIMAL(20,2)),
                    v_qty * CAST(NEW.mrp_price AS DECIMAL(20,2)),
                    NEW.conversion_input,
                    NOW(),
                    NOW()
                )
                ON DUPLICATE KEY UPDATE
                    quantity = quantity + v_qty,
                    unit_price = CAST(NEW.unit_price AS DECIMAL(20,2)),
                    tp_price   = CAST(NEW.tp_price AS DECIMAL(20,2)),
                    mrp_price  = CAST(NEW.mrp_price AS DECIMAL(20,2)),
                    unit_price_total = (quantity + v_qty) * CAST(NEW.unit_price AS DECIMAL(20,2)),
                    tp_price_total   = (quantity + v_qty) * CAST(NEW.tp_price AS DECIMAL(20,2)),
                    mrp_price_total  = (quantity + v_qty) * CAST(NEW.mrp_price AS DECIMAL(20,2)),
                    conversion_input = NEW.conversion_input,
                    updated_at = NOW();
            END
        ');

        // Create trigger for UPDATE
        DB::unprepared('
            CREATE TRIGGER trg_purchase_details_after_update
            AFTER UPDATE ON purchase_details
            FOR EACH ROW
            BEGIN
                DECLARE v_warehouse_id BIGINT;
                DECLARE v_old_qty DECIMAL(20,2);
                DECLARE v_new_qty DECIMAL(20,2);

                SET v_old_qty = CAST(OLD.quantity AS DECIMAL(20,2)) + IFNULL(OLD.gift_qty, 0);
                SET v_new_qty = CAST(NEW.quantity AS DECIMAL(20,2)) + IFNULL(NEW.gift_qty, 0);

                SELECT warehouse_id
                INTO v_warehouse_id
                FROM purchase_masters
                WHERE id = NEW.purchase_master_id;

                -- rollback old qty
                UPDATE warehouse_stocks
                SET
                    quantity = quantity - v_old_qty,
                    unit_price_total = (quantity - v_old_qty) * unit_price,
                    tp_price_total   = (quantity - v_old_qty) * tp_price,
                    mrp_price_total  = (quantity - v_old_qty) * mrp_price
                WHERE warehouse_id = v_warehouse_id
                  AND product_id = OLD.product_id
                  AND attribute_id = OLD.attribute_id;

                -- add new qty (insert if not exists, otherwise update)
                INSERT INTO warehouse_stocks (
                    warehouse_id,
                    product_id,
                    attribute_id,
                    quantity,
                    unit_price,
                    tp_price,
                    mrp_price,
                    unit_price_total,
                    tp_price_total,
                    mrp_price_total,
                    conversion_input,
                    created_at,
                    updated_at
                ) VALUES (
                    v_warehouse_id,
                    NEW.product_id,
                    NEW.attribute_id,
                    v_new_qty,
                    CAST(NEW.unit_price AS DECIMAL(20,2)),
                    CAST(NEW.tp_price AS DECIMAL(20,2)),
                    CAST(NEW.mrp_price AS DECIMAL(20,2)),
                    v_new_qty * CAST(NEW.unit_price AS DECIMAL(20,2)),
                    v_new_qty * CAST(NEW.tp_price AS DECIMAL(20,2)),
                    v_new_qty * CAST(NEW.mrp_price AS DECIMAL(20,2)),
                    NEW.conversion_input,
                    NOW(),
                    NOW()
                )
                ON DUPLICATE KEY UPDATE
                    quantity = quantity + v_new_qty,
                    unit_price = CAST(NEW.unit_price AS DECIMAL(20,2)),
                    tp_price   = CAST(NEW.tp_price AS DECIMAL(20,2)),
                    mrp_price  = CAST(NEW.mrp_price AS DECIMAL(20,2)),
                    unit_price_total = (quantity + v_new_qty) * CAST(NEW.unit_price AS DECIMAL(20,2)),
                    tp_price_total   = (quantity + v_new_qty) * CAST(NEW.tp_price AS DECIMAL(20,2)),
                    mrp_price_total  = (quantity + v_new_qty) * CAST(NEW.mrp_price AS DECIMAL(20,2)),
                    conversion_input = NEW.conversion_input,
                    updated_at = NOW();
            END
        ');

        // Create trigger for DELETE
        DB::unprepared('
            CREATE TRIGGER trg_purchase_details_after_delete
            AFTER DELETE ON purchase_details
            FOR EACH ROW
            BEGIN
                DECLARE v_warehouse_id BIGINT;
                DECLARE v_qty DECIMAL(20,2);

                SET v_qty = CAST(OLD.quantity AS DECIMAL(20,2)) + IFNULL(OLD.gift_qty, 0);

                SELECT warehouse_id
                INTO v_warehouse_id
                FROM purchase_masters
                WHERE id = OLD.purchase_master_id;

                UPDATE warehouse_stocks
                SET
                    quantity = quantity - v_qty,
                    unit_price_total = (quantity - v_qty) * unit_price,
                    tp_price_total   = (quantity - v_qty) * tp_price,
                    mrp_price_total  = (quantity - v_qty) * mrp_price
                WHERE warehouse_id = v_warehouse_id
                  AND product_id = OLD.product_id
                  AND attribute_id = OLD.attribute_id;
            END
        ');

        // Add unique key constraint to warehouse_stocks if it doesn't exist
        try {
            DB::statement('ALTER TABLE warehouse_stocks ADD UNIQUE KEY uq_stock (warehouse_id, product_id, attribute_id)');
        } catch (\Exception $e) {
            // Unique key might already exist, ignore error
        }
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        DB::unprepared('DROP TRIGGER IF EXISTS trg_purchase_details_after_insert');
        DB::unprepared('DROP TRIGGER IF EXISTS trg_purchase_details_after_update');
        DB::unprepared('DROP TRIGGER IF EXISTS trg_purchase_details_after_delete');

        // Remove unique key constraint if it exists
        try {
            DB::statement('ALTER TABLE warehouse_stocks DROP INDEX uq_stock');
        } catch (\Exception $e) {
            // Index might not exist, ignore error
        }
    }
};
