"""Build data/sales_detailed.csv from the 3 country-specific Rekapitulacija
XLSX files in data/prodaja/.

Sources (May 2026 reload):
  - data/prodaja/prodajacro2mj.xlsx   (HR)  — column names use "€"
  - data/prodaja/prodajaslo2mj.xlsx   (SI)  — column names use "EUR"
  - data/prodaja/prodajaasutri2mjj.xlsx (AT) — column names use "EUR"

Output: data/sales_detailed.csv with the 31-column schema migrate_sales.py
expects, plus optional loyalty_kartica (enables has_loyalty detection).

Run from project root:
    python -m db.build_sales_csv_from_prodaja
"""
from __future__ import annotations

from pathlib import Path
import pandas as pd

ROOT = Path(__file__).resolve().parent.parent
SRC_DIR = ROOT / "data" / "prodaja"
OUT_CSV = ROOT / "data" / "sales_detailed.csv"

FILES = [
    ("prodajacro2mj.xlsx",    "cro", "€"),
    ("prodajaslo2mj.xlsx",    "slo", "EUR"),
    ("prodajaasutri2mjj.xlsx", "at",  "EUR"),
]


def _normalise(df: pd.DataFrame, source_country: str, suffix: str,
               source_file: str) -> pd.DataFrame:
    """Map Rekapitulacija XLSX column names to the loader's lower_snake_case
    schema. `suffix` is "€" for HR, "EUR" for SI/AT (only diff between the
    two formats)."""
    # Column rename map — same Croatian column names in all 3 files except
    # currency symbol/suffix.
    rename = {
        "Dokument":            "dokument",
        "Datum":               "date",
        "Partner":             "partner",
        "Naziv partnera":      "naziv_partnera",
        "Artikal":             "sku",
        "Naziv":               "naziv",
        "Proizvođač":          "proizvodjac",
        "Mj.Troška":           "mj_troska",
        "Naziv mjesta troška": "naziv_mj_troska",
        "Jedinica":            "jedinica",
        "Naziv jedinice":      "naziv_jedinice",
        "Tip dok.":            "tip_dok",
        "Kategorija artikla":  "kategorija_artikla",
        "Grupacija artikla":   "grupacija_artikla",
        "Naziv grupacije":     "naziv_grupacije",
        "PodKat":              "podkat",
        "Podkategorija":       "podkategorija",
        "Komercijalist":       "komercijalist",
        "Količina":            "kolicina",
        f"Nabavna vrijednost {suffix}": "nabavna_vrijednost_eur",
        f"RUC {suffix}":                 "ruc_eur",
        "% RUC":                         "ruc_pct",
        f"Porezna osnovica {suffix}":   "porezna_osnovica_eur",
        f"PDV {suffix}":                 "pdv_eur",
        f"Vrijednost {suffix}":          "vrijednost_eur",
        f"Odobreni rabat {suffix}":     "odobreni_rabat_eur",
        "Država":              "drzava",
        "Loyalty kartica":     "loyalty_kartica",
    }
    df = df.rename(columns=rename)

    # Add derived columns
    df["source_country"] = source_country
    df["source_file"]    = source_file
    df["date"]           = pd.to_datetime(df["date"], errors="coerce")
    df["year"]           = df["date"].dt.isocalendar().year
    df["week"]           = df["date"].dt.isocalendar().week
    df["date"]           = df["date"].dt.strftime("%Y-%m-%d")

    # Final 31-column schema migrate_sales.py expects, plus loyalty_kartica
    out_cols = [
        "source_country", "source_file", "date", "year", "week",
        "dokument", "partner", "naziv_partnera", "sku", "naziv",
        "proizvodjac", "mj_troska", "naziv_mj_troska", "jedinica",
        "naziv_jedinice", "tip_dok", "kategorija_artikla",
        "grupacija_artikla", "naziv_grupacije", "podkat",
        "podkategorija", "komercijalist", "kolicina",
        "nabavna_vrijednost_eur", "ruc_eur", "ruc_pct",
        "porezna_osnovica_eur", "pdv_eur", "vrijednost_eur",
        "odobreni_rabat_eur", "drzava", "loyalty_kartica",
    ]
    for col in out_cols:
        if col not in df.columns:
            df[col] = None
    return df[out_cols]


CUTOVER_DATE = "2026-03-30"  # earliest date covered by new XLSX files


def main() -> None:
    # 1. Keep historical rows from existing CSV (date < cutover) so nothing
    #    pre-cutover is lost. The new XLSX exports only go back to 2026-03-30.
    print(f"Reading existing {OUT_CSV.name} (will keep rows < {CUTOVER_DATE}) ...")
    old = pd.read_csv(OUT_CSV, encoding="utf-8", low_memory=False)
    old["date"] = pd.to_datetime(old["date"], errors="coerce")
    keep_mask = old["date"] < CUTOVER_DATE
    kept = old[keep_mask].copy()
    print(f"  total old rows: {len(old):>7,}  "
          f"kept (< {CUTOVER_DATE}): {len(kept):>7,}  "
          f"discarded: {len(old) - len(kept):>7,}")
    kept["date"] = kept["date"].dt.strftime("%Y-%m-%d")

    # 2. Read + normalise the 3 new files (cover 2026-03-30 → today)
    frames = [kept]
    for fname, country, suffix in FILES:
        src = SRC_DIR / fname
        print(f"\nReading {fname} ({country.upper()}) ...")
        df = pd.read_excel(src, sheet_name="po_DOKUMENTU___PARTNERU___ARTI")
        print(f"  raw rows: {len(df):>7,}")
        df = _normalise(df, country, suffix, fname)
        # Sanity: drop any rows < CUTOVER (shouldn't happen but defensive)
        before = len(df)
        df = df[df["date"] >= CUTOVER_DATE]
        if len(df) < before:
            print(f"  dropped {before - len(df)} rows < {CUTOVER_DATE}")
        print(f"  normalised: {len(df):>7,} rows, "
              f"RUC sum {df['ruc_eur'].sum():>12,.0f}")
        frames.append(df)

    combined = pd.concat(frames, ignore_index=True)
    print(f"\nCombined total: {len(combined):,} rows  "
          f"RUC sum {combined['ruc_eur'].sum():,.0f}")
    print(f"Date range: {combined['date'].min()} to {combined['date'].max()}")

    print(f"\nWriting {OUT_CSV} ...")
    combined.to_csv(OUT_CSV, index=False, encoding="utf-8")
    print(f"  done — {OUT_CSV.stat().st_size / 1024 / 1024:.1f} MB")


if __name__ == "__main__":
    main()