import { readFile } from 'node:fs/promises';
import pg from 'pg';
import { mextGroup, 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';

// Standard Tables of Food Composition in Japan, 2020 (Eighth Revised Edition), Supplement 2023 (MEXT), SRC-22.
// The data workbook is "Chapter 2 (data)" of https://www.mext.go.jp/a_menu/syokuhinseibun/mext_00001.html (the
// version updated 2026-03-27). The MEXT website usage policy (https://www.mext.go.jp/en/other/title01/detail01/1375290.htm)
// says the content "may be freely used, copied, publicly transmitted, translated or otherwise modified on condition
// that the user complies with provisions 1) to 7)", adds "Commercial use of Content is also permitted", requires the
// source and, for edited content, a statement that it was edited, and is "compatible with the Creative Commons
// Attribution License 4.0". Names are Japanese only; they are kept as delivered.
export const mextSourceName = 'Standard Tables of Food Composition in Japan 2020 (Eighth Revised Edition), Supplement 2023, MEXT';
export const mextAttribution = 'Created by Calsa based on "Standard Tables of Food Composition in Japan (Eighth Revised Edition) Supplement 2023" '
  + '(MEXT) (https://www.mext.go.jp/a_menu/syokuhinseibun/mext_00001.html)';
export const mextLanguage = 'ja';

// Component identifier (row "成分識別子") -> key. Carbohydrate is "炭水化物" (CHOCDF-), the carbohydrate by difference
// that includes fibre (FD-11 `total`). It is given for every food and sums with the table's own protein and fat; the
// available-carbohydrate columns are built on the amino-acid protein and triacylglycerol fat and are missing or
// parenthesised for most foods.
const nutrients: Record<string, NutrientKey> = {
  ENERC: 'enerc_kj', ENERC_KCAL: 'enerc_kcal', WATER: 'water_g', 'PROT-': 'procnt_g', 'FAT-': 'fat_g', 'CHOCDF-': 'chocdf_g',
  'FIB-': 'fibtg_g', NA: 'na_mg', K: 'k_mg', CA: 'ca_mg', FE: 'fe_mg', VITD: 'vitd_ug', ALC: 'alc_g', CHOLE: 'chole_mg',
};

// Value notation (the book, "数値の表示方法"): "-" not measured; "0" below a tenth of the smallest printed amount or
// not detected; "Tr" trace; "(0)" and "(Tr)" an estimated zero or trace, "(n)" a value that was not analysed but
// estimated, borrowed or calculated from similar foods or a recipe. The book describes a plain number as an
// analysed or literature value. Energy is computed by the table from its component values (FAO/INFOODS method).
export function mextValue(cell: Cell | undefined, key: NutrientKey): { value: number; type: ValueType } | null {
  const text = (cell ?? '').trim();
  if (key === 'enerc_kj' || key === 'enerc_kcal') {
    const value = toNumber(text);
    return value === null ? null : { value, type: 'calculated' };
  }
  if (text === 'Tr' || text === '(Tr)' || text === '(0)') return { value: 0, type: 'assumed_zero' };
  const estimated = /^\((.+)\)$/.exec(text);
  const value = toNumber(estimated ? estimated[1] : text);
  if (value === null || value < 0) return null;
  if (key === 'chocdf_g') return { value, type: 'calculated' };
  return { value, type: estimated ? 'imputed' : 'analytic' };
}

const infantName = /乳児|人乳/;
const clean = (cell: Cell | undefined) => (cell ?? '').replace(/[ \t\r\n]+/g, ' ').trim();

export function parseMext(bytes: Buffer): Parsed {
  const sheet = readXlsx(bytes)[0];
  const tagsAt = sheet?.rows.findIndex(row => row[3]?.trim() === '成分識別子') ?? -1;
  if (!sheet || tagsAt < 1) throw new Error('MEXT tag row is missing');
  const tags = sheet.rows[tagsAt].map(cell => cell?.trim() ?? null);
  const at = (tag: string) => {
    const index = tags.indexOf(tag);
    if (index < 0) throw new Error(`MEXT column ${tag} is missing`);
    return index;
  };
  const units = sheet.rows[tagsAt - 1];
  if (units[at('ENERC')]?.trim() !== 'kJ' || units[at('ENERC_KCAL')]?.trim() !== 'kcal') throw new Error('MEXT energy units changed');
  const nutrientAt = Object.entries(nutrients).map(([tag, key]) => [at(tag), key] as const);
  const header = tags.map((tag, index) => tag ?? `#${index}`);
  const rows = sheet.rows.slice(tagsAt + 1).filter(row => /^\d{5}$/.test(row[1] ?? ''));
  const counts = new Map<string, number>();
  for (const row of rows) counts.set(row[1]!, (counts.get(row[1]!) ?? 0) + 1);

  const out: Parsed = { drafts: [], skipped: [] };
  for (const row of rows) {
    const id = row[1]!;
    if (counts.get(id)! > 1) { out.skipped.push({ recordId: id, reason: 'duplicate source identifier' }); continue; }
    const name = clean(row[3]);
    if (!name) { out.skipped.push({ recordId: id, reason: 'no name' }); continue; }
    if (infantName.test(name)) { out.skipped.push({ recordId: id, reason: 'baby food (C-23)' }); continue; }
    const values: Draft['values'] = {};
    const kinds: Draft['kinds'] = {};
    for (const [index, key] of nutrientAt) {
      const parsed = mextValue(row[index], key);
      if (parsed) { values[key] = parsed.value; kinds[key] = parsed.type; }
    }
    out.drafts.push({
      recordId: id, name, basis: '100g', carbohydrateDefinition: 'total', values, kinds, measures: [],
      group: storedGroup(mextGroup(row[0], name)),
      added: { name_language: mextLanguage, mext_group: row[0] ?? null, mext_index: row[2] ?? null,
        refuse_percent: toNumber(row[at('REFUSE')]) },
      raw: Buffer.from(JSON.stringify({ Food: rowObject(header, row.slice(0, tags.length)) })),
    });
  }
  return out;
}

export async function importMextFile(client: pg.Client, path: string, releasedAt: string) {
  return importDrafts(client, { src: 'SRC-22', market: 'JP', family: 'open' },
    parseMext(await readFile(path)), releasedAt, mextSourceName);
}