USE db_abos_v0.1;

-- =============================================================================================
-- web_store_checkout_procedures.sql — the money-movement half of the storefront checkout flow
-- (companion to web_store_module.sql, applied right after it in apply-migrations.sh). Mirrors
-- the exact debit/credit/ledger-entry/balance-update transactional shape already used by
-- sp_pay_invoice/sp_transfer_wallet/sp_pay_commission (see fix_finance_wallet_procedures.sql and
-- fix_finance_hrm_platform_procedures.sql) — same fact_transactions + fact_ledger_entries +
-- dim_wallets.balance triple-write inside one START TRANSACTION/COMMIT, with the same
-- EXIT HANDLER FOR SQLEXCEPTION rollback guard.
--
-- Unlike sp_pay_invoice (a wallet-to-wallet transfer between two ABOS-internal wallets), a paid
-- storefront order's money comes from an external payment processor (Stripe), not from another
-- wallet this app tracks — so there is no "from" wallet to debit. Only one ledger effect exists:
-- crediting the tenant's corporate wallet for the order total. from_wallet_id is left NULL on the
-- fact_transactions row for that reason (the column is nullable — see fact_transactions schema).
--
-- Idempotency: StripeWebhookController/StripeWebhookService already check
-- fact_web_orders.payment_status = 'Unpaid' before calling this procedure (guards against a
-- redelivered Stripe webhook double-processing the same order). This procedure re-checks the same
-- condition itself, under a row lock (SELECT ... FOR UPDATE), so two concurrent webhook deliveries
-- for the same order can never both pass the check and both credit the wallet — the second call
-- becomes a no-op once the first has committed the status flip.
--
-- Safe to re-run: DROP PROCEDURE IF EXISTS precedes the CREATE.
-- =============================================================================================

DROP PROCEDURE IF EXISTS `sp_pay_web_order`;

DELIMITER $$

CREATE PROCEDURE `sp_pay_web_order`(
    IN p_tenant_id INT,
    IN p_order_id BIGINT,
    IN p_corp_wallet_id INT,
    IN p_updated_by INT
)
BEGIN
    DECLARE v_total_amount DECIMAL(15,2);
    DECLARE v_payment_status VARCHAR(20);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- FOR UPDATE locks this order row for the duration of the transaction, so a second,
    -- concurrently-processed redelivery of the same Stripe webhook blocks here until the first
    -- call's COMMIT, then sees payment_status already 'Paid' and does nothing further.
    SELECT total_amount, payment_status INTO v_total_amount, v_payment_status
    FROM fact_web_orders
    WHERE id = p_order_id AND tenant_id = p_tenant_id
    FOR UPDATE;

    IF v_payment_status = 'Unpaid' THEN
        UPDATE fact_web_orders
        SET payment_status = 'Paid', updated_by = p_updated_by
        WHERE id = p_order_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, NULL, p_corp_wallet_id, v_total_amount,
            CONCAT('Payment for Web Order #', p_order_id), 'WebOrder', p_order_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_corp_wallet_id, 'Credit', v_total_amount, 'WebOrder', p_order_id, p_updated_by);

        UPDATE dim_wallets
        SET balance = balance + v_total_amount
        WHERE id = p_corp_wallet_id AND tenant_id = p_tenant_id;
    END IF;

    COMMIT;
END$$

DELIMITER ;