-- =============================================================================
-- MIGRATION: Email verification — emailVerifiedAt + verification token on users
-- Convention: Prisma maps createdAt→createdOn, updatedAt→updatedOn in DB
-- Run: idempotent — safe to re-execute
-- =============================================================================

BEGIN;

ALTER TABLE admin.users
  ADD COLUMN IF NOT EXISTS "emailVerifiedAt" TIMESTAMP(3),
  ADD COLUMN IF NOT EXISTS "emailVerificationTokenHash" TEXT,
  ADD COLUMN IF NOT EXISTS "emailVerificationTokenExpiresAt" TIMESTAMP(3);

CREATE INDEX IF NOT EXISTS "users_emailVerificationTokenHash_idx"
  ON admin.users ("emailVerificationTokenHash");

-- One-time backfill: every account that already existed before this feature
-- shipped is grandfathered in as verified (as of its own creation date) —
-- the login gate below only ever applies to accounts that register AFTER
-- this migration runs. Safe to re-run: only touches rows still NULL.
UPDATE admin.users
SET "emailVerifiedAt" = "createdOn"
WHERE "emailVerifiedAt" IS NULL;

COMMIT;