-- apps/web/src/server/db/migrations/0072_credit_ledger_hold_cols.sql
-- Apply via Neon MCP run_sql against project rough-brook-49654702.

ALTER TABLE credit_ledger
  ADD COLUMN IF NOT EXISTS mature_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS swept_at  TIMESTAMPTZ;

-- Backfill: all existing rows are already mature (legacy = no hold).
UPDATE credit_ledger SET mature_at = created_at WHERE mature_at IS NULL;

-- Enforce NOT NULL after backfill.
ALTER TABLE credit_ledger ALTER COLUMN mature_at SET NOT NULL;

-- Index for fast mature_at lookups (partial predicates with NOW() require IMMUTABLE;
-- use plain index instead which still serves the sweep cron efficiently).
CREATE INDEX IF NOT EXISTS credit_ledger_pending_mature_idx
  ON credit_ledger(mature_at);

-- Index for unswept rows (swept_at IS NULL partial index is valid — no volatile functions).
CREATE INDEX IF NOT EXISTS credit_ledger_unswept_idx
  ON credit_ledger(mature_at)
  WHERE swept_at IS NULL;
