pelagia-portal/App/lib/po-import-parser.ts

import * as XLSX from "xlsx";

export type ParsedImportLine = {
  name: string;
  unit: string;
  quantity: number;
  unitPrice: number;
  gstRate: number;
};

export type ParsedImport = {
  companyName: string;
  /** Extracted from structured PO number (COMPANY/VESSEL/ID/FY). Null for legacy formats. */
  companyCode: string | null;
  costCentreCode: string | null;
  poSequenceId: number | null;
  poNumber: string;
  piQuotationNo: string;
  placeOfDelivery: string;
  tcDelivery: string;
  tcDispatch: string;
  tcInspection: string;
  tcTransitInsurance: string;
  tcPaymentTerms: string;
  tcOthers: string;
  vendorName: string;
  vendorAddress: string;
  vendorContact: string;
  lineItems: ParsedImportLine[];
};

export function cellStr(sheet: XLSX.WorkSheet, row: number, col: number): string {
  const addr = XLSX.utils.encode_cell({ r: row, c: col });
  const cell = sheet[addr];
  if (!cell) return "";
  return String(cell.v ?? "").trim();
}

export function cellNum(sheet: XLSX.WorkSheet, row: number, col: number): number {
  const addr = XLSX.utils.encode_cell({ r: row, c: col });
  const cell = sheet[addr];
  if (!cell) return 0;
  const v = parseFloat(String(cell.v));
  return isNaN(v) ? 0 : v;
}

/** Parse a structured PO number (COMPANY/VESSEL/ID/FY) into its parts. */
function parsePoNumberParts(poNumber: string): {
  companyCode: string | null;
  costCentreCode: string | null;
  poSequenceId: number | null;
} {
  const parts = poNumber.split("/");
  if (parts.length !== 4) return { companyCode: null, costCentreCode: null, poSequenceId: null };
  const poSequenceId = parseInt(parts[2], 10);
  return {
    companyCode:    parts[0] || null,
    costCentreCode: parts[1] || null,
    poSequenceId:   isNaN(poSequenceId) ? null : poSequenceId,
  };
}

export function parseSheet(sheet: XLSX.WorkSheet): ParsedImport {
  // Row 1 (index 0) = company name, spanning the full header (col 0)
  const companyName = cellStr(sheet, 0, 0);
  const poNumber = cellStr(sheet, 4, 2);
  const { companyCode, costCentreCode, poSequenceId } = parsePoNumberParts(poNumber);
  const piQuotationNo = cellStr(sheet, 5, 2);
  const placeOfDelivery = cellStr(sheet, 8, 2);
  const vendorName = cellStr(sheet, 12, 2);
  const vendorAddress = cellStr(sheet, 12, 3);
  const vendorContact = cellStr(sheet, 13, 2);

  // T&C from instruction rows 28–33 (col 1)
  const tcDelivery = cellStr(sheet, 28, 1).replace(/^DELIVERY\s*:\s*/i, "").trim();
  const tcDispatch = cellStr(sheet, 29, 1).replace(/^DISPATCH INSTRUCTIONS:\s*/i, "").trim();
  const tcInspection = cellStr(sheet, 30, 1).replace(/^INSPECTION\s*:\s*/i, "").trim();
  const tcTransitInsurance = cellStr(sheet, 31, 1).replace(/^TRANSIT INSURANCE:\s*/i, "").trim();
  const tcPaymentTerms = cellStr(sheet, 32, 1).replace(/^PAYMENT TERMS:\s*/i, "").trim();
  const tcOthers = cellStr(sheet, 33, 1).trim();

  const lineItems: ParsedImportLine[] = [];
  for (let r = 15; r <= 100; r++) {
    const sn = cellStr(sheet, r, 0);
    const desc = cellStr(sheet, r, 1);

    // "INSTRUCTIONS TO VENDORS" in col 0 signals the T&C section — stop here
    if (sn.toUpperCase().includes("INSTRUCTION")) break;

    if (!desc && !sn) continue;
    if (!desc) continue;

    if (desc.toLowerCase().includes("total") || desc.toLowerCase().includes("grand")) break;

    const unitRaw = cellStr(sheet, r, 3);
    const qty = cellNum(sheet, r, 4);
    const unitPrice = cellNum(sheet, r, 5);

    // Skip rows with no quantity and no unit price (T&C text rows, etc.)
    if (qty === 0 && unitPrice === 0) continue;

    const gstRaw = cellNum(sheet, r, 7);
    const gstRate = gstRaw > 1 ? gstRaw / 100 : gstRaw;

    lineItems.push({
      name: desc,
      unit: unitRaw || "pc",
      quantity: qty || 1,
      unitPrice,
      gstRate: gstRate || 0.18,
    });
  }

  return {
    companyName,
    companyCode,
    costCentreCode,
    poSequenceId,
    poNumber,
    piQuotationNo,
    placeOfDelivery,
    tcDelivery,
    tcDispatch,
    tcInspection,
    tcTransitInsurance,
    tcPaymentTerms,
    tcOthers,
    vendorName,
    vendorAddress,
    vendorContact,
    lineItems,
  };
}

export function parseWorkbook(buffer: Buffer): ParsedImport[] {
  const workbook = XLSX.read(buffer, { type: "buffer" });
  const results: ParsedImport[] = [];
  for (const sheetName of workbook.SheetNames) {
    const sheet = workbook.Sheets[sheetName];
    try {
      const parsed = parseSheet(sheet);
      if (parsed.lineItems.length > 0) results.push(parsed);
    } catch {
      // skip unparseable sheets
    }
  }
  return results;
}