import "server-only";

/**
 * Spreadsheet parsing for every import wizard (SPEC 13, 14, 15).
 *
 * Accepts .xlsx (primary) and .csv. Legacy .xls is rejected with a clear
 * message rather than silently mis-parsed. Everything is read into plain
 * strings — no formulas are evaluated and no macro content is executed, so an
 * uploaded workbook cannot influence the running system (SPEC 29).
 */

export interface ParsedSheet {
  name: string;
  /** Header labels exactly as they appear in the header row. */
  headers: string[];
  /** Data rows, aligned to `headers` by index. */
  rows: string[][];
  /** 1-based worksheet row number of the header, for error messages. */
  headerRowNumber: number;
  totalRowCount: number;
}

export interface ParsedWorkbook {
  fileName: string;
  sheets: ParsedSheet[];
}

export class WorkbookParseError extends Error {
  readonly code: string;

  constructor(code: string, message: string) {
    super(message);
    this.name = "WorkbookParseError";
    this.code = code;
  }
}

const XLSX_EXTENSIONS = [".xlsx", ".xlsm"];
const CSV_EXTENSIONS = [".csv", ".txt"];

export function extensionOf(fileName: string): string {
  const index = fileName.lastIndexOf(".");
  return index === -1 ? "" : fileName.slice(index).toLowerCase();
}

export function assertAcceptedFile(fileName: string, byteLength: number, maxMb: number): void {
  const extension = extensionOf(fileName);
  if (extension === ".xls") {
    throw new WorkbookParseError(
      "LEGACY_XLS",
      "Legacy .xls workbooks are not supported. Open the file in Excel and save it as .xlsx, then upload again.",
    );
  }
  if (![...XLSX_EXTENSIONS, ...CSV_EXTENSIONS].includes(extension)) {
    throw new WorkbookParseError(
      "UNSUPPORTED_FILE_TYPE",
      `"${extension || "This file"}" is not a supported format. Upload an .xlsx or .csv file.`,
    );
  }
  const maxBytes = maxMb * 1024 * 1024;
  if (byteLength > maxBytes) {
    throw new WorkbookParseError(
      "FILE_TOO_LARGE",
      `This file is ${(byteLength / 1024 / 1024).toFixed(1)} MB, which exceeds the ${maxMb} MB import limit.`,
    );
  }
  if (byteLength === 0) {
    throw new WorkbookParseError("EMPTY_FILE", "The uploaded file is empty.");
  }
}

/** Converts any ExcelJS cell value into a trimmed display string. */
function cellToString(value: unknown): string {
  if (value === null || value === undefined) return "";
  if (typeof value === "string") return value.trim();
  if (typeof value === "number" || typeof value === "boolean") return String(value);
  if (value instanceof Date) return value.toISOString().slice(0, 10);
  if (typeof value === "object") {
    const candidate = value as Record<string, unknown>;
    if (Array.isArray(candidate.richText)) {
      return (candidate.richText as { text?: string }[]).map((part) => part.text ?? "").join("").trim();
    }
    if ("result" in candidate) return cellToString(candidate.result);
    if ("text" in candidate) return cellToString(candidate.text);
    if ("hyperlink" in candidate) return cellToString(candidate.hyperlink);
    if ("error" in candidate) return "";
  }
  return String(value).trim();
}

/** RFC 4180-ish CSV parser, tolerant of quoted fields and CRLF. */
export function parseCsv(text: string): string[][] {
  const rows: string[][] = [];
  let row: string[] = [];
  let field = "";
  let inQuotes = false;
  const source = text.replace(/^/, "");

  for (let i = 0; i < source.length; i += 1) {
    const char = source[i];
    if (inQuotes) {
      if (char === '"') {
        if (source[i + 1] === '"') {
          field += '"';
          i += 1;
        } else {
          inQuotes = false;
        }
      } else {
        field += char;
      }
      continue;
    }
    if (char === '"') {
      inQuotes = true;
    } else if (char === ",") {
      row.push(field);
      field = "";
    } else if (char === "\n") {
      row.push(field);
      rows.push(row);
      row = [];
      field = "";
    } else if (char === "\r") {
      // handled by the \n branch
    } else {
      field += char;
    }
  }
  if (field.length > 0 || row.length > 0) {
    row.push(field);
    rows.push(row);
  }
  return rows.map((r) => r.map((c) => c.trim()));
}

/** Longest a cell can be before it reads as prose rather than a column label. */
const MAX_HEADER_CELL_LENGTH = 60;

/**
 * Decides whether a row plausibly holds column labels.
 *
 * Institutional workbooks routinely carry a merged title banner above the real
 * header. A merged cell is reported by the spreadsheet reader as the SAME value
 * repeated across every column in the merge range, so "many filled cells" alone
 * is not enough — the values must also be distinct and short.
 */
function looksLikeHeader(row: string[], width: number): boolean {
  const filled = row.filter((cell) => cell !== "");
  const distinct = new Set(filled).size;

  // A merged banner collapses to a single distinct value.
  if (distinct < 2) return false;

  // In a wide sheet, a two-cell row is a title fragment, not a header.
  const minimumDistinct = width >= 3 ? 3 : 2;
  if (distinct < minimumDistinct) return false;

  const averageLength = filled.reduce((sum, cell) => sum + cell.length, 0) / filled.length;
  return averageLength <= MAX_HEADER_CELL_LENGTH;
}

/**
 * Finds the header row: the FIRST row that plausibly holds column labels,
 * scanning at most 25 rows. Taking the first plausible row rather than the
 * "best" one keeps the result stable — a header always precedes its data.
 */
function detectHeaderRow(matrix: string[][]): number {
  const limit = Math.min(matrix.length, 25);
  const width = Math.max(0, ...matrix.map((row) => row.length));
  for (let i = 0; i < limit; i += 1) {
    if (looksLikeHeader(matrix[i], width)) return i;
  }
  return 0;
}

function matrixToSheet(name: string, matrix: string[][]): ParsedSheet {
  const trimmedMatrix = matrix.filter((row) => row.some((cell) => cell !== ""));
  if (trimmedMatrix.length === 0) {
    return { name, headers: [], rows: [], headerRowNumber: 1, totalRowCount: 0 };
  }
  const headerIndex = detectHeaderRow(trimmedMatrix);
  const rawHeaders = trimmedMatrix[headerIndex] ?? [];
  const width = Math.max(rawHeaders.length, ...trimmedMatrix.map((r) => r.length));
  const headers = Array.from({ length: width }, (_, i) => rawHeaders[i]?.trim() ?? "");
  const rows = trimmedMatrix
    .slice(headerIndex + 1)
    .map((row) => Array.from({ length: width }, (_, i) => row[i] ?? ""))
    .filter((row) => row.some((cell) => cell !== ""));

  return { name, headers, rows, headerRowNumber: headerIndex + 1, totalRowCount: rows.length };
}

export async function parseWorkbook(
  buffer: ArrayBuffer | Buffer,
  fileName: string,
): Promise<ParsedWorkbook> {
  const extension = extensionOf(fileName);
  const nodeBuffer = Buffer.isBuffer(buffer) ? buffer : Buffer.from(buffer);

  if (CSV_EXTENSIONS.includes(extension)) {
    const matrix = parseCsv(nodeBuffer.toString("utf8"));
    return { fileName, sheets: [matrixToSheet("CSV", matrix)] };
  }

  const ExcelJS = (await import("exceljs")).default;
  const workbook = new ExcelJS.Workbook();
  try {
    await workbook.xlsx.load(nodeBuffer as unknown as ArrayBuffer);
  } catch {
    throw new WorkbookParseError(
      "PARSE_FAILED",
      "This workbook could not be read. Confirm it is a valid .xlsx file that is not password protected.",
    );
  }

  const sheets: ParsedSheet[] = [];
  workbook.eachSheet((worksheet) => {
    const matrix: string[][] = [];
    worksheet.eachRow({ includeEmpty: false }, (row) => {
      const values = row.values as unknown[];
      // ExcelJS row.values is 1-based with a leading empty slot.
      const cells = values.slice(1).map((v) => cellToString(v));
      matrix.push(cells);
    });
    sheets.push(matrixToSheet(worksheet.name, matrix));
  });

  if (sheets.length === 0) {
    throw new WorkbookParseError("NO_SHEETS", "This workbook contains no worksheets.");
  }
  return { fileName, sheets };
}

// -----------------------------------------------------------------------------
// Column mapping
// -----------------------------------------------------------------------------

export interface ColumnDefinition {
  key: string;
  label: string;
  required: boolean;
  /** Header aliases matched case-insensitively, ignoring spaces and symbols. */
  aliases: string[];
  hint?: string;
}

function headerKey(value: string): string {
  return value.toLowerCase().replace(/[^a-z0-9]/g, "");
}

/**
 * Suggests a header -> field mapping. Never depends on one exact Excel header
 * name (SPEC 13 step 3); the user can always change the mapping afterwards.
 */
export function suggestMapping(
  headers: string[],
  definitions: ColumnDefinition[],
): Record<string, number | null> {
  const normalizedHeaders = headers.map(headerKey);
  const used = new Set<number>();
  const mapping: Record<string, number | null> = {};

  for (const definition of definitions) {
    const candidates = [definition.key, definition.label, ...definition.aliases].map(headerKey);
    let found: number | null = null;

    for (const candidate of candidates) {
      const exact = normalizedHeaders.findIndex((h, i) => h === candidate && !used.has(i));
      if (exact !== -1) {
        found = exact;
        break;
      }
    }
    if (found === null) {
      for (const candidate of candidates) {
        if (candidate.length < 4) continue;
        const partial = normalizedHeaders.findIndex(
          (h, i) => h !== "" && !used.has(i) && (h.includes(candidate) || candidate.includes(h)),
        );
        if (partial !== -1) {
          found = partial;
          break;
        }
      }
    }
    if (found !== null) used.add(found);
    mapping[definition.key] = found;
  }
  return mapping;
}

export function readMapped(
  row: string[],
  mapping: Record<string, number | null>,
  key: string,
): string {
  const index = mapping[key];
  if (index === null || index === undefined) return "";
  return (row[index] ?? "").trim();
}

/** Parses a spreadsheet truthiness cell ("yes", "y", "1", "true", "x"). */
export function parseBooleanCell(value: string, fallback: boolean): boolean {
  const v = value.trim().toLowerCase();
  if (v === "") return fallback;
  if (["yes", "y", "true", "1", "x", "required", "counts"].includes(v)) return true;
  if (["no", "n", "false", "0", "-", "optional"].includes(v)) return false;
  return fallback;
}

/** Parses a numeric cell, tolerating thousands separators and stray spaces. */
export function parseNumberCell(value: string): number | null {
  const cleaned = value.replace(/,/g, "").trim();
  if (cleaned === "") return null;
  const parsed = Number(cleaned);
  return Number.isFinite(parsed) ? parsed : null;
}