-- 022_stripe_env_and_audit.sql
--
-- 1. Adds stripe_env to payments: 'sandbox' | 'live'
-- 2. Creates audit_log table for immutable change tracking
-- 3. Creates audit trigger function (basic column list version — superseded by 023)

ALTER TABLE payments
  ADD COLUMN IF NOT EXISTS stripe_env TEXT NOT NULL DEFAULT 'sandbox'
    CHECK (stripe_env IN ('sandbox', 'live'));

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'));

ALTER TABLE payments
  ADD COLUMN IF NOT EXISTS related_customer_id UUID REFERENCES customers(customer_id);

COMMENT ON COLUMN payments.stripe_env IS 'sandbox = Stripe test mode. live = real payment.';
COMMENT ON COLUMN payments.purchase_type IS 'order = original postcard. purchase_key = sender Stargate. purchase_recipient_key = recipient Stargate.';
COMMENT ON COLUMN payments.related_customer_id IS 'For Stargate purchases only — links to the order being retrieved.';

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;

CREATE TABLE IF NOT EXISTS audit_log (
  id            BIGSERIAL PRIMARY KEY,
  table_name    TEXT NOT NULL DEFAULT 'customers',
  record_id     TEXT NOT NULL,
  customer_id   UUID,
  changed_at    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  changed_by    TEXT NOT NULL DEFAULT 'manual_db',
  operation     TEXT NOT NULL,
  field         TEXT NOT NULL,
  old_value     TEXT,
  new_value     TEXT
);

CREATE INDEX IF NOT EXISTS idx_audit_log_customer_id ON audit_log(customer_id);
CREATE INDEX IF NOT EXISTS idx_audit_log_record_id   ON audit_log(record_id);
CREATE INDEX IF NOT EXISTS idx_audit_log_changed_at  ON audit_log(changed_at DESC);

COMMENT ON TABLE audit_log IS 'Immutable audit trail of all changes to the customers table. Written by Postgres trigger, never by application code.';
