"""Load per-buyer listed assortments into wholesale_listings.

Two source workbooks, different shapes:

  data/FMCG artikli HR.xlsx          → KAM 'Selma'
    sheets: Konzum, Spar, Dm, Bipa   (one buyer per sheet)
    columns: Šifra (SKU), Barcode, Naziv, Količina, % RUC, RUC €
    (Total* sheets are rollups — skipped)

  data/SLO kupci - FMCG rangiranje GOLD, SILVER, BRONZE - final list.xlsx → KAM 'Patrik'
    sheets: Mercator, SPAR
    row 0 is the header (SKU / Name / FINAL title); data starts row 1
    rank = FINAL title (gold/silver/bronze)

Matches SKU → dim_products.id; unmatched rows still load (product_id NULL)
so coverage gaps are visible. Idempotent: wipes + reloads each run.

Usage: python db/load_wholesale_listings.py
"""
import sys
sys.stdout.reconfigure(encoding="utf-8")

import pandas as pd
from sqlalchemy import text

from backend.models.database import SessionLocal

FMCG_FILE = "data/FMCG artikli HR.xlsx"
FMCG_BUYER_SHEETS = ["Konzum", "Spar", "Dm", "Bipa"]
SLO_FILE = "data/SLO kupci - FMCG rangiranje GOLD, SILVER, BRONZE - final list.xlsx"
SLO_BUYER_SHEETS = ["Mercator", "SPAR"]

SKU_RE = r"^[A-Za-z]{2,5}\d{3,}"   # POL04229, CEL11615, ZOE12372, …

# The Excel sheet labels ("SPAR", "Spar") are generic, but KAMs submit their
# on-tops under the full retailer name (and the same chain splits by market:
# SPAR Slovenia vs SPAR Croatia). Map sheet → canonical buyer name so the
# listed assortment lines up with on_top_inputs.buyer (else nothing pre-fills
# in the KAM input grid). Keyed by (kam, sheet-derived buyer).
BUYER_CANONICAL = {
    ("Patrik", "SPAR"): "Spar Ljubljana",
    ("Selma",  "Spar"): "Spar Hrvatska",
}


def _rows_from_fmcg() -> list[dict]:
    out: list[dict] = []
    for sheet in FMCG_BUYER_SHEETS:
        df = pd.read_excel(FMCG_FILE, sheet_name=sheet)
        if "Šifra" not in df.columns:
            continue
        for sku in df["Šifra"].dropna().astype(str).str.strip():
            if pd.isna(sku) or not sku:
                continue
            out.append({"kam": "Selma", "buyer": sheet, "sku": sku,
                        "rank": None, "source": "FMCG artikli HR.xlsx"})
    return out


def _rows_from_slo() -> list[dict]:
    out: list[dict] = []
    for sheet in SLO_BUYER_SHEETS:
        # Header is in the first data row; re-read with header=1.
        df = pd.read_excel(SLO_FILE, sheet_name=sheet, header=1)
        # Expect columns: SKU, Name, FINAL title
        cols = {c.lower().strip(): c for c in df.columns.astype(str)}
        sku_col = cols.get("sku") or df.columns[0]
        rank_col = cols.get("final title")
        for _, r in df.iterrows():
            sku = str(r[sku_col]).strip() if pd.notna(r[sku_col]) else ""
            if not sku or sku.lower() == "nan":
                continue
            rank = None
            if rank_col is not None and pd.notna(r[rank_col]):
                rank = str(r[rank_col]).strip().lower() or None
            out.append({"kam": "Patrik", "buyer": sheet, "sku": sku,
                        "rank": rank, "source": "SLO kupci final list.xlsx"})
    return out


def main() -> None:
    rows = _rows_from_fmcg() + _rows_from_slo()
    # Normalize sheet labels to canonical buyer names (see BUYER_CANONICAL).
    for r in rows:
        r["buyer"] = BUYER_CANONICAL.get((r["kam"], r["buyer"]), r["buyer"])
    # Keep only SKU-shaped codes
    rows = [r for r in rows if pd.Series([r["sku"]]).str.match(SKU_RE).iloc[0]]
    print(f"Parsed {len(rows)} listing rows")

    db = SessionLocal()
    try:
        # Resolve SKU → product_id
        skus = sorted({r["sku"] for r in rows})
        pid_map = {
            s: pid for s, pid in db.execute(text(
                "SELECT sku, id FROM dim_products WHERE sku = ANY(:s)"
            ), {"s": skus}).fetchall()
        }
        matched = sum(1 for r in rows if r["sku"] in pid_map)
        print(f"SKUs: {len(skus)}  matched to dim_products: {len(pid_map)}  "
              f"rows matched: {matched}/{len(rows)}")

        db.execute(text("TRUNCATE wholesale_listings RESTART IDENTITY"))
        inserted = 0
        for r in rows:
            db.execute(text("""
                INSERT INTO wholesale_listings
                    (kam, buyer, buyer_lc, product_id, sku, rank, source_file)
                VALUES (:kam, :buyer, LOWER(TRIM(:buyer)), :pid, :sku, :rank, :src)
                ON CONFLICT (kam, buyer_lc, sku) DO NOTHING
            """), {"kam": r["kam"], "buyer": r["buyer"],
                    "pid": pid_map.get(r["sku"]), "sku": r["sku"],
                    "rank": r["rank"], "src": r["source"]})
            inserted += 1
        db.commit()

        summary = db.execute(text("""
            SELECT kam, buyer, COUNT(*) n,
                   COUNT(*) FILTER (WHERE product_id IS NULL) AS unmatched
            FROM wholesale_listings GROUP BY kam, buyer ORDER BY kam, buyer
        """)).fetchall()
        print("\nLoaded:")
        for kam, buyer, n, unm in summary:
            print(f"  {kam:<8s} {buyer:<10s} {n:>4d} SKUs ({unm} unmatched)")
    finally:
        db.close()


if __name__ == "__main__":
    main()