// ur2d-megacheck.jsx — the league Mega Tables vs Bituko's results database, value by value.
// RULE (user, 2026-10-05): no single source of truth. The Mega Table is the default; wherever the two disagree, that
// value loses its truth status and is TAGGED as disputed (both values kept). Tables where the Mega Table is known to be
// unfinished show the database value, still tagged. Used by loadDbStandings (finished seasons on the Standings page) and
// by Data Check.html (the live list for Bituko). Runtime deps: dbGetAll (ur2d-db), resolvePerson (roster), loadArchive (UL).

const UR_MT_SOURCES = {
  ul: { code: 'UL', id: '15fiK0Nic45xvlC0iMc7JilDBYq1KN1Vuo-184SSMp7c', drivers: 0, teams: 564964483, archive: true }, // parsed by ur2d-archive.jsx
  hl: { code: 'CL', id: '1ikGfCHXelVyYX_vGkMYbbQ8-eOU5om_m9H6CNWGpEJA', drivers: 0, teams: 1003306772 },
  jl: { code: 'JL', id: '1jvfXDCsDvrSmF53S7Y-LDmY8rahzKBLEh8IL6jG8j-8', drivers: 0, teams: 67666198 },
  al: { code: 'AL', id: '1Ll_C9zU9nV64MRJN6ofpNKb_1oC5-5gLoDCMwKP4E9Y', drivers: 0, teams: 67666198 },
};
// Old Mega Table team codes → the database's code for the same team (FKR, KSM, WER: probable, points differ).
const UR_MT_TEAM_ALIAS = {
  hl: { LTR: 'LTE', FKR: 'KKR' },
  jl: { DEL: 'DSZ', ART: 'ACE', CTM: 'UWU', EBR: 'MCJ', ESM: '8SR', ASR: 'ASE', KSM: 'KMP', WER: 'SWR' },
  al: { BUL: 'BLT', FUR: 'RFA' },
};
// Mega Table tables that are clearly unfinished (a fraction of the league sheet's points) → database values shown.
const UR_MT_INCOMPLETE = { hl: { S22: ['teams'] }, jl: { S22B: ['teams'] }, al: { S22: ['drivers'], S15: ['teams'] } };
// Alt-account / cheating recount seasons (context only — the Mega Tables include the recounts).
const UR_RECOUNT_SEASONS = { ul: ['S2', 'S20', 'S21', 'S22'], hl: ['S17', 'S18', 'S19'], jl: ['S17B', 'S18A', 'S18B', 'S19A', 'S19B'], al: ['S12', 'S13', 'S14', 'S15', 'S16'] };
const UR_MT_TYPE_LABEL = { points: 'Points differ', 'mt-only': 'Only in Mega Table', 'db-only': 'Only in database', champion: 'Champion differs', unfinished: 'Mega Table unfinished', missing: 'Season missing from database' };

function _mtParseCsv(t) {
  const rows = []; let row = [], f = '', q = false;
  for (let i = 0; i < t.length; i++) {
    const c = t[i];
    if (q) { if (c === '"') { if (t[i + 1] === '"') { f += '"'; i++; } else q = false; } else f += c; }
    else if (c === '"') q = true;
    else if (c === ',') { row.push(f); f = ''; }
    else if (c === '\n') { row.push(f); rows.push(row); row = []; f = ''; }
    else if (c !== '\r') f += c;
  }
  row.push(f); rows.push(row);
  return rows;
}
// Sheet numbers use a comma decimal ("282,5"); dots are thousands separators.
const _mtNum = (s) => { const x = String(s || '').trim(); if (!/^-?[\d.]+(,\d+)?$/.test(x)) return null; const v = parseFloat(x.replace(/\./g, '').replace(',', '.')); return isNaN(v) ? null : v; };
const _mtNorm = (s) => String(s || '').normalize('NFKD').replace(/[\u0300-\u036f]/g, '').toLowerCase().replace(/[^a-z0-9]/g, '');
const _mtDriverKey = (name) => { const p = typeof window.resolvePerson === 'function' ? window.resolvePerson(name) : null; return p && p.handle ? 'h:' + p.handle : 'n:' + _mtNorm(name); };
const _mtNamesClose = (a, b) => { const x = _mtNorm(a), y = _mtNorm(b); return x.length >= 4 && y.length >= 4 && (x.includes(y) || y.includes(x)); };
const _mtRank = (lbl) => { const m = String(lbl).match(/^S(\d+)([AB])?$/); return m ? +m[1] * 10 + (m[2] === 'B' ? 2 : m[2] === 'A' ? 1 : 0) : 0; };
const _mtSum = (rows) => Math.round(rows.reduce((s, r) => s + (Number(r.pts) || 0), 0) * 100) / 100;

const _mtCsvCache = {};
function _mtCsv(id, gid) {
  const k = id + ':' + gid;
  if (!_mtCsvCache[k]) {
    _mtCsvCache[k] = fetch(`https://docs.google.com/spreadsheets/d/${id}/export?format=csv&gid=${gid}`)
      .then((r) => { if (!r.ok) throw new Error('Mega Table HTTP ' + r.status); return r.text(); })
      .then(_mtParseCsv)
      .catch((e) => { delete _mtCsvCache[k]; throw e; });
  }
  return _mtCsvCache[k];
}
// "Season N" label anywhere in a row opens a block; each row = first non-empty cell after the label (skipping
// Sprint/Feature) + the points cell after it. Sprint and feature rows are summed later by key.
function _mtBlocks(rows) {
  const out = {}; let cur = null, base = 0;
  rows.forEach((r) => {
    const li = r.findIndex((c) => /^\s*season\s+\d+/i.test(String(c || '')));
    if (li >= 0) { const m = String(r[li]).match(/season\s+(\d+)\s*([AB])?/i); cur = 'S' + m[1] + (m[2] ? m[2].toUpperCase() : ''); base = li; }
    if (!cur) return;
    const cells = r.slice(base + 1).map((c) => String(c || '').trim()).filter(Boolean);
    let i = 0; if (/^(sprint|feature)$/i.test(cells[0] || '')) i++;
    const name = cells[i], pts = _mtNum(cells[i + 1]);
    if (!name || _mtNum(name) != null || pts == null) return;
    (out[cur] = out[cur] || []).push({ name, pts });
  });
  return out;
}
async function _mtMegaSide(league) {
  const src = UR_MT_SOURCES[league];
  if (src.archive && typeof window.loadArchive === 'function') {
    const a = await window.loadArchive(league);
    const drivers = {}, teams = {};
    Object.values((a && a.seasons) || {}).forEach((s) => {
      if (!s || !s.number) return;
      const lbl = 'S' + s.number;
      if (s.drivers && s.drivers.length) drivers[lbl] = s.drivers.map((d) => ({ name: d.name, pts: Number(d.pts) || 0, starts: d.starts }));
      if (s.teams && s.teams.length && s.teamsComplete !== false) teams[lbl] = s.teams.map((t) => ({ name: t.short, pts: Number(t.pts) || 0 }));
    });
    return { drivers, teams };
  }
  const [d, t] = await Promise.all([_mtCsv(src.id, src.drivers), _mtCsv(src.id, src.teams)]);
  return { drivers: _mtBlocks(d), teams: _mtBlocks(t) };
}
const _mtDbCache = {};
function _mtDbSide(code) {
  const group = (rows, f) => { const g = {}; rows.forEach((r) => { const l = 'S' + r.number + (r.division || ''); (g[l] = g[l] || []).push({ name: r[f], pts: Number(r.points) || 0, full: r.team || null }); }); return g; };
  if (!_mtDbCache[code]) {
    _mtDbCache[code] = Promise.all([
      dbGetAll('v_driver_standings?select=number,division,driver,points&league=eq.' + code + '&order=number,division,driver'),
      dbGetAll('v_team_standings?select=number,division,short_name,team,points&league=eq.' + code + '&order=number,division,short_name'),
    ]).then(([d, t]) => ({ drivers: group(d, 'driver'), teams: group(t, 'short_name') }))
      .catch((e) => { delete _mtDbCache[code]; throw e; });
  }
  return _mtDbCache[code];
}

// One season table → entries { mt, db, mtName, dbName, status, match } + lookup by any raw key from either side.
function _mtCompareTable(league, table, mtRows, dbRows) {
  const isTeam = table === 'teams';
  const alias = isTeam ? (UR_MT_TEAM_ALIAS[league] || {}) : {};
  const up = (s) => String(s || '').trim().toUpperCase();
  const map = {};
  const get = (k) => (map[k] = map[k] || { key: k, mt: null, db: null, mtKeys: [], dbKeys: [] });
  mtRows.forEach((r) => {
    const raw = isTeam ? up(r.name) : _mtDriverKey(r.name);
    const e = get(isTeam ? (alias[raw] || raw) : raw);
    e.mt = (e.mt || 0) + r.pts; e.mtName = e.mtName || r.name;
    if (!e.mtKeys.includes(raw)) e.mtKeys.push(raw);
    if (r.starts != null) e.starts = (e.starts || 0) + r.starts;
    if (isTeam && alias[raw]) e.match = 'code';
  });
  dbRows.forEach((r) => {
    const raw = isTeam ? up(r.name) : _mtDriverKey(r.name);
    const e = get(raw);
    e.db = (e.db || 0) + r.pts; e.dbName = e.dbName || r.name;
    if (!e.dbKeys.includes(raw)) e.dbKeys.push(raw);
    if (r.full) e.fullName = r.full;
  });
  // Leftovers on both sides: an old team code with identical points, or a driver name variant (one name contains the other).
  const onlyDb = Object.values(map).filter((e) => e.mt == null);
  Object.values(map).filter((e) => e.db == null).forEach((m) => {
    const j = onlyDb.findIndex((d) => (isTeam ? m.mt > 0 && Math.abs(d.db - m.mt) < 0.01 : _mtNamesClose(m.mtName, d.dbName)));
    if (j < 0) return;
    const d = onlyDb.splice(j, 1)[0];
    delete map[m.key];
    Object.assign(d, { mt: m.mt, mtName: m.mtName, mtKeys: m.mtKeys, starts: m.starts, match: isTeam ? 'code' : 'name' });
  });
  const entries = Object.values(map).map((e) => {
    e.mt = e.mt == null ? null : Math.round(e.mt * 100) / 100;
    e.db = e.db == null ? null : Math.round(e.db * 100) / 100;
    const listed = !isTeam && e.db == null && e.starts === 0; // on the Mega Table but never started (the UL archive knows starts)
    const status = e.mt != null && e.db != null ? (Math.abs(e.mt - e.db) < 0.001 ? 'match' : 'points') : listed ? 'listed' : e.mt != null ? 'mt-only' : 'db-only';
    return { ...e, status, name: e.mtName || e.dbName };
  });
  const top = (f) => entries.filter((e) => e[f] != null).sort((a, b) => b[f] - a[f])[0] || null;
  const mtTop = top('mt'), dbTop = top('db');
  const byKey = {};
  entries.forEach((e) => [...e.mtKeys, ...e.dbKeys].forEach((k) => { byKey[k] = e; }));
  return { entries, byKey, champion: mtTop && dbTop && mtTop !== dbTop ? { mt: mtTop, db: dbTop } : null };
}

async function _mtBuild(league) {
  const src = UR_MT_SOURCES[league];
  const [mt, db] = await Promise.all([_mtMegaSide(league), _mtDbSide(src.code)]);
  const ad = window.UR2D_ADAPTERS && window.UR2D_ADAPTERS[league];
  const cur = ad && ad.seasons.find((s) => s.isCurrent);
  const recount = new Set(UR_RECOUNT_SEASONS[league] || []);
  const labels = [...new Set([...Object.keys(mt.drivers), ...Object.keys(mt.teams)])]
    .filter((l) => _mtRank(l) > 0 && (!cur || l !== cur.label)).sort((a, b) => _mtRank(a) - _mtRank(b));
  const seasons = {}, issues = [];
  let clean = 0;
  const order = { points: 0, 'mt-only': 1, 'db-only': 2 };
  labels.forEach((lbl) => {
    const S = (seasons[lbl] = { recount: recount.has(lbl) });
    let n = 0;
    [['drivers', 'WDC'], ['teams', 'WCC']].forEach(([table, tableLabel]) => {
      const mtRows = mt[table][lbl];
      if (!mtRows || !mtRows.length) return;
      const dbRows = db[table][lbl] || [];
      const base = { league: src.code, lg: league, season: lbl, table: tableLabel, recount: S.recount };
      if (!dbRows.length) { issues.push({ ...base, type: 'missing', name: 'Whole season', mt: _mtSum(mtRows), db: null }); n++; return; }
      const T = _mtCompareTable(league, table, mtRows, dbRows);
      T.incomplete = ((UR_MT_INCOMPLETE[league] || {})[lbl] || []).includes(table);
      S[table] = T;
      if (T.incomplete) {
        const differing = T.entries.filter((e) => e.status !== 'match' && e.status !== 'listed').length;
        issues.push({ ...base, type: 'unfinished', name: differing + ' values differ · database shown', mt: _mtSum(mtRows), db: _mtSum(dbRows) }); n++;
        return;
      }
      if (T.champion) {
        issues.push({ ...base, type: 'champion', name: T.champion.mt.mtName + ' vs ' + (T.champion.db.dbName || T.champion.db.name), mt: T.champion.mt.mt, db: T.champion.db.db }); n++;
      }
      T.entries.filter((e) => order[e.status] != null)
        .sort((a, b) => order[a.status] - order[b.status] || Math.abs((b.mt || 0) - (b.db || 0)) - Math.abs((a.mt || 0) - (a.db || 0)))
        .forEach((e) => {
          issues.push({ ...base, type: e.status, name: e.status === 'db-only' ? e.dbName : e.mtName, altName: e.match && e.dbName && e.dbName !== e.mtName ? e.dbName : null, mt: e.mt, db: e.db, match: e.match || null });
          n++;
        });
    });
    if (!n) clean++;
  });
  return { league, code: src.code, seasons, issues, stats: { seasons: labels.length, clean, issues: issues.length } };
}
const _mtCompareCache = {};
function loadMegaCompare(league) {
  if (!UR_MT_SOURCES[league]) return Promise.reject(new Error('no Mega Table for ' + league));
  if (!_mtCompareCache[league]) _mtCompareCache[league] = _mtBuild(league).catch((e) => { delete _mtCompareCache[league]; throw e; });
  return _mtCompareCache[league];
}

// Standings overlay for one finished season: Mega Table value by default, database value where the Mega Table table is
// unfinished; every value the two disagree on carries `disputed: { mt, db, shown }`. Rows only one side has are added.
function _mtOverlay(T, rows, isTeam) {
  if (!T || !rows) return rows;
  const used = new Set();
  const keyOf = (r) => (isTeam ? String(r.short || '').trim().toUpperCase() : _mtDriverKey(r.name));
  const disputedOf = (e, showDb) => (['points', 'mt-only', 'db-only'].includes(e.status) ? { mt: e.mt, db: e.db, shown: showDb ? 'db' : 'mt' } : null);
  const out = rows.map((r) => {
    const e = T.byKey[keyOf(r)];
    if (!e) return r;
    used.add(e);
    const showDb = T.incomplete ? e.db != null : e.mt == null;
    const pts = showDb ? e.db : e.mt;
    return { ...r, pts: pts != null ? pts : r.pts, disputed: disputedOf(e, showDb) };
  });
  T.entries.forEach((e) => {
    if (used.has(e) || !['points', 'mt-only', 'db-only'].includes(e.status)) return;
    if (T.incomplete && e.db == null) return; // don't add rows that only an unfinished Mega Table has
    const showDb = T.incomplete || e.mt == null;
    const pts = showDb ? e.db : e.mt;
    if (pts == null) return;
    const disputed = disputedOf(e, showDb);
    out.push(isTeam ?
      { short: e.dbKeys[0] || e.mtKeys[0] || e.key, name: e.fullName || e.dbName || e.mtName, pts, disputed } :
      { name: e.dbName || e.mtName, team: null, pts, wins: 0, podiums: 0, poles: 0, fastestLaps: 0, disputed });
  });
  return out;
}
async function applyMegaOverlay(league, sdef, raw) {
  if (!raw || !sdef || !UR_MT_SOURCES[league]) return raw;
  let cmp;
  try { cmp = await loadMegaCompare(league); } catch (e) { console.warn('[ur2d-megacheck] comparison unavailable:', e.message); return raw; }
  const S = cmp.seasons[sdef.label];
  if (!S) return raw;
  return { ...raw, drivers: _mtOverlay(S.drivers, raw.drivers, false), teams: raw.teams ? _mtOverlay(S.teams, raw.teams, true) : raw.teams };
}

function megaIssueTags(i) {
  const t = [];
  if (i.recount) t.push('Recount season');
  if (i.match === 'code') t.push('Old team code');
  if (i.match === 'name') t.push('Name variant');
  if (i.mt != null && i.db != null && i.type === 'points' && Math.abs(Math.abs(i.mt - i.db) - 0.5) < 0.001) t.push('Half point');
  return t;
}
function megaIssuesCsv(issues) {
  const esc = (v) => { const s = v == null ? '' : String(v); return /[",\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s; };
  const head = ['league', 'season', 'table', 'issue', 'name', 'database_name', 'mega_table', 'database', 'difference', 'context'];
  const lines = issues.map((i) => [i.league, i.season, i.table, UR_MT_TYPE_LABEL[i.type] || i.type, i.name, i.altName || '', i.mt, i.db,
    i.mt != null && i.db != null && i.type !== 'champion' ? Math.round((i.mt - i.db) * 100) / 100 : '', megaIssueTags(i).join('; ')].map(esc).join(','));
  return [head.join(','), ...lines].join('\n');
}

Object.assign(window, { UR_MT_SOURCES, UR_MT_TEAM_ALIAS, UR_MT_INCOMPLETE, UR_RECOUNT_SEASONS, UR_MT_TYPE_LABEL, loadMegaCompare, applyMegaOverlay, megaIssueTags, megaIssuesCsv });