"""Rebuild sku_costs.csv + supply_master.csv from SifrarnikArtikala.xlsx.

Why this exists
---------------
The legacy sku_costs.csv was user-maintained via the Streamlit "Cost
prices" editor and only covered ~7,199 SKUs. Lots of SKUs had cost=0 or
missing, which is why the CFO audit fell back to realized cost from
erp_transactions for many products.

`SifrarnikArtikala.xlsx` is the ERP article master and carries:
  • NabCj      — current per-unit nabavna cijena for 44,785 SKUs
  • Dobavljač  — supplier code for every SKU
  • VPC / MPC  — wholesale + retail catalogue prices

`NabavneCijene.xlsx` is the receiving log (per receipt) — we use it for
cross-validation only, not as the canonical source.

This script writes:
  data/sku_costs.csv         (overwritten)
  data/supply_master.csv     (overwritten — merged with existing LT/MOQ)
  data/_sifrarnik_audit.csv  (validation report: NabCj vs receipts)

…then re-runs migrate_remaining for erp_costs + supply_master so the
DB picks up the new values.

Idempotent: safe to re-run.
"""
from __future__ import annotations

from pathlib import Path
import sys

import pandas as pd
import numpy as np
from sqlalchemy import text

from backend.models.database import SessionLocal

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

SIFRARNIK = DATA / "SifrarnikArtikala.xlsx"
NABAVNE   = DATA / "NabavneCijene.xlsx"
SKU_COSTS = DATA / "sku_costs.csv"
SUPPLY    = DATA / "supply_master.csv"
AUDIT     = DATA / "_sifrarnik_audit.csv"


# ── Step A: supplier name dictionary ──────────────────────────────────
def build_supplier_name_dict(db) -> dict[str, str]:
    """Code → full name. Pulls from existing dim_suppliers (where the
    name was stored as 'CODE Name…' previously) and from the current
    supply_master.csv strings. Falls back to '<code>' for unknown
    codes — these will surface as '(no name yet)' in the UI and Nabava
    can backfill."""
    out: dict[str, str] = {}
    rows = db.execute(text(
        "SELECT id, code, name FROM dim_suppliers"
    )).mappings().all()
    for r in rows:
        n = (r["name"] or "").strip()
        c = (r["code"] or "").strip()
        # If the name starts with a 5-char numeric code, that's the ERP code
        if len(n) >= 5 and n[:5].isdigit():
            out[n[:5]] = n
        if c and c[:5].isdigit():
            out.setdefault(c[:5], n if n else c)

    if SUPPLY.exists():
        sm = pd.read_csv(SUPPLY)
        for v in sm["supplier"].dropna().unique():
            v = str(v).strip()
            if len(v) >= 5 and v[:5].isdigit():
                out.setdefault(v[:5], v)
    print(f"  built supplier dictionary with {len(out)} codes")
    return out


# ── Step B: load Sifrarnik ────────────────────────────────────────────
def load_sifrarnik() -> pd.DataFrame:
    print(f"  reading {SIFRARNIK.name} …")
    df = pd.read_excel(SIFRARNIK, sheet_name="SifrarnikArtikala")
    df = df.rename(columns={
        "Šifra": "sku",
        "Naziv artikla/usluge": "name",
        "Status": "status",
        "Proizvođač": "manufacturer",
        "Dobavljač": "supplier_code",
        "VPC": "vpc_cat",
        "MPC": "mpc_cat",
        "NabCj": "nab_cj",
        "Minimalna količina narudžbe (MOQ)": "moq_master",
        "Pakiranje komada Karton": "pcs_per_carton",
        "Pakiranje komada Paleta": "pcs_per_pallet",
    })
    df["sku"] = df["sku"].astype(str).str.strip()
    df = df[df["sku"] != ""]
    df["supplier_code"] = (df["supplier_code"].fillna("")
                                                  .astype(str).str.strip())
    print(f"    {len(df):,} rows, {df['sku'].nunique():,} unique SKUs")
    print(f"    NabCj > 0: {(df['nab_cj'] > 0).sum():,}")
    print(f"    Status counts: {df['status'].value_counts().head(3).to_dict()}")
    return df


# ── Step C: write new sku_costs.csv ───────────────────────────────────
def write_sku_costs(sif: pd.DataFrame) -> tuple[int, int]:
    """Build sku_costs.csv from Sifrarnik NabCj.

    For each SKU: cost_price = NabCj from master. Existing erp_costs.ruc
    is preserved as a historical fallback (the live UI now derives RUC
    from erp_transactions, so this is only for the no-recent-sales case).
    """
    # Preserve any existing ruc values
    existing_ruc: dict[str, float] = {}
    if SKU_COSTS.exists():
        old = pd.read_csv(SKU_COSTS)
        if "ruc" in old.columns:
            for r in old.itertuples():
                if pd.notna(getattr(r, "ruc", None)):
                    existing_ruc[str(r.sku)] = float(r.ruc)

    out = (sif.dropna(subset=["nab_cj"])
              .query("nab_cj > 0")[["sku", "nab_cj"]]
              .drop_duplicates(subset="sku", keep="first")
              .rename(columns={"nab_cj": "cost_price"}))
    out["ruc"] = out["sku"].map(existing_ruc).fillna(0.0)
    out["cost_price"] = out["cost_price"].round(4)
    out["ruc"] = out["ruc"].round(4)
    out = out[["sku", "cost_price", "ruc"]].sort_values("sku")
    out.to_csv(SKU_COSTS, index=False)
    print(f"    wrote {SKU_COSTS}  ({len(out):,} rows)")
    return len(out), len(existing_ruc)


# ── Step D: write new supply_master.csv ───────────────────────────────
def write_supply_master(sif: pd.DataFrame, sup_dict: dict[str, str]) -> dict:
    """Merge: Sifrarnik supplier codes (broad) + existing LT/MOQ (sparse).

    For every SKU in Sifrarnik:
      supplier         = sup_dict[Dobavljač code]
      lead_time_weeks  = existing value if present, else blank
      moq              = existing supply_master value if present,
                          else Sifrarnik moq_master if > 0, else blank
    """
    existing = {}
    if SUPPLY.exists():
        ex_df = pd.read_csv(SUPPLY)
        for r in ex_df.itertuples():
            existing[str(r.sku)] = {
                "supplier": str(getattr(r, "supplier", "") or "").strip(),
                "lead_time_weeks": getattr(r, "lead_time_weeks", None),
                "moq": getattr(r, "moq", None),
            }
    print(f"    existing supply_master rows: {len(existing)}")

    rows = []
    matched_codes = 0
    no_code = 0
    for r in sif.itertuples():
        code = (r.supplier_code or "").strip()
        if not code:
            no_code += 1
            sup = ""
        else:
            sup = sup_dict.get(code, f"{code} (no name)")
            if code in sup_dict:
                matched_codes += 1
        prev = existing.get(r.sku, {})
        lt = prev.get("lead_time_weeks")
        moq = prev.get("moq")
        # If existing.moq is missing AND Sifrarnik has it, use Sifrarnik
        if (moq is None or (isinstance(moq, float) and pd.isna(moq))) \
                and r.moq_master is not None \
                and not (isinstance(r.moq_master, float) and pd.isna(r.moq_master)) \
                and float(r.moq_master) > 0:
            moq = float(r.moq_master)
        rows.append({
            "sku": r.sku,
            "supplier": sup,
            "lead_time_weeks": lt if (lt is not None and not (isinstance(lt, float) and pd.isna(lt))) else "",
            "moq": moq if (moq is not None and not (isinstance(moq, float) and pd.isna(moq))) else 0,
        })

    out = pd.DataFrame(rows).drop_duplicates(subset="sku", keep="first")
    out = out.sort_values("sku")
    out.to_csv(SUPPLY, index=False)
    print(f"    wrote {SUPPLY}  ({len(out):,} rows)")
    return {
        "rows": len(out),
        "with_supplier": int((out["supplier"] != "").sum()),
        "with_lt":       int((out["lead_time_weeks"] != "").sum()),
        "matched_codes": matched_codes,
        "no_code": no_code,
    }


# ── Step E: cross-check NabCj vs realized receipts ────────────────────
def cross_check_realized(sif: pd.DataFrame) -> None:
    """Compare Sifrarnik NabCj vs realized cost from NabavneCijene
    (Nabavna vrijednost € / Količina, aggregated per SKU)."""
    if not NABAVNE.exists():
        return
    nab = pd.read_excel(NABAVNE)
    nab = nab.rename(columns={
        "Šifra": "sku",
        "Količina": "qty",
        "Nabavna vrijednost €": "nab_eur",
    })
    nab["sku"] = nab["sku"].astype(str).str.strip()
    agg = (nab.groupby("sku", as_index=False)
              .agg(qty=("qty", "sum"), nab_eur=("nab_eur", "sum")))
    agg = agg[agg["qty"] > 0]
    agg["realized_unit_cost"] = (agg["nab_eur"] / agg["qty"]).round(4)
    merged = (sif[["sku", "nab_cj"]]
              .dropna(subset=["nab_cj"])
              .merge(agg[["sku", "qty", "realized_unit_cost"]], on="sku",
                     how="inner"))
    merged["delta_eur"] = (merged["realized_unit_cost"] - merged["nab_cj"]).round(4)
    merged["delta_pct"] = ((merged["realized_unit_cost"] / merged["nab_cj"] - 1) * 100).round(2)
    merged = merged[merged["nab_cj"] > 0]
    merged = merged.sort_values("delta_pct", key=abs, ascending=False)
    merged.to_csv(AUDIT, index=False)
    print(f"    cross-check vs receipts: {len(merged):,} SKUs both sources")
    if len(merged):
        print(f"      median delta: {merged['delta_pct'].abs().median():.1f}%")
        big = merged[merged["delta_pct"].abs() > 20]
        print(f"      SKUs with |delta| > 20%: {len(big):,}")
    print(f"    wrote {AUDIT}")


# ── Step F: trigger DB reload ─────────────────────────────────────────
def run_migrate() -> None:
    """Re-run the cost+supply pieces of migrate_remaining.

    We don't shell out; we import and call the loaders directly. The
    script truncates the target tables, so this is a clean reload."""
    print("  reloading erp_costs + supply_master into DB …")
    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)
        print(f"    products={len(maps['product']):,}  suppliers={len(maps['supplier']):,}")
        n_sm = mr._load_supply_master(conn, maps)
        conn.commit()
        print(f"    supply_master: {n_sm:,} rows inserted")
        # Reload maps so new suppliers discovered above are picked up
        maps = mr._load_lookup_maps(conn)
        n_costs = mr._load_costs(conn, maps)
        conn.commit()
        print(f"    erp_costs:     {n_costs:,} rows inserted")
    finally:
        conn.close()


def verify_db() -> None:
    db = SessionLocal()
    try:
        n_costs = db.execute(text("SELECT COUNT(*) FROM erp_costs")).scalar()
        n_costs_pos = db.execute(text("SELECT COUNT(*) FROM erp_costs WHERE cost_price > 0")).scalar()
        n_sm = db.execute(text("SELECT COUNT(*) FROM supply_master")).scalar()
        n_sm_sup = db.execute(text("SELECT COUNT(*) FROM supply_master WHERE supplier_id IS NOT NULL")).scalar()
        n_suppliers = db.execute(text("SELECT COUNT(*) FROM dim_suppliers")).scalar()
        print("\n=== DB verification ===")
        print(f"  erp_costs:     {n_costs:,} rows  ({n_costs_pos:,} with cost > 0)")
        print(f"  supply_master: {n_sm:,} rows  ({n_sm_sup:,} with supplier_id)")
        print(f"  dim_suppliers: {n_suppliers:,} rows")
    finally:
        db.close()


# ── Main ──────────────────────────────────────────────────────────────
def main() -> None:
    if not SIFRARNIK.exists():
        sys.exit(f"missing {SIFRARNIK}")

    db = SessionLocal()
    try:
        print("Step A — supplier name dictionary")
        sup_dict = build_supplier_name_dict(db)

        print("\nStep B — load SifrarnikArtikala")
        sif = load_sifrarnik()

        print("\nStep C — write new sku_costs.csv")
        n_costs, n_ruc = write_sku_costs(sif)
        print(f"    {n_costs:,} cost rows; {n_ruc:,} preserved ruc values")

        print("\nStep D — write new supply_master.csv")
        sm_stats = write_supply_master(sif, sup_dict)
        print(f"    matched supplier codes: {sm_stats['matched_codes']:,}; "
              f"with LT data: {sm_stats['with_lt']:,}")

        print("\nStep E — cross-check vs NabavneCijene")
        cross_check_realized(sif)
    finally:
        db.close()

    print("\nStep F — DB reload (truncate + insert)")
    run_migrate()
    verify_db()


if __name__ == "__main__":
    main()