CREATE TABLE IF NOT EXISTS email_otp_challenges (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL,
  purpose TEXT NOT NULL,
  code_hash TEXT NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL,
  consumed_at TIMESTAMPTZ,
  verify_attempt_count INTEGER NOT NULL DEFAULT 0,
  created_ip TEXT NOT NULL DEFAULT '',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT email_otp_challenges_purpose_check CHECK (purpose IN ('signup', 'password_reset'))
);

CREATE INDEX IF NOT EXISTS email_otp_challenges_email_purpose_created_idx
  ON email_otp_challenges (lower(email), purpose, created_at DESC);