import { lang as Lang } from "/helpers/lang.js";
import { atlasHero, downloadButton } from "/helpers/uiComponents.ojs";
import { patchWindowsCache, cleanAdminInput_SQL } from "/helpers/std.ojs";
import { atlasTOC } from "/helpers/toc.ojs";
import { renderGeoMap } from "/helpers/figures.ojs";
import { filterableDataTable } from "/components/atlasTable.ojs";

// Data imports
general_translations = await FileAttachment(
  "/data/shared/generalTranslations.json",
).json();

vulnerability_translations = await FileAttachment(
  "/data/vulnerability_notebook/translations.json",
).json();

import {
  admin0Select,
  admin1Select,
  admin2Select,
  admin012Form,
  admin01Form,
} from "/components/_adminSelectors.ojs";

getAdminNameString = () => {
  return [
    _lang(admin0Select.translation),
    admin1Select?.admin1_name,
    admin2Select?.admin2_name,
  ]
    .filter(Boolean)
    .join(", ");
};

removals = { 
  // Not all data avalaible for all countries (i.e. exposure is ssa only, so filter to these countries)
  const country_list = await FileAttachment("/data/shared/atlas_countries.json").json();
  const excluded = country_list
    .filter(({ include }) => !include)
    .map(({ admin0_name }) => admin0_name);
  
  return excluded;
};

sqlAdminQuery = () => {
  const admin0 = cleanAdminInput_SQL(admin0Select?.admin0_name);
  const admin1 = cleanAdminInput_SQL(admin1Select?.admin1_name);
  const admin2 = cleanAdminInput_SQL(admin2Select?.admin2_name);

  const conditions = [
    admin0
      ? `admin0_name = '${admin0}' AND admin1_name IS NOT NULL`
      : `admin1_name IS NULL`,

    admin1 ? `admin1_name = '${admin1}'` : `admin2_name IS NULL`,

    admin1 && `admin2_name IS NOT NULL`,

    `admin0_name NOT IN ('${removals.join("', '")}')`,
  ];

  return conditions.filter(Boolean).join(" AND ");
};

sqlAdminQuerySpecific = () => {
  const admin0 = cleanAdminInput_SQL(admin0Select?.admin0_name);
  const admin1 = cleanAdminInput_SQL(admin1Select?.admin1_name);
  const admin2 = cleanAdminInput_SQL(admin2Select?.admin2_name);

  const conditions = [
    admin0 ? `admin0_name = '${admin0}'` : `admin0_name = 'SSA'`,
    admin1 ? `admin1_name = '${admin1}'` : `admin1_name IS NULL`,
    admin2 ? `admin2_name = '${admin2}'` : `admin2_name IS NULL`,
  ];

  return conditions.join(" AND ");
};

hero_url = "./../../images/default_crop.webp";

atlasHero(nbTitle, hero_url);

atlasTOC({
  skip: ["notebook-title", "appendix", "source-code"],
  heading: `<b>${_lang(general_translations.toc)}</b>`,
});

compositeQuestion = _lang(vulnerability_translations.composite_question);

createGeographicControlsForm();

viewof compositeView = Inputs.radio(["map", "table"], {
  label: "View Type",
  value: "map",
});

{
  if (compositeView === "map") {
    return generateCompositeMap();
  } else {
    return filterableDataTable(longIndexData());
  }
}

// Download button for composite index data
viewof compositeDownloadButton = {
  const region = getAdminNameString().replace(", ", "_");
  
  return downloadButton(
     longIndexData(), 
    `composite_vulnerability_data_${region}`,
    _lang({en: "Download Composite Index Data", fr: "Télécharger les Données de l'Indice Composite"})
  );
}

sensitivityMetadata = await FileAttachment(
  "/data/vulnerability_notebook/urgency_metadata.json",
).json();

function computeIndex(row) {
  const normValues = Object.entries(row)
    .filter(
      ([key, value]) =>
        key.endsWith("_norm") && typeof value === "number" && !isNaN(value),
    )
    .map(([, value]) => value);

  if (normValues.length === 0) return null;

  const sum = normValues.reduce((a, b) => a + b, 0);
  return sum / normValues.length;
}

/**
 * Generate SQL WHERE clause for admin level filtering
 * Returns clause to filter by admin0 (country level) or admin1 (region level) based on selection
 *
 * @param {string|null} country_sel - Selected country code (or "SSA" for all countries)
 * @param {string|null} admin1_sel - Selected admin1 region (currently unused but kept for consistency)
 * @returns {string} SQL WHERE clause fragment
 */
whereAdminNull = (country_sel, admin1_sel, admin2_sel) => {
  // If all countries selected (SSA) or no selection, return country-level data (admin1_name IS NULL)
  // Otherwise, return region-level data (admin1_name IS NOT NULL)
  if (country_sel == "SSA" || !country_sel) {
    return `admin1_name IS NULL`;
  } else if (country_sel && !admin1_sel) {
    return `admin1_name IS NOT NULL AND admin2_name IS NULL`;
  } else {
    return `admin2_name IS NOT NULL`;
  }
};

// Calculate exposure index
exposure_index = {
  try {
    return await localDb.query(`
      WITH raw_exposure AS (
          SELECT 
            admin0_name,
            admin1_name,
            admin2_name,
            SUM(value) AS total_exposure
          FROM exposure
          WHERE isfinite(value)
            AND ${whereAdminNull(
              cleanAdminInput_SQL(admin0Select?.admin0_name),
              cleanAdminInput_SQL(admin1Select?.admin1_name),
              cleanAdminInput_SQL(admin2Select?.admin2_name)
            )}
            AND admin0_name NOT IN ('${removals.join("', '")}')
          GROUP BY admin0_name, admin1_name, admin2_name
        ),
        stats AS (
          SELECT
            MIN(total_exposure) AS exposure_min,
            MAX(total_exposure) AS exposure_max
          FROM raw_exposure
          WHERE ISFINITE(total_exposure)
        )
        SELECT 
          r.admin0_name,
          r.admin1_name,
          r.admin2_name,
          r.total_exposure,
          -- Min-max normalize exposure
          (r.total_exposure - s.exposure_min) / NULLIF(s.exposure_max - s.exposure_min, 0) AS exposure_norm
        FROM raw_exposure r
        CROSS JOIN stats s
        WHERE ISFINITE(r.total_exposure)
        ORDER BY r.total_exposure DESC;
    `);
  } catch (error) {
    console.error("Failed to calculate exposure index:", error);
    return [];
  }
}

// Calculate enabling index (adaptive capacity)
enabling_index = {
  try {
    let resp = await localDb.query(`
SELECT *
FROM enabling
WHERE admin0_name != 'SSA'
  AND ${sqlAdminQuery()}
`);

    return resp.map((row) => ({
      ...row,
      enabling_index: computeIndex(row)
    }));
  } catch (error) {
    console.error("Failed to calculate enabling index:", error);
    return [];
  }
}

// Calculate urgency index
// Index is based on exposure and sensitivity
urgency_index = {
  try {
    let gender = "total"; //NOTE: Keeping this as an option in case future updates want gender specific index
    // sensitivity data
    let sensitivityData = await localDb.query(`
      SELECT *
      FROM urgency
      WHERE admin0_name != 'SSA'
        AND gender = '${gender}'
        AND ${sqlAdminQuery()}
    `);

    // Check if exposure_index exists and is an array before using
    const exposureMap = (exposure_index && Array.isArray(exposure_index))
      ? Object.fromEntries(
          exposure_index.map((d) => {
            const key = `${d.admin0_name}|${d.admin1_name}|${d.admin2_name}`;
            return [key, d.exposure_norm];
          })
        )
      : {};

    // merge into urgencyData
    const merged = sensitivityData.map((d) => {
      const key = `${d.admin0_name}|${d.admin1_name}|${d.admin2_name}`

      return {
        ...d,
        exposure_norm: exposureMap[key] ?? null
      };
    });

    return merged.map(row => {
      const urgency_index = computeIndex(row);

      const drivers = [
        ["exposure", row.exposure_norm],
        ["population_density", row.population_dens_norm],
        ["education", row.education_norm],
        ["poverty", row.poverty_norm]
      ];

      const urgency_driver = drivers.reduce((max, current) =>
        current[1] > max[1] ? current : max
      )[0];

      return {
        ...row,
        urgency_index,
        urgency_driver
      };
    });
  } catch (error) {
    console.error("Failed to calculate urgency index:", error);
    return [];
  }
}

// Calculate vulnerability index (AKA composite index with ND-gain Methodology)
vulnerabilityIndex = {
  const key = (r) => `${r.admin0_name}|${r.admin1_name}|${r.admin2_name}`;
  const parse = (v) => v === "null" ? null : v;
  const toMap = (arr, k, v) => Object.fromEntries(arr.map((r) => [k(r), v(r)]));

  const uMap = toMap(urgency_index, key, (r) => r.urgency_index);
  const enMap = toMap(enabling_index, key, (r) => r.enabling_index);

  const allKeys = new Set([...Object.keys(enMap), ...Object.keys(uMap)]);

  const merged = [...allKeys].map((k) => {
    const [admin0_name, admin1_name, admin2_name] = k.split("|");
    const en = enMap[k],
      u = uMap[k];
    return {
      admin0_name,
      admin1_name: parse(admin1_name),
      admin2_name: parse(admin2_name),
      enabling_index: en ?? null,
      urgency_index: u ?? null,
      gap: en - u,
      vulnerability:
        typeof en === "number" && typeof u === "number"
          ? 100 - (en - u + 1) * 50
          : null
    };
  });

  const metrics = [
    ["vulnerability", "vuln_rank"],
    ["urgency_index", "urgency_rank"],
    ["enabling_index", "enabling_rank"]
  ];

  metrics.forEach(([key, rankField]) => {
    [...merged]
      .sort((a, b) => b[key] - a[key])
      .forEach((d, i) => d[rankField] = i + 1);
  });

  return merged.sort((a, b) => b.vulnerability - a.vulnerability);
}

indexByAdmin = (data) => Object.fromEntries(data.map((d) => [adminKey(d), d]));

adminFilter = (row) => {
  const admin0 = admin0Select?.admin0_name;
  const admin1 = admin1Select?.admin1_name;

  return (
    // admin0 condition
    (admin0 ? row.admin0_name === admin0 : row.admin1_name == null) &&
    // admin1 condition
    (admin1 ? row.admin1_name === admin1 : row.admin2_name == null) &&
    // admin2 existence condition
    (!admin1 || row.admin2_name != null)
  );
};

compositeGeoData = {
  const vulnerabilityByAdmin = indexByAdmin(vulnerabilityIndex);
  const filteredExposure = exposure_index.filter(adminFilter);

  const compiled = filteredExposure.map((row) => {
    const key = adminKey(row);
    const vuln = vulnerabilityByAdmin[key]?.vulnerability ?? null;

    return {
      admin0_name: row.admin0_name,
      admin1_name: row.admin1_name,
      admin2_name: row.admin2_name,

      exposure_index: row.exposure_norm ?? null,
      enabling_index: vulnerabilityByAdmin[key]?.enabling_index ?? null,
      urgency_index: vulnerabilityByAdmin[key]?.urgency_index ?? null,
      vulnerability_index: vuln,
      composite_index: vuln != null ? vuln / 100 : null
    };
  });

  return mergeDataToBoundaries({
    boundaries: filteredBoundaries(),
    data: compiled,
    boundaryKey: (feature) => adminKey(feature.properties),
    dataKey: adminKey,
    dataProp: "data",
    defaultValue: null,
  });
}

generateCompositeMap = () => {
  // Layout
  const totalWidth = typeof width === "number" ? Math.min(1200, width) : 1000;
  const totalHeight = 600;
  const gap = 12;
  const compositeRatio = 2.2;
  const componentRatio = 1;

  const compositeWidth = Math.floor(
    totalWidth * (compositeRatio / (compositeRatio + componentRatio)),
  );
  const componentWidth = Math.floor(
    totalWidth * (componentRatio / (compositeRatio + componentRatio)),
  );
  const componentHeight = Math.floor((totalHeight - gap * 2) / 3);
  const compositeHeight = componentHeight * 3 + gap * 2 + 80;

  const components = [
    {
      key: "composite_index",
      label: _lang({ en: "Vulnerability", fr: "Vulnérabilité" }),
      invert: false,
      featured: true,
    },
    {
      key: "exposure_index",
      label: _lang({ en: "Exposure", fr: "Exposition" }),
    },
    { key: "urgency_index", label: _lang({ en: "Urgency", fr: "Urgence" }) },
    {
      key: "enabling_index",
      label: _lang({ en: "Adaptive Capacity", fr: "Capacité d’Adaptation" }),
      invert: true,
    },
  ];

  const renderComponentMap = (component, width, height) =>
    renderGeoMap({
      features: compositeGeoData.features,
      width,
      height,
      valueAccessor: (d) => d.properties.data?.[component.key],
      color: {
        type: "linear",
        range: component.invert
          ? ["#216729", "#F7D732"]
          : ["#F4BB21", "#EC5A47"],
        legend: true,
        label: component.label,
      },
      tooltip: {
        channels: {
          country: {
            label: _lang({ en: "Country", fr: "Pays" }),
            value: (d) => d.properties.admin0_name,
          },
          region: {
            label: _lang({ en: "Region", fr: "Région" }),
            value: (d) =>
              d.properties.admin2_name
                ? `${d.properties.admin2_name}, ${d.properties.admin1_name}`
                : d.properties.admin1_name,
          },
          score: {
            label: _lang({ en: "Score", fr: "Score" }),
            value: (d) => {
              const v = d.properties.data?.[component.key];
              return v != null
                ? v.toFixed(3)
                : _lang({ en: "No data", fr: "Pas de données" });
            },
          },
        },
      },
      extraMarks: [
        Plot.geo(
          admin2Select?.admin2_name
            ? compositeGeoData.features.filter(
                (d) => d.properties.admin2_name === admin2Select?.admin2_name,
              )
            : [],
          {
            fill: null,
            stroke: "#333",
            strokeWidth: 1.5,
          },
        ),
      ],
    });

  const featured = components.find((c) => c.featured);
  const secondary = components.filter((c) => !c.featured);

  const featuredMap = renderComponentMap(
    featured,
    compositeWidth,
    compositeHeight,
  );
  const secondaryMaps = secondary.map((c) =>
    renderComponentMap(c, componentWidth, componentHeight),
  );

  return htl.html`
    <div style="display:flex; flex-direction:column; gap:20px; width:100%;">
      <div style="
        display:grid;
        grid-template-columns:${compositeRatio}fr ${componentRatio}fr;
        gap:${gap}px;
      ">
        <div>${featuredMap}</div>
        <div style="display:flex; flex-direction:column; gap:${gap}px;">
          ${secondaryMaps}
        </div>
      </div>
    </div>
  `;
};

// Dynamic Insights following wireframe template
compositeVulnerabilityInsights = {
  const numHotspots = 6;
  const templateGroup = vulnerability_translations.composite_insight.most_vulnerable
  const parent = getAdminNameString()
  const intro = Lang.reduceReplaceTemplateItems(_lang(templateGroup.intro), [{ name: "parent", value: parent }])

  const topHotspots = [...vulnerabilityIndex].slice(0, numHotspots);
  const numRegions = vulnerabilityIndex.length

  const hotspotInsights = topHotspots.map(d => {
    const gapDirection = d.gap < 0 ? "below" : "above";

    const adminName = d.admin2_name ?? d.admin1_name ?? d.admin0_name

    let structural_balance;
    if (d.gap < 0) structural_balance = "structural deficit";
    else if (d.gap < 0.15) structural_balance = "thin resilience buffer";
    else if (d.gap < 0.35) structural_balance = "moderate resilience buffer";
    else structural_balance = "strong resilience buffer";

    const insight = Lang.reduceReplaceTemplateItems(
      _lang(templateGroup.vulnerable_region),
      [
        { name: "admin", value: adminName },
        { name: "vuln_rank", value: d.vuln_rank },
        { name: "urgency_rank", value: d.urgency_rank },
        { name: "enabling_rank", value: d.enabling_rank },
        { name: "num_regions", value: numRegions },
        { name: "gapDirection", value: gapDirection },
        { name: "structural_balance", value: structural_balance }
      ]
    );
    return `- ${insight}`
  });

  return createInsightDisplay(md`${intro} \n ${hotspotInsights.join("\n\n")}`);
};

longIndexData = () => {
  const GROUPS = {
    exposure: {
      source: exposure_index,
      variables: ["total_exposure"],
    },

    urgency: {
      source: urgency_index,
      variables: ["population_dens", "education", "poverty"],
    },

    adaptive_capacity: {
      source: enabling_index,
      variables: [
        "pct_electric",
        "pct_piped_water",
        "min_to_cities",
        "conflict_density",
        "pct_cellphone",
      ],
    },

    vulnerability: {
      source: vulnerabilityIndex,
      variables: ["vulnerability", "enabling_index", "urgency_index"],
      derived: (row) => ({
        vulnerability_norm:
          row.vulnerability != null ? row.vulnerability / 100 : null,
      }),
    },
  };

  const filteredExposure = exposure_index.filter(adminFilter);

  // use exposure as the spine for which admins to include
  const admins = filteredExposure.map((d) => ({
    admin0_name: d.admin0_name,
    admin1_name: d.admin1_name,
    admin2_name: d.admin2_name,
  }));

  // index all sources by admin
  const indexedSources = Object.fromEntries(
    Object.entries(GROUPS).map(([group, cfg]) => [
      group,
      indexByAdmin(cfg.source),
    ]),
  );

  const longData = admins.flatMap((admin) => {
    const key = adminKey(admin);

    return Object.entries(GROUPS).flatMap(([group, cfg]) => {
      const row = indexedSources[group][key] ?? {};

      // base variables
      const baseRows = cfg.variables.map((variable) => ({
        ...admin,
        group,
        variable,
        value: row[variable] ?? null,
      }));

      // derived variables (only vulnerability_norm for now)
      const derivedRows = cfg.derived
        ? Object.entries(cfg.derived(row)).map(([variable, value]) => ({
            ...admin,
            group,
            variable,
            value,
          }))
        : [];

      return [...baseRows, ...derivedRows];
    });
  });

  return longData;
};

exposureQuestion = _lang(vulnerability_translations.exposure_question);

exposureControlsForm = createGeographicControlsForm();

viewof exposureView = Inputs.radio(["map", "table"], {
  label: "View Type",
  value: "map",
});

{
  if (exposureView === "map") {
    return exposureChoroplethMap();
  } else {
    return filterableDataTable(exposureData, {
      columns: [
        "admin0_name", "admin1_name", "admin2_name", "value", "hazard_vars", "crop"
      ],
      header: {hazard_vars: "Hazard", crop: "Crop", value: "USD Exposed"},
    });
  }
}

exposureInsights();

exposureDownloadButton = {
  // TODO: Integrate data length/existence check into downloadButton directly
  return downloadButton(
    exposureData, 
    `exposure_data_${getAdminNameString().replace(", ", "_")}_all_hazard_types`,
    _lang(vulnerability_translations.download_data)
  );
}

exposureData = localDb.query(`
SELECT *
FROM exposure
WHERE hazard = 'any'
AND ${sqlAdminQuery()}
`);

totalExposure = Object.values(
  exposureData.reduce((acc, row) => {
    const key = adminKey(row);
    acc[key] ??= {
      admin0_name: row.admin0_name,
      admin1_name: row.admin1_name,
      admin2_name: row.admin2_name,
      total_value: 0,
    };
    const val = Number(row.value);
    acc[key].total_value += Number.isFinite(val) ? val : 0;
    return acc;
  }, {}),
);

exposureMapData = mergeDataToBoundaries({
  boundaries: filteredBoundaries(),
  data: totalExposure,
  boundaryKey: (feature) => adminKey(feature.properties),
  dataKey: adminKey,
  dataProp: "data",
  defaultValue: null,
});

// Visualization with proper loading/no-data states
exposureChoroplethMap = () => {
  // Show loading spinner only if data is still loading (null/undefined)
  if (exposureData === null || exposureData === undefined) {
    return createLoadingState(
      _lang({
        en: "Loading exposure data...",
        fr: "Chargement des données d'exposition...",
      }),
    );
  }

  // If data is loaded but empty, or map data is empty, show no data state
  if (
    !exposureData ||
    exposureData.length === 0 ||
    !exposureMapData ||
    !exposureMapData.features ||
    exposureMapData.features.length === 0
  ) {
    return createNoDataState(
      _lang(vulnerability_translations.no_data_available),
    );
  }

  const data = exposureMapData;

  const plot = renderGeoMap({
    features: data.features,
    width: mapWidth,
    height: mapHeight,
    caption: vopNote.caption,
    projection: {
      type: "azimuthal-equal-area",
      domain: data,
    },
    color: {
      legend: true,
      label: `${_lang({ en: "VoP", fr: "VoP" })} (${intDollarUnit})`,
      range: colorScales.range.yellowGreen,
      unknown: colorScales.unknown,
      tickFormat: formatNumCompactShort,
    },
    valueAccessor: (d) => {
      const dataColumn = "total_value";
      const fillValue = d.properties.data
        ? d.properties.data?.[dataColumn]
        : null;
      return fillValue;
    },
    tooltip: {
      channels: {
        country: {
          label: "Country",
          value: (d) => d.properties.admin0_name,
        },
        name: {
          label: "Region",
          value: (d) => {
            if (d.properties.admin2_name) {
              return `${d.properties.admin2_name}, ${d.properties.admin1_name}`;
            } else {
              return d.properties.admin1_name;
            }
          },
        },
        data: {
          label: "VoP",
          value: (d) => {
            const dataColumn = "total_value";
            const data = d.properties.data
              ? d.properties.data[dataColumn]
              : undefined;
            return data;
          },
        },
      },
      format: {
        country: true,
        name: true,
        data: (d) => (d ? formatNumCompactShort(d) : "No data"),
      },
    },
    extraMarks: [
      Plot.geo(
        admin2Select?.admin2_name
          ? data.features.filter(
              (d) => d.properties.admin2_name === admin2Select?.admin2_name,
            )
          : [],
        {
          fill: null,
          stroke: "#333",
          strokeWidth: 1.5,
        },
      ),
    ],
  });

  return plot;
};

// Dynamic insights
exposureInsights = () => {
  if (!totalExposure || totalExposure.length === 0) {
    return createNoDataState();
  }

  // Calculate total exposure value
  const totalValue = d3.sum(totalExposure, (d) => d.total_value || 0);
  const region = getAdminNameString();
  const hazardLabel = "Any";

  // Get top 3 most exposed commodities
  let topCommodities = [];
  if (exposureData && exposureData.length > 0) {
    // Filter data based on current admin selection
    const filteredCommodityData = exposureData;

    // Group by commodity and sum values
    const commodityGroups = d3.group(filteredCommodityData, (d) => d.crop);
    const commodityTotals = Array.from(commodityGroups.entries()).map(
      ([crop, values]) => ({
        crop: crop,
        totalValue: d3.sum(values, (d) => d.value || 0),
      }),
    );

    // Sort by total value and get top 3
    topCommodities = commodityTotals
      .sort((a, b) => b.totalValue - a.totalValue)
      .slice(0, 3)
      .filter((d) => d.totalValue > 0);
  }

  // Format currency without the "2015 USD" prefix
  const formatCleanCurrency = (number) => {
    return new Intl.NumberFormat("en-US", {
      notation: "compact",
      compactDisplay: "short",
      style: "currency",
      currency: "USD",
    }).format(number);
  };

  const template = _lang(vulnerability_translations.exposure_insight_template);
  const replacements = [
    { name: "region", value: region },
    { name: "amount", value: formatCleanCurrency(totalValue) },
  ];

  const insight = Lang.reduceReplaceTemplateItems(template, replacements);

  // Add top commodities insight if available
  let commoditiesInsight = "";
  if (topCommodities.length > 0) {
    // Create dynamic text with specific commodity names
    const commodityNames = topCommodities
      .map((d) => d.crop.replace(/-/g, " "))
      .join(", ");

    const commoditiesText = _lang({
      en: ` The top 3 most exposed commodities are:`,
      fr: ` Les 3 produits les plus exposés sont:`,
    });

    const fullInsight = html`
      ${insight}${commoditiesText}
      <ol>
        ${topCommodities.map((d) => htl.html`<li>${d.crop.replace(/-/g, " ")} (${formatCleanCurrency(d.totalValue)})</li>`)}
      </ol>
    `;
    return createInsightDisplay(fullInsight);
  }

  return createInsightDisplay(insight);
};

sensitivityQuestion = _lang(vulnerability_translations.sensitivity_question);

sensitivityControlsForm = createGeographicControlsForm();
viewof sensitivityView = Inputs.radio(["plot", "table"], {
  label: "View Type",
  value: "plot",
});

{
  if (sensitivityView === "plot") {
    return renderIcicle();
  } else {
    return filterableDataTable(icicle_pct_table);
  }
}

downloadButton(
  icicle_pct_table,
  `sensitivity_data_${getAdminNameString().replace(", ", "_")}`,
  _lang(vulnerability_translations.download_data),
);

renderSensitivityInsight();

// Load icicle metadata and data with caching
icicleKeys = await FileAttachment(
  "/data/vulnerability_notebook/vulnerability_iciclekeys.json",
).json();

csv = {
  const data = await localDb.query(`
    SELECT *
    FROM vulnerability_icicle
    WHERE 1=1
      AND ${sqlAdminQuerySpecific()}
  `);

  return data
};

function buildHierarchy(csv) {
  // Helper function that transforms the given CSV into a hierarchical format.
  // Skip the population level and start directly with demographic categories
  const root = { name: "root", children: [] };
  for (let i = 0; i < csv.length; i++) {
    const sequence = csv[i].path;
    const size = +csv[i].value;
    // Skip invalid rows
    if (!sequence || isNaN(size)) {
      continue;
    }
    const parts = sequence.split("_");
    let currentNode = root;
    // Skip the first part (population) and start from the second part
    for (let j = 1; j < parts.length; j++) {
      const children = currentNode["children"];
      const nodeName = parts[j];
      let childNode = null;
      let foundChild = false;
      // Search for existing child with the same name
      for (let k = 0; k < children.length; k++) {
        if (children[k]["name"] === nodeName) {
          childNode = children[k];
          foundChild = true;
          break;
        }
      }
      // If not found, create a new child node
      if (!foundChild) {
        childNode = { name: nodeName, children: [] };
        children.push(childNode);
      }
      currentNode = childNode;
      // If it's the last part of the sequence, create a leaf node
      if (j === parts.length - 1) {
        childNode.value = size;
      }
    }
  }
  return root;
}

// Create percentage table for insights

icicle_pct_table = {
  if (!csv || csv.length === 0) return [];
  // A little function to basically calculate the same thing as the breadcrumb but for all the admin region.
  // This is what users should see if going to a tabular view and also what they should download. It is much easier to understand and work with.
  const summarize_icicle = (csv, icicle_keys = null) => {
    const rows = csv.map(({ path, value }) => ({
      parts: path.split("_").slice(1),
      value: value
    }));
    const total = d3.sum(rows, (d) => d.value);

    const maxDepth = Math.max(...rows.map((r) => r.parts.length));
    const levelNames = icicle_keys
      ? Object.keys(icicle_keys)
      : [...Array(maxDepth).keys()].map((i) => `level${i + 1}`);

    const lookupName = (dim, key) => {
      if (!icicle_keys) return key;
      return _lang(icicle_keys[dim]?.[key]) ?? key;
    };

    const genderTotals = {};
    rows.forEach((r) => {
      const gender = r.parts[0];
      genderTotals[gender] = (genderTotals[gender] || 0) + r.value;
    });

    // Recursive helper
    const recurse = (rows, depth = 0, prefix = []) => {
      if (!rows.length) return [];
      if (depth >= maxDepth) return [];

      return d3
        .rollups(
          rows,
          (v) => d3.sum(v, (d) => d.value),
          (d) => d.parts[depth]
        )
        .flatMap(([k, sum]) => {
          const current = [...prefix, k];
          const rowObj = {};
          levelNames.forEach((col, i) => {
            rowObj[col] = current[i] ? lookupName(col, current[i]) : null;
          });
          rowObj.pct_total = (100 * sum) / total;
          // pct relative to gender
          const genderKey = prefix[0];
          if (genderKey && genderTotals[genderKey]) {
            rowObj.pct_of_gender = (100 * sum) / genderTotals[genderKey];
          }
          return [
            rowObj,
            ...recurse(
              rows.filter((r) => r.parts[depth] === k),
              depth + 1,
              current
            )
          ];
        });
    };

    return recurse(rows);
  };

  return summarize_icicle(csv, icicleKeys);
}

icicle_dict = Object.fromEntries(
  Object.values(icicleKeys).flatMap((group) => Object.entries(group)),
);

icicle_color = d3
  .scaleOrdinal()
  .domain([
    "population",
    "gender1", // female
    "gender0", // male

    "poverty1", // low poverty = good // TODO: Check the directionality of this in preprocess code
    "poverty2", // mid. poverty = mid
    "poverty3", // high poverty = bad
    "povertyNA", // no data

    "education3", // high edu. = good
    "education2", // mid. edu. = mid
    "education1", // low edu = bad
    "educationNA", // no data
  ])
  .range([
    "#a4a4a4", // population - removed
    "#59CD90", // female - #59CD90
    "#A491D3", // male

    "#f4bb21", // low poverty = good
    "#fc8a34", // mid. poverty = mid
    "#ec5a47", // high poverty = bad
    "#a4a4a4", // no data

    "#f4bb21", // high edu = good
    "#fc8a34", // mid. edu = mid
    "#ec5a47", // low edu = bad
    "#a4a4a4", // no data
  ]);

targetHeight = 425;
narrowHeight = 600;
icicleHeight = narrow ? narrowHeight : targetHeight;
narrow = width <= 0;

partitionIcicle = (data, height, order = null) =>
  d3
    .partition()
    .padding(1)
    .size(narrow ? [height, width] : [width, height])(
    d3
      .hierarchy(data)
      .sum((d) => d.value)
      .sort((a, b) => {
        if (order) {
          // Look up indices, fallback to 999 if not defined
          return (order[a.data.name] ?? 999) - (order[b.data.name] ?? 999);
        }
        return b.value - a.value;
      }),
  );

renderIcicle = () => {
  const height = icicleHeight;
  const breadcrumbHeight = 40;
  const breadcrumbWidth = 275;
  const verticalSpacing = 20;

  const _data = buildHierarchy(csv);

  const segmentX = (d) => (narrow ? d.y0 : d.x0);
  const segmentY = (d) => (narrow ? d.x0 : d.y0);
  const segmentWidth = (d) => (narrow ? d.y1 - d.y0 : d.x1 - d.x0);
  const segmentHeight = (d) => (narrow ? d.x1 - d.x0 : d.y1 - d.y0);

  if (!_data || !csv || csv.length === 0) {
    const element = document.createElement("div");
    element.value = { sequence: [], percentage: 0 };
    return element;
  }

  const config = {
    gutter: 80,
    layerLabels: ["Gender", "Poverty", "Education"],
    order: {
      povertyNA: 3,
      poverty1: 2,
      poverty2: 1,
      poverty3: 0,
      education1: 0,
      education2: 1,
      education3: 2,
      educationNA: 3,
    },
    legend: {
      height: 30,
      itemSpacing: 100,
      itemOffset: 25,
    },
  };

  const root = partitionIcicle(_data, height, config.order);

  let frozen = false;
  let frozenSequence = [];

  const totalHeight = breadcrumbHeight + config.legend.height + height;

  const svg = d3
    .create("svg")
    .attr("viewBox", `0 0 ${width + config.gutter} ${totalHeight}`)
    .style("font", "12px sans-serif");

  const element = svg.node();
  element.value = { sequence: [], percentage: 0.0 };

  // Breadcrumb
  const breadcrumbGroup = svg.append("g").attr("transform", `translate(10, 0)`);

  function breadcrumbPoints(d, i) {
    const tipWidth = breadcrumbHeight * 0.3;
    return [
      `0,0`,
      `${breadcrumbWidth},0`,
      `${breadcrumbWidth + tipWidth},${breadcrumbHeight / 2}`,
      `${breadcrumbWidth},${breadcrumbHeight}`,
      `0,${breadcrumbHeight}`,
      ...(i > 0 ? [`${tipWidth},${breadcrumbHeight / 2}`] : []),
    ].join(" ");
  }

  function renderBreadcrumb(sequence, percentage) {
    const g = breadcrumbGroup.selectAll("g").data(sequence, (d) => d.data.name);

    g.exit().remove();

    const gEnter = g.enter().append("g");

    gEnter.append("polygon").attr("stroke", "white");

    gEnter
      .append("text")
      .attr("text-anchor", "middle")
      .attr("fill", "white")
      .attr("font-size", "19px")
      .attr("dy", "0.35em");

    const merged = gEnter
      .merge(g)
      .attr("transform", (d, i) => `translate(${i * breadcrumbWidth}, 0)`);

    merged
      .select("polygon")
      .attr("points", breadcrumbPoints)
      .attr("fill", (d) => icicle_color(d.data.name));

    merged
      .select("text")
      .attr("x", breadcrumbWidth / 2)
      .attr("y", breadcrumbHeight / 2)
      .text((d) => {
        const name = d.data.name;
        const translated = icicle_dict?.[name];
        return translated ? _lang(translated) : name;
      });

    breadcrumbGroup.selectAll(".breadcrumb-percentage").remove();

    if (sequence.length > 1) {
      breadcrumbGroup
        .append("text")
        .attr("class", "breadcrumb-percentage")
        .attr("x", (sequence.length + 0.1) * breadcrumbWidth)
        .attr("y", breadcrumbHeight / 2)
        .attr("dy", "0.35em")
        .attr("font-size", "19px")
        .text(percentage > 0 ? percentage + "%" : "");
    }
  }

  // Legend
  const legendY = breadcrumbHeight + verticalSpacing;

  const colorScale = d3
    .scaleOrdinal()
    .domain(["Better", "Moderate", "Worse", "No data"])
    .range(["#F4BB21", "#FC8A34", "#EC5A47", "#a4a4a4"]);

  const legendGroup = svg
    .append("g")
    .attr("transform", `translate(${config.gutter}, ${legendY})`);

  const legend = legendGroup
    .selectAll("g")
    .data(colorScale.domain())
    .join("g")
    .attr(
      "transform",
      (d, i) => `translate(${i * config.legend.itemSpacing}, 0)`,
    );

  legend
    .append("rect")
    .attr("width", 18)
    .attr("height", 18)
    .attr("fill", colorScale);

  legend
    .append("text")
    .attr("x", 24)
    .attr("y", 9)
    .attr("dy", ".35em")
    .text((d) => d);

  // Icicle Plot
  const plot = svg
    .append("g")
    .attr("transform", `translate(${config.gutter}, 0)`);

  plot
    .append("rect")
    .attr("width", width)
    .attr("height", height)
    .attr("fill", "none");

  const segment = plot
    .append("g")
    .attr(
      "transform",
      narrow ? `translate(${-root.y1}, 40)` : `translate(0, 0)`,
    )
    .selectAll("rect")
    .data(root.descendants().filter((d) => d.depth))
    .join("rect")
    .attr("fill", (d) => icicle_color(d.data.name))
    .attr("x", segmentX)
    .attr("y", segmentY)
    .attr("width", segmentWidth)
    .attr("height", segmentHeight)
    .on("mouseenter", (event, d) => {
      if (frozen) return;
      const sequence = d.ancestors().reverse().slice(1);
      segment.attr("fill-opacity", (node) =>
        sequence.indexOf(node) >= 0 ? 1 : 0.3,
      );
      const percentage = (100 * (d.value / root.value)).toPrecision(3);
      element.value = { sequence, percentage };
      element.dispatchEvent(new CustomEvent("input"));
      renderBreadcrumb(sequence, percentage);
    })
    .on("click", (event, d) => {
      if (frozen) {
        frozen = false;
        frozenSequence = [];
        segment.attr("fill-opacity", 1);
        renderBreadcrumb([], 0);
        return;
      }
      frozen = true;
      frozenSequence = d.ancestors().reverse().slice(1);
      segment.attr("fill-opacity", (node) =>
        frozenSequence.indexOf(node) >= 0 ? 1 : 0.3,
      );
      const percentage = (100 * (d.value / root.value)).toPrecision(3);
      element.value = {
        sequence: frozenSequence,
        percentage,
      };
      element.dispatchEvent(new CustomEvent("input"));
      renderBreadcrumb(frozenSequence, percentage);
      event.stopPropagation();
    });

  svg.on("mouseleave", () => {
    if (frozen) return;
    segment.attr("fill-opacity", 1);
    // Update the value of this view
    element.value = { sequence: [], percentage: 0.0 };
    element.dispatchEvent(new CustomEvent("input"));
    renderBreadcrumb([], 0);
  });

  svg.on("click", () => {
    if (frozen) {
      frozen = false;
      frozenSequence = [];
      segment.attr("fill-opacity", 1);
      element.value = { sequence: [], percentage: 0.0 };
      element.dispatchEvent(new CustomEvent("input"));
      renderBreadcrumb([], 0);
    }
  });

  // Layer Labels
  const depthNodes = d3
    .rollups(
      root.descendants().filter((d) => d.depth),
      (v) => v[0],
      (d) => d.depth,
    )
    .sort((a, b) => d3.ascending(a[0], b[0]));

  plot
    .append("g")
    .attr(
      "transform",
      narrow ? `translate(${-root.y1}, 40)` : `translate(0, 0)`,
    )
    .selectAll("text.layer-label")
    .data(
      depthNodes.map(([depth, rep]) => ({
        depth,
        y: segmentY(rep) + segmentHeight(rep) / 2,
      })),
    )
    .join("text")
    .attr("x", -12)
    .attr("y", (d) => d.y)
    .attr("dy", "0.35em")
    .attr("text-anchor", "end")
    .attr("font-weight", "600")
    .text((d) => config.layerLabels[d.depth - 1] ?? `Layer ${d.depth}`);

  // Percentage Labels
  plot
    .append("g")
    .attr(
      "transform",
      narrow ? `translate(${-root.y1}, 40)` : `translate(0, 0)`,
    )
    .selectAll("text")
    .data(
      root.descendants().filter((d) => d.depth && d.data.name !== "population"),
    )
    .join("text")
    .attr("x", (d) => segmentX(d) + segmentWidth(d) / 2)
    .attr("y", (d) => segmentY(d) + segmentHeight(d) / 2)
    .attr("dy", "0.35em")
    .attr("text-anchor", "middle")
    .attr("font-weight", "bold")
    .attr("fill", (d) =>
      ["#A491D3", "#ec5a47", "#fc8a34"].includes(icicle_color(d.data.name))
        ? "white"
        : "black",
    )
    .attr("pointer-events", "none")
    .text((d) => {
      const dataObj = d?.data;
      const genderName = dataObj.name.startsWith("gender")
        ? _lang(icicle_dict[dataObj.name])
        : null;
      const genderStr = genderName ? `${genderName} - ` : "";
      const percentage = (100 * (d.value / root.value)).toFixed(1);
      return segmentWidth(d) > 30 && segmentHeight(d) > 15
        ? `${genderStr}${percentage}%`
        : "";
    });

  return element;
};

renderSensitivityInsight = () => {
  const _data = icicle_pct_table;
  const template = _lang(vulnerability_translations.sensitivity_insight);
  const regionName = getAdminNameString();
  const round = 2;

  const highPovKey = _lang(icicle_dict.poverty3);
  const noEduKey = _lang(icicle_dict.education1);
  const secondaryEduKey = _lang(icicle_dict.education3);
  const maleKey = _lang(icicle_dict.gender0);
  const femaleKey = _lang(icicle_dict.gender1);

  const totalsByGender = Object.fromEntries(
    _data
      .filter((d) => d.poverty === null && d.education === null)
      .map((d) => [d.gender, d.pct_total]),
  );

  const sensitiveGroup = Object.fromEntries(
    _data
      .filter((d) => d.poverty === highPovKey && d.education === noEduKey)
      .map((d) => [d.gender, d.pct_total]),
  );

  const highPoverty = Object.fromEntries(
    _data
      .filter((d) => d.poverty === highPovKey && d.education === null)
      .map((d) => [d.gender, d.pct_of_gender]),
  );

  const secondaryTotals = _data
    .filter((d) => d.education === secondaryEduKey)
    .reduce((acc, d) => {
      acc[d.gender] = (acc[d.gender] || 0) + d.pct_total;
      return acc;
    }, {});

  const noEduTotals = _data
    .filter((d) => d.education === noEduKey)
    .reduce((acc, d) => {
      acc[d.gender] = (acc[d.gender] || 0) + d.pct_total;
      return acc;
    }, {});

  const primaryGap = noEduTotals[femaleKey] - noEduTotals[maleKey];
  const secondaryGap = secondaryTotals[femaleKey] - secondaryTotals[maleKey];

  const items = [
    { name: "region", value: regionName },
    { name: "male_pop", value: totalsByGender[maleKey].toFixed(round) },
    { name: "female_pop", value: totalsByGender[femaleKey].toFixed(round) },
    { name: "sensitiveMale", value: sensitiveGroup[maleKey].toFixed(round) },
    {
      name: "sensitiveFemale",
      value: sensitiveGroup[femaleKey].toFixed(round),
    },
    { name: "noPrimaryFemale", value: noEduTotals[femaleKey].toFixed(round) },
    { name: "noPrimaryMale", value: noEduTotals[maleKey].toFixed(round) },
    { name: "primaryGap", value: primaryGap.toFixed(round) },
    {
      name: "secondaryFemale",
      value: secondaryTotals[femaleKey].toFixed(round),
    },
    { name: "secondaryMale", value: secondaryTotals[maleKey].toFixed(round) },
    { name: "secondaryGap", value: secondaryGap.toFixed(round) },
  ];

  const insight = Lang.reduceReplaceTemplateItems(template, items);
  return createInsightDisplay(insight);
};

adaptiveCapacityQuestion = _lang(
  vulnerability_translations.adaptive_capacity_question,
);

adaptiveCapacityControlsForm = createGeographicControlsForm();
viewof adaptiveCapView = Inputs.radio(["plot", "table"], {
  label: "View Type",
  value: "plot",
});

{
  if (adaptiveCapView == "plot") {
    return renderAdaptiveCapacityPlot();
  } else {
    return filterableDataTable(adaptiveCapacityRawData);
  }
}

downloadButton(
  adaptiveCapacityRawData,
  `adaptive_capacity_data_${getAdminNameString().replace(", ", "_")}`,
  _lang(vulnerability_translations.download_data),
);

adaptiveCapacityInsights();

adaptiveCapacityRawData = {
  const a0 = admin0Select?.admin0_name;
  const a1 = admin1Select?.admin1_name;
  const a2 = admin2Select?.admin2_name;
  const parentOrNULL =
    !a0 ? `admin0_name = 'SSA'` :
    !a1 ? `admin0_name = 'SSA'` :
    !a2 ? `admin0_name = '${a0}' AND admin1_name IS NULL` :
    `admin0_name = '${a0}' AND admin1_name = '${a1}' AND admin2_name IS NULL`;

  // if a0 null = ssa, if a0 not null but a1 null = ssa, if a1 not null but a2 null = a0, if a2 not null = a1

  const resp = await localDb.query(`
    SELECT *
    FROM enabling
    WHERE (${sqlAdminQuerySpecific()})
      OR (${parentOrNULL})
  `);
  return resp;
}

// Shared indicator definitions for plots and insights
adaptiveCapacityIndicators = [
  {
    key: "pct_electric_norm",
    rawKey: "pct_electric",
    label: _lang({ en: "Electricity Access", fr: "Accès à l'électricité" }),
    unit: "%",
  },
  {
    key: "pct_piped_water_norm",
    rawKey: "pct_piped_water",
    label: _lang({ en: "Piped Water Access", fr: "Accès à l'eau courante" }),
    unit: "%",
  },
  {
    key: "min_to_cities_norm",
    rawKey: "min_to_cities",
    label: _lang({
      en: "Distance to Market",
      fr: "Temps de trajet vers les villes",
    }),
    unit: " min.",
  },
  {
    key: "pct_cellphone_norm",
    rawKey: "pct_cellphone",
    label: _lang({
      en: "Cellphone Access",
      fr: "Accès au téléphone portable",
    }),
    unit: "%",
  },
  {
    key: "conflict_density_norm",
    rawKey: "conflict_density",
    label: _lang({ en: "Lack of Conflict", fr: "Absence de conflits" }),
    unit: " conflict count/km²",
  },
];

// Get current admin selections (same variable names as sensitivity section)
adaptiveCapacityAdminSelections = {
  return {
    selectAdmin0: admin0Select?.admin0_name || null,
    selectAdmin1: admin1Select?.admin1_name || null,
    selectAdmin2: admin2Select?.admin2_name || null,
  };
};

adaptiveCapacityPlotSelectionData = {
  const data = adaptiveCapacityRawData;
  const selections = adaptiveCapacityAdminSelections;
  const isSSA = selections.selectAdmin0 === "SSA" || !selections.selectAdmin0;

  const a0 = admin0Select?.admin0_name;
  const a1 = admin1Select?.admin1_name;
  const a2 = admin2Select?.admin2_name;

  const level =
    !a0 || a0 === "SSA" ? "ssa" :
    !a1 ? "admin0" :
    !a2 ? "admin1" :
    "admin2";

  const regionData = data.filter(
    (d) =>
      d.admin0_name === (a0 || "SSA") &&
      d.admin1_name === a1 &&
      d.admin2_name === a2,
  );

  const regionName = getAdminNameString();

  let parentData = [];
  let parentName = "";

  if (!selections.selectAdmin0) {
    parentData = [];
  } else if (!selections.selectAdmin1) {
    parentData = data.filter((d) => d.admin0_name === "SSA");
    parentName = "SSA";
  } else if (!selections.selectAdmin2) {
    parentData = data.filter(
      (d) => d.admin0_name === selections.selectAdmin0 && !d.admin1_name,
    );
    parentName = selections.selectAdmin0;
  } else {
    parentData = data.filter(
      (d) =>
        d.admin0_name === selections.selectAdmin0 &&
        d.admin1_name === selections.selectAdmin1 &&
        !d.admin2_name,
    );
    parentName = selections.selectAdmin1;
  }

  return {
    isSSA,
    regionData,
    regionName,
    parentData,
    parentName,
  };
}

adaptiveCapacityPlotData = {
  const { regionData, parentData } = adaptiveCapacityPlotSelectionData;
  const indicators = adaptiveCapacityIndicators;

  const chartData = indicators.map((indicator) => {
    const region_normalizedValue = regionData.length > 0 
      ? (regionData[0]?.[indicator.key] || null)
      : null;
    const parent_normalizedValue = parentData.length > 0
      ? (parentData[0]?.[indicator.key] || null)
      : null;
    // Get original value for tooltips
    const region_rawValue = regionData.length > 0
      ? (regionData[0]?.[indicator.rawKey] || null)
      : null;
    const parent_rawValue = parentData.length > 0
      ? (parentData[0]?.[indicator.rawKey] || null)
      : null;

    return {
      indicator: indicator.label,
      region_normalizedValue,
      region_rawValue,
      parent_normalizedValue,
      parent_rawValue,
      unit: indicator.unit,
    };
  });

  return chartData.sort((a, b) => a.indicator.localeCompare(b.indicator));
}

renderAdaptiveCapacityPlot = () => {
  const { isSSA, regionName, parentName } = adaptiveCapacityPlotSelectionData;
  const sortedData = adaptiveCapacityPlotData;
  const maxValue = 1.0;

  const plot_channels = {
    [_lang({
      en: `${regionName} Value`,
      fr: "Valeur de la région sélectionnée",
    })]: (d) =>
      d.region_rawValue == null
        ? null
        : Number(d.region_rawValue).toFixed(1) + (d.unit || ""),
  };

  if (!isSSA) {
    plot_channels[
      _lang({ en: `${parentName} Value`, fr: "Moyenne régionale" })
    ] = (d) =>
      d.parent_rawValue == null
        ? null
        : Number(d.parent_rawValue).toFixed(1) + (d.unit || "");
  }

  const plot = Plot.plot({
    width,
    height: 400,
    marginLeft: 160,
    marginRight: 40,
    marginTop: 10,
    marginBottom: 40,
    color: {
      legend: !isSSA,
      domain: [
        _lang({ en: `${regionName} Value`, fr: `${regionName} Value` }),
        _lang({ en: `${parentName} Value`, fr: "Moyenne régionale" }),
      ],
      range: ["#4a90e2", "#e5e5e5"],
    },
    x: {
      label: _lang({ en: "Ability to Adapt", fr: "Niveau de performance" }),
      labelAnchor: "center",
      labelOffset: 30,
      domain: [0, maxValue],
      tickFormat: (d) =>
        d === 0
          ? _lang({ en: "Low", fr: "Faible" })
          : d === 1
            ? _lang({ en: "High", fr: "Élevé" })
            : "",
      ticks: [0, 0.5, 1.0],
      tickSize: 4,
      labelFontSize: 12,
      labelFontWeight: "600",
    },
    y: {
      label: null,
      tickSize: 0,
      grid: true,
    },

    marks: [
      Plot.barX(sortedData, {
        x: "parent_normalizedValue",
        y: "indicator",
        fill: "#e5e5e5",
        opacity: 0.7,
      }),

      Plot.barX(sortedData, {
        x: "region_normalizedValue",
        y: "indicator",
        fill: "#4a90e2",
        insetTop: 15,
        insetBottom: 15,
        channels: plot_channels,
        tip: {
          format: {
            x: false,
          },
        },
      }),
    ],
  });

  return plot;
};

// Dynamic Insights following wireframe template
adaptiveCapacityInsights = () => {
  const selections = adaptiveCapacityAdminSelections;
  let data = adaptiveCapacityRawData;

  if (!selections.selectAdmin0) {
    data = data.filter((d) => d.admin0_name === "SSA" || !d.admin0_name);
  } else if (!selections.selectAdmin1) {
    data = data.filter((d) => d.admin0_name === selections.selectAdmin0);
  } else if (!selections.selectAdmin2) {
    data = data.filter(
      (d) =>
        d.admin0_name === selections.selectAdmin0 &&
        d.admin1_name === selections.selectAdmin1,
    );
  } else {
    data = data.filter(
      (d) =>
        d.admin0_name === selections.selectAdmin0 &&
        d.admin1_name === selections.selectAdmin1 &&
        d.admin2_name === selections.selectAdmin2,
    );
  }
  const region = getAdminNameString();
  const selectedRow = data[0] || {};

  const indicators = adaptiveCapacityIndicators.map((indicator) => ({
    ...indicator,
    originalValue: selectedRow[indicator.rawKey],
    normalizedValue: selectedRow[indicator.key],
  }));

  // Identify limiting factors based on the exact format specified
  const limitingFactors = [];

  // Check each indicator for limiting factors using normalized values
  const limitFormatters = {
    pct_electric: (v) => `${v.toFixed(1)}%`,
    min_to_cities: (v) => `${v.toFixed(0)} min`,
    inet_d_kbps: (v) => `${(v / 1000).toFixed(1)} Mbps`,
    pct_piped_water: (v) => `${v.toFixed(0)}%`,
    pct_cellphone: (v) => `${v.toFixed(0)}%`,
    conflict_density: (v) => `${(v * 1000).toFixed(1)} per 1000 km²`,
  };

  indicators.forEach((indicator) => {
    const normalizedValue = indicator?.normalizedValue;
    const originalValue = indicator?.originalValue;

    if (normalizedValue >= 0.5) return;

    const numValue = Number(originalValue);
    const formatter = limitFormatters[indicator.rawKey];
    const formattedValue = formatter
      ? formatter(numValue)
      : `${numValue.toFixed(1)}${indicator.unit}`;

    limitingFactors.push(`${indicator.label} (${formattedValue})`);
  });

  // Generate insight text in the exact format specified
  const limitationLevel =
    limitingFactors.length > 2
      ? _lang({ en: "significantly limited", fr: "significativement limitée" })
      : limitingFactors.length > 0
        ? _lang({ en: "limited", fr: "limitée" })
        : _lang({ en: "strong", fr: "forte" });

  // Format factors as HTML list
  const factorsList =
    limitingFactors.length > 0
      ? limitingFactors.map((factor) => htl.html`<li>${factor}</li>`)
      : [
          htl.html`<li>${_lang({ en: "Strong infrastructure and services", fr: "Infrastructure et services solides" })}</li>`,
        ];

  const recommendation =
    limitingFactors.length > 0
      ? _lang({
          en: "Addressing these can improve resilience.",
          fr: "Traiter ces problèmes peut améliorer la résilience.",
        })
      : _lang({
          en: "Continue strengthening these systems.",
          fr: "Continuer à renforcer ces systèmes.",
        });

  const insight = htl.html`<div>
    <p>${_lang({
      en: `In ${region}, adaptive capacity is ${limitationLevel} by:`,
      fr: `En ${region}, la capacité d'adaptation est ${limitationLevel} par :`,
    })}</p>
    <ul>${factorsList}</ul>
    <p>${recommendation}</p>
  </div>`;

  return createInsightDisplay(insight);
};

// About this notebook
aboutSection = htl.html`
  <div>
    <h3 style="margin: 0 0 16px 0; color: #2d3748;">
      ${_lang({ en: "About This Notebook", fr: "À propos de ce carnet" })}
    </h3>
    <p style="margin: 0 0 12px 0; line-height: 1.6;">
      ${_lang({
        en: "This vulnerability assessment notebook helps you understand climate vulnerability through three key dimensions: exposure to climate hazards, sensitivity of populations, and adaptive capacity of communities.",
        fr: "Ce carnet d'évaluation de la vulnérabilité vous aide à comprendre la vulnérabilité climatique à travers trois dimensions clés : l'exposition aux dangers climatiques, la sensibilité des populations et la capacité d'adaptation des communautés.",
      })}
    </p>
    <p style="margin: 0 0 12px 0; line-height: 1.6;">
      ${_lang({
        en: "The notebook uses interactive visualizations to explore vulnerability patterns across Sub-Saharan Africa, with data that can be filtered by administrative regions and demographic characteristics.",
        fr: "Le carnet utilise des visualisations interactives pour explorer les schémas de vulnérabilité à travers l'Afrique subsaharienne, avec des données qui peuvent être filtrées par régions administratives et caractéristiques démographiques.",
      })}
    </p>
    <p style="margin: 0; line-height: 1.6; font-size: 14px; color: #6b7280;">
      ${_lang({
        en: "For questions or feedback about this notebook, please contact the Adaptation Atlas team.",
        fr: "Pour des questions ou commentaires sur ce carnet, veuillez contacter l'équipe de l'Atlas d'adaptation.",
      })}
    </p>
  </div>
`;

Source code

mapWidth = typeof width !== "undefined" ? Math.min(width, 625) : 625;
mapHeight = 600;

viewof masterLanguage = Inputs.radio(languages, {
  label: "Main language toggle",
  format: (d) => d.key,
  value: languages.find((x) => x.key === defaultLangKey),
})

languages = [
  { key: "en", label: "English", locale: 'en-US' },
  { key: "fr", label: "Français", locale: 'fr-FR' }
]

defaultLangKey = {
  const name = "lang";
  const list = languages.map((d) => d.key);
  const defaultKey = "en";
  const queryParam = await Lang.getParamFromList({ name, list });
  return queryParam ?? defaultKey;
}

_lang = Lang.lg(masterLanguage.key)

localDb = {
  const db = await DuckDBClient.of({
    exposure: FileAttachment(
      "/data/vulnerability_notebook/notebook_exposure.parquet"
    ),
    vulnerability_icicle: FileAttachment(
      "/data/vulnerability_notebook/vulnerability_icicledata.parquet",
    ),
    urgency: FileAttachment(
      "/data/vulnerability_notebook/urgency_index.parquet"
    ),
    enabling: FileAttachment(
        "/data/vulnerability_notebook/enabling_vars.parquet",
    ),
  });
return db
}

import { getAdminBoundaries } from "/components/_atlasBoundaries.ojs";

boundaries = getAdminBoundaries([0, 1, 2]);

adminKey = (d) => `${d.admin0_name}|${d.admin1_name}|${d.admin2_name}`;

function getAdminSelection(
  admin0,
  admin1,
  admin2,
  globalLabel = "Sub-Saharan Africa",
) {
  const a0 = admin0?.value;
  const a1 = admin1?.value;
  const a2 = admin2?.value;

  return a2 ? a2 : a1 ? a1 : a0 ? a0 : globalLabel;
}

filteredBoundaries = () => {
  // SSA → admin0, no filtering
  if (!admin0Select?.admin0_name) {
    return boundaries.admin0;
  }

  // Country selected → admin1 within admin0
  if (!admin1Select?.admin1_name) {
    return {
      ...boundaries.admin1,
      features: boundaries.admin1.features.filter(
        (d) => d.properties.admin0_name === admin0Select.admin0_name,
      ),
    };
  }

  // Admin1 selected → admin2 within admin1
  return {
    ...boundaries.admin2,
    features: boundaries.admin2.features.filter(
      (d) =>
        d.properties.admin0_name === admin0Select.admin0_name &&
        d.properties.admin1_name === admin1Select.admin1_name,
    ),
  };
};

function mergeDataToBoundaries({
  boundaries,
  data,
  boundaryKey,
  dataKey,
  dataProp = "data",
  defaultValue = null,
}) {
  const index = new Map(data.map((d) => [dataKey(d), d]));

  return {
    type: "FeatureCollection",
    features: boundaries.features.map((feature) => {
      const key = boundaryKey(feature);
      return {
        ...feature,
        properties: {
          ...feature.properties,
          [dataProp]: index.get(key) ?? defaultValue,
        },
      };
    }),
  };
}

//TODO: Remove this (below) and migrate dependencies over to above

/**
 * Bind tabular data to geographic features by matching values in specified columns
 *
 * @param {Object} options - Configuration object
 * @param {Array} options.data - Array of tabular data objects to bind
 * @param {string} options.dataBindColumn - Column name in tabular data to use for matching
 * @param {Object} options.geoData - GeoJSON FeatureCollection to bind data to
 * @param {string} options.geoDataBindColumn - Property name in geo features to match against
 * @returns {Object} GeoJSON FeatureCollection with data bound to each feature's properties.data
 *
 * @example
 * const geoWithData = bindTabularToGeo({
 *   data: [{admin0_name: "Kenya", value: 100}],
 *   dataBindColumn: "admin0_name",
 *   geoData: boundaries.admin0,
 *   geoDataBindColumn: "admin0_name"
 * });
 */
function bindTabularToGeo({
  data = [],
  dataBindColumn = "dataBindColumn",
  geoData = [],
  geoDataBindColumn = "geoDataBindColumn",
}) {
  const index = new Map(data.map((d) => [d[dataBindColumn], d]));
  const geojson = JSON.parse(JSON.stringify(geoData));

  for (const f of geojson.features) {
    f.properties.data = index.get(f.properties[geoDataBindColumn]);
  }
  return geojson;
}

// Formatting utilities
/**
 * Format number as compact currency (e.g., "$1.2M")
 */
formatNumCompactShort = new Intl.NumberFormat("en-US", {
  notation: "compact",
  compactDisplay: "short",
  style: "currency",
  currency: "USD",
}).format;

// Color scales and themes
/**
 * Color scale definitions for visualizations
 */
colorScales = {
  return {
    range: {
      green: ['#E4F5D0', '#015023'],
      blue: ['#E8F2FF', '#003E6B'],
      brown: ['#FFFDE5', '#A87B00'],
      yellowGreen: ['#216729', '#F7D732'], // Reversed: green (low/good) to yellow (high/bad)
      orangeRed: ['#F4BB21', '#EC5A47'],
      redOrange: ['#FEE0D2', '#CB4335'],
      vulnerability: ['#2E8B57', '#FFD700', '#FF6347'], // Low, Medium, High
      adaptive: ['#FF6B6B', '#4ECDC4', '#45B7D1'] // Poor, Fair, Good
    },
    unknown: "#f0f0f0",
    noData: "#f0f0f0"
  }
}

intDollarUnit = `${intDollarYear} USD`
intDollarYear = 2015

/**
 * Value of Production (VoP) note configuration
 */
vopNote = {
  return {
    caption: _lang(vulnerability_translations.vopNoteCaption),
    blurb: _lang(vulnerability_translations.vopNoteBlurb)
  }
}

function createLoadingState(message = null) {
  return htl.html`
    <div style="
      display: flex;
      align-items: center;
      justify-content: center;
      padding: 40px;
      color: #6b7280;
      font-style: italic;
    ">
      <div style="margin-right: 12px;">
        <div class="spinner" style="
          width: 20px;
          height: 20px;
          border: 2px solid #e5e7eb;
          border-top: 2px solid #3b82f6;
          border-radius: 50%;
          animation: spin 1s linear infinite;
        "></div>
      </div>
      ${message || _lang(vulnerability_translations.loading)}
    </div>
    <style>
      @keyframes spin {
        0% { transform: rotate(0deg); }
        100% { transform: rotate(360deg); }
      }
    </style>
  `;
}

/**
 * Create a "no data available" state UI component
 *
 * @param {string|null} message - Optional message (defaults to translation)
 * @returns {HTMLElement} HTML element with no-data display
 */
function createNoDataState(message = null) {
  return htl.html`
    <div style="
      display: flex;
      align-items: center;
      justify-content: center;
      padding: 40px;
      background: #f9fafb;
      border: 2px dashed #d1d5db;
      border-radius: 8px;
      color: #6b7280;
      text-align: center;
      margin-top: 16px;
    ">
      <div>
        <div style="margin-bottom: 8px; font-size: 18px;">📊</div>
        <div>${message || _lang(vulnerability_translations.no_data_available)}</div>
      </div>
    </div>
  `;
}

/**
 * Create a styled insight display component from text or HTML
 * Handles both plain text (with paragraph splitting) and HTML elements
 *
 * @param {string|HTMLElement} insight - Insight text or HTML element to display
 * @returns {HTMLElement} Styled HTML element with insight content
 */
function createInsightDisplay(insight) {
  const containerStyle = `
    background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
    color: white;
    padding: 20px;
    border-radius: 10px;
    margin: 16px 0;
    box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1);
  `;
  const bodyStyle = "font-size: 16px; line-height: 1.6; opacity: 0.95;";

  const title = _lang(vulnerability_translations.quick_insights);

  const renderBody = () => {
    // For when raw html is returned
    if (typeof insight !== "string") {
      return htl.html`<div style="${bodyStyle}">${insight}</div>`;
    }

    const paragraphs = insight.split("\n\n").filter((p) => p.trim().length > 0);

    const paragraphElements = paragraphs
      .map((paragraph) => {
        if (paragraph === "---") {
          return null;
        }
        return htl.html`<p style="${bodyStyle}">${paragraph}</p>`;
      })
      .filter((p) => p !== null);

    return htl.html`<div>${paragraphElements}</div>`;
  };

  return htl.html`
    <div style="
      ${containerStyle}
    ">
      <h3 style="margin: 0 0 12px 0; font-size: 16px; font-weight: 600;">${title}</h3>
      ${renderBody()}
    </div>
  `;
}

/**
 * Create a shared geographic selection controls form.
 *
 * @returns {HTMLElement} Styled HTML element with admin selection controls.
 */
function createGeographicControlsForm() {
  return htl.html`
    <div style="
      background: #f8fafc;
      border: 2px solid #e2e8f0;
      border-radius: 12px;
      padding: 24px;
      box-shadow: 0 4px 6px rgba(0, 0, 0, 0.07);
      margin: 16px 0;
    ">
      <h3 style="margin: 0 0 20px 0; color: #2d3748; font-size: 1.1rem; font-weight: 600;">
        ${_lang(vulnerability_translations.geographic_selection)}
      </h3>
      <div style="
        display: flex;
        align-items: flex-start;
        gap: 20px;
        flex-wrap: wrap;
        justify-content: space-between;
      " class="form-inputs-container">
        <div style="flex: 1; min-width: 180px; max-width: 100%;">${admin012Form(masterLanguage.key)}</div>
      </div>
    </div>
  `;
}

nbTitle = _lang(vulnerability_translations.notebook_title);
overview = _lang(vulnerability_translations.notebook_overview);
appendix = _lang(general_translations.appendix);

function NavbarLangSelector(language_obj, masterLanguage) {
  let navEnd = document.querySelector(".navbar-nav.ms-auto .nav-item.compact");
  if (navEnd) {
    let existingLangSelector = document.getElementById("nav-lang-selector");
    if (!existingLangSelector) {
      let lang_sel = Inputs.bind(
        Inputs.radio(language_obj, {
          label: "",
          format: (d) => d.label
        }),
        viewof masterLanguage
      );
      lang_sel.id = "nav-lang-selector";

      // Style the language selector for navbar integration
      lang_sel.style.display = "flex";
      lang_sel.style.alignItems = "center";
      lang_sel.style.marginLeft = "10px";
      let lang_div = lang_sel.querySelector("div");
      lang_div.style.display = "flex";
      lang_div.style.flexDirection = "column";

      navEnd.parentNode.appendChild(lang_sel);
    }
  }
}

NavbarLangSelector(languages, masterLanguage)