"""Productionize v4 — run all planned SKUs through the bottom-up + reconciled
engine and write a new run into the `forecasts` table (which the app's demand
pages and the supply projection both read). Reuses forecast_db.write_forecast_run
for the run/cycle row; populates forecasts_detail via forecast_detail_service.

A new run becomes MAX(run_id), so it's picked up automatically — the legacy run
stays untouched for side-by-side comparison.
"""
from __future__ import annotations

from datetime import datetime

from sqlalchemy import text

from backend.models.database import engine
from forecast_v4.engine import forecast_sku

_INSERT = text("""
    INSERT INTO forecasts
        (run_id, product_id, year, week, baseline, on_top_wholesale, on_top_retail,
         promo_uplift, planner_factor, total, model_used, channel_mode,
         forecast_retail, forecast_wholesale)
    VALUES
        (:run_id, :product_id, :year, :week, :baseline, :on_top_wholesale, :on_top_retail,
         0, :planner_factor, :total, :model_used, 'v4_bottomup',
         :forecast_retail, :forecast_wholesale)
""")

# KAM/CM on-tops live in on_top_inputs, additive to the engine baseline. Fold them
# in per (product, week): wholesale -> on_top_wholesale, retail + 'food retail'
# (both B2C) -> on_top_retail. quantity already includes the regular-increase part.
_ONTOP = text("""
    SELECT product_id, year_week,
           SUM(CASE WHEN channel = 'wholesale' THEN quantity ELSE 0 END)::float AS ws,
           SUM(CASE WHEN channel IN ('retail', 'food retail') THEN quantity ELSE 0 END)::float AS mp
    FROM on_top_inputs
    GROUP BY product_id, year_week
""")


# Rolling FA snapshot: for every CLOSED week (has actuals) that we forecast but
# haven't scored yet, freeze "the forecast we first made for that week" vs the
# actual into backtest_results. Idempotent (NOT EXISTS), so a re-run only adds
# newly-closed weeks → the Forecast Accuracy chart self-extends each run.
_FA_SNAPSHOT = text("""
    INSERT INTO backtest_results
      (product_id, year, week, forecast, actual, model, channel_mode, run_at,
       forecast_retail, forecast_wholesale, actual_retail, actual_wholesale, ws_share)
    SELECT ff.product_id, ff.year, ff.week, ff.forecast, v.qty_total,
           ff.model, ff.channel_mode, now(),
           ff.forecast_retail, ff.forecast_wholesale,
           COALESCE(v.qty_retail,0)+COALESCE(v.qty_webshop,0),
           COALESCE(v.qty_wholesale,0),
           CASE WHEN v.qty_total > 0 THEN COALESCE(v.qty_wholesale,0)/v.qty_total ELSE 0 END
    FROM (
        SELECT DISTINCT ON (product_id, year, week)
            product_id, year, week, total AS forecast, model_used AS model, channel_mode,
            forecast_retail, forecast_wholesale
        FROM forecasts
        ORDER BY product_id, year, week, run_id ASC, id ASC
    ) ff
    JOIN v_sales_weekly_full v
      ON v.product_id = ff.product_id AND v.year = ff.year AND v.week = ff.week
    WHERE v.qty_total > 0
      -- only FULLY-CLOSED weeks (strictly before the current ISO week) so we
      -- never freeze a partial current-week actual into the FA history.
      AND (ff.year * 100 + ff.week)
          < (EXTRACT(isoyear FROM now())::int * 100 + EXTRACT(week FROM now())::int)
      AND NOT EXISTS (SELECT 1 FROM backtest_results b
                      WHERE b.product_id = ff.product_id AND b.year = ff.year AND b.week = ff.week)
""")


def _new_run(conn, year_week: int, run_by_id, n_skus: int) -> int:
    """Create the sop_cycle (if missing) + forecast_runs row; return run_id."""
    row = conn.execute(text("SELECT id FROM sop_cycles WHERE year_week=:yw"),
                       {"yw": year_week}).first()
    cycle_id = int(row[0]) if row else int(conn.execute(text(
        "INSERT INTO sop_cycles (year_week, status) VALUES (:yw,'active') RETURNING id"),
        {"yw": year_week}).first()[0])
    return int(conn.execute(text("""
        INSERT INTO forecast_runs (cycle_id, run_by_id, run_type, n_skus, params)
        VALUES (:cid, :rid, 'v4_bottomup', :n, CAST(:p AS JSONB)) RETURNING id
    """), {"cid": cycle_id, "rid": run_by_id, "n": n_skus,
           "p": '{"engine": "forecast_v4", "version": "4.0"}'}).first()[0])


def run_v4(run_by_id=None, h: int = 13, tier_only: bool = True,
           fold_ontops: bool = True, carry_factors: bool = True) -> dict:
    with engine.connect() as c:
        q = "SELECT product_id FROM sku_planning"
        if tier_only:
            q += " WHERE tier IS NOT NULL"
        pids = [int(r[0]) for r in c.execute(text(q)).all()]
        # global anchor: latest data week across all SKUs -> every SKU forecasts
        # the same horizon (W+1..W+h), not its own last-sale week.
        anchor = c.execute(text(
            "SELECT MAX(EXTRACT(isoyear FROM transaction_date)::int*100"
            "+EXTRACT(week FROM transaction_date)::int) FROM erp_transactions"
        )).scalar()
        anchor = int(anchor) if anchor else None
        ontop = ({(int(pid), int(yw)): (ws, mp)
                  for pid, yw, ws, mp in c.execute(_ONTOP).all()}
                 if fold_ontops else {})   # baseline run = no KAM/CM on-tops

        # Carry the planner's per-(SKU, week) factors forward from the most
        # recent run so a re-run doesn't wipe the planning overlay. New weeks
        # entering the horizon default to 1.0. 'baseline' runs pass
        # carry_factors=False for a clean machine forecast.
        pfmap: dict = {}
        if carry_factors:
            prev_run = c.execute(text("SELECT MAX(run_id) FROM forecasts")).scalar()
            if prev_run is not None:
                for pid_, y_, w_, pf_ in c.execute(text(
                    "SELECT product_id, year, week, planner_factor FROM forecasts "
                    "WHERE run_id = :r AND planner_factor IS NOT NULL "
                    "AND planner_factor <> 1"), {"r": int(prev_run)}).all():
                    pfmap[(int(pid_), int(y_), int(w_))] = float(pf_)

    iso = datetime.now().isocalendar()
    year_week = int(iso[0]) * 100 + int(iso[1])
    with engine.begin() as conn:
        run_id = _new_run(conn, year_week, run_by_id, len(pids))

    rows, n_ok = [], 0
    for pid in pids:
        r = forecast_sku(pid, h=h, start_yw=anchor)
        if r is None:
            continue
        n_ok += 1
        tw, rw, ww = r["total_week"], r["retail_week"], r["wholesale_week"]
        for j, (y, w) in enumerate(r["weeks"]):
            otw, otr = ontop.get((pid, int(y) * 100 + int(w)), (0.0, 0.0))
            base = float(tw[j])
            pf = pfmap.get((pid, int(y), int(w)), 1.0)   # carried planner factor
            rows.append({
                "run_id": run_id, "product_id": pid, "year": int(y), "week": int(w),
                "baseline": base,
                "planner_factor": pf,
                "on_top_wholesale": otw, "on_top_retail": otr,
                "total": base * pf + otw + otr,
                "forecast_retail": float(rw[j]) * pf + otr,
                "forecast_wholesale": float(ww[j]) * pf + otw,
                "model_used": r["td_model"],
            })

    with engine.begin() as conn:
        for i in range(0, len(rows), 1000):
            conn.execute(_INSERT, rows[i:i + 1000])

    detail_rows = 0
    try:
        from backend.services.forecast_detail_service import allocate
        detail_rows = allocate(run_id=run_id).get("detail_rows", 0)
    except Exception as exc:  # noqa: BLE001
        print(f"  [warn] forecasts_detail allocate skipped: {exc}")

    fa_rows = 0
    try:
        with engine.begin() as conn:
            fa_rows = conn.execute(_FA_SNAPSHOT).rowcount or 0
    except Exception as exc:  # noqa: BLE001
        print(f"  [warn] FA snapshot skipped: {exc}")

    return {"run_id": run_id, "n_skus": n_ok, "forecast_rows": len(rows),
            "detail_rows": detail_rows, "fa_rows_snapshotted": fa_rows}


if __name__ == "__main__":
    print(run_v4())