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

// FSANZ tables: AFCD Release 3 (SRC-05) and AUSNUT 2023 (SRC-09). Both are licensed on a
// share-alike basis (FD-57): the delivered rows go into the raw record untouched, and everything we
// derive (kcal, carbohydrate choice, density, waste) lives in the version and the added table.
export const afcdSourceName = 'Australian Food Composition Database, Release 3 (FSANZ)';
export const ausnutSourceName = 'AUSNUT 2023 (FSANZ)';

// Record-level "Derivation" -> FD-07 type. FSANZ states it describes the majority of a record's
// data, not each value, so this is the closest honest label; unknown words leave values unknown.
const derivations: Record<string, ValueType> = {
  analysed: 'analytic', recipe: 'calculated', imputed: 'imputed', estimated: 'imputed',
  'label data': 'label', borrowed: 'borrowed',
};

type Layout = {
  src: 'SRC-05' | 'SRC-09';
  detailsSheet: (name: string) => boolean;
  // Header of the details sheet (first cell) and how its columns are named.
  idTitle: string; derivation: string; name: string; gravity: string; edible: string; inedible: string;
  classification: string | number;
  profileSheet: (name: string) => boolean;
  profileId: string;
  nutrients: Record<string, NutrientKey>;
};

const shared = {
  'Available carbohydrate, with sugar alcohols (g)': 'chocdf_g', 'Total sugars (g)': 'sugar_g', 'Added sugars (g)': 'sugad_g',
  'Alcohol (g)': 'alc_g', 'Calcium (Ca) (mg)': 'ca_mg', 'Iron (Fe) (mg)': 'fe_mg', 'Potassium (K) (mg)': 'k_mg',
  'Sodium (Na) (mg)': 'na_mg', 'Vitamin D3 equivalents (ug)': 'vitd_ug', 'Caffeine (mg)': 'caffn_mg',
  'Cholesterol (mg)': 'chole_mg', 'Protein (g)': 'procnt_g',
} as const;
export const afcdLayout: Layout = {
  src: 'SRC-05', detailsSheet: name => name === 'Food details', idTitle: 'Public Food Key', derivation: 'Derivation',
  name: 'Food Name', gravity: 'Specific Gravity', edible: 'Analysed Portion', inedible: 'Unanalysed Portion',
  classification: 'Classification', profileSheet: name => name.startsWith('All solids'), profileId: 'Public Food Key',
  nutrients: { ...shared, 'Energy with dietary fibre, equated (kJ)': 'enerc_kj', 'Moisture (water) (g)': 'water_g',
    'Fat, total (g)': 'fat_g', 'Total dietary fibre (g)': 'fibtg_g', 'Total saturated fatty acids, equated (g)': 'fasat_g' },
};
export const ausnutLayout: Layout = {
  src: 'SRC-09', detailsSheet: name => name === 'Food details', idTitle: 'Survey ID', derivation: 'Derivation',
  name: 'Food name', gravity: 'Specific gravity', edible: 'Edible portion', inedible: 'Inedible portion',
  classification: 13, profileSheet: name => name === 'Food nutrient profiles', profileId: 'Survey ID',
  nutrients: { ...shared, 'Energy with dietary fibre (kJ)': 'enerc_kj', 'Moisture (g)': 'water_g',
    'Total fat (g)': 'fat_g', 'Dietary fibre (g)': 'fibtg_g', 'Total saturated fat (g)': 'fasat_g' },
};

const clean = (cell: Cell) => (cell ?? '').replace(/\s+/g, ' ').trim();

function find(sheets: Sheet[], match: (name: string) => boolean, firstTitle: string) {
  const sheet = sheets.find(item => match(item.name));
  const at = sheet?.rows.findIndex(row => clean(row[0]) === firstTitle) ?? -1;
  if (!sheet || at < 0) throw new Error(`FSANZ sheet with ${firstTitle} column is missing`);
  const header = sheet.rows[at];
  const titles = header.map(clean);
  const col = (title: string) => {
    const index = titles.indexOf(title);
    if (index < 0) throw new Error(`FSANZ column ${title} is missing`);
    return index;
  };
  return { header, col, rows: sheet.rows.slice(at + 1) };
}

// "15% (skin)" -> 0.15; a bare number is already a fraction (AUSNUT writes 1 and 0).
export function portionFraction(cell: Cell): number | null {
  const text = clean(cell);
  const percent = text.match(/^(\d+(?:\.\d+)?)\s*%/);
  if (percent) return Number(percent[1]) / 100;
  const bare = toNumber(text);
  return bare !== null && bare >= 0 && bare <= 1 ? bare : null;
}

function parseFsanz(layout: Layout, detailsBytes: Buffer, profileBytes: Buffer, measureBytes: Buffer | null): Parsed {
  const details = find(readXlsx(detailsBytes), layout.detailsSheet, layout.idTitle);
  const profiles = find(readXlsx(profileBytes), layout.profileSheet, layout.profileId);
  const idAt = details.col(layout.idTitle), nameAt = details.col(layout.name), derivationAt = details.col(layout.derivation);
  const gravityAt = details.col(layout.gravity), edibleAt = details.col(layout.edible), inedibleAt = details.col(layout.inedible);
  const classAt = typeof layout.classification === 'number' ? layout.classification : details.col(layout.classification);
  const nutrientAt = Object.entries(layout.nutrients).map(([title, key]) => [profiles.col(title), key] as const);
  const profileIdAt = profiles.col(layout.profileId);
  const profileRows = new Map(profiles.rows.filter(row => row[profileIdAt]).map(row => [row[profileIdAt]!, row]));

  // AUSNUT measures: one row per household measure, keyed by Survey ID; "density" rows are not measures.
  const measuresById = new Map<string, Cell[][]>();
  let measureHeader: Cell[] = [];
  if (measureBytes) {
    const measures = find(readXlsx(measureBytes), name => name !== 'Contents', 'Survey ID');
    measureHeader = measures.header;
    for (const row of measures.rows) if (row[0]) measuresById.set(row[0], [...(measuresById.get(row[0]) ?? []), row]);
  }

  const counts = new Map<string, number>();
  for (const row of details.rows) if (row[idAt]) counts.set(row[idAt]!, (counts.get(row[idAt]!) ?? 0) + 1);
  const out: Parsed = { drafts: [], skipped: [] };
  for (const row of details.rows) {
    const id = row[idAt]?.trim();
    if (!id) continue;
    if ((counts.get(id) ?? 0) > 1) { out.skipped.push({ recordId: id, reason: 'duplicate source identifier' }); continue; }
    const profile = profileRows.get(id);
    const name = clean(row[nameAt]);
    if (!profile || !name) { out.skipped.push({ recordId: id, reason: 'missing nutrient profile or name' }); continue; }

    const type = derivations[clean(row[derivationAt]).toLowerCase()];
    const values: Draft['values'] = {};
    const kinds: Draft['kinds'] = {};
    for (const [at, key] of nutrientAt) {
      const text = profile[at] ?? null;
      if (text === null) continue;
      const value = toNumber(text);
      if (value === null) throw new Error(`FSANZ ${id}: unexpected value ${text}`);
      if (type) { values[key] = value; kinds[key] = type; }
    }
    fillEnergy(values, kinds); // the tables give kJ only

    const entry = fsanzGroup(clean(row[classAt]));
    if (isExcluded(entry)) { out.skipped.push({ recordId: id, reason: excludedReason(entry) }); continue; }
    const measures: Draft['measures'] = [];
    const added: Record<string, unknown> = { classification_code: clean(row[classAt]) || null };
    const gravity = toNumber(row[gravityAt]);
    if (gravity !== null && gravity > 0) added.density_g_per_ml = gravity;
    const refuse = portionFraction(row[inedibleAt]);
    if (refuse !== null) added.refuse_fraction = refuse;
    const measureRows = measuresById.get(id) ?? [];
    if (measureRows.length) {
      const title = (name: string) => measureHeader.findIndex(cell => clean(cell) === name);
      const quantityAt = title('Quantity'), gramsAt = title('Gram amount'), volumeAt = title('Volume');
      const descriptorAt = [title('Descriptor 1'), title('Descriptor 2'), title('Descriptor 3'), title('Descriptor 4')];
      if (quantityAt < 0 || gramsAt < 0 || volumeAt < 0 || descriptorAt.some(at => at < 0)) throw new Error('FSANZ measures columns are missing');
      for (const measure of measureRows) {
        const descriptors = descriptorAt.map(at => clean(measure[at])).filter(Boolean);
        const grams = toNumber(measure[gramsAt]);
        if (descriptors[0] === 'density' || grams === null || grams <= 0 || !descriptors.length) continue;
        // The volume tells apart packs of one kind, such as the 330 and 375 mL cans.
        const volume = toNumber(measure[volumeAt]);
        const size = volume !== null && volume > 0 ? ` (${Number(volume.toFixed(2))} mL)` : '';
        measures.push({ name: `${clean(measure[quantityAt]) || '1'} ${descriptors.join(', ')}${size}`, basis_amount: grams });
      }
    }

    out.drafts.push({
      recordId: id, name, basis: '100g', carbohydrateDefinition: 'available', values, kinds, measures, added, group: storedGroup(entry),
      raw: Buffer.from(JSON.stringify({
        'Food details': rowObject(details.header, row),
        'Nutrient profile': rowObject(profiles.header, profile),
        ...(measureBytes ? { 'Food measures': measureRows.map(measure => rowObject(measureHeader, measure)) } : {}),
      })),
    });
  }
  return out;
}

export const parseAfcd = (details: Buffer, profiles: Buffer) => parseFsanz(afcdLayout, details, profiles, null);
export const parseAusnut = (details: Buffer, profiles: Buffer, measures: Buffer) =>
  parseFsanz(ausnutLayout, details, profiles, measures);

export async function importAfcdFiles(client: pg.Client, paths: { details: string; profiles: string }, releasedAt: string) {
  return importDrafts(client, { src: 'SRC-05', market: 'AU', family: 'fsanz' },
    parseAfcd(await readFile(paths.details), await readFile(paths.profiles)), releasedAt, afcdSourceName);
}
export async function importAusnutFiles(client: pg.Client,
  paths: { details: string; profiles: string; measures: string }, releasedAt: string) {
  return importDrafts(client, { src: 'SRC-09', market: 'AU', family: 'fsanz' },
    parseAusnut(await readFile(paths.details), await readFile(paths.profiles), await readFile(paths.measures)),
    releasedAt, ausnutSourceName);
}