menteithUpdate.ts

import type {NextApiRequest, NextApiResponse} from "next";
import * as XLSX from "xlsx";

// Handle file upload and parse Excel data
export const handleFileUpload = async (file: File) => {
  const reader = new FileReader();
  return new Promise<{
    managerRecords: { imo: string; manager: string }[];
  }>((resolve) => {
    reader.onload = (e) => {
      const data = e.target?.result;
      const workbook = XLSX.read(data, { type: "binary" });
      const managerWorksheet = workbook.Sheets["Recent Changes (EffControl)"];

      // Extract IMO and EffectiveControl columns dynamically by header name
      const managerData = XLSX.utils.sheet_to_json<any>(managerWorksheet, {
        header: 1,
      });
      const headers = managerData[0];
      const imoIndex = headers.findIndex(
        (header: string) => header === "IMONumber",
      );
      const managerIndex = headers.findIndex(
        (header: string) => header === "EffectiveControl",
      );

      const managerRecords = managerData
        .slice(1)
        .map((row: any) => ({
          imo: row[imoIndex],
          manager: row[managerIndex],
        }))
        .filter((record) => record.imo && record.manager); // Filter out empty rows

      resolve({ managerRecords });
    };
    reader.readAsBinaryString(file);
  });
};

// API handler for menteithUpdate
export default async function handler(
  req: NextApiRequest,
  res: NextApiResponse,
) {
  switch (req.method) {
    case "POST":
      if (req.body.action === "previewData") {
        try {
          const file = req.body.file;
          const { managerRecords } = await handleFileUpload(file);
          return res.status(200).json({ success: true, managerRecords });
        } catch (error) {
          return res
            .status(500)
            .json({ success: false, error: (error as Error).message });
        }
      }
      return res.status(400).json({ success: false, error: "Invalid action" });

    default:
      res.setHeader("Allow", ["POST"]);
      return res
        .status(405)
        .send(`Method ${req.method ?? "Undefined"} Not Allowed`);
  }
}