/**
 * Ce que les comptes existants couvrent vraiment : niveau, langue, et les
 * relations qui décident de ce qu'on peut faire dans l'application.
 */
import { eq, isNotNull, sql } from "drizzle-orm"

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

const accounts = await db
  .select({
    id: people.id,
    username: people.username,
    code: people.employeeCode,
    lang: people.language,
    archived: people.archivedAt,
    level: positions.level,
    position: positions.title,
    dept: departments.name,
  })
  .from(people)
  .leftJoin(positions, eq(positions.id, people.positionId))
  .leftJoin(departments, eq(departments.id, positions.departmentId))
  .where(isNotNull(people.username))
  .orderBy(people.username)

for (const a of accounts) {
  const count = async (where: ReturnType<typeof sql>) => {
    const [r] = await db
      .select({ n: sql<number>`count(*)::int` })
      .from(taskInstances)
      .innerJoin(
        recurringTemplates,
        eq(recurringTemplates.id, taskInstances.recurringTemplateId),
      )
      .where(where)
    return r?.n ?? 0
  }

  const assigned = await count(sql`${taskInstances.assigneePersonId} = ${a.id}`)
  const missed = await count(
    sql`${taskInstances.assigneePersonId} = ${a.id} and ${taskInstances.status} = 'missed'`,
  )
  const toCheck = await count(
    sql`${recurringTemplates.checkerPersonId} = ${a.id} and ${taskInstances.status} = 'finished'`,
  )
  const checksAny = await count(sql`${recurringTemplates.checkerPersonId} = ${a.id}`)
  const verifies = await count(sql`${recurringTemplates.verifierPersonId} = ${a.id}`)

  console.log(
    [
      (a.username ?? "").padEnd(11),
      (a.level ?? "sans poste").padEnd(18),
      (a.level ? tierOf(a.level) : "staff").padEnd(8),
      a.lang,
      a.archived ? "PARTI" : "     ",
      `porte ${String(assigned).padStart(3)}`,
      `manquées ${String(missed).padStart(2)}`,
      `à contrôler ${String(toCheck).padStart(2)}`,
      `contrôleur de ${String(checksAny).padStart(3)}`,
      `vérificateur de ${verifies}`,
      a.dept ?? "",
    ].join(" | "),
  )
}

console.log()
const [gaps] = await db
  .select({
    sansAcces: sql<number>`count(*) filter (where ${people.username} is null)::int`,
    archives: sql<number>`count(*) filter (where ${people.archivedAt} is not null)::int`,
    total: sql<number>`count(*)::int`,
  })
  .from(people)
console.log("roster :", gaps)

const [vacants] = await db
  .select({ n: sql<number>`count(*)::int` })
  .from(positions)
  .where(sql`not exists (select 1 from people p where p.position_id = ${positions.id})`)
console.log("postes sans titulaire :", vacants?.n)

process.exit(0)