/* global window */
// =====================================================================
// Supabase data loader for the Performance Marketing Command Center
// ---------------------------------------------------------------------
// Fetches live data from Supabase RPC functions and mutates the same
// window.* globals the original data.jsx mock provides. Shapes match
// the mock exactly so existing views render without changes.
// =====================================================================

(function () {
  // Augment i18n tabs with the new ones added in views2 (TrackingView, CampaignsView)
  if (window.i18n) {
    if (window.i18n.es?.tabs) {
      window.i18n.es.tabs.campaigns = "Campañas";
      window.i18n.es.tabs.tracking = "Sin UTMs";
      window.i18n.es.tabs.unknowns = "Unknowns";
      window.i18n.es.tabs.google = "Google Ads";
    }
    if (window.i18n.en?.tabs) {
      window.i18n.en.tabs.campaigns = "Campaigns";
      window.i18n.en.tabs.tracking = "No UTMs";
      window.i18n.en.tabs.unknowns = "Unknowns";
      window.i18n.en.tabs.google = "Google Ads";
    }
  }

  const URL = window.SUPABASE_URL;
  const KEY = window.SUPABASE_ANON_KEY;
  if (!URL || !KEY) {
    console.warn("[data-supabase] No Supabase config — keeping mock data.");
    window.__dataSource = "mock";
    return;
  }
  if (!window.supabase || !window.supabase.createClient) {
    console.error("[data-supabase] @supabase/supabase-js not loaded — keeping mock.");
    window.__dataSource = "mock";
    return;
  }

  const sb = window.supabase.createClient(URL, KEY);
  window.__sb = sb;
  window.__dataSource = "supabase";

  // ---------- Dashboard scopes ----------
  // Each top-level dashboard has a fixed set of accounts.
  // Reclutamiento additionally pulls campaigns whose name matches the regex,
  // from ANY account. So Freedom/Reclutamiento campaigns appear in BOTH their
  // origin dashboard AND in Reclutamiento.
  window.DASHBOARDS = {
    general: {
      key: "general",
      label_es: "General", label_en: "General",
      accounts: [
        { ext: "act_2039271983307378", label: "Solar" },
        { ext: "act_1867304290625424", label: "Roofing" },
        { ext: "act_1304736984843818", label: "Anker" },
        { ext: "act_1250697670362549", label: "Water" },
      ],
      regex: null,
    },
    consultores: {
      key: "consultores",
      label_es: "Consultores Élite", label_en: "Consultores Élite",
      accounts: [
        { ext: "act_557109357382159", label: "Consultores Élite PR" },
      ],
      regex: null,
    },
    fl: {
      key: "fl",
      label_es: "Florida", label_en: "Florida",
      accounts: [
        { ext: "act_1381637046601971", label: "Consultores Élite FL" },
        { ext: "act_1355678559658807", label: "Windmar FL" },
      ],
      regex: null,
    },
    accion: {
      key: "accion",
      label_es: "Windmar en Acción", label_en: "Windmar en Acción",
      accounts: [
        { ext: "act_25932528559761958", label: "En Acción" },
      ],
      regex: null,
    },
    reclutamiento: {
      key: "reclutamiento",
      label_es: "Reclutamiento", label_en: "Recruitment",
      accounts: [
        { ext: "act_3113211558869620", label: "Careers" },
      ],
      regex: "reclutamiento|recruitment|career|careers",
    },
  };

  // ---------- Enum → TitleCase (match the mock) ----------
  const TYPE_LABEL = {
    founder: "Founder", before_after: "BeforeAfter", static_image: "StaticImage",
    ugc: "UGC", brand_film: "BrandFilm", demo: "Demo", offer: "Offer",
    trend: "Trend", testimonial: "Testimonial", meme: "Meme",
  };
  const ANGLE_LABEL = {
    savings: "Savings", outage: "Outage", independence: "Independence", financing: "Financing",
  };
  const ALL_TYPES = Object.values(TYPE_LABEL);
  const ALL_ANGLES = Object.values(ANGLE_LABEL);

  // ---------- Date range helpers ----------
  function toISO(d) { return d.toISOString().slice(0, 10); }
  function daysAgo(n) { const d = new Date(); d.setDate(d.getDate() - n); return d; }
  function startOfMonth() { const d = new Date(); d.setDate(1); return d; }
  function startOfQuarter() { const d = new Date(); d.setMonth(Math.floor(d.getMonth() / 3) * 3, 1); return d; }

  function resolveRange(preset) {
    // Custom range: object {from, to}
    if (preset && typeof preset === "object" && preset.from && preset.to) {
      return { from: preset.from, to: preset.to, label: `${preset.from} → ${preset.to}` };
    }
    const today = new Date();
    switch (preset) {
      case "today":  return { from: toISO(today),           to: toISO(today), label: "Hoy" };
      case "7d":     return { from: toISO(daysAgo(7)),      to: toISO(today), label: "Últimos 7 días" };
      case "30d":    return { from: toISO(daysAgo(30)),     to: toISO(today), label: "Últimos 30 días" };
      case "90d":    return { from: toISO(daysAgo(90)),     to: toISO(today), label: "Últimos 90 días" };
      case "mtd":    return { from: toISO(startOfMonth()),  to: toISO(today), label: "MTD" };
      case "qtd":    return { from: toISO(startOfQuarter()),to: toISO(today), label: "QTD" };
      default:       return { from: toISO(daysAgo(30)),     to: toISO(today), label: "Últimos 30 días" };
    }
  }
  window.resolveDateRange = resolveRange;

  // ---------- Shape transformers ----------
  function buildProfitTotals(totalsRow, deltaRows) {
    const deltas = {};
    (deltaRows || []).forEach((d) => { deltas[d.metric] = Number(d.delta_pct || 0); });
    const t = totalsRow || {};
    return {
      spend:   { val: Number(t.spend   || 0), delta: deltas.spend   || 0 },
      revenue: { val: Number(t.revenue || 0), delta: deltas.revenue || 0 },
      cm:      { val: Number(t.cm      || 0), delta: deltas.cm      || 0 },
      cmPct:   { val: Number(t.cm_pct  || 0), delta: deltas.cm_pct  || 0 },
      cac:     { val: Number(t.cac     || 0), delta: deltas.cac     || 0 },
      leads:   { val: Number(t.leads   || 0), delta: deltas.leads   || 0 },
      appts:   { val: Number(t.appts   || 0), delta: deltas.appts   || 0 },
      sales:   { val: Number(t.sales   || 0), delta: deltas.sales   || 0 },
      leads_no_utms: Number(t.leads_no_utms || 0),
      leads_lax: Number(t.leads_lax || 0),
      sales_by_modified: Number(t.sales_by_modified || 0),
      sales_strict: Number(t.sales_strict || 0),
      sales_no_utms: Number(t.sales_no_utms || 0),
      sales_unknown: Number(t.sales_unknown || 0),
    };
  }

  function buildProfitSeries(seriesRows) {
    const out = { day: [], spend: [], revenue: [], cm: [], cac: [], leads: [], appts: [], sales: [] };
    (seriesRows || []).forEach((r) => {
      out.day.push(r.day);
      out.spend.push(Number(r.spend || 0));
      out.revenue.push(Number(r.revenue || 0));
      out.cm.push(Number(r.cm || 0));
      out.cac.push(Number(r.cac || 0));
      out.leads.push(Number(r.leads || 0));
      out.appts.push(Number(r.appts || 0));
      out.sales.push(Number(r.sales || 0));
    });
    return out;
  }

  function buildCampaigns(rows) {
    return (rows || []).map((r) => ({
      id: r.campaign_id,
      name: r.campaign_name || "—",
      channel: r.platform || "meta",
      market: r.market || "PR",
      product: r.product || "other",
      spend: Number(r.spend || 0),
      leads: Number(r.leads || 0),
      sales: Number(r.sales || 0),
      cm: Number(r.cm || 0),
      runtime: r.runtime_days || 0,
      status: r.decision || "watch",
      reason_es: r.decision_reason || "Sin razón calculada",
      reason_en: r.decision_reason || "No reason yet",
      action_es: "Sin acción asignada",
      action_en: "No action assigned",
    }));
  }

  // Clean up Meta account labels for filter UI (drop "by WindmarHome" suffix etc.)
  function shortAccountLabel(name) {
    if (!name) return "—";
    return String(name)
      .replace(/\s+by\s+windmar\w*/i, "")
      .replace(/^Windmar\s+/i, "")
      .trim();
  }

  function buildCreatives(rows) {
    return (rows || []).map((r) => ({
      id: r.ad_id,
      name: r.ad_name || r.ad_id,
      campaign_id: r.campaign_id || null,
      campaign_name: r.campaign_name || "(sin nombre)",
      product: (r.product || "solar"),               // keep enum lowercase; UI normalizes
      account_ext: r.account_external_id || null,
      account_name: shortAccountLabel(r.account_name),
      type:  TYPE_LABEL[r.creative_type]  || "StaticImage",
      angle: ANGLE_LABEL[r.creative_angle] || "Savings",
      hook_es: r.hook_es || r.ad_name || "—",
      hook_en: r.hook_en || r.ad_name || "—",
      spend: Number(r.spend || 0),
      leads: Number(r.leads_platform || 0),
      sales: Number(r.sales || 0),
      cac:   r.cac != null ? Number(r.cac) : null,
      cpl: Number(r.cpl || 0),
      qrate: Number(r.qrate || 0),
      crate: Number(r.crate || 0),
      revenue: Number(r.revenue || 0),
      cm: Number(r.cm || 0),
      score: Number(r.score || 0),
      trend: 0,
      status: r.decision || "watch",
      thumb: r.thumbnail_url || "assets/products/qcells.png",
      _meta_ad_id: r.ad_id,
    }));
  }

  const PRODUCT_THUMB = {
    solar: "assets/products/qcells.png",
    storage: "assets/products/anker.png",
    roofing: "assets/products/roofing.png",
    water: "assets/products/water.png",
    other: "assets/products/qcells.png",
  };
  const PRODUCT_NAME = { solar: "Solar", storage: "Storage", roofing: "Roofing", water: "Water", other: "Otros" };

  function buildProducts(rows) {
    return (rows || []).map((r) => ({
      id: r.product || "other",
      name: PRODUCT_NAME[r.product] || (r.product ? String(r.product) : "Otros"),
      thumb: PRODUCT_THUMB[r.product] || PRODUCT_THUMB.other,
      margin: Number(r.margin_pct || 0),
      revenue: Number(r.revenue || 0),
      spend: Number(r.spend || 0),
      leads: Number(r.leads || 0),
      sales: Number(r.sales || 0),
      cac: Number(r.cac || 0),
      cm: Number(r.cm || 0),
      share: Number(r.share_pct || 0),
    }));
  }

  function buildFunnel(rows, bySourceRows) {
    const map = {};
    (rows || []).forEach((r) => { map[r.step] = { count: Number(r.count || 0), rate: Number(r.rate_pct || 0) }; });

    const stepDef = [
      { key: "leads",      mockKey: "leads",       metricKey: "leads",       label_es: "Leads",             label_en: "Leads" },
      { key: "trabajados", mockKey: "qualif",      metricKey: "trabajados",  label_es: "Trabajados",        label_en: "Worked" },
      { key: "contactados",mockKey: "contact",     metricKey: "contactados", label_es: "Contactados",       label_en: "Contacted" },
      { key: "citas",      mockKey: "appt",        metricKey: "citas",       label_es: "Citas coordinadas", label_en: "Appts booked" },
      { key: "ejecutadas", mockKey: "show",        metricKey: "ejecutadas",  label_es: "Citas ejecutadas",  label_en: "Appts executed" },
      { key: "sales",      mockKey: "sale",        metricKey: "sales",       label_es: "Ventas",            label_en: "Sales" },
    ];
    const steps = stepDef.map((s) => ({
      key: s.mockKey,
      metricKey: s.metricKey,
      label_es: s.label_es,
      label_en: s.label_en,
      count: map[s.key]?.count || 0,
      rate:  map[s.key]?.rate  || 0,
      prev:  0,
    }));

    return {
      steps,
      speedToContact: [],  // populated by Zoho CRM lead_events ingest (future)
      qualityHist: [],     // populated by Zoho CRM lead_score ingest (future)
      bySource: (bySourceRows || []).map((s) => {
        const leads = Number(s.leads || 0);
        const qrate = Number(s.qrate_pct || 0);
        const crate = Number(s.crate_pct || 0);
        return {
          src: s.source || s.source_platform || "Desconocido",
          leads,
          qual: Math.round(leads * qrate / 100),
          sales: Math.round(leads * crate / 100),
          qrate,
          crate,
        };
      }),
    };
  }

  function buildHeatmap(rows) {
    // Heatmap cells = SPEND (real Meta data) since CM per-cell needs attribution we don't have.
    // Negative = high CPL, positive = low CPL. Display as raw spend so cells with more invested look bigger.
    const grid = {};
    ALL_TYPES.forEach((t) => {
      grid[t] = {};
      ALL_ANGLES.forEach((a) => { grid[t][a] = 0; });
    });
    (rows || []).forEach((r) => {
      const t = TYPE_LABEL[r.creative_type];
      const a = ANGLE_LABEL[r.creative_angle];
      if (t && a) {
        // Use SPEND as the cell value (real Meta data).
        // Invert CPL into a "score" if you want: lower CPL = higher score.
        grid[t][a] = Number(r.spend || 0);
      }
    });
    return grid;
  }

  const ACTION_LABEL_ES = {
    budget_increase: "Subir budget",
    budget_decrease: "Bajar budget",
    pause: "Pausar",
    resume: "Reactivar",
    duplicate: "Duplicar",
    archive: "Archivar",
    rename: "Renombrar",
    creative_swap: "Cambiar creativo",
    note: "Anotar",
  };
  const ACTION_LABEL_EN = {
    budget_increase: "Scale budget",
    budget_decrease: "Reduce budget",
    pause: "Pause",
    resume: "Resume",
    duplicate: "Duplicate",
    archive: "Archive",
    rename: "Rename",
    creative_swap: "Swap creative",
    note: "Note",
  };

  function buildAlerts(rows) {
    function relTime(ts) {
      if (!ts) return "";
      const diff = (Date.now() - new Date(ts).getTime()) / 1000;
      if (diff < 60) return "ahora";
      if (diff < 3600) return Math.floor(diff/60) + " min";
      if (diff < 86400) return Math.floor(diff/3600) + " h";
      return Math.floor(diff/86400) + " d";
    }
    return (rows || []).map((r) => ({
      id: r.id,
      sev: r.severity,
      icon: r.severity === "critical" ? "alert-triangle" :
            r.severity === "high" ? "alert-circle" :
            r.severity === "good" ? "trending-up" : "info",
      title_es: r.title_es,
      title_en: r.title_en || r.title_es,
      msg_es: r.message_es,
      msg_en: r.message_en || r.message_es,
      impact_es: r.impact_es || "",
      impact_en: r.impact_en || r.impact_es || "",
      action_es: ACTION_LABEL_ES[r.suggested_action_kind] || "",
      action_en: ACTION_LABEL_EN[r.suggested_action_kind] || "",
      action_kind: r.suggested_action_kind || null,
      ad_id: r.ad_id || null,
      campaign_id: r.campaign_id || null,
      time: relTime(r.triggered_at),
      related: null,
      resolved: r.status === "resolved",
    }));
  }

  // Map competitor category from the central message or stored category field
  const CATEGORY_GRAD = {
    Savings:      "linear-gradient(135deg,#FF7A00,#A33D00)",
    Fear:         "linear-gradient(135deg,#7A0000,#3D0000)",
    Independence: "linear-gradient(135deg,#163C1A,#082010)",
    Financing:    "linear-gradient(135deg,#0079C0,#192059)",
  };
  function inferCategory(msg) {
    const s = (msg || "").toLowerCase();
    if (s.includes("ahorr") || s.includes("saving") || s.includes("$")) return "Savings";
    if (s.includes("apag") || s.includes("outage") || s.includes("miedo")) return "Fear";
    if (s.includes("financ") || s.includes("crédito") || s.includes("pago")) return "Financing";
    return "Independence";
  }

  function buildCompetitors(rows) {
    return (rows || []).filter(r => !r.is_windmar).map((r) => {
      const meta = r.metadata || {};
      const category = meta.category || inferCategory(meta.central_message);
      const color = r.color || null;
      // Use real color from scrape if present, else fall back to category gradient
      const bg = color
        ? `linear-gradient(135deg, ${color}, ${color}88)`
        : (CATEGORY_GRAD[category] || CATEGORY_GRAD.Independence);
      return {
        id: r.slug,
        brand: r.brand,
        emoji: r.emoji || "",
        color,
        headline: meta.central_message || r.brand,
        cta: "Ver en Meta",
        meta_ads_library_url: r.meta_ads_library_url || null,
        facebook_page_id: r.facebook_page_id || null,
        category,
        bg,
        spend: Number(meta.google_estimated_monthly || 0),
        freq: Number(meta.ads_meta || 0) / 30,
        change: Number(meta.new_this_week || 0),
        market: r.market,
        ads_meta: meta.ads_meta || 0,
        new_this_week: meta.new_this_week || 0,
        central_message: meta.central_message || "",
        threat: meta.threat || "medium",
        patterns: meta.patterns || [],
        formats: meta.formats || [],
      };
    });
  }

  // ---------- Main fetch ----------
  async function rpc(name, args) {
    const { data, error } = await sb.rpc(name, args);
    if (error) {
      console.error(`[data-supabase] rpc ${name} failed:`, error);
      throw error;
    }
    return data;
  }

  async function fetchAll(rangePreset, dashboardKey, accountFilter, objectiveFilter) {
    const range = resolveRange(rangePreset || "30d");
    window.__currentRange = range;
    // Build scope args
    const dash = window.DASHBOARDS[dashboardKey] || null;
    let account_ext_ids = null;
    let campaign_regex = null;
    if (dash) {
      if (accountFilter && accountFilter !== "all") {
        account_ext_ids = [accountFilter];
        campaign_regex = null;
      } else {
        account_ext_ids = dash.accounts.map(a => a.ext);
        campaign_regex = dash.regex;
      }
    }
    // Default to lead-gen only (excludes awareness/brand campaigns from CAC/CM)
    const objective_filter = objectiveFilter || "leadgen";
    window.__currentScope = { dashboardKey, accountFilter, objectiveFilter: objective_filter, account_ext_ids, campaign_regex };

    // Base args (most RPCs)
    const args = {
      date_from: range.from, date_to: range.to,
      account_ext_ids, campaign_regex,
    };
    // Extended args (only RPCs that accept objective filtering)
    const argsObj = { ...args, objective_filter };

    const results = await Promise.allSettled([
      rpc("f_profit_totals", argsObj),
      rpc("f_overview_deltas", args),
      rpc("f_profit_series", args),
      rpc("f_campaign_rollup", argsObj),
      rpc("f_creative_list", argsObj),
      rpc("f_product_rollup", argsObj),
      rpc("f_funnel", args),
      rpc("f_funnel_by_source", args),
      rpc("f_creative_heatmap", args),
      rpc("f_lead_status_distribution", { ...args, digital_only: true }),
      rpc("f_lead_cohorts", { months_back: 6 }),
      rpc("f_digital_pipeline", {}),
      rpc("f_digital_pipeline_summary", {}),
      rpc("f_leads_by_campaign", args),
      rpc("f_leads_no_utms", args),
      rpc("f_unknown_sales", { date_from: range.from, date_to: range.to }),
      rpc("f_digital_sales", args),
      sb.from("alerts").select("*").eq("status", "open"),
      sb.from("competitors").select("*").eq("is_active", true),
    ]);

    const [totals, deltas, series, camps, creatives, prods, funnel, funnelBy, heat, statusDist, cohorts, pipeline, pipelineSummary, leadsByCampaign, leadsNoUtms, unknownSales, digitalSales, alertsRes, compsRes] =
      results.map((r) => r.status === "fulfilled" ? r.value : null);

    window.unknownSales = (unknownSales || []).map(u => ({
      zoho_id: u.zoho_id, first_name: u.first_name, last_name: u.last_name,
      email: u.email, phone: u.phone,
      created_at_zoho: u.created_at_zoho, modified_at_zoho: u.modified_at_zoho,
      matched_via: u.matched_via, matched_zoho_id: u.matched_zoho_id,
      matched_source: u.matched_source, matched_utm_campaign: u.matched_utm_campaign,
      matched_campaign_name: u.matched_campaign_name, matched_ad_name: u.matched_ad_name,
    }));
    window.digitalSalesList = (digitalSales || []).map(d => ({
      zoho_id: d.zoho_id, first_name: d.first_name, last_name: d.last_name,
      email: d.email, phone: d.phone, source: d.source,
      utm_source: d.utm_source, utm_campaign: d.utm_campaign, utm_medium: d.utm_medium,
      is_strict_digital: d.is_strict_digital,
      created_at_zoho: d.created_at_zoho, modified_at_zoho: d.modified_at_zoho,
      matched_campaign_name: d.matched_campaign_name,
    }));

    window.leadsByCampaign = (leadsByCampaign || []).map(c => ({
      utm_campaign: c.utm_campaign,
      campaign_name: c.campaign_name,
      campaign_id: c.campaign_id,
      total: Number(c.total_leads || 0),
      new: Number(c.new_count || 0),
      working: Number(c.working_count || 0),
      contacted: Number(c.contacted_count || 0),
      qualified: Number(c.qualified_count || 0),
      appt_set: Number(c.appt_set_count || 0),
      appt_held: Number(c.appt_held_count || 0),
      won: Number(c.won_count || 0),
      disqualified: Number(c.disqualified_count || 0),
    }));
    window.leadsNoUtms = (leadsNoUtms || []).map(l => ({
      zoho_id: l.zoho_id,
      first_name: l.first_name, last_name: l.last_name, email: l.email, phone: l.phone,
      source: l.source, status: l.status, created_at_zoho: l.created_at_zoho,
      missing: {
        utm_source: l.missing_utm_source,
        utm_medium: l.missing_utm_medium,
        utm_campaign: l.missing_utm_campaign,
        utm_content: l.missing_utm_content,
        utm_term: l.missing_utm_term,
      },
      invalid: {
        utm_source: l.utm_source_invalid,
        utm_medium: l.utm_medium_invalid,
      },
    }));

    window.pipelineData = {
      deals: (pipeline || []).map(d => ({
        id: d.deal_id, name: d.name, amount: Number(d.amount || 0),
        stage: d.stage, closed_won: d.closed_won, closing_date: d.closing_date, age_days: Number(d.age_days || 0),
      })),
      summary: pipelineSummary?.[0] || { open_deals: 0, open_amount: 0, closed_won_count: 0, closed_won_amount: 0, in_30d_won: 0, in_30d_won_amount: 0 },
    };

    window.cohortData = (cohorts || []).map(c => ({
      cohort_month: c.cohort_month,
      leads: Number(c.leads_created || 0),
      spend: Number(c.spend || 0),
      sales: Number(c.sales_so_far || 0),
      conv_pct: Number(c.sales_conv_pct || 0),
      cpl: c.cpl !== null ? Number(c.cpl) : null,
      cost_per_sale: c.cost_per_sale !== null ? Number(c.cost_per_sale) : null,
      days_since: Number(c.days_since_start || 0),
    }));

    const failed = results.map((r, i) => r.status === "rejected" ? i : -1).filter(i => i >= 0);
    if (failed.length) console.warn("[data-supabase] some endpoints failed:", failed, results);

    // Build & assign — only overwrite globals where we got data; otherwise leave mock
    if (totals !== null)      window.profitTotals = buildProfitTotals((totals || [])[0], deltas);
    if (series !== null)      window.profitSeries = buildProfitSeries(series);
    if (camps !== null)       window.campaigns    = buildCampaigns(camps);
    if (creatives !== null)   window.creatives    = buildCreatives(creatives);
    if (prods !== null)       window.products     = buildProducts(prods);
    if (funnel !== null) {
      window.funnelData = buildFunnel(funnel, funnelBy);
      // attach status distribution to the same global for the Funnel view
      window.funnelData.statusDistribution = (statusDist || []).map(s => ({
        status_raw: s.status_raw || "(sin status)",
        status_mapped: s.status_mapped,
        count: Number(s.count || 0),
        pct: Number(s.pct || 0),
      }));
    }
    if (heat !== null)        window.heatmapData  = buildHeatmap(heat);
    if (alertsRes && Array.isArray(alertsRes.data)) window.alerts = buildAlerts(alertsRes.data);
    if (compsRes && Array.isArray(compsRes.data) && compsRes.data.length) {
      window.competitors = buildCompetitors(compsRes.data);
    }

    // ---------- Google Ads (isolated google_* tables) ----------
    // Campaign daily is filtered by the global date window; ads/keywords/
    // search terms are stored per ingest preset (rolling ~30d) and shown as-is.
    try {
      const [gDaily, gAds, gKw, gSt, gRec] = await Promise.all([
        sb.from("google_campaign_daily").select("*").gte("day", range.from).lte("day", range.to),
        sb.from("google_ads_perf").select("*").order("cost", { ascending: false }).limit(500),
        sb.from("google_keywords").select("*").order("cost", { ascending: false }).limit(500),
        sb.from("google_search_terms").select("*").order("cost", { ascending: false }).limit(500),
        sb.from("google_recommendations").select("*").eq("status", "open"),
      ]);
      window.googleRaw = {
        daily: gDaily.data || [],
        ads: gAds.data || [],
        keywords: gKw.data || [],
        searchTerms: gSt.data || [],
        recs: gRec.data || [],
        range,
      };
    } catch (e) {
      console.warn("[data-supabase] google fetch failed:", e);
      if (!window.googleRaw) window.googleRaw = { daily: [], ads: [], keywords: [], searchTerms: [], recs: [], range };
    }

    window.__lastFetchAt = new Date();
    console.log("[data-supabase] loaded for range", range, {
      totals: window.profitTotals?.spend?.val,
      campaigns: window.campaigns?.length,
      creatives: window.creatives?.length,
      products: window.products?.length,
      alerts: window.alerts?.length,
      competitors: window.competitors?.length,
    });
    return range;
  }

  window.fetchFromSupabase = fetchAll;

  // Lead Association: leads WON sin source que matchean a un lead digital (robados/escondidos).
  // Atribución lead-first (source_platform / detailed_lead_source) desde Performance Marketing.
  window.fetchStolenLeads = async (from, to) => {
    try { return await rpc("f_stolen_leads", { date_from: from, date_to: to }); }
    catch (e) { console.warn("[data-supabase] f_stolen_leads:", e.message); return null; }
  };
  window.fetchStolenSummary = async (from, to) => {
    try { return await rpc("f_stolen_leads_summary", { date_from: from, date_to: to }); }
    catch (e) { return null; }
  };
  // Marca una atribución como resuelta (asociada en Zoho) → se loguea y NUNCA se vuelve a sugerir.
  window.resolveAttribution = async (zoho_id) => {
    try { return await rpc("f_resolve_attribution", { p_zoho_id: zoho_id, p_by: "dashboard" }); }
    catch (e) { console.warn("[data-supabase] f_resolve_attribution:", e.message); return null; }
  };

  // ---------- Realtime: re-fetch on important table changes ----------
  // IMPORTANT: must preserve the active scope (dashboard + account filter)
  // when auto-refreshing, otherwise filters appear to "reset to all".
  let realtimeChannel = null;
  let debounceTimer = null;
  let realtimeDebounceMs = 5000;  // 5s debounce to coalesce bursts

  function startRealtime(getCurrentScope) {
    if (realtimeChannel) return;
    realtimeChannel = sb.channel("dashboard-changes")
      .on("postgres_changes", { event: "*", schema: "public", table: "alerts" },        () => scheduleRefetch(getCurrentScope))
      .on("postgres_changes", { event: "*", schema: "public", table: "daily_metrics" }, () => scheduleRefetch(getCurrentScope))
      .on("postgres_changes", { event: "*", schema: "public", table: "sync_runs" },     () => scheduleRefetch(getCurrentScope))
      .on("postgres_changes", { event: "*", schema: "public", table: "google_campaign_daily" },  () => scheduleRefetch(getCurrentScope))
      .on("postgres_changes", { event: "*", schema: "public", table: "google_recommendations" }, () => scheduleRefetch(getCurrentScope))
      .subscribe((status) => {
        console.log("[data-supabase] realtime status:", status);
        window.__realtimeStatus = status;
      });
  }

  function scheduleRefetch(getCurrentScope) {
    clearTimeout(debounceTimer);
    debounceTimer = setTimeout(async () => {
      const scope = typeof getCurrentScope === "function" ? getCurrentScope() : { dateRange: "30d", dashboardKey: "general", accountFilter: "all" };
      try {
        // Preserve dashboard + accountFilter + objective on auto-refresh
        await fetchAll(scope.dateRange, scope.dashboardKey, scope.accountFilter, scope.objectiveFilter);
        window.dispatchEvent(new CustomEvent("supabase-data-updated", { detail: { reason: "realtime" } }));
      } catch (e) {
        console.warn("[data-supabase] realtime refetch failed:", e);
      }
    }, realtimeDebounceMs);
  }

  window.startSupabaseRealtime = startRealtime;
})();