pgz-sport/erp/ocr.py

#!/usr/bin/env python3
from __future__ import annotations
from dotenv import load_dotenv
load_dotenv('/opt/rinet-gpu/.env.master')
# auto-added by patch_scrapers_with_dotenv.sh
# erp/ocr.py — PGŽ Sport ERP OCR router (M5)
# Author: Damir Radulić <damir@rinet.one> / dradulic@outlook.com
# Date:   2026-05-04
# Description: /api/erp/ocr/upload + /parse — Tesseract OCR + DeepSeek V3 LLM extraction
#              Persists into pgz_sport.invoice_uploads, then offers structured invoice parse.


import os
import re
import json
import hashlib
import subprocess
import tempfile
import traceback
from datetime import datetime, date
from pathlib import Path
from typing import Optional, List, Any

import psycopg2
import psycopg2.extras
import requests
from fastapi import APIRouter, UploadFile, File, Form, HTTPException, Header, Query, Body
from fastapi.responses import JSONResponse, FileResponse

try:
    from erp.permissions import (
        can_view_invoice, can_edit_invoice, can_pay_invoice, can_comment_invoice,
        invoice_actions, audit_invoice, fetch_audit, is_pgz_admin,
    )
except Exception:
    # Fallback (always-allow) for unauth dev
    def can_view_invoice(u, i): return True
    def can_edit_invoice(u, i): return True
    def can_pay_invoice(u, i): return True
    def can_comment_invoice(u, i): return True
    def invoice_actions(u, i): return {"view": True, "edit": True, "pay": True, "comment": True, "delete": False}
    def audit_invoice(u, iid, op, field=None, old=None, new=None): pass
    def fetch_audit(t, r, limit=50): return []
    def is_pgz_admin(u): return False

try:
    from auth.auth_v2 import get_current_user as _auth_user
except Exception:
    _auth_user = None

try:
    from erp.notifications import (
        notify_invoice_created, notify_invoice_paid, notify_invoice_cancelled,
    )
except Exception:
    def notify_invoice_created(*a, **k): return {}
    def notify_invoice_paid(*a, **k): return {}
    def notify_invoice_cancelled(*a, **k): return {}

router = APIRouter(prefix="/api/erp", tags=["erp-ocr"])

# === Config ===
DB = dict(host="10.10.0.2", port=6432, dbname="rinet_v3", user="rinet",
          password=os.environ["DB_PASSWORD"])
UPLOAD_DIR = Path("/opt/pgz-sport/_data/uploads/invoices")
UPLOAD_DIR.mkdir(parents=True, exist_ok=True)

DEEPSEEK_API_KEY = os.environ['DEEPSEEK_API_KEY']  # /opt/rinet-gpu/.env.master  (ZAKON#1)
DEEPSEEK_URL = "https://api.deepseek.com/v1/chat/completions"
DEEPSEEK_MODEL = os.getenv("DEEPSEEK_MODEL", "deepseek-chat")

ALLOWED_EXT = {".pdf", ".jpg", ".jpeg", ".png", ".tif", ".tiff", ".webp"}
MAX_BYTES = 12 * 1024 * 1024  # 12 MB

ADMIN_TOKEN = "admin-pgz-2026"


def _db():
    c = psycopg2.connect(**DB)
    c.autocommit = True
    return c


def _is_admin(authorization: Optional[str]) -> bool:
    if not authorization:
        return False
    t = authorization.replace("Bearer ", "").strip()
    return t == ADMIN_TOKEN


def _resolve_user(authorization: Optional[str]) -> Optional[dict]:
    """Resolve current user via auth_v2 JWT, fallback to admin token (returns synthetic pgz_admin)."""
    if _auth_user:
        try:
            u = _auth_user(authorization)
            if u: return u
        except Exception:
            pass
    if _is_admin(authorization):
        return {"id": 0, "email": "admin@token", "user_type": "pgz_admin",
                "klub_id": None, "savez_id": None, "_synthetic": True}
    return None


def _safe_filename(orig: str) -> str:
    base = re.sub(r"[^A-Za-z0-9._-]+", "_", (orig or "upload").strip())[:120]
    if not base:
        base = "upload"
    ts = datetime.now().strftime("%Y%m%d_%H%M%S")
    return f"{ts}_{base}"


def _extract_text(path: Path) -> tuple[str, str]:
    """Return (text, method). Tries pdftotext first, falls back to tesseract."""
    suf = path.suffix.lower()
    if suf == ".pdf":
        try:
            r = subprocess.run(
                ["pdftotext", "-layout", "-q", str(path), "-"],
                capture_output=True, timeout=45,
            )
            txt = r.stdout.decode("utf-8", "ignore")
            if len(txt.strip()) > 80:
                return txt, "pdftotext"
        except Exception:
            pass
        # Rasterize + tesseract
        try:
            with tempfile.TemporaryDirectory(prefix="ocr_") as td:
                subprocess.run(
                    ["pdftoppm", "-r", "200", str(path), f"{td}/page"],
                    timeout=120, check=True,
                )
                chunks = []
                for img in sorted(Path(td).glob("page-*.ppm"))[:5]:
                    r = subprocess.run(
                        ["tesseract", str(img), "-", "-l", "hrv+eng", "--psm", "6"],
                        capture_output=True, timeout=90,
                    )
                    chunks.append(r.stdout.decode("utf-8", "ignore"))
                return "\n".join(chunks), "tesseract"
        except Exception as e:
            return "", f"pdf_err:{e}"
    if suf in {".jpg", ".jpeg", ".png", ".tif", ".tiff", ".webp"}:
        try:
            r = subprocess.run(
                ["tesseract", str(path), "-", "-l", "hrv+eng", "--psm", "6"],
                capture_output=True, timeout=120,
            )
            return r.stdout.decode("utf-8", "ignore"), "tesseract"
        except Exception as e:
            return "", f"img_err:{e}"
    return "", f"unsupported:{suf}"


# === HR invoice regex helpers ===
_OIB = re.compile(r"\b(\d{11})\b")
_IBAN = re.compile(r"\b(HR\d{19})\b")
_DATE_DOT = re.compile(r"\b(\d{1,2})[.\s\-/]+(\d{1,2})[.\s\-/]+(20\d{2})\b")
_DATE_ISO = re.compile(r"\b(20\d{2})[\-/](\d{1,2})[\-/](\d{1,2})\b")
_AMOUNT_TOTAL = re.compile(
    r"(?i)(?:UKUPNO|TOTAL|SVEUKUPNO|ZA NAPLATU|ZA PLATITI|ZA UPLATU|IZNOS\s+UKUPNO)[\s:€]*([\d.\s]{1,12}[,.]\d{2})"
)
_AMOUNT_VAT = re.compile(r"(?i)(?:PDV|VAT)[\s:%]*?([\d.\s]{1,8}[,.]\d{2})")
_INVOICE_NO = re.compile(r"(?i)(?:ra[čc]un|invoice|broj|fakture|br\.)\s*[:#]?\s*([A-Z0-9\-/.]{3,30})")


def _parse_amount(s: str) -> Optional[float]:
    if not s:
        return None
    s = s.replace(" ", "").replace("\xa0", "")
    # Croatian style "1.234,56" → 1234.56
    if "," in s and "." in s:
        s = s.replace(".", "").replace(",", ".")
    elif "," in s:
        s = s.replace(",", ".")
    try:
        return float(s)
    except Exception:
        return None


def regex_extract(text: str) -> dict:
    out: dict[str, Any] = {"raw_chars": len(text or "")}
    if not text:
        return out
    oibs = list(dict.fromkeys(_OIB.findall(text)))
    if oibs:
        out["oibs_found"] = oibs
        out["vendor_oib"] = oibs[0]
        if len(oibs) > 1:
            out["customer_oib"] = oibs[1]

    m = _IBAN.search(text.replace(" ", ""))
    if m:
        out["iban"] = m.group(1)

    m = _INVOICE_NO.search(text)
    if m:
        out["invoice_no"] = m.group(1).strip().rstrip(".,;")

    for rx, order in [(_DATE_DOT, "dmy"), (_DATE_ISO, "ymd")]:
        m = rx.search(text)
        if m:
            g = m.groups()
            try:
                if order == "dmy":
                    out["invoice_date"] = f"{g[2]}-{int(g[1]):02d}-{int(g[0]):02d}"
                else:
                    out["invoice_date"] = f"{g[0]}-{int(g[1]):02d}-{int(g[2]):02d}"
                # validate
                date.fromisoformat(out["invoice_date"])
                break
            except Exception:
                out.pop("invoice_date", None)

    totals = [_parse_amount(x) for x in _AMOUNT_TOTAL.findall(text)]
    totals = [t for t in totals if t and t > 0.01]
    if totals:
        out["amount_gross"] = max(totals)
        out["amounts_found"] = totals[:6]

    vats = [_parse_amount(x) for x in _AMOUNT_VAT.findall(text)]
    vats = [v for v in vats if v and v > 0.01]
    if vats:
        # smallest plausible PDV (less than gross)
        if "amount_gross" in out:
            cand = [v for v in vats if v < out["amount_gross"]]
            if cand:
                out["amount_vat"] = max(cand)
        else:
            out["amount_vat"] = max(vats)

    if "amount_gross" in out and "amount_vat" in out:
        out["amount_net"] = round(out["amount_gross"] - out["amount_vat"], 2)

    # Vendor name guess: first non-numeric, non-OIB line in header
    for line in text.split("\n")[:12]:
        ln = line.strip()
        if 4 < len(ln) < 80 and not _OIB.search(ln) and not re.match(r"^[\d\s.,\-/€:]+$", ln):
            out["vendor_name"] = ln
            break

    # Crude vendor guess for known HR sellers
    upper = text.upper()
    for keyword, label in [
        ("INA d.d.", "INA"), ("INA-MAZIVA", "INA"), ("TIFON", "TIFON"),
        ("PETROL", "PETROL"), ("HAC", "HAC"), ("BINA-ISTRA", "BINA-ISTRA"),
        ("HRVATSKE AUTOCESTE", "HAC"),
    ]:
        if keyword in upper:
            out.setdefault("vendor_brand", label)
            break

    return out


# === DeepSeek V3 LLM extraction ===
SYSTEM_PROMPT = (
    "Ti si stručnjak za hrvatske račune (R-1, fiskalne, HUB-3). "
    "Korisnik daje tekst računa izvučen OCR-om. Vrati ISKLJUČIVO valjani JSON, bez markdowna i komentara. "
    "Ako neko polje nije sigurno - vrati null. Iznosi su brojevi (decimal s točkom). Datum je 'YYYY-MM-DD'."
)

LLM_SCHEMA_HINT = """{
  "izdavatelj_naziv": str|null,
  "izdavatelj_oib": str|null,
  "izdavatelj_adresa": str|null,
  "kupac_naziv": str|null,
  "kupac_oib": str|null,
  "datum": "YYYY-MM-DD"|null,
  "broj_racuna": str|null,
  "iznos_neto": float|null,
  "iznos_pdv": float|null,
  "iznos_brutto": float|null,
  "stopa_pdv": float|null,
  "valuta": "EUR"|"HRK"|null,
  "nacin_placanja": str|null,
  "IBAN": str|null,
  "opis_svrhe": str|null,
  "vrsta_troska": "gorivo"|"cestarina"|"hotel"|"restoran"|"oprema"|"ostalo"|null,
  "stavke": [
    {"opis": str, "kolicina": float, "jedinica": str, "cijena": float, "ukupno": float}
  ]
}"""


def deepseek_extract(text: str, hint: dict | None = None) -> dict:
    """Call DeepSeek chat completions for structured JSON extraction."""
    if not DEEPSEEK_API_KEY:
        return {"error": "no_api_key"}
    if not text or len(text.strip()) < 20:
        return {"error": "empty_text"}

    user_msg = (
        f"Iz teksta računa ispod izvuci polja po shemi:\n{LLM_SCHEMA_HINT}\n\n"
        f"REGEX hint (može biti nepotpun ili netočan): {json.dumps(hint or {}, ensure_ascii=False)}\n\n"
        f"--- TEKST RAČUNA ---\n{text[:8000]}\n--- KRAJ ---"
    )
    payload = {
        "model": DEEPSEEK_MODEL,
        "messages": [
            {"role": "system", "content": SYSTEM_PROMPT},
            {"role": "user", "content": user_msg},
        ],
        "response_format": {"type": "json_object"},
        "temperature": 0.0,
        "max_tokens": 1200,
    }
    headers = {
        "Authorization": f"Bearer {DEEPSEEK_API_KEY}",
        "Content-Type": "application/json",
    }
    try:
        r = requests.post(DEEPSEEK_URL, headers=headers, json=payload, timeout=60)
    except Exception as e:
        return {"error": f"net:{e}"}
    if r.status_code != 200:
        return {"error": f"http_{r.status_code}", "detail": r.text[:300]}
    try:
        body = r.json()
        content = body["choices"][0]["message"]["content"]
        return json.loads(content)
    except Exception as e:
        return {"error": f"parse:{e}", "raw": (r.text[:500] if r else "")}


# === Endpoints ===

@router.post("/ocr/upload")
async def ocr_upload(
    file: UploadFile = File(...),
    klub_id: Optional[int] = Form(None),
    tenant_id: int = Form(1),
    invoice_kind: str = Form("ostalo"),
    authorization: Optional[str] = Header(None),
):
    """Upload an invoice file (PDF/image) → store on disk + insert pgz_sport.invoice_uploads."""
    user = _resolve_user(authorization)
    # Permission: pgz_admin uvijek; klub_admin/klub_user samo za vlastiti klub (ako je naveden)
    if user and not is_pgz_admin(user):
        if klub_id and user.get("klub_id") != klub_id:
            raise HTTPException(403, "Nemate ovlasti uploadati za ovaj klub")
    suffix = "." + (file.filename or "").rsplit(".", 1)[-1].lower()
    if suffix not in ALLOWED_EXT:
        raise HTTPException(400, f"Tip datoteke nije podržan: {suffix}. Dozvoljeno: {sorted(ALLOWED_EXT)}")

    raw = await file.read()
    if not raw:
        raise HTTPException(400, "Prazna datoteka")
    if len(raw) > MAX_BYTES:
        raise HTTPException(400, f"Datoteka prevelika ({len(raw)} > {MAX_BYTES} bajtova)")

    sha256 = hashlib.sha256(raw).hexdigest()
    fname = _safe_filename(file.filename or "upload")
    if not fname.endswith(suffix):
        fname += suffix
    path = UPLOAD_DIR / fname
    path.write_bytes(raw)

    with _db() as c:
        cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute(
            """
            INSERT INTO pgz_sport.invoice_uploads
                (klub_id, file_name, file_path, file_size, mime, sha256, ocr_status, meta)
            VALUES (%s, %s, %s, %s, %s, %s, 'pending', %s)
            RETURNING id, klub_id, file_name, ocr_status, uploaded_at
            """,
            (klub_id, file.filename, str(path), len(raw), file.content_type or "",
             sha256, json.dumps({"tenant_id": tenant_id, "invoice_kind": invoice_kind})),
        )
        row = cur.fetchone()
    # Audit log za OCR upload
    try:
        with _db() as c:
            c.cursor().execute(
                """INSERT INTO pgz_sport.audit_log
                   (tablica, operacija, record_id, korisnik, promijenjeno_polje, nova_vrijednost)
                   VALUES ('pgz_sport.invoice_uploads','create',%s,%s,'file_name',%s)""",
                (row["id"], (user.get("email") if user else "anon"),
                 f"{file.filename} ({len(raw)} B, sha={sha256[:12]})"),
            )
    except Exception:
        pass
    return {"ok": True, "upload_id": row["id"], "file_name": row["file_name"],
            "size": len(raw), "sha256": sha256, "status": row["ocr_status"]}


@router.post("/ocr/parse")
async def ocr_parse(
    upload_id: Optional[int] = Form(None),
    file: Optional[UploadFile] = File(None),
    use_llm: bool = Form(True),
    authorization: Optional[str] = Header(None),
):
    """Run OCR + (optional) DeepSeek LLM extraction.
    Either pass upload_id (parse a previously uploaded file) or send file directly (one-shot)."""
    tmp_to_clean: Optional[Path] = None
    upload_row = None
    try:
        if upload_id:
            with _db() as c:
                cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
                cur.execute("SELECT * FROM pgz_sport.invoice_uploads WHERE id=%s", (upload_id,))
                upload_row = cur.fetchone()
            if not upload_row:
                raise HTTPException(404, f"Upload id={upload_id} ne postoji")
            target = Path(upload_row["file_path"])
            if not target.exists():
                raise HTTPException(404, f"Datoteka ne postoji na disku: {target}")
        elif file:
            suffix = "." + (file.filename or "").rsplit(".", 1)[-1].lower()
            if suffix not in ALLOWED_EXT:
                raise HTTPException(400, f"Tip datoteke nije podržan: {suffix}")
            raw = await file.read()
            if not raw:
                raise HTTPException(400, "Prazna datoteka")
            tmp = tempfile.NamedTemporaryFile(prefix="parse_", suffix=suffix, delete=False)
            tmp.write(raw); tmp.close()
            target = Path(tmp.name)
            tmp_to_clean = target
        else:
            raise HTTPException(400, "Treba poslati upload_id ILI file")

        text, method = _extract_text(target)
        if len(text.strip()) < 20:
            return {"ok": False, "ocr_method": method, "raw_chars": len(text),
                    "error": "OCR nije uspio izvući dovoljno teksta"}

        regex_fields = regex_extract(text)
        regex_fields["ocr_method"] = method

        llm_fields: dict = {}
        if use_llm:
            llm_fields = deepseek_extract(text, hint=regex_fields)

        # Merge: LLM overrides regex when valid
        merged = dict(regex_fields)
        for k in ("izdavatelj_naziv", "izdavatelj_oib", "kupac_oib", "datum",
                  "broj_racuna", "iznos_neto", "iznos_pdv", "iznos_brutto",
                  "stopa_pdv", "valuta", "IBAN", "opis_svrhe", "vrsta_troska",
                  "izdavatelj_adresa", "nacin_placanja"):
            v = llm_fields.get(k) if isinstance(llm_fields, dict) else None
            if v not in (None, "", "null"):
                merged[k] = v

        # Normalize aliases for UI / DB
        if "izdavatelj_naziv" in merged: merged.setdefault("vendor_name", merged["izdavatelj_naziv"])
        if "izdavatelj_oib"   in merged: merged.setdefault("vendor_oib",  merged["izdavatelj_oib"])
        if "izdavatelj_adresa" in merged: merged.setdefault("vendor_address", merged["izdavatelj_adresa"])
        if "kupac_oib"        in merged: merged.setdefault("customer_oib", merged["kupac_oib"])
        if "datum"            in merged: merged.setdefault("invoice_date", merged["datum"])
        if "broj_racuna"      in merged: merged.setdefault("invoice_no", merged["broj_racuna"])
        if "iznos_brutto"     in merged: merged.setdefault("amount_gross", merged["iznos_brutto"])
        if "iznos_neto"       in merged: merged.setdefault("amount_net", merged["iznos_neto"])
        if "iznos_pdv"        in merged: merged.setdefault("amount_vat", merged["iznos_pdv"])
        if "stopa_pdv"        in merged: merged.setdefault("vat_rate", merged["stopa_pdv"])
        if "valuta"           in merged: merged.setdefault("currency", merged["valuta"])
        if "IBAN"             in merged: merged.setdefault("iban", merged["IBAN"])
        if "opis_svrhe"       in merged: merged.setdefault("description", merged["opis_svrhe"])
        if "vrsta_troska"     in merged: merged.setdefault("category", merged["vrsta_troska"])

        # Persist back to invoice_uploads when we have upload_row
        if upload_row:
            try:
                with _db() as c:
                    c.cursor().execute(
                        """UPDATE pgz_sport.invoice_uploads
                           SET ocr_status='done', processed_at=NOW(),
                               ocr_engine=%s, ocr_text=%s,
                               ai_invoice_no=%s, ai_invoice_date=%s,
                               ai_vendor_name=%s, ai_vendor_oib=%s,
                               ai_amount_gross=%s, ai_currency=%s, ai_iban=%s,
                               ai_extracted=%s, ai_engine=%s
                           WHERE id=%s""",
                        (
                            method, text[:50000],
                            merged.get("invoice_no"),
                            merged.get("invoice_date") if isinstance(merged.get("invoice_date"), str) else None,
                            merged.get("vendor_name"),
                            merged.get("vendor_oib"),
                            merged.get("amount_gross"),
                            merged.get("currency", "EUR"),
                            merged.get("iban"),
                            json.dumps({"regex": regex_fields, "llm": llm_fields, "merged": merged},
                                       ensure_ascii=False, default=str),
                            ("deepseek-v3" if use_llm and "error" not in (llm_fields or {}) else "regex"),
                            upload_row["id"],
                        ),
                    )
            except Exception as e:
                merged["_persist_warn"] = str(e)[:200]

        return {
            "ok": True,
            "upload_id": (upload_row["id"] if upload_row else None),
            "ocr_method": method,
            "raw_chars": len(text),
            "regex": regex_fields,
            "llm": llm_fields,
            "extracted": merged,
            "raw_text_preview": text[:1500],
        }
    finally:
        if tmp_to_clean and tmp_to_clean.exists():
            try:
                tmp_to_clean.unlink()
            except Exception:
                pass


# === Invoices CRUD (M5) ===

@router.get("/invoices")
def invoices_list(
    tenant_id: Optional[int] = Query(None),
    klub_id: Optional[int] = Query(None),
    status: Optional[str] = Query(None),
    kind: Optional[str] = Query(None),
    limit: int = Query(100, le=500),
    offset: int = Query(0),
):
    sql = """SELECT i.id, i.klub_id, k.naziv AS klub_naziv,
                    i.invoice_kind, i.invoice_no, i.internal_no,
                    i.vendor_name, i.vendor_oib, i.customer_name, i.customer_oib,
                    i.invoice_date, i.due_date, i.paid_date, i.currency,
                    i.amount_net, i.amount_vat, i.amount_gross, i.vat_rate,
                    i.payment_status, i.payment_method, i.iban_to,
                    i.description, i.category, i.tenant_id,
                    i.created_at, i.approved_at
             FROM pgz_sport.invoices i
             LEFT JOIN pgz_sport.klubovi k ON k.id = i.klub_id
             WHERE 1=1"""
    args: list = []
    if tenant_id is not None:
        sql += " AND i.tenant_id=%s"; args.append(tenant_id)
    if klub_id is not None:
        sql += " AND i.klub_id=%s"; args.append(klub_id)
    if status:
        sql += " AND i.payment_status=%s"; args.append(status)
    if kind:
        sql += " AND i.invoice_kind=%s"; args.append(kind)
    sql += " ORDER BY i.invoice_date DESC NULLS LAST, i.id DESC LIMIT %s OFFSET %s"
    args += [limit, offset]
    with _db() as c:
        cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute(sql, args)
        rows = cur.fetchall()
    return {"ok": True, "rows": rows, "count": len(rows)}


@router.get("/invoices/{invoice_id}")
def invoices_get(invoice_id: int, authorization: Optional[str] = Header(None)):
    user = _resolve_user(authorization)
    with _db() as c:
        cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute(
            """SELECT i.*, k.naziv AS klub_naziv, k.savez_id
               FROM pgz_sport.invoices i
               LEFT JOIN pgz_sport.klubovi k ON k.id = i.klub_id
               WHERE i.id=%s""", (invoice_id,))
        row = cur.fetchone()
        if not row:
            raise HTTPException(404, "Račun ne postoji")
        if user and not can_view_invoice(user, row):
            raise HTTPException(403, "Nemate ovlasti vidjeti ovaj račun")
        cur.execute("SELECT * FROM pgz_sport.invoice_lines WHERE invoice_id=%s ORDER BY line_no, id",
                    (invoice_id,))
        lines = cur.fetchall()
        cur.execute(
            """SELECT id, file_name, file_size, mime, sha256, ocr_status, ocr_engine,
                      ai_extracted, uploaded_at, processed_at
               FROM pgz_sport.invoice_uploads WHERE invoice_id=%s
               ORDER BY uploaded_at DESC""", (invoice_id,))
        uploads = cur.fetchall()
        cur.execute(
            """SELECT id, payment_date, amount, currency, payment_method, iban_from,
                      iban_to, reference, bank_transaction_id, matched_status, created_at
               FROM pgz_sport.payments WHERE invoice_id=%s ORDER BY payment_date DESC""",
            (invoice_id,))
        payments = cur.fetchall()
    audit = fetch_audit("pgz_sport.invoices", invoice_id, 50)
    actions = invoice_actions(user, row) if user else {"view": True, "edit": False, "pay": False, "comment": False, "delete": False}
    return {"ok": True, "invoice": row, "lines": lines, "uploads": uploads,
            "payments": payments, "audit": audit, "actions": actions}


@router.get("/invoices/{invoice_id}/file")
def invoices_file(invoice_id: int, authorization: Optional[str] = Header(None)):
    """Streamira originalnu datoteku skena/računa (slika ili PDF)."""
    user = _resolve_user(authorization)
    with _db() as c:
        cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute("SELECT i.id, i.klub_id FROM pgz_sport.invoices i WHERE i.id=%s", (invoice_id,))
        inv = cur.fetchone()
        if not inv:
            raise HTTPException(404, "Račun ne postoji")
        if user and not can_view_invoice(user, inv):
            raise HTTPException(403, "Nemate ovlasti")
        cur.execute(
            """SELECT file_path, file_name, mime FROM pgz_sport.invoice_uploads
               WHERE invoice_id=%s ORDER BY uploaded_at DESC LIMIT 1""", (invoice_id,))
        up = cur.fetchone()
    if not up:
        raise HTTPException(404, "Datoteka skena ne postoji za ovaj račun")
    p = Path(up["file_path"])
    if not p.exists():
        raise HTTPException(404, f"Datoteka ne postoji na disku")
    return FileResponse(str(p), media_type=up.get("mime") or "application/octet-stream",
                        filename=up.get("file_name") or p.name)


@router.get("/invoices/uploads/{upload_id}/file")
def upload_file(upload_id: int, authorization: Optional[str] = Header(None)):
    user = _resolve_user(authorization)
    with _db() as c:
        cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute("SELECT * FROM pgz_sport.invoice_uploads WHERE id=%s", (upload_id,))
        up = cur.fetchone()
    if not up:
        raise HTTPException(404, "Upload ne postoji")
    if user and not is_pgz_admin(user) and user.get("klub_id") != up.get("klub_id"):
        raise HTTPException(403, "Nemate ovlasti")
    p = Path(up["file_path"])
    if not p.exists():
        raise HTTPException(404, "Datoteka ne postoji")
    return FileResponse(str(p), media_type=up.get("mime") or "application/octet-stream",
                        filename=up.get("file_name") or p.name)


@router.post("/invoices/{invoice_id}/comment")
def invoices_comment(invoice_id: int, body: dict = Body(...),
                     authorization: Optional[str] = Header(None)):
    """Savez admin / klub admin / pgz admin može dodati komentar (audit log entry)."""
    user = _resolve_user(authorization)
    with _db() as c:
        cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute("SELECT i.*, k.savez_id FROM pgz_sport.invoices i LEFT JOIN pgz_sport.klubovi k ON k.id=i.klub_id WHERE i.id=%s", (invoice_id,))
        inv = cur.fetchone()
    if not inv:
        raise HTTPException(404, "Račun ne postoji")
    if user and not can_comment_invoice(user, inv):
        raise HTTPException(403, "Nemate ovlasti komentirati")
    txt = (body.get("comment") or "").strip()
    if not txt:
        raise HTTPException(400, "Komentar je prazan")
    audit_invoice(user, invoice_id, "comment", field="komentar", old=None, new=txt[:500])
    return {"ok": True, "invoice_id": invoice_id, "comment": txt}


@router.get("/invoices/{invoice_id}/audit")
def invoices_audit(invoice_id: int, limit: int = 100,
                   authorization: Optional[str] = Header(None)):
    user = _resolve_user(authorization)
    with _db() as c:
        cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute("SELECT i.id, i.klub_id FROM pgz_sport.invoices i WHERE i.id=%s", (invoice_id,))
        inv = cur.fetchone()
    if not inv:
        raise HTTPException(404, "Račun ne postoji")
    if user and not can_view_invoice(user, inv):
        raise HTTPException(403, "Nemate ovlasti")
    return {"ok": True, "audit": fetch_audit("pgz_sport.invoices", invoice_id, limit)}


@router.post("/invoices")
def invoices_create(body: dict = Body(...), authorization: Optional[str] = Header(None)):
    """Create an invoice from parsed OCR result.
    Body: {klub_id, tenant_id, invoice_kind, invoice_no, vendor_name, vendor_oib,
           invoice_date, amount_gross, amount_net, amount_vat, vat_rate, currency,
           iban_to, description, category, lines:[{...}], upload_id?}"""
    required = ["invoice_kind", "invoice_no", "invoice_date", "amount_gross"]
    for k in required:
        if body.get(k) in (None, ""):
            raise HTTPException(400, f"Nedostaje polje: {k}")

    user = _resolve_user(authorization)
    klub_id = body.get("klub_id")
    tenant_id = body.get("tenant_id", 1)
    upload_id = body.get("upload_id")
    lines = body.get("lines") or []

    # Permission: pgz_admin uvijek; klub_admin samo za vlastiti klub
    if user and not is_pgz_admin(user):
        if not (user.get("user_type") == "klub_admin" and klub_id == user.get("klub_id")):
            raise HTTPException(403, "Nemate ovlasti kreirati račun za ovaj klub")

    with _db() as c:
        cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute(
            """INSERT INTO pgz_sport.invoices
               (klub_id, invoice_kind, invoice_no, internal_no,
                vendor_oib, vendor_name, vendor_address,
                customer_oib, customer_name,
                invoice_date, due_date, currency,
                amount_net, amount_vat, amount_gross, vat_rate,
                payment_status, payment_method, iban_to,
                description, category, account_code, tenant_id, meta)
               VALUES (%s,%s,%s,%s, %s,%s,%s, %s,%s,
                       %s,%s,COALESCE(%s,'EUR'),
                       %s,%s,%s,%s,
                       COALESCE(%s,'unpaid'),%s,%s,
                       %s,%s,%s,%s,%s)
               ON CONFLICT (klub_id, invoice_kind, invoice_no, vendor_oib)
               DO UPDATE SET amount_gross=EXCLUDED.amount_gross,
                             amount_net=EXCLUDED.amount_net,
                             amount_vat=EXCLUDED.amount_vat,
                             updated_at=NOW()
               RETURNING id, invoice_no, amount_gross, payment_status""",
            (
                klub_id, body["invoice_kind"], body["invoice_no"], body.get("internal_no"),
                body.get("vendor_oib"), body.get("vendor_name"), body.get("vendor_address"),
                body.get("customer_oib"), body.get("customer_name"),
                body["invoice_date"], body.get("due_date"), body.get("currency"),
                body.get("amount_net"), body.get("amount_vat"), body["amount_gross"], body.get("vat_rate"),
                body.get("payment_status"), body.get("payment_method"), body.get("iban_to"),
                body.get("description"), body.get("category"), body.get("account_code"),
                tenant_id, json.dumps(body.get("meta", {})),
            ),
        )
        inv = cur.fetchone()
        inv_id = inv["id"]

        # Replace lines
        cur.execute("DELETE FROM pgz_sport.invoice_lines WHERE invoice_id=%s", (inv_id,))
        for i, ln in enumerate(lines, start=1):
            cur.execute(
                """INSERT INTO pgz_sport.invoice_lines
                   (invoice_id, line_no, description, quantity, unit, unit_price,
                    vat_rate, line_net, line_vat, line_gross, account_code, cost_center, meta)
                   VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
                (
                    inv_id, ln.get("line_no", i), ln.get("description") or ln.get("opis") or "",
                    ln.get("quantity") or ln.get("kolicina") or 1,
                    ln.get("unit") or ln.get("jedinica") or "kom",
                    ln.get("unit_price") or ln.get("cijena"),
                    ln.get("vat_rate", 25),
                    ln.get("line_net"), ln.get("line_vat"),
                    ln.get("line_gross") or ln.get("ukupno"),
                    ln.get("account_code"), ln.get("cost_center"),
                    json.dumps(ln.get("meta", {})),
                ),
            )

        # Link upload to invoice
        if upload_id:
            cur.execute(
                "UPDATE pgz_sport.invoice_uploads SET invoice_id=%s WHERE id=%s",
                (inv_id, upload_id),
            )

    audit_invoice(user, inv_id, "create", field="invoice_no",
                   new=f"{body.get('invoice_no')} €{body.get('amount_gross')}")
    notif = notify_invoice_created({**body, "id": inv_id, "klub_id": klub_id})
    return {"ok": True, "invoice": inv, "notification": notif}


@router.put("/invoices/{invoice_id}")
def invoices_update(invoice_id: int, body: dict = Body(...), authorization: Optional[str] = Header(None)):
    """Update / approve invoice. Body may include any of: payment_status, paid_date,
    approved (bool), notes, category, account_code, due_date."""
    user = _resolve_user(authorization)
    with _db() as c:
        cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute("SELECT i.*, k.savez_id FROM pgz_sport.invoices i LEFT JOIN pgz_sport.klubovi k ON k.id=i.klub_id WHERE i.id=%s", (invoice_id,))
        inv = cur.fetchone()
    if not inv:
        raise HTTPException(404, "Račun ne postoji")
    if user and not can_edit_invoice(user, inv):
        raise HTTPException(403, "Nemate ovlasti uređivati ovaj račun")

    fields = []
    args: list = []
    changes = []
    for col in ("payment_status", "paid_date", "due_date", "category",
                "account_code", "notes", "vat_rate", "amount_net", "amount_vat",
                "amount_gross", "payment_method", "iban_to"):
        if col in body:
            fields.append(f"{col}=%s")
            args.append(body[col])
            changes.append((col, inv.get(col), body[col]))
    if body.get("approved"):
        fields.append("approved_at=NOW()")
        changes.append(("approved_at", inv.get("approved_at"), "now"))
    if not fields:
        raise HTTPException(400, "Nema polja za izmjenu")
    fields.append("updated_at=NOW()")
    args.append(invoice_id)
    with _db() as c:
        cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute(f"UPDATE pgz_sport.invoices SET {','.join(fields)} WHERE id=%s RETURNING *", args)
        row = cur.fetchone()
    for f, o, n in changes:
        audit_invoice(user, invoice_id, "update", field=f, old=o, new=n)
    return {"ok": True, "invoice": row}


@router.delete("/invoices/{invoice_id}")
def invoices_delete(invoice_id: int, authorization: Optional[str] = Header(None)):
    """Brisanje računa — samo pgz_admin."""
    user = _resolve_user(authorization)
    if user and not is_pgz_admin(user):
        raise HTTPException(403, "Nemate ovlasti brisati račun")
    with _db() as c:
        cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute("SELECT * FROM pgz_sport.invoices WHERE id=%s", (invoice_id,))
        inv = cur.fetchone()
        if not inv:
            raise HTTPException(404, "Račun ne postoji")
        cur.execute("UPDATE pgz_sport.invoice_uploads SET invoice_id=NULL WHERE invoice_id=%s", (invoice_id,))
        cur.execute("DELETE FROM pgz_sport.payments WHERE invoice_id=%s", (invoice_id,))
        cur.execute("DELETE FROM pgz_sport.invoices WHERE id=%s", (invoice_id,))
    audit_invoice(user, invoice_id, "delete", field="invoice_no",
                   old=inv.get("invoice_no"), new="(deleted)")
    return {"ok": True, "deleted": invoice_id}


@router.post("/invoices/{invoice_id}/pay")
def invoices_pay(invoice_id: int, body: dict = Body(default={}),
                 authorization: Optional[str] = Header(None)):
    """Označi račun kao plaćen + insert payment record.
    Body: {iban_to, iban_from, paid_date, reference, bank_transaction_id, payment_method, amount}
    """
    user = _resolve_user(authorization)
    with _db() as c:
        cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute("SELECT i.*, k.savez_id FROM pgz_sport.invoices i LEFT JOIN pgz_sport.klubovi k ON k.id=i.klub_id WHERE i.id=%s", (invoice_id,))
        inv = cur.fetchone()
    if not inv:
        raise HTTPException(404, "Račun ne postoji")
    if user and not can_pay_invoice(user, inv):
        raise HTTPException(403, "Nemate ovlasti označiti račun kao plaćen")
    if (inv.get("payment_status") or "").lower() == "paid":
        raise HTTPException(409, "Račun je već označen kao plaćen")

    paid_date = body.get("paid_date") or date.today().isoformat()
    payment_method = body.get("payment_method") or "transfer"
    iban_from = body.get("iban_from")
    iban_to = body.get("iban_to") or inv.get("iban_to")
    reference = body.get("reference")
    tx_id = body.get("bank_transaction_id") or body.get("tx_id")
    amount = body.get("amount") or inv.get("amount_gross")

    with _db() as c:
        cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute(
            """UPDATE pgz_sport.invoices
               SET payment_status='paid', paid_date=%s,
                   payment_method=COALESCE(%s,payment_method),
                   iban_from=COALESCE(%s,iban_from),
                   iban_to=COALESCE(%s,iban_to),
                   updated_at=NOW()
               WHERE id=%s
               RETURNING id, invoice_no, paid_date, amount_gross, payment_status,
                         iban_from, iban_to, payment_method""",
            (paid_date, payment_method, iban_from, iban_to, invoice_id),
        )
        row = cur.fetchone()
        # Insert payment record
        cur.execute(
            """INSERT INTO pgz_sport.payments
               (klub_id, invoice_id, payment_date, amount, currency, payment_method,
                iban_from, iban_to, reference, bank_transaction_id, matched_status)
               VALUES (%s,%s,%s,%s,COALESCE(%s,'EUR'),%s,%s,%s,%s,%s,'matched')
               RETURNING id""",
            (inv.get("klub_id"), invoice_id, paid_date, amount,
             inv.get("currency"), payment_method, iban_from, iban_to,
             reference, tx_id),
        )
        pay = cur.fetchone()
    audit_invoice(user, invoice_id, "pay", field="payment_status",
                   old=inv.get("payment_status"), new="paid")
    notif = notify_invoice_paid(
        {**inv, **(row or {}), "id": invoice_id},
        {"iban_to": iban_to, "iban_from": iban_from, "reference": reference,
         "payment_date": paid_date, "amount": amount},
    )
    return {"ok": True, "invoice": row, "payment_id": pay["id"] if pay else None,
            "notification": notif}


# ── R5.3 BULK OPERATIONS ──────────────────────────────────────────────
@router.post("/invoices/bulk-pay")
def invoices_bulk_pay(body: dict = Body(...), authorization: Optional[str] = Header(None)):
    """Bulk označi listu računa kao plaćene.
    Body: {ids: [int], paid_date?, payment_method?, iban_from?, iban_to?, reference?, tx_id?}"""
    user = _resolve_user(authorization)
    ids = body.get("ids") or []
    if not ids or not isinstance(ids, list):
        raise HTTPException(400, "ids je obavezna ne-prazna lista")
    paid_date = body.get("paid_date") or date.today().isoformat()
    payment_method = body.get("payment_method") or "transfer"
    iban_from = body.get("iban_from")
    iban_to = body.get("iban_to")
    reference = body.get("reference")
    tx_id = body.get("bank_transaction_id") or body.get("tx_id")

    results = {"paid": [], "skipped": [], "forbidden": [], "errors": []}
    with _db() as c:
        cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute(
            """SELECT i.*, k.savez_id FROM pgz_sport.invoices i
               LEFT JOIN pgz_sport.klubovi k ON k.id=i.klub_id
               WHERE i.id = ANY(%s)""", (ids,))
        rows = cur.fetchall()
    for inv in rows:
        if (inv.get("payment_status") or "").lower() == "paid":
            results["skipped"].append(inv["id"]); continue
        if user and not can_pay_invoice(user, inv):
            results["forbidden"].append(inv["id"]); continue
        try:
            with _db() as c:
                cur = c.cursor()
                cur.execute(
                    """UPDATE pgz_sport.invoices
                       SET payment_status='paid', paid_date=%s,
                           payment_method=COALESCE(%s,payment_method),
                           iban_from=COALESCE(%s,iban_from),
                           iban_to=COALESCE(%s,iban_to),
                           updated_at=NOW()
                       WHERE id=%s""",
                    (paid_date, payment_method, iban_from, iban_to, inv["id"]),
                )
                cur.execute(
                    """INSERT INTO pgz_sport.payments
                       (klub_id, invoice_id, payment_date, amount, currency, payment_method,
                        iban_from, iban_to, reference, bank_transaction_id, matched_status)
                       VALUES (%s,%s,%s,%s,COALESCE(%s,'EUR'),%s,%s,%s,%s,%s,'matched')""",
                    (inv.get("klub_id"), inv["id"], paid_date, inv.get("amount_gross"),
                     inv.get("currency"), payment_method, iban_from, iban_to, reference, tx_id),
                )
            audit_invoice(user, inv["id"], "bulk_pay",
                          field="payment_status", old=inv.get("payment_status"), new="paid")
            try:
                notify_invoice_paid(
                    {**inv, "paid_date": paid_date},
                    {"iban_to": iban_to, "iban_from": iban_from, "reference": reference,
                     "payment_date": paid_date, "amount": inv.get("amount_gross")},
                )
            except Exception:
                pass
            results["paid"].append(inv["id"])
        except Exception as e:
            results["errors"].append({"id": inv["id"], "err": str(e)[:200]})
    return {"ok": True, "summary": {k: len(v) for k, v in results.items()}, "details": results}


@router.post("/invoices/bulk-cancel")
def invoices_bulk_cancel(body: dict = Body(...), authorization: Optional[str] = Header(None)):
    """Bulk otkaži (status='cancelled') — samo pgz_admin ili klub_admin svog kluba."""
    user = _resolve_user(authorization)
    ids = body.get("ids") or []
    razlog = body.get("razlog") or body.get("reason") or "(bulk cancel)"
    if not ids:
        raise HTTPException(400, "ids je obavezna ne-prazna lista")
    results = {"cancelled": [], "skipped": [], "forbidden": [], "errors": []}
    with _db() as c:
        cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute(
            """SELECT i.*, k.savez_id FROM pgz_sport.invoices i
               LEFT JOIN pgz_sport.klubovi k ON k.id=i.klub_id
               WHERE i.id = ANY(%s)""", (ids,))
        rows = cur.fetchall()
    for inv in rows:
        if (inv.get("payment_status") or "").lower() in ("paid", "cancelled"):
            results["skipped"].append(inv["id"]); continue
        if user and not can_edit_invoice(user, inv):
            results["forbidden"].append(inv["id"]); continue
        try:
            with _db() as c:
                c.cursor().execute(
                    """UPDATE pgz_sport.invoices
                       SET payment_status='cancelled',
                           notes = COALESCE(notes,'') || E'\n[CANCEL] ' || %s,
                           updated_at=NOW() WHERE id=%s""",
                    (razlog, inv["id"]),
                )
            audit_invoice(user, inv["id"], "bulk_cancel",
                          field="payment_status", old=inv.get("payment_status"),
                          new=f"cancelled: {razlog}")
            try: notify_invoice_cancelled(inv, razlog)
            except Exception: pass
            results["cancelled"].append(inv["id"])
        except Exception as e:
            results["errors"].append({"id": inv["id"], "err": str(e)[:200]})
    return {"ok": True, "summary": {k: len(v) for k, v in results.items()}, "details": results}


# ── R5.4 XLSX EXPORT ───────────────────────────────────────────────────
@router.get("/export/invoices.xlsx")
def invoices_export_xlsx(
    tenant_id: Optional[int] = Query(None),
    klub_id: Optional[int] = Query(None),
    od: Optional[str] = Query(None, description="datum od YYYY-MM-DD"),
    do: Optional[str] = Query(None, description="datum do YYYY-MM-DD"),
    status: Optional[str] = None,
    kind: Optional[str] = None,
    authorization: Optional[str] = Header(None),
):
    """XLSX export računa za knjigovodstvo. Stupci: ID, datum, vrsta, broj,
    izdavatelj, OIB, klub, neto, PDV, brutto, valuta, status, IBAN, opis."""
    from openpyxl import Workbook
    from openpyxl.styles import Font, PatternFill, Alignment
    from io import BytesIO
    from fastapi.responses import StreamingResponse

    user = _resolve_user(authorization)
    sql = """SELECT i.id, i.invoice_date, i.invoice_kind, i.invoice_no,
                    i.vendor_name, i.vendor_oib, i.customer_oib,
                    i.amount_net, i.amount_vat, i.amount_gross, i.vat_rate,
                    i.currency, i.payment_status, i.payment_method,
                    i.iban_to, i.description, i.category,
                    i.paid_date, i.tenant_id, i.klub_id,
                    k.naziv AS klub_naziv, k.savez_id
             FROM pgz_sport.invoices i
             LEFT JOIN pgz_sport.klubovi k ON k.id=i.klub_id
             WHERE 1=1"""
    args: list = []
    if tenant_id is not None: sql += " AND i.tenant_id=%s"; args.append(tenant_id)
    if klub_id  is not None:  sql += " AND i.klub_id=%s";   args.append(klub_id)
    if od: sql += " AND i.invoice_date >= %s"; args.append(od)
    if do: sql += " AND i.invoice_date <= %s"; args.append(do)
    if status: sql += " AND i.payment_status=%s"; args.append(status)
    if kind: sql += " AND i.invoice_kind=%s"; args.append(kind)
    sql += " ORDER BY i.invoice_date DESC, i.id DESC"
    with _db() as c:
        cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute(sql, args)
        rows = cur.fetchall()

    # Filter po user permissions
    if user and not is_pgz_admin(user):
        rows = [r for r in rows if can_view_invoice(user, r)]

    wb = Workbook()
    ws = wb.active
    ws.title = "Računi"
    headers = ["ID", "Datum", "Vrsta", "Broj računa", "Izdavatelj", "OIB",
               "Klub", "Iznos neto", "PDV", "Brutto", "Stopa PDV",
               "Valuta", "Status", "Datum uplate", "IBAN primatelja",
               "Opis", "Kategorija"]
    bold = Font(bold=True, color="FFFFFF")
    fill = PatternFill("solid", fgColor="003087")
    for col_idx, h in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_idx, value=h)
        cell.font = bold; cell.fill = fill
        cell.alignment = Alignment(horizontal="center")
    for r_idx, r in enumerate(rows, 2):
        ws.cell(row=r_idx, column=1,  value=r.get("id"))
        ws.cell(row=r_idx, column=2,  value=str(r.get("invoice_date") or ""))
        ws.cell(row=r_idx, column=3,  value=r.get("invoice_kind"))
        ws.cell(row=r_idx, column=4,  value=r.get("invoice_no"))
        ws.cell(row=r_idx, column=5,  value=r.get("vendor_name"))
        ws.cell(row=r_idx, column=6,  value=r.get("vendor_oib"))
        ws.cell(row=r_idx, column=7,  value=r.get("klub_naziv"))
        ws.cell(row=r_idx, column=8,  value=float(r["amount_net"]) if r.get("amount_net") is not None else None)
        ws.cell(row=r_idx, column=9,  value=float(r["amount_vat"]) if r.get("amount_vat") is not None else None)
        ws.cell(row=r_idx, column=10, value=float(r["amount_gross"]) if r.get("amount_gross") is not None else None)
        ws.cell(row=r_idx, column=11, value=float(r["vat_rate"]) if r.get("vat_rate") is not None else None)
        ws.cell(row=r_idx, column=12, value=r.get("currency"))
        ws.cell(row=r_idx, column=13, value=r.get("payment_status"))
        ws.cell(row=r_idx, column=14, value=str(r.get("paid_date") or ""))
        ws.cell(row=r_idx, column=15, value=r.get("iban_to"))
        ws.cell(row=r_idx, column=16, value=r.get("description"))
        ws.cell(row=r_idx, column=17, value=r.get("category"))
    # Auto width
    widths = [6, 12, 12, 18, 28, 14, 24, 12, 12, 12, 8, 6, 11, 12, 22, 30, 12]
    for i, w in enumerate(widths, 1):
        ws.column_dimensions[ws.cell(row=1, column=i).column_letter].width = w
    ws.freeze_panes = "A2"

    buf = BytesIO()
    wb.save(buf); buf.seek(0)
    fname = f"racuni_{date.today().isoformat()}.xlsx"
    return StreamingResponse(
        buf, media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": f'attachment; filename="{fname}"'},
    )


# ── R5.6 STATS ─────────────────────────────────────────────────────────
@router.get("/stats")
def erp_stats(
    klub_id: Optional[int] = Query(None),
    tenant_id: Optional[int] = Query(None),
    authorization: Optional[str] = Header(None),
):
    """Statistika ERP-a: ukupno troškova mjesec/kvartal/godina po klubu/savezu,
    breakdown po vrstama (gorivo/cestarina/hotel/oprema/ostalo)."""
    user = _resolve_user(authorization)
    today = date.today()
    month_start = today.replace(day=1).isoformat()
    qmonth = ((today.month - 1) // 3) * 3 + 1
    quarter_start = today.replace(month=qmonth, day=1).isoformat()
    year_start = today.replace(month=1, day=1).isoformat()

    where = ["1=1"]; args: list = []
    if klub_id is not None:
        where.append("klub_id=%s"); args.append(klub_id)
    if tenant_id is not None:
        where.append("tenant_id=%s"); args.append(tenant_id)
    where_sql = " AND ".join(where)

    def q_sum(date_from):
        with _db() as c:
            cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
            cur.execute(
                f"""SELECT COUNT(*) AS n,
                           COALESCE(SUM(amount_gross),0)::float AS total,
                           COALESCE(SUM(CASE WHEN payment_status='paid' THEN amount_gross END),0)::float AS paid,
                           COALESCE(SUM(CASE WHEN payment_status<>'paid' THEN amount_gross END),0)::float AS unpaid
                    FROM pgz_sport.invoices
                    WHERE {where_sql} AND invoice_date >= %s""",
                args + [date_from],
            )
            return cur.fetchone()

    def q_breakdown(date_from):
        with _db() as c:
            cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
            cur.execute(
                f"""SELECT invoice_kind, COUNT(*) AS n,
                           COALESCE(SUM(amount_gross),0)::float AS total
                    FROM pgz_sport.invoices
                    WHERE {where_sql} AND invoice_date >= %s
                    GROUP BY invoice_kind ORDER BY total DESC""",
                args + [date_from],
            )
            return cur.fetchall()

    def q_top(date_from):
        with _db() as c:
            cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
            cur.execute(
                f"""SELECT i.klub_id, k.naziv AS klub_naziv,
                           COUNT(*) AS n, COALESCE(SUM(i.amount_gross),0)::float AS total
                    FROM pgz_sport.invoices i
                    LEFT JOIN pgz_sport.klubovi k ON k.id=i.klub_id
                    WHERE {where_sql} AND i.invoice_date >= %s
                    GROUP BY i.klub_id, k.naziv ORDER BY total DESC LIMIT 10""",
                args + [date_from],
            )
            return cur.fetchall()

    # Putni nalozi totals
    def q_pn(date_from):
        with _db() as c:
            cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
            pn_where = ["report_type='putni_nalog'"]; pn_args: list = []
            if klub_id is not None:
                pn_where.append("klub_id=%s"); pn_args.append(klub_id)
            if tenant_id is not None:
                pn_where.append("tenant_id=%s"); pn_args.append(tenant_id)
            cur.execute(
                f"""SELECT COUNT(*) AS n,
                           COALESCE(SUM(cost_total),0)::float AS total,
                           COALESCE(SUM(dnevnice_amount),0)::float AS dnevnice,
                           COALESCE(SUM(cost_transport),0)::float AS transport
                    FROM pgz_sport.expense_reports
                    WHERE {' AND '.join(pn_where)} AND date_from >= %s""",
                pn_args + [date_from],
            )
            return cur.fetchone()

    return {
        "ok": True,
        "as_of": today.isoformat(),
        "filters": {"klub_id": klub_id, "tenant_id": tenant_id},
        "invoices": {
            "month":   {"since": month_start,   **q_sum(month_start),   "by_kind": q_breakdown(month_start)},
            "quarter": {"since": quarter_start, **q_sum(quarter_start), "by_kind": q_breakdown(quarter_start)},
            "year":    {"since": year_start,    **q_sum(year_start),    "by_kind": q_breakdown(year_start)},
        },
        "top_klubovi_godina": q_top(year_start),
        "putni_nalozi": {
            "month":   {"since": month_start,   **q_pn(month_start)},
            "quarter": {"since": quarter_start, **q_pn(quarter_start)},
            "year":    {"since": year_start,    **q_pn(year_start)},
        },
    }


@router.get("/invoices/uploads/list")
def uploads_list(klub_id: Optional[int] = None, status: Optional[str] = None, limit: int = 50):
    sql = """SELECT id, klub_id, file_name, file_size, mime, ocr_status, ocr_engine,
                    ai_invoice_no, ai_invoice_date, ai_vendor_name, ai_vendor_oib,
                    ai_amount_gross, ai_currency, invoice_id, uploaded_at, processed_at
             FROM pgz_sport.invoice_uploads WHERE 1=1"""
    args: list = []
    if klub_id is not None:
        sql += " AND klub_id=%s"; args.append(klub_id)
    if status:
        sql += " AND ocr_status=%s"; args.append(status)
    sql += " ORDER BY uploaded_at DESC LIMIT %s"; args.append(limit)
    with _db() as c:
        cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur.execute(sql, args)
        rows = cur.fetchall()
    return {"ok": True, "rows": rows}