"""Verify is_promo flag — CSV (sales_clean.is_any_promo) vs Postgres
(erp_promo_weeks.is_erp_promo, the source the new JOIN uses).

Picks 3 GOLD-tier SKUs with overlap data in 2026 W10..W19 (the window
where both sources have rows), then prints a week-by-week side-by-side.

Read-only — no writes, no service restarts.
"""
from __future__ import annotations

import sys
from pathlib import Path

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 main() -> int:
    engine = get_engine()
    overlap_yw = [202610, 202611, 202612, 202613, 202614,
                  202615, 202616, 202617, 202618, 202619]

    # ----- Pick 3 GOLD SKUs that exist in both sources --------------------
    print("Picking 3 GOLD SKUs with data in both sources for 2026 W10..W19...")
    with engine.connect() as conn:
        # Use the SAME join logic the new demand_repo.get_sales_weekly uses,
        # so the picks reflect rows the API would actually return.
        sql = """
            SELECT p.sku, COUNT(*) AS n_weeks, SUM(v.qty_total) AS qty_total
            FROM v_sales_weekly v
            JOIN dim_products p ON v.product_id = p.id
            JOIN sku_planning sp ON sp.product_id = p.id
            WHERE sp.tier ILIKE '%GOLD%'
              AND v.year * 100 + v.week BETWEEN 202610 AND 202619
            GROUP BY p.sku
            HAVING COUNT(*) >= 5
            ORDER BY qty_total DESC
            LIMIT 3
        """
        gold_skus = [r[0] for r in conn.execute(text(sql)).all()]
    print(f"  Picked: {gold_skus}")
    print()

    # ----- OLD source: sales_clean.csv ------------------------------------
    print("Loading sales_clean.csv (OLD path)...")
    sc = pd.read_csv(ROOT / "data" / "sales_clean.csv")
    sc["yw"] = sc["year"].astype(int) * 100 + sc["week"].astype(int)
    sc_subset = sc[(sc["sku"].isin(gold_skus)) & (sc["yw"].isin(overlap_yw))][
        ["sku", "year", "week", "yw", "qty_total", "is_any_promo",
         "is_retail_promo", "is_wholesale_spike", "retail_discount_pct"]
    ].copy()

    # ----- NEW source: erp_promo_weeks via the same join the repo uses ----
    with engine.connect() as conn:
        new_q = """
            WITH
            promo_per_week AS (
                SELECT product_id, year, week, BOOL_OR(is_erp_promo) AS is_promo
                FROM erp_promo_weeks
                GROUP BY product_id, year, week
            )
            SELECT p.sku, v.year, v.week, v.qty_total::float AS qty_total_pg,
                   COALESCE(pw.is_promo, false) AS is_promo_new
            FROM v_sales_weekly v
            JOIN dim_products p ON v.product_id = p.id
            LEFT JOIN promo_per_week pw
                ON pw.product_id = p.id AND pw.year = v.year AND pw.week = v.week
            WHERE p.sku = ANY(:skus)
              AND v.year * 100 + v.week BETWEEN 202610 AND 202619
            ORDER BY p.sku, v.year, v.week
        """
        pg_rows = pd.read_sql(text(new_q), conn, params={"skus": gold_skus})
    pg_rows["yw"] = pg_rows["year"].astype(int) * 100 + pg_rows["week"].astype(int)

    # ----- Merge + print ---------------------------------------------------
    merged = pd.merge(
        sc_subset, pg_rows[["sku", "yw", "qty_total_pg", "is_promo_new"]],
        on=["sku", "yw"], how="outer",
    )
    merged["agreement"] = merged.apply(
        lambda r: "✓" if bool(r.get("is_any_promo", 0)) == bool(r.get("is_promo_new", False)) else "✗",
        axis=1,
    )

    for sku in gold_skus:
        sub = merged[merged["sku"] == sku].sort_values("yw")
        print(f"=== {sku} ===")
        print(f"  {'YW':<7} {'qty(CSV)':>10} {'qty(PG)':>10}   "
              f"{'is_any_promo (CSV)':<22} {'is_promo (PG/ERP)':<22} {'agree':<6} "
              f"{'is_retail_promo':<16} {'discount %':<11}")
        for _, r in sub.iterrows():
            yw = int(r["yw"]) if pd.notna(r.get("yw")) else 0
            qcsv = r.get("qty_total")
            qpg  = r.get("qty_total_pg")
            csv_promo = r.get("is_any_promo")
            pg_promo  = r.get("is_promo_new")
            rp = r.get("is_retail_promo")
            disc = r.get("retail_discount_pct")
            print(
                f"  {yw:<7} "
                f"{('—' if pd.isna(qcsv) else f'{float(qcsv):,.0f}'):>10} "
                f"{('—' if pd.isna(qpg) else f'{float(qpg):,.0f}'):>10}   "
                f"{('—' if pd.isna(csv_promo) else str(int(csv_promo))):<22} "
                f"{('—' if pd.isna(pg_promo) else str(bool(pg_promo))):<22} "
                f"{r['agreement']:<6} "
                f"{('—' if pd.isna(rp) else str(int(rp))):<16} "
                f"{('—' if pd.isna(disc) else f'{float(disc):.1f}%'):<11}"
            )
        print()

    # ----- Disagreement summary -------------------------------------------
    print("=== summary ===")
    n_total = len(merged)
    n_agree = (merged["agreement"] == "✓").sum()
    n_disagree = (merged["agreement"] == "✗").sum()
    print(f"  Total (sku × week) rows compared: {n_total}")
    print(f"  Agreement:    {n_agree}  ({n_agree/max(1,n_total)*100:.1f}%)")
    print(f"  Disagreement: {n_disagree}  ({n_disagree/max(1,n_total)*100:.1f}%)")
    if n_disagree:
        print("  Disagreement breakdown:")
        d = merged[merged["agreement"] == "✗"].copy()
        d["csv_says"] = d["is_any_promo"].fillna(-1).astype(int).map(
            {-1: "missing", 0: "no-promo", 1: "promo"}
        )
        d["pg_says"] = d["is_promo_new"].apply(
            lambda x: "missing" if pd.isna(x) else ("promo" if bool(x) else "no-promo")
        )
        for (cs, pg), grp in d.groupby(["csv_says", "pg_says"]):
            print(f"    CSV={cs!s:>9} · PG={pg!s:>9}  →  {len(grp)} rows")

    return 0


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