/* global React */
// =====================================================================
// MELR data layer — shared Supabase fetch helpers exposed on window.
// Loaded as a legacy <script type="text/babel"> BEFORE the screens that
// consume it. Reads `window.melr.supabase` (set by the Vite bundle).
//
// Each helper returns rows already mapped to the *legacy shape* so the
// existing screens (which were written against the static PROJECTS
// array) keep working with minimal changes.
// =====================================================================

(function () {
  function waitForSupabase() {
    return new Promise((resolve) => {
      const tick = () => {
        const sb = window.melr && window.melr.supabase;
        if (sb) resolve(sb);
        else setTimeout(tick, 50);
      };
      tick();
    });
  }

  function phaseLabel(status, lang) {
    const labels = {
      inception: { fr: "Démarrage", en: "Inception" },
      appraisal: { fr: "Évaluation ex ante", en: "Ex-ante appraisal" },
      active:    { fr: "Mise en œuvre", en: "Implementation" },
      paused:    { fr: "En pause", en: "Paused" },
      closing:   { fr: "Clôture", en: "Closing" },
      closed:    { fr: "Clôturé", en: "Closed" },
    };
    return (labels[status] || labels.active)[lang === "en" ? "en" : "fr"];
  }

  function mapProjectRow(p) {
    const pcs = p.project_countries || [];
    const countryFr = pcs.map((pc) => pc.countries && pc.countries.name_fr).filter(Boolean).join(" · ") || "—";
    const countryEn = pcs.map((pc) => pc.countries && pc.countries.name_en).filter(Boolean).join(" · ") || "—";
    return {
      uuid:        p.id,                  // expose Supabase UUID for joins
      id:          p.code,                // legacy id == project code
      organizationId: p.organization_id,  // owning org (super-admin filtering)
      sector:      p.sector_id,
      nameFr:      p.name_fr,
      nameEn:      p.name_en || p.name_fr,
      countriesFr: countryFr,
      countriesEn: countryEn,
      lead:        (p.lead && p.lead.full_name) || "—",
      status:      p.status,
      progress:    p.progress || 0,
      // budget/disbursed are stored as raw <native currency> in Supabase.
      // We expose them as "millions of native currency" so the UI just
      // has to call window.melr.formatAmount(value, nativeCurrency, ...).
      budget:         (Number(p.budget)    || 0) / 1_000_000,
      disbursed:      (Number(p.disbursed) || 0) / 1_000_000,
      nativeCurrency: p.currency || "EUR",
      programmeId:    p.programme_id || null,
      programmeCode:  (p.programmes && p.programmes.code) || null,
      programmeName:  (p.programmes && p.programmes.name_fr) || null,
      indic:       (p.indicators_count    && p.indicators_count[0]    && p.indicators_count[0].count)    || 0,
      sites:       (p.sites_count         && p.sites_count[0]         && p.sites_count[0].count)         || 0,
      phaseFr:     phaseLabel(p.status, "fr"),
      phaseEn:     phaseLabel(p.status, "en"),
      risk:        p.risk,
      donor:       "",
      created_at:  p.created_at,
      updated_at:  p.updated_at,
    };
  }

  // ---- profile + write helpers ---------------------------------------

  // Returns the profile row for the currently authenticated user, or
  // null if there is no session. Result is cached for the lifetime of
  // the page since it changes only on login/logout.
  let _profileCache = undefined;
  async function currentProfile() {
    if (_profileCache !== undefined) return _profileCache;
    const sb = await waitForSupabase();
    const { data: sess } = await sb.auth.getSession();
    if (!sess.session) { _profileCache = null; return null; }
    const { data, error } = await sb.from("profiles")
      .select("id, organization_id, full_name, email, locale")
      .eq("id", sess.session.user.id).maybeSingle();
    if (error) { console.warn("[melr] currentProfile:", error.message); return null; }
    _profileCache = data;
    return data;
  }

  // Reset profile cache when auth changes (login / logout).
  window.addEventListener("melr:auth", () => { _profileCache = undefined; });

  // Update the current user's profile (full_name, locale, avatar_url, …).
  async function updateProfile(patch) {
    const sb = await waitForSupabase();
    const profile = await currentProfile();
    if (!profile) throw new Error("Vous devez être connecté.");
    const { data, error } = await sb.from("profiles")
      .update(patch).eq("id", profile.id)
      .select().single();
    if (error) throw new Error(error.message);
    _profileCache = data;
    return data;
  }

  // Change the password via Supabase Auth.
  async function changePassword(newPassword) {
    const sb = await waitForSupabase();
    if (!newPassword || newPassword.length < 6) {
      throw new Error("Mot de passe trop court (minimum 6 caractères).");
    }
    const { error } = await sb.auth.updateUser({ password: newPassword });
    if (error) throw new Error(error.message);
  }

  // Insert a new programme (a grouping of projects).
  async function createProgramme(payload) {
    const sb = await waitForSupabase();
    const profile = await currentProfile();
    if (!profile || !profile.organization_id) {
      throw new Error("Vous devez appartenir à une organisation pour créer un programme.");
    }
    const { data, error } = await sb.from("programmes")
      .insert({
        organization_id: profile.organization_id,
        code:        payload.code,
        name_fr:     payload.name_fr,
        name_en:     payload.name_en || payload.name_fr,
        sector_id:   payload.sector_id || null,
        description: payload.description || null,
        start_date:  payload.start_date || null,
        end_date:    payload.end_date || null,
        budget:      payload.budget_native != null && payload.budget_native !== ""
                       ? Number(payload.budget_native) * 1_000_000
                       : null,
        currency:    payload.currency || "EUR",
      })
      .select().single();
    if (error) throw new Error(error.message);
    return data;
  }

  // Update an existing programme.
  async function updateProgramme(uuid, patch) {
    const sb = await waitForSupabase();
    const body = {};
    if (patch.code        !== undefined) body.code        = patch.code;
    if (patch.name_fr     !== undefined) body.name_fr     = patch.name_fr;
    if (patch.name_en     !== undefined) body.name_en     = patch.name_en;
    if (patch.sector_id   !== undefined) body.sector_id   = patch.sector_id;
    if (patch.description !== undefined) body.description = patch.description;
    if (patch.start_date  !== undefined) body.start_date  = patch.start_date;
    if (patch.end_date    !== undefined) body.end_date    = patch.end_date;
    if (patch.currency    !== undefined) body.currency    = patch.currency;
    if (patch.budget_native !== undefined) {
      body.budget = patch.budget_native != null && patch.budget_native !== ""
        ? Number(patch.budget_native) * 1_000_000
        : null;
    }
    const { data, error } = await sb.from("programmes")
      .update(body).eq("id", uuid).select().single();
    if (error) throw new Error(error.message);
    return data;
  }

  // Hard-delete a programme. Projects with programme_id pointing to it
  // will be set to null via ON DELETE SET NULL (assumed by schema) — if
  // schema uses RESTRICT instead, Postgres will reject the call and the
  // UI will surface the error.
  async function deleteProgramme(uuid) {
    const sb = await waitForSupabase();
    const { error } = await sb.from("programmes").delete().eq("id", uuid);
    if (error) throw new Error(error.message);
  }

  // List programmes for an organization. Without a target, defaults to the
  // caller's own org. Super-admin acting-as-org passes an explicit
  // targetOrgId; the super-admin SELECT policy on programmes lets the read
  // succeed across orgs.
  async function fetchProgrammes(targetOrgId) {
    const sb = await waitForSupabase();
    let orgId = targetOrgId;
    if (!orgId) {
      const profile = await currentProfile();
      if (!profile || !profile.organization_id) return [];
      orgId = profile.organization_id;
    }
    const { data, error } = await sb.from("programmes")
      .select("id, organization_id, code, name_fr, name_en, sector_id, description, start_date, end_date, budget, currency, created_at")
      .eq("organization_id", orgId)
      .order("code");
    if (error) throw new Error(error.message);
    return data || [];
  }

  // Realtime hook: programmes of an organization. Without an argument,
  // defaults to the caller's own org (existing behaviour). Pass an explicit
  // targetOrgId to scope to another org (super-admin acting-as-org).
  function usePrograms(targetOrgId) {
    const { useState, useEffect, useCallback } = React;
    const [programmes, setProgrammes] = useState([]);
    const [loading, setLoading]   = useState(true);
    const [realtime, setRealtime] = useState(false);
    const refresh = useCallback(async () => {
      try {
        const rows = await fetchProgrammes(targetOrgId);
        setProgrammes(rows);
      } catch (e) { console.error("[MELR] usePrograms:", e); }
      finally    { setLoading(false); }
    }, [targetOrgId]);
    useEffect(() => {
      let mounted = true;
      let channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-programmes-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes", { event: "*", schema: "public", table: "programmes" }, () => { refresh(); })
          .subscribe((status) => { if (status === "SUBSCRIBED" && mounted) setRealtime(true); });
      })();
      return () => {
        mounted = false;
        if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel));
      };
    }, [refresh]);
    return { programmes, loading, refresh, realtime };
  }

  // Insert a new project. Returns the created row mapped to legacy shape.
  async function createProject(payload) {
    const sb = await waitForSupabase();
    const profile = await currentProfile();
    if (!profile) throw new Error("Vous devez être connecté pour créer un projet.");
    // Super-admin acting-as-org can override organization_id; everyone else
    // implicitly creates in their own org.
    const orgId = payload.organization_id || profile.organization_id;
    const { data, error } = await sb.from("projects")
      .insert({
        organization_id: orgId,
        programme_id: payload.programme_id || null,
        code:    payload.code,
        name_fr: payload.name_fr,
        name_en: payload.name_en || payload.name_fr,
        sector_id: payload.sector_id,
        status:  payload.status || "inception",
        risk:    "ok",
        progress: 0,
        budget:  Number(payload.budget_native || payload.budget_meur || 0) * 1_000_000,
        currency: payload.currency || "EUR",
        // When creating in a foreign org, the super-admin probably isn't the
        // right lead — leave the FK null and let the org's admin set it
        // later. When in the user's own org, default to them.
        lead_user_id: (payload.lead_user_id !== undefined)
          ? (payload.lead_user_id || null)
          : (orgId === profile.organization_id ? profile.id : null),
      })
      .select(`
        id, code, name_fr, name_en, organization_id, sector_id, status, risk, progress, budget, disbursed,
        lead:profiles!projects_lead_user_id_fkey(full_name),
        project_countries(country_iso2, countries(name_fr, name_en))
      `).single();
    if (error) throw new Error(error.message);
    return mapProjectRow(data);
  }

  // Super-admin only (enforced by RLS): move an existing project into a
  // different organization. Detaches the project from its current programme
  // (programmes are org-scoped — the link would be stale otherwise) and
  // resets the lead to NULL so the target org's admin re-assigns one of
  // its own members. Existing project_agents rows are also wiped: agents
  // of the previous org would lose access via RLS anyway.
  async function transferProjectToOrg(projectUuid, targetOrgId, opts) {
    if (!projectUuid) throw new Error("projectUuid required");
    if (!targetOrgId) throw new Error("targetOrgId required");
    const sb = await waitForSupabase();
    const options = opts || {};
    // 1. Wipe stale per-agent assignments (best-effort — admin policy
    //    might let us through, otherwise we ignore the error)
    if (options.wipeAgents !== false) {
      await sb.from("project_agents").delete().eq("project_id", projectUuid);
    }
    // 2. Reassign the project
    const patch = {
      organization_id: targetOrgId,
      programme_id: null,    // programme is org-scoped, drop the link
      lead_user_id: null,    // new org will pick its own lead
    };
    const r = await sb.from("projects").update(patch).eq("id", projectUuid)
      .select("id, code, name_fr, name_en, organization_id").single();
    if (r.error) throw new Error(r.error.message);
    return r.data;
  }

  // Transfer an entire programme to another organization, taking all of
  // its projects along. Unlike transferProjectToOrg (which drops the
  // programme link), here we KEEP each project linked to its programme —
  // they all move together.
  //
  // Steps (best-effort, in order):
  //   1. Look up all projects whose programme_id = programmeUuid.
  //   2. Wipe project_agents for those projects (the source-org members
  //      would lose access via RLS anyway).
  //   3. Update each project: organization_id = target, lead_user_id = NULL.
  //      programme_id stays — they remain attached.
  //   4. Update the programme itself: organization_id = target.
  //
  // Steps 3 and 4 each rely on super-admin UPDATE policies that were
  // added in super-admin-projects-write.sql.
  async function transferProgrammeToOrg(programmeUuid, targetOrgId, opts) {
    if (!programmeUuid) throw new Error("programmeUuid required");
    if (!targetOrgId)   throw new Error("targetOrgId required");
    const sb = await waitForSupabase();
    const options = opts || {};

    // 1. Find the projects under this programme.
    const projs = await sb.from("projects")
      .select("id")
      .eq("programme_id", programmeUuid);
    if (projs.error) throw new Error(projs.error.message);
    const projectIds = (projs.data || []).map((r) => r.id);

    // 2. Wipe agent assignments en masse for those projects.
    if (options.wipeAgents !== false && projectIds.length > 0) {
      await sb.from("project_agents").delete().in("project_id", projectIds);
    }

    // 3. Bulk-update each project (org + lead reset). We do this in one
    //    statement using .in() so it stays atomic from the client's POV.
    if (projectIds.length > 0) {
      const upProjs = await sb.from("projects")
        .update({ organization_id: targetOrgId, lead_user_id: null })
        .in("id", projectIds)
        .select("id");
      if (upProjs.error) throw new Error(upProjs.error.message);
    }

    // 4. Move the programme row itself.
    const upProg = await sb.from("programmes")
      .update({ organization_id: targetOrgId })
      .eq("id", programmeUuid)
      .select("id, code, name_fr, name_en, organization_id").single();
    if (upProg.error) throw new Error(upProg.error.message);

    return {
      programme: upProg.data,
      transferredProjects: projectIds.length,
    };
  }

  // Patch a project. `patch` is a shallow object of column updates.
  async function updateProject(projectUuid, patch) {
    const sb = await waitForSupabase();
    const { data, error } = await sb.from("projects")
      .update(patch).eq("id", projectUuid)
      .select("id, code, progress, status, risk, budget, disbursed").single();
    if (error) throw new Error(error.message);
    return data;
  }

  // Insert a new site. `projectCode` is the legacy code (e.g. "P-001").
  // `coords` is an optional { lat, lng } object that gets stored both
  // in the PostGIS `location` column (via st_makepoint) and in
  // `metadata.coords` so the Leaflet map can read it without spatial
  // functions.
  async function createSite(projectCode, payload) {
    const sb = await waitForSupabase();
    // Resolve the project UUID from its legacy code.
    const { data: proj, error: projErr } = await sb.from("projects")
      .select("id, organization_id").eq("code", projectCode).maybeSingle();
    if (projErr) throw new Error(projErr.message);
    if (!proj) throw new Error("Projet introuvable : " + projectCode);

    const insertRow = {
      project_id:    proj.id,
      code:          payload.code,
      name:          payload.name,
      kind:          payload.kind || null,
      region:        payload.region || null,
      country_iso2:  (payload.country_iso2 || "SN").toUpperCase(),
      beneficiaries: payload.beneficiaries == null ? null : Number(payload.beneficiaries),
      staff_count:   payload.staff_count   == null ? null : Number(payload.staff_count),
      status:        payload.status || "ok",
      metadata:      payload.coords
                       ? { coords: { lat: Number(payload.coords.lat), lng: Number(payload.coords.lng) } }
                       : {},
    };

    const { data, error } = await sb.from("sites").insert(insertRow).select().single();
    if (error) throw new Error(error.message);

    // Best-effort: also populate the PostGIS `location` column for
    // future spatial queries (failure is non-blocking).
    if (payload.coords && data && data.id) {
      try {
        await sb.rpc("set_site_location", {
          site_uuid: data.id,
          lat: Number(payload.coords.lat),
          lng: Number(payload.coords.lng),
        });
      } catch (e) {
        // RPC may not exist in the project — silently ignore.
      }
    }
    return data;
  }

  // ─── Score scales (alphabetic indicators) ──────────────────────────
  // Maps a value_kind to its allowed labels + label↔numeric conversion.
  // Used by the indicator UI to render a dropdown when kind != 'numeric'
  // and by createIndicator / createIndicatorValue to fill the numeric
  // column at save time.
  //
  // Mapping per De Renzio (PEFA):
  //   A  = 4.0   B+ = 3.5   B  = 3.0   C+ = 2.5
  //   C  = 2.0   D+ = 1.5   D  = 1.0
  // We also accept "NR" (Not Rated) → null numeric so reports can
  // distinguish "no rating" from "lowest rating".
  const scoreScales = {
    numeric: {
      labels: null,
      toNumeric: (v) => (v === "" || v == null ? null : Number(v)),
      fromNumeric: (n) => (n == null || isNaN(n) ? "" : String(n)),
    },
    score_pefa: {
      labels: ["A", "B+", "B", "C+", "C", "D+", "D", "NR"],
      toNumeric: (v) => ({
        "A": 4.0, "B+": 3.5, "B": 3.0, "C+": 2.5,
        "C": 2.0, "D+": 1.5, "D": 1.0, "NR": null,
      })[String(v || "").trim()] ?? null,
      fromNumeric: (n) => {
        if (n == null || isNaN(n)) return "NR";
        const num = Number(n);
        // Round to nearest 0.5 then map back
        const r = Math.round(num * 2) / 2;
        const map = { 4: "A", 3.5: "B+", 3: "B", 2.5: "C+", 2: "C", 1.5: "D+", 1: "D" };
        return map[r] || String(num);
      },
    },
  };
  function scoreLabels(kind) {
    const s = scoreScales[kind] || scoreScales.numeric;
    return s.labels;
  }
  function scoreToNumeric(kind, value) {
    const s = scoreScales[kind] || scoreScales.numeric;
    return s.toNumeric(value);
  }
  function scoreFromNumeric(kind, n) {
    const s = scoreScales[kind] || scoreScales.numeric;
    return s.fromNumeric(n);
  }

  // Insert a new indicator. `projectCode` is the legacy code, e.g. "P-001".
  async function createIndicator(projectCode, payload) {
    const sb = await waitForSupabase();
    const { data: proj, error: projErr } = await sb.from("projects")
      .select("id, sector_id").eq("code", projectCode).maybeSingle();
    if (projErr) throw new Error(projErr.message);
    if (!proj) throw new Error("Projet introuvable : " + projectCode);

    // Resolve scoring: if the caller passed value_kind=score_pefa with
    // baseline_text/target_text, compute the numeric companion at save
    // time so the existing numeric pipeline (sparklines, progress %)
    // keeps working unchanged.
    const valueKind = payload.value_kind || "numeric";
    const baselineNum = payload.baseline != null && payload.baseline !== ""
      ? Number(payload.baseline)
      : (payload.baseline_text != null ? scoreToNumeric(valueKind, payload.baseline_text) : null);
    const targetNum = payload.target != null && payload.target !== ""
      ? Number(payload.target)
      : (payload.target_text != null ? scoreToNumeric(valueKind, payload.target_text) : null);

    const insertRow = {
      project_id:      proj.id,
      code:            payload.code,
      level:           payload.level || "outcome",
      name_fr:         payload.name_fr,
      name_en:         payload.name_en || payload.name_fr,
      unit:            payload.unit || null,
      baseline:        baselineNum,
      target:          targetNum,
      sector_id:       payload.sector_id || proj.sector_id || null,
      frequency:       payload.frequency || null,
      // Scoring metadata (PEFA-style alphabetic indicators)
      value_kind:      valueKind,
      baseline_text:   payload.baseline_text || null,
      target_text:     payload.target_text || null,
      // Link to the org-wide catalogue when the user picked an imported
      // code; null when they typed a custom code. See indicator-definitions.sql.
      definition_code: payload.definition_code || null,
    };

    const { data, error } = await sb.from("indicators").insert(insertRow).select().single();
    if (error) throw new Error(error.message);
    return data;
  }

  // ─── Support tickets (Help page bug-report form) ─────────────────────
  function useSupportTickets() {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        const profile = await currentProfile();
        if (!profile) { setData([]); return; }
        // RLS already scopes — own tickets + (if admin) all org tickets
        const r = await sb.from("support_tickets")
          .select("id, user_id, category, module, subject, body, steps, state, priority, created_at, updated_at, resolved_at, resolution, url, author:profiles!support_tickets_user_id_fkey(full_name, email)")
          .order("created_at", { ascending: false }).limit(200);
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.error("[MELR] support_tickets:", e); }
      finally { setLoading(false); }
    }, []);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-support-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes", { event: "*", schema: "public", table: "support_tickets" }, () => { refresh(); })
          .subscribe();
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh]);
    return { data, loading, refresh };
  }

  // Returns the set of permission strings the current user holds across
  // all their role assignments. Used to gate admin UI (e.g. show a
  // ticket-management panel only when the user has 'users.manage').
  // React hook around currentProfile(). Re-fetches when the auth state
  // changes so the UI updates immediately on login / logout. Returns
  // { profile: { id, organization_id, full_name, email, locale } | null,
  //   loading }.
  function useCurrentProfile() {
    const { useState, useEffect } = React;
    const [profile, setProfile] = useState(null);
    const [loading, setLoading] = useState(true);
    useEffect(() => {
      let cancelled = false;
      const load = async () => {
        try {
          // Bust the cache before each load so we always see the latest
          // (the cache is reset on melr:auth events from src/main.jsx).
          const p = await currentProfile();
          if (!cancelled) setProfile(p);
        } catch (e) { console.warn("[MELR] useCurrentProfile:", e); }
        finally { if (!cancelled) setLoading(false); }
      };
      load();
      const onAuth = () => { setLoading(true); load(); };
      window.addEventListener("melr:auth", onAuth);
      return () => { cancelled = true; window.removeEventListener("melr:auth", onAuth); };
    }, []);
    return { profile, loading };
  }

  // ─── Multi-org memberships ─────────────────────────────────────────────
  // A user can belong to multiple orgs. profile.organization_id is the home
  // org (default for writes); the table organization_memberships lists every
  // org the user belongs to (home org included via the back-fill).

  // Hook: returns the calling user's memberships, each with an embed of
  // the organization and (optional) role. Sorted by joined_via='home' first
  // so the home org appears at the top of pickers.
  function useUserMemberships() {
    const { useState, useEffect, useCallback } = React;
    const [data, setData]       = useState([]);
    const [loading, setLoading] = useState(true);
    const [realtime, setRealtime] = useState(false);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        const profile = await currentProfile();
        if (!profile) { setData([]); return; }
        const r = await sb.from("organization_memberships")
          .select(`
            id, user_id, organization_id, role_id, joined_via, created_at,
            organizations(id, name, slug, archived_at),
            roles(id, code, name_fr, name_en)
          `)
          .eq("user_id", profile.id)
          .order("joined_via", { ascending: true }); // 'home' < 'invitation' < 'manual' alphabetically
        if (r.error) throw new Error(r.error.message);
        // Filter out archived orgs from the active set, but keep them in the
        // raw data shape so the UI can show them if asked.
        setData((r.data || []).map((m) => ({
          ...m,
          orgName: m.organizations && m.organizations.name,
          orgSlug: m.organizations && m.organizations.slug,
          orgArchived: !!(m.organizations && m.organizations.archived_at),
          roleLabel: m.roles && (m.roles.name_fr || m.roles.code),
          isHome: profile.organization_id === m.organization_id,
        })));
      } catch (e) { console.warn("[MELR] useUserMemberships:", e.message || e); setData([]); }
      finally { setLoading(false); }
    }, []);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-memberships-self-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes",
              { event: "*", schema: "public", table: "organization_memberships" },
              () => { refresh(); })
          .subscribe((s) => { if (s === "SUBSCRIBED" && mounted) setRealtime(true); });
        const onAuth = () => { refresh(); };
        window.addEventListener("melr:auth", onAuth);
        const cleanup = () => window.removeEventListener("melr:auth", onAuth);
        // Stash cleanup so the outer return can use it
        channel._cleanupAuth = cleanup;
      })();
      return () => {
        mounted = false;
        waitForSupabase().then((sb) => {
          if (channel) {
            if (channel._cleanupAuth) channel._cleanupAuth();
            sb.removeChannel(channel);
          }
        });
      };
    }, [refresh]);
    return { data, loading, realtime, refresh };
  }

  // List the memberships of a SPECIFIC org (admin view). RLS lets a member
  // of the org see other members' rows; super-admin sees all.
  function useOrgMembershipsList(targetOrgId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData]       = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      if (!targetOrgId) { setData([]); setLoading(false); return; }
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("organization_memberships")
          .select(`
            id, user_id, organization_id, role_id, joined_via, created_at,
            profiles!user_id(id, full_name, email, organization_id),
            roles(id, code, name_fr, name_en)
          `)
          .eq("organization_id", targetOrgId)
          .order("created_at", { ascending: true });
        if (r.error) throw new Error(r.error.message);
        setData((r.data || []).map((m) => ({
          ...m,
          fullName: m.profiles && m.profiles.full_name,
          email:    m.profiles && m.profiles.email,
          isHomeForUser: m.profiles && m.profiles.organization_id === m.organization_id,
        })));
      } catch (e) { console.warn("[MELR] useOrgMembershipsList:", e.message || e); setData([]); }
      finally { setLoading(false); }
    }, [targetOrgId]);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-memberships-org-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes",
              { event: "*", schema: "public", table: "organization_memberships",
                filter: "organization_id=eq." + targetOrgId },
              () => { refresh(); })
          .subscribe();
      })();
      return () => {
        mounted = false;
        if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel));
      };
    }, [refresh, targetOrgId]);
    return { data, loading, refresh };
  }

  // CRUD wrapper. RLS enforces caller is admin of the target org (or super).
  const membershipsCrud = {
    // Add an existing platform user as a member of an org. Resolves the
    // email to a user_id first; throws if no such user exists. Pre-set a
    // role_id (optional) — caller usually offers the role list from the
    // target org's roles.
    async addByEmail(targetOrgId, email, roleId) {
      if (!targetOrgId) throw new Error("targetOrgId required");
      if (!email)       throw new Error("email required");
      const sb = await waitForSupabase();
      const emailNorm = String(email).trim().toLowerCase();
      // Resolve user_id. profiles is RLS-readable across own org; for the
      // admin to find someone outside their org we rely on the super-admin
      // policy. If the email isn't visible to the caller (regular admin
      // looking for a user not in any of their orgs), this will return null
      // and we surface a clear error.
      const found = await sb.from("profiles")
        .select("id, full_name, email, organization_id")
        .eq("email", emailNorm)
        .maybeSingle();
      if (found.error) throw new Error(found.error.message);
      if (!found.data) {
        throw new Error("Aucun utilisateur avec cet email. Vérifiez qu'il s'est déjà inscrit.");
      }
      const r = await sb.from("organization_memberships").insert({
        user_id:         found.data.id,
        organization_id: targetOrgId,
        role_id:         roleId || null,
        joined_via:      'manual',
      }).select(`
        id, user_id, organization_id, role_id, joined_via, created_at,
        profiles!user_id(id, full_name, email, organization_id),
        roles(id, code, name_fr, name_en)
      `).single();
      if (r.error) {
        // Unique constraint violation = already a member
        if (String(r.error.message).match(/duplicate|unique/i)) {
          throw new Error("Cet utilisateur est déjà membre de cette organisation.");
        }
        throw new Error(r.error.message);
      }
      return r.data;
    },
    async remove(membershipId) {
      if (!membershipId) throw new Error("membershipId required");
      const sb = await waitForSupabase();
      const r = await sb.from("organization_memberships").delete().eq("id", membershipId);
      if (r.error) throw new Error(r.error.message);
    },
    // Self-join: insert a membership for the calling user in the target org.
    // Only succeeds when RLS permits — typically a super-admin (orgs.manage)
    // or someone who already holds users.manage in the target org. Useful
    // when a super-admin needs to grant themselves access to another org's
    // data without going through the 'add by email' admin flow.
    async joinSelf(targetOrgId, roleId) {
      if (!targetOrgId) throw new Error("targetOrgId required");
      const sb = await waitForSupabase();
      const profile = await currentProfile();
      if (!profile) throw new Error("Not authenticated");
      const r = await sb.from("organization_memberships").insert({
        user_id:         profile.id,
        organization_id: targetOrgId,
        role_id:         roleId || null,
        joined_via:      'manual',
      }).select(`
        id, user_id, organization_id, role_id, joined_via, created_at,
        organizations(id, name, slug, archived_at),
        roles(id, code, name_fr, name_en)
      `).single();
      if (r.error) {
        if (String(r.error.message).match(/duplicate|unique/i)) {
          throw new Error("Vous êtes déjà membre de cette organisation.");
        }
        throw new Error(r.error.message);
      }
      return r.data;
    },
  };

  // ─── Active-org switcher (multi-org users) ─────────────────────────────
  // Stored in localStorage as 'melr.active-org'. Defaults to the user's
  // home org (profile.organization_id). The UI reads this to filter the
  // current view; writes use it as the implicit organization_id when the
  // user creates a project / programme / definition without specifying one.
  // Distinct from the super-admin 'acting on' pill: this is a permanent
  // user preference (across sessions), not an admin temporary override.
  function getActiveOrgIdSync() {
    try { return localStorage.getItem("melr.active-org") || null; }
    catch (_) { return null; }
  }
  function setActiveOrgIdSync(orgId) {
    try {
      if (orgId) localStorage.setItem("melr.active-org", orgId);
      else       localStorage.removeItem("melr.active-org");
    } catch (_) {}
    // Broadcast so listening hooks can refresh.
    try { window.dispatchEvent(new CustomEvent("melr:active-org", { detail: { orgId } })); } catch (_) {}
  }
  // Hook variant: returns [activeOrgId, setter] + auto-updates on storage
  // events and on the broadcast.
  function useActiveOrgId() {
    const { useState, useEffect } = React;
    const [orgId, setOrgId] = useState(() => getActiveOrgIdSync());
    useEffect(() => {
      const onStorage = (e) => {
        if (!e || e.key === "melr.active-org") setOrgId(getActiveOrgIdSync());
      };
      const onLocal = (e) => setOrgId(e && e.detail ? e.detail.orgId : getActiveOrgIdSync());
      window.addEventListener("storage", onStorage);
      window.addEventListener("melr:active-org", onLocal);
      return () => {
        window.removeEventListener("storage", onStorage);
        window.removeEventListener("melr:active-org", onLocal);
      };
    }, []);
    const set = (v) => { setActiveOrgIdSync(v); setOrgId(v || null); };
    return [orgId, set];
  }

  function useCurrentUserPermissions() {
    const { useState, useEffect } = React;
    const [perms, setPerms] = useState(new Set());
    const [loading, setLoading] = useState(true);
    useEffect(() => {
      let cancelled = false;
      (async () => {
        try {
          const sb = await waitForSupabase();
          const profile = await currentProfile();
          if (!profile) { if (!cancelled) setPerms(new Set()); return; }
          // There's no direct FK between user_roles and role_permissions
          // (they both point at roles), so PostgREST embeds don't work
          // here. Use two simple queries instead.
          const r1 = await sb.from("user_roles")
            .select("role_id").eq("user_id", profile.id);
          if (cancelled) return;
          if (r1.error) throw new Error(r1.error.message);
          const roleIds = (r1.data || []).map((x) => x.role_id);
          if (roleIds.length === 0) { setPerms(new Set()); return; }
          const r2 = await sb.from("role_permissions")
            .select("permission").in("role_id", roleIds);
          if (cancelled) return;
          if (r2.error) throw new Error(r2.error.message);
          setPerms(new Set((r2.data || []).map((x) => x.permission)));
        } catch (e) { console.error("[MELR] perms:", e); }
        finally { if (!cancelled) setLoading(false); }
      })();
      return () => { cancelled = true; };
    }, []);
    const has = (p) => perms.has(p);
    return { perms, has, loading };
  }

  // Admin update on a support ticket (state, priority, resolution).
  async function updateSupportTicket(id, patch) {
    const sb = await waitForSupabase();
    const profile = await currentProfile();
    const out = { ...patch };
    if (patch.state === "resolved" && !patch.resolved_at) {
      out.resolved_at = new Date().toISOString();
      out.resolved_by = profile && profile.id;
    }
    if (patch.state && patch.state !== "resolved") {
      // Re-opening clears the resolved metadata
      out.resolved_at = null;
      out.resolved_by = null;
    }
    const r = await sb.from("support_tickets").update(out).eq("id", id);
    if (r.error) throw new Error(r.error.message);
  }

  async function createSupportTicket(payload) {
    const sb = await waitForSupabase();
    const profile = await currentProfile();
    if (!profile) throw new Error("Vous devez être connecté.");
    const row = {
      user_id:        profile.id,
      organization_id: profile.organization_id || null,
      category:       payload.category || "bug",
      module:         payload.module || null,
      subject:        payload.subject,
      body:           payload.body,
      steps:          payload.steps || null,
      url:            payload.url || (typeof window !== "undefined" ? window.location.href : null),
      user_agent:     typeof navigator !== "undefined" ? navigator.userAgent : null,
      priority:       payload.priority || "normal",
      attachments:    payload.attachments || null,
    };
    const r = await sb.from("support_tickets").insert(row).select().single();
    if (r.error) throw new Error(r.error.message);
    return r.data;
  }

  // ─── Current organization (read + update) ────────────────────────────
  // Returns the organizations row that the current user belongs to.
  async function fetchCurrentOrganization() {
    const sb = await waitForSupabase();
    const profile = await currentProfile();
    if (!profile || !profile.organization_id) return null;
    const { data, error } = await sb.from("organizations")
      .select("id, name, slug, logo_url, default_locale, created_at, updated_at")
      .eq("id", profile.organization_id)
      .maybeSingle();
    if (error) throw new Error(error.message);
    return data;
  }
  function useCurrentOrganization() {
    const { useState, useEffect, useCallback } = React;
    const [org, setOrg] = useState(null);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      try {
        const o = await fetchCurrentOrganization();
        setOrg(o);
      } catch (e) { console.error("[MELR] org:", e); }
      finally { setLoading(false); }
    }, []);
    useEffect(() => { refresh(); }, [refresh]);
    return { org, loading, refresh };
  }
  async function updateOrganization(patch) {
    const sb = await waitForSupabase();
    const profile = await currentProfile();
    if (!profile || !profile.organization_id) throw new Error("Aucune organisation.");
    const { data, error } = await sb.from("organizations")
      .update(patch).eq("id", profile.organization_id)
      .select().single();
    if (error) throw new Error(error.message);
    return data;
  }

  // ─── Super-admin: all organizations ──────────────────────────────────
  // Visible only to users with the "orgs.manage" permission. RLS in
  // organizations-super-admin.sql lets them SELECT / INSERT / UPDATE /
  // DELETE every org regardless of organization_id scoping.
  function useAllOrganizations() {
    const { useState, useEffect, useCallback } = React;
    const [data, setData]       = useState([]);
    const [loading, setLoading] = useState(true);
    const [realtime, setRealtime] = useState(false);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        // Pull the org rows + per-org counts (members + projects). The
        // profiles table now has TWO FKs to organizations (organization_id
        // for actual membership + requested_organization_id added by the
        // onboarding migration), so we have to spell out which FK to use
        // via the !column hint — otherwise PostgREST refuses the embed.
        const r = await sb.from("organizations")
          .select(`
            id, name, slug, logo_url, default_locale, archived_at, created_at, updated_at,
            members_count:profiles!organization_id(count),
            projects_count:projects(count)
          `)
          .order("name", { ascending: true });
        if (r.error) throw new Error(r.error.message);
        // Embedded counts arrive as [{ count: n }]; flatten
        const rows = (r.data || []).map((o) => ({
          ...o,
          members_count:  (o.members_count  && o.members_count[0]  && o.members_count[0].count)  || 0,
          projects_count: (o.projects_count && o.projects_count[0] && o.projects_count[0].count) || 0,
        }));
        setData(rows);
      } catch (e) { console.warn("[MELR] useAllOrganizations:", e.message || e); setData([]); }
      finally { setLoading(false); }
    }, []);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-orgs-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes", { event: "*", schema: "public", table: "organizations" }, () => { refresh(); })
          .subscribe((s) => { if (s === "SUBSCRIBED" && mounted) setRealtime(true); });
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh]);
    return { data, loading, refresh, realtime };
  }

  const organizationsCrud = {
    async create({ name, slug, default_locale, logo_url }) {
      if (!name || !name.trim()) throw new Error("name required");
      if (!slug || !slug.trim()) throw new Error("slug required");
      // Slug sanity: lowercase + hyphens
      const cleanSlug = slug.trim().toLowerCase().replace(/[^a-z0-9-]+/g, "-").replace(/^-+|-+$/g, "");
      if (!cleanSlug) throw new Error("slug invalid");
      const sb = await waitForSupabase();
      const r = await sb.from("organizations").insert({
        name: name.trim(),
        slug: cleanSlug,
        default_locale: default_locale || "fr",
        logo_url: logo_url || null,
      }).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async update(orgId, patch) {
      const sb = await waitForSupabase();
      const clean = {};
      ["name", "slug", "default_locale", "logo_url"].forEach((k) => {
        if (patch[k] !== undefined) clean[k] = patch[k] === "" ? null : patch[k];
      });
      const r = await sb.from("organizations").update(clean).eq("id", orgId).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async archive(orgId) {
      const sb = await waitForSupabase();
      const r = await sb.from("organizations")
        .update({ archived_at: new Date().toISOString() })
        .eq("id", orgId).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async unarchive(orgId) {
      const sb = await waitForSupabase();
      const r = await sb.from("organizations")
        .update({ archived_at: null })
        .eq("id", orgId).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    // ⚠ Hard delete — cascades to every project / profile / record. The UI
    // wraps this with a typed-confirmation modal where the user must
    // re-enter the slug to proceed.
    async remove(orgId, expectedSlug) {
      if (expectedSlug == null) throw new Error("expectedSlug required (typed confirmation)");
      const sb = await waitForSupabase();
      // Re-read the slug to make sure we're not racing a recent rename
      const cur = await sb.from("organizations").select("slug").eq("id", orgId).single();
      if (cur.error) throw new Error(cur.error.message);
      if (cur.data.slug !== expectedSlug) {
        throw new Error("Confirmation slug ne correspond pas — suppression annulée.");
      }
      const r = await sb.from("organizations").delete().eq("id", orgId);
      if (r.error) throw new Error(r.error.message);
    },
  };

  // ─── Roles & permissions ────────────────────────────────────────────
  function useRoles(targetOrgId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const [realtime, setRealtime] = useState(false);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        let orgId = targetOrgId;
        if (!orgId) {
          const profile = await currentProfile();
          orgId = profile && profile.organization_id;
        }
        if (!orgId) { setData([]); return; }
        const r = await sb.from("roles")
          .select("id, organization_id, code, name_fr, name_en, description, scope, created_at, role_permissions(permission)")
          .eq("organization_id", orgId)
          .order("created_at");
        if (r.error) throw new Error(r.error.message);
        setData((r.data || []).map((row) => ({
          ...row,
          permissions: (row.role_permissions || []).map((rp) => rp.permission),
        })));
      } catch (e) { console.error("[MELR] roles:", e); }
      finally { setLoading(false); }
    }, [targetOrgId]);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        // Subscribe to both roles and role_permissions so the matrix
        // auto-refreshes when either side changes.
        channel = sb.channel("rt-roles-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes", { event: "*", schema: "public", table: "roles" }, () => { refresh(); })
          .on("postgres_changes", { event: "*", schema: "public", table: "role_permissions" }, () => { refresh(); })
          .subscribe((status) => { if (status === "SUBSCRIBED" && mounted) setRealtime(true); });
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh]);
    return { data, loading, refresh, realtime };
  }

  const rolesCrud = {
    // payload may include organization_id (used by super-admin acting-as-org);
    // otherwise defaults to the caller's own org.
    async create(payload) {
      const sb = await waitForSupabase();
      let orgId = payload && payload.organization_id;
      if (!orgId) {
        const profile = await currentProfile();
        if (!profile || !profile.organization_id) throw new Error("Profil sans organisation.");
        orgId = profile.organization_id;
      }
      const row = { ...payload, organization_id: orgId };
      const r = await sb.from("roles").insert(row).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async update(id, patch) {
      const sb = await waitForSupabase();
      const r = await sb.from("roles").update(patch).eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
    async remove(id) {
      const sb = await waitForSupabase();
      const r = await sb.from("roles").delete().eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
    // Toggle a single permission for a role
    async togglePermission(roleId, permission, enabled) {
      const sb = await waitForSupabase();
      if (enabled) {
        const r = await sb.from("role_permissions")
          .upsert({ role_id: roleId, permission }, { onConflict: "role_id,permission" });
        if (r.error) throw new Error(r.error.message);
      } else {
        const r = await sb.from("role_permissions")
          .delete().eq("role_id", roleId).eq("permission", permission);
        if (r.error) throw new Error(r.error.message);
      }
    },
  };

  async function seedDefaultRoles(targetOrgId) {
    const sb = await waitForSupabase();
    // The RPC accepts an optional target_org_id (super-admin can target
    // any org). When unspecified, it defaults to the caller's own org.
    const r = await sb.rpc("seed_default_roles", targetOrgId ? { target_org_id: targetOrgId } : {});
    if (r.error) throw new Error(r.error.message);
    return r.data;
  }

  // user_roles assignments (for the current org)
  function useUserRoles() {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        const profile = await currentProfile();
        if (!profile || !profile.organization_id) { setData([]); return; }
        // Pull all user_roles where user.org = current. RLS already enforces this.
        const r = await sb.from("user_roles")
          .select("id, user_id, role_id, scope_type, scope_id, granted_at, roles(code, name_fr, name_en)");
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.error("[MELR] user_roles:", e); }
      finally { setLoading(false); }
    }, []);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-user-roles-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes", { event: "*", schema: "public", table: "user_roles" }, () => { refresh(); })
          .subscribe();
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh]);
    return { data, loading, refresh };
  }

  const userRolesCrud = {
    async assign(userId, roleId, scopeType, scopeId) {
      const sb = await waitForSupabase();
      const profile = await currentProfile();
      const row = {
        user_id: userId, role_id: roleId,
        scope_type: scopeType || "organization",
        scope_id: scopeId || null,
        granted_by: profile && profile.id,
      };
      const r = await sb.from("user_roles").insert(row).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async remove(assignmentId) {
      const sb = await waitForSupabase();
      const r = await sb.from("user_roles").delete().eq("id", assignmentId);
      if (r.error) throw new Error(r.error.message);
    },
  };

  // ─── Project-agent assignments ───────────────────────────────────────
  // The project_agents table scopes which forms a field agent sees. See
  // Supabase/project-agents.sql for the schema + RLS.
  //
  // Hooks:
  //   useProjectAgents()        — every assignment in the current org (admin)
  //   useMyProjects()           — projects the *current user* is assigned to
  //
  // CRUD (admins only by RLS):
  //   projectAgentsCrud.assign(projectId, agentId, role?)
  //   projectAgentsCrud.remove(projectId, agentId)

  function useProjectAgents() {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        // RLS filters to admin's org rows.
        const r = await sb.from("project_agents")
          .select("project_id, agent_id, role, added_at, projects(code, name_fr, name_en), profiles!project_agents_agent_id_fkey(full_name, email)")
          .order("added_at", { ascending: false });
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.error("[MELR] project_agents:", e); }
      finally { setLoading(false); }
    }, []);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-project-agents-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes", { event: "*", schema: "public", table: "project_agents" }, () => { refresh(); })
          .subscribe();
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh]);
    return { data, loading, refresh };
  }

  // Projects the current user is assigned to (uses my_projects view).
  // Returns [{ id, code, name_fr, name_en, sector_id, status, agent_role, assigned_at }, …].
  function useMyProjects() {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("my_projects")
          .select("id, code, name_fr, name_en, sector_id, status, agent_role, assigned_at")
          .order("code", { ascending: true });
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) {
        // View may not exist yet (migration not run). Surface empty + warning.
        console.warn("[MELR] my_projects unavailable:", e.message || e);
        setData([]);
      } finally { setLoading(false); }
    }, []);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        // Refresh on any project_agents change touching the current user
        channel = sb.channel("rt-my-projects-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes", { event: "*", schema: "public", table: "project_agents" }, () => { refresh(); })
          .subscribe();
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh]);
    return { data, loading, refresh };
  }

  const projectAgentsCrud = {
    async assign(projectId, agentId, role) {
      if (!projectId || !agentId) throw new Error("project_id and agent_id required");
      const sb = await waitForSupabase();
      const profile = await currentProfile();
      const row = {
        project_id: projectId,
        agent_id:   agentId,
        role:       role || null,
        added_by:   profile && profile.id,
      };
      // Upsert so re-adding the same pair is a no-op rather than an error.
      const r = await sb.from("project_agents")
        .upsert(row, { onConflict: "project_id,agent_id" })
        .select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async remove(projectId, agentId) {
      const sb = await waitForSupabase();
      const r = await sb.from("project_agents")
        .delete()
        .eq("project_id", projectId)
        .eq("agent_id", agentId);
      if (r.error) throw new Error(r.error.message);
    },
  };

  // ─── Form submissions Excel export ──────────────────────────────────
  // Builds a multi-sheet .xlsx for a single form: one row per submission
  // with one column per schema field, plus metadata (id, captured/uploaded
  // timestamps, site, agent, GPS). Attachment fields are flattened to the
  // file name in the main sheet; an "Attachments" sheet lists each file
  // with a freshly-signed URL (1h expiry) so the user can download photos
  // and signatures referenced by the submissions. A "Schema" sheet
  // captures the form definition at export time.
  async function exportFormSubmissions(formId, opts) {
    if (!formId) throw new Error("formId is required");
    if (!window.XLSX) throw new Error("XLSX library is not loaded");
    const options = opts || {};
    const startISO = options.startDate ? new Date(options.startDate + "T00:00:00").toISOString() : null;
    const endISO   = options.endDate   ? new Date(options.endDate   + "T23:59:59").toISOString() : null;

    const sb = await waitForSupabase();
    // 1) Form definition
    const formRes = await sb.from("forms")
      .select("id, code, name_fr, name_en, version, schema, projects(code, name_fr)")
      .eq("id", formId).single();
    if (formRes.error) throw new Error(formRes.error.message);
    const form = formRes.data;
    const schemaFields = (form.schema && Array.isArray(form.schema.fields)) ? form.schema.fields : [];

    // 2) Submissions for that form, with site + agent metadata joined
    let q = sb.from("form_submissions")
      .select("id, captured_at, uploaded_at, state, size_bytes, data, sites(code, name), agent:profiles!form_submissions_agent_id_fkey(full_name, email)")
      .eq("form_id", formId)
      .order("uploaded_at", { ascending: false });
    if (startISO) q = q.gte("uploaded_at", startISO);
    if (endISO)   q = q.lte("uploaded_at", endISO);
    const subRes = await q;
    if (subRes.error) throw new Error(subRes.error.message);
    const subs = subRes.data || [];

    // 3) Build the "Saisies" sheet — header row first
    const metaCols = [
      { k: "id",          h: "Submission ID" },
      { k: "captured_at", h: "Captured (terrain)" },
      { k: "uploaded_at", h: "Uploaded (serveur)" },
      { k: "state",       h: "State" },
      { k: "site_code",   h: "Site code" },
      { k: "site_name",   h: "Site name" },
      { k: "agent",       h: "Agent" },
      { k: "geo_lat",     h: "GPS latitude" },
      { k: "geo_lng",     h: "GPS longitude" },
      { k: "geo_accuracy",h: "GPS accuracy (m)" },
    ];
    const fieldCols = schemaFields.map((f) => ({ k: f.k, h: f.l || f.k, type: f.type }));
    const allCols = [...metaCols, ...fieldCols];

    const formatCell = (col, sub) => {
      if (col.k === "id")           return sub.id;
      if (col.k === "captured_at")  return sub.captured_at || "";
      if (col.k === "uploaded_at")  return sub.uploaded_at || "";
      if (col.k === "state")        return sub.state || "";
      if (col.k === "site_code")    return sub.sites ? sub.sites.code : "";
      if (col.k === "site_name")    return sub.sites ? sub.sites.name : "";
      if (col.k === "agent")        return sub.agent ? (sub.agent.full_name || sub.agent.email) : "";
      const geo = (sub.data && sub.data._geo) || null;
      if (col.k === "geo_lat")      return geo ? geo.lat : "";
      if (col.k === "geo_lng")      return geo ? geo.lng : "";
      if (col.k === "geo_accuracy") return geo ? geo.accuracy : "";
      // Schema field
      const v = sub.data ? sub.data[col.k] : undefined;
      if (v === null || v === undefined || v === "") return "";
      if (typeof v === "boolean") return v ? "TRUE" : "FALSE";
      if (typeof v === "object") {
        // Attachment marker — store the file name (URL is in the
        // attachments sheet so Excel can be re-opened later without
        // hitting expired signed URLs).
        if (v._attachment || v._pendingUpload) return v.name || "(file)";
        return JSON.stringify(v);
      }
      return v;
    };

    const headerRow = allCols.map((c) => c.h);
    const dataRows = subs.map((s) => allCols.map((c) => formatCell(c, s)));
    const aoa = [headerRow, ...dataRows];

    // 4) Build the "Attachments" sheet — one row per attachment with a
    // freshly-signed URL. Skipped if no submission has any attachment.
    const attachmentRows = [];
    const includeUrls = options.includeAttachments !== false;
    if (includeUrls) {
      const attachmentFieldKeys = schemaFields
        .filter((f) => f.type === "photo" || f.type === "signature")
        .map((f) => f.k);
      if (attachmentFieldKeys.length > 0) {
        // Collect (submission_id, field_key, path, mime, size, name) tuples
        const tasks = [];
        subs.forEach((s) => {
          attachmentFieldKeys.forEach((k) => {
            const v = s.data && s.data[k];
            if (v && (v._attachment || v._pendingUpload) && v.path) {
              tasks.push({ submission_id: s.id, field_key: k, name: v.name, mime: v.mime, size: v.size, path: v.path });
            }
          });
        });
        // Sign URLs in batches (Supabase has a 1000-path limit per createSignedUrls)
        const BATCH = 200;
        for (let i = 0; i < tasks.length; i += BATCH) {
          const slice = tasks.slice(i, i + BATCH);
          const paths = slice.map((t) => t.path);
          const r = await sb.storage.from("form-attachments").createSignedUrls(paths, 3600);
          if (r.error) {
            // Fall back to per-file signing on error
            for (const t of slice) {
              try {
                const one = await sb.storage.from("form-attachments").createSignedUrl(t.path, 3600);
                t.url = (one.data && one.data.signedUrl) || "";
              } catch (_) { t.url = ""; }
            }
          } else {
            (r.data || []).forEach((d, idx) => { slice[idx].url = (d && d.signedUrl) || ""; });
          }
          attachmentRows.push(...slice);
        }
      }
    }

    // 5) Build the workbook
    const wb = window.XLSX.utils.book_new();
    const wsMain = window.XLSX.utils.aoa_to_sheet(aoa);
    // Set reasonable column widths
    wsMain["!cols"] = allCols.map((c) => ({ wch: Math.min(40, Math.max(12, c.h.length + 2)) }));
    window.XLSX.utils.book_append_sheet(wb, wsMain, "Saisies");

    if (attachmentRows.length > 0) {
      const attHeader = ["Submission ID", "Field key", "File name", "MIME", "Size (bytes)", "Storage path", "Signed URL (1h)"];
      const attAoa = [attHeader, ...attachmentRows.map((a) => [
        a.submission_id, a.field_key, a.name || "", a.mime || "", a.size || "", a.path, a.url || "",
      ])];
      const wsAtt = window.XLSX.utils.aoa_to_sheet(attAoa);
      wsAtt["!cols"] = [
        { wch: 38 }, { wch: 16 }, { wch: 28 }, { wch: 18 }, { wch: 14 }, { wch: 50 }, { wch: 60 },
      ];
      window.XLSX.utils.book_append_sheet(wb, wsAtt, "Pièces jointes");
    }

    // Schema reference sheet
    const schemaAoa = [
      ["Form code", form.code],
      ["Form name (FR)", form.name_fr],
      ["Form name (EN)", form.name_en || ""],
      ["Version", form.version || ""],
      ["Project", form.projects ? (form.projects.code + " · " + form.projects.name_fr) : ""],
      ["Exported at", new Date().toISOString()],
      ["Submissions exported", subs.length],
      [],
      ["Schema fields:"],
      ["Key", "Label", "Type", "Options"],
      ...schemaFields.map((f) => [
        f.k, f.l || "", f.type || "text",
        Array.isArray(f.options) ? f.options.map((o) => (o.value || o) + (o.label ? "=" + o.label : "")).join(" | ") : "",
      ]),
    ];
    const wsSchema = window.XLSX.utils.aoa_to_sheet(schemaAoa);
    wsSchema["!cols"] = [{ wch: 22 }, { wch: 40 }, { wch: 16 }, { wch: 40 }];
    window.XLSX.utils.book_append_sheet(wb, wsSchema, "Schéma");

    // 6) Trigger download
    const filename = (form.code || "form") + "_" + new Date().toISOString().slice(0, 10) + ".xlsx";
    window.XLSX.writeFile(wb, filename);
    return { count: subs.length, attachments: attachmentRows.length, filename };
  }

  // ─── Forms CRUD ──────────────────────────────────────────────────────
  // Admin-only by RLS (forms_admin_insert / _update / _delete in
  // Supabase/form-agents.sql). The form builder UI uses these.
  const formsCrud = {
    async create({ project_id, code, name_fr, name_en, version, schema, active }) {
      if (!code) throw new Error("code requis");
      if (!name_fr) throw new Error("name_fr requis");
      const sb = await waitForSupabase();
      const r = await sb.from("forms").insert({
        project_id: project_id || null,
        code, name_fr,
        name_en: name_en || null,
        version: version || "v1",
        schema:  schema  || { fields: [] },
        active:  active !== false,
      }).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async update(formId, patch) {
      const sb = await waitForSupabase();
      // Don't chain .select().single() — the SELECT half of UPDATE…RETURNING
      // can return 0 rows even when the UPDATE succeeded (RLS scopes for
      // SELECT and UPDATE differ slightly in some setups, e.g. multi-org
      // membership where the user can SELECT via one policy but the
      // post-update row falls outside their SELECT scope for a moment).
      // PostgREST surfaces 0 rows as 'Cannot coerce the result to a single
      // JSON object', which is misleading. We just trust formId and re-fetch.
      const u = await sb.from("forms").update(patch).eq("id", formId);
      if (u.error) throw new Error(u.error.message);
      // Re-fetch with maybeSingle so 0 rows = null (no error).
      const r = await sb.from("forms")
        .select("id, code, name_fr, name_en, version, schema, active, project_id, projects(code, name_fr, name_en)")
        .eq("id", formId).maybeSingle();
      if (r.error) throw new Error(r.error.message);
      // If RLS hides the row from the caller post-update, return a synthetic
      // shape so the caller's `saved.id` chain still works.
      return r.data || { id: formId, ...patch };
    },
    async remove(formId) {
      const sb = await waitForSupabase();
      const r = await sb.from("forms").delete().eq("id", formId);
      if (r.error) throw new Error(r.error.message);
    },
    async setActive(formId, active) {
      return this.update(formId, { active: !!active });
    },
  };

  // ─── Form agents (fine-grained per-form restriction) ────────────────
  // When form_agents has at least one row for a form, only listed agents
  // (plus admins) can see/submit that form within the project. When empty,
  // every agent of the project sees it (project_agents fallback).
  function useFormAgents(formId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      if (!formId) { setData([]); setLoading(false); return; }
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("form_agents")
          .select("form_id, agent_id, added_at, profiles!form_agents_agent_id_fkey(full_name, email)")
          .eq("form_id", formId);
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.warn("[MELR] form_agents:", e.message || e); setData([]); }
      finally { setLoading(false); }
    }, [formId]);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-form-agents-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes", { event: "*", schema: "public", table: "form_agents", filter: formId ? ("form_id=eq." + formId) : undefined }, () => { refresh(); })
          .subscribe();
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh, formId]);
    return { data, loading, refresh };
  }

  const formAgentsCrud = {
    async assign(formId, agentId) {
      if (!formId || !agentId) throw new Error("form_id and agent_id required");
      const sb = await waitForSupabase();
      const profile = await currentProfile();
      const r = await sb.from("form_agents")
        .upsert({ form_id: formId, agent_id: agentId, added_by: profile && profile.id }, { onConflict: "form_id,agent_id" })
        .select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async remove(formId, agentId) {
      const sb = await waitForSupabase();
      const r = await sb.from("form_agents").delete()
        .eq("form_id", formId).eq("agent_id", agentId);
      if (r.error) throw new Error(r.error.message);
    },
    // Replace the whole restriction list at once (used by the form
    // builder after the admin saves their multi-select).
    async setAll(formId, agentIds) {
      const sb = await waitForSupabase();
      // 1) Delete current rows
      const del = await sb.from("form_agents").delete().eq("form_id", formId);
      if (del.error) throw new Error(del.error.message);
      // 2) Insert new ones (if any)
      if (!agentIds || agentIds.length === 0) return;
      const profile = await currentProfile();
      const rows = agentIds.map((aid) => ({
        form_id: formId, agent_id: aid, added_by: profile && profile.id,
      }));
      const ins = await sb.from("form_agents").insert(rows);
      if (ins.error) throw new Error(ins.error.message);
    },
  };

  // Upload a single file to the validation-attachments Supabase Storage
  // bucket. Path convention: <validation_item_id>/<random>__<filename>
  // so the storage RLS policy can join back to the parent item.
  // Returns { path, name, size, type }.
  async function uploadValidationAttachment(itemId, file) {
    if (!itemId || !file) throw new Error("itemId et file requis.");
    const sb = await waitForSupabase();
    const rand = Math.random().toString(36).slice(2, 10);
    const safeName = file.name.replace(/[^A-Za-z0-9._-]/g, "_");
    const path = itemId + "/" + rand + "__" + safeName;
    const { error } = await sb.storage
      .from("validation-attachments")
      .upload(path, file, { cacheControl: "3600", upsert: false });
    if (error) throw new Error(error.message);
    return { path, name: file.name, size: file.size, type: file.type || "" };
  }

  // Generate a short-lived signed URL for a stored path (default 1h).
  async function getValidationAttachmentUrl(path, expiresIn) {
    if (!path) return null;
    const sb = await waitForSupabase();
    const { data, error } = await sb.storage
      .from("validation-attachments")
      .createSignedUrl(path, expiresIn || 3600);
    if (error) throw new Error(error.message);
    return data && data.signedUrl;
  }

  // ─────────────────────────────────────────────────────────────────
  // Project documents — the "Documents" tab on the project page.
  // Backed by public.documents (one row per file) and the Supabase
  // Storage bucket 'project-documents' (one folder per project).
  // ─────────────────────────────────────────────────────────────────
  function useProjectDocuments(projectId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(!!projectId);
    const refresh = useCallback(async () => {
      if (!projectId) { setData([]); setLoading(false); return; }
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("documents")
          .select("id, project_id, name, storage_path, size_bytes, mime, uploaded_by, uploaded_at, profile:uploaded_by(full_name)")
          .eq("project_id", projectId)
          .order("uploaded_at", { ascending: false });
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.error("[MELR] project documents:", e); }
      finally { setLoading(false); }
    }, [projectId]);
    useEffect(() => { refresh(); }, [refresh]);
    return { data, loading, refresh };
  }

  // Upload a file for a project. Creates the storage object first; only
  // if that succeeds do we insert the metadata row (so we never leave
  // dangling rows pointing at missing blobs). Returns the new row.
  async function uploadProjectDocument(projectId, file) {
    if (!projectId || !file) throw new Error("projectId et file requis.");
    const sb = await waitForSupabase();
    const profile = await currentProfile();
    const rand = Math.random().toString(36).slice(2, 10);
    const safeName = file.name.replace(/[^A-Za-z0-9._-]/g, "_");
    const path = projectId + "/" + rand + "__" + safeName;
    const up = await sb.storage
      .from("project-documents")
      .upload(path, file, { cacheControl: "3600", upsert: false });
    if (up.error) throw new Error(up.error.message);
    const ins = await sb.from("documents").insert({
      project_id:   projectId,
      name:         file.name,
      storage_path: path,
      size_bytes:   file.size,
      mime:         file.type || null,
      uploaded_by:  profile && profile.id,
    }).select().single();
    if (ins.error) {
      // Roll back the storage object on metadata failure so we don't
      // leak blobs.
      try { await sb.storage.from("project-documents").remove([path]); } catch (_) {}
      throw new Error(ins.error.message);
    }
    return ins.data;
  }

  // Short-lived signed URL for download (default 1h).
  async function getProjectDocumentUrl(path, expiresIn) {
    if (!path) return null;
    const sb = await waitForSupabase();
    const { data, error } = await sb.storage
      .from("project-documents")
      .createSignedUrl(path, expiresIn || 3600);
    if (error) throw new Error(error.message);
    return data && data.signedUrl;
  }

  // ─────────────────────────────────────────────────────────────────
  // Project team — the "Équipe" tab on the project page. Backed by
  // public.project_team (project_id + user_id + role_label).
  // ─────────────────────────────────────────────────────────────────
  function useProjectTeam(projectId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(!!projectId);
    const refresh = useCallback(async () => {
      if (!projectId) { setData([]); setLoading(false); return; }
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("project_team")
          .select("project_id, user_id, role_label, joined_at, profile:user_id(id, full_name, email, avatar_url)")
          .eq("project_id", projectId)
          .order("joined_at", { ascending: true });
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.error("[MELR] project_team:", e); }
      finally { setLoading(false); }
    }, [projectId]);
    useEffect(() => { refresh(); }, [refresh]);
    return { data, loading, refresh };
  }
  // ─────────────────────────────────────────────────────────────────
  // Project budget lines — the "Budget" tab on the project page.
  // Backed by public.budget_lines (project_id + category + label +
  // budget/disbursed/committed numeric + currency char(3) + position
  // int). RLS: read = members of project's org; write = same.
  // ─────────────────────────────────────────────────────────────────
  function useProjectBudget(projectId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(!!projectId);
    const refresh = useCallback(async () => {
      if (!projectId) { setData([]); setLoading(false); return; }
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("budget_lines")
          .select("id, project_id, category, label, budget, disbursed, committed, currency, position")
          .eq("project_id", projectId)
          .order("position", { ascending: true })
          .order("category");
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.error("[MELR] budget_lines:", e); }
      finally { setLoading(false); }
    }, [projectId]);
    useEffect(() => { refresh(); }, [refresh]);
    return { data, loading, refresh };
  }
  const budgetLinesCrud = {
    async create(projectId, payload) {
      if (!projectId) throw new Error("projectId requis.");
      const sb = await waitForSupabase();
      const r = await sb.from("budget_lines").insert({ ...payload, project_id: projectId }).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async update(id, patch) {
      const sb = await waitForSupabase();
      const r = await sb.from("budget_lines").update(patch).eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
    async remove(id) {
      const sb = await waitForSupabase();
      const r = await sb.from("budget_lines").delete().eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
  };

  // ─────────────────────────────────────────────────────────────────
  // Project risks — the "Risques" tab on the project page. Backed by
  // public.risks (project_id + level L/M/H + title + mitigation +
  // probability 1-5 + impact 1-5 + status + owner_id).
  // RLS: read = members of project's org; write/update/delete = org
  // admins (or super-admin) only.
  // ─────────────────────────────────────────────────────────────────
  function useProjectRisks(projectId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(!!projectId);
    const refresh = useCallback(async () => {
      if (!projectId) { setData([]); setLoading(false); return; }
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("risks")
          .select("id, project_id, level, title, mitigation, probability, impact, status, owner_id, created_at, owner:owner_id(id, full_name)")
          .eq("project_id", projectId)
          .order("created_at", { ascending: false });
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.error("[MELR] risks:", e); }
      finally { setLoading(false); }
    }, [projectId]);
    useEffect(() => { refresh(); }, [refresh]);
    return { data, loading, refresh };
  }
  const risksCrud = {
    async create(projectId, payload) {
      if (!projectId) throw new Error("projectId requis.");
      const sb = await waitForSupabase();
      const r = await sb.from("risks").insert({ ...payload, project_id: projectId }).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async update(id, patch) {
      const sb = await waitForSupabase();
      const r = await sb.from("risks").update(patch).eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
    async remove(id) {
      const sb = await waitForSupabase();
      const r = await sb.from("risks").delete().eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
  };

  // ─────────────────────────────────────────────────────────────────
  // Project activity — the "Activité" tab on the project page. Backed
  // by public.activity_logs (organization_id + project_id + actor_id +
  // action + entity + entity_id + details jsonb + occurred_at).
  // Read-only on the front-end: writes happen via SECURITY DEFINER
  // helpers from other CRUD paths.
  // ─────────────────────────────────────────────────────────────────
  function useProjectActivity(projectId, limit) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(!!projectId);
    const refresh = useCallback(async () => {
      if (!projectId) { setData([]); setLoading(false); return; }
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("activity_logs")
          .select("id, project_id, actor_id, action, entity, entity_id, details, occurred_at, actor:actor_id(id, full_name)")
          .eq("project_id", projectId)
          .order("occurred_at", { ascending: false })
          .limit(limit || 50);
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.error("[MELR] activity_logs:", e); }
      finally { setLoading(false); }
    }, [projectId, limit]);
    useEffect(() => { refresh(); }, [refresh]);
    return { data, loading, refresh };
  }

  // ─────────────────────────────────────────────────────────────────
  // Organization modules — per-org module enable/disable flags.
  // Convention: default-ON. Only rows with enabled=false are stored.
  // Reads open to org members (so the front-end can gate nav/routes);
  // writes restricted to super-admin (enforced by RLS).
  //
  // The hook subscribes to realtime so the sidebar refreshes the
  // moment the platform operator toggles a module.
  // ─────────────────────────────────────────────────────────────────
  function useDisabledModules(orgId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);     // raw rows
    const [loading, setLoading] = useState(!!orgId);
    const refresh = useCallback(async () => {
      if (!orgId) { setData([]); setLoading(false); return; }
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("organization_modules")
          .select("organization_id, module_code, enabled, configured_at")
          .eq("organization_id", orgId);
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.error("[MELR] organization_modules:", e); }
      finally { setLoading(false); }
    }, [orgId]);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted || !orgId) return;
        const sb = await waitForSupabase();
        // Realtime: any toggle by super-admin propagates immediately.
        channel = sb.channel("rt-org-modules-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes",
            { event: "*", schema: "public", table: "organization_modules", filter: "organization_id=eq." + orgId },
            () => { refresh(); })
          .subscribe();
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh, orgId]);

    // Memoised Set of disabled module codes — what the front-end gates on.
    const disabledSet = React.useMemo(() => {
      const s = new Set();
      for (const row of data) {
        if (row && row.enabled === false) s.add(row.module_code);
      }
      return s;
    }, [data]);

    return { disabledSet, data, loading, refresh };
  }

  // For the super-admin UI: list every org's settings in one shot so we
  // can render a per-org checkbox grid without N round-trips.
  function useAllOrganizationModules() {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("organization_modules")
          .select("organization_id, module_code, enabled, configured_at");
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.error("[MELR] all organization_modules:", e); }
      finally { setLoading(false); }
    }, []);
    useEffect(() => { refresh(); }, [refresh]);
    return { data, loading, refresh };
  }

  const organizationModulesCrud = {
    // Toggle a module for an org. enabled=true removes the disable
    // row (default-ON); enabled=false inserts/updates one. Returns
    // the resulting row (or null if removed).
    async setEnabled(orgId, moduleCode, enabled) {
      if (!orgId || !moduleCode) throw new Error("orgId + moduleCode requis.");
      const sb = await waitForSupabase();
      const profile = await currentProfile();
      if (enabled === true) {
        // Default is enabled — drop the row if present.
        const r = await sb.from("organization_modules")
          .delete()
          .eq("organization_id", orgId)
          .eq("module_code", moduleCode);
        if (r.error) throw new Error(r.error.message);
        return null;
      }
      // Upsert a disable row.
      const row = {
        organization_id: orgId,
        module_code:     moduleCode,
        enabled:         false,
        configured_by:   profile && profile.id,
      };
      const r = await sb.from("organization_modules")
        .upsert(row, { onConflict: "organization_id,module_code" })
        .select()
        .single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
  };

  const projectTeamCrud = {
    async add(projectId, userId, roleLabel) {
      if (!projectId || !userId) throw new Error("projectId + userId requis.");
      const sb = await waitForSupabase();
      const r = await sb.from("project_team").insert({
        project_id: projectId,
        user_id:    userId,
        role_label: roleLabel || null,
      });
      if (r.error) throw new Error(r.error.message);
    },
    async updateLabel(projectId, userId, roleLabel) {
      const sb = await waitForSupabase();
      const r = await sb.from("project_team")
        .update({ role_label: roleLabel || null })
        .eq("project_id", projectId)
        .eq("user_id", userId);
      if (r.error) throw new Error(r.error.message);
    },
    async remove(projectId, userId) {
      const sb = await waitForSupabase();
      const r = await sb.from("project_team")
        .delete()
        .eq("project_id", projectId)
        .eq("user_id", userId);
      if (r.error) throw new Error(r.error.message);
    },
  };

  // Delete a project document: remove the row first (RLS lets uploader
  // or org admins delete), then drop the storage object. Order chosen
  // so a partial failure leaves an orphan blob rather than an orphan
  // row — orphan blobs are easier to garbage-collect later.
  async function removeProjectDocument(docId, storagePath) {
    if (!docId) throw new Error("docId requis.");
    const sb = await waitForSupabase();
    const r = await sb.from("documents").delete().eq("id", docId);
    if (r.error) throw new Error(r.error.message);
    if (storagePath) {
      try { await sb.storage.from("project-documents").remove([storagePath]); }
      catch (e) { console.warn("[MELR] orphan blob:", storagePath, e); }
    }
  }

  // Validation templates — org-level approval-chain config
  function useValidationTemplates() {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        const profile = await currentProfile();
        if (!profile || !profile.organization_id) { setData([]); return; }
        const r = await sb.from("validation_templates")
          .select("id, organization_id, object_type, steps, sla_days, reminder_hours, active")
          .eq("organization_id", profile.organization_id)
          .order("object_type");
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.error("[MELR] validation_templates:", e); }
      finally { setLoading(false); }
    }, []);
    useEffect(() => { refresh(); }, [refresh]);
    return { data, loading, refresh };
  }
  const validationTemplatesCrud = {
    async create(payload) {
      const sb = await waitForSupabase();
      const profile = await currentProfile();
      if (!profile || !profile.organization_id) throw new Error("Profil sans organisation.");
      const row = { ...payload, organization_id: profile.organization_id };
      const r = await sb.from("validation_templates").insert(row).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async update(id, patch) {
      const sb = await waitForSupabase();
      const r = await sb.from("validation_templates").update(patch).eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
    async remove(id) {
      const sb = await waitForSupabase();
      const r = await sb.from("validation_templates").delete().eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
  };

  // Alert rules — org-scoped notification triggers. Schema: name (text),
  // condition (jsonb DSL like {indicator:'PORT.1', op:'<', target:0.9}),
  // channels (notification_channel[]), frequency (text), active (boolean).
  // RLS restricts reads to the caller's org and writes to org admins.
  function useAlertRules() {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        const profile = await currentProfile();
        if (!profile || !profile.organization_id) { setData([]); return; }
        const r = await sb.from("alert_rules")
          .select("id, organization_id, name, condition, channels, frequency, active")
          .eq("organization_id", profile.organization_id)
          .order("name");
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.error("[MELR] alert_rules:", e); }
      finally { setLoading(false); }
    }, []);
    useEffect(() => { refresh(); }, [refresh]);
    return { data, loading, refresh };
  }
  const alertRulesCrud = {
    async create(payload) {
      const sb = await waitForSupabase();
      const profile = await currentProfile();
      if (!profile || !profile.organization_id) throw new Error("Profil sans organisation.");
      const row = { ...payload, organization_id: profile.organization_id };
      const r = await sb.from("alert_rules").insert(row).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async update(id, patch) {
      const sb = await waitForSupabase();
      const r = await sb.from("alert_rules").update(patch).eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
    async remove(id) {
      const sb = await waitForSupabase();
      const r = await sb.from("alert_rules").delete().eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
  };

  // Notify several users at once — used by the inline comment box on the
  // workflow screen so @-mentioned users get an in-app alert.
  async function createBulkNotifications(rows) {
    if (!rows || rows.length === 0) return;
    const sb = await waitForSupabase();
    const r = await sb.from("notifications").insert(rows);
    if (r.error) throw new Error(r.error.message);
  }

  // Re-assign a validation item to a specific user (or clear with null).
  // The notify_validation_assignee_change trigger fires automatically.
  async function assignValidationItem(itemId, assigneeId) {
    const sb = await waitForSupabase();
    const r = await sb.from("validation_items")
      .update({ current_assignee_id: assigneeId || null })
      .eq("id", itemId);
    if (r.error) throw new Error(r.error.message);
  }

  // Submit any object (SAT eval, DVT audit, report …) for validation.
  // Creates a row in validation_items linked to the source object. Idempotent
  // by default: if an in-flight item (submitted / in_review / sent_back)
  // exists for the same object_id, returns it instead of inserting a duplicate.
  //
  // payload = {
  //   object_type:  'sat_evaluation' | 'dvt_audit' | 'report' | …
  //   object_id:    uuid
  //   project_id:   uuid (optional)
  //   title:        string
  //   total_steps:  int (default 3)
  //   sla_days:     int (default 5)
  //   priority:     'normal' | 'high'  (default 'normal')
  // }
  async function submitForValidation(payload) {
    const sb = await waitForSupabase();
    const profile = await currentProfile();
    if (!profile) throw new Error("Vous devez être connecté.");
    if (!payload.object_type || !payload.object_id || !payload.title) {
      throw new Error("object_type, object_id et title sont requis.");
    }

    // Check for an in-flight item on the same object
    const existing = await sb.from("validation_items")
      .select("id, state, current_step, total_steps")
      .eq("object_id", payload.object_id)
      .in("state", ["submitted", "in_review", "sent_back"])
      .limit(1);
    if (existing.error) throw new Error(existing.error.message);
    if (existing.data && existing.data.length > 0) {
      return { reused: true, item: existing.data[0] };
    }

    // Look up the active template for this object_type. If the org has
    // configured one in the Workflow → "Configurer flux" modal, its
    // steps.length and sla_days override the caller's defaults — that's
    // the whole point of having configurable templates.
    let template = null;
    try {
      const tplR = await sb.from("validation_templates")
        .select("id, steps, sla_days")
        .eq("organization_id", profile.organization_id)
        .eq("object_type", payload.object_type)
        .eq("active", true)
        .order("id", { ascending: false })
        .limit(1)
        .maybeSingle();
      if (tplR.data) template = tplR.data;
    } catch (e) {
      console.warn("[submitForValidation] template lookup failed:", e.message);
    }

    const stepsFromTemplate = template && Array.isArray(template.steps) ? template.steps.length : 0;
    const totalSteps = stepsFromTemplate > 0
      ? stepsFromTemplate
      : (payload.total_steps || 3);
    const slaDays = (template && template.sla_days) || payload.sla_days || 5;
    const dueAt = new Date(Date.now() + slaDays * 86400000).toISOString();

    const row = {
      organization_id:    profile.organization_id,
      template_id:        template ? template.id : null,
      object_type:        payload.object_type,
      object_id:          payload.object_id,
      project_id:         payload.project_id || null,
      title:              payload.title,
      total_steps:        totalSteps,
      priority:           payload.priority || "normal",
      submitted_by:       profile.id,
      due_at:             dueAt,
      state:              "submitted",
      current_step:       0,
      current_assignee_id: payload.assignee_id || null,
    };
    const r = await sb.from("validation_items").insert(row).select().single();
    if (r.error) throw new Error(r.error.message);
    return { reused: false, item: r.data, template };
  }

  // Insert a row into validation_actions: a comment, delegation,
  // approval, send-back, etc. `payload` looks like:
  //   { action: 'comment',  step_index: 0, note: 'text' }
  //   { action: 'delegate', step_index: 0, attachments: { delegated_to: 'uuid' } }
  //   { action: 'approve',  step_index: 0, note: 'optional comment' }
  async function createValidationAction(itemId, payload) {
    const sb = await waitForSupabase();
    const profile = await currentProfile();
    if (!profile) throw new Error("Vous devez être connecté.");
    const insertRow = {
      item_id:     itemId,
      step_index:  payload.step_index == null ? 0 : Number(payload.step_index),
      actor_id:    profile.id,
      action:      payload.action,
      note:        payload.note || null,
      attachments: payload.attachments || null,
    };
    const { data, error } = await sb.from("validation_actions").insert(insertRow).select().single();
    if (error) throw new Error(error.message);
    return data;
  }

  // List profiles in the current user's organization, used as a picker
  // for delegation.
  async function fetchOrgProfiles() {
    const sb = await waitForSupabase();
    const profile = await currentProfile();
    if (!profile || !profile.organization_id) return [];
    const { data, error } = await sb.from("profiles")
      .select("id, full_name, email")
      .eq("organization_id", profile.organization_id)
      .order("full_name");
    if (error) throw new Error(error.message);
    return data || [];
  }

  // Pending users (no organization). Uses an RPC function because RLS
  // hides these from regular SELECT queries.
  async function fetchPendingUsers() {
    const sb = await waitForSupabase();
    const { data, error } = await sb.rpc("list_pending_users");
    if (error) throw new Error(error.message);
    return data || [];
  }

  // Add a user to the caller's organization (or to a specific org slug).
  async function assignUserToOrg(email, orgSlug) {
    const sb = await waitForSupabase();
    const { error } = await sb.rpc("admin_assign_to_org", {
      target_email: email,
      org_slug: orgSlug || null,
    });
    if (error) throw new Error(error.message);
  }

  // ─── Invitations (Approach C) ────────────────────────────────────────
  // Token-based onboarding. See Supabase/invitations.sql for the schema +
  // RPCs (peek_invitation, create_invitation, redeem_invitation).

  function useInvitations(orgId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData]       = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        let q = sb.from("invitations")
          .select("id, organization_id, email, token, role_id, expires_at, used_at, used_by, max_uses, use_count, created_at, organizations(name, slug)")
          .order("created_at", { ascending: false });
        if (orgId) q = q.eq("organization_id", orgId);
        const r = await q;
        if (r.error) throw new Error(r.error.message);
        // Add a synthetic "status" field for the UI
        const now = Date.now();
        const rows = (r.data || []).map((inv) => ({
          ...inv,
          status: inv.used_at
            ? "used"
            : (new Date(inv.expires_at).getTime() < now ? "expired" : "pending"),
        }));
        setData(rows);
      } catch (e) { console.warn("[MELR] useInvitations:", e.message || e); setData([]); }
      finally { setLoading(false); }
    }, [orgId]);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-invitations-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes",
              { event: "*", schema: "public", table: "invitations",
                filter: orgId ? ("organization_id=eq." + orgId) : undefined },
              () => { refresh(); })
          .subscribe();
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh, orgId]);
    return { data, loading, refresh };
  }

  const invitationsCrud = {
    // Create an invitation row. The RPC enforces auth (admin of target org
    // or super-admin) and returns the opaque token. Returns the new row.
    // max_uses: integer cap on redemptions, or null for unlimited. Default
    // is 1 (single-use, preserves legacy behaviour). Email-restricted
    // invitations are always forced to single-use server-side.
    async create({ organization_id, email, role_id, expires_in_hours, max_uses }) {
      if (!organization_id) throw new Error("organization_id required");
      const sb = await waitForSupabase();
      const r = await sb.rpc("create_invitation", {
        p_organization_id: organization_id,
        p_email:           email && email.trim() ? email.trim().toLowerCase() : null,
        p_role_id:         role_id || null,
        p_expires_in_hours: expires_in_hours || 168, // 7 days default
        p_max_uses:        (max_uses === null || typeof max_uses === "undefined") ? 1 : max_uses,
      });
      if (r.error) throw new Error(r.error.message);
      // The RPC returns a TABLE (array of 1 row)
      const row = Array.isArray(r.data) ? r.data[0] : r.data;
      return row;
    },
    // Revoke = hard delete (token can no longer be redeemed). Could also
    // mark used_at, but a hard delete is cleaner since the row is no longer
    // useful for audit at that point.
    async revoke(invitationId) {
      const sb = await waitForSupabase();
      const r = await sb.from("invitations").delete().eq("id", invitationId);
      if (r.error) throw new Error(r.error.message);
    },
    // Build the share URL that the admin will copy/send. The base is the
    // current origin so dev / prod naturally point at the right host.
    inviteUrl(token) {
      if (!token) return null;
      try {
        return window.location.origin + "/MELR.html?invite=" + encodeURIComponent(token);
      } catch (_) { return null; }
    },
  };

  // Send an invitation email via the send-invitation-email Edge Function.
  // The function expects the bearer JWT of an authenticated caller; we pass
  // a fully-shaped payload (token, recipient email, org/role context, the
  // pre-built invite URL, and the recipient's lang for templating).
  //
  // Returns the function's parsed JSON response: { ok, error?, provider_id? }.
  // The function returns ok:false (HTTP 5xx) when secrets aren't configured;
  // the UI surfaces that as an amber notice + falls back to the existing
  // copy-link flow so the admin can still share the invitation manually.
  async function sendInvitationEmail({ token, email, org_name, role_name, lang, invite_url }) {
    if (!token) throw new Error("token required");
    if (!email) throw new Error("email required");
    const sb = await waitForSupabase();
    const url = invite_url || invitationsCrud.inviteUrl(token);
    const r = await sb.functions.invoke("send-invitation-email", {
      body: {
        token,
        email,
        org_name:   org_name  || "",
        role_name:  role_name || null,
        lang:       lang === "en" ? "en" : "fr",
        invite_url: url,
      },
    });
    // supabase-js v2 wraps the response: { data, error }. The function
    // itself returns JSON; surface it transparently.
    if (r.error) {
      // r.error.message includes "FunctionsHttpError" + the response body
      // when the function returned 4xx/5xx. We expose .message to the UI.
      throw new Error(r.error.message || "Edge function call failed");
    }
    const data = r.data || {};
    if (!data.ok) {
      throw new Error(data.error || "Email send failed");
    }
    return data;
  }

  // Public RPC: returns minimal info about a token without consuming it.
  // Used by the auth panel banner before signup.
  async function peekInvitation(token) {
    if (!token) return null;
    const sb = await waitForSupabase();
    const r = await sb.rpc("peek_invitation", { p_token: token });
    if (r.error) return null;
    return Array.isArray(r.data) ? r.data[0] : r.data;
  }

  // Redeem a token AFTER the user is authenticated. Returns the resolved
  // organization_id on success, throws on failure (already used, expired,
  // email mismatch).
  async function redeemInvitation(token) {
    if (!token) throw new Error("token required");
    const sb = await waitForSupabase();
    const r = await sb.rpc("redeem_invitation", { p_token: token });
    if (r.error) throw new Error(r.error.message);
    return r.data;
  }

  // Wrapper for the public list_public_organizations RPC, callable without
  // auth — used by the signup form.
  async function listPublicOrganizations() {
    const sb = await waitForSupabase();
    const r = await sb.rpc("list_public_organizations");
    if (r.error) throw new Error(r.error.message);
    return r.data || [];
  }

  // Set the caller's requested_organization_id (Approach B). Called RIGHT
  // AFTER auth.signUp succeeds in the signup form.
  async function setMyRequestedOrg(orgId) {
    const sb = await waitForSupabase();
    const r = await sb.rpc("set_my_requested_org", { target_org_id: orgId || null });
    if (r.error) throw new Error(r.error.message);
  }

  // Remove a user's organization assignment.
  async function revokeUserFromOrg(email) {
    const sb = await waitForSupabase();
    const { error } = await sb.rpc("admin_revoke_from_org", {
      target_email: email,
    });
    if (error) throw new Error(error.message);
  }

  // Realtime hook: members of the current user's organization.
  // Optional orgId argument lets a super-admin "act as" any other org —
  // when present, members are filtered by the passed org instead of the
  // caller's profile.organization_id. Non-super-admins won't be able to
  // read other orgs anyway (RLS denies it).
  function useOrgMembers(targetOrgId) {
    const { useState, useEffect, useCallback } = React;
    const [members, setMembers]   = useState([]);
    const [loading, setLoading]   = useState(true);
    const [realtime, setRealtime] = useState(false);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        let orgId = targetOrgId;
        if (!orgId) {
          const profile = await currentProfile();
          orgId = profile && profile.organization_id;
        }
        if (!orgId) {
          setMembers([]); setLoading(false); return;
        }
        const r = await sb.from("profiles")
          .select("id, email, full_name, organization_id, locale, avatar_url, active, created_at")
          .eq("organization_id", orgId)
          .order("full_name");
        if (r.error) throw new Error(r.error.message);
        setMembers(r.data || []);
      } catch (e) { console.error("[MELR] useOrgMembers:", e); }
      finally    { setLoading(false); }
    }, [targetOrgId]);
    useEffect(() => {
      let mounted = true;
      let channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-org-members-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes", { event: "*", schema: "public", table: "profiles" }, () => { refresh(); })
          .subscribe((status) => { if (status === "SUBSCRIBED" && mounted) setRealtime(true); });
      })();
      return () => {
        mounted = false;
        if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel));
      };
    }, [refresh]);
    return { members, loading, refresh, realtime };
  }

  // Realtime hook: stream of actions for a specific validation_items row.
  function useValidationActions(itemId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData]       = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      if (!itemId) { setLoading(false); return; }
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("validation_actions")
          .select("id, action, note, attachments, step_index, created_at, actor:profiles!validation_actions_actor_id_fkey(full_name, email)")
          .eq("item_id", itemId)
          .order("created_at", { ascending: false });
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.error("[MELR] useValidationActions:", e); }
      finally    { setLoading(false); }
    }, [itemId]);
    useEffect(() => {
      let mounted = true;
      let channel = null;
      (async () => {
        await refresh();
        if (!mounted || !itemId) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-vactions-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes",
              { event: "*", schema: "public", table: "validation_actions", filter: "item_id=eq." + itemId },
              () => { refresh(); })
          .subscribe();
      })();
      return () => {
        mounted = false;
        if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel));
      };
    }, [refresh, itemId]);
    return { data, loading, refresh };
  }

  // Update a validation_items row (typically used to advance state:
  // approved / sent_back / rejected). `patch` is a shallow column set.
  async function updateValidationItem(itemId, patch) {
    const sb = await waitForSupabase();
    console.log("[MELR] updateValidationItem id=", itemId, "patch=", patch);
    const { data, error } = await sb.from("validation_items")
      .update(patch).eq("id", itemId)
      .select("id, state, current_step");
    if (error) {
      console.error("[MELR] updateValidationItem error:", error);
      throw new Error(error.message);
    }
    console.log("[MELR] updateValidationItem result:", data);
    if (!data || data.length === 0) {
      throw new Error("Aucune ligne mise à jour — RLS bloque probablement l'UPDATE (votre profil n'est pas autorisé à modifier cet item).");
    }
    return data[0];
  }

  // Update an existing indicator (admin-only by RLS). Accepts a patch
  // object; recomputes the numeric baseline/target from text when the
  // indicator is PEFA-scored. Pass any subset of name_fr, name_en, unit,
  // level, frequency, baseline, target, baseline_text, target_text,
  // value_kind, definition_code.
  async function updateIndicator(indicatorUuid, patch) {
    if (!indicatorUuid) throw new Error("indicatorUuid required");
    const sb = await waitForSupabase();
    const clean = {};
    const fields = [
      "name_fr", "name_en", "unit", "level", "frequency", "sector_id",
      "value_kind", "definition_code",
    ];
    fields.forEach((k) => {
      if (patch[k] !== undefined) clean[k] = patch[k] === "" ? null : patch[k];
    });
    // Resolve numeric / text pairs
    const kind = patch.value_kind;
    const recomputeFromText = (kind === "score_pefa") ||
      (kind === undefined && (patch.baseline_text !== undefined || patch.target_text !== undefined));
    if (patch.baseline !== undefined)      clean.baseline = patch.baseline === "" || patch.baseline == null ? null : Number(patch.baseline);
    if (patch.target   !== undefined)      clean.target   = patch.target   === "" || patch.target   == null ? null : Number(patch.target);
    if (patch.baseline_text !== undefined) {
      clean.baseline_text = patch.baseline_text || null;
      if (recomputeFromText && patch.baseline_text) clean.baseline = scoreToNumeric(kind || "score_pefa", patch.baseline_text);
      if (recomputeFromText && !patch.baseline_text) clean.baseline = null;
    }
    if (patch.target_text !== undefined) {
      clean.target_text = patch.target_text || null;
      if (recomputeFromText && patch.target_text) clean.target = scoreToNumeric(kind || "score_pefa", patch.target_text);
      if (recomputeFromText && !patch.target_text) clean.target = null;
    }
    const r = await sb.from("indicators").update(clean).eq("id", indicatorUuid).select().single();
    if (r.error) throw new Error(r.error.message);
    return r.data;
  }

  // Delete an indicator (admin-only by RLS). FK ON DELETE CASCADE on
  // indicator_values removes its measurements at the same time.
  async function removeIndicator(indicatorUuid) {
    if (!indicatorUuid) throw new Error("indicatorUuid required");
    const sb = await waitForSupabase();
    const r = await sb.from("indicators").delete().eq("id", indicatorUuid);
    if (r.error) throw new Error(r.error.message);
  }

  // Bulk-insert several indicator_values rows for the same indicator + same
  // period (used by Saisie multi-sites). Each row carries site_id + either
  // value (numeric) or value_text (PEFA letter). Returns { inserted, errors[] }.
  async function bulkInsertIndicatorValues(indicatorUuid, period, rows) {
    if (!indicatorUuid) throw new Error("indicatorUuid required");
    if (!period || !period.period_start || !period.period_end) throw new Error("period_start/period_end required");
    if (!Array.isArray(rows) || rows.length === 0) return { inserted: 0, errors: [] };
    const sb = await waitForSupabase();
    const profile = await currentProfile();
    const kind = period.value_kind || "numeric";
    const errors = [];
    const payload = [];
    rows.forEach((r) => {
      const hasText = r.value_text != null && r.value_text !== "";
      const hasNum  = r.value != null && r.value !== "";
      if (!hasText && !hasNum) return; // skip empties
      let numeric;
      if (hasNum)       numeric = Number(r.value);
      else if (hasText) numeric = scoreToNumeric(kind, r.value_text);
      else              numeric = null;
      payload.push({
        indicator_id: indicatorUuid,
        site_id:      r.site_id || null,
        period_start: period.period_start,
        period_end:   period.period_end,
        value:        numeric,
        value_text:   hasText ? r.value_text : null,
        status:       r.status || "pending",
        state:        "submitted",
        comment:      r.comment || null,
        submitted_by: profile ? profile.id : null,
        submitted_at: new Date().toISOString(),
      });
    });
    if (payload.length === 0) return { inserted: 0, errors: [{ message: "Aucune valeur à enregistrer" }] };
    const ins = await sb.from("indicator_values").insert(payload);
    if (ins.error) errors.push({ message: ins.error.message });
    return { inserted: ins.error ? 0 : payload.length, errors };
  }

  // Insert a new indicator value (a "measurement" for a given period).
  // `indicatorUuid` is the UUID of the indicator, not the legacy code.
  // For alphabetic-scoring indicators (PEFA), pass value_text instead of
  // value — the numeric is computed from the scale.
  async function createIndicatorValue(indicatorUuid, payload) {
    const sb = await waitForSupabase();
    const profile = await currentProfile();
    const valueKind = payload.value_kind || "numeric";
    const valueText = payload.value_text || null;
    // Compute numeric from text when applicable
    let numericValue;
    if (payload.value != null && payload.value !== "") {
      numericValue = Number(payload.value);
    } else if (valueText != null) {
      numericValue = scoreToNumeric(valueKind, valueText);
    } else {
      numericValue = null;
    }
    const insertRow = {
      indicator_id: indicatorUuid,
      site_id:      payload.site_id || null,
      period_start: payload.period_start,
      period_end:   payload.period_end,
      value:        numericValue,
      value_text:   valueText,
      // Phase D · disaggregation : pass through numerator + denominator so
      // ratio indicators store the raw N/D alongside the computed value.
      // Both columns added by 20260523180000_add_indicator_disaggregation.sql
      // are optional — null-safe.
      numerator:    (payload.numerator   === "" || payload.numerator   == null) ? null : Number(payload.numerator),
      denominator:  (payload.denominator === "" || payload.denominator == null) ? null : Number(payload.denominator),
      status:       payload.status || "pending",
      state:        payload.state  || "submitted",
      comment:      payload.comment || null,
      submitted_by: profile ? profile.id : null,
      submitted_at: new Date().toISOString(),
    };
    const { data, error } = await sb.from("indicator_values").insert(insertRow).select().single();
    if (error) throw new Error(error.message);
    return data;
  }

  // ---- public API -----------------------------------------------------

  // Fetch all projects for the current tenant. Returns objects in legacy
  // shape. Uses Supabase joins to grab countries + lead profile + counts
  // for sites and indicators in one round-trip.
  async function fetchProjects() {
    const sb = await waitForSupabase();
    const { data, error } = await sb
      .from("projects")
      .select(`
        id, code, name_fr, name_en, organization_id, sector_id, status, risk, progress, budget, disbursed, currency, programme_id,
        created_at, updated_at,
        lead:profiles!projects_lead_user_id_fkey(full_name),
        programmes(id, code, name_fr, name_en),
        project_countries(country_iso2, countries(name_fr, name_en)),
        sites_count:sites(count),
        indicators_count:indicators(count)
      `)
      .order("code");
    if (error) {
      console.error("[MELR] fetchProjects error:", error.message, error);
      throw new Error(error.message);
    }
    console.log("[MELR] fetchProjects OK — " + (data || []).length + " rows");
    return (data || []).map(mapProjectRow);
  }

  // Fetch a single project by code (legacy id), with related sites,
  // indicators and team. Returns null if not found.
  async function fetchProjectDetail(code) {
    const sb = await waitForSupabase();
    const { data, error } = await sb
      .from("projects")
      .select(`
        id, code, name_fr, name_en, short_fr, short_en, sector_id, organization_id, status, risk,
        progress, budget, disbursed, committed, currency, start_date, end_date,
        lead:profiles!projects_lead_user_id_fkey(id, full_name, email, avatar_url),
        project_countries(country_iso2, countries(name_fr, name_en, region)),
        sites(id, code, name, kind, region, country_iso2, beneficiaries, staff_count, status),
        indicators(id, code, name_fr, name_en, level, unit, baseline, target),
        project_team(user_id, role_label, profiles(full_name, avatar_url))
      `)
      .eq("code", code)
      .maybeSingle();
    if (error) {
      console.error("[MELR] fetchProjectDetail error for code=" + code + ":", error.message, error);
      throw new Error(error.message);
    }
    if (!data) {
      console.warn("[MELR] fetchProjectDetail: no project with code=" + code);
      return null;
    }
    console.log("[MELR] fetchProjectDetail OK for code=" + code + ", uuid=" + data.id);
    return {
      ...mapProjectRow(data),
      shortFr:   data.short_fr,
      shortEn:   data.short_en,
      committed: (Number(data.committed) || 0) / 1_000_000,
      currency:  data.currency,
      startDate: data.start_date,
      endDate:   data.end_date,
      countries: data.project_countries || [],
      sitesList: data.sites || [],
      indicators: data.indicators || [],
      team:      data.project_team || [],
    };
  }

  // Fetch reference data (sectors). Sorted by position, returned in the
  // legacy shape { id, fr, en, color, icon }.
  async function fetchSectors() {
    const sb = await waitForSupabase();
    const { data, error } = await sb
      .from("sectors").select("id, name_fr, name_en, color, icon, position")
      .order("position");
    if (error) throw new Error(error.message);
    return (data || []).map((s) => ({
      id: s.id, fr: s.name_fr, en: s.name_en, color: s.color, icon: s.icon,
    }));
  }

  // ---- React hooks (use UMD React already on the page) ---------------

  function useProjects() {
    const { useState, useEffect, useCallback } = React;
    const [projects, setProjects] = useState([]);
    const [loading, setLoading] = useState(true);
    const [error, setError] = useState(null);
    const [realtime, setRealtime] = useState(false);
    const refresh = useCallback(() => {
      setLoading(true);
      return fetchProjects()
        .then((p) => { setProjects(p); setLoading(false); })
        .catch((e) => { setError(e.message); setLoading(false); });
    }, []);
    useEffect(() => {
      let mounted = true;
      let channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        const uniqueChannel = 'rt-projects-' + Math.random().toString(36).slice(2, 8);
        channel = sb.channel(uniqueChannel)
          .on('postgres_changes', { event: '*', schema: 'public', table: 'projects' },
              () => { refresh(); })
          .subscribe((status) => {
            if (status === 'SUBSCRIBED' && mounted) setRealtime(true);
          });
      })();
      return () => {
        mounted = false;
        if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel));
      };
    }, [refresh]);
    return { projects, loading, error, refresh, realtime };
  }

  function useProjectDetail(code) {
    const { useState, useEffect, useCallback } = React;
    const [project, setProject] = useState(null);
    const [loading, setLoading] = useState(true);
    const [error, setError] = useState(null);
    const refresh = useCallback(() => {
      if (!code) { setLoading(false); return Promise.resolve(); }
      setLoading(true);
      return fetchProjectDetail(code)
        .then((p) => { setProject(p); setLoading(false); })
        .catch((e) => { setError(e.message); setLoading(false); });
    }, [code]);
    useEffect(() => { refresh(); }, [refresh]);
    return { project, loading, error, refresh };
  }

  // ---- Generic single-resource hook factory ---------------------------
  // Builds a hook that fetches a tenant-scoped table.
  function makeListHook(tableSelector) {
    return function useList(arg) {
      const { useState, useEffect, useCallback } = React;
      const [data, setData] = useState([]);
      const [loading, setLoading] = useState(true);
      const [error, setError] = useState(null);
      // Bumped by refresh() to trigger a re-fetch without changing arg.
      const [tick, setTick] = useState(0);
      const refresh = useCallback(() => { setTick((n) => n + 1); }, []);
      useEffect(() => {
        let cancelled = false;
        (async () => {
          try {
            const sb = await waitForSupabase();
            const result = await tableSelector(sb, arg);
            if (cancelled) return;
            if (result.error) throw new Error(result.error.message);
            setData(result.data || []);
          } catch (e) {
            if (!cancelled) setError(e.message);
          } finally {
            if (!cancelled) setLoading(false);
          }
        })();
        return () => { cancelled = true; };
      }, [arg, tick]);
      return { data, loading, error, refresh };
    };
  }

  // Realtime-aware variant of makeListHook. The hook also returns
  // `refresh` (manual re-fetch) and `realtime` (true once the channel
  // is subscribed). When ANY postgres_changes event fires on `table`,
  // the data is automatically re-fetched.
  //
  // The table must be enabled in the supabase_realtime publication
  // (see Supabase/realtime-publication.sql).
  function makeRealtimeListHook(table, queryFn, channelName) {
    return function useRealtimeList(arg) {
      const { useState, useEffect, useCallback } = React;
      const [data, setData] = useState([]);
      const [loading, setLoading] = useState(true);
      const [error, setError] = useState(null);
      const [realtime, setRealtime] = useState(false);
      const refresh = useCallback(async () => {
        try {
          const sb = await waitForSupabase();
          const r = await queryFn(sb, arg);
          if (r.error) throw new Error(r.error.message);
          setData(r.data || []);
        } catch (e) { setError(e.message); }
        finally    { setLoading(false); }
      }, [arg]);
      useEffect(() => {
        let mounted = true;
        let channel = null;
        (async () => {
          await refresh();
          if (!mounted) return;
          const sb = await waitForSupabase();
          // Each consumer instance needs a unique channel name, otherwise
          // multiple subscriptions to the same channel name collide.
          const uniqueChannel = (channelName || ('rt-' + table)) + '-' + Math.random().toString(36).slice(2, 8);
          channel = sb.channel(uniqueChannel)
            .on('postgres_changes',
                { event: '*', schema: 'public', table },
                () => { refresh(); })
            .subscribe((status) => {
              if (status === 'SUBSCRIBED' && mounted) setRealtime(true);
            });
        })();
        return () => {
          mounted = false;
          if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel));
        };
      }, [refresh]);
      return { data, loading, error, refresh, realtime };
    };
  }

  // Mark one or all notifications as read. Realtime subscription on the
  // notifications channel auto-refreshes the UI after the UPDATE lands.
  const notificationsCrud = {
    async markRead(id) {
      const sb = await waitForSupabase();
      const r = await sb.from("notifications")
        .update({ read_at: new Date().toISOString() })
        .eq("id", id)
        .is("read_at", null);
      if (r.error) throw new Error(r.error.message);
    },
    async markAllRead() {
      const sb = await waitForSupabase();
      const r = await sb.from("notifications")
        .update({ read_at: new Date().toISOString() })
        .is("read_at", null);
      if (r.error) throw new Error(r.error.message);
    },
    async remove(id) {
      const sb = await waitForSupabase();
      const r = await sb.from("notifications").delete().eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
  };

  function useNotifications() {
    const { useState, useEffect, useCallback } = React;
    const [data, setData]       = useState([]);
    const [loading, setLoading] = useState(true);
    const [error, setError]     = useState(null);
    const [realtime, setRealtime] = useState(false); // true once channel subscribed

    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("notifications")
          .select("id, kind, title, body, severity, link_url, read_at, created_at")
          .order("created_at", { ascending: false }).limit(50);
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { setError(e.message); }
      finally    { setLoading(false); }
    }, []);

    useEffect(() => {
      let mounted = true;
      let channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        // Subscribe to all changes on notifications for the current user
        // (RLS already filters to user_id = auth.uid()).
        channel = sb.channel("melr-notifications")
          .on("postgres_changes",
              { event: "*", schema: "public", table: "notifications" },
              () => { refresh(); })
          .subscribe((status) => {
            if (status === "SUBSCRIBED" && mounted) setRealtime(true);
          });
      })();
      return () => {
        mounted = false;
        if (channel) {
          waitForSupabase().then((sb) => sb.removeChannel(channel));
        }
      };
    }, [refresh]);

    return { data, loading, error, refresh, realtime };
  }

  function useIndicators(projectId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData]       = useState([]);
    const [loading, setLoading] = useState(true);
    const [error, setError]     = useState(null);
    const [realtime, setRealtime] = useState(false);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        let q = sb.from("indicators")
          .select(`
            id, code, level, name_fr, name_en, unit, baseline, target, frequency, sector_id, project_id,
            value_kind, baseline_text, target_text, definition_code,
            projects(code, organization_id),
            indicator_values(value, value_text, period_start, period_end, status)
          `)
          .order("code");
        if (projectId) q = q.eq("project_id", projectId);
        const r = await q;
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { setError(e.message); }
      finally    { setLoading(false); }
    }, [projectId]);
    useEffect(() => {
      let mounted = true;
      let ch1 = null, ch2 = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        const tag = Math.random().toString(36).slice(2, 6);
        // Subscribe to both indicators (new indicators added) and
        // indicator_values (new measurements that animate sparklines).
        ch1 = sb.channel('rt-indicators-' + tag)
          .on('postgres_changes', { event: '*', schema: 'public', table: 'indicators' }, () => { refresh(); })
          .subscribe((status) => { if (status === 'SUBSCRIBED' && mounted) setRealtime(true); });
        ch2 = sb.channel('rt-indvals-' + tag)
          .on('postgres_changes', { event: '*', schema: 'public', table: 'indicator_values' }, () => { refresh(); })
          .subscribe();
      })();
      return () => {
        mounted = false;
        waitForSupabase().then((sb) => {
          if (ch1) sb.removeChannel(ch1);
          if (ch2) sb.removeChannel(ch2);
        });
      };
    }, [refresh]);
    return { data, loading, error, refresh, realtime };
  }

  // ─── Sectors (platform-wide catalogue) ───────────────────────────────
  // Sourced from the public.sectors table. Reads are open to every
  // authenticated user (sectors are universal labels); writes require an
  // admin role. The 12 legacy sectors are seeded as is_builtin = true and
  // are deletion-protected by RLS.
  //
  // On a successful load we MIRROR the rows into window.SECTORS so the
  // existing legacy code that reads SECTORS directly (sectorById, sector
  // colours in chips/Gantt, etc.) keeps working without a wholesale
  // refactor. New callers should use useSectors() to subscribe to live
  // updates.
  function useSectors() {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState((typeof window !== "undefined" && window.SECTORS) ? window.SECTORS.slice() : []);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("sectors")
          .select("id, name_fr, name_en, color, bg, icon, is_builtin, organization_id, archived_at")
          .is("archived_at", null)
          .order("is_builtin", { ascending: false })   // built-ins first
          .order("name_fr",    { ascending: true });
        if (r.error) throw new Error(r.error.message);
        // Map DB rows into the shape the front-end expects (id/fr/en/color/bg/icon).
        const rows = (r.data || []).map((s) => ({
          id:    s.id,
          fr:    s.name_fr,
          en:    s.name_en || s.name_fr,
          color: s.color || "oklch(0.55 0.10 250)",
          bg:    s.bg    || "oklch(0.96 0.03 250)",
          icon:  s.icon  || "building",
          is_builtin:      !!s.is_builtin,
          organization_id: s.organization_id,
        }));
        setData(rows);
        // Mirror to window.SECTORS so legacy non-hook code stays in sync.
        if (typeof window !== "undefined") window.SECTORS = rows;
      } catch (e) { console.warn("[MELR] sectors:", e.message || e); }
      finally { setLoading(false); }
    }, []);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-sectors-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes", { event: "*", schema: "public", table: "sectors" }, () => { refresh(); })
          .subscribe();
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh]);
    return { data, loading, refresh };
  }

  const sectorsCrud = {
    // Slugify a FR/EN name into a URL-safe id. Strips accents (NFD), lowercases,
    // and removes anything that isn't a-z 0-9. We then probe the DB for
    // uniqueness, appending a numeric suffix if needed.
    async generateSlug(name) {
      let base = (name || "")
        .normalize("NFD")
        .replace(/[̀-ͯ]/g, "")
        .toLowerCase()
        .replace(/[^a-z0-9]+/g, "")
        .slice(0, 32);
      if (!base) base = "secteur";
      const sb = await waitForSupabase();
      let candidate = base, i = 2;
      // Cap the loop at 100 attempts so a bug can't hang the UI.
      while (i < 200) {
        const r = await sb.from("sectors").select("id").eq("id", candidate).maybeSingle();
        if (r.error) throw new Error(r.error.message);
        if (!r.data) return candidate;
        candidate = base + i;
        i++;
      }
      throw new Error("Could not allocate a unique sector slug");
    },
    async create({ name_fr, name_en, color, bg, icon }) {
      if (!name_fr || !name_fr.trim()) throw new Error("name_fr required");
      const sb = await waitForSupabase();
      const profile = await currentProfile();
      const id = await sectorsCrud.generateSlug(name_fr.trim());
      const r = await sb.from("sectors").insert({
        id,
        name_fr: name_fr.trim(),
        name_en: name_en && name_en.trim() ? name_en.trim() : null,
        color:   color || "oklch(0.55 0.10 250)",
        bg:      bg    || "oklch(0.96 0.03 250)",
        icon:    icon  || "building",
        is_builtin: false,
        organization_id: profile && profile.organization_id,
        created_by:      profile && profile.id,
      }).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async update(id, patch) {
      const sb = await waitForSupabase();
      const clean = {};
      ["name_fr", "name_en", "color", "bg", "icon"].forEach((k) => {
        if (patch[k] !== undefined) clean[k] = patch[k] === "" ? null : patch[k];
      });
      const r = await sb.from("sectors").update(clean).eq("id", id).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
  };

  // ─── Indicator definitions (org-wide catalogue) ──────────────────────
  // Sourced from indicator_definitions table. RLS allows every org member
  // to read (needed so non-admin agents can pick a code when creating a
  // project indicator); writes are admin-only. PIRS files live in the
  // "indicator-pirs" private bucket under <org_id>/<definition_id>/<file>.
  function useIndicatorDefinitions() {
    const { useState, useEffect, useCallback } = React;
    const [data, setData]       = useState([]);
    const [loading, setLoading] = useState(true);
    const [realtime, setRealtime] = useState(false);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("indicator_definitions")
          .select("id, organization_id, code, name_fr, name_en, origin_institution, level, value_kind, is_shared, sector_id, components, pirs_path, pirs_filename, pirs_mime, pirs_size_bytes, created_at, updated_at")
          .order("code", { ascending: true });
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.warn("[MELR] indicator_definitions:", e.message || e); setData([]); }
      finally { setLoading(false); }
    }, []);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-indicator-defs-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes", { event: "*", schema: "public", table: "indicator_definitions" }, () => { refresh(); })
          .subscribe((s) => { if (s === "SUBSCRIBED" && mounted) setRealtime(true); });
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh]);
    return { data, loading, refresh, realtime };
  }

  const indicatorDefinitionsCrud = {
    async create({ code, name_fr, name_en, origin_institution, level, value_kind, organization_id, is_shared, sector_id, components }) {
      if (!code || !code.trim()) throw new Error("code required");
      if (!name_fr || !name_fr.trim()) throw new Error("name_fr required");
      const sb = await waitForSupabase();
      const profile = await currentProfile();
      // Super-admin acting-as-org passes an organization_id override;
      // everyone else falls back to their own org. RLS still enforces.
      // For SHARED definitions the organization_id still records the
      // creator-org (attribution) — RLS just bypasses the tenant filter
      // on reads.
      const orgId = organization_id || (profile && profile.organization_id);
      if (!orgId) throw new Error("organization_id missing on profile");
      const r = await sb.from("indicator_definitions").insert({
        organization_id:    orgId,
        code:               code.trim(),
        name_fr:            name_fr.trim(),
        name_en:            name_en && name_en.trim() ? name_en.trim() : null,
        origin_institution: origin_institution && origin_institution.trim() ? origin_institution.trim() : null,
        level:              level || null,
        value_kind:         value_kind || "numeric",
        is_shared:          !!is_shared,
        sector_id:          sector_id || null,
        // PEFA components: array of {code, name_fr, name_en}. Empty for
        // non-PEFA. The CHECK constraint enforces array type at DB level.
        components:         Array.isArray(components) ? components : [],
        created_by:         profile && profile.id,
      }).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async update(id, patch) {
      const sb = await waitForSupabase();
      const clean = {};
      // NOTE: is_shared is on the whitelist so a super-admin can flip a
      // definition from private to shared (and vice-versa) from the editor.
      // RLS gates this server-side: only super-admins can flip the flag.
      ["code", "name_fr", "name_en", "origin_institution", "level", "value_kind", "is_shared", "sector_id", "components"].forEach((k) => {
        if (patch[k] !== undefined) {
          // `components` must stay an array (DB CHECK constraint enforces);
          // treat null/undefined as empty array, never collapse to NULL.
          if (k === "components") {
            clean[k] = Array.isArray(patch[k]) ? patch[k] : [];
          } else {
            clean[k] = patch[k] === "" ? null : patch[k];
          }
        }
      });
      const r = await sb.from("indicator_definitions").update(clean).eq("id", id).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async remove(id) {
      const sb = await waitForSupabase();
      // Also attempt to delete the PIRS file if any. Best-effort; ignore errors.
      try {
        const fetchRow = await sb.from("indicator_definitions").select("pirs_path").eq("id", id).single();
        if (fetchRow.data && fetchRow.data.pirs_path) {
          await sb.storage.from("indicator-pirs").remove([fetchRow.data.pirs_path]).catch(() => {});
        }
      } catch (_) {}
      const r = await sb.from("indicator_definitions").delete().eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
    // Bulk delete · super-admin only operation (RLS gates server-side).
    // Returns { deleted, attempted, errors[] }. Performs the storage
    // cleanup (PIRS files) and then a single DELETE … WHERE id IN (…)
    // for efficiency. RLS may silently drop rows the caller isn't
    // allowed to delete — `deleted` reports the actual count.
    async bulkRemove(ids) {
      if (!ids || ids.length === 0) return { deleted: 0, attempted: 0, errors: [] };
      const sb = await waitForSupabase();
      const errors = [];
      // Step 1 — gather all PIRS storage paths to clean up first
      try {
        const rows = await sb.from("indicator_definitions").select("id, pirs_path").in("id", ids);
        if (rows.data) {
          const paths = rows.data.map((r) => r.pirs_path).filter(Boolean);
          if (paths.length > 0) {
            await sb.storage.from("indicator-pirs").remove(paths).catch((e) => {
              errors.push({ where: "storage", message: (e && e.message) || String(e) });
            });
          }
        }
      } catch (e) {
        errors.push({ where: "storage-lookup", message: e.message || String(e) });
      }
      // Step 2 — actual delete. RLS filters per row server-side.
      const r = await sb.from("indicator_definitions").delete().in("id", ids).select("id");
      if (r.error) throw new Error(r.error.message);
      return {
        deleted:   (r.data || []).length,
        attempted: ids.length,
        errors,
      };
    },
    // Bulk attribute change. RLS is applied per-row so the caller may end up
    // updating fewer rows than they selected (e.g. an org-admin trying to
    // flip a shared row will be silently rejected by the RLS policy that
    // restricts is_shared writes to super-admins). We return both counts so
    // the UI can report "N updated, M rejected".
    //
    // Whitelisted fields: sector_id, is_shared. Anything else is ignored.
    async bulkUpdate(ids, patch) {
      if (!ids || ids.length === 0) return { updated: 0, attempted: 0 };
      const sb = await waitForSupabase();
      const clean = {};
      ["sector_id", "is_shared"].forEach((k) => {
        if (patch[k] !== undefined) clean[k] = patch[k] === "" ? null : patch[k];
      });
      if (Object.keys(clean).length === 0) return { updated: 0, attempted: ids.length };
      // PostgREST returns only the rows that actually passed RLS on UPDATE
      // (the others are silently dropped). select("id") lets us count them.
      const r = await sb.from("indicator_definitions")
        .update(clean)
        .in("id", ids)
        .select("id");
      if (r.error) throw new Error(r.error.message);
      return { updated: (r.data || []).length, attempted: ids.length };
    },
  };

  // Upload a PIRS attachment for an existing definition. Replaces any
  // previous file. Path layout: <org_id>/<definition_id>/<sanitized_name>.
  async function uploadIndicatorPirs(definitionId, file) {
    if (!definitionId || !file) throw new Error("definitionId and file required");
    const sb = await waitForSupabase();
    const profile = await currentProfile();
    if (!profile || !profile.organization_id) throw new Error("organization_id missing on profile");
    const safeName = (file.name || "pirs").replace(/[^A-Za-z0-9._-]/g, "_");
    const path = profile.organization_id + "/" + definitionId + "/" + safeName;
    // If there's an existing file for this definition, remove it first
    const existing = await sb.from("indicator_definitions").select("pirs_path").eq("id", definitionId).single();
    if (existing.data && existing.data.pirs_path && existing.data.pirs_path !== path) {
      await sb.storage.from("indicator-pirs").remove([existing.data.pirs_path]).catch(() => {});
    }
    const up = await sb.storage.from("indicator-pirs").upload(path, file, { cacheControl: "3600", upsert: true });
    if (up.error) throw new Error(up.error.message);
    const meta = {
      pirs_path: path,
      pirs_filename: file.name || "pirs",
      pirs_mime: file.type || "",
      pirs_size_bytes: file.size || null,
    };
    const u = await sb.from("indicator_definitions").update(meta).eq("id", definitionId).select().single();
    if (u.error) throw new Error(u.error.message);
    return u.data;
  }
  async function removeIndicatorPirs(definitionId) {
    const sb = await waitForSupabase();
    const row = await sb.from("indicator_definitions").select("pirs_path").eq("id", definitionId).single();
    if (row.error) throw new Error(row.error.message);
    if (row.data && row.data.pirs_path) {
      await sb.storage.from("indicator-pirs").remove([row.data.pirs_path]).catch(() => {});
    }
    const u = await sb.from("indicator_definitions").update({
      pirs_path: null, pirs_filename: null, pirs_mime: null, pirs_size_bytes: null,
    }).eq("id", definitionId).select().single();
    if (u.error) throw new Error(u.error.message);
    return u.data;
  }
  async function getIndicatorPirsUrl(path, expiresIn) {
    if (!path) return null;
    const sb = await waitForSupabase();
    const r = await sb.storage.from("indicator-pirs").createSignedUrl(path, expiresIn || 3600);
    if (r.error) throw new Error(r.error.message);
    return r.data && r.data.signedUrl;
  }

  // ─── Multi-PIRS attachments (1-vers-N per definition) ───────────────
  // See Supabase/indicator-definition-attachments.sql. Each row carries
  // its own language and optional label. The legacy single-file API
  // above remains for backward compat; new code should use these.

  function useDefinitionAttachments(definitionId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData]       = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      if (!definitionId) { setData([]); setLoading(false); return; }
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("indicator_definition_attachments")
          .select("id, definition_id, language, label, path, filename, mime, size_bytes, uploaded_at")
          .eq("definition_id", definitionId)
          .order("uploaded_at", { ascending: true });
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.warn("[MELR] indicator_def_attachments:", e.message || e); setData([]); }
      finally { setLoading(false); }
    }, [definitionId]);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-def-attach-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes",
              { event: "*", schema: "public", table: "indicator_definition_attachments",
                filter: definitionId ? ("definition_id=eq." + definitionId) : undefined },
              () => { refresh(); })
          .subscribe();
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh, definitionId]);
    return { data, loading, refresh };
  }

  // Upload a new PIRS attachment for a definition. Returns the inserted row.
  async function addDefinitionAttachment(definitionId, file, opts) {
    if (!definitionId || !file) throw new Error("definitionId and file required");
    const sb = await waitForSupabase();
    const profile = await currentProfile();
    if (!profile || !profile.organization_id) throw new Error("organization_id missing on profile");
    const options = opts || {};
    const language = options.language ? String(options.language).slice(0, 2).toLowerCase() : null;
    const label    = options.label    ? String(options.label).slice(0, 200) : null;
    // Make filename unique within the definition folder so two FR uploads
    // with the same source name don't clobber each other.
    const safeName = (file.name || "pirs").replace(/[^A-Za-z0-9._-]/g, "_");
    const rand = Math.random().toString(36).slice(2, 8);
    const path = profile.organization_id + "/" + definitionId + "/" + rand + "__" + safeName;
    const up = await sb.storage.from("indicator-pirs").upload(path, file, { cacheControl: "3600", upsert: false });
    if (up.error) throw new Error(up.error.message);
    const ins = await sb.from("indicator_definition_attachments").insert({
      definition_id: definitionId,
      language, label,
      path,
      filename:   file.name || "pirs",
      mime:       file.type || "",
      size_bytes: file.size || null,
      uploaded_by: profile.id,
    }).select().single();
    if (ins.error) {
      // Best-effort cleanup of the storage object if the row insert failed
      await sb.storage.from("indicator-pirs").remove([path]).catch(() => {});
      throw new Error(ins.error.message);
    }
    return ins.data;
  }

  async function removeDefinitionAttachment(attachmentId) {
    if (!attachmentId) throw new Error("attachmentId required");
    const sb = await waitForSupabase();
    const row = await sb.from("indicator_definition_attachments")
      .select("id, path").eq("id", attachmentId).single();
    if (row.error) throw new Error(row.error.message);
    // Delete the row first (RLS will reject if non-admin)
    const del = await sb.from("indicator_definition_attachments").delete().eq("id", attachmentId);
    if (del.error) throw new Error(del.error.message);
    // Best-effort storage cleanup
    if (row.data && row.data.path) {
      await sb.storage.from("indicator-pirs").remove([row.data.path]).catch(() => {});
    }
  }

  async function updateDefinitionAttachment(attachmentId, patch) {
    const sb = await waitForSupabase();
    const clean = {};
    if (patch.language !== undefined) clean.language = patch.language || null;
    if (patch.label    !== undefined) clean.label    = patch.label    || null;
    const r = await sb.from("indicator_definition_attachments")
      .update(clean).eq("id", attachmentId).select().single();
    if (r.error) throw new Error(r.error.message);
    return r.data;
  }

  // Bulk import indicator definitions from an Excel file. Recognised
  // columns (case-insensitive, with FR/EN aliases):
  //   code | nom (FR) | name (FR) | name_fr
  //   nom (EN) | name (EN) | name_en
  //   origine (institution) | origin | origin_institution
  //   niveau | level
  // Returns { inserted, updated, skipped, errors[] }.
  async function importIndicatorDefinitionsFromExcel(file) {
    if (!window.XLSX) throw new Error("XLSX library not loaded");
    if (!file) throw new Error("file required");
    const sb = await waitForSupabase();
    const profile = await currentProfile();
    if (!profile || !profile.organization_id) throw new Error("organization_id missing on profile");

    const buf = await file.arrayBuffer();
    const wb  = window.XLSX.read(buf, { type: "array" });
    const ws  = wb.Sheets[wb.SheetNames[0]];
    const rows = window.XLSX.utils.sheet_to_json(ws, { defval: "" });

    const norm = (s) => (s || "").toString().toLowerCase().trim().replace(/[\s_]+/g, "");
    const pick = (row, keys) => {
      for (const k of Object.keys(row)) {
        if (keys.includes(norm(k))) return row[k];
      }
      return "";
    };

    // Pre-load the sector catalogue once so each row can resolve its
    // "Secteur" column without an extra round-trip per row. We accept the
    // sector by SLUG (id), by FR name, or by EN name — caller can use
    // whichever convention they like in the Excel.
    const sectorsList = await sb.from("sectors")
      .select("id, name_fr, name_en")
      .is("archived_at", null);
    const normKey = (s) => (s || "").toString().toLowerCase().trim()
      .normalize("NFD").replace(/[̀-ͯ]/g, "")
      .replace(/[^a-z0-9]+/g, "");
    const sectorIndex = new Map();
    (sectorsList.data || []).forEach((s) => {
      sectorIndex.set(normKey(s.id), s.id);
      sectorIndex.set(normKey(s.name_fr), s.id);
      if (s.name_en) sectorIndex.set(normKey(s.name_en), s.id);
    });
    const resolveSector = (raw) => {
      const k = normKey(raw);
      if (!k) return null;
      return sectorIndex.get(k) || null;
    };

    let inserted = 0, updated = 0, skipped = 0;
    const errors = [];
    for (let i = 0; i < rows.length; i++) {
      const row = rows[i];
      const code = String(pick(row, ["code"]) || "").trim();
      if (!code) { skipped++; continue; }
      const name_fr = String(pick(row, ["namefr", "nomfr", "nom(fr)", "name(fr)", "nom"]) || "").trim();
      if (!name_fr) { errors.push({ row: i + 2, code, message: "name_fr missing" }); skipped++; continue; }
      const name_en = String(pick(row, ["nameen", "nomen", "nom(en)", "name(en)"]) || "").trim() || null;
      const origin_institution = String(pick(row, ["origine", "origin", "origineinstitution", "originstitution", "institution"]) || "").trim() || null;
      let level = String(pick(row, ["niveau", "level"]) || "").trim().toLowerCase() || null;
      if (level && !["output", "outcome", "impact", "context", "mixed"].includes(level)) {
        // Tolerate French labels and the common "output/outcome" notation
        // for the new "Mixte" level. Order matters: check the more-specific
        // slash form before the bare "output" / "outcome" tests.
        if (/^mixte|^mixed|output\s*\/\s*outcome|outcome\s*\/\s*output|effet\s*\/\s*produit|produit\s*\/\s*effet/.test(level)) level = "mixed";
        else if (/^impact$/.test(level))        level = "impact";
        else if (/^context/.test(level))   level = "context";
        else if (/^output|^extrant|^produit/.test(level))  level = "output";
        else if (/^outcome|^effet|^résultat|^resultat/.test(level)) level = "outcome";
        else level = null;
      }

      // Sector column — optional. We accept slug ("sante"), FR name
      // ("Santé"), or EN name ("Health"). Unknown values produce a warning
      // line but don't block the row (sector stays NULL).
      const rawSector = String(pick(row, ["secteur", "sector", "sectorid", "secteurid"]) || "").trim();
      let sector_id = null;
      if (rawSector) {
        sector_id = resolveSector(rawSector);
        if (!sector_id) {
          errors.push({ row: i + 2, code, message: 'Unknown sector "' + rawSector + '" (row imported without sector — create the sector first or use the slug)' });
        }
      }

      const payload = {
        organization_id:    profile.organization_id,
        code, name_fr, name_en, origin_institution, level,
        sector_id,
        created_by:         profile.id,
      };
      // Upsert on (organization_id, code) — see UNIQUE constraint
      const r = await sb.from("indicator_definitions")
        .upsert(payload, { onConflict: "organization_id,code" })
        .select("id, created_at, updated_at").single();
      if (r.error) {
        errors.push({ row: i + 2, code, message: r.error.message });
        skipped++;
        continue;
      }
      // Treat as inserted iff created_at == updated_at (within 1s)
      const c = new Date(r.data.created_at).getTime();
      const u = new Date(r.data.updated_at).getTime();
      if (Math.abs(u - c) < 1500) inserted++; else updated++;
    }
    return { inserted, updated, skipped, errors };
  }

  function useSites(projectId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData]       = useState([]);
    const [loading, setLoading] = useState(true);
    const [error, setError]     = useState(null);
    const [realtime, setRealtime] = useState(false);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        let q = sb.from("sites")
          .select("id, code, name, kind, region, country_iso2, beneficiaries, staff_count, status, metadata, project_id, projects(code)")
          .order("code");
        if (projectId) q = q.eq("project_id", projectId);
        const r = await q;
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { setError(e.message); }
      finally    { setLoading(false); }
    }, [projectId]);
    useEffect(() => {
      let mounted = true;
      let channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel('rt-sites-' + Math.random().toString(36).slice(2, 6))
          .on('postgres_changes', { event: '*', schema: 'public', table: 'sites' }, () => { refresh(); })
          .subscribe((status) => { if (status === 'SUBSCRIBED' && mounted) setRealtime(true); });
      })();
      return () => {
        mounted = false;
        if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel));
      };
    }, [refresh]);
    return { data, loading, error, refresh, realtime };
  }

  const usePlanActions = makeListHook((sb, projectId) => {
    if (!projectId) return Promise.resolve({ data: [], error: null });
    return sb.from("plan_actions")
      .select("id, wbs, name_fr, name_en, phase_id, start_date, end_date, progress, status, milestone, position")
      .eq("project_id", projectId)
      .order("position");
  });

  async function fetchPlan(projectUuid) {
    const sb = await waitForSupabase();
    const [phasesR, actionsR] = await Promise.all([
      sb.from("plan_phases")
        .select("id, code, name_fr, name_en, color, position")
        .eq("project_id", projectUuid).order("position"),
      sb.from("plan_actions")
        .select("id, wbs, name_fr, name_en, phase_id, start_date, end_date, progress, status, milestone, position")
        .eq("project_id", projectUuid).order("position"),
    ]);
    if (phasesR.error)  throw new Error(phasesR.error.message);
    if (actionsR.error) throw new Error(actionsR.error.message);
    return { phases: phasesR.data || [], actions: actionsR.data || [] };
  }

  function usePlan(projectUuid) {
    const { useState, useEffect, useCallback } = React;
    const [plan, setPlan] = useState(null);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      if (!projectUuid) { setLoading(false); return; }
      try {
        const p = await fetchPlan(projectUuid);
        setPlan(p);
      } catch (e) {
        console.error("[MELR] usePlan refresh:", e);
      } finally {
        setLoading(false);
      }
    }, [projectUuid]);
    useEffect(() => {
      let mounted = true;
      let ch1 = null, ch2 = null;
      (async () => {
        await refresh();
        if (!mounted || !projectUuid) return;
        const sb = await waitForSupabase();
        const tag = Math.random().toString(36).slice(2, 6);
        ch1 = sb.channel("rt-plan-phases-" + tag)
          .on("postgres_changes",
              { event: "*", schema: "public", table: "plan_phases", filter: "project_id=eq." + projectUuid },
              () => { refresh(); })
          .subscribe();
        ch2 = sb.channel("rt-plan-actions-" + tag)
          .on("postgres_changes",
              { event: "*", schema: "public", table: "plan_actions", filter: "project_id=eq." + projectUuid },
              () => { refresh(); })
          .subscribe();
      })();
      return () => {
        mounted = false;
        waitForSupabase().then((sb) => {
          if (ch1) sb.removeChannel(ch1);
          if (ch2) sb.removeChannel(ch2);
        });
      };
    }, [refresh, projectUuid]);
    return { plan, loading, refresh };
  }

  // CRUD helpers for plan_phases and plan_actions
  async function createPlanPhase(projectUuid, payload) {
    const sb = await waitForSupabase();
    const insertRow = {
      project_id: projectUuid,
      code:       payload.code,
      name_fr:    payload.name_fr,
      name_en:    payload.name_en || payload.name_fr,
      color:      payload.color || "oklch(0.55 0.13 230)",
      position:   payload.position == null ? 0 : Number(payload.position),
    };
    const { data, error } = await sb.from("plan_phases").insert(insertRow).select().single();
    if (error) throw new Error(error.message);
    return data;
  }

  async function createPlanAction(projectUuid, payload) {
    const sb = await waitForSupabase();
    const insertRow = {
      project_id: projectUuid,
      phase_id:   payload.phase_id || null,
      wbs:        payload.wbs || null,
      name_fr:    payload.name_fr,
      name_en:    payload.name_en || payload.name_fr,
      start_date: payload.start_date || null,
      end_date:   payload.end_date   || null,
      progress:   payload.progress == null ? 0 : Number(payload.progress),
      status:     payload.status || "planned",
      milestone:  !!payload.milestone,
      position:   payload.position == null ? 0 : Number(payload.position),
    };
    const { data, error } = await sb.from("plan_actions").insert(insertRow).select().single();
    if (error) throw new Error(error.message);
    return data;
  }

  async function updatePlanAction(actionUuid, patch) {
    const sb = await waitForSupabase();
    const { data, error } = await sb.from("plan_actions")
      .update(patch).eq("id", actionUuid)
      .select().single();
    if (error) throw new Error(error.message);
    return data;
  }

  const useValidationQueue = makeRealtimeListHook(
    "validation_items",
    (sb) => sb.from("validation_items")
      .select("id, object_type, title, project_id, current_step, total_steps, state, priority, submitted_at, submitted_by, due_at, current_assignee_id, assignee:profiles!validation_items_current_assignee_id_fkey(id, full_name, email)")
      .order("submitted_at", { ascending: false })
      .limit(100),
    "rt-validation"
  );

  const useReports = makeListHook((sb) =>
    sb.from("reports")
      .select("id, title, project_id, period_start, period_end, state, file_url, created_at, author:profiles!reports_author_id_fkey(full_name), projects(code)")
      .order("created_at", { ascending: false })
      .limit(50)
  );

  const useAudits = makeListHook((sb, projectId) => {
    let q = sb.from("audits")
      .select("id, kind, cycle, project_id, state, score, started_at, closed_at, auditor:profiles!audits_auditor_id_fkey(full_name), projects(code)")
      .order("started_at", { ascending: false });
    if (projectId) q = q.eq("project_id", projectId);
    return q.limit(50);
  });

  // Create a new exante_dossiers row for the given project. version
  // defaults to "v1" but the modal lets the user override (so the second
  // dossier on the same project can be "v2" / "scénario optimiste" /
  // whatever). state defaults to 'draft' — RLS allows org members to
  // insert into their own org's projects.
  async function createExanteDossier(projectUuid, opts) {
    if (!projectUuid) throw new Error("projectUuid requis.");
    const sb = await waitForSupabase();
    const o = opts || {};
    const row = {
      project_id:    projectUuid,
      version:       o.version || "v1",
      state:         o.state   || "draft",
    };
    if (o.discount_rate != null) row.discount_rate = Number(o.discount_rate);
    const r = await sb.from("exante_dossiers").insert(row).select().single();
    if (r.error) throw new Error(r.error.message);
    return r.data;
  }

  const useExanteDossiers = makeListHook((sb, projectId) => {
    // projectId is optional: when missing, return all dossiers visible
    // to the user via RLS (typically all dossiers of their organization).
    // The project lead's email is fetched so the "Soumettre dossier"
    // button can pre-fill the email recipient.
    let q = sb.from("exante_dossiers")
      .select(`
        id, project_id, version, state, npv, irr, dscr, enpv, eirr, bc_ratio, discount_rate, decision, created_at,
        projects(
          code, name_fr, organization_id,
          lead:profiles!projects_lead_user_id_fkey(email, full_name)
        )
      `)
      .order("created_at", { ascending: false });
    if (projectId) q = q.eq("project_id", projectId);
    return q.limit(50);
  });

  const useForms = makeListHook((sb) =>
    sb.from("forms")
      .select("id, code, name_fr, name_en, version, schema, active, project_id, projects(code, name_fr, name_en)")
      .eq("active", true)
      .order("code")
      .limit(50)
  );

  // ─── Form submissions (Data Collection) ──────────────────────────────
  function useFormSubmissions() {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const [realtime, setRealtime] = useState(false);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("form_submissions")
          .select("id, form_id, site_id, agent_id, data, captured_at, uploaded_at, state, size_bytes, forms(code, name_fr, name_en, projects(code)), sites(code, name), agent:profiles!form_submissions_agent_id_fkey(full_name, email)")
          .order("uploaded_at", { ascending: false })
          .limit(100);
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.error("[MELR] form_submissions:", e); }
      finally { setLoading(false); }
    }, []);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-form-sub-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes", { event: "*", schema: "public", table: "form_submissions" }, () => { refresh(); })
          .subscribe((status) => { if (status === "SUBSCRIBED" && mounted) setRealtime(true); });
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh]);
    return { data, loading, refresh, realtime };
  }

  // Queue a submission locally (no network) or post to Supabase if online.
  // The Data Collection screen flushes the local queue when connectivity
  // returns.
  const LOCAL_SUB_KEY = "melr-local-submissions";
  function readLocalQueue() {
    try { return JSON.parse(localStorage.getItem(LOCAL_SUB_KEY) || "[]") || []; }
    catch (e) { return []; }
  }
  function writeLocalQueue(arr) {
    try { localStorage.setItem(LOCAL_SUB_KEY, JSON.stringify(arr)); } catch (e) {}
  }
  // ─── Form attachments (photo + signature fields) ─────────────────
  // Files live in the private `form-attachments` Storage bucket. Path
  // convention: `<form_id>/<random>__<filename>` so RLS can join back to
  // forms.project_id.organization_id. See Supabase/form-attachments-storage.sql.
  function _randomToken() {
    return Date.now().toString(36) + "-" + Math.random().toString(36).slice(2, 10);
  }
  function _sanitizeFilename(name) {
    return (name || "file").replace(/[^a-zA-Z0-9._-]/g, "_").slice(0, 80);
  }
  // Upload a Blob/File for a form. Returns { path, size, mime, name }.
  async function uploadFormAttachment(formId, blob, filename) {
    if (!formId) throw new Error("uploadFormAttachment: formId is required");
    if (!blob)   throw new Error("uploadFormAttachment: blob is required");
    const sb = await waitForSupabase();
    const safeName = _sanitizeFilename(filename || "attachment");
    const path = formId + "/" + _randomToken() + "__" + safeName;
    const r = await sb.storage.from("form-attachments").upload(path, blob, {
      contentType: blob.type || "application/octet-stream",
      upsert: false,
    });
    if (r.error) throw new Error(r.error.message);
    return { path, size: blob.size || null, mime: blob.type || null, name: safeName };
  }
  // Get a signed URL for a stored attachment (default 1h expiry).
  async function getFormAttachmentUrl(path, expiresIn) {
    if (!path) return null;
    const sb = await waitForSupabase();
    const r = await sb.storage.from("form-attachments").createSignedUrl(path, expiresIn || 3600);
    if (r.error) return null;
    return r.data && r.data.signedUrl;
  }
  // Helper used during offline → online flush: take a data URL (from a
  // local capture) and turn it back into a Blob so we can upload it.
  function _dataUrlToBlob(dataUrl) {
    const m = /^data:([^;,]+)(?:;base64)?,(.*)$/.exec(dataUrl || "");
    if (!m) return null;
    const mime = m[1] || "application/octet-stream";
    const isB64 = /;base64,/.test(dataUrl);
    const raw  = isB64 ? atob(m[2]) : decodeURIComponent(m[2]);
    const buf  = new Uint8Array(raw.length);
    for (let i = 0; i < raw.length; i++) buf[i] = raw.charCodeAt(i);
    return new Blob([buf], { type: mime });
  }
  // Scan a submission data payload for offline attachment markers
  // ({ _pendingUpload:true, dataUrl, name, mime }) and replace each with
  // an uploaded attachment descriptor. Mutates `data` in place.
  async function _uploadPendingAttachments(formId, data) {
    if (!data || typeof data !== "object") return;
    const keys = Object.keys(data);
    for (const k of keys) {
      const v = data[k];
      if (v && typeof v === "object" && v._pendingUpload === true && v.dataUrl) {
        const blob = _dataUrlToBlob(v.dataUrl);
        if (!blob) continue;
        const meta = await uploadFormAttachment(formId, blob, v.name || k);
        data[k] = { _attachment: true, ...meta };
      }
    }
  }

  // True iff an error looks like a transient network failure (vs a server
  // rejection like an RLS denial). Distinguishes "I should retry later" from
  // "this submission will never succeed". Safari iOS sometimes throws
  // "Load failed" on cold cellular connections; navigator.onLine is also
  // unreliable, so we trust the actual error over the browser's flag.
  function _isNetworkError(e) {
    if (!e) return false;
    const msg = (e.message || String(e) || "").toLowerCase();
    if (e.name === "TypeError" && /fetch|network|load/.test(msg)) return true;
    if (e.name === "AbortError") return true;
    if (/networkerror|network request failed|failed to fetch|load failed/.test(msg)) return true;
    return false;
  }

  async function submitForm(payload) {
    const sb = await waitForSupabase();
    const profile = await currentProfile();
    const dataClone = payload.data ? JSON.parse(JSON.stringify(payload.data)) : {};
    const row = {
      form_id:     payload.form_id,
      site_id:     payload.site_id || null,
      agent_id:    profile ? profile.id : null,
      device_id:   payload.device_id || null,
      data:        dataClone,
      captured_at: payload.captured_at || new Date().toISOString(),
      state:       "queued",
      size_bytes:  0, // recomputed below
    };
    const queueLocally = () => {
      row.size_bytes = JSON.stringify(dataClone).length;
      const local = readLocalQueue();
      const localId = "local-" + Math.random().toString(36).slice(2, 10);
      local.push({ id: localId, _localQueuedAt: Date.now(), ...row });
      writeLocalQueue(local);
      return { id: localId, local: true };
    };

    // Always TRY the network first. navigator.onLine can be wrong on iOS;
    // trust the actual fetch outcome. Fall back to local queue only on
    // true network failure — never on RLS denial or validation errors.
    try {
      if (payload.form_id) {
        await _uploadPendingAttachments(payload.form_id, dataClone);
      }
      row.size_bytes = JSON.stringify(dataClone).length;
      const r = await sb.from("form_submissions").insert(row).select().single();
      if (r.error) {
        // PostgREST errors (RLS denial, validation, etc.) come back with a
        // populated r.error rather than thrown — treat as non-network.
        const err = new Error(r.error.message || "Insert failed");
        err.code = r.error.code;
        err.details = r.error.details;
        throw err;
      }
      return r.data;
    } catch (e) {
      if (_isNetworkError(e) || !navigator.onLine) {
        return queueLocally();
      }
      // Surface RLS denials and validation errors to the caller — they
      // wouldn't be fixed by a later retry. The UI shows a red toast.
      throw new Error(e.message || String(e));
    }
  }

  // Push any queued local submissions to Supabase. Returns { synced, remaining }
  // plus an optional `errors` array describing rejected submissions so the UI
  // can show a concrete reason (e.g. "RLS denied: missing project_agents row")
  // instead of failing silently.
  async function flushLocalQueue() {
    const sb = await waitForSupabase();
    const local = readLocalQueue();
    const remaining = [];
    const errors = [];
    let synced = 0;
    for (const item of local) {
      const { id, _localQueuedAt, ...row } = item;
      try {
        if (row.form_id && row.data) await _uploadPendingAttachments(row.form_id, row.data);
        if (row.data) row.size_bytes = JSON.stringify(row.data).length;
      } catch (e) {
        if (_isNetworkError(e) || !navigator.onLine) {
          // Transient — keep in queue, try again next time
          remaining.push(item);
        } else {
          // Permanent — surface the reason but keep the row so the admin
          // can decide whether to discard it after fixing the underlying
          // issue (e.g. assign the agent to project_agents).
          errors.push({ id, kind: "attachment", message: e.message || String(e) });
          remaining.push(item);
        }
        continue;
      }
      const r = await sb.from("form_submissions").insert(row);
      if (r.error) {
        const msg = r.error.message || "insert failed";
        if (_isNetworkError(r.error) || !navigator.onLine) {
          remaining.push(item);
        } else {
          errors.push({ id, kind: "insert", message: msg, code: r.error.code });
          remaining.push(item);
        }
      } else {
        synced++;
      }
    }
    writeLocalQueue(remaining);
    return { synced, remaining: remaining.length, errors };
  }
  function useLocalQueueSize() {
    const { useState, useEffect } = React;
    const [size, setSize] = useState(readLocalQueue().length);
    useEffect(() => {
      const tick = () => setSize(readLocalQueue().length);
      window.addEventListener("storage", tick);
      const interval = setInterval(tick, 2000);
      return () => { window.removeEventListener("storage", tick); clearInterval(interval); };
    }, []);
    return size;
  }
  // Returns the FULL local queue array (not just the count). Same polling
  // strategy as useLocalQueueSize. Used by the mobile "Mes saisies" view to
  // show pending offline submissions alongside server ones.
  function useLocalQueue() {
    const { useState, useEffect } = React;
    const [queue, setQueue] = useState(() => readLocalQueue());
    useEffect(() => {
      const tick = () => setQueue(readLocalQueue());
      window.addEventListener("storage", tick);
      const interval = setInterval(tick, 2000);
      return () => { window.removeEventListener("storage", tick); clearInterval(interval); };
    }, []);
    return queue;
  }

  const useLearningQuestions = makeListHook((sb, projectId) => {
    let q = sb.from("learning_questions")
      .select("id, code, question, project_id, state, tags, created_at, owner:profiles!learning_questions_owner_id_fkey(full_name), projects(code)")
      .order("created_at", { ascending: false });
    if (projectId) q = q.eq("project_id", projectId);
    return q.limit(50);
  });

  // ╔══════════════════════════════════════════════════════════════════════╗
  // ║ INDICATOR DISAGGREGATION · Phase B · data layer                      ║
  // ║                                                                       ║
  // ║ Supports the schema introduced by 20260523180000…, 20260523190000…   ║
  // ║ and 20260523200000… migrations:                                       ║
  // ║   - indicator_disaggregation_axes        (sex, age, population, …)   ║
  // ║   - indicator_disaggregation_values      (M/F, <15, MSM, …, per-org) ║
  // ║   - indicator_definition_disaggregations (which axes per indicator)  ║
  // ║   - indicator_value_disaggregations      (the actual breakdown rows) ║
  // ║   - disaggregation_entities              (orgs/communities/districts)║
  // ║                                                                       ║
  // ║ Hooks:                                                                ║
  // ║   useDisaggregationAxes()                                             ║
  // ║   useAxisValues(axisId)                                               ║
  // ║   useDisaggregationEntities()                                         ║
  // ║   useDefinitionDisaggregations(definitionId)                          ║
  // ║   useDisaggregatedValues(indicatorValueId)                            ║
  // ║                                                                       ║
  // ║ CRUDs:                                                                ║
  // ║   disaggregationAxesCrud      · disaggregationAxisValuesCrud          ║
  // ║   disaggregationEntitiesCrud  · definitionDisaggregationsCrud         ║
  // ║   disaggregatedValuesCrud     · with .upsertGrid() for batch saisie   ║
  // ║                                                                       ║
  // ║ Helpers:                                                              ║
  // ║   aggregateDisaggregation(rows, axisCode)                             ║
  // ║   computeRatio({ numerator, denominator })                            ║
  // ╚══════════════════════════════════════════════════════════════════════╝

  // ── HOOK · all axes visible to the current user ──────────────────────────
  // Returns shared (global) axes + the active org's own axes. Subscribes to
  // realtime so the list refreshes if a super-admin adds a new axis or an
  // org admin creates a local one.
  function useDisaggregationAxes() {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("indicator_disaggregation_axes")
          .select("id, code, name_fr, name_en, kind, description, organization_id, is_shared, created_at")
          .order("is_shared", { ascending: false })   // shared first
          .order("code",      { ascending: true });
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.warn("[MELR] disaggregation_axes:", e.message || e); }
      finally { setLoading(false); }
    }, []);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-disagg-axes-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes", { event: "*", schema: "public", table: "indicator_disaggregation_axes" }, () => { refresh(); })
          .subscribe();
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh]);
    return { data, loading, refresh };
  }

  // ── HOOK · values list for ONE axis (by id) ──────────────────────────────
  // Returns globals + the user's own org's customisations for that axis.
  // RLS does the filtering; we just SELECT * and order by sort_order.
  function useAxisValues(axisId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      if (!axisId) { setData([]); setLoading(false); return; }
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("indicator_disaggregation_values")
          .select("id, axis_id, organization_id, code, label_fr, label_en, description_fr, description_en, sort_order, active, created_at")
          .eq("axis_id", axisId)
          .eq("active", true)
          .order("sort_order", { ascending: true })
          .order("code",       { ascending: true });
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.warn("[MELR] axis_values:", e.message || e); }
      finally { setLoading(false); }
    }, [axisId]);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-axis-values-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes", { event: "*", schema: "public", table: "indicator_disaggregation_values" }, (payload) => {
            // Only refresh if the change concerns OUR axis
            const r = payload.new || payload.old;
            if (r && r.axis_id === axisId) refresh();
          })
          .subscribe();
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [axisId, refresh]);
    return { data, loading, refresh };
  }

  // ── HOOK · disaggregation entities (org-scoped, for axis 'entity') ──────
  function useDisaggregationEntities() {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("disaggregation_entities")
          .select("id, organization_id, code, name_fr, name_en, kind, parent_id, active, created_at")
          .eq("active", true)
          .order("kind",    { ascending: true })
          .order("name_fr", { ascending: true });
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.warn("[MELR] entities:", e.message || e); }
      finally { setLoading(false); }
    }, []);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-entities-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes", { event: "*", schema: "public", table: "disaggregation_entities" }, () => { refresh(); })
          .subscribe();
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh]);
    return { data, loading, refresh };
  }

  // ── HOOK · resolve a definition_id from a definition_code ───────────────
  // The indicators table only stores `definition_code` (text) — but the
  // disaggregation tables key on definition_id (UUID). This hook walks the
  // catalogue (RLS-filtered: shared + your org) and returns the first
  // matching definition. Used by the saisie modal to find which axes apply.
  function useDefinitionByCode(code) {
    const { useState, useEffect } = React;
    const [data, setData] = useState(null);
    const [loading, setLoading] = useState(true);
    useEffect(() => {
      let alive = true;
      (async () => {
        if (!code) { setData(null); setLoading(false); return; }
        try {
          const sb = await waitForSupabase();
          // RLS does the visibility filtering. Order: shared first, then
          // own-org. Limit 1 — the first match wins. (Avoid surprises if a
          // super-admin has the same code in multiple orgs.)
          const r = await sb.from("indicator_definitions")
            .select("id, code, name_fr, name_en, value_kind, is_shared, organization_id, components")
            .eq("code", code)
            .order("is_shared", { ascending: false })
            .limit(1);
          if (alive) setData((r.data && r.data[0]) || null);
        } catch (e) { console.warn("[MELR] useDefinitionByCode:", e.message || e); }
        finally { if (alive) setLoading(false); }
      })();
      return () => { alive = false; };
    }, [code]);
    return { data, loading };
  }

  // ── HOOK · which axes are assigned to a given indicator definition ──────
  function useDefinitionDisaggregations(definitionId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      if (!definitionId) { setData([]); setLoading(false); return; }
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("indicator_definition_disaggregations")
          .select("definition_id, axis_id, is_required, display_order, axis:indicator_disaggregation_axes(id, code, name_fr, name_en, kind, is_shared)")
          .eq("definition_id", definitionId)
          .order("display_order", { ascending: true });
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.warn("[MELR] def_disaggregations:", e.message || e); }
      finally { setLoading(false); }
    }, [definitionId]);
    useEffect(() => { refresh(); }, [refresh]);
    return { data, loading, refresh };
  }

  // ── HOOK · disaggregation rows attached to one indicator_value ──────────
  // Each row = one cell of the saisie grid (one combination of axis values).
  function useDisaggregatedValues(indicatorValueId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      if (!indicatorValueId) { setData([]); setLoading(false); return; }
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("indicator_value_disaggregations")
          .select("id, value_id, axis_values, numerator, denominator, value, notes, created_by, created_at")
          .eq("value_id", indicatorValueId);
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.warn("[MELR] disagg_values:", e.message || e); }
      finally { setLoading(false); }
    }, [indicatorValueId]);
    useEffect(() => { refresh(); }, [refresh]);
    return { data, loading, refresh };
  }

  // ── CRUD · axes (super-admin shared, org-admin org-scoped) ──────────────
  const disaggregationAxesCrud = {
    async create({ code, name_fr, name_en, kind, description, organization_id, is_shared }) {
      if (!code || !code.trim()) throw new Error("code required");
      if (!name_fr || !name_fr.trim()) throw new Error("name_fr required");
      if (!kind || !["value-list", "entity-ref"].includes(kind)) throw new Error("kind must be 'value-list' or 'entity-ref'");
      const sb = await waitForSupabase();
      const profile = await currentProfile();
      const r = await sb.from("indicator_disaggregation_axes").insert({
        code:            code.trim(),
        name_fr:         name_fr.trim(),
        name_en:         name_en && name_en.trim() ? name_en.trim() : null,
        kind,
        description:     description && description.trim() ? description.trim() : null,
        organization_id: organization_id || null,
        is_shared:       !!is_shared,
        created_by:      profile && profile.id,
      }).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async update(id, patch) {
      const sb = await waitForSupabase();
      const clean = {};
      ["code", "name_fr", "name_en", "kind", "description", "is_shared"].forEach((k) => {
        if (patch[k] !== undefined) clean[k] = patch[k] === "" ? null : patch[k];
      });
      const r = await sb.from("indicator_disaggregation_axes").update(clean).eq("id", id).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async remove(id) {
      const sb = await waitForSupabase();
      const r = await sb.from("indicator_disaggregation_axes").delete().eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
  };

  // ── CRUD · axis values (globals or per-org additions) ───────────────────
  const disaggregationAxisValuesCrud = {
    async create({ axis_id, organization_id, code, label_fr, label_en, description_fr, description_en, sort_order }) {
      if (!axis_id) throw new Error("axis_id required");
      if (!code || !code.trim()) throw new Error("code required");
      if (!label_fr || !label_fr.trim()) throw new Error("label_fr required");
      const sb = await waitForSupabase();
      const r = await sb.from("indicator_disaggregation_values").insert({
        axis_id,
        organization_id: organization_id || null,
        code:            code.trim(),
        label_fr:        label_fr.trim(),
        label_en:        label_en && label_en.trim() ? label_en.trim() : null,
        description_fr:  description_fr && description_fr.trim() ? description_fr.trim() : null,
        description_en:  description_en && description_en.trim() ? description_en.trim() : null,
        sort_order:      Number.isFinite(sort_order) ? sort_order : 0,
        active:          true,
      }).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async update(id, patch) {
      const sb = await waitForSupabase();
      const clean = {};
      ["code", "label_fr", "label_en", "description_fr", "description_en", "sort_order", "active"].forEach((k) => {
        if (patch[k] !== undefined) clean[k] = patch[k] === "" ? null : patch[k];
      });
      const r = await sb.from("indicator_disaggregation_values").update(clean).eq("id", id).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async remove(id) {
      const sb = await waitForSupabase();
      const r = await sb.from("indicator_disaggregation_values").delete().eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
  };

  // ── CRUD · entities ──
  const disaggregationEntitiesCrud = {
    async create({ organization_id, code, name_fr, name_en, kind, parent_id }) {
      const sb = await waitForSupabase();
      const profile = await currentProfile();
      const orgId = organization_id || (profile && profile.organization_id);
      if (!orgId) throw new Error("organization_id required");
      if (!code || !code.trim()) throw new Error("code required");
      if (!name_fr || !name_fr.trim()) throw new Error("name_fr required");
      const r = await sb.from("disaggregation_entities").insert({
        organization_id: orgId,
        code:            code.trim(),
        name_fr:         name_fr.trim(),
        name_en:         name_en && name_en.trim() ? name_en.trim() : null,
        kind:            kind || "other",
        parent_id:       parent_id || null,
        active:          true,
        created_by:      profile && profile.id,
      }).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async update(id, patch) {
      const sb = await waitForSupabase();
      const clean = {};
      ["code", "name_fr", "name_en", "kind", "parent_id", "active"].forEach((k) => {
        if (patch[k] !== undefined) clean[k] = patch[k] === "" ? null : patch[k];
      });
      const r = await sb.from("disaggregation_entities").update(clean).eq("id", id).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async remove(id) {
      const sb = await waitForSupabase();
      const r = await sb.from("disaggregation_entities").delete().eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
  };

  // ── CRUD · per-definition axis assignment ──
  // Lets the editor say "this indicator must be disaggregated by sex+age".
  const definitionDisaggregationsCrud = {
    // Replace the whole set of axes assigned to a definition.
    // Pass [{ axis_id, is_required, display_order }] — we DELETE the old rows
    // and INSERT the new ones in one transaction-shaped operation (best
    // effort: not in an explicit txn since supabase-js doesn't expose them
    // directly, but acceptable for an admin write of a small list).
    async setAxes(definition_id, axes /* [{axis_id, is_required, display_order}] */) {
      if (!definition_id) throw new Error("definition_id required");
      const sb = await waitForSupabase();
      // 1. Wipe existing
      const del = await sb.from("indicator_definition_disaggregations")
        .delete().eq("definition_id", definition_id);
      if (del.error) throw new Error(del.error.message);
      // 2. Insert new (skip if empty)
      if (!axes || axes.length === 0) return [];
      const rows = axes.map((a, i) => ({
        definition_id,
        axis_id:       a.axis_id,
        is_required:   a.is_required === false ? false : true,
        display_order: Number.isFinite(a.display_order) ? a.display_order : i,
      }));
      const ins = await sb.from("indicator_definition_disaggregations").insert(rows).select();
      if (ins.error) throw new Error(ins.error.message);
      return ins.data || [];
    },
    // Single-axis toggle helpers, kept for the UI's "check/uncheck a box"
    // flow. They go through setAxes underneath to keep the data consistent.
    async addAxis(definition_id, axis_id, opts) {
      const sb = await waitForSupabase();
      const r = await sb.from("indicator_definition_disaggregations").insert({
        definition_id,
        axis_id,
        is_required:   opts && opts.is_required === false ? false : true,
        display_order: opts && Number.isFinite(opts.display_order) ? opts.display_order : 0,
      }).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async removeAxis(definition_id, axis_id) {
      const sb = await waitForSupabase();
      const r = await sb.from("indicator_definition_disaggregations")
        .delete()
        .eq("definition_id", definition_id)
        .eq("axis_id", axis_id);
      if (r.error) throw new Error(r.error.message);
    },
  };

  // ── CRUD · disaggregated value rows (the actual saisie grid storage) ───
  // The flagship operation here is upsertGrid: takes a value_id and an array
  // of {axis_values, numerator, denominator, value, notes}, wipes the old
  // grid for that value, inserts the new one. Used when the saisie modal
  // is saved. Empty-row entries (numerator AND denominator AND value all
  // null/0) are skipped — no point persisting "no observation" cells.
  const disaggregatedValuesCrud = {
    async upsertGrid(indicatorValueId, rows /* [{axis_values, numerator, denominator, value, notes}] */) {
      if (!indicatorValueId) throw new Error("indicator_value_id required");
      const sb = await waitForSupabase();
      const profile = await currentProfile();

      // 1. Wipe existing rows for this value
      const del = await sb.from("indicator_value_disaggregations")
        .delete().eq("value_id", indicatorValueId);
      if (del.error) throw new Error(del.error.message);

      // 2. Filter out empty rows (also treat blank value_text as empty)
      const filtered = (rows || []).filter((r) => {
        const n = r.numerator, d = r.denominator, v = r.value, vt = r.value_text;
        const isEmpty =
          (n == null || n === "") &&
          (d == null || d === "") &&
          (v == null || v === "") &&
          (vt == null || (typeof vt === "string" && vt.trim() === ""));
        return !isEmpty;
      });
      if (filtered.length === 0) return [];

      // 3. Insert new rows.  value_text is forwarded for PEFA-style grids
      // where each cell carries an alphabetic grade (A/B+/B/C+/C/D+/D/NR).
      const payload = filtered.map((r) => ({
        value_id:    indicatorValueId,
        axis_values: r.axis_values || {},
        numerator:   r.numerator   === "" || r.numerator   == null ? null : Number(r.numerator),
        denominator: r.denominator === "" || r.denominator == null ? null : Number(r.denominator),
        value:       r.value       === "" || r.value       == null ? null : Number(r.value),
        value_text:  r.value_text && String(r.value_text).trim() ? String(r.value_text).trim() : null,
        notes:       r.notes      && String(r.notes).trim()      ? String(r.notes).trim()      : null,
        created_by:  profile && profile.id,
      }));
      const ins = await sb.from("indicator_value_disaggregations").insert(payload).select();
      if (ins.error) throw new Error(ins.error.message);
      return ins.data || [];
    },
    // Single-row upsert (rare — usually the grid is the unit of work).
    async addRow(indicatorValueId, { axis_values, numerator, denominator, value, notes }) {
      const sb = await waitForSupabase();
      const profile = await currentProfile();
      const r = await sb.from("indicator_value_disaggregations").insert({
        value_id:    indicatorValueId,
        axis_values: axis_values || {},
        numerator:   numerator   === "" || numerator   == null ? null : Number(numerator),
        denominator: denominator === "" || denominator == null ? null : Number(denominator),
        value:       value       === "" || value       == null ? null : Number(value),
        notes:       notes && String(notes).trim() ? String(notes).trim() : null,
        created_by:  profile && profile.id,
      }).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async remove(id) {
      const sb = await waitForSupabase();
      const r = await sb.from("indicator_value_disaggregations").delete().eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
  };

  // ── Helper · create a form pre-populated for one indicator (Pass 3) ──
  // Builds the schema with a single 'indicator' field pointing to the
  // given indicator. The form code is auto-generated from the indicator
  // code with a unique suffix to avoid collisions if the user clicks the
  // button twice.
  async function createFormForIndicator(indicator, lang) {
    if (!indicator || !indicator.uuid) throw new Error("indicator required");
    const fr = lang !== "en";
    // Suffix the form code with the current YYYYMMDD-HHmm so re-running
    // doesn't collide. Length capped at ~32 chars for sanity.
    const stamp = new Date().toISOString().slice(0, 16).replace(/[-T:]/g, "");
    const code = ("F-" + (indicator.code || indicator.id || "IND") + "-" + stamp).slice(0, 32);
    const name_fr = "Saisie " + (indicator.code || indicator.id) + " - " + (indicator.name_fr || indicator.name || "");
    const name_en = "Entry " + (indicator.code || indicator.id) + " - " + (indicator.name_en || indicator.name_fr || indicator.name || "");
    const schema = {
      fields: [
        { k: "period_start", l: fr ? "Debut de periode" : "Period start", type: "date" },
        { k: "period_end",   l: fr ? "Fin de periode"   : "Period end",   type: "date" },
        {
          k: "ind_" + (indicator.code || "main").toLowerCase().replace(/[^a-z0-9]+/g, "_"),
          l: indicator.name_fr || indicator.name || (indicator.code || ""),
          type: "indicator",
          indicator_id: indicator.uuid,
        },
        { k: "comment", l: fr ? "Commentaire (optionnel)" : "Comment (optional)", type: "text" },
      ],
    };
    return formsCrud.create({
      project_id: indicator.project_id || null,
      code,
      name_fr: name_fr.slice(0, 200),
      name_en: name_en.slice(0, 200),
      version: "v1",
      schema,
      active: true,
    });
  }

  // ── Helper · compute the ratio for one row (raw N/D → percentage) ─────
  // Returns null if the denominator is missing or zero (avoids #DIV/0!).
  function computeRatio({ numerator, denominator }) {
    if (numerator == null || denominator == null) return null;
    const n = Number(numerator), d = Number(denominator);
    if (!Number.isFinite(n) || !Number.isFinite(d) || d === 0) return null;
    return n / d;   // caller multiplies by 100 if they want a %
  }

  // ── Helper · sum a disaggregation across ONE axis ─────────────────────
  // Example : aggregate by 'sex' from the saisie grid → returns a Map
  // keyed by the sex value with totals { numerator, denominator, value, ratio }.
  //
  //   const rows = await sb.from('indicator_value_disaggregations')...
  //   const bySex = aggregateDisaggregation(rows, 'sex');
  //   bySex.get('M') === { numerator: 42, denominator: 100, value: null, ratio: 0.42 }
  //
  // Rows missing the axis (e.g. only disaggregated by age) are bucketed
  // under the special key '__missing'.
  function aggregateDisaggregation(rows, axisCode) {
    const out = new Map();
    (rows || []).forEach((r) => {
      const key = (r.axis_values && r.axis_values[axisCode]) || "__missing";
      const cur = out.get(key) || { numerator: 0, denominator: 0, value: 0, count: 0 };
      if (Number.isFinite(Number(r.numerator)))   cur.numerator   += Number(r.numerator);
      if (Number.isFinite(Number(r.denominator))) cur.denominator += Number(r.denominator);
      if (Number.isFinite(Number(r.value)))       cur.value       += Number(r.value);
      cur.count += 1;
      out.set(key, cur);
    });
    // Compute ratio per bucket
    out.forEach((v) => { v.ratio = computeRatio(v); });
    return out;
  }

  // ============================================================
  // Multi-currency support
  // ============================================================
  // All amounts in memory (after the legacy mapping) are expressed in
  // **millions of EUR** because that's what the legacy fixture used and
  // what we converted budgets to in fetchProjects. This module converts
  // from EUR to the user's chosen display currency.
  //
  // - XOF/EUR is the fixed BCEAO peg (655.957 XOF = 1 EUR).
  // - USD and others can be edited by the user from the topbar widget.

  const DEFAULT_RATES = {
    // rate = how many <currency> for 1 EUR
    EUR: { rate: 1,         decimals: 2, symbol: "€",    name: "Euro" },
    XOF: { rate: 655.957,   decimals: 0, symbol: "FCFA", name: "Franc CFA (UEMOA)" },
    USD: { rate: 1.08,      decimals: 2, symbol: "$",    name: "US Dollar" },
    GBP: { rate: 0.85,      decimals: 2, symbol: "£",    name: "Pound Sterling" },
    CHF: { rate: 0.95,      decimals: 2, symbol: "CHF",  name: "Swiss Franc" },
    CAD: { rate: 1.48,      decimals: 2, symbol: "CA$",  name: "Canadian Dollar" },
  };

  const CURRENCY_KEY      = "melr.currency";
  const CUSTOM_RATES_KEY  = "melr.currency.customRates";

  function loadCustomRates() {
    try {
      const raw = localStorage.getItem(CUSTOM_RATES_KEY);
      return raw ? JSON.parse(raw) : {};
    } catch (e) { return {}; }
  }
  function saveCustomRates(rates) {
    try { localStorage.setItem(CUSTOM_RATES_KEY, JSON.stringify(rates)); } catch (e) {}
  }
  function getMergedRates() {
    return Object.assign({}, DEFAULT_RATES, loadCustomRates());
  }
  function getCurrentCurrency() {
    try { return localStorage.getItem(CURRENCY_KEY) || "XOF"; }
    catch (e) { return "XOF"; }
  }

  // Convert an amount expressed in millions of `sourceCurrency` to EUR
  // millions, going through EUR as the pivot.
  // rate = how many <ccy> for 1 EUR, so EUR amount = native / rate.
  function convertToEur(amountInMillions, sourceCurrency) {
    const rates = getMergedRates();
    const src   = rates[sourceCurrency] || rates.EUR;
    return Number(amountInMillions || 0) / src.rate;
  }

  // Format an amount expressed in millions of the source currency to a
  // localized string in the display currency. The source currency
  // defaults to EUR (backward compat with all existing callers).
  function formatAmount(amount, sourceCurrency, displayCurrency, lang) {
    const rates = getMergedRates();
    const tgtCcy = displayCurrency || getCurrentCurrency();
    const tgt    = rates[tgtCcy] || rates.EUR;
    const inEur  = convertToEur(amount, sourceCurrency || "EUR");
    const inTgt  = inEur * tgt.rate;
    const locale = (lang === "en") ? "en-US" : "fr-FR";
    const body = (tgt.decimals === 0)
      ? Math.round(inTgt).toLocaleString(locale)
      : inTgt.toLocaleString(locale, { minimumFractionDigits: tgt.decimals, maximumFractionDigits: tgt.decimals });
    return `${body} M ${tgt.symbol}`;
  }

  // Backward-compat: formatMoney assumes the amount is already in EUR.
  function formatMoney(amountInMEUR, currency, lang) {
    return formatAmount(amountInMEUR, "EUR", currency, lang);
  }
  function formatMoneyShort(amountInMEUR, currency, lang) {
    return formatMoney(amountInMEUR, currency, lang);
  }

  // React hook giving the current currency + a setter that persists in
  // localStorage. Components re-render via a global event listener so
  // changing the currency in the topbar updates every screen at once.
  function useCurrency() {
    const { useState, useEffect } = React;
    const [currency, setCurrencyState] = useState(getCurrentCurrency());
    useEffect(() => {
      const onChange = (e) => setCurrencyState(e.detail.currency);
      window.addEventListener("melr:currency", onChange);
      return () => window.removeEventListener("melr:currency", onChange);
    }, []);
    const setCurrency = (c) => {
      try { localStorage.setItem(CURRENCY_KEY, c); } catch (e) {}
      window.dispatchEvent(new CustomEvent("melr:currency", { detail: { currency: c } }));
    };
    return { currency, setCurrency, rates: getMergedRates() };
  }

  // Save a custom rate (or override an existing default).
  function setCustomRate(code, rate, options) {
    const code3 = (code || "").toUpperCase().slice(0, 4);
    if (!code3) throw new Error("Code devise vide");
    const r = Number(rate);
    if (!r || r <= 0) throw new Error("Taux invalide");
    const custom = loadCustomRates();
    custom[code3] = {
      rate: r,
      decimals: options && options.decimals != null ? options.decimals : 2,
      symbol:   options && options.symbol   != null ? options.symbol   : code3,
      name:     options && options.name     != null ? options.name     : code3,
    };
    saveCustomRates(custom);
    // Force re-render of all consumers
    window.dispatchEvent(new CustomEvent("melr:currency", { detail: { currency: getCurrentCurrency() } }));
  }

  // ============================================================
  // CSV export utility
  // ============================================================
  // Usage:
  //   exportCSV("projects-2026-05-17.csv", projects, [
  //     { key: "code",  label: "Code" },
  //     { key: "name",  label: "Nom",   value: (p) => p.nameFr || p.name },
  //     { key: "budget", label: "Budget M€", value: (p) => p.budget?.toFixed(2) },
  //   ]);
  function exportCSV(filename, rows, columns) {
    const esc = (v) => {
      if (v == null) return "";
      const s = String(v);
      if (/[",\n\r;]/.test(s)) return '"' + s.replace(/"/g, '""') + '"';
      return s;
    };
    const headers = columns.map((c) => esc(c.label)).join(",");
    const body = (rows || []).map((row) =>
      columns.map((c) => {
        const v = typeof c.value === "function" ? c.value(row) : row[c.key];
        return esc(v);
      }).join(",")
    ).join("\r\n");
    //  BOM = Excel will detect UTF-8 and display accents correctly.
    const csv = "" + headers + "\r\n" + body;
    const blob = new Blob([csv], { type: "text/csv;charset=utf-8" });
    const url  = URL.createObjectURL(blob);
    const a    = document.createElement("a");
    a.href = url;
    a.download = filename;
    document.body.appendChild(a);
    a.click();
    document.body.removeChild(a);
    URL.revokeObjectURL(url);
  }

  // Small reusable component for a pulsing red "Live" badge displayed
  // when a screen is subscribed to realtime updates.
  function LiveBadge({ on, lang }) {
    if (!on) return null;
    const title = lang === "fr" ? "Mises à jour en temps réel" : "Real-time updates";
    return React.createElement(
      "span",
      {
        title,
        style: {
          marginLeft: 10, display: "inline-flex", alignItems: "center", gap: 6,
          padding: "2px 8px", background: "#fee2e2", color: "#991b1b",
          borderRadius: 999, fontSize: 11, fontWeight: 500, verticalAlign: "middle",
        },
      },
      React.createElement("span", {
        style: { width: 7, height: 7, borderRadius: "50%", background: "#dc2626", animation: "pulse 1.5s ease-in-out infinite" },
      }),
      "Live"
    );
  }

  // ==================== EX-ANTE PHASE 1 (multi-country) ====================
  // Identification, Inputs, Scenarios, Calendar — see Supabase/exante-phase1.sql

  async function fetchCountryDefaults(countryCode) {
    const sb = await waitForSupabase();
    const { data, error } = await sb.from("country_exante_defaults")
      .select("*").eq("country_code", countryCode || "DEFAULT").maybeSingle();
    if (error) throw new Error(error.message);
    return data;
  }

  async function listCountryDefaults() {
    const sb = await waitForSupabase();
    const { data, error } = await sb.from("country_exante_defaults")
      .select("country_code, default_currency, strategic_framework_name")
      .order("country_code");
    if (error) throw new Error(error.message);
    return data || [];
  }

  // Seed identification/inputs/scenarios for a fresh dossier with country defaults.
  async function applyExanteCountryDefaults(dossierId, countryCode) {
    const sb = await waitForSupabase();
    const { error } = await sb.rpc("apply_exante_country_defaults", {
      p_dossier_id: dossierId,
      p_country_code: countryCode || "DEFAULT",
    });
    if (error) throw new Error(error.message);
  }

  // ----- Identification (1:1) -----
  async function fetchExanteIdentification(dossierId) {
    const sb = await waitForSupabase();
    const { data, error } = await sb.from("exante_identification")
      .select("*").eq("dossier_id", dossierId).maybeSingle();
    if (error) throw new Error(error.message);
    return data;
  }

  async function upsertExanteIdentification(dossierId, payload) {
    const sb = await waitForSupabase();
    const row = { ...payload, dossier_id: dossierId };
    const { data, error } = await sb.from("exante_identification")
      .upsert(row, { onConflict: "dossier_id" })
      .select().single();
    if (error) throw new Error(error.message);
    return data;
  }

  function useExanteIdentification(dossierId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState(null);
    const [loading, setLoading] = useState(true);
    const [realtime, setRealtime] = useState(false);
    const refresh = useCallback(async () => {
      if (!dossierId) { setData(null); setLoading(false); return; }
      try { setData(await fetchExanteIdentification(dossierId)); }
      catch (e) { console.error("[MELR] useExanteIdentification:", e); }
      finally { setLoading(false); }
    }, [dossierId]);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted || !dossierId) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-exante-ident-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes",
            { event: "*", schema: "public", table: "exante_identification", filter: "dossier_id=eq." + dossierId },
            () => { refresh(); })
          .subscribe((s) => { if (s === "SUBSCRIBED" && mounted) setRealtime(true); });
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh, dossierId]);
    return { data, loading, refresh, realtime };
  }

  // ----- Inputs (1:1) -----
  async function fetchExanteInputs(dossierId) {
    const sb = await waitForSupabase();
    const { data, error } = await sb.from("exante_inputs")
      .select("*").eq("dossier_id", dossierId).maybeSingle();
    if (error) throw new Error(error.message);
    return data;
  }

  async function upsertExanteInputs(dossierId, payload) {
    const sb = await waitForSupabase();
    const row = { ...payload, dossier_id: dossierId };
    const { data, error } = await sb.from("exante_inputs")
      .upsert(row, { onConflict: "dossier_id" })
      .select().single();
    if (error) throw new Error(error.message);
    return data;
  }

  function useExanteInputs(dossierId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState(null);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      if (!dossierId) { setData(null); setLoading(false); return; }
      try { setData(await fetchExanteInputs(dossierId)); }
      catch (e) { console.error("[MELR] useExanteInputs:", e); }
      finally { setLoading(false); }
    }, [dossierId]);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted || !dossierId) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-exante-inputs-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes",
            { event: "*", schema: "public", table: "exante_inputs", filter: "dossier_id=eq." + dossierId },
            () => { refresh(); })
          .subscribe();
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh, dossierId]);
    return { data, loading, refresh };
  }

  // ----- Scenarios (1:1) -----
  async function fetchExanteScenarios(dossierId) {
    const sb = await waitForSupabase();
    const { data, error } = await sb.from("exante_scenarios")
      .select("*").eq("dossier_id", dossierId).maybeSingle();
    if (error) throw new Error(error.message);
    return data;
  }

  async function upsertExanteScenarios(dossierId, payload) {
    const sb = await waitForSupabase();
    const row = { ...payload, dossier_id: dossierId };
    const { data, error } = await sb.from("exante_scenarios")
      .upsert(row, { onConflict: "dossier_id" })
      .select().single();
    if (error) throw new Error(error.message);
    return data;
  }

  function useExanteScenarios(dossierId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState(null);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      if (!dossierId) { setData(null); setLoading(false); return; }
      try { setData(await fetchExanteScenarios(dossierId)); }
      catch (e) { console.error("[MELR] useExanteScenarios:", e); }
      finally { setLoading(false); }
    }, [dossierId]);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted || !dossierId) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-exante-scen-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes",
            { event: "*", schema: "public", table: "exante_scenarios", filter: "dossier_id=eq." + dossierId },
            () => { refresh(); })
          .subscribe();
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh, dossierId]);
    return { data, loading, refresh };
  }

  // ----- Calendar (phases 1:N + activities 1:N) -----
  async function fetchExanteCalendar(dossierId) {
    const sb = await waitForSupabase();
    const [phases, activities] = await Promise.all([
      sb.from("exante_calendar_phases").select("*").eq("dossier_id", dossierId).order("position"),
      sb.from("exante_calendar_activities").select("*").eq("dossier_id", dossierId).order("position"),
    ]);
    if (phases.error) throw new Error(phases.error.message);
    if (activities.error) throw new Error(activities.error.message);
    return { phases: phases.data || [], activities: activities.data || [] };
  }

  async function createExantePhase(dossierId, payload) {
    const sb = await waitForSupabase();
    const { data, error } = await sb.from("exante_calendar_phases")
      .insert({ ...payload, dossier_id: dossierId }).select().single();
    if (error) throw new Error(error.message);
    return data;
  }
  async function updateExantePhase(phaseId, patch) {
    const sb = await waitForSupabase();
    const { error } = await sb.from("exante_calendar_phases").update(patch).eq("id", phaseId);
    if (error) throw new Error(error.message);
  }
  async function deleteExantePhase(phaseId) {
    const sb = await waitForSupabase();
    const { error } = await sb.from("exante_calendar_phases").delete().eq("id", phaseId);
    if (error) throw new Error(error.message);
  }
  async function createExanteActivity(dossierId, payload) {
    const sb = await waitForSupabase();
    const { data, error } = await sb.from("exante_calendar_activities")
      .insert({ ...payload, dossier_id: dossierId }).select().single();
    if (error) throw new Error(error.message);
    return data;
  }
  async function updateExanteActivity(activityId, patch) {
    const sb = await waitForSupabase();
    const { error } = await sb.from("exante_calendar_activities").update(patch).eq("id", activityId);
    if (error) throw new Error(error.message);
  }
  async function deleteExanteActivity(activityId) {
    const sb = await waitForSupabase();
    const { error } = await sb.from("exante_calendar_activities").delete().eq("id", activityId);
    if (error) throw new Error(error.message);
  }

  function useExanteCalendar(dossierId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState({ phases: [], activities: [] });
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      if (!dossierId) { setData({ phases: [], activities: [] }); setLoading(false); return; }
      try { setData(await fetchExanteCalendar(dossierId)); }
      catch (e) { console.error("[MELR] useExanteCalendar:", e); }
      finally { setLoading(false); }
    }, [dossierId]);
    useEffect(() => {
      let mounted = true, ch1 = null, ch2 = null;
      (async () => {
        await refresh();
        if (!mounted || !dossierId) return;
        const sb = await waitForSupabase();
        const tag = Math.random().toString(36).slice(2, 6);
        ch1 = sb.channel("rt-exante-phases-" + tag)
          .on("postgres_changes",
            { event: "*", schema: "public", table: "exante_calendar_phases", filter: "dossier_id=eq." + dossierId },
            () => { refresh(); }).subscribe();
        ch2 = sb.channel("rt-exante-acts-" + tag)
          .on("postgres_changes",
            { event: "*", schema: "public", table: "exante_calendar_activities", filter: "dossier_id=eq." + dossierId },
            () => { refresh(); }).subscribe();
      })();
      return () => { mounted = false; waitForSupabase().then((sb) => { if (ch1) sb.removeChannel(ch1); if (ch2) sb.removeChannel(ch2); }); };
    }, [refresh, dossierId]);
    return { ...data, loading, refresh };
  }

  // ==================== EX-ANTE PHASE 2 — FINANCIAL LINE ITEMS ============
  // See Supabase/exante-phase2.sql for the schema.

  function makeExanteLineHook(table) {
    return function (dossierId) {
      const { useState, useEffect, useCallback } = React;
      const [data, setData] = useState([]);
      const [loading, setLoading] = useState(true);
      const refresh = useCallback(async () => {
        if (!dossierId) { setData([]); setLoading(false); return; }
        try {
          const sb = await waitForSupabase();
          const { data: rows, error } = await sb.from(table)
            .select("*").eq("dossier_id", dossierId).order("position");
          if (error) throw new Error(error.message);
          setData(rows || []);
        } catch (e) { console.error("[MELR] " + table + ":", e); }
        finally { setLoading(false); }
      }, [dossierId]);
      useEffect(() => {
        let mounted = true, channel = null;
        (async () => {
          await refresh();
          if (!mounted || !dossierId) return;
          const sb = await waitForSupabase();
          channel = sb.channel("rt-" + table + "-" + Math.random().toString(36).slice(2, 6))
            .on("postgres_changes",
              { event: "*", schema: "public", table, filter: "dossier_id=eq." + dossierId },
              () => { refresh(); })
            .subscribe();
        })();
        return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
      }, [refresh, dossierId]);
      return { data, loading, refresh };
    };
  }

  function makeExanteLineCRUD(table) {
    return {
      async create(dossierId, payload) {
        const sb = await waitForSupabase();
        const { data, error } = await sb.from(table)
          .insert({ ...payload, dossier_id: dossierId }).select().single();
        if (error) throw new Error(error.message);
        return data;
      },
      async update(id, patch) {
        const sb = await waitForSupabase();
        const { error } = await sb.from(table).update(patch).eq("id", id);
        if (error) throw new Error(error.message);
      },
      async remove(id) {
        const sb = await waitForSupabase();
        const { error } = await sb.from(table).delete().eq("id", id);
        if (error) throw new Error(error.message);
      },
    };
  }

  const useExanteCapex     = makeExanteLineHook("exante_capex_lines");
  const useExanteOpex      = makeExanteLineHook("exante_opex_lines");
  const useExanteRevenue   = makeExanteLineHook("exante_revenue_lines");
  const useExanteFinancing = makeExanteLineHook("exante_financing_sources");

  const exanteCapexCrud     = makeExanteLineCRUD("exante_capex_lines");
  const exanteOpexCrud      = makeExanteLineCRUD("exante_opex_lines");
  const exanteRevenueCrud   = makeExanteLineCRUD("exante_revenue_lines");
  const exanteFinancingCrud = makeExanteLineCRUD("exante_financing_sources");

  // Phase 3 line tables
  const useExantePublicTransfers = makeExanteLineHook("exante_public_transfers");
  const useExanteMprTransfers    = makeExanteLineHook("exante_mpr_transfers");
  const useExanteExternalities   = makeExanteLineHook("exante_externalities");
  const useExanteConversionFactors = makeExanteLineHook("exante_conversion_factors");
  const exantePublicTransfersCrud = makeExanteLineCRUD("exante_public_transfers");
  const exanteMprTransfersCrud    = makeExanteLineCRUD("exante_mpr_transfers");
  const exanteExternalitiesCrud   = makeExanteLineCRUD("exante_externalities");
  const exanteConversionFactorsCrud = makeExanteLineCRUD("exante_conversion_factors");

  // Phase 4 line tables
  const useExanteQualityItems       = makeExanteLineHook("exante_quality_items");
  const useExanteMulticriteriaItems = makeExanteLineHook("exante_multicriteria_items");
  const exanteQualityItemsCrud       = makeExanteLineCRUD("exante_quality_items");
  const exanteMulticriteriaItemsCrud = makeExanteLineCRUD("exante_multicriteria_items");

  async function seedExanteQualityGrid(dossierId) {
    const sb = await waitForSupabase();
    const { error } = await sb.rpc("seed_exante_quality_grid", { p_dossier_id: dossierId });
    if (error) throw new Error(error.message);
  }
  async function seedExanteMulticriteria(dossierId) {
    const sb = await waitForSupabase();
    const { error } = await sb.rpc("seed_exante_multicriteria", { p_dossier_id: dossierId });
    if (error) throw new Error(error.message);
  }

  // Phase 7 — Stakeholder accounts + Institutional
  const useExanteStakeholders = makeExanteLineHook("exante_stakeholder_accounts");
  const exanteStakeholdersCrud = makeExanteLineCRUD("exante_stakeholder_accounts");

  async function seedExanteStakeholders(dossierId) {
    const sb = await waitForSupabase();
    const { error } = await sb.rpc("seed_exante_stakeholders", { p_dossier_id: dossierId });
    if (error) throw new Error(error.message);
  }
  async function seedExanteConversionFactors(dossierId) {
    const sb = await waitForSupabase();
    const { error } = await sb.rpc("seed_exante_conversion_factors", { p_dossier_id: dossierId });
    if (error) throw new Error(error.message);
  }

  // Institutional (1:1)
  async function fetchExanteInstitutional(dossierId) {
    const sb = await waitForSupabase();
    const { data, error } = await sb.from("exante_institutional")
      .select("*").eq("dossier_id", dossierId).maybeSingle();
    if (error) throw new Error(error.message);
    return data;
  }
  async function upsertExanteInstitutional(dossierId, payload) {
    const sb = await waitForSupabase();
    const row = { ...payload, dossier_id: dossierId };
    const { data, error } = await sb.from("exante_institutional")
      .upsert(row, { onConflict: "dossier_id" }).select().single();
    if (error) throw new Error(error.message);
    return data;
  }
  function useExanteInstitutional(dossierId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState(null);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      if (!dossierId) { setData(null); setLoading(false); return; }
      try { setData(await fetchExanteInstitutional(dossierId)); }
      catch (e) { console.error("[MELR] useExanteInstitutional:", e); }
      finally { setLoading(false); }
    }, [dossierId]);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted || !dossierId) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-exante-inst-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes",
            { event: "*", schema: "public", table: "exante_institutional", filter: "dossier_id=eq." + dossierId },
            () => { refresh(); }).subscribe();
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh, dossierId]);
    return { data, loading, refresh };
  }

  // ─── BASELINE TABS — generic project-scoped hook + CRUD ────────────────
  // Mirrors the exante helpers (makeExanteLineHook / makeExanteLineCRUD)
  // but scoped to a project_id instead of a dossier_id.
  function makeBaselineHook(table, opts) {
    opts = opts || {};
    return function useBaseline(projectId) {
      const { useState, useEffect, useCallback } = React;
      const [data, setData] = useState([]);
      const [loading, setLoading] = useState(true);
      const [realtime, setRealtime] = useState(false);
      const refresh = useCallback(async () => {
        if (!projectId) { setData([]); setLoading(false); return; }
        try {
          const sb = await waitForSupabase();
          let q = sb.from(table).select(opts.select || "*").eq("project_id", projectId);
          if (opts.orderBy) q = q.order(opts.orderBy);
          const { data: rows, error } = await q;
          if (error) throw new Error(error.message);
          setData(rows || []);
        } catch (e) { console.error("[MELR] " + table + ":", e); }
        finally { setLoading(false); }
      }, [projectId]);
      useEffect(() => {
        let mounted = true, channel = null;
        (async () => {
          await refresh();
          if (!mounted || !projectId) return;
          const sb = await waitForSupabase();
          channel = sb.channel("rt-" + table + "-" + Math.random().toString(36).slice(2, 6))
            .on("postgres_changes",
              { event: "*", schema: "public", table, filter: "project_id=eq." + projectId },
              () => { refresh(); })
            .subscribe((status) => { if (status === "SUBSCRIBED" && mounted) setRealtime(true); });
        })();
        return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
      }, [refresh, projectId]);
      return { data, loading, refresh, realtime };
    };
  }

  function makeBaselineCrud(table) {
    return {
      async create(projectId, payload) {
        const sb = await waitForSupabase();
        const { data, error } = await sb.from(table)
          .insert({ ...payload, project_id: projectId }).select().single();
        if (error) throw new Error(error.message);
        return data;
      },
      async update(id, patch) {
        const sb = await waitForSupabase();
        const { error } = await sb.from(table).update(patch).eq("id", id);
        if (error) throw new Error(error.message);
      },
      async remove(id) {
        const sb = await waitForSupabase();
        const { error } = await sb.from(table).delete().eq("id", id);
        if (error) throw new Error(error.message);
      },
    };
  }

  // Baseline operational tables
  const useStakeholders            = makeBaselineHook("stakeholders",            { orderBy: "name" });
  const useBaselineIssues          = makeBaselineHook("baseline_issues",         { orderBy: "created_at" });
  const useBaselineOpsLines        = makeBaselineHook("baseline_ops_lines",      { orderBy: "year" });
  const useBaselineInfrastructure  = makeBaselineHook("baseline_infrastructure", { orderBy: "category" });
  const stakeholdersCrud           = makeBaselineCrud("stakeholders");
  const baselineIssuesCrud         = makeBaselineCrud("baseline_issues");
  const baselineOpsLinesCrud       = makeBaselineCrud("baseline_ops_lines");
  const baselineInfrastructureCrud = makeBaselineCrud("baseline_infrastructure");

  // ─── SAT (M&E System Audit) — catalog + evaluations + responses + plan ─────
  // Catalog hooks — global (no tenant scoping). Cached for the page lifetime.
  let _satNormsCache = null, _satDomainsCache = null;
  async function fetchSatDomains() {
    if (_satDomainsCache) return _satDomainsCache;
    const sb = await waitForSupabase();
    const { data, error } = await sb.from("sat_domains")
      .select("code, position, title_fr, title_en, weight")
      .order("position");
    if (error) throw new Error(error.message);
    _satDomainsCache = data || [];
    return _satDomainsCache;
  }
  async function fetchSatNorms() {
    if (_satNormsCache) return _satNormsCache;
    const sb = await waitForSupabase();
    const { data, error } = await sb.from("sat_norms")
      .select("id, domain_code, position, text_fr, text_en, mov_fr, mov_en, max_value")
      .order("domain_code")
      .order("position");
    if (error) throw new Error(error.message);
    _satNormsCache = data || [];
    return _satNormsCache;
  }
  function useSatCatalog() {
    const { useState, useEffect } = React;
    const [domains, setDomains] = useState([]);
    const [norms, setNorms]     = useState([]);
    const [loading, setLoading] = useState(true);
    useEffect(() => {
      let cancelled = false;
      (async () => {
        try {
          const [d, n] = await Promise.all([fetchSatDomains(), fetchSatNorms()]);
          if (!cancelled) { setDomains(d); setNorms(n); }
        } catch (e) { console.error("[MELR] sat catalog:", e); }
        finally { if (!cancelled) setLoading(false); }
      })();
      return () => { cancelled = true; };
    }, []);
    return { domains, norms, loading };
  }

  // Project-scoped: list of evaluations for a project
  function useSatEvaluations(projectId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const [realtime, setRealtime] = useState(false);
    const refresh = useCallback(async () => {
      if (!projectId) { setData([]); setLoading(false); return; }
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("sat_evaluations")
          .select("id, project_id, cycle, state, team_lead, organization_lead, start_date, end_date, country, organization, score_total, score_max, comments, created_at, updated_at")
          .eq("project_id", projectId)
          .order("cycle", { ascending: false });
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.error("[MELR] sat_evaluations:", e); }
      finally { setLoading(false); }
    }, [projectId]);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted || !projectId) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-sat-evals-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes",
            { event: "*", schema: "public", table: "sat_evaluations", filter: "project_id=eq." + projectId },
            () => { refresh(); })
          .subscribe((status) => { if (status === "SUBSCRIBED" && mounted) setRealtime(true); });
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh, projectId]);
    return { data, loading, refresh, realtime };
  }

  // Evaluation-scoped: responses for one evaluation
  function useSatResponses(evaluationId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const [realtime, setRealtime] = useState(false);
    const refresh = useCallback(async () => {
      if (!evaluationId) { setData([]); setLoading(false); return; }
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("sat_responses")
          .select("id, evaluation_id, norm_id, rating, value, observation, is_priority, updated_at")
          .eq("evaluation_id", evaluationId);
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.error("[MELR] sat_responses:", e); }
      finally { setLoading(false); }
    }, [evaluationId]);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted || !evaluationId) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-sat-resp-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes",
            { event: "*", schema: "public", table: "sat_responses", filter: "evaluation_id=eq." + evaluationId },
            () => { refresh(); })
          .subscribe((status) => { if (status === "SUBSCRIBED" && mounted) setRealtime(true); });
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh, evaluationId]);
    return { data, loading, refresh, realtime };
  }

  // Evaluation-scoped: action plan items
  function useSatActionPlan(evaluationId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      if (!evaluationId) { setData([]); setLoading(false); return; }
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("sat_action_plan")
          .select("*").eq("evaluation_id", evaluationId).order("position");
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.error("[MELR] sat_action_plan:", e); }
      finally { setLoading(false); }
    }, [evaluationId]);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted || !evaluationId) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-sat-plan-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes",
            { event: "*", schema: "public", table: "sat_action_plan", filter: "evaluation_id=eq." + evaluationId },
            () => { refresh(); })
          .subscribe();
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh, evaluationId]);
    return { data, loading, refresh };
  }

  // CRUD bundles
  const satEvaluationsCrud = {
    async create(projectId, payload) {
      const sb = await waitForSupabase();
      const r = await sb.from("sat_evaluations")
        .insert({ ...payload, project_id: projectId }).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async update(id, patch) {
      const sb = await waitForSupabase();
      const r = await sb.from("sat_evaluations").update(patch).eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
    async remove(id) {
      const sb = await waitForSupabase();
      const r = await sb.from("sat_evaluations").delete().eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
  };

  const satResponsesCrud = {
    // Upsert by (evaluation_id, norm_id) — the table's UNIQUE constraint
    // makes this safe. Returns the upserted row.
    async upsert(evaluationId, normId, patch) {
      const sb = await waitForSupabase();
      const row = { evaluation_id: evaluationId, norm_id: normId, ...patch };
      const r = await sb.from("sat_responses")
        .upsert(row, { onConflict: "evaluation_id,norm_id" })
        .select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async remove(id) {
      const sb = await waitForSupabase();
      const r = await sb.from("sat_responses").delete().eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
  };

  const satActionPlanCrud = {
    async create(evaluationId, payload) {
      const sb = await waitForSupabase();
      const r = await sb.from("sat_action_plan")
        .insert({ ...payload, evaluation_id: evaluationId }).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async update(id, patch) {
      const sb = await waitForSupabase();
      const r = await sb.from("sat_action_plan").update(patch).eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
    async remove(id) {
      const sb = await waitForSupabase();
      const r = await sb.from("sat_action_plan").delete().eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
  };

  // ─── DVT (Data Verification) — audits + verifications + improvements ──────
  function useDvtAudits(projectId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const [realtime, setRealtime] = useState(false);
    const refresh = useCallback(async () => {
      if (!projectId) { setData([]); setLoading(false); return; }
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("dvt_audits")
          .select("*, sites(code, name)")
          .eq("project_id", projectId)
          .order("cycle", { ascending: false });
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.error("[MELR] dvt_audits:", e); }
      finally { setLoading(false); }
    }, [projectId]);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted || !projectId) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-dvt-aud-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes",
            { event: "*", schema: "public", table: "dvt_audits", filter: "project_id=eq." + projectId },
            () => { refresh(); })
          .subscribe((status) => { if (status === "SUBSCRIBED" && mounted) setRealtime(true); });
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh, projectId]);
    return { data, loading, refresh, realtime };
  }

  function makeDvtChildHook(table, channelTag) {
    return function useChild(auditId) {
      const { useState, useEffect, useCallback } = React;
      const [data, setData] = useState([]);
      const [loading, setLoading] = useState(true);
      const refresh = useCallback(async () => {
        if (!auditId) { setData([]); setLoading(false); return; }
        try {
          const sb = await waitForSupabase();
          const r = await sb.from(table)
            .select("*").eq("audit_id", auditId).order("position");
          if (r.error) throw new Error(r.error.message);
          setData(r.data || []);
        } catch (e) { console.error("[MELR] " + table + ":", e); }
        finally { setLoading(false); }
      }, [auditId]);
      useEffect(() => {
        let mounted = true, channel = null;
        (async () => {
          await refresh();
          if (!mounted || !auditId) return;
          const sb = await waitForSupabase();
          channel = sb.channel("rt-" + channelTag + "-" + Math.random().toString(36).slice(2, 6))
            .on("postgres_changes",
              { event: "*", schema: "public", table, filter: "audit_id=eq." + auditId },
              () => { refresh(); })
            .subscribe();
        })();
        return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
      }, [refresh, auditId]);
      return { data, loading, refresh };
    };
  }
  const useDvtVerifications = makeDvtChildHook("dvt_verifications", "dvt-verif");
  const useDvtImprovements  = makeDvtChildHook("dvt_improvements",  "dvt-improv");

  function makeDvtChildCrud(table) {
    return {
      async upsert(auditId, position, patch) {
        const sb = await waitForSupabase();
        const row = { audit_id: auditId, position, ...patch };
        const r = await sb.from(table)
          .upsert(row, { onConflict: "audit_id,position" })
          .select().single();
        if (r.error) throw new Error(r.error.message);
        return r.data;
      },
      async update(id, patch) {
        const sb = await waitForSupabase();
        const r = await sb.from(table).update(patch).eq("id", id);
        if (r.error) throw new Error(r.error.message);
      },
      async remove(id) {
        const sb = await waitForSupabase();
        const r = await sb.from(table).delete().eq("id", id);
        if (r.error) throw new Error(r.error.message);
      },
    };
  }
  const dvtVerificationsCrud = makeDvtChildCrud("dvt_verifications");
  const dvtImprovementsCrud  = makeDvtChildCrud("dvt_improvements");

  const dvtAuditsCrud = {
    async create(projectId, payload) {
      const sb = await waitForSupabase();
      const r = await sb.from("dvt_audits")
        .insert({ ...payload, project_id: projectId }).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async update(id, patch) {
      const sb = await waitForSupabase();
      const r = await sb.from("dvt_audits").update(patch).eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
    async remove(id) {
      const sb = await waitForSupabase();
      const r = await sb.from("dvt_audits").delete().eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
  };

  // ════════════════════════════════════════════════════════════════════════
  // ACTIVITIES DOMAIN (E3) · activities, types, partners, components, M:N
  // ════════════════════════════════════════════════════════════════════════
  // 1 activity = 1 project (FK NOT NULL). RLS héritée via projects.organization_id.
  // All hooks include realtime subscriptions so the UI reacts to peer edits.

  // ── HOOK · list activity_types (shared + own org) ────────────────────────
  function useActivityTypes() {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("activity_types")
          .select("id, organization_id, code, name_fr, name_en, icon, sort_order, active, is_shared, created_at")
          .eq("active", true)
          .order("sort_order", { ascending: true })
          .order("name_fr",    { ascending: true });
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.warn("[MELR] activity_types:", e.message || e); }
      finally { setLoading(false); }
    }, []);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-activity-types-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes", { event: "*", schema: "public", table: "activity_types" }, () => { refresh(); })
          .subscribe();
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh]);
    return { data, loading, refresh };
  }

  const activityTypesCrud = {
    async create({ code, name_fr, name_en, icon, sort_order, is_shared, organization_id }) {
      if (!code || !code.trim()) throw new Error("code required");
      if (!name_fr || !name_fr.trim()) throw new Error("name_fr required");
      const sb = await waitForSupabase();
      const profile = await currentProfile();
      const orgId = organization_id || (profile && profile.organization_id);
      const r = await sb.from("activity_types").insert({
        organization_id: is_shared ? null : orgId,
        code:            code.trim(),
        name_fr:         name_fr.trim(),
        name_en:         name_en && name_en.trim() ? name_en.trim() : null,
        icon:            icon || null,
        sort_order:      sort_order != null ? sort_order : 100,
        is_shared:       !!is_shared,
        created_by:      profile && profile.id,
      }).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async update(id, patch) {
      const sb = await waitForSupabase();
      const clean = {};
      ["code", "name_fr", "name_en", "icon", "sort_order", "active", "is_shared"].forEach((k) => {
        if (patch[k] !== undefined) clean[k] = patch[k] === "" ? null : patch[k];
      });
      const r = await sb.from("activity_types").update(clean).eq("id", id).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async remove(id) {
      const sb = await waitForSupabase();
      const r = await sb.from("activity_types").delete().eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
  };

  // ── HOOK · list partners (shared + own org) ──────────────────────────────
  function usePartners() {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("partners")
          .select("id, organization_id, code, name_fr, name_en, description, active, is_shared, created_at")
          .eq("active", true)
          .order("name_fr", { ascending: true });
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.warn("[MELR] partners:", e.message || e); }
      finally { setLoading(false); }
    }, []);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-partners-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes", { event: "*", schema: "public", table: "partners" }, () => { refresh(); })
          .subscribe();
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh]);
    return { data, loading, refresh };
  }

  const partnersCrud = {
    async create({ code, name_fr, name_en, description, is_shared, organization_id }) {
      if (!name_fr || !name_fr.trim()) throw new Error("name_fr required");
      const sb = await waitForSupabase();
      const profile = await currentProfile();
      const orgId = organization_id || (profile && profile.organization_id);
      const r = await sb.from("partners").insert({
        organization_id: is_shared ? null : orgId,
        code:            code && code.trim() ? code.trim() : null,
        name_fr:         name_fr.trim(),
        name_en:         name_en && name_en.trim() ? name_en.trim() : null,
        description:     description && description.trim() ? description.trim() : null,
        is_shared:       !!is_shared,
        created_by:      profile && profile.id,
      }).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async update(id, patch) {
      const sb = await waitForSupabase();
      const clean = {};
      ["code", "name_fr", "name_en", "description", "active", "is_shared"].forEach((k) => {
        if (patch[k] !== undefined) clean[k] = patch[k] === "" ? null : patch[k];
      });
      const r = await sb.from("partners").update(clean).eq("id", id).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async remove(id) {
      const sb = await waitForSupabase();
      const r = await sb.from("partners").delete().eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
  };

  // ── HOOK · list project_components for ONE project ───────────────────────
  function useProjectComponents(projectId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      if (!projectId) { setData([]); setLoading(false); return; }
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("project_components")
          .select("id, project_id, code, name_fr, name_en, sort_order, active, created_at")
          .eq("project_id", projectId)
          .eq("active", true)
          .order("sort_order", { ascending: true })
          .order("name_fr",    { ascending: true });
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.warn("[MELR] project_components:", e.message || e); }
      finally { setLoading(false); }
    }, [projectId]);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-proj-comp-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes", { event: "*", schema: "public", table: "project_components" }, () => { refresh(); })
          .subscribe();
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh]);
    return { data, loading, refresh };
  }

  const projectComponentsCrud = {
    async create({ project_id, code, name_fr, name_en, sort_order }) {
      if (!project_id) throw new Error("project_id required");
      if (!name_fr || !name_fr.trim()) throw new Error("name_fr required");
      const sb = await waitForSupabase();
      const profile = await currentProfile();
      const r = await sb.from("project_components").insert({
        project_id,
        code:       code && code.trim() ? code.trim() : null,
        name_fr:    name_fr.trim(),
        name_en:    name_en && name_en.trim() ? name_en.trim() : null,
        sort_order: sort_order != null ? sort_order : 100,
        created_by: profile && profile.id,
      }).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async update(id, patch) {
      const sb = await waitForSupabase();
      const clean = {};
      ["code", "name_fr", "name_en", "sort_order", "active"].forEach((k) => {
        if (patch[k] !== undefined) clean[k] = patch[k] === "" ? null : patch[k];
      });
      const r = await sb.from("project_components").update(clean).eq("id", id).select().single();
      if (r.error) throw new Error(r.error.message);
      return r.data;
    },
    async remove(id) {
      const sb = await waitForSupabase();
      const r = await sb.from("project_components").delete().eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
  };

  // ── HOOK · list activities (optionally filtered by project) ─────────────
  // When projectId is null/undefined, returns every activity the user can
  // see (their org's projects). Useful for an org-wide « Activités » screen.
  function useActivities(projectId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      try {
        const sb = await waitForSupabase();
        let q = sb.from("activities")
          .select("id, project_id, code, title, description, activity_type_id, project_component_id, location_name, latitude, longitude, start_date, end_date, status, gender_sensitive, budget_planned, cost_actual, currency, has_press, has_tdrs, has_report, notes, created_by, created_at, updated_at")
          .order("start_date", { ascending: false, nullsFirst: false })
          .order("created_at", { ascending: false });
        if (projectId) q = q.eq("project_id", projectId);
        const r = await q;
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.warn("[MELR] activities:", e.message || e); }
      finally { setLoading(false); }
    }, [projectId]);
    useEffect(() => {
      let mounted = true, channel = null;
      (async () => {
        await refresh();
        if (!mounted) return;
        const sb = await waitForSupabase();
        channel = sb.channel("rt-activities-" + Math.random().toString(36).slice(2, 6))
          .on("postgres_changes", { event: "*", schema: "public", table: "activities" }, () => { refresh(); })
          .subscribe();
      })();
      return () => { mounted = false; if (channel) waitForSupabase().then((sb) => sb.removeChannel(channel)); };
    }, [refresh]);
    return { data, loading, refresh };
  }

  // ── HOOK · partners + indicators currently linked to ONE activity ────────
  // Returns { partners: [partner_id…], indicatorIds: [indicator_id…] }.
  // The UI uses this to pre-fill the multi-select fields in the edit modal.
  function useActivityLinks(activityId) {
    const { useState, useEffect, useCallback } = React;
    const [partners, setPartners] = useState([]);
    const [indicatorIds, setIndicatorIds] = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      if (!activityId) { setPartners([]); setIndicatorIds([]); setLoading(false); return; }
      try {
        const sb = await waitForSupabase();
        const [pr, ir] = await Promise.all([
          sb.from("activity_partners").select("partner_id, role").eq("activity_id", activityId),
          sb.from("activity_indicators").select("indicator_id").eq("activity_id", activityId),
        ]);
        if (pr.error) throw new Error(pr.error.message);
        if (ir.error) throw new Error(ir.error.message);
        setPartners((pr.data || []).map((r) => ({ partner_id: r.partner_id, role: r.role })));
        setIndicatorIds((ir.data || []).map((r) => r.indicator_id));
      } catch (e) { console.warn("[MELR] activity_links:", e.message || e); }
      finally { setLoading(false); }
    }, [activityId]);
    useEffect(() => { refresh(); }, [refresh]);
    return { partners, indicatorIds, loading, refresh };
  }

  // ── HOOK · attachments for ONE activity ──────────────────────────────────
  function useActivityAttachments(activityId) {
    const { useState, useEffect, useCallback } = React;
    const [data, setData] = useState([]);
    const [loading, setLoading] = useState(true);
    const refresh = useCallback(async () => {
      if (!activityId) { setData([]); setLoading(false); return; }
      try {
        const sb = await waitForSupabase();
        const r = await sb.from("activity_attachments")
          .select("id, activity_id, file_path, file_name, mime_type, size_bytes, kind, uploaded_by, uploaded_at")
          .eq("activity_id", activityId)
          .order("uploaded_at", { ascending: false });
        if (r.error) throw new Error(r.error.message);
        setData(r.data || []);
      } catch (e) { console.warn("[MELR] activity_attachments:", e.message || e); }
      finally { setLoading(false); }
    }, [activityId]);
    useEffect(() => { refresh(); }, [refresh]);
    return { data, loading, refresh };
  }

  const activitiesCrud = {
    async create(payload) {
      if (!payload || !payload.project_id) throw new Error("project_id required");
      if (!payload.title || !payload.title.trim()) throw new Error("title required");
      const sb = await waitForSupabase();
      const profile = await currentProfile();
      const insertRow = {
        project_id:            payload.project_id,
        code:                  payload.code && payload.code.trim() ? payload.code.trim() : null,
        title:                 payload.title.trim(),
        description:           payload.description && payload.description.trim() ? payload.description.trim() : null,
        activity_type_id:      payload.activity_type_id || null,
        project_component_id:  payload.project_component_id || null,
        location_name:         payload.location_name && payload.location_name.trim() ? payload.location_name.trim() : null,
        latitude:              payload.latitude  === "" || payload.latitude  == null ? null : Number(payload.latitude),
        longitude:             payload.longitude === "" || payload.longitude == null ? null : Number(payload.longitude),
        start_date:            payload.start_date || null,
        end_date:              payload.end_date   || null,
        status:                payload.status     || "not_started",
        gender_sensitive:      !!payload.gender_sensitive,
        budget_planned:        payload.budget_planned === "" || payload.budget_planned == null ? null : Number(payload.budget_planned),
        cost_actual:           payload.cost_actual    === "" || payload.cost_actual    == null ? null : Number(payload.cost_actual),
        currency:              payload.currency || "XOF",
        has_press:             !!payload.has_press,
        has_tdrs:              !!payload.has_tdrs,
        has_report:            !!payload.has_report,
        notes:                 payload.notes && payload.notes.trim() ? payload.notes.trim() : null,
        created_by:            profile && profile.id,
      };
      const r = await sb.from("activities").insert(insertRow).select().single();
      if (r.error) throw new Error(r.error.message);
      const created = r.data;
      // Cascade M:N links if provided
      if (Array.isArray(payload.partner_ids) && payload.partner_ids.length) {
        const pr = await sb.from("activity_partners").insert(
          payload.partner_ids.map((pid) => ({ activity_id: created.id, partner_id: pid }))
        );
        if (pr.error) console.warn("[MELR] activity_partners insert:", pr.error.message);
      }
      if (Array.isArray(payload.indicator_ids) && payload.indicator_ids.length) {
        const ir = await sb.from("activity_indicators").insert(
          payload.indicator_ids.map((iid) => ({ activity_id: created.id, indicator_id: iid }))
        );
        if (ir.error) console.warn("[MELR] activity_indicators insert:", ir.error.message);
      }
      return created;
    },
    async update(id, patch) {
      const sb = await waitForSupabase();
      const clean = {};
      const passthrough = [
        "code", "title", "description", "activity_type_id", "project_component_id",
        "location_name", "start_date", "end_date", "status", "currency", "notes",
      ];
      passthrough.forEach((k) => {
        if (patch[k] !== undefined) clean[k] = patch[k] === "" ? null : patch[k];
      });
      // Coerce numerics + booleans explicitly so an empty form field doesn't
      // accidentally NaN its way into the DB.
      ["latitude", "longitude", "budget_planned", "cost_actual"].forEach((k) => {
        if (patch[k] !== undefined) clean[k] = patch[k] === "" || patch[k] == null ? null : Number(patch[k]);
      });
      ["gender_sensitive", "has_press", "has_tdrs", "has_report"].forEach((k) => {
        if (patch[k] !== undefined) clean[k] = !!patch[k];
      });
      const r = await sb.from("activities").update(clean).eq("id", id).select().single();
      if (r.error) throw new Error(r.error.message);
      // Replace partners / indicators if the caller passed arrays. The
      // semantics are intentionally "full replace" so the modal can stay
      // simple (no diff logic needed in the UI).
      if (Array.isArray(patch.partner_ids)) {
        const del = await sb.from("activity_partners").delete().eq("activity_id", id);
        if (del.error) console.warn("[MELR] partners replace · delete:", del.error.message);
        if (patch.partner_ids.length) {
          const ins = await sb.from("activity_partners").insert(
            patch.partner_ids.map((pid) => ({ activity_id: id, partner_id: pid }))
          );
          if (ins.error) console.warn("[MELR] partners replace · insert:", ins.error.message);
        }
      }
      if (Array.isArray(patch.indicator_ids)) {
        const del = await sb.from("activity_indicators").delete().eq("activity_id", id);
        if (del.error) console.warn("[MELR] indicators replace · delete:", del.error.message);
        if (patch.indicator_ids.length) {
          const ins = await sb.from("activity_indicators").insert(
            patch.indicator_ids.map((iid) => ({ activity_id: id, indicator_id: iid }))
          );
          if (ins.error) console.warn("[MELR] indicators replace · insert:", ins.error.message);
        }
      }
      return r.data;
    },
    async remove(id) {
      const sb = await waitForSupabase();
      // Best-effort cleanup of attachment files BEFORE deleting the row
      // (FK CASCADE handles the M:N rows). We don't fail the delete if
      // storage cleanup fails — the DB row removal is the primary intent.
      try {
        const rows = await sb.from("activity_attachments")
          .select("file_path").eq("activity_id", id);
        const paths = (rows.data || []).map((r) => r.file_path).filter(Boolean);
        if (paths.length) await sb.storage.from("activity-attachments").remove(paths).catch(() => {});
      } catch (_) {}
      const r = await sb.from("activities").delete().eq("id", id);
      if (r.error) throw new Error(r.error.message);
    },
  };

  // ── Upload helper for activity attachments ───────────────────────────────
  // Path layout : <org_id>/<activity_id>/<random>-<safe_filename>
  // org_id prefix mirrors indicator-pirs convention and is required by RLS.
  async function uploadActivityAttachment(activityId, orgId, file, kind) {
    if (!activityId) throw new Error("activityId required");
    if (!orgId)      throw new Error("orgId required");
    if (!file)       throw new Error("file required");
    const sb = await waitForSupabase();
    const profile = await currentProfile();
    const safeName = (file.name || "file").replace(/[^a-zA-Z0-9._-]/g, "_");
    const rand = Math.random().toString(36).slice(2, 10);
    const path = `${orgId}/${activityId}/${rand}-${safeName}`;
    const up = await sb.storage.from("activity-attachments").upload(path, file, {
      cacheControl: "3600",
      upsert: false,
      contentType: file.type || "application/octet-stream",
    });
    if (up.error) throw new Error(up.error.message);
    const ins = await sb.from("activity_attachments").insert({
      activity_id: activityId,
      file_path:   path,
      file_name:   file.name || safeName,
      mime_type:   file.type || null,
      size_bytes:  file.size || null,
      kind:        kind || null,
      uploaded_by: profile && profile.id,
    }).select().single();
    if (ins.error) {
      // Roll back the storage upload to avoid orphan files
      await sb.storage.from("activity-attachments").remove([path]).catch(() => {});
      throw new Error(ins.error.message);
    }
    return ins.data;
  }

  async function removeActivityAttachment(attachmentId) {
    const sb = await waitForSupabase();
    const row = await sb.from("activity_attachments").select("file_path").eq("id", attachmentId).single();
    if (row.error) throw new Error(row.error.message);
    if (row.data && row.data.file_path) {
      await sb.storage.from("activity-attachments").remove([row.data.file_path]).catch(() => {});
    }
    const del = await sb.from("activity_attachments").delete().eq("id", attachmentId);
    if (del.error) throw new Error(del.error.message);
  }

  async function getActivityAttachmentUrl(filePath, ttlSeconds = 3600) {
    if (!filePath) return null;
    const sb = await waitForSupabase();
    const r = await sb.storage.from("activity-attachments").createSignedUrl(filePath, ttlSeconds);
    if (r.error) { console.warn("[MELR] activity attach signed url:", r.error.message); return null; }
    return r.data && r.data.signedUrl;
  }

  // Expose everything on window.melr — namespace already created by
  // src/main.jsx (Vite bundle). Defensive in case main.jsx hasn't run yet.
  window.melr = window.melr || {};
  Object.assign(window.melr, {
    fetchProjects,
    fetchProjectDetail,
    fetchSectors,
    currentProfile,
    updateProfile,
    changePassword,
    createProject,
    transferProjectToOrg,
    transferProgrammeToOrg,
    updateProject,
    createProgramme,
    updateProgramme,
    deleteProgramme,
    fetchProgrammes,
    usePrograms,
    notificationsCrud,
    createSite,
    createIndicator,
    updateIndicator,
    removeIndicator,
    createIndicatorValue,
    bulkInsertIndicatorValues,
    updateValidationItem,
    createValidationAction,
    submitForValidation,
    assignValidationItem,
    createBulkNotifications,
    useValidationTemplates,
    validationTemplatesCrud,
    useAlertRules,
    alertRulesCrud,
    uploadValidationAttachment,
    getValidationAttachmentUrl,
    useProjectDocuments,
    uploadProjectDocument,
    getProjectDocumentUrl,
    removeProjectDocument,
    useProjectTeam,
    projectTeamCrud,
    useProjectRisks,
    risksCrud,
    useProjectActivity,
    useProjectBudget,
    budgetLinesCrud,
    useDisabledModules,
    useAllOrganizationModules,
    organizationModulesCrud,
    useRoles, rolesCrud, seedDefaultRoles,
    useUserRoles, userRolesCrud,
    fetchCurrentOrganization, useCurrentOrganization, updateOrganization,
    useSupportTickets, createSupportTicket, updateSupportTicket,
    useCurrentProfile,
    useUserMemberships, useOrgMembershipsList, membershipsCrud,
    getActiveOrgIdSync, setActiveOrgIdSync, useActiveOrgId,
    useCurrentUserPermissions,
    useAllOrganizations, organizationsCrud,
    useInvitations, invitationsCrud, peekInvitation, redeemInvitation, sendInvitationEmail,
    listPublicOrganizations, setMyRequestedOrg,
    fetchOrgProfiles,
    useValidationActions,
    fetchPendingUsers,
    assignUserToOrg,
    revokeUserFromOrg,
    useOrgMembers,
    createPlanPhase,
    createPlanAction,
    updatePlanAction,
    useProjects,
    useProjectDetail,
    useNotifications,
    useIndicators,
    useSites,
    usePlanActions,
    fetchPlan,
    usePlan,
    useValidationQueue,
    useReports,
    useAudits,
    useExanteDossiers,
    createExanteDossier,
    // ex-ante phase 1
    fetchCountryDefaults,
    listCountryDefaults,
    applyExanteCountryDefaults,
    fetchExanteIdentification,
    upsertExanteIdentification,
    useExanteIdentification,
    fetchExanteInputs,
    upsertExanteInputs,
    useExanteInputs,
    fetchExanteScenarios,
    upsertExanteScenarios,
    useExanteScenarios,
    fetchExanteCalendar,
    createExantePhase,
    updateExantePhase,
    deleteExantePhase,
    createExanteActivity,
    updateExanteActivity,
    deleteExanteActivity,
    useExanteCalendar,
    // ex-ante phase 2: financial line items
    useExanteCapex,    exanteCapexCrud,
    useExanteOpex,     exanteOpexCrud,
    useExanteRevenue,  exanteRevenueCrud,
    useExanteFinancing, exanteFinancingCrud,
    // ex-ante phase 3
    useExantePublicTransfers, exantePublicTransfersCrud,
    useExanteMprTransfers,    exanteMprTransfersCrud,
    useExanteExternalities,   exanteExternalitiesCrud,
    useExanteConversionFactors, exanteConversionFactorsCrud,
    // ex-ante phase 4
    useExanteQualityItems, exanteQualityItemsCrud, seedExanteQualityGrid,
    useExanteMulticriteriaItems, exanteMulticriteriaItemsCrud, seedExanteMulticriteria,
    // ex-ante phase 7
    useExanteStakeholders, exanteStakeholdersCrud, seedExanteStakeholders,
    seedExanteConversionFactors,
    fetchExanteInstitutional, upsertExanteInstitutional, useExanteInstitutional,
    // baseline tabs CRUD
    useStakeholders,           stakeholdersCrud,
    useBaselineIssues,         baselineIssuesCrud,
    useBaselineOpsLines,       baselineOpsLinesCrud,
    useBaselineInfrastructure, baselineInfrastructureCrud,
    // SAT audit
    useSatCatalog, fetchSatDomains, fetchSatNorms,
    useSatEvaluations, satEvaluationsCrud,
    useSatResponses,   satResponsesCrud,
    useSatActionPlan,  satActionPlanCrud,
    // DVT audit
    useDvtAudits,         dvtAuditsCrud,
    useDvtVerifications,  dvtVerificationsCrud,
    useDvtImprovements,   dvtImprovementsCrud,
    useForms,
    useFormSubmissions, submitForm, flushLocalQueue, useLocalQueueSize, useLocalQueue,
    readLocalQueue,
    uploadFormAttachment, getFormAttachmentUrl,
    useProjectAgents, useMyProjects, projectAgentsCrud,
    formsCrud, useFormAgents, formAgentsCrud, exportFormSubmissions,
    useSectors, sectorsCrud,
    useIndicatorDefinitions, indicatorDefinitionsCrud,
    uploadIndicatorPirs, removeIndicatorPirs, getIndicatorPirsUrl,
    useDefinitionAttachments, addDefinitionAttachment, removeDefinitionAttachment, updateDefinitionAttachment,
    importIndicatorDefinitionsFromExcel,
    scoreScales, scoreLabels, scoreToNumeric, scoreFromNumeric,
    // Indicator disaggregation (Phase B)
    useDisaggregationAxes,
    useAxisValues,
    useDisaggregationEntities,
    useDefinitionByCode,
    useDefinitionDisaggregations,
    useDisaggregatedValues,
    disaggregationAxesCrud,
    disaggregationAxisValuesCrud,
    disaggregationEntitiesCrud,
    definitionDisaggregationsCrud,
    disaggregatedValuesCrud,
    computeRatio,
    aggregateDisaggregation,
    createFormForIndicator,
    // Activities domain (E3)
    useActivityTypes,    activityTypesCrud,
    usePartners,         partnersCrud,
    useProjectComponents, projectComponentsCrud,
    useActivities,       activitiesCrud,
    useActivityLinks,
    useActivityAttachments,
    uploadActivityAttachment, removeActivityAttachment, getActivityAttachmentUrl,
    useLearningQuestions,
    phaseLabel,
    LiveBadge,
    exportCSV,
    formatMoney,
    formatMoneyShort,
    formatAmount,
    convertToEur,
    useCurrency,
    setCustomRate,
    getMergedRates,
    getCurrentCurrency,
  });
})();