"""Generate REAL promo-board sample data from recent ERP promo campaigns.

Replaces the fabricated SAMPLE in PromoBoard.tsx with our actual last-month(s)
campaigns (AKCIJE 06/26, Summer buddy 26, AKCIJE 04/26, HIT TJEDNA …), each
expanded into ARTICLE GROUPS (e.g. "Polleo 1st Whey 454g") so the board's
drop-down shows which product lines are on promo.

Source: erp_promo_campaigns + erp_promo_items (read-only, polleo_ai_ro).
Grouping: product NAME stem up to the size/unit token (brand/family are
empty in the dump). Discount = avg discount_pct of the group; price = avg
normal retail price; qty = group's last-8w avg weekly sales × promo weeks
(a plausible projection — this is a preview board, not a committed plan).

Channel / market / partner are NOT stored on the ERP promo, so they are set
to sensible retail defaults (flagged in the UI). Everything else is real.

Writes: frontend/src/pages/promo/promoSampleData.ts

Run:  py build_promo_board_data.py
"""
from __future__ import annotations
import io, re, json, subprocess, datetime as dt

import pandas as pd

DBCONT="polleo_db"; DB_USER="polleo_ai_ro"; PGPW="polleo_ro_dev"; DB_NAME="polleo_demand"
OUT_TS = "frontend/src/pages/promo/promoSampleData.ts"

# Curated set of recent real campaigns → (channels, markets) defaults + display.
# (ERP promo carries no channel/market; these are retail defaults, flagged in UI.)
CAMPAIGNS = {
    387: dict(name="Akcije 06/26",     channels=["retail"],          markets=["HR","SLO","AT"], partner=""),
    388: dict(name="Summer buddy 26",  channels=["retail","ecom"],   markets=["HR","SLO"],      partner=""),
    5:   dict(name="Akcije 04/26",     channels=["retail"],          markets=["HR","SLO","AT"], partner=""),
    1:   dict(name="Hit tjedna 18",    channels=["retail"],          markets=["HR"],            partner=""),
    2:   dict(name="Hit tjedna 17",    channels=["retail"],          markets=["HR"],            partner=""),
    3:   dict(name="Hit tjedna 16",    channels=["retail"],          markets=["HR"],            partner=""),
}
DEPT = "Category"

SIZE_RE = re.compile(
    r"(.*?\b\d+(?:[.,]\d+)?\s?(?:g|kg|ml|l|caps|kaps|kapsula|tbl|tableta|kom|pak|servings|doza|caps\.?))",
    re.IGNORECASE)


def q(sql: str) -> pd.DataFrame:
    copy = f"COPY ({sql}) TO STDOUT WITH CSV HEADER"
    cmd = ["docker","exec","-i","-e",f"PGPASSWORD={PGPW}",DBCONT,
           "psql","-U",DB_USER,"-d",DB_NAME,"-q","-v","ON_ERROR_STOP=1"]
    out = subprocess.run(cmd, input=copy, capture_output=True, text=True, encoding="utf-8")
    if out.returncode:
        raise RuntimeError(out.stderr)
    return pd.read_csv(io.StringIO(out.stdout))


def iso_week(d: str) -> int:
    return dt.date.fromisoformat(d).isocalendar().week


def group_label(name: str) -> str:
    """Article-group stem from the product name: keep up to the size/unit token
    (so flavor/colour variants collapse into one line). Fallback: first 3 words."""
    n = re.sub(r"[™®]", "", str(name)).strip()
    m = SIZE_RE.match(n)
    if m:
        return re.sub(r"\s+", " ", m.group(1)).strip()
    return " ".join(n.split()[:3])


def main():
    ids = ",".join(str(i) for i in CAMPAIGNS)
    camp = q(f"SELECT id, valid_from::text vf, valid_to::text vt FROM erp_promo_campaigns WHERE id IN ({ids})")
    camp = camp.set_index("id")

    items = q(f"""
        WITH rr AS (   -- last 8 calendar weeks avg weekly qty per product
          WITH last8 AS (
            SELECT DISTINCT (EXTRACT(ISOYEAR FROM transaction_date)::int*100
                           + EXTRACT(WEEK FROM transaction_date)::int) yw
            FROM erp_transactions ORDER BY yw DESC LIMIT 8)
          SELECT product_id, SUM(quantity)/8.0 AS wk
          FROM erp_transactions
          WHERE (EXTRACT(ISOYEAR FROM transaction_date)::int*100
               + EXTRACT(WEEK FROM transaction_date)::int) IN (SELECT yw FROM last8)
          GROUP BY product_id)
        SELECT i.campaign_id, p.name,
               COALESCE(c.name,'') AS category,
               i.discount_pct::float AS disc,
               COALESCE(ep.normal_retail_ppp,0)::float AS price,
               COALESCE(rr.wk,0)::float AS wk
        FROM erp_promo_items i
        JOIN dim_products p ON p.id=i.product_id
        LEFT JOIN dim_categories c ON c.id=p.category_id
        LEFT JOIN erp_prices ep ON ep.product_id=p.id
        LEFT JOIN rr ON rr.product_id=i.product_id
        WHERE i.campaign_id IN ({ids})
    """)
    items["grp"] = items["name"].map(group_label)

    promos = []
    pid = 1
    for cid, meta in CAMPAIGNS.items():
        if cid not in camp.index:
            continue
        vf, vt = camp.at[cid, "vf"], camp.at[cid, "vt"]
        start, end = iso_week(vf), iso_week(vt)
        weeks = max(1, end - start + 1)
        sub = items[items["campaign_id"] == cid]
        groups = []
        for (grp, cat), g in sub.groupby(["grp", "category"]):
            price = g.loc[g["price"] > 0, "price"].mean()
            qty = float(g["wk"].sum()) * weeks
            groups.append({
                "what": grp,
                "cat": cat,
                "nSkus": int(len(g)),
                "price": round(float(price), 2) if pd.notna(price) else "",
                "disc": int(round(float(g["disc"].mean()))),
                "qty": int(round(qty)),
            })
        # biggest groups first (by projected qty, then SKU count)
        groups.sort(key=lambda x: (-(x["qty"]), -x["nSkus"]))
        promos.append({
            "id": pid, "name": meta["name"], "channels": meta["channels"],
            "markets": meta["markets"], "partner": meta["partner"],
            "start": start, "end": end, "dept": DEPT,
            "outcome": "", "items": groups,
        })
        pid += 1

    wk_min = min(p["start"] for p in promos)
    wk_max = max(p["end"] for p in promos)

    header = ("// AUTO-GENERATED by build_promo_board_data.py — do not edit by hand.\n"
              "// Real recent ERP promo campaigns (erp_promo_campaigns/_items), grouped\n"
              "// into article lines by name stem. Channel/market are retail defaults\n"
              "// (the ERP promo carries none); name, period, articles and discount are real.\n")
    body = (header +
            f"export const PROMO_WEEK_START = {wk_min}\n"
            f"export const PROMO_WEEK_END = {wk_max}\n"
            "export const SAMPLE = " + json.dumps(promos, ensure_ascii=False, indent=2) + "\n")
    with open(OUT_TS, "w", encoding="utf-8") as fh:
        fh.write(body)

    ng = sum(len(p["items"]) for p in promos)
    print(f"wrote {OUT_TS}: {len(promos)} campaigns, {ng} article groups, "
          f"weeks CW{wk_min}–CW{wk_max}")
    for p in promos:
        print(f"  {p['name']:20} CW{p['start']:02d}-{p['end']:02d}  "
              f"{len(p['items']):2d} skupina  ({sum(g['nSkus'] for g in p['items'])} SKU)")


if __name__ == "__main__":
    main()