"""Phase 2a — channel × region forecast allocation (see FORECASTING_PLAN.md).

Splits each SKU's published weekly forecast (`forecasts.total`) into channel ×
region rows in `forecasts_detail`, by the SKU's trailing 26-week sales mix from
`v_sales_weekly_channel_region`. UNK (unknown region) is redistributed
proportionally into known regions. Every (product, year, week) reconciles
EXACTLY back to `forecasts.total` (rounding residual placed on the largest
bucket).

DELIBERATELY DECOUPLED from forecast_engine.py: only READS `forecasts` and
WRITES the additive `forecasts_detail` table — the live total and
forecast_for_supply.csv are never touched. Idempotent (clears the run's detail
first), safe to re-run. Called automatically after each forecast run
(forecast_service) and runnable standalone (scripts/allocate_forecast_detail.py).
"""
from __future__ import annotations

from collections import defaultdict
from datetime import date, timedelta

from sqlalchemy import text

from backend.models.database import engine

LOOKBACK_WEEKS = 26


def _build_shares(rows):
    """rows: mappings of (product_id, channel, region, qty). Returns per-SKU
    {channel: [channel_qty, {region: qty_known}]} plus a global fallback mix."""
    per_sku: dict[int, dict[str, list]] = defaultdict(
        lambda: defaultdict(lambda: [0.0, defaultdict(float)])
    )
    glob: dict[str, list] = defaultdict(lambda: [0.0, defaultdict(float)])
    for r in rows:
        sku, ch, reg, qty = r["product_id"], r["channel"], r["region"], float(r["qty"] or 0)
        if qty <= 0 or ch is None:
            continue
        per_sku[sku][ch][0] += qty
        glob[ch][0] += qty
        if reg and reg != "UNK":
            per_sku[sku][ch][1][reg] += qty
            glob[ch][1][reg] += qty
    return per_sku, glob


def _weights(mix: dict[str, list]) -> dict[tuple[str, str], float]:
    """Normalize a channel/region mix into {(channel,region): weight} summing to
    1.0. UNK is redistributed (only known regions appear); a channel with qty but
    no known region is parked on HR."""
    total = sum(v[0] for v in mix.values())
    if total <= 0:
        return {}
    out: dict[tuple[str, str], float] = {}
    for ch, (ch_qty, regions) in mix.items():
        ch_share = ch_qty / total
        known = sum(regions.values())
        if known > 0:
            for reg, q in regions.items():
                out[(ch, reg)] = ch_share * (q / known)
        else:
            out[(ch, "HR")] = ch_share
    return out


def allocate(run_id: int | None = None) -> dict:
    """Populate forecasts_detail for a run (default: latest). Returns a summary
    dict. Never raises on empty input — returns counts."""
    today = date.today()
    iso = (today - timedelta(weeks=LOOKBACK_WEEKS)).isocalendar()
    cut_yw = iso[0] * 100 + iso[1]

    with engine.begin() as conn:
        if run_id is None:
            run_id = conn.execute(text("SELECT MAX(run_id) FROM forecasts")).scalar()
        if run_id is None:
            return {"run_id": None, "detail_rows": 0, "forecast_rows": 0, "skipped": 0}

        share_rows = conn.execute(text("""
            SELECT product_id, channel, region, SUM(qty) AS qty
            FROM v_sales_weekly_channel_region
            WHERE (year * 100 + week) >= :cut AND qty > 0
            GROUP BY product_id, channel, region
        """), {"cut": cut_yw}).mappings().all()
        per_sku, glob = _build_shares(share_rows)
        glob_w = _weights(glob)

        fc_rows = conn.execute(text("""
            SELECT product_id, year, week, total
            FROM forecasts
            WHERE run_id = :r AND total IS NOT NULL AND total <> 0
        """), {"r": run_id}).mappings().all()

        conn.execute(text("DELETE FROM forecasts_detail WHERE run_id = :r"), {"r": run_id})

        out_rows: list[dict] = []
        skipped = 0
        for fr in fc_rows:
            sku, yr, wk, tot = fr["product_id"], fr["year"], fr["week"], float(fr["total"])
            w = _weights(per_sku.get(sku, {})) or glob_w
            if not w:
                skipped += 1
                continue
            alloc = {k: round(tot * wt, 4) for k, wt in w.items()}
            residual = round(tot - sum(alloc.values()), 4)
            top = max(alloc, key=alloc.get)
            alloc[top] = round(alloc[top] + residual, 4)
            for (ch, reg), val in alloc.items():
                out_rows.append({
                    "run_id": run_id, "product_id": sku, "channel": ch, "region": reg,
                    "year": yr, "week": wk, "baseline": None, "on_top": None,
                    "promo_uplift": None, "total": val,
                })

        if out_rows:
            conn.execute(text("""
                INSERT INTO forecasts_detail
                    (run_id, product_id, channel, region, year, week,
                     baseline, on_top, promo_uplift, total)
                VALUES
                    (:run_id, :product_id, :channel, :region, :year, :week,
                     :baseline, :on_top, :promo_uplift, :total)
            """), out_rows)

    return {"run_id": run_id, "detail_rows": len(out_rows),
            "forecast_rows": len(fc_rows), "skipped": skipped}