USE db_abos_v0.1;

-- =============================================================================================
-- fix_invoice_optional_client.sql — lets a fact_invoices row exist without a client/wallet,
-- so a paid web-store order can get a record-only CRM invoice.
--
-- Background: fact_invoices.client_id and .wallet_id are genuinely NOT NULL at the DB level
-- (unlike fact_opportunities.company_id, which was already made optional for company-less
-- deals — see fix_opportunity_company_less_deals.sql). The normal CRM invoice flow
-- (sp_create_invoice_with_ledger / sp_pay_invoice, still used unchanged by InvoiceService for
-- that flow) always has a real DimCompany client and a real DimWallet to bill against, so those
-- columns stayed required until now.
--
-- A web-store order is different: real money already moved client -> Stripe -> the tenant's
-- corporate wallet inside sp_pay_web_order (see web_store_checkout_procedures.sql), which
-- already wrote its own fact_transactions row + fact_ledger_entries Credit row + dim_wallets
-- balance update under reference_entity='WebOrder'. The invoice CrmDealFromWebService creates
-- for that order is a record-only artifact for the CRM/Invoices UI — it must NOT move any
-- ledger money a second time, and a storefront customer is a DimWebCustomer/guest, not
-- necessarily a DimCompany row, so client_id has nothing sensible to point at. Hence both
-- columns need to become nullable, and sp_record_paid_web_order_invoice (below) only ever
-- performs a single plain INSERT with no fact_transactions/fact_ledger_entries/dim_wallets
-- writes at all.
--
-- fact_invoices.opportunity_id is already nullable (added in an earlier migration) — no change
-- needed for that column.
--
-- Safe to re-run: MODIFY COLUMN ... NULL on an already-nullable column is a no-op on both
-- MySQL and MariaDB (unlike ADD COLUMN, there's no "already exists" error to guard against), and
-- DROP PROCEDURE IF EXISTS precedes the CREATE PROCEDURE below.
-- =============================================================================================

ALTER TABLE fact_invoices
    MODIFY COLUMN client_id INT NULL,
    MODIFY COLUMN wallet_id INT NULL;

-- ── sp_record_paid_web_order_invoice — record-only invoice insert for a paid web order ────────
-- Deliberately does NOT touch fact_transactions, fact_ledger_entries, or dim_wallets.balance:
-- the money for this order already moved inside sp_pay_web_order. This procedure only inserts
-- a fact_invoices row (status 'Paid' from creation) so the order shows up in the existing
-- Invoices UI/API — calling sp_create_invoice_with_ledger/sp_pay_invoice here would each
-- independently move ledger money again and double-count against the wallet.
DROP PROCEDURE IF EXISTS `sp_record_paid_web_order_invoice`;

DELIMITER $$

CREATE PROCEDURE `sp_record_paid_web_order_invoice`(
    IN p_tenant_id INT,
    IN p_business_id INT,
    IN p_web_order_id BIGINT,
    IN p_opportunity_id BIGINT,
    IN p_client_name VARCHAR(255),
    IN p_amount DECIMAL(15,2),
    IN p_tax_amount DECIMAL(15,2),
    IN p_currency VARCHAR(10),
    IN p_invoice_no VARCHAR(100),
    IN p_created_by INT,
    OUT p_invoice_id BIGINT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    INSERT INTO fact_invoices (
        tenant_id, business_id, client_id, wallet_id, client_name, status, amount, tax_amount,
        currency, invoice_no, opportunity_id, issue_date, due_date, created_by
    ) VALUES (
        p_tenant_id, p_business_id, NULL, NULL, p_client_name, 'Paid', p_amount, p_tax_amount,
        p_currency, p_invoice_no, p_opportunity_id, CURDATE(), CURDATE(), p_created_by
    );

    SET p_invoice_id = LAST_INSERT_ID();

    COMMIT;
END$$

DELIMITER ;