import pg from 'pg';
import { foldFoodText } from '../../../core/food-search-name.ts';
import { UNMAPPED_REASON } from '../groups.ts';
import { assignedGroup } from './assigned-groups.ts';
import type { FoodGroup } from '../groups.ts';

// Shared core of the draft importers for national food tables (CoFID, CNF, AFCD, AUSNUT, Ciqual).
// The source-record and version insert follows the USDA importer; it is copied here instead of
// imported because that file is being generalised separately. Dedupe once it settles.
export const nutrientKeys = [
  'enerc_kcal', 'enerc_kj', 'procnt_g', 'fat_g', 'chocdf_g', 'fibtg_g', 'sugar_g', 'sugad_g',
  'fasat_g', 'na_mg', 'chole_mg', 'k_mg', 'ca_mg', 'fe_mg', 'vitd_ug', 'alc_g', 'caffn_mg', 'water_g',
] as const;
export type NutrientKey = typeof nutrientKeys[number];
export type ValueType = 'analytic' | 'calculated' | 'borrowed' | 'imputed' | 'label' | 'label_per_serving' | 'assumed_zero';
// The four marketing countries and the European tables are listed; any other ISO 3166-1 alpha-2 code is a market too.
export type Market = 'US' | 'GB' | 'CA' | 'AU' | 'SE' | 'NO' | 'DK' | 'DE' | (string & {});
export type Cell = string | null;

export type Draft = {
  recordId: string;
  raw: Buffer;
  name: string;
  // 'one_piece' (FD-30): a chain dish with no published weight; values are per piece.
  basis: '100g' | '100ml' | 'one_piece';
  carbohydrateDefinition: 'total' | 'available' | 'available_mono';
  values: Partial<Record<NutrientKey, number | null>>;
  kinds: Partial<Record<NutrientKey, ValueType>>;
  measures: { name: string; basis_amount: number }[];
  // Calsa food group from the source's own category (../groups.ts); null keeps the record out of search.
  group?: FoodGroup | null;
  // Our own additions to the delivered record (FD-57): kept apart from the raw bytes.
  added: Record<string, unknown>;
  // Branded sources only: the code linked to this food (14 digits, FD-14), set on the one record
  // that owns it (FD-42).
  gtin?: string;
  // Chain dishes only (SRC-07, FD-28): the chain and the publication the dish was read from.
  chain?: { name: string; uri: string; checkedAt: string };
  // FD-13 for per-serving label values: kcal per basis that label rounding alone can explain.
  energyRounding?: number;
  // FD-13 for per-serving label values: grams per 100 that rounding of one label figure alone can explain.
  gramRounding?: number;
  // A fact the source parser already decided (FD-43): the record is stored as quarantine.
  forceQuarantine?: string;
};
export type Skipped = { recordId: string | null; reason: string };
export type Parsed = { drafts: Draft[]; skipped: Skipped[] };
// Large sources stream their drafts; `skipped` is complete once the drafts are consumed.
export type DraftSource = { drafts: Iterable<Draft> | AsyncIterable<Draft>; skipped: Skipped[] };

export type Target = {
  src: `SRC-${number}`;
  market: Market | (string & {});
  family: 'open' | 'fsanz';
  layer?: 'common' | 'branded' | 'chain';
};

const numberPattern = /^-?\d+(\.\d+)?([eE][-+]?\d+)?$/;
export function toNumber(text: Cell | undefined): number | null {
  if (text === null || text === undefined || !numberPattern.test(text.trim())) return null;
  const value = Number(text);
  return Number.isFinite(value) ? value : null;
}

// Delivered cells of one row as an object keyed by header; empty cells are left out so the
// bytes stay small, and a blank or repeated header is keyed by its column position.
export function rowObject(header: Cell[], row: Cell[]): Record<string, string> {
  const out: Record<string, string> = {};
  header.forEach((title, index) => {
    const value = row[index];
    if (value === null || value === undefined) return;
    const clean = title?.replace(/\s+/g, ' ').trim();
    out[clean && !(clean in out) ? clean : `#${index}`] = value;
  });
  return out;
}

// FD-12: a missing energy unit is derived from the other by 4.184 and typed "calculated".
export function fillEnergy(values: Draft['values'], kinds: Draft['kinds']) {
  const kcal = values.enerc_kcal ?? null, kj = values.enerc_kj ?? null;
  if (kcal === null && kj !== null) { values.enerc_kcal = kj / 4.184; kinds.enerc_kcal = 'calculated'; }
  if (kj === null && kcal !== null) { values.enerc_kj = kcal * 4.184; kinds.enerc_kj = 'calculated'; }
}

export function normalizeSearch(name: string): string {
  return foldFoodText(name).replace(/\s+/g, ' ').trim();
}

export function inferState(name: string) {
  const state = /\braw\b/i.test(name) ? 'raw'
    : /\b(cooked|boiled|steamed|baked|roasted|grilled|fried|stewed|braised)\b/i.test(name) ? 'cooked' : 'as_sold';
  const method = name.match(/\b(deep[- ]fried|boiled|steamed|baked|roasted|grilled|fried|microwaved)\b/i)?.[1]
    .toLowerCase().replace(/[- ]/, '_');
  return { state, preparation: method ?? 'none' };
}

const factors: Record<string, (v: Record<string, number>) => { unit: 'kcal' | 'kj'; expected: number }> = {
  // FD-13: energy checked against the factors of the table's own market.
  US: (v: Record<string, number>) => ({ unit: 'kcal' as const, expected: 4 * v.p + 9 * v.f + 4 * v.c + 7 * v.alc }),
  CA: (v: Record<string, number>) => ({ unit: 'kcal' as const, expected: 4 * v.p + 9 * v.f + 4 * v.c + 7 * v.alc }),
  GB: (v: Record<string, number>) => ({ unit: 'kcal' as const, expected: 4 * v.p + 9 * v.f + 4 * v.c + 2 * v.fib + 7 * v.alc }),
  // Tables built on the plain 4-9-4 factors (carbohydrate by difference or available, fibre not added).
  TW: (v: Record<string, number>) => ({ unit: 'kcal' as const, expected: 4 * v.p + 9 * v.f + 4 * v.c + 7 * v.alc }),
  // Carbohydrate by difference includes fibre, which the table counts at 2 kcal/g instead of 4.
  JP: (v: Record<string, number>) => ({ unit: 'kcal' as const, expected: 4 * v.p + 9 * v.f + 4 * v.c - 2 * v.fib + 7 * v.alc }),
  BR: (v: Record<string, number>) => ({ unit: 'kcal' as const, expected: 4 * v.p + 9 * v.f + 4 * v.c + 7 * v.alc }),
  AU: (v: Record<string, number>) => ({ unit: 'kj' as const, expected: 17 * v.p + 37 * v.f + 17 * v.c + 8 * v.fib + 29 * v.alc }),
};

export function assess(draft: Draft, market: string) {
  const values = { ...draft.values };
  const kinds = { ...draft.kinds };
  const drop = (key: NutrientKey) => { values[key] = null; delete kinds[key]; };
  // FD-13 keeps an otherwise usable food when an optional nutrient is implausible.
  if ((values.sugad_g ?? null) !== null && (values.sugar_g ?? null) !== null && values.sugad_g! > values.sugar_g!) drop('sugad_g');
  if ((values.fasat_g ?? null) !== null && (values.fat_g ?? null) !== null && values.fasat_g! > values.fat_g!) drop('fasat_g');
  if ((values.na_mg ?? null) !== null && values.na_mg! > 39300) drop('na_mg');
  const get = (key: NutrientKey) => values[key] ?? null;
  if (draft.forceQuarantine) return { values, kinds, status: 'quarantine' as const, reason: draft.forceQuarantine, detail: '' };
  const complete = (['enerc_kcal', 'procnt_g', 'fat_g', 'chocdf_g'] as const).every(key => get(key) !== null);
  if (!complete) return { values, kinds, status: 'quarantine' as const, reason: 'incomplete', detail: '' };
  const total = draft.carbohydrateDefinition === 'total';
  const parts = { p: get('procnt_g') ?? 0, f: get('fat_g') ?? 0, c: get('chocdf_g') ?? 0,
    alc: get('alc_g') ?? 0, fib: get('fibtg_g') ?? 0 };
  const { unit, expected } = (factors[market] ?? factors.GB)(parts);
  const declared = unit === 'kcal' ? get('enerc_kcal')! : get('enerc_kj') ?? get('enerc_kcal')! * 4.184;
  const tolerance = unit === 'kcal' ? 15 : 15 * 4.184;
  const mass = draft.basis === 'one_piece' ? 0 : parts.p + parts.f + parts.c + (get('water_g') ?? 0) + parts.alc + (total ? 0 : parts.fib);
  const negative = Object.values(values).some(value => value !== null && value !== undefined && value < 0);
  const sugar = get('sugar_g');
  const gap = Math.max(0, Math.abs(declared - expected) - (draft.energyRounding ?? 0));
  const rounding = draft.gramRounding ?? 0;
  const detail = negative ? 'negative' : mass > 102 + 3 * rounding ? 'mass'
    : sugar !== null && sugar > parts.c + 0.5 + 2 * rounding ? 'sugar_over_carbohydrate'
      : total && get('fibtg_g') !== null && parts.fib > parts.c + 2 * rounding ? 'fibre_over_carbohydrate'
        : gap > tolerance && gap > expected * 0.2 ? 'energy_mismatch' : '';
  // FD-09, FD-43: no group, no search; the record waits in quarantine until its category is decided.
  const unmapped = !detail && (draft.group ?? null) === null;
  return { values, kinds, status: detail || unmapped ? 'quarantine' as const : 'active' as const,
    reason: detail ? 'implausible' : unmapped ? UNMAPPED_REASON : '', detail };
}

// jsonb does not keep key order, so additions are compared with their keys sorted.
const canonical = (value: unknown) => JSON.stringify(value, (_, item) =>
  item && typeof item === 'object' && !Array.isArray(item)
    ? Object.fromEntries(Object.entries(item as object).sort(([a], [b]) => a < b ? -1 : 1)) : item);

export async function importDrafts(client: pg.Client, target: Target, parsed: Parsed | DraftSource, releasedAt: string,
  sourceName: string) {
  if (!/^\d{4}-\d{2}-\d{2}$/.test(releasedAt) || Number.isNaN(Date.parse(releasedAt)))
    throw new Error('Release date must be YYYY-MM-DD');
  const release = releasedAt.slice(0, 7);
  const addedTable = `food.food_added_${target.family}`;
  const columns = [...nutrientKeys];
  const count = { rows: 0, newRaw: 0, newVersions: 0, quarantine: 0,
    quarantineReasons: {} as Record<string, number>, quarantineDetails: {} as Record<string, number>,
    skipped: {} as Record<string, number> };
  await client.query('begin');
  try {
    // One transaction holds the whole import, so the planner never sees statistics for its rows.
    // A cached generic plan of the version trigger then scans the whole index for every measure
    // (quadratic on hundreds of thousands of rows); a plan per statement uses the primary key.
    await client.query('set local plan_cache_mode = force_custom_plan');
    let releaseId: string | null = null;
    const seen = new Set<string>();
    for await (const draft of parsed.drafts) {
      count.rows++;
      if (seen.has(draft.recordId)) throw new Error(`Duplicate record ${draft.recordId}`);
      seen.add(draft.recordId);
      const source = await client.query(`insert into food.food_source_record
        (src,record_id,raw_record,source_release,source_released_at)
        values($1,$2,$3,$4,$5)
        on conflict (src,record_id,content_sha256) do nothing returning id`,
      [target.src, draft.recordId, draft.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=$1 and record_id=$2 and content_sha256=encode(sha256($3::bytea),'hex')`,
      [target.src, draft.recordId, draft.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=$1 and s.record_id=$2 and v.licence_family=$3 order by r.version desc limit 1`,
      [target.src, draft.recordId, target.family])).rows[0];
      if (draft.group == null) draft.group = assignedGroup(target.src, draft.recordId);
      const checked = assess(draft, target.market);
      const status = checked.status;
      const { state, preparation } = inferState(draft.name);
      const byName = (a: { name: string; basis_amount: number }, b: { name: string; basis_amount: number }) =>
        a.name < b.name ? -1 : a.name > b.name ? 1 : a.basis_amount - b.basis_amount;
      const measures = [...new Map(draft.measures.map(m => [`${m.name}|${m.basis_amount}`, m])).values()].sort(byName);
      const searchName = normalizeSearch(draft.name);
      const priorMeasures = previous ? (await client.query(`select name,basis_amount from food.food_measure
        where licence_family=$2 and food_version_id=$1`,
      [previous.id, target.family])).rows.sort(byName) : [];
      const priorAdded = previous ? (await client.query(`select fields from ${addedTable}
        where source_record_id=$1 order by id desc limit 1`, [previous.source_record_id])).rows[0]?.fields : undefined;
      const same = previous && previous.name === draft.name && previous.search_name === searchName
        && previous.state === state && previous.preparation === preparation && previous.basis === draft.basis
        && (previous.chain_publication_uri ?? null) === (draft.chain?.uri ?? null)
        && (previous.chain_checked_at ? `${previous.chain_checked_at.getFullYear()}-${String(previous.chain_checked_at.getMonth() + 1).padStart(2, '0')}-${String(previous.chain_checked_at.getDate()).padStart(2, '0')}` : '') === (draft.chain?.checkedAt ?? '')
        && previous.carbohydrate_definition === draft.carbohydrateDefinition && previous.food_status === status
        && (previous.food_group ?? null) === (draft.group ?? null)
        && columns.every(key => (previous[key] ?? null) === (checked.values[key] ?? null))
        && JSON.stringify(priorMeasures) === JSON.stringify(measures)
        && canonical(priorAdded ?? {}) === canonical(draft.added);
      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($1,$2) returning id", [target.market, target.layer ?? 'common'])).rows[0].id;
      const provenance = Object.fromEntries(Object.entries(checked.kinds).map(([key, type]) =>
        [key, { src: target.src, record_id: draft.recordId, release, type }]));
      const params = [foodId, releaseId, sourceId, draft.name, searchName, sourceName, release, releasedAt,
        status, state, preparation, draft.basis, draft.carbohydrateDefinition,
        ...columns.map(key => checked.values[key] ?? null), provenance, draft.group ?? null,
        draft.chain?.name ?? null, draft.chain?.uri ?? null, draft.chain?.checkedAt ?? null];
      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,chain_name,chain_publication_uri,chain_checked_at)
        values('${target.family}',${slots.slice(0, 13).join(',')},'{}',${slots.slice(13).join(',')}) returning id`,
      params)).rows[0];
      for (const measure of measures) await client.query(`insert into food.food_measure
        (licence_family,food_version_id,name,basis_amount) values($1,$2,$3,$4)`,
      [target.family, version.id, measure.name, measure.basis_amount]);
      if (Object.keys(draft.added).length) await client.query(`insert into ${addedTable}
        (source_record_id,food_release_id,fields) values($1,$2,$3)`, [sourceId, releaseId, draft.added]);
      if (draft.gtin) await client.query(`insert into food.food_barcode(gtin,market,food_id) values($1,$2,$3)
        on conflict do nothing`, [draft.gtin, target.market, foodId]);
      count.newVersions++;
      if (status === 'quarantine') {
        count.quarantine++;
        count.quarantineReasons[checked.reason] = (count.quarantineReasons[checked.reason] ?? 0) + 1;
        if (checked.detail) count.quarantineDetails[checked.detail] = (count.quarantineDetails[checked.detail] ?? 0) + 1;
      }
    }
    for (const item of parsed.skipped) count.skipped[item.reason] = (count.skipped[item.reason] ?? 0) + 1;
    await client.query('commit');
    return { ...count, releaseId };
  } catch (error) { await client.query('rollback'); throw error; }
}

// Dry-run summary of what an import would do, without a database.
export function summarize(parsed: Parsed, market: string) {
  const out = { parsed: parsed.drafts.length, active: 0, quarantine: {} as Record<string, number>,
    skipped: {} as Record<string, number>, withoutType: 0 };
  for (const item of parsed.skipped) out.skipped[item.reason] = (out.skipped[item.reason] ?? 0) + 1;
  for (const draft of parsed.drafts) {
    const checked = assess(draft, market);
    if (checked.status === 'active') out.active++;
    else out.quarantine[checked.reason] = (out.quarantine[checked.reason] ?? 0) + 1;
    if (Object.keys(draft.kinds).length === 0) out.withoutType++;
  }
  return out;
}