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

// Bundeslebensmittelschluessel 4.0 (Max Rubner-Institut), Germany. Data page: "Die Nutzung ist unter der Lizenz
// CC BY 4.0 ... gestattet. Bei Verwendung ist das Max Rubner-Institut als Herausgeber zu nennen." The official ZIP
// holds one workbook with, per nutrient, a value, a "Datenherkunft" (origin) and a "Referenz" column.
// Names are English in the "Food name" column; the German name is kept in the additions.
export const blsSourceName = 'Bundeslebensmittelschluessel (BLS) 4.0, Max Rubner-Institut';
export const blsAttribution = 'Max Rubner-Institut (2025): Bundeslebensmittelschluessel (BLS), Version 4.0 - Deutsche Naehrstoffdatenbank. Karlsruhe. DOI: 10.25826/Data20251217-134202-0. CC BY 4.0.';
export const blsLanguage = 'en';

// code -> [key, unit in the column title]. Carbohydrate is the available carbohydrate of EU 1169/2011
// (FD-11 `available`); fibre is BLS "Ballaststoffe, gesamt" under the Codex definition (AOAC 2009.01 family).
const columns: Record<string, [NutrientKey, string]> = {
  ENERCC: ['enerc_kcal', 'kcal/100g'], ENERCJ: ['enerc_kj', 'kJ/100g'], PROT625: ['procnt_g', 'g/100g'], FAT: ['fat_g', 'g/100g'],
  CHO: ['chocdf_g', 'g/100g'], FIBT: ['fibtg_g', 'g/100g'], SUGAR: ['sugar_g', 'g/100g'], FASAT: ['fasat_g', 'g/100g'],
  NA: ['na_mg', 'mg/100g'], CHORL: ['chole_mg', 'mg/100g'], K: ['k_mg', 'mg/100g'], CA: ['ca_mg', 'mg/100g'],
  FE: ['fe_mg', 'mg/100g'], VITD: ['vitd_ug', 'µg/100g'], ALC: ['alc_g', 'g/100g'], WATER: ['water_g', 'g/100g'],
};

// Datenherkunft -> FD-07 type, from the BLS documentation and FAQ. "Aggregation" is the aggregate of several
// literature values, "Uebernommener Wert" a value taken over from another record. "-" means no value.
const origins: Record<string, ValueType> = {
  Analyse: 'analytic', Formelberechnung: 'calculated', Rezeptberechnung: 'calculated', Musterberechnung: 'calculated',
  Reskalierung: 'calculated', Literatur: 'borrowed', Aggregation: 'borrowed', Nährstoffdatenbank: 'borrowed',
  'Übernommener Wert': 'borrowed', 'Logische Null': 'assumed_zero', Spuren: 'assumed_zero', Labelangabe: 'label',
  'Logische Annahme': 'imputed',
};

export function parseBls(zipBytes: Buffer): Parsed {
  const archive = openZip(zipBytes);
  const name = archive.names.find(item => /BLS_4_0_Daten_.*\.xlsx$/.test(item));
  const bytes = name ? archive.read(name) : null;
  if (!bytes) throw new Error('BLS data workbook is missing');
  const sheet = readXlsx(bytes)[0];
  const header = sheet?.rows[0];
  if (!header || header[0] !== 'BLS Code' || header[2] !== 'Food name') throw new Error('BLS header changed');

  const at = new Map<NutrientKey, number>();
  for (const [code, [key, unit]] of Object.entries(columns)) {
    const index = header.findIndex(title => title?.startsWith(`${code} `) && !title.includes('Datenherkunft') && !title.includes('Referenz'));
    if (index < 0 || !header[index]!.endsWith(`[${unit}]`) || header[index + 1] !== `${code} Datenherkunft`)
      throw new Error(`BLS column ${code} is missing or changed`);
    at.set(key, index);
  }

  const out: Parsed = { drafts: [], skipped: [] };
  const seen = new Map<string, number>();
  for (const row of sheet.rows.slice(1)) if (row[0]) seen.set(row[0], (seen.get(row[0]) ?? 0) + 1);
  for (const row of sheet.rows.slice(1)) {
    const id = row[0]?.trim();
    if (!id) continue;
    if ((seen.get(id) ?? 0) > 1) { out.skipped.push({ recordId: id, reason: 'duplicate source identifier' }); continue; }
    const english = row[2]?.replace(/\s+/g, ' ').trim();
    if (!english) { out.skipped.push({ recordId: id, reason: 'no English name' }); continue; }
    if (isInfantFood(english)) { out.skipped.push({ recordId: id, reason: 'baby food (C-23)' }); continue; }

    const values: Draft['values'] = {};
    const kinds: Draft['kinds'] = {};
    for (const [key, index] of at) {
      const origin = row[index + 1] ?? '-';
      const type = origins[origin];
      if (!type) continue;
      const text = row[index];
      // "TR" under origin "Spuren" is a stated trace: 0 typed assumed_zero (FD-08, as for CoFID "Tr").
      const value = text === 'TR' && origin === 'Spuren' ? 0 : toNumber(text);
      if (value === null || value < 0) continue;
      values[key] = value; kinds[key] = origin === 'Spuren' ? 'assumed_zero' : type;
    }

    out.drafts.push({
      recordId: id, name: english, basis: '100g', carbohydrateDefinition: 'available', values, kinds, measures: [],
      group: storedGroup(blsGroup(id)),
      added: { name_language: blsLanguage, native_name: row[1] ?? null, native_language: 'de', bls_group_code: id[0] },
      raw: Buffer.from(JSON.stringify(rowObject(header, row))),
    });
  }
  return out;
}

export async function importBlsFile(client: pg.Client, path: string, releasedAt: string) {
  return importDrafts(client, { src: 'SRC-15', market: 'DE', family: 'open' },
    parseBls(await readFile(path)), releasedAt, blsSourceName);
}