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

// Frida, the Danish Food Composition Database (DTU National Food Institute), version 5.5. The dataset
// (doi 10.11583/DTU.29500682, https://data.dtu.dk) is licensed CC BY 4.0 and delivered as one workbook;
// its Readme sheet asks for clear source information. English names are in the Food sheet; the Danish
// name is kept in the additions. Values come from the long sheet Data_Normalised, one row per food and
// parameter with its source id.
export const fridaSourceName = 'Frida, the Danish Food Composition Database, DTU National Food Institute';
export const fridaAttribution = 'Langwagen M, Jacobsen J, Christensen T, Poulsen A: The Danish Food Composition Database, Frida version 5.5, November 2025, National Food Institute, Technical University of Denmark. https://doi.org/10.11583/DTU.29500682. CC BY 4.0.';
export const fridaLanguage = 'en';

// ParameterID -> [key, unit]. Carbohydrate is "Available carbohydrates" (FD-11 `available`); energy is the
// plain "Energy" pair, not the "labelling" pair. Frida reports added sugar and caffeine.
const parameters: Record<string, [NutrientKey, string]> = {
  '137': ['enerc_kj', 'kJ/100g'], '356': ['enerc_kcal', 'kcal/100 g'], '218': ['procnt_g', 'g/100g'], '141': ['fat_g', 'g/100g'],
  '172': ['chocdf_g', 'g/100g'], '168': ['fibtg_g', 'g/100g'], '245': ['sugar_g', 'g/100g'], '417': ['sugad_g', 'g/100g'],
  '248': ['fasat_g', 'g/100g'], '201': ['na_mg', 'mg/100g'], '115': ['chole_mg', 'mg/100g'], '165': ['k_mg', 'mg/100g'],
  '108': ['ca_mg', 'mg/100g'], '162': ['fe_mg', 'mg/100g'], '126': ['vitd_ug', 'µg/100g'], '19': ['alc_g', 'g/100g'],
  '121': ['caffn_mg', 'mg/100g'], '268': ['water_g', 'g/100g'],
};

// Frida groups infant foods and breast milk (Calsa is for adults, C-23).
const infantGroups = new Set(['6', '135']);

// Source -> FD-07 type, following the "Database structure" section of the Frida documentation: analytical data
// (reports of the Danish analysis projects, reference type R), values borrowed from other databases, books and
// the literature (B, WW, AJ, AB), recipe calculations and estimates (the E sources named in the Source sheet).
// Source 1267 is the nitrogen-to-protein factor report. Personal or internal data (P) is analytical only when
// the source title says so (analysis results, quality control data, a survey or study of products); other P
// sources and unclassified sources stay unknown rather than getting an invented type (FD-07). Energy, available carbohydrate and
// added sugar are calculated parameters in Frida (documentation, 5.1 and 5.2) whatever their source.
const estimateSources: Record<string, ValueType> = {
  '1001': 'imputed', '1002': 'imputed', '1933': 'imputed', '1003': 'calculated', '1004': 'calculated',
  '1005': 'calculated', '1655': 'assumed_zero', '1267': 'calculated',
};
const calculatedParameters = new Set(['137', '356', '172', '417']);
const analysedTitle = /^(Results of analy|Quality control data|Survey of|Study of)/i;
const referenceTypes: Record<string, ValueType> = { R: 'analytic', B: 'borrowed', WW: 'borrowed', AJ: 'borrowed', AB: 'borrowed' };

function table(sheets: ReturnType<typeof readXlsx>, name: string) {
  const sheet = sheets.find(item => item.name === name);
  if (!sheet?.rows[0]) throw new Error(`Frida sheet ${name} is missing`);
  return { header: sheet.rows[0], rows: sheet.rows.slice(1) };
}

export function parseFrida(bytes: Buffer): Parsed {
  const sheets = readXlsx(bytes);
  const col = (t: { header: Cell[] }, title: string) => {
    const at = t.header.indexOf(title);
    if (at < 0) throw new Error(`Frida column ${title} is missing`);
    return at;
  };
  const parameterSheet = table(sheets, 'Parameter');
  const units = new Map(parameterSheet.rows.map(row => [row[col(parameterSheet, 'ParameterID')], row[col(parameterSheet, 'Unit')]]));
  for (const [id, [, unit]] of Object.entries(parameters))
    if (units.get(id) !== unit) throw new Error(`Frida parameter ${id} unit changed`);

  const sourceSheet = table(sheets, 'Source');
  const sourceType = new Map<string, ValueType | undefined>(sourceSheet.rows.map(row => {
    const kind = row[col(sourceSheet, 'EurofirRefType')] ?? '';
    const analysed = kind === 'P' && analysedTitle.test(row[col(sourceSheet, 'TitleEnglish')] ?? '');
    return [row[col(sourceSheet, 'SourceID')]!, analysed ? 'analytic' : referenceTypes[kind]];
  }));
  const groupSheet = table(sheets, 'FoodGroup');
  // The English group name is FoodGroupName up to Frida 5.5 and GroupNameUK from the Danish table 6.1 on.
  const groupName = col(groupSheet, groupSheet.header.includes('GroupNameUK') ? 'GroupNameUK' : 'FoodGroupName');
  const groupNames = new Map(groupSheet.rows.map(row => [row[col(groupSheet, 'FoodGroupID')]!, row[groupName]?.trim() ?? null]));

  const data = table(sheets, 'Data_Normalised');
  const foodAt = col(data, 'FoodID'), parameterAt = col(data, 'ParameterID'), valueAt = col(data, 'ResVal');
  const sourceAt = col(data, 'Source');
  const dataRows = new Map<string, Cell[][]>();
  for (const row of data.rows) {
    const id = row[foodAt];
    if (id) dataRows.set(id, [...(dataRows.get(id) ?? []), row]);
  }

  const foods = table(sheets, 'Food');
  const idAt = col(foods, 'FoodID'), nameAt = col(foods, 'FoodName'), danishAt = col(foods, 'FødevareNavn');
  const groupAt = col(foods, 'FoodGroupID');
  const counts = new Map<string, number>();
  for (const row of foods.rows) if (row[idAt]) counts.set(row[idAt]!, (counts.get(row[idAt]!) ?? 0) + 1);

  const out: Parsed = { drafts: [], skipped: [] };
  for (const row of foods.rows) {
    const id = row[idAt];
    if (!id) { out.skipped.push({ recordId: null, reason: 'missing source identifier' }); continue; }
    if ((counts.get(id) ?? 0) > 1) { out.skipped.push({ recordId: id, reason: 'duplicate source identifier' }); continue; }
    const name = row[nameAt]?.replace(/\s+/g, ' ').trim();
    if (!name) { out.skipped.push({ recordId: id, reason: 'no English name' }); continue; }
    if (infantGroups.has(row[groupAt] ?? '')) { out.skipped.push({ recordId: id, reason: 'baby food (C-23)' }); continue; }
    const rows = dataRows.get(id) ?? [];
    if (!rows.length) { out.skipped.push({ recordId: id, reason: 'no composition rows' }); continue; }

    const values: Draft['values'] = {};
    const kinds: Draft['kinds'] = {};
    for (const item of rows) {
      const known = parameters[item[parameterAt] ?? ''];
      if (!known) continue;
      const value = toNumber(item[valueAt]);
      // A value can name several sources; it is typed only when they all agree.
      const types = new Set((item[sourceAt] ?? '').split(',').map(id => id.trim())
        .map(id => estimateSources[id] ?? sourceType.get(id)));
      const type = calculatedParameters.has(item[parameterAt]!) ? 'calculated' : types.size === 1 ? [...types][0] : undefined;
      if (value === null || value < 0 || !type) continue;
      values[known[0]] = value; kinds[known[0]] = type;
    }

    out.drafts.push({
      recordId: id, name, basis: '100g', carbohydrateDefinition: 'available', values, kinds, measures: [],
      group: storedGroup(fridaGroup(row[groupAt])),
      added: { name_language: fridaLanguage, native_name: row[danishAt] ?? null, native_language: 'da',
        frida_group_id: row[groupAt], frida_group: groupNames.get(row[groupAt] ?? '') ?? null },
      raw: Buffer.from(JSON.stringify({ Food: rowObject(foods.header, row), Data: rows.map(item => rowObject(data.header, item)) })),
    });
  }
  return out;
}

export async function importFridaFile(client: pg.Client, path: string, releasedAt: string) {
  return importDrafts(client, { src: 'SRC-14', market: 'DK', family: 'open' },
    parseFrida(await readFile(path)), releasedAt, fridaSourceName);
}