"""Per-week supplier-incoming plan, parsed from the Ulaz sheet of
`data/Template_Plan_ulaza_izlaza_2026_*.xlsx`.

The template is a buying-side view: rows = suppliers, columns = ISO weeks,
cells = nabavna vrijednost (cost EUR) of planned incoming goods. This module
collapses it to a single dict[(iso_year, iso_week)] -> total_nv_eur so the
Stock Projection and Scenario Planner charts can overlay the supplier-team's
post-CW28 plan on top of the per-SKU PO data we have for CW21–CW28.

We don't write to incoming_supply — per the user's call: "just put in graphs
those values, just artificially". Treat this as a display-only overlay.
"""
from __future__ import annotations

import glob
import os
from datetime import date
from functools import lru_cache
from typing import Optional

import pandas as pd


def _newest_template_path() -> Optional[str]:
    """Pick the most recently modified Template_Plan_ulaza_izlaza_* file."""
    candidates = glob.glob("data/Template_Plan_ulaza_izlaza_*.xlsx")
    if not candidates:
        return None
    return max(candidates, key=os.path.getmtime)


@lru_cache(maxsize=1)
def load_supplier_plan_per_week() -> dict[tuple[int, int], float]:
    """Return {(iso_year, iso_week): total_nv_eur} from the latest template.

    Sheet layout:
      row 0: week-start dates (Mon) from col 3 onward
      row 1: week-end dates (Sun) from col 3 onward
      row 2: per-week totals across suppliers (we recompute from rows below
             so unmatched/edited cells don't drift)
      row 3: empty
      row 4: column headers: [grupacija, Naziv, Početno stanje NV …, …]
      row 5+: data rows (per supplier × group)
    """
    path = _newest_template_path()
    if not path:
        return {}
    df = pd.read_excel(path, sheet_name="Ulaz", header=None)
    if df.shape[0] < 6 or df.shape[1] < 4:
        return {}

    # Map column index → (iso_year, iso_week) using row 0 (Monday-of-week)
    col_to_yw: dict[int, tuple[int, int]] = {}
    for col in range(3, df.shape[1]):
        v = df.iloc[0, col]
        if hasattr(v, "isocalendar"):
            iso = v.isocalendar()
            col_to_yw[col] = (int(iso[0]), int(iso[1]))

    # Sum data rows (row 5 to end) per week column
    out: dict[tuple[int, int], float] = {}
    data = df.iloc[5:, :]
    for col, yw in col_to_yw.items():
        s = pd.to_numeric(data.iloc[:, col], errors="coerce").sum()
        if pd.notna(s) and float(s) != 0.0:
            out[yw] = float(s)
    return out


def planned_incoming_eur(year: int, week: int,
                          min_week_key: int = 202629) -> float:
    """Return the planned EUR incoming for a single (year, week) — but only
    for weeks ≥ min_week_key. The template covers all of 2026 but the user
    wants the overlay to start at CW29/2026 (`min_week_key=202629`) since
    earlier weeks already have real per-SKU ABC POs in incoming_supply.
    """
    if year * 100 + week < min_week_key:
        return 0.0
    return load_supplier_plan_per_week().get((year, week), 0.0)