import path from 'node:path';
import { statSync } from 'node:fs';

import type { CityTariffRow } from './types';
import {
  parseFirstSheetToObjects,
  parseSharedStrings,
  readXlsxXmlFilesCached,
} from '@/lib/xlsx/simpleXlsx';

function slugify(input: string) {
  const base = input
    .normalize('NFKD')
    .replace(/[\u0300-\u036f]/g, '')
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, '-')
    .replace(/(^-|-$)/g, '');
  return base || 'city';
}

function parsePickup(value: unknown) {
  const v = String(value ?? '').trim().toLowerCase();
  return v === 'yes' || v === 'true' || v === '1' || v === 'oui';
}

function parsePriceValue(price: unknown): number | null {
  const v = String(price ?? '').trim();
  if (!v) return null;
  const m = v.match(/-?\d+(\.\d+)?/);
  if (!m) return null;
  const n = Number(m[0]);
  return Number.isFinite(n) ? n : null;
}

function toNumberOrNull(value: unknown): number | null {
  if (value == null || value === '') return null;
  const n = typeof value === 'number' ? value : Number(String(value).trim());
  return Number.isFinite(n) ? n : null;
}

let cached: { mtimeMs: number; data: CityTariffRow[] } | null = null;

export function loadCitiesFromXlsx() {
  const xlsxPath = path.join(process.cwd(), 'src/models/cities.xlsx');
  const mtimeMs = statSync(xlsxPath).mtimeMs;
  if (cached && cached.mtimeMs === mtimeMs) return cached.data;

  const files = readXlsxXmlFilesCached(xlsxPath);

  const sharedStringsXml = files.get('xl/sharedStrings.xml');
  const sheetXml = files.get('xl/worksheets/sheet1.xml');
  if (!sharedStringsXml || !sheetXml) {
    throw new Error('cities.xlsx is missing required XML parts');
  }

  const sharedStrings = parseSharedStrings(sharedStringsXml);
  const rawObjects = parseFirstSheetToObjects({ sheetXml, sharedStrings });

  const usedIds = new Map<string, number>();
  const rows: CityTariffRow[] = [];

  for (const obj of rawObjects) {
    const city = String(obj.city ?? '').trim();
    if (!city) continue;

    const baseId = slugify(city);
    const count = (usedIds.get(baseId) ?? 0) + 1;
    usedIds.set(baseId, count);
    const id = count === 1 ? baseId : `${baseId}-${count}`;

    const price = String(obj.price ?? '').trim();
    const duration = String(obj.duration ?? '').trim();
    const pickup = parsePickup(obj.pickup);
    const x = toNumberOrNull(obj.x);
    const y = toNumberOrNull(obj.y);

    rows.push({
      id,
      city,
      name: { en: city, fr: city, ar: city },
      price,
      priceValue: parsePriceValue(price),
      duration,
      pickup,
      x,
      y,
    });
  }

  cached = { mtimeMs, data: rows };
  return rows;
}