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 { openZip } from './xlsx.ts';
import { cnfGroup, excludedReason, isExcluded, storedGroup } from '../groups.ts';

// Canadian Nutrient File (SRC-04, Open Government Licence - Canada): the official all-files ZIP
// holds relational CSV files. Foods, nutrient amounts and measure conversions are joined by Food_Code.
export const cnfSourceName = 'Canadian Nutrient File';
const refuseMeasure = '750'; // Measure_Code "total refuse"; its conversion value is a percentage.

const nutrients: Record<string, [NutrientKey, string]> = {
  '208': ['enerc_kcal', 'kilocalorie'], '268': ['enerc_kj', 'kilojoule'], '203': ['procnt_g', 'Gram'],
  '204': ['fat_g', 'Gram'], '205': ['chocdf_g', 'Gram'], '291': ['fibtg_g', 'Gram'], '269': ['sugar_g', 'Gram'],
  '606': ['fasat_g', 'Gram'], '307': ['na_mg', 'Milligram'], '601': ['chole_mg', 'Milligram'],
  '306': ['k_mg', 'Milligram'], '301': ['ca_mg', 'Milligram'], '303': ['fe_mg', 'Milligram'],
  '328': ['vitd_ug', 'Microgram'], '221': ['alc_g', 'Gram'], '262': ['caffn_mg', 'Milligram'], '255': ['water_g', 'Gram'],
};

// Nutrient_Source_Code -> FD-07 type. Codes without a clear match leave the value unknown.
const sourceTypes: Record<string, ValueType> = {
  '0': 'borrowed', '1': 'borrowed', '2': 'calculated', '3': 'analytic', '4': 'calculated', '5': 'imputed',
  '6': 'label', '7': 'analytic', '9': 'label', '10': 'borrowed', '12': 'assumed_zero', '15': 'imputed',
  '16': 'calculated', '17': 'calculated', '50': 'calculated', '51': 'calculated', '81': 'borrowed',
};

// RFC 4180 reader: quoted fields, doubled quotes, CRLF or LF, optional byte order mark.
export function parseCsv(bytes: Buffer): Cell[][] {
  const text = new TextDecoder('utf-8', { fatal: true }).decode(bytes).replace(/^/, '');
  const rows: Cell[][] = [];
  let row: Cell[] = [];
  let field = '';
  let quoted = false;
  const endField = () => { row.push(field === '' ? null : field); field = ''; };
  for (let i = 0; i < text.length; i++) {
    const char = text[i];
    if (quoted) {
      if (char !== '"') field += char;
      else if (text[i + 1] === '"') { field += '"'; i++; }
      else quoted = false;
    } else if (char === '"' && field === '') quoted = true;
    else if (char === ',') endField();
    else if (char === '\n' || char === '\r') {
      if (char === '\r' && text[i + 1] === '\n') i++;
      endField();
      if (row.length > 1 || row[0] !== null) rows.push(row);
      row = [];
    } else field += char;
  }
  if (quoted) throw new Error('CSV ends inside a quoted field');
  if (field !== '' || row.length) { endField(); rows.push(row); }
  return rows;
}

function csv(archive: ReturnType<typeof openZip>, file: string) {
  const name = archive.names.find(item => item.toLowerCase().endsWith(`/${file}`) || item.toLowerCase() === file);
  const bytes = name ? archive.read(name) : null;
  if (!bytes) throw new Error(`CNF file ${file} is missing`);
  const [header, ...rows] = parseCsv(bytes);
  if (!header) throw new Error(`CNF file ${file} is empty`);
  return { header, rows };
}

function group<T>(rows: T[], key: (row: T) => string | null) {
  const map = new Map<string, T[]>();
  for (const row of rows) {
    const id = key(row);
    if (id !== null) map.set(id, [...(map.get(id) ?? []), row]);
  }
  return map;
}

export function parseCnf(bytes: Buffer): Parsed {
  const archive = openZip(bytes);
  const foods = csv(archive, 'food_name.csv');
  const amounts = csv(archive, 'nutrient_amount.csv');
  const nutrientNames = csv(archive, 'nutrient_name.csv');
  const conversions = csv(archive, 'measure_weight_conversion.csv');
  const measureNames = csv(archive, 'measure_name.csv');
  const groups = csv(archive, 'cnf_food_group.csv');
  const column = (file: { header: Cell[] }, title: string) => {
    const at = file.header.indexOf(title);
    if (at < 0) throw new Error(`CNF column ${title} is missing`);
    return at;
  };

  // Units of the nutrients we read are verified against the file, not assumed.
  const unitAt = column(nutrientNames, 'Nutrient_Unit'), codeAt = column(nutrientNames, 'Nutrient_Code');
  for (const row of nutrientNames.rows) {
    const known = row[codeAt] ? nutrients[row[codeAt]!] : undefined;
    if (known && row[unitAt] !== known[1]) throw new Error(`CNF nutrient ${row[codeAt]} unit changed`);
  }
  const groupNames = new Map(groups.rows.map(row => [row[column(groups, 'CNF_Food_Group_Code')]!, row[column(groups, 'CNF_Food_Group_Description_EN')]]));
  const measureName = new Map(measureNames.rows.map(row => [row[column(measureNames, 'Measure_Code')]!, row[column(measureNames, 'Measure_Description_and_Unit_EN')]]));
  const foodAt = column(amounts, 'Food_Code');
  const amountsByFood = group(amounts.rows, row => row[foodAt]);
  const conversionsByFood = group(conversions.rows, row => row[column(conversions, 'Food_Code')]);
  const idAt = column(foods, 'Food_Code'), nameAt = column(foods, 'Food_Description_EN');
  const groupAt = column(foods, 'CNF_Food_Group_Code');
  const nutrientAt = column(amounts, 'Nutrient_Code'), amountAt = column(amounts, 'Nutrient_Amount');
  const sourceAt = column(amounts, 'Nutrient_Source_Code');
  const typeAt = column(conversions, 'Measure_Type_Code'), measureAt = column(conversions, 'Measure_Code');
  const weightAt = column(conversions, 'Measure_Weight_Conversion');
  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];
    const name = row[nameAt]?.trim();
    if (!id || !name) { out.skipped.push({ recordId: id ?? null, reason: 'missing identifier or name' }); continue; }
    if ((counts.get(id) ?? 0) > 1) { out.skipped.push({ recordId: id, reason: 'duplicate source identifier' }); continue; }
    // Calsa is for adults (C-23): infant foods are not part of the base.
    const entry = cnfGroup(row[groupAt]);
    if (isExcluded(entry)) { out.skipped.push({ recordId: id, reason: excludedReason(entry) }); continue; }

    const values: Draft['values'] = {};
    const kinds: Draft['kinds'] = {};
    const seen = new Set<string>();
    const foodAmounts = amountsByFood.get(id) ?? [];
    for (const amount of foodAmounts) {
      const known = nutrients[amount[nutrientAt] ?? ''];
      if (!known) continue;
      const key = known[0];
      // Two rows for one nutrient cannot both be right; neither is used.
      if (seen.has(key)) { delete values[key]; delete kinds[key]; values[key] = null; continue; }
      seen.add(key);
      const value = toNumber(amount[amountAt]);
      const type = sourceTypes[amount[sourceAt] ?? ''];
      if (value === null || value < 0 || !type) continue;
      values[key] = value; kinds[key] = type;
    }
    for (const key of Object.keys(values) as NutrientKey[]) if (values[key] === null) { delete values[key]; delete kinds[key]; }

    fillEnergy(values, kinds);

    const measures: Draft['measures'] = [];
    const added: Record<string, unknown> = { cnf_food_group_code: row[groupAt], cnf_food_group: groupNames.get(row[groupAt] ?? '') ?? null,
      food_source_code: row[column(foods, 'Food_Source_Code')], usda_ndb_code: row[column(foods, 'USDA_NDB_Code')] };
    const foodConversions = conversionsByFood.get(id) ?? [];
    for (const conversion of foodConversions) {
      const weight = toNumber(conversion[weightAt]);
      if (conversion[typeAt] === '3' && conversion[measureAt] === refuseMeasure && weight !== null && weight >= 0 && weight <= 100)
        added.refuse_fraction = weight / 100;
      // Type 6 is a household measure with a weight in grams; type 9 is a cooking yield, not a measure.
      if (conversion[typeAt] === '6' && weight !== null && weight > 0) {
        const label = measureName.get(conversion[measureAt] ?? '');
        if (label) measures.push({ name: label, basis_amount: weight });
      }
    }

    out.drafts.push({
      recordId: id, name, basis: '100g', carbohydrateDefinition: 'total', values, kinds, measures, added, group: storedGroup(entry),
      raw: Buffer.from(JSON.stringify({
        Food_Name: rowObject(foods.header, row),
        Nutrient_Amount: foodAmounts.map(item => rowObject(amounts.header, item)),
        Measure_Weight_Conversion: foodConversions.map(item => rowObject(conversions.header, item)),
      })),
    });
  }
  return out;
}

export async function importCnfFile(client: pg.Client, path: string, releasedAt: string) {
  return importDrafts(client, { src: 'SRC-04', market: 'CA', family: 'open' },
    parseCnf(await readFile(path)), releasedAt, cnfSourceName);
}