"""Verify the FA aggregation fix:
   - OLD method: simple arithmetic mean of per-row FAs (what the backend
     was doing before the fix).
   - NEW method: per-week-average rule from app.py:4430-4442
     `_per_week_avg_metrics` (what Streamlit's Forecast Accuracy weekly
     view uses by default).

Runs both methods on backtest_results pulled from Postgres for
tier='GOLD' and for ALL tiers. Prints side-by-side.
"""
from __future__ import annotations

import sys
from pathlib import Path

import numpy as np
import pandas as pd
from sqlalchemy import text

try:
    sys.stdout.reconfigure(encoding="utf-8")
except Exception:
    pass

ROOT = Path(__file__).resolve().parent
sys.path.insert(0, str(ROOT))

from db.connection import get_engine  # noqa: E402


def _load_rows(engine, tier_filter: str | None) -> pd.DataFrame:
    """Mirrors backend/repositories/demand_repo.get_forecast_accuracy."""
    sql = """
        SELECT
            p.sku, br.year, br.week,
            br.forecast::float AS forecast, br.actual::float AS actual,
            sp.tier, sp.total_xyz AS xyz
        FROM backtest_results br
        JOIN dim_products p ON br.product_id = p.id
        LEFT JOIN sku_planning sp ON sp.product_id = p.id
        WHERE br.actual IS NOT NULL AND br.actual > 0
    """
    params: dict = {}
    if tier_filter:
        sql += " AND sp.tier ILIKE :tier"
        params["tier"] = f"%{tier_filter}%"
    return pd.read_sql(text(sql), engine, params=params)


def _enrich(fa: pd.DataFrame) -> pd.DataFrame:
    """Per-row enrichment — verbatim from app.py:4172-4181."""
    fa = fa[fa["actual"] > 0].copy()
    if len(fa) == 0:
        return fa
    fa["error"] = np.abs(fa["forecast"] - fa["actual"])
    fa["fa"] = np.maximum(0, 1 - fa["error"] / fa["actual"])
    fa["fa_signed"] = fa["forecast"] / fa["actual"]
    fa["bias"] = (fa["forecast"] - fa["actual"]) / fa["actual"]
    fa["hit"] = (fa["error"] / fa["actual"].clip(lower=1) <= 0.3).astype(int)
    return fa


def old_method(fa: pd.DataFrame) -> dict:
    """The OLD backend rule: simple arithmetic mean of per-row metrics."""
    if len(fa) == 0:
        return {"n": 0, "fa": 0, "fa_signed": 0, "bias": 0, "hit": 0}
    sum_f = fa["forecast"].sum()
    sum_a = fa["actual"].sum()
    return {
        "n":         int(len(fa)),
        "fa":        float(fa["fa"].mean() * 100),
        "fa_signed": float(sum_f / sum_a * 100) if sum_a else 0.0,
        "bias":      float((sum_f - sum_a) / sum_a * 100) if sum_a else 0.0,
        "hit":       float(fa["hit"].mean() * 100),
    }


def new_method(fa: pd.DataFrame) -> dict:
    """Per-week-average rule — verbatim from app.py:4430-4442 (groups by week
    only). Hit rate stays simple mean of per-row hits."""
    if len(fa) == 0:
        return {"n": 0, "fa": 0, "fa_signed": 0, "bias": 0, "hit": 0, "n_weeks": 0}
    wk_totals = fa.groupby("week", as_index=False).agg(
        ac=("actual", "sum"), fc=("forecast", "sum"))
    wk_totals = wk_totals[wk_totals["ac"] > 0]
    if len(wk_totals) == 0:
        return {"n": int(len(fa)), "fa": 0, "fa_signed": 0, "bias": 0, "hit": 0, "n_weeks": 0}
    fa_list = (1 - (wk_totals["fc"] - wk_totals["ac"]).abs() / wk_totals["ac"]).clip(lower=0) * 100
    fa_signed_list = (wk_totals["fc"] / wk_totals["ac"]) * 100
    bias_list = (wk_totals["fc"] - wk_totals["ac"]) / wk_totals["ac"] * 100
    return {
        "n":         int(len(fa)),
        "fa":        float(fa_list.mean()),
        "fa_signed": float(fa_signed_list.mean()),
        "bias":      float(bias_list.mean()),
        "hit":       float(fa["hit"].mean() * 100),
        "n_weeks":   int(len(wk_totals)),
    }


def new_method_year_week(fa: pd.DataFrame) -> dict:
    """Same as new_method but groups by (year, week). What our backend
    actually does. Should equal new_method when data is single-year."""
    if len(fa) == 0:
        return {"n": 0, "fa": 0, "fa_signed": 0, "bias": 0, "hit": 0, "n_weeks": 0}
    wk_totals = fa.groupby(["year", "week"], as_index=False).agg(
        ac=("actual", "sum"), fc=("forecast", "sum"))
    wk_totals = wk_totals[wk_totals["ac"] > 0]
    if len(wk_totals) == 0:
        return {"n": int(len(fa)), "fa": 0, "fa_signed": 0, "bias": 0, "hit": 0, "n_weeks": 0}
    fa_list = (1 - (wk_totals["fc"] - wk_totals["ac"]).abs() / wk_totals["ac"]).clip(lower=0) * 100
    fa_signed_list = (wk_totals["fc"] / wk_totals["ac"]) * 100
    bias_list = (wk_totals["fc"] - wk_totals["ac"]) / wk_totals["ac"] * 100
    return {
        "n":         int(len(fa)),
        "fa":        float(fa_list.mean()),
        "fa_signed": float(fa_signed_list.mean()),
        "bias":      float(bias_list.mean()),
        "hit":       float(fa["hit"].mean() * 100),
        "n_weeks":   int(len(wk_totals)),
    }


def _print(label: str, m: dict) -> None:
    nw = f"  weeks={m.get('n_weeks', '-'):>3}" if 'n_weeks' in m else ""
    print(f"  {label:22s} n={m['n']:>5}  fa={m['fa']:>7.4f}  fa_signed={m['fa_signed']:>7.4f}  bias={m['bias']:>+8.4f}  hit={m['hit']:>7.4f}{nw}")


def main() -> int:
    engine = get_engine()

    for tier_label, tier in [("ALL", None), ("GOLD", "GOLD"), ("SILVER", "SILVER"), ("BRONZE", "BRONZE")]:
        print()
        print(f"=== tier = {tier_label} ===")
        df_raw = _load_rows(engine, tier)
        fa = _enrich(df_raw)
        years = sorted(fa["year"].unique().tolist())
        print(f"  raw rows pulled: {len(df_raw):,}   after `actual>0` filter: {len(fa):,}   years in data: {years}")

        _print("OLD (simple mean)",        old_method(fa))
        _print("NEW (week, app.py)",       new_method(fa))
        _print("NEW (year, week)",         new_method_year_week(fa))

    return 0


if __name__ == "__main__":
    sys.exit(main())