sori.studio/server/repositories/email-otp-repository.js

import { createError } from 'h3'
import { getPostgresClient } from './postgres-client'
import { hashOtpCode, normalizeOtpEmail, timingSafeEqualHex } from '../utils/email-otp'

/** 최대 검증 시도 횟수(초과 시 해당 챌린지는 더 이상 사용 불가) */
const MAX_OTP_VERIFY_ATTEMPTS = 8

/**
 * DB 클라이언트 조회 (필수)
 * @returns {ReturnType<typeof import('postgres').default>} postgres sql
 */
const requireSql = () => {
  const sql = getPostgresClient()
  if (!sql) {
    throw createError({
      statusCode: 500,
      message: '데이터베이스 설정이 필요합니다.'
    })
  }
  return sql
}

/**
 * 동일 이메일·용도의 미소진 OTP를 무효화한다.
 * @param {import('postgres').TransactionSql} sql - sql 또는 트랜잭션
 * @param {string} email - 정규화된 이메일
 * @param {string} purpose - signup | password_reset
 * @returns {Promise<void>}
 */
export const invalidatePendingOtpChallenges = async (sql, email, purpose) => {
  await sql`
    UPDATE email_otp_challenges
    SET consumed_at = now()
    WHERE lower(email) = lower(${email})
      AND purpose = ${purpose}
      AND consumed_at IS NULL
  `
}

/**
 * 특정 OTP를 제외한 동일 이메일·용도의 미소진 OTP를 무효화한다.
 * @param {import('postgres').TransactionSql} sql - sql 또는 트랜잭션
 * @param {string} email - 정규화된 이메일
 * @param {string} purpose - signup | password_reset
 * @param {string} keepId - 유지할 챌린지 ID
 * @returns {Promise<void>}
 */
export const invalidatePendingOtpChallengesExcept = async (sql, email, purpose, keepId) => {
  await sql`
    UPDATE email_otp_challenges
    SET consumed_at = now()
    WHERE lower(email) = lower(${email})
      AND purpose = ${purpose}
      AND id <> ${keepId}
      AND consumed_at IS NULL
  `
}

/**
 * 특정 OTP 챌린지를 삭제한다.
 * @param {import('postgres').TransactionSql} sql - sql 또는 트랜잭션
 * @param {string} id - 챌린지 ID
 * @returns {Promise<void>}
 */
export const deleteOtpChallengeById = async (sql, id) => {
  await sql`
    DELETE FROM email_otp_challenges
    WHERE id = ${id}
  `
}

/**
 * 최근 짧은 시간 내 동일 이메일·용도 발송이 있는지 확인한다.
 * @param {import('postgres').Sql} sql - sql
 * @param {string} email - 이메일
 * @param {string} purpose - 용도
 * @returns {Promise<boolean>} true면 재요청 쿨다운 중
 */
export const hasRecentOtpSend = async (sql, email, purpose) => {
  const rows = await sql`
    SELECT 1 AS "x"
    FROM email_otp_challenges
    WHERE lower(email) = lower(${email})
      AND purpose = ${purpose}
      AND created_at > now() - interval '55 seconds'
    LIMIT 1
  `
  return Boolean(rows?.[0])
}

/**
 * 1시간 내 발송 횟수
 * @param {import('postgres').Sql} sql - sql
 * @param {string} email - 이메일
 * @param {string} purpose - 용도
 * @returns {Promise<number>}
 */
export const countOtpSendsLastHour = async (sql, email, purpose) => {
  const rows = await sql`
    SELECT COUNT(*)::int AS "c"
    FROM email_otp_challenges
    WHERE lower(email) = lower(${email})
      AND purpose = ${purpose}
      AND created_at > now() - interval '1 hour'
  `
  return Number(rows?.[0]?.c || 0)
}

/**
 * OTP 챌린지 행을 삽입한다.
 * @param {import('postgres').Sql} sql - sql
 * @param {{ email: string, purpose: string, codeHash: string, expiresAt: Date, createdIp: string }} input - 입력
 * @returns {Promise<string>} 삽입된 id
 */
export const insertOtpChallenge = async (sql, input) => {
  const rows = await sql`
    INSERT INTO email_otp_challenges (email, purpose, code_hash, expires_at, created_ip)
    VALUES (${input.email}, ${input.purpose}, ${input.codeHash}, ${input.expiresAt}, ${input.createdIp})
    RETURNING id
  `
  const id = rows?.[0]?.id
  if (!id) {
    throw createError({
      statusCode: 500,
      message: '인증 정보 저장에 실패했습니다.'
    })
  }
  return String(id)
}

/**
 * 이메일 OTP를 검증하고 소진 처리한다.
 * @param {{ email: string, purpose: string, code: string, pepper: string }} input - 입력
 * @returns {Promise<{ ok: boolean, reason?: 'none' | 'expired' | 'locked' | 'mismatch' }>}
 */
export const verifyAndConsumeEmailOtp = async (input) => {
  const sql = requireSql()
  const email = normalizeOtpEmail(input.email)
  const purpose = String(input.purpose || '').trim()
  const code = String(input.code || '').trim()
  const pepper = String(input.pepper || '')

  if (!email || !purpose || !/^\d{6}$/.test(code) || !pepper) {
    return { ok: false, reason: 'mismatch' }
  }

  return await sql.begin(async (tx) => {
    const rows = await tx`
      SELECT id, code_hash AS "codeHash", verify_attempt_count AS "verifyAttemptCount", expires_at AS "expiresAt"
      FROM email_otp_challenges
      WHERE lower(email) = lower(${email})
        AND purpose = ${purpose}
        AND consumed_at IS NULL
      ORDER BY created_at DESC
      LIMIT 1
      FOR UPDATE
    `

    const row = rows?.[0]
    if (!row) {
      return { ok: false, reason: 'none' }
    }

    const expiresAt = new Date(row.expiresAt)
    if (Number.isNaN(expiresAt.getTime()) || expiresAt.getTime() < Date.now()) {
      await tx`
        UPDATE email_otp_challenges
        SET consumed_at = now()
        WHERE id = ${row.id}
      `
      return { ok: false, reason: 'expired' }
    }

    if (Number(row.verifyAttemptCount || 0) >= MAX_OTP_VERIFY_ATTEMPTS) {
      return { ok: false, reason: 'locked' }
    }

    const expected = hashOtpCode({ pepper, email, purpose, code })
    if (!timingSafeEqualHex(expected, row.codeHash)) {
      await tx`
        UPDATE email_otp_challenges
        SET verify_attempt_count = verify_attempt_count + 1
        WHERE id = ${row.id}
      `
      return { ok: false, reason: 'mismatch' }
    }

    await tx`
      UPDATE email_otp_challenges
      SET consumed_at = now()
      WHERE id = ${row.id}
    `

    return { ok: true }
  })
}