const { splitSite } = require("../middlewares");
const db = require("../models");
const fs = require("fs");
const path = require("path");
const crypto = require("crypto");
const moment = require("moment-timezone");
const archiver = require("archiver");
const { PDFDocument, rgb } = require("pdf-lib");
const fontkit = require("@pdf-lib/fontkit");
const Report = db.report;
const RoutePath = db.route_path;
const Employee = db.employee;
const Vehicle = db.vehicle;
const Customer = db.customer;

const ROOT_DIR = path.resolve(__dirname, "../..");
const TARGET_TIMEZONE = "America/New_York";

const sanitizeFileName = (name) =>
  (name || "route")
    .toString()
    .replace(/[\\/:*?"<>|]/g, "_")
    .trim();

const toObjectIdText = (value) => {
  if (!value) return "";
  if (typeof value === "string") return value;
  if (typeof value === "object" && value._id) return `${value._id}`;
  return `${value}`;
};
const normalizeCustomerName = (name) => (name || "").toString().trim().toLowerCase().replace(/\s+/g, " ");

const findTemplatePathBySite = (site) => {
  const safeSite = [1, 2, 3].includes(Number(site)) ? Number(site) : 1;
  const fileName = `pdf_templete${safeSite}.pdf`;
  const cwd = process.cwd();
  const candidatePaths = [
    path.join(ROOT_DIR, "app", "views", "upload", fileName),
    path.join(ROOT_DIR, "client", "build", "upload", fileName),
    path.join(ROOT_DIR, "client", "public", "upload", fileName),
    path.join(cwd, "app", "views", "upload", fileName),
    path.join(cwd, "client", "build", "upload", fileName),
    path.join(cwd, "client", "public", "upload", fileName),
    path.join("/www/wwwroot/upload", fileName),
    path.join(`/www/wwwroot/worldshine${safeSite}`, "app", "views", "upload", fileName),
    path.join(`/www/wwwroot/worldshine${safeSite}-tspt`, "app", "views", "upload", fileName)
  ];
  return candidatePaths.find((candidate) => fs.existsSync(candidate)) || "";
};

const findUnicodeFontPath = (site) => {
  const safeSite = [1, 2, 3].includes(Number(site)) ? Number(site) : 1;
  const primaryFileName = "NotoSansSC-Regular.ttf";
  const secondaryFileName = "NotoSansSC-VF.ttf";
  const fallbackFileName = "NotoSansCJKsc-Regular.otf";
  const cwd = process.cwd();
  const candidatePaths = [
    path.join(ROOT_DIR, "app", "assets", "fonts", primaryFileName),
    path.join(ROOT_DIR, "app", "views", "upload", primaryFileName),
    path.join(ROOT_DIR, "client", "build", "upload", primaryFileName),
    path.join(ROOT_DIR, "client", "public", "upload", primaryFileName),
    path.join(cwd, "app", "assets", "fonts", primaryFileName),
    path.join(cwd, "app", "views", "upload", primaryFileName),
    path.join(cwd, "client", "build", "upload", primaryFileName),
    path.join(cwd, "client", "public", "upload", primaryFileName),
    path.join("/www/wwwroot/upload", primaryFileName),
    path.join(`/www/wwwroot/worldshine${safeSite}`, "app", "assets", "fonts", primaryFileName),
    path.join(`/www/wwwroot/worldshine${safeSite}-tspt`, "app", "assets", "fonts", primaryFileName),
    path.join(ROOT_DIR, "app", "assets", "fonts", secondaryFileName),
    path.join(ROOT_DIR, "app", "views", "upload", secondaryFileName),
    path.join(ROOT_DIR, "client", "build", "upload", secondaryFileName),
    path.join(ROOT_DIR, "client", "public", "upload", secondaryFileName),
    path.join(cwd, "app", "assets", "fonts", secondaryFileName),
    path.join(cwd, "app", "views", "upload", secondaryFileName),
    path.join(cwd, "client", "build", "upload", secondaryFileName),
    path.join(cwd, "client", "public", "upload", secondaryFileName),
    path.join("/www/wwwroot/upload", secondaryFileName),
    path.join(`/www/wwwroot/worldshine${safeSite}`, "app", "assets", "fonts", secondaryFileName),
    path.join(`/www/wwwroot/worldshine${safeSite}-tspt`, "app", "assets", "fonts", secondaryFileName),
    path.join(ROOT_DIR, "app", "assets", "fonts", fallbackFileName),
    path.join(ROOT_DIR, "app", "views", "upload", fallbackFileName),
    path.join(ROOT_DIR, "client", "build", "upload", fallbackFileName),
    path.join(ROOT_DIR, "client", "public", "upload", fallbackFileName),
    path.join(cwd, "app", "assets", "fonts", fallbackFileName),
    path.join(cwd, "app", "views", "upload", fallbackFileName),
    path.join(cwd, "client", "build", "upload", fallbackFileName),
    path.join(cwd, "client", "public", "upload", fallbackFileName),
    path.join("/www/wwwroot/upload", fallbackFileName),
    path.join(`/www/wwwroot/worldshine${safeSite}`, "app", "assets", "fonts", fallbackFileName),
    path.join(`/www/wwwroot/worldshine${safeSite}-tspt`, "app", "assets", "fonts", fallbackFileName)
  ];
  return candidatePaths.find((candidate) => fs.existsSync(candidate)) || "";
};

const formatUtcToLocalHm = (dateLike) => {
  if (!dateLike) return "";
  const parsed = moment.utc(dateLike);
  if (!parsed.isValid()) return "";
  return parsed.tz(TARGET_TIMEZONE).format("HH:mm");
};

const safeSetField = (form, fieldName, value, options = {}) => {
  if (!fieldName || value === undefined || value === null) return;
  const text = `${value}`;
  try {
    const field = form.getTextField(fieldName);
    if (typeof options.fontSize === "number") {
      field.setFontSize(options.fontSize);
    }
    if (options.center === true) {
      field.setAlignment(1);
    }
    if (options.blackText === true) {
      field.setTextColor(rgb(0, 0, 0));
    }
    field.setText(text);
    return;
  } catch (_textErr) {}
  try {
    const checkbox = form.getCheckBox(fieldName);
    if (text.trim()) {
      checkbox.check();
    } else {
      checkbox.uncheck();
    }
  } catch (_checkboxErr) {}
};

const findOutboundStatusByCustomerId = (outboundCustomerStatuses, customerId) => {
  const targetId = toObjectIdText(customerId);
  return outboundCustomerStatuses.find((item) => toObjectIdText(item?.customer_id) === targetId) || {};
};

const normalizeRouteName = (name) => (name || "").toString().toLowerCase().replace(/\s+/g, "");

const findRelatedCustomersForReport = (route, customer, allRoutes) => {
  const customerId = toObjectIdText(customer?.customer_id);
  const oppositeType = route?.type === "inbound" ? "outbound" : "inbound";
  const sameDateOppositeRoutes = (allRoutes || []).filter(
    (item) => item?.type === oppositeType && item?.schedule_date === route?.schedule_date
  );
  const relativeRoute = sameDateOppositeRoutes.find(
    (item) => normalizeRouteName(item?.name) === normalizeRouteName(route?.name)
  );
  const relativeRouteCustomer = (relativeRoute?.route_customer_list || []).find(
    (item) => toObjectIdText(item?.customer_id) === customerId
  );
  const otherRoute = sameDateOppositeRoutes.find(
    (item) =>
      normalizeRouteName(item?.name) !== normalizeRouteName(route?.name) &&
      (item?.route_customer_list || []).some((itemCustomer) => toObjectIdText(itemCustomer?.customer_id) === customerId)
  );
  const customerInOtherRoute = (otherRoute?.route_customer_list || []).find(
    (item) => toObjectIdText(item?.customer_id) === customerId
  );
  const otherOutboundRoute = route?.type === "outbound"
    ? (allRoutes || []).find(
        (item) =>
          item?.type === "inbound" &&
          item?.schedule_date === route?.schedule_date &&
          toObjectIdText(item?._id || item?.id) !== toObjectIdText(route?._id || route?.id) &&
          (item?.route_customer_list || []).some((itemCustomer) => toObjectIdText(itemCustomer?.customer_id) === customerId)
      )
    : undefined;
  return { relativeRouteCustomer, customerInOtherRoute, otherRoute, otherOutboundRoute };
};

const hasEnterCenterTimeByCandidate = (...candidates) =>
  candidates.some((item) => item?.customer_enter_center_time !== undefined && item?.customer_enter_center_time !== null && item?.customer_enter_center_time !== "");

const hasInCenterStatusByCandidate = (...candidates) =>
  candidates.some((item) => (item?.customer_route_status || "").toString() === "inCenter");

const getTimeCandidate = (...values) => values.find((value) => value !== null && value !== undefined && value !== "");

const appendNoticeLine = (parts, text) => {
  const value = (text || "").toString().trim();
  if (!value) return;
  if (!parts.includes(value)) {
    parts.push(value);
  }
};

const collectFieldRects = (form, fieldName) => {
  try {
    const field = form.getTextField(fieldName);
    const widgets = field?.acroField?.getWidgets?.() || [];
    return widgets.map((widget) => widget.getRectangle());
  } catch (_err) {
    return [];
  }
};

const buildRoutePdfBuffer = async (templateBytes, route, seqNum, driversMap, vehiclesMap, outboundCustomerStatuses, unicodeFontBytes, allRoutes, customerNotesById, customerNotesByName, debugRows) => {
  const pdfDoc = await PDFDocument.load(templateBytes);
  pdfDoc.registerFontkit(fontkit);
  const unicodeFont = await pdfDoc.embedFont(unicodeFontBytes, { subset: true });
  const form = pdfDoc.getForm();
  const markDrawQueue = [];
  const routeName = route?.name || "";
  const scheduleDate = route?.schedule_date || "";
  const driverId = toObjectIdText(route?.driver);
  const vehicleId = toObjectIdText(route?.vehicle);
  const driver = driversMap.get(driverId);
  const vehicle = vehiclesMap.get(vehicleId);
  const driverName = driver
    ? `${driver.name || ""}${driver.name_cn ? driver.name_cn : ""}`
    : "";

  safeSetField(form, "route", routeName);
  safeSetField(form, "driver", driverName);
  safeSetField(form, "date", scheduleDate);
  safeSetField(form, "vehicle", vehicle?.vehicle_number || "");
  safeSetField(form, "seq_num", seqNum);

  const customers = Array.isArray(route?.route_customer_list) ? route.route_customer_list : [];
  customers.forEach((customer, index) => {
    const row = index + 1;
    const { relativeRouteCustomer, customerInOtherRoute, otherRoute, otherOutboundRoute } = findRelatedCustomersForReport(route, customer, allRoutes);
    safeSetField(form, `name_${row}`, customer?.customer_name || "");
    safeSetField(form, `addr_${row}`, customer?.customer_address || "");
    safeSetField(form, `phone_${row}`, customer?.customer_phone || "");
    const customerId = toObjectIdText(customer?.customer_id);
    const profileNoteToDriver = (
      customerNotesById?.get(customerId) ||
      customerNotesByName?.get(normalizeCustomerName(customer?.customer_name || "")) ||
      ""
    ).toString().trim();
    const customerNoteText = profileNoteToDriver;
    safeSetField(form, `note_${row}`, customerNoteText);

    const pickupTime = formatUtcToLocalHm(
      customer?.customer_pickup_time ||
      relativeRouteCustomer?.customer_pickup_time ||
      customerInOtherRoute?.customer_pickup_time
    );
    if (pickupTime) safeSetField(form, `pick_${row}`, pickupTime);

    const hasEnterCenterTime = hasEnterCenterTimeByCandidate(customer, relativeRouteCustomer, customerInOtherRoute);
    let yValue = "";
    let nValue = "";
    if (hasEnterCenterTime) {
      const enterCenterTime = formatUtcToLocalHm(
        customer?.customer_enter_center_time ||
        relativeRouteCustomer?.customer_enter_center_time ||
        customerInOtherRoute?.customer_enter_center_time
      );
      if (enterCenterTime) {
        safeSetField(form, `arrive_${row}`, enterCenterTime);
      }
      yValue = "Y";
      nValue = "";
      safeSetField(form, `y_${row}`, yValue, { fontSize: 12, center: true, blackText: true });
      safeSetField(form, `n_${row}`, nValue, { fontSize: 12, center: true, blackText: true });
      const yRects = collectFieldRects(form, `y_${row}`);
      yRects.forEach((rect) => markDrawQueue.push({ rect, text: "Y" }));
    } else if (hasInCenterStatusByCandidate(customer, relativeRouteCustomer, customerInOtherRoute)) {
      yValue = "Y";
      nValue = "";
      safeSetField(form, `y_${row}`, yValue, { fontSize: 12, center: true, blackText: true });
      safeSetField(form, `n_${row}`, nValue, { fontSize: 12, center: true, blackText: true });
      const yRects = collectFieldRects(form, `y_${row}`);
      yRects.forEach((rect) => markDrawQueue.push({ rect, text: "Y" }));
    } else {
      yValue = "";
      nValue = "N";
      safeSetField(form, `y_${row}`, yValue, { fontSize: 12, center: true, blackText: true });
      safeSetField(form, `n_${row}`, nValue, { fontSize: 12, center: true, blackText: true });
      const nRects = collectFieldRects(form, `n_${row}`);
      nRects.forEach((rect) => markDrawQueue.push({ rect, text: "N" }));
    }

    const outboundStatus = findOutboundStatusByCustomerId(outboundCustomerStatuses, customer?.customer_id);
    const leaveCenterTime = formatUtcToLocalHm(outboundStatus?.customer_leave_center_time);
    const dropoffTime = formatUtcToLocalHm(outboundStatus?.customer_dropoff_time);
    if (leaveCenterTime) safeSetField(form, `departure_${row}`, leaveCenterTime);
    if (dropoffTime) safeSetField(form, `drop_${row}`, dropoffTime);

    const noticeParts = [];
    appendNoticeLine(noticeParts, customerNoteText);
    if ((customer?.customer_type || "").toString().toLowerCase() !== "member" && customer?.customer_type) {
      appendNoticeLine(noticeParts, customer.customer_type);
    }
    if (!relativeRouteCustomer && otherRoute?.name) {
      appendNoticeLine(
        noticeParts,
        route?.type === "inbound"
          ? `Switch to Route ${otherRoute.name}`
          : `Switch from Route ${otherRoute.name}`
      );
    }
    if ((customer?.customer_route_status || "").toString() === "skipDropOff" && otherOutboundRoute?.name) {
      appendNoticeLine(noticeParts, `Switch to Route ${otherOutboundRoute.name}`);
    }

    if ((route?.type || "").toString() === "inbound") {
      const pickupRaw = getTimeCandidate(
        customer?.customer_pickup_time,
        relativeRouteCustomer?.customer_pickup_time,
        customerInOtherRoute?.customer_pickup_time
      );
      const enterRaw = getTimeCandidate(
        customer?.customer_enter_center_time,
        relativeRouteCustomer?.customer_enter_center_time,
        customerInOtherRoute?.customer_enter_center_time
      );
      if (pickupRaw && enterRaw) {
        const diffMs = new Date(enterRaw).getTime() - new Date(pickupRaw).getTime();
        const diffHours = diffMs / (1000 * 60 * 60);
        if (diffHours > 1) appendNoticeLine(noticeParts, "Rest Stop");
      }
    }

    if ((route?.type || "").toString() === "outbound") {
      const leaveRaw = getTimeCandidate(
        customer?.customer_leave_center_time,
        relativeRouteCustomer?.customer_leave_center_time,
        customerInOtherRoute?.customer_leave_center_time
      );
      const dropRaw = getTimeCandidate(
        customer?.customer_dropoff_time,
        relativeRouteCustomer?.customer_dropoff_time,
        customerInOtherRoute?.customer_dropoff_time
      );
      if (leaveRaw && dropRaw) {
        const diffMs = new Date(dropRaw).getTime() - new Date(leaveRaw).getTime();
        const diffHours = diffMs / (1000 * 60 * 60);
        if (diffHours > 1) appendNoticeLine(noticeParts, "Rest Stop");
      }
    }

    if (noticeParts.length > 0) safeSetField(form, `note_${row}`, noticeParts.join(" "));

    if (Array.isArray(debugRows)) {
      debugRows.push({
        route: route?.name || "",
        row,
        customer_id: customerId,
        customer_name: customer?.customer_name || "",
        hasEnterCenterTime,
        hasInCenterStatus: hasInCenterStatusByCandidate(customer, relativeRouteCustomer, customerInOtherRoute),
        y_field_value: yValue,
        n_field_value: nValue,
        note_to_driver: customerNoteText,
        final_notice: noticeParts.join(" ")
      });
    }
  });

  // Rebuild form appearances with a Unicode font so UTF-8 (e.g., Chinese) renders correctly.
  form.updateFieldAppearances(unicodeFont);
  form.flatten();
  const firstPage = pdfDoc.getPage(0);
  markDrawQueue.forEach(({ rect, text }) => {
    const fontSize = Math.min(Math.max(rect.height * 0.72, 9), 14);
    const textWidth = unicodeFont.widthOfTextAtSize(text, fontSize);
    const x = rect.x + Math.max((rect.width - textWidth) / 2, 1);
    const y = rect.y + Math.max((rect.height - fontSize) / 2, 1);
    firstPage.drawText(text, { x, y, size: fontSize, font: unicodeFont, color: rgb(0, 0, 0) });
  });
  return pdfDoc.save();
};
exports.createReport = (req, res) => {
  // Validate request
  if (!req.body.data) {
    res.status(400).send({ message: "Content can not be empty!" });
    return;
  }
  const site = splitSite.findSiteNumber(req);
  // Create a Report
  const report = new Report({
    date: req.body.date || '',
    type: req.body.type || '',
    route_id: req.body.route_id || '',
    driver_name: req.body.driver_name || '',
    route_name: req.body.route_name || '',
    data: req.body.data || [],
    head: req.body.head || [],
    chinese_head: req.body.chinese_head || [],
    checklist_result: req.body.checklist_result || [],
    vehicle_number: req.body.vehicle_number || null,
    site
  });

  // Save Report in the database
  report
    .save(report)
    .then(data => {
      res.send(data);
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while creating the Report."
      });
    });
};
// Retrieve all Reports from the database.
exports.getAllReports = (req, res) => {
  var condition = {};
  condition = splitSite.splitSiteGet(req, condition);
  Report.find(condition)
    .then(data => {
      res.send(data);
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving reports."
      });
    });
};
// Retrieve all Active Reports By Date and Type (Admin Reports).
exports.getReportsByDateAndType = (req, res) => {
  const params = req.query;
  const date = params?.date;
  const type = params?.type;
  var condition = { date, type };
  condition = splitSite.splitSiteGet(req, condition);
  Report.find(condition)
    .then(data => {
      res.send(data);
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving Reports with Date and Type."
      });
    });
};
// Retrieve reports By RouteId and Type (Senior Route report)
exports.getReportsByRouteIdAndType = (req, res) => {
  const params = req.query;
  const route_id = params?.route_id;
  const type = params?.type;
  var condition = { route_id, type };
  condition = splitSite.splitSiteGet(req, condition);
  Report.find(condition)
    .then(data => {
      res.send(data);
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving Reports with Date and Type."
      });
    });
};

// Get One Report by Id
exports.getReport = (req, res) => {

};
// Update a Report by the id in the request
exports.updateReport = (req, res) => {
  if (!req.body) {
    return res.status(400).send({
      message: "Data to update can not be empty!"
    });
  }
  const id = req.params.id;
  Report.findByIdAndUpdate(id, req.body, { useFindAndModify: false })
    .then(data => {
      if (!data) {
        res.status(404).send({
          message: `Cannot update Report with id=${id}. Maybe Report was not found!`
        });
      } else res.send({ success: true, message: "Report was updated successfully." });
    })
    .catch(err => {
      res.status(500).send({
        success: false,
        message: "Error updating Report with id=" + id
      });
    });
};

exports.exportRouteReportZip = async (req, res) => {
  try {
    const date = req.query?.date;
    const debugMode = `${req.query?.debug || ""}`.toLowerCase() === "1" || `${req.query?.debug || ""}`.toLowerCase() === "true";
    if (!date) {
      return res.status(400).send({ message: "date query is required." });
    }

    const site = splitSite.findSiteNumber(req);
    const templatePath = findTemplatePathBySite(site);
    const unicodeFontPath = findUnicodeFontPath(site);
    if (!templatePath) {
      return res.status(500).send({ message: `Missing PDF template for site ${site}.` });
    }
    if (!unicodeFontPath) {
      return res.status(500).send({ message: "Missing Unicode font file: NotoSansSC-Regular.ttf" });
    }

    const [routes, drivers, vehicles, customers] = await Promise.all([
      RoutePath.find(splitSite.splitSiteGet(req, { schedule_date: date, status: { $ne: "disabled" } })),
      Employee.find(splitSite.splitSiteGet(req, {})),
      Vehicle.find(splitSite.splitSiteGet(req, { status: "active" })),
      Customer.find(splitSite.splitSiteGet(req, {}))
    ]);

    const outboundRoutes = (routes || []).filter((route) => route?.type === "outbound");
    const outboundCustomerStatuses = outboundRoutes.reduce((acc, route) => {
      const list = Array.isArray(route?.route_customer_list) ? route.route_customer_list : [];
      return acc.concat(list);
    }, []);

    const driversMap = new Map(
      (drivers || []).map((driver) => [
        toObjectIdText(driver?._id || driver?.id),
        driver
      ])
    );
    const vehiclesMap = new Map(
      (vehicles || []).map((vehicle) => [
        toObjectIdText(vehicle?._id || vehicle?.id),
        vehicle
      ])
    );
    const customerNotesById = new Map(
      (customers || []).map((customer) => [
        toObjectIdText(customer?._id || customer?.id),
        (customer?.notes_for_driver || "").toString().trim()
      ])
    );
    const customerNotesByName = new Map();
    (customers || []).forEach((customer) => {
      const note = (customer?.notes_for_driver || "").toString().trim();
      if (!note) return;
      const key = normalizeCustomerName(customer?.name || customer?.name_cn || `${customer?.firstname || ""} ${customer?.lastname || ""}`);
      if (!key) return;
      const previous = customerNotesByName.get(key);
      if (!previous || (customer?.status || "").toString().toLowerCase() === "active") {
        customerNotesByName.set(key, note);
      }
    });
    const getRouteVehicleNumber = (route) => {
      const vehicleId = toObjectIdText(route?.vehicle);
      const vehicleNumberRaw = vehiclesMap.get(vehicleId)?.vehicle_number;
      if (vehicleNumberRaw === undefined || vehicleNumberRaw === null || vehicleNumberRaw === "") {
        return Number.MAX_SAFE_INTEGER;
      }
      const numeric = Number(vehicleNumberRaw);
      if (!Number.isNaN(numeric)) return numeric;
      return Number.MAX_SAFE_INTEGER;
    };

    const inboundRoutes = (routes || [])
      .filter((route) => route?.type === "inbound")
      .sort((a, b) => {
        const numberA = getRouteVehicleNumber(a);
        const numberB = getRouteVehicleNumber(b);
        if (numberA !== numberB) return numberA - numberB;
        return (a?.name || "").localeCompare(b?.name || "");
      });
    const templateBytes = fs.readFileSync(templatePath);
    const unicodeFontBytes = fs.readFileSync(unicodeFontPath);

    const zipName = `${crypto.randomBytes(8).toString("hex")}.zip`;
    res.setHeader("Content-Type", "application/zip");
    res.setHeader("Content-Disposition", `attachment; filename="${zipName}"`);

    const archive = archiver("zip", { zlib: { level: 9 } });
    archive.on("error", (err) => {
      if (!res.headersSent) {
        res.status(500).send({ message: err.message || "Failed to generate route report zip." });
      } else {
        res.end();
      }
    });
    archive.pipe(res);

    const filenameCounter = new Map();
    const debugRows = [];
    for (let i = 0; i < inboundRoutes.length; i += 1) {
      const route = inboundRoutes[i];
      const pdfBytes = await buildRoutePdfBuffer(
        templateBytes,
        route,
        i + 1,
        driversMap,
        vehiclesMap,
        outboundCustomerStatuses,
        unicodeFontBytes,
        routes,
        customerNotesById,
        customerNotesByName,
        debugMode ? debugRows : null
      );
      const base = sanitizeFileName(route?.name || `route_${i + 1}`) || `route_${i + 1}`;
      const existingCount = filenameCounter.get(base) || 0;
      filenameCounter.set(base, existingCount + 1);
      const finalName = existingCount ? `${base}_${existingCount + 1}.pdf` : `${base}.pdf`;
      archive.append(Buffer.from(pdfBytes), { name: finalName });
    }

    if (debugMode) {
      archive.append(
        Buffer.from(
          JSON.stringify(
            {
              date,
              site,
              generated_at: new Date().toISOString(),
              rows: debugRows
            },
            null,
            2
          )
        ),
        { name: "debug_export.json" }
      );
    }

    archive.finalize();
  } catch (err) {
    console.error("Failed to export route report zip:", err);
    res.status(500).send({
      message: err.message || "Failed to export route reports."
    });
  }
};

