"""WEB (webshop) sales report — RUC-led, by country, from the source prodaja xlsx.

WHY source files, not the DB: the per-country prodaja exports in data/prodaja/
carry the country (one file per country) AND the RUC column. The DB load drops
store_id on recent uploads, which buckets sales as '(unknown)'. Reading the
source files gives the true country split with zero '(unknown)'.

Metric: everything leads on RUC (margin €). Revenue/units are secondary context.

Channel: webshop = WSA/WSB/WSC/WSD (B2C). RAC (web orders priced as wholesale)
is excluded — pass --with-rac to include it.

Windows are day-matched to the common coverage of the files (e.g. if the export
ends 16 May, it compares May 1-16 vs Apr 1-16).

USAGE (from repo root):
    python scripts/web_sales_report.py
    python scripts/web_sales_report.py --with-rac

Promo penetration (one sheet) still comes from the Magento coupon log in Postgres
(the only promo signal); it's unit/discount based and labelled as a separate source.
Output: WEB_Sales_RUC_<Mon><Year>.xlsx
"""
from __future__ import annotations

import calendar
import glob
import os
import sys
from datetime import date
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parents[1]))

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

from backend.repositories.upload_repo import parse_excel_file

WEB = {"WSA", "WSB", "WSC", "WSD"}
WITH_RAC = "--with-rac" in sys.argv
if WITH_RAC:
    WEB = WEB | {"RAC"}
PRODAJA_DIR = "data/prodaja"


def _country_of(fname: str) -> str | None:
    f = fname.lower()
    if "cro" in f or "hrv" in f or "_hr" in f:
        return "HR"
    if "slo" in f or "_si" in f or "slv" in f:
        return "SI"
    if "austr" in f or "asutri" in f or "avstri" in f or "_at" in f:
        return "AT"
    return None


def load_web() -> pd.DataFrame:
    frames = []
    found = {}
    for fp in sorted(glob.glob(os.path.join(PRODAJA_DIR, "*.xlsx"))):
        country = _country_of(os.path.basename(fp))
        if country is None:
            print(f"  ! skipping unclassified file: {fp}")
            continue
        parsed = parse_excel_file(Path(fp).read_bytes(), filename=os.path.basename(fp))
        df = parsed["df"]
        df = df[df["tip"].isin(WEB)].copy()
        df["country"] = country
        df["ruc"] = pd.to_numeric(df["ruc"], errors="coerce").fillna(0.0)
        frames.append(df)
        found[country] = (df["date"].min(), df["date"].max(), len(df))
    if not frames:
        raise SystemExit(f"No classifiable prodaja files in {PRODAJA_DIR}")
    for c, (lo, hi, n) in found.items():
        print(f"  {c}: {n} web rows  {lo.date()} -> {hi.date()}")
    return pd.concat(frames, ignore_index=True)


def windows(df: pd.DataFrame):
    cutoff = df["date"].max()
    ty, tm = cutoff.year, cutoff.month
    # common depth: the earliest per-country last-day within the target month
    days = []
    for c, g in df.groupby("country"):
        gm = g[(g["date"].dt.year == ty) & (g["date"].dt.month == tm)]["date"].max()
        if pd.notna(gm):
            days.append(gm.day)
    depth = min(days) if days else cutoff.day
    py, pm = (ty - 1, 12) if tm == 1 else (ty, tm - 1)
    pdepth = min(depth, calendar.monthrange(py, pm)[1])
    return dict(ty=ty, tm=tm, py=py, pm=pm, depth=depth,
                t0=date(ty, tm, 1), t1=date(ty, tm, depth),
                p0=date(py, pm, 1), p1=date(py, pm, pdepth))


def tag_period(df, w):
    d = df["date"].dt.date
    df = df.copy()
    df["period"] = None
    df.loc[(d >= w["t0"]) & (d <= w["t1"]), "period"] = "cur"
    df.loc[(d >= w["p0"]) & (d <= w["p1"]), "period"] = "pri"
    return df[df["period"].notna()]


def pivot(df, keys):
    g = df.groupby(keys + ["period"]).agg(ruc=("ruc", "sum"), val=("value", "sum"),
                                          qty=("qty", "sum")).reset_index()
    out = {}
    for _, r in g.iterrows():
        k = r[keys[0]] if len(keys) == 1 else tuple(r[k] for k in keys)
        out.setdefault(k, {"pri": [0, 0, 0], "cur": [0, 0, 0]})
        out[k][r["period"]] = [float(r["ruc"]), float(r["val"]), float(r["qty"])]
    return out


def db_catalog():
    """sku -> (canonical_name, canonical_category) from the DB, so the combined
    all-countries view groups on ONE language instead of the per-file localised
    labels (PROTEINI/PROTEINE/ŠPORTNA PREHRANA are the same category)."""
    try:
        from sqlalchemy import text
        from backend.models.database import SessionLocal
        db = SessionLocal()
        rows = db.execute(text("""SELECT dp.sku, dp.name, COALESCE(dc.name,'(uncategorised)') cat
            FROM dim_products dp LEFT JOIN dim_categories dc ON dp.category_id=dc.id""")).fetchall()
        db.close()
        return {r[0]: (r[1], r[2]) for r in rows}
    except Exception as e:
        print("  (DB catalog unavailable — using per-file labels, which fragment by language:",
              repr(e)[:80], ")")
        return {}


def promo_penetration(w):
    """Coupon penetration from the DB coupon log (separate source)."""
    try:
        from sqlalchemy import text
        from backend.models.database import SessionLocal
        db = SessionLocal()
        cmax = db.execute(text("""SELECT MAX(order_date) FROM webshop_coupon_orders
            WHERE order_date BETWEEN :a AND :b"""),
            {"a": w["t0"].isoformat(), "b": w["t1"].isoformat()}).scalar()
        depth = min(w["depth"], cmax.day) if cmax else w["depth"]
        pdepth = min(depth, calendar.monthrange(w["py"], w["pm"])[1])
        res = {}
        for k, (y, m, dd) in [("pri", (w["py"], w["pm"], pdepth)), ("cur", (w["ty"], w["tm"], depth))]:
            d0, d1 = date(y, m, 1).isoformat(), date(y, m, dd).isoformat()
            web = db.execute(text("""SELECT COALESCE(SUM(t.quantity),0) FROM erp_transactions t
                JOIN lookup_channel_map cm ON t.channel_map_id=cm.id
                WHERE cm.channel='webshop' AND t.transaction_date BETWEEN :d0 AND :d1"""),
                {"d0": d0, "d1": d1}).scalar()
            cp = db.execute(text("""SELECT COALESCE(SUM(quantity),0), ROUND(AVG(discount_pct)::numeric,1), COUNT(*)
                FROM webshop_coupon_orders WHERE order_date BETWEEN :d0 AND :d1
                  AND order_status NOT ILIKE '%vrac%'"""), {"d0": d0, "d1": d1}).first()
            res[k] = dict(lbl=f"{calendar.month_abbr[m]} 1-{dd}", web=float(web),
                          cq=float(cp[0]), disc=float(cp[1] or 0), n=int(cp[2]))
        # sku->coupon units (cur) for grower driver
        rows = db.execute(text("""SELECT dp.sku, COALESCE(SUM(w.quantity),0) q
            FROM webshop_coupon_orders w JOIN dim_products dp ON w.product_id=dp.id
            WHERE w.order_date BETWEEN :d0 AND :d1 GROUP BY dp.sku"""),
            {"d0": w["t0"].isoformat(), "d1": w["t1"].isoformat()}).fetchall()
        db.close()
        return res, {r[0]: float(r[1]) for r in rows}
    except Exception as e:
        print("  (promo penetration skipped:", repr(e)[:120], ")")
        return None, {}


# ---------------------------------------------------------------- build
def build():
    print("Loading source prodaja files...")
    df = load_web()
    cat_map = db_catalog()
    if cat_map:
        df["cat"] = df["sku"].map({k: v[1] for k, v in cat_map.items()}).fillna(df["cat"])
        df["name"] = df["sku"].map({k: v[0] for k, v in cat_map.items()}).fillna(df["name"])
    w = windows(df)
    dfp = tag_period(df, w)
    tgt = f"{calendar.month_name[w['tm']]} {w['ty']}"
    pri = f"{calendar.month_name[w['pm']]} {w['py']}"
    TL = f"{calendar.month_abbr[w['tm']]} 1-{w['depth']}"
    PL = f"{calendar.month_abbr[w['pm']]} 1-{w['p1'].day}"

    def tot(period, country=None):
        d = dfp[dfp["period"] == period]
        if country:
            d = d[d["country"] == country]
        return dict(ruc=float(d["ruc"].sum()), val=float(d["value"].sum()), qty=float(d["qty"].sum()))

    head = {"pri": tot("pri"), "cur": tot("cur")}
    cats = pivot(dfp, ["cat"])
    countries = pivot(dfp, ["country"])
    # articles (cur) by RUC
    acur = dfp[dfp["period"] == "cur"].groupby(["sku", "name", "cat"]).agg(
        ruc=("ruc", "sum"), qty=("qty", "sum"), val=("value", "sum")).reset_index().sort_values("ruc", ascending=False)
    # growers by RUC delta
    gp = pivot(dfp, ["sku"])
    name_map = dfp.drop_duplicates("sku").set_index("sku")[["name", "cat"]].to_dict("index")
    growers = sorted(((sku, v) for sku, v in gp.items()),
                     key=lambda kv: kv[1]["cur"][0] - kv[1]["pri"][0], reverse=True)[:10]
    pen, coupon_by_sku = promo_penetration(w)

    # ---- styling ----
    FONT = "Arial"
    TITLE = Font(name=FONT, size=14, bold=True, color="1F3864")
    SUB = Font(name=FONT, size=9, italic=True, color="595959")
    HEAD = Font(name=FONT, size=10, bold=True, color="FFFFFF")
    BODY = Font(name=FONT, size=10)
    BOLD = Font(name=FONT, size=10, bold=True)
    HFILL = PatternFill("solid", fgColor="1F3864")
    TOTFILL = PatternFill("solid", fgColor="D9E1F2")
    OKFILL = PatternFill("solid", fgColor="E2EFDA")
    CEN = Alignment(horizontal="center")
    LEFT = Alignment(horizontal="left", wrap_text=True)
    thin = Side(style="thin", color="BFBFBF")
    BORD = Border(left=thin, right=thin, top=thin, bottom=thin)
    EUR = '#,##0;(#,##0);"-"'
    PCT = '0.0%;(0.0%);"-"'
    NUM = '#,##0;(#,##0);"-"'
    wb = Workbook()

    def hrow(ws, row, labels):
        for i, l in enumerate(labels):
            c = ws.cell(row=row, column=1 + i, value=l)
            c.font = HEAD; c.fill = HFILL
            c.alignment = CEN if i > 0 else LEFT; c.border = BORD

    def bord(ws, r, n):
        for i in range(n):
            ws.cell(row=r, column=1 + i).border = BORD

    # ===== SUMMARY =====
    ws = wb.active; ws.title = "Summary"; ws.sheet_view.showGridLines = False
    ws["A1"] = f"WEB (Webshop) Sales by RUC — {tgt} vs {pri}"; ws["A1"].font = TITLE
    ws["A2"] = (f"Channel: webshop (WSA-WSD{', +RAC' if WITH_RAC else ''}). Day-matched {TL} vs {PL}. "
                f"Source: data/prodaja/*.xlsx (per-country, RUC column). Metric leads on RUC (margin €)."); ws["A2"].font = SUB
    ws.merge_cells("A2:F2")
    hrow(ws, 4, ["Metric (all countries)", PL, TL, "Δ", "Δ %"])
    rows = [("RUC / margin (€)", head["pri"]["ruc"], head["cur"]["ruc"], EUR),
            ("Revenue (€)", head["pri"]["val"], head["cur"]["val"], EUR),
            ("Units", head["pri"]["qty"], head["cur"]["qty"], NUM)]
    r = 5
    for label, av, mv, fmt in rows:
        ws.cell(r, 1, label).font = BODY if "RUC" not in label else BOLD
        ws.cell(r, 2, av).number_format = fmt; ws.cell(r, 3, mv).number_format = fmt
        ws.cell(r, 4, f"=C{r}-B{r}").number_format = fmt
        ws.cell(r, 5, f'=IF(B{r}=0,"",C{r}/B{r}-1)').number_format = PCT
        for cc in range(2, 6):
            ws.cell(r, cc).font = BODY
        bord(ws, r, 5); r += 1
    ws.cell(r, 1, "RUC margin %").font = BODY
    ws.cell(r, 2, "=B5/B6").number_format = PCT; ws.cell(r, 3, "=C5/C6").number_format = PCT
    ws.cell(r, 4, f"=C{r}-B{r}").number_format = PCT
    bord(ws, r, 5); r += 1
    ws.cell(r, 1, "RUC per unit (€)").font = BODY
    ws.cell(r, 2, "=B5/B7").number_format = '#,##0.00'; ws.cell(r, 3, "=C5/C7").number_format = '#,##0.00'
    ws.cell(r, 4, f"=C{r}-B{r}").number_format = '#,##0.00'; ws.cell(r, 5, f"=C{r}/B{r}-1").number_format = PCT
    bord(ws, r, 5)
    ir = r + 2
    ws.cell(ir, 1, "Notes").font = Font(name=FONT, size=11, bold=True, color="1F3864")
    for i, n in enumerate([
        "All rankings lead on RUC (margin €). Source = per-country prodaja exports, so country is exact (no '(unknown)').",
        f"Window is day-matched to the export coverage ({TL}); the prodaja files end mid-month even though the DB",
        "  reaches further — re-export prodaja through month-end (or fix the loader's store_id) to extend the window.",
        "Promo Boost sheet is from the Magento coupon log (DB) — a separate, unit/discount-based source.",
    ]):
        c = ws.cell(ir + 1 + i, 1, n); c.font = Font(name=FONT, size=9); c.alignment = LEFT
        ws.merge_cells(start_row=ir + 1 + i, start_column=1, end_row=ir + 1 + i, end_column=6)
    for col, wd in zip("ABCDEF", [24, 14, 14, 12, 10, 10]):
        ws.column_dimensions[col].width = wd

    # ===== PER COUNTRY =====
    ws = wb.create_sheet("Per Country"); ws.sheet_view.showGridLines = False
    ws["A1"] = f"Webshop RUC per country — {tgt} vs {pri} (day-matched {TL})"; ws["A1"].font = TITLE
    hrow(ws, 3, ["Country", f"{PL} RUC €", f"{TL} RUC €", "Δ RUC €", "Δ % RUC", f"{TL} units", f"{TL} rev €"])
    r = 4
    order = sorted(countries, key=lambda c: countries[c]["cur"][0], reverse=True)
    for c in order:
        v = countries[c]
        ws.cell(r, 1, c).font = BODY
        ws.cell(r, 2, v["pri"][0]).number_format = EUR
        ws.cell(r, 3, v["cur"][0]).number_format = EUR
        ws.cell(r, 4, f"=C{r}-B{r}").number_format = EUR
        ws.cell(r, 5, f'=IF(B{r}=0,"",C{r}/B{r}-1)').number_format = PCT
        ws.cell(r, 6, v["cur"][2]).number_format = NUM
        ws.cell(r, 7, v["cur"][1]).number_format = EUR
        for cc in range(2, 8):
            ws.cell(r, cc).font = BODY
        bord(ws, r, 7); r += 1
    ws.cell(r, 1, "TOTAL").font = BOLD
    ws.cell(r, 2, f"=SUM(B4:B{r-1})").number_format = EUR
    ws.cell(r, 3, f"=SUM(C4:C{r-1})").number_format = EUR
    ws.cell(r, 4, f"=C{r}-B{r}").number_format = EUR
    ws.cell(r, 5, f"=C{r}/B{r}-1").number_format = PCT
    ws.cell(r, 6, f"=SUM(F4:F{r-1})").number_format = NUM
    ws.cell(r, 7, f"=SUM(G4:G{r-1})").number_format = EUR
    for cc in range(1, 8):
        ws.cell(r, cc).font = BOLD; ws.cell(r, cc).fill = TOTFILL; ws.cell(r, cc).border = BORD
    for col, wd in zip("ABCDEFG", [10, 13, 13, 12, 10, 10, 12]):
        ws.column_dimensions[col].width = wd

    # ===== BY CATEGORY =====
    ws = wb.create_sheet("By Category"); ws.sheet_view.showGridLines = False
    ws["A1"] = f"Webshop RUC by category — {tgt} vs {pri} (day-matched, all countries)"; ws["A1"].font = TITLE
    hrow(ws, 3, ["Category", f"{PL} RUC €", f"{TL} RUC €", "Δ RUC €", "Δ % RUC", f"{TL} units", f"{TL} rev €"])
    r = 4
    for c in sorted(cats, key=lambda k: cats[k]["cur"][0], reverse=True):
        v = cats[c]
        ws.cell(r, 1, str(c)).font = BODY
        ws.cell(r, 2, v["pri"][0]).number_format = EUR
        ws.cell(r, 3, v["cur"][0]).number_format = EUR
        ws.cell(r, 4, f"=C{r}-B{r}").number_format = EUR
        ws.cell(r, 5, f'=IF(B{r}=0,"",C{r}/B{r}-1)').number_format = PCT
        ws.cell(r, 6, v["cur"][2]).number_format = NUM
        ws.cell(r, 7, v["cur"][1]).number_format = EUR
        for cc in range(2, 8):
            ws.cell(r, cc).font = BODY
        bord(ws, r, 7); r += 1
    ws.cell(r, 1, "TOTAL").font = BOLD
    for col, f in [(2, f"=SUM(B4:B{r-1})"), (3, f"=SUM(C4:C{r-1})"), (4, f"=C{r}-B{r}"),
                   (5, f"=C{r}/B{r}-1"), (6, f"=SUM(F4:F{r-1})"), (7, f"=SUM(G4:G{r-1})")]:
        ws.cell(r, col, f).number_format = PCT if col == 5 else (NUM if col == 6 else EUR)
    for cc in range(1, 8):
        ws.cell(r, cc).font = BOLD; ws.cell(r, cc).fill = TOTFILL; ws.cell(r, cc).border = BORD
    for col, wd in zip("ABCDEFG", [24, 13, 13, 12, 10, 10, 12]):
        ws.column_dimensions[col].width = wd

    # ===== TOP ARTICLES =====
    ws = wb.create_sheet("Top Articles"); ws.sheet_view.showGridLines = False
    ws["A1"] = f"Top 15 articles by {tgt} webshop RUC ({TL})"; ws["A1"].font = TITLE
    hrow(ws, 3, ["SKU", "Article", "Category", f"{TL} RUC €", f"{TL} units", f"{TL} rev €"])
    r = 4
    for _, row in acur.head(15).iterrows():
        ws.cell(r, 1, row["sku"]).font = BODY
        ws.cell(r, 2, str(row["name"])[:42]).font = BODY
        ws.cell(r, 3, str(row["cat"])).font = BODY
        ws.cell(r, 4, float(row["ruc"])).number_format = EUR
        ws.cell(r, 5, float(row["qty"])).number_format = NUM
        ws.cell(r, 6, float(row["val"])).number_format = EUR
        for cc in range(4, 7):
            ws.cell(r, cc).font = BODY
        bord(ws, r, 6); r += 1
    for col, wd in zip("ABCDEF", [12, 44, 18, 12, 10, 12]):
        ws.column_dimensions[col].width = wd

    # ===== TOP GROWERS =====
    ws = wb.create_sheet("Top 10 Growers"); ws.sheet_view.showGridLines = False
    ws["A1"] = f"Top 10 growers — webshop RUC Δ, {TL} vs {PL}"; ws["A1"].font = TITLE
    hrow(ws, 3, ["SKU", "Article", "Category", f"{PL} RUC €", f"{TL} RUC €", "Δ RUC €",
                 f"{PL} units", f"{TL} units", "Coupon units", "Driver"])
    r = 4
    for sku, v in growers:
        nm = name_map.get(sku, {"name": "", "cat": ""})
        apr_ruc, cur_ruc = v["pri"][0], v["cur"][0]
        apr_q, cur_q = v["pri"][2], v["cur"][2]
        cq = coupon_by_sku.get(sku, 0.0)
        driver = "Promo (coupon)" if cq > 0 else ("New listing ramp" if (apr_q == 0 and cur_q > 0) else "Organic / availability")
        ws.cell(r, 1, sku).font = BODY
        ws.cell(r, 2, str(nm["name"])[:40]).font = BODY
        ws.cell(r, 3, str(nm["cat"])).font = BODY
        ws.cell(r, 4, apr_ruc).number_format = EUR
        ws.cell(r, 5, cur_ruc).number_format = EUR
        ws.cell(r, 6, f"=E{r}-D{r}").number_format = EUR
        ws.cell(r, 7, apr_q).number_format = NUM
        ws.cell(r, 8, cur_q).number_format = NUM
        ws.cell(r, 9, cq).number_format = NUM
        ws.cell(r, 10, driver).font = BODY
        for cc in range(4, 10):
            ws.cell(r, cc).font = BODY
        bord(ws, r, 10); r += 1
    for col, wd in zip("ABCDEFGHIJ", [11, 40, 16, 11, 11, 11, 9, 9, 12, 20]):
        ws.column_dimensions[col].width = wd

    # ===== PROMO BOOST =====
    if pen:
        ws = wb.create_sheet("Promo Boost"); ws.sheet_view.showGridLines = False
        ws["A1"] = "Promo boost — webshop coupon penetration (Magento coupon log, DB)"; ws["A1"].font = TITLE
        hrow(ws, 3, ["Window", "Web units", "Coupon units", "Penetration %", "Avg discount %", "Coupon orders"])
        r = 4
        for k in ("pri", "cur"):
            p = pen[k]
            ws.cell(r, 1, p["lbl"]).font = BODY
            ws.cell(r, 2, p["web"]).number_format = NUM
            ws.cell(r, 3, p["cq"]).number_format = NUM
            ws.cell(r, 4, f'=IF(B{r}=0,"",C{r}/B{r})').number_format = PCT
            ws.cell(r, 5, p["disc"] / 100).number_format = PCT
            ws.cell(r, 6, p["n"]).number_format = NUM
            for cc in range(2, 7):
                ws.cell(r, cc).font = BODY
            bord(ws, r, 6); r += 1
        ws.cell(r + 1, 1, ("Separate source (Magento), unit/discount based — RUC is not in the coupon log. "
                           "Rising penetration with a falling discount = a broad sitewide code.")).font = SUB
        ws.merge_cells(start_row=r + 1, start_column=1, end_row=r + 1, end_column=6)
        for col, wd in zip("ABCDEF", [12, 12, 14, 14, 16, 14]):
            ws.column_dimensions[col].width = wd

    wb.calculation.fullCalcOnLoad = True
    out = f"WEB_Sales_RUC_{calendar.month_abbr[w['tm']]}{w['ty']}.xlsx"
    wb.save(out)
    print(f"\nsaved {out}  (RUC-led, {TL} vs {PL}, per country from source files)")
    # quick console headline
    print(f"  RUC all-countries: {PL} €{head['pri']['ruc']:,.0f} -> {TL} €{head['cur']['ruc']:,.0f} "
          f"({(head['cur']['ruc']/head['pri']['ruc']-1)*100:+.1f}%)")
    for c in order:
        v = countries[c]
        gp_ = (v["cur"][0]/v["pri"][0]-1)*100 if v["pri"][0] else float('nan')
        print(f"  {c}: RUC €{v['pri'][0]:,.0f} -> €{v['cur'][0]:,.0f} ({gp_:+.1f}%)")


if __name__ == "__main__":
    build()