import { and, asc, eq, gte, inArray, lt, lte, 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, OWED, type TaskStatus } from "@/lib/task-actions"
import { windowFor, type View } from "@/lib/task-views"
import type { SignedIn } from "@/lib/services/session"

/**
 * My Tasks.
 *
 * Personal, per routes.json: this lists what the viewer has to do, and it does
 * that for an admin exactly as it does for anybody else. Work waiting for their
 * check or their verification is a different question and a different page,
 * `/review`. An admin who wants to see everything is not served by a bigger
 * version of this list; they are served by the task page, where their relation
 * to one task decides what they may do with it.
 */

export type { View } from "@/lib/task-views"

export type TaskRow = {
  id: string
  /**
   * The duty this instance came from. Carried because the carried-over section
   * groups on it: a person can hold positions in two departments running duties
   * spelled identically, and grouping on the words would put one department's
   * backlog under the other's line.
   */
  templateId: string
  description: string
  dueDate: string
  status: TaskStatus
  /** Computed here and never stored, so it cannot go stale in a table. */
  late: boolean
  position: string
  department: string
  checker: string | null
}

export type TaskList = {
  view: View
  /** The day the window is drawn around, and the day lateness is judged from. */
  today: string
  from: string
  to: string
  /** Inside the window. */
  rows: TaskRow[]
  /**
   * Still open from before the window.
   *
   * Shown alongside rather than left behind, because a task that scrolls out of
   * view is a task nobody does. The day view is the one this matters for: a
   * person opening their list on Thursday would otherwise see no trace of what
   * they did not finish on Tuesday.
   *
   * `missed` is carried here like anything else outstanding. It used to be left
   * out, and the effect was the opposite of what the status means: the cut-off
   * sweep says an undone task stays open, and then the moment it wrote `missed`
   * the task left the screen of the only person who could still do it.
   * ELEVA-162, answered by the client on 4 August with "work owed is work
   * owed".
   */
  overdue: TaskRow[]
}

export async function myTasks(
  user: SignedIn,
  view: View = "day",
  today: string = dayIn(new Date()),
): Promise<TaskList> {
  const { from, to } = windowFor(view, today)

  const checker = sql<
    string | null
  >`(select btrim(c.first_name || ' ' || coalesce(c.last_name, '')) from people c where c.id = ${recurringTemplates.checkerPersonId})`

  const rows = await db
    .select({
      id: taskInstances.id,
      templateId: recurringTemplates.id,
      description: recurringTemplates.description,
      dueDate: taskInstances.dueDate,
      status: taskInstances.status,
      position: positions.title,
      department: departments.name,
      checker,
    })
    .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(
        eq(taskInstances.assigneePersonId, user.personId),
        or(
          // Inside the window, whatever its status.
          and(gte(taskInstances.dueDate, from), lte(taskInstances.dueDate, to)),
          // Or older and still owed.
          and(
            lt(taskInstances.dueDate, from),
            inArray(taskInstances.status, OWED),
          ),
        ),
      ),
    )
    .orderBy(asc(taskInstances.dueDate), asc(recurringTemplates.description))

  const shaped = rows.map((r) => ({
    ...r,
    status: r.status as TaskStatus,
    late: isLateOnDay(r.status as TaskStatus, r.dueDate, today),
  }))

  return {
    view,
    today,
    from,
    to,
    rows: shaped.filter((r) => r.dueDate >= from),
    overdue: shaped.filter((r) => r.dueDate < from),
  }
}