-- Migration 002: Payment reliability improvements

-- Track email send status on customers table
ALTER TABLE customers ADD COLUMN IF NOT EXISTS email_retry      BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS email_sent_at    TIMESTAMPTZ;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS email_attempts   INTEGER NOT NULL DEFAULT 0;

-- Track webhook processing status on payments table
ALTER TABLE payments ADD COLUMN IF NOT EXISTS webhook_processed   BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE payments ADD COLUMN IF NOT EXISTS webhook_processed_at TIMESTAMPTZ;
ALTER TABLE payments ADD COLUMN IF NOT EXISTS refund_id           TEXT;
ALTER TABLE payments ADD COLUMN IF NOT EXISTS refunded_at         TIMESTAMPTZ;

-- Extend pending_orders expiry tracking
ALTER TABLE pending_orders ADD COLUMN IF NOT EXISTS expires_at TIMESTAMPTZ 
  NOT NULL DEFAULT (NOW() + INTERVAL '7 days');

-- Index for fast retry lookups
CREATE INDEX IF NOT EXISTS idx_customers_email_retry ON customers(email_retry) WHERE email_retry = TRUE;
CREATE INDEX IF NOT EXISTS idx_payments_unprocessed  ON payments(status, webhook_processed) WHERE status = 'paid' AND webhook_processed = FALSE;
CREATE INDEX IF NOT EXISTS idx_pending_orders_expiry ON pending_orders(expires_at);

