// Live shipment data, sourced from the public Google Sheet:
// https://docs.google.com/spreadsheets/d/11DQiAzSNcT2Fkwcx8Ao-9rhwQTTRsPZaO9rQpjn8_lI
//
// Uses the gviz JSON endpoint — works for any public sheet, no API key required.
// Edit the sheet → reload the page (or wait for the auto-refresh) → site updates.
//
// PUBLIC-SAFE COLUMNS ONLY. The sheet also holds two private contact columns
// used for internal email notifications. Those are NEVER requested here: the
// fetch runs a two-step gviz query — a one-row schema read to learn which column
// letters hold the safe fields, then a `select` naming ONLY those letters. The
// private values are absent from the network response, not filtered afterwards.
//
// Safe columns: AWB | Airline | Origin | Destination | Status | ETA | Tracking URL
//
// We tolerate header typos / capitalisation by matching loosely.

const SHEET_ID = '11DQiAzSNcT2Fkwcx8Ao-9rhwQTTRsPZaO9rQpjn8_lI';
const SHEET_NAME = 'Sheet1';
const REFRESH_MS = 60_000; // re-fetch every minute

// gviz returns JSONP-ish text wrapped in google.visualization.Query.setResponse(...)
// Strip the wrapper and parse the JSON inside.
function parseGviz(text) {
  const match = text.match(/google\.visualization\.Query\.setResponse\(([\s\S]+)\);?/);
  if (!match) return null;
  try { return JSON.parse(match[1]); } catch { return null; }
}

// Loosely normalise a column label so "Orgin", "Origin ", "ORIGIN" all match.
function norm(s) { return String(s || '').toLowerCase().replace(/[^a-z]/g, ''); }

// Anything matching these is treated as private and never selected, even if a
// header is renamed later (e.g. "Client Email", "Buyer Name", "Contact").
const PRIVATE_PATTERNS = ['customer', 'client', 'buyer', 'consignee', 'email', 'mail', 'contact', 'phone', 'tel'];
function isPrivateLabel(label) {
  const n = norm(label);
  return PRIVATE_PATTERNS.some(p => n.includes(p));
}

const COLUMN_KEYS = {
  awb:         ['awb'],
  airline:     ['airline', 'carrier'],
  origin:      ['origin', 'orgin', 'from'],
  destination: ['destination', 'destinatin', 'to'],
  status:      ['status'],
  eta:         ['eta'],
  trackingUrl: ['trackingurl', 'trackurl', 'url', 'link'],
};

function buildIdxFromLabels(labels) {
  const idx = {};
  for (const [key, candidates] of Object.entries(COLUMN_KEYS)) {
    idx[key] = labels.findIndex((l, i) =>
      !isPrivateLabel(labels[i]) && candidates.some(c => l === c || l.includes(c))
    );
  }
  return idx;
}

function buildRowMapper(cols) {
  // cols is the gviz `cols` array. Build {key -> column index} by matching labels.
  const labels = cols.map(c => norm(c.label || ''));
  return buildIdxFromLabels(labels);
}

function cellValue(cell) {
  if (!cell) return '';
  // gviz formatted value (`f`) preserves the user's display formatting (dates, etc).
  // Fall back to raw value (`v`) if no formatted version.
  if (cell.f != null && cell.f !== '') return String(cell.f);
  if (cell.v != null) return String(cell.v);
  return '';
}

function gvizUrl(query) {
  const base = `https://docs.google.com/spreadsheets/d/${SHEET_ID}/gviz/tq?tqx=out:json&sheet=${encodeURIComponent(SHEET_NAME)}`;
  return `${base}${query ? `&tq=${encodeURIComponent(query)}` : ''}&_=${Date.now()}`;
}

async function gvizFetch(query) {
  const res = await fetch(gvizUrl(query));
  if (!res.ok) throw new Error('Sheet fetch failed: ' + res.status);
  const data = parseGviz(await res.text());
  if (!data || !data.table) throw new Error('Could not parse sheet response');
  return data.table;
}

// Step 1 — schema. gviz doesn't surface labels for this sheet (the header sits
// in row 1 as data), so we read exactly ONE row to learn the header text and
// work out which letters are safe. Cached for the page's lifetime.
let safeSelectPromise = null;
function resolveSafeSelect() {
  if (!safeSelectPromise) {
    safeSelectPromise = (async () => {
      const table = await gvizFetch('select * limit 1');
      let labels = table.cols.map(c => norm(c.label || ''));
      let dataStartsAtRow2 = false;
      if (labels.filter(Boolean).length < 3 && table.rows.length) {
        labels = table.rows[0].c.map(c => norm(c ? (c.f != null ? c.f : c.v) : ''));
        dataStartsAtRow2 = true;
      }
      const idx = buildIdxFromLabels(labels);
      const keys = Object.keys(COLUMN_KEYS).filter(k => idx[k] >= 0);
      if (keys.length < 3) return null; // unusable — caller falls back
      const letters = keys.map(k => table.cols[idx[k]].id);
      return {
        select: `select ${letters.join(',')}${dataStartsAtRow2 ? ' offset 1' : ''}`,
        keys,
      };
    })().catch(() => null);
  }
  return safeSelectPromise;
}

async function fetchShipments() {
  const safe = await resolveSafeSelect();

  // Step 2 — request ONLY the safe column letters.
  if (safe) {
    const table = await gvizFetch(safe.select);
    return table.rows
      .map(r => {
        const out = {};
        safe.keys.forEach((k, i) => { out[k] = cellValue(r.c[i]); });
        return { awb:'', airline:'', origin:'', destination:'', status:'', eta:'', trackingUrl:'', ...out };
      })
      .filter(s => s.awb || s.airline || s.origin);
  }

  // Fallback (labels unreadable, e.g. header sitting in row 2): read the sheet
  // and map by position, still emitting only the safe fields.
  const table = await gvizFetch();
  let idx = buildRowMapper(table.cols);
  let rows = table.rows;
  const matched = Object.values(idx).filter(v => v >= 0).length;
  if (matched < 3 && rows.length > 0) {
    const labels = rows[0].c.map(c => norm(c ? (c.f != null ? c.f : c.v) : ''));
    idx = buildIdxFromLabels(labels);
    rows = rows.slice(1);
  }
  return rows
    .map(r => {
      const get = key => idx[key] >= 0 ? cellValue(r.c[idx[key]]) : '';
      return {
        awb:         get('awb'),
        airline:     get('airline'),
        origin:      get('origin'),
        destination: get('destination'),
        status:      get('status'),
        eta:         get('eta'),
        trackingUrl: get('trackingUrl'),
      };
    })
    .filter(s => s.awb || s.airline || s.origin);
}

// React hook — components call this to get { shipments, loading, error, lastUpdated }.
function useShipments() {
  const [state, setState] = React.useState({
    shipments: null, loading: true, error: null, lastUpdated: null,
  });

  React.useEffect(() => {
    let cancelled = false;
    let timer = null;

    async function load() {
      try {
        const shipments = await fetchShipments();
        if (cancelled) return;
        setState({ shipments, loading: false, error: null, lastUpdated: new Date() });
      } catch (err) {
        if (cancelled) return;
        setState(s => ({ ...s, loading: false, error: err.message }));
      }
    }

    load();
    timer = setInterval(load, REFRESH_MS);
    return () => { cancelled = true; if (timer) clearInterval(timer); };
  }, []);

  return state;
}

// Format "X minutes ago" for the "Updated …" timestamp.
function formatRelative(date) {
  if (!date) return 'just now';
  const secs = Math.round((Date.now() - date.getTime()) / 1000);
  if (secs < 30) return 'just now';
  if (secs < 120) return '1 min ago';
  if (secs < 3600) return Math.round(secs / 60) + ' min ago';
  if (secs < 7200) return '1 hr ago';
  return Math.round(secs / 3600) + ' hr ago';
}

// ── Status model ────────────────────────────────────────────────────────────
// Three public states. Colours are the site's existing theme tokens, never new
// hex values: tomato (logo / CTA red), amber (gold), leaf (green).
function shipmentState(status) {
  const st = String(status || '').toLowerCase();
  if (/(arriv|deliver|landed|complete)/.test(st)) {
    return { key:'arrived',  label:'Arrived',    color:'var(--leaf)',   progress:100, icon:'check', pulse:false };
  }
  if (/(transit|flight|en route|enroute|air)/.test(st)) {
    return { key:'transit',  label:'In Transit', color:'var(--amber)',  progress:58,  icon:'flight', pulse:true };
  }
  return   { key:'departed', label:'Departed',   color:'var(--tomato)', progress:18,  icon:'depart', pulse:false };
}

Object.assign(window, { useShipments, fetchShipments, formatRelative, shipmentState });