import { and, asc, desc, eq, isNull, ne, sql } from "drizzle-orm"

import type { Choice } from "@/lib/choice"
import type { Cover } from "@/lib/cover"
import type { SignedIn } from "@/lib/services/session"

import { isIsoDate } from "@/lib/calendar"
import { db } from "@/lib/db/client"
import { departments, people, positionCovers, positions } from "@/lib/db/schema"

/**
 * Opening, ending and listing temporary cover.
 *
 * A cover says that for a stretch of Bangkok calendar days the duties of one
 * position are assigned to somebody who does not hold it. Which day that
 * actually changes anything on is decided by `lib/cover.ts`, which is pure and
 * tested without a database; this file reads and writes rows and does no
 * thinking about dates beyond refusing the ones that are not dates.
 */

export type { Choice } from "@/lib/choice"

/* -------------------------------------------------------------------------- */
/* Who may do this                                                            */
/* -------------------------------------------------------------------------- */

/**
 * **A manager over their own department, an admin anywhere, staff never.**
 *
 * A cover moves somebody else's work to a third person for a fortnight. That is
 * a decision about who answers for a job, which is what a manager is for and
 * what a staff member is not: `withinScope` in duties.ts gives staff their own
 * position because open point G lets them write their own reminders, and
 * `visibleTo` in positions.ts gives them the same for the same reason. Neither
 * reading survives being applied here, where the branch would let somebody hand
 * their own duties to a colleague and walk away from them.
 *
 * So this is its own condition rather than a call to either of those. The three
 * agree about admins and about managers and disagree about staff, and one
 * shared helper would make the day one of them has to move a surprise in the
 * other two.
 *
 * `/positions/[id]` is `requireAtLeast("admin")` today, so in practice no
 * manager reaches the screen at all. That is not the permission. The page guard
 * is one line that ELEVA-139 may move; this is the rule, and a Server Action is
 * a public endpoint whichever page happens to link to it.
 *
 * Archived positions are out for everybody, admins included. There is no job
 * left to stand in for, and the row would sit in the table taking part in the
 * overlap constraint for a position that no longer exists.
 */
function withinScope(user: SignedIn) {
  const live = isNull(positions.archivedAt)
  if (user.tier === "admin") return live
  if (user.tier === "staff") return sql`false`

  // A manager with no department covers nobody, which is correct: they are
  // between positions and there is no team whose work is theirs to move.
  return and(
    live,
    user.department ? eq(departments.name, user.department) : sql`false`,
  )
}

/**
 * The same question asked on the way in, and it is asked every time.
 *
 * The screen the caller was served is a convenience. This is the rule, and it
 * is what stops a position id from another department arriving in a request
 * body the screen never offered. `createDuty` does exactly this and says the
 * same thing about it.
 */
async function mayCover(user: SignedIn, positionId: 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, positionId), withinScope(user)))
  return Boolean(row)
}

/* -------------------------------------------------------------------------- */
/* Reading                                                                    */
/* -------------------------------------------------------------------------- */

/**
 * Every cover the generator might need to consider, flattened into the shape
 * the pure decision takes.
 *
 * **The whole table, with no date in the query, and that is the point.** The
 * generator asks this once per day it generates and hands the rows to
 * `dutyHolderOn`, which decides which of them is in force. Filtering here on
 * `from_date <= date AND until_date >= date` would move the inclusive boundary
 * into a WHERE clause, where the tests in lib/cover.test.ts cannot reach it and
 * where getting it wrong looks like nothing at all: the stand-in simply does not
 * receive the first or the last day's work.
 *
 * The cost is a table scan of a table that holds one row per temporary absence.
 * If it ever grows past the point where that is free, the filter that bounds it
 * belongs around the nightly job's whole window, not around a single day, and it
 * needs a test that proves the two boundary days still resolve the same way.
 *
 * Archived stand-ins are left out, and it is the only condition in the query. A
 * person who has left cannot receive tomorrow's work, and a cover naming them is
 * stale data rather than an instruction. Their name stays on every instance that
 * was already generated, because those record what somebody really did.
 */
export async function activeCovers(): Promise<Cover[]> {
  const rows = await db
    .select({
      positionId: positionCovers.positionId,
      personId: positionCovers.coveringPersonId,
      pattern: people.workPattern,
      from: positionCovers.fromDate,
      until: positionCovers.untilDate,
    })
    .from(positionCovers)
    .innerJoin(people, eq(people.id, positionCovers.coveringPersonId))
    .where(isNull(people.archivedAt))

  return rows
}

/**
 * Everybody who could stand in for this position.
 *
 * **Not narrowed to the caller's department, and that is the decision rather
 * than an oversight.** Covering somebody is very often exactly a person from
 * another department: the schema says so in as many words on
 * `covering_person_id`, and the client described it. Narrowing this to the
 * caller's own department would refuse the ordinary case, and it would do it by
 * leaving names out of a list, which reads as "that person does not exist".
 *
 * The asymmetry is deliberate and it is where the authorisation actually lives:
 * **the position being covered is scoped, the person standing in is not.** A
 * manager decides about their own department's work and may reach across the
 * factory for somebody to do it; they cannot reach across the factory to move
 * another department's work. Naming a person is not a write against that
 * person, so there is nothing here for a scope to protect.
 *
 * Takes no `SignedIn` for that reason. A parameter this never reads would be a
 * guard somebody assumes is there.
 *
 * Archived people are left out, because `openCover` refuses them and a list
 * offering a name the write answers no to is a list that lies. The holders of
 * this position are left out for the same reason: `covers_own_position`.
 *
 * Somebody between two positions is absent, because the join to `positions` is
 * an inner one. That is what `peopleInScope` in duties.ts does with the same
 * query, and it is worth writing down rather than discovering: a person with no
 * position could stand in perfectly well, and the day somebody asks for that,
 * this join is the line to change.
 */
export async function standInsFor(positionId: string): Promise<Choice[]> {
  const rows = await db
    .select({
      id: people.id,
      firstName: people.firstName,
      lastName: people.lastName,
      displayName: people.displayName,
      title: positions.title,
      department: departments.name,
    })
    .from(people)
    .innerJoin(positions, eq(positions.id, people.positionId))
    .innerJoin(departments, eq(departments.id, positions.departmentId))
    .where(
      and(
        isNull(people.archivedAt),
        isNull(positions.archivedAt),
        ne(people.positionId, positionId),
      ),
    )
    .orderBy(asc(departments.name), asc(people.firstName))

  return rows.map((r) => ({
    id: r.id,
    // 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.
    label: r.displayName ?? `${r.firstName} ${r.lastName}`.trim(),
    // 193 people over 46 departments and 147 distinct titles: the hint is what
    // tells two people with the same first name apart in a list of that size.
    hint: `${r.title} · ${r.department}`,
  }))
}

export type CoverRow = {
  id: string
  positionId: string
  positionTitle: string
  coveringPersonId: string
  coveringPerson: string
  /**
   * The job the stand-in normally does, which is not this one.
   *
   * On screen beside their name, because 193 people over 46 departments produce
   * repeated first names and because it is the thing that makes the line read
   * as a fact rather than a name: "the CAD designer is answering for the stock
   * line leader this fortnight". Null while they are between two positions.
   */
  coveringPersonPosition: string | null
  fromDate: string
  untilDate: string
  reason: string | null
}

/**
 * Every cover ever opened on a position, the window that starts latest first.
 *
 * Past ones included. A cover is a record of who was answering for this job in
 * some week, which is exactly the question somebody asks months later about a
 * task that was done badly, and hiding the finished ones would leave that
 * question unanswerable.
 */
export async function coversForPosition(
  positionId: string,
): Promise<CoverRow[]> {
  const rows = await db
    .select({
      id: positionCovers.id,
      positionId: positionCovers.positionId,
      positionTitle: positions.title,
      coveringPersonId: positionCovers.coveringPersonId,
      firstName: people.firstName,
      lastName: people.lastName,
      displayName: people.displayName,
      // A subquery and not a second join to `positions`. The table is already
      // joined once, to the position being covered, and joining it again under
      // an alias to read a title makes the row this function returns depend on
      // which of the two aliases a later editor reaches for.
      coveringPersonPosition: sql<string | null>`(
        select p.title from positions p where p.id = ${people.positionId}
      )`,
      fromDate: positionCovers.fromDate,
      untilDate: positionCovers.untilDate,
      reason: positionCovers.reason,
    })
    .from(positionCovers)
    .innerJoin(positions, eq(positions.id, positionCovers.positionId))
    .innerJoin(people, eq(people.id, positionCovers.coveringPersonId))
    .where(eq(positionCovers.positionId, positionId))
    .orderBy(desc(positionCovers.fromDate), asc(positionCovers.untilDate))

  return rows.map((r) => ({
    id: r.id,
    positionId: r.positionId,
    positionTitle: r.positionTitle,
    coveringPersonId: r.coveringPersonId,
    // 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.
    coveringPerson: r.displayName ?? `${r.firstName} ${r.lastName}`.trim(),
    coveringPersonPosition: r.coveringPersonPosition,
    fromDate: r.fromDate,
    untilDate: r.untilDate,
    reason: r.reason,
  }))
}

/* -------------------------------------------------------------------------- */
/* Writing                                                                    */
/* -------------------------------------------------------------------------- */

export type NewCover = {
  positionId: string
  coveringPersonId: string
  /** Inclusive, `YYYY-MM-DD`, Bangkok. */
  fromDate: string
  /** Inclusive, `YYYY-MM-DD`, Bangkok. */
  untilDate: string
  reason?: string | null
}

/**
 * Why a cover was refused, as a key and never as a sentence.
 *
 * 183 of the 193 people here read Thai. A service that returned "That person
 * already holds this position" would have chosen the language before knowing
 * who was going to read it, which is the same mistake `lib/notice.ts` exists to
 * avoid for notifications. The caller has the reader.
 */
export type CoverRefusal =
  /**
   * One key for two facts, the way `createDuty` answers with
   * `position_out_of_scope`: the position is not there, or it is not the
   * caller's to touch. The sentence the screen already has for this says both
   * ("That position is not one you can change. Reload the page.") because both
   * are answered the same way, and telling them apart would answer "does this
   * id exist" for somebody who is not allowed to ask.
   */
  | "position_out_of_scope"
  | "no_such_person"
  | "covers_own_position"
  | "window_ends_before_it_starts"
  | "not_a_calendar_day"
  | "overlaps_an_existing_cover"

export type OpenCoverResult =
  { ok: true; id: string } | { ok: false; reason: CoverRefusal }

/** Nothing typed is nothing stored, rather than an empty string to clean up later. */
function trimmed(value: string | null | undefined): string | null {
  return value?.trim() || null
}

/**
 * SQLSTATE 23P01, `exclusion_violation`: the only thing the exclusion
 * constraint on `position_covers` can throw, and the answer to "somebody is
 * already covering this position on one of those days".
 *
 * Caught by code and not by message, because the message is a sentence Postgres
 * composes and localises. Anything without that code is a real failure and is
 * rethrown, because swallowing an unknown database error here would report a
 * broken table as a busy one.
 *
 * **The chain is walked, and that is not defensive coding.** The `postgres`
 * driver puts `code` on the error it throws, and Drizzle 1.0 then wraps it in a
 * `DrizzleQueryError` carrying the SQL and the parameters, with the driver's
 * error underneath as `cause`. Reading `code` off the error Drizzle hands back
 * finds nothing, so the refusal escapes as an unhandled exception with the
 * parameters printed in it. Measured: it did exactly that the first time this
 * ran against the real database.
 */
function isOverlap(error: unknown): boolean {
  for (let at = error; at; at = (at as { cause?: unknown }).cause) {
    if (typeof at !== "object") return false
    if ("code" in at && (at as { code?: string }).code === "23P01") return true
  }
  return false
}

/**
 * Open a cover.
 *
 * The window is inclusive at both ends and the ends are refused before the
 * database sees them, so that a screen gets a reason it can say rather than a
 * constraint violation it reports as silence. The database checks the ordering
 * again anyway, for the row that did not come through here.
 *
 * The overlap is the other way round: it is checked by the database and only
 * translated here. A read-then-write in this function cannot see the other
 * transaction, so two people opening a cover on the same position at the same
 * moment would both find the table empty and both write.
 *
 * The scope is asked first, before the dates. Somebody who may not touch this
 * position learns nothing about it, not even whether the days they sent were
 * days, and the answer is the same whatever else was in the body.
 */
export async function openCover(
  user: SignedIn,
  cover: NewCover,
): Promise<OpenCoverResult> {
  if (!(await mayCover(user, cover.positionId))) {
    return { ok: false, reason: "position_out_of_scope" }
  }

  if (!isIsoDate(cover.fromDate) || !isIsoDate(cover.untilDate)) {
    return { ok: false, reason: "not_a_calendar_day" }
  }

  // A window that ends before it starts is in force on no day at all: it stores
  // cleanly, generates nothing, reports nothing, and is discovered when somebody
  // asks why the stand-in never got the work.
  if (cover.untilDate < cover.fromDate) {
    return { ok: false, reason: "window_ends_before_it_starts" }
  }

  /**
   * The stand-in is checked for being a real, present person and for nothing
   * else, and the omission is the decision.
   *
   * **They are deliberately not held to the caller's department.** Standing in
   * for somebody very often means somebody from elsewhere in the factory, which
   * is what the schema says on `covering_person_id` and what the client
   * described. The scope in this file protects the position whose work is being
   * moved; naming a person is not a write against that person, so there is
   * nothing for a scope to protect on this side. `standInsFor` builds its list
   * on exactly the same two conditions, so what the screen offers and what this
   * accepts cannot drift apart.
   */
  const [person] = await db
    .select({ id: people.id, positionId: people.positionId })
    .from(people)
    .where(
      and(eq(people.id, cover.coveringPersonId), isNull(people.archivedAt)),
    )

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

  /**
   * Somebody may not stand in for the job they already do.
   *
   * Not merely pointless: it is the one input that makes the cover invisible
   * while changing the rules. The row would take over the resolution from the
   * holder branch and hand the work back to the same person, so nothing on any
   * screen would look different, and the next real cover on that position would
   * be refused as an overlap with a row nobody can see the effect of.
   */
  if (person.positionId === cover.positionId) {
    return { ok: false, reason: "covers_own_position" }
  }

  try {
    const [row] = await db
      .insert(positionCovers)
      .values({
        positionId: cover.positionId,
        coveringPersonId: cover.coveringPersonId,
        fromDate: cover.fromDate,
        untilDate: cover.untilDate,
        reason: trimmed(cover.reason),
        // From the session and never from the caller's body. Who opened a cover
        // is the one thing on this row nobody may choose for themselves.
        createdBy: user.personId,
      })
      .returning({ id: positionCovers.id })

    return { ok: true, id: row!.id }
  } catch (error) {
    if (isOverlap(error)) {
      return { ok: false, reason: "overlaps_an_existing_cover" }
    }
    throw error
  }
}

export type EndCoverResult =
  | { ok: true; outcome: "deleted" | "shortened" | "already_over" }
  | { ok: false; reason: "no_such_cover" | "position_out_of_scope" }

/**
 * End a cover, as of `today`.
 *
 * `today` is a parameter and never `new Date()` inside the function, the same
 * way `generateFor(date)` takes its day: ending a cover is a decision about a
 * calendar day in Bangkok, and a function that reads the clock cannot be tested
 * without moving one.
 *
 * ## Why one path deletes the row and the other does not
 *
 * Ending early is not a second mechanism. It is shortening the window, and the
 * two branches are the same rule applied to two different rows.
 *
 * **A cover that has already started is shortened to `today`.** Today's work has
 * already been generated and assigned to the stand-in, and that instance must
 * keep its assignee: it is a record of who was told to do it. So the window
 * keeps today and loses tomorrow, and tomorrow's generation goes back to the
 * holder.
 *
 * **A cover that has not started yet is deleted outright**, and this is the part
 * that looks inconsistent and is not. Nothing was ever generated from it, so
 * there is no history to protect, and shortening it instead would leave a row
 * saying somebody covered a stretch of days they never covered. The rule
 * elsewhere in this schema is archive and never delete, and it holds because
 * those rows record what somebody did. This one recorded an intention that was
 * withdrawn before it did anything.
 *
 * ## The scope
 *
 * Ending a cover is a write against the position it sits on, so it asks the
 * same question `openCover` asks, about the position on the row rather than
 * about a position id the caller sent. Two steps here rather than one scoped
 * query, which is what `setDutyActive` does with the same shape of act:
 * existence is answered first, and "not yours" second. Somebody holding a cover
 * id already knows the cover exists, so there is nothing left to withhold.
 */
export async function endCover(
  user: SignedIn,
  id: string,
  today: string,
): Promise<EndCoverResult> {
  const [existing] = await db
    .select({
      id: positionCovers.id,
      positionId: positionCovers.positionId,
      fromDate: positionCovers.fromDate,
      untilDate: positionCovers.untilDate,
    })
    .from(positionCovers)
    .where(eq(positionCovers.id, id))

  if (!existing) return { ok: false, reason: "no_such_cover" }
  if (!(await mayCover(user, existing.positionId))) {
    return { ok: false, reason: "position_out_of_scope" }
  }

  if (existing.fromDate > today) {
    await db.delete(positionCovers).where(eq(positionCovers.id, id))
    return { ok: true, outcome: "deleted" }
  }

  // Already finished. Writing `today` here would *extend* a window that closed
  // last week, which is the one way this function could hand work to somebody
  // who has stopped standing in.
  if (existing.untilDate <= today) return { ok: true, outcome: "already_over" }

  await db
    .update(positionCovers)
    .set({ untilDate: today })
    .where(eq(positionCovers.id, id))

  return { ok: true, outcome: "shortened" }
}