"""Refill missing names + load DataLink from SifrarnikArtikala.

Why:
  • 40k+ SKUs in dim_products were auto-discovered (sku-only) and have
    no name. The Sifrarnik master has the canonical names.
  • Sifrarnik also has a `DataLink` integer that counts up as articles
    are created — higher = newer. We store it on dim_products so the
    finance analyses can recognise newly-listed SKUs (top 10k by
    DataLink) and exclude them from "dead stock" classification when
    they have no sales.

Idempotent. Safe to re-run after a fresh Sifrarnik upload.
"""
from __future__ import annotations

from pathlib import Path

import pandas as pd
from sqlalchemy import text

from backend.models.database import SessionLocal

ROOT = Path(__file__).resolve().parents[1]
SIF  = ROOT / "data" / "SifrarnikArtikala.xlsx"


def main() -> None:
    if not SIF.exists():
        raise SystemExit(f"missing {SIF}")

    print("Step 1 — read Sifrarnik")
    df = pd.read_excel(SIF, sheet_name="SifrarnikArtikala")
    df = df.rename(columns={
        "Šifra": "sku",
        "Naziv artikla/usluge": "name",
        "DataLink": "datalink",
        "Item name": "name_en",
    })
    df["sku"] = df["sku"].astype(str).str.strip()
    df["name"] = df["name"].fillna("").astype(str).str.strip()
    df["name_en"] = df["name_en"].fillna("").astype(str).str.strip()
    # Prefer Croatian Naziv, fall back to English Item name
    df["best_name"] = df["name"].where(df["name"] != "", df["name_en"])
    df["datalink"] = pd.to_numeric(df["datalink"], errors="coerce").astype("Int64")
    df = df[df["sku"] != ""].drop_duplicates(subset="sku", keep="first")
    print(f"  {len(df):,} unique SKUs in Sifrarnik")
    print(f"  {(df['best_name'] != '').sum():,} have a usable name")
    print(f"  datalink range: {int(df['datalink'].min())} … {int(df['datalink'].max())}")

    db = SessionLocal()
    try:
        # Step 2 — ensure datalink column exists
        print("\nStep 2 — ensure dim_products.datalink column")
        db.execute(text("""
            ALTER TABLE dim_products
            ADD COLUMN IF NOT EXISTS datalink INTEGER
        """))
        db.execute(text("""
            CREATE INDEX IF NOT EXISTS idx_dim_products_datalink
            ON dim_products(datalink)
        """))
        db.commit()

        # Step 3 — UPDATE names + datalink in batches
        print("\nStep 3 — UPDATE dim_products from Sifrarnik")
        # Build a temp table from the upload and join — much faster than
        # 50k individual UPDATEs.
        db.execute(text("DROP TABLE IF EXISTS _sif_upload"))
        db.execute(text("""
            CREATE TEMP TABLE _sif_upload (
                sku VARCHAR PRIMARY KEY,
                best_name TEXT,
                datalink INTEGER
            )
        """))
        # COPY in batches
        rows = list(df[["sku", "best_name", "datalink"]].itertuples(index=False, name=None))
        # psycopg2 execute_values is faster but plain executemany is fine for 50k
        ins = text("INSERT INTO _sif_upload (sku, best_name, datalink) VALUES (:sku, :n, :d)")
        for i in range(0, len(rows), 5000):
            chunk = rows[i:i+5000]
            db.execute(ins, [{"sku": s, "n": n or None,
                              "d": (int(d) if pd.notna(d) else None)}
                             for (s, n, d) in chunk])
        db.commit()
        print(f"  staged {len(rows):,} rows")

        # Update names where missing or empty
        n_name = db.execute(text("""
            UPDATE dim_products p
            SET name = u.best_name
            FROM _sif_upload u
            WHERE p.sku = u.sku
              AND u.best_name IS NOT NULL AND u.best_name <> ''
              AND (p.name IS NULL OR TRIM(p.name) = '')
        """)).rowcount
        db.commit()
        print(f"  names filled in:  {n_name:,}")

        # Update datalink for every matched SKU (overwrite — Sifrarnik is canonical)
        n_dl = db.execute(text("""
            UPDATE dim_products p
            SET datalink = u.datalink
            FROM _sif_upload u
            WHERE p.sku = u.sku
              AND u.datalink IS NOT NULL
        """)).rowcount
        db.commit()
        print(f"  datalink updated: {n_dl:,}")

        # Step 4 — stats after
        print("\nStep 4 — verify")
        n_total = db.execute(text("SELECT COUNT(*) FROM dim_products")).scalar()
        n_named = db.execute(text(
            "SELECT COUNT(*) FROM dim_products WHERE name IS NOT NULL AND TRIM(name) <> ''"
        )).scalar()
        n_dl_total = db.execute(text(
            "SELECT COUNT(*) FROM dim_products WHERE datalink IS NOT NULL"
        )).scalar()
        mn_dl, mx_dl = db.execute(text(
            "SELECT MIN(datalink), MAX(datalink) FROM dim_products WHERE datalink IS NOT NULL"
        )).first()
        # Threshold = top 10k by datalink (= "newly listed")
        threshold = db.execute(text("""
            SELECT datalink FROM dim_products
            WHERE datalink IS NOT NULL
            ORDER BY datalink DESC
            OFFSET 9999 LIMIT 1
        """)).scalar()
        print(f"  total SKUs:       {n_total:,}")
        print(f"  with name:        {n_named:,}  ({n_named/n_total*100:.1f}%)")
        print(f"  with datalink:    {n_dl_total:,}")
        print(f"  datalink range:   {mn_dl} … {mx_dl}")
        print(f"  top-10k threshold: datalink >= {threshold}")
    finally:
        db.close()


if __name__ == "__main__":
    main()