import { eq, isNotNull, sql } from "drizzle-orm"
import { db } from "@/lib/db/client"
import { people, positions, recurringTemplates, tierOf } from "@/lib/db/schema"

const rows = await db
  .selectDistinct({ id: people.id, level: positions.level, code: people.employeeCode })
  .from(recurringTemplates)
  .innerJoin(people, eq(people.id, recurringTemplates.checkerPersonId))
  .leftJoin(positions, eq(positions.id, people.positionId))
  .where(isNotNull(recurringTemplates.checkerPersonId))

const byTier = new Map<string, number>()
for (const r of rows) {
  const t = r.level ? tierOf(r.level) : "staff"
  byTier.set(t, (byTier.get(t) ?? 0) + 1)
}
console.log("contrôleurs distincts :", rows.length)
console.log("par niveau d'accès :", Object.fromEntries(byTier))

const [verif] = await db
  .select({ n: sql<number>`count(distinct ${recurringTemplates.verifierPersonId})::int` })
  .from(recurringTemplates)
  .where(isNotNull(recurringTemplates.verifierPersonId))
console.log("vérificateurs distincts :", verif?.n)
process.exit(0)