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

// McCance and Widdowson's CoFID (SRC-03, Open Government Licence v3.0): one workbook, one sheet per
// nutrient family. Each sheet has a header row, a row of INFOODS tags and a row of names; the
// data starts on the fourth row. Only the sheets that carry the FD-10 nutrients are read.
export const cofidSourceName = "McCance and Widdowson's Composition of Foods Integrated Dataset (CoFID)";
const sheetNames = { factors: '1.2 Factors', proximates: '1.3 Proximates', inorganics: '1.4 Inorganics', vitamins: '1.5 Vitamins' };
const tags: Record<string, NutrientKey> = {
  KCALS: 'enerc_kcal', KJ: 'enerc_kj', PROT: 'procnt_g', FAT: 'fat_g', AOACFIB: 'fibtg_g', TOTSUG: 'sugar_g',
  SATFOD: 'fasat_g', ALCO: 'alc_g', WATER: 'water_g', CHOL: 'chole_mg', NA: 'na_mg', K: 'k_mg', CA: 'ca_mg',
  FE: 'fe_mg', VITD: 'vitd_ug',
};

type Table = { header: Cell[]; tags: Cell[]; rows: Cell[][] };
function table(sheets: Sheet[], name: string): Table {
  const sheet = sheets.find(item => item.name === name);
  if (!sheet) throw new Error(`CoFID sheet ${name} is missing`);
  const at = sheet.rows.findIndex(row => row[1] === 'Food Name');
  if (at < 0 || !sheet.rows[at + 1]) throw new Error(`CoFID sheet ${name} has no header`);
  return { header: sheet.rows[at], tags: sheet.rows[at + 1], rows: sheet.rows.slice(at + 3) };
}

// CoFID gives no type per value. The type follows how the record's Description says it was made;
// a mix of sources is left unclassified, and an unclassified record keeps its values unknown
// rather than getting an invented type (FD-07).
export function cofidValueType(description: Cell): ValueType | null {
  const text = description ?? '';
  const literature = /literature/i.test(text);
  const analysed = /\bsamples?\b|\b(?:cans|jars|brands|products)\b|\b(?:mean|average) of\b|\banaly[sz]/i.test(text);
  if (literature && analysed) return null;
  if (/\brecipe\b|\bcalculat|^\s*based on\b/i.test(text)) return 'calculated';
  if (/\blabel\b/i.test(text)) return 'label';
  if (literature) return 'borrowed';
  return analysed ? 'analytic' : null;
}

export function parseCofid(bytes: Buffer): Parsed {
  const sheets = readXlsx(bytes);
  const tables = Object.fromEntries(Object.entries(sheetNames).map(([key, name]) => [key, table(sheets, name)])) as
    Record<keyof typeof sheetNames, Table>;
  const factorIndex = (title: string) => tables.factors.header.findIndex(cell => cell?.trim() === title);
  const edibleAt = factorIndex('Edible proportion');
  const gravityAt = factorIndex('Specific gravity');
  if (edibleAt < 0 || gravityAt < 0) throw new Error('CoFID factors columns are missing');
  const byCode = (t: Table) => new Map(t.rows.filter(row => row[0]?.trim()).map(row => [row[0]!, row]));
  const factorRows = byCode(tables.factors), inorganics = byCode(tables.inorganics), vitamins = byCode(tables.vitamins);
  const counts = new Map<string, number>();
  for (const row of tables.proximates.rows) if (row[0]) counts.set(row[0], (counts.get(row[0]) ?? 0) + 1);

  const out: Parsed = { drafts: [], skipped: [] };
  for (const row of tables.proximates.rows) {
    const code = row[0]?.trim();
    if (!code) continue;
    // One code is used for two different foods in the 2021 workbook; neither can be trusted.
    if ((counts.get(code) ?? 0) > 1) { out.skipped.push({ recordId: code, reason: 'duplicate source identifier' }); continue; }
    const factor = factorRows.get(code), inorganic = inorganics.get(code), vitamin = vitamins.get(code);
    if (!factor || !inorganic || !vitamin || !row[1]?.trim()) { out.skipped.push({ recordId: code, reason: 'missing sheet row or name' }); continue; }

    const recordType = cofidValueType(row[2]);
    const values: Draft['values'] = {};
    const kinds: Draft['kinds'] = {};
    const read = (source: Table, cells: Cell[]) => {
      source.tags.forEach((tag, index) => {
        const key = tag ? tags[tag] : undefined;
        if (!key) return;
        const text = cells[index] ?? null;
        if (text === null || text === 'N') return;
        if (text === 'Tr') { values[key] = 0; kinds[key] = 'assumed_zero'; return; }
        const value = toNumber(text);
        if (value === null) throw new Error(`CoFID ${code}: unexpected value ${text}`);
        if (recordType) { values[key] = value; kinds[key] = recordType; }
      });
    };
    read(tables.proximates, row); read(tables.inorganics, inorganic); read(tables.vitamins, vitamin);

    // Carbohydrate is in monosaccharide equivalents; FD-11 converts it to mass (starch / 1.1,
    // sugars / 1.05, oligosaccharides as starch). Trace counts as nothing, "N" or a blank blocks it.
    const at = (tag: string) => tables.proximates.tags.indexOf(tag);
    const part = (tag: string): number | null => {
      const text = row[at(tag)] ?? null;
      return text === 'Tr' ? 0 : toNumber(text);
    };
    const carbText = row[at('CHO')] ?? null;
    let definition: Draft['carbohydrateDefinition'] = 'available_mono';
    if (carbText === 'Tr') { values.chocdf_g = 0; kinds.chocdf_g = 'assumed_zero'; definition = 'available'; }
    else if (toNumber(carbText) !== null && recordType) {
      const starch = part('STAR'), sugars = part('TOTSUG');
      const oligo = row[at('OLIGO')] === null ? 0 : part('OLIGO');
      if (starch !== null && sugars !== null && oligo !== null) {
        values.chocdf_g = starch / 1.1 + sugars / 1.05 + oligo / 1.1;
        kinds.chocdf_g = 'calculated';
        // Total sugars are in the same equivalents; they are converted too so they stay within carbohydrate.
        values.sugar_g = sugars / 1.05;
        kinds.sugar_g = 'calculated';
        definition = 'available';
      } else { values.chocdf_g = toNumber(carbText); kinds.chocdf_g = recordType; }
    }

    fillEnergy(values, kinds);

    // Alcoholic beverages (group Q) are per 100 ml; with a density they become per 100 g (FD-06).
    const group = row[3]?.trim() ?? '';
    const gravity = toNumber(factor[gravityAt] ?? null);
    let basis: Draft['basis'] = '100g';
    if (group.startsWith('Q')) {
      if (gravity !== null && gravity > 0) {
        for (const key of Object.keys(values) as NutrientKey[]) values[key] = (values[key] ?? 0) / gravity;
      } else basis = '100ml';
    }
    const edible = toNumber(factor[edibleAt] ?? null);
    const added: Record<string, unknown> = { cofid_group: group };
    if (edible !== null && edible >= 0 && edible <= 1) added.refuse_fraction = Math.round((1 - edible) * 1e6) / 1e6;
    if (gravity !== null && gravity > 0) added.density_g_per_ml = gravity;
    if (group.startsWith('Q') && gravity !== null && gravity > 0) added.converted_from = 'per 100 ml';

    out.drafts.push({
      recordId: code, name: row[1].trim(), basis, carbohydrateDefinition: definition, values, kinds, measures: [], added,
      group: storedGroup(cofidGroup(group)),
      raw: Buffer.from(JSON.stringify(Object.fromEntries([
        [sheetNames.factors, rowObject(tables.factors.header, factor)],
        [sheetNames.proximates, rowObject(tables.proximates.header, row)],
        [sheetNames.inorganics, rowObject(tables.inorganics.header, inorganic)],
        [sheetNames.vitamins, rowObject(tables.vitamins.header, vitamin)],
      ]))),
    });
  }
  return out;
}

export async function importCofidFile(client: pg.Client, path: string, releasedAt: string) {
  return importDrafts(client, { src: 'SRC-03', market: 'GB', family: 'open' },
    parseCofid(await readFile(path)), releasedAt, cofidSourceName);
}