-- ============================================================
-- Migration 048: 创建邮件验证码表
--
-- 存储邮箱验证码记录，支持注册/登录/重置密码/换绑邮箱等场景。
-- 与 sms_verifications 结构对称。
-- ============================================================

CREATE TABLE IF NOT EXISTS email_verifications (
  id          BIGSERIAL     PRIMARY KEY,
  email       VARCHAR(100)  NOT NULL,
  code        VARCHAR(255)  NOT NULL,
  type        VARCHAR(20)   NOT NULL,
  expires_at  TIMESTAMPTZ   NOT NULL,
  verified_at TIMESTAMPTZ,
  attempts    INT           NOT NULL DEFAULT 0,
  created_at  TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_email_verif_email_type ON email_verifications (email, type);
CREATE INDEX IF NOT EXISTS idx_email_verif_expires    ON email_verifications (expires_at);

-- ============================================================
-- Migration 049: 创建邮件发送日志表
-- ============================================================

CREATE TABLE IF NOT EXISTS email_logs (
  id          BIGSERIAL     PRIMARY KEY,
  user_id     UUID,
  email       VARCHAR(100)  NOT NULL,
  type        VARCHAR(20)   NOT NULL,
  status      VARCHAR(20)   NOT NULL DEFAULT 'PENDING',
  provider    VARCHAR(50),
  provider_id VARCHAR(200),
  error_msg   TEXT,
  created_at  TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_email_logs_email   ON email_logs (email);
CREATE INDEX IF NOT EXISTS idx_email_logs_created ON email_logs (created_at);