"""Set dim_products.name to the English article name from the Sifrarnik.

The product names in dim_products are a mix of languages — many came in
from the Slovenian/Croatian POS sales feed (e.g. "Vanilijev sladoled",
"Arašidovo Maslo brez koščkov"). The article master
(data/SifrarnikArtikala.xlsx) carries a clean English name in its
**"Item name"** column. This loader copies that English name onto
dim_products, matched by SKU (Šifra).

Rules:
  - Use "Item name" only (the English column). When it's blank we LEAVE
    the existing name untouched rather than downgrade to a local name —
    the goal is "only English," not "any name."
  - Match on SKU = Šifra (trimmed).
  - Idempotent: only writes rows whose name actually differs.

Re-run after a catalog refresh. (New SKUs created during a sales upload
still get the POS name initially — re-running this re-Englishes them.)

Usage: python db/load_product_names_en.py [--apply]
       (without --apply it's a dry run that only reports counts)
"""
import sys
sys.stdout.reconfigure(encoding="utf-8")

import pandas as pd
from sqlalchemy import text

from backend.models.database import SessionLocal

SIFRARNIK = "data/SifrarnikArtikala.xlsx"


def build_en_name_map() -> dict[str, str]:
    df = pd.read_excel(SIFRARNIK, sheet_name="SifrarnikArtikala",
                       usecols=["Šifra", "Item name"])
    df["Šifra"] = df["Šifra"].astype(str).str.strip()
    out: dict[str, str] = {}
    for _, r in df.iterrows():
        en = r["Item name"]
        if pd.isna(en):
            continue
        en = str(en).strip()
        if en:
            out[r["Šifra"]] = en
    return out


def main(apply: bool) -> None:
    name_map = build_en_name_map()
    print(f"Sifrarnik English names: {len(name_map)}")

    db = SessionLocal()
    try:
        rows = db.execute(text("SELECT id, sku, name FROM dim_products")).fetchall()
        to_update: list[tuple[int, str]] = []
        for pid, sku, cur in rows:
            en = name_map.get(str(sku).strip())
            if en and (cur or "").strip() != en:
                to_update.append((pid, en))

        print(f"dim_products: {len(rows)}  will update: {len(to_update)}")
        for pid, en in to_update[:10]:
            print(f"  id={pid} -> {en!r}")

        if not apply:
            print("\nDRY RUN — pass --apply to write.")
            return

        for pid, en in to_update:
            db.execute(text("UPDATE dim_products SET name = :n WHERE id = :id"),
                       {"n": en, "id": pid})
        db.commit()
        print(f"\nUpdated {len(to_update)} product names.")
    finally:
        db.close()


if __name__ == "__main__":
    main(apply="--apply" in sys.argv)