// sheets.jsx — RTDC data layer.
//
// Talks to the private Apps Script backend (see backend/Code.gs). The backend
// validates login against a PRIVATE Google Sheet and only returns data the
// signed-in role is allowed to see, so the spreadsheet itself stays private —
// the browser never sees the Sheet ID, passwords, or raw rows.

(function () {
  // ──────────────────────────────────────────────────────────────────────────
  // ⬇️  PASTE your deployed Apps Script Web App URL here (it ends with /exec).
  //     See backend/Code.gs and DEPLOY.md for how to get it.
  // ──────────────────────────────────────────────────────────────────────────
  const BACKEND_URL = 'https://script.google.com/macros/s/AKfycbzYTuthYbb6GOHIazttlp46yBRyL4HYML4dhrDZDP7oppc_D25MYOgwTYPjEX0XRXMA/exec';

  // Session state, captured at login.
  const state = { token: null, permissions: null, user: null };

  // POST as text/plain so the browser sends NO CORS preflight (Apps Script has
  // no doOptions handler — a preflight would fail). The body is a JSON string.
  async function api(payload) {
    if (!BACKEND_URL || BACKEND_URL.indexOf('PASTE_YOUR') === 0) {
      throw new Error('BACKEND_URL is not set in src/sheets.jsx');
    }
    const res = await fetch(BACKEND_URL, {
      method: 'POST',
      body: JSON.stringify(payload),
      redirect: 'follow',
    });
    if (!res.ok) throw new Error('Backend HTTP ' + res.status);
    return res.json();
  }

  // Validate credentials server-side. Returns { ok, user, permissions, token }
  // or { ok:false, error }. On success, caches token + permissions for reuse.
  async function login(username, password) {
    const data = await api({ action: 'login', username: username, password: password });
    if (data && data.ok) {
      state.token = data.token;
      state.permissions = data.permissions;
      state.user = data.user;
    }
    return data;
  }

  // The permission matrix captured at login — no extra network round-trip.
  async function loadPermissions() {
    if (!state.permissions) throw new Error('Not authenticated');
    return state.permissions;
  }

  // Find a column index whose header contains ALL given (lower-cased) keywords.
  function colIndex(headers, keywords) {
    const norm = headers.map((h) => String(h || '').toLowerCase().replace(/\s+/g, ' '));
    for (let i = 0; i < norm.length; i++) {
      if (keywords.every((k) => norm[i].indexOf(k) !== -1)) return i;
    }
    return -1;
  }

  // Talent roster (token-gated). Maps the backend's rows-of-arrays into objects;
  // columns are matched by header keywords (robust to the sheet's multi-line headers).
  async function loadTalents() {
    const data = await api({ action: 'talents', token: state.token });
    if (!data || !data.ok) throw new Error((data && data.error) || 'Failed to load talents');
    const headers = data.headers || [];
    const rows = data.talents || [];
    const idx = {
      photo:        colIndex(headers, ['photo']),
      name:         colIndex(headers, ['talent name']),
      company:      colIndex(headers, ['company']),
      age:          colIndex(headers, ['age', 'jan']),
      serviceYears: colIndex(headers, ['service years']),
      position:     colIndex(headers, ['current position']),
      hipo:         colIndex(headers, ['high potential']),
      successor:    colIndex(headers, ['next successor']),
      readiness:    colIndex(headers, ['readiness']),
      program:      colIndex(headers, ['program', 'yssa']),
      yearAttend:   colIndex(headers, ['attendance']),
      nextProgram:  colIndex(headers, ['next leadership']),
      talentBox:    colIndex(headers, ['talent box']),
    };
    const cell = (r, i) => (i >= 0 && r[i] != null ? String(r[i]).trim() : '');
    const clean = (h) => String(h || '').replace(/\s+/g, ' ').trim();
    const labels = headers.map(clean);
    return rows
      .map((r) => ({
        photo:        cell(r, idx.photo),
        name:         cell(r, idx.name),
        company:      cell(r, idx.company),
        age:          cell(r, idx.age),
        serviceYears: cell(r, idx.serviceYears),
        position:     cell(r, idx.position),
        hipo:         cell(r, idx.hipo),
        successor:    cell(r, idx.successor),
        readiness:    cell(r, idx.readiness),
        program:      cell(r, idx.program),
        yearAttend:   cell(r, idx.yearAttend),
        nextProgram:  cell(r, idx.nextProgram),
        talentBox:    cell(r, idx.talentBox),
        all: labels
          .map((label, i) => ({ label, value: cell(r, i) }))
          .filter((f) => f.label !== '' && f.value !== ''),
      }))
      .filter((t) => t.name !== '');
  }

  // ── CCD assessment results ──
  // Save the current user's result. Identity (user) is taken from the token
  // server-side; we pass name only for the sheet's readability.
  async function ccdSave(result) {
    return api({
      action: 'ccd_save',
      token: state.token,
      dominant: result.dominant,
      tally: result.tally,
      name: (state.user && state.user.name) || '',
    });
  }

  // The signed-in user's own CCD history (newest handling done in the UI).
  async function ccdHistory() {
    const data = await api({ action: 'ccd_history', token: state.token });
    if (!data || !data.ok) throw new Error((data && data.error) || 'Failed to load CCD history');
    return data.attempts || [];
  }

  // ── Google Drive share-link → embeddable image URL. Uses the official
  //    /thumbnail endpoint (CDN-backed, handles volume) — more reliable than
  //    lh3/d/, which returns 503 under load. Pair with referrerPolicy="no-referrer".
  //    NOTE: the photo file must be shared "Anyone with the link", otherwise Google
  //    returns an HTML access page and the <img> falls back to initials.
  function driveImageUrl(link, size) {
    if (!link) return '';
    const s = String(link);
    const m = s.match(/\/d\/([A-Za-z0-9_-]+)/) || s.match(/[?&]id=([A-Za-z0-9_-]+)/);
    const id = m ? m[1] : null;
    return id ? 'https://drive.google.com/thumbnail?id=' + id + '&sz=w' + (size || 200) : s;
  }

  window.RTDC_SHEETS = { login, loadPermissions, loadTalents, ccdSave, ccdHistory, driveImageUrl };
})();