-- =============================================================================
-- Round 10 schema changes — adultwork2
-- =============================================================================
-- Run these against the production Supabase Postgres ONE STATEMENT AT A TIME.
-- CREATE INDEX CONCURRENTLY cannot run inside a transaction; either paste each
-- statement individually into the Supabase SQL editor, or use psql with
-- `\set AUTOCOMMIT on`.
--
-- After running, follow up with `npx prisma db pull` and `npx prisma generate`
-- so the Prisma model stays in sync (the schema already has the new column
-- defined; pull is for safety).
-- =============================================================================

-- B.10 — Re-verification token issuance timestamp.
-- Existing token-expiry logic in /api/auth/verify-email checks
-- `now - user.created_at > 24h`. That works for a fresh registration, but
-- for an existing user changing their email the check is always true and
-- the link is rejected. New column is set whenever we mint a remember_token
-- for verification, and the route uses it for the expiry check.
ALTER TABLE users
  ADD COLUMN IF NOT EXISTS verification_token_sent_at TIMESTAMPTZ;

-- B.12 — panic_alerts already exists in production (id SERIAL, user_id,
-- latitude DOUBLE PRECISION, longitude DOUBLE PRECISION, ip_address VARCHAR,
-- created_at TIMESTAMP). The previous request handler issued
-- `CREATE TABLE IF NOT EXISTS` on every panic press; that was harmless when
-- the table existed but locked the catalog and invalidated plan caches under
-- load. Application code now skips the DDL — this migration just adds the
-- index that was missing for time-bounded incident lookups.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_panic_alerts_user_created
  ON panic_alerts (user_id, created_at DESC);

-- C.7 — perf indexes on the highest-frequency queries.
--
-- Notifications: every logged-in user polls the unread count every 30 s for
-- the nav-badge. A partial index on `is_read = false` keeps the index small
-- (most rows are read) and converts the seqscan to an index-only scan.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_notifications_user_unread
  ON notifications (user_id, is_read) WHERE is_read = false;

-- push_subscriptions: looked up on every push fan-out.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_push_subscriptions_user_id
  ON push_subscriptions (user_id);

-- recently_viewed: AvailabilityHeatmap aggregates recent views per profile
-- over a 90-day window. The column is `viewed_at` (not created_at).
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_recently_viewed_user_viewed
  ON recently_viewed (viewed_user_id, viewed_at DESC);