"""Convert the GATH NC30 export ('PregledProvjeraNNC30.xlsx') into
data/nc30.csv that PromoTool's check_nc30() consumes.

The GATH layout: col A = Artikal (SKU), col R = "Najniža cijena 30 dana".
Many rows have NC30 = 0 (SKU had no recent promo / no reference) — those
are dropped so the check returns 'no data' for them rather than '€0 ceiling'.

Run from PromoTool/ whenever GATH gives you a new file:
    python gath_to_nc30.py
"""
from __future__ import annotations

from pathlib import Path
import sys

import openpyxl
import pandas as pd

HERE = Path(__file__).parent
DATA_DIR = HERE.parent / "data"
OUT = DATA_DIR / "nc30.csv"

# Find the most recent GATH NC30 file in this folder
candidates = sorted(HERE.glob("Pregled*nc30*.xlsx"), key=lambda p: p.stat().st_mtime, reverse=True)
if not candidates:
    candidates = sorted(HERE.glob("*nc30*.xlsx"), key=lambda p: p.stat().st_mtime, reverse=True)
if not candidates:
    print("[error] No 'Pregled*nc30*.xlsx' file found in PromoTool/.")
    print("        Drop the GATH export here and re-run.")
    sys.exit(1)

src = candidates[0]
print(f"Reading: {src.name}")

wb = openpyxl.load_workbook(src, data_only=True, read_only=True)
ws = wb.active

# Walk rows. Col A = SKU, col R (18) = NC30.
rows = []
seen_sku = set()
n_zero = 0
n_dup = 0
for row_idx, row in enumerate(ws.iter_rows(min_row=2, values_only=True), start=2):
    if not row or len(row) < 18:
        continue
    sku_raw = row[0]
    nc30_raw = row[17]
    if sku_raw in (None, ""):
        continue
    sku = str(sku_raw).strip()
    if not sku:
        continue
    try:
        nc30 = float(nc30_raw or 0)
    except (TypeError, ValueError):
        nc30 = 0
    if nc30 <= 0:
        n_zero += 1
        continue
    # Keep first occurrence; if same SKU appears again with different NC30,
    # take the LOWER (most conservative — never run promo above ANY observed
    # 30-day low). GATH sometimes has multiple rows per SKU per rabatna polit.
    if sku in seen_sku:
        n_dup += 1
        # Update with lower of the two
        for i, r in enumerate(rows):
            if r["sku"] == sku and r["nc30_price"] > nc30:
                rows[i]["nc30_price"] = nc30
                break
        continue
    seen_sku.add(sku)
    rows.append({"sku": sku, "nc30_price": round(nc30, 2)})

wb.close()

DATA_DIR.mkdir(parents=True, exist_ok=True)
df = pd.DataFrame(rows)
df.to_csv(OUT, index=False)

print(f"Wrote: {OUT}")
print(f"  Source rows scanned: {ws.max_row - 1:,}")
print(f"  SKUs with NC30:      {len(df):,}")
print(f"  Skipped (NC30=0):    {n_zero:,}")
print(f"  Duplicate rows merged (kept lowest): {n_dup:,}")
print()
print(f"NC30 check in PromoTool is now live for {len(df):,} SKUs.")
print("Re-run this script whenever GATH gives a new export.")