// 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.
//
// Sheet columns (header row exactly as it appears in the sheet):
//   Name | AWB | Airline | Orgin | Destinatin | 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, ''); }

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

function buildRowMapper(cols) {
  // cols is the gviz `cols` array. Build {key -> column index} by matching labels.
  const labels = cols.map(c => norm(c.label || c.id));
  const idx = {};
  for (const [key, candidates] of Object.entries(COLUMN_KEYS)) {
    idx[key] = labels.findIndex(l => candidates.some(c => l === c || l.includes(c)));
  }
  return idx;
}

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 '';
}

async function fetchShipments() {
  const url = `https://docs.google.com/spreadsheets/d/${SHEET_ID}/gviz/tq?tqx=out:json&sheet=${encodeURIComponent(SHEET_NAME)}&_=${Date.now()}`;
  const res = await fetch(url);
  if (!res.ok) throw new Error('Sheet fetch failed: ' + res.status);
  const text = await res.text();
  const data = parseGviz(text);
  if (!data || !data.table) throw new Error('Could not parse sheet response');

  const idx = buildRowMapper(data.table.cols);

  return data.table.rows
    .map(r => {
      const get = key => idx[key] >= 0 ? cellValue(r.c[idx[key]]) : '';
      return {
        name:        get('name'),
        awb:         get('awb'),
        airline:     get('airline'),
        origin:      get('origin'),
        destination: get('destination'),
        status:      get('status'),
        eta:         get('eta'),
        trackingUrl: get('trackingUrl'),
      };
    })
    // Drop fully-empty rows (sheet has many blank rows after the data).
    .filter(s => s.name || s.awb || s.airline);
}

// 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';
}

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