import { createAdminClient } from '@/lib/supabase/admin'

export interface BlockingProject {
  id: string
  label: string
  isLead: boolean
  isCreator: boolean
}

export interface DependencyResult {
  assignmentCount: number
  projectCount: number
  /**
   * Projects that reference this consultant via a RESTRICT foreign key
   * (`projects.project_lead_id` / `projects.created_by_consultant_id`).
   * These hard-block deletion at the DB level — surface their names so the
   * caller can reassign before deleting instead of hitting a raw FK error.
   */
  leadProjects: BlockingProject[]
  total: number
  canHardDelete: boolean
}

interface ProjectRow {
  id: string
  supplier_name: string | null
  project_code: string | null
}

function projectLabel(row: ProjectRow): string {
  const supplier = row.supplier_name?.trim() || 'Unbenanntes Projekt'
  return row.project_code ? `${supplier} (${row.project_code})` : supplier
}

export async function checkEmployeeDependencies(consultantId: string): Promise<DependencyResult> {
  const admin = createAdminClient()
  // `assignments`/`project_consultants` are ON DELETE CASCADE, so they do not
  // block the delete at the DB level — but the product rule is to keep people
  // with planning history, so they still gate `canHardDelete`.
  // `project_lead_id`/`created_by_consultant_id` are ON DELETE RESTRICT: they
  // throw a FK violation if not cleared first. Two `.eq()` queries (not `.or()`)
  // keep the consultant id out of any interpolated filter string.
  const [
    { count: assignmentCount },
    { count: projectCount },
    { data: ledRows },
    { data: createdRows },
  ] = await Promise.all([
    admin.from('assignments').select('id', { count: 'exact', head: true }).eq('consultant_id', consultantId),
    admin.from('project_consultants').select('id', { count: 'exact', head: true }).eq('consultant_id', consultantId),
    admin.from('projects').select('id, supplier_name, project_code').eq('project_lead_id', consultantId),
    admin.from('projects').select('id, supplier_name, project_code').eq('created_by_consultant_id', consultantId),
  ])

  const byId = new Map<string, BlockingProject>()
  for (const row of (ledRows ?? []) as ProjectRow[]) {
    byId.set(row.id, { id: row.id, label: projectLabel(row), isLead: true, isCreator: false })
  }
  for (const row of (createdRows ?? []) as ProjectRow[]) {
    const existing = byId.get(row.id)
    if (existing) existing.isCreator = true
    else byId.set(row.id, { id: row.id, label: projectLabel(row), isLead: false, isCreator: true })
  }
  const leadProjects = [...byId.values()]

  // assignments/project_consultants are the "history" soft-blockers (product rule:
  // keep people with planning history). leadProjects are NOT a blocker here —
  // they are auto-reassigned by the delete flow (see lib/employees/reassign-lead.ts),
  // so they only gate the *warning*, not canHardDelete.
  const softCount = (assignmentCount ?? 0) + (projectCount ?? 0)
  const total = softCount + leadProjects.length
  return {
    assignmentCount: assignmentCount ?? 0,
    projectCount:    projectCount    ?? 0,
    leadProjects,
    total,
    canHardDelete: softCount === 0,
  }
}