USE db_abos_v0.1;

-- Fixes two Finance stored-procedure contract bugs found in a Finance stored-procedure audit
-- (companion to fix_finance_hrm_platform_procedures.sql, applied right after it in
-- apply-migrations.sh). Safe to re-run — every CREATE PROCEDURE is preceded by
-- DROP PROCEDURE IF EXISTS.
--
-- Pair this with the matching Java changes: FinanceProcedureRepository.transferWallet() and
-- CrmProcedureRepository (new voidInvoiceCall) must declare the exact same parameter list, in the
-- same order, as each procedure below, or SimpleJdbcCall will bind values to the wrong positions.

-- =============================================================================================
-- 1. sp_transfer_wallet — Java's FinanceProcedureRepository declared 4 IN params (no tenant) +
--    1 OUT (p_transaction_id), but the live procedure took 5 IN params starting with
--    p_tenant_id and had NO OUT parameter at all (it computed v_transaction_id internally via
--    LAST_INSERT_ID() but never surfaced it). Every call from Java was therefore already broken
--    (SimpleJdbcCall param-count/order mismatch). Fixed to add the missing p_tenant_id IN param
--    (kept first, matching the convention every other procedure in this codebase uses) and to
--    add the OUT parameter the Java side already expects.
-- =============================================================================================
DROP PROCEDURE IF EXISTS `sp_transfer_wallet`;

DELIMITER $$

CREATE PROCEDURE `sp_transfer_wallet`(
    IN p_tenant_id INT,
    IN p_from_wallet_id INT,
    IN p_to_wallet_id INT,
    IN p_amount DECIMAL(15,2),
    IN p_created_by INT,
    OUT p_transaction_id BIGINT
)
BEGIN
    DECLARE v_transaction_id BIGINT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;
    INSERT INTO fact_transactions (tenant_id, from_wallet_id, to_wallet_id, amount, description, transaction_type, status, created_by)
    VALUES (p_tenant_id, p_from_wallet_id, p_to_wallet_id, p_amount,
            CONCAT('Transfer from wallet #', p_from_wallet_id, ' to wallet #', p_to_wallet_id),
            'Transfer', 'Completed', p_created_by);
    SET v_transaction_id = LAST_INSERT_ID();
    INSERT INTO fact_ledger_entries (tenant_id, wallet_id, entry_type, amount, reference_entity, reference_id, created_by)
    VALUES (p_tenant_id, p_from_wallet_id, 'Debit', p_amount, 'Transfer', v_transaction_id, p_created_by);
    INSERT INTO fact_ledger_entries (tenant_id, wallet_id, entry_type, amount, reference_entity, reference_id, created_by)
    VALUES (p_tenant_id, p_to_wallet_id, 'Credit', p_amount, 'Transfer', v_transaction_id, p_created_by);
    UPDATE dim_wallets SET balance = balance - p_amount WHERE id = p_from_wallet_id;
    UPDATE dim_wallets SET balance = balance + p_amount WHERE id = p_to_wallet_id;
    SET p_transaction_id = v_transaction_id;
    COMMIT;
END$$

DELIMITER ;

-- =============================================================================================
-- 2. sp_void_invoice — on a Paid invoice, the old body debited `v_wallet_id` (fact_invoices.
--    wallet_id, i.e. the CLIENT's wallet per InvoiceService.createInvoice) a second time, with
--    an inline comment mislabeling it "the affected corporate wallet". sp_pay_invoice's effect at
--    payment time is: debit the client wallet, credit the corp wallet. Voiding a paid invoice must
--    be the exact mirror: credit the client wallet back, debit the corp wallet back — not debit
--    the client wallet again. Fixed to accept a new p_corp_wallet_id IN param and, on a Paid
--    invoice, insert a Credit ledger entry + balance increase for the client wallet and a Debit
--    ledger entry + balance decrease for the corp wallet.
-- =============================================================================================
-- =============================================================================================
-- 3. sp_pay_commission — same missing-`description` bug as sp_transfer_wallet above
--    (fact_transactions.description is NOT NULL with no default; this INSERT never supplied
--    one). Discovered by directly testing sp_transfer_wallet after this file's first pass and
--    checking sibling procedures with the same INSERT shape. Everything else in this procedure
--    was already correct (both ledger entries + both wallet balance updates present).
-- =============================================================================================
DROP PROCEDURE IF EXISTS `sp_pay_commission`;

DELIMITER $$

CREATE PROCEDURE `sp_pay_commission`(IN p_tenant_id INT, IN p_commission_id BIGINT, IN p_corp_wallet_id INT, IN p_agent_wallet_id INT, IN p_performed_by INT)
BEGIN
    DECLARE v_amount DECIMAL(15,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;

    SELECT total INTO v_amount FROM fact_commissions WHERE id = p_commission_id AND tenant_id = p_tenant_id;

    UPDATE fact_commissions SET status = 'Paid', paid_date = CURRENT_DATE, updated_by = p_performed_by WHERE id = p_commission_id AND tenant_id = p_tenant_id;

    INSERT INTO fact_transactions (tenant_id, from_wallet_id, to_wallet_id, amount, description, reference_entity, reference_id, transaction_type, status, created_by)
    VALUES (p_tenant_id, p_corp_wallet_id, p_agent_wallet_id, v_amount,
            CONCAT('Commission payout #', p_commission_id), 'Commission', p_commission_id, 'Transfer', 'Completed', p_performed_by);

    INSERT INTO fact_ledger_entries (tenant_id, wallet_id, entry_type, amount, reference_entity, reference_id, created_by)
    VALUES (p_tenant_id, p_corp_wallet_id, 'Debit', v_amount, 'Commission', p_commission_id, p_performed_by);

    INSERT INTO fact_ledger_entries (tenant_id, wallet_id, entry_type, amount, reference_entity, reference_id, created_by)
    VALUES (p_tenant_id, p_agent_wallet_id, 'Credit', v_amount, 'Commission', p_commission_id, p_performed_by);

    UPDATE dim_wallets SET balance = balance - v_amount WHERE id = p_corp_wallet_id;
    UPDATE dim_wallets SET balance = balance + v_amount WHERE id = p_agent_wallet_id;
    COMMIT;
END$$

DELIMITER ;

DROP PROCEDURE IF EXISTS `sp_void_invoice`;

DELIMITER $$

CREATE PROCEDURE `sp_void_invoice`(
    IN p_tenant_id INT,
    IN p_invoice_id BIGINT,
    IN p_corp_wallet_id INT,
    IN p_voided_by INT
)
BEGIN
    DECLARE v_status VARCHAR(50);
    DECLARE v_amount DECIMAL(15,2);
    DECLARE v_wallet_id INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;

    SELECT status, (amount + tax_amount), wallet_id INTO v_status, v_amount, v_wallet_id
    FROM fact_invoices WHERE id = p_invoice_id AND tenant_id = p_tenant_id;

    UPDATE fact_invoices SET status = 'Cancelled', updated_by = p_voided_by WHERE id = p_invoice_id AND tenant_id = p_tenant_id;

    -- If Paid previously, we must safely reverse the ledger impact: this is the mirror image of
    -- sp_pay_invoice (which debits the client wallet and credits the corp wallet), so voiding
    -- credits the client wallet back and debits the corp wallet back.
    IF v_status = 'Paid' THEN
        INSERT INTO fact_ledger_entries (tenant_id, wallet_id, entry_type, amount, reference_entity, reference_id, created_by)
        VALUES (p_tenant_id, v_wallet_id, 'Credit', v_amount, 'Void_Invoice', p_invoice_id, p_voided_by);

        INSERT INTO fact_ledger_entries (tenant_id, wallet_id, entry_type, amount, reference_entity, reference_id, created_by)
        VALUES (p_tenant_id, p_corp_wallet_id, 'Debit', v_amount, 'Void_Invoice', p_invoice_id, p_voided_by);

        -- Give the client back the money it paid, and reverse the corresponding credit on the
        -- corporate wallet.
        UPDATE dim_wallets SET balance = balance + v_amount WHERE id = v_wallet_id AND tenant_id = p_tenant_id;
        UPDATE dim_wallets SET balance = balance - v_amount WHERE id = p_corp_wallet_id AND tenant_id = p_tenant_id;
    END IF;
    COMMIT;
END$$

DELIMITER ;