"""Incoming-PO upload service.

Accepts ONE Excel in the "incoming supply po tjednima" shape — a wide
SKU × week matrix:

    | Šifra artikla | 21 | 22 | 23 | ... |
    | BSN06909      |    | 500|1200|     |

The first column is the SKU; every other column header is an ISO week
number, and the cells are incoming quantities. We melt it to the long
shape the rest of the system already uses (sku, year, week, qty),
overwrite data/incoming_supply.csv, and re-run the existing
_load_incoming_supply loader — which TRUNCATES incoming_supply and
re-inserts. So each upload fully replaces the incoming picture, which is
how a weekly snapshot of open POs is meant to work.

incoming_supply drives Stock Projection, Coverage and Scenario Planner,
so a successful upload flows straight through to those pages.

The `year` is supplied by the caller (the file carries only week
numbers); every week in the file is stamped with it.
"""
from __future__ import annotations

import io
import re
from pathlib import Path
from typing import Optional

import pandas as pd
from sqlalchemy.orm import Session

ROOT = Path(__file__).resolve().parents[2]
DATA = ROOT / "data"
INCOMING_CSV = DATA / "incoming_supply.csv"

# SKU column header — tolerate the mojibake'd "Šifra artikla" and aliases.
_SKU_HEADER_HINTS = ("ifra artikla", "ifra", "sku", "artik", "code", "šifra", "sifra")

# Pseudo-SKU prefixes — non-product line items. Mirrors upload_stock_service
# so incoming stays consistent with stock/sales.
_PSEUDO_SKU_RE = re.compile(r"^(OST|MKT|CARD|WOO|AMB|USL|MSM|WOLTD)", re.IGNORECASE)


def _as_week(col) -> Optional[int]:
    """Return the ISO week number a column header represents, or None.
    Headers come in as ints (21) or strings ('21', 'CW21', 'W21')."""
    if isinstance(col, (int, float)) and not pd.isna(col):
        w = int(col)
        return w if 1 <= w <= 53 else None
    s = str(col).strip().lower().lstrip("cw").lstrip("w").strip()
    if s.isdigit():
        w = int(s)
        return w if 1 <= w <= 53 else None
    return None


def _pick_sku_col(cols) -> Optional[str]:
    """The SKU column: first header that matches a name hint, else the
    first column that isn't a week number."""
    for c in cols:
        low = str(c).strip().lower()
        if any(h in low for h in _SKU_HEADER_HINTS):
            return c
    for c in cols:
        if _as_week(c) is None:
            return c
    return None


def _parse_incoming_matrix(blob: bytes, filename: str, year: int) -> pd.DataFrame:
    """Wide SKU × week matrix → long df [sku, year, week, qty]."""
    # First sheet that has both a SKU column and at least one week column.
    xls = pd.ExcelFile(io.BytesIO(blob))
    parsed: Optional[pd.DataFrame] = None
    sku_col = None
    week_cols: dict = {}
    for sn in xls.sheet_names:
        for header_row in range(0, 4):
            try:
                df = pd.read_excel(io.BytesIO(blob), sheet_name=sn, header=header_row)
            except Exception:
                continue
            sc = _pick_sku_col(df.columns)
            wc = {c: _as_week(c) for c in df.columns if _as_week(c) is not None}
            if sc is not None and wc:
                parsed, sku_col, week_cols = df, sc, wc
                break
        if parsed is not None:
            break

    if parsed is None or sku_col is None or not week_cols:
        raise ValueError(
            f"Could not read {filename}: expected a SKU column (e.g. "
            f"'Šifra artikla') plus week-number columns (21, 22, …)."
        )

    long_rows: list[dict] = []
    for _, row in parsed.iterrows():
        sku = str(row[sku_col]).strip()
        if not sku or sku.lower() == "nan":
            continue
        if _PSEUDO_SKU_RE.match(sku):
            continue
        for col, wk in week_cols.items():
            qty = pd.to_numeric(row[col], errors="coerce")
            if pd.isna(qty) or float(qty) <= 0:
                continue
            long_rows.append({"sku": sku, "year": year, "week": int(wk), "qty": float(qty)})

    out = pd.DataFrame(long_rows, columns=["sku", "year", "week", "qty"])
    if not out.empty:
        # One file may list a SKU/week twice — sum so the key is unique.
        out = (out.groupby(["sku", "year", "week"], as_index=False)["qty"].sum())
    return out


def process_incoming_po_upload(
    db: Session,
    filename: str,
    blob: bytes,
    year: int,
) -> dict:
    """Parse the PO matrix, overwrite incoming_supply.csv, and reload the
    incoming_supply table (full replace). Returns a summary dict."""
    DATA.mkdir(parents=True, exist_ok=True)
    warnings: list[str] = []

    df = _parse_incoming_matrix(blob, filename, year)
    if df.empty:
        raise ValueError(
            f"{filename} parsed to zero incoming rows — no positive quantities found."
        )

    weeks = sorted(df["week"].unique().tolist())
    file_skus = int(df["sku"].nunique())
    total_units = float(df["qty"].sum())

    # Write the canonical CSV the loader reads (status column kept for schema
    # parity; left blank for uploaded POs).
    df_out = df.copy()
    df_out["status"] = ""
    df_out = df_out[["sku", "year", "week", "qty", "status"]]
    df_out.to_csv(INCOMING_CSV, index=False, encoding="utf-8")

    # Full-replace reload via the existing loader (truncates + inserts).
    db_rows = 0
    db_error: Optional[str] = None
    try:
        from db.connection import get_connection
        import db.migrate_remaining as mr
        conn = get_connection()
        conn.autocommit = False
        try:
            maps = mr._load_lookup_maps(conn)
            db_rows = mr._load_incoming_supply(conn, maps)
            conn.commit()
        finally:
            conn.close()
    except Exception as e:
        db_error = str(e)
        warnings.append(f"DB reload failed: {e}")

    if db_rows and db_rows < len(df):
        warnings.append(
            f"{len(df) - db_rows} row(s) were dropped during load "
            f"(unmappable SKUs)."
        )

    return {
        "filename": filename,
        "year": year,
        "rows_parsed": int(len(df)),
        "skus": file_skus,
        "weeks": [int(w) for w in weeks],
        "total_units": total_units,
        "rows_inserted_to_db": int(db_rows),
        "db_error": db_error,
        "warnings": warnings,
    }