/** Ma vérification du semis, écrite indépendamment. */
import { and, eq, isNotNull, sql } from "drizzle-orm"

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

const one = async (label: string, q: Promise<{ n: number }[]>) => {
  const [r] = await q
  console.log(`${label.padEnd(46)} ${r?.n ?? 0}`)
  return r?.n ?? 0
}

console.log("=== couverture ===")
await one(
  "départements portant du travail",
  db
    .select({ n: sql<number>`count(distinct ${departments.id})::int` })
    .from(taskInstances)
    .innerJoin(recurringTemplates, eq(recurringTemplates.id, taskInstances.recurringTemplateId))
    .innerJoin(positions, eq(positions.id, recurringTemplates.positionId))
    .innerJoin(departments, eq(departments.id, positions.departmentId)),
)
await one(
  "personnes portant au moins une tâche",
  db
    .select({ n: sql<number>`count(distinct ${taskInstances.assigneePersonId})::int` })
    .from(taskInstances)
    .where(isNotNull(taskInstances.assigneePersonId)),
)
await one("instances", db.select({ n: sql<number>`count(*)::int` }).from(taskInstances))
await one("consignes", db.select({ n: sql<number>`count(*)::int` }).from(recurringTemplates))
await one(
  "consignes nommant un vérificateur",
  db
    .select({ n: sql<number>`count(*)::int` })
    .from(recurringTemplates)
    .where(isNotNull(recurringTemplates.verifierPersonId)),
)
await one(
  "postes sans titulaire actif",
  db
    .select({ n: sql<number>`count(*)::int` })
    .from(positions)
    .where(sql`not exists (select 1 from people p where p.position_id = ${positions.id} and p.archived_at is null)`),
)
await one(
  "postes à deux titulaires ou plus",
  db
    .select({ n: sql<number>`count(*)::int` })
    .from(
      db
        .select({ p: people.positionId })
        .from(people)
        .where(and(isNotNull(people.positionId), sql`${people.archivedAt} is null`))
        .groupBy(people.positionId)
        .having(sql`count(*) > 1`)
        .as("m"),
    ),
)
await one(
  "horodatages de fin distincts",
  db
    .select({ n: sql<number>`count(distinct ${taskInstances.finishedAt})::int` })
    .from(taskInstances)
    .where(isNotNull(taskInstances.finishedAt)),
)

console.log()
console.log("=== états impossibles, tous doivent valoir 0 ===")
const TODAY = sql`current_date`
await one(
  "manquées datées dans le futur",
  db.select({ n: sql<number>`count(*)::int` }).from(taskInstances)
    .where(sql`${taskInstances.status} = 'missed' and ${taskInstances.dueDate}::date > ${TODAY}`),
)
await one(
  "manquées portant un contrôle",
  db.select({ n: sql<number>`count(*)::int` }).from(taskInstances)
    .where(sql`${taskInstances.status} = 'missed' and ${taskInstances.checkedAt} is not null`),
)
await one(
  "rendus sans tâche terminée",
  db.select({ n: sql<number>`count(*)::int` }).from(taskSubmissions)
    .innerJoin(taskInstances, eq(taskInstances.id, taskSubmissions.taskInstanceId))
    .where(sql`${taskInstances.finishedAt} is null`),
)
await one(
  "horodatages contredisant le statut",
  db.select({ n: sql<number>`count(*)::int` }).from(taskInstances)
    .where(sql`(${taskInstances.status} = 'todo' and ${taskInstances.startedAt} is not null)
            or (${taskInstances.status} <> 'checked' and ${taskInstances.checkedAt} is not null)
            or (${taskInstances.status} in ('todo','started') and ${taskInstances.finishedAt} is not null)`),
)
await one(
  "instances dues un dimanche",
  db.select({ n: sql<number>`count(*)::int` }).from(taskInstances)
    .where(sql`extract(dow from ${taskInstances.dueDate}::date) = 0`),
)
await one(
  "instances dues un jour férié",
  db.select({ n: sql<number>`count(*)::int` }).from(taskInstances)
    .where(sql`exists (select 1 from ${holidays} h where h.holiday_date = ${taskInstances.dueDate})`),
)
await one(
  "doublons (consigne, jour)",
  db.select({ n: sql<number>`count(*)::int` }).from(
    db.select({ t: taskInstances.recurringTemplateId, d: taskInstances.dueDate })
      .from(taskInstances)
      .groupBy(taskInstances.recurringTemplateId, taskInstances.dueDate)
      .having(sql`count(*) > 1`)
      .as("d"),
  ),
)

process.exit(0)