import { z } from "zod";
import { projectSchema } from "@/lib/validation/project";

// Turning a sheet (grid of strings) into import candidates, and deciding for
// each whether it is new, an update, a duplicate or invalid. Pure functions —
// the server module fetches the existing apps and applies the result.

export const IMPORT_COLUMNS = ["Project Code", "Project Name", "API Notifier URL"] as const;

// Header cells are compared after lower-casing and stripping everything but
// letters and digits, so "API Notifier URL", "api_notifier_url" and
// "ApiNotifierUrl" all match.
const HEADER_ALIASES = {
  projectCode: ["projectcode", "code", "appcode", "applicationcode", "projectid"],
  projectName: ["projectname", "name", "appname", "applicationname", "application", "project"],
  productionUrl: [
    "apinotifierurl",
    "notifierurl",
    "apinotifier",
    "url",
    "healthurl",
    "healthapiurl",
    "healthapi",
    "productionurl",
    "productionhealthapiurl",
    "apiurl",
    "endpoint",
    "link",
  ],
} as const;

type ImportField = keyof typeof HEADER_ALIASES;

const HEADER_SEARCH_ROWS = 20;

export type RawImportRow = {
  /** 1-based row number in the sheet, for messages. */
  line: number;
  projectCode: string;
  projectName: string;
  productionUrl: string;
};

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

function findHeader(grid: string[][]): { rowIndex: number; columns: Record<ImportField, number> } | null {
  for (let rowIndex = 0; rowIndex < Math.min(grid.length, HEADER_SEARCH_ROWS); rowIndex++) {
    const cells = grid[rowIndex].map(normaliseHeader);
    const columns: Partial<Record<ImportField, number>> = {};
    for (const field of Object.keys(HEADER_ALIASES) as ImportField[]) {
      const aliases: readonly string[] = HEADER_ALIASES[field];
      // Most specific alias first: "projectcode" must win over "code" if both exist.
      for (const alias of aliases) {
        const index = cells.findIndex((cell, i) => cell === alias && !Object.values(columns).includes(i));
        if (index !== -1) {
          columns[field] = index;
          break;
        }
      }
    }
    if (columns.projectCode !== undefined && columns.projectName !== undefined && columns.productionUrl !== undefined) {
      return { rowIndex, columns: columns as Record<ImportField, number> };
    }
  }
  return null;
}

export type ExtractResult = { ok: true; rows: RawImportRow[] } | { ok: false; error: string };

/** Locates the header row, then reads every non-empty row below it. */
export function extractImportRows(grid: string[][]): ExtractResult {
  const header = findHeader(grid);
  if (!header) {
    return {
      ok: false,
      error: `Could not find the header row. The sheet needs the columns ${IMPORT_COLUMNS.map((c) => `"${c}"`).join(", ")}.`,
    };
  }

  const rows: RawImportRow[] = [];
  for (let i = header.rowIndex + 1; i < grid.length; i++) {
    const cells = grid[i];
    const value = (field: ImportField) => (cells[header.columns[field]] ?? "").trim();
    const row = { line: i + 1, projectCode: value("projectCode"), projectName: value("projectName"), productionUrl: value("productionUrl") };
    if (!row.projectCode && !row.projectName && !row.productionUrl) continue;
    rows.push(row);
  }
  if (rows.length === 0) return { ok: false, error: "The sheet has a header row but no data rows." };
  return { ok: true, rows };
}

// ---------------------------------------------------------------------------

export const importRowSchema = projectSchema.pick({ projectCode: true, projectName: true, productionUrl: true });
export type ImportRowInput = z.output<typeof importRowSchema>;

export type ExistingProject = { id: string; projectCode: string; projectName: string; productionUrl: string };

export type ImportPreviewRow = {
  line: number;
  projectCode: string;
  projectName: string;
  productionUrl: string;
  kind: "new" | "existing" | "duplicate" | "invalid";
  /** For "existing": the app that will be updated and whether anything differs. */
  existing?: { id: string; projectName: string; productionUrl: string; changed: boolean };
  /** For "invalid": why the row cannot be imported. For "duplicate": which row it repeats. */
  error?: string;
};

export type ImportPreview = {
  rows: ImportPreviewRow[];
  counts: { new: number; existing: number; changed: number; duplicate: number; invalid: number };
};

function firstIssue(result: z.ZodSafeParseResult<ImportRowInput>): string {
  if (result.success) return "";
  return result.error.issues.map((issue) => issue.message).join("; ");
}

/**
 * Classifies each row. Every data row is listed, including ones that cannot be
 * imported (missing URL, bad code) so the reviewer sees what the sheet holds.
 * Project Codes are compared case-insensitively, both against the existing
 * apps and within the file (the second occurrence of a code is a duplicate
 * and is never imported).
 */
export function analyseImport(rows: RawImportRow[], existing: ExistingProject[]): ImportPreview {
  const byCode = new Map(existing.map((project) => [project.projectCode.toLowerCase(), project]));
  const seen = new Map<string, number>();
  const preview: ImportPreviewRow[] = [];

  for (const raw of rows) {
    if (raw.productionUrl === "") {
      // Named after the sheet column rather than the Add form's field label.
      preview.push({ ...raw, kind: "invalid", error: "API Notifier URL is required" });
      continue;
    }
    const parsed = importRowSchema.safeParse(raw);
    if (!parsed.success) {
      preview.push({ ...raw, kind: "invalid", error: firstIssue(parsed) });
      continue;
    }
    const row = parsed.data;
    const key = row.projectCode.toLowerCase();
    const firstLine = seen.get(key);
    if (firstLine !== undefined) {
      preview.push({ line: raw.line, ...row, kind: "duplicate", error: `Same Project Code as row ${firstLine}` });
      continue;
    }
    seen.set(key, raw.line);

    const current = byCode.get(key);
    if (current) {
      const changed = current.projectName !== row.projectName || current.productionUrl !== row.productionUrl;
      preview.push({
        line: raw.line,
        ...row,
        kind: "existing",
        existing: { id: current.id, projectName: current.projectName, productionUrl: current.productionUrl, changed },
      });
    } else {
      preview.push({ line: raw.line, ...row, kind: "new" });
    }
  }

  const counts = { new: 0, existing: 0, changed: 0, duplicate: 0, invalid: 0 };
  for (const row of preview) {
    counts[row.kind]++;
    if (row.kind === "existing" && row.existing?.changed) counts.changed++;
  }
  return { rows: preview, counts };
}