import { and, asc, eq, ilike, inArray, or, sql } from "drizzle-orm"

import type { SignedIn } from "@/lib/services/session"

import { db } from "@/lib/db/client"
import {
  departments,
  occurrence,
  people,
  positions,
  recurringTemplates,
  type positionLevel,
} from "@/lib/db/schema"

/**
 * Reading positions.
 *
 * The first service, so it sets the shape: screens ask for what they display
 * and get plain data back. No Drizzle leaves this file, and the row type owes
 * nothing to the table layout. The lint rule in eslint.config.mjs is what keeps
 * that true once there are twenty of these.
 *
 * The filtering happens in SQL rather than over a fetched array. At 193 rows
 * either would work; the point is that the query is the thing that has to be
 * right when there are ten times as many, and discovering that later means
 * rewriting the screen as well as the service.
 */

export type Level = (typeof positionLevel.enumValues)[number]

export type PositionRow = {
  id: string
  title: string
  department: string
  level: Level
  /**
   * Everybody holding this position. A list and not one name: the schema
   * deliberately allows several holders, because a real production line puts
   * ten operators on one position, and a screen that shows the first of them
   * as though they were the only one is how that gets missed.
   */
  holders: string[]
  duties: number
  inactiveDuties: number
}

export type PositionFilters = {
  /** Matches a position title or a holder's name. */
  query?: string
  /** Department names, as they appear on screen. Empty means all of them. */
  departments?: string[]
  vacantOnly?: boolean
}

/**
 * What the person looking is allowed to see, from routes.json: "THE single
 * positions list. Manager tier: own department. Admin: all departments."
 *
 * The same shape as `withinScope` in duties.ts, and deliberately not shared
 * with it: this one decides what may be read and that one what may be assigned
 * to. They agree today and there is no reason they always must, and a single
 * helper serving both would make the day they diverge a surprise.
 *
 * A manager with no position has no department and therefore no list. That is
 * the honest answer: they are between jobs and there is no team whose work is
 * theirs to see.
 *
 * A staff member reaches their own position and no other, which is open point
 * H's conservative default written down, and which is narrower than the
 * department they were getting. Nothing changes on screen today: every page
 * behind this is `requireAtLeast("admin")`. It is written now because
 * `canWritePosition` reads it, and a staff member creating their own recurring
 * to-dos is the first time somebody below manager has had a reason to.
 */
function visibleTo(user: SignedIn) {
  if (user.tier === "admin") return sql`true`
  if (user.tier === "staff") {
    return user.positionId ? eq(positions.id, user.positionId) : sql`false`
  }
  return user.department ? eq(departments.name, user.department) : sql`false`
}

/**
 * A holder's name for display. The roster is Thai for 183 of 193 people and one
 * person has no surname at all, so this joins what is there and trims rather
 * than assuming two parts.
 */
/**
 * The id arrives from a url segment and the column is a `uuid`, so a malformed
 * one is answered by Postgres with an error rather than an empty result. The
 * same guard as `taskDetail`, and for the same reason.
 */
const UUID = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i

const holderName = sql<string>`btrim(${people.firstName} || ' ' || coalesce(${people.lastName}, ''))`

export async function listPositions(
  user: SignedIn,
  filters: PositionFilters = {},
): Promise<PositionRow[]> {
  const { query, departments: within = [], vacantOnly = false } = filters

  const holders = sql<
    string[]
  >`coalesce(array_agg(${holderName}) filter (where ${people.id} is not null), '{}')`

  // Counted with `distinct` because the join to people multiplies the template
  // rows: a position with three holders would otherwise report three times its
  // duties. This is the mistake that looks like a data problem for a week.
  const duties = sql<number>`count(distinct ${recurringTemplates.id}) filter (where ${recurringTemplates.active})`
  const inactiveDuties = sql<number>`count(distinct ${recurringTemplates.id}) filter (where not ${recurringTemplates.active})`

  const where = [sql`${positions.archivedAt} is null`, visibleTo(user)]
  if (within.length) where.push(inArray(departments.name, within))
  if (query?.trim()) {
    const needle = `%${query.trim()}%`
    // The holder half is an EXISTS and not a condition on the joined row. Put
    // it in the WHERE and the aggregate only ever sees the holders who matched:
    // searching one operator's name on a position held by three would list that
    // one and quietly drop the other two.
    where.push(
      or(
        ilike(positions.title, needle),
        sql`exists (
          select 1 from ${people}
          where ${people.positionId} = ${positions.id}
            and ${people.archivedAt} is null
            and btrim(${people.firstName} || ' ' || coalesce(${people.lastName}, '')) ilike ${needle}
        )`,
      )!,
    )
  }

  const rows = await db
    .select({
      id: positions.id,
      title: positions.title,
      department: departments.name,
      level: positions.level,
      holders,
      duties,
      inactiveDuties,
    })
    .from(positions)
    .innerJoin(departments, eq(departments.id, positions.departmentId))
    .leftJoin(
      people,
      and(
        eq(people.positionId, positions.id),
        sql`${people.archivedAt} is null`,
      ),
    )
    .leftJoin(
      recurringTemplates,
      eq(recurringTemplates.positionId, positions.id),
    )
    .where(and(...where))
    .groupBy(positions.id, departments.name)
    .orderBy(asc(departments.name), asc(positions.title))

  // Vacancy is filtered here and not in SQL: it is a property of the aggregate,
  // and a HAVING clause on a filtered aggregate is harder to read than one line
  // of TypeScript for no gain at this size.
  const shaped = rows.map((r) => ({
    ...r,
    holders: r.holders ?? [],
    duties: Number(r.duties),
    inactiveDuties: Number(r.inactiveDuties),
  }))

  return vacantOnly ? shaped.filter((r) => r.holders.length === 0) : shaped
}

/** Every department that has a position, in the order a list should show them. */
export async function listDepartmentNames(user: SignedIn): Promise<string[]> {
  // A manager's department filter offers one department, which is theirs. It
  // stays on screen rather than being hidden: a filter that vanishes reads as a
  // missing feature, and one that says "Accessory Center" says plainly whose
  // list this is.
  const rows = await db
    .select({ name: departments.name })
    .from(departments)
    .where(
      and(
        sql`${departments.archivedAt} is null`,
        user.tier === "admin"
          ? sql`true`
          : user.department
            ? eq(departments.name, user.department)
            : sql`false`,
      ),
    )
    .orderBy(asc(departments.name))
  return rows.map((r) => r.name)
}

/** How many positions nobody holds. Shown whether or not the list is narrowed. */
export async function countVacantPositions(user: SignedIn): Promise<number> {
  const [row] = await db
    .select({ n: sql<number>`count(*)` })
    .from(positions)
    .innerJoin(departments, eq(departments.id, positions.departmentId))
    .where(
      and(
        sql`${positions.archivedAt} is null`,
        visibleTo(user),
        sql`not exists (select 1 from ${people} where ${people.positionId} = ${positions.id} and ${people.archivedAt} is null)`,
      ),
    )
  return Number(row?.n ?? 0)
}

export async function countPositions(user: SignedIn): Promise<number> {
  const [row] = await db
    .select({ n: sql<number>`count(*)` })
    .from(positions)
    .innerJoin(departments, eq(departments.id, positions.departmentId))
    .where(and(sql`${positions.archivedAt} is null`, visibleTo(user)))
  return Number(row?.n ?? 0)
}

/* -------------------------------------------------------------------------- */
/* One position                                                               */
/* -------------------------------------------------------------------------- */

export type PositionDuty = {
  id: string
  description: string
  occurrence: (typeof occurrence.enumValues)[number]
  dayOfWeek: number | null
  dayOfMonth: number | null
  minutes: number | null
  active: boolean
  checker: string | null
}

export type PositionDetail = {
  id: string
  title: string
  department: string
  level: Level
  jobDescription: string | null
  holders: { id: string; name: string; employeeCode: string }[]
  duties: PositionDuty[]
}

/**
 * One position, everything on it.
 *
 * `routes.json` calls this page "THE position page = job-description editor and
 * the only write surface", and says of the two read projections that "if a
 * projection displays something different, the projection is the bug". So this
 * returns the duties whether they are active or not: a deactivated duty is
 * still part of what this position is, and hiding it here would leave nowhere
 * to turn it back on.
 *
 * `visibleTo` again, the same condition the list uses. Reading one position and
 * reading a page of them is the same question asked about a different number of
 * rows.
 */
export async function positionDetail(
  user: SignedIn,
  id: string,
): Promise<PositionDetail | null> {
  if (!UUID.test(id)) return null

  const [row] = await db
    .select({
      id: positions.id,
      title: positions.title,
      department: departments.name,
      level: positions.level,
      jobDescription: positions.jobDescription,
    })
    .from(positions)
    .innerJoin(departments, eq(departments.id, positions.departmentId))
    .where(
      and(
        eq(positions.id, id),
        sql`${positions.archivedAt} is null`,
        visibleTo(user),
      ),
    )

  if (!row) return null

  const holders = await db
    .select({
      id: people.id,
      firstName: people.firstName,
      lastName: people.lastName,
      displayName: people.displayName,
      employeeCode: people.employeeCode,
    })
    .from(people)
    .where(and(eq(people.positionId, id), sql`${people.archivedAt} is null`))
    .orderBy(asc(people.firstName))

  const duties = await db
    .select({
      id: recurringTemplates.id,
      description: recurringTemplates.description,
      occurrence: recurringTemplates.occurrence,
      dayOfWeek: recurringTemplates.dayOfWeek,
      dayOfMonth: recurringTemplates.dayOfMonth,
      minutes: recurringTemplates.timeToDoMinutes,
      active: recurringTemplates.active,
      checker: sql<
        string | null
      >`(select btrim(c.first_name || ' ' || coalesce(c.last_name, '')) from people c where c.id = ${recurringTemplates.checkerPersonId})`,
    })
    .from(recurringTemplates)
    .where(eq(recurringTemplates.positionId, id))
    .orderBy(asc(recurringTemplates.createdAt))

  return {
    ...row,
    holders: holders.map((h) => ({
      id: h.id,
      name: h.displayName ?? `${h.firstName} ${h.lastName}`.trim(),
      employeeCode: h.employeeCode,
    })),
    duties,
  }
}

/**
 * The same shape of guard as the read, asked before a write.
 *
 * Exported because `lib/services/duties.ts` writes to a position and must ask
 * the same question rather than a similar one.
 */
export async function canWritePosition(
  user: SignedIn,
  id: string,
): Promise<boolean> {
  const [row] = await db
    .select({ id: positions.id })
    .from(positions)
    .innerJoin(departments, eq(departments.id, positions.departmentId))
    .where(and(eq(positions.id, id), visibleTo(user)))
  return Boolean(row)
}