import { readdirSync, readFileSync } from "node:fs"
import { join } from "node:path"

import { describe, expect, test } from "bun:test"
import { getTableConfig } from "drizzle-orm/pg-core"

import { instanceKey } from "@/lib/recurrence"
import {
  departments,
  holidays,
  occurrence,
  people,
  positions,
  recurringTemplates,
  taskInstances,
  taskStatus,
  taskSubmissions,
} from "@/lib/db/schema"
import { OCCURRENCES } from "@/lib/task-form"

/**
 * What the schema promises.
 *
 * Half of these read the schema objects and half read the SQL that
 * `drizzle-kit generate` actually emitted, on purpose: a constraint that exists
 * only in TypeScript is not a constraint, and the only way to know it survived
 * the generator is to read what the generator wrote.
 */

/** The migration as it will be applied to the database. */
const migrationSql = (() => {
  const dir = join(import.meta.dir, "..", "..", "drizzle")
  const folders = readdirSync(dir).filter((f) => !f.startsWith("."))
  return folders
    .map((f) => readFileSync(join(dir, f, "migration.sql"), "utf8"))
    .join("\n")
})()

const columnsOf = (table: Parameters<typeof getTableConfig>[0]) =>
  new Map(getTableConfig(table).columns.map((c) => [c.name, c]))

describe("idempotent generation", () => {
  const config = getTableConfig(taskInstances)
  const guarantee = config.indexes.find(
    (i) => i.config.name === "task_instances_template_due_key",
  )

  test("one instance per template per due date, and the database says so", () => {
    expect(guarantee).toBeDefined()
    expect(guarantee!.config.unique).toBe(true)
    expect(
      guarantee!.config.columns.map((c) => (c as { name: string }).name),
    ).toEqual(["recurring_template_id", "due_date"])
  })

  /**
   * The point of the whole exercise. `lib/recurrence.ts` calls the key a
   * promise; this is the line that makes a second run collide with the first
   * instead of adding to it.
   */
  test("the guarantee reaches SQL, not only TypeScript", () => {
    expect(migrationSql).toContain(
      'CREATE UNIQUE INDEX "task_instances_template_due_key" ON "task_instances" ("recurring_template_id","due_date");',
    )
  })

  /**
   * Postgres treats NULLs as distinct, so a nullable column in a unique index
   * is a hole in it. Both columns are NOT NULL, so there is no row the index
   * does not cover.
   */
  test("no row escapes the guarantee through a null", () => {
    const columns = columnsOf(taskInstances)
    expect(columns.get("recurring_template_id")!.notNull).toBe(true)
    expect(columns.get("due_date")!.notNull).toBe(true)
    expect(migrationSql).toContain('"recurring_template_id" uuid NOT NULL')
    expect(migrationSql).toContain('"due_date" date NOT NULL')
  })

  /**
   * The engine builds its key out of a template id and a due date. The index is
   * on those two columns and nothing else: if either side ever grew a third
   * component, generation would stop being idempotent and this would fail.
   */
  test("the key the engine builds is the key the database enforces", () => {
    expect(instanceKey("t1", "2026-08-03")).toBe("t1#2026-08-03")
    expect(guarantee!.config.columns).toHaveLength(2)
  })
})

describe("a wrong date is permanent", () => {
  /**
   * A due date is a day on a wall calendar in Bangkok, not an instant. Stored
   * as `date`, it has no timezone left in it to be reinterpreted in: nothing
   * downstream can shift the day by reading it from another zone.
   *
   * The unique index cannot tell a correction from a duplicate, so the second
   * run collides with a wrong date rather than repairing it. That is why the
   * column type matters more here than it usually would.
   */
  test("a due date is a day, and carries no timezone to be reinterpreted in", () => {
    expect(columnsOf(taskInstances).get("due_date")!.getSQLType()).toBe("date")
  })

  test("a moment is a moment, and keeps its zone", () => {
    const columns = columnsOf(taskInstances)
    for (const name of ["started_at", "finished_at", "checked_at"]) {
      expect(columns.get(name)!.getSQLType()).toBe("timestamp with time zone")
    }
  })

  test("holidays are keyed on the day itself", () => {
    const columns = columnsOf(holidays)
    expect(columns.get("holiday_date")!.getSQLType()).toBe("date")
    expect(columns.get("holiday_date")!.primary).toBe(true)
  })
})

describe("the lifecycle", () => {
  test("stores the five statuses of the specification and no others", () => {
    expect(taskStatus.enumValues).toEqual([
      "todo",
      "started",
      "finished",
      "checked",
      "missed",
    ])
  })

  /**
   * A task is complete at `checked`. Verification is a random-sample audit
   * after the fact, so it is a timestamp and never a status: no task can be
   * left reading as incomplete because an optional audit never happened.
   */
  test("verified is an audit after checked, not a step on the way", () => {
    expect(taskStatus.enumValues).not.toContain("verified")
    const columns = columnsOf(taskInstances)
    expect(columns.has("verified_at")).toBe(true)
    expect(columns.has("verified_by")).toBe(true)
    expect(migrationSql).toContain(
      'CONSTRAINT "task_instances_verified_after_checked" CHECK ("verified_at" IS NULL OR "checked_at" IS NOT NULL)',
    )
  })

  test("records started, finished and checked", () => {
    const columns = columnsOf(taskInstances)
    expect(columns.has("started_at")).toBe(true)
    expect(columns.has("finished_at")).toBe(true)
    expect(columns.has("checked_at")).toBe(true)
  })

  /**
   * Late is `now() > due AND status IN (todo, started)`, asked at the moment
   * somebody looks. Stored, it would say a task became late when a job happened
   * to run rather than when the day actually passed.
   */
  test("never stores late", () => {
    // Word-boundary, not substring: `recurring_template_id` contains "late".
    const isLateish = /(^|_)late(_|$)/
    const names = [...columnsOf(taskInstances).keys()]
    expect(names.filter((n) => isLateish.test(n))).toEqual([])
    expect(migrationSql).not.toMatch(/"(is_)?late(_at)?"/)
  })

  /**
   * A missed task stays open and can still be done, so nothing forbids it from
   * moving on. What is forbidden is the reverse.
   */
  test("a checked task can never be marked missed afterwards", () => {
    expect(migrationSql).toContain(
      'CONSTRAINT "task_instances_checked_is_not_missed" CHECK ("status" <> \'missed\' OR "checked_at" IS NULL)',
    )
  })
})

describe("duties", () => {
  test("attach to a position, never to a person", () => {
    const columns = columnsOf(recurringTemplates)
    expect(columns.get("position_id")!.notNull).toBe(true)
    expect([...columns.keys()]).not.toContain("assignee_person_id")
  })

  test("carry every occurrence the form offers, including the ones that never generate", () => {
    expect([...occurrence.enumValues]).toEqual([...OCCURRENCES])
  })

  /**
   * A weekly duty saved with no day of week is due on no day at all: it
   * generates nothing, reports nothing, and is found when somebody asks why the
   * work never appeared.
   */
  test("cannot carry a schedule its occurrence does not ask for", () => {
    expect(migrationSql).toContain(
      'CONSTRAINT "recurring_templates_schedule_matches_occurrence"',
    )
    expect(migrationSql).toContain(
      'WHEN \'weekly\' THEN "deadline" IS NULL\n            AND "day_of_week" IS NOT NULL AND "day_of_month" IS NULL',
    )
  })

  /**
   * Anything that becomes a task has a checker; a verifier is optional. Both
   * are people, and nothing constrains either to be a manager.
   *
   * The checker used to be `NOT NULL` outright, which contradicted
   * `missingFields` in lib/task-form.ts: a `general` duty is a line in a job
   * description that never becomes a task, so there is nothing to approve, and
   * the form had already stopped asking. The column would have rejected the row
   * at insert. It is a constraint now, so the requirement still holds everywhere
   * it means anything.
   */
  test("everything that becomes a task has a checker, and general alone may not", () => {
    const columns = columnsOf(recurringTemplates)
    expect(columns.get("checker_person_id")!.notNull).toBe(false)
    expect(migrationSql).toContain(
      `CHECK ("occurrence" = 'general' OR "checker_person_id" IS NOT NULL)`,
    )
    expect(columns.get("verifier_person_id")!.notNull).toBe(false)
    const targets = getTableConfig(recurringTemplates)
      .foreignKeys.map((fk) => fk.reference())
      .filter((r) =>
        r.columns.some((c) =>
          ["checker_person_id", "verifier_person_id"].includes(c.name),
        ),
      )
    expect(targets).toHaveLength(2)
    for (const target of targets) {
      expect(getTableConfig(target.foreignTable).name).toBe("people")
    }
  })
})

describe("people and their accounts", () => {
  /**
   * The login identifier, and the one column that must never be reused: a
   * retired code keeps every historical row pointing at the person who did the
   * work rather than at whoever inherited the number.
   */
  test("an employee code is required and unique", () => {
    expect(columnsOf(people).get("employee_code")!.notNull).toBe(true)
    expect(migrationSql).toContain('"employee_code" text NOT NULL UNIQUE')
  })

  test("a person holds at most one position", () => {
    expect(columnsOf(people).has("position_id")).toBe(true)
  })

  /**
   * Credentials moved to Better Auth, so there is no `accounts` table any more.
   * What remains here is the link, and it must be at most one person per user:
   * two people sharing a login would make every audit trail ambiguous about who
   * actually did the work.
   */
  test("a person links to at most one login, and the link is optional", () => {
    expect(columnsOf(people).get("auth_user_id")!.notNull).toBe(false)
    expect(migrationSql).toContain(
      'ADD CONSTRAINT "people_auth_user_id_key" UNIQUE("auth_user_id")',
    )
  })

  /**
   * The English rendering of a name written in Thai, for a bilingual interface.
   * It is display and never identity: not unique, not required, and nothing
   * resolves on it. Making it unique would reject two people who happen to
   * share a name in English.
   */
  test("the English name is display, never an identifier", () => {
    const column = columnsOf(people).get("display_name")
    expect(column).toBeDefined()
    expect(column!.notNull).toBe(false)
    expect(migrationSql).not.toContain('"display_name" text NOT NULL')
    expect(migrationSql).not.toContain('"display_name" text UNIQUE')
  })
})

describe("what an assignee hands in", () => {
  test("cannot be handed in twice for the same task", () => {
    expect(migrationSql).toContain('"task_instance_id" uuid NOT NULL UNIQUE')
  })

  test("always carries a note, whatever else it carries", () => {
    expect(columnsOf(taskSubmissions).get("note")!.notNull).toBe(true)
  })

  /**
   * `/tasks/[id]` prints this beside the note, through the instant formatter and
   * not the calendar-day one. A `timestamp` without a zone would reach that
   * formatter as a moment the server invented in its own zone, and would be
   * shown to a factory that has one clock and is not on it.
   */
  test("the moment it was handed in keeps its zone", () => {
    expect(columnsOf(taskSubmissions).get("submitted_at")!.getSQLType()).toBe(
      "timestamp with time zone",
    )
  })

  /**
   * The checker has to be told whose words they are reading. Nullable, it could
   * be a note attributed to nobody, which on a screen whose whole purpose is
   * accountability is worse than no card at all.
   */
  test("carries who handed it in", () => {
    expect(columnsOf(taskSubmissions).get("submitted_by")!.notNull).toBe(true)
  })
})

describe("nothing is ever deleted", () => {
  /**
   * Archive or deactivate. A cascade is a delete somebody else triggered, which
   * is the hardest kind to notice.
   *
   * Better Auth's `session` and `account` are the exception, and the exception
   * is the reason the rule holds elsewhere. Neither holds a business record: one
   * is a token that expires on its own and the other is a password hash. If a
   * login is ever removed, leaving those two behind is worse than taking them
   * with it. Nothing that records what somebody did is reachable this way.
   */
  test("no foreign key cascades a delete, outside the two auth tables", () => {
    const cascading = migrationSql
      .split(";")
      .filter((s) => /ON DELETE (CASCADE|SET NULL)/.test(s))
    const allowed = /ALTER TABLE "(session|account)" ADD CONSTRAINT/
    const offenders = cascading.filter((s) => !allowed.test(s))
    expect(offenders).toEqual([])
    // And the exemption is only worth having if it is narrow: two, no more.
    expect(cascading).toHaveLength(2)
  })

  test("the things that outlive their use can be archived or deactivated", () => {
    for (const table of [departments, positions, people]) {
      expect(columnsOf(table).has("archived_at")).toBe(true)
    }
    expect(columnsOf(recurringTemplates).has("active")).toBe(true)
  })
})