Py.Cafe

"""
PRMS Portfolio Map — Streamlit dashboard
=======================================
Replaces the Excel "PRMS Portfolio Map" and "Bubble Explorer". Reads the Asset Opportunities
Register from a SQL database and renders the SPE-PRMS 2018 classification
matrix: one row per PRMS sub-class (maturity tier), project cards sorted
largest -> smallest volume inside each row, and class totals at the top.

    streamlit run app.py

Database connection (first one found wins):
    1. .streamlit/secrets.toml   ->  [db] url = "postgresql+psycopg2://..."
    2. environment variable      ->  PRMS_DB_URL
    3. default                   ->  sqlite:///opportunities.db  (next to app.py)
Table name: [db] table in secrets, or PRMS_TABLE, default "opportunity_register".
Build the default SQLite file with:  python load_register_to_db.py <register.xlsx>
"""
from __future__ import annotations

import html
import os
import re
from pathlib import Path

import altair as alt
import pandas as pd
import streamlit as st
from sqlalchemy import MetaData, Table, create_engine, select
from sqlalchemy.engine import make_url

# ════════════════════════════════════════════════════════════════════════════
# 1. CONFIGURATION
# ════════════════════════════════════════════════════════════════════════════
APP_DIR = Path(__file__).parent
UNIT = "mmboe"

# PRMS 2018 matrix, most mature at the top. (sub-class, class)
PRMS_MATRIX: list[tuple[str, str]] = [
    ("On Production", "Reserves"),
    ("Approved For Development", "Reserves"),
    ("Justified For Development", "Reserves"),
    ("Development Pending", "Contingent Resource"),
    ("Development On Hold", "Contingent Resource"),
    ("Development Unclarified", "Contingent Resource"),
    ("Development Not Viable", "Contingent Resource"),
    ("Prospect", "Prospective Resource"),
    ("Lead", "Prospective Resource"),
    ("Idea", "Prospective Resource"),          # PRMS 2018 term: "Play"
]
SUBCLASS_TO_CLASS = dict(PRMS_MATRIX)
CLASS_ORDER = ["Reserves", "Contingent Resource", "Prospective Resource"]
DISCOVERED = {"Reserves", "Contingent Resource"}

CLASS_STYLE = {
    "Reserves":             {"accent": "#1B7A3A", "tint": "#EAF6EE", "short": "Reserves"},
    "Contingent Resource":  {"accent": "#B86B00", "tint": "#FFF4E2", "short": "Contingent"},
    "Prospective Resource": {"accent": "#1F5FA8", "tint": "#E8F0FB", "short": "Prospective"},
}

# Alternative spellings accepted in the tier column -> canonical sub-class
TIER_ALIASES = {
    "approved": "Approved For Development",
    "justified": "Justified For Development",
    "onhold": "Development On Hold",
    "unclarified": "Development Unclarified",
    "notviable": "Development Not Viable",
    "producing": "On Production",
    "play": "Idea",
}

# Logical field -> candidate DB column names (matched case/punctuation-insensitive)
COLUMN_CANDIDATES: dict[str, list[str]] = {
    "opp_id":           ["opp_id", "opportunity_id", "id"],
    "name":             ["opportunity_name", "project_name", "name"],
    "tier":             ["prms_sub_class", "maturity_tier", "tier", "sub_class"],
    "prms_class":       ["prms_class", "resource_class"],
    "risked_net_p50":   ["risked_net_p50_boe_mmboe", "risked_net_p50_mmboe", "risked_net_p50"],
    "net_p50":          ["net_p50_boe_eur_mmboe", "net_p50_boe_mmboe", "net_p50_mmboe", "net_p50"],
    "gross_p50":        ["p50_boe_eur_mmboe", "gross_p50_mmboe", "p50_boe_mmboe", "p50_mmboe"],
    "cos":              ["chance_of_success", "cos", "pg"],
    "effective_equity": ["effective_equity", "working_interest"],
    "bu":               ["bu", "business_unit"],
    "hub":              ["hub"],
    "status":           ["status"],
    "summary_status":   ["summary_status"],
    "opp_type":         ["opportunity_type", "type"],
    "description":      ["description_notes", "description", "notes"],
    "expl_maturity":    ["exploration_maturity"],
    # extra numeric fields used by the bubble explorer (economics come from the Economics sheet)
    "p90":              ["p90_boe_eur_mmboe", "p90_mmboe"],
    "p10":              ["p10_boe_eur_mmboe", "p10_mmboe"],
    "wi":               ["working_interest"],
    "npv":              ["npv_p50", "npv_p50_mm", "npv"],
    "npv_low":          ["npv_low_price_p50"],
    "npv_fail":         ["npv_failure", "npv_failure_case"],
    "emv":              ["emv", "emv_mm"],
    "pir":              ["pir_p50", "pir"],
    "abex":             ["abex"],
    "dist":             ["distance_to_host_km", "distance_to_host"],
    "ttfo":             ["time_to_first_oil_yrs", "time_to_first_oil"],
}
REQUIRED = ["name", "tier"]

# ── Bubble explorer ──
# field: (label, unit, is_percent, usable as bubble size)
BUBBLE_FIELDS: dict[str, tuple[str, str, bool, bool]] = {
    "risked_net_p50": ("Risked Net P50", UNIT, False, True),
    "net_p50":        ("Net P50 (unrisked)", UNIT, False, True),
    "gross_p50":      ("Gross P50 EUR", UNIT, False, True),
    "p90":            ("Gross P90 EUR", UNIT, False, True),
    "p10":            ("Gross P10 EUR", UNIT, False, True),
    "cos":            ("Chance of success", "%", True, True),
    "npv":            ("NPV P50", "$mm", False, False),
    "npv_low":        ("NPV P50 - low price", "$mm", False, False),
    "npv_fail":       ("NPV failure case", "$mm", False, False),
    "emv":            ("EMV (register)", "$mm", False, False),
    "emv_calc":       ("EMV (calculated)", "$mm", False, False),
    "pir":            ("PIR P50", "x", False, True),
    "abex":           ("ABEX", "$mm", False, True),
    "mat_score":      ("Maturity score", "1-7", False, False),
    "wi":             ("Working interest", "%", True, True),
    "dist":           ("Distance to host", "km", False, True),
    "ttfo":           ("Time to first oil", "yrs", False, True),
}
# Maturity score = same lookup as the register's Maturity vs Value sheet
MATURITY_SCORE = {
    "Entry": 1, "Idea": 1, "Lead": 2, "Prospect": 3, "Prioritised Prospect": 4, "Drillable Prospect": 5,
    "Discovery": 6, "Development Unclarified": 6, "Development Pending": 6, "Development On Hold": 6,
    "Development Not Viable": 6, "Justified For Development": 6, "Approved For Development": 7, "On Production": 7,
}
MAT_LABELS = {1: "Entry / Idea", 2: "Lead", 3: "Prospect", 4: "Prioritised", 5: "Drillable",
              6: "Discovery / Contingent", 7: "Reserves"}
COLOUR_BY = {"PRMS class": "prms_class", "Maturity (sub-class)": "tier",
             "Business unit": "bu", "Summary status": "summary_status"}
CATEGORICAL = ["#2A78D6", "#EB6834", "#1BAF7A"]          # validated CVD-safe trio
CLASS_COLOURS = {"Reserves": "#1BAF7A", "Contingent Resource": "#EB6834", "Prospective Resource": "#2A78D6"}
SUB_RAMP = {  # ordinal blue ramp: least mature (light) -> most mature (dark)
    "Idea": "#86B6EF", "Lead": "#6DA7EC", "Prospect": "#5598E7", "Development Not Viable": "#3987E5",
    "Development Unclarified": "#2A78D6", "Development On Hold": "#256ABF", "Development Pending": "#1C5CAB",
    "Justified For Development": "#184F95", "Approved For Development": "#104281", "On Production": "#0D366B",
}
NEUTRAL = "#9AA59F"
SIZE_RANGE = (40, 2400)   # bubble area in px²

VOLUME_METRICS = {
    "Risked Net P50": "risked_net_p50",
    "Net P50 (unrisked)": "net_p50",
    "Gross P50 EUR": "gross_p50",
}

# ════════════════════════════════════════════════════════════════════════════
# 2. DATA ACCESS
# ════════════════════════════════════════════════════════════════════════════
def _key(s: str) -> str:
    return re.sub(r"[^0-9a-z]", "", str(s).lower())


def resolve_db_config() -> tuple[str, str]:
    url = table = None
    try:
        url = st.secrets["db"]["url"]
        table = st.secrets["db"].get("table")
    except Exception:
        pass
    url = url or os.getenv("PRMS_DB_URL") or f"sqlite:///{APP_DIR / 'opportunities.db'}"
    table = table or os.getenv("PRMS_TABLE", "opportunity_register")
    return url, table


@st.cache_resource(show_spinner=False)
def get_engine(url: str):
    return create_engine(url, pool_pre_ping=True)


def source_version(url: str) -> float:
    """Changes whenever a SQLite file is replaced, so the cache refreshes straight after an upload."""
    try:
        u = make_url(url)
        if u.get_backend_name() == "sqlite" and u.database:
            return os.path.getmtime(u.database)
    except Exception:
        pass
    return 0.0


@st.cache_data(ttl=300, show_spinner="Reading opportunity register…")
def read_table(url: str, table: str, version: float = 0.0) -> pd.DataFrame:
    schema, _, name = table.rpartition(".")
    engine = get_engine(url)
    tbl = Table(name, MetaData(), schema=schema or None, autoload_with=engine)  # safe: no string SQL
    with engine.connect() as conn:
        return pd.read_sql(select(tbl), conn)


def normalise_tier(v) -> str | None:
    if pd.isna(v) or str(v).strip() == "":
        return None
    k = _key(v)
    for sub in SUBCLASS_TO_CLASS:
        if _key(sub) == k:
            return sub
    for alias, sub in TIER_ALIASES.items():
        if k == alias or k.endswith(alias):
            return sub
    return str(v).strip()          # unknown → reported in Data Quality


def standardise(raw: pd.DataFrame) -> tuple[pd.DataFrame, dict[str, str | None]]:
    lookup = {_key(c): c for c in raw.columns}
    mapping = {f: next((lookup[_key(c)] for c in cands if _key(c) in lookup), None)
               for f, cands in COLUMN_CANDIDATES.items()}
    df = pd.DataFrame({f: (raw[src] if src else pd.NA) for f, src in mapping.items()}, index=raw.index)

    df = df[df["name"].notna() & (df["name"].astype(str).str.strip() != "")].copy()
    df["opp_id"] = df["opp_id"].fillna(pd.Series([f"ROW-{i+1:03d}" for i in range(len(df))], index=df.index))

    for c in ["risked_net_p50", "net_p50", "gross_p50", "cos", "effective_equity", "p90", "p10", "wi",
              "npv", "npv_low", "npv_fail", "emv", "pir", "abex", "dist", "ttfo"]:
        df[c] = pd.to_numeric(df[c], errors="coerce").astype(float)
    for c in ["cos", "effective_equity", "wi"]:              # accept 72 or 0.72
        df.loc[df[c] > 1, c] = df.loc[df[c] > 1, c] / 100

    df["tier"] = df["tier"].map(normalise_tier)
    df["prms_class"] = df["tier"].map(SUBCLASS_TO_CLASS).fillna(df["prms_class"])  # sub-class is authoritative
    for c in ["bu", "hub", "status", "summary_status", "opp_type", "description"]:
        df[c] = df[c].astype("string").str.strip()
    df["summary_status"] = df["summary_status"].fillna("Unspecified")
    # EMV (calculated) = CoS x NPV P50 + (1 - CoS) x NPV failure, only where all three exist
    df["emv_calc"] = df["cos"] * df["npv"] + (1 - df["cos"]) * df["npv_fail"]
    df["mat_score"] = (df["expl_maturity"].map(MATURITY_SCORE)
                       .fillna(df["tier"].map(MATURITY_SCORE)).astype(float))
    df["bu"] = df["bu"].fillna("Unspecified")
    return df, mapping


def apply_volume(df: pd.DataFrame, metric_field: str, gap_fill: bool) -> pd.DataFrame:
    """Pick the volume shown on cards. Optional gap-fill uses the register's own
    formulas only (Net × CoS, with CoS = 100 % for discovered volumes)."""
    df = df.copy()
    df["vol"] = df[metric_field]
    df["estimated"] = False
    if gap_fill:
        net = df["net_p50"].fillna(df["gross_p50"] * df["effective_equity"])
        if metric_field == "net_p50":
            est = net
        elif metric_field == "risked_net_p50":
            cos = df["cos"].where(df["cos"].notna(), df["prms_class"].isin(DISCOVERED).map({True: 1.0, False: pd.NA}))
            est = net * pd.to_numeric(cos, errors="coerce")
        else:
            est = pd.Series(pd.NA, index=df.index)
        mask = df["vol"].isna() & pd.to_numeric(est, errors="coerce").notna()
        df.loc[mask, "vol"] = pd.to_numeric(est[mask])
        df.loc[mask, "estimated"] = True
    return df


# ════════════════════════════════════════════════════════════════════════════
# 3. RENDERING
# ════════════════════════════════════════════════════════════════════════════
CSS = """<style>
.pm-kpis{display:grid;grid-template-columns:repeat(auto-fit,minmax(220px,1fr));gap:14px;margin:4px 0 18px}
.pm-kpi{background:#fff;border:1px solid #E3E6EA;border-top:5px solid var(--a);border-radius:10px;padding:14px 16px;color:#1d2530}
.pm-kpi .l{font-size:.78rem;letter-spacing:.06em;text-transform:uppercase;color:var(--a);font-weight:700}
.pm-kpi .v{font-size:2rem;font-weight:700;line-height:1.15;margin:4px 0 2px;font-variant-numeric:tabular-nums}
.pm-kpi .v small{font-size:.9rem;font-weight:500;color:#5b6573;margin-left:4px}
.pm-kpi .s{font-size:.8rem;color:#5b6573}
.pm-kpi .s b{color:#b3261e;font-weight:600}
.pm-band{display:flex;border:1px solid #E3E6EA;border-radius:10px;overflow:hidden;margin-bottom:12px;background:#fff}
.pm-bandlbl{background:var(--a);color:#fff;writing-mode:vertical-rl;transform:rotate(180deg);text-align:center;
  font-weight:700;letter-spacing:.12em;text-transform:uppercase;font-size:.78rem;padding:12px 8px;flex:0 0 auto}
.pm-rows{flex:1;min-width:0}
.pm-row{display:flex;border-top:1px solid #EDEFF2}
.pm-row:first-child{border-top:none}
.pm-head{flex:0 0 190px;background:var(--t);padding:10px 12px;color:#1d2530;border-right:1px solid #EDEFF2}
.pm-head .n{font-weight:700;font-size:.92rem;line-height:1.2}
.pm-head .t{font-size:1.25rem;font-weight:700;color:var(--a);margin-top:6px;font-variant-numeric:tabular-nums}
.pm-head .t small{font-size:.75rem;color:#5b6573;font-weight:500}
.pm-head .c{font-size:.75rem;color:#5b6573;margin-top:2px}
.pm-cards{flex:1;display:flex;flex-wrap:wrap;gap:8px;padding:10px;align-content:flex-start;min-width:0}
.pm-empty{color:#98a1ad;font-size:.82rem;font-style:italic;padding:6px 2px}
.pm-card{width:168px;background:#fff;border:1px solid #DDE1E6;border-left:4px solid var(--a);border-radius:7px;
  padding:7px 9px 8px;color:#1d2530;box-shadow:0 1px 2px rgba(16,24,40,.05)}
.pm-card .r{display:flex;justify-content:space-between;font-size:.68rem;color:#6b7480;gap:6px}
.pm-card .r .bu{background:#F1F3F5;border-radius:4px;padding:0 5px;white-space:nowrap}
.pm-card .nm{font-weight:650;font-size:.8rem;line-height:1.2;margin:3px 0 4px;height:2.4em;overflow:hidden}
.pm-card .v{font-size:1.05rem;font-weight:700;font-variant-numeric:tabular-nums}
.pm-card .v small{font-size:.68rem;color:#6b7480;font-weight:500}
.pm-card .v .est{font-size:.66rem;color:#8a5a00;background:#FFF1D6;border-radius:3px;padding:0 4px;margin-left:4px;font-weight:600}
.pm-card .bar{height:4px;background:#EEF0F3;border-radius:2px;margin:4px 0 5px;overflow:hidden}
.pm-card .bar i{display:block;height:100%;background:var(--a)}
.pm-card .m{font-size:.68rem;color:#6b7480}
.pm-card.novol{border-left-style:dashed;background:#FAFBFC}
.pm-card.novol .v{color:#98a1ad;font-size:.85rem}
.pm-card.stopped{opacity:.55}
.pm-card.stopped .nm::after{content:" · stopped";font-weight:500;color:#b3261e;font-size:.68rem}
@media (max-width:700px){.pm-row{flex-direction:column}.pm-head{flex:none;border-right:none;border-bottom:1px solid #EDEFF2}
  .pm-card{width:calc(50% - 4px)}}
</style>"""


def fmt(v: float | None) -> str:
    if v is None or pd.isna(v):
        return "–"
    return f"{v:,.1f}" if abs(v) >= 10 else f"{v:,.2f}"


def e(s) -> str:
    return "" if s is None or pd.isna(s) else html.escape(str(s))


def kpi_html(df: pd.DataFrame, metric_label: str) -> str:
    tiles = []
    for cls in CLASS_ORDER:
        sub = df[df["prms_class"] == cls]
        st_ = CLASS_STYLE[cls]
        missing = int(sub["vol"].isna().sum())
        est = int(sub["estimated"].sum())
        notes = [f"{len(sub)} projects"]
        if est:
            notes.append(f"{est} estimated")
        if missing:
            notes.append(f"<b>{missing} without volume</b>")
        tiles.append(
            f'<div class="pm-kpi" style="--a:{st_["accent"]}"><div class="l">{cls}</div>'
            f'<div class="v">{fmt(sub["vol"].sum(min_count=1)) if len(sub) else "0.00"}<small>{UNIT}</small></div>'
            f'<div class="s">{metric_label} · {" · ".join(notes)}</div></div>'
        )
    return f'<div class="pm-kpis">{"".join(tiles)}</div>'


def card_html(r: pd.Series, rank: int, vmax: float) -> str:
    has_vol = pd.notna(r["vol"])
    cls = ["pm-card"] + ([] if has_vol else ["novol"]) + (["stopped"] if r["summary_status"] == "Stopped" else [])
    if has_vol:
        est = '<span class="est">est.</span>' if r["estimated"] else ""
        val = f'{fmt(r["vol"])}<small> {UNIT}</small>{est}'
        width = max(2.0, 100 * r["vol"] / vmax) if vmax else 0
    else:
        val, width = "no volume", 0
    meta = []
    if pd.notna(r["cos"]):
        meta.append(f"CoS {r['cos']:.0%}")
    if pd.notna(r["hub"]):
        meta.append(e(r["hub"]))
    tip = " | ".join(x for x in [e(r["opp_id"]), e(r["name"]), e(r["status"]), e(r["description"])] if x)
    return (
        f'<div class="{" ".join(cls)}" title="{tip}">'
        f'<div class="r"><span>{f"#{rank} · " if has_vol else ""}{e(r["opp_id"])}</span><span class="bu">{e(r["bu"])}</span></div>'
        f'<div class="nm">{e(r["name"])}</div><div class="v">{val}</div>'
        f'<div class="bar"><i style="width:{width:.1f}%"></i></div>'
        f'<div class="m">{" · ".join(meta) or "&nbsp;"}</div></div>'
    )


def matrix_html(df: pd.DataFrame, show_empty: bool) -> str:
    vmax = float(df["vol"].max()) if df["vol"].notna().any() else 0.0
    bands = []
    for cls in CLASS_ORDER:
        style = CLASS_STYLE[cls]
        rows = []
        for sub, c in PRMS_MATRIX:
            if c != cls:
                continue
            g = df[df["tier"] == sub].sort_values(["vol", "name"], ascending=[False, True], na_position="last")
            if g.empty and not show_empty:
                continue
            n_missing = int(g["vol"].isna().sum())
            cnt = f"{len(g)} project{'s' if len(g) != 1 else ''}" + (f" · {n_missing} no volume" if n_missing else "")
            cards = "".join(card_html(r, i + 1, vmax) for i, (_, r) in enumerate(g.iterrows())) \
                or '<div class="pm-empty">No projects in this sub-class</div>'
            rows.append(
                f'<div class="pm-row"><div class="pm-head"><div class="n">{sub}</div>'
                f'<div class="t">{fmt(g["vol"].sum(min_count=1)) if len(g) else "0.00"} <small>{UNIT}</small></div>'
                f'<div class="c">{cnt}</div></div><div class="pm-cards">{cards}</div></div>'
            )
        if rows:
            bands.append(
                f'<div class="pm-band" style="--a:{style["accent"]};--t:{style["tint"]}">'
                f'<div class="pm-bandlbl">{style["short"]}</div><div class="pm-rows">{"".join(rows)}</div></div>'
            )
    return "".join(bands)


def colour_scale(df: pd.DataFrame, field: str) -> alt.Scale:
    if field == "prms_class":
        dom = [c for c in CLASS_ORDER if c in set(df[field])]
        return alt.Scale(domain=dom, range=[CLASS_COLOURS[c] for c in dom])
    if field == "tier":
        dom = [s for s, _ in PRMS_MATRIX if s in set(df[field])]
        return alt.Scale(domain=dom, range=[SUB_RAMP[s] for s in dom])
    if field == "summary_status":
        dom = [s for s in ["Active", "Inactive", "Stopped"] if s in set(df[field])]
        return alt.Scale(domain=dom, range=[{"Active": CATEGORICAL[0], "Inactive": CATEGORICAL[1]}.get(s, NEUTRAL) for s in dom])
    dom = sorted(set(df[field].dropna()))
    return alt.Scale(domain=dom, range=[CATEGORICAL[i] if i < 3 else NEUTRAL for i in range(len(dom))])


def axis_title(f: str) -> str:
    label, unit, pct, _ = BUBBLE_FIELDS[f]
    return label if pct or f == "mat_score" else f"{label} ({unit})"


def quant(f: str, title: str, log: bool, orient: str) -> dict:
    label, unit, pct, _ = BUBBLE_FIELDS[f]
    if f == "mat_score":
        expr = " : ".join(f"datum.value == {k} ? '{v}'" for k, v in MAT_LABELS.items()) + " : ''"
        return dict(scale=alt.Scale(domain=[0.5, 7.5]), axis=alt.Axis(values=list(MAT_LABELS), labelExpr=expr, title=title))
    if pct:
        return dict(scale=alt.Scale(domain=[-0.03, 1.14], nice=False),
                    axis=alt.Axis(format=".0%", title=title, values=[i / 10 for i in range(11)]))
    return dict(scale=alt.Scale(type="log") if log else alt.Scale(zero=False, nice=True), axis=alt.Axis(title=title))


def is_dark_theme() -> bool:
    try:
        return getattr(st.context.theme, "type", "light") == "dark"
    except Exception:
        return False


def bubble_chart(pts: pd.DataFrame, x: str, y: str, size: str, colour: str,
                 div_x: float | None, div_y: float | None, log_x: bool, log_y: bool, n_labels: int) -> alt.LayerChart:
    d = pts.copy()
    if size != "none":
        d["_size"] = d[size].where(d[size] > 0)
        zmax = float(d["_size"].max()) if d["_size"].notna().any() else 1.0
    else:
        d["_size"], zmax = 1.0, 1.0
    d["_hollow"] = d["_size"].isna()
    d["_size_plot"] = d["_size"].fillna(0.0)
    # radius in px (for label offsets) from the same linear area scale Vega-Lite uses
    area = SIZE_RANGE[0] + (SIZE_RANGE[1] - SIZE_RANGE[0]) * d["_size_plot"] / zmax
    d["_dx"] = ((area / 3.1416) ** 0.5 + 5).round().clip(lower=8)
    d["_label"] = d["name"].astype(str).str.slice(0, 28)
    for c in ["name", "opp_id", "bu", "tier", "prms_class", "summary_status", "hub"]:
        d[c] = d[c].astype(object).where(d[c].notna(), None)

    fmt = lambda f: ".0%" if BUBBLE_FIELDS[f][2] else (".0f" if f == "mat_score" else ",.2f")
    tips = [alt.Tooltip("name:N", title="Opportunity"), alt.Tooltip("opp_id:N", title="Opp ID"),
            alt.Tooltip("bu:N", title="BU"), alt.Tooltip("tier:N", title="Sub-class")]
    for f in dict.fromkeys([y, x] + ([size] if size != "none" else [])):
        tips.append(alt.Tooltip(f"{f}:Q", title=axis_title(f), format=fmt(f)))

    X = alt.X(f"{x}:Q", **quant(x, axis_title(x) + (" - log" if log_x else ""), log_x, "bottom"))
    Y = alt.Y(f"{y}:Q", **quant(y, axis_title(y) + (" - log" if log_y else ""), log_y, "left"))
    cscale = colour_scale(d, colour)
    legend = alt.Legend(orient="top", title=None, symbolType="circle", labelLimit=220)
    pick = alt.selection_point(name="pick", fields=["opp_id"], on="click")

    base = alt.Chart(d)
    points = base.mark_circle(strokeWidth=2).encode(
        x=X, y=Y,
        size=alt.Size("_size_plot:Q", scale=alt.Scale(domain=[0, zmax], range=list(SIZE_RANGE)), legend=None),
        color=alt.Color(f"{colour}:N", scale=cscale, legend=legend),
        stroke=alt.Stroke(f"{colour}:N", scale=cscale, legend=None),
        fillOpacity=alt.condition("datum._hollow", alt.value(0.0), alt.value(0.78)),
        order=alt.Order("_size_plot:Q", sort="descending"),          # small bubbles drawn on top
        tooltip=tips,
    ).add_params(pick)

    layers = []
    xs, ys = d[x].dropna(), d[y].dropna()
    if not log_y and not BUBBLE_FIELDS[y][2] and len(ys) and ys.min() < 0 < ys.max():
        layers.append(alt.Chart(pd.DataFrame({"v": [0]})).mark_rule(color="#6B7870", strokeWidth=1).encode(y="v:Q"))
    rule = dict(strokeDash=[4, 4], color="#6B7870", strokeWidth=1)
    if div_x is not None and (not log_x or div_x > 0):
        layers.append(alt.Chart(pd.DataFrame({"v": [div_x]})).mark_rule(**rule).encode(x=alt.X("v:Q")))
    if div_y is not None and (not log_y or div_y > 0):
        layers.append(alt.Chart(pd.DataFrame({"v": [div_y]})).mark_rule(**rule).encode(y=alt.Y("v:Q")))
    layers.append(points)

    lab = d.sort_values("_size_plot", ascending=False).head(n_labels)
    for dx, g in lab.groupby("_dx"):                                # Vega-Lite dx is per-layer, so bucket by radius
        layers.append(alt.Chart(g).mark_text(align="left", baseline="middle", dx=float(dx), fontSize=11,
                                             fontWeight=600, color="#E4EBE6" if is_dark_theme() else "#15201A")
                      .encode(x=alt.X(f"{x}:Q"), y=alt.Y(f"{y}:Q"), text="_label:N"))

    return alt.layer(*layers).properties(height=540)


def show_detail(r: pd.Series) -> None:
    st.subheader(f"{r['opp_id']} · {r['name']}")
    c1, c2, c3, c4 = st.columns(4)
    c1.metric(f"PRMS sub-class · {r['prms_class'] if pd.notna(r['prms_class']) else 'unclassified'}", r["tier"] or "–")
    c2.metric(f"Risked Net P50 ({UNIT})", fmt(r["risked_net_p50"]))
    c3.metric(f"Net P50 ({UNIT})", fmt(r["net_p50"]))
    c4.metric("Chance of success", f"{r['cos']:.0%}" if pd.notna(r["cos"]) else "–")
    c5, c6, c7, c8 = st.columns(4)
    c5.metric("NPV P50 ($mm)", fmt(r["npv"]))
    c6.metric("EMV calculated ($mm)", fmt(r["emv_calc"]))
    c7.metric("PIR P50", f"{r['pir']:.2f}x" if pd.notna(r["pir"]) else "–")
    c8.metric("Maturity score", f"{int(r['mat_score'])} · {MAT_LABELS[int(r['mat_score'])]}" if pd.notna(r["mat_score"]) else "–")
    if pd.notna(r["description"]):
        st.write(r["description"])


# ════════════════════════════════════════════════════════════════════════════
# 4. APP
# ════════════════════════════════════════════════════════════════════════════
st.set_page_config(page_title="PRMS Portfolio Map", page_icon="🛢️", layout="wide")
st.markdown(CSS, unsafe_allow_html=True)

db_url, db_table = resolve_db_config()
try:
    raw = read_table(db_url, db_table, source_version(db_url))
except Exception as exc:
    st.error(f"Could not read table **{db_table}** from `{make_url(db_url).render_as_string(hide_password=True)}`\n\n{exc}")
    st.info("Build the default database with:  `python load_register_to_db.py <Asset_Opportunities_Register.xlsx>`")
    st.stop()

base, mapping = standardise(raw)
missing_req = [f for f in REQUIRED if mapping[f] is None]
if missing_req or all(mapping[v] is None for v in VOLUME_METRICS.values()):
    st.error(f"Required columns not found: {missing_req or 'a P50 volume column'}. "
             f"Add an alias in COLUMN_CANDIDATES. Columns in table: {list(raw.columns)}")
    st.stop()

# ── Sidebar ────────────────────────────────────────────────────────────────
with st.sidebar:
    st.header("Filters")
    bus = sorted(base["bu"].dropna().unique())
    sel_bu = st.multiselect("Business Unit", bus, default=bus)
    statuses = sorted(base["summary_status"].dropna().unique())
    sel_status = st.multiselect("Summary status", statuses,
                                default=[s for s in statuses if s != "Stopped"] or statuses,
                                help="Stopped opportunities are excluded by default.")
    hubs = sorted(base["hub"].dropna().unique())
    sel_hub = st.multiselect("Hub", hubs, placeholder="All hubs")
    query = st.text_input("Search name / ID", placeholder="e.g. Tuna, OPP-012")

    st.header("Volume")
    available = [k for k, v in VOLUME_METRICS.items() if mapping[v] is not None]
    metric_label = st.radio("Metric on cards & totals", available, index=0)
    min_vol = st.number_input(f"Hide cards below ({UNIT})", min_value=0.0, value=0.0, step=0.5,
                              help="Cards without a volume are always shown so gaps stay visible.")
    gap_fill = st.toggle("Estimate missing volumes", value=False,
                         help="Uses the register formulas only: Net P50 = Gross × Effective equity; "
                              "Risked = Net × CoS, with CoS = 100 % for discovered (Reserves/Contingent). "
                              "Estimated cards are tagged 'est.'")

    st.header("Display")
    show_empty = st.toggle("Show empty sub-classes", value=True)

    st.divider()
    st.caption(f"Source: `{make_url(db_url).render_as_string(hide_password=True)}` · table `{db_table}`")
    if st.button("↻ Reload from database", width="stretch"):
        read_table.clear()
        st.rerun()

# ── Filter ─────────────────────────────────────────────────────────────────
df = apply_volume(base, VOLUME_METRICS[metric_label], gap_fill)
mask = df["bu"].isin(sel_bu) & df["summary_status"].isin(sel_status)
if sel_hub:
    mask &= df["hub"].isin(sel_hub)
if query:
    q = query.strip().lower()
    mask &= df["name"].astype(str).str.lower().str.contains(q, regex=False) | \
            df["opp_id"].astype(str).str.lower().str.contains(q, regex=False)
if min_vol > 0:
    mask &= df["vol"].isna() | (df["vol"] >= min_vol)
view = df[mask]

# ── Header & KPIs ──────────────────────────────────────────────────────────
st.title("PRMS Portfolio Map")
st.caption(f"SPE-PRMS 2018 classification · {len(view)} of {len(df)} opportunities shown · "
           f"cards sorted by {metric_label} ({UNIT}), largest first")
st.markdown(kpi_html(view, metric_label), unsafe_allow_html=True)
st.caption("Class totals are shown separately on purpose — PRMS does not permit adding Reserves, "
           "Contingent and Prospective volumes into a single number.")

tab_map, tab_bub, tab_reg, tab_dq = st.tabs(["Portfolio matrix", "Bubble explorer", "Register", "Data quality"])

with tab_map:
    if view.empty:
        st.info("No opportunities match the current filters.")
    else:
        st.markdown(matrix_html(view, show_empty), unsafe_allow_html=True)
        st.caption("Hover a card for status and description. Bar length is relative to the largest card on the map.")

with tab_bub:
    avail = [f for f in BUBBLE_FIELDS if df[f].notna().any()]
    count = {f: int(df[f].notna().sum()) for f in avail}
    label_of = lambda f: f"{axis_title(f)} · {count[f]}" if f in count else "Same size for all"
    pick_default = lambda opts, want: opts.index(want) if want in opts else 0
    b1, b2, b3, b4 = st.columns(4)
    bx = b1.selectbox("X axis", avail, index=pick_default(avail, "cos"), format_func=label_of, key="bx")
    by = b2.selectbox("Y axis", avail, index=pick_default(avail, "npv"), format_func=label_of, key="by")
    size_opts = [f for f in avail if BUBBLE_FIELDS[f][3]] + ["none"]
    bs = b3.selectbox("Bubble size", size_opts, index=pick_default(size_opts, "risked_net_p50"), format_func=label_of, key="bs")
    bc = COLOUR_BY[b4.selectbox("Colour by", list(COLOUR_BY), key="bc")]
    st.caption("The number after each field is how many opportunities in the register have a value for it.")

    pts = view[view[bx].notna() & view[by].notna()]

    def default_div(f: str) -> float:
        if f == "cos": return 50.0
        if f == "npv": return 30.0
        if f == "mat_score": return 5.5
        v = df[f].dropna()
        med = float(v.median()) if len(v) else 0.0
        return round(med * (100 if BUBBLE_FIELDS[f][2] else 1), 2)

    o1, o2, o3, o4, o5, o6 = st.columns([1.3, 1, 1, 0.8, 0.8, 1.2])
    show_div = o1.toggle("Quadrant dividers", value=True, key="bdiv")
    dx_in = o2.number_input(f"X divider{' (%)' if BUBBLE_FIELDS[bx][2] else ''}", value=default_div(bx), key=f"dx_{bx}", disabled=not show_div)
    dy_in = o3.number_input(f"Y divider{' (%)' if BUBBLE_FIELDS[by][2] else ''}", value=default_div(by), key=f"dy_{by}", disabled=not show_div)
    can_log_x = bx != "mat_score" and not BUBBLE_FIELDS[bx][2] and len(pts) > 0 and bool((pts[bx] > 0).all())
    can_log_y = by != "mat_score" and not BUBBLE_FIELDS[by][2] and len(pts) > 0 and bool((pts[by] > 0).all())
    log_x = o4.toggle("Log X", key="blogx", disabled=not can_log_x) and can_log_x
    log_y = o5.toggle("Log Y", key="blogy", disabled=not can_log_y) and can_log_y
    label_all = o6.toggle("Name every bubble", key="ball")

    if pts.empty:
        st.info("No opportunity has both of these values. Pick other axes, or fill them in the register.")
    else:
        to_frac = lambda v, f: v / 100 if BUBBLE_FIELDS[f][2] else v
        chart = bubble_chart(pts, bx, by, bs, bc,
                             to_frac(dx_in, bx) if show_div else None, to_frac(dy_in, by) if show_div else None,
                             log_x, log_y, len(pts) if label_all else 7)
        ev = st.altair_chart(chart, width="stretch", on_select="rerun", selection_mode="pick", key="bubble")
        missing_x, missing_y = int(view[bx].isna().sum()), int(view[by].isna().sum())
        note = f"**{len(pts)} of {len(view)} plotted.** "
        if len(pts) < len(view):
            note += f"{len(view) - len(pts)} not shown - {missing_x} missing {BUBBLE_FIELDS[bx][0]}, {missing_y} missing {BUBBLE_FIELDS[by][0]}. "
        if bs != "none":
            note += f"Bubble area ∝ {BUBBLE_FIELDS[bs][0]}; hollow = no value. "
        if "emv_calc" in (bx, by):
            note += "EMV (calculated) = CoS × NPV P50 + (1 − CoS) × NPV failure. "
        st.caption(note + "Hover for values; click a bubble for its detail.")
        picked = (ev.selection.get("pick") or []) if ev and getattr(ev, "selection", None) else []
        if picked and picked[0].get("opp_id") is not None:
            match = view[view["opp_id"] == picked[0]["opp_id"]]
            if not match.empty:
                show_detail(match.iloc[0])

with tab_reg:
    table = view.assign(
        prms_class=pd.Categorical(view["prms_class"], CLASS_ORDER, ordered=True),
        tier_rank=view["tier"].map({s: i for i, (s, _) in enumerate(PRMS_MATRIX)}),
    ).sort_values(["tier_rank", "vol"], ascending=[True, False], na_position="last")
    cols = ["opp_id", "name", "bu", "hub", "prms_class", "tier", "summary_status", "status",
            "cos", "gross_p50", "net_p50", "risked_net_p50", "vol", "estimated"]
    vmax = float(table["vol"].max()) if table["vol"].notna().any() else 1.0
    event = st.dataframe(
        table[cols], hide_index=True, width="stretch", height=460,
        on_select="rerun", selection_mode="single-row",
        column_config={
            "opp_id": "Opp ID", "name": st.column_config.TextColumn("Opportunity", width="large"),
            "bu": "BU", "hub": "Hub", "prms_class": "PRMS class", "tier": "Sub-class",
            "summary_status": "Summary", "status": "Status",
            "cos": st.column_config.NumberColumn("CoS", format="percent"),
            "gross_p50": st.column_config.NumberColumn(f"Gross P50 ({UNIT})", format="%.2f"),
            "net_p50": st.column_config.NumberColumn(f"Net P50 ({UNIT})", format="%.2f"),
            "risked_net_p50": st.column_config.NumberColumn(f"Risked Net P50 ({UNIT})", format="%.2f"),
            "vol": st.column_config.ProgressColumn(f"Shown: {metric_label}", format="%.2f", min_value=0, max_value=vmax),
            "estimated": st.column_config.CheckboxColumn("Est."),
        },
    )
    st.download_button("Download filtered register (CSV)", table[cols].to_csv(index=False).encode(),
                       "prms_portfolio_filtered.csv", "text/csv")
    rows_sel = event.selection.rows if event and event.selection else []
    if rows_sel:
        show_detail(table.iloc[rows_sel[0]])
    else:
        st.caption("Select a row to see the opportunity detail.")

with tab_dq:
    issues = []
    for _, r in df.iterrows():
        if r["tier"] is None:
            issues.append((r["opp_id"], r["name"], "PRMS sub-class blank — not placed on the map"))
        elif r["tier"] not in SUBCLASS_TO_CLASS:
            issues.append((r["opp_id"], r["name"], f"Unrecognised sub-class '{r['tier']}' — add to TIER_ALIASES"))
        if pd.isna(r["vol"]):
            why = []
            if pd.isna(r["gross_p50"]): why.append("P50 EUR")
            if pd.isna(r["effective_equity"]) and pd.isna(r["net_p50"]): why.append("working interest")
            if pd.isna(r["cos"]) and r["prms_class"] == "Prospective Resource": why.append("chance of success")
            issues.append((r["opp_id"], r["name"], f"No {metric_label}" + (f" — missing {', '.join(why)}" if why else "")))
        elif r["estimated"]:
            issues.append((r["opp_id"], r["name"], f"{metric_label} estimated in dashboard — confirm in register"))
    if issues:
        st.warning(f"{len(issues)} items to fix in the register (all opportunities, ignoring filters).")
        st.dataframe(pd.DataFrame(issues, columns=["Opp ID", "Opportunity", "Issue"]),
                     hide_index=True, width="stretch")
    else:
        st.success("Every opportunity has a sub-class and a volume.")
    with st.expander("Column mapping used"):
        st.json({k: v for k, v in mapping.items()})