"""Food assortment rationalization — propose 50 SKUs to DELIST to make room
for 50 incoming launches. Output: data/food_kickout_50.xlsx + console summary.

Food = SPORTSKA PREHRANA, PROTEINI, RTD & SNACKS, BIO I SUPERFOODS.
We want the lowest-performing, cash-holding items that make no sense to keep.

Delist score (higher = kick) combines four signals, each as a 0..1 rank
within the food pool, so the ranking is transparent:
  * low margin     : 1 - pctl(regular RUC, 12wk non-promo)   — weak earner
  * not selling now : +1.0 if 0 sales in last 4 weeks         — stalled
  * cash tied      : pctl(stock value at cost)               — locks cash
  * overstocked    : +0.5 if weeks-cover > 26                 — won't sell through
Eligible = active food SKU we actually CARRY (stock on hand or sold in window),
excluding new launches (first sale recent & still selling) so we don't kill a
fresh listing, and excluding anything currently Gold (never kick a top earner).
"""
from __future__ import annotations
import re
import numpy as np
import pandas as pd
from sqlalchemy import text
from backend.models.database import SessionLocal

WF, WT, LF = "2026-03-02", "2026-05-24", "2026-04-27"
FOOD = ("SPORTSKA PREHRANA", "PROTEINI", "RTD & SNACKS", "BIO I SUPERFOODS")
PSEUDO = re.compile(r"^(OST|MKT|CARD|WOO|AMB|USL|MSM|WOLTD|SHM)", re.IGNORECASE)
N_KICK = 50

db = SessionLocal()
# Regular (non-promo) weekly grain → margin, units, weeks, first week
weekly = pd.read_sql(text("""
    WITH tx AS (
        SELECT t.product_id, EXTRACT(isoyear FROM t.transaction_date)::int yr,
               EXTRACT(week FROM t.transaction_date)::int wk,
               t.quantity, t.ruc_eur, t.purchase_value
        FROM erp_transactions t JOIN dim_products p ON p.id=t.product_id
        JOIN dim_categories dc ON dc.id=p.category_id
        WHERE dc.name = ANY(:food) AND t.transaction_date BETWEEN :wf AND :wt
    )
    SELECT tx.product_id, tx.yr*100+tx.wk AS yw,
           SUM(tx.quantity)::float units, SUM(tx.ruc_eur)::float ruc,
           SUM(tx.purchase_value)::float cogs
    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*100+tx.wk
"""), db.bind, params={"food": list(FOOD), "wf": WF, "wt": WT})

last4 = pd.read_sql(text("""
    SELECT t.product_id, SUM(t.quantity)::float last4_units
    FROM erp_transactions t JOIN dim_products p ON p.id=t.product_id
    JOIN dim_categories dc ON dc.id=p.category_id
    WHERE dc.name = ANY(:food) AND t.transaction_date BETWEEN :lf AND :wt
    GROUP BY t.product_id
"""), db.bind, params={"food": list(FOOD), "lf": LF, "wt": WT})

stock = pd.read_sql(text("""
    SELECT s.product_id, SUM(s.stock_qty)::float stock_qty
    FROM erp_stock_current s JOIN dim_products p ON p.id=s.product_id
    JOIN dim_categories dc ON dc.id=p.category_id
    WHERE dc.name = ANY(:food)
    GROUP BY s.product_id
"""), db.bind, params={"food": list(FOOD)})

prod = pd.read_sql(text("""
    SELECT p.id product_id, p.sku, p.name, dc.name AS category, sp.tier cur_tier
    FROM dim_products p JOIN dim_categories dc ON dc.id=p.category_id
    LEFT JOIN sku_planning sp ON sp.product_id=p.id
    WHERE dc.name = ANY(:food) AND p.active = true
"""), db.bind, params={"food": list(FOOD)})
db.close()

# unit cost (COGS-based) for stock valuation
g = weekly.groupby("product_id")
agg = pd.DataFrame({
    "reg_ruc": g["ruc"].sum(), "reg_units": g["units"].sum(),
    "nz_weeks": g["yw"].nunique(), "first_yw": g["yw"].min(),
    "cogs": g["cogs"].sum(),
}).reset_index()
agg["unit_cost"] = (agg["cogs"] / agg["reg_units"].replace(0, np.nan)).fillna(0.0)

df = (prod.merge(agg, on="product_id", how="left")
          .merge(last4, on="product_id", how="left")
          .merge(stock, on="product_id", how="left"))
df = df[~df["sku"].fillna("").str.match(PSEUDO)]
for c in ("reg_ruc", "reg_units", "nz_weeks", "cogs", "unit_cost", "last4_units", "stock_qty"):
    df[c] = df[c].fillna(0.0)

# Fallback unit cost from any transaction cost when a SKU had only promo weeks
df["stock_value"] = df["stock_qty"] * df["unit_cost"]
df["wk_units"] = df["reg_units"] / 12.0
df["weeks_cover"] = np.where(df["wk_units"] > 0, df["stock_qty"] / df["wk_units"],
                             np.where(df["stock_qty"] > 0, 999.0, 0.0))

# New-launch guard (don't kick fresh listings)
all_yw = sorted(weekly["yw"].unique())
launch_cut = all_yw[-6] if len(all_yw) >= 6 else all_yw[0]
df["is_new_launch"] = (df["first_yw"] >= launch_cut) & (df["last4_units"] > 0)

# Eligible: we carry it (stock or recent sales history), not a launch, not Gold
elig = df[((df["stock_qty"] > 0) | (df["reg_units"] > 0))
          & (~df["is_new_launch"])
          & (df["cur_tier"].fillna("") != "01 GOLD")].copy()

# ── Delist score ─────────────────────────────────────────────────────────────
def pctl(s):  # 0..1 rank
    return s.rank(pct=True)
elig["s_lowmargin"] = 1 - pctl(elig["reg_ruc"])
elig["s_cash"]      = pctl(elig["stock_value"])
elig["s_stalled"]   = np.where(elig["last4_units"] <= 0, 1.0, 0.0)
elig["s_overstock"] = np.where(elig["weeks_cover"] > 26, 0.5, 0.0)
elig["kick_score"]  = elig["s_lowmargin"] + elig["s_cash"] + elig["s_stalled"] + elig["s_overstock"]

def reason(r):
    rs = []
    if r["last4_units"] <= 0: rs.append("no sales last 4 wks")
    if r["reg_ruc"] <= 0:     rs.append("zero/negative margin")
    elif r["s_lowmargin"] > 0.8: rs.append("bottom-20% margin")
    if r["weeks_cover"] > 26 and r["stock_qty"] > 0: rs.append(f"{r['weeks_cover']:.0f}wk overstock")
    if r["stock_value"] > 0 and r["s_cash"] > 0.8: rs.append("high cash tied")
    return "; ".join(rs) or "low performer"
elig["reason"] = elig.apply(reason, axis=1)

kick = elig.sort_values("kick_score", ascending=False).head(N_KICK).copy()

cash_freed = kick["stock_value"].sum()
margin_lost = kick["reg_ruc"].clip(lower=0).sum()
print(f"Food pool eligible: {len(elig)}  (of {len(df)} active food SKUs)")
print(f"Proposing to delist {len(kick)}.")
print(f"  cash freed (stock at cost):     EUR {cash_freed:,.0f}")
print(f"  quarterly regular margin lost:  EUR {margin_lost:,.0f}")
print(f"  of the 50: {int((kick['last4_units']<=0).sum())} have no sales in 4 wks; "
      f"{int((kick['reg_ruc']<=0).sum())} zero/neg margin; "
      f"{int((kick['weeks_cover']>26).sum())} overstocked >6mo")
print("  current tier of the 50:", kick["cur_tier"].fillna("(untiered)").value_counts().to_dict())
print("  by category:", kick["category"].value_counts().to_dict())

out = kick[["sku", "name", "category", "cur_tier", "reg_ruc", "reg_units",
            "last4_units", "stock_qty", "stock_value", "weeks_cover",
            "kick_score", "reason"]].copy()
out = out.rename(columns={"cur_tier": "current_tier", "reg_ruc": "regular_ruc_12wk_eur",
                          "reg_units": "regular_units_12wk", "stock_value": "stock_value_cost_eur"})
for c in ("regular_ruc_12wk_eur", "stock_value_cost_eur", "weeks_cover", "kick_score"):
    out[c] = out[c].round(2)
path = "data/food_kickout_50.xlsx"
with pd.ExcelWriter(path, engine="openpyxl") as xl:
    out.to_excel(xl, sheet_name="Delist 50", index=False)
print(f"Wrote {path}")