src/data360/visualization.py

"""
Visualization generation for Data360 data.

Fetches series via the Data360 API (``data360.api.get_data_api_url``), builds
Vega-Lite specifications, then persists them either through the optional Charts API
(``charts_api_url``) or as static JSON under ``static/viz_specs/``.

**Public tools**

- ``get_viz_spec`` — one indicator. Uses ``data360.viz_config`` strategy dispatch
  to build validated Vega-Lite v5 specs directly.
- ``get_multi_indicator_viz_spec`` — two to four indicators; merges frames and
  dispatches multi-series strategies (scatter, layered lines, connected scatter, etc.).

Return shape for both: ``{"url": str|None, "error": str|None, ...}`` plus optional
``database_id``, ``database_name``, ``indicator_id``, ``indicator_name``, optional
``strategy`` / ``reason``, and optional preformatted ``source_line`` /
``subtitle_line`` for clients that only render strings. Before any HTTP
or ``json.dump``, specs are passed through ``_vega_spec_to_json_safe`` so
``data.values`` never contains raw ``pandas.Timestamp`` / numpy scalars that would
break JSON encoding.
"""

from __future__ import annotations

import asyncio
import json
import logging
import math
import os
import re
import uuid
from datetime import date, datetime
from typing import Any
from urllib.parse import parse_qs, urlparse

import httpx
import numpy as np
import pandas as pd

from data360 import viz_config
from data360.config import get_mcp_server_settings
from data360.http_client import get_shared_httpx_client
from data360.providers import get_database_mapping

_logger = logging.getLogger(__name__)

VizResult = dict[str, Any]


class VizInsufficientDataError(Exception):
    """Raised when data returned from the API is too sparse to render a meaningful chart.

    Prefer a clear, actionable error over a misleading visualization.
    """

# Disaggregation dimensions considered during viz data cleaning and encoding.
# Mirrors _DISAGG_DIMS_TO_DETECT from api.py but in lowercase (post-column-rename).
# Single source of truth for the viz pipeline: _clean_single_df, color dim
# detection, and multi-indicator join keys all reference this tuple.
#
# unit_measure is included last and uses a separate sentinel check (_UNIT_MEASURE_TRIVIAL)
# because its "total" sentinel is 'U' (Unitless), not '_T'. When unit_measure has
# 2+ distinct non-trivial values (e.g. Persons + Percentage for IPC), it is kept
# as a discriminating column so select_strategy can route correctly instead of
# silently mixing incompatible values on the same Y-axis.
_UNIT_MEASURE_TRIVIAL: frozenset[str] = frozenset({"U", ""})
_VIZ_DISAGG_DIMS: tuple[str, ...] = (
    "sex",
    "age",
    "urbanisation",
    "residence",
    "comp_breakdown_1",
    "comp_breakdown_2",
    "comp_breakdown_3",
    "unit_measure",
)

# Fallback attribution string when neither database nor indicator name is available.
# Mirrors DATA360_CHART_SOURCE_FALLBACK in the TypeScript frontend.
_SOURCE_FALLBACK = "World Bank — Data360"


def _unit_measure_for_formatting(
    raw_unit: str | None, resolved_unit_label: str | None = None
) -> str | None:
    """Return a formatting-friendly unit token for viz_config formatters."""
    raw = (raw_unit or "").strip()
    label = (resolved_unit_label or "").strip()
    raw_norm = raw.upper()
    label_norm = label.upper()

    if raw_norm == "PT" or ("PERCENT" in label_norm and "PERSON" not in label_norm):
        return "%"
    if (
        "$" in raw_norm
        or "USD" in raw_norm
        or "$" in label_norm
        or "USD" in label_norm
        or "DOLLAR" in label_norm
    ):
        return "USD"
    # If the label has descriptive keywords like PROPORTION or SHARE, return the label
    # so downstream rules can detect proportion formatting.
    if "PROPORTION" in label_norm or "SHARE" in label_norm:
        return label
    if raw:
        return raw
    if label:
        return label
    return None


# ============================================================================
# STORAGE HELPERS
# ============================================================================


def save_specs_to_static(vl_spec: dict) -> str:
    """Persist ``vl_spec`` as JSON and return a URL the **browser** can fetch.

    The frontend (or another client) loads this file over HTTP; it is not read
    only inside Docker. ``WEBSITE_HOSTNAME`` should be a host:port the user's browser
    can resolve; when unset we default to ``localhost`` and the MCP server port from
    ``data360.config.get_mcp_server_settings()``.
    """
    spec_id = str(uuid.uuid4())
    server_dir = os.path.dirname(os.path.abspath(__file__))
    project_root = os.path.abspath(os.path.join(server_dir, "..", ".."))
    specs_dir = os.path.join(project_root, "static", "viz_specs")
    os.makedirs(specs_dir, exist_ok=True)
    vega_path = os.path.join(specs_dir, f"{spec_id}_vega.json")
    with open(vega_path, "w") as f:
        json.dump(vl_spec, f, indent=2)
    base_url = os.environ.get(
        "WEBSITE_HOSTNAME", f"http://localhost:{get_mcp_server_settings().port}"
    )
    return f"{base_url}/static/viz_specs/{spec_id}_vega.json"


async def post_spec_to_charts_api(vl_spec: dict) -> str:
    """POST a Vega-Lite spec to the external Charts API (JSON body).

    The service expects standard Vega-Lite keys (``$schema``, ``data``, ``mark``,
    ``encoding``, …). Many deployments require a non-empty ``title``; we set a default
    if missing. On success, returns a chart URL from ``Location``, response JSON
    ``url`` / ``id``, or the configured API base as a last resort.
    """
    settings = get_mcp_server_settings()
    url = settings.charts_api_url
    if not url:
        raise ValueError("charts_api_url is not configured")
    payload = dict(vl_spec)
    # Charts API often rejects payloads without title
    payload.setdefault("title", vl_spec.get("title") or "Generated Visualization")
    headers = {"accept": "application/json", "Content-Type": "application/json"}
    if settings.charts_api_token:
        headers["Authorization"] = f"Bearer {settings.charts_api_token}"
    client = get_shared_httpx_client()
    response = await client.post(
        url,
        json=payload,
        headers=headers,
    )
    response.raise_for_status()
    location = response.headers.get("Location")
    if location:
        return (
            location
            if location.startswith("http")
            else f"{url.rsplit('/', 1)[0]}/{location.lstrip('/')}"
        )
    body = response.json() if response.content else {}
    if isinstance(body, dict):
        if body.get("url"):
            return body["url"]
        if body.get("id"):
            return f"{url.rstrip('/')}/{body['id']}"
    return url


def _vega_spec_to_json_safe(obj: object) -> object:
    """Recursively convert a Vega-Lite spec tree to JSON-serializable Python types.

    Covers pandas/numpy scalars and datetimes that can appear in ``data.values`` or
    elsewhere after ``DataFrame.to_dict`` (including merged dtypes and edge cases the
    DataFrame-only sanitizer misses).
    """
    if obj is None:
        return None
    if isinstance(obj, (bool, str)):
        return obj
    if isinstance(obj, (int, float)) and not isinstance(obj, bool):
        if isinstance(obj, float) and (math.isnan(obj) or math.isinf(obj)):
            return None
        return obj
    if isinstance(obj, pd.Timestamp):
        return obj.isoformat() if pd.notna(obj) else None
    if isinstance(obj, (datetime, date)):
        return obj.isoformat()
    if isinstance(obj, np.datetime64):
        return str(pd.Timestamp(obj))
    if isinstance(obj, (np.integer, np.floating)):
        if pd.isna(obj):
            return None
        return obj.item()
    if isinstance(obj, np.bool_):
        return bool(obj)
    if isinstance(obj, dict):
        return {k: _vega_spec_to_json_safe(v) for k, v in obj.items()}
    if isinstance(obj, (list, tuple)):
        return [_vega_spec_to_json_safe(v) for v in obj]
    if hasattr(obj, "item"):
        try:
            return _vega_spec_to_json_safe(obj.item())
        except (ValueError, AttributeError, TypeError):
            pass
    raise TypeError(
        f"Vega-Lite spec contains unsupported type for JSON: {type(obj).__name__}"
    )


_UNSET = object()  # sentinel for "not provided" (distinct from None which means "disable")


async def _store_spec(
    vl_spec: dict,
    charts_api_url_override: object = _UNSET,
) -> str:
    """Persist a Vega-Lite dict: Charts API when configured, else static file.

    Always runs ``_vega_spec_to_json_safe`` first so httpx ``json=`` and
    ``json.dump`` cannot fail on non-JSON-native types in embedded data.

    Args:
        vl_spec: The Vega-Lite spec dict to persist.
        charts_api_url_override: When provided, overrides the ``charts_api_url``
            from settings *for this call only*, without mutating the shared singleton.
            Pass ``None`` to force local-only storage even when a Charts API URL is
            configured. Use the sentinel ``_UNSET`` (default) to read from settings.
    """
    safe = _vega_spec_to_json_safe(vl_spec)
    if not isinstance(safe, dict):
        raise TypeError("Vega spec must serialize to a JSON object")

    settings = get_mcp_server_settings()
    if charts_api_url_override is _UNSET:
        charts_url = settings.charts_api_url
    else:
        charts_url = charts_api_url_override  # type: ignore[assignment]
    env = settings.env or "local"
    is_prod = env.lower() in ("prod", "production")

    if charts_url:
        try:
            url = await post_spec_to_charts_api(safe)
            # In production, do not save locally if Charts API is active and succeeded
            if is_prod:
                return url
            # Otherwise (local/dev), still save locally for caching/debugging
            save_specs_to_static(safe)
            return url
        except Exception as e:
            _logger.warning("Charts API store failed, falling back to static local: %s", e)

    # Fallback to local static file
    return save_specs_to_static(safe)


def _ok(
    url: str,
    warning: str | None = None,
    *,
    source_attribution: dict[str, str] | None = None,
    strategy: str | None = None,
    reason: str | None = None,
    dimensions: dict[str, list] | None = None,
    data_summary: dict | None = None,
    data_profile: dict | None = None,
    spec: dict | None = None,
) -> VizResult:
    r: VizResult = {"url": url, "error": None}
    if warning:
        r["warning"] = warning
    if source_attribution:
        for key, val in source_attribution.items():
            if val:
                r[key] = val
    if strategy:
        r["strategy"] = strategy
    if reason:
        r["reason"] = reason
    if dimensions:
        r["dimensions"] = dimensions  # type: ignore[assignment]
    if data_summary:
        r["data_summary"] = data_summary  # type: ignore[assignment]
    if data_profile:
        r["data_profile"] = data_profile  # type: ignore[assignment]
    if spec is not None:
        r["spec"] = spec
    attrib_for_line = {
        k: str(v)
        for k, v in r.items()
        if k
        in (
            "database_id",
            "database_name",
            "indicator_id",
            "indicator_name",
        )
        and v
    }
    r["source_line"] = _format_source_line_from_attribution(attrib_for_line)
    strat_v = r.get("strategy")
    reas_v = r.get("reason")
    sub = _format_subtitle_line(
        warning,
        strat_v if isinstance(strat_v, str) else None,
        reas_v if isinstance(reas_v, str) else None,
    )
    if sub:
        r["subtitle_line"] = sub
    return r


def _err(msg: str) -> VizResult:
    return {"url": None, "error": msg}


# Dimensions surfaced to the LLM in the tool response when they discriminate rows.
_SURFACE_DIMS: list[str] = [
    "unit_measure",
    "sex",
    "age",
    "urbanisation",
    "comp_breakdown_1",
    "comp_breakdown_2",
]


def _extract_dimension_summary(df: pd.DataFrame) -> dict[str, list]:
    """Return dimension -> sorted distinct values for non-trivial dims in *df*.

    Only dimensions with 2+ distinct values after excluding trivial sentinels
    ('_T', '_Z' for totals, 'U' for Unitless, '') are included. Values are
    expected to already be resolved to human-readable labels when called.
    The result is included in the tool response so the LLM knows what is in
    the data without needing to parse the Vega-Lite spec.
    """
    _trivial: frozenset[str] = frozenset({"_T", "_Z", "U", ""})
    result: dict[str, list] = {}
    for dim in _SURFACE_DIMS:
        if dim not in df.columns:
            continue
        vals = sorted(
            str(v) for v in df[dim].dropna().unique() if str(v) not in _trivial
        )
        if len(vals) > 1:
            result[dim] = vals
    return result


def _build_data_summary(df: pd.DataFrame) -> dict:
    """Compact structural summary of the plotted DataFrame for LLM narration.

    Supplements ``_extract_dimension_summary`` (which covers categorical dims)
    with quantitative/shape information: how many rows, what year range, which
    countries, and the numeric value range. All fields are optional — missing
    columns are silently skipped.

    Kept deliberately small: no raw rows, no per-dimension statistics. The goal
    is to give the LLM enough to narrate the chart without overwhelming context.
    """
    out: dict = {"shape": list(df.shape)}
    if "year" in df.columns:
        years = df["year"].dropna().astype(str)
        # Strip time components if ISO timestamps leaked through
        years = years.str[:4]
        unique_years = sorted(years.unique())
        if unique_years:
            out["year_range"] = [unique_years[0], unique_years[-1]]
    if "country" in df.columns:
        countries = sorted(df["country"].dropna().unique().tolist())
        if countries:
            out["countries"] = countries
    if "value" in df.columns:
        vals = pd.to_numeric(df["value"], errors="coerce").dropna()
        if not vals.empty:
            out["value"] = {
                "min": round(float(vals.min()), 4),
                "max": round(float(vals.max()), 4),
                "has_negatives": bool((vals < 0).any()),
            }
    return out


# ---------------------------------------------------------------------------
# Scale-type inference from unit codes / labels
# ---------------------------------------------------------------------------

_PERCENTAGE_TOKENS: frozenset[str] = frozenset({
    "PT", "PC", "PERCENT", "PERCENTAGE", "%", "RATE", "SHARE", "PROPORTION",
    "PC_GDP", "PC_GNI", "PC_TOT",
})

_CURRENCY_TOKENS: frozenset[str] = frozenset({
    "$", "USD", "CURRENCY", "DOLLARS", "LCU", "EUR", "GBP",
})

_PERSONS_TOKENS: frozenset[str] = frozenset({
    "PS", "PEOPLE", "PERSONS", "COUNT", "HEADCOUNT", "NR", "NUMBER",
})


def _infer_scale_type(unit_code: str | None, unit_label: str | None = None) -> str:
    """Infer a semantic scale type from a unit code or label string.

    Returns one of: ``"percentage"``, ``"currency"``, ``"persons"``, ``"index"``.
    """
    tokens: set[str] = set()
    import re
    for raw in (unit_code, unit_label):
        if raw:
            normalised = raw.upper().strip()
            tokens.add(normalised)
            # Remove all punctuation before splitting
            clean_str = re.sub(r'[^\w\s]', ' ', normalised)
            tokens.update(clean_str.split())

    if tokens & _PERSONS_TOKENS:
        return "persons"
    if tokens & _PERCENTAGE_TOKENS:
        return "percentage"
    if tokens & _CURRENCY_TOKENS:
        return "currency"
    return "index"


# ---------------------------------------------------------------------------
# Comprehensive data profile for LLM reasoning
# ---------------------------------------------------------------------------


def _build_data_profile(
    df: pd.DataFrame,
    *,
    scale_type: str | None = None,
    indicator_cols: list[str] | None = None,
    indicator_names: list[str] | None = None,
    units_raw: list[str] | None = None,
    units_label: list[str] | None = None,
    dim_name_labels: dict[str, str] | None = None,
) -> dict:
    """Build a comprehensive data profile for LLM charting reasoning.

    Computed **before routing**, so signals flow into strategy selection and
    spec building — not just post-hoc narration.

    Parameters
    ----------
    df : pd.DataFrame
        The cleaned DataFrame (post-_clean_single_df, post-country-mapping).
    scale_type : str | None
        Semantic unit type: ``"percentage"``, ``"currency"``, ``"persons"``,
        ``"index"``.  Derived from unit codes when omitted.
    indicator_cols : list[str] | None
        Wide-format column names for multi-indicator charts.
    indicator_names : list[str] | None
        Human-readable display names aligned with indicator_cols.
    units_raw : list[str] | None
        Raw unit codes, e.g. ``["PT", "USD_K_2015"]``.
    units_label : list[str] | None
        Resolved unit labels, e.g. ``["Percent", "Constant 2015 USD"]``.
    dim_name_labels : dict[str, str] | None
        API-sourced human-readable names for comp_breakdown_* columns.

    Sections returned
    -----------------
    indicators
        Per-indicator: name, unit, scale_type, value_range (with skewness,
        p25/p75, null_count/null_pct), all_positive, is_proportion.
    scale_compatibility   (multi-indicator only)
        Whether indicators can share a Y-axis.
    structure
        Year range, temporal density, gaps.
    coverage_quality
        Completeness pct, sparse countries (< 3 years), missing years.
    temporal_trend   (temporal data, single-indicator)
        Direction, total pct change, monotonicity, per-country when divergent.
    cross_country    (multi-country only)
        Spread, leader/laggard at latest year, convergence signal.
    breakdowns
        Disaggregation dimensions with actual values, cardinality, meaning,
        codelist name, and value_labels dict.
    composition_hint
        Whether data looks like parts-of-a-whole.
    """
    profile: dict[str, Any] = {}

    # ── helpers ────────────────────────────────────────────────────────────────
    def _series_stats(series: pd.Series) -> dict:
        """Full numeric stats including nulls, percentiles, skewness."""
        raw = pd.to_numeric(series, errors="coerce")
        null_count = int(raw.isna().sum())
        total = len(raw)
        null_pct = round(null_count / total * 100, 1) if total else 0.0
        clean = raw.dropna()
        if clean.empty:
            return {
                "min": None, "max": None, "median": None,
                "p25": None, "p75": None, "skewness": None,
                "null_count": null_count, "null_pct": null_pct,
            }
        try:
            skew = float(clean.skew())
            skew = round(skew, 3) if not pd.isna(skew) else None
        except Exception:
            skew = None
        return {
            "min": round(float(clean.min()), 4),
            "max": round(float(clean.max()), 4),
            "median": round(float(clean.median()), 4),
            "p25": round(float(clean.quantile(0.25)), 4),
            "p75": round(float(clean.quantile(0.75)), 4),
            "skewness": skew,
            "null_count": null_count,
            "null_pct": null_pct,
        }

    # ── 1.  Indicator profiles ─────────────────────────────────────────────────
    ind_profiles: list[dict] = []

    if indicator_cols and len(indicator_cols) >= 2:
        # Multi-indicator (wide-format columns)
        for i, col in enumerate(indicator_cols):
            if col not in df.columns:
                continue
            name = (indicator_names[i] if indicator_names and i < len(indicator_names) else col)
            unit_raw = (units_raw[i] if units_raw and i < len(units_raw) else None)
            unit_lbl = (units_label[i] if units_label and i < len(units_label) else None)
            sc = _infer_scale_type(unit_raw, unit_lbl)
            stats = _series_stats(df[col])
            clean = pd.to_numeric(df[col], errors="coerce").dropna()
            ind_profiles.append({
                "name": name,
                "unit_code": unit_raw or None,
                "unit_label": unit_lbl or None,
                "scale_type": sc,
                "value_range": stats,
                "all_positive": bool(clean.ge(0).all()) if not clean.empty else None,
                "is_proportion": sc == "percentage" and stats["max"] is not None and stats["max"] <= 100,
            })
    elif "value" in df.columns:
        # Single-indicator (long-format value column)
        sc = scale_type or _infer_scale_type(
            units_raw[0] if units_raw else None,
            units_label[0] if units_label else None,
        )
        stats = _series_stats(df["value"])
        clean = pd.to_numeric(df["value"], errors="coerce").dropna()
        ind_profiles.append({
            "name": (indicator_names[0] if indicator_names else None),
            "unit_code": (units_raw[0] if units_raw else None),
            "unit_label": (units_label[0] if units_label else None),
            "scale_type": sc,
            "value_range": stats,
            "all_positive": bool(clean.ge(0).all()) if not clean.empty else None,
            "is_proportion": sc == "percentage" and stats["max"] is not None and stats["max"] <= 100,
        })

    if ind_profiles:
        profile["indicators"] = ind_profiles

    # ── 2.  Scale compatibility (multi-indicator only) ─────────────────────────
    if len(ind_profiles) >= 2:
        maxes = [
            p["value_range"]["max"] for p in ind_profiles
            if p["value_range"].get("max") and p["value_range"]["max"] > 0
        ]
        mins_nonzero = [
            p["value_range"]["min"] for p in ind_profiles
            if p["value_range"].get("min") and p["value_range"]["min"] > 0
        ]
        all_same_unit = len(set(p.get("unit_code") for p in ind_profiles if p.get("unit_code"))) <= 1
        all_same_scale = len(set(p["scale_type"] for p in ind_profiles)) == 1

        if maxes and mins_nonzero:
            ratio = max(maxes) / min(mins_nonzero)

            # Bounded percentages relaxation
            is_percentage = (ind_profiles[0]["scale_type"] == "percentage") if ind_profiles else False
            maxes_val = [p["value_range"]["max"] for p in ind_profiles if p["value_range"]["max"] is not None]
            any_gt_1 = any(m > 1.0 for m in maxes_val)
            all_lte_1 = all(m <= 1.0 for m in maxes_val)
            same_numeric_scale = any_gt_1 or all_lte_1

            if is_percentage and same_numeric_scale:
                thresh = 100.0
            else:
                thresh = 10.0

            can_share = all_same_scale and ratio <= thresh
            reason_parts = []
            if all_same_scale:
                reason_parts.append(f"same scale type ({ind_profiles[0]['scale_type']})")
            else:
                types = ", ".join(p["scale_type"] for p in ind_profiles)
                reason_parts.append(f"different scale types ({types})")
            reason_parts.append(f"value ratio {ratio:.1f}x")
            reason_parts.append(f"{'within' if ratio <= thresh else 'exceeds'} {int(thresh)}x threshold")
            profile["scale_compatibility"] = {
                "same_unit": all_same_unit,
                "same_scale_type": all_same_scale,
                "max_min_ratio": round(ratio, 2),
                "can_share_axis": can_share,
                "reason": ", ".join(reason_parts),
            }
        else:
            profile["scale_compatibility"] = {
                "same_unit": all_same_unit,
                "same_scale_type": all_same_scale,
                "max_min_ratio": None,
                "can_share_axis": False,
                "reason": "Could not compute ratio — zero or missing values",
            }

    # ── 3.  Structure (temporal + geographic shape) ────────────────────────────
    structure: dict[str, Any] = {}
    unique_years: list[str] = []
    if "country" in df.columns:
        countries = sorted(df["country"].dropna().unique().tolist())
        structure["countries"] = countries
        structure["country_count"] = len(countries)
    if "year" in df.columns:
        years_str = df["year"].dropna().astype(str).str[:4]
        unique_years = sorted(years_str.unique().tolist())
        if unique_years:
            structure["year_range"] = [unique_years[0], unique_years[-1]]
            structure["year_count"] = len(unique_years)
        if "country" in df.columns and not df.empty:
            try:
                ypc = df.groupby("country")["year"].nunique()
                avg = float(ypc.mean())
                structure["avg_years_per_country"] = round(avg, 1)
                expected = len(unique_years)
                structure["temporal_density"] = (
                    "dense" if avg >= expected * 0.8
                    else "moderate" if avg >= expected * 0.5
                    else "sparse"
                )
                structure["has_gaps"] = avg < expected
            except Exception:
                pass
    if structure:
        profile["structure"] = structure

    if "country" in df.columns and "year" in df.columns and not df.empty:
        try:
            # Use the first available value column for null filtering
            _val_col = indicator_cols[0] if indicator_cols else "value"
            _val_col = _val_col if _val_col in df.columns else None

            _subset = df
            if _val_col:
                _subset = df[pd.to_numeric(df[_val_col], errors="coerce").notna()]

            ypc = _subset.groupby("country")["year"].nunique()
            n_countries = len(ypc)
            expected_years = len(unique_years) if unique_years else (int(ypc.max()) if not ypc.empty else 1)
            _SPARSE_THRESHOLD_YEARS = 3 if expected_years >= 3 else 1

            total_expected = n_countries * expected_years
            total_filled = int(ypc.sum())
            completeness_pct = round(total_filled / total_expected * 100, 1) if total_expected > 0 else 100.0

            sparse = sorted(ypc[ypc < _SPARSE_THRESHOLD_YEARS].index.tolist())

            # Per-country missing years
            all_years_set = set(unique_years)
            per_country_missing: dict[str, list[str]] = {}
            if unique_years:
                for country, grp in _subset.groupby("country"):
                    country_years = set(grp["year"].astype(str).str[:4].unique())
                    missing = sorted(all_years_set - country_years)
                    if missing:
                        per_country_missing[str(country)] = missing

            cq: dict[str, Any] = {
                "total_expected_cells": total_expected,
                "total_filled_cells": total_filled,
                "completeness_pct": completeness_pct,
                "sparse_threshold_years": _SPARSE_THRESHOLD_YEARS,
                "sparse_countries": sparse,
            }
            if per_country_missing:
                cq["missing_years_by_country"] = per_country_missing
            profile["coverage_quality"] = cq
        except Exception:
            pass

    # ── 5.  Temporal trend (single-indicator, temporal data) ───────────────────
    if "year" in df.columns and "value" in df.columns and len(unique_years) >= 2:
        try:
            _df_t = df.copy()
            _df_t["_year_int"] = pd.to_numeric(df["year"].astype(str).str[:4], errors="coerce")
            _df_t["_val"] = pd.to_numeric(df["value"], errors="coerce")
            _df_t = _df_t.dropna(subset=["_year_int", "_val"])

            if not _df_t.empty and _df_t["_year_int"].nunique() >= 2:
                # Aggregate: mean across countries per year
                agg = _df_t.groupby("_year_int")["_val"].mean().sort_index()
                first_yr, last_yr = int(agg.index.min()), int(agg.index.max())
                first_val, last_val = float(agg.iloc[0]), float(agg.iloc[-1])

                pct_change = (
                    round((last_val - first_val) / abs(first_val) * 100, 1)
                    if first_val != 0 else None
                )

                diffs = agg.diff().dropna()
                pos = int((diffs > 0).sum())
                neg = int((diffs < 0).sum())
                if pos == 0 and neg == 0:
                    direction = "flat"
                elif pos >= len(diffs) * 0.75:
                    direction = "increasing"
                elif neg >= len(diffs) * 0.75:
                    direction = "decreasing"
                else:
                    direction = "volatile"

                monotonic = (pos == len(diffs)) or (neg == len(diffs))
                multi_country = "country" in df.columns and df["country"].nunique() > 1

                trend: dict[str, Any] = {
                    "direction": direction,
                    "pct_change_total": pct_change,
                    "monotonic": monotonic,
                }
                if multi_country:
                    trend["note"] = "Averaged across all countries"

                # Per-country directions when countries diverge
                if multi_country:
                    per_country: dict[str, str] = {}
                    for ctry, grp in _df_t.groupby("country"):
                        c_agg = grp.groupby("_year_int")["_val"].mean().sort_index()
                        if c_agg.nunique() < 2:
                            continue
                        c_diffs = c_agg.diff().dropna()
                        c_pos = int((c_diffs > 0).sum())
                        c_neg = int((c_diffs < 0).sum())
                        n = len(c_diffs)
                        if c_pos >= n * 0.75:
                            per_country[str(ctry)] = "increasing"
                        elif c_neg >= n * 0.75:
                            per_country[str(ctry)] = "decreasing"
                        else:
                            per_country[str(ctry)] = "volatile"
                    # Only include per-country if directions differ across countries
                    if len(set(per_country.values())) > 1:
                        trend["per_country"] = per_country

                profile["temporal_trend"] = {k: v for k, v in trend.items() if v is not None}
        except Exception:
            pass

    # ── 6.  Cross-country divergence (multi-country only) ─────────────────────
    if "country" in df.columns and "year" in df.columns and "value" in df.columns:
        try:
            _df_cc = df.copy()
            _df_cc["_val"] = pd.to_numeric(df["value"], errors="coerce")
            _df_cc["_year_int"] = pd.to_numeric(df["year"].astype(str).str[:4], errors="coerce")
            _df_cc = _df_cc.dropna(subset=["country", "_year_int", "_val"])

            if _df_cc["country"].nunique() >= 2 and not _df_cc.empty:
                latest_year = int(_df_cc["_year_int"].max())
                earliest_year = int(_df_cc["_year_int"].min())

                latest_agg = _df_cc[_df_cc["_year_int"] == latest_year].groupby("country")["_val"].mean()
                earliest_agg = _df_cc[_df_cc["_year_int"] == earliest_year].groupby("country")["_val"].mean()

                if len(latest_agg) >= 2:
                    spread_latest = round(float(latest_agg.max() - latest_agg.min()), 4)
                    leader = str(latest_agg.idxmax())
                    laggard = str(latest_agg.idxmin())

                    cc: dict[str, Any] = {
                        "latest_year": str(latest_year),
                        "spread": spread_latest,
                        "leader": {"country": leader, "value": round(float(latest_agg[leader]), 4)},
                        "laggard": {"country": laggard, "value": round(float(latest_agg[laggard]), 4)},
                    }

                    # Convergence: spread narrowing?
                    common = list(set(latest_agg.index) & set(earliest_agg.index))
                    if len(common) >= 2:
                        spread_earliest = float(
                            earliest_agg[common].max() - earliest_agg[common].min()
                        )
                        converging = spread_latest < spread_earliest
                        cc["converging"] = converging
                        cc["spread_note"] = (
                            f"Spread {'narrowed' if converging else 'widened'} from "
                            f"{round(spread_earliest, 2)} ({earliest_year}) to "
                            f"{round(spread_latest, 2)} ({latest_year})"
                        )

                    profile["cross_country"] = cc
        except Exception:
            pass

    # ── 7.  Breakdowns with values, labels, and codelist definitions ───────────
    _trivial: frozenset[str] = frozenset({"_T", "_Z", "U", ""})
    _DIM_MEANINGS: dict[str, str] = {
        "sex": "Gender breakdown",
        "age": "Age group breakdown",
        "urbanisation": "Urban/rural breakdown",
        "residence": "Residence type",
        "comp_breakdown_1": "Indicator subtype",
        "comp_breakdown_2": "Secondary breakdown",
        "comp_breakdown_3": "Tertiary breakdown",
        "unit_measure": "Unit of measurement",
    }
    _DIM_TO_CODELIST: dict[str, str] = {
        "sex": "SEX",
        "age": "AGE",
        "urbanisation": "URBANISATION",
        "residence": "RESIDENCE",
        "comp_breakdown_1": "COMP_BREAKDOWN_1",
        "comp_breakdown_2": "COMP_BREAKDOWN_2",
        "comp_breakdown_3": "COMP_BREAKDOWN_3",
        "unit_measure": "UNIT_MEASURE",
    }
    _cl: Any = None
    try:
        from data360.providers import get_codelist_manager as _get_cl_mgr
        _cl = _get_cl_mgr()
    except Exception:
        pass

    breakdowns: dict[str, dict] = {}
    for dim in _SURFACE_DIMS:
        if dim not in df.columns:
            continue
        vals = sorted(str(v) for v in df[dim].dropna().unique() if str(v) not in _trivial)
        if len(vals) > 1:
            meaning = _DIM_MEANINGS.get(dim, dim.replace("_", " ").title())
            if dim_name_labels and dim in dim_name_labels:
                meaning = dim_name_labels[dim]
            bd_entry: dict[str, Any] = {
                "values": vals,
                "cardinality": len(vals),
                "meaning": meaning,
            }
            if _cl and dim in _DIM_TO_CODELIST:
                try:
                    all_labels = _cl.get_dimension_labels(_DIM_TO_CODELIST[dim])
                    code_vals = [v for v in df[dim].dropna().unique() if str(v) not in _trivial]
                    val_labels = {
                        str(code): all_labels[str(code)]
                        for code in code_vals
                        if str(code) in all_labels and all_labels[str(code)] != str(code)
                    }
                    if val_labels:
                        bd_entry["value_labels"] = val_labels
                        bd_entry["codelist"] = _DIM_TO_CODELIST[dim]
                except Exception:
                    pass
            breakdowns[dim] = bd_entry

    if breakdowns:
        profile["breakdowns"] = breakdowns

    # ── 8.  Composition hint ───────────────────────────────────────────────────
    composition: dict[str, Any] = {"suitable_for_stacked": False}
    if "value" in df.columns:
        val_series = pd.to_numeric(df["value"], errors="coerce").dropna()
        if not val_series.empty:
            composition["all_positive"] = bool((val_series >= 0).all())
            group_cols = [c for c in ("country", "year") if c in df.columns]
            if group_cols and breakdowns:
                try:
                    sums = df.assign(
                        _val=pd.to_numeric(df["value"], errors="coerce")
                    ).groupby(group_cols)["_val"].sum()
                    non_null_sums = sums.dropna()
                    if not non_null_sums.empty:
                        mean_sum = float(non_null_sums.mean())
                        composition["sums_to_100"] = 90 <= mean_sum <= 110
                        if composition["sums_to_100"] and composition["all_positive"]:
                            composition["suitable_for_stacked"] = True
                            composition["reason"] = (
                                f"Values within groups average {mean_sum:.0f} "
                                "— looks like parts summing to ~100%"
                            )
                except Exception:
                    composition["sums_to_100"] = False
            if not composition.get("reason"):
                composition["reason"] = (
                    "Independent values — not parts of a whole"
                    if not composition.get("suitable_for_stacked")
                    else ""
                )
    profile["composition_hint"] = composition
    return profile

def _format_source_line_from_attribution(attrib: dict[str, str]) -> str:
    """One-line \"Source\" string; matches client `formatData360VizSourceLine`."""
    ind_name = (attrib.get("indicator_name") or "").strip()
    ind_id = (attrib.get("indicator_id") or "").strip()

    if ind_name and ind_id and ind_name != ind_id:
        ind = f"{ind_name} ({ind_id})"
    else:
        ind = ind_name or ind_id

    if ind:
        return f"World Bank — {ind}"
    return _SOURCE_FALLBACK


def _format_subtitle_line(
    warning: str | None,
    strategy: str | None,
    reason: str | None,
) -> str | None:
    """Optional subtitle under chart title; matches client `formatData360VizSubtitleLine`."""
    parts: list[str] = []
    if warning:
        parts.append(warning)
    if strategy or reason:
        parts.append(" — ".join(x for x in (strategy or "", reason or "") if x))
    if not parts:
        return None
    return " · ".join(parts)


def _scalar_for_json(value: object) -> object:
    """Coerce pandas/numpy scalars so stdlib json can encode them."""
    if isinstance(value, pd.Timestamp):
        return value.isoformat() if pd.notna(value) else None
    return value


def _sanitize_dataframe_for_json_records(df: pd.DataFrame) -> pd.DataFrame:
    """Ensure ``DataFrame.to_dict(orient='records')`` is JSON-serializable (no Timestamp)."""
    out = df.copy()
    for col in out.columns:
        if pd.api.types.is_datetime64_any_dtype(out[col]):
            out[col] = out[col].map(lambda x: x.isoformat() if pd.notna(x) else None)
        elif out[col].dtype == object:
            out[col] = out[col].map(_scalar_for_json)
    return out


# ============================================================================
# DATA FETCHING
# ============================================================================


async def _fetch_data_internal(url: str) -> pd.DataFrame:
    from urllib.parse import urlparse, parse_qs, urlencode, urlunparse
    client = get_shared_httpx_client()

    parsed = urlparse(url)
    params = parse_qs(parsed.query)

    all_raw_data = []
    has_more = True
    offset = 0

    while has_more:
        params["offset"] = [str(offset)]
        new_query = urlencode(params, doseq=True)
        page_url = urlunparse(parsed._replace(query=new_query))

        response = await client.get(page_url)
        response.raise_for_status()
        data = response.json()
        raw_data = data.get("value", [])
        if not raw_data:
            break
        all_raw_data.extend(raw_data)

        has_more = data.get("has_more", False)
        next_offset = data.get("next_offset")
        if next_offset is not None and next_offset > offset:
            offset = next_offset
        else:
            offset += len(raw_data)

        if len(all_raw_data) >= 1000:
            _logger.warning(
                "_fetch_data_internal: capped at 1000 rows for %s — some data may be truncated.",
                url,
            )
            break

    if not all_raw_data:
        raise ValueError("No data found at the provided URL.")
    return pd.DataFrame(all_raw_data)



async def _fetch_single_indicator(
    database_id: str,
    indicator_id: str,
    country_code: str | None,
    start_year: int | None,
    end_year: int | None,
    disaggregation_filters: dict | None,
) -> tuple[pd.DataFrame, str | None, str | None]:
    """Fetch one indicator and return (DataFrame, title, unit_label).

    Returns (empty_df, None, None) on error — caller checks df.empty.
    """
    from data360.api import get_data_api_url, get_metadata

    try:
        data_url = await get_data_api_url(
            database_id=database_id,
            indicator_id=indicator_id,
            country_code=country_code,
            start_year=start_year,
            end_year=end_year,
            disaggregation_filters=disaggregation_filters,
        )
        df = await _fetch_data_internal(data_url)
        df.columns = [c.lower() for c in df.columns]
        if "urbanisation" in df.columns:
            df = df.rename(columns={"urbanisation": "residence"})
        elif "urbanization" in df.columns:
            df = df.rename(columns={"urbanization": "residence"})

        # Filter out total sentinels if breakdown values exist
        for col in ["sex", "age", "residence", "comp_breakdown_1", "comp_breakdown_2", "comp_breakdown_3"]:
            if col in df.columns:
                uv = df[col].dropna().unique()
                if len(uv) > 1 and "_T" in uv:
                    df = df[df[col] != "_T"].copy()
    except Exception as e:
        _logger.error(f"Failed to fetch {indicator_id}: {e}")
        return pd.DataFrame(), None, None

    # Fetch title + unit from metadata
    title, unit = None, None
    try:
        parsed = urlparse(data_url)
        params = parse_qs(parsed.query)
        db_id = params.get("DATABASE_ID", [database_id])[0]
        ind_id = (
            params.get("indicatorId", [None])[0]
            or params.get("INDICATOR", [None])[0]
            or indicator_id
        )
        meta = await get_metadata(db_id, ind_id)
        if meta and meta.indicator_metadata:
            title = meta.indicator_metadata.get("name")
            unit = meta.indicator_metadata.get(
                "measurement_unit"
            ) or meta.indicator_metadata.get("unit_measure")
    except Exception as e:
        _logger.warning(f"Could not fetch metadata for {indicator_id}: {e}")

    # Qualify unit using unit_mult if present
    raw_unit = ""
    raw_unit_mult = 0
    if not df.empty:
        if "unit_measure" in df.columns:
            _units = df["unit_measure"].dropna().unique().tolist()
            if len(_units) == 1:
                raw_unit = _units[0]
        if "unit_mult" in df.columns:
            _mults = df["unit_mult"].dropna().unique().tolist()
            if len(_mults) == 1:
                try:
                    raw_unit_mult = int(_mults[0])
                except (ValueError, TypeError):
                    pass

    try:
        from data360.providers import get_codelist_manager
        _cl_mgr = get_codelist_manager()
        await _cl_mgr._ensure_extdataportal_loaded()

        resolved_label = ""
        if raw_unit:
            _resolved = _cl_mgr.get_label("UNIT_MEASURE", raw_unit)
            resolved_label = _resolved if _resolved != raw_unit else ""

        unit_name = resolved_label or unit or raw_unit

        from data360.api import _qualify_unit_name
        has_mapping = bool(resolved_label or unit)
        is_special_ps = bool(raw_unit and raw_unit.upper() == "PS" and raw_unit_mult > 0)
        if has_mapping or is_special_ps:
            unit = _qualify_unit_name(unit_name, raw_unit_mult, raw_unit)
        else:
            unit = None

    except Exception as e:
        _logger.warning(f"Could not qualify unit for {indicator_id}: {e}")

    return df, title, unit


# ============================================================================
# DATA CLEANING HELPERS
# ============================================================================


def _clean_single_df(
    data: pd.DataFrame,
    relevant_fields: list[str] | None,
    chart_type: str | None,
    data_frequency: str | None,
) -> tuple[pd.DataFrame, list[str], viz_config.TemporalFreq]:
    if "ref_area" in data.columns:
        try:
            from data360.providers import get_group_hierarchy_manager
            _ghm = get_group_hierarchy_manager()
            has_leaf = data["ref_area"].apply(lambda x: _ghm.is_country(str(x))).any()
            if has_leaf:
                data = data[data["ref_area"].apply(lambda x: not _ghm.is_group(str(x)))]
        except Exception as e:
            _logger.warning(f"Could not filter FMR leaf economies: {e}")

    # Trivial values for disaggregation dimensions: _T = aggregate total, _Z = not applicable.
    # unit_measure uses a different sentinel: 'U' = Unitless (defined in _UNIT_MEASURE_TRIVIAL).
    _TRIVIAL_DIM_VALUES = ("_T", "_Z")

    def _is_non_trivial(col_name: str, series: pd.Series) -> bool:
        """Return True when a dimension column carries discriminating values."""
        uv = series.unique()
        if col_name == "unit_measure":
            non_trivial = set(uv) - _UNIT_MEASURE_TRIVIAL
            return len(non_trivial) > 1
        return len(uv) > 1 or (len(uv) == 1 and uv[0] not in _TRIVIAL_DIM_VALUES)

    if relevant_fields:
        req = [f.lower() for f in relevant_fields]
        missing = [f for f in req if f not in data.columns]
        if missing:
            raise ValueError(
                f"Requested fields not found: {missing}. Available: {list(data.columns)}"
            )
        valid_cols = req
        for dim in ["ref_area", *_VIZ_DISAGG_DIMS]:
            if dim in data.columns and dim not in valid_cols:
                if _is_non_trivial(dim, data[dim]):
                    valid_cols.append(dim)
        viz_data = data[valid_cols].copy()
        relevant_cols = valid_cols
    else:
        relevant_cols = []
        for col in ["time_period", "obs_value", "ref_area"]:
            if col in data.columns:
                relevant_cols.append(col)
        for dim in _VIZ_DISAGG_DIMS:
            if dim in data.columns:
                if _is_non_trivial(dim, data[dim]):
                    relevant_cols.append(dim)
        viz_data = data[relevant_cols].copy() if relevant_cols else data.copy()

    # Temporal preparation — use API's FREQ code directly; fall back to
    # inference from time_period values only when FREQ is unavailable.
    _FREQ_TO_TEMPORAL: dict[str, viz_config.TemporalFreq] = {
        "A": "annual",
        "M": "monthly",
        "Q": "quarterly",
        "D": "daily",
    }
    temporal_frequency: viz_config.TemporalFreq = "annual"
    if "time_period" in viz_data.columns:
        try:
            # Prefer the FREQ code from the API (already parsed by the caller).
            if data_frequency and data_frequency.upper() in _FREQ_TO_TEMPORAL:
                temporal_frequency = _FREQ_TO_TEMPORAL[data_frequency.upper()]
            else:
                # Fallback: infer from the actual time_period values.
                temporal_frequency = viz_config._detect_temporal_frequency(
                    viz_data["time_period"]
                )
            viz_data["time_period"] = viz_config._format_time_period_series(
                viz_data["time_period"], temporal_frequency
            )
        except Exception as e:
            _logger.warning(f"time_period conversion failed: {e}")
            try:
                viz_data["time_period"] = pd.to_datetime(
                    viz_data["time_period"]
                ).dt.year.astype(str)
            except Exception:
                pass

    if "obs_value" in viz_data.columns:
        viz_data["obs_value"] = pd.to_numeric(viz_data["obs_value"], errors="coerce")

    # Rename to friendly names
    viz_data = viz_data.rename(
        columns={"time_period": "year", "obs_value": "value", "ref_area": "country"}
    )
    relevant_cols = [
        {"time_period": "year", "obs_value": "value", "ref_area": "country"}.get(c, c)
        for c in relevant_cols
    ]
    if "value" in viz_data.columns:
        viz_data["value"] = pd.to_numeric(viz_data["value"], errors="coerce")
    return viz_data, relevant_cols, temporal_frequency


# Inverse rename map: viz_data friendly column names → raw API column names.
# Used by _resolve_hidden_dimension to identify which raw columns are already
# represented in the cleaned DataFrame.
_VIZ_TO_RAW_NAMES: dict[str, str] = {
    "year": "time_period",
    "value": "obs_value",
    "country": "ref_area",
}


def _resolve_hidden_dimension(
    viz_data: pd.DataFrame,
    raw_data: pd.DataFrame,
    max_cardinality: int = 8,
) -> tuple[pd.DataFrame, str | None]:
    """Detect and resolve duplicate (key → value) rows in viz_data.

    After ``_clean_single_df``, columns present in the raw API data that lie
    outside ``_VIZ_DISAGG_DIMS`` are silently dropped.  When such a column has
    ``n_unique > 1`` it creates rows that share the same visualization key
    (year × country × breakdowns) but carry different values, producing a
    sawtooth / zigzag pattern in line and bar charts.

    Resolution strategy
    -------------------
    1. **Surface**: if a hidden dimension is found with cardinality
       ≤ *max_cardinality*, add it to ``viz_data`` under the first unused
       ``comp_breakdown_N`` slot (N ∈ {1, 2, 3}) so that ``select_strategy``
       naturally routes it as a chart breakdown dimension.
    2. **Aggregate**: if no suitable hidden dimension exists, collapse
       duplicate rows to their row-wise mean and return a warning string that
       the caller appends to the chart subtitle.

    Parameters
    ----------
    viz_data :
        DataFrame produced by ``_clean_single_df`` (columns: year, value,
        country, and any breakdown dims already surfaced).
    raw_data :
        Original API response DataFrame — all columns present before cleaning.
    max_cardinality :
        Maximum number of unique values a hidden dimension may have to be
        surfaced as a chart series.  Higher-cardinality dims are aggregated.

    Returns
    -------
    (resolved_df, warning_message_or_None)
        *warning_message* is ``None`` when no duplicates were found.
    """
    key_cols = [c for c in viz_data.columns if c != "value"]

    # Fast path: no duplicates — nothing to resolve.
    if not viz_data.duplicated(subset=key_cols).any():
        return viz_data, None

    # Build the set of raw column names already represented in viz_data so we
    # can find truly hidden columns (present in raw_data but absent in viz_data).
    raw_names_in_viz: set[str] = {_VIZ_TO_RAW_NAMES.get(c, c) for c in viz_data.columns}

    # Candidate hidden dimensions: in raw_data, not in viz_data, 1 < n_unique ≤ max_cardinality.
    candidates: list[tuple[str, int]] = []
    for col in raw_data.columns:
        if col in raw_names_in_viz:
            continue
        try:
            n_u = raw_data.loc[viz_data.index, col].dropna().nunique()
        except (KeyError, IndexError):
            continue
        if 1 < n_u <= max_cardinality:
            candidates.append((col, n_u))

    # Prefer the dimension with the smallest cardinality (fewer panels / series).
    candidates.sort(key=lambda x: x[1])

    if candidates:
        hidden_col, hidden_n = candidates[0]
        # Assign to the first comp_breakdown_N slot not already used in viz_data.
        target_slot: str | None = None
        for slot in ("comp_breakdown_1", "comp_breakdown_2", "comp_breakdown_3"):
            if slot not in viz_data.columns:
                target_slot = slot
                break

        if target_slot is not None:
            viz_data = viz_data.copy()
            viz_data[target_slot] = raw_data.loc[viz_data.index, hidden_col].values
            _logger.info(
                "Hidden dimension '%s' (%d values) surfaced as '%s'.",
                hidden_col,
                hidden_n,
                target_slot,
            )
            return viz_data, (
                f"Additional dimension '{hidden_col}' ({hidden_n} values) "
                f"detected in the source data and surfaced as a chart series."
            )

    # Fallback: aggregate duplicate rows to their mean value.
    n_before = len(viz_data)
    viz_data = viz_data.groupby(key_cols, sort=False)["value"].mean().reset_index()
    n_collapsed = n_before - len(viz_data)
    dim_hint = f" (hidden dimension: '{candidates[0][0]}'" if candidates else ""
    _logger.warning(
        "Collapsed %d duplicate rows to row-wise mean%s.",
        n_collapsed,
        (
            f" — hidden dim '{candidates[0][0]}' had too many values"
            if candidates
            else ""
        ),
    )
    return viz_data, (
        f"Note: {n_collapsed} duplicate data rows were averaged"
        + (f" over hidden dimension '{candidates[0][0]}'." if candidates else ".")
    )


async def _map_country_codes(viz_data: pd.DataFrame) -> pd.DataFrame:
    """Map REF_AREA / country codes to human-readable names."""
    col = "country" if "country" in viz_data.columns else None
    if col is None:
        return viz_data
    try:
        from data360.providers import get_codelist_mapping

        country_map = await get_codelist_mapping("REF_AREA")
        viz_data[col] = viz_data[col].map(lambda x: country_map.get(x, x))
    except Exception as e:
        _logger.warning(f"Could not map country codes: {e}")
    return viz_data


# Dimensions resolved automatically from the extdataportal bundle.
# Maps DataFrame column name → extdataportal dimension key.
_EXTDATAPORTAL_DIM_MAP: dict[str, str] = {
    "comp_breakdown_1": "COMP_BREAKDOWN_1",
    "comp_breakdown_2": "COMP_BREAKDOWN_2",
    "comp_breakdown_3": "COMP_BREAKDOWN_3",
    "sex": "SEX",
    "age": "AGE",
    "urbanisation": "URBANISATION",
    "residence": "URBANISATION",
    "unit_measure": "UNIT_MEASURE",
}


async def _map_dimension_codes(viz_data: pd.DataFrame) -> pd.DataFrame:
    """Replace raw dimension codes with human-readable labels from extdataportal.

    Resolves COMP_BREAKDOWN_1/2/3, SEX, AGE, URBANISATION, and UNIT_MEASURE
    columns using the extdataportal codelist (fetched lazily on first call,
    same as REF_AREA). Columns absent from the DataFrame are silently skipped.
    Codes not found are left unchanged (graceful fallback).

    Must be called *before* the ``series_labels`` override so that LLM-supplied
    labels can still take precedence over auto-resolved ones.
    """
    try:
        from data360.providers import get_codelist_manager

        manager = get_codelist_manager()
        # Lazy-load the extdataportal mapping on first use (no startup hook needed).
        await manager._ensure_extdataportal_loaded()
        for col, dim in _EXTDATAPORTAL_DIM_MAP.items():
            if col not in viz_data.columns:
                continue
            lookup = manager.get_dimension_labels(dim)
            if lookup:
                viz_data[col] = viz_data[col].map(lambda x, lu=lookup: lu.get(x, x))
    except Exception as exc:
        _logger.warning("Could not auto-resolve dimension codes: %s", exc)
    return viz_data


def _find_common_prefix(strings: list[str]) -> str:
    """Return the longest string that is a prefix of every element in *strings*."""
    if not strings:
        return ""
    prefix = strings[0]
    for s in strings[1:]:
        while not s.startswith(prefix):
            prefix = prefix[:-1]
            if not prefix:
                return ""
    return prefix


def _strip_common_prefix_in_dims(
    df: pd.DataFrame,
    dim_cols: list[str],
    min_prefix_len: int = 15,
) -> pd.DataFrame:
    """Strip a long shared prefix from each comp_breakdown_* column.

    When all values in a column share a long common prefix (e.g.
    ``"Severity Phase of Acute Food Insecurity or Malnutrition : "``), the
    prefix carries no information and Vega-Lite labels become indistinguishable
    after truncation.  This function strips the prefix in-place and leaves only
    the unique suffix (e.g. ``"Phase 1 - Minimal"``).

    The ``series_labels`` override runs *after* this step, so the LLM can
    further customise labels for heterogeneous cases (e.g. WGI breakdowns).

    *min_prefix_len* guards against spurious stripping when a short coincidental
    prefix exists (default 15 chars).
    """
    for col in dim_cols:
        if col not in df.columns:
            continue
        unique_vals = [v for v in df[col].dropna().unique() if isinstance(v, str)]
        if len(unique_vals) < 2:
            continue
        prefix = _find_common_prefix(unique_vals)
        if prefix and prefix[-1].isalnum():
            # Find the last space or punctuation character to avoid cutting a word in half
            match = re.search(r'[^a-zA-Z0-9][a-zA-Z0-9]+$', prefix)
            if match:
                prefix = prefix[:match.start() + 1]
        if len(prefix) < min_prefix_len:
            continue
        # Strip trailing separator characters so suffixes start cleanly.
        stripped_prefix = prefix.rstrip(": -_/\\ ")
        if not stripped_prefix:
            continue
        sep_len = len(prefix) - len(stripped_prefix)
        cut = len(stripped_prefix) + sep_len  # includes trailing separator(s)
        mapping = {v: v[cut:].lstrip(": -_/\\ ") for v in unique_vals}
        df = df.copy()
        df[col] = df[col].map(
            lambda x, m=mapping: m.get(x, x) if isinstance(x, str) else x
        )
        _logger.debug(
            "Stripped common prefix %r from column %r (%d values)",
            stripped_prefix,
            col,
            len(unique_vals),
        )
    return df


def _slugify(name: str) -> str:
    """Convert indicator name to a safe column name."""
    s = name.lower().strip()
    s = re.sub(r"[^a-z0-9]+", "_", s)
    return s[:40].strip("_") or "indicator"


def _make_unique_col(base: str, existing: set[str]) -> str:
    col, n = base, 1
    while col in existing:
        col = f"{base}_{n}"
        n += 1
    return col


# ============================================================================
# TOOL: get_supported_chart_types
# ============================================================================


def get_supported_chart_types() -> str:
    """Return supported chart types and their data requirements as JSON.

    Call before data360_get_viz_spec or data360_get_multi_indicator_viz_spec
    to choose the right chart_type for the data and user intent.

    Returns:
        JSON string with chart_types list and strategy guidance.
    """
    chart_types = {
        "chart_types": [
            {
                "id": "line",
                "description": "Line chart for temporal trends.",
                "when_to_use": "Single indicator, 1+ countries, multiple years.",
                "data_requirements": "time_period + obs_value. Color-codes countries automatically.",
            },
            {
                "id": "bar",
                "description": "Horizontal bar chart for ranking/comparison.",
                "when_to_use": "Single indicator, multiple countries, typically one year.",
                "data_requirements": "obs_value + country dimension.",
            },
            {
                "id": "scatter",
                "description": "Scatterplot for correlation between two indicators.",
                "when_to_use": "Exactly 2 indicator_ids, multiple countries, single year.",
                "data_requirements": "Requires indicator_ids list with 2 entries in get_multi_indicator_viz_spec.",
            },
            {
                "id": "connected_scatter",
                "description": "Connected scatterplot: 2 indicators over time.",
                "when_to_use": "Exactly 2 indicator_ids, multiple countries, multiple years.",
                "data_requirements": "Same as scatter but multi-year.",
            },
            {
                "id": "layered_lines",
                "description": "Dual/multi-axis line chart for 2-3 indicators in one country.",
                "when_to_use": "2-3 indicator_ids, typically 1 country, multi-year.",
                "data_requirements": "Requires indicator_ids list in get_multi_indicator_viz_spec.",
            },
            {
                "id": "small_multiples",
                "description": "Faceted panel chart for breakdown × country comparisons.",
                "when_to_use": "1 indicator, multiple breakdowns (sex/age) or many countries.",
                "data_requirements": "Disaggregation dimensions with multiple values.",
            },
            {
                "id": "strip",
                "description": "Strip/beeswarm chart for cross-country distribution.",
                "when_to_use": ">8 countries, single year.",
                "data_requirements": "obs_value + many country values.",
            },
            {
                "id": "area",
                "description": "Stacked area chart for part-to-whole composition over time.",
                "when_to_use": "User asks about composition, share, or breakdown over time.",
                "data_requirements": "Requires multiple breakdown series or indicator_ids that sum to a whole.",
            },
            {
                "id": "heatmap",
                "description": "Heatmap matrix for high-cardinality time series.",
                "when_to_use": ">8 countries over multiple years without breakdowns.",
                "data_requirements": "Automatically selected for dense country x year data.",
            },
            {
                "id": "map",
                "description": "Geographic choropleth map.",
                "when_to_use": "User asks for a map or spatial distribution across countries.",
                "data_requirements": "Single indicator across multiple countries.",
            },
        ],
        "multi_indicator_note": (
            "For scatter, connected_scatter, and layered_lines, use "
            "data360_get_multi_indicator_viz_spec with an indicator_ids list."
        ),
    }
    return json.dumps(chart_types, indent=2)


# ============================================================================
# TOOL: get_viz_spec (single indicator — original interface preserved)
# ============================================================================


async def _detect_missing_countries(country_code: str | None, present_countries: set[str]) -> list[str]:
    """Identify which of the requested country codes are missing from the returned set."""
    if not country_code:
        return []
    requested_list = [c.strip().upper() for c in country_code.replace(";", ",").split(",") if c.strip()]
    if not requested_list:
        return []
    try:
        from data360.providers import get_codelist_mapping
        country_map = await get_codelist_mapping("REF_AREA")
    except Exception:
        country_map = {}

    missing_names = []
    present_upper = {str(c).upper() for c in present_countries}
    for code in requested_list:
        name = country_map.get(code, code)
        if code not in present_upper and name.upper() not in present_upper:
            missing_names.append(name)
    return missing_names


def _apply_post_processing_rules(
    spec: dict,
    data_frequency: str | None,
    unit_measure: str | None,
    strategy_result: "viz_config.StrategyResult",
    df: pd.DataFrame
) -> dict:
    """Recursively applies post-processing rules to all sub-views/panels in a Vega-Lite spec."""
    import inspect

    def _apply_rule_recursively(rule, subspec, data_root=None, is_composite=False, **kwargs):
        if not isinstance(subspec, dict):
            return subspec

        # Structural rules must be run at the top-level
        is_structural = rule.name in ("general_error_band", "population_pyramid", "apply_wb_style")
        if is_structural:
            sig = inspect.signature(rule.apply)
            rule_kwargs = dict(kwargs)
            if "is_composite" in sig.parameters:
                rule_kwargs["is_composite"] = is_composite
            return rule.apply(subspec, data_frequency=data_frequency, unit_measure=unit_measure, **rule_kwargs)

        # For rules like line_year_gap_stroke_dash, if we are at a panel root (has layer or spec),
        # apply the rule directly here so it can handle the layer/spec structure and data localization.
        if rule.name == "line_year_gap_stroke_dash" and ("layer" in subspec or "spec" in subspec):
            sig = inspect.signature(rule.apply)
            rule_kwargs = dict(kwargs)
            if "is_composite" in sig.parameters:
                rule_kwargs["is_composite"] = is_composite
            return rule.apply(subspec, data_frequency=data_frequency, unit_measure=unit_measure, **rule_kwargs)

        current_data_root = data_root if data_root is not None else subspec

        # Recurse into composite views
        if "concat" in subspec and isinstance(subspec["concat"], list):
            subspec["concat"] = [
                _apply_rule_recursively(rule, child, current_data_root, is_composite=True, **kwargs)
                for child in subspec["concat"]
            ]
            return subspec
        elif "vconcat" in subspec and isinstance(subspec["vconcat"], list):
            subspec["vconcat"] = [
                _apply_rule_recursively(rule, child, current_data_root, is_composite=True, **kwargs)
                for child in subspec["vconcat"]
            ]
            return subspec
        elif "concat" in subspec and isinstance(subspec["concat"], list):
            subspec["concat"] = [
                _apply_rule_recursively(rule, child, current_data_root, is_composite=True, **kwargs)
                for child in subspec["concat"]
            ]
            return subspec
        elif "hconcat" in subspec and isinstance(subspec["hconcat"], list):
            subspec["hconcat"] = [
                _apply_rule_recursively(rule, child, current_data_root, is_composite=True, **kwargs)
                for child in subspec["hconcat"]
            ]
            return subspec
        elif "layer" in subspec and isinstance(subspec["layer"], list):
            subspec["layer"] = [
                _apply_rule_recursively(rule, child, current_data_root, is_composite=is_composite, **kwargs)
                for child in subspec["layer"]
            ]
            return subspec
        elif "spec" in subspec and isinstance(subspec["spec"], dict):
            subspec["spec"] = _apply_rule_recursively(rule, subspec["spec"], current_data_root, is_composite=is_composite, **kwargs)
            return subspec

        # Leaf view: temporarily inject data/datasets from root if missing
        has_local_data = "data" in subspec
        if not has_local_data and current_data_root and "data" in current_data_root:
            subspec["data"] = current_data_root["data"]
        if not has_local_data and current_data_root and "datasets" in current_data_root:
            subspec["datasets"] = current_data_root["datasets"]

        # Apply the rule
        sig = inspect.signature(rule.apply)
        rule_kwargs = dict(kwargs)
        if "is_composite" in sig.parameters:
            rule_kwargs["is_composite"] = is_composite
        subspec = rule.apply(subspec, data_frequency=data_frequency, unit_measure=unit_measure, **rule_kwargs)

        # Cleanup injected data if it wasn't modified (still references parent data/datasets)
        if not has_local_data:
            if current_data_root and subspec.get("data") is current_data_root.get("data"):
                subspec.pop("data", None)
            if current_data_root and subspec.get("datasets") is current_data_root.get("datasets"):
                subspec.pop("datasets", None)


        return subspec


    for rule in viz_config.POST_PROCESSING_RULES:
        sig = inspect.signature(rule.apply)
        kwargs = {}
        if "scale_type" in sig.parameters:
            kwargs["scale_type"] = strategy_result.scale_type
        if "unit_mult" in sig.parameters:
            kwargs["unit_mult"] = strategy_result.unit_mult
        if "df" in sig.parameters:
            kwargs["df"] = df
        if "raw_hint" in sig.parameters:
            kwargs["raw_hint"] = strategy_result.raw_hint

        spec = _apply_rule_recursively(rule, spec, **kwargs)

    return spec


async def get_viz_spec(
    database_id: str,
    indicator_id: str,
    country_code: str | None = None,
    start_year: int | None = None,
    end_year: int | None = None,
    disaggregation_filters: dict[str, str | None] | None = None,
    chart_type: str | None = None,
    relevant_fields: list[str] | None = None,
    custom_constraints: list[str] | None = None,
    use_default_constraints: bool = True,
    chart_title: str | None = None,
    series_labels: dict[str, str] | None = None,
    strategy_override: str | None = None,
    charts_api_url_override: object = _UNSET,
) -> VizResult:
    """Generate a Vega-Lite chart from a single Data360 indicator.

    Use when the user wants a visualization for ONE indicator. For comparing multiple
    indicators (scatter, dual-axis), use data360_get_multi_indicator_viz_spec instead.

    ## Grammar of Graphics Contract

    The pipeline maps data dimensions to Vega-Lite aesthetic channels automatically
    (color, facet). DO NOT pre-filter a dimension to simplify the chart — that collapses
    multi-series data into a single undifferentiated line and silently discards information.

    ### disaggregation_filters decision rule

    Only pin a dimension when the user has explicitly requested a specific value:

      CORRECT — user asked for females only:
        disaggregation_filters={"SEX": "F"}

      CORRECT — force aggregate totals (suppress breakdown display):
        disaggregation_filters={"SEX": "_T"}

      CORRECT — dimension is not applicable for this indicator:
        disaggregation_filters={"SEX": "_Z"}

      WRONG — do not pre-filter to reduce chart complexity:
        disaggregation_filters={"COMP_BREAKDOWN_1": "WGI_EST"}  # silently drops other series
        disaggregation_filters={"SEX": "_T"}

    When a dimension has multiple meaningful values and the user has not requested a
    specific one, OMIT it from disaggregation_filters entirely. The pipeline will:
      - detect non-trivial values (anything other than "_T" or "_Z")
      - choose the correct Vega-Lite channel based on data shape (see chart strategy table)

    ### UNIT_MEASURE must never be pre-filtered

    UNIT_MEASURE is a first-class dimension. When an indicator has multiple units
    (e.g. Persons + Percentage, or USD + % of GDP), the pipeline automatically
    produces a separate panel for each unit with independent Y-axes. Pre-filtering
    to one unit (e.g. UNIT_MEASURE="PERSONS") silently discards the other unit and
    produces a misleading single-unit chart.

      WRONG — do not pick a "preferred" unit:
        disaggregation_filters={"UNIT_MEASURE": "PERSONS"}   # drops Percentage panel
        disaggregation_filters={"UNIT_MEASURE": "PT"}        # drops Persons panel

      CORRECT — omit UNIT_MEASURE entirely:
        disaggregation_filters={}   # pipeline creates one panel per unit automatically

    Only pin UNIT_MEASURE when the USER explicitly says "show me only percentages" or
    "I only want the persons count". In that case, pass the exact code from the
    disaggregation response (e.g. "PERSONS", "PT", "USD").

    ### Chart strategy → Vega-Lite encoding table

    The pipeline selects a strategy from the shape of the data after filtering. All
    strategies bypass Draco and produce validated Vega-Lite v5 specs directly.

    Strategy              | When selected                                       | Vega-Lite encoding
    ----------------------|-----------------------------------------------------|-------------------------------------------
    TEMPORAL_SINGLE       | multi-year, ≤8 countries, 0-1 breakdowns            | x=year(temporal), y=value(Q), color=country or breakdown(N)
    CROSS_SECTIONAL       | single year, ≤8 countries, no breakdown             | y=country(N) sorted, x=value(Q) (horizontal bar)
    DISTRIBUTION          | single year, >8 countries                           | x=value(Q), y=country(N) (tick/strip chart)
    BREAKDOWN_COMPARISON  | 1 breakdown, single year, ≤4 countries              | x=country(N), xOffset=breakdown(N), y=value(Q) (grouped bar)
    SMALL_MULTIPLES       | breakdown + >1 country, OR 2+ breakdowns            | facet=country(N), color=breakdown(N), x=year(temporal), y=value(Q)
    TEMPORAL_SINGLE*      | 1 breakdown (any dim), multi-year                   | x=year(temporal), y=value(Q), color=breakdown(N) (multi-series line)
    HEATMAP               | >8 countries, multi-year, 0 breakdowns              | x=year(temporal), y=country(N), color=value(Q) (rect grid)
    STACKED_AREA          | chart_type="area"/"stacked_area", composition Q     | x=year(temporal), y=value(Q) stacked, color=breakdown(N)
    FALLBACK_LINE         | unclassified shapes                                 | x=year(temporal), y=value(Q), color=country(N)

    *When comp_breakdown_1/2 has multiple values and year_count > 1, the pipeline routes
    to TEMPORAL_SINGLE with color=comp_breakdown_1 — producing one colored line per
    breakdown series. This is the correct encoding for WGI, sectoral breakdowns, etc.

    ### High-cardinality and high-dimensionality guidance

    DO NOT reduce country_code or disaggregation_filters to simplify the output.
    The pipeline handles high-cardinality data automatically:

    | Data shape                              | Pipeline action                              |
    |-----------------------------------------|----------------------------------------------|
    | >8 countries, multi-year, 0 breakdowns  | Auto-routes to HEATMAP (rect grid)           |
    | >8 countries + breakdown                | SMALL_MULTIPLES, caps at 6 facets            |
    | Single year, >8 countries               | DISTRIBUTION (strip chart), caps at 20 bars  |
    | ≤8 countries + breakdown, multi-year    | TEMPORAL_SINGLE with colored lines per series|

    If the LLM passes 3 countries when the user asked for all Sub-Saharan Africa,
    the pipeline cannot recover the missing data — it will produce a misleading chart.
    Always pass the full country list and let the pipeline decide the correct strategy.

    ### Visual channel hierarchy (perception accuracy)

    Channels are ranked by how accurately viewers read encoded values:

    Channel           | Type        | Used for
    ------------------|-------------|----------------------------------------
    Position (x/y)    | Highest     | Quantitative values, time axis
    Color (hue)       | Medium      | Nominal categories (country, breakdown)
    Color (lightness) | Medium      | Quantitative gradient (heatmap cells)
    Size              | Medium-low  | Not used in current strategies
    Shape             | Low         | Not used in current strategies

    The pipeline always maps `value` → position (y-axis) and `country`/`breakdown` → color
    (hue). Do not override this — a chart that encodes values as colors (instead of y) is
    strictly less readable, except in heatmaps where a 2D spatial grid makes position
    unavailable for both axes simultaneously.

    ### Encoding type rules (Vega-Lite v5)

    - Year fields: always type="temporal", format="%Y". Do not use "ordinal" for years
      — ordinal maps ISO timestamps to raw millisecond integers on the axis.
    - Country / breakdown fields: type="nominal"
    - Numeric values: type="quantitative"
    - Legend titles: derived from _TOOLTIP_SPECS labels (e.g. "Breakdown", not
      "Comp_Breakdown_1").
    - Color scale: WB categorical palette (9 colors). Gender data uses WB_GENDER_COLORS.
    - Heatmap color scale: sequential ("yellowgreenblue") for positive-only data;
      divergent ("redblue") when data contains negative values.

    Args:
        database_id: Database identifier (e.g., WB_HNP, WB_WDI).
        indicator_id: Indicator ID (e.g., WB_HNP_SP_POP_TOTL).
        country_code: Optional ISO code(s) for REF_AREA — one code, or several separated
            by semicolons (e.g. KEN or CHN;USA, matching required_country style) or commas
            (also accepted). Normalized to comma-separated for the Data API.
        start_year: Optional start year (inclusive).
        end_year: Optional end year (inclusive).
        disaggregation_filters: Optional dimension filters; each value is str or null, not a list.
            Only pin a dimension when the user explicitly requested a specific value. Follow
            these exact rules for pre-filtering:

            1. COMP_BREAKDOWN_1/2/3: NEVER PRE-FILTER. These contain structural components
               (like estimates vs standard errors). Pass them entirely unfiltered and
               let the visualization engine map them to the correct panels/colors.
            2. Demographic Dimensions (SEX, AGE, URBANISATION, EDUCATION): Pre-filter to
               the aggregate total ("_T") by default unless the user explicitly asks for
               a breakdown (e.g., "by sex"). If no "_T" exists, leave unfiltered.
            3. UNIT_MEASURE: NEVER PRE-FILTER. The pipeline automatically creates
               vertically-stacked panels for each unit of measure.

            For REF_AREA use comma-separated ISO codes (e.g. 'KEN,TZA');
            semicolons in REF_AREA are normalized to commas.
        chart_type: Optional hint — "line", "bar", "scatter", "strip", "small_multiples",
            "map", "choropleth", "heatmap", "area", "stacked_area". Use to force a specific chart family.

            ## chart_type decision rule

            Only pass "area" or "stacked_area" when the user's question implies
            **composition or share over time** — i.e., the series together add up to a
            meaningful whole. Typical signal words: "breakdown of", "composition of",
            "share of", "proportion", "what portion", "structure of".

            CORRECT — user asked about composition:
              chart_type="stacked_area"   # "Show the age group breakdown in Japan"
              chart_type="area"           # "What is the composition of electricity by source?"

            WRONG — user asked about a trend:
              chart_type="area"           # "How has GDP changed in Africa?"   ← use default
              chart_type="stacked_area"   # "What is the trend of poverty?"    ← use default

            For trend questions ("how has X changed?", "show the evolution of Y"), omit
            chart_type entirely and let the pipeline choose the correct strategy
            (line chart, heatmap, etc.) based on data shape.
        relevant_fields: Optional list of column names to include in the chart.
        custom_constraints: Deprecated legacy field; ignored by strategy-based specs.
        use_default_constraints: If True (default), apply standard encoding heuristics.
        chart_title: You MUST provide a custom, human-readable chart title here (e.g., 'Male vs. Female Unemployment'). Do not include chart types (e.g. 'Heatmap', 'Line Chart') in the title, as the strategy engine may override the requested chart type based on data cardinality. Do not leave this blank.
        series_labels: Optional. A dictionary mapping raw dimension codes or
            auto-resolved labels to short, human-readable names for legends and
            panel titles (e.g., {"WGI_EST": "Estimate", "WGI_SC": "Score"}).

            The pipeline already handles two cases automatically:
            - Code → label resolution: COMP_BREAKDOWN, SEX, AGE, URBANISATION,
              UNIT_MEASURE codes are resolved to full labels via the extdataportal
              codelist (e.g. "IPC_IPC_PHASE1" → full phase name).
            - Common-prefix stripping: when all values in a dimension share a
              long common prefix, the prefix is stripped automatically, leaving
              only the unique suffix (e.g. all IPC phase labels share
              "Severity Phase of Acute Food Insecurity or Malnutrition : " —
              it is stripped so the legend shows "Phase 1 - Minimal" etc.).

            Use series_labels only when the auto-resolved labels are still too
            long or unclear after prefix stripping — typically for heterogeneous
            dimensions like WGI breakdowns where each label describes a
            structurally different metric (e.g. "Standard error of the governance
            estimate" → "Std Error", "Governance score (0-100)" → "Score").

    Returns:
        Dict with the following fields:

        url (str | None): Browser-accessible URL to the rendered Vega-Lite spec. None on error.
        error (str | None): Error message. None on success.
        strategy (str): Strategy selected by the pipeline (e.g. "small_multiples", "temporal_single").
        reason (str): Human-readable explanation of why that strategy was chosen.
        dimensions (dict[str, list[str]] | None): Non-trivial categorical dimensions present
            in the plotted data, each mapped to its distinct resolved values (human-readable labels,
            not raw codes). Example: {"unit_measure": ["Persons", "Percentage"],
            "comp_breakdown_2": ["Phase 1 - Minimal", "Phase 2 - Stressed"]}.

            When present, narrate what was plotted and how each dimension was encoded
            (color, facet, or filtered). Then offer the user the option to re-call with
            disaggregation_filters pinned to a specific value if they want to focus on
            one breakdown (e.g. {"UNIT_MEASURE": "PT"} for percentage only).

            The pipeline handles encoding automatically per Grammar of Graphics rules —
            dimensions here are informational so you can explain the chart, not instructions
            to re-encode manually.
        data_summary (dict | None): Structural summary of the plotted data:
            - shape: [n_rows, n_cols] — total observations and columns in the chart data.
            - year_range: [first_year, last_year] as strings.
            - countries: sorted list of country names in the chart.
            - value.min / value.max: numeric range of the plotted values (4 decimal places).
            - value.has_negatives: True if the value axis includes negative numbers.
            Use this to narrate scale, scope, and coverage (e.g. "data spans 2000–2022
            across 3 countries, values ranging from 0.2% to 87.4%").
        source_line (str): Formatted attribution string.
        subtitle_line (str | None): Subtitle with strategy and warning info for the chart.
    """
    from data360.api import get_data_api_url, get_disaggregation, get_metadata

    # ── Detect "expand" dimensions ─────────────────────────────────────────────
    # If the caller passes disaggregation_filters={"SEX": None}, it means
    # "fetch all values for SEX". The Data360 API only accepts a single scalar
    # per dimension, so a None value is our internal sentinel for "expand me".
    # We resolve the valid non-trivial codes via get_disaggregation, then fire
    # one fetch per value concurrently and concat the results.
    _TRIVIAL_CODES = {"_T", "_Z"}
    expand_dims: dict[str, list[str]] = {}  # dim → [valid non-trivial codes]
    if disaggregation_filters:
        for dim, val in disaggregation_filters.items():
            if val is None:
                try:
                    disagg = await get_disaggregation(database_id, indicator_id)
                    for d in disagg.get("dimensions") or []:
                        if d.get("field_name", "").upper() == dim.upper():
                            codes = [
                                c
                                for c in (d.get("field_value") or [])
                                if c not in _TRIVIAL_CODES
                            ]
                            if codes:
                                expand_dims[dim] = codes
                            break
                except Exception as e:
                    _logger.warning(f"Could not resolve expand dim {dim}: {e}")

    # ── Fetch ──────────────────────────────────────────────────────────────────
    if expand_dims:
        # Build one filter dict per value combination (only handles single expand dim for now).
        # Multi-dim expansion (e.g. SEX × AGE) would be a combinatorial explosion — skip it.
        first_dim, codes = next(iter(expand_dims.items()))
        base_filters = {
            k: v for k, v in (disaggregation_filters or {}).items() if k != first_dim
        }

        async def _fetch_one(code: str) -> pd.DataFrame:
            filters = {**base_filters, first_dim: code}
            try:
                url = await get_data_api_url(
                    database_id=database_id,
                    indicator_id=indicator_id,
                    country_code=country_code,
                    start_year=start_year,
                    end_year=end_year,
                    disaggregation_filters=filters,
                )
                df = await _fetch_data_internal(url)
                df.columns = [c.lower() for c in df.columns]
                return df
            except Exception as e:
                _logger.warning(f"Expand fetch failed for {first_dim}={code}: {e}")
                return pd.DataFrame()

        frames = await asyncio.gather(*[_fetch_one(c) for c in codes])
        non_empty = [f for f in frames if not f.empty]
        if not non_empty:
            return _err("Error: No data returned for any disaggregation value.")
        data = pd.concat(non_empty, ignore_index=True)
        # data.columns already lowercased inside _fetch_one

        # Build a canonical data_url for the expand-dims case so that
        # frequency detection (step 3) and metadata extraction (step 4)
        # can parse it the same way as the single-fetch path.
        try:
            data_url = await get_data_api_url(
                database_id=database_id,
                indicator_id=indicator_id,
                country_code=country_code,
                start_year=start_year,
                end_year=end_year,
                disaggregation_filters={
                    k: v
                    for k, v in (disaggregation_filters or {}).items()
                    if v is not None
                },
            )
        except Exception:
            # Fall back to a simple constructed URL so metadata steps degrade
            # gracefully rather than crashing on UnboundLocalError.
            data_url = f"?DATABASE_ID={database_id}&INDICATOR={indicator_id}"
    else:
        # 1. Build URL
        try:
            data_url = await get_data_api_url(
                database_id=database_id,
                indicator_id=indicator_id,
                country_code=country_code,
                start_year=start_year,
                end_year=end_year,
                disaggregation_filters=disaggregation_filters,
            )
        except ValueError as e:
            return _err(f"Error: {e}")

        # 2. Fetch data
        try:
            data = await _fetch_data_internal(data_url)
        except ValueError as e:
            return _err(f"Error: {e}")
        except httpx.HTTPStatusError as e:
            return _err(f"Error fetching data: {e.response.status_code}")
        except Exception as e:
            _logger.exception("Failed to fetch data")
            return _err(f"Error fetching data: {e}")

        data.columns = [c.lower() for c in data.columns]

    # 3. Detect frequency
    data_frequency = None
    try:
        parsed = urlparse(data_url)
        params = parse_qs(parsed.query)
        db_id = params.get("DATABASE_ID", [database_id])[0]
        ind_id_param = (
            params.get("indicatorId", [None])[0]
            or params.get("INDICATOR", [None])[0]
            or indicator_id
        )
        if db_id and ind_id_param:
            meta = await get_metadata(db_id, ind_id_param)
            if meta:
                if meta.disaggregation_options:
                    for d in meta.disaggregation_options:
                        if d.get("field_name") == "FREQ" and d.get("field_value"):
                            data_frequency = d["field_value"][0]
                            break
                if not data_frequency and meta.indicator_metadata:
                    data_frequency = viz_config.infer_frequency_from_periodicity(
                        meta.indicator_metadata.get("periodicity", "")
                    )
    except Exception as e:
        _logger.warning(f"Could not detect frequency: {e}")

    # 4. Fetch title and unit
    chart_title_auto = "Generated Visualization"
    raw_unit = ""
    try:
        parsed = urlparse(data_url)
        params = parse_qs(parsed.query)
        db_id = params.get("DATABASE_ID", [database_id])[0]
        ind_id_param = (
            params.get("indicatorId", [None])[0]
            or params.get("INDICATOR", [None])[0]
            or indicator_id
        )
        if db_id and ind_id_param:
            meta = await get_metadata(db_id, ind_id_param)
            if meta and meta.indicator_metadata:
                chart_title_auto = meta.indicator_metadata.get("name", chart_title_auto)
                raw_unit = (
                    meta.indicator_metadata.get("measurement_unit")
                    or meta.indicator_metadata.get("unit_measure")
                    or ""
                )
    except Exception as e:
        _logger.warning(f"Could not fetch metadata for title: {e}")

    # Prefer the unit code from the actual data column over the metadata freeform string.
    # Metadata APIs often return display labels (e.g. "Unit") rather than codelist codes
    # (e.g. "PS" for Persons). The SDMX data column is authoritative.
    _TRIVIAL_UNIT_CODES = {"", "_T", "_Z", "U"}  # Unitless / catch-all / not meaningful
    if "unit_measure" in data.columns:
        _data_units = data["unit_measure"].dropna().unique().tolist()
        if len(_data_units) == 1 and _data_units[0] not in _TRIVIAL_UNIT_CODES:
            raw_unit = _data_units[0]  # e.g. "PS" → will resolve to "Persons"
        elif len(_data_units) > 1:
            # Multi-unit datasets: keep the metadata value; _clean_single_df will
            # retain unit_measure as a dimension and the chart will facet on it.
            pass

    try:
        db_map = await get_database_mapping()
    except Exception as e:
        _logger.warning(f"Could not load database mapping for source attribution: {e}")
        db_map = {}
    database_display = db_map.get(database_id, database_id)
    chart_title_auto = viz_config._clean_label_generic(chart_title_auto)
    indicator_display = (
        chart_title_auto
        if chart_title_auto != "Generated Visualization"
        else indicator_id
    )
    indicator_display = viz_config._clean_label_generic(indicator_display)
    source_attribution: dict[str, str] = {
        "database_id": database_id,
        "database_name": database_display,
        "indicator_id": indicator_id,
        "indicator_name": indicator_display,
    }

    # 5. Clean data — column selection, bar-vs-temporal time handling, renames (→ year/value/country)
    if "obs_value" in data.columns:
        data["obs_value"] = pd.to_numeric(data["obs_value"], errors="coerce")

    # Auto-filter unit_measure for population pyramid candidates (containing age and sex dimensions)
    # when multiple units are present to avoid faceting on unit_measure and collapsing age groups.
    cols = {c.lower() for c in data.columns}
    if "sex" in cols and "age" in cols and "unit_measure" in cols:
        unit_col = [c for c in data.columns if c.lower() == "unit_measure"][0]
        unique_units = data[unit_col].dropna().unique()
        if len(unique_units) > 1:
            count_units = [u for u in unique_units if str(u).upper() in ("COUNT", "VAL", "NUMBER", "VALUE")]
            selected_unit = count_units[0] if count_units else unique_units[0]
            data = data[data[unit_col] == selected_unit].copy()
            _logger.info("Multi-unit population data detected. Auto-filtered unit_measure to '%s' to preserve age/sex breakdown structure.", selected_unit)

    try:
        viz_data, relevant_cols, temporal_frequency = _clean_single_df(
            data, relevant_fields, chart_type, data_frequency
        )
    except ValueError as e:
        return _err(str(e))

    if viz_data.empty:
        return _err("Error: No data available for visualization after cleaning.")

    # 5.1 Detect and resolve hidden dimensions that create duplicate (key → value)
    # rows after cleaning.  Surface as the next unused comp_breakdown_N slot when
    # cardinality is small, or collapse to mean with a subtitle warning.
    viz_data, _hidden_dim_warning = _resolve_hidden_dimension(viz_data, data)

    # 6. Map country codes
    viz_data = await _map_country_codes(viz_data)

    # 6.1 Auto-resolve dimension codes from extdataportal codelist
    # (COMP_BREAKDOWN_1/2/3, SEX, AGE, URBANISATION → human-readable labels).
    viz_data = await _map_dimension_codes(viz_data)

    # 6.1b Strip long shared prefixes from comp_breakdown_* columns so Vega-Lite
    # legend/panel labels are unique and readable without truncation.
    # e.g. "Severity Phase of Acute Food Insecurity or Malnutrition : Phase 1 - Minimal"
    # → "Phase 1 - Minimal".  The series_labels override below still takes priority.
    _CB_DIMS = [
        c
        for c in ["comp_breakdown_1", "comp_breakdown_2", "comp_breakdown_3"]
        if c in viz_data.columns
    ]
    viz_data = _strip_common_prefix_in_dims(viz_data, _CB_DIMS)

    # 6.2 Resolve raw_unit code to human-readable label for axis/subtitle.
    # get_label returns the raw input unchanged when the code is not found in the
    # extdataportal mapping. We check: if the resolved label equals the raw code,
    # no mapping exists and we suppress the label (show nothing rather than a raw
    # code like "PS" or a freeform metadata string like "Unit").
    raw_unit_label: str = ""
    raw_unit_mult = 0
    if "unit_mult" in data.columns:
        _data_mults = data["unit_mult"].dropna().unique().tolist()
        if len(_data_mults) == 1:
            try:
                raw_unit_mult = int(_data_mults[0])
            except (ValueError, TypeError):
                pass

    try:
        from data360.providers import get_codelist_manager

        _cl_mgr = get_codelist_manager()
        if raw_unit:
            _resolved = _cl_mgr.get_label("UNIT_MEASURE", raw_unit)
            # Only use the resolved label if get_label actually found a mapping.
            has_mapping = _resolved != raw_unit
            raw_unit_label = _resolved if has_mapping else ""

            from data360.api import _qualify_unit_name
            is_special_ps = bool(raw_unit and raw_unit.upper() == "PS" and raw_unit_mult > 0)
            if has_mapping or is_special_ps:
                unit_name = raw_unit_label if raw_unit_label else raw_unit
                raw_unit_label = _qualify_unit_name(unit_name, raw_unit_mult, raw_unit) or ""

    except Exception:
        pass  # No label; y-axis will have no title rather than a raw code

    # 6.5 Apply custom series labels (override auto-resolved labels).
    # series_labels is now optional — the pipeline auto-resolves the dimensions above.
    if series_labels and isinstance(series_labels, dict):
        for col in _VIZ_DISAGG_DIMS:
            if col in viz_data.columns:
                viz_data[col] = viz_data[col].replace(series_labels)

    # 6.5b Pre-filter for confidence interval error bands if present
    from data360.viz_config import _filter_df_for_error_band
    viz_data = _filter_df_for_error_band(viz_data)

    # 6.6 If a cross-sectional chart type is explicitly requested (bar, map, tick) but years are omitted
    # or a multi-year/multi-country dataset is passed, default to the latest available year to prevent
    # a cluttered "bar chart for time series".
    # Exclusions:
    #   - "stacked_bar" is always temporal — never strip its years.
    #   - Any hint when start_year AND end_year were both explicitly provided — the caller
    #     requested a time range; routing (not this pre-filter) decides what to do with it.
    is_cross_sectional_hint = chart_type and any(
        kw in chart_type.lower() and not (kw == "map" and "heatmap" in chart_type.lower())
        for kw in ("bar", "column", "ranking", "map", "choropleth", "tick", "strip", "beeswarm", "distribution")
    ) and chart_type.lower() != "stacked_bar"
    caller_set_year_range = start_year is not None and end_year is not None
    if is_cross_sectional_hint and not caller_set_year_range and not viz_data.empty:
        unique_years = viz_data["year"].nunique() if "year" in viz_data.columns else 0
        unique_countries = viz_data["country"].nunique() if "country" in viz_data.columns else 0
        if unique_years > 1 and unique_countries > 1:
            if "year" in viz_data.columns:
                latest_year = viz_data["year"].max()
                viz_data = viz_data[viz_data["year"] == latest_year].copy()
                _logger.info(
                    f"[get_viz_spec] chart_type={chart_type}: filtered data to latest year {latest_year} "
                    f"to prevent cluttered cross-sectional time-series."
                )

    import textwrap

    # Apply text wrapping (Typography T3 constraint) so long single-indicator titles don't overflow
    if chart_title and isinstance(chart_title, str):
        # Allow LLM to override the title completely
        final_title = chart_title
    else:
        # Wrap the auto-generated title
        final_title = (
            textwrap.wrap(chart_title_auto, width=80)
            if isinstance(chart_title_auto, str)
            else chart_title_auto
        )
    # Vega-Lite title + subtitle (geography, year range, unit) after data is cleaned
    chart_title_vl: str | dict = viz_config.build_chart_title_with_context(
        final_title, raw_unit_label or None, viz_data
    )

    # Append missing-country warning and hidden-dimension warning to the subtitle so the user sees it.
    if isinstance(chart_title_vl, dict):
        _sub = chart_title_vl.get("subtitle", [])
        if isinstance(_sub, str):
            _sub = [_sub]
        else:
            _sub = list(_sub)

        present_set = set(viz_data["country"].dropna().unique()) if "country" in viz_data.columns else set()
        missing_names = await _detect_missing_countries(country_code, present_set)
        if missing_names:
            missing_str = ", ".join(missing_names)
            _sub.append(f"Note: Data unavailable for {missing_str}.")

        if _hidden_dim_warning:
            _sub.append(_hidden_dim_warning)

        chart_title_vl["subtitle"] = _sub
    elif isinstance(chart_title_vl, str):
        present_set = set(viz_data["country"].dropna().unique()) if "country" in viz_data.columns else set()
        missing_names = await _detect_missing_countries(country_code, present_set)
        _sub = []
        if missing_names:
            missing_str = ", ".join(missing_names)
            _sub.append(f"Note: Data unavailable for {missing_str}.")
        if _hidden_dim_warning:
            _sub.append(_hidden_dim_warning)
        if _sub:
            chart_title_vl = {"text": chart_title_vl, "subtitle": _sub}

    # 7. Fetch dim labels + build data profile BEFORE routing
    # dim_name_labels must be fetched first so the profile can include codelist-
    # resolved breakdown names.
    _pre_dim_name_labels: dict[str, str] = {}
    try:
        from data360.api import get_comp_breakdown_dim_names
        _pre_dim_name_labels = await get_comp_breakdown_dim_names(database_id, indicator_id)
    except Exception as _exc:
        _logger.debug("Could not fetch comp_breakdown dim names: %s", _exc)

    # Build comprehensive data profile from real cleaned data.
    # This runs BEFORE select_strategy so coverage signals (sparse countries,
    # completeness %) can inform routing decisions and so that the profile is
    # available for spec builders — not just post-hoc narration.
    data_profile = _build_data_profile(
        viz_data,
        scale_type=_infer_scale_type(raw_unit, raw_unit_label) if (raw_unit or raw_unit_label) else None,
        indicator_names=[indicator_display] if indicator_display else None,
        units_raw=[raw_unit] if raw_unit else None,
        units_label=[raw_unit_label] if raw_unit_label else None,
        dim_name_labels=_pre_dim_name_labels or None,
    )

    # Determine strategy (informed by data_profile coverage signals)
    n_indicators = 1
    strategy_result = viz_config.select_strategy(
        viz_data,
        n_indicators=n_indicators,
        chart_type_hint=chart_type,
        raw_unit=raw_unit,
        raw_unit_mult=raw_unit_mult,
        data_profile=data_profile,
        strategy_override=strategy_override,
    )

    # Post-routing sparsity filtering: Only drop countries with sparse data points
    # if the strategy is temporal (which requires drawing line segments).
    # Cross-sectional and distribution charts only require 1 data point per country,
    # so dropping them is incorrect.
    is_temporal = strategy_result.strategy in (
        viz_config.ChartStrategy.TEMPORAL_SINGLE,
        viz_config.ChartStrategy.TEMPORAL_MULTI_IND,
        viz_config.ChartStrategy.SMALL_MULTIPLES,
    )
    if is_temporal:
        _sparse_from_profile = (
            data_profile.get("coverage_quality", {}).get("sparse_countries", [])
        )
        _n_countries_in_data = (
            viz_data["country"].nunique() if "country" in viz_data.columns else 0
        )
        if _sparse_from_profile and _n_countries_in_data >= 2:
            _logger.info(
                f"[get_viz_spec] Dropping sparse countries (< 3 years) for temporal strategy: {_sparse_from_profile}"
            )
            viz_data = viz_data[~viz_data["country"].isin(_sparse_from_profile)].copy()
            if viz_data.empty:
                return _err("Error: No data available for visualization after filtering sparse countries.")

            # Rebuild profile and re-run strategy selection to reflect pruned data shape
            data_profile = _build_data_profile(
                viz_data,
                scale_type=_infer_scale_type(raw_unit, raw_unit_label) if (raw_unit or raw_unit_label) else None,
                indicator_names=[indicator_display] if indicator_display else None,
                units_raw=[raw_unit] if raw_unit else None,
                units_label=[raw_unit_label] if raw_unit_label else None,
                dim_name_labels=_pre_dim_name_labels or None,
            )
            strategy_result = viz_config.select_strategy(
                viz_data,
                n_indicators=n_indicators,
                chart_type_hint=chart_type,
                raw_unit=raw_unit,
                raw_unit_mult=raw_unit_mult,
                data_profile=data_profile,
                strategy_override=strategy_override,
            )

    # Thread detected temporal frequency through to spec builders.
    strategy_result.temporal_frequency = temporal_frequency
    # Thread dim_name_labels into result for spec builders.
    strategy_result.dim_name_labels = _pre_dim_name_labels

    # If the strategy is CROSS_SECTIONAL, keep only the latest available year per country
    if strategy_result.strategy == viz_config.ChartStrategy.CROSS_SECTIONAL and not viz_data.empty:
        if "country" in viz_data.columns and "year" in viz_data.columns:
            try:
                valid_data = viz_data.dropna(subset=["country", "year"])
                if not valid_data.empty:
                    years_numeric = pd.to_numeric(valid_data["year"], errors="coerce")
                    if years_numeric.notna().any():
                        valid_data = valid_data.assign(_years_num=years_numeric)
                        idx = valid_data.groupby("country")["_years_num"].idxmax()
                    else:
                        idx = valid_data.groupby("country")["year"].idxmax()
                    viz_data = viz_data.loc[idx].copy()
                    _logger.info("[get_viz_spec] CROSS_SECTIONAL strategy: filtered to latest year per country.")
            except Exception as e:
                _logger.warning(f"Failed to filter cross-sectional data to latest year per country: {e}")

    # 7.05  Data sufficiency guards — return error before chart dispatch rather
    # than produce a misleading visualization with too little data.
    #
    # Minimum country counts per strategy:
    #   - CROSS_SECTIONAL: ≥3 bars needed for a meaningful comparison
    #   - DISTRIBUTION:    ≥10 required so tick/strip chart has visual density
    #   - BREAKDOWN_COMPARISON / SMALL_MULTIPLES: ≥2 countries to show contrast
    _MIN_COUNTRIES_BY_STRATEGY: dict[viz_config.ChartStrategy, int] = {
        viz_config.ChartStrategy.CROSS_SECTIONAL:      1,  # 1+ bars always valid
        viz_config.ChartStrategy.DISTRIBUTION:         10,
        viz_config.ChartStrategy.BREAKDOWN_COMPARISON: 2,
        viz_config.ChartStrategy.SMALL_MULTIPLES:      2,
    }
    if strategy_result.strategy in _MIN_COUNTRIES_BY_STRATEGY:
        _actual_countries = (
            int(viz_data["country"].nunique()) if "country" in viz_data.columns else 0
        )
        _minimum = _MIN_COUNTRIES_BY_STRATEGY[strategy_result.strategy]
        # If it is a population pyramid request, it will be transformed into a single-panel chart, so 1 country is sufficient.
        if strategy_result.strategy == viz_config.ChartStrategy.SMALL_MULTIPLES:
            hint_str = (chart_type or "").lower().strip()
            if "pyramid" in hint_str or "population_pyramid" in hint_str:
                _minimum = 1

        # If we facet or group by breakdown dimensions (not by country), 1 country is sufficient.
        if strategy_result.strategy in (viz_config.ChartStrategy.SMALL_MULTIPLES, viz_config.ChartStrategy.BREAKDOWN_COMPARISON):
            facet_dim = getattr(strategy_result, "facet_dim", None)
            color_dim = getattr(strategy_result, "color_dim", None)
            secondary_color_dim = getattr(strategy_result, "secondary_color_dim", None)
            if facet_dim != "country" and color_dim != "country" and secondary_color_dim != "country":
                _minimum = 1

        if _actual_countries < _minimum:
            return _err(
                f"Insufficient data for a {strategy_result.strategy.value} chart: "
                f"only {_actual_countries} country/countries returned data "
                f"(minimum required: {_minimum}). "
                f"The requested countries/indicator/time period combination had too "
                f"few values in the Data360 API."
            )

    # Sparse time-series guard: if average data points per country < 3,
    # lines will be jagged and disconnected — fall back to cross_sectional bar
    # instead of raising an error.  A single-year request is always valid as a
    # bar chart; only raise if we cannot produce any useful visualization at all.
    if strategy_result.strategy in (
        viz_config.ChartStrategy.SMALL_MULTIPLES,
        viz_config.ChartStrategy.TEMPORAL_SINGLE,
    ) and not viz_data.empty and "country" in viz_data.columns:
        _n_series = max(int(viz_data["country"].nunique()), 1)
        if "year" in viz_data.columns:
            _avg_pts = float(viz_data.groupby("country")["year"].nunique().mean())
        else:
            _avg_pts = len(viz_data) / _n_series
        # Population pyramid requests are designed to show cross-sectional cohorts in a single year, so they should not fall back.
        hint_str = (chart_type or "").lower().strip()
        is_pyramid = "pyramid" in hint_str or "population_pyramid" in hint_str

        # Don't fall back to cross-sectional if we are faceting/grouping by breakdown dimensions,
        # as doing so would collapse the breakdown details.
        is_breakdown = False
        if strategy_result.strategy in (viz_config.ChartStrategy.SMALL_MULTIPLES, viz_config.ChartStrategy.BREAKDOWN_COMPARISON):
            facet_dim = getattr(strategy_result, "facet_dim", None)
            color_dim = getattr(strategy_result, "color_dim", None)
            secondary_color_dim = getattr(strategy_result, "secondary_color_dim", None)
            if facet_dim != "country" and color_dim != "country" and secondary_color_dim != "country":
                is_breakdown = True

        if _avg_pts < 3 and not is_pyramid and not is_breakdown:
            _logger.info(
                f"[get_viz_spec] Sparse data ({_avg_pts:.1f} pts/country) — "
                f"falling back from {strategy_result.strategy.value} to cross_sectional bar."
            )
            strategy_result = viz_config.StrategyResult(
                viz_config.ChartStrategy.CROSS_SECTIONAL,
                f"Sparse data ({_avg_pts:.1f} pts/country) → bar chart",
                mark_hint="bar",
            )

    _logger.info(
        f"Chart strategy: {strategy_result.strategy.value} — {strategy_result.reason}"
    )

    if strategy_result.strategy == viz_config.ChartStrategy.FALLBACK_LINE:
        return _err(
            "Could not determine a suitable visualization strategy for this data structure. "
            "A fallback visualization was not generated."
        )


    try:
        spec = viz_config.dispatch_spec(
            strategy_result.strategy,
            viz_data,
            chart_title_vl,
            strategy_result,
            indicator_labels=series_labels,
            y_label=raw_unit_label if raw_unit_label else "Value",
            x_label="Value",
            unit_measure=_unit_measure_for_formatting(raw_unit, raw_unit_label),
            indicator_name=indicator_display or None,
        )
        # Apply post-processing rules
        spec = _apply_post_processing_rules(
            spec,
            data_frequency=data_frequency,
            unit_measure=_unit_measure_for_formatting(raw_unit, raw_unit_label),
            strategy_result=strategy_result,
            df=viz_data
        )

        out_reason = strategy_result.reason
        if strategy_result.strategy == viz_config.ChartStrategy.TEMPORAL_SINGLE:
            resolved_mark = viz_config.extract_top_level_mark_type(spec)
            if resolved_mark:
                out_reason = viz_config.patch_strategy_reason_chart_phrase(
                    strategy_result.reason, resolved_mark
                )

        dim_summary = _extract_dimension_summary(viz_data)
        data_summary = _build_data_summary(viz_data)
        # data_profile was built before routing — read from strategy_result
        data_profile = strategy_result.data_profile or {}

        warning_msg = None
        if chart_type:
            requested_lower = chart_type.lower()
            core_intent = (
                requested_lower.replace("chart", "")
                .replace("stacked", "")
                .replace("grouped", "")
                .replace("_", " ")
                .strip()
            )
            if core_intent == "scatter":
                core_intent = "point"
            elif core_intent == "strip":
                core_intent = "beeswarm"

            reason_lower = out_reason.lower()
            reason_suffix = (
                reason_lower.split("→")[-1] if "→" in reason_lower else reason_lower
            )

            is_scatter_match = (core_intent == "point" and ("scatter" in reason_suffix or "scatterplot" in reason_suffix))
            is_point_fallback_to_line = (core_intent == "point" and "line" in reason_suffix)
            if core_intent and core_intent not in reason_suffix and not is_scatter_match and not is_point_fallback_to_line:
                warning_msg = (
                    f"You requested '{chart_type}', but the visualization engine "
                    f"selected a different strategy based on data cardinality: {out_reason}. "
                    "The chart was successfully generated. Please ensure your response and the chart title reflect this actual strategy."
                )

        return _ok(
            await _store_spec(spec, charts_api_url_override=charts_api_url_override),
            warning=warning_msg,
            source_attribution=source_attribution,
            strategy=strategy_result.strategy.value,
            reason=out_reason,
            dimensions=dim_summary or None,
            data_summary=data_summary or None,
            data_profile=data_profile or None,
            spec=spec,
        )
    except Exception as e:
        _logger.exception("Strategy builder failed")
        return _err(f"Chart generation failed: {e}")


# ============================================================================
# TOOL: get_multi_indicator_viz_spec (NEW)
# ============================================================================


async def get_multi_indicator_viz_spec(
    indicator_ids: list[dict[str, str]] | None = None,
    country_code: str | None = None,
    start_year: int | None = None,
    end_year: int | None = None,
    disaggregation_filters: dict[str, str | None] | None = None,
    chart_type: str | None = None,
    chart_title: str | None = None,
    series_labels: dict[str, str] | None = None,
    strategy_override: str | None = None,
) -> VizResult:
    """Generate a Vega-Lite chart comparing multiple Data360 indicators.

    REQUIRED for success: indicator_ids (2–4 entries). Call data360_search_indicators first,
    then pass each series as {"database_id": "...", "indicator_id": "..."}. If you omit
    indicator_ids or pass fewer than 2 entries, this tool returns {"error": "..."} — fix
    the arguments and call again (do not rely on country_code alone).

    Use for: scatterplots (2 indicators vs each other), layered/dual-axis line charts
    (2-3 indicators over time in one country), connected scatter (trajectory charts).

    ## Grammar of Graphics Contract

    The pipeline maps data dimensions to Vega-Lite aesthetic channels automatically.
    DO NOT pre-filter a dimension to simplify the chart — that collapses multi-series
    data into a single undifferentiated line and silently discards information.

    ### disaggregation_filters decision rule

    disaggregation_filters applies to ALL indicators in this call. Only pin a dimension
    when the user has explicitly requested a specific value. Follow these exact rules:

    1. COMP_BREAKDOWN_1/2/3: NEVER PRE-FILTER. These contain structural phases or
       statistical components (e.g. Estimate vs Standard Error). Pass them entirely
       unfiltered and let the visualization engine map them to separate panels/colors.
       (e.g. DO NOT filter {"COMP_BREAKDOWN_1": "WGI_EST"} just to simplify the chart).

    2. Demographic Dimensions (SEX, AGE, URBANISATION, EDUCATION): Pre-filter to the
       aggregate total ("_T") by default unless the user explicitly asks for a breakdown.
       - "What is unemployment?" -> filter {"SEX": "_T"}
       - "Unemployment by sex" -> OMIT the filter so the engine colors them.
       If there is no "_T" available, leave it unfiltered.

    3. UNIT_MEASURE: NEVER PRE-FILTER. The pipeline automatically creates vertically-stacked
       panels for each unit of measure. Omitting this filter is the only way to generate
       multi-unit comparison charts.

    ### Chart strategy → Vega-Lite encoding table

    Strategy              | When selected                                       | Vega-Lite encoding
    ----------------------|-----------------------------------------------------|-------------------------------------------
    CORRELATION           | 2 indicators, >1 country, single year               | x=indicator1(Q), y=indicator2(Q), color=country(N) (scatter)
    CORRELATION_TEMPORAL  | 2 indicators, >1 country, multi-year                | x=indicator1(Q), y=indicator2(Q), color=country(N), order=year (connected scatter)
    TEMPORAL_MULTI_IND    | 2-4 indicators, 1 country or 3+ indicators          | layered lines, independent y-scales per indicator
    STACKED_AREA          | chart_type="area"/"stacked_area", composition Q     | x=year(temporal), y=value(Q) stacked, color=indicator(N)

    ### High-cardinality guidance

    DO NOT reduce indicator_ids or disaggregation_filters to simplify the output.
    Pass the full set of indicators the user asked about. The pipeline will:
    - Merge all indicator series into a single aligned DataFrame
    - Auto-select the correct strategy (scatter, layered lines, stacked area)
    - Cap series count if needed and annotate the subtitle with a trim note

    ### Visual channel hierarchy (perception accuracy)

    Channels are ranked by how accurately viewers read encoded values:

    Channel           | Type        | Used for
    ------------------|-------------|----------------------------------------
    Position (x/y)    | Highest     | Quantitative values, time axis
    Color (hue)       | Medium      | Nominal categories (country, indicator)
    Color (lightness) | Medium      | Not used for multi-indicator charts
    Size / Shape      | Low         | Not used in current strategies

    The pipeline always maps quantitative values → position (y-axis) and nominal
    categories → color (hue). Do not override this with custom disaggregation_filters.

    ### Encoding type rules (Vega-Lite v5)

    - Year fields: type="temporal", format="%Y". Never "ordinal" for years.
    - Country / breakdown fields: type="nominal"
    - Numeric values: type="quantitative", scale.zero=False for non-ratio data
    - Multi-indicator layers: each indicator gets its own y-axis (left/right), its own
      WB categorical color. Resolved with resolve.scale.y="independent".
    - Connected scatter uses order channel (order=year, type=temporal) to draw
      trajectory lines in chronological order.

    Args:
        indicator_ids: List of dicts, each with "database_id" and "indicator_id".
            Example: [
                {"database_id": "WB_WDI", "indicator_id": "WB_WDI_NY_GDP_PCAP_KD"},
                {"database_id": "WB_WDI", "indicator_id": "WB_WDI_SP_DYN_LE00_IN"}
            ]
        country_code: Optional ISO code(s): one code or semicolon-separated (e.g. KEN;MAR)
            or comma-separated; normalized for the Data API like data360_get_data.
        start_year: Optional start year (inclusive).
        end_year: Optional end year (inclusive).
        disaggregation_filters: Optional filters applied to ALL indicators; values are str or null.
            See grammar of graphics contract above — only pin a dimension when the user
            explicitly requested a specific value. Omitting a dimension fetches all values
            and lets the pipeline choose the correct Vega-Lite channel automatically.
            REF_AREA uses comma-separated ISO codes (semicolons normalized to commas).
        chart_type: Optional hint — "scatter", "connected_scatter", "layered_lines",
            "stacked_area", "map", "choropleth", etc. Used to guide the underlying ChartStrategy. If omitted, auto-selected by data shape.

            ## chart_type decision rule

            Only pass "area" or "stacked_area" when the user's question implies
            **composition or share over time** — i.e., the multiple indicators together
            add up to a meaningful whole (e.g., age groups summing to total population,
            electricity sources summing to total generation).
            Typical signal words: "breakdown of", "composition of", "share of",
            "proportion", "structure of".

            CORRECT — user asked about composition across multiple indicators:
              chart_type="stacked_area"   # "Show the age group breakdown in Japan"

            WRONG — user asked about a trend:
              chart_type="area"           # "How has GDP and poverty changed?" ← use default

            For trend or comparison questions, omit chart_type and let the pipeline
            select the correct strategy (layered lines, scatter, etc.).

        chart_title: You MUST provide a custom, human-readable chart title here (e.g., 'Electricity Mix by Source'). Do not include chart types (e.g. 'Heatmap', 'Line Chart') in the title, as the strategy engine may override the requested chart type based on data cardinality. Do not leave this blank.
        series_labels: Optional. A dictionary mapping indicator IDs to short,
            human-readable labels (e.g., {"WB_WDI_EG_ELC_HYRO_ZS": "Hydro"}).
            Provide this to shorten long auto-generated indicator names in legends.

    Returns:
        Dict with "url" (chart URL on success), "error" (on failure),
        "strategy" (which chart type was chosen), "warning" (if applicable).
    """
    if indicator_ids is None or len(indicator_ids) < 2:
        return _err(
            "indicator_ids is required: pass a JSON array of 2–4 objects, each "
            '{"database_id":"<db>","indicator_id":"<id>"} from data360_search_indicators '
            "(use idno + database_id). Example: "
            '[{"database_id":"WB_WDI","indicator_id":"WB_WDI_NY_GDP_PCAP_KD"},'
            '{"database_id":"WB_WDI","indicator_id":"WB_WDI_SP_DYN_LE00_IN"}]. '
            "Then add country_code, start_year, end_year as needed. "
            "Do not call this tool with only country_code or chart_type."
        )
    if len(indicator_ids) > 4:
        return _err("Maximum 4 indicators supported in one chart.")

    # 1. Fetch all indicators concurrently
    tasks = [
        _fetch_single_indicator(
            ind["database_id"],
            ind["indicator_id"],
            country_code,
            start_year,
            end_year,
            disaggregation_filters,
        )
        for ind in indicator_ids
    ]
    results = await asyncio.gather(*tasks, return_exceptions=True)

    dfs: list[pd.DataFrame] = []
    titles: list[str] = []
    units: list[str] = []
    indicator_col_names: list[str] = []
    used_cols: set[str] = set()

    for i, (res, ind) in enumerate(zip(results, indicator_ids)):
        if isinstance(res, Exception):
            return _err(f"Failed to fetch indicator {ind['indicator_id']}: {res}")
        df, title, unit = res
        if df.empty:
            return _err(f"No data returned for indicator {ind['indicator_id']}.")

        df, _, _ = _clean_single_df(
            df, relevant_fields=None, chart_type=None, data_frequency=None
        )

        ind_name = title or ind["indicator_id"]
        # Allow LLM to override the indicator name directly in the dataframe
        if series_labels and ind["indicator_id"] in series_labels:
            ind_name = series_labels[ind["indicator_id"]]

        ind_name = viz_config._clean_label_generic(ind_name)



        col_base = _slugify(ind_name)
        col = _make_unique_col(col_base, used_cols)
        used_cols.add(col)

        dfs.append(df)
        titles.append(ind_name)
        units.append(unit or "")
        indicator_col_names.append(col)

    # 2. Standardize each DataFrame
    std_dfs: list[pd.DataFrame] = []
    for df, col in zip(dfs, indicator_col_names):
        # _clean_single_df already renamed time_period -> year, ref_area -> country, obs_value -> value
        df = df.rename(columns={"value": col})

        # Keep only join keys + value column.
        # CRITICAL: Drop any disaggregation dimension that is CONSTANT (nunique <= 1)
        # for this specific indicator. If Indicator 1 is "Male" and Indicator 2 is "Female",
        # keeping `sex` would cause the outer merge to fail (creating disjoint rows with NaNs).
        keep_dims = [
            c for c in _VIZ_DISAGG_DIMS if c in df.columns and df[c].nunique() > 1
        ]
        keep = ["year", "country"] + keep_dims + [col]

        # Only keep columns that actually exist in the dataframe
        keep = [c for c in keep if c in df.columns]
        std_dfs.append(df[keep])

    # 3. Map country codes and auto-resolve dimension codes (reuse the same helpers
    #    as get_viz_spec for consistency — no duplicate logic).
    for i, df in enumerate(std_dfs):
        std_dfs[i] = await _map_country_codes(df)
        std_dfs[i] = await _map_dimension_codes(std_dfs[i])

    # 4. Merge on common keys
    join_keys = [
        c
        for c in ["year", "country", *_VIZ_DISAGG_DIMS]
        if all(c in df.columns for df in std_dfs)
    ]

    if not join_keys:
        return _err(
            "Indicators could not be merged: no common dimensions (for example, one "
            "series is time-only and another is geography-only). Choose indicators "
            "that share at least one of: year, country, or the same disaggregation "
            "columns."
        )

    merged = std_dfs[0]
    for df in std_dfs[1:]:
        merged = pd.merge(merged, df, on=join_keys, how="outer")

    if merged.empty:
        return _err("No overlapping data found across indicators after merging.")

    # Collapse duplicate rows introduced by the outer-merge fanout.
    # Strategy:
    # 1. Drop rows that are identical across every column — safe, no data loss.
    # 2. If true ambiguous duplicates remain (same join keys, different indicator
    #    values), emit a warning and take the mean as a fallback.  This should
    #    not happen for well-aligned indicators but is guarded explicitly so that
    #    any averaging is visible in the server log rather than silent.
    groupby_keys = [k for k in join_keys if k in merged.columns]
    agg_cols = [col for col in indicator_col_names if col in merged.columns]

    merged = merged.drop_duplicates()

    if groupby_keys and agg_cols:
        dup_mask = merged.duplicated(subset=groupby_keys, keep=False)
        if dup_mask.any():
            _logger.warning(
                "Multi-indicator merge produced %d ambiguous duplicate rows "
                "(same %s keys, differing indicator values). Taking mean as "
                "fallback — verify that the indicators share compatible "
                "disaggregation dimensions.",
                dup_mask.sum(),
                groupby_keys,
            )
            agg_dict = {col: "mean" for col in agg_cols}
            merged = merged.groupby(groupby_keys, as_index=False).agg(agg_dict)


    merged = _sanitize_dataframe_for_json_records(merged)

    import textwrap

    # 5. Build chart title (with subtitle when all indicators share the same unit)
    # GoG / AntVis guideline: Do not arbitrarily truncate strings with ellipses.
    # Instead, preserve the full text but word-wrap it so it fits the chart width.
    if chart_title:
        # If the LLM provided a custom title, use it directly without wrapping.
        # We assume the LLM provides a concise, readable title.
        final_chart_title = chart_title
    else:
        if len(titles) == 2:
            full_title = f"{titles[0]} vs. {titles[1]}"
        else:
            full_title = " | ".join(titles)

        # Wrap at 80 characters to ensure it fits safely within standard chart widths
        final_chart_title = textwrap.wrap(full_title, width=80)

    units_raw_per_indicator = list(units)  # preserve raw codes before label resolution
    unique_raw_units = list(dict.fromkeys(u for u in units if u))
    # Resolve raw unit codes (e.g. "PT") to human-readable labels (e.g. "Percent")
    # using the same extdataportal bundle used by get_viz_spec.
    try:
        from data360.providers import get_codelist_manager as _get_cl_mgr

        _cl = _get_cl_mgr()
        units = [_cl.get_label("UNIT_MEASURE", u) if u else u for u in units]
    except Exception:
        pass
    unique_label_units = list(dict.fromkeys(u for u in units if u))
    shared_unit_raw = unique_raw_units[0] if len(unique_raw_units) == 1 else ""
    shared_unit_label = unique_label_units[0] if len(unique_label_units) == 1 else ""
    chart_title_vl: str | dict = viz_config.build_chart_title_with_context(
        final_chart_title, shared_unit_label or None, merged
    )

    # Append missing-country warning to the subtitle so the user sees it.
    if isinstance(chart_title_vl, dict):
        _sub = chart_title_vl.get("subtitle", [])
        if isinstance(_sub, str):
            _sub = [_sub]
        else:
            _sub = list(_sub)

        present_set = set(merged["country"].dropna().unique()) if "country" in merged.columns else set()
        missing_names = await _detect_missing_countries(country_code, present_set)
        if missing_names:
            missing_str = ", ".join(missing_names)
            _sub.append(f"Note: Data unavailable for {missing_str}.")

        chart_title_vl["subtitle"] = _sub
    elif isinstance(chart_title_vl, str):
        present_set = set(merged["country"].dropna().unique()) if "country" in merged.columns else set()
        missing_names = await _detect_missing_countries(country_code, present_set)
        if missing_names:
            missing_str = ", ".join(missing_names)
            chart_title_vl = {
                "text": chart_title_vl,
                "subtitle": [f"Note: Data unavailable for {missing_str}."]
            }

    # 5b. Shorten titles to their differentiators for mapping, legends, and sub-charts
    diff_map = viz_config._get_label_differentiators(titles)
    shortened_titles = [diff_map.get(t, t) for t in titles]

    # 6. Build indicator_labels for axis/tooltip
    def _format_label(t: str, u: str | None) -> str:
        if not u:
            return t
        # Prevent redundant units like "GDP (annual % growth) (%)"
        if str(u).lower() in str(t).lower():
            return t
        return f"{t} ({u})"

    indicator_labels = {
        col: _format_label(title, unit)
        for col, title, unit in zip(indicator_col_names, shortened_titles, units)
    }

    if series_labels:
        for col in indicator_col_names:
            if col in series_labels:
                indicator_labels[col] = series_labels[col]

    # 7. Build data profile BEFORE routing so coverage signals inform strategy selection
    data_profile = _build_data_profile(
        merged,
        indicator_cols=indicator_col_names,
        indicator_names=titles,
        units_raw=units_raw_per_indicator,
        units_label=list(units),
    )

    # Select strategy (informed by data_profile coverage signals)
    strategy_result = viz_config.select_strategy(
        merged,
        n_indicators=len(indicator_ids),
        chart_type_hint=chart_type,
        indicator_cols=indicator_col_names,
        data_profile=data_profile,
        strategy_override=strategy_override,
    )
    _logger.info(
        f"Multi-indicator strategy: {strategy_result.strategy.value} — {strategy_result.reason}"
    )

    if strategy_result.strategy == viz_config.ChartStrategy.FALLBACK_LINE:
        return _err(
            "Could not determine a suitable visualization strategy for this data structure. "
            "A fallback visualization was not generated."
        )


    # 7b. Reshape for stacked area / stacked bar: melt wide → long
    # build_stacked_area_spec and build_stacked_bar_spec expect a DataFrame with a single "value" column and a
    # "indicator" color column, not the wide merged layout produced by the join above.
    spec_df = merged
    if strategy_result.strategy in (viz_config.ChartStrategy.STACKED_AREA, viz_config.ChartStrategy.STACKED_BAR):
        id_cols = [c for c in merged.columns if c not in indicator_col_names]
        spec_df = merged.melt(
            id_vars=id_cols,
            value_vars=indicator_col_names,
            var_name="indicator",
            value_name="value",
        )
        # Map internal slugified column names back to human-readable indicator titles
        slug_to_title = dict(zip(indicator_col_names, shortened_titles))
        spec_df["indicator"] = spec_df["indicator"].map(slug_to_title)
        spec_df = spec_df.dropna(subset=["value"])
        # Propagate the color_dim so the builder picks up the indicator column
        strategy_result = viz_config.StrategyResult(
            strategy_result.strategy,
            strategy_result.reason,
            indicator_cols=strategy_result.indicator_cols,
            color_dim="indicator",
        )

    # 7c. Reshape for grouped bar or small multiples (multi-indicator path): melt wide → long.
    # build_breakdown_comparison_spec and build_small_multiples_spec expect df["value"] + df[color_dim].
    # When color_dim="indicator" the wide merged frame must be melted so each
    # (country, indicator) pair becomes a row with a single "value" and an
    # "indicator" label column used as the grouping/coloring key.
    elif (
        strategy_result.strategy
        in (
            viz_config.ChartStrategy.BREAKDOWN_COMPARISON,
            viz_config.ChartStrategy.SMALL_MULTIPLES,
        )
        and (strategy_result.color_dim == "indicator" or strategy_result.facet_dim == "indicator")
    ):
        id_cols = [c for c in merged.columns if c not in indicator_col_names]
        spec_df = merged.melt(
            id_vars=id_cols,
            value_vars=indicator_col_names,
            var_name="indicator",
            value_name="value",
        )
        slug_to_title = dict(zip(indicator_col_names, shortened_titles))
        spec_df["indicator"] = spec_df["indicator"].map(slug_to_title)
        spec_df = spec_df.dropna(subset=["value"])

    # 8. Build spec
    try:
        # If there is a shared unit, it makes sense to use it as the Y-axis label.
        # Otherwise, fall back to the second indicator's name (useful for scatterplots).
        # If all units are percentage-based, label the axis as "Percentage".
        is_all_pct = False
        if unique_label_units:
            is_all_pct = all(
                any(x in str(u).lower() for x in ["percent", "pct", "%"])
                for u in unique_label_units
            )

        if shared_unit_label:
            computed_y_label = shared_unit_label
        elif is_all_pct:
            computed_y_label = "Percentage"
        else:
            # For scatterplots, the second indicator maps to the Y axis.
            # Otherwise, use "Value" to avoid misleadingly labeling the axis with only one series name.
            if strategy_result.strategy == viz_config.ChartStrategy.CORRELATION:
                computed_y_label = indicator_labels.get(indicator_col_names[1], "Value")
            else:
                computed_y_label = "Value"

        spec = viz_config.dispatch_spec(
            strategy_result.strategy,
            spec_df,
            chart_title_vl,
            strategy_result,
            indicator_labels=indicator_labels,
            y_label=computed_y_label,
            x_label=indicator_labels.get(indicator_col_names[0], "Value"),
            unit_measure=_unit_measure_for_formatting(
                shared_unit_raw, shared_unit_label
            ),
        )
    except Exception as e:
        _logger.exception(f"Spec build failed: {e}")
        return _err(f"Error building chart spec: {e}")

    # 9. Store and return
    try:
        db_map_multi = await get_database_mapping()
    except Exception as e:
        _logger.warning(f"Could not load database mapping for source attribution: {e}")
        db_map_multi = {}
    db_displays = [
        db_map_multi.get(ind["database_id"], ind["database_id"])
        for ind in indicator_ids
    ]
    unique_db_displays = list(dict.fromkeys(db_displays))
    database_display_multi = (
        unique_db_displays[0]
        if len(unique_db_displays) == 1
        else " · ".join(unique_db_displays)
    )
    indicator_display_multi = " | ".join(titles)
    ids_joined = " · ".join(ind["indicator_id"] for ind in indicator_ids)
    dbs_ids_joined = " · ".join(
        dict.fromkeys(ind["database_id"] for ind in indicator_ids)
    )
    source_attribution_multi: dict[str, str] = {
        "database_id": dbs_ids_joined,
        "database_name": database_display_multi,
        "indicator_id": ids_joined,
        "indicator_name": indicator_display_multi,
    }

    # Apply post-processing rules
    spec = _apply_post_processing_rules(
        spec,
        data_frequency=None,
        unit_measure=_unit_measure_for_formatting(
            shared_unit_raw, shared_unit_label
        ),
        strategy_result=strategy_result,
        df=spec_df
    )

    out_reason = strategy_result.reason
    if strategy_result.strategy == viz_config.ChartStrategy.TEMPORAL_SINGLE:
        resolved_mark = viz_config.extract_top_level_mark_type(spec)
        if resolved_mark:
            out_reason = viz_config.patch_strategy_reason_chart_phrase(
                strategy_result.reason, resolved_mark
            )

    url = await _store_spec(spec)

    # data_profile was built before routing — read from strategy_result
    data_profile = strategy_result.data_profile or {}

    warning_msg = None
    if chart_type:
        requested_lower = chart_type.lower()
        core_intent = (
            requested_lower.replace("chart", "")
            .replace("stacked", "")
            .replace("grouped", "")
            .replace("_", " ")
            .strip()
        )
        if core_intent == "scatter":
            core_intent = "point"
        elif core_intent == "strip":
            core_intent = "beeswarm"

        reason_lower = strategy_result.reason.lower()
        reason_suffix = (
            reason_lower.split("→")[-1] if "→" in reason_lower else reason_lower
        )

        is_scatter_match = (core_intent == "point" and ("scatter" in reason_suffix or "scatterplot" in reason_suffix))
        if core_intent and core_intent not in reason_suffix and not is_scatter_match:
            warning_msg = (
                f"You requested '{chart_type}', but the visualization engine "
                f"selected a different strategy based on data cardinality: {strategy_result.reason}. "
                "The chart was successfully generated. Please ensure your response and the chart title reflect this actual strategy."
            )

    return _ok(
        url,
        warning=warning_msg,
        source_attribution=source_attribution_multi,
        strategy=strategy_result.strategy.value,
        reason=out_reason,
        data_profile=data_profile or None,
        spec=spec,
    )