import { mkdtempSync, rmSync } from 'node:fs'
import { tmpdir } from 'node:os'
import { join } from 'node:path'
import Database from 'better-sqlite3'
import { afterEach, describe, expect, it } from 'vitest'
import {
  SCHEMA_VERSION,
  appendAudit,
  appendAuditBatch,
  closeDb,
  consumeOidcAuthCode,
  countWebauthnCredentials,
  createDb,
  createOidcAuthCode,
  createRefreshSession,
  createUser,
  createWebauthnCredential,
  getActiveOidcSigningKey,
  getUserApps,
  getUserById,
  getUserPermissions,
  insertOidcSigningKey,
  listActiveSessions,
  listAudit,
  migrateSchema,
  revokeSessionById,
  runHousekeeping,
  searchDirectoryUsers,
  setUserAccess,
  withTransaction,
  type AppDb,
  type Sqlite,
} from '../src/index.js'

const open: Sqlite[] = []
const dirs: string[] = []

function memDb(): { db: AppDb; sqlite: Sqlite } {
  const r = createDb('sqlite::memory:')
  migrateSchema(r.sqlite)
  open.push(r.sqlite)
  return r
}

afterEach(() => {
  for (const s of open.splice(0)) closeDb(s)
  for (const d of dirs.splice(0)) rmSync(d, { recursive: true, force: true })
})

function mkUser(db: AppDb, email: string, extra: { name?: string } = {}) {
  return createUser(db, {
    email,
    name: extra.name ?? email.split('@')[0]!,
    passwordHash: 'x',
    isAdmin: false,
  })
}

describe('createDb / closeDb', () => {
  it('выставляет pragma на файловой БД', () => {
    const dir = mkdtempSync(join(tmpdir(), 'ap-db-'))
    dirs.push(dir)
    const { sqlite } = createDb(join(dir, 'app.db'))
    open.push(sqlite)
    expect(sqlite.pragma('journal_mode', { simple: true })).toBe('wal')
    expect(sqlite.pragma('busy_timeout', { simple: true })).toBe(5000)
    expect(sqlite.pragma('synchronous', { simple: true })).toBe(1)
    expect(sqlite.pragma('temp_store', { simple: true })).toBe(2)
    expect(sqlite.pragma('cache_size', { simple: true })).toBe(-16000)
    expect(sqlite.pragma('foreign_keys', { simple: true })).toBe(1)
  })

  it('closeDb идемпотентен', () => {
    const { sqlite } = memDb()
    closeDb(sqlite)
    expect(sqlite.open).toBe(false)
    expect(() => closeDb(sqlite)).not.toThrow()
  })
})

describe('migrateSchema', () => {
  it('ставит user_version и повторный запуск — no-op', () => {
    const { sqlite } = memDb()
    expect(sqlite.pragma('user_version', { simple: true })).toBe(SCHEMA_VERSION)
    expect(() => migrateSchema(sqlite)).not.toThrow()
  })

  it('создаёт новые индексы', () => {
    const { sqlite } = memDb()
    const names = (
      sqlite
        .prepare(`SELECT name FROM sqlite_master WHERE type='index'`)
        .all() as Array<{ name: string }>
    ).map((r) => r.name)
    for (const n of [
      'uq_user_apps',
      'uq_user_permissions',
      'idx_refresh_sessions_expires',
      'idx_oidc_auth_codes_expires',
      'idx_audit_log_severity',
      'idx_audit_log_actor_email',
    ]) {
      expect(names).toContain(n)
    }
  })

  it('мигрирует «старую» схему: дедупликация и недостающие колонки', () => {
    const sqlite = new Database(':memory:')
    open.push(sqlite)
    sqlite.pragma('foreign_keys = ON')
    sqlite.exec(`
      CREATE TABLE users (
        id TEXT PRIMARY KEY NOT NULL, email TEXT NOT NULL UNIQUE,
        name TEXT NOT NULL, password_hash TEXT NOT NULL,
        is_admin INTEGER NOT NULL DEFAULT 0, disabled INTEGER NOT NULL DEFAULT 0,
        created_at TEXT NOT NULL, updated_at TEXT NOT NULL
      );
      CREATE TABLE user_apps (user_id TEXT NOT NULL, app_id TEXT NOT NULL);
      CREATE TABLE user_permissions (user_id TEXT NOT NULL, permission TEXT NOT NULL);
      INSERT INTO users VALUES ('u1','a@b.c','A','h',0,0,'t','t');
      INSERT INTO user_apps VALUES ('u1','dns'),('u1','dns'),('u1','vps');
      INSERT INTO user_permissions VALUES ('u1','p'),('u1','p');
    `)
    migrateSchema(sqlite)
    expect(
      sqlite.prepare('SELECT COUNT(*) c FROM user_apps').get(),
    ).toEqual({ c: 2 })
    expect(
      sqlite.prepare('SELECT COUNT(*) c FROM user_permissions').get(),
    ).toEqual({ c: 1 })
    const cols = (
      sqlite.prepare('PRAGMA table_info(users)').all() as Array<{ name: string }>
    ).map((c) => c.name)
    expect(cols).toContain('last_login_at')
    expect(sqlite.pragma('user_version', { simple: true })).toBe(SCHEMA_VERSION)
    expect(() =>
      sqlite.prepare(`INSERT INTO user_apps VALUES ('u1','dns')`).run(),
    ).toThrow(/UNIQUE/)
  })

  it('откатывается целиком при ошибке', () => {
    const sqlite = new Database(':memory:')
    open.push(sqlite)
    // users — это view-подобная помеха: таблица с несовместимой схемой user_apps без user_id
    sqlite.exec(`CREATE TABLE user_apps (foo TEXT)`)
    expect(() => migrateSchema(sqlite)).toThrow()
    expect(sqlite.pragma('user_version', { simple: true })).toBe(0)
  })
})

describe('withTransaction / setUserAccess', () => {
  it('откатывает все изменения при исключении', () => {
    const { db } = memDb()
    const u = mkUser(db, 'a@x.io')
    setUserAccess(db, u.id, ['dns'], ['p1'])
    expect(() =>
      withTransaction(db, () => {
        setUserAccess(db, u.id, ['vps', 'bgp'], ['p2'])
        throw new Error('boom')
      }),
    ).toThrow('boom')
    expect(getUserApps(db, u.id)).toEqual(['dns'])
    expect(getUserPermissions(db, u.id)).toEqual(['p1'])
  })

  it('схлопывает дубликаты во входе', () => {
    const { db } = memDb()
    const u = mkUser(db, 'a@x.io')
    setUserAccess(db, u.id, ['dns', 'dns'], ['p', 'p'])
    expect(getUserApps(db, u.id)).toEqual(['dns'])
    expect(getUserPermissions(db, u.id)).toEqual(['p'])
  })

  it('вложенная транзакция откатывается как savepoint', () => {
    const { db } = memDb()
    const u = mkUser(db, 'a@x.io')
    withTransaction(db, () => {
      setUserAccess(db, u.id, ['dns'], [])
      try {
        withTransaction(db, () => {
          setUserAccess(db, u.id, ['vps'], [])
          throw new Error('inner')
        })
      } catch {
        /* ожидаемо */
      }
    })
    expect(getUserApps(db, u.id)).toEqual(['dns'])
  })
})

describe('сессии и каталог', () => {
  it('listActiveSessions: только активные, фильтр по пользователю, сортировка', () => {
    const { db } = memDb()
    const a = mkUser(db, 'a@x.io')
    const b = mkUser(db, 'b@x.io')
    const future = new Date(Date.now() + 3600_000)
    const past = new Date(Date.now() - 3600_000)
    const s1 = createRefreshSession(db, a.id, 't1', future)
    createRefreshSession(db, a.id, 't2', past)
    const s3 = createRefreshSession(db, b.id, 't3', future)
    const s4 = createRefreshSession(db, b.id, 't4', future)
    revokeSessionById(db, s4)

    const all = listActiveSessions(db).map((s) => s.id)
    expect(new Set(all)).toEqual(new Set([s1, s3]))
    expect(listActiveSessions(db, { userId: a.id }).map((s) => s.id)).toEqual([
      s1,
    ])
  })

  it('searchDirectoryUsers: без passwordHash, без disabled, LIMIT, экранирование', () => {
    const { db } = memDb()
    for (let i = 0; i < 60; i++) mkUser(db, `user${i}@x.io`)
    const weird = mkUser(db, 'pct@x.io', { name: '100%' })
    const dis = mkUser(db, 'off@x.io')
    db.$client.prepare('UPDATE users SET disabled=1 WHERE id=?').run(dis.id)

    const page = searchDirectoryUsers(db, '', 50)
    expect(page).toHaveLength(50)
    expect(Object.keys(page[0]!).sort()).toEqual(['email', 'id', 'name'])
    expect(searchDirectoryUsers(db, 'off@x.io')).toEqual([])
    expect(searchDirectoryUsers(db, 'USER7@').map((u) => u.email)).toEqual([
      'user7@x.io',
    ])
    expect(searchDirectoryUsers(db, '%').map((u) => u.id)).toEqual([weird.id])
    expect(searchDirectoryUsers(db, weird.id)).toHaveLength(1)
  })

  it('countWebauthnCredentials считает COUNT(*)', () => {
    const { db } = memDb()
    const u = mkUser(db, 'a@x.io')
    expect(countWebauthnCredentials(db, u.id)).toBe(0)
    for (const id of ['c1', 'c2']) {
      createWebauthnCredential(db, {
        userId: u.id,
        credentialId: id,
        publicKey: 'k',
        counter: 0,
        name: id,
      })
    }
    expect(countWebauthnCredentials(db, u.id)).toBe(2)
  })
})

describe('OIDC', () => {
  function code(db: AppDb, userId: string, raw: string, expiresAt: Date) {
    createOidcAuthCode(db, {
      rawCode: raw,
      clientId: 'c',
      userId,
      redirectUri: 'https://x/cb',
      scope: 'openid',
      expiresAt,
    })
  }

  it('consumeOidcAuthCode: одноразовый, просроченный отклоняется', () => {
    const { db } = memDb()
    const u = mkUser(db, 'a@x.io')
    code(db, u.id, 'ok', new Date(Date.now() + 60_000))
    code(db, u.id, 'old', new Date(Date.now() - 60_000))
    expect(consumeOidcAuthCode(db, 'ok')?.userId).toBe(u.id)
    expect(consumeOidcAuthCode(db, 'ok')).toBeUndefined()
    expect(consumeOidcAuthCode(db, 'old')).toBeUndefined()
    expect(consumeOidcAuthCode(db, 'missing')).toBeUndefined()
  })

  it('insertOidcSigningKey оставляет один активный ключ — новый', () => {
    const { db } = memDb()
    insertOidcSigningKey(db, { kid: 'k1', privatePem: 'p', publicJwkJson: '{}' })
    insertOidcSigningKey(db, { kid: 'k2', privatePem: 'p', publicJwkJson: '{}' })
    expect(getActiveOidcSigningKey(db)?.kid).toBe('k2')
    const active = db.$client
      .prepare('SELECT COUNT(*) c FROM oidc_signing_keys WHERE active=1')
      .get()
    expect(active).toEqual({ c: 1 })
  })
})

describe('audit', () => {
  it('appendAudit: дубликат event_id → false', () => {
    const { db } = memDb()
    expect(appendAudit(db, { eventId: 'e1', action: 'a', summary: 's' })).toBe(
      true,
    )
    expect(appendAudit(db, { eventId: 'e1', action: 'a', summary: 's' })).toBe(
      false,
    )
    expect(appendAudit(db, { action: 'a', summary: 's' })).toBe(true)
    expect(appendAudit(db, { action: 'a', summary: 's' })).toBe(true)
  })

  it('appendAuditBatch: принимает, пропускает дубли, не теряет остальные', () => {
    const { db } = memDb()
    const events = Array.from({ length: 500 }, (_, i) => ({
      eventId: `e${i % 400}`,
      action: 'x.y',
      summary: 's',
    }))
    const res = appendAuditBatch(db, events)
    expect(res).toEqual({ accepted: 400, duplicates: 100, failed: 0 })
    expect(listAudit(db, { limit: 1000 })).toHaveLength(400)
  })

  it('listAudit kind=logins/changes делит по префиксу auth.', () => {
    const { db } = memDb()
    appendAudit(db, { action: 'auth.login', summary: 's' })
    appendAudit(db, { action: 'auth.login_failed', summary: 's' })
    appendAudit(db, { action: 'user.create', summary: 's' })
    appendAudit(db, { action: 'audit.purge', summary: 's' })
    appendAudit(db, { action: 'authz.other', summary: 's' })
    const logins = listAudit(db, { kind: 'logins' }).map((e) => e.action)
    const changes = listAudit(db, { kind: 'changes' }).map((e) => e.action)
    expect(logins.sort()).toEqual(['auth.login', 'auth.login_failed'])
    expect(changes.sort()).toEqual(['audit.purge', 'authz.other', 'user.create'])
  })
})

describe('runHousekeeping', () => {
  it('удаляет старые истёкшие/отозванные, оставляя активные и свежие', () => {
    const { db } = memDb()
    const u = mkUser(db, 'a@x.io')
    const day = 86_400_000
    const active = createRefreshSession(db, u.id, 'a', new Date(Date.now() + day))
    createRefreshSession(db, u.id, 'oldexp', new Date(Date.now() - 10 * day))
    createRefreshSession(db, u.id, 'freshexp', new Date(Date.now() - day))
    const oldRevoked = createRefreshSession(
      db,
      u.id,
      'oldrev',
      new Date(Date.now() + day),
    )
    db.$client
      .prepare('UPDATE refresh_sessions SET revoked_at=? WHERE id=?')
      .run(new Date(Date.now() - 10 * day).toISOString(), oldRevoked)

    createOidcAuthCode(db, {
      rawCode: 'stale',
      clientId: 'c',
      userId: u.id,
      redirectUri: 'r',
      scope: 's',
      expiresAt: new Date(Date.now() - 10 * day),
    })
    createOidcAuthCode(db, {
      rawCode: 'live',
      clientId: 'c',
      userId: u.id,
      redirectUri: 'r',
      scope: 's',
      expiresAt: new Date(Date.now() + 60_000),
    })
    db.$client
      .prepare(
        `INSERT INTO webauthn_challenges (id,purpose,challenge,expires_at,created_at)
         VALUES ('c1','register','x',?,?)`,
      )
      .run(new Date(Date.now() - 1000).toISOString(), new Date().toISOString())

    const res = runHousekeeping(db, { retentionDays: 7 })
    expect(res).toEqual({ sessions: 2, authCodes: 1, challenges: 1 })
    const left = db.$client
      .prepare('SELECT id FROM refresh_sessions')
      .all() as Array<{ id: string }>
    expect(left).toHaveLength(2)
    expect(left.map((r) => r.id)).toContain(active)

    expect(runHousekeeping(db, { retentionDays: 7 })).toEqual({
      sessions: 0,
      authCodes: 0,
      challenges: 0,
    })
  })

  it('удаляет пачками > 1000 строк', () => {
    const { db } = memDb()
    const u = mkUser(db, 'a@x.io')
    const ins = db.$client.prepare(
      `INSERT INTO refresh_sessions (id,user_id,token_hash,expires_at,created_at)
       VALUES (?,?,?,?,?)`,
    )
    db.$client.transaction(() => {
      for (let i = 0; i < 2500; i++) {
        ins.run(`s${i}`, u.id, `h${i}`, '2000-01-01T00:00:00.000Z', 't')
      }
    })()
    expect(runHousekeeping(db, { retentionDays: 1 }).sessions).toBe(2500)
  })
})

describe('подготовленные запросы', () => {
  it('горячие запросы не вызывают prepare повторно и дают прежние результаты', () => {
    const { db, sqlite } = memDb()
    const u = mkUser(db, 'p@x.io')
    setUserAccess(db, u.id, ['dns', 'vps'], ['dns:dns:read'])
    // прогрев: первая подготовка
    getUserById(db, u.id)
    getUserApps(db, u.id)
    getUserPermissions(db, u.id)

    const calls: string[] = []
    const orig = sqlite.prepare.bind(sqlite)
    sqlite.prepare = ((sql: string) => {
      calls.push(sql)
      return orig(sql)
    }) as typeof sqlite.prepare

    for (let i = 0; i < 20; i++) {
      expect(getUserById(db, u.id)?.email).toBe('p@x.io')
      expect(getUserApps(db, u.id).sort()).toEqual(['dns', 'vps'])
      expect(getUserPermissions(db, u.id)).toEqual(['dns:dns:read'])
    }
    expect(getUserById(db, 'missing')).toBeUndefined()
    expect(calls).toHaveLength(0)
  })

  it('независимые соединения не делят подготовленные запросы', () => {
    const a = memDb()
    const b = memDb()
    const ua = mkUser(a.db, 'a@x.io')
    expect(getUserById(a.db, ua.id)).toBeDefined()
    expect(getUserById(b.db, ua.id)).toBeUndefined()
  })
})