/* =========================================================
   Tappt — Supabase client + data layer (TapptDB)
   Real auth + profile/collection sync. Loaded BEFORE app-parts.jsx
   so the Store can defer to it. Falls back to localStorage-only
   when offline or not configured, so the app never hard-breaks.
   ========================================================= */
(function () {
  var SUPA_URL = 'https://ihgrxadtvidrlywmqhgk.supabase.co';
  var SUPA_KEY = 'sb_publishable_WFycUS-HOzdY1566tA-n_A_knhgiHWt';

  var sb = null;
  try {
    if (window.supabase && window.supabase.createClient) {
      sb = window.supabase.createClient(SUPA_URL, SUPA_KEY, {
        // We consume the OAuth hash ourselves in initOAuth (detectSessionInUrl off so there's
        // no double-handling). Sessions persist to localStorage and auto-refresh.
        auth: { persistSession: true, autoRefreshToken: true, detectSessionInUrl: false, flowType: 'implicit', storageKey: 'tappt_sb_auth' },
      });
    }
  } catch (e) { sb = null; }

  /* ---- OAuth completion ----
     Google/Apple return with an #access_token in the URL. We set the session from it directly
     (fast + reliable), then clean the URL. Writes await `sessionReady` so they carry the JWT. */
  var sessionReady = Promise.resolve(null);
  (function initOAuth() {
    if (!sb) return;
    try {
      if (!/[#&]access_token=|[?&]code=/.test((location.hash || '') + (location.search || ''))) return;
      window.__tapptOAuthBusy = true;
      sessionReady = (async function () {
        try {
          var hp = new URLSearchParams((location.hash || '').replace(/^#/, ''));
          if (hp.get('access_token')) {
            var rr = await sb.auth.setSession({ access_token: hp.get('access_token'), refresh_token: hp.get('refresh_token') || '' });
            try { history.replaceState(null, '', '/login'); } catch (e) {}
            return (rr && rr.data && rr.data.user) || null;
          }
          var code = new URLSearchParams((location.search || '').replace(/^\?/, '')).get('code');
          if (code) {
            var r2 = await sb.auth.exchangeCodeForSession(code);
            try { history.replaceState(null, '', '/login'); } catch (e) {}
            return (r2 && r2.data && r2.data.user) || null;
          }
        } catch (e) {}
        return null;
      })();
      window.__tapptOAuth = sessionReady;
    } catch (e) {}
  })();

  /* ---- map our camelCase user object <-> snake_case profile row ---- */
  var COLS = {
    handle: 'handle', name: 'name', bio: 'bio', avatar: 'avatar', cover: 'cover',
    coverKind: 'cover_kind', avatarColor: 'avatar_color', accountType: 'account_type',
    birthday: 'birthday', pro: 'pro', verified: 'verified', onboarded: 'onboarded',
    company: 'company', role: 'role',
    sections: 'sections', links: 'links', socials: 'socials', socialData: 'social_data',
    actions: 'actions', actionData: 'action_data', products: 'products', gallery: 'gallery',
    embeds: 'embeds', personas: 'personas', exchange: 'exchange', design: 'design',
    tipsConfig: 'tips_config', leadStages: 'lead_stages', navByPersona: 'nav_by_persona', prefs: 'prefs',
  };
  function toRow(user) {
    var row = {};
    for (var k in COLS) { if (user[k] !== undefined) row[COLS[k]] = user[k]; }
    // design + tips + nav live as loose keys on the user object — fold into jsonb columns
    row.design = {
      profileTheme: user.profileTheme, linkAnim: user.linkAnim, iconAnim: user.iconAnim, headlineFont: user.headlineFont,
      buttonStyle: user.buttonStyle, buttonRadius: user.buttonRadius, wallpaper: user.wallpaper, cardEffect: user.cardEffect,
      avatarRing: user.avatarRing, align: user.align, socialRotate: user.socialRotate,
      showAvatar: user.showAvatar, profileAnim: user.profileAnim, covers: user.covers,
    };
    row.tips_config = {
      tipsOn: user.tipsOn, tipHeadline: user.tipHeadline, tipSubtext: user.tipSubtext,
      tipAmounts: user.tipAmounts, tipIcon: user.tipIcon, tipDisplay: user.tipDisplay, tipColor: user.tipColor,
    };
    return row;
  }
  function fromRow(row) {
    if (!row) return null;
    var u = {};
    for (var k in COLS) { if (row[COLS[k]] !== undefined && row[COLS[k]] !== null) u[k] = row[COLS[k]]; }
    var d = row.design || {}; for (var dk in d) { if (d[dk] !== undefined && d[dk] !== null) u[dk] = d[dk]; }
    var t = row.tips_config || {}; for (var tk in t) { if (t[tk] !== undefined && t[tk] !== null) u[tk] = t[tk]; }
    u.id = row.id;
    return u;
  }

  var TapptDB = {
    available: function () { return !!sb; },
    client: sb,

    /* ---- AUTH ---- */
    async signUp(email, password, meta) {
      if (!sb) throw new Error('offline');
      var r = await sb.auth.signUp({ email: email, password: password, options: { data: meta || {} } });
      if (r.error) throw r.error;
      return r.data;
    },
    async signIn(email, password) {
      if (!sb) throw new Error('offline');
      var r = await sb.auth.signInWithPassword({ email: email, password: password });
      if (r.error) throw r.error;
      return r.data;
    },
    async verifyOtp(email, token) {
      if (!sb) throw new Error('offline');
      // Supabase emails a 6-digit signup code when "Confirm email" is ON. This verifies it
      // and establishes the session in one step. (No-op path if confirmation is OFF.)
      var r = await sb.auth.verifyOtp({ email: email, token: String(token), type: 'email' });
      if (r.error) throw r.error;
      return r.data;
    },
    async signInOAuth(provider) {
      if (!sb) throw new Error('offline');
      // Always land back on the canonical login URL of whatever origin we're on, so the
      // return target is deterministic (must be allow-listed in Supabase → URL Config).
      var redirectTo = location.origin + '/login';
      // Always show the account picker so people can choose WHICH Google/Apple account to
      // use (instead of Google silently auto-signing them into whatever they're logged into).
      // Lets them sign up with a different Gmail, or pick the right one on a shared device.
      var qp = provider === 'google' ? { prompt: 'select_account' } : {};
      return sb.auth.signInWithOAuth({ provider: provider, options: { redirectTo: redirectTo, queryParams: qp } });
    },
    async signOut() { if (sb) { try { await sb.auth.signOut(); } catch (e) {} } },
    async resendConfirm(email) {
      if (!sb) return;
      try { await sb.auth.resend({ type: 'signup', email: email }); } catch (e) {}
    },
    async currentUser() {
      if (!sb) return null;
      try { await sessionReady; } catch (e) {}
      var r = await sb.auth.getUser();
      return (r && r.data && r.data.user) || null;
    },
    /* uid from the LOCALLY-PERSISTED session (no network round-trip). This is what the write
       path uses — getUser() can fail on a flaky network and make a save silently bail, but
       getSession() just reads the token we already stored, so writes are reliable. */
    async sessionUid() {
      if (!sb) return null;
      try { await sessionReady; } catch (e) {}
      try { var r = await sb.auth.getSession(); return (r && r.data && r.data.session && r.data.session.user && r.data.session.user.id) || null; } catch (e) { return null; }
    },
    /* The signed-in user from the LOCALLY-PERSISTED session (no network) — authoritative for
       boot routing. Returns null when there is genuinely no session (→ show login). */
    async sessionUser() {
      if (!sb) return null;
      try { await sessionReady; } catch (e) {}
      try { var r = await sb.auth.getSession(); return (r && r.data && r.data.session && r.data.session.user) || null; } catch (e) { return null; }
    },
    /* Manually finish an OAuth round-trip from whatever the provider left in the URL.
       supabase-js's auto-detection sometimes does NOT consume the hash (token stays in
       the address bar, no session) — so we parse it ourselves and set the session. Handles
       BOTH implicit (#access_token=…) and PKCE (?code=…) responses. Returns the user. */
    async completeOAuthFromUrl() {
      if (!sb) return null;
      try {
        var hp = new URLSearchParams((location.hash || '').replace(/^#/, ''));
        if (hp.get('access_token')) {
          var r = await sb.auth.setSession({ access_token: hp.get('access_token'), refresh_token: hp.get('refresh_token') || '' });
          return (r && r.data && r.data.user) || null;
        }
        var qp = new URLSearchParams((location.search || '').replace(/^\?/, ''));
        if (qp.get('code')) {
          var r2 = await sb.auth.exchangeCodeForSession(qp.get('code'));
          return (r2 && r2.data && r2.data.user) || null;
        }
      } catch (e) {}
      // session may already be established (auto-detection won the race) — fall back to it
      try { var g = await sb.auth.getUser(); return (g && g.data && g.data.user) || null; } catch (e) {}
      return null;
    },
    onAuth(cb) { if (!sb) return null; var r = sb.auth.onAuthStateChange(function (_e, s) { cb(s && s.user || null); }); return (r && r.data && r.data.subscription) || null; },

    /* ---- PROFILE ---- */
    async loadProfile(uid) {
      if (!sb) return null;
      var r = await sb.from('profiles').select('*').eq('id', uid).maybeSingle();
      if (r.error) throw r.error;
      return fromRow(r.data);
    },
    async loadProfileByHandle(handle) {
      if (!sb) return null;
      var r = await sb.from('profiles').select('*').eq('handle', handle).maybeSingle();
      if (r.error) return null;
      return fromRow(r.data);
    },
    async saveProfile(uid, user) {
      if (!sb) return false;
      try { await sessionReady; } catch (e) {}
      if (!user || !user.handle) return false;          // nothing worth persisting pre-onboarding
      var row = toRow(user); row.id = uid;
      // Standard authenticated upsert. RLS (auth.uid() = id) restricts it to the owner's row;
      // the `authenticated` role now has the table grant, so this just works. .select() makes
      // a silent 0-row write throw instead of faking success.
      var r = await sb.from('profiles').upsert(row, { onConflict: 'id' }).select('id');
      if (r.error) throw r.error;
      if (!r.data || !r.data.length) throw new Error('profile write affected 0 rows');
      return true;
    },
    async handleAvailable(handle) {
      if (!sb) return true;
      var r = await sb.from('profiles').select('id').eq('handle', handle).maybeSingle();
      return !(r && r.data);
    },

    /* ---- STORAGE (media) ---- */
    // uploads a data URL or File to media/<uid>/<path>, returns the public URL
    async uploadMedia(dataUrlOrFile, ext) {
      if (!sb) return null;
      var au = await this.currentUser();
      if (!au || !au.id) return null;
      var blob = dataUrlOrFile;
      if (typeof dataUrlOrFile === 'string') {
        var res = await fetch(dataUrlOrFile); blob = await res.blob();
      }
      var e = ext || (blob.type && blob.type.indexOf('video') === 0 ? 'mp4' : 'jpg');
      var path = au.id + '/' + Date.now() + '_' + Math.random().toString(36).slice(2, 8) + '.' + e;
      var up = await sb.storage.from('media').upload(path, blob, { contentType: blob.type, upsert: true });
      if (up.error) throw up.error;
      var pub = sb.storage.from('media').getPublicUrl(path);
      return pub.data.publicUrl;
    },

    /* ---- PRE-GENERATED PROFILES (CEO builds, influencer claims) ---- */
    async savePregen(token, payload) {
      if (!sb) return false;
      try { await sessionReady; } catch (e) {}
      var au = await this.currentUser();
      var row = {
        token: token,
        handle: payload.handle || null,
        name: payload.name || null,
        bio: payload.bio || null,
        data: payload.data || {},
        created_by: au && au.id ? au.id : null,
      };
      var r = await sb.from('pregen_profiles').upsert(row, { onConflict: 'token' });
      return !r.error;
    },
    async loadPregen(token) {
      if (!sb) return null;
      var r = await sb.from('pregen_profiles').select('*').eq('token', token).maybeSingle();
      if (r.error || !r.data) return null;
      return r.data;
    },
    /* fetch a URL's OG image / title (server-side, no CORS) for link thumbnails */
    async linkPreview(url) {
      if (!sb || !url) return null;
      try {
        var r = await sb.functions.invoke('link-preview', { body: { url: url } });
        if (r.error) return null;
        return r.data || null;
      } catch (e) { return null; }
    },
    async listPregen() {
      if (!sb) return [];
      var r = await sb.from('pregen_profiles').select('token,handle,name,claimed,created_at').order('created_at', { ascending: false }).limit(200);
      if (r.error || !r.data) return [];
      return r.data;
    },
    async claimPregen(token) {
      if (!sb) return null;
      try { await sessionReady; } catch (e) {}
      var r = await sb.rpc('claim_pregen', { p_token: token });
      if (r.error) return { error: 'rpc' };
      return r.data;
    },
  };

  /* =========================================================
     MOMENTS — dedicated `moments` + `moment_comments` tables.
     Replaces the old "moments ride inside the profile blob" model so
     posts/likes/comments persist cross-device and other signed-in
     users can comment (cross-user). The app keeps its camelCase shape
     ({ id, cap, img, imgKind, ts, editedTs, taps, comments:[…] }); we
     map to/from snake_case rows here. Comments are a flat table that we
     re-nest into the app's { …, replies:[] } tree via client ids.
     ========================================================= */
  // upload inline media (data: URL) to Storage; pass through real URLs; drop idb: tokens.
  async function momentMedia(img) {
    if (!img || typeof img !== 'string') return null;
    if (img.indexOf('idb:') === 0) return null;          // local-only token — no cloud copy
    if (img.indexOf('data:') === 0) {
      try { return await TapptDB.uploadMedia(img); } catch (e) { return null; }
    }
    return img;                                          // already a URL
  }
  function tsToIso(ts) { try { return ts ? new Date(ts).toISOString() : new Date().toISOString(); } catch (e) { return new Date().toISOString(); } }
  function isoToTs(iso) { var n = iso ? Date.parse(iso) : NaN; return isNaN(n) ? Date.now() : n; }

  function commentToRow(momentId, c, parentClientId, uid) {
    return {
      moment_id: momentId,
      client_id: c.id,
      parent_client_id: parentClientId || null,
      author: uid || null,
      author_name: c.name || null,
      author_handle: c.handle || null,
      // fold the avatar descriptor into the single text column
      author_avatar: JSON.stringify({ a: c.avatar || null, c: c.avatarColor || null, cv: c.cover || null, ck: c.coverKind || null }),
      text: c.text || '',
      likes: c.likes || 0,
      created_at: tsToIso(c.ts),
    };
  }
  function rowToComment(row) {
    var av = {}; try { av = JSON.parse(row.author_avatar || '{}') || {}; } catch (e) {}
    return {
      id: row.client_id || row.id,
      name: row.author_name || 'Someone',
      handle: row.author_handle || '',
      avatar: av.a || undefined, avatarColor: av.c || undefined, cover: av.cv || undefined, coverKind: av.ck || undefined,
      text: row.text || '', ts: isoToTs(row.created_at),
      likes: row.likes || 0, liked: false, replies: [],
    };
  }
  // flat comment rows -> nested [{…, replies:[]}] tree, oldest-first (matches array append order)
  function buildCommentTree(rows) {
    var byId = {}, top = [];
    rows.forEach(function (r) { byId[r.client_id] = rowToComment(r); });
    rows.forEach(function (r) {
      var node = byId[r.client_id];
      if (r.parent_client_id && byId[r.parent_client_id]) byId[r.parent_client_id].replies.push(node);
      else top.push(node);
    });
    var byTs = function (a, b) { return a.ts - b.ts; };
    top.sort(byTs); top.forEach(function (n) { n.replies.sort(byTs); });
    return top;
  }
  // nested tree -> flat rows (parents + replies)
  function flattenComments(momentId, tree, uid) {
    var out = [];
    (tree || []).forEach(function (c) {
      out.push(commentToRow(momentId, c, null, c.authorUid || uid));
      (c.replies || []).forEach(function (r) { out.push(commentToRow(momentId, r, c.id, r.authorUid || uid)); });
    });
    return out;
  }

  TapptDB.moments = {
    /* list a user's moments (with comment trees), newest first */
    async list(uid) {
      if (!sb || !uid) return null;
      var mr = await sb.from('moments').select('*').eq('owner', uid).order('created_at', { ascending: false });
      if (mr.error) throw mr.error;
      var moments = (mr.data || []).map(function (m) {
        return { id: m.id, cap: m.caption || '', img: m.img || null, imgKind: m.img_kind || 'image', taps: m.taps || 0, ts: isoToTs(m.created_at), editedTs: m.edited_ts ? isoToTs(m.edited_ts) : 0, comments: [] };
      });
      if (!moments.length) return moments;
      var ids = moments.map(function (m) { return m.id; });
      var cr = await sb.from('moment_comments').select('*').in('moment_id', ids);
      if (!cr.error && cr.data) {
        var byMoment = {};
        cr.data.forEach(function (row) { (byMoment[row.moment_id] = byMoment[row.moment_id] || []).push(row); });
        moments.forEach(function (m) { m.comments = buildCommentTree(byMoment[m.id] || []); });
      }
      return moments;
    },
    /* resolve a handle -> owner id, then list (for the public profile) */
    async listByHandle(handle) {
      if (!sb || !handle) return null;
      var pr = await sb.from('profiles').select('id').eq('handle', handle).maybeSingle();
      if (pr.error || !pr.data) return null;
      return this.list(pr.data.id);
    },
    /* insert a new moment (id is a client-generated uuid so edits/deletes match) */
    async create(moment, uid) {
      if (!sb || !uid) return moment;
      var url = await momentMedia(moment.img);
      var row = { id: moment.id, owner: uid, caption: moment.cap || '', img: url, img_kind: moment.imgKind || 'image', taps: moment.taps || 0, created_at: tsToIso(moment.ts) };
      var r = await sb.from('moments').insert(row);
      if (r.error) throw r.error;
      return Object.assign({}, moment, { img: url || moment.img });
    },
    /* patch caption / media / taps / editedTs */
    async update(id, patch, uid) {
      if (!sb || !id) return;
      var row = {};
      if (patch.cap !== undefined) row.caption = patch.cap;
      if (patch.imgKind !== undefined) row.img_kind = patch.imgKind;
      if (patch.taps !== undefined) row.taps = patch.taps;
      if (patch.editedTs !== undefined) row.edited_ts = patch.editedTs ? tsToIso(patch.editedTs) : null;
      if (patch.img !== undefined) row.img = await momentMedia(patch.img);
      if (!Object.keys(row).length) return;
      var r = await sb.from('moments').update(row).eq('id', id);
      if (r.error) throw r.error;
    },
    async remove(id) {
      if (!sb || !id) return;
      await sb.from('moment_comments').delete().eq('moment_id', id);
      var r = await sb.from('moments').delete().eq('id', id);
      if (r.error) throw r.error;
    },
    /* reconcile a moment's full comment tree (owner side): upsert present rows, delete the rest */
    async syncComments(momentId, tree, uid) {
      if (!sb || !momentId) return;
      var rows = flattenComments(momentId, tree, uid);
      if (rows.length) {
        var up = await sb.from('moment_comments').upsert(rows, { onConflict: 'moment_id,client_id' });
        if (up.error) throw up.error;
      }
      // delete rows no longer in the tree
      var keep = {}; rows.forEach(function (r) { keep[r.client_id] = 1; });
      var ex = await sb.from('moment_comments').select('client_id').eq('moment_id', momentId);
      if (!ex.error && ex.data) {
        var gone = ex.data.map(function (r) { return r.client_id; }).filter(function (cid) { return cid && !keep[cid]; });
        if (gone.length) await sb.from('moment_comments').delete().eq('moment_id', momentId).in('client_id', gone);
      }
    },
    /* insert ONE comment/reply (visitor side — never deletes others' rows) */
    async addComment(momentId, comment, parentClientId, uid) {
      if (!sb || !momentId) return comment;
      var row = commentToRow(momentId, comment, parentClientId, uid);
      var r = await sb.from('moment_comments').upsert(row, { onConflict: 'moment_id,client_id' });
      if (r.error) throw r.error;
      return comment;
    },
  };

  /* =========================================================
     COLLECTIONS — connections / leads / tips / cards on their own
     tables (instead of the profile blob). Each def maps the app's
     camelCase object <-> a snake_case row. Mutations are granular
     (upsert one / remove one / clear) so cross-user inserts (a
     visitor's exchange-lead or tip) are never reconcile-deleted.
     ========================================================= */
  var COLLECTION_DEFS = {
    connections: {
      table: 'connections',
      toRow: function (c, uid) {
        return {
          id: c.id, owner: uid,
          name: c.name || null, handle: c.handle || null, persona: c.persona || null,
          place: c.place || null, met_when: c.when || null,
          got: c.got || [], tags: c.tags || [], sent_back: !!c.sentBack,
          photo: c.photo || null, accent: c.accent || null,
          lat: (c.lat != null ? c.lat : null), lng: (c.lng != null ? c.lng : null),
          extra: { icon: c.icon || null, company: c.company || null, role: c.role || null, jobTitle: c.jobTitle || null, email: c.email || null, phone: c.phone || null, website: c.website || null, linkedin: c.linkedin || null, answer: c.answer || null, custom: c.custom || [], via: c.via || null, location: c.location || null, source: c.source || null, note: c.note || null, stage: c.stage || null, notes: c.notes || null, nextAction: c.nextAction || null, potential: c.potential || null, ts: c.ts || null },
        };
      },
      fromRow: function (r) {
        var e = r.extra || {};
        return Object.assign({
          id: r.id, name: r.name, handle: r.handle, persona: r.persona,
          place: r.place, when: r.met_when, got: r.got || [], tags: r.tags || [],
          sentBack: !!r.sent_back, photo: r.photo, accent: r.accent, lat: r.lat, lng: r.lng,
        }, { icon: e.icon || undefined, company: e.company || undefined, role: e.role || undefined, jobTitle: e.jobTitle || undefined, email: e.email || undefined, phone: e.phone || undefined, website: e.website || undefined, linkedin: e.linkedin || undefined, answer: e.answer || undefined, custom: e.custom || undefined, via: e.via || undefined, location: e.location || undefined, source: e.source || undefined, note: e.note || undefined, stage: e.stage || undefined, notes: e.notes || undefined, nextAction: e.nextAction || undefined, potential: e.potential || undefined, ts: e.ts || undefined });
      },
    },
    leads: {
      table: 'leads',
      toRow: function (l, uid) {
        return {
          id: l.id, owner: uid,
          name: l.name || null, email: l.email || null, phone: l.phone || null,
          company: l.company || null, job_title: l.jobTitle || null, linkedin: l.linkedin || null,
          answer: l.answer || null, photo: l.photo || null,
          custom: l.custom || [], location: l.location || null,
          source: l.source || null, stage: l.stage || 'new',
          potential: (l.potential != null && l.potential !== '') ? String(l.potential) : null,
          notes: l.notes || null, next_action: l.nextAction || null, met_at: l.metAt || null,
          created_at: tsToIso(l.ts),
        };
      },
      fromRow: function (r) {
        return {
          id: r.id, name: r.name, email: r.email, phone: r.phone, company: r.company,
          jobTitle: r.job_title, linkedin: r.linkedin, answer: r.answer, photo: r.photo,
          custom: r.custom || [], location: r.location || null, source: r.source,
          stage: r.stage || 'new', potential: r.potential || '', notes: r.notes || '',
          nextAction: r.next_action || null, metAt: r.met_at || '', ts: isoToTs(r.created_at),
        };
      },
    },
    tips: {
      table: 'tips',
      toRow: function (t, uid) {
        var note = (t.from ? String(t.from) + ' — ' : '') + (t.msg || t.note || '');
        return { id: t.id, owner: uid, amount: Number(t.amount) || 0, note: note || null, created_at: tsToIso(t.ts) };
      },
      fromRow: function (r) { return { id: r.id, amount: Number(r.amount) || 0, note: r.note || '', ts: isoToTs(r.created_at) }; },
    },
    cards: {
      table: 'cards',
      toRow: function (c, uid) {
        return {
          id: c.id, owner: uid, card_type: c.cardType || c.type || null, card_img: c.cardImg || c.img || null,
          status: c.status || null, paired: !!c.paired, persona: c.persona || null,
          paired_at: c.pairedAt ? tsToIso(c.pairedAt) : null,
        };
      },
      fromRow: function (r) {
        return { id: r.id, cardType: r.card_type, cardImg: r.card_img, status: r.status, paired: !!r.paired, persona: r.persona, pairedAt: r.paired_at ? isoToTs(r.paired_at) : 0 };
      },
    },
  };

  TapptDB.collections = {
    defs: COLLECTION_DEFS,
    /* list a collection for an owner (newest first). null if unknown/offline. */
    async list(name, uid) {
      var def = COLLECTION_DEFS[name];
      if (!sb || !uid || !def) return null;
      var r = await sb.from(def.table).select('*').eq('owner', uid).order('created_at', { ascending: false });
      if (r.error) throw r.error;
      return (r.data || []).map(def.fromRow);
    },
    /* resolve handle -> owner, then list (public side) */
    async listByHandle(name, handle) {
      if (!sb || !handle) return null;
      var pr = await sb.from('profiles').select('id').eq('handle', handle).maybeSingle();
      if (pr.error || !pr.data) return null;
      return this.list(name, pr.data.id);
    },
    /* insert OR update one item (id = client-generated uuid, matches the table PK) */
    async upsert(name, item, uid) {
      var def = COLLECTION_DEFS[name];
      if (!sb || !uid || !def) return item;
      var row = def.toRow(item, uid);
      var r = await sb.from(def.table).upsert(row, { onConflict: 'id' });
      if (r.error) throw r.error;
      return item;
    },
    async remove(name, id) {
      var def = COLLECTION_DEFS[name];
      if (!sb || !def || !id) return;
      var r = await sb.from(def.table).delete().eq('id', id);
      if (r.error) throw r.error;
    },
    async clearAll(name, uid) {
      var def = COLLECTION_DEFS[name];
      if (!sb || !def || !uid) return;
      var r = await sb.from(def.table).delete().eq('owner', uid);
      if (r.error) throw r.error;
    },
  };

  /* ---- ANALYTICS + CARD CLAIM ---- */
  TapptDB.logEvent = async function (profileId, ev) {
    if (!sb || !profileId) return;
    try {
      await sb.from('events').insert(Object.assign({ profile_id: profileId }, ev));
    } catch (e) { /* analytics is best-effort, never block UX */ }
  };
  // resolve a card code (works logged-out). returns {code,claimed,persona,owner_handle} or null
  TapptDB.resolveCard = async function (code) {
    if (!sb || !code) return null;
    var r = await sb.rpc('resolve_card', { p_code: code });
    if (r.error) return null;
    return (r.data && r.data[0]) || null;
  };
  // claim an (un)seeded card to the current signed-in user
  TapptDB.claimCard = async function (code, persona) {
    if (!sb) throw new Error('offline');
    var r = await sb.rpc('claim_card', { p_code: code, p_persona: persona || null });
    if (r.error) throw r.error;
    return r.data;
  };
  // aggregate stats for the owner dashboard (real events). Returns null if offline/none.
  TapptDB.analytics = async function (uid, sinceIso) {
    if (!sb || !uid) return null;
    var r = await sb.from('events').select('kind,source,card_id,persona,country,created_at')
      .eq('profile_id', uid).gte('created_at', sinceIso).order('created_at', { ascending: false }).limit(5000);
    if (r.error) return null;
    return r.data || [];
  };

  /* ---- ORDERS (Shopify → Supabase via shopify-order-webhook) ----
     Normalises a raw order row to the shape the dashboard tracker + CEO list use.
     Defensive aliasing because the webhook's column names may vary. */
  function normOrder(o) {
    if (!o) return null;
    var raw = String(o.status || o.fulfillment_status || o.financial_status || 'placed').toLowerCase();
    var step = 'placed';
    if (/cancel|refund|void/.test(raw)) step = 'cancelled';
    else if (/deliver/.test(raw)) step = 'delivered';
    else if (/fulfil|ship|complete|out_for/.test(raw)) step = 'shipped';
    var items = o.items || o.line_items || [];
    return {
      id: o.id,
      orderNo: o.order_number || o.order_name || o.name || o.order_id || null,
      email: o.email || o.buyer_email || o.customer_email || null,
      ref: o.ref || o.referrer || o.referrer_handle || null,
      total: o.total != null ? o.total : (o.total_price != null ? o.total_price : o.amount),
      currency: o.currency || 'GBP',
      status: step, rawStatus: raw,
      items: items,
      productTitle: (items[0] && (items[0].title || items[0].name)) || o.product_title || o.title || 'Tappt Card',
      createdAt: o.created_at || o.inserted_at || o.placed_at || null,
      shippedAt: o.shipped_at || o.fulfilled_at || null,
      tracking: o.tracking_url || o.tracking || null,
    };
  }
  // the signed-in buyer's own orders (RLS: buyer/referrer read). null = offline/none.
  TapptDB.listOrders = async function (email) {
    if (!sb) return null;
    try {
      var q = sb.from('orders').select('*').order('created_at', { ascending: false }).limit(50);
      if (email) q = q.eq('email', email);
      var r = await q;
      if (r.error) return null;
      return (r.data || []).map(normOrder);
    } catch (e) { return null; }
  };
  // ALL orders, for the CEO console. null = offline/none.
  TapptDB.listAllOrders = async function (limit) {
    if (!sb) return null;
    try {
      var r = await sb.from('orders').select('*').order('created_at', { ascending: false }).limit(limit || 200);
      if (r.error) return null;
      return (r.data || []).map(normOrder);
    } catch (e) { return null; }
  };

  /* ---- STRIPE CONNECT PAYOUTS (creator gets paid) ----
     connectOnboard() → returns a Stripe-hosted onboarding URL to open.
     connectStatus()  → refreshes payouts_enabled from Stripe, returns state
                        (+ an Express dashboard URL once live). */
  TapptDB.connectOnboard = async function () {
    if (!sb) throw new Error('offline');
    var r = await sb.functions.invoke('connect-onboard', { body: { action: 'onboard' } });
    if (r.error) throw r.error;
    return r.data; // { account, url }
  };
  TapptDB.connectStatus = async function () {
    if (!sb) return null;
    var r = await sb.functions.invoke('connect-onboard', { body: { action: 'status' } });
    if (r.error) return null;
    return r.data; // { account, payouts_enabled, onboarded, dashboard_url, requirements }
  };
  /* ---- TIPS: real Stripe Checkout (Connect destination charge) ----
     Returns { url } to redirect the visitor to Stripe's hosted checkout, or null
     if the creator can't take tips yet (no connected account) → caller falls back
     to the recorded-only flow. Money lands in the creator's Connect account minus
     the platform fee; a checkout.session.completed webhook records the tip. */
  TapptDB.startTipCheckout = async function (opts) {
    if (!sb || !opts || !opts.handle || !opts.amount) return null;
    try {
      var r = await sb.functions.invoke('create-tip-checkout', { body: {
        handle: opts.handle,
        amount_pence: Math.round(opts.amount * 100),
        note: opts.note || '',
        return_url: location.origin + location.pathname,
      } });
      if (r.error || !r.data || !r.data.url) return null;
      return r.data; // { url }
    } catch (e) { return null; }
  };

  window.TapptDB = TapptDB;
})();