type ExportTask = {
  day: number;
  client: string;
  type: string;
  number: number;
  role: string;
  completed: boolean;
};

type ExportAttendance = {
  attendance_date: string;
  check_in_at: string;
  check_out_at: string | null;
  check_in_location: "office" | "home";
  check_out_location?: "office" | "home" | null;
  check_in_distance_meters: number;
  late?: boolean;
  incomplete?: boolean;
  working_minutes?: number | null;
  profile?: { full_name?: string; designation?: string };
};

export type InvoiceItem = { description: string; price: number; quantity: number };
export type CashFlowRecord = { id: string; type: "income" | "expense"; category: string; description: string; amount: number; date: string; status: "cleared" | "pending"; createdAt: string };
export type InvoiceDocument = {
  invoiceNumber: string; invoiceDate: string; dueDate: string; billingMonth: string;
  senderName: string; senderAddress: string; clientName: string; clientAddress: string; clientPhone: string;
  items: InvoiceItem[]; paid: number; paymentTitle: string; accountNumber: string; accountName: string;
  bankName: string; branchName: string; mobileBanking: string; terms: string; signedBy: string; signerRole: string;
};

export async function buildInvoicePdf(invoice: InvoiceDocument, logoBytes?: Uint8Array) {
  const { jsPDF } = await import("jspdf");
  const autoTable = (await import("jspdf-autotable")).default;
  const doc = new jsPDF({ unit: "mm", format: "a4" });
  const pink: [number, number, number] = [238, 22, 83];
  const dark: [number, number, number] = [55, 55, 54];
  const subtotal = invoice.items.reduce((sum, item) => sum + item.price * item.quantity, 0);
  const due = Math.max(0, subtotal - invoice.paid);
  const money = (amount: number) => `${amount.toLocaleString("en-BD")} /-`;
  doc.setFillColor(...dark); doc.roundedRect(7, 7, 196, 32, 8, 8, "F");
  if (logoBytes?.length) {
    doc.addImage(logoBytes, "PNG", 18, 14.5, 52, 16.6);
  } else {
    doc.setTextColor(...pink); doc.setFont("helvetica", "bold"); doc.setFontSize(18); doc.text(invoice.senderName, 18, 26);
  }
  doc.setFillColor(255, 255, 255); doc.roundedRect(128, 11, 69, 28, 7, 7, "F");
  doc.setTextColor(0, 0, 0); doc.setFontSize(21); doc.text("INVOICE", 162.5, 28.5, { align: "center" });
  doc.setFontSize(8.5); doc.setFont("helvetica", "bold"); doc.text(`INVOICE #  ${invoice.invoiceNumber}`, 22, 55);
  doc.setFontSize(7.3); doc.text(`INVOICE DATE  :  ${invoice.invoiceDate}`, 22, 66); doc.text(`DUE DATE       :  ${invoice.dueDate}`, 22, 73); doc.text(`BILLING MONTH  :  ${invoice.billingMonth}`, 22, 80);
  doc.setFontSize(9.5); doc.text("BILL TO", 117, 55); doc.setFontSize(7.8); doc.text(invoice.clientName, 117, 66);
  doc.setFont("helvetica", "normal"); doc.setFontSize(7.2); doc.text(doc.splitTextToSize(invoice.clientAddress, 76), 117, 73); doc.setFont("helvetica", "bold"); doc.text(invoice.clientPhone, 117, 88);
  autoTable(doc, { startY: 95, tableWidth: 196, head: [["NO", "DESCRIPTION", "PRICE", "QTY", "TOTAL"]], body: invoice.items.map((item, index) => [String(index + 1), item.description, money(item.price), String(item.quantity).padStart(2, "0"), money(item.price * item.quantity)]), theme: "plain", styles: { fontSize: 7.8, cellPadding: 4, fillColor: [245, 245, 246], textColor: [20, 20, 20], valign: "middle" }, headStyles: { fillColor: pink, textColor: [255, 255, 255], fontStyle: "bold", fontSize: 8.5 }, columnStyles: { 0: { cellWidth: 16, halign: "center" }, 1: { cellWidth: 80 }, 2: { cellWidth: 34, halign: "right" }, 3: { cellWidth: 22, halign: "center" }, 4: { cellWidth: 44, halign: "right" } }, alternateRowStyles: { fillColor: [238, 238, 239] }, margin: { left: 7, right: 7 } });
  const tableEnd = Math.max(124, (doc as unknown as { lastAutoTable?: { finalY: number } }).lastAutoTable?.finalY ?? 124);
  const totalsY = Math.min(tableEnd + 8, 194);
  doc.setFillColor(245, 245, 246); doc.rect(123, totalsY, 80, 27, "F"); doc.setTextColor(0, 0, 0); doc.setFont("helvetica", "bold"); doc.setFontSize(8.5);
  doc.text("SUB-TOTAL", 129, totalsY + 7); doc.text(money(subtotal), 196, totalsY + 7, { align: "right" }); doc.text("PAID", 129, totalsY + 14); doc.text(money(invoice.paid), 196, totalsY + 14, { align: "right" }); doc.text("DUE", 129, totalsY + 21); doc.text(money(due), 196, totalsY + 21, { align: "right" });
  doc.setFillColor(...pink); doc.rect(123, totalsY + 27, 80, 12, "F"); doc.setTextColor(255, 255, 255); doc.setFontSize(9.5); doc.text("TOTAL DUE", 129, totalsY + 35); doc.text(money(due), 196, totalsY + 35, { align: "right" });
  const detailsY = Math.min(totalsY + 6, 200); doc.setTextColor(0, 0, 0); doc.setFontSize(8.5); doc.text(invoice.paymentTitle || "PAYMENT METHOD", 23, detailsY);
  doc.setFont("helvetica", "normal"); doc.setFontSize(7.3); [`Account Number  :  ${invoice.accountNumber}`, `Account Name     :  ${invoice.accountName}`, `Bank Name        :  ${invoice.bankName}`, `Branch Name      :  ${invoice.branchName}`, `Mobile Banking   :  ${invoice.mobileBanking}`].forEach((line, index) => doc.text(line, 23, detailsY + 8 + index * 6));
  doc.setFont("helvetica", "bold"); doc.setFontSize(8.5); doc.text("TERMS AND CONDITIONS", 23, 235); doc.setFont("helvetica", "normal"); doc.setFontSize(7); doc.text(doc.splitTextToSize(invoice.terms, 103), 23, 242);
  doc.setFont("helvetica", "bold"); doc.setFontSize(9.5); doc.text(invoice.signedBy, 166, 237, { align: "center" }); doc.setFont("helvetica", "normal"); doc.setFontSize(7); doc.text(invoice.signerRole, 166, 244, { align: "center" });
  doc.setFont("helvetica", "bold"); doc.setFontSize(9); doc.text("THANK YOU FOR CHOOSING US!", 105, 270, { align: "center" }); doc.setFillColor(...dark); doc.roundedRect(7, 276, 196, 15, 7, 7, "F"); doc.setTextColor(255, 255, 255); doc.setFont("helvetica", "normal"); doc.setFontSize(7); doc.text(invoice.senderAddress, 105, 285, { align: "center", maxWidth: 180 });
  return doc;
}

export async function exportInvoicePdf(invoice: InvoiceDocument) {
  const blob = await createInvoicePdfBlob(invoice);
  const url = URL.createObjectURL(blob);
  const link = document.createElement("a");
  link.href = url;
  link.download = `Invoice-${invoice.invoiceNumber}.pdf`;
  link.click();
  window.setTimeout(() => URL.revokeObjectURL(url), 1000);
}

export async function createInvoicePdfBlob(invoice: InvoiceDocument) {
  let logoBytes: Uint8Array | undefined;
  try {
    const response = await fetch("/dmark-network-logo.png");
    if (response.ok) logoBytes = new Uint8Array(await response.arrayBuffer());
  } catch { /* Use the business-name fallback if the logo is unavailable. */ }
  const doc = await buildInvoicePdf(invoice, logoBytes);
  return doc.output("blob");
}

const attendanceRows = (records: ExportAttendance[]) => records.map((row) => ({
  Member: row.profile?.full_name ?? "Team member",
  Designation: row.profile?.designation ?? "",
  Date: row.attendance_date,
  "Check in": new Date(row.check_in_at).toLocaleTimeString([], { hour: "2-digit", minute: "2-digit" }),
  "Check out": row.check_out_at ? new Date(row.check_out_at).toLocaleTimeString([], { hour: "2-digit", minute: "2-digit" }) : "Not recorded",
  Location: row.check_in_location === "office" ? "Office" : "Home / Remote",
  Status: row.incomplete ? "Incomplete checkout" : row.late ? "Late" : "On time",
  "Working hours": row.working_minutes == null ? "—" : `${Math.floor(row.working_minutes / 60)}h ${row.working_minutes % 60}m`,
  "Office distance": `${row.check_in_distance_meters} m`,
}));

export async function exportAttendanceWorkbook(records: ExportAttendance[], month: string) {
  const XLSX = await import("xlsx");
  const workbook = XLSX.utils.book_new();
  const sheet = XLSX.utils.json_to_sheet(attendanceRows(records));
  sheet["!cols"] = [{ wch: 24 }, { wch: 22 }, { wch: 13 }, { wch: 12 }, { wch: 14 }, { wch: 16 }, { wch: 20 }, { wch: 16 }, { wch: 16 }];
  sheet["!autofilter"] = { ref: `A1:I${records.length + 1}` };
  XLSX.utils.book_append_sheet(workbook, sheet, "Attendance");
  XLSX.writeFile(workbook, `Planexa-Attendance-${month}.xlsx`);
}

export async function exportCashFlowWorkbook(records: CashFlowRecord[], from: string, to: string) {
  const XLSX = await import("xlsx");
  const workbook = XLSX.utils.book_new();
  const sorted = records.slice().sort((a, b) => a.date.localeCompare(b.date) || a.createdAt.localeCompare(b.createdAt));
  let runningBalance = 0;
  const ledgerRows = sorted.map((record) => {
    if (record.status === "cleared") runningBalance += record.type === "income" ? record.amount : -record.amount;
    return {
      Date: new Date(`${record.date}T00:00:00`),
      Type: record.type === "income" ? "Income" : "Expense",
      Category: record.category,
      Description: record.description,
      Status: record.status === "cleared" ? "Cleared" : "Pending",
      "Inflow (BDT)": record.type === "income" ? record.amount : 0,
      "Outflow (BDT)": record.type === "expense" ? record.amount : 0,
      "Period running balance (BDT)": runningBalance,
    };
  });
  const ledger = XLSX.utils.json_to_sheet(ledgerRows, { header: ["Date", "Type", "Category", "Description", "Status", "Inflow (BDT)", "Outflow (BDT)", "Period running balance (BDT)"] });
  ledger["!cols"] = [{ wch: 13 }, { wch: 11 }, { wch: 20 }, { wch: 42 }, { wch: 12 }, { wch: 17 }, { wch: 17 }, { wch: 29 }];
  ledger["!autofilter"] = { ref: `A1:H${Math.max(2, ledgerRows.length + 1)}` };
  for (let row = 2; row <= ledgerRows.length + 1; row++) {
    if (ledger[`A${row}`]) ledger[`A${row}`].z = "dd/mm/yyyy";
    for (const column of ["F", "G", "H"]) if (ledger[`${column}${row}`]) ledger[`${column}${row}`].z = "#,##0.00;[Red](#,##0.00);-";
  }
  XLSX.utils.book_append_sheet(workbook, ledger, "Cash Flow");

  const lastRow = Math.max(2, ledgerRows.length + 1);
  const summary = XLSX.utils.aoa_to_sheet([
    ["Planexa Cash Flow Summary", ""], ["Period", `${from.split("-").reverse().join("/")} to ${to.split("-").reverse().join("/")}`], ["Generated", new Date()], ["", ""],
    ["Metric", "Amount (BDT)"], ["Cleared inflow", ""], ["Cleared outflow", ""], ["Net cleared cash flow", ""],
    ["Pending receivable", ""], ["Pending payable", ""], ["Transactions", records.length],
  ]);
  summary["B6"] = { t: "n", f: `SUMIFS('Cash Flow'!F2:F${lastRow},'Cash Flow'!E2:E${lastRow},"Cleared")` };
  summary["B7"] = { t: "n", f: `SUMIFS('Cash Flow'!G2:G${lastRow},'Cash Flow'!E2:E${lastRow},"Cleared")` };
  summary["B8"] = { t: "n", f: "B6-B7" };
  summary["B9"] = { t: "n", f: `SUMIFS('Cash Flow'!F2:F${lastRow},'Cash Flow'!E2:E${lastRow},"Pending")` };
  summary["B10"] = { t: "n", f: `SUMIFS('Cash Flow'!G2:G${lastRow},'Cash Flow'!E2:E${lastRow},"Pending")` };
  summary["B3"].z = "yyyy-mm-dd hh:mm";
  for (let row = 6; row <= 10; row++) summary[`B${row}`].z = "#,##0.00;[Red](#,##0.00);-";
  summary["!cols"] = [{ wch: 27 }, { wch: 26 }];
  XLSX.utils.book_append_sheet(workbook, summary, "Summary");

  const categories = [...new Set(sorted.map(record => record.category))].sort();
  const categoryRows = categories.map(category => ({
    Category: category,
    "Income (BDT)": sorted.filter(record => record.category === category && record.type === "income").reduce((sum, record) => sum + record.amount, 0),
    "Expense (BDT)": sorted.filter(record => record.category === category && record.type === "expense").reduce((sum, record) => sum + record.amount, 0),
  }));
  const categorySheet = XLSX.utils.json_to_sheet(categoryRows, { header: ["Category", "Income (BDT)", "Expense (BDT)"] });
  categorySheet["!cols"] = [{ wch: 24 }, { wch: 18 }, { wch: 18 }];
  categorySheet["!autofilter"] = { ref: `A1:C${Math.max(2, categoryRows.length + 1)}` };
  for (let row = 2; row <= categoryRows.length + 1; row++) for (const column of ["B", "C"]) if (categorySheet[`${column}${row}`]) categorySheet[`${column}${row}`].z = "#,##0.00;[Red](#,##0.00);-";
  XLSX.utils.book_append_sheet(workbook, categorySheet, "Category Summary");
  XLSX.writeFile(workbook, `Planexa-Cash-Flow-${from}-to-${to}.xlsx`);
}

export async function exportAttendancePdf(records: ExportAttendance[], month: string) {
  const { jsPDF } = await import("jspdf");
  const autoTable = (await import("jspdf-autotable")).default;
  const doc = new jsPDF({ orientation: "landscape", unit: "mm", format: "a4" });
  doc.setFont("helvetica", "bold");
  doc.setFontSize(17);
  doc.text("Planexa Attendance Report", 14, 16);
  doc.setFont("helvetica", "normal");
  doc.setFontSize(9);
  doc.text(month, 14, 23);
  const rows = attendanceRows(records);
  autoTable(doc, { startY: 29, head: [["Member", "Date", "Check in", "Check out", "Location", "Status", "Hours"]], body: rows.map((row) => [row.Member, row.Date, row["Check in"], row["Check out"], row.Location, row.Status, row["Working hours"]]), theme: "grid", styles: { fontSize: 7.5 }, headStyles: { fillColor: [116, 88, 232] } });
  doc.save(`Planexa-Attendance-${month}.pdf`);
}

export async function exportTasksToExcel(tasks: ExportTask[], month = "August", year = 2026) {
  const XLSX = await import("xlsx");
  const rows = tasks.map((task) => ({
    Date: `${month} ${task.day}, ${year}`,
    Client: task.client,
    Task: `${task.type} ${task.number}`,
    Employee: task.role,
    Status: task.completed ? "Completed" : "Pending",
  }));
  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, XLSX.utils.json_to_sheet(rows), "Monthly Plan");
  XLSX.writeFile(workbook, `DMark-${month}-${year}-Plan.xlsx`);
}

export async function exportDepartmentTaskWorkbook(tasks: ExportTask[], month = "August", year = 2026) {
  const { XLSX, workbook } = await buildDepartmentWorkbook(tasks, month, year);
  XLSX.writeFile(workbook, `Planexa-${month}-${year}-Department-Tasks.xlsx`);
}

async function buildDepartmentWorkbook(tasks: ExportTask[], month = "August", year = 2026) {
  const XLSX = await import("xlsx");
  const workbook = XLSX.utils.book_new();
  const appendSheet = (role: string, name: string) => {
    const roleTasks = tasks.filter((task) => task.role === role).sort((a, b) => a.day - b.day || a.client.localeCompare(b.client) || a.number - b.number);
    const rows: (string | boolean)[][] = [["Date", "Client", "Status", "Content"]];
    const merges: { s: { r: number; c: number }; e: { r: number; c: number } }[] = [];
    let cursor = 1;
    for (const day of [...new Set(roleTasks.map((task) => task.day))]) {
      const daily = roleTasks.filter((task) => task.day === day);
      const start = cursor;
      daily.forEach((task, index) => {
        rows.push([index === 0 ? `${month} ${day}, ${year}` : "", task.client, task.completed, ""]);
        cursor++;
      });
      if (daily.length > 1) merges.push({ s: { r: start, c: 0 }, e: { r: cursor - 1, c: 0 } });
    }
    const sheet = XLSX.utils.aoa_to_sheet(rows);
    sheet["!merges"] = merges;
    sheet["!cols"] = [{ wch: 20 }, { wch: 30 }, { wch: 12 }, { wch: 55 }];
    sheet["!autofilter"] = { ref: `A1:D${rows.length}` };
    XLSX.utils.book_append_sheet(workbook, sheet, name);
  };
  appendSheet("Graphic Designer", "Graphic Designer - Static");
  appendSheet("Video Editor", "Video Editor - Video");
  return { XLSX, workbook };
}

export async function openDepartmentTasksInGoogleSheets(tasks: ExportTask[], month = "August", year = 2026) {
  const target = window.open("about:blank", "_blank");
  const clientId = process.env.NEXT_PUBLIC_GOOGLE_CLIENT_ID;
  if (!clientId) {
    const rows = [["Date", "Client", "Department", "Status", "Content"], ...tasks
      .slice()
      .sort((a, b) => a.day - b.day || a.role.localeCompare(b.role) || a.client.localeCompare(b.client))
      .map((task) => [`${month} ${task.day}, ${year}`, task.client, task.role, task.completed ? "Completed" : "Pending", ""] )];
    await navigator.clipboard.writeText(rows.map((row) => row.join("\t")).join("\n"));
    if (target) target.location.href = "https://docs.google.com/spreadsheets/u/0/create";
    return "clipboard" as const;
  }

  const { XLSX, workbook } = await buildDepartmentWorkbook(tasks, month, year);
  const file = XLSX.write(workbook, { type: "array", bookType: "xlsx" });
  if (!(window as unknown as { google?: unknown }).google) {
    await new Promise<void>((resolve, reject) => {
      const script = document.createElement("script");
      script.src = "https://accounts.google.com/gsi/client";
      script.onload = () => resolve();
      script.onerror = () => reject(new Error("Google connection could not be loaded"));
      document.head.appendChild(script);
    });
  }
  const google = (window as unknown as { google: { accounts: { oauth2: { initTokenClient: (config: { client_id: string; scope: string; callback: (response: { access_token?: string; error?: string }) => void }) => { requestAccessToken: () => void } } } } }).google;
  const accessToken = await new Promise<string>((resolve, reject) => {
    const tokenClient = google.accounts.oauth2.initTokenClient({
      client_id: clientId,
      scope: "https://www.googleapis.com/auth/drive.file",
      callback: (response) => response.access_token ? resolve(response.access_token) : reject(new Error(response.error ?? "Google authorization failed")),
    });
    tokenClient.requestAccessToken();
  });
  const boundary = `planexa_${Date.now()}`;
  const metadata = JSON.stringify({ name: `Planexa ${month} ${year} Department Tasks`, mimeType: "application/vnd.google-apps.spreadsheet" });
  const body = new Blob([
    `--${boundary}\r\nContent-Type: application/json; charset=UTF-8\r\n\r\n${metadata}\r\n`,
    `--${boundary}\r\nContent-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\r\n\r\n`,
    file,
    `\r\n--${boundary}--`,
  ]);
  const response = await fetch("https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart&fields=id,webViewLink", {
    method: "POST",
    headers: { Authorization: `Bearer ${accessToken}`, "Content-Type": `multipart/related; boundary=${boundary}` },
    body,
  });
  const created = await response.json();
  if (!response.ok || !created.webViewLink) throw new Error(created.error?.message ?? "Google Sheet could not be created");
  if (target) target.location.href = created.webViewLink;
  return "direct" as const;
}

export async function exportRoleTaskSheets(tasks: ExportTask[], month = "August", year = 2026) {
  const { jsPDF } = await import("jspdf");
  const autoTable = (await import("jspdf-autotable")).default;
  for (const role of ["Graphic Designer", "Video Editor"]) {
    const doc = new jsPDF({ unit: "mm", format: "a4" });
    doc.setFont("helvetica", "bold");
    doc.setFontSize(18);
    doc.text("DMark Production Planner", 14, 18);
    doc.setFontSize(12);
    doc.text(`${role} Monthly Task Sheet`, 14, 27);
    doc.setFont("helvetica", "normal");
    doc.setFontSize(9);
    doc.text(`${month} ${year}`, 14, 34);
    autoTable(doc, {
      startY: 40,
      head: [["Date", "Task", "Status"]],
      body: tasks.filter((task) => task.role === role).map((task) => [
        `${month} ${task.day}`,
        `${task.client} — ${task.type} ${task.number}`,
        task.completed ? "Completed" : "□",
      ]),
      theme: "grid",
      styles: { fontSize: 8, cellPadding: 2.5 },
      headStyles: { fillColor: [116, 88, 232] },
      columnStyles: { 0: { cellWidth: 28 }, 2: { cellWidth: 30 } },
    });
    doc.save(`DMark-${role.replaceAll(" ", "-")}-${month}-${year}.pdf`);
  }
}