// supabase-rest.jsx — direct REST wrapper for Supabase (no @supabase-js, no build step)
// Loads AFTER supabase-config.js, BEFORE data.jsx.
// Exposes window.pccAuth (GoTrue email/password) + window.pccDb (table CRUD).

(function () {
  const cfg = window.PCC_CONFIG;
  if (!cfg) { console.error('[pccDb] PCC_CONFIG missing'); return; }

  const REST = cfg.SUPABASE_URL + '/rest/v1';
  const GOTRUE = cfg.SUPABASE_URL + '/auth/v1';
  const SESSION_KEY = 'pcc-session';

  /* ── Auth state ─────────────────────────────────────────────────────── */
  // Session shape: { access_token, refresh_token, expires_at (epoch sec), user }
  let session = null;
  try {
    const raw = localStorage.getItem(SESSION_KEY);
    if (raw) session = JSON.parse(raw);
  } catch (_) { session = null; }

  function persist(s) {
    session = s;
    if (s) localStorage.setItem(SESSION_KEY, JSON.stringify(s));
    else   localStorage.removeItem(SESSION_KEY);
  }

  function isExpired() {
    if (!session || !session.expires_at) return true;
    // 60s safety margin
    return Date.now() / 1000 > (session.expires_at - 60);
  }

  function toSession(json) {
    return {
      access_token:  json.access_token,
      refresh_token: json.refresh_token,
      expires_at:    json.expires_at || (Math.floor(Date.now() / 1000) + (json.expires_in || 3600)),
      user:          json.user || null,
    };
  }

  async function tokenRequest(grant, body) {
    const res = await fetch(`${GOTRUE}/token?grant_type=${grant}`, {
      method: 'POST',
      headers: { apikey: cfg.SUPABASE_ANON_KEY, 'Content-Type': 'application/json' },
      body: JSON.stringify(body),
    });
    const json = await res.json().catch(() => ({}));
    if (!res.ok) {
      const msg = json.error_description || json.msg || json.error || `${res.status}`;
      throw new Error(msg);
    }
    return json;
  }

  async function signIn(email, password) {
    try {
      const json = await tokenRequest('password', { email, password });
      persist(toSession(json));
      return { ok: true, user: session.user };
    } catch (err) {
      return { ok: false, error: err.message || 'Sign-in failed' };
    }
  }

  async function refresh() {
    if (!session || !session.refresh_token) return false;
    try {
      const json = await tokenRequest('refresh_token', { refresh_token: session.refresh_token });
      persist(toSession(json));
      return true;
    } catch (_) {
      persist(null);
      return false;
    }
  }

  // Called once at boot: returns a usable session, refreshing if stale.
  async function ensureSession() {
    if (!session) return null;
    if (isExpired()) {
      const ok = await refresh();
      if (!ok) return null;
    }
    return session;
  }

  async function signOut() {
    if (session && session.access_token) {
      // best-effort server-side revoke
      fetch(`${GOTRUE}/logout`, {
        method: 'POST',
        headers: {
          apikey: cfg.SUPABASE_ANON_KEY,
          Authorization: 'Bearer ' + session.access_token,
        },
      }).catch(() => {});
    }
    persist(null);
  }

  window.pccAuth = {
    signIn,
    signOut,
    refresh,
    ensureSession,
    getSession: () => (session && !isExpired() ? session : null),
    currentUser: () => (session ? session.user : null),
  };

  /* ── REST layer ─────────────────────────────────────────────────────── */
  // Use the signed-in user's token when present (so RLS can enforce auth.uid()),
  // otherwise fall back to the anon key. Headers are built per-request.
  function authHeaders() {
    const token = (session && !isExpired() && session.access_token) || cfg.SUPABASE_ANON_KEY;
    return {
      apikey: cfg.SUPABASE_ANON_KEY,
      Authorization: 'Bearer ' + token,
      'Content-Type': 'application/json',
    };
  }

  async function req(method, path, body, opts) {
    const init = { method, headers: { ...authHeaders(), ...(opts && opts.headers || {}) } };
    if (body !== undefined) init.body = JSON.stringify(body);
    const res = await fetch(REST + path, init);
    if (!res.ok) {
      const txt = await res.text().catch(() => '');
      throw new Error(`[pccDb] ${method} ${path} → ${res.status} ${txt}`);
    }
    if (res.status === 204) return null;
    const ct = res.headers.get('content-type') || '';
    return ct.includes('application/json') ? res.json() : res.text();
  }

  function table(name) {
    return {
      list: (filter = '') => req('GET', `/${name}?select=*&order=created_at.desc${filter ? '&' + filter : ''}`),
      get:  (id)          => req('GET', `/${name}?id=eq.${id}&select=*`).then(r => r[0]),
      create: (row)       => req('POST', `/${name}`, row, { headers: { Prefer: 'return=representation' } }).then(r => r[0]),
      update: (id, patch) => req('PATCH', `/${name}?id=eq.${id}`, patch, { headers: { Prefer: 'return=representation' } }).then(r => r[0]),
      remove: (id)        => req('DELETE', `/${name}?id=eq.${id}`, undefined, { headers: { Prefer: 'return=minimal' } }),
    };
  }

  // ── Pipeline calculation helpers ───────────────────────────────────────
  // Weighted pipeline value: stage probability × monthly_value × 12 (annual)
  const STAGE_WEIGHTS = {
    Lead:       0.10,
    Prospect:   0.25,
    Demo:       0.50,
    Proposal:   0.75,
    Onboarding: 1.00,
    Active:     1.00,
    Churned:    0.00,
  };

  function calcPipeline(clients) {
    const byStage = {};
    let mrrActive = 0;
    let mrrAll    = 0;
    let weighted  = 0;
    let active    = 0;
    let onboard   = 0;
    let leads     = 0;
    let churned   = 0;

    for (const c of clients) {
      const stage = c.pipeline_stage || 'Lead';
      const value = Number(c.monthly_value || 0);
      byStage[stage] = (byStage[stage] || 0) + 1;
      mrrAll += value;
      weighted += value * (STAGE_WEIGHTS[stage] ?? 0);
      if (stage === 'Active')     { mrrActive += value; active++; }
      if (stage === 'Onboarding') onboard++;
      if (stage === 'Lead')       leads++;
      if (stage === 'Churned')    churned++;
    }

    const total = clients.length;
    const conversionPct = leads > 0 ? Math.round((active / (active + leads + onboard)) * 100) : 0;
    const churnPct      = total > 0 ? Math.round((churned / total) * 100) : 0;

    return {
      total, active, onboard, leads, churned,
      byStage,
      mrrActive,
      mrrAll,
      arrActive: mrrActive * 12,
      weightedMrr:    Math.round(weighted),
      weightedArr:    Math.round(weighted * 12),
      conversionPct,
      churnPct,
    };
  }

  // ── Activity feed helper (best-effort, never blocks the UI) ───────────
  // activity_feed columns: type (category enum), description, related_id, related_table.
  // Callers pass dotted actions like 'client.created'; the prefix sets the category
  // and we render a plain-English sentence (NOT the raw dotted action).
  const ACTIVITY_TYPES = ['lead', 'invoice', 'demo', 'client', 'system', 'provisioning'];
  const ACTIVITY_PHRASE = {
    'client.created':         t => `New client added — ${t}`,
    'client.updated':         t => `Updated client — ${t}`,
    'client.deleted':         t => `Deleted client — ${t}`,
    'client.promoted':        t => `Client moved to Active — ${t}`,
    'client.email_docs':      t => `Emailed onboarding documents — ${t}`,
    'lead.created':           t => `New prospect added — ${t}`,
    'lead.updated':           t => `Updated prospect — ${t}`,
    'lead.deleted':           t => `Deleted prospect — ${t}`,
    'lead.converted':         t => `Prospect converted to client — ${t}`,
    'invoice.created':        t => `Invoice created — ${t}`,
    'invoice.paid':           t => `Invoice marked paid — ${t}`,
    'system.diary_created':   t => `Diary entry added — ${t}`,
    'system.diary_updated':   t => `Diary entry updated — ${t}`,
    'system.diary_deleted':   t => `Diary entry deleted — ${t}`,
    'system.document_created':t => `Document template created — ${t}`,
    'system.settings_updated':()=> `Business settings updated`,
  };
  function logActivity(action, target, detail, relatedId) {
    const prefix = String(action || '').split('.')[0];
    const type = ACTIVITY_TYPES.includes(prefix) ? prefix : 'system';
    const phrase = ACTIVITY_PHRASE[action];
    const base = phrase ? phrase(target || '') : [target, action].filter(Boolean).join(' — ');
    const description = detail ? `${base} · ${detail}` : base;
    const row = { type, description, related_table: 'master_clients' };
    if (relatedId) row.related_id = relatedId;
    req('POST', '/activity_feed', row, { headers: { Prefer: 'return=minimal' } })
      .catch(err => console.warn('[pccDb] activity log failed', err.message));
  }

  window.pccDb = {
    clients:    table('master_clients'),
    leads:      table('leads'),
    onboarding: table('onboarding_steps'),
    activity:   table('activity_feed'),
    diary:      table('diary_entries'),
    invoices:   table('invoices'),
    expenses:   table('expenses'),
    income:     table('other_income'),
    subscriptions: table('recurring_expenses'),
    documents:  table('document_templates'),
    welcomePacks: table('welcome_packs'),
    infra:      table('infra_projects'),
    todos:      table('todos'),
    payments:   table('payments'),
    settings:   table('settings'),
    calcPipeline,
    logActivity,
    STAGE_WEIGHTS,
  };
})();