import crypto from 'node:crypto';
import { Router, raw, type IRouter, type NextFunction, type Request, type Response } from 'express';
import multer from 'multer';
import XLSX from 'xlsx';
import { DOMParser } from '@xmldom/xmldom';
import type { LayerSymbology, ClusterConfig, DerivedColumnConfig } from '../data/store';
import { compileFormula, type DashboardLayerDataItem } from '@workspace/api-zod';
import {
  layers, pendingUploads, addAuditEvent, newId, users,
  addLayer, updateLayer, removeLayer, reorderLayers,
  indicatorConfigs, removeIndicatorConfig,
  filterConfigs, removeFilterConfig,
  type Layer, type LayerRow, type ColumnType, type PendingUpload,
} from '../data/store';
import { requireAuth, requireAdmin } from '../middlewares/auth';

// ── Row-filter types & evaluator ──────────────────────────────────────────────
type FilterOp =
  | 'eq' | 'neq' | 'contains' | 'not_contains' | 'is_empty' | 'is_not_empty'
  | 'gt' | 'lt' | 'gte' | 'lte' | 'between'
  | 'date_before' | 'date_after' | 'date_last_n_months' | 'date_last_n_days';
type RowFilterCondition = { kind: 'condition'; column: string; operator: FilterOp; value: string; value2?: string };
type RowFilterGroup = { kind: 'group'; combinator: 'and' | 'or'; children: RowFilterNode[] };
type RowFilterNode = RowFilterCondition | RowFilterGroup;
type RowFilters = RowFilterGroup; // top-level is always a group

/** Migrate old flat format { combinator, conditions[] } → recursive tree */
function migrateFilters(raw: unknown): RowFilters | undefined {
  if (!raw || typeof raw !== 'object') return undefined;
  const r = raw as Record<string, unknown>;
  if (r.kind === 'group') return r as RowFilters;
  // old flat format
  if (Array.isArray(r.conditions) && r.conditions.length) {
    return {
      kind: 'group',
      combinator: (r.combinator as 'and' | 'or') ?? 'and',
      children: (r.conditions as RowFilterCondition[]).map((c) => ({ ...c, kind: 'condition' as const })),
    };
  }
  return undefined;
}

/** Flatten all leaf conditions from a filter tree */
function flattenConditions(node: RowFilterNode): RowFilterCondition[] {
  if (node.kind === 'condition') return [node];
  return node.children.flatMap(flattenConditions);
}

/** Recursively strip conditions matching a predicate; returns null if the node should be removed */
function stripConditions(
  node: RowFilterNode,
  predicate: (c: RowFilterCondition) => boolean,
): RowFilterNode | null {
  if (node.kind === 'condition') return predicate(node) ? null : node;
  const filteredChildren = node.children
    .map((child) => stripConditions(child, predicate))
    .filter((child): child is RowFilterNode => child !== null);
  return { ...node, children: filteredChildren };
}

function evalCondition(row: LayerRow, cond: RowFilterCondition, types: Record<string, ColumnType>): boolean {
  const raw = row[cond.column];
  const colType = types[cond.column] ?? 'text';
  if (cond.operator === 'is_empty') return raw == null || String(raw).trim() === '';
  if (cond.operator === 'is_not_empty') return raw != null && String(raw).trim() !== '';
  const str = String(raw ?? '').toLowerCase();
  const val = (cond.value ?? '').toLowerCase();
  if (colType === 'number') {
    const n = Number(raw);
    const v1 = Number(cond.value);
    if (cond.operator === 'eq') return n === v1;
    if (cond.operator === 'neq') return n !== v1;
    if (cond.operator === 'gt') return n > v1;
    if (cond.operator === 'lt') return n < v1;
    if (cond.operator === 'gte') return n >= v1;
    if (cond.operator === 'lte') return n <= v1;
    if (cond.operator === 'between') return n >= v1 && n <= Number(cond.value2);
  } else if (colType === 'date') {
    const d = parseStoredDate(raw);
    if (isNaN(d.getTime())) return false;
    if (cond.operator === 'date_before') return d < new Date(cond.value);
    if (cond.operator === 'date_after') return d > new Date(cond.value);
    if (cond.operator === 'date_last_n_months') {
      const cutoff = new Date(); cutoff.setMonth(cutoff.getMonth() - Number(cond.value)); return d >= cutoff;
    }
    if (cond.operator === 'date_last_n_days') return d >= new Date(Date.now() - Number(cond.value) * 86_400_000);
    if (cond.operator === 'eq') return d.toDateString() === new Date(cond.value).toDateString();
  } else {
    if (cond.operator === 'eq') return str === val;
    if (cond.operator === 'neq') return str !== val;
    if (cond.operator === 'contains') return str.includes(val);
    if (cond.operator === 'not_contains') return !str.includes(val);
  }
  return true;
}

/** Parse a stored date value that may be an ISO string, a formatted date string,
 *  or an Excel serial number (stored as number or numeric string). */
function parseStoredDate(raw: unknown): Date {
  const n = Number(raw);
  // Excel serial: integer in the plausible date range 30000–70000 (~1982–2064)
  if (!isNaN(n) && Number.isInteger(n) && n > 30000 && n < 70000) {
    return new Date(Math.round((n - 25569) * 86_400_000));
  }
  return new Date(String(raw ?? ''));
}

function evalNode(row: LayerRow, node: RowFilterNode, types: Record<string, ColumnType>): boolean {
  if (node.kind === 'condition') return evalCondition(row, node, types);
  if (!Array.isArray(node.children) || !node.children.length) return true; // guard + empty group passes all
  const results = node.children.map((child) => evalNode(row, child, types));
  return node.combinator === 'and' ? results.every(Boolean) : results.some(Boolean);
}

function hasConditions(node: RowFilterNode): boolean {
  if (node.kind === 'condition') return true;
  if (!Array.isArray(node.children)) return false; // guard against old flat-format objects
  return node.children.some(hasConditions);
}

function applyRowFilters(rows: LayerRow[], types: Record<string, ColumnType>, filters: RowFilters): LayerRow[] {
  if (!filters || !hasConditions(filters)) return rows;
  return rows.filter((row) => evalNode(row, filters, types));
}

const upload = multer({ storage: multer.memoryStorage(), limits: { fileSize: 20 * 1024 * 1024 } });
const router: IRouter = Router();

// ── Operators valid per column type (mirrors frontend OPS_BY_TYPE) ────────────
const OPS_VALID_FOR_TYPE: Record<ColumnType, Set<FilterOp>> = {
  text:   new Set(['eq','neq','contains','not_contains','is_empty','is_not_empty']),
  number: new Set(['eq','neq','gt','lt','gte','lte','between','is_empty','is_not_empty']),
  date:   new Set(['date_before','date_after','date_last_n_months','date_last_n_days','eq','is_empty','is_not_empty']),
};

// ── Column-type detection ─────────────────────────────────────────────────────
const DATE_PATTERNS = [
  /^\d{4}-\d{2}-\d{2}/,            // ISO: 2024-01-15 or with time
  /^\d{2}\/\d{2}\/\d{4}$/,          // DD/MM/YYYY or MM/DD/YYYY
  /^\d{4}\/\d{2}\/\d{2}$/,          // YYYY/MM/DD
  /^\d{2}\.\d{2}\.\d{4}$/,          // DD.MM.YYYY
  /^\d{2}-\d{2}-\d{4}$/,            // DD-MM-YYYY
  /^\d{1,2}\s+\w{3,9}\s+\d{4}$/,   // 15 January 2024
  /^\w{3,9}\s+\d{1,2},?\s+\d{4}$/, // January 15, 2024
];
const DATE_COL_HINT = /date|time|dat|created|updated|timestamp|dob|birth|expir|issued|recorded|start|end|from|to|period/i;

function detectColumnType(values: unknown[], colName = ''): ColumnType {
  const nonEmpty = values.filter((v) => v !== null && v !== undefined && v !== '');
  if (nonEmpty.length === 0) return 'text';
  // JS Date objects (produced by Excel cellDates: true)
  if (nonEmpty.every((v) => v instanceof Date)) return 'date';
  // Broad date-string patterns — checked BEFORE number to avoid mis-typing formatted dates
  if (nonEmpty.every((v) => DATE_PATTERNS.some((p) => p.test(String(v))))) return 'date';
  if (nonEmpty.every((v) => !isNaN(Number(v)))) {
    // Column name strongly suggests date + values look like YYYYMMDD (e.g. 20240115)
    if (DATE_COL_HINT.test(colName) && nonEmpty.every((v) => /^\d{8}$/.test(String(v)))) return 'date';
    // Column name strongly suggests date + values are Excel serial numbers (5-digit int in ~1982–2064 range)
    if (DATE_COL_HINT.test(colName) && nonEmpty.every((v) => {
      const n = Number(v);
      return Number.isInteger(n) && n > 30000 && n < 70000;
    })) return 'date';
    return 'number';
  }
  return 'text';
}

function suggestLat(cols: string[]): string | undefined {
  const names = ['lat', 'latitude', 'y', 'lat_dd', 'latitude_dd', 'y_coord', '__lat'];
  return cols.find((c) => names.includes(c.toLowerCase().replace(/[\s_-]/g, '')) || c === '__lat');
}

function suggestLng(cols: string[]): string | undefined {
  const names = ['lng', 'lon', 'long', 'longitude', 'x', 'lng_dd', 'longitude_dd', 'x_coord', '__lng'];
  return cols.find((c) => names.includes(c.toLowerCase().replace(/[\s_-]/g, '')) || c === '__lng');
}

function suggestGeometryColumn(cols: string[]): string | undefined {
  // Prefer the synthetic __geometry column created by KML/GeoJSON parsers,
  // then fall back to any column whose name suggests it holds geometry/WKT.
  if (cols.includes('__geometry')) return '__geometry';
  const names = ['geometry', 'geom', 'shape', 'wkt', 'the_geom'];
  return cols.find((c) => names.includes(c.toLowerCase().replace(/[\s_-]/g, '')));
}

// ── File parsers ──────────────────────────────────────────────────────────────
function parseExcel(buffer: Buffer): { columns: string[]; columnTypes: Record<string, ColumnType>; rows: LayerRow[] } {
  const wb = XLSX.read(buffer, { type: 'buffer', cellDates: true });
  const ws = wb.Sheets[wb.SheetNames[0]];

  // Extract every header column directly from the sheet range so we never
  // miss columns that happen to be blank in the first data row.
  const ref = ws['!ref'];
  const sheetHeaders: string[] = [];
  if (ref) {
    const range = XLSX.utils.decode_range(ref);
    for (let c = range.s.c; c <= range.e.c; c++) {
      const cellAddr = XLSX.utils.encode_cell({ r: range.s.r, c });
      const cell = ws[cellAddr];
      sheetHeaders.push(cell ? String(cell.v).trim() : `Column${c + 1}`);
    }
  }

  // Use sheetHeaders as the explicit column list so row keys are guaranteed to
  // match — if we let sheet_to_json auto-detect headers it deduplicates duplicate
  // names (appending _1, _2 …) while sheetHeaders keeps the originals, causing
  // a silent key mismatch where affected columns appear blank in every row.
  const rawRows = sheetHeaders.length > 0
    ? (XLSX.utils.sheet_to_json(ws, { header: sheetHeaders, defval: null, range: 1 }) as Record<string, unknown>[])
    : (XLSX.utils.sheet_to_json(ws, { defval: null }) as Record<string, unknown>[]);
  return buildFromRawRows(rawRows, sheetHeaders.length > 0 ? sheetHeaders : undefined);
}

function parseCSV(buffer: Buffer): { columns: string[]; columnTypes: Record<string, ColumnType>; rows: LayerRow[] } {
  const text = buffer.toString('utf-8').replace(/\r\n/g, '\n').replace(/\r/g, '\n');
  const lines = text.split('\n').filter((l) => l.trim());
  if (lines.length < 2) throw new Error('CSV must have at least a header row and one data row');

  // Detect delimiter
  const sample = lines[0];
  const delimiter = sample.split('\t').length > sample.split(',').length ? '\t' : ',';

  const parseRow = (line: string): string[] => {
    const result: string[] = [];
    let inQuotes = false;
    let current = '';
    for (let i = 0; i < line.length; i++) {
      const ch = line[i];
      if (ch === '"') { inQuotes = !inQuotes; continue; }
      if (ch === delimiter && !inQuotes) { result.push(current); current = ''; continue; }
      current += ch;
    }
    result.push(current);
    return result;
  };

  const headers = parseRow(lines[0]).map((h) => h.trim());
  const rawRows = lines.slice(1).map((line) => {
    const vals = parseRow(line);
    return Object.fromEntries(headers.map((h, i) => [h, vals[i]?.trim() ?? null]));
  });
  return buildFromRawRows(rawRows);
}

function parseGeoJSON(buffer: Buffer): { columns: string[]; columnTypes: Record<string, ColumnType>; rows: LayerRow[]; geometryType: Layer['geometryType'] } {
  const geojson = JSON.parse(buffer.toString('utf-8')) as {
    features?: { geometry?: { type?: string; coordinates?: unknown }; properties?: Record<string, unknown> }[];
  };
  const features = geojson.features ?? [];

  // Detect dominant geometry type from first feature that has one
  let geometryType: Layer['geometryType'] = 'Point';
  for (const f of features) {
    const t = f.geometry?.type;
    if (t === 'Point') { geometryType = 'Point'; break; }
    if (t === 'LineString' || t === 'MultiLineString') { geometryType = 'LineString'; break; }
    if (t === 'Polygon') { geometryType = 'Polygon'; break; }
    if (t === 'MultiPolygon') { geometryType = 'MultiPolygon'; break; }
  }

  const rawRows = features.map((f) => {
    const isPoint = f.geometry?.type === 'Point';
    const coords = f.geometry?.coordinates;
    const pointCoords = isPoint ? coords as number[] : undefined;
    const row: Record<string, unknown> = { ...(f.properties ?? {}) };
    if (isPoint) {
      row['__lat'] = pointCoords ? pointCoords[1] : null;
      row['__lng'] = pointCoords ? pointCoords[0] : null;
    } else if (f.geometry?.coordinates !== undefined) {
      row['__geometry'] = JSON.stringify(f.geometry.coordinates);
    }
    return row;
  });
  return { ...buildFromRawRows(rawRows), geometryType };
}

function parseKML(buffer: Buffer): { columns: string[]; columnTypes: Record<string, ColumnType>; rows: LayerRow[]; geometryType: Layer['geometryType'] } {
  const text = buffer.toString('utf-8');
  const doc = new DOMParser().parseFromString(text, 'text/xml');

  // @xmldom provides DOM objects at runtime, while this server tsconfig does not
  // include the browser DOM library. Keep these parser-local values structural.
  const getEls = (parent: any, tag: string): any[] => {
    const nl = parent.getElementsByTagName(tag);
    const arr: any[] = [];
    for (let i = 0; i < nl.length; i++) arr.push(nl.item(i));
    return arr;
  };
  const getChildText = (parent: any, tag: string): string =>
    parent.getElementsByTagName(tag).item(0)?.textContent?.trim() ?? '';

  const placemarks = getEls(doc, 'Placemark');

  // Detect dominant geometry type
  let geometryType: Layer['geometryType'] = 'Point';
  for (const pm of placemarks) {
    if (pm.getElementsByTagName('Point').length > 0) { geometryType = 'Point'; break; }
    if (pm.getElementsByTagName('LineString').length > 0) { geometryType = 'LineString'; break; }
    if (pm.getElementsByTagName('Polygon').length > 0) { geometryType = 'Polygon'; break; }
    if (pm.getElementsByTagName('MultiGeometry').length > 0) { geometryType = 'MultiPolygon'; break; }
  }

  const rawRows: Record<string, unknown>[] = placemarks.map((pm) => {
    const row: Record<string, unknown> = {};

    // Standard fields
    const name = getChildText(pm, 'name');
    if (name) row['name'] = name;
    const desc = getChildText(pm, 'description');
    if (desc) row['description'] = desc;

    // SchemaData SimpleData
    for (const sd of getEls(pm, 'SimpleData')) {
      const key = sd.getAttribute('name');
      if (key) row[key] = sd.textContent?.trim() ?? null;
    }
    // ExtendedData Data/value
    for (const d of getEls(pm, 'Data')) {
      const key = d.getAttribute('name');
      if (key) {
        const val = d.getElementsByTagName('value').item(0);
        row[key] = val?.textContent?.trim() ?? null;
      }
    }

    // Point → __lat / __lng
    const pointEl = pm.getElementsByTagName('Point').item(0);
    if (pointEl) {
      const parts = getChildText(pointEl, 'coordinates').split(',').map((s) => parseFloat(s.trim()));
      row['__lng'] = isNaN(parts[0]) ? null : parts[0];
      row['__lat'] = isNaN(parts[1]) ? null : parts[1];
    }

    // LineString / Polygon → __geometry (raw coordinate string)
    const lineEl = pm.getElementsByTagName('LineString').item(0);
    const polyEl = pm.getElementsByTagName('Polygon').item(0);
    if (lineEl) {
      row['__geometry'] = getChildText(lineEl, 'coordinates');
    } else if (polyEl) {
      const outer = polyEl.getElementsByTagName('outerBoundaryIs').item(0);
      row['__geometry'] = outer
        ? getChildText(outer, 'coordinates')
        : getChildText(polyEl, 'coordinates');
    }

    return row;
  });

  return { ...buildFromRawRows(rawRows), geometryType };
}

function buildFromRawRows(rawRows: Record<string, unknown>[], knownColumns?: string[]): { columns: string[]; columnTypes: Record<string, ColumnType>; rows: LayerRow[] } {
  if (rawRows.length === 0) return { columns: knownColumns ?? [], columnTypes: {}, rows: [] };
  // Use explicit headers when provided (e.g. from Excel sheet range) so that
  // columns which are blank in the first data row are never silently dropped.
  const columns = knownColumns && knownColumns.length > 0
    ? knownColumns
    : Object.keys(rawRows[0]);
  const columnTypes: Record<string, ColumnType> = {};
  for (const col of columns) {
    const sample = rawRows.slice(0, 200).map((r) => r[col]);
    columnTypes[col] = detectColumnType(sample, col);
  }
  // Coerce values: Date → ISO string, numbers → Number
  const rows: LayerRow[] = rawRows.map((r) =>
    Object.fromEntries(
      columns.map((c) => {
        const v = r[c];
        // Normalize JS Date objects (from Excel cellDates: true) to YYYY-MM-DD strings
        if (v instanceof Date) return [c, isNaN(v.getTime()) ? null : v.toISOString().slice(0, 10)];
        // Convert Excel serial numbers stored in date columns to ISO dates
        // Formula: (serial - 25569) * 86400s = Unix seconds; 25569 = days from 1900-01-01 to 1970-01-01
        if (columnTypes[c] === 'date' && typeof v === 'number' && !isNaN(v)) {
          const d = new Date(Math.round((v - 25569) * 86_400_000));
          return [c, isNaN(d.getTime()) ? null : d.toISOString().slice(0, 10)];
        }
        if (columnTypes[c] === 'number' && v !== null && v !== '') {
          const n = Number(v);
          return [c, isNaN(n) ? null : n];
        }
        return [c, v as string | number | boolean | null];
      }),
    ),
  );
  return { columns, columnTypes, rows };
}

function coerceDerivedValue(value: unknown, type: ColumnType): string | number | null {
  if (value === null || value === undefined || value === '') return null;
  if (type === 'number') {
    const numberValue = Number(value);
    return Number.isFinite(numberValue) ? numberValue : null;
  }
  if (type === 'date') {
    const date = new Date(String(value));
    return Number.isNaN(date.getTime()) ? null : date.toISOString().slice(0, 10);
  }
  return String(value);
}

function calculateDerivedValue(row: LayerRow, config: DerivedColumnConfig): string | number | null {
  const calculation = config.calculation;
  if (!calculation) return null;
  const values = calculation.sourceColumns.map((column) => row[column]);

  if (calculation.operation === 'copy') {
    return coerceDerivedValue(values[0], config.dataType);
  }
  if (calculation.operation === 'concat') {
    const result = values
      .filter((value) => value !== null && value !== undefined && value !== '')
      .map(String)
      .join(calculation.separator ?? ' ');
    return coerceDerivedValue(result, config.dataType);
  }

  const left = Number(values[0]);
  const right = Number(values[1]);
  if (!Number.isFinite(left) || !Number.isFinite(right)) return null;

  let result: number;
  switch (calculation.operation) {
    case 'add': result = left + right; break;
    case 'subtract': result = left - right; break;
    case 'multiply': result = left * right; break;
    case 'divide': result = right === 0 ? Number.NaN : left / right; break;
    default: return null;
  }
  if (!Number.isFinite(result)) return null;
  const decimals = Math.max(0, Math.min(10, calculation.decimalPlaces ?? 2));
  return Number(result.toFixed(decimals));
}

function materializeDerivedColumns(
  baseColumns: string[],
  baseColumnTypes: Record<string, ColumnType>,
  baseRows: LayerRow[],
  configs: DerivedColumnConfig[],
): { columns: string[]; columnTypes: Record<string, ColumnType>; rows: LayerRow[] } {
  const columns = [...baseColumns];
  const columnTypes = { ...baseColumnTypes };
  let rows = baseRows.map((row) => ({ ...row }));
  const ids = new Set<string>();

  for (const rawConfig of configs) {
    const config: DerivedColumnConfig = { ...rawConfig, name: rawConfig.name.trim() };
    if (!config.id || ids.has(config.id)) throw new Error('Every added column must have a unique id.');
    if (!config.name) throw new Error('Added column name is required.');
    if (columns.includes(config.name)) throw new Error(`Column "${config.name}" already exists.`);
    ids.add(config.id);

    if (config.mode === 'calculated') {
      if (!config.calculation) throw new Error(`Calculation is required for "${config.name}".`);
      if (config.calculation.operation !== 'formula') {
        const requiredSources = config.calculation.operation === 'copy' ? 1 : 2;
        if (config.calculation.sourceColumns.length < requiredSources) {
          throw new Error(`Select ${requiredSources} source column(s) for "${config.name}".`);
        }
        for (const source of config.calculation.sourceColumns.slice(0, requiredSources)) {
          if (!columns.includes(source)) throw new Error(`Source column "${source}" does not exist.`);
        }
      }
    }

    const compiledFormula = config.mode === 'calculated' && config.calculation?.operation === 'formula'
      ? compileFormula(config.calculation.formula ?? '', columns)
      : undefined;

    rows = rows.map((row) => {
      if (config.mode === 'calculated') {
        const value = compiledFormula
          ? coerceDerivedValue(compiledFormula.evaluate(row), config.dataType)
          : calculateDerivedValue(row, config);
        return { ...row, [config.name]: value };
      }
      const existing = row[config.name];
      const shouldApply = config.defaultMode === 'all' || existing === null || existing === undefined || existing === '';
      return shouldApply
        ? { ...row, [config.name]: coerceDerivedValue(config.defaultValue, config.dataType) }
        : row;
    });
    columns.push(config.name);
    columnTypes[config.name] = config.dataType;
  }

  return { columns, columnTypes, rows };
}

function stripDerivedColumns(layer: Layer): { columns: string[]; columnTypes: Record<string, ColumnType>; rows: LayerRow[] } {
  const derivedNames = new Set((layer.derivedColumns ?? []).map((column) => column.name));
  const columns = layer.columns.filter((column) => !derivedNames.has(column));
  const columnTypes = Object.fromEntries(
    Object.entries(layer.columnTypes).filter(([column]) => !derivedNames.has(column)),
  ) as Record<string, ColumnType>;
  const rows = layer.rows.map((row) => Object.fromEntries(
    Object.entries(row).filter(([column]) => !derivedNames.has(column)),
  ) as LayerRow);
  return { columns, columnTypes, rows };
}

function parseFile(buffer: Buffer, originalname: string, sourceType: string): { columns: string[]; columnTypes: Record<string, ColumnType>; rows: LayerRow[]; geometryType: Layer['geometryType'] } {
  const lower = originalname.toLowerCase();
  if (sourceType === 'KML' || lower.endsWith('.kml')) return parseKML(buffer);
  if (sourceType === 'GeoJSON' || lower.endsWith('.geojson') || lower.endsWith('.json')) return parseGeoJSON(buffer);
  if (sourceType === 'CSV' || lower.endsWith('.csv') || lower.endsWith('.tsv')) return { ...parseCSV(buffer), geometryType: 'Point' };
  return { ...parseExcel(buffer), geometryType: 'Point' };
}

// ── ArcGIS Feature Service fetcher ───────────────────────────────────────────
function isArcGISFeatureService(url: string): boolean {
  return /\/(?:Feature|Map)Server\/\d+\/?$/i.test(url.split('?')[0]);
}

async function fetchArcGISFeatureService(serviceUrl: string): Promise<Buffer> {
  const base = serviceUrl.split('?')[0].replace(/\/$/, '');
  const allFeatures: unknown[] = [];
  let offset = 0;
  const PAGE_SIZE = 1000;

  for (;;) {
    const qUrl = `${base}/query?where=1%3D1&outFields=*&f=geojson&resultOffset=${offset}&resultRecordCount=${PAGE_SIZE}`;
    const res = await fetch(qUrl, {
      signal: AbortSignal.timeout(30_000),
      headers: { 'User-Agent': 'Mozilla/5.0 (compatible; ShelterCluster/1.0)' },
    });
    if (!res.ok) {
      if (res.status === 401 || res.status === 403) {
        const err = new Error(`auth_required:${res.status}`) as Error & { needsAuth: boolean };
        err.needsAuth = true;
        throw err;
      }
      throw new Error(
        `ArcGIS service returned HTTP ${res.status}. Make sure the URL ends with /FeatureServer/0 (or the correct layer index) and the service is publicly accessible.`,
      );
    }
    const page = await res.json() as {
      features?: unknown[];
      exceededTransferLimit?: boolean;
      error?: { message: string; code?: number };
    };
    if (page.error) {
      throw new Error(
        `ArcGIS error: ${page.error.message}${page.error.code ? ` (code ${page.error.code})` : ''}`,
      );
    }
    const batch = page.features ?? [];
    allFeatures.push(...batch);
    if (!page.exceededTransferLimit || batch.length === 0) break;
    offset += batch.length;
    if (allFeatures.length >= 100_000) break; // safety cap
  }

  return Buffer.from(JSON.stringify({ type: 'FeatureCollection', features: allFeatures }));
}

// ── URL fetcher ───────────────────────────────────────────────────────────────
async function fetchUrlBuffer(url: string): Promise<{ buffer: Buffer; contentType: string }> {
  const res = await fetch(url, {
    signal: AbortSignal.timeout(30_000),
    headers: { 'User-Agent': 'Mozilla/5.0 (compatible; ShelterCluster/1.0)' },
  });
  if (!res.ok) {
    if (res.status === 401 || res.status === 403) {
      const err = new Error(`auth_required:${res.status}`) as Error & { needsAuth: boolean };
      err.needsAuth = true;
      throw err;
    }
    if (res.status === 404) throw new Error(`File not found at that URL (HTTP 404). Check the link is correct.`);
    throw new Error(`The remote server returned HTTP ${res.status}. Try a different URL or download the file and upload it directly.`);
  }
  const contentType = res.headers.get('content-type') ?? '';
  const arrayBuffer = await res.arrayBuffer();
  return { buffer: Buffer.from(arrayBuffer), contentType };
}

function guessSourceTypeFromUrl(url: string, contentType: string): string {
  if (isArcGISFeatureService(url)) return 'ArcGIS';
  const u = url.toLowerCase().split('?')[0];
  if (u.endsWith('.csv') || u.endsWith('.tsv') || contentType.includes('text/csv')) return 'CSV';
  if (u.endsWith('.geojson') || u.endsWith('.json') || contentType.includes('json')) return 'GeoJSON';
  if (u.endsWith('.kml') || contentType.includes('vnd.google-earth.kml')) return 'KML';
  return 'Excel'; // default — xlsx, xls, Google Sheets export, etc.
}

// ── Auto-refresh scheduler ────────────────────────────────────────────────────
async function refreshLayerFromUrl(layer: Layer): Promise<void> {
  if (!layer.sourceUrl) return;
  try {
    layer.syncStatus = 'Refreshing…';
    let buffer: Buffer;
    let guessed: string;
    if (layer.sourceType === 'ArcGIS') {
      buffer = await fetchArcGISFeatureService(layer.sourceUrl);
      guessed = 'GeoJSON'; // ArcGIS data is always parsed as GeoJSON
    } else {
      const result = await fetchUrlBuffer(layer.sourceUrl);
      buffer = result.buffer;
      guessed = guessSourceTypeFromUrl(layer.sourceUrl, result.contentType);
    }
    const parsed = parseFile(buffer, layer.sourceUrl, guessed);
    const refreshed = materializeDerivedColumns(
      parsed.columns,
      parsed.columnTypes,
      parsed.rows,
      layer.derivedColumns ?? [],
    );
    layer.columns = refreshed.columns;
    layer.columnTypes = refreshed.columnTypes;
    layer.rows = refreshed.rows;

    // Re-apply any admin column type overrides that are still valid for the
    // refreshed column set — prevents manual corrections from being silently lost.
    const overrides = layer.columnTypeOverrides;
    if (overrides) {
      const validTypes: ColumnType[] = ['text', 'number', 'date'];
      for (const [col, overriddenType] of Object.entries(overrides)) {
        if (!layer.columns.includes(col)) continue; // column no longer present
        if (!validTypes.includes(overriddenType)) continue;
        if (layer.columnTypes[col] !== overriddenType) {
          layer.columnTypes[col] = overriddenType;
          layer.rows = recoerceColumn(layer.rows, col, overriddenType);
        }
      }
    }

    layer.featureCount = layer.rowFilters && hasConditions(layer.rowFilters)
      ? applyRowFilters(layer.rows, layer.columnTypes, layer.rowFilters).length
      : layer.rows.length;
    layer.syncStatus = 'Loaded';
    layer.lastRefreshedAt = new Date().toISOString();
    layer.updatedAt = new Date().toISOString();
    await updateLayer(layer);
    await addAuditEvent('Auto-refreshed layer', layer.name, 'Scheduler',
      `${layer.featureCount} visible rows, ${layer.rows.length} stored rows, ${layer.columns.length} columns`,
      { layerId: layer.id, featureCount: layer.featureCount, storedRowCount: layer.rows.length, columnCount: layer.columns.length, sourceUrl: layer.sourceUrl ?? null });
  } catch (err) {
    layer.syncStatus = `Error: ${(err as Error).message}`;
    await updateLayer(layer);
    await addAuditEvent('Auto-refresh failed', layer.name, 'Scheduler',
      (err as Error).message,
      { layerId: layer.id, sourceUrl: layer.sourceUrl ?? null, error: (err as Error).message });
  }
}

setInterval(() => {
  const now = Date.now();
  for (const layer of layers) {
    if (!layer.sourceUrl || !layer.refreshInterval || layer.refreshInterval <= 0) continue;
    const lastMs = layer.lastRefreshedAt ? new Date(layer.lastRefreshedAt).getTime() : 0;
    const intervalMs = layer.refreshInterval * 60 * 1000;
    if (now - lastMs >= intervalMs) {
      void refreshLayerFromUrl(layer);
    }
  }
}, 60_000); // check every minute

// ── Coordinate coverage warning ───────────────────────────────────────────────
function computeCoordinateWarning(rows: LayerRow[], latSuggestion?: string, lngSuggestion?: string): string | undefined {
  if (rows.length === 0) return undefined;
  if (!latSuggestion && !lngSuggestion) {
    return 'No latitude/longitude columns were detected — select them manually in the next step to show points on the map.';
  }
  if (latSuggestion && lngSuggestion) {
    const validCount = rows.filter((r) => {
      const lat = r[latSuggestion]; const lng = r[lngSuggestion];
      return lat != null && lat !== '' && lng != null && lng !== '' &&
        !isNaN(Number(lat)) && !isNaN(Number(lng));
    }).length;
    const pct = Math.round((validCount / rows.length) * 100);
    if (pct < 50) {
      return `Only ${pct}% of rows (${validCount.toLocaleString()} of ${rows.length.toLocaleString()}) have valid coordinates in the suggested columns — the map may show few or no points. Check that the correct latitude/longitude columns are selected.`;
    }
  }
  return undefined;
}

// ── Routes ────────────────────────────────────────────────────────────────────

// POST /api/layers/parse — parse file, return columns (temporary, not saved)
router.post(
  '/layers/parse',
  requireAuth,
  upload.single('file'),
  (req: Request, res: Response): void => {
    try {
      if (!req.file) { res.status(400).json({ error: 'No file uploaded' }); return; }
      const sourceType = (req.body as Record<string, string>).sourceType ?? 'Excel';
      const parsed = parseFile(req.file.buffer, req.file.originalname, sourceType);

      const uploadId = newId();
      const pending: PendingUpload = {
        ...parsed,
        latSuggestion: suggestLat(parsed.columns),
        lngSuggestion: suggestLng(parsed.columns),
        geometryColumnSuggestion: parsed.geometryType !== 'Point' ? suggestGeometryColumn(parsed.columns) : undefined,
        geometryType: parsed.geometryType,
        expiresAt: Date.now() + 30 * 60 * 1000, // 30 min
      };
      pendingUploads.set(uploadId, pending);

      // Compute coordinate coverage warning (only relevant for Point layers)
      const coordinateWarning = parsed.geometryType === 'Point'
        ? computeCoordinateWarning(parsed.rows, pending.latSuggestion, pending.lngSuggestion)
        : undefined;

      res.json({
        uploadId,
        columns: parsed.columns,
        columnTypes: parsed.columnTypes,
        rowCount: parsed.rows.length,
        sampleRows: parsed.rows.slice(0, 50),
        latSuggestion: pending.latSuggestion,
        lngSuggestion: pending.lngSuggestion,
        geometryColumnSuggestion: pending.geometryColumnSuggestion,
        geometryType: parsed.geometryType,
        coordinateWarning,
      });
    } catch (err) {
      res.status(400).json({ error: (err as Error).message ?? 'Parse error' });
    }
  },
);

// POST /api/layers/parse-url — fetch a remote URL and parse it
router.post('/layers/parse-url', requireAuth, (req: Request, res: Response): void => {
  const { url } = req.body as { url?: string };
  if (!url?.trim()) { res.status(400).json({ error: 'url required' }); return; }

  const trimmed = url.trim();
  const arcgis = isArcGISFeatureService(trimmed);

  // ArcGIS Feature Services are fetched via the paginated REST query endpoint;
  // everything else goes through the generic URL fetcher.
  const fetchPromise: Promise<{ buffer: Buffer; guessed: string }> = arcgis
    ? fetchArcGISFeatureService(trimmed).then((buffer) => ({ buffer, guessed: 'ArcGIS' }))
    : fetchUrlBuffer(trimmed).then(({ buffer, contentType }) => ({
        buffer,
        guessed: guessSourceTypeFromUrl(trimmed, contentType),
      }));

  fetchPromise
    .then(({ buffer, guessed }) => {
      // ArcGIS data comes back as GeoJSON; everything else keeps its own parser.
      const parsed = parseFile(buffer, trimmed, guessed === 'ArcGIS' ? 'GeoJSON' : guessed);

      const uploadId = newId();
      const pending: PendingUpload = {
        ...parsed,
        latSuggestion: suggestLat(parsed.columns),
        lngSuggestion: suggestLng(parsed.columns),
        geometryColumnSuggestion: parsed.geometryType !== 'Point' ? suggestGeometryColumn(parsed.columns) : undefined,
        geometryType: parsed.geometryType,
        expiresAt: Date.now() + 30 * 60 * 1000,
      };
      pendingUploads.set(uploadId, pending);

      const coordinateWarning = parsed.geometryType === 'Point'
        ? computeCoordinateWarning(parsed.rows, pending.latSuggestion, pending.lngSuggestion)
        : undefined;

      res.json({
        uploadId,
        sourceUrl: trimmed,
        guessedType: guessed,
        columns: parsed.columns,
        columnTypes: parsed.columnTypes,
        rowCount: parsed.rows.length,
        sampleRows: parsed.rows.slice(0, 50),
        latSuggestion: pending.latSuggestion,
        lngSuggestion: pending.lngSuggestion,
        geometryColumnSuggestion: pending.geometryColumnSuggestion,
        geometryType: parsed.geometryType,
        coordinateWarning,
      });
    })
    .catch((err: Error & { needsAuth?: boolean }) => {
      if (err.needsAuth) {
        res.status(403).json({ needsAuth: true, error: 'The remote server requires authentication.' });
      } else {
        res.status(400).json({ error: err.message ?? 'Failed to fetch URL' });
      }
    });
});

// POST /api/layers — create layer from pending upload
router.post('/layers', requireAdmin, async (req: Request, res: Response): Promise<void> => {
  const body = req.body as {
    uploadId?: string;
    name?: string;
    description?: string;
    color?: string;
    sourceType?: string;
    geometryType?: string;
    latColumn?: string;
    lngColumn?: string;
    sourceUrl?: string;
    refreshInterval?: number;
  };

  if (!body.uploadId) { res.status(400).json({ error: 'uploadId required' }); return; }
  if (!body.name?.trim()) { res.status(400).json({ error: 'name required' }); return; }

  const pending = pendingUploads.get(body.uploadId);
  if (!pending) { res.status(404).json({ error: 'Upload not found or expired. Please upload the file again.' }); return; }

  pendingUploads.delete(body.uploadId);

  // Store ALL rows; filters are applied at query time so they can be edited later
  const rawFilters = migrateFilters((body as Record<string, unknown>).rowFilters);
  const activeFilters = rawFilters && hasConditions(rawFilters) ? rawFilters : undefined;
  const allRows = pending.rows;

  const now = new Date().toISOString();
  const newLayer: Layer = {
    id: newId(),
    name: body.name.trim(),
    description: body.description ?? '',
    sourceType: (body.sourceType as Layer['sourceType']) ?? 'Excel',
    geometryType: (body.geometryType as Layer['geometryType']) ?? pending.geometryType ?? 'Point',
    color: body.color ?? '#2563eb',
    visibility: true,
    priority: layers.length + 1,
    columns: pending.columns,
    columnTypes: pending.columnTypes,
    latColumn: ((body.geometryType ?? pending.geometryType ?? 'Point') === 'Point') ? (body.latColumn || pending.latSuggestion) : undefined,
    lngColumn: ((body.geometryType ?? pending.geometryType ?? 'Point') === 'Point') ? (body.lngColumn || pending.lngSuggestion) : undefined,
    geometryColumn: (body as Record<string, string>).geometryColumn || pending.geometryColumnSuggestion,
    rows: allRows,
    rowFilters: activeFilters,
    featureCount: activeFilters
      ? applyRowFilters(allRows, pending.columnTypes, activeFilters).length
      : allRows.length,
    syncStatus: 'Loaded',
    updatedAt: now,
    sourceUrl: body.sourceUrl || undefined,
    refreshInterval: body.refreshInterval ?? 0,
    lastRefreshedAt: body.sourceUrl ? now : undefined,
  };

  await addLayer(newLayer);
  const actor = users.find((u) => u.id === req.session!.userId)?.name ?? 'Admin';
  await addAuditEvent('Created layer', newLayer.name, actor,
    `${newLayer.featureCount} rows, ${newLayer.columns.length} columns, type: ${newLayer.sourceType}${newLayer.sourceUrl ? ', URL source' : ''}`,
    { layerId: newLayer.id, sourceType: newLayer.sourceType, geometryType: newLayer.geometryType, featureCount: newLayer.featureCount, columnCount: newLayer.columns.length, columns: newLayer.columns, sourceUrl: newLayer.sourceUrl ?? null });

  const { rows: _rows, ...meta } = newLayer;
  res.status(201).json(meta);
});

// POST /api/layers/reorder — bulk-update layer priorities
router.post('/layers/reorder', requireAdmin, async (req: Request, res: Response): Promise<void> => {
  const { ids } = req.body as { ids: string[] };
  if (!Array.isArray(ids)) { res.status(400).json({ error: 'ids array required' }); return; }
  await reorderLayers(ids);
  const actor = users.find((u) => u.id === req.session!.userId)?.name ?? 'Admin';
  await addAuditEvent('Reordered layers', 'Map layers', actor,
    `New order contains ${ids.length} layers`, { layerIds: ids, count: ids.length });
  res.json({ ok: true });
});

// POST /api/layers/:id/refresh — manually re-fetch data from sourceUrl
router.post('/layers/:id/refresh', requireAdmin, (req: Request, res: Response): void => {
  const layer = layers.find((l) => l.id === req.params.id);
  if (!layer) { res.status(404).json({ error: 'Layer not found' }); return; }
  if (!layer.sourceUrl) { res.status(400).json({ error: 'Layer has no source URL' }); return; }

  const actor = users.find((u) => u.id === req.session!.userId)?.name ?? 'Admin';

  refreshLayerFromUrl(layer)
    .then(async () => {
      await addAuditEvent('Manually refreshed layer', layer.name, actor,
        `${layer.featureCount} visible rows, ${layer.rows.length} stored rows, ${layer.columns.length} columns`,
        { layerId: layer.id, featureCount: layer.featureCount, storedRowCount: layer.rows.length, columnCount: layer.columns.length, sourceUrl: layer.sourceUrl ?? null });
      const { rows: _rows, ...meta } = layer;
      res.json(meta);
    })
    .catch(async (err: Error) => {
      await addAuditEvent('Manual refresh failed', layer.name, actor, err.message,
        { layerId: layer.id, sourceUrl: layer.sourceUrl ?? null, error: err.message });
      res.status(500).json({ error: err.message });
    });
});

// GET /api/layers — list all layers (metadata only, no rows)
router.get('/layers', (_req: Request, res: Response): void => {
  res.json(layers.map(({ rows: _rows, ...meta }) => meta));
});

// GET /api/layers/:id — layer metadata (no rows for performance)
router.get('/layers/:id', (req: Request, res: Response): void => {
  const layer = layers.find((l) => l.id === req.params.id);
  if (!layer) { res.status(404).json({ error: 'Layer not found' }); return; }
  const { rows: _rows, ...meta } = layer;
  res.json(meta);
});

// GET /api/layers/:id/rows — returns just the data rows
router.get('/layers/:id/rows', requireAuth, (req: Request, res: Response): void => {
  const layer = layers.find((l) => l.id === req.params.id);
  if (!layer) { res.status(404).json({ error: 'Layer not found' }); return; }
  res.json(layer.rows);
});

// ── Column type re-coercion helper ────────────────────────────────────────────
function recoerceColumn(rows: LayerRow[], col: string, newType: ColumnType): LayerRow[] {
  return rows.map((row) => {
    const v = row[col];
    if (v === null || v === undefined) return row;
    if (newType === 'number') {
      const n = Number(v);
      return { ...row, [col]: isNaN(n) ? null : n };
    }
    if (newType === 'text') {
      return { ...row, [col]: String(v) };
    }
    // date: convert Excel serial numbers to ISO strings; keep other values as-is
    if (newType === 'date') {
      const n = Number(v);
      if (!isNaN(n) && Number.isInteger(n) && n > 30000 && n < 70000) {
        const d = new Date(Math.round((n - 25569) * 86_400_000));
        return { ...row, [col]: isNaN(d.getTime()) ? String(v) : d.toISOString().slice(0, 10) };
      }
    }
    return { ...row, [col]: String(v) };
  });
}

// PATCH /api/layers/:id — update layer metadata
function prepareLayerReplacement(
  layer: Layer,
  source: { columns: string[]; columnTypes: Record<string, ColumnType>; rows: LayerRow[] },
): Layer {
  const materialized = materializeDerivedColumns(
    source.columns, source.columnTypes, source.rows, layer.derivedColumns ?? [],
  );
  const next: Layer = {
    ...layer,
    columns: materialized.columns,
    columnTypes: materialized.columnTypes,
    rows: materialized.rows,
  };
  const validTypes: ColumnType[] = ['text', 'number', 'date'];
  for (const [column, type] of Object.entries(layer.columnTypeOverrides ?? {})) {
    if (!next.columns.includes(column) || !validTypes.includes(type)) continue;
    next.columnTypes[column] = type;
    next.rows = recoerceColumn(next.rows, column, type);
  }
  next.featureCount = next.rowFilters && hasConditions(next.rowFilters)
    ? applyRowFilters(next.rows, next.columnTypes, next.rowFilters).length
    : next.rows.length;
  next.syncStatus = 'Loaded';
  next.updatedAt = new Date().toISOString();
  next.lastRefreshedAt = new Date().toISOString();
  return next;
}

function requirePowerAutomateSecret(req: Request, res: Response, next: NextFunction): void {
  const expected = process.env.POWER_AUTOMATE_UPLOAD_SECRET;
  if (!expected || expected.length < 32) {
    res.status(503).json({ error: 'Power Automate upload is not securely configured.' });
    return;
  }
  const authorization = req.get('authorization') ?? '';
  const supplied = authorization.startsWith('Bearer ') ? authorization.slice(7).trim() : '';
  const expectedBuffer = Buffer.from(expected);
  const suppliedBuffer = Buffer.from(supplied);
  if (expectedBuffer.length !== suppliedBuffer.length || !crypto.timingSafeEqual(expectedBuffer, suppliedBuffer)) {
    res.status(401).json({ error: 'Invalid integration credentials.' });
    return;
  }
  next();
}

const powerAutomateExcelBody = raw({
  type: [
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    'application/vnd.ms-excel',
    'application/octet-stream',
  ],
  limit: '50mb',
});

router.patch('/layers/:id', requireAdmin, async (req: Request, res: Response): Promise<void> => {

  const layer = layers.find((l) => l.id === req.params.id);
  if (!layer) { res.status(404).json({ error: 'Layer not found' }); return; }
  const body = req.body as Partial<Layer>;
  const auditBefore = {
    name: layer.name, description: layer.description, color: layer.color, visibility: layer.visibility,
    latColumn: layer.latColumn, lngColumn: layer.lngColumn, geometryColumn: layer.geometryColumn,
    rowFilters: layer.rowFilters, symbology: layer.symbology, polygonStyle: layer.polygonStyle,
    polygonSymbology: layer.polygonSymbology, clusterConfig: layer.clusterConfig,
    popupConfig: layer.popupConfig, columnTypeOverrides: layer.columnTypeOverrides,
    derivedColumns: layer.derivedColumns ?? [],
  };
  if (body.name !== undefined) layer.name = body.name;
  if (body.description !== undefined) layer.description = body.description;
  if (body.color !== undefined) layer.color = body.color;
  if (body.visibility !== undefined) layer.visibility = body.visibility;
  if (body.latColumn !== undefined) layer.latColumn = body.latColumn;
  if (body.lngColumn !== undefined) layer.lngColumn = body.lngColumn;
  if ((body as Record<string, unknown>).geometryColumn !== undefined) layer.geometryColumn = (body as Record<string, string>).geometryColumn;
  if ((body as Record<string, unknown>).rowFilters !== undefined) {
    const rf = migrateFilters((body as Record<string, unknown>).rowFilters);
    layer.rowFilters = rf && hasConditions(rf) ? rf : undefined;
    layer.featureCount = layer.rowFilters && hasConditions(layer.rowFilters)
      ? applyRowFilters(layer.rows, layer.columnTypes, layer.rowFilters).length
      : layer.rows.length;
  }
  if (body.derivedColumns !== undefined) {
    const configs = Array.isArray(body.derivedColumns) ? body.derivedColumns : [];
    const nextNames = new Set(configs.map((column) => column.name));
    const removedNames = (layer.derivedColumns ?? [])
      .map((column) => column.name)
      .filter((column) => !nextNames.has(column));
    for (const column of removedNames) {
      const dependencies: string[] = [];
      if (layer.latColumn === column || layer.lngColumn === column || layer.geometryColumn === column) dependencies.push('map geometry');
      if (layer.symbology?.column === column || layer.polygonSymbology?.column === column) dependencies.push('symbology');
      if (layer.popupConfig?.fields.some((field) => field.column === column)) dependencies.push('popup');
      if (layer.rowFilters && flattenConditions(layer.rowFilters).some((condition) => condition.column === column)) dependencies.push('row filters');
      if (indicatorConfigs.some((config) => config.layerId === layer.id && config.column === column)) dependencies.push('indicators');
      if (filterConfigs.some((config) => config.layerId === layer.id && config.column === column)) dependencies.push('dashboard filters');
      if (dependencies.length > 0) {
        res.status(400).json({ error: `Column "${column}" is used by ${dependencies.join(', ')}. Remove those references first.` });
        return;
      }
    }
    const base = stripDerivedColumns(layer);
    try {
      const materialized = materializeDerivedColumns(base.columns, base.columnTypes, base.rows, configs);
      layer.derivedColumns = configs;
      layer.columns = materialized.columns;
      layer.columnTypes = materialized.columnTypes;
      layer.rows = materialized.rows;
      layer.featureCount = layer.rowFilters && hasConditions(layer.rowFilters)
        ? applyRowFilters(layer.rows, layer.columnTypes, layer.rowFilters).length
        : layer.rows.length;
    } catch (error) {
      res.status(400).json({ error: (error as Error).message });
      return;
    }
  }
  // Column type overrides — re-coerce stored rows for any changed columns
  const overrides = (body as Record<string, unknown>).columnTypeOverrides as Record<string, ColumnType> | undefined;
  const removedFilterConditions: { column: string; operator: FilterOp; newType: ColumnType }[] = [];
  if (overrides && typeof overrides === 'object') {
    const changedCols: string[] = [];
    for (const [col, newType] of Object.entries(overrides)) {
      if (!layer.columns.includes(col)) continue;
      const valid: ColumnType[] = ['text', 'number', 'date'];
      if (!valid.includes(newType)) continue;
      if (layer.columnTypes[col] !== newType) {
        // Check for filter conditions on this column that are incompatible with the new type
        if (layer.rowFilters && hasConditions(layer.rowFilters)) {
          const validOps = OPS_VALID_FOR_TYPE[newType];
          const conflicting = flattenConditions(layer.rowFilters).filter(
            (c) => c.column === col && !validOps.has(c.operator as FilterOp),
          );
          if (conflicting.length > 0) {
            for (const c of conflicting) removedFilterConditions.push({ column: col, operator: c.operator as FilterOp, newType });
            const stripped = stripConditions(
              layer.rowFilters,
              (c) => c.column === col && !validOps.has(c.operator as FilterOp),
            );
            layer.rowFilters = stripped?.kind === 'group' && hasConditions(stripped) ? stripped as RowFilters : undefined;
          }
        }
        layer.columnTypes[col] = newType;
        layer.rows = recoerceColumn(layer.rows, col, newType);
        changedCols.push(`${col}→${newType}`);
      }
      // Persist this override so auto-refreshes will re-apply it
      layer.columnTypeOverrides = { ...(layer.columnTypeOverrides ?? {}), [col]: newType };
    }
    if (changedCols.length > 0) {
      // Re-evaluate row filter counts after re-coercion
      layer.featureCount = layer.rowFilters && hasConditions(layer.rowFilters)
        ? applyRowFilters(layer.rows, layer.columnTypes, layer.rowFilters).length
        : layer.rows.length;
    }
  }
  if (body.symbology !== undefined) layer.symbology = body.symbology as LayerSymbology;
  if ((body as Record<string, unknown>).polygonStyle !== undefined) layer.polygonStyle = (body as Record<string, unknown>).polygonStyle as import('../data/store.js').PolygonStyle;
  if ((body as Record<string, unknown>).polygonSymbology !== undefined) layer.polygonSymbology = (body as Record<string, unknown>).polygonSymbology as import('../data/store.js').PolygonSymbology;
  if (body.clusterConfig !== undefined) layer.clusterConfig = body.clusterConfig as ClusterConfig;
  if ((body as Record<string, unknown>).popupConfig !== undefined) layer.popupConfig = (body as Record<string, unknown>).popupConfig as import('../data/store.js').PopupConfig;
  layer.updatedAt = new Date().toISOString();

  await updateLayer(layer);

  const actor = users.find((u) => u.id === req.session!.userId)?.name ?? 'Admin';
  const changedFields: string[] = [];
  const normalizeForAudit = (value: unknown): unknown => {
    if (Array.isArray(value)) return value.map(normalizeForAudit);
    if (value && typeof value === 'object') {
      return Object.fromEntries(Object.entries(value as Record<string, unknown>)
        .filter(([, item]) => item !== undefined)
        .sort(([left], [right]) => left.localeCompare(right))
        .map(([key, item]) => [key, normalizeForAudit(item)]));
    }
    return value ?? null;
  };
  const changed = (before: unknown, after: unknown) =>
    JSON.stringify(normalizeForAudit(before)) !== JSON.stringify(normalizeForAudit(after));
  const displayAuditValue = (value: unknown) => {
    if (value === '') return 'empty';
    if (value === undefined || value === null) return 'none';
    const rendered = typeof value === 'string' ? `"${value}"` : JSON.stringify(normalizeForAudit(value));
    return rendered.length > 180 ? `${rendered.slice(0, 177)}...` : rendered;
  };
  if (changed(auditBefore.name, layer.name)) changedFields.push(`name: "${auditBefore.name}" → "${layer.name}"`);
  if (changed(auditBefore.description, layer.description)) changedFields.push(`description: ${displayAuditValue(auditBefore.description)} → ${displayAuditValue(layer.description)}`);
  if (changed(auditBefore.color, layer.color)) changedFields.push(`color: ${auditBefore.color} → ${layer.color}`);
  if (changed(auditBefore.visibility, layer.visibility)) changedFields.push(`visibility: ${auditBefore.visibility} → ${layer.visibility}`);
  if (changed(auditBefore.latColumn, layer.latColumn)) changedFields.push(`latitude column: ${auditBefore.latColumn ?? 'none'} → ${layer.latColumn ?? 'none'}`);
  if (changed(auditBefore.lngColumn, layer.lngColumn)) changedFields.push(`longitude column: ${auditBefore.lngColumn ?? 'none'} → ${layer.lngColumn ?? 'none'}`);
  if (changed(auditBefore.geometryColumn, layer.geometryColumn)) changedFields.push(`geometry column: ${auditBefore.geometryColumn ?? 'none'} → ${layer.geometryColumn ?? 'none'}`);
  if (changed(auditBefore.rowFilters, layer.rowFilters)) changedFields.push(`row filters: ${displayAuditValue(auditBefore.rowFilters)} → ${displayAuditValue(layer.rowFilters)}`);
  if (changed(auditBefore.symbology, layer.symbology)) {
    const before = auditBefore.symbology;
    const after = layer.symbology;
    if (changed(before?.column, after?.column)) changedFields.push(`symbology column: ${displayAuditValue(before?.column)} → ${displayAuditValue(after?.column)}`);
    if (changed(before?.defaultColor, after?.defaultColor)) changedFields.push(`default point/line color: ${displayAuditValue(before?.defaultColor)} → ${displayAuditValue(after?.defaultColor)}`);
    if (changed(before?.defaultIcon, after?.defaultIcon)) changedFields.push(`default point icon: ${displayAuditValue(before?.defaultIcon)} → ${displayAuditValue(after?.defaultIcon)}`);
    if (changed(before?.defaultSize, after?.defaultSize)) changedFields.push(`default point size: ${displayAuditValue(before?.defaultSize)} → ${displayAuditValue(after?.defaultSize)}`);
    if (changed(before?.rules, after?.rules)) changedFields.push(`symbology rules: ${before?.rules?.length ?? 0} → ${after?.rules?.length ?? 0}`);
  }
  if (changed(auditBefore.polygonStyle, layer.polygonStyle)) changedFields.push(`polygon style: ${displayAuditValue(auditBefore.polygonStyle)} → ${displayAuditValue(layer.polygonStyle)}`);
  if (changed(auditBefore.polygonSymbology, layer.polygonSymbology)) changedFields.push(`polygon symbology: ${displayAuditValue(auditBefore.polygonSymbology)} → ${displayAuditValue(layer.polygonSymbology)}`);
  if (changed(auditBefore.clusterConfig, layer.clusterConfig)) changedFields.push(`clustering: ${displayAuditValue(auditBefore.clusterConfig)} → ${displayAuditValue(layer.clusterConfig)}`);
  if (changed(auditBefore.popupConfig, layer.popupConfig)) changedFields.push(`popup settings: ${displayAuditValue(auditBefore.popupConfig)} → ${displayAuditValue(layer.popupConfig)}`);
  if (changed(auditBefore.columnTypeOverrides, layer.columnTypeOverrides)) changedFields.push(`column types: ${displayAuditValue(auditBefore.columnTypeOverrides)} → ${displayAuditValue(layer.columnTypeOverrides)}`);
  const beforeDerived = new Set(auditBefore.derivedColumns.map((column) => column.name));
  const afterDerived = new Set((layer.derivedColumns ?? []).map((column) => column.name));
  const addedColumns = [...afterDerived].filter((name) => !beforeDerived.has(name));
  const removedColumns = [...beforeDerived].filter((name) => !afterDerived.has(name));
  if (addedColumns.length) changedFields.push(`columns added: ${addedColumns.join(', ')}`);
  if (removedColumns.length) changedFields.push(`columns deleted: ${removedColumns.join(', ')}`);
  if (changed(auditBefore.derivedColumns, layer.derivedColumns ?? []) && !addedColumns.length && !removedColumns.length) changedFields.push(`calculated/default column definitions: ${displayAuditValue(auditBefore.derivedColumns)} → ${displayAuditValue(layer.derivedColumns ?? [])}`);
  if (changedFields.length > 0) {
    console.log(`Layer ${layer.name} updated by ${actor}: ${changedFields.join('; ')}`);
    await addAuditEvent('Updated layer', layer.name, actor, changedFields.join('; '),
      { layerId: layer.id });
  }
  const { rows: _rows, ...meta } = layer;
  res.json({ ...meta, removedFilterConditions: removedFilterConditions.length ? removedFilterConditions : undefined });
});

// POST /api/layers/:id/reupload — replace layer data from a pending upload
router.post('/layers/:id/reupload', requireAdmin, async (req: Request, res: Response): Promise<void> => {
  const layer = layers.find((l) => l.id === req.params.id);
  if (!layer) { res.status(404).json({ error: 'Layer not found' }); return; }

  const { uploadId } = req.body as { uploadId?: string };
  if (!uploadId) { res.status(400).json({ error: 'uploadId required' }); return; }

  const pending = pendingUploads.get(uploadId);
  if (!pending) { res.status(404).json({ error: 'Upload not found or expired. Please re-upload the file.' }); return; }

  let replacement: Layer;
  try {
    replacement = prepareLayerReplacement(layer, pending);
  } catch (error) {
    res.status(400).json({
      error: `The replacement data is incompatible with an added column: ${(error as Error).message}`,
    });
    return;
  }
  await updateLayer(replacement);
  Object.assign(layer, replacement);
  pendingUploads.delete(uploadId);
  const actor = users.find((u) => u.id === req.session!.userId)?.name ?? 'Admin';
  await addAuditEvent('Replaced layer data', layer.name, actor,
    `${layer.featureCount} visible rows, ${layer.rows.length} stored rows, ${layer.columns.length} columns`,
    { layerId: layer.id, sourceType: layer.sourceType, featureCount: layer.featureCount, storedRowCount: layer.rows.length, columnCount: layer.columns.length, columns: layer.columns });
  const { rows: _rows, ...meta } = layer;
  res.json(meta);
});

// POST /api/integrations/layers/:id/upload — Power Automate raw Excel replacement
router.post(
  '/integrations/layers/:id/upload',
  requirePowerAutomateSecret,
  powerAutomateExcelBody,
  async (req: Request, res: Response): Promise<void> => {
    const layer = layers.find((candidate) => candidate.id === req.params.id);
    if (!layer) { res.status(404).json({ error: 'Layer not found.' }); return; }
    if (layer.sourceType !== 'Excel') {
      res.status(400).json({ error: 'This integration endpoint currently accepts Excel-backed layers only.' });
      return;
    }
    if (!Buffer.isBuffer(req.body) || req.body.length === 0) {
      res.status(400).json({
        error: 'Send the Excel file content as the raw request body with an Excel or application/octet-stream content type.',
      });
      return;
    }

    const fileName = req.get('x-file-name')?.trim() || 'power-automate-upload.xlsx';
    const previousStoredRows = layer.rows.length;
    const previousVisibleRows = layer.featureCount;
    let replacement: Layer;
    try {
      const parsed = parseFile(req.body, fileName, 'Excel');
      replacement = prepareLayerReplacement(layer, parsed);
    } catch (error) {
      const message = (error as Error).message;
      await addAuditEvent('Power Automate refresh failed', layer.name, 'Power Automate', message,
        { layerId: layer.id, fileName, fileSize: req.body.length, error: message });
      res.status(400).json({ error: message, layerId: layer.id, unchanged: true });
      return;
    }

    try {
      await updateLayer(replacement);
      Object.assign(layer, replacement);
    } catch (error) {
      const message = (error as Error).message;
      await addAuditEvent('Power Automate refresh failed', layer.name, 'Power Automate', message,
        { layerId: layer.id, fileName, fileSize: req.body.length, error: message });
      res.status(500).json({ error: 'The replacement could not be saved.', layerId: layer.id, unchanged: true });
      return;
    }

    await addAuditEvent('Power Automate refreshed layer', layer.name, 'Power Automate',
      `${previousStoredRows} → ${layer.rows.length} stored rows; ${previousVisibleRows} → ${layer.featureCount} visible rows; ${layer.columns.length} columns`,
      {
        layerId: layer.id, fileName, fileSize: req.body.length,
        previousStoredRows, storedRowCount: layer.rows.length,
        previousVisibleRows, featureCount: layer.featureCount,
        columnCount: layer.columns.length,
        derivedColumns: (layer.derivedColumns ?? []).map((column) => column.name),
      });
    res.json({
      ok: true,
      layerId: layer.id,
      layerName: layer.name,
      storedRowCount: layer.rows.length,
      visibleRowCount: layer.featureCount,
      columnCount: layer.columns.length,
      derivedColumns: (layer.derivedColumns ?? []).map((column) => column.name),
      refreshedAt: layer.lastRefreshedAt,
    });
  },
);

// DELETE /api/layers/:id
router.delete('/layers/:id', requireAdmin, async (req: Request, res: Response): Promise<void> => {
  const layer = layers.find((l) => l.id === req.params.id);
  if (!layer) { res.status(404).json({ error: 'Layer not found' }); return; }
  const featureCount = layer.featureCount;
  const actor = users.find((u) => u.id === req.session!.userId)?.name ?? 'Admin';

  // Cascade: remove all indicator and filter configs that reference this layer
  const orphanedIndicators = indicatorConfigs.filter((c) => c.layerId === layer.id).map((c) => c.id);
  const orphanedFilters = filterConfigs.filter((c) => c.layerId === layer.id).map((c) => c.id);
  await Promise.all([
    ...orphanedIndicators.map((id) => removeIndicatorConfig(id)),
    ...orphanedFilters.map((id) => removeFilterConfig(id)),
  ]);
  if (orphanedIndicators.length > 0) {
    await addAuditEvent('Removed orphaned indicators', layer.name, actor,
      `${orphanedIndicators.length} indicator config(s) deleted with layer`,
      { layerId: layer.id, indicatorIds: orphanedIndicators, count: orphanedIndicators.length });
  }
  if (orphanedFilters.length > 0) {
    await addAuditEvent('Removed orphaned slicers', layer.name, actor,
      `${orphanedFilters.length} slicer config(s) deleted with layer`,
      { layerId: layer.id, slicerIds: orphanedFilters, count: orphanedFilters.length });
  }

  await removeLayer(layer.id);
  await addAuditEvent('Deleted layer', layer.name, actor,
    `had ${featureCount} visible rows, ${layer.rows.length} stored rows, ${layer.columns.length} columns`,
    { layerId: layer.id, sourceType: layer.sourceType, featureCount, storedRowCount: layer.rows.length, columnCount: layer.columns.length, columns: layer.columns });
  res.json({ ok: true });
});

// GET /api/dashboard/layer-data — all visible layers WITH rows (for dashboard computation)
// The response shape is typed against DashboardLayerDataItem (from @workspace/api-zod),
// which is also imported by the frontend. TypeScript's `satisfies` check here means any
// field the frontend map renderer reads must be present — the compiler catches omissions
// before they silently drop map points at runtime.
router.get('/dashboard/layer-data', requireAuth, (_req: Request, res: Response): void => {
  res.json(
    layers
      .filter((l) => l.visibility)
      .map((l) => ({
        id: l.id,
        name: l.name,
        color: l.color,
        columns: l.columns,
        columnTypes: l.columnTypes,
        latColumn: l.latColumn,
        lngColumn: l.lngColumn,
        geometryType: l.geometryType,
        geometryColumn: l.geometryColumn,
        visibility: l.visibility,
        featureCount: l.featureCount,
        sourceType: l.sourceType,
        symbology: l.symbology,
        polygonStyle: l.polygonStyle,
        polygonSymbology: l.polygonSymbology,
        clusterConfig: l.clusterConfig,
        popupConfig: l.popupConfig,
        derivedColumns: l.derivedColumns,
        rows: l.rowFilters && hasConditions(l.rowFilters)
          ? applyRowFilters(l.rows, l.columnTypes, l.rowFilters)
          : l.rows,
      } satisfies DashboardLayerDataItem)),
  );
});

export default router;