"""ABC-XYZ tier proposal — Gold/Silver/Bronze, max 500, on regular (non-promo)
sales over the last ~3 months (2026 ISO weeks 10-21, the full erp_transactions
window).

Method
------
* Period: transaction_date 2026-03-02 .. 2026-05-24 (12 ISO weeks, W10-W21).
* Regular only: a (product, ISO-week) is dropped when erp_promo_weeks flags it
  is_erp_promo — so promo-inflated weeks don't drive importance.
* Value metric: RUC (gross margin EUR). Gold/Silver/Bronze is fundamentally an
  importance ranking; margin is what the business actually earns, and matches
  the "look at RUC, not revenue" steer. Revenue + units carried for context.
* Scope: every real SKU with positive regular margin (whole ERP universe, not
  just the current list) — that's how new winners surface and dead ones drop.
* ABC (within the top 500 by RUC): cumulative-margin Pareto —
  <=80% Gold, 80-95% Silver, 95-100% Bronze.
* XYZ (predictability): CV of weekly regular units across all 12 weeks
  (missing week = 0 demand). X CV<=0.5, Y 0.5-1.0, Z >1.0. Reported alongside;
  it does NOT override the value tier (kept as a planning attribute, per the
  "tier stays ABC Gold/Silver/Bronze" rule).
"""
from __future__ import annotations
import re
import numpy as np
import pandas as pd
from sqlalchemy import text
from backend.models.database import SessionLocal

WIN_FROM, WIN_TO = "2026-03-02", "2026-05-24"
MAX_TIERED = 500
MIN_REG_WEEKS = 6          # kick SKUs with < this many regular (non-promo) weeks
LAST4_FROM = "2026-04-27"  # W18 start — "last 4 weeks" = W18..W21
PSEUDO = re.compile(r"^(OST|MKT|CARD|WOO|AMB|USL|MSM|WOLTD)", re.IGNORECASE)

db = SessionLocal()

# ── 1. Non-promo weekly grain (product × ISO week) ───────────────────────────
weekly = pd.read_sql(text(f"""
    WITH tx AS (
        SELECT t.product_id,
               EXTRACT(isoyear FROM t.transaction_date)::int AS yr,
               EXTRACT(week    FROM t.transaction_date)::int AS wk,
               t.quantity, t.ruc_eur, t.tax_base
        FROM erp_transactions t
        WHERE t.transaction_date BETWEEN :wf AND :wt
    )
    SELECT tx.product_id, tx.yr, tx.wk,
           SUM(tx.quantity)::float AS units,
           SUM(tx.ruc_eur)::float  AS ruc,
           SUM(tx.tax_base)::float AS revenue
    FROM tx
    LEFT JOIN erp_promo_weeks pw
           ON pw.product_id = tx.product_id AND pw.year = tx.yr AND pw.week = tx.wk
    WHERE COALESCE(pw.is_erp_promo, FALSE) = FALSE
    GROUP BY tx.product_id, tx.yr, tx.wk
"""), db.bind, params={"wf": WIN_FROM, "wt": WIN_TO})

# Product meta + current tier
meta = pd.read_sql(text("""
    SELECT p.id AS product_id, p.sku, p.name,
           sp.tier AS cur_tier
    FROM dim_products p
    LEFT JOIN sku_planning sp ON sp.product_id = p.id
"""), db.bind)

# Last-4-week activity — ANY sale (incl. promo), to detect listed-out SKUs.
# Promo runs full calendar months, so a regular-only check would wrongly
# flag an active promo item as delisted; total units is the right signal.
last4 = pd.read_sql(text(f"""
    SELECT product_id, SUM(quantity)::float AS last4_units
    FROM erp_transactions
    WHERE transaction_date BETWEEN :lf AND :wt
    GROUP BY product_id
"""), db.bind, params={"lf": LAST4_FROM, "wt": WIN_TO})
db.close()

weekly = weekly.merge(meta, on="product_id", how="left")
weekly = weekly[~weekly["sku"].fillna("").str.match(PSEUDO)]
# Per user: exclude Shieldmixer (SHM*) entirely — not wanted in the tier.
weekly = weekly[~weekly["sku"].fillna("").str.upper().str.startswith("SHM")]

# Full week axis (W10..W21 2026) so absent weeks count as zero demand for CV.
all_weeks = sorted(set(zip(weekly["yr"], weekly["wk"])))
n_weeks = len(all_weeks)

# ── 2. Per-SKU aggregates ────────────────────────────────────────────────────
weekly["yw"] = weekly["yr"] * 100 + weekly["wk"]
g = weekly.groupby("product_id")
agg = pd.DataFrame({
    "ruc":      g["ruc"].sum(),
    "revenue":  g["revenue"].sum(),
    "units":    g["units"].sum(),
    "nz_weeks": g["wk"].nunique(),
    "first_yw": g["yw"].min(),    # first regular-sale week (launch detection)
}).reset_index()
agg = agg.merge(meta, on="product_id", how="left")

# Weekly-units matrix for CV (fill missing weeks with 0, floor returns at 0).
pivot = (weekly.assign(yw=lambda d: d["yr"] * 100 + d["wk"])
         .pivot_table(index="product_id", columns="yw", values="units",
                      aggfunc="sum", fill_value=0.0))
pivot = pivot.clip(lower=0.0)
mean_u = pivot.mean(axis=1)
std_u  = pivot.std(axis=1, ddof=0)
cv = (std_u / mean_u).replace([np.inf, -np.inf], np.nan)
agg = agg.merge(cv.rename("cv").reset_index(), on="product_id", how="left")

def xyz(c):
    if pd.isna(c):      return "Z"
    if c <= 0.5:        return "X"
    if c <= 1.0:        return "Y"
    return "Z"
agg["xyz"] = agg["cv"].apply(xyz)

# Last-4-week activity (0 when the SKU had no transactions at all).
agg = agg.merge(last4, on="product_id", how="left")
agg["last4_units"] = agg["last4_units"].fillna(0.0)

# ── 3. Hygiene filters, then ABC within the top-500 by regular RUC ───────────
# Rule 1: drop SKUs with < MIN_REG_WEEKS regular non-zero weeks (not steady).
# Rule 2: drop SKUs with no sales (any kind) in the last 4 weeks (likely
#         listed out). Both are recorded on each row so the kicks are auditable.
# New launch = first regular sale within the last MIN_REG_WEEKS weeks AND still
# selling now. It literally can't have MIN_REG_WEEKS weeks of history yet, so
# the consistency rule shouldn't drop it.
launch_cutoff_yw = all_weeks[-MIN_REG_WEEKS][0] * 100 + all_weeks[-MIN_REG_WEEKS][1]
agg["is_new_launch"] = (agg["first_yw"] >= launch_cutoff_yw) & (agg["last4_units"] > 0)
agg["fail_weeks"]  = (agg["nz_weeks"] < MIN_REG_WEEKS) & (~agg["is_new_launch"])
agg["fail_recent"] = agg["last4_units"] <= 0
def _excl_reason(r):
    reasons = []
    if r["fail_weeks"]:  reasons.append(f"<{MIN_REG_WEEKS} regular weeks ({int(r['nz_weeks'])})")
    if r["fail_recent"]: reasons.append("no sales last 4 weeks")
    return "; ".join(reasons)
agg["exclude_reason"] = agg.apply(_excl_reason, axis=1)

pos = agg[agg["ruc"] > 0].copy()
elig = pos[~pos["fail_weeks"] & ~pos["fail_recent"]].sort_values("ruc", ascending=False).reset_index(drop=True)
top = elig.head(MAX_TIERED).copy()
top["cum_ruc_pct"] = top["ruc"].cumsum() / top["ruc"].sum() * 100

def abc(p):
    if p <= 80:  return "01 GOLD"
    if p <= 95:  return "02 SILVER"
    return "03 BRONZE"
top["new_tier"] = top["cum_ruc_pct"].apply(abc)

# ── 4. Output / comparison ───────────────────────────────────────────────────
total_regular_ruc = agg["ruc"].clip(lower=0).sum()
print(f"Window: {WIN_FROM}..{WIN_TO}  ({n_weeks} ISO weeks: {all_weeks[0]}..{all_weeks[-1]})")
print(f"SKUs with ANY regular sale: {len(agg)}  |  with positive regular margin: {len(pos)}")
print(f"Total regular margin (RUC) in window: EUR {total_regular_ruc:,.0f}")
print()
print("=== Hygiene filters (applied to the positive-margin pool) ===")
print(f"  failed <{MIN_REG_WEEKS} regular weeks: {int(pos['fail_weeks'].sum())}")
print(f"  failed no-sales-last-4-weeks:  {int(pos['fail_recent'].sum())}")
print(f"  failed either:                 {int((pos['fail_weeks'] | pos['fail_recent']).sum())}")
print(f"  new launches exempted from 6-wk rule: {int(agg['is_new_launch'].sum())} "
      f"(first sale >= {launch_cutoff_yw} & selling now)")
print(f"  eligible after filters:        {len(elig)}  -> tiering top {min(MAX_TIERED, len(elig))}")
# Casualties: SKUs that rank in the top-500 by RUC but were filtered out.
ranked = pos.sort_values("ruc", ascending=False).reset_index(drop=True)
would_make = set(ranked.head(MAX_TIERED)["product_id"])
casualties = pos[(pos["product_id"].isin(would_make)) & (pos["fail_weeks"] | pos["fail_recent"])]
print(f"  of which would otherwise have made the top {MAX_TIERED}: {len(casualties)}")
print()
print("=== PROPOSED tier counts (top 500 by regular RUC) ===")
print(top["new_tier"].value_counts().sort_index().to_string())
print(f"  total tiered: {len(top)}")
print(f"  margin captured by these {len(top)} SKUs: "
      f"EUR {top['ruc'].sum():,.0f} ({top['ruc'].sum()/total_regular_ruc*100:.1f}% of regular RUC)")
print()
print("=== ABC × XYZ matrix (proposed, SKU counts) ===")
mtx = pd.crosstab(top["new_tier"], top["xyz"])
for col in ("X", "Y", "Z"):
    if col not in mtx: mtx[col] = 0
print(mtx[["X", "Y", "Z"]].to_string())
print()

# Compare to current
cur = agg.copy()
cur["is_current"] = cur["cur_tier"].notna() & (cur["cur_tier"] != "")
n_current = int(cur["is_current"].sum())
cur_ids = set(cur.loc[cur["is_current"], "product_id"])
new_ids = set(top["product_id"])
print("=== CURRENT vs PROPOSED ===")
print(f"Current tiered SKUs: {n_current}  |  Proposed: {len(new_ids)}")
print(f"  kept (in both):   {len(cur_ids & new_ids)}")
print(f"  dropped (current, not proposed): {len(cur_ids - new_ids)}")
print(f"  added (new, not current):        {len(new_ids - cur_ids)}")

# Dead weight: current-tier SKUs with ~zero regular sales in window
cur_meta = agg[agg["product_id"].isin(cur_ids)]
dead = cur_meta[(cur_meta["ruc"].fillna(0) <= 0)]
# current SKUs with NO regular row at all
cur_no_sales = cur_ids - set(agg["product_id"])
print(f"  current SKUs with <=0 regular margin in window: {len(dead) + len(cur_no_sales)}")

# How much regular RUC does the CURRENT set capture vs proposed?
cur_ruc = agg.loc[agg["product_id"].isin(cur_ids), "ruc"].clip(lower=0).sum()
print(f"  regular RUC captured by CURRENT set:  EUR {cur_ruc:,.0f} ({cur_ruc/total_regular_ruc*100:.1f}%)")
print(f"  regular RUC captured by PROPOSED set: EUR {top['ruc'].sum():,.0f} ({top['ruc'].sum()/total_regular_ruc*100:.1f}%)")

# Promo-dependent current Golds: high current tier but low regular margin
merged = top.merge(agg[["product_id", "cur_tier"]], on="product_id", how="outer", suffixes=("", "_y"))

# ── 5. Excel proposal ────────────────────────────────────────────────────────
out = top[["sku", "name", "cur_tier", "new_tier", "xyz", "cv",
           "ruc", "revenue", "units", "nz_weeks", "is_new_launch", "cum_ruc_pct"]].copy()
out = out.rename(columns={"cur_tier": "current_tier", "new_tier": "proposed_tier",
                          "ruc": "regular_ruc_eur", "revenue": "regular_revenue_eur",
                          "units": "regular_units", "is_new_launch": "new_launch"})
out["moved"] = np.where(out["current_tier"].isna(), "NEW",
                np.where(out["current_tier"] == out["proposed_tier"], "same",
                         out["current_tier"].astype(str) + " -> " + out["proposed_tier"]))
for c in ("regular_ruc_eur", "regular_revenue_eur", "regular_units", "cv", "cum_ruc_pct"):
    out[c] = out[c].round(2)

# Dropped current SKUs (so the user sees what leaves)
dropped = agg[agg["product_id"].isin(cur_ids - new_ids)][
    ["sku", "name", "cur_tier", "ruc", "revenue", "units", "nz_weeks", "cv"]].copy()
dropped = dropped.rename(columns={"cur_tier": "current_tier", "ruc": "regular_ruc_eur",
                                  "revenue": "regular_revenue_eur", "units": "regular_units"})
dropped = dropped.sort_values("regular_ruc_eur", ascending=False)
for c in ("regular_ruc_eur", "regular_revenue_eur", "regular_units", "cv"):
    dropped[c] = dropped[c].round(2)

# Excluded-by-filter audit (positive-margin SKUs kicked by a hygiene rule),
# worst-first by the margin we're forgoing.
excl = pos[pos["fail_weeks"] | pos["fail_recent"]][
    ["sku", "name", "cur_tier", "ruc", "revenue", "units", "nz_weeks",
     "last4_units", "cv", "exclude_reason"]].copy()
excl = excl.rename(columns={"cur_tier": "current_tier", "ruc": "regular_ruc_eur",
                            "revenue": "regular_revenue_eur", "units": "regular_units"})
excl = excl.sort_values("regular_ruc_eur", ascending=False)
for c in ("regular_ruc_eur", "regular_revenue_eur", "regular_units", "last4_units", "cv"):
    excl[c] = excl[c].round(2)

path = "data/abc_xyz_tier_proposal.xlsx"
with pd.ExcelWriter(path, engine="openpyxl") as xl:
    out.to_excel(xl, sheet_name="Proposed", index=False)
    dropped.to_excel(xl, sheet_name="Dropped from current", index=False)
    excl.to_excel(xl, sheet_name="Excluded by filter", index=False)
    mtx[["X", "Y", "Z"]].to_excel(xl, sheet_name="ABC x XYZ matrix")
print(f"\nWrote {path}  ({len(out)} proposed, {len(dropped)} dropped, {len(excl)} filter-excluded)")