USE db_abos_v0.1;

-- =============================================================================================
-- fix_pay_invoice_ledger_balance.sql — sp_pay_invoice (last defined in
-- fix_finance_hrm_platform_procedures.sql) records a fact_transactions row and a single Debit
-- fact_ledger_entries row against the client wallet, but never:
--   1. inserts the matching Credit ledger entry for the corporate wallet (double-entry requires
--      one Debit + one Credit per movement — sp_transfer_wallet, sp_pay_commission, and
--      sp_void_invoice all do this pair; sp_pay_invoice was missing the Credit half), and
--   2. updates dim_wallets.balance at all, for either wallet.
--
-- Net effect of the bug: marking an invoice Paid never actually moves any money — the client
-- wallet's and corp wallet's balances are untouched. Meanwhile sp_void_invoice (fixed in
-- fix_finance_wallet_procedures.sql) DOES move dim_wallets.balance when reversing a Paid invoice
-- (credits the client wallet back, debits the corp wallet back) — since payment never applied
-- those balance changes in the first place, voiding a paid invoice corrupts both wallets'
-- balances instead of restoring them to their pre-payment state.
--
-- Fixed to mirror sp_void_invoice's reversal exactly: debit the client wallet's balance, credit
-- the corporate wallet's balance, and insert both ledger legs. Also tenant-scopes the initial
-- SELECT (every sibling procedure filters by tenant_id there; this one didn't).
--
-- Safe to re-run: DROP PROCEDURE IF EXISTS precedes the CREATE PROCEDURE.
-- =============================================================================================

DROP PROCEDURE IF EXISTS sp_pay_invoice;

DELIMITER $$

CREATE PROCEDURE sp_pay_invoice(
    IN p_tenant_id INT,
    IN p_invoice_id BIGINT,
    IN p_client_wallet_id INT,
    IN p_corp_wallet_id INT,
    IN p_updated_by INT
)
BEGIN
    DECLARE v_total_amount DECIMAL(15,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SELECT (amount + tax_amount) INTO v_total_amount
    FROM fact_invoices WHERE id = p_invoice_id AND tenant_id = p_tenant_id;

    UPDATE fact_invoices SET status = 'Paid', updated_by = p_updated_by
    WHERE id = p_invoice_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_client_wallet_id, p_corp_wallet_id, v_total_amount,
        CONCAT('Payment for Invoice #', p_invoice_id), 'Invoice', p_invoice_id, 'Income',
        'Completed', p_updated_by
    );

    INSERT INTO fact_ledger_entries (tenant_id, wallet_id, entry_type, amount, reference_entity, reference_id, created_by)
    VALUES (p_tenant_id, p_client_wallet_id, 'Debit', v_total_amount, 'Invoice', p_invoice_id, p_updated_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, 'Credit', v_total_amount, 'Invoice', p_invoice_id, p_updated_by);

    UPDATE dim_wallets SET balance = balance - v_total_amount WHERE id = p_client_wallet_id AND tenant_id = p_tenant_id;
    UPDATE dim_wallets SET balance = balance + v_total_amount WHERE id = p_corp_wallet_id AND tenant_id = p_tenant_id;

    COMMIT;
END$$

DELIMITER ;