import { createServerFn } from "@tanstack/react-start";
import { z } from "zod";
import * as XLSX from "xlsx";
import { getSql } from "@/lib/db";
import { LOCATIONS, ROLES } from "@/lib/careers";

const locationIds = LOCATIONS.map((l) => l.id) as [string, ...string[]];
const roleIds = ROLES.map((r) => r.id) as [string, ...string[]];

export const applicantSchema = z.object({
  name: z.string().trim().min(2, "Enter your full name").max(120),
  email: z.string().trim().email("Enter a valid email").max(200),
  phone: z
    .string()
    .trim()
    .min(7, "Enter a valid phone number")
    .max(30)
    .regex(/^[0-9()+\-.\s]+$/, "Use digits and phone characters only"),
  age: z
    .number({ error: "Enter a valid age" })
    .int()
    .min(16, "Must be at least 16")
    .max(99, "Enter a valid age"),
  location: z.enum(locationIds, { error: "Select a location" }),
  role: z.enum(roleIds, { error: "Select a role" }),
  notes: z.string().trim().max(1000).optional().or(z.literal("")),
});

export type ApplicantInput = z.infer<typeof applicantSchema>;

export type ApplicantRow = {
  id: string;
  name: string;
  email: string;
  phone: string;
  age: number;
  location: string;
  role: string;
  notes: string | null;
  created_at: string;
};

function newId() {
  return crypto.randomUUID();
}

function labelForLocation(id: string) {
  return LOCATIONS.find((l) => l.id === id)?.label ?? id;
}

function labelForRole(id: string) {
  return ROLES.find((r) => r.id === id)?.title ?? id;
}

export async function fetchApplicants(): Promise<ApplicantRow[]> {
  const sql = await getSql();
  return sql<ApplicantRow>`
    select id, name, email, phone, age, location, role, notes, created_at
    from applicants
    order by created_at desc
  `;
}

export function applicantsToWorkbook(rows: ApplicantRow[]) {
  const sheetRows = rows.map((row, index) => ({
    "#": index + 1,
    Name: row.name,
    Email: row.email,
    Phone: row.phone,
    Age: row.age,
    Location: labelForLocation(row.location),
    Role: labelForRole(row.role),
    "Submitted At": row.created_at,
    "Application ID": row.id,
  }));

  const worksheet = XLSX.utils.json_to_sheet(
    sheetRows.length
      ? sheetRows
      : [
          {
            "#": "",
            Name: "",
            Email: "",
            Phone: "",
            Age: "",
            Location: "",
            Role: "",
            "Submitted At": "",
            "Application ID": "",
          },
        ],
  );

  worksheet["!cols"] = [
    { wch: 4 },
    { wch: 22 },
    { wch: 28 },
    { wch: 16 },
    { wch: 6 },
    { wch: 28 },
    { wch: 26 },
    { wch: 22 },
    { wch: 36 },
  ];

  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, "Applicants");
  return workbook;
}

export function workbookToBuffer(workbook: XLSX.WorkBook): Buffer {
  const data = XLSX.write(workbook, { type: "buffer", bookType: "xlsx" }) as Buffer;
  return data;
}

export const submitApplication = createServerFn({ method: "POST" })
  .validator((data: unknown) => applicantSchema.parse(data))
  .handler(async ({ data }) => {
    const sql = await getSql();
    const id = newId();
    const notes = data.notes?.trim() ? data.notes.trim() : null;

    await sql`
      insert into applicants (id, name, email, phone, age, location, role, notes)
      values (
        ${id},
        ${data.name},
        ${data.email.toLowerCase()},
        ${data.phone},
        ${data.age},
        ${data.location},
        ${data.role},
        ${notes}
      )
    `;

    return { ok: true as const, id };
  });

export const listApplicants = createServerFn({ method: "GET" }).handler(async () => {
  return fetchApplicants();
});