import { and, asc, eq, gte, inArray, lt, lte, or, sql } from "drizzle-orm"
import { alias } from "drizzle-orm/pg-core"

import { addDays, dayIn } from "@/lib/calendar"
import { db } from "@/lib/db/client"
import {
  departments,
  people,
  positions,
  recurringTemplates,
  taskInstances,
} from "@/lib/db/schema"
import {
  availableActions,
  DONE,
  isLateOnDay,
  OWED,
  type TaskAction,
  type TaskStatus,
} from "@/lib/task-actions"
import { relationsOf } from "@/lib/task-relations"
import type { Occurrence } from "@/lib/task-form"
import { byName, personOf } from "@/lib/team-view"
import type { SignedIn } from "@/lib/services/session"

/**
 * My department, today.
 *
 * A third question, and its own service. `tasks.ts` answers "what do I have to
 * do", `review.ts` answers "what is waiting for me to judge". This one answers
 * "how is my department doing", which is nobody's personal list and is the only
 * one of the three whose rows belong to other people.
 *
 * It is not `/review` widened. A review queue is sorted by who has to act on a
 * task; this is sorted by what is behind, what is happening today, and what has
 * stopped moving. The overlap is one bucket out of three and even there the
 * useful fact is different: `/review` never has to name the checker, because
 * the checker is the reader.
 *
 * ## Scope is asked here and nowhere else
 *
 * The department is derived from the viewer inside this function. The page
 * reads `?dept=` from the url and hands it over, and for anybody below admin
 * that value is dropped on the floor. A list narrowed on the way out is a list
 * somebody widens by posting a name that was never offered to them.
 */

export type TeamRow = {
  id: string
  /**
   * The duty this instance came from. Carried because the Late tab groups on
   * it: two departments run duties spelled identically, and grouping on the
   * words would merge one department's missed work into another's.
   */
  templateId: string
  description: string
  /** A Bangkok calendar day, `YYYY-MM-DD`. Never an instant. */
  dueDate: string
  status: TaskStatus
  /** Computed at the moment somebody looks, and never stored. */
  late: boolean
  /** Null while the position has nobody in it. The work is still owed. */
  assignee: string | null
  position: string
  /**
   * Carried on every row even though a single-department view repeats it. An
   * admin may hold several at once, and then it is the only thing telling two
   * identical duties apart.
   */
  department: string
  checker: string | null
  finishedAt: Date | null
  /**
   * What THIS viewer may do with THIS task, from `availableActions`.
   *
   * Per row and never per tab, and never per tier. A list that offered Approve
   * by rank would decide on the reader's level what the schema says depends on
   * their relation to the row: `relationsOf` is asked once per row here with the
   * three people the duty actually names, and most rows come back empty.
   *
   * The buttons drawn from this are not a permission. Every one of them reaches
   * a Server Action that asks `relationsOf` again from the session.
   */
  actions: TaskAction[]
}

/**
 * How much of what was due in a window got done.
 *
 * "Done" is `finished` or `checked`. `finished` counts because the work was
 * done: it is sitting in somebody's check queue, and a figure that called it
 * undone would blame the person who did the work for their checker being slow.
 * `checked` is the same work with the approval on it. `todo`, `started` and
 * `missed` are all not done, including `started`, which is work begun and not
 * handed in.
 *
 * The window is the last 30 days INCLUDING today, so `today - 29` to `today`
 * inclusive, cut on `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 department's schedule was kept.
 */
export type TeamCompletion = { done: number; due: number }

export type TeamOverview = {
  /**
   * Every department in view, in the order they will be listed. One for a
   * manager, always; one or more for an admin, who chooses.
   */
  departments: string[]
  /** The day the three buckets are cut around, and lateness judged from. */
  today: string
  /**
   * How many people hold a position in the department(s) in view. People who
   * have left are not counted: they hold nothing, and a headcount that carried
   * them would grow every year while the factory stayed the same size.
   */
  headcount: number
  /**
   * Null when nothing at all was due in the window, which is a different answer
   * from nothing having been done. A new department, or a run of holidays,
   * would otherwise read 0% and look like a failure.
   */
  completion: TeamCompletion | null
  late: TeamRow[]
  dueToday: TeamRow[]
  waitingCheck: TeamRow[]
}

/** The window the completion figure is cut from, counted inclusive of today. */
export const COMPLETION_DAYS = 30

/**
 * What counts as behind: everything still owed, `missed` included.
 *
 * This bucket held `missed` before anything else did, at a time when a manager
 * was the only person who could see the work at all: `myTasks` dropped those
 * instances the moment the sweep wrote the status, and `availableActions`
 * offered nobody a verb on one. That was ELEVA-162, and the client settled it
 * on 4 August, so the two now agree with this rather than the other way about.
 * `OWED` is the shared answer.
 */
const BEHIND: readonly TaskStatus[] = OWED

/** Everything worth carrying forward from before today, missed and unfinished alike. */
const CARRIED: TaskStatus[] = [...BEHIND, "finished"]

const assignee = alias(people, "team_assignee")
const checker = alias(people, "team_checker")

/**
 * A person's name for display: 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.
 *
 * A left join and not the `btrim(first || ' ' || last)` subselect that
 * `tasks.ts` and `review.ts` use. That subselect never looks at `display_name`,
 * so it shows a Thai name to an English reader even once the English one has
 * been filled in. It is a known inconsistency and copying it would make it
 * three files deep instead of two.
 */
type NameParts = {
  firstName: string
  lastName: string
  displayName: string | null
}

function nameOf(parts: NameParts | null): string | null {
  if (!parts) return null
  return parts.displayName ?? `${parts.firstName} ${parts.lastName}`.trim()
}

/*
 * `personOf` and `byName` used to be defined here. They moved to
 * `lib/team-view.ts` when the screen gained filters and grouping, because the
 * tabs and the filter bar need the same two answers and a client component may
 * not import a value from a service. One definition, imported both ways.
 */

export async function teamOverview(
  user: SignedIn,
  opts: { departments?: string[]; today?: string } = {},
): Promise<TeamOverview | null> {
  /**
   * Only an admin may look past their own department, and the check is here
   * rather than on the page for the reason in the header comment.
   *
   * An admin who has picked nothing falls back to their own, so the screen
   * opens on an answer rather than on a prompt. Everybody else gets exactly
   * one department whatever the url said.
   */
  const chosen = opts.departments?.filter((d) => d.trim() !== "") ?? []
  const dept =
    user.tier === "admin"
      ? chosen.length > 0
        ? [...new Set(chosen)].sort(byName)
        : user.department
          ? [user.department]
          : []
      : user.department
        ? [user.department]
        : []

  // A manager between positions, or an admin who has cleared the picker. Null
  // and not an empty overview: having no department is a different answer from
  // a department having nothing in it, and the screen says different things.
  if (dept.length === 0) return null

  const today = opts.today ?? dayIn(new Date())

  /**
   * One query for all three buckets, cut apart in TypeScript below, and two
   * counts alongside it that answer about a window the buckets do not cover.
   *
   * The largest department holds twelve people, so a day's work plus whatever
   * is still open behind it is a few hundred rows at the very worst. Three
   * round trips to save reading three arrays would be the wrong trade; the two
   * counts are separate because they are about other rows entirely, and they go
   * in parallel rather than one after the other.
   */
  const [rows, headcount, completion] = await Promise.all([
    db
      .select({
        id: taskInstances.id,
        templateId: taskInstances.recurringTemplateId,
        description: recurringTemplates.description,
        dueDate: taskInstances.dueDate,
        status: taskInstances.status,
        finishedAt: taskInstances.finishedAt,
        verifiedAt: taskInstances.verifiedAt,
        occurrence: recurringTemplates.occurrence,
        position: positions.title,
        department: departments.name,
        // The three ids the relation is decided from, next to the three names
        // the screen shows, so one row answers both questions and a second
        // query cannot disagree with the first.
        assigneePersonId: taskInstances.assigneePersonId,
        checkerPersonId: recurringTemplates.checkerPersonId,
        verifierPersonId: recurringTemplates.verifierPersonId,
        createdBy: recurringTemplates.createdBy,
        assignee: {
          firstName: assignee.firstName,
          lastName: assignee.lastName,
          displayName: assignee.displayName,
        },
        checker: {
          firstName: checker.firstName,
          lastName: checker.lastName,
          displayName: checker.displayName,
        },
      })
      .from(taskInstances)
      .innerJoin(
        recurringTemplates,
        eq(recurringTemplates.id, taskInstances.recurringTemplateId),
      )
      .innerJoin(positions, eq(positions.id, recurringTemplates.positionId))
      .innerJoin(departments, eq(departments.id, positions.departmentId))
      // Both people are left joins: an instance exists while the position is
      // vacant, and a `general` duty names no checker.
      .leftJoin(assignee, eq(assignee.id, taskInstances.assigneePersonId))
      .leftJoin(checker, eq(checker.id, recurringTemplates.checkerPersonId))
      .where(
        and(
          inArray(departments.name, dept),
          or(
            // Today, whatever became of it.
            eq(taskInstances.dueDate, today),
            // Or older and not yet finished with. `checked` is absent on
            // purpose: a checked task is over, and a manager who wants its
            // history opens the task.
            and(
              lt(taskInstances.dueDate, today),
              inArray(taskInstances.status, CARRIED),
            ),
          ),
        ),
      )
      .orderBy(asc(taskInstances.dueDate), asc(recurringTemplates.description)),
    headcountOf(dept),
    completionOf(dept, today),
  ])

  const shaped: TeamRow[] = rows.map((r) => {
    const status = r.status as TaskStatus
    // Asked per row, from the three people this duty actually names. Nothing
    // here reads the viewer's tier except through `relationsOf`, which is the
    // same function `/tasks/[id]` and every Server Action ask.
    const { relations, isOneTime } = relationsOf({
      viewerPersonId: user.personId,
      viewerTier: user.tier,
      viewerDepartment: user.department,
      assigneePersonId: r.assigneePersonId,
      checkerPersonId: r.checkerPersonId,
      verifierPersonId: r.verifierPersonId,
      createdByPersonId: r.createdBy,
      taskDepartment: r.department,
      occurrence: r.occurrence as Occurrence,
    })
    return {
      id: r.id,
      templateId: r.templateId,
      description: r.description,
      dueDate: r.dueDate,
      status,
      late: isLateOnDay(status, r.dueDate, today),
      assignee: nameOf(r.assignee),
      position: r.position,
      department: r.department,
      checker: nameOf(r.checker),
      finishedAt: r.finishedAt,
      // `verified` matters because the status does not move when a sample audit
      // happens, so nothing else can tell that it already has, and a second
      // press would overwrite who audited the task and when.
      actions: availableActions(status, relations, {
        oneTime: isOneTime,
        verified: r.verifiedAt !== null,
      }),
    }
  })

  /**
   * Disjoint by construction, and it has to be: a task that appears twice is a
   * task somebody counts twice. Today goes to the middle bucket whatever its
   * status, so work finished this morning sits under Due today wearing its
   * "waiting for check" badge rather than under the section about work that has
   * been stuck since last week.
   */
  const late = shaped.filter(
    (r) => r.dueDate < today && BEHIND.includes(r.status),
  )
  const dueToday = shaped.filter((r) => r.dueDate === today)
  const waitingCheck = shaped.filter(
    (r) => r.dueDate < today && r.status === "finished",
  )

  /**
   * Late stays oldest first, which the query already did, and deliberately does
   * not group by department: the most overdue thing is the most overdue thing
   * whichever service it belongs to.
   *
   * Today reads person by person, because somebody scanning it is looking for a
   * name and not for a time. Department leads only when more than one is in
   * view, and then it has to: two services can run the same duty under the same
   * words, and interleaving them makes one list of work belonging to nobody.
   */
  dueToday.sort(
    (a, b) =>
      byName(a.department, b.department) ||
      byName(personOf(a), personOf(b)) ||
      byName(a.description, b.description),
  )
  // Longest wait first. A task handed in a week ago and still unchecked is the
  // one worth chasing, and that is when it was finished rather than when it was
  // due.
  waitingCheck.sort((a, b) => {
    const at = a.finishedAt?.getTime() ?? 0
    const bt = b.finishedAt?.getTime() ?? 0
    return at - bt
  })

  return {
    departments: dept,
    today,
    headcount,
    completion,
    late,
    dueToday,
    waitingCheck,
  }
}

/**
 * How many people hold a position in these departments.
 *
 * Counted from `people` through `position_id` and not from the positions, so a
 * vacant position adds nobody and the number is people rather than seats. The
 * department is reached through the position because that is the only place it
 * is written: copying it onto the person would be two rows to read the same
 * fact and one of them wrong after a transfer.
 */
async function headcountOf(dept: string[]): Promise<number> {
  const [row] = await db
    .select({ n: sql<number>`count(*)::int` })
    .from(people)
    .innerJoin(positions, eq(positions.id, people.positionId))
    .innerJoin(departments, eq(departments.id, positions.departmentId))
    .where(
      and(
        inArray(departments.name, dept),
        // Somebody who has left holds nothing. They are archived rather than
        // deleted, so their history stays, and a headcount that counted them
        // would grow every year while the factory stayed the same size.
        sql`${people.archivedAt} is null`,
      ),
    )
  return row?.n ?? 0
}

/**
 * The completion figure, over the window named on `TeamCompletion`.
 *
 * Counted in Postgres rather than from the rows already in hand, because those
 * rows are three buckets around today and this is thirty days of history: most
 * of what it counts is `checked`, which the main query leaves out on purpose.
 */
async function completionOf(
  dept: string[],
  today: string,
): Promise<TeamCompletion | null> {
  const from = addDays(today, -(COMPLETION_DAYS - 1))
  const [row] = await db
    .select({
      due: sql<number>`count(*)::int`,
      // The statuses spelled out here once, and now shared with /kpi, which
      // shows the same figure to the person the manager reads it about.
      done: sql<number>`count(*) filter (where ${inArray(taskInstances.status, [...DONE])})::int`,
    })
    .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(
        inArray(departments.name, dept),
        gte(taskInstances.dueDate, from),
        lte(taskInstances.dueDate, today),
      ),
    )
  // Nothing was due at all. Null and not a zero percentage: a department that
  // was owed nothing has not failed at anything.
  if (!row || row.due === 0) return null
  return { done: row.done, due: row.due }
}