-- 020_stargate_payments.sql
--
-- Adds tracking for Stargate (5-minute retrieval key) purchases on the payments table.
-- These payments are NOT linked via customers.payment_id (that FK points only to the
-- original postcard order payment) — instead they're linked via related_customer_id,
-- a non-exclusive reference back to the order they retrieve, so an order can have
-- multiple Stargate payments over time without conflicting with its original payment_id.

ALTER TABLE payments
  ADD COLUMN IF NOT EXISTS purchase_type TEXT NOT NULL DEFAULT 'order'
    CHECK (purchase_type IN ('order', 'purchase_key', 'purchase_recipient_key')),
  ADD COLUMN IF NOT EXISTS related_customer_id UUID REFERENCES customers(customer_id);

CREATE INDEX IF NOT EXISTS idx_payments_purchase_type ON payments(purchase_type);
CREATE INDEX IF NOT EXISTS idx_payments_related_customer ON payments(related_customer_id) WHERE related_customer_id IS NOT NULL;

COMMENT ON COLUMN payments.purchase_type IS 'order = original postcard purchase. purchase_key = sender Stargate retrieval. purchase_recipient_key = recipient Stargate retrieval.';
COMMENT ON COLUMN payments.related_customer_id IS 'For Stargate purchases only — the order (customers row) this retrieval relates to. NULL for original order payments (use customers.payment_id instead).';
