import { and, asc, eq, ilike, isNotNull, or, sql, type SQL } from "drizzle-orm"

import { auth } from "@/lib/auth"
import { db } from "@/lib/db/client"
import { account } from "@/lib/db/auth-schema"
import { departments, people, positions } from "@/lib/db/schema"
import { generateFirstPassword } from "@/lib/first-password"
import type { Locale } from "@/lib/i18n"
import { checkUsername, normaliseUsername } from "@/lib/username"

/**
 * Questions about a person that something other than a screen needs to ask.
 *
 * This exists because `lib/auth.ts` needs one of them and must not reach for
 * Drizzle itself. The lint rule that said so is the rule working: an auth
 * configuration is not a place where queries should accumulate, and moving the
 * one it needs behind a name keeps it that way.
 */

/**
 * Have they left?
 *
 * By username, because that is what arrives at the sign-in route. `archived_at`
 * is the only fact consulted, and deliberately the only one: a second column
 * recording whether a login still works is how somebody ends up archived in the
 * roster and still signing in.
 */
export async function hasLeft(username: string): Promise<boolean> {
  const [gone] = await db
    .select({ id: people.id })
    .from(people)
    .where(
      and(
        eq(people.username, normaliseUsername(username)),
        isNotNull(people.archivedAt),
      ),
    )
  return Boolean(gone)
}

/**
 * Set the language somebody reads the interface in.
 *
 * On the person and never on the device. These are shared terminals: a
 * preference kept in a browser is inherited by whoever walks up next, and with
 * 183 of the 193 people reading Thai, the one who inherits it is usually the
 * one it is wrong for.
 */
export async function setLanguage(
  personId: string,
  language: Locale,
): Promise<void> {
  await db.update(people).set({ language }).where(eq(people.id, personId))
}

/* -------------------------------------------------------------------------- */
/* The admin screen                                                           */
/* -------------------------------------------------------------------------- */

/**
 * Same guard as everywhere else: a url segment is not a uuid until it is, and
 * Postgres answers a malformed one with an error rather than an empty result.
 * Every function below that takes an id checks it, including the writes, which
 * are reachable as server actions without passing through a page.
 */
const UUID = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i

export type PersonRow = {
  id: string
  employeeCode: string
  name: string
  username: string | null
  hasLogin: boolean
  archived: boolean
  position: string | null
  department: string | null
}

/** One shape, built fresh each time, so the list and the detail cannot drift. */
function roster(where: SQL | undefined) {
  return db
    .select({
      id: people.id,
      employeeCode: people.employeeCode,
      firstName: people.firstName,
      lastName: people.lastName,
      displayName: people.displayName,
      username: people.username,
      authUserId: people.authUserId,
      archivedAt: people.archivedAt,
      position: positions.title,
      department: departments.name,
    })
    .from(people)
    .leftJoin(positions, eq(positions.id, people.positionId))
    .leftJoin(departments, eq(departments.id, positions.departmentId))
    .where(where)
    .orderBy(asc(people.employeeCode))
}

type RosterRow = Awaited<ReturnType<typeof roster>>[number]

function toRow(r: RosterRow): PersonRow {
  return {
    id: r.id,
    employeeCode: r.employeeCode,
    // `displayName` is what the roster spells, which for most people is Thai.
    // The two halves are the fallback and not the other way round: one person
    // in the export has no surname at all.
    name: r.displayName ?? `${r.firstName} ${r.lastName}`.trim(),
    username: r.username,
    hasLogin: r.authUserId !== null,
    archived: r.archivedAt !== null,
    position: r.position,
    department: r.department,
  }
}

/**
 * The roster, searchable by the two things HR has in front of them.
 *
 * By name and by employee code, because the code is what is on the form on the
 * desk and the name is what is on the person. Not by username: somebody who has
 * forgotten theirs is exactly who HR is looking up, and asking for it would be
 * asking for the thing they came without.
 *
 * Archived people are included and marked. Somebody who left in March is who a
 * question about March is about, and a list that hides them makes the answer
 * "that person does not exist".
 */
export async function listPeople(query?: string): Promise<PersonRow[]> {
  const term = query?.trim()
  const needle = term ? `%${term}%` : null

  const rows = await roster(
    needle
      ? or(
          ilike(people.employeeCode, needle),
          ilike(people.firstName, needle),
          ilike(people.lastName, needle),
          ilike(people.displayName, needle),
        )
      : undefined,
  )

  return rows.map(toRow)
}

/** How many there are, for the "12 of 193" the narrowed list needs. */
export async function countPeople(): Promise<number> {
  const [row] = await db.select({ n: sql<number>`count(*)::int` }).from(people)
  return row?.n ?? 0
}

export async function personDetail(id: string): Promise<PersonRow | null> {
  if (!UUID.test(id)) return null
  const [row] = await roster(eq(people.id, id))
  return row ? toRow(row) : null
}

export type LoginResult =
  | { ok: true; password: string }
  | {
      ok: false
      reason:
        | "no_such_person"
        | "already_has_login"
        | "no_login_yet"
        | "username_taken"
        | "bad_username"
        | "archived"
    }

/**
 * Open a login for somebody who has none.
 *
 * The username is chosen here, by HR, and written to the person as well as to
 * the account. The person is the source: somebody exists in the roster before an
 * account is opened for them, and the sign-in screen needs the name on the
 * person to tell "no such name" from "no account yet".
 *
 * The password comes back once and is never stored legibly. Whoever calls this
 * shows it and forgets it.
 */
export async function openLogin(
  personId: string,
  rawUsername: string,
): Promise<LoginResult> {
  if (!UUID.test(personId)) return { ok: false, reason: "no_such_person" }
  const username = normaliseUsername(rawUsername)
  if (checkUsername(username)) return { ok: false, reason: "bad_username" }

  const [person] = await db
    .select({
      id: people.id,
      firstName: people.firstName,
      lastName: people.lastName,
      displayName: people.displayName,
      authUserId: people.authUserId,
      archivedAt: people.archivedAt,
    })
    .from(people)
    .where(eq(people.id, personId))

  if (!person) return { ok: false, reason: "no_such_person" }
  if (person.authUserId) return { ok: false, reason: "already_has_login" }
  // Opening a login for somebody who has left is not a mistake worth allowing
  // quietly: the sign-in hook would refuse them anyway, and HR would be left
  // with a password that does not work and no reason why.
  if (person.archivedAt) return { ok: false, reason: "archived" }

  const [taken] = await db
    .select({ id: people.id })
    .from(people)
    .where(eq(people.username, username))
  if (taken) return { ok: false, reason: "username_taken" }

  const password = generateFirstPassword()
  const name =
    person.displayName ?? `${person.firstName} ${person.lastName}`.trim()

  /**
   * The lookup above asks `people`, and a username can exist in the auth tables
   * without belonging to anybody in the roster. Better Auth then refuses by
   * throwing, which escaped as far as the server action and left HR looking at
   * the generic "no answer" message with no idea what went wrong.
   *
   * So the same refusal comes back the same way as the other five. The
   * difference matters most for the one case that put it there: an account
   * created through the open sign-up route, which is closed now but which
   * existed long enough for somebody to have used it.
   */
  let result: Awaited<ReturnType<typeof auth.api.signUpEmail>>
  try {
    result = await auth.api.signUpEmail({
      body: {
        // Derived and unreachable. See the note in lib/auth.ts.
        email: `${username}@advmfr.local`,
        password,
        name,
        username,
      },
    })
  } catch {
    return { ok: false, reason: "username_taken" }
  }

  await db
    .update(people)
    .set({ authUserId: result.user.id, username })
    .where(eq(people.id, personId))

  return { ok: true, password }
}

/**
 * Set a new password for somebody who has lost theirs.
 *
 * Hashed through Better Auth's own hasher, reached from `auth.$context`, so the
 * stored value is exactly what a sign-in will be checked against and nothing
 * here re-implements a credential. The hash goes on the `credential` provider
 * row in `account`, and on that row only: `account` holds one row per way of
 * signing in, so an update filtered by user alone would write a password hash
 * into every other row the moment a second method exists.
 */
export async function resetPassword(personId: string): Promise<LoginResult> {
  if (!UUID.test(personId)) return { ok: false, reason: "no_such_person" }

  const [person] = await db
    .select({ authUserId: people.authUserId })
    .from(people)
    .where(eq(people.id, personId))

  if (!person) return { ok: false, reason: "no_such_person" }
  if (!person.authUserId) return { ok: false, reason: "no_login_yet" }

  const password = generateFirstPassword()
  const context = await auth.$context
  const hash = await context.password.hash(password)

  const updated = await db
    .update(account)
    .set({ password: hash })
    .where(
      and(
        eq(account.userId, person.authUserId),
        eq(account.providerId, "credential"),
      ),
    )
    .returning({ id: account.id })

  if (updated.length === 0) return { ok: false, reason: "no_login_yet" }
  return { ok: true, password }
}

/**
 * Archive somebody who has left, or bring them back.
 *
 * Archiving is not deleting and never becomes deleting. Everything they did
 * stays exactly where it is; what changes is that the sign-in hook refuses them
 * from the next attempt, and `currentUser` refuses the ones already holding a
 * cookie.
 */
export async function setArchived(
  personId: string,
  archived: boolean,
): Promise<{ ok: boolean }> {
  if (!UUID.test(personId)) return { ok: false }

  const rows = await db
    .update(people)
    .set({ archivedAt: archived ? new Date() : null })
    .where(eq(people.id, personId))
    .returning({ id: people.id })
  return { ok: rows.length > 0 }
}