-- 038_email_verification.sql
-- OTP email verification for sender email before payment

CREATE TABLE IF NOT EXISTS email_verifications (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL,
  code CHAR(6) NOT NULL,
  ip_address TEXT,
  attempts INTEGER NOT NULL DEFAULT 0,
  max_attempts INTEGER NOT NULL DEFAULT 3,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '10 minutes',
  verified BOOLEAN NOT NULL DEFAULT FALSE,
  verified_at TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_email_verifications_email
  ON email_verifications (email, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_email_verifications_ip
  ON email_verifications (ip_address, created_at DESC)
  WHERE ip_address IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_email_verifications_expires
  ON email_verifications (expires_at)
  WHERE verified = FALSE;

CREATE TABLE IF NOT EXISTS trusted_devices (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL,
  device_token TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '90 days',
  last_used_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_trusted_devices_token
  ON trusted_devices (device_token);
CREATE INDEX IF NOT EXISTS idx_trusted_devices_email
  ON trusted_devices (email);
