"""GADGETI category deep-dive → Markdown (data/gadget_analysis.md).

Self-contained doc to paste into a Claude chat. Covers performance, channel
split, NEW-article handling, overstock, dead/dormant cash, watches, a PROMO
effectiveness analysis, and how to forecast the category.

Window = 2026-03-02..2026-05-24 (12 ISO weeks, W10-W21).
* "Not selling" / dead uses an 8-WEEK recency lens (no sale since W14, 03-30).
* "New article" = first sale in the last 6 weeks (W16+) — recently introduced.
* Stock: erp_stock_current (store_id 1 = central WH; 33/34/35 = stores).
* Unit cost = COGS-based (sum purchase_value / sum qty).
"""
from __future__ import annotations
import datetime as dt
import numpy as np
import pandas as pd
from sqlalchemy import text
from backend.models.database import SessionLocal

WF, WT = "2026-03-02", "2026-05-24"
L8F = "2026-03-30"          # last 8 weeks (W14..W21) — recency lens for "dead"
NEW_CUT_YW = 202616        # first sale in W16+ ⇒ recently introduced
WATCH_RE = r"(garmin|fenix|forerunner|venu|instinct|vivoactive|vívoactive|epix|marq|lily|enduro|polar|suunto|coros|amazfit|whoop|apple watch|smartwatch|watch|huawei watch|galaxy watch)"
CAT = "GADGETI"

db = SessionLocal()
# Weekly grain WITH promo flag (one row per product×week)
wk = pd.read_sql(text(f"""
    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.tax_base, 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=:cat AND t.transaction_date BETWEEN :wf AND :wt
    )
    SELECT tx.product_id, tx.yr*100+tx.wk AS yw,
           BOOL_OR(COALESCE(pw.is_erp_promo,FALSE)) AS on_promo,
           SUM(tx.quantity)::float units, SUM(tx.tax_base)::float revenue,
           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
    GROUP BY tx.product_id, tx.yr*100+tx.wk
"""), db.bind, params={"cat": CAT, "wf": WF, "wt": WT})

# Channel grain (for web vs store split)
sales_ch = pd.read_sql(text(f"""
    SELECT t.product_id, cm.channel, SUM(t.quantity)::float units, SUM(t.tax_base)::float revenue
    FROM erp_transactions t JOIN dim_products p ON p.id=t.product_id
    JOIN dim_categories dc ON dc.id=p.category_id
    LEFT JOIN lookup_channel_map cm ON cm.id=t.channel_map_id
    WHERE dc.name=:cat AND t.transaction_date BETWEEN :wf AND :wt
    GROUP BY t.product_id, cm.channel
"""), db.bind, params={"cat": CAT, "wf": WF, "wt": WT})

monthly = pd.read_sql(text(f"""
    SELECT date_trunc('month', t.transaction_date)::date mon,
           SUM(t.quantity)::float units, SUM(t.tax_base)::float revenue, SUM(t.ruc_eur)::float ruc
    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=:cat AND t.transaction_date BETWEEN :wf AND :wt GROUP BY 1 ORDER BY 1
"""), db.bind, params={"cat": CAT, "wf": WF, "wt": WT})

last8 = pd.read_sql(text(f"""
    SELECT t.product_id, SUM(t.quantity)::float l8_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=:cat AND t.transaction_date BETWEEN :l8 AND :wt GROUP BY t.product_id
"""), db.bind, params={"cat": CAT, "l8": L8F, "wt": WT})

stock = pd.read_sql(text(f"""
    SELECT s.product_id,
           SUM(CASE WHEN s.store_id=1  THEN s.stock_qty ELSE 0 END)::float wh_qty,
           SUM(CASE WHEN s.store_id<>1 THEN s.stock_qty ELSE 0 END)::float store_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=:cat GROUP BY s.product_id
"""), db.bind, params={"cat": CAT})

prod = pd.read_sql(text(f"""
    SELECT p.id product_id, p.sku, p.name, 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=:cat AND p.active=true
"""), db.bind, params={"cat": CAT})

# Latest landed cost per SKU (values never-sold stock that has no COGS history)
costs = pd.read_sql(text(f"""
    SELECT DISTINCT ON (ec.product_id) ec.product_id, ec.cost_price::float AS cost_price
    FROM erp_costs ec JOIN dim_products p ON p.id=ec.product_id
    JOIN dim_categories dc ON dc.id=p.category_id
    WHERE dc.name=:cat ORDER BY ec.product_id, ec.valid_from DESC NULLS LAST
"""), db.bind, params={"cat": CAT})
db.close()

# ── Per-SKU aggregates ───────────────────────────────────────────────────────
g = wk.groupby("product_id")
agg = pd.DataFrame({
    "units": g["units"].sum(), "revenue": g["revenue"].sum(),
    "ruc": g["ruc"].sum(), "cogs": g["cogs"].sum(),
    "first_yw": g["yw"].min(), "nz_weeks": g["yw"].nunique(),
}).reset_index()
agg["cogs_unit_cost"] = (agg["cogs"] / agg["units"].replace(0, np.nan))
df = (prod.merge(agg, on="product_id", how="left")
          .merge(last8, on="product_id", how="left")
          .merge(stock, on="product_id", how="left")
          .merge(costs, on="product_id", how="left"))
# Unit cost: prefer the cost ledger (covers never-sold stock), else COGS-derived.
df["unit_cost"] = df["cost_price"].fillna(df["cogs_unit_cost"]).fillna(0.0)
for c in ("units","revenue","ruc","cogs","unit_cost","l8_units","wh_qty","store_qty","nz_weeks"):
    df[c] = df[c].fillna(0.0)
df["sold_in_window"] = df["units"] != 0
df["stock_qty"] = df["wh_qty"] + df["store_qty"]
df["stock_value"] = df["stock_qty"] * df["unit_cost"]
df["wk_units"] = df["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))
df["is_watch"] = df["name"].fillna("").str.lower().str.contains(WATCH_RE, regex=True)
df["is_new"] = df["sold_in_window"] & (df["first_yw"] >= NEW_CUT_YW)
# Buckets
df["never_sold_stocked"] = (~df["sold_in_window"]) & (df["stock_qty"] > 0)
df["dead8"] = df["sold_in_window"] & (df["l8_units"] <= 0) & (df["stock_qty"] > 0) & (~df["is_new"])

def ch(metric):
    p = sales_ch.pivot_table(index="product_id", columns="channel", values=metric, aggfunc="sum", fill_value=0.0)
    for c in ("retail","webshop","wholesale"):
        if c not in p: p[c]=0.0
    return p[["retail","webshop","wholesale"]]
rev_ch, unit_ch = ch("revenue").sum(), ch("units").sum()

# ── Promo effectiveness ──────────────────────────────────────────────────────
promo_tot = wk.groupby("on_promo").agg(units=("units","sum"), revenue=("revenue","sum"),
                                       ruc=("ruc","sum"), weeks=("yw","count")).reset_index()
# within-SKU: weekly units on promo vs non-promo weeks, for SKUs with both
wsku = wk.groupby(["product_id","on_promo"]).agg(u=("units","sum"), nwk=("yw","nunique")).reset_index()
piv = wsku.pivot_table(index="product_id", columns="on_promo", values=["u","nwk"], fill_value=0)
both = piv[(piv[("nwk",True)]>0) & (piv[("nwk",False)]>0)].copy()
both["wu_promo"]    = both[("u",True)]  / both[("nwk",True)]
both["wu_nonpromo"] = both[("u",False)] / both[("nwk",False)]
both["uplift"] = both["wu_promo"] / both["wu_nonpromo"].replace(0,np.nan)
med_uplift = both["uplift"].replace([np.inf,-np.inf],np.nan).median()
n_both = len(both)

def eur(x): return f"€{x:,.0f}"
def pct(x): return f"{x:.1f}%"
def row_promo(flag):
    r = promo_tot[promo_tot["on_promo"]==flag]
    if r.empty: return (0,0,0)
    r=r.iloc[0]; return (r["units"], r["revenue"], r["ruc"])
pu,prv,prc = row_promo(True); nu,nrv,nrc = row_promo(False)

# ── Markdown ─────────────────────────────────────────────────────────────────
L = []
A = L.append
A("# GADGETI — category deep-dive & forecasting plan\n")
A(f"_Window {WF} → {WT} (12 ISO weeks). 'Not selling' = no sale in the last 8 weeks "
  f"(since {L8F}). 'New' = first sale in W16+. Generated {dt.date.today()}._\n")

n_sku=len(df); n_sold=int(df["sold_in_window"].sum()); n_new=int(df["is_new"].sum())
n_never=int(df["never_sold_stocked"].sum())
tot_rev,tot_ruc,tot_units=df["revenue"].sum(),df["ruc"].sum(),df["units"].sum()
tot_stock_val=df["stock_value"].sum()
A("## 1. Headline\n")
A(f"- **{n_sku} active SKUs**; {n_sold} sold in the window, **{n_new} newly introduced** (first sale in last 6 wks), **{n_never} stocked but never sold in the window**.")
A(f"- Sales: **{eur(tot_rev)} revenue**, **{eur(tot_ruc)} margin (RUC)** = **{pct(tot_ruc/tot_rev*100 if tot_rev else 0)}** blended, {tot_units:,.0f} units (~{tot_units/12:.0f}/week across the whole range).")
A(f"- **Stock: {df['stock_qty'].sum():,.0f} units, ~{eur(tot_stock_val)} at cost** — vs {eur(tot_rev)} revenue/quarter (**~{tot_stock_val/(df['cogs'].sum()/3) if df['cogs'].sum() else 0:.1f} months of cover**).\n")

A("## 2. Channel split — web vs stores\n")
A("| Channel | Revenue | % rev | Units | % units |")
A("|---|--:|--:|--:|--:|")
for c in ("retail","webshop","wholesale"):
    A(f"| {c.capitalize()} | {eur(rev_ch[c])} | {pct(rev_ch[c]/rev_ch.sum()*100 if rev_ch.sum() else 0)} | {unit_ch[c]:,.0f} | {pct(unit_ch[c]/unit_ch.sum()*100 if unit_ch.sum() else 0)} |")
b2c=rev_ch["webshop"]+rev_ch["retail"]
A(f"\n**Of B2C: web {pct(rev_ch['webshop']/b2c*100 if b2c else 0)} / stores {pct(rev_ch['retail']/b2c*100 if b2c else 0)}** of revenue. Wholesale is marginal.\n")

A("## 3. Monthly trend\n")
A("| Month | Units | Revenue | Margin |\n|---|--:|--:|--:|")
for _,r in monthly.iterrows():
    A(f"| {r['mon']:%b %Y} | {r['units']:,.0f} | {eur(r['revenue'])} | {eur(r['ruc'])} |")
A("")

A("## 4. New / recently introduced gadgets\n")
new = df[df["is_new"]].sort_values("revenue", ascending=False)
A(f"- **{n_new} SKUs first sold in the last 6 weeks** — ramping, *not* to be read as dead. "
  f"Early traction: {new['units'].sum():,.0f} units, {eur(new['revenue'].sum())} revenue, {eur(new['stock_value'].sum())} stock at cost.")
A("\nTop new intros by early revenue:\n")
A("| SKU | Name | Units | Revenue | Stock | €cost stock |\n|---|---|--:|--:|--:|--:|")
for _,r in new.head(12).iterrows():
    A(f"| {r['sku']} | {str(r['name'])[:44]} | {r['units']:,.0f} | {eur(r['revenue'])} | {r['stock_qty']:,.0f} | {eur(r['stock_value'])} |")
A("")

A("## 5. Stock & overstock (excludes new intros)\n")
wh_val=(df['wh_qty']*df['unit_cost']).sum(); st_val=(df['store_qty']*df['unit_cost']).sum()
A(f"- WH: **{df['wh_qty'].sum():,.0f} units (~{eur(wh_val)})**; stores: **{df['store_qty'].sum():,.0f} units (~{eur(st_val)})** — most stock sits in stores.")
over = df[(df["weeks_cover"]>26)&(df["stock_qty"]>0)&(~df["is_new"])&(df["sold_in_window"])].sort_values("stock_value",ascending=False)
A(f"- **{len(over)} established SKUs carry >26 weeks cover** — {eur(over['stock_value'].sum())} tied.\n")
A("| SKU | Name | Stock | Weeks cover | €cost tied | 8wk units |\n|---|---|--:|--:|--:|--:|")
for _,r in over.head(12).iterrows():
    wc="∞" if r["weeks_cover"]>=999 else f"{r['weeks_cover']:.0f}"
    A(f"| {r['sku']} | {str(r['name'])[:42]} | {r['stock_qty']:,.0f} | {wc} | {eur(r['stock_value'])} | {r['l8_units']:,.0f} |")
A("")

A("## 6. Dead / dying — sold earlier, nothing in 8 weeks\n")
dead = df[df["dead8"]].sort_values("stock_value",ascending=False)
A(f"- **{len(dead)} SKUs sold earlier in the window but had ZERO sales in the last 8 weeks**, still holding **{eur(dead['stock_value'].sum())}** at cost. This is the genuine dead tail (new intros excluded).\n")
A("| SKU | Name | Stock | €cost tied | Units(12wk) | First sold |\n|---|---|--:|--:|--:|--:|")
for _,r in dead.head(15).iterrows():
    A(f"| {r['sku']} | {str(r['name'])[:42]} | {r['stock_qty']:,.0f} | {eur(r['stock_value'])} | {r['units']:,.0f} | {int(r['first_yw'])} |")
A("")

A("## 7. Stocked but never sold in the window — new arrivals OR dormant\n")
never = df[df["never_sold_stocked"]].sort_values("stock_value",ascending=False)
A(f"- **{n_never} SKUs hold stock but recorded no sale in 12 weeks — {eur(never['stock_value'].sum())} at cost.** "
  f"These are *either* brand-new arrivals not yet sold *or* dormant lines. They need a CM eyeball "
  f"(no sales signal can't tell them apart). Sorted by cash tied:\n")
A("| SKU | Name | Stock | €cost tied |\n|---|---|--:|--:|")
for _,r in never.head(15).iterrows():
    A(f"| {r['sku']} | {str(r['name'])[:48]} | {r['stock_qty']:,.0f} | {eur(r['stock_value'])} |")
A("")

A("## 8. Watches not selling (8-week lens, new intros excluded)\n")
w = df[df["is_watch"]]
wdead = w[((w["dead8"]) | (w["never_sold_stocked"])) & (~w["is_new"])].sort_values("stock_value",ascending=False)
A(f"- Watch-type SKUs: **{int(w['is_watch'].sum())}**; with stock but no recent/any sell-through: **{len(wdead)}** (~{eur(wdead['stock_value'].sum())} tied).\n")
A("| SKU | Name | Stock | €cost tied | Units(12wk) | Weeks cover |\n|---|---|--:|--:|--:|--:|")
for _,r in wdead.head(15).iterrows():
    wc="∞" if r["weeks_cover"]>=999 else f"{r['weeks_cover']:.0f}"
    A(f"| {r['sku']} | {str(r['name'])[:42]} | {r['stock_qty']:,.0f} | {eur(r['stock_value'])} | {r['units']:,.0f} | {wc} |")
A("")

A("## 9. Does promo help gadgets?\n")
A("| | Units | % units | Revenue | Margin | Margin % |\n|---|--:|--:|--:|--:|--:|")
A(f"| Off promo | {nu:,.0f} | {pct(nu/(nu+pu)*100 if (nu+pu) else 0)} | {eur(nrv)} | {eur(nrc)} | {pct(nrc/nrv*100 if nrv else 0)} |")
A(f"| On promo  | {pu:,.0f} | {pct(pu/(nu+pu)*100 if (nu+pu) else 0)} | {eur(prv)} | {eur(prc)} | {pct(prc/prv*100 if prv else 0)} |")
A(f"\n- Only **{pct(pu/(nu+pu)*100 if (nu+pu) else 0)} of units** sell on promo, and promo **margin collapses to {pct(prc/prv*100 if prv else 0)}** (vs {pct(nrc/nrv*100 if nrv else 0)} off promo).")
A(f"- Within the **{n_both} SKUs that ran both promo and non-promo weeks**, the median weekly-units uplift on promo is **{med_uplift:.2f}×**.")
verdict = ("barely moves volume while roughly halving margin — promo is **margin-dilutive, not a volume lever** for gadgets"
           if (med_uplift is not None and med_uplift < 1.5) else "drives meaningful extra volume")
A(f"- **Verdict: promo {verdict}.** Watches/devices are considered, brand-priced (often MAP-restricted) purchases — discounting gives away margin on units that would largely sell anyway. Don't lean on promo to clear gadget stock; use it surgically (true EOL clearance only).\n")

A("## 10. Top performers (for contrast)\n")
top = df.sort_values("ruc",ascending=False).head(12)
rev_by = ch("revenue")
A("| SKU | Name | Margin | Revenue | Units | Web% |\n|---|---|--:|--:|--:|--:|")
for _,r in top.iterrows():
    rr = rev_by.loc[r["product_id"]] if r["product_id"] in rev_by.index else None
    wp = (rr["webshop"]/rr.sum()*100) if rr is not None and rr.sum() else 0
    A(f"| {r['sku']} | {str(r['name'])[:42]} | {eur(r['ruc'])} | {eur(r['revenue'])} | {r['units']:,.0f} | {pct(wp)} |")
A("")

A("## 11. How to forecast gadgets (and why not like food)\n")
A("Food is continuous, repeat-purchase demand → statistical time-series (the current engine) works. "
  "Gadgets are the opposite, and the numbers above prove it: ~**2–3 units/SKU per quarter**, "
  f"**{pct(tot_ruc/tot_rev*100 if tot_rev else 0)} margin**, **~{tot_stock_val/(df['cogs'].sum()/3) if df['cogs'].sum() else 0:.0f} months of stock**, and a long dead/dormant tail. "
  "Running statistical TS on 2-units-a-quarter SKUs just projects the recent level flat and keeps 'needing' stock "
  "long after the successor model has landed — that is the dead pile.\n")
A("**Approach — lifecycle planning, not history-extrapolation:**\n")
A("1. **Launch-curve from an analog.** Forecast a new watch off the model it replaces, scaled by an expected uplift, with an explicit introduction → growth → maturity → **planned decay** curve. New SKUs have no history to fit.")
A("2. **End-of-life / cannibalisation rule.** When a successor launches (Fenix 7 → 8 → 8 Pro), flag the predecessor EOL → **reorder stops, sell down stock**. This single rule is what prevents the dead pile.")
A("3. **Buy-to-plan against ATP, not auto-replenish.** High value, low velocity, supplier MOQ/allocation anyway → forecast coarsely at *model-family* level in € + units, then place small, frequent, deliberate buys.")
A("4. **Web vs store separately.** ~46% web / 54% store: web demand is centralised & more forecastable; stores need a **presentation minimum** (display availability), not deep stock.")
A("5. **Seasonal overlay.** Strong Q4 gifting; 12 weeks can't learn an annual cycle → apply a **category seasonal index**, not per-SKU seasonality.")
A("6. **Promo:** per §9, not a volume lever — exclude promo uplift from gadget forecasts; use discounting only for deliberate EOL clearance.")
A("7. **Measure differently.** Unit/weekly FA is meaningless at this velocity — track **sell-through %, weeks-of-cover, dead-stock €** at family/monthly grain. Drop the food 2×/0.5× sanity bounds; the binding constraints are MOQ, allocation and EOL run-off.\n")
A("**Build steps:** add a `demand_profile` (lifecycle) flag for the category, a **lifecycle/analog + EOL table**, exclude gadgets from the food auto-reorder engine, and stand up the KPIs above (dead €, cover, sell-through, web/store) as the standing gadget dashboard.\n")

path="data/gadget_analysis.md"
open(path,"w",encoding="utf-8").write("\n".join(L))
print(f"Wrote {path} ({len(L)} lines)")
print(f"SKUs {n_sku} | sold {n_sold} | new {n_new} | never-sold stocked {n_never}")
print(f"dead8 {int(df['dead8'].sum())} ({eur(df[df['dead8']]['stock_value'].sum())}) | overstock(estab) {len(over)} | watches dead {len(wdead)}")
print(f"promo: off {nu:.0f}u@{pct(nrc/nrv*100 if nrv else 0)} vs on {pu:.0f}u@{pct(prc/prv*100 if prv else 0)} | median uplift {med_uplift:.2f}x ({n_both} SKUs)")