"""Stock upload service.

Accepts 1-3 Excel files (one per country: HR / SI / AT), parses them,
splits the HR file into central warehouse (Jedin. = '01') vs stores,
aggregates SI and AT as stores-only, writes the four CSVs the
existing stock loader expects, then re-runs that loader so the
erp_stock_current table is refreshed.

CSVs produced (overwritten each upload):
  data/stock.csv            — HR WH only           (sku, on_hand)
  data/stock_stores.csv     — HR stores aggregate  (sku, on_hand)
  data/stock_stores_slo.csv — SI stores aggregate  (sku, on_hand)
  data/stock_stores_at.csv  — AT stores aggregate  (sku, on_hand)

A missing country file is treated as "keep the existing CSV unchanged"
— i.e. uploading only the HR file won't blank out SI/AT stock.
"""
from __future__ import annotations

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

import pandas as pd
from sqlalchemy.orm import Session

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

# Columns we'll try to find in any order/casing. Order = priority.
SKU_NAMES   = ("šifra", "sifra", "sku", "artikal", "artikl", "code")
QTY_NAMES   = ("zaliha", "stock", "stock_qty", "on_hand", "on hand", "qty", "quantity", "količina", "kolicina")
LOC_NAMES   = ("jedin.", "jedin", "jedinica", "location", "store", "store_code", "unit", "unit_code")
NAME_LOC    = ("naziv odjela/jedinice", "store_name", "unit_name", "naziv")

COUNTRY_MAP = {
    "HR":  {"wh_csv": "stock.csv",            "stores_csv": "stock_stores.csv"},
    "SI":  {"wh_csv": None,                    "stores_csv": "stock_stores_slo.csv"},
    "SLO": {"wh_csv": None,                    "stores_csv": "stock_stores_slo.csv"},
    "AT":  {"wh_csv": None,                    "stores_csv": "stock_stores_at.csv"},
}

# Pseudo-SKU prefixes — non-product line items (coupons, postage, marketing
# rebates, gift cards, packaging labels, marketing materials, services).
# Mirrors db/migrate_sales.py:_PSEUDO_SKU_RE so stock + sales stay consistent;
# without this filter AMB/OST/etc. inflate erp_stock_current (especially the
# stores aggregate) with non-sellable inventory.
_PSEUDO_SKU_RE = re.compile(r"^(OST|MKT|CARD|WOO|AMB|USL|MSM|WOLTD)", re.IGNORECASE)


def _drop_pseudo_skus(df: pd.DataFrame) -> pd.DataFrame:
    if df.empty or "sku" not in df.columns:
        return df
    mask = df["sku"].fillna("").str.match(_PSEUDO_SKU_RE)
    return df[~mask].copy()


def _find_col(cols: Iterable[str], needles: Iterable[str]) -> Optional[str]:
    lower = {str(c).lower().strip(): c for c in cols}
    for n in needles:
        if n in lower:
            return lower[n]
    # Fuzzy fallback: substring match
    for n in needles:
        for low, orig in lower.items():
            if n in low:
                return orig
    return None


def _parse_stock_qty(s: pd.Series) -> pd.Series:
    """Coerce a stock-qty Series to float, tolerant of European number format
    in CSVs ('1.234,56', '1234,56', Croatian thousands '1.234' = 1234). Numeric
    (Excel) Series pass through. Format decided per column (see _to_numeric_eu
    in upload_repo for the rationale)."""
    if not (s.dtype == object or pd.api.types.is_string_dtype(s)):
        return pd.to_numeric(s, errors="coerce").fillna(0)

    txt = s.astype(str).str.strip()
    european = bool(txt.str.contains(r",\d{1,2}(?:\D|$)", regex=True, na=False).any())

    def fix(v):
        t = str(v).strip()
        if t == "" or t.lower() == "nan":
            return None
        if european:
            return t.replace(".", "").replace(",", ".")
        return t.replace(",", "")

    return pd.to_numeric(txt.map(fix), errors="coerce").fillna(0)


def _parse_excel(data: bytes, filename: str) -> pd.DataFrame:
    """Return a DataFrame with normalized columns: sku, qty, loc (location
    code, may be empty for SI/AT-style aggregate files). Accepts .xlsx/.xls
    AND .csv (emailed daily stock exports)."""
    raw: Optional[pd.DataFrame] = None

    if filename.lower().endswith(".csv"):
        # CSV: sniff delimiter (Croatian exports often ';') + tolerate a title
        # block by trying header offsets, same detection as the Excel path.
        for sep in (";", ",", "\t"):
            for header_row in range(0, 6):
                try:
                    df = pd.read_csv(io.BytesIO(data), sep=sep, header=header_row,
                                     dtype=str, encoding="utf-8-sig",
                                     engine="python", on_bad_lines="skip")
                    if df.shape[1] < 2:
                        continue
                    if _find_col(df.columns, SKU_NAMES) and _find_col(df.columns, QTY_NAMES):
                        raw = df
                        break
                except Exception:
                    continue
            if raw is not None:
                break
    else:
        # Try the first sheet that looks like data (skip Sheet1..N if present)
        import openpyxl
        wb = openpyxl.load_workbook(io.BytesIO(data), read_only=True)
        sheet_names = wb.sheetnames
        wb.close()

        target = None
        for sn in sheet_names:
            if sn.lower().startswith("sheet"):
                continue
            target = sn
            break
        if target is None:
            target = sheet_names[0]

        # Try header rows 0..5 — some ERP exports have a title block
        for header_row in range(0, 6):
            try:
                df = pd.read_excel(io.BytesIO(data), sheet_name=target, header=header_row)
                if _find_col(df.columns, SKU_NAMES) and _find_col(df.columns, QTY_NAMES):
                    raw = df
                    break
            except Exception:
                continue

    if raw is None:
        raise ValueError(
            f"Could not detect SKU + qty columns in {filename}. "
            f"Need columns matching 'Šifra' + 'Zaliha' (or sku/on_hand)."
        )

    sku_col = _find_col(raw.columns, SKU_NAMES)
    qty_col = _find_col(raw.columns, QTY_NAMES)
    loc_col = _find_col(raw.columns, LOC_NAMES)

    out = pd.DataFrame()
    out["sku"] = raw[sku_col].astype(str).fillna("").str.strip()
    out["qty"] = _parse_stock_qty(raw[qty_col])
    if loc_col is not None:
        out["loc"] = (raw[loc_col].astype(str).fillna("")
                        .str.strip().str.lstrip("0").replace("", "0"))
        # Reconstruct: '1' or '01' both → '01' for HR WH detection
        out["loc_raw"] = raw[loc_col].astype(str).fillna("").str.strip()
    else:
        out["loc"] = ""
        out["loc_raw"] = ""

    # Drop empty SKUs
    out = out[out["sku"] != ""]
    # Drop pseudo-SKUs (AMB/OST/MKT/CARD/WOO/USL/MSM/WOLTD)
    out = _drop_pseudo_skus(out)
    return out


def _aggregate_stores(df: pd.DataFrame, wh_loc_codes: set[str]) -> pd.DataFrame:
    """Sum qty per SKU across all non-WH locations."""
    if df.empty:
        return pd.DataFrame(columns=["sku", "on_hand"])
    if "loc_raw" in df.columns and df["loc_raw"].any():
        mask = ~df["loc_raw"].isin(wh_loc_codes)
        stores = df[mask]
    else:
        stores = df
    agg = stores.groupby("sku", as_index=False)["qty"].sum()
    agg = agg.rename(columns={"qty": "on_hand"})
    return agg[agg["on_hand"] > 0]


def _wh_only(df: pd.DataFrame, wh_loc_codes: set[str]) -> pd.DataFrame:
    """Return WH-only rows (HR location '01' / '1' / 'Warehouse')."""
    if "loc_raw" not in df.columns:
        return pd.DataFrame(columns=["sku", "on_hand"])
    mask = df["loc_raw"].isin(wh_loc_codes)
    wh = df[mask]
    agg = wh.groupby("sku", as_index=False)["qty"].sum()
    agg = agg.rename(columns={"qty": "on_hand"})
    return agg[agg["on_hand"] > 0]


# HR codes that mean "central warehouse"
_HR_WH_LOC_CODES = {"01", "1", "001", "Warehouse", "WAREHOUSE", "warehouse", "WH"}


def process_stock_upload(
    db: Session,
    payload: list[tuple[str, str, bytes]],
) -> dict:
    """payload = [(country, filename, bytes), ...].

    Country is the explicit tag the user picked in the UI (HR/SI/AT).
    Each country's CSV is overwritten in place; countries not in the
    payload are left as-is, so partial uploads are safe.
    """
    DATA.mkdir(parents=True, exist_ok=True)
    file_results: list[dict] = []
    warnings: list[str] = []
    csvs_written: list[str] = []

    for (country, filename, blob) in payload:
        country_u = country.upper().strip()
        if country_u not in COUNTRY_MAP:
            warnings.append(f"Unknown country '{country}' for {filename} — skipped")
            continue
        cfg = COUNTRY_MAP[country_u]
        try:
            df = _parse_excel(blob, filename)
        except Exception as e:
            file_results.append({
                "country": country_u, "filename": filename,
                "rows_parsed": 0, "wh_skus": 0, "store_skus": 0,
                "error": str(e),
            })
            continue

        if country_u == "HR":
            wh_df     = _wh_only(df, _HR_WH_LOC_CODES)
            stores_df = _aggregate_stores(df, _HR_WH_LOC_CODES)
        else:
            # SI / AT — no separate WH; everything is "stores aggregate"
            wh_df = pd.DataFrame(columns=["sku", "on_hand"])
            stores_df = _aggregate_stores(df, set())

        if cfg["wh_csv"] and not wh_df.empty:
            (DATA / cfg["wh_csv"]).write_text(
                wh_df.to_csv(index=False), encoding="utf-8",
            )
            csvs_written.append(cfg["wh_csv"])
        if cfg["stores_csv"] and not stores_df.empty:
            (DATA / cfg["stores_csv"]).write_text(
                stores_df.to_csv(index=False), encoding="utf-8",
            )
            csvs_written.append(cfg["stores_csv"])

        file_results.append({
            "country": country_u, "filename": filename,
            "rows_parsed": int(len(df)),
            "wh_skus":     int(len(wh_df)),
            "store_skus":  int(len(stores_df)),
            "wh_units":    float(wh_df["on_hand"].sum())     if not wh_df.empty else 0.0,
            "store_units": float(stores_df["on_hand"].sum()) if not stores_df.empty else 0.0,
            "error": None,
        })

    # ---- Refresh erp_stock_current via the existing CSV loader ----
    n_rows = 0
    db_error: Optional[str] = None
    if csvs_written:
        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)
                n_rows = mr._load_stock_current(conn, maps)
                conn.commit()
            finally:
                conn.close()
        except Exception as e:
            db_error = str(e)
            warnings.append(f"DB reload failed: {e}")

    return {
        "n_files": len(payload),
        "files": file_results,
        "csvs_written": csvs_written,
        "rows_inserted_to_db": n_rows,
        "db_error": db_error,
        "warnings": warnings,
    }