pgz-sport/routers/clan_panel_router.py_prije_env_deepseek

#!/usr/bin/env python3
# ═══════════════════════════════════════════════════════════════════
# Fajl: routers/clan_panel_router.py | v1.0.0 | 05.05.2026
# Autor: Damir Radulić <dradulic@outlook.com> / damir@rinet.one
# Lokacija: /opt/pgz-sport/routers/clan_panel_router.py
# Svrha: CRM Dashboard člana — full panel (sve), edit s permission gating,
#         avatar upload.
# ═══════════════════════════════════════════════════════════════════
"""CRM Član Panel router.

Endpointi (montirani na /api/crm):
  GET  /clanovi/{id}/full      → SVI podaci o članu + povijest svega
  PUT  /clanovi/{id}           → edit (permission gating po roli)
  POST /clanovi/{id}/avatar    → upload slike
  GET  /clanovi/search?q=...   → quick search za panel
"""
from __future__ import annotations

import os
import io
import shutil
import uuid as _uuid
from datetime import date, datetime
from decimal import Decimal
from typing import Optional, Any
from pathlib import Path

import psycopg2
from psycopg2.extras import RealDictCursor
from fastapi import APIRouter, HTTPException, Query, UploadFile, File, Header
from fastapi.responses import JSONResponse
from pydantic import BaseModel

router = APIRouter(prefix="/api/crm", tags=["crm-clan-panel"])

DSN = f"host=10.10.0.2 port=6432 dbname=rinet_v3 user=rinet password={os.environ['DB_PASSWORD']}"

UPLOADS_DIR = Path("/opt/pgz-sport/static/uploads/avatars")
UPLOADS_DIR.mkdir(parents=True, exist_ok=True)
PUBLIC_AVATAR_PREFIX = "/sport/static/uploads/avatars"

# Polja koja smiju editirati pojedine role.
# Hard rules iz briefa:
#   sportas (sebe): kontakt + slike
#   klub_admin:   sve osim OIB
#   savez_admin:  pregled + napomene
#   pgz_admin:    full
#   super_admin:  full
EDITABLE_BY_ROLE = {
    "sportas": {
        "email", "telefon", "adresa", "grad", "postanski_broj",
        "biografija", "slika_url",
    },
    "klub_admin": {
        # sve osim "oib"
        "ime", "prezime", "datum_rodenja", "spol", "adresa", "grad",
        "postanski_broj", "email", "telefon", "kategorija", "podkategorija",
        "pozicija", "licenca_broj", "licenca_vrijedi_do", "reprezentativac",
        "kategoriziran", "kategorija_hoo", "stipendiran", "stipendija_iznos",
        "radno_pravni_status", "aktivan", "datum_pristupa", "datum_napustanja",
        "napomena", "dominantna_noga", "visina_cm", "tezina_kg", "broj_dresa",
        "reprezentacija_kategorija", "biografija", "mjesto_rodenja",
        "sport", "uloga", "uloga_detalj", "klub_id", "slika_url",
    },
    "savez_admin": {
        "napomena",
    },
    "pgz_admin": "ALL",
    "super_admin": "ALL",
    "klub_trener": {
        "kategorija", "podkategorija", "pozicija", "broj_dresa",
        "dominantna_noga", "visina_cm", "tezina_kg", "napomena", "biografija",
    },
}


def _conn():
    return psycopg2.connect(DSN, cursor_factory=RealDictCursor)


def _conv(v):
    if isinstance(v, (date, datetime)):
        return v.isoformat()
    if isinstance(v, Decimal):
        return float(v)
    if isinstance(v, _uuid.UUID):
        return str(v)
    return v


def _row(d):
    return None if d is None else {k: _conv(v) for k, v in dict(d).items()}


def _resolve_role(authorization: Optional[str]) -> str:
    """
    Vrlo pojednostavljeno: dok puni JWT M1 ne propagira context, čitamo
    'X-Role' header (postavi UI). Inače: ako je authorization == admin token
    → pgz_admin, inače → viewer.
    """
    if not authorization:
        return "viewer"
    tok = authorization.replace("Bearer ", "").strip()
    if tok == "admin-pgz-2026":
        return "pgz_admin"
    # decode JWT (best-effort)
    try:
        import jwt as _jwt  # type: ignore
        # JWT secret iz auth_v2 — bez tvrde ovisnosti
        for secret in (os.environ.get("JWT_SECRET"), "rinet-jwt-secret-2026"):
            if not secret:
                continue
            try:
                payload = _jwt.decode(tok, secret, algorithms=["HS256"])
                return payload.get("role") or payload.get("user_type") or "viewer"
            except Exception:
                continue
    except Exception:
        pass
    return "viewer"


def _check_field_perm(role: str, fields: set[str]) -> set[str]:
    """Vrati SAMO polja koja role smije editirati."""
    allowed = EDITABLE_BY_ROLE.get(role, set())
    if allowed == "ALL":
        return fields
    return fields & allowed


# ───────────── search ─────────────

@router.get("/clanovi/search")
def clanovi_search(q: Optional[str] = Query(None, min_length=2),
                   klub_id: Optional[int] = Query(None),
                   limit: int = Query(20, le=100)):
    where, params = ["c.aktivan = TRUE"], []
    if q:
        where.append("(c.ime || ' ' || c.prezime) ILIKE %s OR c.oib ILIKE %s")
        params += [f"%{q}%", f"%{q}%"]
    if klub_id:
        where.append("c.klub_id = %s"); params.append(klub_id)
    params.append(limit)
    sql = f"""
      SELECT c.id, c.ime, c.prezime, c.oib, c.kategorija, c.pozicija,
             c.slika_url, c.broj_dresa,
             k.id AS klub_id, k.naziv AS klub
        FROM pgz_sport.clanovi c
        LEFT JOIN pgz_sport.klubovi k ON k.id = c.klub_id
       WHERE {' AND '.join(where)}
       ORDER BY c.prezime, c.ime
       LIMIT %s
    """
    with _conn() as conn, conn.cursor() as cur:
        cur.execute(sql, params)
        rows = [_row(r) for r in cur.fetchall()]
    return {"count": len(rows), "rows": rows}


# ───────────── full panel ─────────────

@router.get("/clanovi/{cid}/full")
def clan_full(cid: int):
    """
    Vraća SVE podatke o članu + sve povezane pod-tablice:
      - personal, kontakt, sport, status, reprezentacija, stipendija
      - klub (trenutni + povijest preko clan_sezona.klub_naziv)
      - sezone (clan_sezona)
      - utakmice (zadnjih 20 — clan_utakmica)
      - lijecnicki (lijecnicki_pregledi po clan_id)
      - clanarine (clanarine po clan_id) + dug
      - dokumenti (clan_godisnjak ↔ dokumenti)
      - obrasci (form_submissions po clan_id)
      - nagrade (clan_nagrada)
    """
    with _conn() as conn, conn.cursor() as cur:
        cur.execute("""
          SELECT c.*,
                 k.id   AS klub__id,
                 k.naziv AS klub__naziv,
                 k.oib   AS klub__oib,
                 k.iban  AS klub__iban,
                 k.adresa AS klub__adresa,
                 k.grad  AS klub__grad,
                 k.sport AS klub__sport,
                 k.savez_id AS klub__savez_id,
                 s.naziv AS klub__savez_naziv,
                 EXTRACT(YEAR FROM age(COALESCE(c.datum_rodenja, c.datum_rodjenja)))::int AS dob_calc
            FROM pgz_sport.clanovi c
            LEFT JOIN pgz_sport.klubovi k ON k.id = c.klub_id
            LEFT JOIN pgz_sport.savezi  s ON s.id = k.savez_id
           WHERE c.id = %s
        """, (cid,))
        clan_raw = cur.fetchone()
        if not clan_raw:
            raise HTTPException(404, "Član ne postoji")

        # rastavi klub__* u nested objekt
        c = {}
        klub: dict = {}
        for k, v in dict(clan_raw).items():
            if k.startswith("klub__"):
                klub[k.replace("klub__", "")] = v
            else:
                c[k] = v

        # avatar URL fallback (slika_url može biti relativna)
        slika = c.get("slika_url") or ""
        if slika and not (slika.startswith("http") or slika.startswith("/")):
            slika = f"{PUBLIC_AVATAR_PREFIX}/{slika}"
        c["slika_url_full"] = slika or None

        # SEZONE
        cur.execute("""
          SELECT id, sezona, natjecanje, klub_naziv, nastupi, zapoceo, zamjena,
                 pogoci, asistencije, zuti_kartoni, crveni_kartoni, minute_total,
                 napomena, scrape_url
            FROM pgz_sport.clan_sezona
           WHERE clan_id = %s
           ORDER BY sezona DESC
           LIMIT 50
        """, (cid,))
        sezone = [_row(r) for r in cur.fetchall()]

        # UTAKMICE (zadnjih 20)
        cur.execute("""
          SELECT id, datum, domacin, gost, rezultat, natjecanje,
                 pogoci, zuti, crveni, minute, utakmica_url
            FROM pgz_sport.clan_utakmica
           WHERE clan_id = %s
           ORDER BY datum DESC NULLS LAST
           LIMIT 20
        """, (cid,))
        utakmice = [_row(r) for r in cur.fetchall()]

        # LIJEČNIČKI
        cur.execute("""
          SELECT id, datum_pregleda, vrijedi_do, vrsta_pregleda, ustanova, lijecnik,
                 spreman_za_natjecanje, ekg, krv, spirometrija,
                 placeno, iznos, datum_placanja,
                 (vrijedi_do - CURRENT_DATE)::int AS dana_do_isteka,
                 CASE
                   WHEN vrijedi_do IS NULL THEN 'nepoznato'
                   WHEN vrijedi_do < CURRENT_DATE THEN 'istekao'
                   WHEN vrijedi_do <= (CURRENT_DATE + INTERVAL '30 days') THEN 'uskoro'
                   ELSE 'vazeci'
                 END AS status_calc
            FROM pgz_sport.lijecnicki_pregledi
           WHERE clan_id = %s
           ORDER BY datum_pregleda DESC
        """, (cid,))
        lijecnicki = [_row(r) for r in cur.fetchall()]

        # ČLANARINE
        cur.execute("""
          SELECT id, godina, razdoblje, iznos_propisan, iznos_placen,
                 (iznos_propisan - COALESCE(iznos_placen,0))::numeric(10,2) AS dug,
                 datum_uplate, status, racun_broj, referenca, napomena
            FROM pgz_sport.clanarine
           WHERE clan_id = %s
           ORDER BY godina DESC
        """, (cid,))
        clanarine = [_row(r) for r in cur.fetchall()]

        # DOKUMENTI (preko clan_godisnjak)
        cur.execute("""
          SELECT cg.id AS link_id, cg.godina, cg.snippet, cg.has_medal, cg.has_kategorija,
                 d.id AS dokument_id, d.title, d.url, d.pdf_url, d.izvor_url,
                 d.vrsta, d.organizacija, d.izdano_datum
            FROM pgz_sport.clan_godisnjak cg
            JOIN pgz_sport.dokumenti d ON d.id = cg.dokument_id
           WHERE cg.clan_id = %s
           ORDER BY cg.godina DESC
           LIMIT 50
        """, (cid,))
        dokumenti = [_row(r) for r in cur.fetchall()]

        # OBRASCI (form_submissions)
        cur.execute("""
          SELECT s.id, s.template_id, s.template_code, s.status, s.reference_no,
                 s.submitted_at, s.created_at,
                 t.naziv AS template_naziv, t.kategorija
            FROM pgz_sport.form_submissions s
            LEFT JOIN pgz_sport.form_templates t ON t.id = s.template_id
           WHERE s.clan_id = %s
           ORDER BY s.created_at DESC
        """, (cid,))
        obrasci = [_row(r) for r in cur.fetchall()]

        # NAGRADE
        cur.execute("""
          SELECT id, godina, sezona, natjecanje, razina_natjecanja,
                 dobna_kategorija, disciplina, plasman, klub_naziv
            FROM pgz_sport.clan_nagrada
           WHERE clan_id = %s
           ORDER BY godina DESC NULLS LAST
           LIMIT 50
        """, (cid,))
        nagrade = [_row(r) for r in cur.fetchall()]

        # POVIJEST KLUBOVA (iz clan_sezona.klub_naziv distinct)
        cur.execute("""
          SELECT klub_naziv, MIN(sezona) AS od, MAX(sezona) AS do_, COUNT(*) AS broj_sezona
            FROM pgz_sport.clan_sezona
           WHERE clan_id = %s AND klub_naziv IS NOT NULL
           GROUP BY klub_naziv
           ORDER BY MAX(sezona) DESC
        """, (cid,))
        povijest_klubova = [_row(r) for r in cur.fetchall()]

    # KPI / sažetak za panel
    dug_total = sum(float(r.get("dug") or 0) for r in clanarine)
    placeno_total = sum(float(r.get("iznos_placen") or 0) for r in clanarine)
    propisan_total = sum(float(r.get("iznos_propisan") or 0) for r in clanarine)
    last_lij = lijecnicki[0] if lijecnicki else None
    nastupi_total = sum(int(r.get("nastupi") or 0) for r in sezone)
    pogoci_total = sum(int(r.get("pogoci") or 0) for r in sezone)

    return {
        "clan": _row(c),
        "klub": _row(klub) if klub.get("id") else None,
        "kpi": {
            "dug_clanarina_eur": round(dug_total, 2),
            "placeno_clanarina_eur": round(placeno_total, 2),
            "propisan_clanarina_eur": round(propisan_total, 2),
            "lijecnicki_status": last_lij and last_lij.get("status_calc"),
            "lijecnicki_dana_do_isteka": last_lij and last_lij.get("dana_do_isteka"),
            "broj_sezona": len(sezone),
            "broj_utakmica_zadnjih": len(utakmice),
            "nastupi_total": nastupi_total,
            "pogoci_total": pogoci_total,
            "broj_obrazaca": len(obrasci),
            "broj_nagrada": len(nagrade),
        },
        "sezone": sezone,
        "utakmice_zadnje20": utakmice,
        "lijecnicki": lijecnicki,
        "clanarine": clanarine,
        "dokumenti": dokumenti,
        "obrasci": obrasci,
        "nagrade": nagrade,
        "povijest_klubova": povijest_klubova,
    }


# ───────────── edit (PUT s permission gating) ─────────────

class ClanPatch(BaseModel):
    # Sva potencijalno-editabilna polja (subset full schema-e):
    ime: Optional[str] = None
    prezime: Optional[str] = None
    oib: Optional[str] = None
    datum_rodenja: Optional[date] = None
    spol: Optional[str] = None
    mjesto_rodenja: Optional[str] = None
    adresa: Optional[str] = None
    grad: Optional[str] = None
    postanski_broj: Optional[str] = None
    email: Optional[str] = None
    telefon: Optional[str] = None
    kategorija: Optional[str] = None
    podkategorija: Optional[str] = None
    pozicija: Optional[str] = None
    licenca_broj: Optional[str] = None
    licenca_vrijedi_do: Optional[date] = None
    reprezentativac: Optional[bool] = None
    reprezentacija_kategorija: Optional[str] = None
    kategoriziran: Optional[bool] = None
    kategorija_hoo: Optional[int] = None
    stipendiran: Optional[bool] = None
    stipendija_iznos: Optional[float] = None
    radno_pravni_status: Optional[str] = None
    aktivan: Optional[bool] = None
    datum_pristupa: Optional[date] = None
    datum_napustanja: Optional[date] = None
    napomena: Optional[str] = None
    dominantna_noga: Optional[str] = None
    visina_cm: Optional[int] = None
    tezina_kg: Optional[int] = None
    broj_dresa: Optional[int] = None
    biografija: Optional[str] = None
    sport: Optional[str] = None
    uloga: Optional[str] = None
    uloga_detalj: Optional[str] = None
    klub_id: Optional[int] = None
    slika_url: Optional[str] = None


@router.put("/clanovi/{cid}")
def update_clan(cid: int, patch: ClanPatch,
                authorization: Optional[str] = Header(None),
                x_role: Optional[str] = Header(None)):
    role = (x_role or _resolve_role(authorization) or "viewer").lower()

    requested = {k: v for k, v in patch.dict(exclude_unset=True).items() if v is not None}
    if not requested:
        raise HTTPException(400, "Nema polja za izmjenu")

    allowed_fields = _check_field_perm(role, set(requested.keys()))
    if not allowed_fields:
        raise HTTPException(403, f"Role '{role}' nema dozvolu za nijedno od poslanih polja")

    rejected = set(requested.keys()) - allowed_fields
    final = {k: requested[k] for k in allowed_fields}

    set_clauses = [f"{k} = %s" for k in final.keys()]
    set_clauses.append("updated_at = now()")
    params = list(final.values()) + [cid]

    with _conn() as conn, conn.cursor() as cur:
        cur.execute(f"UPDATE pgz_sport.clanovi SET {', '.join(set_clauses)} WHERE id=%s RETURNING *",
                    params)
        r = cur.fetchone()
        if not r:
            raise HTTPException(404, "Član ne postoji")
        # audit log (best-effort)
        try:
            import json as _json
            cur.execute("""INSERT INTO pgz_sport.audit_feed (entity_type, entity_id, action, payload)
                           VALUES (%s,%s,%s,%s::jsonb)""",
                        ("clan", cid, "edit",
                         _json.dumps({"role": role,
                                      "applied": list(final.keys()),
                                      "rejected": list(rejected)})))
        except Exception:
            pass
        conn.commit()

    return {
        "ok": True,
        "id": cid,
        "role": role,
        "applied_fields": sorted(final.keys()),
        "rejected_fields": sorted(rejected),
        "clan": _row(r),
    }


# ───────────── avatar upload ─────────────

@router.post("/clanovi/{cid}/avatar")
async def upload_avatar(cid: int, file: UploadFile = File(...),
                         authorization: Optional[str] = Header(None),
                         x_role: Optional[str] = Header(None)):
    """Upload avatar. R6 #2 demo mode: if there is no/invalid token,
    accept upload but DO NOT persist (FS or DB) — return demo flag + mock URL.
    Real save (FS + DB) requires a valid Bearer JWT for an authorized role."""
    # validate file type early — applies to both demo and real
    allowed_ct = {"image/jpeg", "image/png", "image/webp", "image/gif"}
    ext_map = {"image/jpeg": "jpg", "image/png": "png",
               "image/webp": "webp", "image/gif": "gif"}
    ct = (file.content_type or "").lower()
    if ct not in allowed_ct:
        raise HTTPException(400, f"Nedozvoljeni tip slike: {ct}. Dozvoljeno: jpeg/png/webp/gif")

    contents = await file.read()
    if len(contents) > 5 * 1024 * 1024:
        raise HTTPException(413, "Slika prevelika (max 5 MB)")

    # Try to resolve role from JWT (via auth_v2 — proper secret + revocation check)
    resolved_role = ""
    has_valid_auth = False
    if authorization and authorization.lower().startswith("bearer "):
        tok = authorization.split(" ", 1)[1].strip()
        try:
            import sys as _s; _s.path.insert(0, '/opt/pgz-sport')
            from auth.auth_v2 import decode_token as _dt, _is_revoked as _rev
            payload = _dt(tok)
            if payload.get("typ") in (None, "access") and not _rev(payload.get("jti","")):
                resolved_role = (payload.get("role") or "").lower()
                has_valid_auth = True
        except Exception:
            has_valid_auth = False
    role = (x_role or resolved_role or "").lower()

    # ───── DEMO MODE: no/invalid token → mock storage ─────
    if not has_valid_auth:
        import hashlib as _h
        digest = _h.sha256(contents).hexdigest()[:12]
        mock_fname = f"demo-{cid}-{digest}.{ext_map[ct]}"
        return {
            "ok": True,
            "id": cid,
            "demo_mode": True,
            "message": "Demo mode — slika nije spremljena. Prijavite se za pravu pohranu.",
            "slika_url": None,
            "mock_filename": mock_fname,
            "size_bytes": len(contents),
            "content_type": ct,
            "sha256": digest,
        }

    # ───── REAL SAVE: valid auth + role check ─────
    if role not in EDITABLE_BY_ROLE and role not in ("pgz_admin", "super_admin"):
        raise HTTPException(403, f"Role '{role}' nema dozvolu za upload avatara")

    # provjeri da član postoji
    with _conn() as conn, conn.cursor() as cur:
        cur.execute("SELECT id, slika_url FROM pgz_sport.clanovi WHERE id=%s", (cid,))
        r = cur.fetchone()
        if not r:
            raise HTTPException(404, "Član ne postoji")

    fname = f"{cid}-{_uuid.uuid4().hex[:8]}.{ext_map[ct]}"
    fpath = UPLOADS_DIR / fname
    with open(fpath, "wb") as fh:
        fh.write(contents)

    public_url = f"{PUBLIC_AVATAR_PREFIX}/{fname}"

    with _conn() as conn, conn.cursor() as cur:
        old = r["slika_url"]
        if old and PUBLIC_AVATAR_PREFIX in old:
            try:
                old_name = old.split("/")[-1]
                old_path = UPLOADS_DIR / old_name
                if old_path.exists() and str(old_path).startswith(str(UPLOADS_DIR)):
                    old_path.unlink()
            except Exception:
                pass
        cur.execute("UPDATE pgz_sport.clanovi SET slika_url=%s, updated_at=now() WHERE id=%s",
                    (public_url, cid))
        conn.commit()
    return {
        "ok": True,
        "id": cid,
        "demo_mode": False,
        "slika_url": public_url,
        "size_bytes": len(contents),
        "content_type": ct,
        "filename": fname,
    }


# ───────────── permissions info (za UI) ─────────────

@router.get("/clanovi/permissions")
def permissions_matrix(role: Optional[str] = Query(None)):
    if role:
        r = role.lower()
        allowed = EDITABLE_BY_ROLE.get(r, set())
        return {"role": r, "editable": "ALL" if allowed == "ALL" else sorted(allowed)}
    return {
        "roles": {
            r: ("ALL" if v == "ALL" else sorted(v))
            for r, v in EDITABLE_BY_ROLE.items()
        }
    }