// Zyro Supabase wrapper — auth + orders + profile helpers + React context.
// Exports: window.zsb (client wrapper) and window.AuthContext / window.useAuthStore.
//
// Design notes:
// - useAuthStore is defined ALWAYS, even if Supabase is unconfigured, so the
//   hook rule "always call in same order" holds in app.jsx.
// - We subscribe to onAuthStateChange exactly once and clean up on unmount.
// - State updates skip no-op user changes (TOKEN_REFRESHED) to avoid render churn.
// - A 5s safety timeout flips `ready` to true even if Supabase never responds,
//   so the UI never gets stuck on "Laden…".
// - Password recovery uses a separate static page (reset-password.html) so the
//   `#access_token=...&type=recovery` fragment doesn't collide with the hash router.

(() => {
  const cfg = window.ZYRO_CONFIG || {};
  let client = null;
  let initError = null;

  try {
    if (cfg.isConfigured && window.supabase && window.supabase.createClient) {
      client = window.supabase.createClient(cfg.SUPABASE_URL, cfg.SUPABASE_ANON_KEY, {
        auth: {
          persistSession: true,
          autoRefreshToken: true,
          detectSessionInUrl: true,
          flowType: "implicit",
        },
      });
    } else if (!cfg.isConfigured) {
      initError = "Supabase is nog niet geconfigureerd (zie SETUP.md).";
    } else if (!window.supabase) {
      initError = "Supabase SDK kon niet geladen worden.";
    }
  } catch (e) {
    initError = (e && e.message) || String(e);
    client = null;
  }

  const STATUS_LABELS = {
    in_behandeling: "In behandeling",
    geaccepteerd: "Geaccepteerd",
    geweigerd: "Geweigerd",
    klaar_voor_ophalen: "Klaar voor ophalen",
    onderweg: "Onderweg",
    afgerond: "Afgerond",
    geannuleerd: "Geannuleerd",
    paid: "Betaald",
    payment_paid: "Betaling gelukt",
    payment_canceled: "Betaling geannuleerd",
    payment_expired: "Betaling verlopen",
    payment_failed: "Betaling mislukt",
    payment_pending: "Betaling wordt gecontroleerd",
  };
  const STATUS_LABELS_EN = {
    in_behandeling: "Pending",
    geaccepteerd: "Accepted",
    geweigerd: "Rejected",
    klaar_voor_ophalen: "Ready for pickup",
    onderweg: "On the way",
    afgerond: "Completed",
    geannuleerd: "Cancelled",
    paid: "Paid",
    payment_paid: "Payment successful",
    payment_canceled: "Payment canceled",
    payment_expired: "Payment expired",
    payment_failed: "Payment failed",
    payment_pending: "Payment is being verified",
  };
  // Per-lang status label resolver. Falls back to NL if no override exists.
  const statusLabel = (status, lang) => {
    if (lang === "en" && STATUS_LABELS_EN[status]) return STATUS_LABELS_EN[status];
    return STATUS_LABELS[status] || status || "—";
  };
  const STATUS_ORDER = [
    "in_behandeling","paid","geaccepteerd","klaar_voor_ophalen","onderweg","afgerond","geweigerd","geannuleerd",
  ];
  const STATUS_TONE = {
    in_behandeling:"neutral", geaccepteerd:"ok", klaar_voor_ophalen:"ok",
    onderweg:"ok", afgerond:"done", betaald:"done", paid:"done", payment_paid:"done",
    payment_pending:"neutral", geweigerd:"bad", geannuleerd:"bad",
    payment_canceled:"bad", payment_expired:"bad", payment_failed:"bad",
  };
  const PAYMENT_STATUS_LABELS = {
    pending_payment: "Betaling wordt gecontroleerd",
    pending_cash: "Wacht op contante betaling",
    open: "Betaling open",
    pending: "Betaling wordt gecontroleerd",
    authorized: "Betaling geautoriseerd",
    paid: "Betaald",
    canceled: "Geannuleerd",
    expired: "Verlopen",
    failed: "Mislukt",
  };
  const PAYMENT_STATUS_LABELS_EN = {
    pending_payment: "Payment is being verified",
    pending_cash: "Awaiting cash payment",
    open: "Payment open",
    pending: "Payment is being verified",
    authorized: "Payment authorized",
    paid: "Paid",
    canceled: "Canceled",
    expired: "Expired",
    failed: "Failed",
  };
  const paymentStatusLabel = (status, lang) => {
    const s = status || "pending_payment";
    if (lang === "en" && PAYMENT_STATUS_LABELS_EN[s]) return PAYMENT_STATUS_LABELS_EN[s];
    return PAYMENT_STATUS_LABELS[s] || s;
  };

  // Map Supabase error strings to Dutch. Falls back to original message.
  function translateError(e) {
    if (!e) return "Onbekende fout";
    const raw = (e && (e.message || e.error_description || e.error || e.msg)) || String(e);
    const m = raw.toLowerCase();
    if (m.includes("invalid login credentials") || m.includes("invalid credentials"))
      return "E-mail of wachtwoord onjuist. Heb je je e-mailadres al bevestigd?";
    if (m.includes("email not confirmed"))
      return "Bevestig eerst je e-mailadres via de link die we je hebben gestuurd.";
    if (m.includes("user already registered") || m.includes("already registered") || m.includes("already exists"))
      return "Er bestaat al een account met dit e-mailadres. Probeer in te loggen of vraag een nieuw wachtwoord aan.";
    if (m.includes("password should be at least") || m.includes("weak password") || m.includes("password must"))
      return "Wachtwoord moet minimaal 8 tekens zijn.";
    if (m.includes("rate limit") || m.includes("too many requests"))
      return "Te veel pogingen. Probeer het over een paar minuten opnieuw.";
    if (m.includes("network") || m.includes("failed to fetch") || m.includes("fetch failed"))
      return "Geen verbinding met de server. Controleer je internet en probeer opnieuw.";
    if (m.includes("invalid email") || m.includes("email address is invalid"))
      return "Vul een geldig e-mailadres in.";
    if (m.includes("new password should be different"))
      return "Nieuw wachtwoord moet anders zijn dan je huidige wachtwoord.";
    if (m.includes("auth session missing") || m.includes("not authenticated"))
      return "Je bent niet (meer) ingelogd. Log opnieuw in.";
    if (m.includes("jwt expired"))
      return "Je sessie is verlopen. Log opnieuw in.";
    return raw;
  }

  // ---------- Compute the URL of the static reset-password page ----------
  // location.pathname may be "/" or "/index.html" — strip the file segment.
  function resetPasswordUrl() {
    const base = location.origin + (location.pathname.replace(/[^/]+$/, "") || "/");
    return base + "reset-password.html";
  }

  // ---------- SESSION / TOKEN HELPERS ----------
  // Strict helpers used by every authed fetch (Mollie checkout, resend confirmation, …).
  // The Supabase SDK *can* hand back stale or partial sessions; we defensively
  // reject anything that doesn't smell like a real JWT before sending it to /api.
  async function getSupabaseSession() {
    const c = client || (window.ZSB && window.ZSB.client) || window.supabaseClient || window.supabase;
    if (!c || !c.auth || typeof c.auth.getSession !== "function") return null;
    try {
      const result = await c.auth.getSession();
      return (result && result.data && result.data.session) || null;
    } catch (_) {
      return null;
    }
  }

  async function getSupabaseAccessToken() {
    const session = await getSupabaseSession();
    const token = session && session.access_token;
    if (
      !token ||
      typeof token !== "string" ||
      token === "undefined" ||
      token === "null" ||
      token.split(".").length !== 3
    ) {
      return null;
    }
    return token;
  }

  const api = {
    isConfigured: !!client,
    client,
    initError,
    translateError,
    resetPasswordUrl,
    getSession: getSupabaseSession,
    getAccessToken: getSupabaseAccessToken,

    // ---------- AUTH ----------
    async signUp({ email, password, fullName, phone }) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      try {
        const { data, error } = await client.auth.signUp({
          email, password,
          options: {
            data: { full_name: fullName || "", phone: phone || "" },
            emailRedirectTo: location.origin + (location.pathname.replace(/[^/]+$/, "") || "/") + "index.html#login",
          },
        });
        if (error) return { error: translateError(error) };
        return { data };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    async signIn({ email, password }) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      try {
        const { data, error } = await client.auth.signInWithPassword({ email, password });
        if (error) return { error: translateError(error) };
        return { data };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    async signOut() {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      try {
        const { error } = await client.auth.signOut();
        if (error) return { error: translateError(error) };
        return { data: true };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    async resetPassword({ email }) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      try {
        const { error } = await client.auth.resetPasswordForEmail(email, {
          redirectTo: resetPasswordUrl(),
        });
        if (error) return { error: translateError(error) };
        return { data: true };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    async updatePassword(newPassword) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      if (!newPassword || newPassword.length < 8) return { error: "Wachtwoord moet minimaal 8 tekens zijn." };
      try {
        const { data, error } = await client.auth.updateUser({ password: newPassword });
        if (error) return { error: translateError(error) };
        return { data };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    // ---------- PROFILE ----------
    async getProfile(userId) {
      if (!client) return { data: null };
      try {
        const { data, error } = await client.from("profiles").select("*").eq("id", userId).maybeSingle();
        if (error) return { error: translateError(error) };
        return { data };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    async upsertProfile({ id, full_name, phone }) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      try {
        const { data, error } = await client.from("profiles")
          .upsert({ id, full_name, phone }, { onConflict: "id" }).select().maybeSingle();
        if (error) return { error: translateError(error) };
        return { data };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    // ---------- ORDERS ----------
    async createOrder(payload) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      try {
        const { data, error } = await client.from("orders").insert(payload).select().maybeSingle();
        if (error) return { error: translateError(error) };
        if (data && data.id) {
          // Best-effort event log; ignore failure.
          client.from("order_events").insert({
            order_id: data.id, status: payload.status || "in_behandeling", note: "order_created_by_customer",
          }).then(() => {}, () => {});
        }
        return { data };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    async getMyOrders(userId) {
      if (!client || !userId) return { data: [] };
      try {
        const { data, error } = await client.from("orders")
          .select("*").eq("user_id", userId).order("created_at", { ascending: false });
        if (error) return { error: translateError(error), data: [] };
        return { data: data || [] };
      } catch (e) {
        return { error: translateError(e), data: [] };
      }
    },

    async getOrder(id) {
      if (!client || !id) return { data: null };
      try {
        const { data, error } = await client.from("orders")
          .select("*").eq("id", id).maybeSingle();
        if (error) return { error: translateError(error), data: null };
        return { data: data || null };
      } catch (e) {
        return { error: translateError(e), data: null };
      }
    },

    async getAllOrders() {
      if (!client) return { data: [] };
      try {
        const { data, error } = await client.from("orders")
          .select("*").order("created_at", { ascending: false });
        if (error) return { error: translateError(error), data: [] };
        return { data: data || [] };
      } catch (e) {
        return { error: translateError(e), data: [] };
      }
    },

    // Admin live order updates via Supabase Realtime. Subscribes to INSERT and
    // UPDATE on public.orders and invokes the supplied callbacks. The caller is
    // expected to RE-FETCH through getAllOrders() on each event — the realtime
    // payload is only a TRIGGER, never trusted as the source of truth and never
    // injected into the UI. Uses the existing anon client + the logged-in
    // session (RLS applies); NEVER a service role. Returns an unsubscribe()
    // function, or null when realtime is unavailable (caller falls back to
    // polling). Adds no Vercel function — realtime runs entirely client-side.
    subscribeOrders(handlers) {
      if (!client || typeof client.channel !== "function") return null;
      const h = handlers || {};
      const onInsert = typeof h.onInsert === "function" ? h.onInsert : function () {};
      const onUpdate = typeof h.onUpdate === "function" ? h.onUpdate : function () {};
      const onStatus = typeof h.onStatus === "function" ? h.onStatus : function () {};

      // Best-effort: hand Realtime the admin's JWT so RLS lets order events
      // through. Fire-and-forget; fallback polling covers any gap.
      try {
        if (client.realtime && typeof client.realtime.setAuth === "function") {
          getSupabaseAccessToken().then(function (tok) {
            if (tok) { try { client.realtime.setAuth(tok); } catch (_) {} }
          }).catch(function () {});
        }
      } catch (_) {}

      let channel = null;
      try {
        channel = client
          .channel("admin-orders-" + Date.now())
          .on("postgres_changes", { event: "INSERT", schema: "public", table: "orders" }, function (payload) { try { onInsert(payload); } catch (_) {} })
          .on("postgres_changes", { event: "UPDATE", schema: "public", table: "orders" }, function (payload) { try { onUpdate(payload); } catch (_) {} })
          .subscribe(function (status) { try { onStatus(status); } catch (_) {} });
      } catch (e) {
        return null;
      }

      return function unsubscribe() {
        try { if (channel) client.removeChannel(channel); } catch (_) {}
      };
    },

    async updateOrder(id, patch) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      try {
        if (patch && patch.status === "paid") {
          return { error: "Betaalstatus kan alleen server-side door de betaalprovider worden bevestigd." };
        }
        // Separate `note` (history-only, goes to order_events) from the
        // actual orders columns. The orders table has no `note` column;
        // sending it would 400 against PostgREST.
        const { note, ...orderPatch } = patch || {};
        const { data, error } = await client.from("orders")
          .update({ ...orderPatch, updated_at: new Date().toISOString() })
          .eq("id", id).select().maybeSingle();
        if (error) return { error: translateError(error) };
        // Log status changes (or any save with a note) in the history table.
        if (orderPatch.status || note) {
          client.from("order_events").insert({
            order_id: id,
            status: orderPatch.status || (data && data.status) || "in_behandeling",
            note: note || null,
          }).then(() => {}, () => {});
        }
        return { data };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    async createMolliePayment(orderId, lang) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      if (!orderId) return { error: "Order-ID ontbreekt." };
      try {
        const token = await getSupabaseAccessToken();
        if (!token) {
          return { error: lang === "en" ? "Sign in to pay securely." : "Log in om veilig te betalen." };
        }
        const resp = await fetch("/api/create-mollie-payment", {
          method: "POST",
          headers: {
            "Content-Type": "application/json",
            Authorization: "Bearer " + token,
          },
          body: JSON.stringify({
            order_id: orderId,
            locale: lang === "en" ? "en_GB" : "nl_NL",
            // The order already exists and was confirmed at checkout time; send the
            // flags so a (future) "retry payment" passes the server confirmation gate.
            confirmed_details: true,
            accepted_terms: true,
          }),
        });
        const json = await resp.json().catch(() => ({}));
        if (!resp.ok) return { error: json.error || "Betaling kon niet gestart worden." };
        return { checkoutUrl: json.checkoutUrl };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    // Triggers /api/mark-order-ready-for-pickup. Server-side: sets status to
    // klaar_voor_ophalen, stamps pickup_ready_at + (on email success)
    // pickup_ready_sent_at, and sends the pickup-ready email. Idempotent —
    // a second call after pickup_ready_sent_at is set just returns
    // { alreadySent: true } without re-mailing.
    async markOrderReadyForPickup(orderId, pickupNote) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      if (!orderId) return { error: "Order-ID ontbreekt." };
      try {
        const token = await getSupabaseAccessToken();
        if (!token) return { error: "Je bent niet (meer) ingelogd. Log opnieuw in." };
        const resp = await fetch("/api/mark-order-ready-for-pickup", {
          method: "POST",
          headers: { "Content-Type": "application/json", Authorization: "Bearer " + token },
          body: JSON.stringify({
            order_id: orderId,
            pickup_note: pickupNote || null,
          }),
        });
        const json = await resp.json().catch(() => ({}));
        if (!resp.ok) return { error: json.error || "Ophaalmail kon niet verstuurd worden." };
        return { success: true, alreadySent: !!json.alreadySent, message: json.message || null };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    // Triggers /api/send-cash-confirmation (customer, ownership-gated). Used by
    // the checkout when the customer chooses "contant betalen bij ophalen".
    // Server-side: re-validates the cash rules (pickup + total < €3000), stamps
    // payment_method="cash" / payment_status="pending_cash", mints a
    // confirmation_number if missing, and sends the "bestelling ontvangen –
    // contant betalen" email. Starts NO Mollie payment. Idempotent on
    // cash_confirmation_sent_at.
    async sendCashOrderConfirmation(orderId, extra) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      if (!orderId) return { error: "Order-ID ontbreekt." };
      try {
        const token = await getSupabaseAccessToken();
        if (!token) return { error: "Je bent niet (meer) ingelogd. Log opnieuw in." };
        // `extra` carries the checkout confirmation flags (confirmed_details,
        // accepted_terms); the server re-validates them before creating the order.
        const extraBody = (extra && typeof extra === "object") ? extra : {};
        const resp = await fetch("/api/send-cash-confirmation", {
          method: "POST",
          headers: { "Content-Type": "application/json", Authorization: "Bearer " + token },
          body: JSON.stringify({ order_id: orderId, ...extraBody }),
        });
        const json = await resp.json().catch(() => ({}));
        if (!resp.ok) return { error: json.error || "Bevestiging kon niet verstuurd worden." };
        return {
          success: true,
          alreadySent: !!json.alreadySent,
          confirmationNumber: json.confirmation_number || null,
          message: json.message || null,
        };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    // Triggers /api/mark-cash-received (admin, admin_users-gated). For a cash
    // PICKUP order this single action confirms the cash AND auto-completes the
    // order server-side: validates cash_amount_received (>= total), recomputes
    // the change, sets payment_status="paid", status="afgerond", completed_at,
    // review_requested_at, invoice_number + the cash bookkeeping, and sends the
    // existing completed/invoice email (with PDF). No separate cash-receipt mail.
    // `email_sent:false` means the order is afgerond + paid but the invoice mail
    // failed and can be resent. Idempotent.
    async markCashReceived(orderId, cashAmountReceived, cashNote) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      if (!orderId) return { error: "Order-ID ontbreekt." };
      try {
        const token = await getSupabaseAccessToken();
        if (!token) return { error: "Je bent niet (meer) ingelogd. Log opnieuw in." };
        const resp = await fetch("/api/mark-cash-received", {
          method: "POST",
          headers: { "Content-Type": "application/json", Authorization: "Bearer " + token },
          body: JSON.stringify({
            order_id: orderId,
            cash_amount_received: cashAmountReceived,
            cash_note: cashNote || null,
          }),
        });
        const json = await resp.json().catch(() => ({}));
        if (!resp.ok) return { error: json.error || "Contante betaling kon niet bevestigd worden." };
        return {
          success: true,
          alreadyReceived: !!json.alreadyReceived,
          alreadyCompleted: !!json.alreadyCompleted,
          message: json.message || null,
          status: json.status || null,
          cashAmountReceived: json.cash_amount_received != null ? json.cash_amount_received : null,
          cashChangeGiven: json.cash_change_given != null ? json.cash_change_given : null,
          cashReceivedByName: json.cash_received_by_name || null,
          cashReceivedByEmail: json.cash_received_by_email || null,
          invoiceNumber: json.invoice_number || null,
          completedEmailSentAt: json.completed_email_sent_at || null,
          emailSent: !!json.email_sent,
        };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    // Resend the completed/invoice email for an already-afgerond cash order.
    // Reuses /api/mark-cash-received with only { order_id } — the server detects
    // the order is already confirmed and (re)sends the invoice mail if it never
    // went out, stamping completed_email_sent_at. No new serverless function.
    async resendCashPaymentReceived(orderId) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      if (!orderId) return { error: "Order-ID ontbreekt." };
      try {
        const token = await getSupabaseAccessToken();
        if (!token) return { error: "Je bent niet (meer) ingelogd. Log opnieuw in." };
        const resp = await fetch("/api/mark-cash-received", {
          method: "POST",
          headers: { "Content-Type": "application/json", Authorization: "Bearer " + token },
          body: JSON.stringify({ order_id: orderId }),
        });
        const json = await resp.json().catch(() => ({}));
        if (!resp.ok) return { error: json.error || "Factuurmail kon niet verzonden worden." };
        return {
          success: true,
          emailSent: !!json.email_sent,
          completedEmailSentAt: json.completed_email_sent_at || null,
          message: json.message || null,
        };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    // Google Places (New) autocomplete proxy. The frontend never sees the
    // Google API key — the request lands at /api/address-autocomplete which
    // injects X-Goog-Api-Key server-side. Returns { predictions: [{ place_id, description }] }.
    async addressAutocomplete(input, sessionToken) {
      const text = String(input || "").trim();
      if (!text || text.length < 2) return { predictions: [] };
      try {
        const resp = await fetch("/api/address-autocomplete", {
          method: "POST",
          headers: { "Content-Type": "application/json" },
          body: JSON.stringify({ input: text, session_token: sessionToken || null }),
        });
        const json = await resp.json().catch(() => ({}));
        if (!resp.ok) return { error: json.error || "Adres-zoekservice niet beschikbaar.", predictions: [] };
        return { predictions: Array.isArray(json.predictions) ? json.predictions : [] };
      } catch (e) {
        return { error: translateError(e), predictions: [] };
      }
    },

    // Google Places (New) details proxy. Returns the structured delivery_*
    // fields that the checkout state machine + the orders insert want.
    async addressDetails(placeId, sessionToken) {
      const id = String(placeId || "").trim();
      if (!id) return { error: "place_id ontbreekt." };
      try {
        const resp = await fetch("/api/address-details", {
          method: "POST",
          headers: { "Content-Type": "application/json" },
          body: JSON.stringify({ place_id: id, session_token: sessionToken || null }),
        });
        const json = await resp.json().catch(() => ({}));
        if (!resp.ok) return { error: json.error || "Adres-detailservice niet beschikbaar." };
        return {
          ok: true,
          delivery_place_id: json.delivery_place_id,
          delivery_formatted_address: json.delivery_formatted_address,
          delivery_street: json.delivery_street,
          delivery_postcode: json.delivery_postcode,
          delivery_city: json.delivery_city,
          delivery_country: json.delivery_country,
          delivery_lat: json.delivery_lat,
          delivery_lng: json.delivery_lng,
        };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    // Triggers /api/mark-order-completed. Server-side: sets status to
    // "afgerond", stamps completed_at + review_requested_at, generates an
    // invoice PDF and sends the aftercare email with the PDF attached.
    // Idempotent on completed_email_sent_at — a second call returns
    // { alreadySent: true } without re-mailing.
    async markOrderCompleted(orderId) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      if (!orderId) return { error: "Order-ID ontbreekt." };
      try {
        const token = await getSupabaseAccessToken();
        if (!token) return { error: "Je bent niet (meer) ingelogd. Log opnieuw in." };
        const resp = await fetch("/api/mark-order-completed", {
          method: "POST",
          headers: { "Content-Type": "application/json", Authorization: "Bearer " + token },
          body: JSON.stringify({ order_id: orderId }),
        });
        const json = await resp.json().catch(() => ({}));
        if (!resp.ok) return { error: json.error || "Afgerond-mail kon niet verstuurd worden." };
        return {
          success: true,
          alreadySent: !!json.alreadySent,
          invoiceNumber: json.invoice_number || null,
          message: json.message || null,
        };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    // Triggers /api/mark-order-out-for-delivery. Server-side: sets status to
    // onderweg, stamps delivery_started_at + (on email success)
    // delivery_email_sent_at, optionally stores tracking_url, and sends the
    // shipment email.
    async markOrderOutForDelivery(orderId, trackingUrl) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      if (!orderId) return { error: "Order-ID ontbreekt." };
      try {
        const token = await getSupabaseAccessToken();
        if (!token) return { error: "Je bent niet (meer) ingelogd. Log opnieuw in." };
        const resp = await fetch("/api/mark-order-out-for-delivery", {
          method: "POST",
          headers: { "Content-Type": "application/json", Authorization: "Bearer " + token },
          body: JSON.stringify({
            order_id: orderId,
            tracking_url: trackingUrl || null,
          }),
        });
        const json = await resp.json().catch(() => ({}));
        if (!resp.ok) return { error: json.error || "Bezorgmail kon niet verstuurd worden." };
        return { success: true, alreadySent: !!json.alreadySent, message: json.message || null };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    async resendConfirmationEmail(orderId) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      if (!orderId) return { error: "Order-ID ontbreekt." };
      try {
        const token = await getSupabaseAccessToken();
        if (!token) return { error: "Je bent niet (meer) ingelogd. Log opnieuw in." };
        // Hits the new SDK-free endpoint. The old /api/send-order-confirmation
        // requires @supabase/supabase-js which isn't installed in production.
        const resp = await fetch("/api/resend-order-confirmation", {
          method: "POST",
          headers: {
            "Content-Type": "application/json",
            Authorization: "Bearer " + token,
          },
          body: JSON.stringify({ order_id: orderId }),
        });
        const json = await resp.json().catch(() => ({}));
        if (!resp.ok) return { error: json.error || "Email kon niet verstuurd worden." };
        return { success: true };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    async getOrderEvents(orderId) {
      if (!client || !orderId) return { data: [] };
      try {
        const { data, error } = await client.from("order_events")
          .select("*").eq("order_id", orderId).order("created_at", { ascending: true });
        if (error) return { error: translateError(error), data: [] };
        return { data: data || [] };
      } catch (e) {
        return { error: translateError(e), data: [] };
      }
    },

    // Download the customer's OWN invoice PDF for a paid + afgerond order.
    // Hits /api/invoice (token-gated, ownership-checked server-side), receives an
    // application/pdf blob and opens it in a new tab. There is NO public invoice
    // URL — the PDF is generated on the fly and never persisted client-side.
    async downloadCustomerInvoice(orderId) {
      if (!orderId) return { error: "Order-ID ontbreekt." };
      try {
        const token = await getSupabaseAccessToken();
        if (!token) return { error: "Je bent niet (meer) ingelogd. Log opnieuw in." };
        const resp = await fetch("/api/invoice", {
          method: "POST",
          headers: { "Content-Type": "application/json", Authorization: "Bearer " + token },
          body: JSON.stringify({ order_id: orderId }),
        });
        if (!resp.ok) {
          const j = await resp.json().catch(() => ({}));
          return { error: j.error || "Factuur kon niet geladen worden." };
        }
        const blob = await resp.blob();
        const url = URL.createObjectURL(blob);
        window.open(url, "_blank", "noopener");
        setTimeout(() => { try { URL.revokeObjectURL(url); } catch (_) {} }, 60000);
        return { success: true };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    // ---------- PRODUCTS (live prices) ----------
    async getProducts() {
      if (!client) return { data: [] };
      try {
        const { data, error } = await client.from("products")
          .select("*").order("slug", { ascending: true });
        if (error) return { error: translateError(error), data: [] };
        return { data: data || [] };
      } catch (e) {
        return { error: translateError(e), data: [] };
      }
    },

    async updateProduct(idOrSlug, patch) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      try {
        // Whitelist of editable columns to prevent stray fields hitting the table.
        const allowed = ["price", "old_price", "assembly_price", "active", "name", "short_name", "sort_order", "image_url", "description", "category", "icon", "availability_status"];
        const clean = {};
        for (const k of allowed) if (k in patch) clean[k] = patch[k];
        const key = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i.test(String(idOrSlug || "")) ? "id" : "slug";
        const { data, error } = await client.from("products")
          .update({ ...clean, updated_at: new Date().toISOString() })
          .eq(key, idOrSlug).select().maybeSingle();
        if (error) return { error: translateError(error) };
        return { data };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    // ============================================================
    // ADMIN / AUDIT / SETTINGS
    // ============================================================
    // All helpers gracefully return { schemaMissing:true } when admin_users
    // / audit_logs / site_settings tables don't exist yet — so /#admin can
    // render a "run SQL migration" hint instead of crashing.

    // Lookup current admin row by JWT email via the SECURITY DEFINER RPC
    // `public.get_my_admin_row()`. The RPC strips actor columns
    // (created_by/updated_by/deleted_by) so the affected user can NEVER see
    // who changed their permissions — that info is owner-only via audit_logs.
    // Returns null if not in admin_users. If the RPC isn't yet installed,
    // falls back to a direct select with an explicit safe column list (so the
    // page still works between SQL migrations).
    async getCurrentAdmin() {
      if (!client) return { data: null };
      try {
        const sess = await client.auth.getUser();
        const email = sess && sess.data && sess.data.user && sess.data.user.email;
        if (!email) return { data: null };

        const rpc = await client.rpc("get_my_admin_row");
        if (!rpc.error) {
          return { data: rpc.data || null };
        }
        if (rpc.error.code !== "PGRST202"
            && !/get_my_admin_row.*(does not exist|not find)/i.test(rpc.error.message || "")) {
          return { error: translateError(rpc.error), data: null };
        }
        // RPC not installed yet — direct select on the safe columns. Actor cols
        // are excluded so it works even after the column-level REVOKE landed.
        const SAFE_COLS = "id,email,role,active,can_manage_prices,can_manage_orders,can_manage_settings,can_manage_admins,can_view_audit_logs,created_at,updated_at,deleted_at";
        const { data, error } = await client.from("admin_users")
          .select(SAFE_COLS).eq("email", email.toLowerCase()).maybeSingle();
        if (error) {
          if ((error.code === "42P01") || /admin_users.*does not exist/i.test(error.message||"")) {
            return { data: null, schemaMissing: true };
          }
          return { error: translateError(error), data: null };
        }
        return { data: data || null };
      } catch (e) {
        return { error: translateError(e), data: null };
      }
    },

    // Owner-only table view. Explicit column list — actor columns
    // (created_by/updated_by/deleted_by) are revoked at the column level for
    // `authenticated`, so a `select *` would 403. The owner reads actor info
    // exclusively from the audit_logs panel.
    async getAdminUsers() {
      if (!client) return { data: [] };
      try {
        const SAFE_COLS = "id,email,role,active,can_manage_prices,can_manage_orders,can_manage_settings,can_manage_admins,can_view_audit_logs,created_at,updated_at,deleted_at";
        const { data, error } = await client.from("admin_users")
          .select(SAFE_COLS).order("role", { ascending: true }).order("email", { ascending: true });
        if (error) {
          if ((error.code === "42P01")) return { data: [], schemaMissing: true };
          return { error: translateError(error), data: [] };
        }
        return { data: data || [] };
      } catch (e) {
        return { error: translateError(e), data: [] };
      }
    },

    async createAdminUser({ email, role, can_manage_prices, can_manage_orders, can_manage_settings, can_manage_admins, created_by }) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      if (!email || !/^[^@\s]+@[^@\s]+\.[^@\s]+$/.test(email)) return { error: "Vul een geldig e-mailadres in." };
      try {
        // can_view_audit_logs intentionally omitted (DB default false): audit logs
        // are owner-only. NB: direct INSERT is blocked by RLS — the live path is
        // the owner_add_admin_user RPC; this helper is legacy/unused.
        const { data, error } = await client.from("admin_users").insert({
          email: email.toLowerCase(),
          role: role || "admin",
          active: true,
          can_manage_prices:   !!can_manage_prices,
          can_manage_orders:   can_manage_orders !== false,
          can_manage_settings: !!can_manage_settings,
          can_manage_admins:   !!can_manage_admins,
          created_by: created_by || null,
          updated_by: created_by || null,
        }).select().maybeSingle();
        if (error) return { error: translateError(error) };
        return { data };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    async updateAdminUser(id, patch, actorEmail) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      try {
        // can_view_audit_logs is intentionally not in `allowed`: audit logs are
        // owner-only, so the UI can never set it. (Legacy/unused direct-update path.)
        const allowed = ["role","active","can_manage_prices","can_manage_orders","can_manage_settings","can_manage_admins"];
        const clean = {};
        for (const k of allowed) if (k in patch) clean[k] = patch[k];
        if (actorEmail) clean.updated_by = actorEmail;
        const { data, error } = await client.from("admin_users")
          .update(clean).eq("id", id).select().maybeSingle();
        if (error) return { error: translateError(error) };
        return { data };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    // Owner-only: verify the e-mail has a Supabase Auth account (server-side, via
    // the SECURITY DEFINER RPC owner_add_admin_user — the frontend NEVER reads
    // auth.users) and then insert the admin_users row. The RPC is owner-gated and
    // returns ONLY a status. Returns { data:{ status:"added"|"no_account", id } }.
    async ownerAddAdminUser({ email, can_manage_orders, can_manage_prices, can_manage_settings }) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      const mail = String(email || "").trim().toLowerCase();
      if (!mail || !/^[^@\s]+@[^@\s]+\.[^@\s]+$/.test(mail)) return { error: "Vul een geldig e-mailadres in." };
      try {
        // can_view_audit_logs is intentionally NOT sent: audit logs are owner-only,
        // so the RPC defaults it to false. The column stays in the DB but is inert.
        const { data, error } = await client.rpc("owner_add_admin_user", {
          p_email: mail,
          p_can_manage_orders: can_manage_orders !== false,
          p_can_manage_prices: !!can_manage_prices,
          p_can_manage_settings: !!can_manage_settings,
        });
        if (error) {
          if (error.code === "PGRST202" || /owner_add_admin_user.*(does not exist|not find)/i.test(error.message || "")) {
            return { error: "Owner-RPC ontbreekt — draai supabase-owner-admin-rpcs.sql in Supabase.", rpcMissing: true };
          }
          return { error: translateError(error) };
        }
        return { data: data || {} };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    // Owner-only: audited delete of an admin. The RPC refuses the owner row and the
    // caller's own account, and writes an admin_deleted audit row before deleting.
    async ownerDeleteAdminUser(id) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      if (!id) return { error: "Geen gebruiker geselecteerd." };
      try {
        const { data, error } = await client.rpc("owner_delete_admin_user", { p_id: id });
        if (error) {
          if (error.code === "PGRST202" || /owner_delete_admin_user.*(does not exist|not find)/i.test(error.message || "")) {
            return { error: "Owner-RPC ontbreekt — draai supabase-owner-admin-rpcs.sql in Supabase.", rpcMissing: true };
          }
          return { error: translateError(error) };
        }
        return { data: data || {} };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    // ─── Owner-only update / soft-delete / reactivate (RPC-only) ────────────
    // Owner-only permission/active update. Server-side self-protection refuses
    // the owner row AND the caller's own account. The audit trigger writes a
    // catch-all audit entry + ONE notification (kind based on what changed).
    async ownerUpdateAdminUser(id, patch) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      if (!id) return { error: "Geen gebruiker geselecteerd." };
      const p = patch || {};
      try {
        // can_view_audit_logs is intentionally NOT sent (null => unchanged): audit
        // logs are owner-only, so the UI never grants/sets it. Column stays inert.
        const { data, error } = await client.rpc("owner_update_admin_user", {
          p_id: id,
          p_can_manage_orders:   typeof p.can_manage_orders   === "boolean" ? p.can_manage_orders   : null,
          p_can_manage_prices:   typeof p.can_manage_prices   === "boolean" ? p.can_manage_prices   : null,
          p_can_manage_settings: typeof p.can_manage_settings === "boolean" ? p.can_manage_settings : null,
          p_can_view_audit_logs: null,
          p_active:              typeof p.active              === "boolean" ? p.active              : null,
        });
        if (error) {
          if (error.code === "PGRST202" || /owner_update_admin_user.*(does not exist|not find)/i.test(error.message || "")) {
            return { error: "Owner-RPC ontbreekt — draai supabase-admin-role-notifications.sql in Supabase.", rpcMissing: true };
          }
          return { error: translateError(error) };
        }
        return { data: data || {} };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    // Owner-only soft delete (active=false + deleted_at/deleted_by). The audit
    // trigger writes admin_disabled + the soft-delete RPC writes an explicit
    // admin_deleted audit row. ONE notification row (kind='access_revoked').
    async ownerSoftDeleteAdminUser(id) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      if (!id) return { error: "Geen gebruiker geselecteerd." };
      try {
        const { data, error } = await client.rpc("owner_soft_delete_admin_user", { p_id: id });
        if (error) {
          if (error.code === "PGRST202" || /owner_soft_delete_admin_user.*(does not exist|not find)/i.test(error.message || "")) {
            return { error: "Owner-RPC ontbreekt — draai supabase-admin-role-notifications.sql in Supabase.", rpcMissing: true };
          }
          return { error: translateError(error) };
        }
        return { data: data || {} };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    // Owner-only reactivate (clears deleted_at + sets active=true). Trigger
    // writes admin_enabled + RPC writes admin_reactivated. ONE notification
    // row (kind='reactivated').
    async ownerReactivateAdminUser(id) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      if (!id) return { error: "Geen gebruiker geselecteerd." };
      try {
        const { data, error } = await client.rpc("owner_reactivate_admin_user", { p_id: id });
        if (error) {
          if (error.code === "PGRST202" || /owner_reactivate_admin_user.*(does not exist|not find)/i.test(error.message || "")) {
            return { error: "Owner-RPC ontbreekt — draai supabase-admin-role-notifications.sql in Supabase.", rpcMissing: true };
          }
          return { error: translateError(error) };
        }
        return { data: data || {} };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    // ─── Role-change notifications for the target (employee/admin) ──────────
    // The notification rows contain NO actor information by design. They tell
    // the target "something about your role changed → re-fetch" — the popup
    // text is rendered client-side from the `kind` enum.
    async getMyUnseenRoleNotifications() {
      if (!client) return { data: [] };
      try {
        const { data, error } = await client
          .from("admin_role_notifications")
          .select("id,kind,created_at,seen_at")
          .is("seen_at", null)
          .order("created_at", { ascending: false })
          .limit(20);
        if (error) {
          if ((error.code === "42P01") || /admin_role_notifications.*does not exist/i.test(error.message || "")) {
            return { data: [], schemaMissing: true };
          }
          return { error: translateError(error), data: [] };
        }
        return { data: data || [] };
      } catch (e) {
        return { error: translateError(e), data: [] };
      }
    },

    async markRoleNotificationSeen(id) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      if (!id) return { error: "Geen notificatie." };
      try {
        const { error } = await client.rpc("mark_role_notification_seen", { p_id: id });
        if (error) {
          if (error.code === "PGRST202") return { error: "RPC ontbreekt", rpcMissing: true };
          return { error: translateError(error) };
        }
        return { data: true };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    async markAllMyRoleNotificationsSeen() {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      try {
        const { data, error } = await client.rpc("mark_all_my_role_notifications_seen");
        if (error) {
          if (error.code === "PGRST202") return { error: "RPC ontbreekt", rpcMissing: true };
          return { error: translateError(error) };
        }
        return { data: data || 0 };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    // Realtime subscription for role-change events. Listens to INSERT on
    // public.admin_role_notifications filtered to the caller's own
    // target_email — the notification table has NO actor column, so this
    // channel cannot leak the owner's email to the target. The payload is
    // used only as a TRIGGER for a fresh getCurrentAdmin() + unseen-fetch.
    // Returns an unsubscribe() function or null when realtime is unavailable.
    // No service role; uses the existing anon client + the logged-in JWT.
    subscribeMyRoleNotifications(handlers) {
      if (!client || typeof client.channel !== "function") return null;
      const h = handlers || {};
      const email = String(h.email || "").trim().toLowerCase();
      if (!email) return null;
      const onInsert = typeof h.onInsert === "function" ? h.onInsert : function () {};
      const onStatus = typeof h.onStatus === "function" ? h.onStatus : function () {};

      // Hand Realtime the JWT so RLS lets the target receive their own rows.
      try {
        if (client.realtime && typeof client.realtime.setAuth === "function") {
          getSupabaseAccessToken().then(function (tok) {
            if (tok) { try { client.realtime.setAuth(tok); } catch (_) {} }
          }).catch(function () {});
        }
      } catch (_) {}

      let channel = null;
      try {
        channel = client
          .channel("my-role-notifs-" + Date.now())
          .on("postgres_changes", {
            event: "INSERT",
            schema: "public",
            table: "admin_role_notifications",
            filter: "target_email=eq." + email,
          }, function (payload) { try { onInsert(payload); } catch (_) {} })
          .subscribe(function (status) { try { onStatus(status); } catch (_) {} });
      } catch (e) {
        return null;
      }

      return function unsubscribe() {
        try { if (channel) client.removeChannel(channel); } catch (_) {}
      };
    },

    async getAuditLogs({ limit = 100, actorEmail = null, action = null, entityType = null, fromDate = null, toDate = null } = {}) {
      if (!client) return { data: [] };
      try {
        let q = client.from("audit_logs").select("*").order("created_at", { ascending: false }).limit(limit);
        if (actorEmail) q = q.ilike("actor_email", `%${actorEmail}%`);
        if (action) q = q.eq("action", action);
        if (entityType) q = q.eq("entity_type", entityType);
        if (fromDate) q = q.gte("created_at", fromDate);
        if (toDate) q = q.lte("created_at", toDate);
        const { data, error } = await q;
        if (error) {
          if (error.code === "42P01") return { data: [], schemaMissing: true };
          return { error: translateError(error), data: [] };
        }
        return { data: data || [] };
      } catch (e) {
        return { error: translateError(e), data: [] };
      }
    },

    async createAuditLog(action, entityType, entityId, metadata) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      try {
        const { error } = await client.rpc("log_admin_action", {
          p_action: action,
          p_entity_type: entityType,
          p_entity_id: entityId || null,
          p_metadata: metadata || null,
        });
        if (error) return { error: translateError(error) };
        return { data: true };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    // updateProductWithAudit / updateOrderWithAudit are just the existing
    // update functions — the audit log entry is written automatically by
    // the trg_audit_* triggers in Postgres. Aliased here so the frontend
    // intent ("with audit") is explicit at the call site.
    async updateProductWithAudit(id, patch) {
      return this.updateProduct(id, patch);
    },
    async updateOrderWithAudit(id, patch) {
      return this.updateOrder(id, patch);
    },

    async getSetting(key) {
      if (!client) return { data: null };
      try {
        const { data, error } = await client.from("site_settings")
          .select("value").eq("key", key).maybeSingle();
        if (error) {
          if (error.code === "42P01") return { data: null, schemaMissing: true };
          return { error: translateError(error), data: null };
        }
        return { data: data ? data.value : null };
      } catch (e) {
        return { error: translateError(e), data: null };
      }
    },

    async getAllSettings() {
      if (!client) return { data: [] };
      try {
        const { data, error } = await client.from("site_settings")
          .select("*").order("key", { ascending: true });
        if (error) {
          if (error.code === "42P01") return { data: [], schemaMissing: true };
          return { error: translateError(error), data: [] };
        }
        return { data: data || [] };
      } catch (e) {
        return { error: translateError(e), data: [] };
      }
    },

    async updateSettingWithAudit(key, value, actorEmail) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      try {
        const { data, error } = await client.from("site_settings")
          .upsert({ key, value, updated_by: actorEmail || null, updated_at: new Date().toISOString() }, { onConflict: "key" })
          .select().maybeSingle();
        if (error) return { error: translateError(error) };
        return { data };
      } catch (e) {
        return { error: translateError(e) };
      }
    },

    statusLabels: STATUS_LABELS,
    statusLabelsEn: STATUS_LABELS_EN,
    statusLabel: statusLabel,
    statusOrder: STATUS_ORDER,
    statusTone: STATUS_TONE,
    paymentStatusLabels: PAYMENT_STATUS_LABELS,
    paymentStatusLabelsEn: PAYMENT_STATUS_LABELS_EN,
    paymentStatusLabel: paymentStatusLabel,
    isAdminEmail(email) {
      return !!email && !!cfg.ADMIN_EMAIL && email.toLowerCase() === cfg.ADMIN_EMAIL.toLowerCase();
    },

    // ============================================================
    // PRODUCT REVIEWS (all via SECURITY DEFINER RPCs — no direct table access)
    // ============================================================
    // Customer submits a review for an OWN, afgeronde order line. The server
    // forces status='pending' and validates ownership/completion/dedup.
    async submitReview({ order_id, product_slug, rating, title, body }) {
      if (!client) return { error: initError || "Supabase niet geconfigureerd." };
      try {
        const { data, error } = await client.rpc("submit_review", {
          p_order_id: order_id,
          p_product_slug: product_slug,
          p_rating: rating,
          p_title: title || null,
          p_body: body || null,
        });
        if (error) {
          if (error.code === "PGRST202" || /submit_review.*(does not exist|not find)/i.test(error.message || "")) {
            return { error: "Reviews zijn nog niet geactiveerd — draai supabase-product-reviews.sql in Supabase.", rpcMissing: true };
          }
          return { error: translateError(error) };
        }
        return { data: data || {} };
      } catch (e) { return { error: translateError(e) }; }
    },

    // The caller's own reviews (any status) — to show status + which products are reviewed.
    async getMyReviews() {
      if (!client) return { data: [] };
      try {
        const { data, error } = await client.rpc("get_my_reviews");
        if (error) {
          if (error.code === "PGRST202") return { data: [], rpcMissing: true };
          return { error: translateError(error), data: [] };
        }
        return { data: data || [] };
      } catch (e) { return { error: translateError(e), data: [] }; }
    },

    // Public: approved reviews + summary for a product slug.
    async getProductReviews(slug, limit) {
      if (!client || !slug) return { data: [] };
      try {
        const { data, error } = await client.rpc("get_product_reviews", { p_product_slug: slug, p_limit: limit || 20 });
        if (error) {
          if (error.code === "PGRST202") return { data: [], rpcMissing: true };
          return { error: translateError(error), data: [] };
        }
        return { data: data || [] };
      } catch (e) { return { error: translateError(e), data: [] }; }
    },
    async getReviewSummary(slug) {
      const empty = { count: 0, average: null };
      if (!client || !slug) return { data: empty };
      try {
        const { data, error } = await client.rpc("get_review_summary", { p_product_slug: slug });
        if (error) {
          if (error.code === "PGRST202") return { data: empty, rpcMissing: true };
          return { error: translateError(error), data: empty };
        }
        return { data: data || empty };
      } catch (e) { return { error: translateError(e), data: empty }; }
    },

    // Owner / can_manage_orders: moderation list + approve/reject.
    async getReviewsForModeration(status) {
      if (!client) return { data: [] };
      try {
        const { data, error } = await client.rpc("owner_list_reviews", { p_status: status || null });
        if (error) {
          if (error.code === "PGRST202") return { data: [], rpcMissing: true };
          return { error: translateError(error), data: [] };
        }
        return { data: data || [] };
      } catch (e) { return { error: translateError(e), data: [] }; }
    },
    async approveReview(id) {
      if (!client || !id) return { error: "Geen review geselecteerd." };
      try {
        const { data, error } = await client.rpc("owner_approve_review", { p_id: id });
        if (error) {
          if (error.code === "PGRST202") return { error: "Review-RPC ontbreekt — draai supabase-product-reviews.sql.", rpcMissing: true };
          return { error: translateError(error) };
        }
        return { data: data || {} };
      } catch (e) { return { error: translateError(e) }; }
    },
    async rejectReview(id) {
      if (!client || !id) return { error: "Geen review geselecteerd." };
      try {
        const { data, error } = await client.rpc("owner_reject_review", { p_id: id });
        if (error) {
          if (error.code === "PGRST202") return { error: "Review-RPC ontbreekt — draai supabase-product-reviews.sql.", rpcMissing: true };
          return { error: translateError(error) };
        }
        return { data: data || {} };
      } catch (e) { return { error: translateError(e) }; }
    },
  };

  window.zsb = api;
  // Uppercase alias so callers can use `window.ZSB.getAccessToken()` as well
  // as the original lowercase `window.zsb.*`. Both point at the same object.
  window.ZSB = api;

  // ---------- React auth context ----------
  const AuthContext = React.createContext({
    user: null, profile: null, ready: true, isAdmin: false, isStaff: false, isRecovery: false,
    refreshProfile: () => {},
  });

  // Active-staff check from a getCurrentAdmin() row (admin_users via the
  // SECURITY DEFINER get_my_admin_row() RPC). True ONLY for an active,
  // non-deleted owner/admin, or one that holds at least one manage permission.
  // Plain customers (no admin_users row) get null -> false -> no admin button.
  function isActiveStaffRow(row) {
    if (!row) return false;
    if (row.active !== true) return false;
    if (row.deleted_at) return false;
    if (row.role === "owner" || row.role === "admin") return true;
    return !!(row.can_manage_orders || row.can_manage_prices || row.can_manage_settings);
  }

  const useAuthStore = () => {
    const [state, setState] = React.useState(() => ({
      user: null,
      profile: null,
      ready: !api.isConfigured, // if not configured, "ready" immediately with null user
      isRecovery: false,
      adminRow: null,    // sanitized admin_users row (get_my_admin_row) or null
      staffAccess: false, // derived: active owner/admin or perm-holder
    }));
    const mountedRef = React.useRef(true);

    // Track mount so async callbacks don't setState on unmounted component
    React.useEffect(() => {
      mountedRef.current = true;
      return () => { mountedRef.current = false; };
    }, []);

    // Safety: if Supabase never responds within 5s, mark ready with no user
    // so the UI doesn't hang on "Laden…" forever.
    React.useEffect(() => {
      if (!api.isConfigured) return;
      const t = setTimeout(() => {
        if (!mountedRef.current) return;
        setState(s => s.ready ? s : { ...s, ready: true });
      }, 5000);
      return () => clearTimeout(t);
    }, []);

    // Initial session + subscription. Runs once.
    React.useEffect(() => {
      if (!api.isConfigured || !client) return;

      const loadProfile = async (uid) => {
        if (!uid) return;
        const { data } = await api.getProfile(uid);
        if (!mountedRef.current) return;
        setState(s => ({ ...s, profile: data || null }));
      };

      // Resolve the caller's admin_users row via get_my_admin_row() (RPC strips
      // actor columns). Drives the "Naar adminpaneel" affordances. Never throws:
      // non-admins get { data: null } -> staffAccess false.
      const loadAdminRow = async () => {
        const { data } = await api.getCurrentAdmin();
        if (!mountedRef.current) return;
        setState(s => ({ ...s, adminRow: data || null, staffAccess: isActiveStaffRow(data) }));
      };

      // Resolve any persisted session from localStorage
      client.auth.getSession().then(({ data }) => {
        if (!mountedRef.current) return;
        const u = data && data.session ? data.session.user : null;
        setState(s => ({ ...s, user: u, ready: true }));
        if (u) { loadProfile(u.id); loadAdminRow(); }
      }).catch(() => {
        if (!mountedRef.current) return;
        setState(s => ({ ...s, ready: true }));
      });

      // Subscribe to auth changes (login, logout, refresh, recovery)
      const sub = client.auth.onAuthStateChange((event, session) => {
        if (!mountedRef.current) return;
        const u = session ? session.user : null;
        if (event === "PASSWORD_RECOVERY") {
          setState(s => ({ ...s, user: u, ready: true, isRecovery: true }));
          return;
        }
        if (event === "SIGNED_OUT") {
          setState(s => ({ ...s, user: null, profile: null, ready: true, isRecovery: false, adminRow: null, staffAccess: false }));
          return;
        }
        // SIGNED_IN, TOKEN_REFRESHED, USER_UPDATED, INITIAL_SESSION
        setState(s => {
          const sameId = (s.user && s.user.id) === (u && u.id);
          if (sameId) return { ...s, ready: true };
          return { ...s, user: u, ready: true };
        });
        if (u) { loadProfile(u.id); loadAdminRow(); }
      });

      const subscription = sub && sub.data && sub.data.subscription;
      return () => {
        if (subscription && typeof subscription.unsubscribe === "function") {
          subscription.unsubscribe();
        }
      };
    }, []);

    const refreshProfile = React.useCallback(async () => {
      const uid = state.user && state.user.id;
      if (!uid) return;
      const { data } = await api.getProfile(uid);
      if (!mountedRef.current) return;
      setState(s => ({ ...s, profile: data || null }));
    }, [state.user && state.user.id]);

    const isAdmin = !!(state.user && api.isAdminEmail(state.user.email));
    // isStaff = real admin_users access (owner OR medewerker), from get_my_admin_row().
    // isAdmin stays the owner-email check so owner navigation is unchanged.
    return { ...state, isAdmin, isStaff: !!state.staffAccess, refreshProfile };
  };

  window.AuthContext = AuthContext;
  window.useAuthStore = useAuthStore;

  // ---------- React products context (live prices) ----------
  // Mutates window.PRODUCTS / window.ASSEMBLY_PER_BIKE in place so EVERY
  // component that reads PRODUCTS[id].price (cart, checkout, p-home, p-product,
  // p-shop, p-compare) automatically picks up the new value on the next render.
  // The React state change here is what triggers that re-render.
  const ProductsContext = React.createContext({
    products: {}, ready: true, error: null, refresh: () => {},
  });

  // Apply a Supabase products row to the in-memory static PRODUCTS /
  // ACCESSORIES / ALL_ITEMS / ASSEMBLY_PER_BIKE objects. Matches by slug
  // — bikes by slug ('v20'/'gt2000'), accessories by id ('acc-…'), services
  // by id ('svc-…'). Stays a no-op if no matching in-memory entry exists.
  function applyProductRow(row) {
    if (!row || !row.slug) return;
    const slug = row.slug;
    const cat = row.category || (slug.startsWith("acc-") ? "accessory" : slug.startsWith("svc-") ? "service" : "bike");
    const P = window.PRODUCTS;
    const ACC = window.ACCESSORIES;
    const A = window.ALL_ITEMS;
    const ASMs = window.ASSEMBLY_PER_BIKE;

    if (cat === "bike" && P && P[slug]) {
      P[slug].price = Number(row.price);
      P[slug].oldPrice = row.old_price == null ? null : Number(row.old_price);
      P[slug].assemblyPrice = Number(row.assembly_price);
      P[slug].active = !!row.active;
      if ("availability_status" in row) P[slug].availability_status = row.availability_status || "available";
    }

    if (cat === "accessory" && Array.isArray(ACC)) {
      // Accessories are keyed by id (slug), e.g. 'acc-lock'
      let entry = ACC.find(a => a.id === slug);
      if (!entry) {
        // Live add: a new accessory created in admin appears in the list
        entry = {
          id: slug, name: row.name, type: "accessoire", icon: row.icon || "lock",
          desc: row.description || "", price: Number(row.price), oldPrice: row.old_price == null ? null : Number(row.old_price),
          active: !!row.active, sort_order: row.sort_order || 0,
          availability_status: row.availability_status || "available",
        };
        ACC.push(entry);
        if (A) A[slug] = entry;
      } else {
        entry.name = row.name || entry.name;
        entry.price = Number(row.price);
        entry.oldPrice = row.old_price == null ? null : Number(row.old_price);
        entry.active = !!row.active;
        entry.sort_order = row.sort_order || 0;
        if (row.description) entry.desc = row.description;
        if (row.icon) entry.icon = row.icon;
        if ("availability_status" in row) entry.availability_status = row.availability_status || "available";
      }
    }

    if (cat === "service" && ASMs) {
      // Match service rows to ASMs by suffix — svc-assembly-v20 → ASMs.v20
      const bikeKey = slug.replace(/^svc-assembly-/, "");
      if (ASMs[bikeKey]) {
        ASMs[bikeKey].name = row.name || ASMs[bikeKey].name;
        ASMs[bikeKey].short = row.short_name || ASMs[bikeKey].short;
        ASMs[bikeKey].price = Number(row.price);
        ASMs[bikeKey].oldPrice = row.old_price == null ? null : Number(row.old_price);
        ASMs[bikeKey].active = !!row.active;
        ASMs[bikeKey].sort_order = row.sort_order || 0;
        if (row.description) ASMs[bikeKey].desc = row.description;
        if ("availability_status" in row) ASMs[bikeKey].availability_status = row.availability_status || "available";
        if (A) A[ASMs[bikeKey].id] = ASMs[bikeKey];
      }
      // Also keep the generic ASSEMBLY_SERVICE in sync if its price changes
      if (slug === "svc-assembly" && window.ASSEMBLY_SERVICE) {
        window.ASSEMBLY_SERVICE.name = row.name || window.ASSEMBLY_SERVICE.name;
        window.ASSEMBLY_SERVICE.short = row.short_name || window.ASSEMBLY_SERVICE.short;
        window.ASSEMBLY_SERVICE.price = Number(row.price);
        window.ASSEMBLY_SERVICE.oldPrice = row.old_price == null ? null : Number(row.old_price);
        window.ASSEMBLY_SERVICE.active = !!row.active;
        window.ASSEMBLY_SERVICE.sort_order = row.sort_order || 0;
        if (row.description) window.ASSEMBLY_SERVICE.desc = row.description;
        if ("availability_status" in row) window.ASSEMBLY_SERVICE.availability_status = row.availability_status || "available";
        if (A) A["svc-assembly"] = window.ASSEMBLY_SERVICE;
      }
    }
  }

  const useProductsStore = () => {
    const [state, setState] = React.useState(() => ({
      products: {}, ready: !api.isConfigured, error: null, version: 0,
    }));
    const mountedRef = React.useRef(true);

    React.useEffect(() => {
      mountedRef.current = true;
      return () => { mountedRef.current = false; };
    }, []);

    const load = React.useCallback(async () => {
      if (!api.isConfigured) {
        if (!mountedRef.current) return;
        setState(s => ({ ...s, ready: true }));
        return;
      }
      const { data, error } = await api.getProducts();
      if (!mountedRef.current) return;
      if (error || !data) {
        // Supabase down or RLS blocked it — keep static fallback prices,
        // mark ready, expose error for diagnostic UI.
        setState(s => ({ ...s, ready: true, error: error || null }));
        return;
      }
      // Mutate the in-memory mirrors so every screen sees fresh values.
      const bySlug = {};
      for (const row of data) {
        applyProductRow(row);
        bySlug[row.slug] = row;
      }
      setState(s => ({ products: bySlug, ready: true, error: null, version: s.version + 1 }));
    }, []);

    // Initial load + safety timeout (5s) so we never block forever.
    React.useEffect(() => {
      load();
      const t = setTimeout(() => {
        if (!mountedRef.current) return;
        setState(s => s.ready ? s : { ...s, ready: true });
      }, 5000);
      return () => clearTimeout(t);
    }, [load]);

    return { ...state, refresh: load };
  };

  window.ProductsContext = ProductsContext;
  window.useProductsStore = useProductsStore;

  // ============================================================
  // Site settings store (shipping fees, free-shipping threshold)
  // ============================================================
  // Fetches /rest/v1/site_settings on mount, normalises shipping_* values
  // into window.SHIPPING_CONFIG so the cart store (data.jsx) can read live
  // values. Defaults shipped here match the original hardcoded 19 / 500 so
  // the cart still works when Supabase is unreachable.
  const DEFAULT_SHIPPING_CONFIG = {
    nl: 19, be: 29, de: 29,
    free_shipping_threshold: 500,
    default_country: "NL",
    pickup_enabled: true,
    delivery_enabled: true,
  };
  if (!window.SHIPPING_CONFIG) window.SHIPPING_CONFIG = { ...DEFAULT_SHIPPING_CONFIG };

  // Helper used by zsb.getAllSettings consumers + the cart store.
  function applySettingRow(row) {
    if (!row || !row.key) return;
    const v = row.value;
    const num = (x) => (x && typeof x === "object" && "amount" in x) ? Number(x.amount) : Number(x);
    switch (row.key) {
      case "shipping_nl_fee":          window.SHIPPING_CONFIG.nl = num(v); break;
      case "shipping_be_fee":          window.SHIPPING_CONFIG.be = num(v); break;
      case "shipping_de_fee":          window.SHIPPING_CONFIG.de = num(v); break;
      case "free_shipping_threshold":  window.SHIPPING_CONFIG.free_shipping_threshold = num(v); break;
      case "default_shipping_country": window.SHIPPING_CONFIG.default_country = String(v || "NL").replace(/"/g,""); break;
      case "pickup_enabled":           window.SHIPPING_CONFIG.pickup_enabled = v === true || v === "true"; break;
      case "delivery_enabled":         window.SHIPPING_CONFIG.delivery_enabled = v === true || v === "true"; break;
      case "whatsapp_number": {
        // Store the raw admin value. The normalize/format helpers in data.jsx
        // accept any of: bare digits, +XX..., spaced "+31 6 ...", or a full
        // https://wa.me/... URL.
        const s = (v && typeof v === "object" && "value" in v) ? v.value : v;
        const str = (typeof s === "string") ? s.trim() : (s == null ? "" : String(s).trim());
        if (str) window.WHATSAPP_NUMBER = str;
        break;
      }
    }
  }
  window.applySettingRow = applySettingRow;

  const SettingsContext = React.createContext({ ready: false, version: 0, refresh: () => {} });

  const useSettingsStore = () => {
    const [state, setState] = React.useState({ ready: !api.isConfigured, version: 0 });
    const mountedRef = React.useRef(true);
    React.useEffect(() => { mountedRef.current = true; return () => { mountedRef.current = false; }; }, []);

    const load = React.useCallback(async () => {
      if (!api.isConfigured) {
        if (mountedRef.current) setState(s => ({ ...s, ready: true }));
        return;
      }
      const { data } = await api.getAllSettings();
      if (!mountedRef.current) return;
      (data || []).forEach(applySettingRow);
      setState(s => ({ ready: true, version: s.version + 1 }));
    }, []);

    React.useEffect(() => {
      load();
      const t = setTimeout(() => { if (mountedRef.current) setState(s => s.ready ? s : { ...s, ready: true }); }, 5000);
      return () => clearTimeout(t);
    }, [load]);

    return { ...state, refresh: load };
  };

  window.SettingsContext = SettingsContext;
  window.useSettingsStore = useSettingsStore;
})();