"""Load data/NPD.xlsx into the npd_products table.

Idempotent: applies the schema (DROP+CREATE) then INSERTs all rows.
Run manually each time Lovro replaces NPD.xlsx:

    python db/load_npd.py

The launch month is parsed from the "Okvirno vrijeme dolaska" column,
which contains EITHER:
  - An Excel date (number) — interpreted as a date, kept at month resolution
  - A string of the form "MM/YY" (e.g. "10/25" for Oct 2025)
  - "NOVO!" / blank — left NULL

Other rows are taken verbatim; numeric fields are coerced and NaN → NULL.
"""
from __future__ import annotations

import math
import re
from datetime import date, datetime, timedelta
from pathlib import Path

import pandas as pd
from sqlalchemy import text

from backend.models.database import SessionLocal

ROOT      = Path(__file__).resolve().parents[1]
SCHEMA    = Path(__file__).parent / "npd_schema.sql"
NPD_XLSX  = ROOT / "data" / "NPD.xlsx"

# Excel epoch (Windows): 1900-01-00 is "day 0"; day 60 is a fake leap year.
EXCEL_EPOCH = datetime(1899, 12, 30)


def parse_launch_month(value) -> date | None:
    """Return a DATE on the first of the month, or None."""
    if value is None:
        return None
    if isinstance(value, float) and math.isnan(value):
        return None
    if isinstance(value, (datetime, date)):
        d = value if isinstance(value, date) else value.date()
        return date(d.year, d.month, 1)
    if isinstance(value, (int, float)):
        try:
            d = (EXCEL_EPOCH + timedelta(days=float(value))).date()
            return date(d.year, d.month, 1)
        except (OverflowError, ValueError):
            return None
    s = str(value).strip()
    if not s or s.upper() in {"NOVO!", "TBD", "N/A", "-"}:
        return None
    m = re.match(r"^(\d{1,2})\s*[/.-]\s*(\d{2,4})$", s)
    if m:
        mm = int(m.group(1))
        yy = int(m.group(2))
        if yy < 100:
            yy += 2000
        try:
            return date(yy, mm, 1)
        except ValueError:
            return None
    # Try ISO-like
    try:
        dt = datetime.fromisoformat(s)
        return date(dt.year, dt.month, 1)
    except ValueError:
        return None


def _num(value, kind=float):
    if value is None:
        return None
    if isinstance(value, float) and math.isnan(value):
        return None
    if isinstance(value, str):
        s = value.strip().replace(",", ".")
        if not s or s in {"-", "—"}:
            return None
        try:
            return kind(s)
        except ValueError:
            return None
    try:
        return kind(value)
    except (TypeError, ValueError):
        return None


def _txt(value) -> str | None:
    if value is None:
        return None
    if isinstance(value, float) and math.isnan(value):
        return None
    s = str(value).strip()
    return s or None


def main() -> None:
    if not NPD_XLSX.exists():
        raise SystemExit(f"NPD.xlsx not found at {NPD_XLSX}")

    print(f"Reading {NPD_XLSX} …")
    df = pd.read_excel(NPD_XLSX)
    print(f"  {len(df)} rows")

    db = SessionLocal()
    try:
        print(f"Applying schema from {SCHEMA} …")
        db.execute(text(SCHEMA.read_text(encoding="utf-8")))
        db.commit()

        n_inserted = 0
        for _, r in df.iterrows():
            sku = _txt(r.get("ŠIFRA"))
            name = _txt(r.get("PROIZVOD")) or _txt(r.get("Naziv ERP i WEB"))
            if not sku or not name:
                continue
            db.execute(text("""
                INSERT INTO npd_products
                  (sku, name, manufacturer, brand, launch_month, arrived,
                   barcode, neto, packaging, cost_price,
                   msrp_hr, msrp_si, msrp_at, moq)
                VALUES
                  (:sku, :name, :mfr, :brand, :launch, :arrived,
                   :barcode, :neto, :pack, :cost,
                   :hr, :si, :at, :moq)
                ON CONFLICT (sku) DO UPDATE SET
                  name = EXCLUDED.name,
                  manufacturer = EXCLUDED.manufacturer,
                  brand = EXCLUDED.brand,
                  launch_month = EXCLUDED.launch_month,
                  arrived = EXCLUDED.arrived,
                  barcode = EXCLUDED.barcode,
                  neto = EXCLUDED.neto,
                  packaging = EXCLUDED.packaging,
                  cost_price = EXCLUDED.cost_price,
                  msrp_hr = EXCLUDED.msrp_hr,
                  msrp_si = EXCLUDED.msrp_si,
                  msrp_at = EXCLUDED.msrp_at,
                  moq = EXCLUDED.moq,
                  updated_at = NOW()
            """), {
                "sku":     sku,
                "name":    name,
                "mfr":     _txt(r.get("PROIZVOĐAČ")),
                "brand":   _txt(r.get("BREND")),
                "launch":  parse_launch_month(r.get("Okvirno vrijeme dolaska")),
                "arrived": bool(r.get("Stiglo", False)),
                "barcode": _txt(r.get("BARKOD")),
                "neto":    _txt(r.get("NETO")),
                "pack":    _txt(r.get("TIP PAKIRANJA")),
                "cost":    _num(r.get("N.C.")),
                "hr":      _num(r.get("MPC HR")),
                "si":      _num(r.get("MPC SI")),
                "at":      _num(r.get("MPC AT")),
                "moq":     _txt(r.get("MOQ")),
            })
            n_inserted += 1
        db.commit()
        print(f"Inserted/updated {n_inserted} NPD rows.")

        # Sanity print
        rows = db.execute(text("""
            SELECT COUNT(*) AS n,
                   SUM(CASE WHEN arrived THEN 1 ELSE 0 END) AS arrived,
                   MIN(launch_month) AS earliest,
                   MAX(launch_month) AS latest
            FROM npd_products
        """)).mappings().first()
        print(f"  total={rows['n']} arrived={rows['arrived']} "
              f"earliest={rows['earliest']} latest={rows['latest']}")
    finally:
        db.close()


if __name__ == "__main__":
    main()