import { and, eq, gte, inArray, isNotNull, lt, lte, sql } from "drizzle-orm"

import { addDays, dayIn } from "@/lib/calendar"
import { db } from "@/lib/db/client"
import {
  departments,
  people,
  positions,
  recurringTemplates,
  taskInstances,
} from "@/lib/db/schema"
import {
  foldGroups,
  totalOf,
  type KpiFigures,
  type KpiGroup,
  type KpiRow,
} from "@/lib/kpi-view"
import { DONE, OPEN } from "@/lib/task-actions"
import { byName } from "@/lib/team-view"
/*
  The window is borrowed from /team rather than chosen again. Its completion
  tile and the one here are the same measure, and a manager comparing the two
  screens on one morning must not find two figures. Importing the constant is
  what keeps that true the day somebody changes it.
*/
import { COMPLETION_DAYS } from "@/lib/services/team"
import type { SignedIn } from "@/lib/services/session"

/**
 * The measures, over a month.
 *
 * `routes.json` calls this "KPI of MY department", and ELEVA-116 says what that
 * has to mean: *a person is evaluated on KPI they cannot see*. So this service
 * answers the same four questions at two widths and never two different
 * questions. A manager reading their department reads a row per person; each of
 * those people, opening this screen themselves, reads their own row as the four
 * figures at the top. The numbers are the same numbers because they come out of
 * the same query with a different scope, and the department's totals are folded
 * from the rows rather than counted a second time.
 *
 * ## What is not here
 *
 * Nothing that sums `time_to_do_minutes`. The column is nullable and, at the
 * time of writing, unset on all 318 duties, so a figure about how long work
 * takes would be a fact invented from an empty column. There is no "on time"
 * either: a due date is a Bangkok calendar day, `finished_at` is an instant, and
 * calling work late at some hour of its own due day is a judgement the schema
 * does not support. `late` below is a comparison between days, which is the one
 * the specification defines.
 *
 * ## Scope is asked here and nowhere else
 *
 * The same shape as `teamOverview`, and for the same reason: a list narrowed on
 * the way out is a list somebody widens by posting a department that was never
 * offered to them. Staff see themselves whatever `?dept=` says; a manager sees
 * their own department whatever it says; only an admin is read.
 */

/*
  The row shapes and the arithmetic over them live in `lib/kpi-view.ts`, pure,
  so that the one thing which must not be wrong, the rows adding up to the total
  above them, is tested without a database in the room. Re-exported here so a
  screen imports the service it calls rather than two modules.
*/
export type { KpiFigures, KpiRow } from "@/lib/kpi-view"

/**
 * How long finished work waits for its approval.
 *
 * The middle wait and not the mean: one task approved after a fortnight would
 * drag an average away from what happens to almost everything, and this is a
 * number somebody is meant to act on. In whole hours because half the
 * departments sit under two days and "0.9 days" is a figure people re-read
 * rather than use.
 *
 * It is deliberately not on `KpiRow`. The delay belongs to whoever checks the
 * work, not to whoever did it, and a column of it beside a person's completion
 * would read as their failing on the one screen that must not mislead about
 * that.
 */
export type KpiCheckWait = {
  /** How many approved instances the middle was taken from. */
  from: number
  hours: number
}

export type KpiOverview = {
  /** One person's figures, or one or more departments'. */
  scope: "self" | "departments"
  /** Empty when the scope is a person. */
  departments: string[]
  /** The window, as Bangkok calendar days, both ends inclusive. */
  from: string
  to: string
  /**
   * Null when nothing at all fell due in the window, which is a different
   * answer from nothing having been done. `ram` holds a position in a
   * department outside the pilot and has never had a task; a screen of noughts
   * would read as a month of failure rather than as an empty plate.
   */
  overall: KpiFigures | null
  /** Null when nothing due in the window has been approved yet. */
  checkWait: KpiCheckWait | null
  /**
   * Empty for a staff member, on purpose: `overall` above already is their one
   * row, and a table repeating it underneath would say the same thing twice.
   */
  people: KpiRow[]
}

type Scope = { kind: "self" } | { kind: "departments"; names: string[] }

/**
 * Who this reader is allowed to be shown, and it is not what they asked for.
 *
 * Staff are their own scope and nothing else: `?dept=` is not narrowed for
 * them, it is never read. A manager gets their department whatever the url
 * carries. An admin who has chosen nothing falls back to their own, so the
 * screen opens on an answer, exactly as /team does.
 */
function scopeOf(user: SignedIn, asked: string[]): Scope | null {
  if (user.tier === "staff") return { kind: "self" }

  const chosen = asked.filter((d) => d.trim() !== "")
  const names =
    user.tier === "admin" && chosen.length > 0
      ? [...new Set(chosen)].sort(byName)
      : user.department
        ? [user.department]
        : []

  // A manager between positions, or an admin who has cleared the picker.
  return names.length > 0 ? { kind: "departments", names } : null
}

export async function departmentKpi(
  user: SignedIn,
  opts: { departments?: string[]; today?: string } = {},
): Promise<KpiOverview | null> {
  const scope = scopeOf(user, opts.departments ?? [])
  if (!scope) return null

  const to = opts.today ?? dayIn(new Date())
  const from = addDays(to, -(COMPLETION_DAYS - 1))

  /**
   * One condition, written once and asked by both queries below.
   *
   * A person's own figures are cut on the assignee and never on their
   * department. That is what makes a manager's row for somebody and that
   * person's own screen the same number: the department reaches the same
   * instances through the position the duty hangs off, and an instance whose
   * assignee sat in another department would be counted by one and not the
   * other. There are none today and the two agree, which was measured rather
   * than assumed.
   */
  const scoped =
    scope.kind === "self"
      ? eq(taskInstances.assigneePersonId, user.personId)
      : inArray(departments.name, scope.names)

  // Cut on the due date and not on when anything happened. A task due three
  // weeks ago and approved this morning counts in the window it was due in,
  // because the question is whether the schedule was kept.
  const inWindow = and(
    scoped,
    gte(taskInstances.dueDate, from),
    lte(taskInstances.dueDate, to),
  )

  const [rows, wait] = await Promise.all([
    db
      .select({
        personId: taskInstances.assigneePersonId,
        firstName: people.firstName,
        lastName: people.lastName,
        displayName: people.displayName,
        archivedAt: people.archivedAt,
        positionId: positions.id,
        position: positions.title,
        due: sql<number>`count(*)::int`,
        done: sql<number>`count(*) filter (where ${inArray(taskInstances.status, [...DONE])})::int`,
        // The two halves of `isLateOnDay`, asked of a month at once. The
        // statuses come from the same constant the row badge reads.
        late: sql<number>`count(*) filter (where ${inArray(taskInstances.status, [...OPEN])} and ${lt(taskInstances.dueDate, to)})::int`,
        missed: sql<number>`count(*) filter (where ${eq(taskInstances.status, "missed")})::int`,
      })
      .from(taskInstances)
      .innerJoin(
        recurringTemplates,
        eq(recurringTemplates.id, taskInstances.recurringTemplateId),
      )
      .innerJoin(positions, eq(positions.id, recurringTemplates.positionId))
      .innerJoin(departments, eq(departments.id, positions.departmentId))
      // Left, because an instance exists while the position is vacant and the
      // work is still owed by the department.
      .leftJoin(people, eq(people.id, taskInstances.assigneePersonId))
      .where(inWindow)
      .groupBy(
        taskInstances.assigneePersonId,
        people.firstName,
        people.lastName,
        people.displayName,
        people.archivedAt,
        positions.id,
        positions.title,
      ),
    db
      .select({
        from: sql<number>`count(*)::int`,
        seconds: sql<
          number | null
        >`percentile_cont(0.5) within group (order by extract(epoch from (${taskInstances.checkedAt} - ${taskInstances.finishedAt})))`,
      })
      .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(
          inWindow,
          eq(taskInstances.status, "checked"),
          /*
            Both stamps in the WHERE and not only in the arithmetic, so the
            count and the middle cover exactly the same rows. A null would be
            skipped by `percentile_cont` and counted by `count(*)`, and the
            sub-line would then say the figure came from more work than it did.

            They are also why this reads `checked` alone. 65 `missed`
            instances carry a `finished_at`: they were handed in, sent back,
            and then ran out of time, so that stamp does not mean the work is
            waiting for anybody.
          */
          isNotNull(taskInstances.finishedAt),
          isNotNull(taskInstances.checkedAt),
        ),
      ),
  ])

  /**
   * The name is resolved here, where the columns are, and the folding is done
   * by `lib/kpi-view.ts`, where it can be tested.
   *
   * The English name when somebody has taken the trouble, otherwise the
   * roster's own spelling, which for 183 of the 193 people is Thai. Trimmed
   * rather than joined blindly, because one person on the roster has no
   * surname. The same rule `teamOverview` follows.
   */
  const groups: KpiGroup[] = rows.map((r) => ({
    personId: r.personId,
    name: r.personId
      ? (r.displayName ?? `${r.firstName ?? ""} ${r.lastName ?? ""}`.trim())
      : null,
    positionId: r.positionId,
    position: r.position,
    left: r.archivedAt !== null,
    figures: { due: r.due, done: r.done, late: r.late, missed: r.missed },
  }))

  const list: KpiRow[] = foldGroups(groups)
  const overall: KpiFigures | null = totalOf(list)

  const seconds = wait[0]?.seconds
  return {
    scope: scope.kind,
    departments: scope.kind === "departments" ? scope.names : [],
    from,
    to,
    overall,
    checkWait:
      wait[0] && wait[0].from > 0 && seconds !== null && seconds !== undefined
        ? { from: wait[0].from, hours: Math.round(Number(seconds) / 3600) }
        : null,
    people: scope.kind === "self" ? [] : list,
  }
}