/** Jetable. Où en est /team aujourd'hui, avant la refonte 5a. */
import { and, eq, inArray, lt, or, sql } from "drizzle-orm"

import { db } from "@/lib/db/client"
import {
  departments,
  people,
  positions,
  recurringTemplates,
  taskInstances,
} from "@/lib/db/schema"

const TODAY = "2026-08-04"

const [buckets] = await db
  .select({
    late: sql<number>`count(*) filter (where ${taskInstances.dueDate} < ${TODAY} and ${taskInstances.status} in ('todo','started','missed'))::int`,
    today: sql<number>`count(*) filter (where ${taskInstances.dueDate} = ${TODAY})::int`,
    waiting: sql<number>`count(*) filter (where ${taskInstances.dueDate} < ${TODAY} and ${taskInstances.status} = 'finished')::int`,
  })
  .from(taskInstances)

console.log("buckets, toutes départements :", buckets)

// Les 57 en retard : combien de couples (duty, personne) distincts ?
const late = await db
  .select({
    template: taskInstances.recurringTemplateId,
    person: taskInstances.assigneePersonId,
    due: taskInstances.dueDate,
    status: taskInstances.status,
    dept: departments.name,
    desc: recurringTemplates.description,
  })
  .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(
      lt(taskInstances.dueDate, TODAY),
      inArray(taskInstances.status, ["todo", "started", "missed"]),
    ),
  )

const groups = new Map<string, number>()
for (const r of late) {
  const key = `${r.template}|${r.person}`
  groups.set(key, (groups.get(key) ?? 0) + 1)
}
console.log("late rows :", late.length, "→ groupes :", groups.size)
console.log("statuts :", [...new Set(late.map((r) => r.status))])
console.log(
  "par département :",
  [...new Set(late.map((r) => r.dept))].map(
    (d) => `${d}:${late.filter((r) => r.dept === d).length}`,
  ),
)

// Effectif : combien de gens tiennent un poste, par département.
const head = await db
  .select({ dept: departments.name, n: sql<number>`count(*)::int` })
  .from(people)
  .innerJoin(positions, eq(positions.id, people.positionId))
  .innerJoin(departments, eq(departments.id, positions.departmentId))
  .where(sql`${people.archivedAt} is null`)
  .groupBy(departments.name)
console.log("effectifs :", head)

// Complétion 30 jours, toutes départements.
const from = "2026-07-06"
const [comp] = await db
  .select({
    due: sql<number>`count(*)::int`,
    done: sql<number>`count(*) filter (where ${taskInstances.status} in ('finished','checked'))::int`,
  })
  .from(taskInstances)
  .where(
    and(
      sql`${taskInstances.dueDate} >= ${from}`,
      sql`${taskInstances.dueDate} <= ${TODAY}`,
    ),
  )
console.log("complétion 30j (toutes dept) :", comp, from, "→", TODAY)

process.exit(0)