/**
 * createUserRepo(db) —— 本包唯一写 SQL 的地方，db（Knex 实例）由 composition root 注入。
 *
 * 表结构定义在 `src/db/tables/`（users 归 `@drama/db`），本包不建连接：
 *   users(id, email, password_hash, locale, email_verified_at, created_at)
 *   user_oauth(user_id, provider, provider_user_id)
 *   auth_sessions(sid, user_id, refresh_hash, family, expires_at, used, revoked)
 *   auth_attempts(key, ip, at)
 *   auth_codes(email, purpose, code_hash, expires_at, attempts)
 *
 * 字段纪律（plan/db-root-solution.md 决定 2/3）：出入参、返回对象与列名同名透传（snake_case）；
 * 时间列是 DATETIME(3) UTC，进出都是 JS Date。
 *
 * 仅落库状态：会话族、refresh hash、防爆破计数、验证码 hash。密码学不在此处。
 */

import { AuthError } from './errors.js'
import authAttemptsTable from './db/tables/auth_attempts.cjs'
import authCodesTable from './db/tables/auth_codes.cjs'
import authSessionsTable from './db/tables/auth_sessions.cjs'
import userOauthTable from './db/tables/user_oauth.cjs'

const USERS = 'users'
const USER_OAUTH = userOauthTable.tableName
const AUTH_SESSIONS = authSessionsTable.tableName
const AUTH_ATTEMPTS = authAttemptsTable.tableName
const AUTH_CODES = authCodesTable.tableName

/**
 * @param {import('knex').Knex} db
 * @returns {import('./index.js').UserRepo}
 */
export function createUserRepo(db) {
  if (!db || typeof db !== 'function') {
    throw new AuthError({ code: 'USER_NOT_FOUND', message: 'createUserRepo: db (Knex) required' })
  }

  /** @param {any} row @param {any[]} [oauthRows] @returns {import('./index.js').User} */
  function rowToUser(row, oauthRows = []) {
    return {
      id: String(row.id),
      email: row.email,
      password_hash: row.password_hash ?? undefined,
      locale: row.locale ?? undefined,
      role: row.role ?? undefined,
      email_verified_at: row.email_verified_at ?? undefined,
      oauth_providers: oauthRows.map((o) => ({
        provider: o.provider,
        provider_user_id: o.provider_user_id,
      })),
    }
  }

  /** @param {string} user_id */
  async function loadOauth(user_id) {
    return db(USER_OAUTH).where({ user_id }).select('provider', 'provider_user_id')
  }

  return {
    async findByEmail(email) {
      const row = await db(USERS).where({ email: norm(email) }).first()
      if (!row) return null
      return rowToUser(row, await loadOauth(row.id))
    },

    async findById(id) {
      const row = await db(USERS).where({ id }).first()
      if (!row) return null
      return rowToUser(row, await loadOauth(row.id))
    },

    async create({ email, password_hash, oauth_providers, locale, email_verified_at }) {
      const id = newId()
      try {
        await db(USERS).insert({
          id,
          email: norm(email),
          password_hash: password_hash ?? null,
          locale: locale ?? null,
          email_verified_at: email_verified_at ?? null,
          created_at: new Date(),
        })
      } catch (error) {
        if (error.code === 'ER_DUP_ENTRY') throw new AuthError({ code: 'EMAIL_TAKEN' })
        throw error
      }
      if (oauth_providers && oauth_providers.length) {
        await db(USER_OAUTH).insert(
          oauth_providers.map((p) => ({
            user_id: id,
            provider: p.provider,
            provider_user_id: p.provider_user_id,
          })),
        )
      }
      const row = await db(USERS).where({ id }).first()
      return rowToUser(row, await loadOauth(id))
    },

    async linkOauth({ user_id, provider, provider_user_id }) {
      const exists = await db(USER_OAUTH)
        .where({ user_id, provider, provider_user_id })
        .first()
      if (!exists) {
        await db(USER_OAUTH).insert({ user_id, provider, provider_user_id })
      }
      const row = await db(USERS).where({ id: user_id }).first()
      if (!row) throw new AuthError({ code: 'USER_NOT_FOUND' })
      return rowToUser(row, await loadOauth(user_id))
    },

    async updatePasswordHash({ user_id, password_hash }) {
      const count = await db(USERS)
        .where({ id: user_id })
        .update({ password_hash, updated_at: new Date() })
      if (!count) throw new AuthError({ code: 'USER_NOT_FOUND' })
    },

    async saveSession({ user_id, sid, refresh_hash, family, expires_at }) {
      await db(AUTH_SESSIONS).insert({
        sid,
        user_id,
        refresh_hash,
        family,
        expires_at,
        used: false,
        revoked: false,
      })
    },

    async findSession({ sid }) {
      const row = await db(AUTH_SESSIONS).where({ sid }).first()
      if (!row) return null
      return {
        sid: row.sid,
        user_id: String(row.user_id),
        refresh_hash: row.refresh_hash,
        family: row.family,
        expires_at: row.expires_at,
        used: Boolean(row.used),
        revoked: Boolean(row.revoked),
      }
    },

    async markRefreshUsed({ sid }) {
      await db(AUTH_SESSIONS).where({ sid }).update({ used: true })
    },

    async revokeFamily({ family }) {
      await db(AUTH_SESSIONS).where({ family }).update({ revoked: true })
    },

    async revokeUserSessions({ user_id }) {
      await db(AUTH_SESSIONS).where({ user_id }).update({ revoked: true })
    },

    async recordAttempt({ key, ip, at }) {
      await db(AUTH_ATTEMPTS).insert({ key, ip: ip ?? null, at })
    },

    async countRecentAttempts({ key, windowMs }) {
      const since = new Date(Date.now() - windowMs)
      const res = await db(AUTH_ATTEMPTS)
        .where({ key })
        .andWhere('at', '>=', since)
        .count({ c: '*' })
        .first()
      return Number(res?.c ?? 0)
    },

    async saveCode({ email, purpose, code_hash, expires_at }) {
      // 同 email+purpose 只保留最新一条：先删旧。
      await db(AUTH_CODES).where({ email: norm(email), purpose }).del()
      await db(AUTH_CODES).insert({
        email: norm(email),
        purpose,
        code_hash,
        expires_at,
        attempts: 0,
      })
    },

    async consumeCode({ email, purpose }) {
      const row = await db(AUTH_CODES).where({ email: norm(email), purpose }).first()
      if (!row) return null
      // 读取即递增尝试次数（消费语义），并把超额删除留给下次 saveCode。
      await db(AUTH_CODES)
        .where({ email: norm(email), purpose })
        .update({ attempts: Number(row.attempts) + 1 })
      return {
        email: row.email,
        purpose: row.purpose,
        code_hash: row.code_hash,
        expires_at: row.expires_at,
        attempts: Number(row.attempts),
      }
    },
  }
}

/** @param {string} e */
function norm(e) {
  return String(e ?? '').trim().toLowerCase()
}

function newId() {
  return globalThis.crypto.randomUUID()
}