/**
 * All SQL for the auth module: admin_users lookups + lockout bookkeeping,
 * admin_refresh_tokens (rotation lineage), password_reset_tokens.
 * Free functions, ids minted here — mirrors src/modules/enquiry/enquiry.repository.ts.
 */
import { db } from '../../lib/db';
import { newId } from '../../lib/ulid';

// Timestamps that must line up with the DB's own clock (`expires_at > NOW()`
// checks, lockout windows) are computed in SQL — `NOW() + INTERVAL ? …` — so no
// JS Date is ever handed to the driver, which mis-shifts it under this
// connection's timezone config (see src/lib/dates.ts).
const nowPlus = (amount: number, unit: 'SECOND' | 'MINUTE') =>
  db.raw(`NOW() + INTERVAL ? ${unit}`, [amount]);

export interface AdminUserRow {
  id: string;
  phone: string;
  phone_display: string;
  email: string | null;
  name: string;
  password_hash: string | null;
  role: 'superadmin' | 'admin';
  status: 'active' | 'inactive';
  failed_attempts: number;
  locked_until: string | null;
  last_login_at: string | null;
}

export interface RefreshTokenRow {
  id: string;
  admin_user_id: string;
  family_id: string;
  token_hash: string;
  expires_at: string;
  revoked_at: string | null;
  replaced_by: string | null;
}

interface ResetTokenRow {
  id: string;
  admin_user_id: string;
  token_hash: string;
  purpose: 'reset' | 'invite';
  expires_at: string;
  used_at: string | null;
}

const ADMIN_COLUMNS = [
  'id',
  'phone',
  'phone_display',
  'email',
  'name',
  'password_hash',
  'role',
  'status',
  'failed_attempts',
  'locked_until',
  'last_login_at',
] as const;

// ─── admin_users ───────────────────────────────────────────────────────────

export function findAdminByPhone(e164: string): Promise<AdminUserRow | undefined> {
  return db<AdminUserRow>('admin_users')
    .select(...ADMIN_COLUMNS)
    .where({ phone: e164 })
    .first();
}

// `email` is UNIQUE on admin_users (migrations/0001) — used by forgot-password.
export function findAdminByEmail(email: string): Promise<AdminUserRow | undefined> {
  return db<AdminUserRow>('admin_users')
    .select(...ADMIN_COLUMNS)
    .where({ email })
    .first();
}

export function findAdminById(id: string): Promise<AdminUserRow | undefined> {
  return db<AdminUserRow>('admin_users')
    .select(...ADMIN_COLUMNS)
    .where({ id })
    .first();
}

export async function recordFailedLogin(
  id: string,
  failedAttempts: number,
  lockMinutes: number | null,
): Promise<void> {
  await db('admin_users')
    .where({ id })
    .update({
      failed_attempts: failedAttempts,
      locked_until: lockMinutes ? nowPlus(lockMinutes, 'MINUTE') : null,
    });
}

export async function recordSuccessfulLogin(id: string): Promise<void> {
  await db('admin_users').where({ id }).update({
    failed_attempts: 0,
    locked_until: null,
    last_login_at: db.fn.now(),
  });
}

export async function updateAdminPassword(id: string, passwordHash: string): Promise<void> {
  await db('admin_users').where({ id }).update({ password_hash: passwordHash });
}

export async function clearLockout(id: string): Promise<void> {
  await db('admin_users').where({ id }).update({ failed_attempts: 0, locked_until: null });
}

// ─── admin_refresh_tokens ─────────────────────────────────────────────────

export async function insertRefreshToken(input: {
  adminUserId: string;
  familyId: string;
  tokenHash: string;
  expiresInSeconds: number;
  userAgent: string | null;
  ipHash: string | null;
}): Promise<string> {
  const id = newId();
  await db('admin_refresh_tokens').insert({
    id,
    admin_user_id: input.adminUserId,
    family_id: input.familyId,
    token_hash: input.tokenHash,
    expires_at: nowPlus(input.expiresInSeconds, 'SECOND'),
    user_agent: input.userAgent,
    ip_hash: input.ipHash,
  });
  return id;
}

export function findRefreshByHash(tokenHash: string): Promise<RefreshTokenRow | undefined> {
  return db<RefreshTokenRow>('admin_refresh_tokens')
    .select(
      'id',
      'admin_user_id',
      'family_id',
      'token_hash',
      'expires_at',
      'revoked_at',
      'replaced_by',
    )
    .where({ token_hash: tokenHash })
    .first();
}

export async function markRefreshRotated(id: string, successorId: string): Promise<void> {
  await db('admin_refresh_tokens')
    .where({ id })
    .update({ revoked_at: db.fn.now(), replaced_by: successorId });
}

export async function revokeRefreshByHash(tokenHash: string): Promise<void> {
  await db('admin_refresh_tokens')
    .where({ token_hash: tokenHash })
    .whereNull('revoked_at')
    .update({ revoked_at: db.fn.now() });
}

export async function revokeRefreshFamily(familyId: string): Promise<void> {
  await db('admin_refresh_tokens')
    .where({ family_id: familyId })
    .whereNull('revoked_at')
    .update({ revoked_at: db.fn.now() });
}

export async function revokeAllRefreshForUser(adminUserId: string): Promise<void> {
  await db('admin_refresh_tokens')
    .where({ admin_user_id: adminUserId })
    .whereNull('revoked_at')
    .update({ revoked_at: db.fn.now() });
}

// ─── password_reset_tokens ───────────────────────────────────────────────

export async function invalidatePriorResetTokens(adminUserId: string): Promise<void> {
  await db('password_reset_tokens')
    .where({ admin_user_id: adminUserId })
    .whereNull('used_at')
    .update({ used_at: db.fn.now() });
}

export async function insertResetToken(input: {
  adminUserId: string;
  tokenHash: string;
  purpose: 'reset' | 'invite';
  expiresInSeconds: number;
}): Promise<void> {
  await db('password_reset_tokens').insert({
    id: newId(),
    admin_user_id: input.adminUserId,
    token_hash: input.tokenHash,
    purpose: input.purpose,
    expires_at: nowPlus(input.expiresInSeconds, 'SECOND'),
  });
}

export function findValidResetToken(
  tokenHash: string,
): Promise<Pick<ResetTokenRow, 'id' | 'admin_user_id'> | undefined> {
  return db<ResetTokenRow>('password_reset_tokens')
    .select('id', 'admin_user_id')
    .where({ token_hash: tokenHash })
    .whereNull('used_at')
    .where('expires_at', '>', db.fn.now())
    .first();
}

export async function consumeResetToken(id: string): Promise<void> {
  await db('password_reset_tokens').where({ id }).update({ used_at: db.fn.now() });
}