import { and, eq, isNull, sql } from "drizzle-orm"
import { db } from "@/lib/db/client"
import { departments, people, positions, taskInstances } from "@/lib/db/schema"

const rows = await db
  .select({
    code: people.employeeCode,
    level: positions.level,
    dept: departments.name,
    tasks: sql<number>`(select count(*) from ${taskInstances} where ${taskInstances.assigneePersonId} = ${people.id})::int`,
  })
  .from(people)
  .innerJoin(positions, eq(positions.id, people.positionId))
  .innerJoin(departments, eq(departments.id, positions.departmentId))
  .where(and(isNull(people.username), isNull(people.archivedAt)))
  .orderBy(sql`3 desc`)

const withTasks = rows.filter((r) => r.tasks > 0)
console.log("sans accès ET avec des tâches :", withTasks.length)
console.log(withTasks.slice(0, 12))

const depts = await db
  .select({ d: departments.name, n: sql<number>`count(*)::int` })
  .from(taskInstances)
  .innerJoin(people, eq(people.id, taskInstances.assigneePersonId))
  .innerJoin(positions, eq(positions.id, people.positionId))
  .innerJoin(departments, eq(departments.id, positions.departmentId))
  .groupBy(departments.name)
console.log("départements portant du travail :", depts)
process.exit(0)