import { readFile } from 'node:fs/promises';
import pg from 'pg';
import { foldFoodText } from '../../../core/food-search-name.ts';
import { excludedReason, isExcluded, storedGroup, usdaGroup } from '../groups.ts';

type JsonObject = Record<string, unknown>;
type Nutrient = { nutrient?: { id?: number; unitName?: string }; amount?: number;
  foodNutrientDerivation?: { code?: string; description?: string;
    foodNutrientSource?: { code?: string } } };
type Food = { fdcId?: number; dataType?: string; description?: string;
  foodCategory?: { description?: string }; wweiaFoodCategory?: { wweiaFoodCategoryDescription?: string };
  foodNutrients?: Nutrient[]; foodPortions?: { amount?: number; gramWeight?: number;
    measureUnit?: { name?: string }; modifier?: string; portionDescription?: string }[] };
type Parsed = { raw: Buffer; food: Food };

// The three FoodData Central JSON downloads share the food and nutrient shape and differ in the
// top-level array name and dataType. Verified on the 2026-04-30 Foundation, 2018-04 SR Legacy
// and 2024-10-31 Survey (FNDDS) downloads.
const layouts = {
  foundation: { array: 'FoundationFoods', dataType: 'Foundation' },
  sr_legacy: { array: 'SRLegacyFoods', dataType: 'SR Legacy' },
  survey: { array: 'SurveyFoods', dataType: 'Survey (FNDDS)' },
} as const;
export type UsdaDataset = keyof typeof layouts;
export const usdaDatasets = Object.keys(layouts) as UsdaDataset[];

// The USDA JSON download has one array of foods. Keep each object's exact UTF-8
// slice for the immutable source record; JSON.stringify would change the source bytes.
export function parseUsdaDownload(bytes: Buffer, dataset: UsdaDataset): { records: Parsed[]; empty: number } {
  const layout = layouts[dataset];
  if (!layout) throw new Error('Unknown USDA dataset');
  const input = new TextDecoder('utf-8', { fatal: true }).decode(bytes);
  const document = JSON.parse(input) as JsonObject;
  const rows = document[layout.array];
  if (!Array.isArray(rows)) throw new Error(`USDA ${layout.array} array is missing`);
  const key = `"${layout.array}"`;
  const keyAt = input.indexOf(key);
  const arrayAt = input.indexOf('[', keyAt + key.length);
  if (keyAt < 0 || arrayAt < 0) throw new Error(`USDA ${layout.array} array is missing`);
  const records: Parsed[] = [];
  let empty = 0;
  let start = arrayAt + 1;
  let quoted = false;
  let escaped = false;
  let depth = 0;
  for (let i = start; i < input.length; i++) {
    const char = input[i];
    if (quoted) {
      if (escaped) escaped = false;
      else if (char === '\\') escaped = true;
      else if (char === '"') quoted = false;
    } else if (char === '"') quoted = true;
    else if (char === '{' || char === '[') depth++;
    else if (char === '}' || char === ']') {
      if (depth === 0 && char === ']') {
        const value = input.slice(start, i).trim();
        if (value) add(value);
        break;
      }
      depth--;
    } else if (char === ',' && depth === 0) {
      add(input.slice(start, i).trim());
      start = i + 1;
    }
    function add(value: string) {
      if (value === 'null') { empty++; return; }
      const food = JSON.parse(value) as Food;
      if (!food || typeof food !== 'object' || Array.isArray(food)) throw new Error('Invalid USDA food row');
      records.push({ raw: Buffer.from(value), food });
    }
  }
  if (records.length + empty !== rows.length) throw new Error('USDA row extraction did not match JSON');
  return { records, empty };
}

const nutrients = {
  enerc_kcal: [2048, 2047, 1008], procnt_g: [1003], fat_g: [1004], chocdf_g: [1005],
  fibtg_g: [1079], sugar_g: [2000], sugad_g: [1235], fasat_g: [1258], na_mg: [1093],
  chole_mg: [1253], k_mg: [1092], ca_mg: [1087], fe_mg: [1089], vitd_ug: [1114],
  alc_g: [1018], caffn_mg: [1057], water_g: [1051],
} as const;
const units: Record<string, string> = {
  enerc_kcal: 'kcal', procnt_g: 'g', fat_g: 'g', chocdf_g: 'g', fibtg_g: 'g',
  sugar_g: 'g', sugad_g: 'g', fasat_g: 'g', na_mg: 'mg', chole_mg: 'mg',
  k_mg: 'mg', ca_mg: 'mg', fe_mg: 'mg', vitd_ug: 'µg', alc_g: 'g',
  caffn_mg: 'mg', water_g: 'g',
};
type NutrientKey = keyof typeof nutrients;
const keys = Object.keys(nutrients) as NutrientKey[];

type ValueType = 'analytic' | 'calculated' | 'borrowed' | 'imputed' | 'label' | 'assumed_zero';
// FD-07: derivation code + USDA source code -> closed value type. Any pair not listed leaves
// the nutrient unknown (MC, ML, JO, O, S and nutrients without a derivation stay unmapped).
const derivationTypes: Record<string, ValueType> = {};
for (const [type, pairs] of Object.entries({
  analytic: 'A:1 AR:1 DA:1 PAK:1 RKA:1 RPA:1 MA:12 AI:13 JA:6',
  calculated: `AS:1 NC:4 NR:4 NP:4 DI:4 FLA:4 FLC:4 FLM:4 RA:4 RC:4 RK:4 RP:4 RF:4 RPI:6 RKI:6
    PIK:4 PIE:4 PAE:4`,
  // Scaled from another form of the same or a similar food.
  imputed: `BD:4 BNA:4 BFAN:4 BFCN:4 BFFN:4 BFFY:4 BFNN:4 BFNY:4 BFPN:4 BFPY:4 BFSN:4 BFSY:4
    BFYN:4 BFYY:4 BFZN:4 BFZY:4 CAAN:4 CAFN:4 CASN:4 CAZN:4`,
  borrowed: 'T:4',
  label: 'LC:8',
  assumed_zero: 'Z:7',
})) for (const pair of pairs.split(/\s+/)) derivationTypes[pair] = type as ValueType;

function derivationType(nutrient: Nutrient, dataset: UsdaDataset): ValueType | null {
  const derivation = nutrient.foodNutrientDerivation;
  // FNDDS carries no derivation: USDA computes every Survey value from ingredient foods.
  if (!derivation) return dataset === 'survey' ? 'calculated' : null;
  const type = derivationTypes[`${derivation.code}:${derivation.foodNutrientSource?.code}`] ?? null;
  return type === 'assumed_zero' && nutrient.amount !== 0 ? null : type;
}

function normalize(food: Food, dataset: UsdaDataset) {
  if (!Number.isSafeInteger(food.fdcId) || (food.fdcId ?? 0) <= 0) throw new Error('Missing USDA identity');
  const values: Record<string, number | null> = {};
  const kinds: Record<string, string> = {};
  for (const key of keys) {
    const selected = nutrients[key].map(id => food.foodNutrients?.find(n => n.nutrient?.id === id))
      .find(n => n && typeof n.amount === 'number' && n.nutrient?.unitName === units[key]
        && derivationType(n, dataset) !== null);
    const amount = selected?.amount;
    values[key] = typeof amount === 'number' && Number.isFinite(amount) && amount >= 0 ? amount : null;
    if (values[key] !== null && selected) kinds[key] = derivationType(selected, dataset)!;
  }
  values.enerc_kj = values.enerc_kcal === null ? null : values.enerc_kcal * 4.184;
  if (values.enerc_kj !== null) kinds.enerc_kj = 'calculated';
  // FD-13 keeps an otherwise usable food when an optional nutrient is implausible.
  if (values.sugad_g !== null && values.sugar_g !== null && values.sugad_g > values.sugar_g) {
    values.sugad_g = null; delete kinds.sugad_g;
  }
  if (values.fasat_g !== null && values.fat_g !== null && values.fasat_g > values.fat_g) {
    values.fasat_g = null; delete kinds.fasat_g;
  }
  if (values.na_mg !== null && values.na_mg > 39300) {
    values.na_mg = null; delete kinds.na_mg;
  }
  const name = food.description?.trim() || `Unknown FDC ${food.fdcId}`;
  const state = /\braw\b/i.test(name) ? 'raw' : /\b(cooked|boiled|steamed|baked|roasted|grilled|fried)\b/i.test(name) ? 'cooked' : 'as_sold';
  const preparation = (name.match(/\b(boiled|steamed|baked|roasted|grilled|fried|microwaved)\b/i)?.[1] ?? 'none').toLowerCase();
  // Foundation names a unit; SR Legacy and Survey often have the unit 'undetermined' and put
  // the wording in the modifier (SR Legacy) or the portion description (Survey, where the
  // modifier is a numeric code).
  const measures = (food.foodPortions ?? []).filter(p => Number.isFinite(p.gramWeight) && (p.gramWeight ?? 0) > 0)
    .map(p => {
      const unit = p.measureUnit?.name && p.measureUnit.name !== 'undetermined' ? p.measureUnit.name : null;
      const label = unit ? `${p.amount ?? 1} ${unit}${p.modifier ? `, ${p.modifier}` : ''}`
        : p.portionDescription || (p.modifier ? `${p.amount ?? 1} ${p.modifier}` : `${p.amount ?? 1} portion`);
      return { name: label, basis_amount: p.gramWeight! };
    });
  const complete = ['enerc_kcal', 'procnt_g', 'fat_g', 'chocdf_g'].every(key => values[key] !== null);
  const p = values.procnt_g ?? 0, f = values.fat_g ?? 0, c = values.chocdf_g ?? 0;
  const energy = values.enerc_kcal ?? 0;
  const expected = 4 * p + 9 * f + 4 * c + 7 * (values.alc_g ?? 0);
  const invalid = food.dataType !== layouts[dataset].dataType || !food.description?.trim()
    || Object.values(values).some(value => value !== null && value < 0)
    || p + f + c + (values.water_g ?? 0) + (values.alc_g ?? 0) > 102
    || (values.sugar_g !== null && values.sugar_g > c + 0.5)
    || (values.fibtg_g !== null && values.fibtg_g > c)
    || (complete && Math.abs(energy - expected) > 15 && Math.abs(energy - expected) > expected * 0.2);
  const searchName = foldFoodText(name)
    .replace(/\s+/g, ' ').trim();
  return { name, search_name: searchName, state, preparation,
    food_status: complete && !invalid ? 'active' : 'quarantine', values, kinds, measures };
}

export async function importUsda(client: pg.Client, bytes: Buffer, releasedAt: string, dataset: UsdaDataset) {
  if (!/^\d{4}-(04|10)-\d{2}$/.test(releasedAt) || Number.isNaN(Date.parse(releasedAt)))
    throw new Error('USDA release date must be YYYY-04-DD or YYYY-10-DD');
  const release = releasedAt.slice(0, 7);
  const parsed = parseUsdaDownload(bytes, dataset);
  const count = { rows: parsed.records.length, empty: parsed.empty, newRaw: 0, newVersions: 0, quarantine: 0,
    unmappedGroup: 0, skipped: {} as Record<string, number> };
  await client.query('begin');
  try {
    let releaseId: string | null = null;
    const seen = new Set<number>();
    for (const { raw, food } of parsed.records) {
      if (food.fdcId && seen.has(food.fdcId)) throw new Error(`Duplicate FDC ID ${food.fdcId}`);
      if (food.fdcId) seen.add(food.fdcId);
      // A row without an FDC identifier cannot satisfy the source-record key.
      if (!Number.isSafeInteger(food.fdcId) || (food.fdcId ?? 0) <= 0) { count.quarantine++; continue; }
      // Calsa is for adults and sells no supplements (C-23, C-26): such records are not imported.
      const entry = usdaGroup(dataset, food);
      if (isExcluded(entry)) { const reason = excludedReason(entry); count.skipped[reason] = (count.skipped[reason] ?? 0) + 1; continue; }
      const id = String(food.fdcId);
      const source = await client.query(`insert into food.food_source_record
        (src,record_id,raw_record,source_release,source_released_at)
        values('SRC-01',$1,$2,$3,$4)
        on conflict (src,record_id,content_sha256) do nothing returning id`, [id, raw, release, releasedAt]);
      if (source.rowCount) count.newRaw++;
      const sourceId = source.rows[0]?.id ?? (await client.query(`select id from food.food_source_record
        where src='SRC-01' and record_id=$1 and content_sha256=encode(sha256($2::bytea),'hex')`, [id, raw])).rows[0].id;
      const previous = (await client.query(`select v.*, f.id as existing_food_id from food.food_version v
        join food.food f on f.id=v.food_id join food.food_source_record s on s.id=v.source_record_id
        join food.food_release r on r.id=v.food_release_id
        where s.src='SRC-01' and s.record_id=$1 order by r.version desc limit 1`, [id])).rows[0];
      let normalized: ReturnType<typeof normalize>;
      try { normalized = normalize(food, dataset); } catch { count.quarantine++; continue; }
      // FD-09, FD-43: a record whose source category is not decided has no group and is quarantined.
      const group = storedGroup(entry);
      const unmapped = group === null && normalized.food_status === 'active';
      if (unmapped) { normalized.food_status = 'quarantine'; count.unmappedGroup++; }
      const priorMeasures = previous ? (await client.query(`select name,basis_amount from food.food_measure
        where licence_family='open' and food_version_id=$1 order by name,basis_amount`, [previous.id])).rows : [];
      const same = previous && previous.name === normalized.name && previous.search_name === normalized.search_name
        && previous.state === normalized.state && (previous.food_group ?? null) === group
        && previous.preparation === normalized.preparation && previous.food_status === normalized.food_status
        && keys.concat(['enerc_kj' as NutrientKey]).every(key => previous[key] === normalized.values[key])
        && JSON.stringify(priorMeasures) === JSON.stringify([...normalized.measures].sort((a,b) => a.name.localeCompare(b.name) || a.basis_amount-b.basis_amount));
      if (same) continue;
      if (!releaseId) releaseId = (await client.query('insert into food.food_release default values returning id')).rows[0].id;
      const foodId = previous?.existing_food_id ?? (await client.query("insert into food.food(market,layer) values('US','common') returning id")).rows[0].id;
      const provenance = Object.fromEntries(Object.entries(normalized.kinds).map(([key, type]) =>
        [key, { src: 'SRC-01', record_id: id, release, type }]));
      const columns = [...keys, 'enerc_kj'];
      const params = [foodId, releaseId, sourceId, normalized.name, normalized.search_name,
        release, releasedAt, normalized.food_status, normalized.state, normalized.preparation,
        ...columns.map(key => normalized.values[key]), provenance, group];
      const slots = params.map((_, i) => `$${i + 1}`);
      const version = (await client.query(`insert into food.food_version
        (licence_family,food_id,food_release_id,source_record_id,name,search_name,source_name,
          source_release,source_released_at,food_status,state,preparation,basis,carbohydrate_definition,
          allergens,${columns.join(',')},value_provenance,food_group)
        values('open',${slots.slice(0,5).join(',')},'USDA FoodData Central',
          ${slots.slice(5,10).join(',')},'100g','total','{}',${slots.slice(10).join(',')}) returning id`, params)).rows[0];
      for (const measure of normalized.measures) await client.query(`insert into food.food_measure
        (licence_family,food_version_id,name,basis_amount) values('open',$1,$2,$3)`,
      [version.id, measure.name, measure.basis_amount]);
      count.newVersions++;
      if (normalized.food_status === 'quarantine') count.quarantine++;
    }
    await client.query('commit');
    return { ...count, releaseId };
  } catch (error) { await client.query('rollback'); throw error; }
}

export async function importUsdaFile(client: pg.Client, path: string, releasedAt: string, dataset: UsdaDataset) {
  return importUsda(client, await readFile(path), releasedAt, dataset);
}