import { readFile } from 'node:fs/promises';
import pg from 'pg';
import { importDrafts } from './national.ts';
import type { Draft, Parsed } from './national.ts';
import { chainDraft } from './chains.ts';
import { readXlsx } from './xlsx.ts';
import type { Cell } from './national.ts';
import { oxfordGroup, storedGroup } from '../groups.ts';
import type { ChainEntry } from '../chains/registry.ts';
import type { ChainItem, Nutrients } from '../chains/parse.ts';

// The Oxford 2024 dataset of UK restaurant chain menus (SRC-07, market GB): A. O'Hagan, University of Oxford,
// Oxford University Research Archive, DOI 10.5287/ora-6raddkrg9, licence CC BY 4.0, Dataset.xlsx, one sheet,
// one dish per row, values per 100 g and per single portion collected from the chains' online menus in 2024.
//
// FD-63 GATE DOES NOT APPLY HERE. The registry gate (chains/registry.ts) answers "may Calsa read this chain's
// website", and this import never reads a chain site: the licence is the dataset's own CC BY 4.0, which allows
// commercial reuse with attribution. The attribution below is therefore mandatory on every record.
export const oxfordDoi = 'https://doi.org/10.5287/ora-6raddkrg9';
export const oxfordAttribution = "O'Hagan et al., University of Oxford, CC BY 4.0";
export const oxfordSourceName = `${oxfordAttribution}: UK restaurant chain nutrition information, 2024 (${oxfordDoi})`;
// The dataset says "in 2024" and no day; the earliest date keeps the 6-month refresh rule (FD-28) honest.
export const oxfordCollectedAt = '2024-01-01';

const slug = (text: string) => text.normalize('NFKD').replace(/\p{M}/gu, '').toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-|-$/g, '');
const cell = (row: Record<string, Cell>, key: string) => {
  const value = row[key];
  if (value === null || value === undefined || value.trim() === '') return undefined;
  const n = Number(value);
  return Number.isFinite(n) && n >= 0 ? n : undefined;
};
const nutrients = (row: Record<string, Cell>, suffix: string): Nutrients => {
  const sodium = cell(row, `FINAL_Sodium_${suffix}`);
  const all: Nutrients = {
    kj: cell(row, `FINAL_Kj_${suffix}`), kcal: cell(row, `FINAL_Kcal_${suffix}`), fat: cell(row, `FINAL_Fat_${suffix}`),
    satfat: cell(row, `FINAL_Sat_${suffix}`), carbs: cell(row, `FINAL_Carb_${suffix}`), sugars: cell(row, `FINAL_Sugar_${suffix}`),
    fibre: cell(row, `FINAL_Fibre_${suffix}`), protein: cell(row, `FINAL_Protein_${suffix}`), saltG: cell(row, `FINAL_Salt_${suffix}`),
    sodiumMg: sodium === undefined ? undefined : sodium * 1000, // the sheet gives sodium in grams
  };
  return Object.fromEntries(Object.entries(all).filter(([, v]) => v !== undefined));
};

// The sheet gives no portion weight. It is implied by the pair of rows (portion / per 100 g) and used only
// when energy in kcal and in kJ imply the same weight; otherwise the dish has no measure (FD-30).
export function impliedPortionG(per100: Nutrients, portion: Nutrients): number | null {
  const byKcal = per100.kcal && portion.kcal !== undefined ? portion.kcal / per100.kcal * 100 : null;
  const byKj = per100.kj && portion.kj !== undefined ? portion.kj / per100.kj * 100 : null;
  if (byKcal === null || byKj === null || byKcal < 5 || byKcal > 3000) return null;
  return Math.abs(byKcal - byKj) / byKcal <= 0.02 ? Math.round(byKcal * 10) / 10 : null;
}

export function parseOxford(bytes: Buffer): Parsed & { chains: string[] } {
  const [sheet] = readXlsx(bytes);
  const header = sheet.rows[0].map(title => title?.trim() ?? '');
  const out: Parsed & { chains: string[] } = { drafts: [], skipped: [], chains: [] };
  const seen = new Set<string>();
  const names = new Set<string>();
  sheet.rows.slice(1).forEach((cells, index) => {
    const row: Record<string, Cell> = Object.fromEntries(header.map((title, at) => [title, cells[at] ?? null]));
    const restaurant = row.Restaurant?.trim(), product = row.Product_name?.trim();
    if (!restaurant || !product) { out.skipped.push({ recordId: null, reason: 'no chain or product name' }); return; }
    const per100 = nutrients(row, '100'), portion = nutrients(row, 'single_portion');
    const grams = impliedPortionG(per100, portion);
    const hasPer100 = per100.kcal !== undefined || per100.kj !== undefined;
    const item: ChainItem = { chain: slug(restaurant), market: 'GB', section: row.Subcategory?.trim() || null, name: product,
      servingG: hasPer100 ? grams : null, per100: hasPer100 ? per100 : null, perServing: portion,
      sourceUrl: oxfordDoi, collectedAt: oxfordCollectedAt };
    const entry: ChainEntry = { id: item.chain, name: restaurant, sources: [], termsUrl: null, verdict: 'allowed',
      basis: 'Oxford 2024 dataset, CC BY 4.0: not a chain-site read', checkedAt: oxfordCollectedAt };
    const draft = chainDraft(item, entry);
    if (!draft) { out.skipped.push({ recordId: product, reason: 'no energy' }); return; }
    // Per 100 g values keep the 100 g base even when no portion weight is implied; only per-portion-only dishes are one piece (FD-30).
    if (hasPer100) draft.basis = '100g';
    // FD-09: the group comes from the dataset's own subcategory table, like every other source.
    draft.group = storedGroup(oxfordGroup(item.section));
    let id = draft.recordId;
    if (seen.has(id)) id = `${id}-row${index + 2}`;
    seen.add(id);
    draft.recordId = id;
    draft.added = { ...draft.added, attribution: oxfordAttribution, licence: 'CC BY 4.0', dataset_doi: oxfordDoi,
      collected_year: 2024, limited_time_product: row.Limited_time_product?.trim() === 'Yes',
      portion_g_implied: grams !== null && hasPer100, restaurant_group: row.Restaurant_group?.trim() ?? null };
    if (!names.has(restaurant)) { names.add(restaurant); out.chains.push(restaurant); }
    out.drafts.push(draft);
  });
  return out;
}

export async function importOxfordFile(client: pg.Client, file: string) {
  const parsed = parseOxford(await readFile(file));
  return importDrafts(client, { src: 'SRC-07', market: 'GB', family: 'open', layer: 'chain' }, parsed, oxfordCollectedAt, oxfordSourceName);
}

export const hasFullMacros = (draft: Draft) => (['enerc_kcal', 'procnt_g', 'fat_g', 'chocdf_g'] as const).every(key => draft.values[key] != null);