import { readFile } from 'node:fs/promises';
import pg from 'pg';
import { blvGroup, storedGroup } from '../groups.ts';
import { fillEnergy, importDrafts, rowObject, toNumber } from './national.ts';
import type { Cell, Draft, NutrientKey, Parsed, ValueType } from './national.ts';
import { readXlsx } from './xlsx.ts';

// Swiss Food Composition Database (Bundesamt für Lebensmittelsicherheit und Veterinärwesen, BLV), SRC-21.
// Open data (opendata.swiss terms_by); the download page naehrwertdaten.ch/de/downloads/ states: "Das Herunterladen
// ist kostenlos und die Daten dürfen unter Angabe eines Quellenvermerks auch für kommerzielle (z.B. Einbindung in
// Nährwertberechnungssoftware oder Ernährungstagebuch-App) und wissenschaftliche Zwecke verwendet werden."
// The workbook (version 7.1 of 2026-07-01) carries German names; the sheet "Generische Lebensmittel" is read,
// the sheet "Markenprodukte" (brand products without a barcode) is not.
export const blvSourceName = 'Swiss Food Composition Database, Federal Food Safety and Veterinary Office (BLV)';
export const blvAttribution = 'Bundesamt für Lebensmittelsicherheit und Veterinärwesen BLV, Schweizer Nährwertdatenbank, Version 7.1, '
  + 'https://naehrwertdaten.ch';
export const blvLanguage = 'de';

const nutrients: Record<string, NutrientKey> = {
  'Energie, Kilojoule (kJ)': 'enerc_kj', 'Energie, Kilokalorien (kcal)': 'enerc_kcal', 'Protein (g)': 'procnt_g',
  'Fett, total (g)': 'fat_g', 'Kohlenhydrate, verfügbar (g)': 'chocdf_g', 'Nahrungsfasern (g)': 'fibtg_g', 'Zucker (g)': 'sugar_g',
  'Fettsäuren, gesättigt (g)': 'fasat_g', 'Natrium (Na) (mg)': 'na_mg', 'Cholesterin (mg)': 'chole_mg', 'Kalium (K) (mg)': 'k_mg',
  'Calcium (Ca) (mg)': 'ca_mg', 'Eisen (Fe) (mg)': 'fe_mg', 'Vitamin D (Calciferol) (µg)': 'vitd_ug', 'Alkohol (g)': 'alc_g',
  'Wasser (g)': 'water_g',
};

// A value cites its source (sheet "Quellen") and says how it was derived ("Herleitung des Wertes"). The derivation
// names are the table's own: computed from the other values, computed from the ingredients, or estimated. "-" means
// the value was taken from the cited source without further derivation; it is `borrowed` when every cited source is
// a food composition table or database, and stays unknown when the source is a study, a laboratory or a maker
// (the table does not say how those were obtained, FD-07).
const derivations: Record<string, ValueType> = {
  'Automatisierte Berechnung': 'calculated', 'Berechnung anhand von Zutaten': 'calculated', 'Schätzung': 'imputed',
};
const tableSource = /(lebensmittelschlüssel|nährwert-?tabelle|souci|ciqual|banca dati|food composition|composition of foods|food data|database|databank|datenbank|livsmedels|nutrient file|fichier canadien|répertoire général|table de composition|nevo|nutridata|fineli|matvaretabellen|standard tables of food|integrated dataset)/i;
// Laboratory reports made for the database itself and journal papers also say "database"; they are not tables.
const laboratoryReport = /(Analyseresultate|Bestimmung von|Laborbericht|determination of|analysis of|Journal of)/i;
export const blvSourceIsTable = (text: string | undefined) => !!text && tableSource.test(text) && !laboratoryReport.test(text);

const babyName = /\b(Säugling|Baby|Kleinkind|Folgemilch|Anfangsnahrung|Beikost)/i;
const clean = (cell: Cell | undefined) => (cell ?? '').replace(/\s+/g, ' ').trim();

export function parseBlv(bytes: Buffer): Parsed {
  const sheets = readXlsx(bytes);
  const sheet = sheets.find(item => item.name === 'Generische Lebensmittel');
  const sourceSheet = sheets.find(item => item.name === 'Quellen');
  const brands = sheets.find(item => item.name === 'Markenprodukte');
  if (!sheet || !sourceSheet) throw new Error('BLV sheet is missing');
  const headerAt = sheet.rows.findIndex(row => row[0] === 'ID');
  if (headerAt < 0) throw new Error('BLV header row is missing');
  const header = sheet.rows[headerAt];
  const column = (title: string) => {
    const at = header.indexOf(title);
    if (at < 0) throw new Error(`BLV column ${title} is missing`);
    return at;
  };
  const idAt = column('ID'), nameAt = column('Name'), synonymAt = column('Synonyme'), categoryAt = column('Kategorie');
  const unitAt = column('Bezugseinheit'), densityAt = column('Dichte');
  const nutrientAt = Object.entries(nutrients).map(([title, key]) => [column(title), key] as const);
  for (const [at] of nutrientAt) if (header[at + 1] !== 'Herleitung des Wertes' || header[at + 2] !== 'Quelle') throw new Error('BLV value columns changed');
  const sources = new Map(sourceSheet.rows.filter(row => row[0] && row[1]).map(row => [row[0]!, row[1]!]));

  const rows = sheet.rows.slice(headerAt + 1).filter(row => row[idAt]);
  const counts = new Map<string, number>();
  for (const row of rows) counts.set(row[idAt]!, (counts.get(row[idAt]!) ?? 0) + 1);
  const out: Parsed = { drafts: [], skipped: [] };
  for (const row of brands?.rows.slice(3) ?? []) {
    if (row[0]) out.skipped.push({ recordId: row[0], reason: 'brand product sheet, no barcode (generic foods only)' });
  }
  for (const row of rows) {
    const id = row[idAt]!.trim();
    if ((counts.get(row[idAt]!) ?? 0) > 1) { out.skipped.push({ recordId: id, reason: 'duplicate source identifier' }); continue; }
    const name = clean(row[nameAt]);
    if (!name) { out.skipped.push({ recordId: id, reason: 'no name' }); continue; }
    const category = clean(row[categoryAt]);
    if (babyName.test(name)) { out.skipped.push({ recordId: id, reason: 'baby food (C-23)' }); continue; }
    if (category.includes('Supplemente')) { out.skipped.push({ recordId: id, reason: 'dietary supplement (C-26)' }); continue; }
    if (clean(row[unitAt]) !== 'pro 100g essbarer Anteil') { out.skipped.push({ recordId: id, reason: 'not per 100 g edible portion' }); continue; }

    const values: Draft['values'] = {};
    const kinds: Draft['kinds'] = {};
    for (const [at, key] of nutrientAt) {
      const value = toNumber(row[at]?.trim());
      if (value === null || value < 0) continue;
      const derivation = clean(row[at + 1]);
      let type: ValueType | undefined = derivations[derivation];
      if (derivation === '-') {
        const cited = clean(row[at + 2]).split(';').map(item => sources.get(item.trim()));
        if (cited.length && cited.every(blvSourceIsTable)) type = 'borrowed';
      }
      if (!type) continue;
      values[key] = value; kinds[key] = type;
    }
    fillEnergy(values, kinds);
    const density = toNumber(row[densityAt]?.trim());
    out.drafts.push({
      recordId: id, name, basis: '100g', carbohydrateDefinition: 'available', values, kinds, measures: [],
      group: storedGroup(blvGroup(category)),
      added: { name_language: blvLanguage, synonyms: clean(row[synonymAt]) || null, blv_category: category,
        ...(density !== null && density > 0 ? { density_g_per_ml: density } : {}) },
      raw: Buffer.from(JSON.stringify({ Food: rowObject(header, row),
        Sources: [...new Set(nutrientAt.flatMap(([at]) => clean(row[at + 2]).split(';').map(item => item.trim()).filter(Boolean)))]
          .map(item => ({ id: item, text: sources.get(item) ?? null })) })),
    });
  }
  return out;
}

export async function importBlvFile(client: pg.Client, path: string, releasedAt: string) {
  return importDrafts(client, { src: 'SRC-21', market: 'CH', family: 'open' },
    parseBlv(await readFile(path)), releasedAt, blvSourceName);
}