import { eq } from "drizzle-orm"
import { alias } from "drizzle-orm/pg-core"

import { dayIn } from "@/lib/calendar"
import { db } from "@/lib/db/client"
import {
  departments,
  people,
  positions,
  recurringTemplates,
  taskInstances,
  taskSubmissions,
} from "@/lib/db/schema"
import {
  availableActions,
  isLateOnDay,
  type Relation,
  type TaskAction,
  type TaskStatus,
} from "@/lib/task-actions"
import type { Occurrence } from "@/lib/task-form"
import { relationsOf } from "@/lib/task-relations"
import type { SignedIn } from "@/lib/services/session"

/**
 * One task, with everything `/tasks/[id]` shows and everything it needs to
 * decide what the viewer may press.
 *
 * ## What this does not decide
 *
 * Existence, and nothing else. Whether this person may LOOK at this task is a
 * different question from whether they may ACT on it, and only the second is
 * answered here, by `relations` and `actions`. The page answers the first,
 * because the two have different right answers: a verifier auditing a sample and
 * a manager reading their department's work both have to open tasks they can do
 * nothing at all with, and a service that returned null for them would make the
 * task look deleted rather than read-only.
 *
 * That is not a hole in invariant 3. The scope is still asked twice; it is asked
 * on the way in by the page and again by every server action, and neither of
 * them trusts a list. What this returns is a description, and the description of
 * a task nobody may touch carries an empty `actions` array.
 */

export type TaskPerson = { id: string; name: string }

/**
 * What the assignee handed in when they marked the task finished.
 *
 * `finish` has been writing this row since the verb existed and nothing read it,
 * so a checker was shown Approve and Send back with the history saying "handed
 * in" and stopping. This is the row, and the reason it is a separate type rather
 * than three nullable fields on `TaskDetail` is that the three arrive together or
 * not at all: `note` is NOT NULL, `submitted_by` is NOT NULL, and there is one
 * row per task.
 *
 * `readings`, `delivered_name`, `delivered_url` and `delivered_how` are on the
 * table and are not here. `finish` writes none of them and every one of the 637
 * rows has them null, so a field for them would be a promise the screen cannot
 * keep. What an assignee is actually asked to hand in is ELEVA-121 and still
 * open.
 */
export type TaskSubmissionDetail = {
  by: TaskPerson
  /** An instant, printed by the instant formatter and never the calendar one. */
  at: Date
  /**
   * Somebody's own words, in whichever language they typed them. Data, like a
   * name or a position title, so it is rendered as written and never looked up
   * in a dictionary.
   */
  note: string
}

export type TaskDetail = {
  id: string
  description: string
  /** A Bangkok calendar day, `YYYY-MM-DD`. Never an instant. */
  dueDate: string
  status: TaskStatus
  /** Computed at the moment somebody looks, and never stored. */
  late: boolean
  occurrence: Occurrence
  position: string
  department: string
  /** Null while the position has no holder. The task waits rather than vanishing. */
  assignee: TaskPerson | null
  /** Null only on a `general` duty, which never becomes a task. */
  checker: TaskPerson | null
  verifier: TaskPerson | null
  createdBy: TaskPerson
  startedAt: Date | null
  finishedAt: Date | null
  checkedAt: Date | null
  verifiedAt: Date | null
  sentBackAt: Date | null
  sentBackNote: string | null
  issueNote: string | null
  /**
   * Null until the task has been finished once, which is most tasks: 637 of the
   * instances carry one. A task still to do has nothing to say about itself, and
   * the page draws nothing rather than a card admitting it.
   */
  submission: TaskSubmissionDetail | null
  relations: Relation[]
  isOneTime: boolean
  actions: TaskAction[]
}

const assignee = alias(people, "assignee")
const checker = alias(people, "checker")
const verifier = alias(people, "verifier")
const creator = alias(people, "creator")
const submitter = alias(people, "submitter")

type NameParts = {
  id: string
  firstName: string
  lastName: string
  displayName: string | null
}

/**
 * A person's name for display, the same rule as the positions list: the English
 * name when somebody has taken the trouble, otherwise the roster's own spelling,
 * which for 183 of 193 people is Thai. Trimmed rather than joined blindly,
 * because one person on the roster has no surname.
 *
 * Takes the whole joined row, which Drizzle gives as null when a left join
 * matched nothing, so the vacant position and the missing verifier come back
 * here rather than being tested twice at the call site.
 */
function personOf(parts: NameParts | null): TaskPerson | null {
  if (!parts) return null
  return {
    id: parts.id,
    name: parts.displayName ?? `${parts.firstName} ${parts.lastName}`.trim(),
  }
}

/**
 * The id arrives from a url segment, where anybody can type anything, and the
 * column it is compared against is a `uuid`. Postgres answers a malformed one
 * with error 22P02 rather than an empty result, so without this the service
 * throws for exactly the input most likely to reach it, and `no_such_task`,
 * which the contract promises covers this, never happens.
 */
const UUID = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i

export async function taskDetail(
  user: SignedIn,
  id: string,
  // Passed in when the caller already has a day in hand, so a page cannot call
  // the same task late in one place and on time in another across midnight in
  // Bangkok.
  today: string = dayIn(new Date()),
): Promise<TaskDetail | null> {
  if (!UUID.test(id)) return null
  const [row] = await db
    .select({
      id: taskInstances.id,
      dueDate: taskInstances.dueDate,
      status: taskInstances.status,
      startedAt: taskInstances.startedAt,
      finishedAt: taskInstances.finishedAt,
      checkedAt: taskInstances.checkedAt,
      verifiedAt: taskInstances.verifiedAt,
      sentBackAt: taskInstances.sentBackAt,
      sentBackNote: taskInstances.sentBackNote,
      issueNote: taskInstances.issueNote,
      description: recurringTemplates.description,
      occurrence: recurringTemplates.occurrence,
      position: positions.title,
      department: departments.name,
      // The same four columns five times. A helper would be shorter and would
      // not typecheck: `alias` writes the alias name into the table's type, so
      // these are five unrelated types with nothing to be generic over.
      assignee: {
        id: assignee.id,
        firstName: assignee.firstName,
        lastName: assignee.lastName,
        displayName: assignee.displayName,
      },
      checker: {
        id: checker.id,
        firstName: checker.firstName,
        lastName: checker.lastName,
        displayName: checker.displayName,
      },
      verifier: {
        id: verifier.id,
        firstName: verifier.firstName,
        lastName: verifier.lastName,
        displayName: verifier.displayName,
      },
      creator: {
        id: creator.id,
        firstName: creator.firstName,
        lastName: creator.lastName,
        displayName: creator.displayName,
      },
      submittedAt: taskSubmissions.submittedAt,
      submissionNote: taskSubmissions.note,
      submitter: {
        id: submitter.id,
        firstName: submitter.firstName,
        lastName: submitter.lastName,
        displayName: submitter.displayName,
      },
    })
    .from(taskInstances)
    .innerJoin(
      recurringTemplates,
      eq(recurringTemplates.id, taskInstances.recurringTemplateId),
    )
    .innerJoin(positions, eq(positions.id, recurringTemplates.positionId))
    .innerJoin(departments, eq(departments.id, positions.departmentId))
    // The three people a duty may name are left joins: an instance exists while
    // the position is vacant, a `general` duty names no checker, and a verifier
    // is optional everywhere.
    .leftJoin(assignee, eq(assignee.id, taskInstances.assigneePersonId))
    .leftJoin(checker, eq(checker.id, recurringTemplates.checkerPersonId))
    .leftJoin(verifier, eq(verifier.id, recurringTemplates.verifierPersonId))
    // The creator is an inner join, and can be: `created_by` is NOT NULL and
    // references people, and nothing in this database is ever deleted, so the
    // row is always there. Somebody who has left is archived, not gone.
    .innerJoin(creator, eq(creator.id, recurringTemplates.createdBy))
    // The hand-in, and the person who made it. A left join and not a second
    // query: `task_instance_id` is unique, so this can add no row and drop none,
    // and one round trip cannot come back describing a task and a submission
    // that belong to different moments.
    .leftJoin(
      taskSubmissions,
      eq(taskSubmissions.taskInstanceId, taskInstances.id),
    )
    .leftJoin(submitter, eq(submitter.id, taskSubmissions.submittedBy))
    .where(eq(taskInstances.id, id))

  // An id that names nothing. The page turns this into a 404 rather than an
  // apology, because there is no task to say anything about.
  if (!row) return null

  const status = row.status as TaskStatus
  const assigned = personOf(row.assignee)
  const checks = personOf(row.checker)
  const verifies = personOf(row.verifier)
  const createdBy: TaskPerson = {
    id: row.creator.id,
    name:
      row.creator.displayName ??
      `${row.creator.firstName} ${row.creator.lastName}`.trim(),
  }

  /*
    Built from the three columns together rather than from any one of them. All
    three are NOT NULL on a row that exists, so they are present or absent as a
    set; testing `submittedAt` alone would typecheck and would leave the other
    two to be asserted non-null somewhere further from the query.

    The submitter goes through `personOf` like everybody else, so the one person
    on the roster with no surname is not printed with a trailing space here
    either.
  */
  const submittedBy = personOf(row.submitter)
  const submission: TaskSubmissionDetail | null =
    submittedBy && row.submittedAt && row.submissionNote !== null
      ? { by: submittedBy, at: row.submittedAt, note: row.submissionNote }
      : null

  // The same three names the screen shows decide what the viewer may press, so
  // they are read once. A second query answering the permission separately is a
  // second query that can disagree with the first.
  const { relations, isOneTime } = relationsOf({
    viewerPersonId: user.personId,
    viewerTier: user.tier,
    viewerDepartment: user.department,
    assigneePersonId: assigned?.id ?? null,
    checkerPersonId: checks?.id ?? null,
    verifierPersonId: verifies?.id ?? null,
    createdByPersonId: createdBy.id,
    taskDepartment: row.department,
    occurrence: row.occurrence,
  })

  return {
    id: row.id,
    description: row.description,
    dueDate: row.dueDate,
    status,
    late: isLateOnDay(status, row.dueDate, today),
    occurrence: row.occurrence,
    position: row.position,
    department: row.department,
    assignee: assigned,
    checker: checks,
    verifier: verifies,
    createdBy,
    startedAt: row.startedAt,
    finishedAt: row.finishedAt,
    checkedAt: row.checkedAt,
    verifiedAt: row.verifiedAt,
    sentBackAt: row.sentBackAt,
    sentBackNote: row.sentBackNote,
    issueNote: row.issueNote,
    submission,
    relations,
    isOneTime,
    // `verified` matters because the status does not move when a sample audit
    // happens, so nothing else can tell that it already has. Without it, Verify
    // stays on screen afterwards and a second press overwrites who audited the
    // task and when, which is the one record the audit exists to leave.
    actions: availableActions(status, relations, {
      oneTime: isOneTime,
      verified: row.verifiedAt !== null,
    }),
  }
}