import { inArray } from "drizzle-orm";
import { db, people, clients, projects, projectAssignments, type Office } from "@/db";
import type { WorkamajigSource } from "./types";

const officeFromName = (name?: string): Office => {
  const n = (name ?? "").toLowerCase();
  if (n.includes("boston")) return "BOSTON";
  if (n.includes("vancouver")) return "VANCOUVER";
  if (n.includes("dublin") || n.includes("ireland")) return "DUBLIN";
  return "REMOTE";
};

/** "Siobhán O'Reilly" -> "siobhan-oreilly" */
const slugify = (s: string) =>
  s.toLowerCase().normalize("NFKD").replace(/\p{M}/gu, "").replace(/['’]/g, "").replace(/[^a-z0-9]+/g, "-").replace(/(^-|-$)/g, "");
const toDate = (s?: string) => (s ? new Date(s) : null);

/**
 * Idempotent sync: upserts people (by email), clients, projects and assignments.
 * Never overwrites fields staff personalise themselves (bio, photo, interests, skills).
 * Safe to run nightly via /api/workamajig/sync or `npm run wmj:sync`.
 */
export async function syncWorkamajig(source: WorkamajigSource) {
  const [users, clientRows, projectRows, assignmentRows] = await Promise.all([
    source.users(), source.clients(), source.projects(), source.assignments(),
  ]);
  const counts = { people: 0, clients: 0, projects: 0, assignments: 0 };

  for (const u of users) {
    if (!u.email) continue;
    const slug = slugify(`${u.firstName}-${u.lastName}`) || slugify(u.email.split("@")[0]);
    await db.insert(people).values({
      email: u.email.toLowerCase(), slug, workamajigId: u.userKey, firstName: u.firstName, lastName: u.lastName,
      title: u.title ?? "Team member", department: u.department ?? "Unassigned", office: officeFromName(u.officeName), isActive: u.isActive,
    }).onConflictDoUpdate({
      target: people.email,
      set: { workamajigId: u.userKey, title: u.title ?? undefined, department: u.department ?? undefined, isActive: u.isActive, updatedAt: new Date() },
    });
    counts.people++;
  }

  const clientIdByKey = new Map<string, string>();
  for (const c of clientRows) {
    const [row] = await db.insert(clients).values({ name: c.clientName, workamajigId: c.clientKey, vertical: c.industry ?? null })
      .onConflictDoUpdate({ target: clients.name, set: { workamajigId: c.clientKey, vertical: c.industry ?? undefined } })
      .returning({ id: clients.id });
    clientIdByKey.set(c.clientKey, row.id);
    counts.clients++;
  }

  const projectIdByKey = new Map<string, string>();
  for (const p of projectRows) {
    let clientId = p.clientKey ? clientIdByKey.get(p.clientKey) : undefined;
    if (!clientId && p.clientName) {
      const [c] = await db.insert(clients).values({ name: p.clientName }).onConflictDoUpdate({ target: clients.name, set: { name: p.clientName } }).returning({ id: clients.id });
      clientId = c.id;
    }
    const values = {
      workamajigId: p.projectKey, number: p.projectNumber ?? null, name: p.projectName, description: p.description ?? null, status: p.status ?? null,
      startDate: toDate(p.startDate), endDate: toDate(p.endDate), clientId: clientId ?? null, services: p.services ?? [], syncedAt: new Date(),
    };
    const [row] = await db.insert(projects).values(values).onConflictDoUpdate({ target: projects.workamajigId, set: values }).returning({ id: projects.id });
    projectIdByKey.set(p.projectKey, row.id);
    counts.projects++;
  }

  const personRows = await db.select({ id: people.id, workamajigId: people.workamajigId }).from(people);
  const personIdByKey = new Map(personRows.filter((p) => p.workamajigId).map((p) => [p.workamajigId!, p.id]));

  for (const a of assignmentRows) {
    const personId = personIdByKey.get(a.userKey);
    const projectId = projectIdByKey.get(a.projectKey);
    if (!personId || !projectId) continue;
    const values = { personId, projectId, role: a.role ?? null, hours: a.hours, firstSeen: toDate(a.firstDate), lastSeen: toDate(a.lastDate) };
    await db.insert(projectAssignments).values(values).onConflictDoUpdate({ target: [projectAssignments.personId, projectAssignments.projectId], set: values });
    counts.assignments++;
  }

  // Deactivate people Workamajig no longer lists as active (never delete – history stays).
  const activeKeys = users.filter((u) => u.isActive).map((u) => u.userKey);
  if (activeKeys.length) {
    const stale = personRows.filter((p) => p.workamajigId && !activeKeys.includes(p.workamajigId)).map((p) => p.id);
    if (stale.length) await db.update(people).set({ isActive: false }).where(inArray(people.id, stale));
  }
  return counts;
}