// Einlesen der Keramix-Preisliste (.xlsx) im Browser ohne Zusatzpaket + Abgleich mit den Shop-Varianten.
// Eine .xlsx-Datei ist ein ZIP mit XML-Dateien; entpackt wird mit der Browser-/Node-API DecompressionStream.

export interface PriceListEntry {
  sku: string;
  name: string;
  sheet: string;
  price: number;
  /** Stück pro Karton (VE); null, wenn das Blatt keine VE-Spalte hat (z. B. „Glasuren"). */
  ve: number | null;
}

export interface PriceListParseResult {
  entries: PriceListEntry[];
  /** SKUs, die in mehreren Zeilen vorkommen (die erste Zeile gilt). */
  duplicates: string[];
  /** Blätter ohne erkennbare SKU-/Preisspalte, Zeilen ohne gültigen Preis usw. */
  warnings: string[];
  sheets: { name: string; rows: number; hasVe: boolean }[];
}

// ---------- ZIP ----------

const u16 = (d: DataView, o: number) => d.getUint16(o, true);
const u32 = (d: DataView, o: number) => d.getUint32(o, true);

async function inflateRaw(data: Uint8Array): Promise<Uint8Array> {
  const stream = new Blob([data as BlobPart]).stream().pipeThrough(new DecompressionStream('deflate-raw'));
  return new Uint8Array(await new Response(stream).arrayBuffer());
}

async function readZip(buf: ArrayBuffer): Promise<Map<string, () => Promise<string>>> {
  const bytes = new Uint8Array(buf);
  const dv = new DataView(buf);
  let eocd = -1;
  for (let i = bytes.length - 22; i >= Math.max(0, bytes.length - 65557); i--) {
    if (u32(dv, i) === 0x06054b50) {
      eocd = i;
      break;
    }
  }
  if (eocd < 0) throw new Error('Keine gültige .xlsx-Datei (ZIP-Struktur nicht gefunden).');
  const count = u16(dv, eocd + 10);
  let p = u32(dv, eocd + 16);
  const files = new Map<string, () => Promise<string>>();
  const dec = new TextDecoder('utf-8');
  for (let n = 0; n < count; n++) {
    if (u32(dv, p) !== 0x02014b50) throw new Error('Beschädigte .xlsx-Datei (Zentralverzeichnis).');
    const method = u16(dv, p + 10);
    const compSize = u32(dv, p + 20);
    const nameLen = u16(dv, p + 28);
    const extraLen = u16(dv, p + 30);
    const commentLen = u16(dv, p + 32);
    const localOff = u32(dv, p + 42);
    const name = dec.decode(bytes.subarray(p + 46, p + 46 + nameLen));
    files.set(name, async () => {
      const lnLen = u16(dv, localOff + 26);
      const leLen = u16(dv, localOff + 28);
      const start = localOff + 30 + lnLen + leLen;
      const raw = bytes.subarray(start, start + compSize);
      if (method === 0) return dec.decode(raw);
      if (method === 8) return dec.decode(await inflateRaw(raw));
      throw new Error(`Nicht unterstütztes ZIP-Kompressionsverfahren (${method}).`);
    });
    p += 46 + nameLen + extraLen + commentLen;
  }
  return files;
}

// ---------- XML (per Regex, ausreichend für SpreadsheetML) ----------

const decodeXml = (s: string): string =>
  s
    .replace(/&lt;/g, '<')
    .replace(/&gt;/g, '>')
    .replace(/&quot;/g, '"')
    .replace(/&apos;/g, "'")
    .replace(/&#(\d+);/g, (_, d) => String.fromCodePoint(Number(d)))
    .replace(/&#x([0-9a-fA-F]+);/g, (_, h) => String.fromCodePoint(parseInt(h, 16)))
    .replace(/&amp;/g, '&');

const attr = (tag: string, name: string): string => {
  const m = tag.match(new RegExp(`(?:^|\\s)${name}="([^"]*)"`));
  return m ? decodeXml(m[1]) : '';
};

const textOf = (xml: string): string => {
  const parts: string[] = [];
  const re = /<t\b[^>]*>([\s\S]*?)<\/t>/g;
  let m: RegExpExecArray | null;
  while ((m = re.exec(xml))) parts.push(decodeXml(m[1]));
  return parts.join('');
};

const colIndex = (ref: string): number => {
  const letters = ref.replace(/[^A-Z]/gi, '').toUpperCase();
  let n = 0;
  for (const ch of letters) n = n * 26 + (ch.charCodeAt(0) - 64);
  return n - 1;
};

type Cell = { text: string; num: number | null };

function parseSheet(xml: string, shared: string[]): Map<number, Map<number, Cell>> {
  const rows = new Map<number, Map<number, Cell>>();
  const rowRe = /<row\b([^>]*?)(?:\/>|>([\s\S]*?)<\/row>)/g;
  let rm: RegExpExecArray | null;
  let autoRow = 0;
  while ((rm = rowRe.exec(xml))) {
    autoRow++;
    const rAttr = attr(rm[1], 'r');
    const rowNo = rAttr ? Number(rAttr) : autoRow;
    const cells = new Map<number, Cell>();
    const body = rm[2] || '';
    const cellRe = /<c\b([^>]*?)(?:\/>|>([\s\S]*?)<\/c>)/g;
    let cm: RegExpExecArray | null;
    let autoCol = -1;
    while ((cm = cellRe.exec(body))) {
      autoCol++;
      const ref = attr(cm[1], 'r');
      const col = ref ? colIndex(ref) : autoCol;
      const t = attr(cm[1], 't');
      const inner = cm[2] || '';
      let text = '';
      let num: number | null = null;
      if (t === 'inlineStr') {
        text = textOf(inner);
      } else {
        const v = inner.match(/<v>([\s\S]*?)<\/v>/);
        const raw = v ? decodeXml(v[1]) : '';
        if (t === 's') text = shared[Number(raw)] ?? '';
        else if (t === 'str' || t === 'e' || t === 'b') text = raw;
        else {
          text = raw;
          const n = Number(raw);
          num = raw !== '' && Number.isFinite(n) ? n : null;
        }
      }
      cells.set(col, { text: text.trim(), num });
    }
    rows.set(rowNo, cells);
  }
  return rows;
}

const norm = (s: string) => s.toLowerCase().replace(/\s+/g, ' ').trim();

const cellNumber = (c: Cell | undefined): number | null => {
  if (!c) return null;
  if (c.num !== null) return c.num;
  if (!c.text) return null;
  const n = Number(c.text.replace(/\s/g, '').replace(',', '.'));
  return Number.isFinite(n) ? n : null;
};

export async function parsePriceListXlsx(buf: ArrayBuffer): Promise<PriceListParseResult> {
  const zip = await readZip(buf);
  const get = async (name: string): Promise<string | null> => {
    const f = zip.get(name);
    return f ? f() : null;
  };

  const wbXml = await get('xl/workbook.xml');
  const relXml = await get('xl/_rels/workbook.xml.rels');
  if (!wbXml || !relXml) throw new Error('Keine gültige .xlsx-Datei (workbook.xml fehlt).');

  const targets = new Map<string, string>();
  for (const m of relXml.matchAll(/<Relationship\b([^>]*?)\/?>/g)) {
    const target = attr(m[1], 'Target');
    targets.set(attr(m[1], 'Id'), target.startsWith('/') ? target.slice(1) : `xl/${target}`);
  }

  const shared: string[] = [];
  const ssXml = await get('xl/sharedStrings.xml');
  if (ssXml) for (const m of ssXml.matchAll(/<si\b[^>]*>([\s\S]*?)<\/si>/g)) shared.push(textOf(m[1]));

  const result: PriceListParseResult = { entries: [], duplicates: [], warnings: [], sheets: [] };
  const seen = new Set<string>();

  for (const m of wbXml.matchAll(/<sheet\b([^>]*?)\/?>/g)) {
    const name = attr(m[1], 'name');
    const target = targets.get(attr(m[1], 'r:id'));
    const xml = target ? await get(target) : null;
    if (!xml) {
      result.warnings.push(`Blatt „${name}" konnte nicht gelesen werden.`);
      continue;
    }
    const rows = parseSheet(xml, shared);
    const rowNos = [...rows.keys()].sort((a, b) => a - b);

    // Kopfzeile = erste Zeile mit einer Zelle „KMX Code"
    let headerNo = -1;
    let skuCol = -1;
    let nameCol = -1;
    let priceCol = -1;
    let veCol = -1;
    for (const rn of rowNos.slice(0, 20)) {
      const cells = rows.get(rn)!;
      const idx = [...cells.entries()].find(([, c]) => norm(c.text).startsWith('kmx code'));
      if (!idx) continue;
      headerNo = rn;
      skuCol = idx[0];
      for (const [col, c] of cells) {
        const h = norm(c.text);
        if (priceCol < 0 && h.startsWith('preis')) priceCol = col;
        else if (veCol < 0 && h.startsWith('stk')) veCol = col;
        else if (nameCol < 0 && h.endsWith('produktname')) nameCol = col;
      }
      break;
    }
    if (headerNo < 0 || priceCol < 0) {
      result.warnings.push(`Blatt „${name}": Spalte „KMX Code" oder „Preis" nicht gefunden – Blatt übersprungen.`);
      continue;
    }

    let count = 0;
    for (const rn of rowNos) {
      if (rn <= headerNo) continue;
      const cells = rows.get(rn)!;
      const sku = (cells.get(skuCol)?.text || '').trim();
      if (!sku) continue;
      const price = cellNumber(cells.get(priceCol));
      if (price === null) {
        result.warnings.push(`Blatt „${name}", Zeile ${rn}: SKU ${sku} ohne gültigen Preis – übersprungen.`);
        continue;
      }
      const veRaw = veCol >= 0 ? cellNumber(cells.get(veCol)) : null;
      const key = sku.toUpperCase();
      if (seen.has(key)) {
        result.duplicates.push(sku);
        continue;
      }
      seen.add(key);
      count++;
      result.entries.push({
        sku,
        name: nameCol >= 0 ? cells.get(nameCol)?.text || '' : '',
        sheet: name,
        price,
        ve: veRaw !== null && veRaw > 0 ? Math.round(veRaw) : null,
      });
    }
    result.sheets.push({ name, rows: count, hasVe: veCol >= 0 });
  }
  return result;
}

// ---------- Abgleich ----------

export interface ShopVariant {
  id: string;
  sku: string;
  displayName: string;
  einkaufspreis: string; // aktueller Betrag als Zahlenstring ('' wenn leer)
  currency: string; // Währung des Metafelds (Standard EUR)
  salePrice: string; // aktueller Verkaufspreis der Variante ('' wenn unbekannt)
  productId: string;
  productStatus: string;
  tags: string[];
}

export interface PriceCompareRow {
  sku: string;
  variantId: string;
  productId: string;
  title: string;
  currentPrice: string;
  newPrice: string;
  currency: string;
  currentSale: string; // aktueller Verkaufspreis
  currentVe: string; // '' wenn kein VE-Tag
  newVe: string; // '' wenn die Liste keine VE liefert
  currentVeTags: string[];
  priceChanged: boolean;
  veChanged: boolean;
  veConflict: boolean;
  hasAufschlag: boolean; // Produkt trägt den Tag „Aufschlag"
  status: 'unchanged' | 'changed' | 'error';
  errorMessage?: string;
}

export interface OnlyInShopRow {
  sku: string;
  title: string;
  productStatus: string;
}

export interface OnlyInListRow {
  sku: string;
  name: string;
  sheet: string;
  price: number;
  ve: number | null;
}

export interface PriceCompareResult {
  rows: PriceCompareRow[];
  onlyInShop: OnlyInShopRow[];
  onlyInList: OnlyInListRow[];
}

/** Metafeld vom Typ „money" ({"amount":"2.99","currency_code":"EUR"}); akzeptiert zur Sicherheit auch eine reine Zahl. */
export function parseMoneyMetafield(raw: string): { einkaufspreis: string; currency: string } {
  const v = (raw || '').trim();
  if (!v) return { einkaufspreis: '', currency: 'EUR' };
  if (v.startsWith('{')) {
    try {
      const o = JSON.parse(v);
      return { einkaufspreis: o?.amount != null ? String(o.amount) : '', currency: o?.currency_code || 'EUR' };
    } catch {
      return { einkaufspreis: '', currency: 'EUR' };
    }
  }
  return { einkaufspreis: v, currency: 'EUR' };
}

export const VE_TAG_RE = /^VE\s*[:\-]?\s*(\d+)$/i;

export function veFromTags(tags: string[]): { ve: string; veTags: string[] } {
  const veTags = tags.filter((t) => VE_TAG_RE.test(t.trim()));
  const first = veTags[0]?.trim().match(VE_TAG_RE);
  return { ve: first ? String(Number(first[1])) : '', veTags };
}

const samePrice = (current: string, next: number): boolean => {
  if (current === '') return false;
  const c = Number(String(current).replace(',', '.'));
  return Number.isFinite(c) && Math.abs(c - next) < 0.005;
};

export function comparePriceList(entries: PriceListEntry[], variants: ShopVariant[]): PriceCompareResult {
  const byKey = new Map<string, PriceListEntry>();
  for (const e of entries) byKey.set(e.sku.trim().toUpperCase(), e);

  const rows: PriceCompareRow[] = [];
  const onlyInShop: OnlyInShopRow[] = [];
  const matched = new Set<string>();

  for (const v of variants) {
    const sku = (v.sku || '').trim();
    const entry = sku ? byKey.get(sku.toUpperCase()) : undefined;
    if (!entry) {
      onlyInShop.push({ sku, title: v.displayName, productStatus: v.productStatus });
      continue;
    }
    matched.add(sku.toUpperCase());
    const { ve: currentVe, veTags } = veFromTags(v.tags);
    const newVe = entry.ve !== null ? String(entry.ve) : '';
    const priceChanged = !samePrice(v.einkaufspreis, entry.price);
    // VE nur vergleichen, wenn die Liste eine VE enthält; mehrere/uneinheitliche VE-Tags gelten als Abweichung.
    // Ausnahme: Kein VE-Tag und neue VE = 1 gilt als unverändert (VE1 ist der Normalfall und wird nicht getaggt).
    const noTagAndVe1 = veTags.length === 0 && newVe === '1';
    const veChanged = newVe !== '' && !noTagAndVe1 && (currentVe !== newVe || veTags.length !== 1);
    rows.push({
      sku,
      variantId: v.id,
      productId: v.productId,
      title: v.displayName,
      currentPrice: v.einkaufspreis,
      newPrice: String(entry.price),
      currency: v.currency || 'EUR',
      currentSale: v.salePrice || '',
      currentVe,
      newVe,
      currentVeTags: veTags,
      priceChanged,
      veChanged,
      veConflict: false,
      hasAufschlag: v.tags.some((t) => t.trim().toLowerCase() === 'aufschlag'),
      status: priceChanged || veChanged ? 'changed' : 'unchanged',
    });
  }

  // Produkt mit mehreren Varianten, deren Liste unterschiedliche VE verlangt: nicht automatisch änderbar.
  const veByProduct = new Map<string, Set<string>>();
  for (const r of rows) {
    if (!r.newVe) continue;
    if (!veByProduct.has(r.productId)) veByProduct.set(r.productId, new Set());
    veByProduct.get(r.productId)!.add(r.newVe);
  }
  for (const r of rows) {
    if (r.veChanged && (veByProduct.get(r.productId)?.size ?? 0) > 1) {
      r.errorMessage = 'Varianten desselben Produkts haben in der Preisliste unterschiedliche VE – VE bitte manuell prüfen.';
      r.veChanged = false;
      r.veConflict = true;
    }
  }

  const onlyInList: OnlyInListRow[] = entries
    .filter((e) => !matched.has(e.sku.trim().toUpperCase()))
    .map((e) => ({ sku: e.sku, name: e.name, sheet: e.sheet, price: e.price, ve: e.ve }));

  return { rows, onlyInShop, onlyInList };
}

// ---------- Verkaufspreis ----------

export const DEFAULT_SALE_FACTOR = '2.232';

/** Faktor aus Eingabefeld (Komma oder Punkt); null, wenn ungültig. */
export function parseFactor(input: string): number | null {
  const n = Number(String(input).trim().replace(',', '.'));
  return Number.isFinite(n) && n > 0 && n < 1000 ? n : null;
}

/** Verkaufspreis = Einkaufspreis × Faktor, kaufmännisch auf 2 Nachkommastellen gerundet. */
export function computeSalePrice(purchase: number, factor: number): number {
  return Math.round(purchase * factor * 100 + 1e-9) / 100;
}

export interface PriceRowView extends PriceCompareRow {
  newSale: string; // '' wenn der Faktor ungültig ist
  saleChanged: boolean;
}

export function withSalePrice(rows: PriceCompareRow[], factor: number | null): PriceRowView[] {
  return rows.map((r) => {
    if (factor === null) return { ...r, newSale: '', saleChanged: false };
    const ns = computeSalePrice(Number(r.newPrice), factor);
    return { ...r, newSale: ns.toFixed(2), saleChanged: !samePrice(r.currentSale, ns) };
  });
}

// ---------- Richtung der Änderung (für Farben und Vorauswahl) ----------

const num = (v: string): number | null => {
  if (v === '' || v == null) return null;
  const n = Number(String(v).replace(',', '.'));
  return Number.isFinite(n) ? n : null;
};

/** 'up' = neuer Wert höher, 'down' = tiefer, 'same' = gleich. Fehlender aktueller Wert zählt als „höher". */
export function direction(current: string, next: string, missingCurrent: number | null = null): 'up' | 'down' | 'same' {
  const n = num(next);
  if (n === null) return 'same';
  const c = num(current) ?? missingCurrent;
  if (c === null) return 'up';
  if (Math.abs(n - c) < 0.005) return 'same';
  return n > c ? 'up' : 'down';
}