import * as XLSX from "xlsx";

// Excel import for container shipments: one spreadsheet row per physical
// container, mirroring the manual per-unit fields (number, seal, VGM) plus the
// hazardous/reefer/return flags when the contract allows them. The parser is
// all-or-nothing — any bad row rejects the file with row-numbered errors so a
// partial import can never silently drop containers.

// ISO 6346: 3-letter owner code + category id (U/J/Z) + 6-digit serial + check digit.
const ISO_CONTAINER_NUMBER_REGEX = /^[A-Z]{4}\d{7}$/;

export interface ContainerExcelOptions {
  /** Container sizes the contract scope allows (e.g. ["20ft", "40ft"]). */
  allowedSizes: string[];
  includeHazardous: boolean;
  includeReefer: boolean;
  /** Contract was created WITH_RETURN — offer the empty-return column. */
  includeReturn?: boolean;
}

export interface ImportedContainerRow {
  containerSize: string;
  containerNumber: string;
  sealNumber: string;
  vgmTons: string;
  hazardous: boolean;
  reefer: boolean;
  withReturn: boolean;
}

export interface ContainerExcelResult {
  rows: ImportedContainerRow[];
  errors: string[];
}

type ColumnKey =
  | "containerSize"
  | "containerNumber"
  | "sealNumber"
  | "vgmTons"
  | "hazardous"
  | "reefer"
  | "withReturn";

/** Match a header cell to a known column, tolerant of casing/spacing/units. */
function headerKey(raw: string): ColumnKey | null {
  const h = raw.toLowerCase().replace(/[^a-z]/g, "");
  if (!h) return null;
  if (h.includes("size")) return "containerSize";
  if (h.includes("seal")) return "sealNumber";
  if (h.includes("vgm") || h.includes("weight")) return "vgmTons";
  if (h.includes("hazard")) return "hazardous";
  if (h.includes("reefer") || h.includes("refrigerat")) return "reefer";
  if (h.includes("return")) return "withReturn";
  // After the more specific matches: "Container Number", "Container No", …
  if (h.includes("container") || h.includes("number")) return "containerNumber";
  return null;
}

/** "20", "20ft", "20 FT" … → the matching contracted size, or null. */
function normalizeSize(raw: string, allowed: string[]): string | null {
  const digits = raw.replace(/[^0-9]/g, "");
  if (!digits) return null;
  return allowed.find((s) => s.replace(/[^0-9]/g, "") === digits) ?? null;
}

function parseFlag(raw: string): boolean {
  const v = raw.trim().toLowerCase();
  return v === "yes" || v === "y" || v === "true" || v === "1" || v === "x";
}

/**
 * Parse an uploaded workbook into one row per container. Returns either the
 * full row set or the list of row-numbered problems (never both).
 */
export async function parseContainerExcel(
  file: File,
  opts: ContainerExcelOptions,
): Promise<ContainerExcelResult> {
  let sheet: XLSX.WorkSheet | undefined;
  try {
    const workbook = XLSX.read(await file.arrayBuffer(), { type: "array" });
    sheet = workbook.Sheets[workbook.SheetNames[0]];
  } catch {
    return { rows: [], errors: ["Could not read the file — is it a valid Excel file?"] };
  }
  if (!sheet) {
    return { rows: [], errors: ["The file has no sheets."] };
  }

  const grid = XLSX.utils.sheet_to_json<string[]>(sheet, {
    header: 1,
    raw: false,
    defval: "",
  });

  // First row with a recognizable column is the header; everything above
  // (titles, blank rows) is ignored.
  let headerRowIdx = -1;
  let columns: Array<ColumnKey | null> = [];
  for (let i = 0; i < grid.length; i++) {
    const mapped = (grid[i] ?? []).map((c) => headerKey(String(c ?? "")));
    if (mapped.includes("containerNumber") && mapped.includes("containerSize")) {
      headerRowIdx = i;
      columns = mapped;
      break;
    }
  }
  if (headerRowIdx < 0) {
    return {
      rows: [],
      errors: [
        'Could not find the expected columns. The sheet needs at least "Container Size" and "Container Number" headers — download the template to see the format.',
      ],
    };
  }
  if (!columns.includes("vgmTons")) {
    return {
      rows: [],
      errors: ['Missing a "VGM (Tons)" column — download the template to see the format.'],
    };
  }

  const rows: ImportedContainerRow[] = [];
  const errors: string[] = [];
  const numberCounts = new Map<string, number>();

  for (let i = headerRowIdx + 1; i < grid.length; i++) {
    const cells = grid[i] ?? [];
    if (cells.every((c) => String(c ?? "").trim() === "")) continue;
    const rowNo = i + 1; // 1-based, as shown in Excel

    const cell = (key: ColumnKey) => {
      const idx = columns.indexOf(key);
      return idx >= 0 ? String(cells[idx] ?? "").trim() : "";
    };

    const size = normalizeSize(cell("containerSize"), opts.allowedSizes);
    if (!size) {
      errors.push(
        `Row ${rowNo}: container size "${cell("containerSize") || "—"}" is not in this contract's scope (allowed: ${opts.allowedSizes.join(", ")}).`,
      );
    }

    const containerNumber = cell("containerNumber").toUpperCase();
    if (!ISO_CONTAINER_NUMBER_REGEX.test(containerNumber)) {
      errors.push(
        `Row ${rowNo}: "${cell("containerNumber") || "—"}" is not a valid ISO container number (e.g. MSCU1234567).`,
      );
    } else {
      numberCounts.set(containerNumber, (numberCounts.get(containerNumber) ?? 0) + 1);
    }

    const vgmRaw = cell("vgmTons");
    const vgm = Number(vgmRaw);
    if (!vgmRaw || Number.isNaN(vgm) || vgm <= 0) {
      errors.push(`Row ${rowNo}: VGM "${vgmRaw || "—"}" must be a number greater than 0.`);
    }

    rows.push({
      containerSize: size ?? "",
      containerNumber,
      sealNumber: cell("sealNumber"),
      vgmTons: vgmRaw,
      hazardous: opts.includeHazardous && parseFlag(cell("hazardous")),
      reefer: opts.includeReefer && parseFlag(cell("reefer")),
      withReturn: Boolean(opts.includeReturn) && parseFlag(cell("withReturn")),
    });
  }

  numberCounts.forEach((count, num) => {
    if (count > 1) errors.push(`Container number ${num} appears ${count} times — numbers must be unique.`);
  });

  if (rows.length === 0 && errors.length === 0) {
    errors.push("The sheet has no container rows below the header.");
  }

  return errors.length > 0 ? { rows: [], errors } : { rows, errors: [] };
}

/** Generate and download the simple import template with one sample row per size. */
export function downloadContainerImportTemplate(opts: ContainerExcelOptions) {
  const headers = ["Container Size", "Container Number", "Seal Number", "VGM (Tons)"];
  if (opts.includeHazardous) headers.push("Hazardous (YES/NO)");
  if (opts.includeReefer) headers.push("Reefer (YES/NO)");
  if (opts.includeReturn) headers.push("With Return (YES/NO)");

  const sizes = opts.allowedSizes.length > 0 ? opts.allowedSizes : ["20ft"];
  const sampleRows = sizes.map((size, i) => {
    const row: Array<string | number> = [
      size,
      `MSCU${String(1234567 + i).padStart(7, "0")}`,
      `SL${String(482910 + i)}`,
      size.startsWith("40") ? 28 : 24.5,
    ];
    if (opts.includeHazardous) row.push("NO");
    if (opts.includeReefer) row.push("NO");
    if (opts.includeReturn) row.push("NO");
    return row;
  });

  const sheet = XLSX.utils.aoa_to_sheet([headers, ...sampleRows]);
  sheet["!cols"] = headers.map((h) => ({ wch: Math.max(h.length + 2, 16) }));
  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, sheet, "Containers");
  XLSX.writeFile(workbook, "container-import-template.xlsx");
}
