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

import { db } from "@/lib/db/client"
import {
  departments,
  people,
  positions,
  recurringTemplates,
} from "@/lib/db/schema"
import type { Choice } from "@/lib/choice"
import type { Chair } from "@/lib/job-description-view"
import { canWritePosition } from "@/lib/services/positions"
import type { SignedIn } from "@/lib/services/session"
import {
  RECURRING_OCCURRENCES,
  isDayOfMonth,
  isDeadline,
  isMinutes,
  isWeekday,
  occurrencesFor,
  type Occurrence,
  type Refusal,
} from "@/lib/task-form"

/**
 * Duties: the recurring work a position owes.
 *
 * A duty hangs off a position and never off a person, so when a holder leaves
 * the work does not leave with them. That is the whole reason positions are the
 * stable entity, and it is why nothing here takes a person as the owner.
 */

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

/**
 * Who may be given work by whom, answered on 3 August: a manager assigns within
 * their own department, an admin across every one of them.
 *
 * A staff member reaches exactly one position, their own. That is the whole of
 * open point G, which the client answered on the same day: they add recurring
 * work for themselves, and nothing here lets them add it for the colleague at
 * the next bench. It is one branch and not a second screen, because the thing
 * a staff member is doing is the thing this file already does.
 *
 * The scope is computed here rather than checked afterwards, because a list
 * filtered on the way out is a list somebody can widen by posting an id that
 * was never on it. Whatever comes back from here is what `createDuty` will
 * accept, and the two read the same condition.
 */
function withinScope(user: SignedIn) {
  const live = isNull(positions.archivedAt)
  if (user.tier === "admin") return live

  if (user.tier === "staff") {
    // Somebody between positions holds none, and reaches none. The same shape
    // of answer as the manager with no department below it.
    return and(
      live,
      user.positionId ? eq(positions.id, user.positionId) : sql`false`,
    )
  }

  return and(
    live,
    // A manager with no department can assign to nobody, which is correct: they
    // are between positions and have no team to give work to.
    user.department ? eq(departments.name, user.department) : sql`false`,
  )
}

export async function assignablePositions(user: SignedIn): Promise<Choice[]> {
  const rows = await db
    .select({
      id: positions.id,
      title: positions.title,
      department: departments.name,
    })
    .from(positions)
    .innerJoin(departments, eq(departments.id, positions.departmentId))
    .where(withinScope(user))
    .orderBy(asc(departments.name), asc(positions.title))

  return rows.map((r) => ({
    id: r.id,
    label: r.title,
    // An admin sees 193 titles across 46 departments, and several of them read
    // the same. The department is what tells two "หัวหน้างาน (Supervisor)"
    // apart, so it travels with the title rather than being left to guesswork.
    hint: r.department,
  }))
}

/**
 * Who can be named on a duty: the checker, and the verifier who audits a sample
 * of it afterwards.
 *
 * One list for both, because the scope is a property of the viewer and not of
 * the role: a manager names people from their own department whichever box they
 * are filling. Nothing here requires the checker to outrank the assignee, or the
 * verifier to be somebody other than the checker, because the specification says
 * neither. `lib/task-relations.ts` is explicit about the second: "the two-person
 * departments name the same person twice as a matter of course."
 */
export async function peopleInScope(user: SignedIn): 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), withinScope(user)))
    .orderBy(asc(people.firstName))

  return rows.map((r) => ({
    id: r.id,
    label: r.displayName ?? `${r.firstName} ${r.lastName}`.trim(),
    hint: `${r.title} · ${r.department}`,
  }))
}

export type NewDuty = {
  positionId: string
  description: string
  occurrence: "once" | "daily" | "weekly" | "monthly" | "quarterly" | "general"
  /** Only for `once`, as a Bangkok calendar day. */
  deadline?: string | null
  /** 1 Monday to 7 Sunday. Weekly only. */
  dayOfWeek?: number | null
  /** 1 to 31. Monthly and quarterly only. */
  dayOfMonth?: number | null
  checkerPersonId?: string | null
  /** Optional. A duty nobody audits is the normal case. */
  verifierPersonId?: string | null
  reportToDeliver?: string | null
  wiSopUrl?: string | null
  reportExampleUrl?: string | null
  /** Whole minutes, 1 or more. Feeds the workload sum. */
  timeToDoMinutes?: number | null
  customerTag?: string | null
}

export type CreateResult =
  { ok: true; id: string } | { ok: false; reason: Refusal }

/** 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
}

/**
 * Is this person one the viewer may name on a duty?
 *
 * Asked of the checker and of the verifier with the same query, because they
 * are the same question. The list the screen showed is a convenience; this is
 * the rule, and it is what stops a verifier id from another department arriving
 * in a request body that the screen never offered.
 */
async function nameable(user: SignedIn, personId: string): Promise<boolean> {
  const [person] = await db
    .select({ id: people.id })
    .from(people)
    .innerJoin(positions, eq(positions.id, people.positionId))
    .innerJoin(departments, eq(departments.id, positions.departmentId))
    .where(
      and(
        eq(people.id, personId),
        isNull(people.archivedAt),
        withinScope(user),
      ),
    )
  return Boolean(person)
}

export async function createDuty(
  user: SignedIn,
  duty: NewDuty,
): Promise<CreateResult> {
  // Asked again, on the way in. The list the screen showed is a convenience;
  // this is the rule. A manager who posts a position id from another department
  // is refused whether they did it by hand or by keeping a stale page open.
  const [allowed] = await db
    .select({ id: positions.id })
    .from(positions)
    .innerJoin(departments, eq(departments.id, positions.departmentId))
    .where(and(eq(positions.id, duty.positionId), withinScope(user)))

  if (!allowed) return { ok: false, reason: "position_out_of_scope" }

  // The client answered on 3 August that one-time self to-dos are not needed,
  // so a staff member gets the four occurrences that repeat and neither of the
  // other two. Asked here and not only drawn on the screen: the radio group a
  // staff member is served has four buttons, and a posted body has six.
  if (!occurrencesFor(user.tier).includes(duty.occurrence)) {
    return { ok: false, reason: "occurrence_not_allowed" }
  }

  if (duty.checkerPersonId && !(await nameable(user, duty.checkerPersonId))) {
    return { ok: false, reason: "checker_out_of_scope" }
  }

  // The verifier is optional and scoped exactly like the checker. Optional is
  // not the same as unchecked: a duty naming nobody is the ordinary case, and a
  // duty naming somebody the viewer cannot see is refused.
  if (duty.verifierPersonId && !(await nameable(user, duty.verifierPersonId))) {
    return { ok: false, reason: "verifier_out_of_scope" }
  }

  // Ranges the database also checks, asked here so the answer is a sentence
  // about the number instead of a constraint violation the screen reports as
  // silence. A monthly duty with no day at all lands here too: it is not 1 to
  // 31 either, and the row would have been rejected on the way in.
  const monthly =
    duty.occurrence === "monthly" || duty.occurrence === "quarterly"
  if (monthly && !isDayOfMonth(duty.dayOfMonth)) {
    return { ok: false, reason: "day_of_month_out_of_range" }
  }

  // The other two schedule fields, asked the same way and for the same reason.
  // The screen cannot send a bad one: the weekday is a radio group of seven and
  // the deadline a date box. That is not a reason to trust either, because a
  // Server Action is a public endpoint and the list it handed out is not the
  // rule. `dayOfWeek` of 99 reached a check constraint and a deadline of
  // "not-a-date" reached `new Date` and became an Invalid Date, and both came
  // back as "the system did not answer".
  if (duty.occurrence === "weekly" && !isWeekday(duty.dayOfWeek)) {
    return { ok: false, reason: "day_of_week_out_of_range" }
  }

  if (duty.occurrence === "once" && !isDeadline(duty.deadline)) {
    return { ok: false, reason: "deadline_not_a_date" }
  }

  const minutes = duty.timeToDoMinutes ?? null
  if (minutes !== null && !isMinutes(minutes)) {
    return { ok: false, reason: "time_to_do_not_a_number" }
  }

  // The schedule columns are set to exactly what the occurrence allows and null
  // everywhere else. The database checks the same thing, and would reject the
  // row rather than store a weekly duty with no day, which is a duty that is
  // never due and reports nothing.
  const [row] = await db
    .insert(recurringTemplates)
    .values({
      positionId: duty.positionId,
      description: duty.description,
      occurrence: duty.occurrence,
      deadline:
        duty.occurrence === "once" && duty.deadline
          ? new Date(`${duty.deadline}T00:00:00+07:00`)
          : null,
      dayOfWeek: duty.occurrence === "weekly" ? (duty.dayOfWeek ?? null) : null,
      dayOfMonth: monthly ? (duty.dayOfMonth ?? null) : null,
      checkerPersonId: duty.checkerPersonId ?? null,
      verifierPersonId: duty.verifierPersonId ?? null,
      reportToDeliver: trimmed(duty.reportToDeliver),
      wiSopUrl: trimmed(duty.wiSopUrl),
      reportExampleUrl: trimmed(duty.reportExampleUrl),
      timeToDoMinutes: minutes,
      customerTag: trimmed(duty.customerTag),
      createdBy: user.personId,
    })
    .returning({ id: recurringTemplates.id })

  return { ok: true, id: row!.id }
}

/** Every duty on a position, newest first, active and inactive together. */
export async function listDutiesForPosition(positionId: string) {
  return db
    .select({
      id: recurringTemplates.id,
      description: recurringTemplates.description,
      occurrence: recurringTemplates.occurrence,
      dayOfWeek: recurringTemplates.dayOfWeek,
      dayOfMonth: recurringTemplates.dayOfMonth,
      deadline: recurringTemplates.deadline,
      active: recurringTemplates.active,
    })
    .from(recurringTemplates)
    .where(eq(recurringTemplates.positionId, positionId))
    .orderBy(asc(recurringTemplates.createdAt))
}

export type OwnDuty = {
  id: string
  description: string
  occurrence: Occurrence
  dayOfWeek: number | null
  dayOfMonth: number | null
  active: boolean
}

/**
 * The recurring to-dos a staff member wrote for themselves.
 *
 * Exactly the set they may switch off, which is why it is one query and not a
 * list filtered afterwards: `setOwnDutyActive` asks the same two conditions
 * again, and a screen that offered a fifth row would offer a button that
 * answers no.
 *
 * Their manager's assignments are deliberately absent. Those are on the same
 * position and would pass a check that asked only about the position, and a
 * to-do list somebody can switch their job off from is not a to-do list.
 *
 * Staff, and empty for everybody else, which is what the client asked about on
 * 3 August. A manager's own duties are their job rather than their reminders,
 * and duties are edited on `/positions/[id]`. Handing a manager a stop button
 * over the work on their own position is a capability nobody asked for, and it
 * was on screen for one measurement before this line was written.
 *
 * The four that repeat only. A `once` duty is not what open point G asked for
 * and `general` never becomes work, so neither belongs on a screen about the
 * reminders somebody set themselves.
 */
export async function ownRecurringDuties(user: SignedIn): Promise<OwnDuty[]> {
  if (user.tier !== "staff" || !user.positionId) return []

  const rows = await db
    .select({
      id: recurringTemplates.id,
      description: recurringTemplates.description,
      occurrence: recurringTemplates.occurrence,
      dayOfWeek: recurringTemplates.dayOfWeek,
      dayOfMonth: recurringTemplates.dayOfMonth,
      active: recurringTemplates.active,
    })
    .from(recurringTemplates)
    .where(
      and(
        eq(recurringTemplates.positionId, user.positionId),
        eq(recurringTemplates.createdBy, user.personId),
        inArray(recurringTemplates.occurrence, [...RECURRING_OCCURRENCES]),
      ),
    )
    .orderBy(asc(recurringTemplates.createdAt))

  return rows.map((r) => ({ ...r, occurrence: r.occurrence as Occurrence }))
}

export type WriteResult =
  { ok: true } | { ok: false; reason: "out_of_scope" | "no_such_duty" }

/**
 * Stop a duty producing work, or start it again.
 *
 * Deactivating and never deleting. The instances a duty has already produced
 * are somebody's history, and `active` is exactly the switch the recurrence
 * engine reads: `isDueOn` returns false for an inactive template, so tomorrow
 * brings nothing new and yesterday keeps everything it had.
 *
 * The scope is asked here and not taken from the screen, which is the same rule
 * `createDuty` follows: a list narrowed on the way out is a list somebody
 * widens by posting an id.
 *
 * A staff member is held to a second condition: they wrote it. Nothing reaches
 * here as one today, since the only caller is `/positions/[id]`'s admin-guarded
 * action and their own to-dos go through `setOwnDutyActive`. It is written down
 * because `canWritePosition` now says yes to a staff member about their own
 * position, and the next action wired to this must not be the one that lets
 * somebody switch off the work they were assigned.
 */
export async function setDutyActive(
  user: SignedIn,
  dutyId: string,
  active: boolean,
): Promise<WriteResult> {
  const [duty] = await db
    .select({
      positionId: recurringTemplates.positionId,
      createdBy: recurringTemplates.createdBy,
    })
    .from(recurringTemplates)
    .where(eq(recurringTemplates.id, dutyId))

  if (!duty) return { ok: false, reason: "no_such_duty" }
  if (!(await canWritePosition(user, duty.positionId))) {
    return { ok: false, reason: "out_of_scope" }
  }
  if (user.tier === "staff" && duty.createdBy !== user.personId) {
    return { ok: false, reason: "out_of_scope" }
  }

  await db
    .update(recurringTemplates)
    .set({ active })
    .where(eq(recurringTemplates.id, dutyId))
  return { ok: true }
}

/**
 * The same switch, asked about a recurring to-do somebody set for themselves.
 *
 * Its own function rather than a tier inside `setDutyActive`, because the two
 * answer different questions. That one asks whether the caller may edit this
 * position, which for a manager is a whole department, and it is reached from
 * `/positions/[id]` behind an admin guard. This one is reached from
 * `/tasks/new` behind no guard at all, so it asks the narrowest question there
 * is: is this duty on your own position, and did you write it.
 *
 * The same two conditions `ownRecurringDuties` lists by, so what the screen
 * offers and what the service accepts cannot drift apart. Reusing the wider
 * function here would have let a manager stop any duty in their department by
 * posting an id, from a page that was never a write surface.
 */
export async function setOwnDutyActive(
  user: SignedIn,
  dutyId: string,
  active: boolean,
): Promise<WriteResult> {
  const mine = await ownRecurringDuties(user)
  if (!mine.some((duty) => duty.id === dutyId)) {
    // No `no_such_duty` here on purpose: whether a duty somebody may not touch
    // exists is not a question this has to answer.
    return { ok: false, reason: "out_of_scope" }
  }

  await db
    .update(recurringTemplates)
    .set({ active })
    .where(eq(recurringTemplates.id, dutyId))
  return { ok: true }
}

/**
 * The job description as prose.
 *
 * The duties below it are rows and this is the sentence that says what the job
 * is for. `machine/routes.json` puts both on the same page on purpose: a job
 * description kept somewhere else is a job description that disagrees with the
 * work.
 */
export async function setJobDescription(
  user: SignedIn,
  positionId: string,
  text: string,
): Promise<WriteResult> {
  if (!(await canWritePosition(user, positionId))) {
    return { ok: false, reason: "out_of_scope" }
  }
  await db
    .update(positions)
    // Empty means none, not an empty paragraph somebody has to delete later.
    .set({ jobDescription: text.trim() || null })
    .where(eq(positions.id, positionId))
  return { ok: true }
}

/* -------------------------------------------------------------------------- */
/* The overview                                                               */
/* -------------------------------------------------------------------------- */

/**
 * Every chair in the factory, with what describes it and who is sitting there.
 *
 * Two queries rather than one, because the two questions have different shapes:
 * a position has one job description and any number of holders, and folding
 * that into a single row means aggregating names into an array and taking them
 * apart again. Assembling them here costs one extra round trip and keeps both
 * halves readable.
 *
 * Inactive duties are not counted. Deactivating one stops it generating and
 * leaves its history alone, which makes it a duty the position no longer owes;
 * counting it would describe a job nobody does any more.
 *
 * The shaping — vacant chairs, chairs with two holders, the order — is not
 * here. It is in `lib/job-description-view.ts`, where it can be read and tested
 * without a database.
 */
export async function listChairs(): Promise<Chair[]> {
  const owed = (comparison: "=" | "<>") => sql<number>`(
    select count(*)::int
    from ${recurringTemplates}
    where ${recurringTemplates.positionId} = ${positions.id}
      and ${recurringTemplates.active}
      and ${recurringTemplates.occurrence} ${sql.raw(comparison)} 'general'
  )`

  const [chairs, holders] = await Promise.all([
    db
      .select({
        positionId: positions.id,
        title: positions.title,
        department: departments.name,
        jobDescription: positions.jobDescription,
        generalDuties: owed("="),
        recurringDuties: owed("<>"),
      })
      .from(positions)
      .innerJoin(departments, eq(departments.id, positions.departmentId)),

    db
      .select({
        id: people.id,
        positionId: people.positionId,
        firstName: people.firstName,
        lastName: people.lastName,
        displayName: people.displayName,
        archivedAt: people.archivedAt,
      })
      .from(people),
  ])

  const seated = new Map<string, Chair["holders"]>()
  for (const holder of holders) {
    if (!holder.positionId) continue
    const list = seated.get(holder.positionId) ?? []
    list.push({
      id: holder.id,
      // The same fallback as the roster screen, and for the same reason: one
      // person in the export has their whole name in the given name column.
      name:
        holder.displayName ?? `${holder.firstName} ${holder.lastName}`.trim(),
      archived: holder.archivedAt !== null,
    })
    seated.set(holder.positionId, list)
  }

  return chairs.map((chair) => ({
    ...chair,
    holders: seated.get(chair.positionId) ?? [],
  }))
}