-- 20260814030000 dropped invoices_payment_id_fkey to allow AI_CREDIT_PURCHASE
-- invoices (payment_id -> ai_credit_orders(id)) alongside the pre-existing
-- TRACECOIN_PURCHASE invoices (payment_id -> payments(id)), but left no
-- replacement integrity check at all -- any invoice_type could reference a
-- nonexistent payment_id. Postgres has no native polymorphic FK, so enforce
-- this with a trigger that picks the right table based on invoice_type.

BEGIN;

CREATE OR REPLACE FUNCTION check_invoices_payment_id() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.invoice_type = 'TRACECOIN_PURCHASE' THEN
        IF NOT EXISTS (SELECT 1 FROM payments WHERE id = NEW.payment_id) THEN
            RAISE EXCEPTION 'invoices.payment_id % does not reference an existing payments row (invoice_type=TRACECOIN_PURCHASE)', NEW.payment_id;
        END IF;
    ELSIF NEW.invoice_type = 'AI_CREDIT_PURCHASE' THEN
        IF NOT EXISTS (SELECT 1 FROM ai_credit_orders WHERE id = NEW.payment_id) THEN
            RAISE EXCEPTION 'invoices.payment_id % does not reference an existing ai_credit_orders row (invoice_type=AI_CREDIT_PURCHASE)', NEW.payment_id;
        END IF;
    ELSE
        RAISE EXCEPTION 'invoices.invoice_type % is not a recognized value (expected TRACECOIN_PURCHASE or AI_CREDIT_PURCHASE)', NEW.invoice_type;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER invoices_payment_id_check
    BEFORE INSERT OR UPDATE OF payment_id, invoice_type ON invoices
    FOR EACH ROW
    EXECUTE FUNCTION check_invoices_payment_id();

COMMIT;
