-- Spending paid out of a cash advance points back at it. This is how an advance is
-- accounted for: the team's purchases and expenses are the settlement, so the advance
-- itself never carries a copy of what was bought.
-- RESTRICT rather than SET NULL: dropping an advance that still has spending on it
-- would leave that spending looking like it came straight out of the drawer.
ALTER TABLE purchase_orders
    ADD COLUMN IF NOT EXISTS cash_advance_id UUID;

ALTER TABLE purchase_orders
    ADD CONSTRAINT fk_purchase_orders_cash_advance
        FOREIGN KEY (cash_advance_id) REFERENCES cash_advances(id) ON DELETE RESTRICT;

CREATE INDEX IF NOT EXISTS idx_purchase_orders_cash_advance_id
    ON purchase_orders(cash_advance_id);

ALTER TABLE expenses
    ADD COLUMN IF NOT EXISTS cash_advance_id UUID;

ALTER TABLE expenses
    ADD CONSTRAINT fk_expenses_cash_advance
        FOREIGN KEY (cash_advance_id) REFERENCES cash_advances(id) ON DELETE RESTRICT;

CREATE INDEX IF NOT EXISTS idx_expenses_cash_advance_id
    ON expenses(cash_advance_id);