import { sql } from "drizzle-orm"
import {
  boolean,
  check,
  date,
  index,
  integer,
  jsonb,
  pgEnum,
  pgTable,
  smallint,
  text,
  timestamp,
  uniqueIndex,
  uuid,
} from "drizzle-orm/pg-core"

import { LOCALES } from "@/lib/i18n"
import { LEVELS } from "@/lib/levels"
import type { NoticeKey } from "@/lib/notice"

/**
 * The database behind the task and job-description system.
 *
 * PostgreSQL, decided in `machine/decisions.json` (`postgres-docker`), with
 * Drizzle 1.0. The tables here serve `lib/recurrence.ts` and `lib/calendar.ts`
 * rather than restate them: the rules about which duty is due on which day are
 * pure functions and stay there, and what the database owes them is a place to
 * put the answer that cannot hold the same answer twice.
 *
 * Two conventions run through the whole file.
 *
 * **Days are days, instants are instants.** A due date is a day on a wall
 * calendar in Bangkok, stored as `date`, read and written as a `YYYY-MM-DD`
 * string, which is exactly the type `lib/calendar.ts` works in. A timestamp is a
 * moment, stored as `timestamptz`. The two are never mixed, because a moment
 * silently rendered as a day is how a row gets dated a day early.
 *
 * **Nothing is ever deleted.** Rows are archived (`archived_at`) or deactivated
 * (`active`), per the specification's `AGENTS.md`. There is no `on delete
 * cascade` anywhere in this file, and that is deliberate: cascade is a delete.
 */

/* -------------------------------------------------------------------------- */
/* Enumerations                                                               */
/* -------------------------------------------------------------------------- */

/**
 * `machine/lifecycle.json`, exactly. Five statuses and no more.
 *
 * `verified` is deliberately absent. It is a random-sample audit that happens
 * *after* `checked`, not a step on the way to it: a task is complete at
 * `checked`, and whether somebody later audited it must not be able to change
 * that. So verification is recorded as a timestamp on the instance, never as a
 * status, and no task can be left sitting in a state that reads as incomplete
 * because an optional audit never happened.
 */
export const taskStatus = pgEnum("task_status", [
  "todo",
  "started",
  "finished",
  "checked",
  "missed",
])

/**
 * `machine/task-fields.tsv`, and the same six values as `lib/task-form.ts`.
 *
 * `general` is a duty listed in the job description that never generates an
 * instance. `once` is a single task with a deadline. The other four are what
 * `lib/recurrence.ts` calls a `Recurrence`.
 */
export const occurrence = pgEnum("occurrence", [
  "once",
  "daily",
  "weekly",
  "monthly",
  "quarterly",
  "general",
])

/**
 * The employee's week. It decides whether a Saturday exists for them at all,
 * which is why `lib/calendar.ts` takes it before it will answer whether a day
 * is a working day.
 *
 * Two values and not three, answered by the client on 4 August: "Monday to
 * Saturday is regular. Sunday is OT. Although we do a lot of Sunday OT, it is
 * not supposed to be a regular thing in any way." So Sunday is overtime and
 * never a pattern. Building it as one would put a recurring task on every
 * Sunday of the year for people who are there occasionally, which is the
 * opposite of what "not regular" means. An occasional Sunday is a one-time task
 * somebody assigns.
 *
 * Also answered at the same time: the six-day week is every week, and shifts do
 * not rotate. Either of those would have made this column the wrong shape
 * rather than a missing value.
 */
export const workPattern = pgEnum("work_pattern", ["mon_fri", "mon_sat"])

/**
 * The three tiers of `machine/roles.json`. The client's Manager, Assistant
 * Manager, Supervisor and Line Leader are all `manager`: they are position
 * titles, and the system only cares about the tier.
 */
export const accountRole = pgEnum("account_role", ["admin", "manager", "staff"])

/**
 * The client's own six ranks, kept as they write them.
 *
 * These are not the tier above and not the position title either: the source
 * spreadsheet carries Level in its own column, next to Positions, so a Line
 * Leader Accessory Stock is a Supervisor and says so. Collapsing the six into
 * the three at import would throw away the only place that distinction is
 * recorded, and it is the distinction an org chart is read for.
 *
 * `accountRole` is derived from this, never stored twice. See `tierOf`.
 */
export const positionLevel = pgEnum("position_level", LEVELS)

/**
 * The language somebody reads the interface in.
 *
 * Derived from the same list the dictionary is keyed on, so a third language
 * would be added in one place. The names and the position titles are not
 * translated by this: they are data, in Thai, and they stay as the roster
 * spells them.
 */
export const locale = pgEnum("locale", LOCALES)

/** The tier a rank grants. The one place the six become the three. */
export function tierOf(
  level: (typeof positionLevel.enumValues)[number],
): (typeof accountRole.enumValues)[number] {
  if (level === "admin") return "admin"
  if (level === "staff") return "staff"
  return "manager"
}

/* -------------------------------------------------------------------------- */
/* Organisation                                                               */
/* -------------------------------------------------------------------------- */

export const departments = pgTable("departments", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: text("name").notNull().unique(),
  // Archived, never deleted. A department that closes still owns the history
  // of everything its people did.
  archivedAt: timestamp("archived_at", { withTimezone: true }),
  createdAt: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
})

/**
 * The stable entity, per the glossary: a position owns the job description, the
 * duties, the SOP links and the history. People come and go through it.
 *
 * This is why duties hang off a position and never off a person. When a holder
 * leaves, the work does not leave with them, and nothing has to be recreated
 * for whoever arrives next.
 */
export const positions = pgTable(
  "positions",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    departmentId: uuid("department_id")
      .notNull()
      .references(() => departments.id),
    title: text("title").notNull(),
    /**
     * The rank this job carries, which is what decides the holder's tier. It
     * sits on the position and not on the person for the same reason the duties
     * do: the rank belongs to the job, and a new holder inherits it.
     */
    level: positionLevel("level").notNull().default("staff"),
    // The job description as prose. The duties that make it up are rows in
    // `recurring_templates`, including the `general` ones that never generate.
    jobDescription: text("job_description"),
    archivedAt: timestamp("archived_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => [
    uniqueIndex("positions_department_title_key").on(t.departmentId, t.title),
  ],
)

/**
 * A person. Their department is reached through their position, and is not
 * copied here: two places to read the same fact is one place to read it wrong
 * after a transfer.
 */
export const people = pgTable("people", {
  id: uuid("id").primaryKey().defaultRandom(),
  /**
   * The internal identifier, and not the login.
   *
   * Unique, and never reassigned: when somebody leaves, their code retires with
   * them, so every historical row keeps pointing at the person who did the work
   * rather than at whoever inherited the number.
   *
   * DECISION, corrected on 4 August. This was the login identifier for a day.
   * The client's written answer to their own question 10 says otherwise: "this
   * employee code will be an ID stored for later, for sync with old database",
   * and the code is a pillar of the accounting system it already lives in. So
   * it stays, it is linked to the person, and nobody types it to get in.
   *
   * The earlier note in this file said "the client answered on 3 August" for
   * the opposite decision. That answer came from our side, not from the client,
   * and recording it as theirs was wrong.
   */
  employeeCode: text("employee_code").notNull().unique(),
  /**
   * What somebody types to sign in, given to them when they are hired.
   *
   * Here and not only on Better Auth's `user`, which also holds one, because a
   * person exists in the roster long before an account is opened for them: 190
   * of the 193 have none today. Without a username on the person, somebody
   * typing theirs before HR has opened the account would be told no such user
   * exists, when the truth is that they exist and their account does not. That
   * is the commonest failure this screen has, and it is worth a column to
   * answer it properly.
   *
   * Nullable, because HR assigns it and has not yet. `db:provision` copies it
   * onto the account it creates; this row is the source and that one is the
   * copy.
   */
  username: text("username").unique(),
  firstName: text("first_name").notNull(),
  lastName: text("last_name").notNull(),
  /**
   * The same name written in English, for an interface that is bilingual and
   * for anybody who cannot read the Thai. Functional specification section 1:
   * "on your side you add everyone's name in English in your system".
   *
   * Nullable, because a person exists before somebody has taken the trouble.
   * Never an identifier: it is not unique and nothing resolves on it.
   */
  displayName: text("display_name"),
  /**
   * The position this person holds. `machine/roles.json`: one person holds
   * exactly one position, which a single column is exactly enough to enforce.
   * Nullable while somebody is between positions.
   *
   * DECISION. Not unique. The glossary speaks of "the holder" in the singular,
   * but nothing in the specification says a position may not have several
   * holders, and a real line has ten operators on one position. A unique
   * constraint here would reject that data outright, so the looser reading is
   * the safe one.
   */
  positionId: uuid("position_id").references(() => positions.id),
  /**
   * Six days by default, because that is the company's regular week, answered
   * on 4 August. It was five, which was the schema's own default standing in
   * for data nobody had supplied, and it silently removed every Saturday from
   * every person in the roster.
   */
  workPattern: workPattern("work_pattern").notNull().default("mon_sat"),
  // Somebody who has left. Their rows, their submissions and their history all
  // stay; only the person is archived.
  archivedAt: timestamp("archived_at", { withTimezone: true }),
  /**
   * The Better Auth user this person signs in as, when they have one.
   *
   * Nullable, and that is the normal state: the roster holds 193 people and an
   * account is created for a person when somebody decides they need one. A
   * position and its duties are tracked whether or not the holder ever signs in.
   *
   * The link lives here rather than as a `personId` on Better Auth's `user`
   * because that table is regenerated by its CLI whenever a plugin changes, and
   * a column we added there would be silently dropped.
   */
  authUserId: text("auth_user_id").unique(),
  /**
   * Which language they read the interface in.
   *
   * On the person and never in the browser. These are shared terminals: a
   * preference stored on the device is inherited by whoever walks up next, and
   * with 183 of 193 people reading Thai, the one who inherits it is usually the
   * one it is wrong for. Signed out there is no person, which is why the login
   * screen shows both languages and has no switcher at all.
   *
   * Thai by default, because that is what almost everybody here reads. The ten
   * who do not change it once and it stays changed.
   */
  language: locale("language").notNull().default("th"),
  createdAt: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
})

/**
 * There is no `accounts` table here any more.
 *
 * It held a password hash, a role and a link to a person, and Better Auth's own
 * tables in `auth-schema.ts` now hold all three better: the hash lives in
 * `account.password`, the link is `people.auth_user_id` below, and the role was
 * never a stored fact in the first place. It is derived from the rank on the
 * position by `tierOf`, and a role written down in a second place is a role that
 * disagrees with itself after somebody is promoted.
 *
 * The table was empty when it was dropped. Nothing read it.
 */

/**
 * Somebody standing in for a position, for a stretch of days.
 *
 * The client's words, functional specification section 8: "if someone doing
 * temp for another position, the tasks should be copied temporarily into his
 * original position". A row here says that between two Bangkok calendar days
 * the duties of one position fall to somebody who does not hold it.
 *
 * **It is not a copy of the duty rows, and the difference is the whole design.**
 * Copying `recurring_templates` onto the stand-in's own position would put
 * another job's duties into their job description, permanently unless something
 * later remembered to take them out again. This table changes who the generator
 * hands the work to and nothing else, which produces the same list on the same
 * screen with none of the cleanup: the duties appear for the stand-in, stop
 * appearing for the holder, and their own duties carry on beside them. Cover
 * adds, it never replaces.
 *
 * Whole position, not duty by duty. Nobody asked for duty by duty, and the two
 * cannot be added later without this table growing a nullable template column
 * that every reader would then have to interpret.
 *
 * **Instances already generated never move.** Ending a cover shortens the
 * window, so tomorrow's generation goes back to the holder and every row that
 * already exists keeps the assignee it was written with. That is the same
 * sentence `active` already carries on `recurring_templates`, and it holds for
 * the same reason: the history of work that was really done must not move.
 */
export const positionCovers = pgTable(
  "position_covers",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    positionId: uuid("position_id")
      .notNull()
      .references(() => positions.id),
    /**
     * The stand-in. A person like any other, and deliberately not required to
     * be in the same department: covering across a department is exactly the
     * case the client described.
     */
    coveringPersonId: uuid("covering_person_id")
      .notNull()
      .references(() => people.id),
    /**
     * Inclusive at both ends, as Bangkok calendar days.
     *
     * `date` and not `timestamptz`, for the reason the file header gives: a
     * cover runs over days on a wall calendar, and a moment rendered as a day is
     * how a window silently loses its first or last morning. Both ends are read
     * and written as `YYYY-MM-DD` strings, which is the type `lib/calendar.ts`
     * and `lib/cover.ts` work in.
     *
     * Not nullable, neither of them. An open-ended cover would be a cover
     * nobody ever ends, and it would also take this table outside what the
     * exclusion constraint below can reason about: `daterange` with a null
     * bound is null, and a null range overlaps nothing at all, so an unbounded
     * cover would silently stop being checked against the others.
     */
    fromDate: date("from_date", { mode: "string" }).notNull(),
    untilDate: date("until_date", { mode: "string" }).notNull(),
    /** Why, in whatever words the person opening it chose. Never read by logic. */
    reason: text("reason"),
    createdBy: uuid("created_by")
      .notNull()
      .references(() => people.id),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => [
    index("position_covers_position_idx").on(t.positionId),
    /**
     * A window that ends before it starts is in force on no day at all, so it
     * generates nothing, reports no error, and is found when somebody asks why
     * the stand-in never got the work. The service answers the same question
     * first, with a reason the screen can say; this is what stops a row that
     * did not come through the service.
     */
    check(
      "position_covers_window_ordered",
      sql`${t.untilDate} >= ${t.fromDate}`,
    ),
    /**
     * ONE COVER PER POSITION PER DAY, and it is enforced by an exclusion
     * constraint that is **not written here**. Drizzle 1.0 has no builder for
     * `EXCLUDE`, so the constraint lives in
     * `drizzle/20260805064637_position_covers_no_overlap/migration.sql`, added
     * by hand alongside `CREATE EXTENSION btree_gist` (available here, 1.8):
     *
     *     EXCLUDE USING gist (
     *       position_id WITH =,
     *       daterange(from_date, until_date, '[]') WITH &&
     *     )
     *
     * `'[]'` because both ends are inclusive here and `daterange` is half-open
     * by default: the default would let two covers meet on the same last and
     * first day and call it no overlap, which is the one day on which two
     * people would each be sent the same task and neither would know who owed
     * it.
     *
     * It is a real database constraint and not a service check, so it is worth
     * saying what that buys: two people opening a cover on the same position at
     * the same moment both read an empty table and both decide they may write.
     * A check in the service cannot see the other transaction. This can.
     *
     * `openCover` in lib/services/covers.ts catches the violation (SQLSTATE
     * 23P01) and turns it into a refusal rather than an error.
     */
  ],
)

/* -------------------------------------------------------------------------- */
/* The calendar                                                               */
/* -------------------------------------------------------------------------- */

/**
 * The holiday calendar, maintained by Admin, and the third input to every date
 * `lib/calendar.ts` computes.
 *
 * Keyed on the day itself: a day is a holiday or it is not, and a surrogate key
 * would let it be both. Read as `YYYY-MM-DD` strings straight into the
 * `ReadonlySet<string>` the calendar functions take.
 */
export const holidays = pgTable("holidays", {
  holidayDate: date("holiday_date", { mode: "string" }).primaryKey(),
  name: text("name").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
})

/* -------------------------------------------------------------------------- */
/* Duties                                                                     */
/* -------------------------------------------------------------------------- */

/**
 * A duty attached to a position: the template an instance is generated from,
 * and at the same time the line item that makes up the job description.
 *
 * Fields follow `machine/task-fields.tsv`. All six occurrences live here, not
 * only the recurring four, because the job description is one list: a `general`
 * duty is described exactly like a daily one and simply never generates.
 */
export const recurringTemplates = pgTable(
  "recurring_templates",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    // Tasks attach to positions, never to people (`machine/roles.json`).
    positionId: uuid("position_id")
      .notNull()
      .references(() => positions.id),
    description: text("description").notNull(),
    occurrence: occurrence("occurrence").notNull(),
    // `once` only: the single moment the task is due.
    deadline: timestamp("deadline", { withTimezone: true }),
    // `weekly` only. 1 Monday to 7 Sunday, the ISO numbering `lib/calendar.ts`
    // reads.
    dayOfWeek: smallint("day_of_week"),
    // `monthly` and `quarterly` only. 1 to 31; a day past the end of a short
    // month is clamped, then shifted off weekends and holidays, by
    // `dueDateInMonth`. The stored value is the intent and is never rewritten.
    dayOfMonth: smallint("day_of_month"),
    /**
     * Every task has a checker: approval is what completes it. Nothing here
     * requires them to be a manager, and the schema does not pretend otherwise.
     *
     * Nullable in the column and required by a constraint, because `general` is
     * the exception. A general duty is a line in a job description that never
     * becomes a task, so there is nothing for anybody to approve, and demanding
     * a checker for it would mean naming somebody who will never check
     * anything. `missingFields` in lib/task-form.ts already stopped asking; the
     * column said otherwise and would have rejected the row at insert.
     */
    checkerPersonId: uuid("checker_person_id").references(() => people.id),
    /** Optional, and a person like any other. Audits a sample after `checked`. */
    verifierPersonId: uuid("verifier_person_id").references(() => people.id),
    reportToDeliver: text("report_to_deliver"),
    reportExampleUrl: text("report_example_url"),
    wiSopUrl: text("wi_sop_url"),
    timeToDoMinutes: integer("time_to_do_minutes"),
    // An identifier from the customer database, for per-customer analytics.
    // Opaque here on purpose: that database is not ours to join against.
    customerTag: text("customer_tag"),
    /**
     * Deactivated, never deleted. `lib/recurrence.ts` stops generating from an
     * inactive template and leaves every instance it already produced alone,
     * because the history of work that was really done must not move.
     */
    active: boolean("active").notNull().default(true),
    createdBy: uuid("created_by")
      .notNull()
      .references(() => people.id),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => [
    index("recurring_templates_position_idx").on(t.positionId),
    /**
     * Anything that becomes a task has somebody to approve it. `general` never
     * becomes one, so it is the only occurrence allowed to name nobody.
     */
    check(
      "recurring_templates_checker_unless_general",
      sql`${t.occurrence} = 'general' OR ${t.checkerPersonId} IS NOT NULL`,
    ),
    check(
      "recurring_templates_day_of_week_range",
      sql`${t.dayOfWeek} IS NULL OR ${t.dayOfWeek} BETWEEN 1 AND 7`,
    ),
    check(
      "recurring_templates_day_of_month_range",
      sql`${t.dayOfMonth} IS NULL OR ${t.dayOfMonth} BETWEEN 1 AND 31`,
    ),
    /**
     * The schedule a duty carries must be the one its occurrence asks for, the
     * same mapping `scheduleFor` makes in `lib/task-form.ts`.
     *
     * This is here because the alternative is silent: a weekly duty saved with
     * no day of week is not due on any day, so it generates nothing, reports no
     * error, and is discovered when somebody asks why the work never appeared.
     */
    check(
      "recurring_templates_schedule_matches_occurrence",
      sql`
        CASE ${t.occurrence}
          WHEN 'once' THEN ${t.deadline} IS NOT NULL
            AND ${t.dayOfWeek} IS NULL AND ${t.dayOfMonth} IS NULL
          WHEN 'daily' THEN ${t.deadline} IS NULL
            AND ${t.dayOfWeek} IS NULL AND ${t.dayOfMonth} IS NULL
          WHEN 'weekly' THEN ${t.deadline} IS NULL
            AND ${t.dayOfWeek} IS NOT NULL AND ${t.dayOfMonth} IS NULL
          WHEN 'monthly' THEN ${t.deadline} IS NULL
            AND ${t.dayOfWeek} IS NULL AND ${t.dayOfMonth} IS NOT NULL
          WHEN 'quarterly' THEN ${t.deadline} IS NULL
            AND ${t.dayOfWeek} IS NULL AND ${t.dayOfMonth} IS NOT NULL
          WHEN 'general' THEN ${t.deadline} IS NULL
            AND ${t.dayOfWeek} IS NULL AND ${t.dayOfMonth} IS NULL
        END
      `,
    ),
  ],
)

/* -------------------------------------------------------------------------- */
/* Task instances                                                             */
/* -------------------------------------------------------------------------- */

/**
 * One occurrence of a duty on one day: the row a person actually works on.
 *
 * ## The guarantee this table exists to enforce
 *
 * `lib/recurrence.ts` builds a key of template plus due date and calls it a
 * promise of idempotent generation. It is only a promise until something
 * enforces it. `task_instances_template_due_key` below is the enforcement: a
 * second run of the generator collides with the first instead of adding to it,
 * so a job that runs twice, a catch-up that overlaps a normal night, and a
 * retry after a crash all converge on the same single row.
 *
 * **The consequence is worth stating plainly: a wrong date is permanent.** The
 * unique index cannot tell a correction from a duplicate. If a run computes the
 * wrong due date, the second run does not repair it; it collides with the
 * mistake and leaves it standing, and every later run agrees with it. The
 * system this replaces dated every row a day early on a UTC host, permanently,
 * for exactly this reason.
 *
 * That is why due dates are computed in Bangkok and never in the process
 * timezone. `lib/calendar.ts` does the arithmetic on `YYYY-MM-DD` strings and
 * never touches the machine's clock zone; this column stores a `date`, which
 * has no timezone to be reinterpreted in. Neither half is optional: correctness
 * here is not recoverable later.
 */
export const taskInstances = pgTable(
  "task_instances",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    /**
     * Not nullable, on purpose. Every task descends from a duty, including a
     * one-time one, which is a `once` template that generates exactly one
     * instance.
     *
     * A nullable column would let ad-hoc rows past the unique index below,
     * because Postgres treats NULLs as distinct: the guarantee would hold for
     * the rows that happened to have a template and quietly not for the others.
     * A guarantee with an exception is not one.
     */
    recurringTemplateId: uuid("recurring_template_id")
      .notNull()
      .references(() => recurringTemplates.id),
    /**
     * The Bangkok calendar day this instance is due, as `YYYY-MM-DD`.
     *
     * `date`, not `timestamptz`: a due date is a day, and a day that is really
     * a moment is a day that moves when the reader's timezone does.
     */
    dueDate: date("due_date", { mode: "string" }).notNull(),
    /**
     * Who is doing it. Nullable: the duty belongs to the position, so an
     * instance exists even while the position has no holder, and it waits
     * rather than vanishing.
     */
    assigneePersonId: uuid("assignee_person_id").references(() => people.id),
    status: taskStatus("status").notNull().default("todo"),
    /**
     * The three timestamps `machine/open-points.tsv` E asks to record. There is
     * no `late_at` and there never will be: late is `now() > due AND status IN
     * (todo, started)`, asked at the moment somebody looks, by
     * `isLate` in `lib/task-actions.ts`. Storing it would mean a task became
     * late when a job happened to run rather than when the day actually passed.
     */
    startedAt: timestamp("started_at", { withTimezone: true }),
    finishedAt: timestamp("finished_at", { withTimezone: true }),
    checkedAt: timestamp("checked_at", { withTimezone: true }),
    checkedBy: uuid("checked_by").references(() => people.id),
    /**
     * The audit, after the fact. A verified task was already complete when it
     * was checked; these two columns record that somebody sampled it, and
     * cannot make it any more or less complete.
     */
    verifiedAt: timestamp("verified_at", { withTimezone: true }),
    verifiedBy: uuid("verified_by").references(() => people.id),
    /**
     * The checker sending the work back (`finished` to `todo`), with the note
     * `machine/lifecycle.json` says that transition carries.
     *
     * DECISION. `machine/task-fields.tsv` has no field for this, but the
     * transition is normative and cannot happen without somewhere to put the
     * note, so the columns are here. Only the most recent send-back is kept.
     */
    sentBackAt: timestamp("sent_back_at", { withTimezone: true }),
    sentBackBy: uuid("sent_back_by").references(() => people.id),
    sentBackNote: text("sent_back_note"),
    /**
     * Reporting an issue flags the task and notifies the manager without
     * changing its status, per `machine/lifecycle.json`. Clearing the flag is
     * setting these back to null.
     *
     * DECISION. Same as above: the behaviour is normative, the field list does
     * not mention it. One open flag per task rather than a history of them.
     */
    issueReportedAt: timestamp("issue_reported_at", { withTimezone: true }),
    issueReportedBy: uuid("issue_reported_by").references(() => people.id),
    issueNote: text("issue_note"),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => [
    /**
     * THE GUARANTEE. One instance per template per due date.
     *
     * Both columns are NOT NULL, so there is no row this does not cover.
     * Generation inserts with `ON CONFLICT DO NOTHING` against it and is
     * therefore safe to run again, concurrently, or after a partial failure.
     */
    uniqueIndex("task_instances_template_due_key").on(
      t.recurringTemplateId,
      t.dueDate,
    ),
    // What /my-tasks asks for: one person, around one day.
    index("task_instances_assignee_due_idx").on(t.assigneePersonId, t.dueDate),
    // What the cut-off evaluation asks for: everything still open, by day.
    index("task_instances_status_due_idx").on(t.status, t.dueDate),
    /**
     * Verification is an audit *after* `checked`. A row cannot claim to have
     * been verified without having been checked first.
     */
    check(
      "task_instances_verified_after_checked",
      sql`${t.verifiedAt} IS NULL OR ${t.checkedAt} IS NOT NULL`,
    ),
    /**
     * A missed task stays open and can still be done, so `missed` is not a
     * terminal state and nothing here forbids it from moving on. What is
     * forbidden is the reverse: a task that has been checked is complete, and
     * the cut-off evaluation must never be able to mark it missed afterwards.
     */
    check(
      "task_instances_checked_is_not_missed",
      sql`${t.status} <> 'missed' OR ${t.checkedAt} IS NULL`,
    ),
  ],
)

/* -------------------------------------------------------------------------- */
/* Submissions                                                                */
/* -------------------------------------------------------------------------- */

/**
 * What the assignee hands in when they mark a task finished.
 *
 * PROTOTYPE, and the one table here that the specification does not describe.
 * `machine/task-fields.tsv` carries `report_to_deliver` and
 * `report_example_url`, which say what was *asked for*, and nothing at all for
 * what *came back*: without this, a checker is shown Approve and Send back with
 * no trace of the work, and Approve becomes a reflex. The shape follows
 * `TaskSubmission` in `lib/task.ts` and is tracked in ELEVA-121. The
 * specification folder is not ours to edit, so nothing here is written back to
 * `machine/`.
 */
export const taskSubmissions = pgTable("task_submissions", {
  id: uuid("id").primaryKey().defaultRandom(),
  /**
   * One submission per task. Finishing tells the assignee "you cannot change it
   * afterwards", and this is the sentence being true rather than being said.
   */
  taskInstanceId: uuid("task_instance_id")
    .notNull()
    .unique()
    .references(() => taskInstances.id),
  submittedBy: uuid("submitted_by")
    .notNull()
    .references(() => people.id),
  submittedAt: timestamp("submitted_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
  /** Free text, in the assignee's words. The one part that cannot be dropped. */
  note: text("note").notNull(),
  /**
   * The numbers the task exists to capture, as `{ label, value }` objects.
   *
   * DECISION. `jsonb` rather than a table: the readings differ per duty, are
   * written once with the submission, and are read back as a block. A row per
   * reading buys querying across duties that nothing in the specification asks
   * for.
   */
  readings: jsonb("readings").$type<{ label: string; value: string }[]>(),
  /** The report as actually delivered, against the one that was asked for. */
  deliveredName: text("delivered_name"),
  deliveredUrl: text("delivered_url"),
  deliveredHow: text("delivered_how"),
})

/* -------------------------------------------------------------------------- */
/* Domain events                                                              */
/* -------------------------------------------------------------------------- */

/**
 * What happened, in the vocabulary `architecture.md` §5 uses for the outbox:
 * `task.created · task.checked · task.missed · holder.changed…`.
 *
 * An enum and not free text, for the same reason the statuses are one: a typo
 * in a type name is an event nothing ever consumes, and it is discovered by the
 * consumer that stayed silent rather than by the insert that was wrong. The
 * cost is that a new event type is a migration, which is the right price for a
 * list this short.
 *
 * `task.created` is deliberately not here yet. The nightly job writes three to
 * eight thousand instances in bulk, nobody is notified of any of them, and an
 * event type with no consumer is a table growing for nothing. The day a webhook
 * wants it, it is one value and one line in the fan-out.
 *
 * `holder.changed` likewise: nothing in this half moves a person between
 * positions, and naming an event that nothing emits would make the outbox read
 * as more complete than it is.
 */
export const domainEventType = pgEnum("domain_event_type", [
  "task.finished",
  "task.checked",
  "task.sent_back",
  "task.reminded",
  "task.missed",
])

/**
 * The outbox: what happened, written in the same transaction as the change that
 * caused it.
 *
 * That single sentence is the whole design. An event cannot survive a
 * rolled-back change, because it rolls back with it; a committed change cannot
 * lose its event, because the commit carries both. Anything else has a window:
 * write the row and then send, and a crash between the two loses the message;
 * send and then write the row, and the same crash sends a message about work
 * that never happened.
 *
 * **Application code enqueues, and never sends.** `architecture.md` §4 draws
 * the seam as event → queue → worker → adapter, and the seam that matters is
 * the event: today the puller is `dispatchPending` in
 * lib/services/notifications.ts, tomorrow it is a pg-boss worker, and nothing
 * that writes an event has to know which. That is why there is no channel, no
 * driver and no delivery anywhere near a service that changes a task.
 */
export const domainEvents = pgTable(
  "domain_events",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    type: domainEventType("type").notNull(),
    /**
     * The task it concerns. Nullable because the outbox is not the task
     * module's: `holder.changed` concerns a person and a position, and a future
     * HR module's events concern neither. Every type this file names today
     * carries one.
     */
    taskInstanceId: uuid("task_instance_id").references(() => taskInstances.id),
    /**
     * Who did it, which is also who must not be told about it: nobody needs a
     * notification about the button they just pressed. Nullable, and the null
     * is meaningful rather than missing data: the nightly sweep marks work
     * missed and no person did that.
     */
    actorPersonId: uuid("actor_person_id").references(() => people.id),
    /**
     * What the act carried that the columns do not: today the note on a
     * send-back or a reminder, and nothing else.
     *
     * `jsonb` for the same reason `task_submissions.readings` is: it differs by
     * event type, is written once, and is read back as a block. It holds the
     * words somebody typed, never a rendered sentence about them.
     */
    payload: jsonb("payload").$type<{ note?: string }>(),
    /** When it happened. An instant, so `timestamptz`. */
    occurredAt: timestamp("occurred_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    /**
     * When the fan-out ran, and null while it is still owed.
     *
     * This is what makes a missed dispatch recoverable rather than lost:
     * delivery is attempted the moment the transaction commits, and if that
     * attempt fails the row simply stays null and the next drain finds it. It
     * is a marker and not the guarantee; the guarantee that nobody is notified
     * twice is the unique index on the notifications below, because a process
     * can die between sending and stamping.
     */
    dispatchedAt: timestamp("dispatched_at", { withTimezone: true }),
  },
  (t) => [
    /**
     * What the drain asks, and the only question that has to be fast: the
     * oldest events nobody has fanned out yet. Partial, so it holds the handful
     * that are owed rather than every event in the history of the factory.
     */
    index("domain_events_undispatched_idx")
      .on(t.occurredAt)
      .where(sql`${t.dispatchedAt} IS NULL`),
    index("domain_events_task_idx").on(t.taskInstanceId),
  ],
)

/**
 * One row per recipient per event: somebody was told something.
 *
 * The event says what happened; this says who it was for. They are two tables
 * and not one because the fan-out is a decision with its own rules — a finished
 * task tells its checker and nobody else, and a missed one tells nobody at all
 * — and folding the two together would mean either an event per recipient,
 * which is no longer a record of what happened, or a recipient list on the
 * event, which cannot carry a read mark.
 *
 * **What it does not hold: a sentence.** `message_key` names one and the
 * dictionary writes it, in the reader's language, at the moment they open the
 * list. See lib/notice.ts.
 *
 * **What it also does not hold: the actor's name, or the note.** Both are on
 * the event this row points at, and copying either here would be a second place
 * to read one fact — the same reason `people` does not carry a department. The
 * feed joins through and reads them once.
 */
export const notifications = pgTable(
  "notifications",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    /** Who it is for. A person, not an account: 190 of the 193 have no login. */
    personId: uuid("person_id")
      .notNull()
      .references(() => people.id),
    /** Which event it fans out from, and where its words come from. */
    eventId: uuid("event_id")
      .notNull()
      .references(() => domainEvents.id),
    /**
     * The sentence, as a dictionary key. Typed as one, so a rendered English
     * string cannot be written here by accident: the compiler refuses anything
     * the dictionary does not name.
     */
    messageKey: text("message_key").$type<NoticeKey>().notNull(),
    /**
     * The task to open. Denormalised from the event on purpose, and it is the
     * one thing that is: this is what every row links to, it is asked for on
     * every render of the list, and a driver that is handed a notice is handed
     * the task id with it. `NotificationDriver.send` in `architecture.md` §4
     * takes exactly this field.
     */
    taskInstanceId: uuid("task_instance_id").references(() => taskInstances.id),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    /**
     * When the person saw it. Null is unread, which is what the badge counts.
     */
    readAt: timestamp("read_at", { withTimezone: true }),
  },
  (t) => [
    /**
     * THE GUARANTEE, and the same shape as the one on `task_instances`: one
     * notification per person per event.
     *
     * It is what makes draining the outbox safe to repeat. Delivery is
     * attempted at commit and again by the nightly drain, a worker may be
     * restarted mid-fan-out, and two of them may run at once; all of those
     * converge on this index rather than on a second row telling somebody the
     * same thing twice. Dispatch inserts with `ON CONFLICT DO NOTHING` against
     * it.
     */
    uniqueIndex("notifications_event_person_key").on(t.eventId, t.personId),
    /**
     * What the list and the badge ask: one person's, unread first, newest
     * first. `read_at` sits between the two so the badge's count is answered
     * from the index alone.
     */
    index("notifications_person_idx").on(t.personId, t.readAt, t.createdAt),
  ],
)

/* -------------------------------------------------------------------------- */
/* Inferred types                                                             */
/* -------------------------------------------------------------------------- */

export type Department = typeof departments.$inferSelect
export type Position = typeof positions.$inferSelect
export type Person = typeof people.$inferSelect
export type PositionCover = typeof positionCovers.$inferSelect
export type Holiday = typeof holidays.$inferSelect
export type RecurringTemplate = typeof recurringTemplates.$inferSelect
export type TaskInstance = typeof taskInstances.$inferSelect
export type TaskSubmission = typeof taskSubmissions.$inferSelect
export type DomainEvent = typeof domainEvents.$inferSelect
export type DomainEventType = (typeof domainEventType.enumValues)[number]
export type Notification = typeof notifications.$inferSelect