import { and, eq, gte, inArray, isNull, lte, or, sql as raw } from "drizzle-orm"

import { addDays, dayIn } from "@/lib/calendar"
import { db, sql } from "@/lib/db/client"
import {
  departments,
  domainEvents,
  notifications,
  people,
  positions,
  recurringTemplates,
  taskInstances,
  taskSubmissions,
  tierOf,
} from "@/lib/db/schema"
import {
  DEMO_WORK,
  dutiesFor,
  type Auditor,
  type Chair,
} from "@/lib/demo-duties"
import { at, hashOf, planFor, type Act } from "@/lib/demo-plan"
import { rankOf, type Level } from "@/lib/levels"
import {
  generateFor,
  markMissedAsOf,
  runDailyJob,
} from "@/lib/services/generation"
import type { SignedIn } from "@/lib/services/session"
import {
  approve,
  finish,
  reportIssue,
  sendBack,
  start,
  verify,
} from "@/lib/services/task-commands"

/**
 * A factory's month of work, for a database that has to be shown to somebody.
 *
 *     bun run db:seed     # the roster first: this needs it
 *     bun run db:demo
 *
 * ## The one rule this file exists to keep
 *
 * **Nothing here writes a status.** The seed it replaces did, and it wrote six
 * instances as `missed` with a due date of tomorrow — a state `markMissedAsOf`
 * cannot reach, because the cut-off follows each task's own cycle and nothing
 * is missed before that cycle closes (ELEVA-170). A seed that writes rows
 * directly can invent a state no code path produces, and then nothing notices
 * when the engine stops being able to produce it either.
 *
 * So this simulates the month instead. It generates the instances with
 * `generateFor`, walks the days one at a time, performs the acts a person would
 * have performed with the very functions the buttons call — `start`, `finish`,
 * `approve`, `sendBack`, `verify`, `reportIssue` — and then runs
 * `markMissedAsOf` for that night. Every `missed` row in the result was found
 * by the sweep, every submission was written by `finish`, every notification
 * was fanned out by the outbox. A seed that goes through the engine tests the
 * engine on every run.
 *
 * The one thing it writes over afterwards is the clock. A command stamps
 * `new Date()`, which is right for a button and wrong for a month of history,
 * so the last pass backdates the instants the simulation just produced to the
 * moments lib/demo-plan.ts computed for them. The *shape* of the data comes
 * from the engine; only the hands on the clock are moved.
 *
 * ## Idempotent, and how
 *
 * Four separate guarantees, one per thing this writes.
 *
 *  1. **Duties** are matched on (position, description) and updated rather than
 *     inserted twice.
 *  2. **Instances** are the engine's own guarantee: the unique index on
 *     (recurring_template_id, due_date) makes `generateFor` collide with itself.
 *  3. **Acts** are skipped for any instance that already carries a `started_at`.
 *     Every fate but `untouched` begins by starting the task, so a row with a
 *     `started_at` has already been through here — or somebody has pressed the
 *     button themselves during a demonstration, and their timestamps are not
 *     ours to overwrite.
 *  4. **Backdating** only touches rows this process wrote, which is what the
 *     `runStart` filters below are for.
 *
 * Run it twice and the second run performs no acts, creates no events and sends
 * no notifications. There is a proof in tmp/seed/.
 */

const BACK = 28
const AHEAD = 14

const today = dayIn(new Date())
const now = new Date()
/** Everything written after this instant is this run's, and nothing else is. */
const runStart = new Date()

/* -------------------------------------------------------------------------- */
/* The roster, read once                                                      */
/* -------------------------------------------------------------------------- */

type Seat = Chair & { department: string }

const seats = await db
  .select({
    positionId: positions.id,
    title: positions.title,
    level: positions.level,
    department: departments.name,
  })
  .from(positions)
  .innerJoin(departments, eq(departments.id, positions.departmentId))

const roster = await db
  .select({
    id: people.id,
    employeeCode: people.employeeCode,
    firstName: people.firstName,
    lastName: people.lastName,
    displayName: people.displayName,
    language: people.language,
    archivedAt: people.archivedAt,
    positionId: people.positionId,
    title: positions.title,
    level: positions.level,
    department: departments.name,
  })
  .from(people)
  .leftJoin(positions, eq(positions.id, people.positionId))
  .leftJoin(departments, eq(departments.id, positions.departmentId))

if (roster.length === 0) {
  console.error("The roster is empty. Run bun run db:seed first.")
  await sql.end()
  process.exit(1)
}

const seatsIn = (department: string): Seat[] =>
  seats.filter((s) => s.department === department)

type Person = (typeof roster)[number]

const holdersOf = (positionId: string): Person[] =>
  roster.filter((p) => p.positionId === positionId && !p.archivedAt)

/** Highest rank first. Who a department's work answers to. */
const staffOf = (department: string): Person[] =>
  roster
    .filter((p) => p.department === department && !p.archivedAt)
    .sort(
      (a, b) =>
        rankOf(a.level as Level) - rankOf(b.level as Level) ||
        a.employeeCode.localeCompare(b.employeeCode),
    )

/* -------------------------------------------------------------------------- */
/* What the previous demonstration left behind                                */
/* -------------------------------------------------------------------------- */

/**
 * Two families of duty are retired here, and deleting them is deliberate.
 *
 * The first is this file's own previous output: "…: daily check", "…: weekly
 * count", "…: monthly report", eight positions' worth. Their instances carry
 * the statuses that seed wrote by hand, including the impossible ones, and one
 * single `finished_at` shared by all fifteen finished rows. Leaving them would
 * mean the states this rewrite exists to stop producing survive the rewrite.
 *
 * The second is three duties typed into the interface during a test and left
 * there, in French, which is not a language this product speaks. A database
 * being shown to the client cannot carry them.
 *
 * Nothing else is touched. `AUTHZ PROBE`, a one-time duty somebody made to
 * check that a creator who is not the checker is refused, is somebody's fixture
 * and generates nothing; it stays.
 *
 * "Nothing is ever deleted" is a rule about the product, where a row is a
 * record of work somebody really did. This is demonstration furniture and the
 * rule does not reach it.
 */
const RETIRED = [
  raw`${recurringTemplates.description} like '%: daily check'`,
  raw`${recurringTemplates.description} like '%: weekly count'`,
  raw`${recurringTemplates.description} like '%: monthly report'`,
  raw`${recurringTemplates.description} = 'Compter le stock d''accessoires'`,
  raw`${recurringTemplates.description} = 'Tenir le poste propre'`,
  raw`${recurringTemplates.description} = 'Verifier le stock d''accessoires et signaler les manques'`,
]

const retired = await db
  .select({ id: recurringTemplates.id })
  .from(recurringTemplates)
  .where(or(...RETIRED))

if (retired.length > 0) {
  const ids = retired.map((r) => r.id)
  const doomed = await db
    .select({ id: taskInstances.id })
    .from(taskInstances)
    .where(inArray(taskInstances.recurringTemplateId, ids))
  const instanceIds = doomed.map((d) => d.id)

  // Children before parents, and the outbox before the tasks it points at.
  if (instanceIds.length > 0) {
    await db
      .delete(notifications)
      .where(inArray(notifications.taskInstanceId, instanceIds))
    await db
      .delete(domainEvents)
      .where(inArray(domainEvents.taskInstanceId, instanceIds))
    await db
      .delete(taskSubmissions)
      .where(inArray(taskSubmissions.taskInstanceId, instanceIds))
    await db.delete(taskInstances).where(inArray(taskInstances.id, instanceIds))
  }
  await db.delete(recurringTemplates).where(inArray(recurringTemplates.id, ids))
  console.log(
    `retired ${retired.length} duties from the previous demonstration and ${instanceIds.length} of their tasks`,
  )
}

/* -------------------------------------------------------------------------- */
/* The three fixtures the product could not show                              */
/* -------------------------------------------------------------------------- */

/**
 * **A position with nobody in it, and a position with two people in it.**
 *
 * Both come out of one transfer, and a transfer is the only way to make either
 * of them without inventing over the roster. All 193 people hold exactly one of
 * 193 positions, so moving one person onto a colleague's position empties the
 * chair behind them and doubles up the chair in front. That is a thing that
 * really happens — a job is folded into the one next to it — and it needs no
 * name the client's spreadsheet does not already contain.
 *
 * What it exists to show is two behaviours nobody could reach before:
 *
 *   - the vacant cell on /positions and /team, and the duties of an empty chair
 *     going on generating tasks that wait for a holder rather than vanishing
 *   - what `liveTemplates` in lib/services/generation.ts does with two holders,
 *     which is to leave the task unassigned rather than pick one of them at
 *     random. That was written against a roster where the case could not occur;
 *     now it can, and it can be looked at.
 *
 * Neither position belongs to anybody in docs/test-accounts.csv, and the seed
 * refuses rather than guesses if the roster no longer holds these two titles.
 * A re-run of `db:seed` puts the person back where the spreadsheet says they
 * are, so this runs after it and not before.
 */
const TRANSFER = {
  department: "Metal Center",
  from: "บัญชี (สต๊อค)",
  to: "บัญชีแผนก",
}

const emptied = seatsIn(TRANSFER.department).find(
  (s) => s.title === TRANSFER.from,
)
const doubled = seatsIn(TRANSFER.department).find(
  (s) => s.title === TRANSFER.to,
)

if (!emptied || !doubled) {
  console.warn(
    `no transfer: ${TRANSFER.department} no longer holds both "${TRANSFER.from}" and "${TRANSFER.to}". The vacant and two-holder fixtures are missing.`,
  )
} else {
  // The state is read rather than assumed, because the second run finds the
  // chair already empty and must recognise its own work instead of warning
  // about it. `db:seed` puts the person back where the spreadsheet says they
  // are, so both shapes are ordinary.
  const behind = holdersOf(emptied.positionId)
  const ahead = holdersOf(doubled.positionId)

  if (behind.length === 0 && ahead.length > 1) {
    console.log(
      `transfer already in place: "${emptied.title}" vacant, "${doubled.title}" held by ${ahead.length}`,
    )
  } else if (behind.length === 1 && ahead.length >= 1) {
    const mover = behind[0]!
    await db
      .update(people)
      .set({ positionId: doubled.positionId })
      .where(eq(people.id, mover.id))
    mover.positionId = doubled.positionId
    mover.title = doubled.title
    console.log(
      `transferred ${mover.employeeCode} to "${doubled.title}": "${emptied.title}" is now vacant and "${doubled.title}" has two holders`,
    )
  } else {
    console.warn(
      `no transfer: "${emptied.title}" has ${behind.length} holders and "${doubled.title}" has ${ahead.length}. Expected one and one.`,
    )
  }
}

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

/**
 * **A duty with a verifier**, which is the third fixture, and the one that made
 * `verify` an action `availableActions` knows how to produce and nobody could
 * reach: not one row in `recurring_templates` named a `verifier_person_id`.
 *
 * The catalogue names a chair rather than a person, and this is where a chair
 * becomes whoever is sitting in it today. All three sit outside the chain that
 * does and checks the work, because a sample audited by the two people who
 * already handled it is not a sample.
 */
function auditor(slot: Auditor): Person | undefined {
  if (slot === "quality") return staffOf("Quality Control")[0]
  if (slot === "accounts") return staffOf("Accounting")[0]
  // The other person in Accounting: a clerk auditing what Costing worked out.
  return staffOf("Accounting")[1]
}

/**
 * Who approves a duty: the highest-ranked person in the department who is not
 * the one doing it.
 *
 * The fallbacks matter more than the rule. A position with nobody in it still
 * needs a checker, and so does the last person left in a department of one, who
 * ends up checking their own work — a real arrangement in a small department
 * and not a placeholder. Only a department where every single holder is
 * archived has nobody at all, and there the archived holder is named, because
 * the alternative is a duty the check constraint refuses to store.
 */
function checkerFor(department: string, positionId: string): string | null {
  const held = holdersOf(positionId).map((p) => p.id)
  const team = staffOf(department)
  const other = team.find((p) => !held.includes(p.id))
  const fallback =
    team[0]?.id ?? roster.find((p) => p.department === department)?.id ?? null
  return other?.id ?? fallback
}

const existing = await db
  .select({
    id: recurringTemplates.id,
    positionId: recurringTemplates.positionId,
    description: recurringTemplates.description,
  })
  .from(recurringTemplates)

const known = new Map(
  existing.map((t) => [`${t.positionId}${t.description}`, t.id]),
)

let added = 0
let updated = 0

for (const [department, shapes] of Object.entries(DEMO_WORK)) {
  const chairs = seatsIn(department)
  if (chairs.length === 0) {
    console.warn(`no such department: ${department}`)
    continue
  }

  // The person the duties are recorded as having been written by. The head of
  // the department, which is who would have written them.
  const author =
    staffOf(department)[0]?.id ??
    roster.find((p) => p.department === department)?.id
  if (!author) continue

  for (const duty of dutiesFor(shapes, chairs)) {
    const verifierPersonId = duty.auditedBy
      ? (auditor(duty.auditedBy)?.id ?? null)
      : null
    /**
     * A general duty is the one occurrence allowed to name nobody, because it
     * never becomes a task and there is nothing for anybody to approve. The
     * check constraint on the table says the same thing.
     */
    const checkerPersonId =
      duty.occurrence === "general"
        ? null
        : checkerFor(department, duty.positionId)

    const values = {
      positionId: duty.positionId,
      description: duty.description,
      occurrence: duty.occurrence,
      dayOfWeek: duty.dayOfWeek,
      dayOfMonth: duty.dayOfMonth,
      checkerPersonId,
      verifierPersonId,
      createdBy: author,
    }

    const already = known.get(`${duty.positionId}${duty.description}`)
    if (already) {
      await db
        .update(recurringTemplates)
        .set(values)
        .where(eq(recurringTemplates.id, already))
      updated++
    } else {
      await db.insert(recurringTemplates).values(values)
      added++
    }
  }
}

console.log(
  `${added} duties added, ${updated} already there, across ${Object.keys(DEMO_WORK).length} departments`,
)

/* -------------------------------------------------------------------------- */
/* The work itself                                                            */
/* -------------------------------------------------------------------------- */

const from = addDays(today, -BACK)
const to = addDays(today, AHEAD)

let created = 0
for (let i = -BACK; i <= AHEAD; i++) {
  created += (await generateFor(addDays(today, i))).created
}
console.log(`${created} tasks generated, ${from} to ${to}`)

/**
 * The one place the seed names an assignee, and it is a fact rather than a
 * status: who held the position on the day the work was due.
 *
 * Center 3 has one chair and its holder has left, so `liveTemplates` finds no
 * holder and generation assigns nobody. That is correct for today and wrong for
 * last month: she was there, the work was hers, and `docs/test-accounts.csv`
 * says her past tasks still name her — which is the whole point of an archived
 * person rather than a deleted one. The engine wrote exactly this row every day
 * she held the chair, and the seed is writing what it would have written.
 *
 * A week's grace before today, so the boundary reads as somebody leaving rather
 * than as a switch being thrown at midnight.
 */
const departed = roster.find((p) => p.department === "Center 3" && p.archivedAt)
if (departed?.positionId) {
  const hers = await db
    .select({ id: recurringTemplates.id })
    .from(recurringTemplates)
    .where(eq(recurringTemplates.positionId, departed.positionId))
  if (hers.length > 0) {
    const handed = await db
      .update(taskInstances)
      .set({ assigneePersonId: departed.id })
      .where(
        and(
          inArray(
            taskInstances.recurringTemplateId,
            hers.map((h) => h.id),
          ),
          isNull(taskInstances.assigneePersonId),
          lte(taskInstances.dueDate, addDays(today, -7)),
        ),
      )
      .returning({ id: taskInstances.id })
    if (handed.length > 0) {
      console.log(
        `${handed.length} tasks in Center 3 still name ${departed.employeeCode}, who held the chair until she left`,
      )
    }
  }
}

/* -------------------------------------------------------------------------- */
/* The month, one day at a time                                               */
/* -------------------------------------------------------------------------- */

/** What the commands want: a person, as the session would have described them. */
function signedIn(person: Person): SignedIn {
  return {
    personId: person.id,
    employeeCode: person.employeeCode,
    name: person.displayName ?? `${person.firstName} ${person.lastName}`.trim(),
    positionId: person.positionId,
    positionTitle: person.title ?? null,
    department: person.department ?? null,
    level: (person.level as Level | null) ?? null,
    tier: person.level ? tierOf(person.level) : "staff",
    language: person.language,
  }
}

const asPerson = new Map(roster.map((p) => [p.id, signedIn(p)]))

/**
 * What somebody writes when they hand work in, when they flag a problem, and
 * when they send work back.
 *
 * Short, specific and in the voice of the person typing it. The alternative was
 * one sentence repeated a thousand times, which tells a checker nothing and
 * makes the submission panel look like a stub rather than a record.
 */
const HANDED_IN = [
  "Done and counted. Figures on the sheet.",
  "All clear, nothing to report.",
  "Finished. Two pieces held back for rework.",
  "Counted twice, the second count is the one on the form.",
  "Done. Machine ran warm again, logged it with Maintenance.",
  "Complete. One short against the job card, noted on the reverse.",
  "Finished early, the line was waiting on stones.",
  "Done. Readings within tolerance.",
]

const FLAGGED = [
  "Waiting on the job card, cannot start the count.",
  "Machine down since this morning, Maintenance have been told.",
  "Short of material, the store room has none left.",
  "The file the customer sent will not open.",
]

const SENT_BACK = [
  "The figures do not add up to the job card. Please check and hand in again.",
  "No reading for the second bath. Add it and send it back to me.",
  "This is last week's sheet. Please redo it for this week.",
]

const pick = <T>(list: readonly T[], key: string, salt: string): T =>
  list[hashOf(`${salt}:${key}`) % list.length]!

/** Everything in the window, with what its duty says about it. */
const board = await db
  .select({
    id: taskInstances.id,
    dueDate: taskInstances.dueDate,
    status: taskInstances.status,
    startedAt: taskInstances.startedAt,
    assigneePersonId: taskInstances.assigneePersonId,
    checkerPersonId: recurringTemplates.checkerPersonId,
    verifierPersonId: recurringTemplates.verifierPersonId,
    description: recurringTemplates.description,
    title: positions.title,
    department: departments.name,
  })
  .from(taskInstances)
  .innerJoin(
    recurringTemplates,
    eq(recurringTemplates.id, taskInstances.recurringTemplateId),
  )
  .innerJoin(positions, eq(positions.id, recurringTemplates.positionId))
  .innerJoin(departments, eq(departments.id, positions.departmentId))
  .where(and(gte(taskInstances.dueDate, from), lte(taskInstances.dueDate, to)))

type Card = (typeof board)[number]

/**
 * The key the whole demonstration is deterministic from, and it is deliberately
 * not a uuid. Department, chair, duty and day are all facts the roster and the
 * catalogue already hold, so two databases built from the same spreadsheet show
 * the same month, and a screenshot taken on Tuesday still matches on Wednesday.
 */
const keyOf = (c: Card) =>
  `${c.department}|${c.title}|${c.description}|${c.dueDate}`

type Step = { card: Card; act: Act }

const byDay = new Map<string, Step[]>()
const plans = new Map<string, { acts: Act[] }>()

let unheld = 0

for (const card of board) {
  // Guarantee 3. A row that has been started has been through here already, or
  // somebody pressed the button themselves and their clock is not ours to move.
  if (card.startedAt) continue
  /**
   * Nobody holds the chair, so nobody does the work, so nothing happens to it
   * at all — not even the checker's half, because there is nothing to approve.
   * This is the vacant position and the position with two holders, and their
   * tasks sitting untouched until the sweep finds them is the fixture rather
   * than a failure.
   */
  if (!card.assigneePersonId) {
    unheld++
    continue
  }
  const plan = planFor(
    keyOf(card),
    card.dueDate,
    card.verifierPersonId !== null,
  )
  if (plan.acts.length === 0) continue
  plans.set(card.id, plan)
  for (const act of plan.acts) {
    if (act.day > today || act.when > now) continue
    const list = byDay.get(act.day) ?? []
    list.push({ card, act })
    byDay.set(act.day, list)
  }
}

/** Who performs which verb. A verb with nobody to perform it does not happen. */
function actorFor(step: Step): SignedIn | undefined {
  const { card, act } = step
  const id =
    act.verb === "approve" || act.verb === "send_back"
      ? card.checkerPersonId
      : act.verb === "verify"
        ? card.verifierPersonId
        : card.assigneePersonId
  return id ? asPerson.get(id) : undefined
}

/**
 * `null` when there is nobody to do it, which is not a refusal: the vacant
 * chair and the chair with two people in it both leave their tasks unassigned,
 * and nothing happening to them is the fixture rather than a failure.
 */
type Outcome = { ok: true } | { ok: false; reason: string } | null

async function perform(step: Step): Promise<Outcome> {
  const actor = actorFor(step)
  if (!actor) return null
  const { card, act } = step
  const key = keyOf(card)
  const said = await (async () => {
    switch (act.verb) {
      case "start":
        return start(actor, card.id)
      case "finish":
        return finish(actor, card.id, pick(HANDED_IN, key, "note"))
      case "report_issue":
        return reportIssue(actor, card.id, pick(FLAGGED, key, "issue"))
      case "approve":
        return approve(actor, card.id)
      case "send_back":
        return sendBack(actor, card.id, pick(SENT_BACK, key, "back"))
      case "verify":
        return verify(actor, card.id)
    }
  })()
  return said.ok
    ? { ok: true }
    : { ok: false, reason: `${act.verb}: ${said.reason}` }
}

/** Which instant a performed act belongs on. */
type Stamp =
  | "startedAt"
  | "finishedAt"
  | "issueReportedAt"
  | "checkedAt"
  | "sentBackAt"
  | "verifiedAt"

const COLUMN: Record<Act["verb"], Stamp> = {
  start: "startedAt",
  finish: "finishedAt",
  report_issue: "issueReportedAt",
  approve: "checkedAt",
  send_back: "sentBackAt",
  verify: "verifiedAt",
}

const clock = new Map<string, Partial<Record<Stamp, Date>>>()

let performed = 0
let swept = 0
/**
 * Every refusal, by what was asked and what came back. It is printed rather
 * than swallowed: a refusal here is the engine disagreeing with the plan, and
 * a seed that hides that is a seed that stops testing anything.
 */
const refusals = new Map<string, number>()

for (let i = -BACK; i <= 0; i++) {
  const day = addDays(today, i)
  for (const step of byDay.get(day) ?? []) {
    const done = (await perform(step)) ?? {
      ok: false as const,
      reason: `${step.act.verb}: nobody to do it`,
    }
    if (done.ok) {
      performed++
      const stamps = clock.get(step.card.id) ?? {}
      stamps[COLUMN[step.act.verb]] = step.act.when
      clock.set(step.card.id, stamps)
    } else {
      refusals.set(done.reason, (refusals.get(done.reason) ?? 0) + 1)
    }
  }

  /**
   * The night. It marks and never removes, it follows each task's own cycle,
   * and it is the only thing in this file that produces a `missed` row.
   *
   * It runs after the day's acts and not before, for the same reason
   * `runDailyJob` puts it after generation: work handed in during the day
   * should not be marked missed that night.
   */
  const { marked } = await markMissedAsOf(day)
  swept += marked

  // The sweep's own events happened that night, not at the moment this loop
  // reached them. Backdated as we go, so that each pass only sees the events
  // the pass before it left alone.
  if (marked > 0) {
    const nightfall = new Date(
      Math.min(at(day, 22 * 60).getTime(), now.getTime()),
    )
    await db
      .update(domainEvents)
      .set({ occurredAt: nightfall })
      .where(
        and(
          eq(domainEvents.type, "task.missed"),
          gte(domainEvents.occurredAt, runStart),
        ),
      )
  }
}

const refused = [...refusals.values()].reduce((a, b) => a + b, 0)
console.log(
  `${performed} acts performed, ${unheld} tasks left alone because nobody holds the chair, ${refused} refused, ${swept} marked missed by the sweep`,
)
for (const [reason, n] of [...refusals].sort((a, b) => b[1] - a[1])) {
  console.log(`  refused ${String(n).padStart(4)}  ${reason}`)
}

/* -------------------------------------------------------------------------- */
/* The nightly job, which is the honest way to finish                         */
/* -------------------------------------------------------------------------- */

/**
 * The same three things the machine does every night: generate the window
 * either side of today, mark what has run out of time, drain the outbox. It
 * changes very little after the walk above, and that is the point — a seed that
 * leaves the database in a state tonight's job would disagree with has not
 * finished.
 */
const run = await runDailyJob(today)
console.log(
  `nightly job: ${run.created} created, ${run.marked} marked, ${run.dispatched} events drained to ${run.notified} people`,
)

/* -------------------------------------------------------------------------- */
/* Moving the hands on the clock                                              */
/* -------------------------------------------------------------------------- */

/**
 * Every instant this run wrote, rewritten to the moment it stands for.
 *
 * Until this pass the whole month happened in the ten seconds the seed took to
 * run, which is what made the old demonstration unusable: thirty rows carrying
 * one and the same `finished_at`, so /team's "longest wait first" had never
 * been ordered against anything. The values come from lib/demo-plan.ts and are
 * ordered by construction, so nothing here can put a check before the hand-in
 * it approved.
 */
let restamped = 0
for (const [id, stamps] of clock) {
  if (Object.keys(stamps).length === 0) continue
  await db.update(taskInstances).set(stamps).where(eq(taskInstances.id, id))
  restamped++
}

const moved = [...clock.keys()]

if (moved.length > 0) {
  // A submission was handed in when the task was finished. It is written inside
  // `finish`'s own transaction, so there is exactly one per finished task and
  // this only has to agree with it.
  await db
    .update(taskSubmissions)
    .set({
      submittedAt: raw`(select ${taskInstances.finishedAt} from ${taskInstances}
        where ${taskInstances.id} = ${taskSubmissions.taskInstanceId})`,
    })
    .where(
      and(
        inArray(taskSubmissions.taskInstanceId, moved),
        gte(taskSubmissions.submittedAt, runStart),
      ),
    )

  /**
   * An event happened when the act that caused it happened. Which column that
   * is depends on the type, which is why this is three statements and not one:
   * the outbox is a record of what happened, and a record dated by the run that
   * replayed it is not one. `task.missed` is not here because the walk above
   * dates each night's sweep as it goes.
   */
  const WHEN = [
    { type: "task.finished" as const, column: taskInstances.finishedAt },
    { type: "task.checked" as const, column: taskInstances.checkedAt },
    { type: "task.sent_back" as const, column: taskInstances.sentBackAt },
  ]
  for (const { type, column } of WHEN) {
    await db
      .update(domainEvents)
      .set({
        occurredAt: raw`(select ${column} from ${taskInstances}
          where ${taskInstances.id} = ${domainEvents.taskInstanceId})`,
      })
      .where(
        and(
          eq(domainEvents.type, type),
          inArray(domainEvents.taskInstanceId, moved),
          gte(domainEvents.occurredAt, runStart),
        ),
      )
  }

  // A notification was created when its event was. It carries no clock of its
  // own that anybody reads except this one.
  await db
    .update(notifications)
    .set({
      createdAt: raw`(select ${domainEvents.occurredAt} from ${domainEvents}
        where ${domainEvents.id} = ${notifications.eventId})`,
    })
    .where(gte(notifications.createdAt, runStart))

  /**
   * And most of them have been read. A bell holding a thousand unread notices
   * is a bell nobody looks at, and the badge is meant to be demonstrable:
   * anything older than three days is marked read and the last three days are
   * not, which leaves about one apiece across the ninety-seven people who own
   * work.
   */
  await db
    .update(notifications)
    .set({ readAt: raw`${notifications.createdAt} + interval '4 hours'` })
    .where(
      and(
        isNull(notifications.readAt),
        raw`${notifications.createdAt} < now() - interval '3 days'`,
      ),
    )
}

console.log(
  `${restamped} tasks restamped with the moments they actually happened`,
)

/* -------------------------------------------------------------------------- */
/* What came out                                                              */
/* -------------------------------------------------------------------------- */

type Measured = {
  departments: number
  owners: number
  todo: number
  started: number
  finished: number
  checked: number
  missed: number
  verifiers: number
  verified: number
  vacant: number
  shared: number
  finish_moments: number
  earliest: string | null
  latest: string | null
}

const counted = (await db.execute(raw`
  select
    (select count(distinct d.id) from task_instances ti
       join recurring_templates rt on rt.id = ti.recurring_template_id
       join positions p on p.id = rt.position_id
       join departments d on d.id = p.department_id)::int as departments,
    (select count(distinct assignee_person_id) from task_instances)::int as owners,
    (select count(*) from task_instances where status = 'todo')::int as todo,
    (select count(*) from task_instances where status = 'started')::int as started,
    (select count(*) from task_instances where status = 'finished')::int as finished,
    (select count(*) from task_instances where status = 'checked')::int as checked,
    (select count(*) from task_instances where status = 'missed')::int as missed,
    (select count(*) from recurring_templates where verifier_person_id is not null)::int as verifiers,
    (select count(*) from task_instances where verified_at is not null)::int as verified,
    (select count(*) from positions p where not exists (
       select 1 from people pe where pe.position_id = p.id and pe.archived_at is null))::int as vacant,
    (select count(*) from (select position_id from people
       where archived_at is null and position_id is not null
       group by 1 having count(*) > 1) x)::int as shared,
    (select count(distinct finished_at) from task_instances where finished_at is not null)::int as finish_moments,
    (select min(finished_at)::text from task_instances) as earliest,
    (select max(finished_at)::text from task_instances) as latest
`)) as unknown as Measured[]

const m = counted[0]!

console.log()
console.log("departments carrying work ", m.departments)
console.log("people owning a task      ", m.owners)
console.log(
  "by status                 ",
  `todo ${m.todo} · started ${m.started} · finished ${m.finished} · checked ${m.checked} · missed ${m.missed}`,
)
console.log("duties naming a verifier  ", m.verifiers)
console.log("tasks actually verified   ", m.verified)
console.log("vacant positions          ", m.vacant)
console.log("positions with two holders", m.shared)
console.log(
  "distinct finished_at      ",
  `${m.finish_moments}, ${m.earliest} to ${m.latest}`,
)

await sql.end()