# -*- coding: utf-8 -*-
from __future__ import annotations

from pathlib import Path
from datetime import datetime, timedelta
from typing import Optional, List, Dict, Any, Callable
import re, time, json, shutil
import xml.etree.ElementTree as ET

import requests
import pandas as pd
import numpy as np

BASE_URL = "https://live.trading212.com/api/v0"
API_LIMIT = 50
SLEEP_SECONDS = 1
ACCOUNT_CURRENCY_FALLBACK = "EUR"
APPROX_FX_TO_EUR_FALLBACK = {
    "USD": 0.85,
    "GBP": 1.16,
}

DATE_COLS = {
    "orders": ["fill.filledAt", "order.createdAt", "dateCreated", "dateExecuted", "fillDate", "filledOn", "created", "executed", "timestamp", "date", "time"],
    "transactions": ["dateTime", "date", "timestamp", "time", "created", "eventDate", "createdOn"],
    "dividends": ["paidOn", "date", "timestamp", "time", "created", "eventDate", "createdOn"],
}

HISTORY_ENDPOINTS = {
    "orders": "/equity/history/orders",
    "transactions": "/equity/history/transactions",
    "dividends": "/equity/history/dividends",
}

MANUAL_CURRENCY = {
    "ASMLa_EQ": "EUR",
    "ATOp_EQ": "EUR",
    "GREp_EQ": "EUR",
    "VUAAm_EQ": "EUR",
}

ALIASES = {}

def clean_symbol_label(value):
    s = "" if value is None else str(value)
    s = re.sub(r"_US_EQ$", "", s, flags=re.IGNORECASE)
    s = re.sub(r"_EQ$", "", s, flags=re.IGNORECASE)
    s = re.sub(r"_US$", "", s, flags=re.IGNORECASE)
    return s

OLD_DEFAULT_ALIASES = {
    "IPOE_US_EQ / old SOFI SPAC",
    "YNDX_US_EQ / old Yandex",
}

def normalize_mapping_defaults(df):
    if df is None or df.empty or "symbol_raw" not in df.columns:
        return df
    out = df.copy()
    for col in ["true_ticker", "display_name"]:
        if col not in out.columns:
            out[col] = np.nan
        cleaned = out["symbol_raw"].apply(clean_symbol_label)
        raw = out["symbol_raw"].astype(str)
        cur = out[col].astype(str)
        blank = out[col].isna() | (cur.str.strip() == "") | (cur.str.lower() == "nan")
        looks_default = blank | (cur == raw) | cur.isin(OLD_DEFAULT_ALIASES) | cur.str.endswith("_US_EQ", na=False) | cur.str.endswith("_EQ", na=False)
        out.loc[looks_default, col] = cleaned[looks_default]
    return out

def default_log(msg: str):
    print(msg)

def app_base_dir() -> Path:
    import sys
    return Path(sys.executable).resolve().parent if getattr(sys, "frozen", False) else Path(__file__).resolve().parent

def paths(base_dir: Optional[Path]=None) -> Dict[str, Path]:
    b = Path(base_dir) if base_dir else app_base_dir()
    p = {
        "base": b,
        "data": b/"data",
        "raw": b/"data"/"raw",
        "silver": b/"data"/"silver",
        "gold": b/"data"/"gold",
        "cache": b/"data"/"yearly_cache",
        "output": b/"output",
        "analysis_csv": b/"output"/"analysis_csv",
        "mapping": b/"mapping",
        "manual": b/"data"/"manual_inputs",
        "external": b/"data"/"external",
        "benchmarks": b/"data"/"external"/"benchmarks",
        "sp500_benchmark_file": b/"data"/"external"/"benchmarks"/"sp500_daily_stooq.csv",
        "sp500_benchmark_meta_file": b/"data"/"external"/"benchmarks"/"sp500_daily_stooq_meta.json",
        "manual_transactions_file": b/"data"/"manual_inputs"/"manual_transactions.csv",
        "manual_positions_file": b/"data"/"manual_inputs"/"manual_positions.csv",
        "mapping_file": b/"mapping"/"ticker_mapping.csv",
    }
    file_keys = {"mapping_file", "manual_transactions_file", "manual_positions_file", "sp500_benchmark_file", "sp500_benchmark_meta_file"}
    for k, v in p.items():
        if k not in file_keys:
            v.mkdir(parents=True, exist_ok=True)
    return p

def save_df(df, path):
    path.parent.mkdir(parents=True, exist_ok=True)
    df.to_csv(path, index=False, encoding="utf-8-sig")

def backup_local_state(base_dir: Optional[Path]=None, label: str="before_local_data_change") -> Optional[Path]:
    """Move local data/output/mapping folders into source_backups before replacing them."""
    b = Path(base_dir) if base_dir else app_base_dir()
    safe_label = re.sub(r"[^A-Za-z0-9_-]+", "_", str(label).strip()) or "backup"
    targets = [b / "data", b / "output", b / "mapping"]
    existing = [p for p in targets if p.exists()]
    if not existing:
        return None
    backup_root = b / "source_backups" / f"{safe_label}_{datetime.now():%Y%m%d_%H%M%S}"
    backup_root.mkdir(parents=True, exist_ok=True)
    for p in existing:
        shutil.move(str(p), str(backup_root / p.name))
    return backup_root

def list_local_state_backups(base_dir: Optional[Path]=None) -> List[Dict[str, Any]]:
    """List restorable local data/output/mapping backups, newest first."""
    b = Path(base_dir) if base_dir else app_base_dir()
    backup_root = b / "source_backups"
    if not backup_root.exists():
        return []
    backups = []
    for child in backup_root.iterdir():
        if not child.is_dir():
            continue
        has_data = (child / "data").is_dir()
        has_output = (child / "output").is_dir()
        has_mapping = (child / "mapping").is_dir()
        if not (has_data or has_output or has_mapping):
            continue
        stat = child.stat()
        marker = child / "data" / "demo_showcase_marker.json"
        backups.append({
            "name": child.name,
            "modified_ts": float(stat.st_mtime),
            "modified": datetime.fromtimestamp(stat.st_mtime).strftime("%Y-%m-%d %H:%M:%S"),
            "has_data": has_data,
            "has_output": has_output,
            "has_mapping": has_mapping,
            "is_demo": marker.exists(),
        })
    return sorted(backups, key=lambda row: row["modified_ts"], reverse=True)

def resolve_local_state_backup(base_dir: Optional[Path], backup_name: str) -> Path:
    b = Path(base_dir) if base_dir else app_base_dir()
    name = str(backup_name or "").strip()
    if not name or Path(name).name != name:
        raise ValueError("Invalid backup name.")
    backup_root = (b / "source_backups").resolve()
    backup = (backup_root / name).resolve()
    if backup.parent != backup_root or not backup.is_dir():
        raise ValueError("Backup folder not found.")
    if not ((backup / "data").is_dir() or (backup / "output").is_dir() or (backup / "mapping").is_dir()):
        raise ValueError("Backup folder does not contain restorable data/output/mapping folders.")
    return backup

def restore_local_state_backup(base_dir: Optional[Path], backup_name: str, label: str="before_restore") -> Dict[str, Any]:
    """Restore selected backup after first backing up current active local state."""
    b = Path(base_dir) if base_dir else app_base_dir()
    backup = resolve_local_state_backup(b, backup_name)
    current_backup = backup_local_state(b, label)
    restored = []
    for folder_name in ["data", "output", "mapping"]:
        src = backup / folder_name
        dst = b / folder_name
        if src.is_dir():
            if dst.exists():
                raise ValueError(f"Cannot restore because active folder still exists: {dst}")
            shutil.copytree(src, dst)
            restored.append(folder_name)
    return {
        "restored_from": backup.name,
        "current_backup": str(current_backup) if current_backup else "",
        "restored_folders": restored,
    }

def delete_local_state_backup(base_dir: Optional[Path], backup_name: str) -> Dict[str, Any]:
    """Delete a single selected local backup folder under source_backups."""
    backup = resolve_local_state_backup(base_dir, backup_name)
    shutil.rmtree(backup)
    return {"deleted": backup.name}

def read_csv(path):
    return pd.read_csv(path) if path.exists() else pd.DataFrame()

def flatten(x):
    if x is None:
        return pd.DataFrame()
    if isinstance(x, list):
        return pd.json_normalize(x)
    if isinstance(x, dict):
        return pd.json_normalize(x.get("items", [])) if "items" in x else pd.json_normalize([x])
    return pd.DataFrame()

def first_col(df, candidates):
    if df is None or df.empty:
        return None
    lm = {str(c).lower(): c for c in df.columns}
    for c in candidates:
        if c.lower() in lm:
            return lm[c.lower()]
    return None

def num(df, col, default=0.0):
    if not col or col not in df.columns:
        return pd.Series(default, index=df.index, dtype="float64")
    return pd.to_numeric(df[col], errors="coerce").fillna(default)

def add_source(df, source="trading212", broker="trading212"):
    out = df.copy() if df is not None else pd.DataFrame()
    if not out.empty:
        if "source" not in out.columns:
            out["source"] = source
        if "broker" not in out.columns:
            out["broker"] = broker
    return out

def ensure_date(df, candidates):
    if df is None or df.empty:
        return df
    c = first_col(df, candidates)
    if not c:
        df["event_date"] = pd.NaT
        return df
    df["event_date"] = pd.to_datetime(df[c], errors="coerce", utc=True)
    df["year"] = df["event_date"].dt.year
    df["quarter"] = df["event_date"].dt.quarter
    df["month"] = df["event_date"].dt.month
    df["year_month"] = df["event_date"].dt.strftime("%Y-%m")
    df["year_quarter"] = df["year"].astype("Int64").astype(str) + "Q" + df["quarter"].astype("Int64").astype(str)
    return df

def safe_div(a, b):
    a = pd.to_numeric(a, errors="coerce")
    b = pd.to_numeric(b, errors="coerce")
    return np.where(b != 0, a / b, np.nan)

class Trading212Client:
    def __init__(self, api_key, api_secret, log=default_log):
        self.session = requests.Session()
        self.session.auth = (api_key, api_secret)
        self.log = log

    @staticmethod
    def describe_endpoint(endpoint):
        clean = str(endpoint or "").split("?", 1)[0]
        clean = clean.replace("https://live.trading212.com/api/v0", "")
        clean = clean.replace("https://live.trading212.com", "")
        clean = clean.replace(BASE_URL, "")
        clean = clean.replace("/api/v0", "")
        return clean or "Trading212 API"

    def get(self, endpoint, params=None):
        if endpoint.startswith("http"):
            url = endpoint
        elif endpoint.startswith("/api/v0"):
            url = "https://live.trading212.com" + endpoint
        else:
            url = BASE_URL + endpoint
        r = self.session.get(url, params=params, timeout=60)
        if r.status_code == 429:
            self.log("Rate limited. Waiting 65 seconds...")
            time.sleep(65)
            r = self.session.get(url, params=params, timeout=60)
        if not r.ok:
            endpoint_label = self.describe_endpoint(endpoint)
            self.log(f"Trading212 request failed for {endpoint_label}: status {r.status_code}")
            if r.text:
                self.log(r.text[:300])
            return None
        return r.json() if r.text.strip() else None

    def paginated(self, endpoint, dataset=None, current_year_only=False):
        rows, next_path, page = [], endpoint, 0
        params = {"limit": API_LIMIT}
        current_year = datetime.now().year
        while next_path:
            page += 1
            self.log(f"Fetching {dataset or endpoint}, page {page}...")
            data = self.get(next_path, params=params if page == 1 else None)
            if data is None:
                break
            if isinstance(data, list):
                items, next_page = data, None
            elif isinstance(data, dict):
                items, next_page = data.get("items", []), data.get("nextPagePath")
            else:
                items, next_page = [], None

            if current_year_only and dataset:
                tmp = ensure_date(flatten(items), DATE_COLS.get(dataset, []))
                if "year" in tmp.columns and tmp["year"].notna().any():
                    mask = tmp["year"] == current_year
                    rows.extend([items[i] for i in range(len(items)) if bool(mask.iloc[i])])
                    yrs = pd.to_numeric(tmp["year"], errors="coerce").dropna()
                    if not yrs.empty and yrs.max() < current_year:
                        self.log("Reached older data. Stopping current-year refresh.")
                        break
                else:
                    rows.extend(items)
            else:
                rows.extend(items)

            self.log(f"Rows in page: {len(items)} | total kept: {len(rows)}")
            if not next_page:
                break
            next_path = next_page
            time.sleep(SLEEP_SECONDS)
        return rows

    def positions(self):
        candidates = []
        for ep in ["/equity/portfolio", "/equity/positions"]:
            self.log(f"Fetching positions from {ep}...")
            df = flatten(self.get(ep))
            if df.empty:
                continue
            df["source_endpoint"] = ep
            score = len(df) * 100 + len(df.columns)
            for c in ["ticker", "quantity", "averagePrice", "currentPrice", "ppl", "fxPpl"]:
                if c in df.columns:
                    score += 1000
            candidates.append((score, ep, df))
        if not candidates:
            return pd.DataFrame()
        candidates.sort(reverse=True, key=lambda x: x[0])
        self.log(f"Selected positions endpoint: {candidates[0][1]}")
        return candidates[0][2]

def cache_write(name, df, p):
    if df.empty or "year" not in df.columns:
        return
    valid = df[df["year"].notna()].copy()
    if valid.empty:
        return
    valid["year"] = valid["year"].astype(int)
    for y, ydf in valid.groupby("year"):
        save_df(ydf, p["cache"]/name/f"{name}_{y}.csv")


def latest_raw_dataset(name, p):
    files = sorted(p["raw"].glob(f"raw_{name}_trading212_*.csv"))
    if not files:
        return pd.DataFrame()
    latest = files[-1]
    try:
        df = pd.read_csv(latest)
        df = add_source(ensure_date(df, DATE_COLS.get(name, [])))
        return df
    except Exception:
        return pd.DataFrame()

def cache_load(name, p):
    folder = p["cache"]/name
    if not folder.exists():
        return latest_raw_dataset(name, p)
    dfs = []
    for f in sorted(folder.glob(f"{name}_*.csv")):
        try:
            dfs.append(pd.read_csv(f))
        except Exception:
            pass
    if dfs:
        return add_source(ensure_date(pd.concat(dfs, ignore_index=True), DATE_COLS.get(name, [])))
    return latest_raw_dataset(name, p)

def fetch_history(client, name, p, full_refresh=False):
    cached = cache_load(name, p)
    rows = client.paginated(HISTORY_ENDPOINTS[name], dataset=name, current_year_only=(not full_refresh and not cached.empty))
    df = add_source(ensure_date(flatten(rows), DATE_COLS.get(name, [])))
    if not df.empty:
        cache_write(name, df, p)
    suffix = "full" if full_refresh or cached.empty else f"current_year_{datetime.now().year}"
    save_df(df, p["raw"]/f"raw_{name}_trading212_{suffix}_{datetime.now():%Y%m%d_%H%M%S}.csv")
    return add_source(cache_load(name, p))

def latest_positions_snapshot(p):
    files = list(p["raw"].glob("raw_positions_trading212_current_*.csv")) + list(p["raw"].glob("positions_current_*.csv"))
    scored = []
    for f in files:
        try:
            df = pd.read_csv(f)
            score = len(df) * 100 + len(df.columns) + sum(1000 for c in ["ticker","quantity","currentPrice","ppl","fxPpl"] if c in df.columns)
            scored.append((score, df))
        except Exception:
            pass
    return add_source(sorted(scored, reverse=True, key=lambda x:x[0])[0][1]) if scored else pd.DataFrame()

def account_reference(client, use_api, p, log=default_log):
    """Load Trading212 account reference values. Prefer /equity/account/summary,
    but keep legacy info/cash fallbacks because Trading212 API fields may change."""
    path = p["raw"] / "raw_account_reference_trading212_latest.csv"
    if use_api:
        payload = None
        endpoint = None
        for ep in ["/equity/account/summary", "/equity/account/cash", "/equity/account/info"]:
            data = client.get(ep)
            if isinstance(data, dict) and data:
                payload = data
                endpoint = ep
                break
        if payload:
            flat = pd.json_normalize(payload)
            flat["source"] = "trading212"
            flat["broker"] = "trading212"
            flat["source_endpoint"] = endpoint
            flat["fetched_at"] = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            save_df(flat, path)
            return flat
    return read_csv(path)

def account_currency_from_reference(df):
    if df is None or df.empty:
        return ACCOUNT_CURRENCY_FALLBACK
    for c in ["currencyCode", "currency", "account.currencyCode", "account.currency"]:
        if c in df.columns:
            v = df[c].dropna().astype(str)
            if not v.empty and v.iloc[-1].strip():
                return v.iloc[-1].strip().upper()
    return ACCOUNT_CURRENCY_FALLBACK

def metadata(client, use_api, p):
    path = p["raw"]/"raw_metadata_instruments_trading212_latest.csv"
    if use_api:
        df = add_source(flatten(client.get("/equity/metadata/instruments")))
        if not df.empty:
            save_df(df, path)
            return df
    return read_csv(path)

def infer_currency(ticker):
    if pd.isna(ticker):
        return "UNKNOWN", "missing"
    t, tu = str(ticker), str(ticker).upper()
    if t in MANUAL_CURRENCY:
        return MANUAL_CURRENCY[t], "manual"
    if tu.endswith("_US_EQ"):
        return "USD", "us_suffix"
    if re.search(r"l_EQ$", t):
        return "GBX", "london_gbx"
    if re.search(r"(d_EQ|a_EQ|p_EQ|m_EQ)$", t) or tu.endswith(("_DE_EQ","_PA_EQ","_MI_EQ","_AS_EQ")):
        return "EUR", "europe_suffix"
    return "UNKNOWN", "unknown"

def normalize_amount(amount, currency):
    amount = pd.to_numeric(amount, errors="coerce")
    cur = currency.astype(str).str.upper().replace({"NAN": np.nan, "NONE": np.nan, "": np.nan})
    gbx = cur.eq("GBX")
    amount = pd.Series(np.where(gbx, amount/100.0, amount), index=amount.index)
    cur = pd.Series(np.where(gbx, "GBP", cur), index=cur.index)
    return amount, cur

def fx_rates(currencies, target, allow_online, p, log):
    target = str(target).upper()
    currencies = sorted(set(str(c).upper() for c in currencies if pd.notna(c) and str(c).upper() not in ["", "NAN", "NONE", "UNKNOWN"]))
    cached = read_csv(p["raw"]/"raw_fx_rates_latest.csv")
    if not cached.empty:
        cached["source_currency"] = cached["source_currency"].astype(str).str.upper()
        cached["target_currency"] = cached["target_currency"].astype(str).str.upper()
    ecb_rates = None
    rows = []
    for src in currencies:
        if src == target:
            rows.append({"source_currency":src,"target_currency":target,"rate_to_target":1.0,"rate_date":datetime.now().date().isoformat(),"source":"identity","broker":"external_fx","fetched_at":datetime.now().strftime("%Y-%m-%d %H:%M:%S")})
            continue
        match = cached[(cached["source_currency"]==src)&(cached["target_currency"]==target)] if not cached.empty else pd.DataFrame()
        if allow_online:
            try:
                log(f"Fetching FX {src}->{target}...")
                r = requests.get("https://api.frankfurter.app/latest", params={"from":src,"to":target}, timeout=20)
                if r.ok and r.text.strip():
                    payload = r.json()
                    rate = payload.get("rates", {}).get(target)
                    if rate is not None:
                        rows.append({"source_currency":src,"target_currency":target,"rate_to_target":float(rate),"rate_date":payload.get("date"),"source":"frankfurter_latest","broker":"external_fx","fetched_at":datetime.now().strftime("%Y-%m-%d %H:%M:%S")})
                        continue
            except Exception as e:
                log(f"FX error {src}->{target}: {e}")
            try:
                log(f"Fetching FX {src}->{target} from open exchange-rate fallback...")
                r = requests.get(f"https://open.er-api.com/v6/latest/{src}", timeout=20)
                if r.ok and r.text.strip():
                    payload = r.json()
                    rate = payload.get("rates", {}).get(target)
                    if payload.get("result") == "success" and rate is not None:
                        rows.append({"source_currency":src,"target_currency":target,"rate_to_target":float(rate),"rate_date":payload.get("time_last_update_utc"),"source":"open_er_api_latest","broker":"external_fx","fetched_at":datetime.now().strftime("%Y-%m-%d %H:%M:%S")})
                        continue
            except Exception as e:
                log(f"FX fallback error {src}->{target}: {e}")
            try:
                if ecb_rates is None:
                    ecb_rates = fetch_ecb_fx_rates(currencies, target, log)
                if src in ecb_rates:
                    rows.append(ecb_rates[src])
                    continue
            except Exception as e:
                log(f"ECB FX fallback error {src}->{target}: {e}")
        if not match.empty:
            rows.append(match.tail(1).iloc[0].to_dict())
        elif src != target:
            approx = approximate_fx_rate(src, target)
            if approx is not None:
                log(f"FX missing for {src}->{target}; using approximate fallback rate {approx:.4f}. Refresh FX later for a live rate.")
                rows.append({"source_currency":src,"target_currency":target,"rate_to_target":approx,"rate_date":datetime.now().date().isoformat(),"source":"approx_static_fallback","broker":"external_fx","fetched_at":datetime.now().strftime("%Y-%m-%d %H:%M:%S")})
            else:
                log(f"FX missing for {src}->{target}; values in that currency cannot be converted until an online rate or cached rate is available.")
    df = pd.DataFrame(rows).drop_duplicates(["source_currency","target_currency"], keep="last") if rows else pd.DataFrame()
    if not df.empty:
        combined = pd.concat([cached, df], ignore_index=True) if not cached.empty else df
        combined = combined.drop_duplicates(["source_currency","target_currency"], keep="last")
        save_df(combined, p["raw"]/"raw_fx_rates_latest.csv")
    return df

def approximate_fx_rate(src, target):
    src = str(src).upper()
    target = str(target).upper()
    if src == target:
        return 1.0
    if target == "EUR" and src in APPROX_FX_TO_EUR_FALLBACK:
        return APPROX_FX_TO_EUR_FALLBACK[src]
    if src == "EUR" and target in APPROX_FX_TO_EUR_FALLBACK:
        rate = APPROX_FX_TO_EUR_FALLBACK[target]
        return 1.0 / rate if rate else None
    if src in APPROX_FX_TO_EUR_FALLBACK and target in APPROX_FX_TO_EUR_FALLBACK:
        return APPROX_FX_TO_EUR_FALLBACK[src] / APPROX_FX_TO_EUR_FALLBACK[target]
    return None

def fetch_ecb_fx_rates(currencies, target, log):
    """Return latest ECB reference conversions from each source currency to target."""
    target = str(target).upper()
    needed = sorted(set(str(c).upper() for c in currencies if pd.notna(c) and str(c).upper() not in ["", "NAN", "NONE", "UNKNOWN"]))
    log("Fetching FX from ECB reference-rate fallback...")
    r = requests.get("https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml", timeout=20)
    r.raise_for_status()
    root = ET.fromstring(r.text)
    cube = {}
    rate_date = datetime.now().date().isoformat()
    for elem in root.iter():
        attrs = elem.attrib
        if "time" in attrs:
            rate_date = attrs["time"]
        if "currency" in attrs and "rate" in attrs:
            cube[str(attrs["currency"]).upper()] = float(attrs["rate"])
    cube["EUR"] = 1.0
    if target not in cube:
        return {}
    rows = {}
    for src in needed:
        if src not in cube:
            continue
        rate = cube[target] / cube[src]
        rows[src] = {
            "source_currency": src,
            "target_currency": target,
            "rate_to_target": float(rate),
            "rate_date": rate_date,
            "source": "ecb_reference_latest",
            "broker": "external_fx",
            "fetched_at": datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
        }
    return rows

def mapping_load(p):
    cols = ["symbol_raw","true_ticker","display_name","asset_type","sector","industry","country","is_etf","is_leveraged","risk_bucket","custom_portfolio","source","broker","notes"]
    if p["mapping_file"].exists():
        df = pd.read_csv(p["mapping_file"])
        for c in cols:
            if c not in df.columns:
                df[c] = np.nan
        return normalize_mapping_defaults(df[cols])
    df = pd.DataFrame(columns=cols)
    save_df(df, p["mapping_file"])
    return df

def mapping_apply(df, mapping):
    if df.empty or "ticker" not in df.columns:
        return df
    out = df.copy()
    out["symbol_raw"] = out["ticker"]
    if mapping is not None and not mapping.empty and "symbol_raw" in mapping.columns:
        m = mapping[[c for c in mapping.columns if c not in ["source","broker"]]].drop_duplicates("symbol_raw")
        out = out.merge(m, on="symbol_raw", how="left")
    default_label = out["ticker"].apply(clean_symbol_label)
    out["true_ticker"] = out.get("true_ticker", pd.Series(index=out.index)).fillna(default_label)
    out["display_name"] = out.get("display_name", pd.Series(index=out.index)).fillna(out.get("ticker_display", default_label))
    for col in ["true_ticker", "display_name"]:
        cur = out[col].astype(str)
        raw = out["ticker"].astype(str)
        looks_default = (cur.str.strip() == "") | (cur.str.lower() == "nan") | (cur == raw) | cur.isin(OLD_DEFAULT_ALIASES) | cur.str.endswith("_US_EQ", na=False) | cur.str.endswith("_EQ", na=False)
        out.loc[looks_default, col] = default_label[looks_default]
    for c in ["asset_type","sector","industry","country","is_etf","is_leveraged","risk_bucket","custom_portfolio"]:
        if c not in out.columns:
            out[c] = "Unknown"
        out[c] = out[c].fillna("Unknown")
    return out

def clean_positions(raw, meta, acc_cur, allow_online_fx, fx_mode, p, log):
    df = add_source(raw.copy())
    if df.empty:
        return df, pd.DataFrame()
    tc = first_col(df, ["ticker","instrumentCode","symbol"])
    if tc and tc != "ticker":
        df["ticker"] = df[tc]
    for c in ["quantity","averagePrice","currentPrice","ppl","fxPpl"]:
        if c in df.columns:
            df[c] = pd.to_numeric(df[c], errors="coerce")
    if meta is not None and not meta.empty:
        m = meta.copy()
        mtc = first_col(m, ["ticker","instrumentCode","symbol"])
        if mtc:
            if mtc != "ticker":
                m = m.rename(columns={mtc:"ticker"})
            keep = [c for c in ["ticker","currencyCode","name","shortName","type","isin"] if c in m.columns]
            df = df.merge(m[keep].drop_duplicates("ticker"), on="ticker", how="left")
    inf = df["ticker"].apply(infer_currency)
    df["inferred_currency"] = inf.apply(lambda x:x[0]); df["currency_source"] = inf.apply(lambda x:x[1])
    if "currencyCode" in df.columns:
        df["price_currency_raw"] = df["currencyCode"].astype(str).str.upper().replace({"NAN":np.nan,"NONE":np.nan,"":np.nan})
        df["price_currency_raw"] = df["price_currency_raw"].fillna(df["inferred_currency"])
    else:
        df["price_currency_raw"] = df["inferred_currency"]
    q = num(df,"quantity"); cp = num(df,"currentPrice", np.nan); ap = num(df,"averagePrice", np.nan)
    df["market_value_native"], df["value_currency"] = normalize_amount(q*cp, df["price_currency_raw"])
    df["cost_basis_native"], _ = normalize_amount(q*ap, df["price_currency_raw"])
    if fx_mode == "off":
        fx = pd.DataFrame()
        df["fx_rate_to_account_currency"] = np.where(df["value_currency"]==acc_cur, 1.0, np.nan)
        df["fx_rate_source"] = "fx_off"
    else:
        fx = fx_rates(df["value_currency"].dropna().unique().tolist(), acc_cur, allow_online_fx, p, log)
        mp = fx.set_index("source_currency")["rate_to_target"].to_dict() if not fx.empty else {}
        src = fx.set_index("source_currency")["source"].to_dict() if not fx.empty else {}
        df["fx_rate_to_account_currency"] = df["value_currency"].astype(str).str.upper().map(mp)
        df["fx_rate_source"] = df["value_currency"].astype(str).str.upper().map(src)
    df["market_value_eur"] = df["market_value_native"] * df["fx_rate_to_account_currency"]
    df["cost_basis_eur"] = df["cost_basis_native"] * df["fx_rate_to_account_currency"]
    df["unrealized_pnl_eur"] = num(df,"ppl")
    df["fx_pnl_eur"] = num(df,"fxPpl")
    df["total_unrealized_pnl_eur"] = df["unrealized_pnl_eur"] + df["fx_pnl_eur"]
    total = df["market_value_eur"].sum(skipna=True)
    df["allocation_pct"] = df["market_value_eur"] / total if total else np.nan
    df["return_pct_on_cost_eur"] = safe_div(df["total_unrealized_pnl_eur"], df["cost_basis_eur"])
    df["conversion_check"] = np.where(df["market_value_eur"].notna(), "converted", "missing_fx_or_currency")
    df["ticker_display"] = df["ticker"].apply(clean_symbol_label)
    return df, fx

def clean_dividends(raw):
    df = ensure_date(add_source(raw.copy()), DATE_COLS["dividends"])
    if df.empty:
        return df
    tc = first_col(df, ["ticker","instrumentCode","symbol"])
    if tc and tc != "ticker":
        df["ticker"] = df[tc]
    rc = first_col(df, ["amountInEuro","amount","netAmount","paidAmount"])
    tax = first_col(df, ["tax","withholdingTax"])
    df["dividend_received_eur"] = num(df, rc, np.nan) if rc else np.nan
    df["dividend_net_eur"] = df["dividend_received_eur"]
    df["dividend_tax_eur"] = num(df, tax, np.nan) if tax else np.nan
    df["dividend_count"] = 1
    if "ticker" in df.columns:
        df["ticker_display"] = df["ticker"].apply(clean_symbol_label)
    return df

def clean_orders(raw):
    df = ensure_date(add_source(raw.copy()), DATE_COLS["orders"])
    if df.empty:
        return df

    tc = first_col(df, ["ticker", "instrumentCode", "symbol", "order.ticker", "order.instrument.ticker"])
    if tc and tc != "ticker":
        df["ticker"] = df[tc]

    qty = first_col(df, ["filledQuantity", "quantity", "filled", "fill.quantity", "order.filledQuantity", "order.quantity"])
    price = first_col(df, ["averagePrice", "price", "limitPrice", "fill.price", "order.limitPrice", "order.stopPrice"])
    val = first_col(df, ["value", "amount", "total", "fill.walletImpact.netValue", "order.filledValue", "order.value"])
    side = first_col(df, ["side", "type", "action", "order.side"])
    status = first_col(df, ["status", "order.status"])
    order_type = first_col(df, ["order.type", "type"])
    strategy = first_col(df, ["order.strategy", "strategy"])
    order_currency = first_col(df, ["currency", "order.currency", "fill.walletImpact.currency"])
    instrument_currency = first_col(df, ["order.instrument.currency", "instrument.currency", "currencyCode"])
    realised = first_col(df, ["fill.walletImpact.realisedProfitLoss", "realisedProfitLoss", "realizedProfitLoss"])

    if val:
        df["trade_value_reported"] = num(df, val, np.nan).abs()
    elif qty and price:
        df["trade_value_reported"] = (num(df, qty, np.nan) * num(df, price, np.nan)).abs()
    else:
        df["trade_value_reported"] = np.nan

    df["filled_quantity_reported"] = num(df, qty, np.nan) if qty else np.nan
    df["filled_quantity_abs"] = pd.to_numeric(df["filled_quantity_reported"], errors="coerce").abs()
    df["fill_price_reported"] = num(df, price, np.nan) if price else np.nan

    df["side_clean"] = df[side].astype(str).str.upper() if side else "UNKNOWN"
    df["order_status_clean"] = df[status].astype(str).str.upper() if status else "UNKNOWN"
    df["order_type_clean"] = df[order_type].astype(str).str.upper() if order_type else "UNKNOWN"
    df["strategy_clean"] = df[strategy].astype(str).str.upper() if strategy else "UNKNOWN"

    df["order_currency_reported"] = df[order_currency].astype(str).str.upper() if order_currency else ""
    df["instrument_currency_reported"] = df[instrument_currency].astype(str).str.upper() if instrument_currency else ""

    df["buy_value_reported"] = np.where(df["side_clean"].str.contains("BUY", na=False), df["trade_value_reported"], 0.0)
    df["sell_value_reported"] = np.where(df["side_clean"].str.contains("SELL", na=False), df["trade_value_reported"], 0.0)
    df["net_buy_value_reported"] = df["buy_value_reported"] - df["sell_value_reported"]

    if realised:
        realised_raw = pd.to_numeric(df[realised], errors="coerce")
        df["realized_pnl_available_count"] = realised_raw.notna().astype(int)
        df["realized_pnl_reported"] = realised_raw.fillna(0.0)
    else:
        df["realized_pnl_available_count"] = 0
        df["realized_pnl_reported"] = 0.0
    df["realised_pnl_reported"] = df["realized_pnl_reported"]
    df["sell_order_count"] = np.where(df["side_clean"].str.contains("SELL", na=False), 1, 0)
    df["sell_order_without_reported_realized_pnl_count"] = np.where((df["sell_order_count"] == 1) & (df["realized_pnl_available_count"] == 0), 1, 0)
    df["order_count"] = 1

    if "ticker" in df.columns:
        df["ticker_display"] = df["ticker"].apply(clean_symbol_label)

    return df

def clean_transactions(raw):
    df = ensure_date(add_source(raw.copy()), DATE_COLS["transactions"])
    if df.empty:
        return df
    amount = first_col(df, ["amount","value","total","result"])
    typ = first_col(df, ["type","eventType","transactionType","category"])
    df["transaction_amount_reported"] = num(df, amount, np.nan) if amount else np.nan
    df["transaction_type_clean"] = df[typ].astype(str).str.upper() if typ else "UNKNOWN"
    df["transaction_count"] = 1
    return df

def gsum(df, groups, sums):
    if df.empty:
        return pd.DataFrame()
    groups = [c for c in groups if c in df.columns]
    sums = [c for c in sums if c in df.columns]
    if not groups or not sums:
        return pd.DataFrame()
    return df.groupby(groups, dropna=False)[sums].sum(min_count=1).reset_index()


MANUAL_TRANSACTION_COLUMNS = [
    "enabled", "date", "broker", "event_type", "symbol", "quantity", "price", "gross_amount", "net_amount",
    "currency", "fx_rate_to_account_currency", "tax_amount", "fee_amount", "realized_pnl", "notes", "source_row_id"
]

MANUAL_POSITION_COLUMNS = [
    "enabled", "broker", "symbol", "quantity", "average_price", "current_price", "market_value_eur",
    "cost_basis_eur", "unrealized_pnl_eur", "fx_pnl_eur", "currency", "fx_rate_to_account_currency", "notes", "source_row_id"
]

def empty_manual_transactions():
    return pd.DataFrame(columns=MANUAL_TRANSACTION_COLUMNS)

def empty_manual_positions():
    return pd.DataFrame(columns=MANUAL_POSITION_COLUMNS)

def _ensure_manual_file(path, columns):
    path.parent.mkdir(parents=True, exist_ok=True)
    if not path.exists():
        pd.DataFrame(columns=columns).to_csv(path, index=False, encoding="utf-8-sig")
    df = pd.read_csv(path) if path.exists() else pd.DataFrame(columns=columns)
    for c in columns:
        if c not in df.columns:
            df[c] = ""
    return df[columns]

def load_manual_source_tables(p):
    tx = _ensure_manual_file(p["manual_transactions_file"], MANUAL_TRANSACTION_COLUMNS)
    pos = _ensure_manual_file(p["manual_positions_file"], MANUAL_POSITION_COLUMNS)
    return {"manual_transactions": tx, "manual_positions": pos}

def save_manual_source_tables(p, manual_transactions=None, manual_positions=None):
    p["manual"].mkdir(parents=True, exist_ok=True)
    if manual_transactions is not None:
        df = pd.DataFrame(manual_transactions)
        for c in MANUAL_TRANSACTION_COLUMNS:
            if c not in df.columns: df[c] = ""
        df = df[MANUAL_TRANSACTION_COLUMNS]
        df.to_csv(p["manual_transactions_file"], index=False, encoding="utf-8-sig")
    if manual_positions is not None:
        df = pd.DataFrame(manual_positions)
        for c in MANUAL_POSITION_COLUMNS:
            if c not in df.columns: df[c] = ""
        df = df[MANUAL_POSITION_COLUMNS]
        df.to_csv(p["manual_positions_file"], index=False, encoding="utf-8-sig")
    return load_manual_source_tables(p)

def backup_file(path, label="backup"):
    try:
        path = Path(path)
        if not path.exists():
            return None
        backup_dir = path.parent / "backups"
        backup_dir.mkdir(parents=True, exist_ok=True)
        backup = backup_dir / f"{path.stem}_{datetime.now():%Y%m%d_%H%M%S}_{label}{path.suffix}"
        import shutil
        shutil.copy2(path, backup)
        return backup
    except Exception:
        return None

def _manual_enabled(df):
    if df is None or df.empty:
        return df
    out = df.copy()
    if "enabled" not in out.columns:
        out["enabled"] = True
    en = out["enabled"].astype(str).str.strip().str.lower()
    return out[~en.isin(["false", "0", "no", "n", "disabled", "delete", "deleted"])].copy()

def _fill_account_currency_fx_rate(df, account_currency="EUR"):
    rate = pd.to_numeric(df.get("fx_rate_to_account_currency", np.nan), errors="coerce")
    currency = df.get("currency", account_currency).fillna(account_currency).replace("", account_currency).astype(str).str.upper()
    account = str(account_currency).upper()
    return rate.mask(rate.isna() & currency.eq(account), 1.0)

def _manual_base(df, date_col="date"):
    out = df.copy()
    out["source"] = "manual"
    out["broker"] = out.get("broker", "manual").fillna("manual").replace("", "manual")
    out["ticker"] = out.get("symbol", "").astype(str).str.strip()
    out["ticker_display"] = out["ticker"].apply(clean_symbol_label)
    out["symbol_raw"] = out["ticker"]
    if date_col in out.columns:
        out["event_date"] = pd.to_datetime(out[date_col], errors="coerce", utc=True)
        out["year"] = out["event_date"].dt.year
        out["quarter"] = out["event_date"].dt.quarter
        out["month"] = out["event_date"].dt.month
        out["year_month"] = out["event_date"].dt.strftime("%Y-%m")
        out["year_quarter"] = out["year"].astype("Int64").astype(str) + "Q" + out["quarter"].astype("Int64").astype(str)
    return out

def clean_manual_inputs(manual_sources, account_currency="EUR"):
    tx_src = _manual_enabled(manual_sources.get("manual_transactions", pd.DataFrame()))
    pos_src = _manual_enabled(manual_sources.get("manual_positions", pd.DataFrame()))
    manual_orders, manual_dividends, manual_transactions, manual_positions = pd.DataFrame(), pd.DataFrame(), pd.DataFrame(), pd.DataFrame()

    if tx_src is not None and not tx_src.empty:
        tx = _manual_base(tx_src)
        tx["event_type_clean"] = tx.get("event_type", "").astype(str).str.strip().str.upper()
        for c in ["quantity", "price", "gross_amount", "net_amount", "fx_rate_to_account_currency", "tax_amount", "fee_amount", "realized_pnl"]:
            tx[c] = pd.to_numeric(tx.get(c, np.nan), errors="coerce")
        tx["currency"] = tx.get("currency", account_currency).fillna(account_currency).replace("", account_currency).astype(str).str.upper()
        tx["fx_rate_to_account_currency"] = _fill_account_currency_fx_rate(tx, account_currency)
        tx["manual_amount_native"] = tx["net_amount"].fillna(tx["gross_amount"]).fillna(tx["quantity"] * tx["price"])
        tx["manual_amount_eur"] = tx["manual_amount_native"] * tx["fx_rate_to_account_currency"].fillna(1.0)
        tx["manual_fx_conversion_note"] = np.where(
            tx["currency"].eq(str(account_currency).upper()),
            "account_currency",
            np.where(tx["fx_rate_to_account_currency"].notna(), "converted_with_manual_rate", "missing_fx_rate_assumed_account_currency")
        )

        div_mask = tx["event_type_clean"].isin(["DIVIDEND", "DIVIDENDS"])
        if div_mask.any():
            d = tx[div_mask].copy()
            d["dividend_received_eur"] = d["manual_amount_eur"].fillna(0.0)
            d["dividend_net_eur"] = d["dividend_received_eur"]
            d["dividend_tax_eur"] = d["tax_amount"].fillna(0.0) * d["fx_rate_to_account_currency"].fillna(1.0)
            d["dividend_count"] = 1
            manual_dividends = d

        order_mask = tx["event_type_clean"].isin(["BUY", "SELL"])
        if order_mask.any():
            o = tx[order_mask].copy()
            o["side_clean"] = o["event_type_clean"]
            o["order_status_clean"] = "MANUAL"
            o["order_type_clean"] = "MANUAL"
            o["strategy_clean"] = "MANUAL"
            o["trade_value_reported"] = o["manual_amount_eur"].abs()
            o["filled_quantity_reported"] = o["quantity"]
            o["filled_quantity_abs"] = o["quantity"].abs()
            o["fill_price_reported"] = o["price"] * o["fx_rate_to_account_currency"].fillna(1.0)
            o["order_currency_reported"] = o["currency"]
            o["instrument_currency_reported"] = o["currency"]
            o["buy_value_reported"] = np.where(o["side_clean"].eq("BUY"), o["trade_value_reported"], 0.0)
            o["sell_value_reported"] = np.where(o["side_clean"].eq("SELL"), o["trade_value_reported"], 0.0)
            o["net_buy_value_reported"] = o["buy_value_reported"] - o["sell_value_reported"]
            o["realized_pnl_available_count"] = o["realized_pnl"].notna().astype(int)
            o["realized_pnl_reported"] = o["realized_pnl"].fillna(0.0) * o["fx_rate_to_account_currency"].fillna(1.0)
            o["realised_pnl_reported"] = o["realized_pnl_reported"]
            o["sell_order_count"] = np.where(o["side_clean"].eq("SELL"), 1, 0)
            o["sell_order_without_reported_realized_pnl_count"] = np.where((o["sell_order_count"] == 1) & (o["realized_pnl_available_count"] == 0), 1, 0)
            o["order_count"] = 1
            manual_orders = o

        transaction_mask = ~div_mask & ~order_mask
        if transaction_mask.any():
            t = tx[transaction_mask].copy()
            t["transaction_type_clean"] = t["event_type_clean"].replace("", "MANUAL")
            sign = np.where(t["event_type_clean"].isin(["WITHDRAWAL", "FEE", "TAX"]), -1.0, 1.0)
            t["transaction_amount_reported"] = t["manual_amount_eur"].abs() * sign
            t["transaction_count"] = 1
            manual_transactions = t

    if pos_src is not None and not pos_src.empty:
        p0 = pos_src.copy()
        p0["source"] = "manual"
        p0["broker"] = p0.get("broker", "manual").fillna("manual").replace("", "manual")
        p0["ticker"] = p0.get("symbol", "").astype(str).str.strip()
        p0["symbol_raw"] = p0["ticker"]
        p0["ticker_display"] = p0["ticker"].apply(clean_symbol_label)
        for c in ["quantity", "average_price", "current_price", "market_value_eur", "cost_basis_eur", "unrealized_pnl_eur", "fx_pnl_eur", "fx_rate_to_account_currency"]:
            p0[c] = pd.to_numeric(p0.get(c, np.nan), errors="coerce")
        p0["currency"] = p0.get("currency", account_currency).fillna(account_currency).replace("", account_currency).astype(str).str.upper()
        p0["fx_rate_to_account_currency"] = _fill_account_currency_fx_rate(p0, account_currency)
        rate = p0["fx_rate_to_account_currency"].fillna(1.0)
        p0["currentPrice"] = p0["current_price"]
        p0["averagePrice"] = p0["average_price"]
        p0["market_value_eur"] = p0["market_value_eur"].fillna(p0["quantity"] * p0["current_price"])
        p0["cost_basis_eur"] = p0["cost_basis_eur"].fillna(p0["quantity"] * p0["average_price"])
        convert_mask = ~p0["currency"].eq(str(account_currency).upper()) & p0["fx_rate_to_account_currency"].notna()
        for c in ["market_value_eur", "cost_basis_eur", "unrealized_pnl_eur", "fx_pnl_eur"]:
            p0.loc[convert_mask, c] = p0.loc[convert_mask, c] * rate.loc[convert_mask]
        p0["unrealized_pnl_eur"] = p0["unrealized_pnl_eur"].fillna(p0["market_value_eur"] - p0["cost_basis_eur"])
        p0["fx_pnl_eur"] = p0["fx_pnl_eur"].fillna(0.0)
        p0["total_unrealized_pnl_eur"] = p0["unrealized_pnl_eur"] + p0["fx_pnl_eur"]
        p0["market_value_native"] = p0["market_value_eur"]
        p0["cost_basis_native"] = p0["cost_basis_eur"]
        p0["value_currency"] = account_currency
        p0["price_currency_raw"] = p0["currency"]
        p0["fx_rate_to_account_currency"] = p0["fx_rate_to_account_currency"].fillna(1.0)
        p0["fx_rate_source"] = np.where(convert_mask, "manual_row_rate", "manual_assumed_account_currency")
        p0["conversion_check"] = "manual_input"
        total = p0["market_value_eur"].sum(skipna=True)
        p0["allocation_pct"] = p0["market_value_eur"] / total if total else np.nan
        p0["return_pct_on_cost_eur"] = safe_div(p0["total_unrealized_pnl_eur"], p0["cost_basis_eur"])
        manual_positions = p0

    return {
        "manual_orders": manual_orders,
        "manual_dividends": manual_dividends,
        "manual_transactions_clean": manual_transactions,
        "manual_positions_clean": manual_positions,
    }

def validate_manual_sources(manual_sources):
    rows = []
    def _row_label(frame):
        labels = []
        for idx, rr in frame.head(5).iterrows():
            rowid = rr.get("source_row_id") if hasattr(rr, "get") else None
            labels.append(str(rowid if pd.notna(rowid) and str(rowid).strip() else idx + 1))
        suffix = "" if len(frame) <= 5 else f" +{len(frame) - 5} more"
        return ", ".join(labels) + suffix

    tx = manual_sources.get("manual_transactions", pd.DataFrame())
    if tx is not None and not tx.empty:
        en = _manual_enabled(tx)
        if "source_row_id" in en.columns:
            ids = en["source_row_id"].astype(str).str.strip()
            dup_ids = ids[(ids != "") & (~ids.str.lower().isin(["nan", "none"])) & ids.duplicated(keep=False)]
            for sid in sorted(dup_ids.unique())[:50]:
                rows.append({"table":"Manual transactions","row":sid,"severity":"warning","message":"Possible duplicate source_row_id. If this was an append upload, check that the same broker file was not imported twice."})
        dup_cols = [c for c in ["broker", "date", "event_type", "symbol", "quantity", "price", "gross_amount", "net_amount", "currency"] if c in en.columns]
        if dup_cols:
            dup = en[en.duplicated(subset=dup_cols, keep=False)]
            for _, grp in dup.groupby(dup_cols, dropna=False, sort=False):
                rowid = _row_label(grp)
                rows.append({"table":"Manual transactions","row":rowid,"severity":"warning","message":f"{len(grp)} enabled rows share the same broker/date/type/symbol/amount fingerprint. The app keeps all enabled rows in calculations; disable or delete extras if these are accidental duplicates."})
                if len([r for r in rows if r.get("table") == "Manual transactions" and "fingerprint" in r.get("message", "")]) >= 50:
                    break
        for idx, r in en.iterrows():
            rowid = r.get("source_row_id") or idx + 1
            et = str(r.get("event_type", "")).strip()
            sym = str(r.get("symbol", "")).strip()
            dt = pd.to_datetime(r.get("date", ""), errors="coerce")
            if not et:
                rows.append({"table":"Manual transactions","row":rowid,"severity":"error","message":"Missing event_type."})
            if et.upper() in ["BUY", "SELL", "DIVIDEND", "DIVIDENDS"] and not sym:
                rows.append({"table":"Manual transactions","row":rowid,"severity":"error","message":"Missing symbol for buy/sell/dividend row."})
            if pd.isna(dt):
                rows.append({"table":"Manual transactions","row":rowid,"severity":"error","message":"Missing or invalid date."})
            amount = pd.to_numeric(pd.Series([r.get("net_amount", np.nan), r.get("gross_amount", np.nan)]), errors="coerce")
            quantity = pd.to_numeric(r.get("quantity", np.nan), errors="coerce")
            price = pd.to_numeric(r.get("price", np.nan), errors="coerce")
            cur = str(r.get("currency", "")).strip().upper()
            fx = pd.to_numeric(r.get("fx_rate_to_account_currency", np.nan), errors="coerce")
            if cur and cur not in ["NAN", "NONE", "EUR"] and pd.isna(fx):
                rows.append({"table":"Manual transactions","row":rowid,"severity":"warning","message":"Currency is not EUR/account currency but fx_rate_to_account_currency is missing. Amount will be treated as account-currency."})
            if amount.isna().all() and (pd.isna(quantity) or pd.isna(price)):
                rows.append({"table":"Manual transactions","row":rowid,"severity":"warning","message":"No amount and no quantity × price fallback."})
    pos = manual_sources.get("manual_positions", pd.DataFrame())
    if pos is not None and not pos.empty:
        en = _manual_enabled(pos)
        if "source_row_id" in en.columns:
            ids = en["source_row_id"].astype(str).str.strip()
            dup_ids = ids[(ids != "") & (~ids.str.lower().isin(["nan", "none"])) & ids.duplicated(keep=False)]
            for sid in sorted(dup_ids.unique())[:50]:
                rows.append({"table":"Manual positions","row":sid,"severity":"warning","message":"Possible duplicate source_row_id. Check that the same broker positions file was not imported twice."})
        dup_cols = [c for c in ["broker", "symbol"] if c in en.columns]
        if dup_cols:
            dup = en[en.duplicated(subset=dup_cols, keep=False)]
            for _, grp in dup.groupby(dup_cols, dropna=False, sort=False):
                rowid = _row_label(grp)
                rows.append({"table":"Manual positions","row":rowid,"severity":"warning","message":f"{len(grp)} enabled current-position rows have the same broker/symbol. The app sums enabled position rows; disable/delete extras if they are duplicate imports, or consolidate partial lots if preferred."})
                if len([r for r in rows if r.get("table") == "Manual positions" and "broker/symbol" in r.get("message", "")]) >= 50:
                    break
        for idx, r in en.iterrows():
            rowid = r.get("source_row_id") or idx + 1
            if not str(r.get("symbol", "")).strip():
                rows.append({"table":"Manual positions","row":rowid,"severity":"error","message":"Missing symbol."})
            quantity = pd.to_numeric(r.get("quantity", np.nan), errors="coerce")
            mv = pd.to_numeric(r.get("market_value_eur", np.nan), errors="coerce")
            cp = pd.to_numeric(r.get("current_price", np.nan), errors="coerce")
            cur = str(r.get("currency", "")).strip().upper()
            fx = pd.to_numeric(r.get("fx_rate_to_account_currency", np.nan), errors="coerce")
            if cur and cur not in ["NAN", "NONE", "EUR"] and pd.isna(fx):
                rows.append({"table":"Manual positions","row":rowid,"severity":"warning","message":"Currency is not EUR/account currency but fx_rate_to_account_currency is missing. Values will be treated as account-currency."})
            if pd.isna(mv) and (pd.isna(quantity) or pd.isna(cp)):
                rows.append({"table":"Manual positions","row":rowid,"severity":"warning","message":"Provide market_value_eur or quantity + current_price."})
    if not rows:
        rows.append({"table":"Manual inputs","row":"-","severity":"ok","message":"No manual input issues found."})
    return pd.DataFrame(rows)


def load_sp500_benchmark(p, allow_online=True, max_age_hours=24, log=default_log):
    """Load/cache a basic S&P 500 daily close benchmark.

    v2.21 uses multiple no-key public sources because one source can fail on
    some networks. Primary source is FRED (SP500 CSV). Stooq and Yahoo chart
    JSON are fallbacks. The cache is local and the dashboard should not break
    if online refresh fails.
    """
    cache_path = p["sp500_benchmark_file"]
    meta_path = p["sp500_benchmark_meta_file"]
    cache_path.parent.mkdir(parents=True, exist_ok=True)

    def _read_cache():
        try:
            if cache_path.exists():
                df = pd.read_csv(cache_path)
                if not df.empty:
                    return _normalize_sp500_df(df, source="cached")
        except Exception as e:
            log(f"Could not read S&P 500 cache: {e}")
        return pd.DataFrame()

    def _write_cache(df, source_name):
        df = _normalize_sp500_df(df, source=source_name)
        if df.empty:
            return pd.DataFrame()
        save_df(df, cache_path)
        meta = {
            "benchmark": "S&P 500 price index",
            "source": source_name,
            "downloaded_at": datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
            "rows": int(len(df)),
            "min_date": str(df["date"].min()),
            "max_date": str(df["date"].max()),
            "note": "Approximate benchmark only. Excludes dividends, EUR/USD FX conversion, taxes and fees."
        }
        try:
            meta_path.write_text(json.dumps(meta, indent=2), encoding="utf-8")
        except Exception:
            pass
        return df

    needs_refresh = True
    if cache_path.exists():
        age = datetime.now() - datetime.fromtimestamp(cache_path.stat().st_mtime)
        needs_refresh = age > timedelta(hours=max_age_hours)

    if allow_online and needs_refresh:
        # 1) FRED: clean no-key CSV. Columns: observation_date, SP500.
        fred_url = "https://fred.stlouisfed.org/graph/fredgraph.csv?id=SP500"
        try:
            log("Fetching S&P 500 benchmark from FRED...")
            r = requests.get(fred_url, timeout=30, headers={"User-Agent": "portfolio-dashboard-local-test/1.0"})
            if r.ok and r.text and "SP500" in r.text[:500]:
                from io import StringIO
                df = pd.read_csv(StringIO(r.text))
                out = _write_cache(df, "FRED SP500 CSV")
                if not out.empty:
                    return out
            log("FRED S&P 500 benchmark response was not usable. Trying fallback source...")
        except Exception as e:
            log(f"FRED S&P 500 benchmark fetch failed: {e}. Trying fallback source...")

        # 2) Stooq fallback: no-key CSV for ^SPX.
        stooq_url = "https://stooq.com/q/d/l/?s=%5Espx&i=d"
        try:
            log("Fetching S&P 500 benchmark from Stooq fallback...")
            r = requests.get(stooq_url, timeout=30, headers={"User-Agent": "portfolio-dashboard-local-test/1.0"})
            if r.ok and r.text and "Date" in r.text[:500]:
                from io import StringIO
                df = pd.read_csv(StringIO(r.text))
                out = _write_cache(df, "Stooq ^SPX CSV")
                if not out.empty:
                    return out
            log("Stooq S&P 500 benchmark response was not usable. Trying Yahoo chart fallback...")
        except Exception as e:
            log(f"Stooq S&P 500 benchmark fetch failed: {e}. Trying Yahoo chart fallback...")

        # 3) Yahoo chart fallback: no API key, but less official/stable.
        yahoo_url = "https://query1.finance.yahoo.com/v8/finance/chart/%5EGSPC?period1=0&period2=4102444800&interval=1d"
        try:
            log("Fetching S&P 500 benchmark from Yahoo chart fallback...")
            r = requests.get(yahoo_url, timeout=30, headers={"User-Agent": "portfolio-dashboard-local-test/1.0"})
            if r.ok:
                js = r.json()
                result = (((js or {}).get("chart") or {}).get("result") or [])
                if result:
                    res = result[0]
                    ts = res.get("timestamp") or []
                    q = (((res.get("indicators") or {}).get("quote") or [{}])[0])
                    closes = q.get("close") or []
                    df = pd.DataFrame({"date": pd.to_datetime(ts, unit="s", errors="coerce").date.astype(str), "sp500_close": closes})
                    out = _write_cache(df, "Yahoo ^GSPC chart fallback")
                    if not out.empty:
                        return out
            log("Yahoo S&P 500 benchmark response was not usable. Using cache if available.")
        except Exception as e:
            log(f"Yahoo S&P 500 benchmark fetch failed: {e}. Using cache if available.")

    return _read_cache()


def _normalize_sp500_df(df, source="benchmark"):
    if df is None or df.empty:
        return pd.DataFrame()
    out = df.copy()
    cols = {str(c).strip().lower(): c for c in out.columns}
    # Supported source shapes:
    # - FRED: observation_date, SP500
    # - Stooq: Date, Open, High, Low, Close, Volume
    # - Yahoo fallback/cache: date, sp500_close
    date_col = cols.get("date") or cols.get("observation_date")
    close_col = cols.get("sp500_close") or cols.get("sp500") or cols.get("close") or cols.get("adj close")
    if date_col is None or close_col is None:
        return pd.DataFrame()
    out = out.rename(columns={date_col: "date", close_col: "sp500_close"})
    out["date"] = pd.to_datetime(out["date"], errors="coerce")
    out["sp500_close"] = pd.to_numeric(out["sp500_close"].replace(".", np.nan), errors="coerce")
    out = out.dropna(subset=["date", "sp500_close"]).sort_values("date")
    # Keep only strictly positive prices.
    out = out[out["sp500_close"] > 0].copy()
    if out.empty:
        return pd.DataFrame()
    out["date"] = out["date"].dt.date.astype(str)
    out["year"] = pd.to_datetime(out["date"], errors="coerce").dt.year
    out["month"] = pd.to_datetime(out["date"], errors="coerce").dt.month
    out["year_month"] = pd.to_datetime(out["date"], errors="coerce").dt.strftime("%Y-%m")
    out["benchmark_symbol"] = "SP500"
    out["benchmark_name"] = "S&P 500 price index"
    out["source"] = source
    out["broker"] = "external_benchmark"
    keep = [c for c in ["date", "year", "month", "year_month", "sp500_close", "benchmark_symbol", "benchmark_name", "source", "broker"] if c in out.columns]
    return out[keep].drop_duplicates(subset=["date"], keep="last").sort_values("date")


def build_sp500_benchmark_views(monthly, kpis, sp500_daily):
    """Build approximate benchmark rows from available monthly cash flows.

    S&P virtual portfolio: each month, monthly net buy value is invested into
    the S&P 500 price index at month-end close. Negative monthly net buy values
    reduce the virtual benchmark position.

    Actual return line is intentionally conservative: it uses only observed
    cumulative dividends + reported realized PnL, and adds current open PnL only
    on the latest available month. This avoids pretending that the app has
    historical mark-to-market portfolio values for every month.
    """
    if monthly is None or monthly.empty or sp500_daily is None or sp500_daily.empty:
        status = pd.DataFrame([{
            "benchmark_status": "not_available",
            "benchmark_name": "S&P 500",
            "message": "Benchmark data or monthly activity data is missing. Benchmark plots are hidden.",
            "note": "The app tried FRED, Stooq and Yahoo fallbacks. Run once with internet and check that local firewall/proxy allows HTTPS requests."
        }])
        return pd.DataFrame(), pd.DataFrame(), status

    m = monthly.copy()
    if "year_month" not in m.columns:
        status = pd.DataFrame([{"benchmark_status": "not_available", "benchmark_name": "S&P 500", "message": "Monthly summary has no year_month column.", "note": "No benchmark view built."}])
        return pd.DataFrame(), pd.DataFrame(), status
    for c in ["net_buy_value_reported", "dividend_received_eur", "realized_pnl_reported"]:
        if c not in m.columns:
            m[c] = 0.0
        m[c] = pd.to_numeric(m[c], errors="coerce").fillna(0.0)
    cash = m.groupby("year_month", dropna=False)[["net_buy_value_reported", "dividend_received_eur", "realized_pnl_reported"]].sum(min_count=1).reset_index()
    cash["period_date"] = pd.to_datetime(cash["year_month"].astype(str) + "-01", errors="coerce")
    cash = cash.dropna(subset=["period_date"]).sort_values("period_date")
    if cash.empty:
        status = pd.DataFrame([{"benchmark_status": "not_available", "benchmark_name": "S&P 500", "message": "No dated cash-flow rows available.", "note": "No benchmark view built."}])
        return pd.DataFrame(), pd.DataFrame(), status

    b = sp500_daily.copy()
    b["date_dt"] = pd.to_datetime(b["date"], errors="coerce")
    b = b.dropna(subset=["date_dt", "sp500_close"]).sort_values("date_dt")
    if b.empty:
        status = pd.DataFrame([{"benchmark_status": "not_available", "benchmark_name": "S&P 500", "message": "S&P 500 cache is empty after cleaning.", "note": "No benchmark view built."}])
        return pd.DataFrame(), pd.DataFrame(), status
    bm = b.groupby("year_month", dropna=False).tail(1)[["year_month", "date", "sp500_close", "source"]].rename(columns={"date": "benchmark_price_date", "source": "benchmark_source"})
    cash = cash.merge(bm, on="year_month", how="left")
    cash["sp500_close"] = cash["sp500_close"].ffill().bfill()
    cash["benchmark_price_date"] = cash["benchmark_price_date"].ffill().bfill()
    cash["benchmark_source"] = cash["benchmark_source"].ffill().bfill()
    cash = cash.dropna(subset=["sp500_close"])

    shares = 0.0
    rows = []
    cum_net, cum_div, cum_realized = 0.0, 0.0, 0.0
    current_value = 0.0
    current_open_pnl = 0.0
    if kpis is not None and not kpis.empty:
        current_value = float(pd.to_numeric(pd.Series([kpis.iloc[0].get("calculated_positions_market_value_eur", 0)]), errors="coerce").fillna(0).iloc[0])
        current_open_pnl = float(pd.to_numeric(pd.Series([kpis.iloc[0].get("current_total_unrealized_pnl_eur", 0)]), errors="coerce").fillna(0).iloc[0])
    last_idx = cash.index[-1] if not cash.empty else None
    for idx, r in cash.iterrows():
        net = float(r.get("net_buy_value_reported", 0) or 0)
        div = float(r.get("dividend_received_eur", 0) or 0)
        realized = float(r.get("realized_pnl_reported", 0) or 0)
        price = float(r.get("sp500_close", np.nan))
        if pd.notna(price) and price != 0:
            shares += net / price
        cum_net += net
        cum_div += div
        cum_realized += realized
        sp_value = shares * price if pd.notna(price) else np.nan
        actual_proxy_result = cum_div + cum_realized
        if idx == last_idx:
            actual_proxy_result += current_open_pnl
        rows.append({
            "year_month": r["year_month"],
            "period_date": r["period_date"].date().isoformat(),
            "benchmark_price_date": r.get("benchmark_price_date"),
            "benchmark_source": r.get("benchmark_source"),
            "monthly_net_buy_value_eur": net,
            "monthly_dividends_received_eur": div,
            "monthly_reported_realized_pnl_eur": realized,
            "cumulative_net_buy_value_eur": cum_net,
            "cumulative_dividends_received_eur": cum_div,
            "cumulative_reported_realized_pnl_eur": cum_realized,
            "current_portfolio_value_reference_eur": current_value,
            "current_open_pnl_added_to_latest_month_eur": current_open_pnl if idx == last_idx else 0.0,
            "sp500_close": price,
            "sp500_virtual_shares": shares,
            "sp500_virtual_portfolio_value_eur": sp_value,
            "sp500_virtual_result_eur": sp_value - cum_net if pd.notna(sp_value) else np.nan,
            "sp500_virtual_return_pct": (sp_value - cum_net) / cum_net if cum_net else np.nan,
            "actual_available_result_eur": actual_proxy_result,
            "actual_available_return_pct": actual_proxy_result / cum_net if cum_net else np.nan,
            "actual_simple_result_eur": (current_value + cum_div - cum_net) if current_value or cum_div or cum_net else np.nan,
            "actual_simple_return_pct": (current_value + cum_div - cum_net) / cum_net if cum_net else np.nan,
            "benchmark_note": "Approximate: S&P 500 price index only, no dividends, no EUR/USD FX, no tax/fees. Actual line uses observed dividends + reported realized PnL; current open PnL is only added to latest month."
        })
    timeline = pd.DataFrame(rows)

    comp_rows = []
    latest = timeline.tail(1)
    if not latest.empty:
        r = latest.iloc[0]
        comp_rows.append({
            "period": "Since first available cash flow",
            "period_start": timeline["period_date"].min(),
            "period_end": timeline["period_date"].max(),
            "actual_available_return_pct": r.get("actual_available_return_pct"),
            "sp500_virtual_return_pct": r.get("sp500_virtual_return_pct"),
            "actual_available_result_eur": r.get("actual_available_result_eur"),
            "sp500_virtual_result_eur": r.get("sp500_virtual_result_eur"),
            "method": "Actual available return uses cumulative dividends + reported realized PnL, plus current open PnL only in the latest month. S&P virtual return invests the same monthly net buys into the S&P 500 price index.",
            "reliability": "Approximate. Actual monthly mark-to-market is not available yet."
        })
    comparison = pd.DataFrame(comp_rows)
    src = ""
    try:
        src = str(sp500_daily.get("source", pd.Series([""])).dropna().iloc[-1])
    except Exception:
        src = "external source/cache"
    status = pd.DataFrame([{
        "benchmark_status": "available" if not timeline.empty else "not_available",
        "benchmark_name": "S&P 500 price index",
        "source": src,
        "rows_daily": int(len(sp500_daily)),
        "rows_timeline": int(len(timeline)),
        "min_benchmark_date": str(sp500_daily["date"].min()) if not sp500_daily.empty and "date" in sp500_daily.columns else "",
        "max_benchmark_date": str(sp500_daily["date"].max()) if not sp500_daily.empty and "date" in sp500_daily.columns else "",
        "note": "Approximate benchmark only: S&P 500 price index movement, excluding dividends, EUR/USD FX conversion, taxes and fees. Source fallback order: FRED, Stooq, Yahoo, then local cache."
    }])
    return timeline, comparison, status

def build_decision_quality(pos, divs, orders):
    dims = ["source","broker","ticker","ticker_display","symbol_raw","true_ticker","display_name","asset_type","sector","industry","country","is_etf","is_leveraged","risk_bucket","custom_portfolio"]
    parts = []
    if orders is not None and not orders.empty and "ticker" in orders.columns:
        o = orders.copy()
        for c in dims:
            if c not in o.columns:
                o[c] = "Unknown" if c not in ["source","broker","ticker"] else o.get(c, "trading212")
        agg = gsum(o, dims, ["buy_value_reported","sell_value_reported","net_buy_value_reported","realized_pnl_reported","realised_pnl_reported","order_count","filled_quantity_abs","sell_order_count","realized_pnl_available_count","sell_order_without_reported_realized_pnl_count"])
        dts = o.dropna(subset=["event_date"]).groupby(dims, dropna=False)["event_date"].agg(first_order_date="min", last_order_date="max").reset_index() if "event_date" in o.columns else pd.DataFrame()
        if not dts.empty:
            agg = agg.merge(dts, on=dims, how="left")
        parts.append(agg)
    if pos is not None and not pos.empty and "ticker" in pos.columns:
        h = gsum(pos, dims, ["quantity","market_value_eur","cost_basis_eur","unrealized_pnl_eur","fx_pnl_eur","total_unrealized_pnl_eur"])
        parts.append(h)
    if divs is not None and not divs.empty and "ticker" in divs.columns:
        d = gsum(divs, dims, ["dividend_received_eur","dividend_net_eur","dividend_tax_eur","dividend_count"])
        parts.append(d)
    if not parts:
        return pd.DataFrame()
    out = parts[0]
    for part in parts[1:]:
        keys = [c for c in dims if c in out.columns and c in part.columns]
        out = out.merge(part, on=keys, how="outer")
    for c in out.select_dtypes(include=["number"]).columns:
        out[c] = out[c].fillna(0)
    total_value = out.get("market_value_eur", pd.Series(dtype=float)).sum(skipna=True)
    out["current_allocation_pct"] = safe_div(out.get("market_value_eur", 0), total_value) if total_value else np.nan
    out["open_position_return_pct"] = safe_div(out.get("total_unrealized_pnl_eur", 0), out.get("cost_basis_eur", np.nan))
    out["realized_return_on_sells_pct"] = safe_div(out.get("realized_pnl_reported", 0), out.get("sell_value_reported", np.nan))
    out["total_observed_result_eur"] = out.get("realized_pnl_reported", 0) + out.get("total_unrealized_pnl_eur", 0) + out.get("dividend_received_eur", 0)
    out["dividend_vs_cost_pct"] = safe_div(out.get("dividend_received_eur", 0), out.get("cost_basis_eur", np.nan))
    if "first_order_date" in out.columns:
        out["first_order_date"] = pd.to_datetime(out["first_order_date"], errors="coerce")
        out["last_order_date"] = pd.to_datetime(out.get("last_order_date"), errors="coerce")
        try:
            out["days_since_first_order"] = (pd.Timestamp.now() - out["first_order_date"].dt.tz_localize(None)).dt.days
        except Exception:
            out["days_since_first_order"] = np.nan
    else:
        out["days_since_first_order"] = np.nan
    def note(r):
        notes = []
        if r.get("current_allocation_pct", 0) >= 0.15:
            notes.append("large current allocation")
        if r.get("total_unrealized_pnl_eur", 0) < 0 and r.get("dividend_received_eur", 0) <= 0:
            notes.append("open loss, no dividend offset in available data")
        if r.get("realized_pnl_reported", 0) < 0:
            notes.append("reported realized loss")
        if r.get("dividend_received_eur", 0) > 0 and r.get("total_unrealized_pnl_eur", 0) >= 0:
            notes.append("dividend contributor with non-negative open PnL")
        if r.get("buy_value_reported", 0) > 0 and r.get("sell_value_reported", 0) == 0:
            notes.append("buy-only so far")
        return "; ".join(notes) if notes else "no obvious flag from available source data"
    out["decision_quality_note"] = out.apply(note, axis=1)
    preferred = ["display_name","true_ticker","asset_type","sector","country","current_allocation_pct","market_value_eur","cost_basis_eur","buy_value_reported","sell_value_reported","net_buy_value_reported","realized_pnl_reported","sell_order_count","realized_pnl_available_count","sell_order_without_reported_realized_pnl_count","unrealized_pnl_eur","fx_pnl_eur","total_unrealized_pnl_eur","dividend_received_eur","total_observed_result_eur","open_position_return_pct","realized_return_on_sells_pct","dividend_vs_cost_pct","order_count","days_since_first_order","decision_quality_note","source","broker","symbol_raw","ticker"]
    cols = [c for c in preferred if c in out.columns] + [c for c in out.columns if c not in preferred]
    return out[cols].sort_values("total_observed_result_eur", ascending=False) if "total_observed_result_eur" in out.columns else out[cols]

def build_gold(pos, divs, orders, tx, cash, fx, acc_cur):
    dims = ["source","broker","ticker","ticker_display","symbol_raw","true_ticker","display_name","asset_type","sector","industry","country","is_etf","is_leveraged","risk_bucket","custom_portfolio"]
    hold = gsum(pos, dims, ["quantity","market_value_eur","cost_basis_eur","unrealized_pnl_eur","fx_pnl_eur","total_unrealized_pnl_eur"])
    if not hold.empty:
        total = hold["market_value_eur"].sum(skipna=True)
        hold["allocation_pct"] = hold["market_value_eur"] / total if total else np.nan
        hold["return_pct_on_cost_eur"] = safe_div(hold["total_unrealized_pnl_eur"], hold["cost_basis_eur"])
        hold = hold.sort_values("market_value_eur", ascending=False)

    divsym = gsum(divs, dims, ["dividend_received_eur","dividend_net_eur","dividend_tax_eur","dividend_count"])
    if not divsym.empty:
        divsym = divsym.sort_values("dividend_received_eur", ascending=False)

    sym = hold.copy()
    if not divsym.empty:
        merge_cols = [c for c in dims if c in sym.columns and c in divsym.columns]
        sym = sym.merge(divsym, on=merge_cols, how="outer")
    for c in sym.select_dtypes(include=["number"]).columns:
        sym[c] = sym[c].fillna(0)
    if "dividend_received_eur" in sym.columns:
        sym["price_pnl_plus_dividends_eur"] = sym.get("unrealized_pnl_eur", 0) + sym["dividend_received_eur"]
        sym["total_pnl_plus_dividends_including_fx_eur"] = sym.get("total_unrealized_pnl_eur", 0) + sym["dividend_received_eur"]
        sym["dividend_yield_on_current_value"] = safe_div(sym["dividend_received_eur"], sym.get("market_value_eur", np.nan))

    def merge_parts(parts, keys):
        parts = [p for p in parts if not p.empty]
        if not parts:
            return pd.DataFrame()
        out = parts[0]
        for p in parts[1:]:
            out = out.merge(p, on=keys, how="outer")
        for c in out.select_dtypes(include=["number"]).columns:
            out[c] = out[c].fillna(0)
        return out.sort_values(keys)

    annual_parts, quarter_parts, month_parts = [], [], []
    if not divs.empty:
        annual_parts.append(gsum(divs, ["source","broker","year"], ["dividend_received_eur","dividend_net_eur","dividend_tax_eur","dividend_count"]))
        quarter_parts.append(gsum(divs, ["source","broker","year","quarter","year_quarter"], ["dividend_received_eur","dividend_net_eur","dividend_tax_eur","dividend_count"]))
        month_parts.append(gsum(divs, ["source","broker","year","month","year_month"], ["dividend_received_eur","dividend_net_eur","dividend_tax_eur","dividend_count"]))
    if not orders.empty:
        annual_parts.append(gsum(orders, ["source","broker","year"], ["buy_value_reported","sell_value_reported","net_buy_value_reported","realized_pnl_reported","realised_pnl_reported","order_count","sell_order_count","realized_pnl_available_count","sell_order_without_reported_realized_pnl_count"]))
        quarter_parts.append(gsum(orders, ["source","broker","year","quarter","year_quarter"], ["buy_value_reported","sell_value_reported","net_buy_value_reported","realized_pnl_reported","realised_pnl_reported","order_count","sell_order_count","realized_pnl_available_count","sell_order_without_reported_realized_pnl_count"]))
        month_parts.append(gsum(orders, ["source","broker","year","month","year_month"], ["buy_value_reported","sell_value_reported","net_buy_value_reported","realized_pnl_reported","realised_pnl_reported","order_count","sell_order_count","realized_pnl_available_count","sell_order_without_reported_realized_pnl_count"]))
    if not tx.empty:
        annual_parts.append(gsum(tx, ["source","broker","year"], ["transaction_amount_reported","transaction_count"]))
        quarter_parts.append(gsum(tx, ["source","broker","year","quarter","year_quarter"], ["transaction_amount_reported","transaction_count"]))
        month_parts.append(gsum(tx, ["source","broker","year","month","year_month"], ["transaction_amount_reported","transaction_count"]))

    annual = merge_parts(annual_parts, ["source","broker","year"])
    quarterly = merge_parts(quarter_parts, ["source","broker","year","quarter","year_quarter"])
    monthly = merge_parts(month_parts, ["source","broker","year","month","year_month"])
    if not monthly.empty and "dividend_received_eur" in monthly.columns:
        monthly["cumulative_dividend_received_eur"] = monthly["dividend_received_eur"].cumsum()

    total_value = hold["market_value_eur"].sum(skipna=True) if "market_value_eur" in hold.columns else np.nan
    total_cost = hold["cost_basis_eur"].sum(skipna=True) if "cost_basis_eur" in hold.columns else np.nan
    price_pnl = hold["unrealized_pnl_eur"].sum(skipna=True) if "unrealized_pnl_eur" in hold.columns else np.nan
    fx_pnl = hold["fx_pnl_eur"].sum(skipna=True) if "fx_pnl_eur" in hold.columns else np.nan
    total_pnl = hold["total_unrealized_pnl_eur"].sum(skipna=True) if "total_unrealized_pnl_eur" in hold.columns else np.nan
    total_div = divs["dividend_received_eur"].sum(skipna=True) if "dividend_received_eur" in divs.columns else np.nan
    acc_total = np.nan; cash_free = np.nan; invested = np.nan
    if cash is not None and not cash.empty:
        for nm, var in [("total","acc_total"),("free","cash_free"),("invested","invested")]:
            if nm in cash.columns:
                s = pd.to_numeric(cash[nm], errors="coerce").dropna().tail(1)
                if not s.empty:
                    if var == "acc_total": acc_total = float(s.iloc[0])
                    if var == "cash_free": cash_free = float(s.iloc[0])
                    if var == "invested": invested = float(s.iloc[0])
    largest = hold["allocation_pct"].max(skipna=True) if "allocation_pct" in hold.columns and not hold.empty else np.nan
    top5 = hold["allocation_pct"].sort_values(ascending=False).head(5).sum(skipna=True) if "allocation_pct" in hold.columns and not hold.empty else np.nan
    sources_present = sorted(set(str(x) for x in pos.get("source", pd.Series(dtype=str)).dropna().unique()))
    brokers_present = sorted(set(str(x) for x in pos.get("broker", pd.Series(dtype=str)).dropna().unique()))
    kpis = pd.DataFrame([{
        "source":"combined" if len(sources_present) > 1 or "manual" in sources_present else (sources_present[0] if sources_present else "unknown"),
        "broker":"all brokers" if len(brokers_present) > 1 else (brokers_present[0] if brokers_present else "unknown"),
        "generated_at":datetime.now().strftime("%Y-%m-%d %H:%M:%S"),"reporting_currency":acc_cur,
        "positions_count":len(hold),"orders_count":len(orders),"transactions_count":len(tx),"dividends_count":len(divs),
        "account_total_reference_from_trading212":acc_total,"calculated_positions_market_value_eur":total_value,
        "difference_account_total_vs_calculated_positions":acc_total-total_value if pd.notna(acc_total) and pd.notna(total_value) else np.nan,
        "cash_available_reference_eur":cash_free,"invested_reference_eur":invested,"current_cost_basis_eur":total_cost,
        "current_price_pnl_eur":price_pnl,"current_fx_impact_eur":fx_pnl,"current_total_unrealized_pnl_eur":total_pnl,
        "current_return_pct_on_cost":total_pnl/total_cost if total_cost and total_cost != 0 else np.nan,
        "lifetime_dividends_received_eur":total_div,"unrealized_pnl_plus_lifetime_dividends_eur":total_pnl+total_div if pd.notna(total_pnl) and pd.notna(total_div) else np.nan,
        "largest_position_pct":largest,"top5_concentration_pct":top5
    }])
    dq = pd.DataFrame([
        {"check":"positions_rows","value":len(pos)},
        {"check":"positions_with_missing_market_value_eur","value":int(pos["market_value_eur"].isna().sum()) if "market_value_eur" in pos.columns else None},
        {"check":"positions_converted","value":int((pos.get("conversion_check")=="converted").sum()) if "conversion_check" in pos.columns else None},
        {"check":"fx_rates_used","value":len(fx)}
    ])
    forecast = forecast_table(kpis, annual, monthly=monthly)
    decision = build_decision_quality(pos, divs, orders)
    return {
        "gold_overview_kpis": kpis, "gold_data_quality": dq, "raw_account_cash_reference": cash, "raw_fx_rates_used": fx,
        "gold_current_positions_by_symbol": hold, "gold_dividend_summary_by_symbol": divsym, "gold_symbol_summary": sym,
        "gold_annual_summary": annual, "gold_quarterly_summary": quarterly, "gold_monthly_summary": monthly, "gold_forecast_scenarios": forecast, "gold_decision_quality": decision
    }


def forecast_table(kpis, annual, years=20, monthly=None):
    if kpis.empty:
        return pd.DataFrame()

    row = kpis.iloc[0]
    start = float(row.get("calculated_positions_market_value_eur", 0) or 0)

    start_div = float(row.get("lifetime_dividends_received_eur", 0) or 0)
    start_div_basis = "Lifetime dividends received, fallback"

    # Better baseline for dividend forecasting:
    # Prefer trailing 12 months, then latest complete year, then latest available year.
    if monthly is not None and not monthly.empty and "dividend_received_eur" in monthly.columns:
        m = monthly.copy()
        if "year_month" in m.columns:
            m["_ym"] = pd.to_datetime(m["year_month"].astype(str) + "-01", errors="coerce")
            m = m.dropna(subset=["_ym"]).sort_values("_ym")
            m = m.groupby("_ym", as_index=False)["dividend_received_eur"].sum().sort_values("_ym")
            if len(m) >= 12:
                start_div = float(m.tail(12)["dividend_received_eur"].sum())
                start_div_basis = "Trailing 12 available months"
            elif len(m) > 0:
                months_available = len(m)
                start_div = float(m["dividend_received_eur"].sum()) * (12 / months_available)
                start_div_basis = f"Annualized from {months_available} available months"

    if (start_div <= 0 or start_div_basis == "Lifetime dividends received, fallback") and annual is not None and not annual.empty and "dividend_received_eur" in annual.columns:
        a = annual.dropna(subset=["year"]).copy()
        if not a.empty:
            a["year"] = a["year"].astype(int)
            current_year = datetime.now().year
            complete = a[a["year"] < current_year]
            if not complete.empty:
                latest_complete = int(complete["year"].max())
                val = complete.loc[complete["year"] == latest_complete, "dividend_received_eur"].sum()
                if val > 0:
                    start_div = float(val)
                    start_div_basis = f"Latest complete year ({latest_complete})"
            else:
                latest = int(a["year"].max())
                val = a.loc[a["year"] == latest, "dividend_received_eur"].sum()
                if val > 0:
                    start_div = float(val)
                    start_div_basis = f"Latest available year ({latest})"

    # Available-history return assumption:
    # Do not use the current open return directly as an annual forecast rate.
    # That can overreact to a final/current PnL spike. Instead, use the
    # available observed result and annualize it over the activity window.
    hist_ret = 0.07
    hist_return_basis = "Default 7%, not enough available history"
    observed_result = (
        pd.to_numeric(pd.Series([row.get("current_total_unrealized_pnl_eur", np.nan)]), errors="coerce").iloc[0]
        + pd.to_numeric(pd.Series([row.get("lifetime_dividends_received_eur", np.nan)]), errors="coerce").iloc[0]
    )
    if annual is not None and not annual.empty and "realized_pnl_reported" in annual.columns:
        observed_result += pd.to_numeric(annual["realized_pnl_reported"], errors="coerce").sum(skipna=True)
    invested_base = pd.to_numeric(pd.Series([row.get("current_cost_basis_eur", np.nan)]), errors="coerce").iloc[0]

    activity_years = np.nan
    if monthly is not None and not monthly.empty and "year_month" in monthly.columns:
        mm = monthly.copy()
        mm["_ym"] = pd.to_datetime(mm["year_month"].astype(str) + "-01", errors="coerce")
        mm = mm.dropna(subset=["_ym"]).sort_values("_ym")
        if not mm.empty:
            start_month = mm["_ym"].min()
            end_month = mm["_ym"].max()
            activity_years = max((end_month - start_month).days / 365.25, 1 / 12)
    elif annual is not None and not annual.empty and "year" in annual.columns:
        yy = pd.to_numeric(annual["year"], errors="coerce").dropna()
        if not yy.empty:
            activity_years = max(float(yy.max() - yy.min() + 1), 1.0)

    if pd.notna(observed_result) and pd.notna(invested_base) and invested_base > 0 and pd.notna(activity_years) and activity_years > 0:
        total_return_proxy = observed_result / invested_base
        hist_ret = (1 + total_return_proxy) ** (1 / activity_years) - 1 if total_return_proxy > -0.99 else -0.25
        hist_ret = max(min(float(hist_ret), 0.15), -0.15)
        hist_return_basis = f"Annualized observed result over {activity_years:.1f} years"

    # Better available-history dividend growth:
    # Use complete years only. This avoids nonsense CAGR from a tiny first partial
    # year or a current partial year. Keep the cap modest because portfolio
    # contributions can make dividends rise quickly even when underlying dividend
    # growth is not actually that high.
    div_g = 0.04
    div_growth_basis = "Default 4%, not enough complete annual history"

    if annual is not None and not annual.empty and "dividend_received_eur" in annual.columns:
        dy = annual.dropna(subset=["year"]).copy()
        if not dy.empty:
            dy["year"] = dy["year"].astype(int)
            current_year = datetime.now().year
            dy = dy[dy["year"] < current_year]
            dy = dy.groupby("year")["dividend_received_eur"].sum().sort_index()

            # Drop tiny partial starter year if it is obviously much smaller than the next year.
            if len(dy) >= 3 and dy.iloc[0] < 0.25 * dy.iloc[1]:
                dy = dy.iloc[1:]

            if len(dy) >= 2:
                yoy = dy.pct_change().replace([np.inf, -np.inf], np.nan).dropna()
                if not yoy.empty:
                    div_g = float(yoy.mean())
                    div_g = max(min(div_g, 0.08), -0.08)
                    div_growth_basis = (
                        f"Average YoY growth from complete years "
                        f"{int(dy.index.min())}-{int(dy.index.max())}, capped at 8%"
                    )

    scenarios = [
        ("Default", "Conservative", 0.04, 0.02, "Fixed default conservative assumptions"),
        ("Default", "Base", 0.07, 0.04, "Fixed default base assumptions"),
        ("Default", "Optimistic", 0.10, 0.06, "Fixed default optimistic assumptions"),
        ("Based on available broker/manual history", "Available-history based", hist_ret, div_g, f"Return basis: {hist_return_basis}. Dividend growth basis: {div_growth_basis}")
    ]

    rows = []
    for mode, sc, ret, dg, basis in scenarios:
        value, div, cumdiv = start, start_div, 0.0
        prev_value, prev_div = np.nan, np.nan
        for y in range(years + 1):
            if y == 0:
                portfolio_step = "Year 0 = current calculated portfolio value"
                dividend_step = f"Year 0 = baseline annual dividend ({start_div_basis})"
                cumdiv_step = "Year 0 = 0 cumulative projected dividends"
            else:
                portfolio_step = f"({prev_value:.2f} + {prev_div:.2f} + 0.00) * (1 + {ret:.4f})"
                dividend_step = f"{prev_div:.2f} * (1 + {dg:.4f})"
                cumdiv_step = f"previous cumulative dividends + previous annual dividend ({prev_div:.2f})"

            rows.append({
                "source": row.get("source", "combined"),
                "broker": row.get("broker", "all brokers"),
                "forecast_mode": mode,
                "scenario": sc,
                "year_number": y,
                "portfolio_value_eur": value,
                "annual_dividend_eur": div,
                "cumulative_dividends_eur": cumdiv,
                "annual_return_assumption": ret,
                "dividend_growth_assumption": dg,
                "annual_contribution_assumption_eur": 0.0,
                "portfolio_previous_value_eur": prev_value,
                "previous_annual_dividend_eur": prev_div,
                "starting_portfolio_value_eur": start,
                "starting_annual_dividend_eur": start_div,
                "starting_dividend_basis": start_div_basis,
                "portfolio_formula": "Year 0 = current portfolio value. Next year = (previous portfolio value + previous annual dividend + annual contribution) * (1 + annual return). Default contribution is 0.",
                "dividend_formula": "Year 0 = baseline annual dividend. Next year = previous annual dividend * (1 + dividend growth).",
                "portfolio_calculation_step": portfolio_step,
                "dividend_calculation_step": dividend_step,
                "cumulative_dividend_calculation_step": cumdiv_step,
                "calculation_basis": basis,
                "note": "Scenario model based on available broker/manual data and assumptions, not a prediction."
            })

            if y < years:
                prev_value, prev_div = value, div
                cumdiv += div
                value = (value + div) * (1 + ret)
                div = div * (1 + dg)

    return pd.DataFrame(rows)

def excel_safe(df):
    if df is None or df.empty: return df
    out = df.copy()
    for c in out.columns:
        if pd.api.types.is_datetime64_any_dtype(out[c]):
            try:
                if getattr(out[c].dt, "tz", None) is not None:
                    out[c] = out[c].dt.tz_convert(None)
            except Exception:
                pass
    return out

def export_all(tables, raw, p):
    allout = {**tables, **raw}
    for name, df in allout.items():
        if df is None or df.empty: continue
        layer = "gold" if name.startswith("gold_") else "silver" if name.startswith("silver_") else "raw" if name.startswith("raw_") else "analysis_csv"
        save_df(df, p.get(layer, p["analysis_csv"])/f"{name}.csv")
        save_df(df, p["analysis_csv"]/f"{name}.csv")
    xlsx = p["output"]/f"portfolio_analytics_dashboard_{datetime.now():%Y%m%d_%H%M%S}.xlsx"
    engine = "openpyxl"
    engine_kwargs = {}
    try:
        import xlsxwriter  # noqa: F401
        engine = "xlsxwriter"
        engine_kwargs = {"options": {"strings_to_urls": False, "strings_to_formulas": False}}
    except Exception:
        pass

    with pd.ExcelWriter(xlsx, engine=engine, engine_kwargs=engine_kwargs) as writer:
        for name, df in allout.items():
            if df is not None and not df.empty:
                excel_safe(df).to_excel(writer, sheet_name=name[:31], index=False)
    return xlsx

def llm_prompt(kpis, holdings, annual, benchmark_comparison=None, benchmark_status=None):
    if kpis.empty: return "No portfolio data available yet."
    r = kpis.iloc[0]
    account_reference = pd.to_numeric(pd.Series([r.get("account_total_reference_from_trading212", np.nan)]), errors="coerce").iloc[0]
    account_reference_line = (
        f"- Trading212 account total reference: {account_reference:,.2f} EUR"
        if pd.notna(account_reference)
        else "- Trading212 account total reference: not available in this run. The calculated portfolio value below can include Trading212 plus manual broker inputs."
    )
    name = "display_name" if "display_name" in holdings.columns else "ticker"
    broker_lines = []
    if holdings is not None and not holdings.empty and "broker" in holdings.columns and "market_value_eur" in holdings.columns:
        total_value = pd.to_numeric(holdings["market_value_eur"], errors="coerce").sum(skipna=True)
        by_broker = holdings.copy()
        by_broker["market_value_eur"] = pd.to_numeric(by_broker["market_value_eur"], errors="coerce")
        by_broker = by_broker.groupby("broker", dropna=False)["market_value_eur"].sum().sort_values(ascending=False)
        for broker, value in by_broker.items():
            pct_value = value / total_value if total_value else np.nan
            pct_txt = f", {pct_value*100:,.2f}%" if pd.notna(pct_value) else ""
            broker_lines.append(f"- {broker}: {value:,.2f} EUR{pct_txt}")
    top = []
    if holdings is not None and not holdings.empty:
        for _, h in holdings.sort_values("market_value_eur", ascending=False).head(10).iterrows():
            top.append(f"- {h.get(name)} ({h.get('broker','broker unknown')}): value {h.get('market_value_eur',0):,.2f} EUR, allocation {h.get('allocation_pct',0)*100:,.2f}%, price PnL {h.get('unrealized_pnl_eur',0):,.2f}, FX {h.get('fx_pnl_eur',0):,.2f}")
    divs = []
    if annual is not None and not annual.empty and "dividend_received_eur" in annual.columns:
        a = annual.dropna(subset=["year"]).copy()
        if not a.empty:
            a["year"] = a["year"].astype(int)
            a = a.groupby("year", as_index=False)["dividend_received_eur"].sum().sort_values("year")
            for _, d in a.tail(5).iterrows():
                divs.append(f"- {int(d.get('year'))}: {d.get('dividend_received_eur',0):,.2f} EUR")
    benchmark_lines = []
    if benchmark_comparison is not None and not benchmark_comparison.empty:
        b = benchmark_comparison.iloc[-1]
        benchmark_lines.append(f"- Period: {b.get('period_start','?')} to {b.get('period_end','?')}")
        benchmark_lines.append(f"- Actual available-data return proxy: {float(pd.to_numeric(pd.Series([b.get('actual_available_return_pct', np.nan)]), errors='coerce').iloc[0])*100:,.2f}%")
        benchmark_lines.append(f"- S&P 500 virtual return: {float(pd.to_numeric(pd.Series([b.get('sp500_virtual_return_pct', np.nan)]), errors='coerce').iloc[0])*100:,.2f}%")
        benchmark_lines.append(f"- Actual available result: {float(pd.to_numeric(pd.Series([b.get('actual_available_result_eur', np.nan)]), errors='coerce').iloc[0]):,.2f} EUR")
        benchmark_lines.append(f"- S&P 500 virtual result: {float(pd.to_numeric(pd.Series([b.get('sp500_virtual_result_eur', np.nan)]), errors='coerce').iloc[0]):,.2f} EUR")
    if benchmark_status is not None and not benchmark_status.empty:
        s = benchmark_status.iloc[0]
        benchmark_lines.append(f"- Benchmark source/status: {s.get('benchmark_status','unknown')} / {s.get('source','cache or external source')}")
    return f"""Analyze my investment portfolio based on this data. Treat it as educational analysis, not guaranteed financial advice.

Portfolio snapshot:
- Calculated current portfolio value: {r.get('calculated_positions_market_value_eur',0):,.2f} EUR
{account_reference_line}
- Price PnL excluding FX: {r.get('current_price_pnl_eur',0):,.2f} EUR
- FX impact: {r.get('current_fx_impact_eur',0):,.2f} EUR
- Total unrealized PnL including FX: {r.get('current_total_unrealized_pnl_eur',0):,.2f} EUR
- Dividends received from available broker/manual data: {r.get('lifetime_dividends_received_eur',0):,.2f} EUR
- Largest position weight: {r.get('largest_position_pct',0)*100:,.2f}%
- Top 5 concentration: {r.get('top5_concentration_pct',0)*100:,.2f}%

Top positions:
{chr(10).join(top) if top else "- No positions available"}

Broker value breakdown:
{chr(10).join(broker_lines) if broker_lines else "- No broker breakdown available"}

Recent annual dividends:
{chr(10).join(divs) if divs else "- No annual dividend data available"}

S&P 500 benchmark comparison:
{chr(10).join(benchmark_lines) if benchmark_lines else "- No S&P 500 benchmark comparison available"}

Benchmark caveats:
- The S&P 500 comparison is approximate and uses SP500 / S&P 500 price-index data only.
- It excludes S&P dividends, EUR/USD FX conversion, taxes and fees.
- Trading212/API history does not provide exact month-end portfolio value for every past month.
- The actual portfolio return line is an available-data proxy, not true historical valuation, broker MWRR or time-weighted return.

Please provide:
1. Portfolio strengths
2. Portfolio weaknesses
3. Concentration risks
4. FX/currency risks
5. Dividend observations based only on available data
6. Performance observations versus the approximate S&P 500 benchmark
7. Possible rebalancing considerations
8. Forecasting assumptions I should test
9. Questions I should ask myself before buying more or selling positions
"""

def run_analysis(api_key="", api_secret="", data_refresh_mode="refresh_current_year", fx_refresh_mode="online_or_cache", base_dir=None, log=default_log):
    p = paths(base_dir)
    if data_refresh_mode not in ["refresh_current_year","cache_only","full_refresh"]:
        raise ValueError("Invalid data refresh mode.")
    if fx_refresh_mode not in ["online_or_cache","cache_only","off"]:
        raise ValueError("Invalid FX refresh mode.")
    use_api = data_refresh_mode in ["refresh_current_year","full_refresh"]
    if use_api and not api_key:
        raise ValueError("API key is required unless Data mode is cache_only. API secret can be left empty if your Trading212 key does not use one.")
    allow_online_fx = fx_refresh_mode == "online_or_cache"
    client = Trading212Client(api_key, api_secret or "", log=log) if use_api else None
    log("Preparing folders and loading mapping/manual inputs...")
    mapping = mapping_load(p)
    manual_sources = load_manual_source_tables(p)
    manual_validation = validate_manual_sources(manual_sources)
    cash = account_reference(client, use_api, p, log)
    acc_cur = account_currency_from_reference(cash)
    meta = metadata(client, use_api, p)
    if use_api:
        log("Fetching current positions...")
        pos_raw = add_source(client.positions())
        save_df(pos_raw, p["raw"]/f"raw_positions_trading212_current_{datetime.now():%Y%m%d_%H%M%S}.csv")
    else:
        log("Loading current positions from local cache...")
        pos_raw = latest_positions_snapshot(p)
    log("Loading history datasets...")
    if data_refresh_mode == "cache_only":
        orders_raw = add_source(cache_load("orders", p))
        tx_raw = add_source(cache_load("transactions", p))
        div_raw = add_source(cache_load("dividends", p))

        if pos_raw.empty and orders_raw.empty and tx_raw.empty and div_raw.empty:
            raise ValueError(
                "No local cached data found in this app folder. "
                "Run once with 'Refresh current year only - recommended', "
                "or copy the previous data/ folder into this app folder before using cache-only mode."
            )
    else:
        full = data_refresh_mode == "full_refresh"
        orders_raw = fetch_history(client, "orders", p, full)
        tx_raw = fetch_history(client, "transactions", p, full)
        div_raw = fetch_history(client, "dividends", p, full)
    log("Building silver layer...")
    pos, fx = clean_positions(pos_raw, meta, acc_cur, allow_online_fx, fx_refresh_mode, p, log)
    divs = mapping_apply(clean_dividends(div_raw), mapping)
    orders = mapping_apply(clean_orders(orders_raw), mapping)
    tx = mapping_apply(clean_transactions(tx_raw), mapping)
    pos = mapping_apply(pos, mapping)

    log("Applying user-managed manual source inputs...")
    manual_clean = clean_manual_inputs(manual_sources, acc_cur)
    manual_pos = mapping_apply(manual_clean.get("manual_positions_clean", pd.DataFrame()), mapping)
    manual_divs = mapping_apply(manual_clean.get("manual_dividends", pd.DataFrame()), mapping)
    manual_orders = mapping_apply(manual_clean.get("manual_orders", pd.DataFrame()), mapping)
    manual_tx = mapping_apply(manual_clean.get("manual_transactions_clean", pd.DataFrame()), mapping)

    if not manual_pos.empty:
        pos = pd.concat([pos, manual_pos], ignore_index=True, sort=False)
    if not manual_divs.empty:
        divs = pd.concat([divs, manual_divs], ignore_index=True, sort=False)
    if not manual_orders.empty:
        orders = pd.concat([orders, manual_orders], ignore_index=True, sort=False)
    if not manual_tx.empty:
        tx = pd.concat([tx, manual_tx], ignore_index=True, sort=False)

    log("Building gold layer...")
    tables = build_gold(pos, divs, orders, tx, cash, fx, acc_cur)

    log("Updating S&P 500 benchmark cache and building benchmark views...")
    sp500_daily = load_sp500_benchmark(p, allow_online=True, log=log)
    benchmark_timeline, benchmark_comparison, benchmark_status = build_sp500_benchmark_views(
        tables.get("gold_monthly_summary", pd.DataFrame()),
        tables.get("gold_overview_kpis", pd.DataFrame()),
        sp500_daily
    )
    tables["raw_sp500_benchmark_daily"] = sp500_daily
    tables["gold_benchmark_timeline"] = benchmark_timeline
    tables["gold_benchmark_comparison"] = benchmark_comparison
    tables["gold_benchmark_status"] = benchmark_status

    if tables.get("gold_current_positions_by_symbol", pd.DataFrame()).empty:
        log(
            "WARNING: No holdings were produced. "
            "This usually means the selected data mode did not load positions. "
            "Try 'Refresh current year only - recommended'."
        )

    raw = {
        "silver_positions":pos, "silver_dividends":divs, "silver_orders":orders, "silver_transactions":tx,
        "source_manual_transactions": manual_sources.get("manual_transactions", pd.DataFrame()),
        "source_manual_positions": manual_sources.get("manual_positions", pd.DataFrame()),
        "manual_input_validation": manual_validation
    }
    prompt = llm_prompt(
        tables["gold_overview_kpis"],
        tables["gold_current_positions_by_symbol"],
        tables["gold_annual_summary"],
        tables.get("gold_benchmark_comparison"),
        tables.get("gold_benchmark_status")
    )
    log("Exporting Excel/CSV...")
    xlsx = export_all(tables, raw, p)
    log("Done. Dashboard is ready.")
    return {"tables":tables, "raw_clean":raw, "paths":p, "excel_path":xlsx, "account_currency":acc_cur, "llm_prompt":prompt, "mapping":mapping, "manual_sources": manual_sources, "manual_validation": manual_validation}
