"""Reload incoming_supply table from `data/incoming supply po tjednima.xlsx`.

The file is wide-format: column 0 = `šifra artikla` (SKU), columns 1..N
= ISO weeks (21..33 etc). Values are units scheduled to arrive in that
week. Year is implicit — the current calendar year for any week >=
current week, previous year otherwise (handles year-end wrap).

Workflow:
  1. read xlsx, melt to long [sku, year, week, qty]
  2. write data/incoming_supply.csv (sku, year, week, qty, status)
  3. truncate + reinsert incoming_supply via the existing CSV loader

Idempotent.
"""
from __future__ import annotations

from datetime import date
from pathlib import Path

import pandas as pd
from sqlalchemy import text

from backend.models.database import SessionLocal

ROOT = Path(__file__).resolve().parents[1]
DATA = ROOT / "data"
XLSX = DATA / "incoming supply po tjednima.xlsx"
CSV  = DATA / "incoming_supply.csv"


def _current_iso_yw() -> tuple[int, int]:
    iso = date.today().isocalendar()
    return iso[0], iso[1]


def load_and_melt() -> pd.DataFrame:
    print(f"  reading {XLSX.name} …")
    df = pd.read_excel(XLSX)
    print(f"    shape: {df.shape}")
    # First column is SKU; the rest are week numbers
    sku_col = df.columns[0]
    week_cols = list(df.columns[1:])
    long = df.melt(id_vars=[sku_col], value_vars=week_cols,
                   var_name="week", value_name="qty")
    long = long.rename(columns={sku_col: "sku"})
    long["sku"] = long["sku"].astype(str).str.strip()
    long["week"] = pd.to_numeric(long["week"], errors="coerce").astype("Int64")
    long["qty"] = pd.to_numeric(long["qty"], errors="coerce")

    before = len(long)
    long = long.dropna(subset=["sku", "week", "qty"])
    long = long[(long["sku"] != "") & (long["qty"] > 0)]
    print(f"    {before:,} rows after melt → {len(long):,} non-zero rows")

    # Year inference — current ISO year for weeks >= current week,
    # else next year (so a December-current-year run loading week 02
    # would tag it next year).
    cy, cw = _current_iso_yw()
    print(f"    current ISO {cy}W{cw:02d}")
    def _year(w: int) -> int:
        return cy if int(w) >= cw else cy + 1
    long["year"] = long["week"].astype(int).map(_year)
    long["status"] = None
    long = long[["sku", "year", "week", "qty", "status"]]
    long["qty"] = long["qty"].astype(float).round(2)
    return long.sort_values(["year", "week", "sku"]).reset_index(drop=True)


def write_csv(df: pd.DataFrame) -> None:
    df.to_csv(CSV, index=False)
    print(f"    wrote {CSV.name}  ({len(df):,} rows)")


def reload_db() -> int:
    print("  reloading incoming_supply table …")
    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)
        n = mr._load_incoming_supply(conn, maps)
        conn.commit()
        return n
    finally:
        conn.close()


def verify() -> None:
    db = SessionLocal()
    try:
        r = db.execute(text("""
            SELECT COUNT(*) AS rows,
                   COUNT(DISTINCT product_id) AS skus,
                   MIN(year * 100 + week) AS min_yw,
                   MAX(year * 100 + week) AS max_yw,
                   SUM(quantity)::float AS total_qty
            FROM incoming_supply
        """)).mappings().first()
        print(f"\n  DB after reload:")
        print(f"    rows:    {r['rows']:,}")
        print(f"    SKUs:    {r['skus']:,}")
        print(f"    span:    {r['min_yw']} → {r['max_yw']}")
        print(f"    total q: {r['total_qty']:,.0f}")
    finally:
        db.close()


def main() -> None:
    if not XLSX.exists():
        raise SystemExit(f"Missing {XLSX}")
    df = load_and_melt()
    if df.empty:
        raise SystemExit("No non-zero rows after melt — nothing to load")
    write_csv(df)
    n = reload_db()
    print(f"    inserted {n:,} rows")
    verify()


if __name__ == "__main__":
    main()