import { and, asc, eq, isNull, or, sql } from "drizzle-orm"

import { dayIn } from "@/lib/calendar"
import { db } from "@/lib/db/client"
import {
  departments,
  positions,
  recurringTemplates,
  taskInstances,
} from "@/lib/db/schema"
import { isLateOnDay } from "@/lib/task-actions"
import type { SignedIn } from "@/lib/services/session"

/**
 * What is waiting for me to judge it.
 *
 * Not what I have to do: that is My Tasks, and the two are separate so a
 * manager's own work does not sit in the same list as the work they are
 * approving. `routes.json` names this `/review` and says plainly that it is
 * data-driven, with no `/checker` or `/verifier` route: whether somebody has a
 * queue is a fact about the rows, never about their tier.
 *
 * Which matters here more than it sounds. Three of the seven checkers in the
 * roster hold staff-tier positions, and the schema says outright that a checker
 * need not be a manager. A queue guarded at manager level would hide work from
 * the people who have to do it, while granting them nothing they cannot already
 * do: a checker can approve any of these tasks by opening it, and this list only
 * saves them from being handed a url.
 */

export type Waiting = "check" | "verification"

export type ReviewRow = {
  id: string
  description: string
  dueDate: string
  /**
   * Whether the due date had passed, asked of work that is already in.
   *
   * Every row on this queue is `finished` or `checked`, so nothing here is
   * owed and `isLateOnDay` would answer false on all of them. It is called with
   * `todo` below for that reason: the question a checker has is whether this
   * was already a problem before it reached them, not whether anybody still
   * owes it. Work caught up from `missed` arrives here reading late, like any
   * other task handed in after its day.
   */
  late: boolean
  waitingFor: Waiting
  /** Who did the work, which is the first thing a checker wants to know. */
  assignee: string | null
  position: string
  department: string
  /** When it was handed in, or approved, depending on what it waits for. */
  since: Date | null
}

export async function reviewQueue(
  user: SignedIn,
  today: string = dayIn(new Date()),
): Promise<ReviewRow[]> {
  const assignee = sql<
    string | null
  >`(select btrim(a.first_name || ' ' || coalesce(a.last_name, '')) from people a where a.id = ${taskInstances.assigneePersonId})`

  const rows = await db
    .select({
      id: taskInstances.id,
      description: recurringTemplates.description,
      dueDate: taskInstances.dueDate,
      status: taskInstances.status,
      finishedAt: taskInstances.finishedAt,
      checkedAt: taskInstances.checkedAt,
      checkerPersonId: recurringTemplates.checkerPersonId,
      assignee,
      position: 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(
      or(
        // Handed in, and I am the one who says whether it is done.
        and(
          eq(recurringTemplates.checkerPersonId, user.personId),
          eq(taskInstances.status, "finished"),
        ),
        /**
         * Approved, and I am the one who audits a sample of it. `verified_at is
         * null` is what takes a task off this list: verifying does not move the
         * status, so without it every task anybody ever verified would sit here
         * for ever.
         */
        and(
          eq(recurringTemplates.verifierPersonId, user.personId),
          eq(taskInstances.status, "checked"),
          isNull(taskInstances.verifiedAt),
        ),
      ),
    )
    // Oldest first. A queue is worked from the end that has been waiting
    // longest, and sorting by due date rather than by when it arrived is what
    // puts a task somebody finished late above one they finished early.
    .orderBy(asc(taskInstances.dueDate), asc(recurringTemplates.description))

  return rows.map((r) => {
    const waitingFor: Waiting =
      r.status === "finished" ? "check" : "verification"
    return {
      id: r.id,
      description: r.description,
      dueDate: r.dueDate,
      // The work is done, so lateness here is about the day it was due rather
      // than about anybody still owing it. It tells a checker which of these
      // was already a problem before it reached them.
      late: isLateOnDay("todo", r.dueDate, today),
      waitingFor,
      assignee: r.assignee,
      position: r.position,
      department: r.department,
      since: waitingFor === "check" ? r.finishedAt : r.checkedAt,
    }
  })
}