/**
 * SQL for admin-user management (PLANNING.md §4b, §7). The auth module owns
 * admin_users lookups used by login; this file owns the CRUD list/create/update
 * the superadmin portal drives. No hard delete — deactivation via `status`
 * (enquiry replies reference their author).
 */
import type { Knex } from 'knex';
import { db } from '../../lib/db';
import { newId } from '../../lib/ulid';

export type Role = 'superadmin' | 'admin';
export type UserStatus = 'active' | 'inactive';

interface AdminUsersRow {
  id: string;
  phone: string;
  phone_display: string;
  email: string | null;
  name: string;
  password_hash: string | null;
  role: Role;
  status: UserStatus;
  last_login_at: string | null;
  created_by: string | null;
  created_at: string;
  updated_at: string;
}

const USER_COLUMNS = [
  'id',
  'phone',
  'phone_display',
  'email',
  'name',
  'password_hash',
  'role',
  'status',
  'last_login_at',
  'created_by',
  'created_at',
  'updated_at',
] as const;

export type UserRow = Pick<AdminUsersRow, (typeof USER_COLUMNS)[number]>;

export interface UserFilter {
  q?: string;
  status?: UserStatus;
  role?: Role;
}

function applyFilters(query: Knex.QueryBuilder, f: UserFilter): Knex.QueryBuilder {
  if (f.status) query.where('status', f.status);
  if (f.role) query.where('role', f.role);
  if (f.q) {
    const like = `%${f.q}%`;
    query.where((w) =>
      w
        .where('name', 'like', like)
        .orWhere('phone_display', 'like', like)
        .orWhere('phone', 'like', like)
        .orWhere('email', 'like', like),
    );
  }
  return query;
}

export function listUsers(
  filter: UserFilter,
  page: { limit: number; offset: number; sort: 'newest' | 'oldest' },
): Promise<UserRow[]> {
  const dir = page.sort === 'oldest' ? 'asc' : 'desc';
  return applyFilters(db<AdminUsersRow>('admin_users'), filter)
    .select(...USER_COLUMNS)
    .orderBy('created_at', dir)
    .orderBy('id', dir)
    .limit(page.limit)
    .offset(page.offset);
}

export async function countUsers(filter: UserFilter): Promise<number> {
  const row = (await applyFilters(db('admin_users'), filter).count({ n: '*' }).first()) as
    { n: number | string } | undefined;
  return Number(row?.n ?? 0);
}

export function findUserById(id: string): Promise<UserRow | undefined> {
  return db<AdminUsersRow>('admin_users')
    .select(...USER_COLUMNS)
    .where({ id })
    .first();
}

export async function phoneInUse(e164: string, exceptId?: string): Promise<boolean> {
  const q = db('admin_users').where({ phone: e164 });
  if (exceptId) q.whereNot({ id: exceptId });
  return (await q.first()) !== undefined;
}

export async function emailInUse(email: string, exceptId?: string): Promise<boolean> {
  const q = db('admin_users').where({ email });
  if (exceptId) q.whereNot({ id: exceptId });
  return (await q.first()) !== undefined;
}

/** Active superadmins other than `exceptId` — the last-superadmin guard. */
export async function countActiveSuperadmins(exceptId?: string): Promise<number> {
  const q = db('admin_users').where({ role: 'superadmin', status: 'active' });
  if (exceptId) q.whereNot({ id: exceptId });
  const row = (await q.count({ n: '*' }).first()) as { n: number | string } | undefined;
  return Number(row?.n ?? 0);
}

export interface NewUserRow {
  name: string;
  phone: string;
  phone_display: string;
  email: string;
  role: Role;
  status: UserStatus;
  created_by: string | null;
  password_hash: string;
}

export async function insertUser(row: NewUserRow): Promise<string> {
  const id = newId();
  await db('admin_users').insert({ id, ...row });
  return id;
}

export async function updateUser(
  id: string,
  patch: Partial<{
    name: string;
    email: string;
    phone: string;
    phone_display: string;
    role: Role;
    status: UserStatus;
  }>,
): Promise<void> {
  await db('admin_users').where({ id }).update(patch);
}