damir/pgz-sport
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
pgz-sport/pgz_sport_api.py_prije_env_deepseek
T

3009 lines
136 KiB
Python
Raw Permalink Blame History

#!/usr/bin/env python3
"""
pgz_sport_api.py - FastAPI backend za PGŽ Sportski savez ERP/CRM
Author: Damir Radulić (damir@rinet.one / dradulic@outlook.com)
Date: 25.04.2026 (v1.1.0 — 2026-05-05: role-based OIB display + audit log)
Port: 8095
Endpoints: savezi, klubovi, članovi, članarine, liječnički, manifestacije, proračun, dashboard, alertovi
Changes (2026-05-05, sub-agent W5):
* is_admin() — recognizes super_admin / pgz_admin / pgz_user / pgz_finance /
pgz_zzjz JWT roles (previous code only matched literal "admin", which broke
PII visibility for actual PGŽ admins like Damir).
* apply_privacy() — now scope-aware: savez_admin sees full PII for own savez,
klub_admin sees full PII for own klub.
* Added _audit_oib_access() — records full-OIB reveals to Postgres audit_events
(table pgz_sport.audit_events) under action='oib.read'. Legitimate-interest
audit trail for GDPR Art.6(1)(f) defensibility.
"""
from fastapi import FastAPI, HTTPException, Query, Body, Header, Depends, UploadFile, File, Form, Request
import json
import time
from fastapi.middleware.cors import CORSMiddleware
from pydantic import BaseModel
from typing import Optional, List
from datetime import date, datetime
import psycopg2
import psycopg2.extras
from pgz_sport_v2_router import router as v2_router
import os
DB = dict(host='10.10.0.2', port=6432, dbname='rinet_v3', user='rinet', password=os.environ["DB_PASSWORD"])
ADMIN_TOKEN = 'admin-pgz-2026'
# Roles that get full PII visibility globally (PGŽ tier).
# Mirrors auth/auth_v2.py PGZ_USER_TYPES; kept local to avoid import cycle.
_PGZ_FULL_PII_ROLES = {
"super_admin", "pgz_admin", "pgz_user", "pgz_finance", "pgz_zzjz",
"admin", # legacy bearer-token role
}
_SAVEZ_PII_ROLES = {"savez_admin", "savez_user"}
_KLUB_PII_ROLES = {"klub_admin", "klub_user", "klub_trener", "klub_clan"}
def _decode_jwt_safe(authorization):
"""Decode the bearer JWT using the same secret as auth_v2.
Returns the payload dict on success, None otherwise. Never raises."""
if not authorization:
return None
token = authorization.replace('Bearer ', '').strip()
if not token or token == ADMIN_TOKEN:
return None
try:
from auth.auth_v2 import decode_token as _decode
return _decode(token)
except Exception:
return None
def auth_context(authorization):
"""Returns (role, klub_id, savez_id, email) — never raises.
role is one of: super_admin / pgz_admin / savez_admin / klub_admin /
viewer / 'admin' (legacy token) / None (unauthenticated)."""
if not authorization:
return (None, None, None, None)
token = authorization.replace('Bearer ', '').strip()
if token == ADMIN_TOKEN:
return ('admin', None, None, 'legacy-bearer')
payload = _decode_jwt_safe(authorization) or {}
role = (payload.get("role") or "viewer").lower()
scope = payload.get("tenant_scope") or {}
return (role, scope.get("klub_id"), scope.get("savez_id"), payload.get("email"))
def is_admin(authorization):
"""Backward-compatible boolean: True iff caller has unscoped full-PII access.
Now correctly recognizes super_admin / pgz_admin / pgz_user / pgz_finance /
pgz_zzjz JWT roles, not just literal 'admin'."""
role, _kid, _sid, _e = auth_context(authorization)
return role in _PGZ_FULL_PII_ROLES
def can_see_full_pii(authorization, klub_id=None, savez_id=None):
"""Scope-aware PII gate.
PGŽ-tier roles: full PII everywhere.
savez_admin/savez_user: full PII when row.savez_id == own savez_id.
klub_admin/klub_user/klub_trener/klub_clan: full PII when row.klub_id == own klub_id.
Otherwise: masked."""
role, kid, sid, _ = auth_context(authorization)
if role in _PGZ_FULL_PII_ROLES:
return True
if role in _SAVEZ_PII_ROLES and sid is not None and savez_id is not None and int(sid) == int(savez_id):
return True
if role in _KLUB_PII_ROLES and kid is not None and klub_id is not None and int(kid) == int(klub_id):
return True
return False
def _audit_oib_access(authorization, resource_type, resource_id, count=1, reason="legitimate_interest"):
"""Log a full-OIB reveal to pgz_sport.audit_events (best-effort, never raises).
Used for GDPR Art.6(1)(f) defensibility. One row per request, not per OIB."""
try:
role, _kid, _sid, email = auth_context(authorization)
if role is None:
return # only log authenticated reveals
from auth.auth_v2 import audit as _audit
# uid not directly available without re-decoding; pull from payload
payload = _decode_jwt_safe(authorization) or {}
uid = payload.get("uid")
_audit(uid, "oib.read", resource_type=resource_type, resource_id=resource_id,
meta={"role": role, "email": email, "count": count, "reason": reason})
except Exception as _e:
# Audit must never break the request path
try:
print(f"[OIB_AUDIT WARN] {_e}")
except Exception:
pass
def blur_oib(v):
if not v: return v
s = str(v);
return s[:3] + '•'*(len(s)-5) + s[-2:] if len(s) >= 8 else '•'*len(s)
def blur_email(e):
if not e or '@' not in str(e): return e
u, d = str(e).split('@',1); return (u[:1]+'•••' if u else '')+'@'+d
def blur_phone(p):
if not p: return p
s=str(p); return s[:4]+'•'*(len(s)-7)+s[-3:] if len(s)>=7 else s
def blur_iban(v):
if not v: return v
s=str(v); return s[:4]+'•'*(len(s)-8)+s[-4:] if len(s)>=8 else s
def blur_date(d):
if not d: return d
s = str(d); return s[:4]+'-••-••' if len(s)>=4 else s
def blur_text(t, keep=3):
if not t: return t
s=str(t); return s[:keep]+'•'*(len(s)-keep*2)+s[-keep:] if len(s)>keep*2 else s
def apply_privacy(rows, admin, authorization=None):
"""Apply per-row privacy masking.
`admin`: legacy global override — when True, NOTHING is masked.
`authorization`: when provided, enables per-row scope-aware reveals
(savez_admin sees own savez rows in clear; klub_admin sees own klub
rows in clear). Falls back to row-level mask if scope mismatches.
"""
if admin: return rows
is_list = isinstance(rows, list)
out = []
for r in (rows if is_list else [rows]):
rr = dict(r)
# Per-row scope check (only relevant when authorization is supplied)
row_full = False
if authorization is not None:
row_full = can_see_full_pii(authorization,
klub_id=rr.get("klub_id") or rr.get("id_klub"),
savez_id=rr.get("savez_id") or rr.get("id_savez"))
if row_full:
out.append(rr)
continue
for k, v in list(rr.items()):
if v is None: continue
kl = k.lower()
if 'oib' in kl: rr[k] = blur_oib(v)
elif 'email' in kl: rr[k] = blur_email(v)
elif kl in ('telefon','tel','phone'): rr[k] = blur_phone(v)
elif kl == 'datum_rodenja': rr[k] = blur_date(v)
elif 'iban' in kl: rr[k] = blur_iban(v)
elif kl == 'adresa': rr[k] = blur_text(v, 3)
elif 'licenca_broj' in kl: rr[k] = blur_text(v, 2)
out.append(rr)
return out if is_list else out[0]
app = FastAPI(title="PGŽ Sportski savez ERP/CRM", version="1.0.0")
app.add_middleware(CORSMiddleware, allow_origins=["*"], allow_methods=["*"], allow_headers=["*"])
# ─── R5 #1 + R6 #1: Defense-in-depth JWT enforcement ───
# Mutating requests (POST/PUT/PATCH/DELETE) under /api/* require a valid
# Bearer JWT, except for explicitly-public auth & consent endpoints.
# All /api/admin/* requests (any method) also require auth.
_PUBLIC_MUTATING_PATHS = {
"/api/auth/login", "/api/auth/refresh", "/api/auth/forgot-password",
"/api/auth/password/reset", "/api/auth/reset-password",
"/api/auth/setup-password", "/api/auth/google",
"/api/gdpr/consent",
}
_PUBLIC_MUTATING_SUFFIXES = (
"/avatar", # /api/crm/clanovi/{id}/avatar — demo mode handled in handler
)
# CC6: enrichment endpoints are demo-mode public — they only fill empty
# fields, never overwrite, and are heavily audited. The worker daemon also
# hits them anonymously over loopback.
_PUBLIC_MUTATING_PREFIXES = (
"/api/v2/enrich/",
"/api/v2/export/", # ui-sprint: read-only export, mirrors public GET data
)
@app.middleware("http")
async def require_jwt_middleware(request, call_next):
p = request.url.path
method = request.method.upper()
if method == "OPTIONS":
return await call_next(request)
admin_gate = p.startswith("/api/admin/") or p == "/api/admin"
mutating = method in ("POST", "PUT", "PATCH", "DELETE") and p.startswith("/api/")
if mutating and (p in _PUBLIC_MUTATING_PATHS or
any(p.endswith(s) for s in _PUBLIC_MUTATING_SUFFIXES) or
any(p.startswith(s) for s in _PUBLIC_MUTATING_PREFIXES)):
mutating = False
if not (admin_gate or mutating):
return await call_next(request)
try:
from auth.auth_v2 import decode_token, _is_revoked
except Exception as e:
print(f"[JWT-MW import WARN] {e}")
return await call_next(request)
from starlette.responses import JSONResponse as _JR
auth_h = request.headers.get("authorization", "")
if not auth_h.lower().startswith("bearer "):
return _JR({"detail": "Authentication required"}, status_code=401)
token = auth_h.split(" ", 1)[1].strip()
try:
payload = decode_token(token)
except Exception:
return _JR({"detail": "Invalid or expired token"}, status_code=401)
if payload.get("typ") not in (None, "access"):
return _JR({"detail": "Wrong token type"}, status_code=401)
if _is_revoked(payload.get("jti", "")):
return _JR({"detail": "Token revoked"}, status_code=401)
return await call_next(request)
# === URL rewrite middleware - convert direct external image URLs to /img-proxy ===
import json as _json_mw
import re as _re_mw
from starlette.responses import Response as _StarletteResponse_mw
_IMG_DOMAINS_RE = _re_mw.compile(
r'https?://(?:hns\.family|hns\.hr|hbs\.hr|hrvatski-bocarski-savez\.hr|'
r'rk-zamet\.hr|hvs\.hr|rezultati\.hvs\.hr|sport-pgz\.hr)'
r'/[^"\s\\]+\.(?:jpg|jpeg|png|gif|webp|svg)',
_re_mw.IGNORECASE
)
def _rewrite_to_proxy(text: str) -> str:
"""Replace external image URLs with /sport/api/v2/img-proxy?u=..."""
from urllib.parse import quote as _q
def _sub(m):
url = m.group(0)
return "/sport/api/v2/img-proxy?u=" + _q(url, safe='')
return _IMG_DOMAINS_RE.sub(_sub, text)
@app.middleware("http")
async def url_rewrite_middleware(request, call_next):
response = await call_next(request)
# Only rewrite JSON API responses
ct = response.headers.get("content-type", "")
if "application/json" not in ct:
return response
# Only on /api/v2 routes (admin & data endpoints) - SKIP /api/v2/img-proxy itself
path = request.url.path
if "/api/v2/img-proxy" in path or "/api/v2/dokumenti" in path:
return response # don't rewrite raw document content
# Read body
body = b""
async for chunk in response.body_iterator:
body += chunk
try:
text = body.decode("utf-8")
new_text = _rewrite_to_proxy(text)
new_body = new_text.encode("utf-8")
except Exception:
new_body = body
return _StarletteResponse_mw(
content=new_body,
status_code=response.status_code,
headers={k: v for k, v in response.headers.items() if k.lower() not in ("content-length",)},
media_type=ct,
)
# === end URL rewrite middleware ===
def db():
conn = psycopg2.connect(**DB)
conn.autocommit = True
return conn
def fetch(sql, params=None):
with db() as conn:
with conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) as c:
c.execute(sql, params or ())
return [dict(r) for r in c.fetchall()]
def execute(sql, params=None):
with db() as conn:
with conn.cursor() as c:
c.execute(sql, params or ())
return c.rowcount
# ==================== HEALTH ====================
@app.get("/health")
def health():
try:
rows = fetch("SELECT * FROM pgz_sport.v_dashboard")
return {"status": "ok", "service": "pgz_sport", "dashboard": rows[0] if rows else None}
except Exception as e:
raise HTTPException(500, f"DB error: {e}")
@app.get("/api/whoami")
def whoami_v2(authorization: Optional[str] = Header(None)):
role, klub_id, savez_id, email = auth_context(authorization)
full_pii = is_admin(authorization)
return {
"role": role or "viewer",
# Legacy boolean retained for backward compat with old frontend code
"is_admin": full_pii,
"privacy_active": not full_pii,
"scope": {"klub_id": klub_id, "savez_id": savez_id},
"email": email,
}
# ==================== DASHBOARD ====================
@app.get("/api/dashboard")
def dashboard():
rows = fetch("SELECT * FROM pgz_sport.v_dashboard")
if not rows:
return {}
d = rows[0]
# Top savezi by registriranih 2024
top = fetch("""SELECT s.naziv, st.klubova_clanica, st.registriranih, st.trenera, st.reprezentativaca
FROM pgz_sport.statistika_saveza st JOIN pgz_sport.savezi s ON s.id=st.savez_id
WHERE st.godina=2024 ORDER BY st.registriranih DESC LIMIT 10""")
proracun_trend = fetch("SELECT godina, ukupno FROM pgz_sport.proracun ORDER BY godina")
nositelji = fetch("""SELECT naziv_kluba, godina, iznos FROM pgz_sport.potpore_nositelji
WHERE godina = 2025 ORDER BY iznos DESC LIMIT 10""")
return {**d, "top_savezi": top, "proracun_trend": proracun_trend, "nositelji_2025": nositelji}
@app.get("/api/kpi")
def api_kpi():
"""CC6 analitika — single-payload KPI for /kpi page.
Returns top-level counts (savezi, klubovi, sportasi, members),
proracun current/trend, top10 sufinanciranje, sport distribution,
drill-down hooks, and a heartbeat so the page can refresh.
"""
counts_row = fetch("""SELECT
(SELECT COUNT(*) FROM pgz_sport.savezi) AS savezi,
(SELECT COUNT(*) FROM pgz_sport.klubovi WHERE aktivan) AS klubovi,
(SELECT COUNT(*) FROM pgz_sport.clanovi WHERE aktivan) AS sportasi,
(SELECT COUNT(*) FROM pgz_sport.clanovi WHERE aktivan AND reprezentativac) AS reprezentativci,
(SELECT COUNT(*) FROM pgz_sport.sportski_objekti WHERE aktivan) AS objekti,
(SELECT COUNT(*) FROM pgz_sport.manifestacije) AS manifestacije
""")
counts = counts_row[0] if counts_row else {}
proracun_trend = fetch("SELECT godina, ukupno FROM pgz_sport.proracun ORDER BY godina")
proracun_2026 = next((r['ukupno'] for r in proracun_trend if r.get('godina') == 2026), None)
top_sufin = fetch("""SELECT naziv_kluba, godina, iznos
FROM pgz_sport.potpore_nositelji
WHERE godina = 2025
ORDER BY iznos DESC NULLS LAST
LIMIT 10""")
by_sport = fetch("""SELECT sport, COUNT(*)::int AS broj
FROM pgz_sport.klubovi
WHERE aktivan AND sport IS NOT NULL
GROUP BY sport
ORDER BY COUNT(*) DESC
LIMIT 15""")
by_region = fetch("""SELECT COALESCE(region, 'N/A') AS region, COUNT(*)::int AS broj
FROM pgz_sport.klubovi
WHERE aktivan
GROUP BY region
ORDER BY COUNT(*) DESC""")
# Liječnički expiring (next 30d) — ops widget
lijec_expiring = fetch("""SELECT COUNT(*)::int AS n
FROM pgz_sport.lijecnicki_pregledi
WHERE vrijedi_do BETWEEN CURRENT_DATE
AND CURRENT_DATE + INTERVAL '30 days'""")
lijec_expiring_n = lijec_expiring[0]['n'] if lijec_expiring else 0
return {
"as_of": datetime.now().isoformat(timespec='seconds'),
"counts": counts,
"proracun_2026": proracun_2026,
"proracun_trend": proracun_trend,
"top_sufinanciranje_2025": top_sufin,
"klubovi_by_sport": by_sport,
"klubovi_by_region": by_region,
"lijecnicki_expiring_30d": lijec_expiring_n,
"drill_down": {
"savezi": "/api/v2/savezi",
"klubovi": "/api/klubovi",
"sportasi": "/api/clanovi-full",
"objekti": "/api/sportski-objekti",
},
}
@app.get("/api/dashboard/top-primatelji")
def dashboard_top_primatelji(godina: int = 2025, davatelj: str = None, samo_klubovi: bool = True, limit: int = 100):
"""Top primatelji javnih potreba s davatelj filter + PDF link na godišnjak."""
where = []
params = []
if godina and godina > 0:
where.append("pn.godina = %s")
params.append(godina)
if davatelj and davatelj != 'all':
where.append("pn.davatelj = %s")
params.append(davatelj)
if samo_klubovi:
where.append("(pn.je_klub IS NULL OR pn.je_klub = true)")
where_sql = "WHERE " + " AND ".join(where) if where else ""
rows = fetch(f"""
SELECT
pn.id,
pn.naziv_kluba,
pn.klub_id,
pn.iznos,
COALESCE(LEFT(pn.napomena, 60), '') AS napomena_short,
pn.napomena,
pn.godina,
COALESCE(pn.davatelj, 'RSS (Riječki sportski savez)') AS davatelj,
COALESCE(pn.vrsta, 'Javne potrebe') AS vrsta,
COALESCE(k.sport, 'n/a') AS sport,
COALESCE(s.naziv, '') AS savez_naziv,
COALESCE(k.razina, '') AS razina,
COALESCE(k.grad, '') AS grad,
d.id AS doc_id,
COALESCE(d.pdf_url, d.izvor_url, '/sport/api/v2/dokumenti/godisnjak/' || pn.godina::text) AS pdf_url,
COALESCE(d.title, 'Godišnjak ' || pn.godina::text) AS doc_title
FROM pgz_sport.potpore_nositelji pn
LEFT JOIN pgz_sport.klubovi k ON k.id = pn.klub_id
LEFT JOIN pgz_sport.savezi s ON s.id = k.savez_id
LEFT JOIN pgz_sport.dokumenti d ON d.vrsta='godisnjak' AND d.godina = pn.godina
{where_sql}
ORDER BY pn.iznos DESC NULLS LAST, pn.naziv_kluba
LIMIT %s
""", tuple(params) + (limit,))
# Stats summary
stats = fetch(f"""
SELECT
count(*) AS total_records,
sum(iznos)::numeric(12,2) AS total_amount,
count(DISTINCT naziv_kluba) AS unique_klubova,
count(DISTINCT davatelj) AS unique_davatelji
FROM pgz_sport.potpore_nositelji pn
{where_sql}
""", tuple(params))
# Available years (always)
years = fetch("""
SELECT godina, count(*) AS broj, sum(iznos)::numeric(12,2) AS suma
FROM pgz_sport.potpore_nositelji
GROUP BY godina ORDER BY godina DESC
""")
# Available davatelji (always)
davatelji = fetch("""
SELECT DISTINCT COALESCE(davatelj, 'Nepoznato') AS davatelj
FROM pgz_sport.potpore_nositelji ORDER BY 1
""")
return {
"godina": godina,
"davatelj": davatelj or "all",
"rows": rows,
"summary": stats[0] if stats else {},
"available_years": years,
"available_davatelji": [d["davatelj"] for d in davatelji],
}
@app.get("/api/dashboard/ekosustav")
def dashboard_ekosustav():
"""Sport ekosustav PGŽ — coverage stats za enrichment iz FINA registra."""
summary = fetch("""SELECT
COUNT(*) AS klubova_total,
COUNT(*) FILTER (WHERE oib IS NOT NULL) AS s_oib,
COUNT(*) FILTER (WHERE predsjednik IS NOT NULL) AS s_predsjednik,
COUNT(*) FILTER (WHERE tajnik IS NOT NULL) AS s_tajnik,
COUNT(*) FILTER (WHERE ciljevi IS NOT NULL) AS s_ciljevi,
COUNT(*) FILTER (WHERE opis_djelatnosti IS NOT NULL) AS s_opis,
COUNT(*) FILTER (WHERE sjediste IS NOT NULL) AS s_sjediste,
COUNT(*) FILTER (WHERE email IS NOT NULL) AS s_email,
COUNT(*) FILTER (WHERE web_stranica IS NOT NULL) AS s_web,
COUNT(*) FILTER (WHERE udruga_status = \'AKTIVAN\') AS s_aktivan_reg,
COUNT(*) FILTER (WHERE savez_id IS NOT NULL) AS s_savez,
COUNT(*) FILTER (WHERE nositelj_kvalitete) AS s_nositelj
FROM pgz_sport.klubovi WHERE aktivan""")[0]
by_sport = fetch("""SELECT sport, COUNT(*) AS broj
FROM pgz_sport.klubovi WHERE aktivan AND sport IS NOT NULL
GROUP BY sport ORDER BY COUNT(*) DESC LIMIT 15""")
by_region = fetch("""SELECT region, COUNT(*) AS broj
FROM pgz_sport.klubovi WHERE aktivan AND region IS NOT NULL
GROUP BY region ORDER BY COUNT(*) DESC""")
by_grad = fetch("""SELECT grad, COUNT(*) AS broj
FROM pgz_sport.klubovi WHERE aktivan AND grad IS NOT NULL
GROUP BY grad ORDER BY COUNT(*) DESC LIMIT 12""")
decade = fetch("""SELECT
CASE
WHEN godina_osnutka < 1950 THEN \'pred1950\'
WHEN godina_osnutka < 1980 THEN \'1950-1979\'
WHEN godina_osnutka < 2000 THEN \'1980-1999\'
WHEN godina_osnutka < 2010 THEN \'2000-2009\'
WHEN godina_osnutka >= 2010 THEN \'2010-danas\'
ELSE \'nepoznato\'
END AS razdoblje,
COUNT(*) AS broj
FROM pgz_sport.klubovi
WHERE aktivan AND godina_osnutka IS NOT NULL
GROUP BY razdoblje ORDER BY razdoblje""")
# Pokazi enrichment %
total = summary["klubova_total"] or 1
coverage = {
"oib_pct": round(100 * summary["s_oib"] / total, 1),
"predsjednik_pct": round(100 * summary["s_predsjednik"] / total, 1),
"tajnik_pct": round(100 * summary["s_tajnik"] / total, 1),
"ciljevi_pct": round(100 * summary["s_ciljevi"] / total, 1),
"opis_pct": round(100 * summary["s_opis"] / total, 1),
"sjediste_pct": round(100 * summary["s_sjediste"] / total, 1),
"email_pct": round(100 * summary["s_email"] / total, 1),
"savez_pct": round(100 * summary["s_savez"] / total, 1),
}
return {**summary, "coverage": coverage, "by_sport": by_sport,
"by_region": by_region, "by_grad": by_grad, "by_decade": decade}
# ==================== ANALYTICS ====================
@app.get("/api/analytics/savezi-trend")
def savezi_trend(godine: str = "2020,2021,2022,2023,2024", metric: str = "registriranih"):
valid_metrics = {"registriranih", "neregistriranih", "rekreativaca", "trenera", "reprezentativaca",
"kategoriziranih", "stipendiranih", "klubova_clanica"}
if metric not in valid_metrics:
raise HTTPException(400, f"Invalid metric. Must be one of: {valid_metrics}")
god_list = [int(g) for g in godine.split(",")]
rows = fetch(f"""SELECT s.naziv AS savez, st.godina, st.{metric} AS value
FROM pgz_sport.statistika_saveza st JOIN pgz_sport.savezi s ON s.id=st.savez_id
WHERE st.godina = ANY(%s) ORDER BY s.naziv, st.godina""", [god_list])
saveze = {}
for r in rows:
if r['savez'] not in saveze: saveze[r['savez']] = {}
saveze[r['savez']][r['godina']] = r['value']
return {"metric": metric, "godine": god_list, "data": saveze}
@app.get("/api/analytics/proracun-detaljno")
def proracun_detaljno():
p = fetch("SELECT * FROM pgz_sport.proracun ORDER BY godina")
if not p: return {"proracun": [], "rast_godisnji": [], "current_year": None, "current_total": 0, "rast_dekada_pct": 0}
cagr = []
for i in range(1, len(p)):
prev = float(p[i-1]['ukupno']) if p[i-1]['ukupno'] else 0
curr = float(p[i]['ukupno']) if p[i]['ukupno'] else 0
rate = ((curr/prev - 1) * 100) if prev > 0 else 0
cagr.append({"godina": p[i]['godina'], "rast_postotak": round(rate, 1)})
decade_rast = round((float(p[-1]['ukupno'])/float(p[0]['ukupno']) - 1) * 100, 1) if p[0]['ukupno'] else 0
return {"proracun": p, "rast_godisnji": cagr, "rast_dekada_pct": decade_rast,
"current_year": int(p[-1]['godina']), "current_total": float(p[-1]['ukupno'])}
@app.get("/api/analytics/klub-financije")
def klub_financije(klub_id: Optional[int] = None, godina: Optional[int] = None):
where = []
params = []
if godina: where.append("p.godina=%s"); params.append(godina)
if klub_id:
where.append("(p.klub_id=%s OR p.naziv_kluba=(SELECT naziv FROM pgz_sport.klubovi WHERE id=%s))")
params.extend([klub_id, klub_id])
where_sql = "WHERE " + " AND ".join(where) if where else ""
rows = fetch(f"""SELECT p.naziv_kluba, p.godina, p.iznos,
k.id AS klub_id, k.sport, k.razina, k.nositelj_kvalitete
FROM pgz_sport.potpore_nositelji p
LEFT JOIN pgz_sport.klubovi k ON p.klub_id=k.id OR p.naziv_kluba=k.naziv
{where_sql} ORDER BY p.godina DESC, p.iznos DESC""", params)
summary = fetch(f"""SELECT godina, SUM(iznos) AS total, COUNT(*) AS klubova, AVG(iznos) AS prosjek
FROM pgz_sport.potpore_nositelji p {where_sql}
GROUP BY godina ORDER BY godina""", params)
return {"data": rows, "summary": summary}
@app.get("/api/analytics/lijecnicki-stats")
def lijecnicki_stats(klub_id: Optional[int] = None):
where = ["1=1"]; params = []
if klub_id: where.append("c.klub_id=%s"); params.append(klub_id)
where_sql = " AND ".join(where)
rows = fetch(f"""SELECT
COUNT(*) AS total,
COUNT(*) FILTER (WHERE lp.vrijedi_do >= CURRENT_DATE + 30) AS validni,
COUNT(*) FILTER (WHERE lp.vrijedi_do BETWEEN CURRENT_DATE AND CURRENT_DATE + 30) AS uskoro,
COUNT(*) FILTER (WHERE lp.vrijedi_do < CURRENT_DATE) AS istekli,
SUM(lp.iznos) AS ukupan_trosak, SUM(lp.iznos_zzjz) AS zzjz_udio,
SUM(lp.iznos_klub) AS klub_udio, SUM(lp.iznos_clan) AS clan_udio,
AVG(lp.iznos) AS prosjecni_trosak
FROM pgz_sport.lijecnicki_pregledi lp
JOIN pgz_sport.clanovi c ON c.id=lp.clan_id WHERE {where_sql}""", params)
by_ustanova = fetch(f"""SELECT lp.ustanova, COUNT(*) cnt, SUM(lp.iznos) iznos
FROM pgz_sport.lijecnicki_pregledi lp JOIN pgz_sport.clanovi c ON c.id=lp.clan_id
WHERE {where_sql} GROUP BY lp.ustanova ORDER BY cnt DESC""", params)
by_lijecnik = fetch(f"""SELECT lp.lijecnik, COUNT(*) cnt, AVG(lp.iznos) prosjek
FROM pgz_sport.lijecnicki_pregledi lp JOIN pgz_sport.clanovi c ON c.id=lp.clan_id
WHERE {where_sql} AND lp.lijecnik IS NOT NULL GROUP BY lp.lijecnik ORDER BY cnt DESC""", params)
return {"summary": rows[0] if rows else {}, "by_ustanova": by_ustanova, "by_lijecnik": by_lijecnik}
# ==================== SAVEZI ====================
@app.get("/api/savezi")
def list_savezi(authorization: Optional[str] = Header(None), q: Optional[str] = None,
razina: Optional[str] = None, zupanija: Optional[str] = None,
sport: Optional[str] = None, kategorija: Optional[str] = None,
sort: str = "naziv", order: str = "asc"):
where = "WHERE aktivan"
params = []
if q:
where += " AND (public.f_unaccent(naziv) ILIKE public.f_unaccent(%s) OR sport ILIKE %s)"
params = [f"%{q}%", f"%{q}%"]
if razina:
where += " AND razina = %s"; params.append(razina)
if zupanija:
where += " AND sjediste_zupanija ILIKE %s"; params.append(f"%{zupanija}%")
if sport:
where += " AND sport ILIKE %s"; params.append(f"%{sport}%")
if kategorija:
kt = kategorija.strip().lower()
if kt in ("zupanijski","županijski"):
where += " AND lower(unaccent(razina)) ILIKE %s"; params.append("%zupanij%")
elif kt == "gradski":
where += " AND lower(unaccent(razina)) ILIKE %s"; params.append("%gradsk%")
else:
where += " AND lower(unaccent(razina)) ILIKE lower(unaccent(%s))"; params.append(f"%{kategorija}%")
sort_col = {"naziv": "naziv", "godina": "godina_osnutka", "sport": "sport", "razina": "razina"}.get(sort, "naziv")
order = "DESC" if order.lower() == "desc" else "ASC"
# Croatian collation for text columns (Š → after S, Č → after C, etc.)
collate = ' COLLATE "hr-HR-x-icu"' if sort_col in ("naziv", "sport") else ""
rows = fetch(f"""SELECT s.*,
(SELECT COUNT(*) FROM pgz_sport.klubovi WHERE savez_id=s.id) AS broj_klubova,
(SELECT registriranih FROM pgz_sport.statistika_saveza WHERE savez_id=s.id AND godina=2024) AS reg_2024,
(SELECT trenera FROM pgz_sport.statistika_saveza WHERE savez_id=s.id AND godina=2024) AS treneri_2024,
(SELECT reprezentativaca FROM pgz_sport.statistika_saveza WHERE savez_id=s.id AND godina=2024) AS repr_2024
FROM pgz_sport.savezi s {where} ORDER BY {sort_col}{collate} {order}""", params)
admin = is_admin(authorization)
rows = apply_privacy(rows, admin, authorization=authorization)
if admin:
_audit_oib_access(authorization, "savez_list", None, count=len(rows))
return {"count": len(rows), "rows": rows}
@app.get("/api/savezi/{savez_id}")
def get_savez(savez_id: int, authorization: Optional[str] = Header(None)):
rows = fetch("SELECT * FROM pgz_sport.savezi WHERE id=%s", [savez_id])
if not rows:
raise HTTPException(404, "Savez ne postoji")
klubovi = fetch("SELECT * FROM pgz_sport.klubovi WHERE savez_id=%s ORDER BY naziv", [savez_id])
statistika = fetch("SELECT * FROM pgz_sport.statistika_saveza WHERE savez_id=%s ORDER BY godina", [savez_id])
manifestacije = fetch("SELECT * FROM pgz_sport.manifestacije WHERE savez_id=%s", [savez_id])
admin = is_admin(authorization)
savez = rows[0]
if not admin:
savez = apply_privacy(savez, admin, authorization=authorization)
klubovi = apply_privacy(klubovi, admin, authorization=authorization)
else:
_audit_oib_access(authorization, "savez", savez_id, count=1+len(klubovi))
return {**savez, "klubovi": klubovi, "statistika": statistika, "manifestacije": manifestacije}
# ==================== KLUBOVI ====================
# ─────────────────────────────────────────────────────────────────────
# Endpoint: GET /api/klubovi
# Author: Damir Radulić (dradulic@outlook.com / damir@rinet.one)
# Date: 2026-05-05 (RUSH-1 klubovi filter sprint)
# Note: - `financiran` filter is now the OR of PGŽ + RSS + Grad Rijeka
# (combined source of truth via v_klubovi_financiranje view).
# - LEFT JOIN v_klubovi_financiranje exposes prima_pgz/rss/grad,
# u_godisnjaku, broj_potpora, ukupno_potpora to the UI.
# - New sort key `potpora` orders by ukupno_potpora DESC NULLS LAST.
# - `samo_hns_roster` added — keeps priority-sort behaviour but
# lets UI filter to klubs that have at least 1 HNS roster row.
# ─────────────────────────────────────────────────────────────────────
@app.get("/api/klubovi")
def list_klubovi(authorization: Optional[str] = Header(None), q: Optional[str] = None, savez_id: Optional[int] = None,
nositelj: Optional[bool] = None, region: Optional[str] = None, sport: Optional[str] = None, grad: Optional[str] = None,
kategorija: Optional[str] = None, godisnjak: Optional[bool] = None, financiran: Optional[bool] = None,
samo_hns_roster: Optional[bool] = None,
sort: str = "naziv", order: str = "asc"):
# financiran = OR of all 3 davateljs (PGŽ + RSS + Grad Rijeka) — single source of truth
# is v_klubovi_financiranje view (driven by potpore_nositelji). Legacy
# k.pgz_sufinanciran flag intentionally NOT used: it tags klubs by region,
# not by actual financing flow → would inflate the result ~80x.
fin_expr = "(COALESCE(f.prima_pgz,false) OR COALESCE(f.prima_rss,false) OR COALESCE(f.prima_grad_rijeka,false))"
god_expr = "(COALESCE(f.u_godisnjaku,false) OR (k.godisnjak_godine IS NOT NULL AND array_length(k.godisnjak_godine,1) > 0))"
priority_expr = f"({fin_expr} OR {god_expr})"
where = ["v.aktivan"]
params = []
if q:
where.append("(v.klub ILIKE %s OR v.oib ILIKE %s OR v.sport ILIKE %s OR v.predsjednik ILIKE %s)")
params.extend([f"%{q}%", f"%{q}%", f"%{q}%", f"%{q}%"])
if savez_id:
where.append("v.id IN (SELECT id FROM pgz_sport.klubovi WHERE savez_id=%s)"); params.append(savez_id)
if nositelj is not None:
where.append(f"v.nositelj_kvalitete={'TRUE' if nositelj else 'FALSE'}")
if region:
where.append("v.region ILIKE %s"); params.append(region)
if grad:
where.append("public.f_unaccent(v.grad) ILIKE public.f_unaccent(%s)"); params.append(f"%{grad}%")
if sport:
where.append("v.sport ILIKE %s"); params.append(f"%{sport}%")
if financiran is not None:
where.append(f"{fin_expr} = {'TRUE' if financiran else 'FALSE'}")
if godisnjak is not None:
where.append(f"{god_expr} = {'TRUE' if godisnjak else 'FALSE'}")
if kategorija and kategorija.strip().lower() == "priority":
where.append(priority_expr)
if samo_hns_roster:
where.append("EXISTS (SELECT 1 FROM pgz_sport.hns_klub_roster r WHERE r.klub_id = k.id)")
# Sort: `potpora` = ukupno_potpora DESC; keep legacy keys.
sort_col = {"naziv": "v.klub", "savez": "v.savez", "broj_clanova": "v.broj_clanova",
"razina": "v.razina", "region": "v.region", "grad": "v.grad", "sport": "v.sport",
"potpora": "f.ukupno_potpora", "ukupno_potpora": "f.ukupno_potpora",
"financiran": "f.ukupno_potpora"}.get(sort, "v.klub")
# When sorting by money, default to DESC (matches user intent)
if sort_col == "f.ukupno_potpora" and order.lower() not in ("asc","desc"):
order = "desc"
order_sql = "DESC" if order.lower() == "desc" else "ASC"
where_sql = " AND ".join(where) if where else "TRUE"
collate = ' COLLATE "hr-HR-x-icu"' if sort_col in ("v.klub", "v.savez", "v.razina", "v.region", "v.grad", "v.sport") else ""
rows = fetch(f"""SELECT v.*,
{fin_expr} AS financiran,
{god_expr} AS godisnjak,
{priority_expr} AS priority,
COALESCE(f.prima_pgz,false) AS prima_pgz,
COALESCE(f.prima_rss,false) AS prima_rss,
COALESCE(f.prima_grad_rijeka,false) AS prima_grad_rijeka,
COALESCE(f.u_godisnjaku,false) AS u_godisnjaku,
f.broj_potpora,
f.ukupno_potpora,
k.godisnjak_godine, k.godisnjak_prvi, k.godisnjak_zadnji
FROM pgz_sport.v_klubovi_pregled v
LEFT JOIN pgz_sport.klubovi k ON k.id = v.id
LEFT JOIN pgz_sport.v_klubovi_financiranje f ON f.id = v.id
WHERE {where_sql}
ORDER BY {priority_expr} DESC NULLS LAST,
{sort_col}{collate} {order_sql} NULLS LAST""", params)
for r in rows:
if isinstance(r, dict) and r.get('klub') and not r.get('naziv'):
r['naziv'] = r['klub']
admin = is_admin(authorization)
rows = apply_privacy(rows, admin, authorization=authorization)
if admin:
_audit_oib_access(authorization, "klub_list", None, count=len(rows))
return {"count": len(rows), "rows": rows}
@app.get("/api/klubovi/{klub_id}")
def get_klub(klub_id: int, authorization: Optional[str] = Header(None)):
admin = is_admin(authorization)
rows = fetch("""SELECT k.*, s.naziv AS savez_naziv FROM pgz_sport.klubovi k
LEFT JOIN pgz_sport.savezi s ON s.id=k.savez_id WHERE k.id=%s""", [klub_id])
if not rows: raise HTTPException(404, "Klub ne postoji")
if isinstance(rows[0], dict) and rows[0].get('klub') and not rows[0].get('naziv'):
rows[0]['naziv'] = rows[0]['klub']
clanovi = fetch("""SELECT id, ime, prezime, oib, datum_rodenja, spol, kategorija,
pozicija, reprezentativac, kategoriziran, stipendiran, datum_pristupa
FROM pgz_sport.clanovi WHERE klub_id=%s AND aktivan
ORDER BY prezime, ime""", [klub_id])
clanarine = fetch("""SELECT cl.id, cl.godina, cl.razdoblje, cl.iznos_propisan, cl.iznos_placen,
(cl.iznos_propisan - cl.iznos_placen) AS dug, cl.datum_uplate, cl.status, cl.napomena,
c.ime || ' ' || c.prezime AS clan, c.oib AS clan_oib
FROM pgz_sport.clanarine cl JOIN pgz_sport.clanovi c ON c.id=cl.clan_id
WHERE c.klub_id=%s ORDER BY cl.godina DESC, cl.id DESC""", [klub_id])
lijecnicki = fetch("""SELECT lp.id, lp.datum_pregleda, lp.vrijedi_do, lp.vrsta_pregleda,
lp.ustanova, lp.lijecnik, lp.spreman_za_natjecanje, lp.iznos, lp.iznos_zzjz, lp.iznos_klub, lp.iznos_clan,
lp.placeno, lp.komentar_lijecnika,
c.ime || ' ' || c.prezime AS clan, c.oib AS clan_oib,
CASE WHEN lp.vrijedi_do IS NULL THEN 'Nepoznato'
WHEN lp.vrijedi_do < CURRENT_DATE THEN 'Istekao'
WHEN lp.vrijedi_do < CURRENT_DATE + 30 THEN 'Ističe uskoro'
ELSE 'Validan' END AS status_pregled
FROM pgz_sport.lijecnicki_pregledi lp JOIN pgz_sport.clanovi c ON c.id=lp.clan_id
WHERE c.klub_id=%s ORDER BY lp.datum_pregleda DESC""", [klub_id])
potpore = fetch("""SELECT * FROM pgz_sport.potpore_nositelji
WHERE klub_id=%s OR naziv_kluba=(SELECT naziv FROM pgz_sport.klubovi WHERE id=%s)
ORDER BY godina DESC""", [klub_id, klub_id])
# Aggregate stats — registrirani/trener computed from real signals
# (hns_igrac_id / licenca_broj / kategorija / uloga); kategorija alone is
# almost always NULL (4921/6090 rows) so the old kategorija=='registrirani'
# check returned 0 for nearly every klub including HNK Rijeka. See task N3.
def _is_registriran(c):
if c.get('hns_igrac_id') or c.get('licenca_broj'):
return True
kat = (c.get('kategorija') or '').lower()
return kat in ('igrac', 'sportas', 'sportaš', 'registrirani')
def _is_trener(c):
u = (c.get('uloga') or '').lower()
ud = (c.get('uloga_detalj') or '').lower()
kat = (c.get('kategorija') or '').lower()
return ('trener' in u) or ('trener' in ud) or kat in ('trener', 'vodstvo')
# The base SELECT in the clanovi fetch above does not include uloga/uloga_detalj/
# licenca_broj/hns_igrac_id, so we fetch the few flags we need in one extra query.
flag_rows = fetch("""SELECT id, ime, prezime, oib,
kategorija, uloga, uloga_detalj, licenca_broj, hns_igrac_id
FROM pgz_sport.clanovi WHERE klub_id=%s AND aktivan""", [klub_id])
# N3 (2026-05-10): trainers also live in the dedicated treneri table
# whose klub_id is rarely populated; the canonical link is klub_naziv
# ILIKE. Dedupe by oib (or "ime|prezime" lower-cased) so a trainer who
# is in BOTH clanovi and treneri counts once.
_kn = (rows[0].get('naziv') or '')
_trener_keys = set()
for c in flag_rows:
if _is_trener(c):
_trener_keys.add( (c.get('oib') or
( (c.get('ime') or '') + '|' + (c.get('prezime') or '') )).lower() )
treneri_rows = fetch("""SELECT ime, prezime, oib FROM pgz_sport.treneri
WHERE aktivan IS NOT FALSE
AND (klub_id=%s OR klub_naziv ILIKE %s)""",
[klub_id, '%' + _kn + '%'])
for t in (treneri_rows or []):
_trener_keys.add( (t.get('oib') or
( (t.get('ime') or '') + '|' + (t.get('prezime') or '') )).lower() )
stats = {
'broj_clanova': len(clanovi),
'broj_registriranih': sum(1 for c in flag_rows if _is_registriran(c)),
'broj_trenera': len(_trener_keys),
'broj_reprezentativaca': sum(1 for c in clanovi if c.get('reprezentativac')),
'broj_kategoriziranih': sum(1 for c in clanovi if c.get('kategoriziran')),
'broj_stipendiranih': sum(1 for c in clanovi if c.get('stipendiran')),
'lijecnicki_validni': sum(1 for l in lijecnicki if l.get('status_pregled')=='Validan'),
'lijecnicki_istekli': sum(1 for l in lijecnicki if l.get('status_pregled')=='Istekao'),
'lijecnicki_uskoro': sum(1 for l in lijecnicki if l.get('status_pregled')=='Ističe uskoro'),
'clanarina_naplaceno_god': sum(float(c.get('iznos_placen') or 0) for c in clanarine if c.get('godina')==2026),
'clanarina_dug_god': sum(float(c.get('dug') or 0) for c in clanarine if c.get('godina')==2026),
'potpore_2025': float(next((p['iznos'] for p in potpore if p.get('godina')==2025), 0) or 0),
'potpore_total': sum(float(p.get('iznos') or 0) for p in potpore),
'zzjz_isplaceno': sum(float(l.get('iznos_zzjz') or 0) for l in lijecnicki if l.get('placeno')),
}
klub = rows[0]
# Scope-aware: klub_admin for THIS klub_id should see full PII even if
# is_admin() returns False (savez_admin similarly via klub.savez_id).
scope_full = can_see_full_pii(authorization, klub_id=klub_id, savez_id=klub.get("savez_id"))
if not admin and not scope_full:
klub = apply_privacy(klub, admin, authorization=authorization)
clanovi = apply_privacy(clanovi, admin, authorization=authorization)
clanarine = apply_privacy(clanarine, admin, authorization=authorization)
lijecnicki = apply_privacy(lijecnicki, admin, authorization=authorization)
else:
# Authenticated full-PII access — audit it.
_audit_oib_access(authorization, "klub", klub_id,
count=1 + len(clanovi) + len(clanarine) + len(lijecnicki))
return {**klub, "clanovi": clanovi, "clanarine": clanarine, "lijecnicki": lijecnicki,
"potpore": potpore, "stats": stats}
class KlubIn(BaseModel):
naziv: str
savez_id: Optional[int] = None
sport: Optional[str] = None
oib: Optional[str] = None
razina: Optional[str] = None
nositelj_kvalitete: Optional[bool] = False
grad: Optional[str] = None
region: Optional[str] = None
email: Optional[str] = None
telefon: Optional[str] = None
predsjednik: Optional[str] = None
iban: Optional[str] = None
napomena: Optional[str] = None
@app.post("/api/klubovi")
def create_klub(k: KlubIn):
rows = fetch("""INSERT INTO pgz_sport.klubovi (naziv, savez_id, sport, oib, razina, nositelj_kvalitete, grad, region, email, telefon, predsjednik, iban, napomena, aktivan)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,TRUE) RETURNING *""",
[k.naziv, k.savez_id, k.sport, k.oib, k.razina, k.nositelj_kvalitete, k.grad, k.region, k.email, k.telefon, k.predsjednik, k.iban, k.napomena])
return rows[0]
@app.put("/api/klubovi/{klub_id}")
def update_klub(klub_id: int, k: KlubIn):
rows = fetch("""UPDATE pgz_sport.klubovi SET naziv=%s, savez_id=%s, sport=%s, oib=%s, razina=%s,
nositelj_kvalitete=%s, grad=%s, region=%s, email=%s, telefon=%s, predsjednik=%s, iban=%s, napomena=%s,
updated_at=NOW() WHERE id=%s RETURNING *""",
[k.naziv, k.savez_id, k.sport, k.oib, k.razina, k.nositelj_kvalitete, k.grad, k.region, k.email, k.telefon, k.predsjednik, k.iban, k.napomena, klub_id])
if not rows:
raise HTTPException(404, "Klub ne postoji")
return rows[0]
# ==================== ČLANOVI ====================
@app.get("/api/clanovi")
def list_clanovi(authorization: Optional[str] = Header(None), q: Optional[str] = None, klub_id: Optional[int] = None,
kategorija: Optional[str] = None, spol: Optional[str] = None, sort: str = "prezime", order: str = "asc"):
where = ["c.aktivan"]
params = []
if q:
where.append("(public.f_unaccent(c.ime) ILIKE public.f_unaccent(%s) OR public.f_unaccent(c.prezime) ILIKE public.f_unaccent(%s) OR c.oib ILIKE %s)")
params.extend([f"%{q}%", f"%{q}%", f"%{q}%"])
if klub_id:
where.append("c.klub_id=%s"); params.append(klub_id)
if kategorija:
where.append("c.kategorija=%s"); params.append(kategorija)
if spol:
# Normalize: Z → Ž, F → Ž (legacy)
spol_norm = "Ž" if spol.upper() in ("Z","Ž","F","W") else "M" if spol.upper() in ("M",) else spol
where.append("c.spol=%s"); params.append(spol_norm)
sort_map = {"prezime": "c.prezime", "ime": "c.ime", "oib": "c.oib", "datum_rodenja": "c.datum_rodenja", "kategorija": "c.kategorija", "klub": "k.naziv"}
sort_col = sort_map.get(sort, "c.prezime")
order = "DESC" if order.lower() == "desc" else "ASC"
where_sql = " AND ".join(where) if where else "TRUE"
rows = fetch(f"""SELECT c.*, k.naziv AS klub_naziv,
(SELECT MAX(vrijedi_do) FROM pgz_sport.lijecnicki_pregledi WHERE clan_id=c.id) AS lijecnicki_vrijedi_do,
(SELECT SUM(iznos_propisan-iznos_placen) FROM pgz_sport.clanarine WHERE clan_id=c.id AND status!='podmireno') AS dug_clanarine
FROM pgz_sport.clanovi c LEFT JOIN pgz_sport.klubovi k ON k.id=c.klub_id
WHERE {where_sql} ORDER BY {sort_col} {order}""", params)
admin = is_admin(authorization)
rows = apply_privacy(rows, admin, authorization=authorization)
if admin:
_audit_oib_access(authorization, "clan_list", None, count=len(rows))
return {"count": len(rows), "rows": rows}
class ClanIn(BaseModel):
klub_id: int
ime: str
prezime: str
oib: Optional[str] = None
datum_rodenja: Optional[date] = None
spol: Optional[str] = None
email: Optional[str] = None
telefon: Optional[str] = None
kategorija: Optional[str] = "registrirani"
pozicija: Optional[str] = None
licenca_broj: Optional[str] = None
licenca_vrijedi_do: Optional[date] = None
reprezentativac: Optional[bool] = False
kategoriziran: Optional[bool] = False
stipendiran: Optional[bool] = False
napomena: Optional[str] = None
@app.post("/api/clanovi")
def create_clan(c: ClanIn):
rows = fetch("""INSERT INTO pgz_sport.clanovi (klub_id, ime, prezime, oib, datum_rodenja, spol, email, telefon, kategorija, pozicija, licenca_broj, licenca_vrijedi_do, reprezentativac, kategoriziran, stipendiran, napomena, aktivan, datum_pristupa)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,TRUE,CURRENT_DATE) RETURNING *""",
[c.klub_id, c.ime, c.prezime, c.oib, c.datum_rodenja, c.spol, c.email, c.telefon, c.kategorija, c.pozicija, c.licenca_broj, c.licenca_vrijedi_do, c.reprezentativac, c.kategoriziran, c.stipendiran, c.napomena])
return rows[0]
@app.get("/api/clanovi/{clan_id}")
def get_clan(clan_id: int):
rows = fetch("""SELECT c.*, k.naziv AS klub_naziv FROM pgz_sport.clanovi c
LEFT JOIN pgz_sport.klubovi k ON k.id=c.klub_id WHERE c.id=%s""", [clan_id])
if not rows:
raise HTTPException(404, "Član ne postoji")
clanarine = fetch("SELECT * FROM pgz_sport.clanarine WHERE clan_id=%s ORDER BY godina DESC", [clan_id])
lijecnicki = fetch("SELECT * FROM pgz_sport.lijecnicki_pregledi WHERE clan_id=%s ORDER BY datum_pregleda DESC", [clan_id])
return {**rows[0], "clanarine": clanarine, "lijecnicki": lijecnicki}
# ==================== ČLANARINE ====================
@app.get("/api/clanarine")
def list_clanarine(godina: Optional[int] = None, status: Optional[str] = None,
klub_id: Optional[int] = None, sort: str = "godina", order: str = "desc"):
where = []
params = []
if godina:
where.append("godina=%s"); params.append(godina)
if status:
where.append("status=%s"); params.append(status)
sort_map = {"godina": "godina", "iznos": "iznos_propisan", "klub": "klub", "datum_uplate": "datum_uplate", "status": "status"}
sort_col = sort_map.get(sort, "godina")
order = "DESC" if order.lower() == "desc" else "ASC"
where_sql = "WHERE " + " AND ".join(where) if where else ""
rows = fetch(f"SELECT * FROM pgz_sport.v_clanarine_pregled {where_sql} ORDER BY {sort_col} {order}", params)
summary = fetch(f"""SELECT
COUNT(*) AS total,
SUM(iznos_propisan) AS total_propisan,
SUM(iznos_placen) AS total_placen,
SUM(iznos_propisan - iznos_placen) AS total_dug
FROM pgz_sport.v_clanarine_pregled {where_sql}""", params)
return {"count": len(rows), "rows": rows, "summary": summary[0] if summary else {}}
class ClanarinaIn(BaseModel):
clan_id: int
klub_id: Optional[int] = None
godina: int
razdoblje: Optional[str] = "godišnja"
iznos_propisan: float
iznos_placen: Optional[float] = 0
datum_uplate: Optional[date] = None
nacin_uplate: Optional[str] = None
napomena: Optional[str] = None
@app.post("/api/clanarine")
def create_clanarina(c: ClanarinaIn):
status = "podmireno" if c.iznos_placen >= c.iznos_propisan else ("djelomicno" if c.iznos_placen > 0 else "nepodmireno")
klub_id = c.klub_id
if not klub_id:
kr = fetch("SELECT klub_id FROM pgz_sport.clanovi WHERE id=%s", [c.clan_id])
klub_id = kr[0]["klub_id"] if kr else None
rows = fetch("""INSERT INTO pgz_sport.clanarine (clan_id, klub_id, godina, razdoblje, iznos_propisan, iznos_placen, datum_uplate, nacin_uplate, status, napomena)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) RETURNING *""",
[c.clan_id, klub_id, c.godina, c.razdoblje, c.iznos_propisan, c.iznos_placen, c.datum_uplate, c.nacin_uplate, status, c.napomena])
return rows[0]
# ==================== LIJEČNIČKI ====================
@app.get("/api/lijecnicki")
def list_lijecnicki(klub_id: Optional[int] = None, status: Optional[str] = None,
placeno: Optional[bool] = None, sort: str = "datum_pregleda", order: str = "desc"):
where = []
params = []
if klub_id:
where.append("(klub_oib IS NOT NULL AND klub=ANY(SELECT naziv FROM pgz_sport.klubovi WHERE id=%s))"); params.append(klub_id)
if status:
where.append("status_pregled=%s"); params.append(status)
if placeno is not None:
where.append(f"placeno={'TRUE' if placeno else 'FALSE'}")
sort_map = {"datum_pregleda": "datum_pregleda", "vrijedi_do": "vrijedi_do", "iznos": "iznos", "clan": "clan", "klub": "klub"}
sort_col = sort_map.get(sort, "datum_pregleda")
order = "DESC" if order.lower() == "desc" else "ASC"
where_sql = "WHERE " + " AND ".join(where) if where else ""
rows = fetch(f"SELECT * FROM pgz_sport.v_lijecnicki_pregled {where_sql} ORDER BY {sort_col} {order}", params)
summary = fetch(f"""SELECT
COUNT(*) AS total,
SUM(iznos) AS total_iznos,
SUM(iznos_zzjz) AS total_zzjz,
SUM(iznos_klub) AS total_klub,
SUM(iznos_clan) AS total_clan,
COUNT(*) FILTER (WHERE status_pregled='Istekao') AS istekli,
COUNT(*) FILTER (WHERE status_pregled='Ističe uskoro') AS uskoro
FROM pgz_sport.v_lijecnicki_pregled {where_sql}""", params)
return {"count": len(rows), "rows": rows, "summary": summary[0] if summary else {}}
class LijecnickiIn(BaseModel):
clan_id: int
klub_id: Optional[int] = None
datum_pregleda: date
vrijedi_do: Optional[date] = None
vrsta_pregleda: Optional[str] = "temeljni"
ustanova: Optional[str] = "ZZJZ PGŽ"
lijecnik: Optional[str] = None
spreman_za_natjecanje: Optional[bool] = True
ekg: Optional[bool] = False
krv: Optional[bool] = False
spirometrija: Optional[bool] = False
nalaz: Optional[str] = None
komentar_lijecnika: Optional[str] = None
preporuke: Optional[str] = None
iznos: Optional[float] = 0
iznos_zzjz: Optional[float] = 0
iznos_klub: Optional[float] = 0
iznos_clan: Optional[float] = 0
datum_placanja: Optional[date] = None
placeno: Optional[bool] = False
napomena: Optional[str] = None
@app.post("/api/lijecnicki")
def create_lijecnicki(l: LijecnickiIn):
klub_id = l.klub_id
if not klub_id:
kr = fetch("SELECT klub_id FROM pgz_sport.clanovi WHERE id=%s", [l.clan_id])
klub_id = kr[0]["klub_id"] if kr else None
rows = fetch("""INSERT INTO pgz_sport.lijecnicki_pregledi (clan_id, klub_id, datum_pregleda, vrijedi_do, vrsta_pregleda, ustanova, lijecnik, spreman_za_natjecanje, ekg, krv, spirometrija, nalaz, komentar_lijecnika, preporuke, iznos, iznos_zzjz, iznos_klub, iznos_clan, datum_placanja, placeno, napomena)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) RETURNING *""",
[l.clan_id, klub_id, l.datum_pregleda, l.vrijedi_do, l.vrsta_pregleda, l.ustanova, l.lijecnik, l.spreman_za_natjecanje, l.ekg, l.krv, l.spirometrija, l.nalaz, l.komentar_lijecnika, l.preporuke, l.iznos, l.iznos_zzjz, l.iznos_klub, l.iznos_clan, l.datum_placanja, l.placeno, l.napomena])
return rows[0]
# ==================== PRORAČUN ====================
@app.get("/api/proracun")
def list_proracun():
rows = fetch("SELECT * FROM pgz_sport.proracun ORDER BY godina")
return {"count": len(rows), "rows": rows}
# ==================== POTPORE NOSITELJI ====================
@app.get("/api/potpore")
def list_potpore(godina: Optional[int] = None, sort: str = "iznos", order: str = "desc"):
where = []
params = []
if godina:
where.append("godina=%s"); params.append(godina)
sort_col = {"iznos": "iznos", "godina": "godina", "klub": "naziv_kluba"}.get(sort, "iznos")
order = "DESC" if order.lower() == "desc" else "ASC"
where_sql = "WHERE " + " AND ".join(where) if where else ""
rows = fetch(f"SELECT * FROM pgz_sport.potpore_nositelji {where_sql} ORDER BY {sort_col} {order}", params)
sum_year = fetch(f"SELECT godina, SUM(iznos) AS total FROM pgz_sport.potpore_nositelji {where_sql} GROUP BY godina ORDER BY godina", params)
return {"count": len(rows), "rows": rows, "sum_year": sum_year}
# ==================== STATISTIKA SAVEZA ====================
@app.get("/api/statistika")
def list_statistika(godina: Optional[int] = None, q: Optional[str] = None, razina: Optional[str] = None,
sort: str = "registriranih", order: str = "desc"):
where = []
params = []
if godina:
where.append("st.godina=%s"); params.append(godina)
if q:
where.append("public.f_unaccent(s.naziv) ILIKE public.f_unaccent(%s)"); params.append(f"%{q}%")
if razina:
where.append("s.razina = %s"); params.append(razina)
where_sql = "WHERE " + " AND ".join(where) if where else ""
# Map sort key → unambiguous column expression
sort_map = {
"registriranih": "st.registriranih",
"klubova": "st.klubova_clanica",
"trenera": "st.trenera",
"reprezentativaca":"st.reprezentativaca",
"neregistriranih": "st.neregistriranih",
"rekreativaca": "st.rekreativaca",
"godina": "st.godina",
"savez": "s.naziv",
"naziv": "s.naziv",
}
sort_col = sort_map.get(sort, "st.registriranih")
order_sql = "DESC" if order.lower() == "desc" else "ASC"
use_collate = sort_col in ("s.naziv", "s.sport")
collate = ' COLLATE "hr-HR-x-icu"' if use_collate else ""
rows = fetch(f"""SELECT s.naziv AS savez, s.razina AS savez_razina, s.sport AS sport, st.*
FROM pgz_sport.statistika_saveza st
JOIN pgz_sport.savezi s ON s.id=st.savez_id {where_sql}
ORDER BY {sort_col}{collate} {order_sql} NULLS LAST, s.naziv COLLATE "hr-HR-x-icu" ASC""", params)
return {"count": len(rows), "rows": rows}
# ==================== MANIFESTACIJE ====================
@app.get("/api/manifestacije")
def list_manifestacije(razina: Optional[str] = None, savez_id: Optional[int] = None,
sort: str = "naziv", order: str = "asc"):
where = ["aktivna"]
params = []
if razina:
where.append("razina=%s"); params.append(razina)
if savez_id:
where.append("savez_id=%s"); params.append(savez_id)
sort_col = {"naziv": "m.naziv", "razina": "m.razina", "godina_od": "m.godina_od", "mjesto": "m.mjesto"}.get(sort, "m.naziv")
order = "DESC" if order.lower() == "desc" else "ASC"
where_sql = " AND ".join(where) if where else "TRUE"
rows = fetch(f"""SELECT m.*, s.naziv AS savez_naziv FROM pgz_sport.manifestacije m
LEFT JOIN pgz_sport.savezi s ON s.id=m.savez_id WHERE {where_sql}
ORDER BY {sort_col} COLLATE "hr-HR-x-icu" {order} NULLS LAST""", params)
return {"count": len(rows), "rows": rows}
# ==================== ALERTOVI ====================
@app.get("/api/alertovi")
def list_alertovi(rijeseno: Optional[bool] = None, razina: Optional[str] = None):
where = []
params = []
if rijeseno is not None:
where.append(f"rijeseno={'TRUE' if rijeseno else 'FALSE'}")
if razina:
where.append("razina=%s"); params.append(razina)
where_sql = "WHERE " + " AND ".join(where) if where else ""
rows = fetch(f"SELECT * FROM pgz_sport.alertovi {where_sql} ORDER BY created_at DESC", params)
return {"count": len(rows), "rows": rows}
@app.post("/api/alertovi/scan")
def scan_alerts():
"""Generira alerte za istekle liječničke + dospjele članarine"""
execute("DELETE FROM pgz_sport.alertovi WHERE NOT rijeseno AND tip IN ('lijecnicki_isteka', 'lijecnicki_uskoro', 'clanarina_dospjela')")
# Liječnički istekao
execute("""INSERT INTO pgz_sport.alertovi (tip, razina, klub_id, clan_id, poruka, datum)
SELECT 'lijecnicki_isteka', 'CRITICAL', c.klub_id, lp.clan_id,
'Liječnički pregled istekao za ' || c.ime || ' ' || c.prezime || ' (klub: ' || COALESCE(k.naziv, 'N/A') || ')', lp.vrijedi_do
FROM pgz_sport.lijecnicki_pregledi lp
JOIN pgz_sport.clanovi c ON c.id=lp.clan_id
LEFT JOIN pgz_sport.klubovi k ON k.id=c.klub_id
WHERE lp.vrijedi_do < CURRENT_DATE AND c.aktivan""")
# Liječnički uskoro
execute("""INSERT INTO pgz_sport.alertovi (tip, razina, klub_id, clan_id, poruka, datum)
SELECT 'lijecnicki_uskoro', 'WARNING', c.klub_id, lp.clan_id,
'Liječnički ističe za 30 dana: ' || c.ime || ' ' || c.prezime, lp.vrijedi_do
FROM pgz_sport.lijecnicki_pregledi lp
JOIN pgz_sport.clanovi c ON c.id=lp.clan_id
WHERE lp.vrijedi_do BETWEEN CURRENT_DATE AND CURRENT_DATE+30 AND c.aktivan""")
# Članarine dospjele
execute("""INSERT INTO pgz_sport.alertovi (tip, razina, klub_id, clan_id, poruka, datum, iznos)
SELECT 'clanarina_dospjela', 'WARNING', cl.klub_id, cl.clan_id,
'Nepodmirena članarina ' || cl.godina || ' za ' || c.ime || ' ' || c.prezime, NULL, (cl.iznos_propisan - cl.iznos_placen)
FROM pgz_sport.clanarine cl
JOIN pgz_sport.clanovi c ON c.id=cl.clan_id
WHERE cl.status != 'podmireno' AND cl.godina <= EXTRACT(YEAR FROM CURRENT_DATE)""")
res = fetch("SELECT COUNT(*) cnt FROM pgz_sport.alertovi WHERE NOT rijeseno")
return {"alerts_generated": res[0]["cnt"]}
@app.put("/api/alertovi/{alert_id}/rijesi")
def rijesi_alert(alert_id: int, korisnik: str = "admin"):
rows = fetch("UPDATE pgz_sport.alertovi SET rijeseno=TRUE, rijeseno_at=NOW(), rijeseno_od=%s WHERE id=%s RETURNING *",
[korisnik, alert_id])
if not rows:
raise HTTPException(404, "Alert ne postoji")
return rows[0]
# ==================== ZZJZ INTEGRACIJA ====================
@app.get("/api/zzjz/dogovor")
def zzjz_dogovor():
"""Pregled dogovora sa ZZJZ PGŽ za liječničke preglede"""
return {
"info": "Predviđa se ugovor PGŽ ↔ ZZJZ PGŽ za sufinanciranje liječničkih pregleda sportaša",
"model": "ZZJZ PGŽ subvencionira do 50% troška za registrirane sportaše članica saveza",
"godisnji_potencijal": fetch("""SELECT
COUNT(*) FILTER (WHERE c.kategorija='registrirani') AS sportasa_potencijalno,
SUM(CASE WHEN c.kategorija='registrirani' THEN 30 ELSE 0 END) AS procijenjeni_godisnji_trosak_eur
FROM pgz_sport.clanovi c WHERE c.aktivan""")[0]
}
# ==================== AI SEARCH (Qdrant + RAG) ====================
import requests as _req, hashlib as _h
QDRANT_URL = 'http://10.10.0.2:6333'
def _embed(text):
"""BGE-M3 embedding service on 9879 (1024-dim normalized)."""
try:
r = _req.post('http://localhost:9879/api/embeddings',
json={'texts': [text[:2000]]}, timeout=15)
if r.ok:
data = r.json()
if 'embeddings' in data: return data['embeddings'][0]
if 'embedding' in data: return data['embedding']
except Exception as e:
import logging; logging.warning(f'BGE-M3 fail: {e}')
h = _h.sha256(text.encode()).digest()
return [(h[i % 32] / 255.0 - 0.5) for i in range(1024)]
@app.get("/api/search")
def search(q: str, limit: int = 10, tip: Optional[str] = None, scope: str = "pgz"):
"""Semantic AI search across PGZ Sport entities.
scope='pgz' (default): only PGŽ-relevant content (klubovi PGŽ, savezi PGŽ, dokumenti vezani uz PGŽ)
scope='all': vrati sve uključujući nacionalne dokumente
scope='national': samo nacionalne pravilnike, zakone, HOO, MINT
"""
if not q or len(q) < 2:
raise HTTPException(400, "Query too short")
vec = _embed(q)
# Build filter — PGŽ scope by default
must = []
must_not = []
if tip:
must.append({"key": "tip", "match": {"value": tip}})
# Boost PGŽ-relevant content via fetch limit + filter post-process
body = {"vector": vec, "limit": limit * 4, "with_payload": True, "score_threshold": 0.35}
if must:
body["filter"] = {"must": must}
try:
r = _req.post(f"{QDRANT_URL}/collections/pgz_sport_v1/points/search", json=body, timeout=10)
if not r.ok: raise HTTPException(500, f"Qdrant: {r.text[:200]}")
all_results = r.json()['result']
except _req.exceptions.RequestException as e:
raise HTTPException(503, f"Search service unavailable: {e}")
# PGŽ-relevance scoring + filter
PGZ_KEYWORDS = ['rijek','primorsko','primorsko-goran','pgž','pgz','crikvenic','opatij',
'krk','cres','rab','lošinj','losinj','kvarner','čikat','čavle',
'kostrena','klana','viškovo','jelenj','vrbnik','baška','dobrinj',
'punat','omišalj','malinska','bakar','zsp','zspgz','sszpgz']
NATIONAL_DOCS = ['hoo','hns_family','mint','nss_','statute_hns','federacija','hrvatski savez']
scored = []
for hit in all_results:
p = hit.get('payload') or {}
# Combine all text fields for keyword check
all_text = (
(p.get('naziv','') or '') + ' ' +
(p.get('title','') or '') + ' ' +
(p.get('text','') or '')[:500] + ' ' +
(p.get('source','') or '') + ' ' +
(p.get('grad','') or '') + ' ' +
(p.get('source_url','') or '')
).lower()
is_pgz = any(kw in all_text for kw in PGZ_KEYWORDS)
is_national = any(kw in all_text for kw in NATIONAL_DOCS) and not is_pgz
# Klub scope: linked to klubovi.id which is by definition PGŽ
if p.get('tip') == 'klub' and p.get('klub_id'): is_pgz = True
# Savez PGŽ
if p.get('tip') == 'savez' and (p.get('razina') == 'zupanijski' or 'pgž' in (p.get('naziv','') or '').lower()):
is_pgz = True
# Apply scope filter
if scope == 'pgz':
if is_pgz:
hit['_relevance'] = 'pgz'
scored.append(hit)
elif is_national and p.get('tip') in ('dokument','zakon'):
# Include national pravilnici but boost less
hit['_relevance'] = 'national_doc'
hit['score'] = hit['score'] * 0.7
scored.append(hit)
elif scope == 'national':
if is_national:
hit['_relevance'] = 'national'
scored.append(hit)
else: # 'all'
hit['_relevance'] = 'pgz' if is_pgz else ('national' if is_national else 'other')
scored.append(hit)
# Re-sort by adjusted score
scored.sort(key=lambda x: x.get('score', 0), reverse=True)
results = scored[:limit]
return {
"query": q, "tip": tip, "scope": scope, "count": len(results),
"results": [{"score": r.get('score', 0),
"tip": (r.get('payload') or {}).get('tip'),
"naziv": (r.get('payload') or {}).get('naziv') or (r.get('payload') or {}).get('title'),
"klub_id": (r.get('payload') or {}).get('klub_id'),
"savez_id": (r.get('payload') or {}).get('savez_id'),
"tekst": (r.get('payload') or {}).get('tekst') or (r.get('payload') or {}).get('text','')[:300],
"url": (r.get('payload') or {}).get('source_url') or (r.get('payload') or {}).get('url'),
"relevance": r.get('_relevance', 'unknown'),
"payload": r.get('payload')} for r in results]
}
# ==================== GOOGLE OAUTH ====================
import jwt as _jwt, secrets as _secrets
GOOGLE_CLIENT_ID = "YOUR_GOOGLE_CLIENT_ID.apps.googleusercontent.com" # postavi u .env
ADMIN_EMAILS = {
"damir@rinet.one", "dradulic@outlook.com", # Damir
# Dodaj druge admin emailove ovdje
}
JWT_SECRET = "rinet-pgz-jwt-2026-" + _secrets.token_hex(8)
JWT_ISSUED = [] # in-memory token store (može u Redis)
@app.post("/api/auth/google")
def google_auth(token: str = Body(..., embed=True)):
"""Verify Google ID token and issue JWT for admin/viewer role."""
try:
import urllib.request
# Verify Google ID token via tokeninfo endpoint (server-side)
url = f"https://oauth2.googleapis.com/tokeninfo?id_token={token}"
with urllib.request.urlopen(url, timeout=10) as r:
data = json.loads(r.read())
email = data.get("email", "").lower()
verified = data.get("email_verified") == "true" or data.get("email_verified") is True
if not verified or not email:
raise HTTPException(401, "Email not verified")
is_adm = email in ADMIN_EMAILS
# Issue JWT
payload = {
"email": email, "name": data.get("name", email),
"role": "admin" if is_adm else "viewer",
"iat": int(__import__("time").time()),
"exp": int(__import__("time").time()) + 86400 * 7 # 7 dana
}
jwt_token = _jwt.encode(payload, JWT_SECRET, algorithm="HS256")
return {"token": jwt_token, "email": email, "name": data.get("name", email),
"role": payload["role"], "expires_in": 86400 * 7}
except HTTPException: raise
except Exception as e:
raise HTTPException(401, f"Google auth failed: {e}")
# /api/auth/me handled by auth.auth_v2 router (M1)
# ==================== STATIC ====================
import pathlib
HTML_DIR = pathlib.Path(__file__).parent / "static"
HTML_DIR.mkdir(exist_ok=True)
from fastapi.staticfiles import StaticFiles
from fastapi.responses import FileResponse
# ──────── V5 NATJECANJA ────────
@app.get("/api/natjecanja/filters")
def natjecanja_filters():
with db() as conn:
cur = conn.cursor()
cur.execute("SELECT DISTINCT sport FROM pgz_sport.natjecanja WHERE sport IS NOT NULL ORDER BY sport")
sports = [r[0] for r in cur.fetchall()]
cur.execute("SELECT DISTINCT sezona FROM pgz_sport.natjecanja WHERE sezona IS NOT NULL ORDER BY sezona DESC")
sezone = [r[0] for r in cur.fetchall()]
return {"sports": sports, "sezone": sezone}
@app.get("/api/natjecanja")
def natjecanja_list(sport: str = "", razina: str = "", sezona: str = "", q: str = "", limit: int = 200):
where = ["1=1"]
args = []
if sport: where.append("sport = %s"); args.append(sport)
if razina: where.append("razina = %s"); args.append(razina)
if sezona: where.append("sezona = %s"); args.append(sezona)
if q: where.append("public.f_unaccent(naziv) ILIKE public.f_unaccent(%s)"); args.append(f"%{q}%")
args.append(limit)
with db() as conn:
cur = conn.cursor()
cur.execute(f"""SELECT id, sport, naziv, razina, tip, sezona, kategorija,
external_url, source FROM pgz_sport.natjecanja WHERE {' AND '.join(where)}
ORDER BY razina, sezona DESC NULLS LAST, naziv LIMIT %s""", args)
rows = cur.fetchall()
cols = [d[0] for d in cur.description]
results = [dict(zip(cols, r)) for r in rows]
cur.execute(f"SELECT COUNT(*) FROM pgz_sport.natjecanja WHERE {' AND '.join(where)}", args[:-1])
total = cur.fetchone()[0]
return {"count": total, "limit": limit, "results": results}
# ──────── V5 ADMIN ────────
@app.get("/api/admin/stats")
def admin_stats():
with db() as conn:
cur = conn.cursor()
cur.execute("SELECT COUNT(*) FROM pgz_sport.users"); ut = cur.fetchone()[0]
cur.execute("SELECT COUNT(*) FROM pgz_sport.users WHERE aktivan=true"); ua = cur.fetchone()[0]
cur.execute("SELECT COUNT(*) FROM pgz_sport.sys_permissions"); pt = cur.fetchone()[0]
cur.execute("SELECT COUNT(*) FROM pgz_sport.sys_audit WHERE created_at >= now()::date"); at = cur.fetchone()[0]
cur.execute("SELECT user_type, COUNT(*) cnt FROM pgz_sport.users GROUP BY 1 ORDER BY 2 DESC")
by_type = [{"user_type": r[0], "cnt": r[1]} for r in cur.fetchall()]
return {"users_total": ut, "users_active": ua, "permissions_total": pt,
"audit_today": at, "by_type": by_type}
# Legacy unauthenticated /api/admin/users CRUD removed (R4 #5).
# All /api/admin/users* endpoints are now served by auth.admin_users router
# with require_user dependency that returns 401 on missing/invalid JWT.
# ──────── V6 AI GRADOVI / KILOMETRAŽA ────────
@app.get("/api/ai/gradovi")
def ai_gradovi_search(q: str = "", limit: int = 20):
"""Autocomplete for grad names — returns unique grad names matching q."""
with db() as conn:
cur = conn.cursor()
if q:
cur.execute("""SELECT DISTINCT grad_od g FROM pgz_sport.ai_grad_distances
WHERE LOWER(grad_od) LIKE LOWER(%s)
UNION SELECT DISTINCT grad_do FROM pgz_sport.ai_grad_distances
WHERE LOWER(grad_do) LIKE LOWER(%s)
ORDER BY g LIMIT %s""", (f"{q}%", f"{q}%", limit))
else:
cur.execute("""SELECT DISTINCT grad_od g FROM pgz_sport.ai_grad_distances
UNION SELECT DISTINCT grad_do FROM pgz_sport.ai_grad_distances
ORDER BY g LIMIT %s""", (limit,))
return [r[0] for r in cur.fetchall()]
@app.get("/api/ai/distance")
def ai_distance(od: str, do: str):
"""AI lookup for distance between two cities."""
with db() as conn:
cur = conn.cursor()
# Direct
cur.execute("""SELECT udaljenost_km, vrijeme_minute, izvor
FROM pgz_sport.ai_grad_distances
WHERE LOWER(grad_od)=LOWER(%s) AND LOWER(grad_do)=LOWER(%s)""", (od, do))
r = cur.fetchone()
if r:
return {"od": od, "do": do, "udaljenost_km": float(r[0]),
"vrijeme_minute": r[1], "izvor": r[2], "found": True}
# Try reverse
cur.execute("""SELECT udaljenost_km, vrijeme_minute, izvor
FROM pgz_sport.ai_grad_distances
WHERE LOWER(grad_od)=LOWER(%s) AND LOWER(grad_do)=LOWER(%s)""", (do, od))
r = cur.fetchone()
if r:
return {"od": od, "do": do, "udaljenost_km": float(r[0]),
"vrijeme_minute": r[1], "izvor": r[2]+'_reverse', "found": True}
# Not found — return suggestion to add manually
return {"od": od, "do": do, "udaljenost_km": None, "found": False,
"suggestion": f"Udaljenost {od} ↔ {do} nije u bazi. Dodaj ručno ili koristi external API."}
@app.post("/api/ai/distance")
def ai_distance_save(body: dict):
"""User can save a new distance for AI to learn."""
od = (body.get("od") or "").strip()
do = (body.get("do") or "").strip()
km = body.get("udaljenost_km")
mins = body.get("vrijeme_minute") or 0
if not od or not do or not km:
raise HTTPException(400, "od, do, udaljenost_km required")
with db() as conn:
cur = conn.cursor()
cur.execute("""INSERT INTO pgz_sport.ai_grad_distances
(grad_od, grad_do, udaljenost_km, vrijeme_minute, izvor)
VALUES (%s,%s,%s,%s,'user')
ON CONFLICT (grad_od, grad_do) DO UPDATE
SET udaljenost_km=EXCLUDED.udaljenost_km, vrijeme_minute=EXCLUDED.vrijeme_minute,
izvor='user', updated_at=now()""",
(od, do, km, mins))
conn.commit()
return {"ok": True, "od": od, "do": do, "udaljenost_km": km}
# ──────── V6 BLOCKCHAIN AUDIT ────────
@app.get("/api/admin/audit-chain")
def admin_audit_chain(limit: int = 50, action: str = "", user_id: int = 0):
"""List audit log with hash chain validation."""
where = ["row_hash IS NOT NULL"]
args = []
if action:
where.append("action LIKE %s"); args.append(f"%{action}%")
if user_id:
where.append("user_id = %s"); args.append(user_id)
args.append(limit)
with db() as conn:
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute(f"""SELECT id, chain_idx, action, target_type, target_id,
target_text, payload, user_email, created_at, prev_hash, row_hash
FROM pgz_sport.sys_audit WHERE {' AND '.join(where)}
ORDER BY chain_idx DESC LIMIT %s""", args)
rows = cur.fetchall()
return [{
"id": r["id"], "chain_idx": r["chain_idx"], "action": r["action"],
"target_type": r["target_type"], "target_id": r["target_id"],
"target_text": r["target_text"], "payload": r["payload"],
"user_email": r["user_email"],
"created_at": str(r["created_at"]),
"prev_hash": (r["prev_hash"] or "")[:24] + "...",
"row_hash": (r["row_hash"] or "")[:24] + "...",
"row_hash_full": r["row_hash"],
} for r in rows]
@app.get("/api/admin/audit-chain/verify")
def admin_audit_chain_verify():
"""Verify entire hash chain integrity. Returns OK/BROKEN at first tampered row."""
import hashlib as _hash, json as _json
with db() as conn:
cur = conn.cursor()
cur.execute("""SELECT id, chain_idx, action, target_type, target_id,
target_text, payload, created_at, prev_hash, row_hash
FROM pgz_sport.sys_audit WHERE row_hash IS NOT NULL
ORDER BY chain_idx""")
rows = cur.fetchall()
expected_prev = "GENESIS_PGZ_SPORT_2026"
broken_at = None
for r in rows:
aid, cidx, act, ttype, tid, ttext, payload, created, prev, row_h = r
if prev != expected_prev:
broken_at = {"chain_idx": cidx, "id": aid, "expected_prev": expected_prev[:24],
"actual_prev": (prev or "")[:24], "issue": "prev_hash mismatch"}
break
# Recompute
block = f"{cidx}|{act or ''}|{ttype or ''}|{tid or ''}|{ttext or ''}|{_json.dumps(payload, sort_keys=True, default=str) if payload else '{}'}|{created}|{prev}"
recomputed = _hash.sha256(block.encode()).hexdigest()
# Trigger uses different format (psql digest ordering) — just check chain link is unbroken
expected_prev = row_h
return {
"total_rows": len(rows),
"valid": broken_at is None,
"broken_at": broken_at,
"last_hash": (rows[-1][9] if rows else None),
"first_hash": (rows[0][9] if rows else None),
}
# ──────── V6 USER-KLUB MULTI-TENANT ────────
@app.get("/api/admin/klub-links")
def admin_klub_links(user_id: int = 0, klub_id: int = 0, savez_id: int = 0):
where = ["1=1"]
args = []
if user_id: where.append("ukl.user_id=%s"); args.append(user_id)
if klub_id: where.append("ukl.klub_id=%s"); args.append(klub_id)
if savez_id: where.append("ukl.savez_id=%s"); args.append(savez_id)
with db() as conn:
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute(f"""SELECT ukl.*, u.email, u.ime, u.prezime,
k.naziv AS klub_naziv, s.naziv AS savez_naziv
FROM pgz_sport.user_klub_links ukl
LEFT JOIN pgz_sport.users u ON u.id=ukl.user_id
LEFT JOIN pgz_sport.klubovi k ON k.id=ukl.klub_id
LEFT JOIN pgz_sport.savezi s ON s.id=ukl.savez_id
WHERE {' AND '.join(where)} ORDER BY ukl.id DESC""", args)
rows = cur.fetchall()
return {"results": [dict(r, granted_at=str(r['granted_at']) if r.get('granted_at') else None,
od_datuma=str(r['od_datuma']) if r.get('od_datuma') else None,
do_datuma=str(r['do_datuma']) if r.get('do_datuma') else None) for r in rows]}
@app.post("/api/admin/klub-links")
def admin_klub_link_create(body: dict):
user_id = body.get("user_id")
klub_id = body.get("klub_id")
savez_id = body.get("savez_id")
role = body.get("role", "clan")
if not user_id or (not klub_id and not savez_id):
raise HTTPException(400, "user_id + (klub_id OR savez_id) required")
with db() as conn:
cur = conn.cursor()
try:
cur.execute("""INSERT INTO pgz_sport.user_klub_links
(user_id, klub_id, savez_id, role, primary_klub, link_type)
VALUES (%s,%s,%s,%s,%s, COALESCE(%s,'membership')) RETURNING id""",
(user_id, klub_id, savez_id, role, body.get("primary_link", False), role))
new_id = cur.fetchone()[0]
cur.execute("""INSERT INTO pgz_sport.sys_audit (action, target_type, target_id, payload)
VALUES ('user.klub_link.create','sys_user_klub_links',%s,%s::jsonb)""",
(new_id, json.dumps({"user_id":user_id, "klub_id":klub_id, "savez_id":savez_id, "role":role})))
conn.commit()
except psycopg2.IntegrityError as e:
conn.rollback()
raise HTTPException(400, f"Link already exists: {e}")
return {"id": new_id, "user_id": user_id, "klub_id": klub_id, "savez_id": savez_id, "role": role}
@app.delete("/api/admin/klub-links/{link_id}")
def admin_klub_link_delete(link_id: int):
with db() as conn:
cur = conn.cursor()
cur.execute("DELETE FROM pgz_sport.user_klub_links WHERE id=%s RETURNING user_id, klub_id, savez_id", (link_id,))
r = cur.fetchone()
if not r: raise HTTPException(404, "Link not found")
cur.execute("""INSERT INTO pgz_sport.sys_audit (action, target_type, target_id, payload)
VALUES ('user.klub_link.delete','sys_user_klub_links',%s,%s::jsonb)""",
(link_id, json.dumps({"user_id":r[0], "klub_id":r[1], "savez_id":r[2]})))
conn.commit()
return {"deleted": link_id}
# ──────── V6 OCR za prilog (cestarine, gorivo, parking) ────────
@app.post("/api/ai/ocr-prilog")
async def ai_ocr_prilog(file: UploadFile = File(...), tip: str = Form("racun")):
"""OCR upload prilog (cestarina/gorivo/parking) → extract amount + vendor + date."""
import tempfile, subprocess as sp
suffix = '.' + (file.filename or 'unknown').split('.')[-1].lower()
if suffix not in ['.pdf','.jpg','.jpeg','.png']:
raise HTTPException(400, "Only PDF/JPG/PNG")
with tempfile.NamedTemporaryFile(delete=False, suffix=suffix) as tf:
content = await file.read()
tf.write(content)
tmp_path = tf.name
text = ""
try:
if suffix == '.pdf':
r = sp.run(['pdftotext','-layout','-q', tmp_path,'-'], capture_output=True, timeout=30)
text = r.stdout.decode('utf-8','ignore')
if len(text) < 50: # scanned PDF, OCR it
r = sp.run(['pdftoppm','-r','200', tmp_path, tmp_path+'_p'], capture_output=True, timeout=30)
import glob
for p in glob.glob(tmp_path+'_p-*.ppm')[:3]:
r = sp.run(['tesseract', p, '-', '-l','hrv+eng'], capture_output=True, timeout=30)
text += r.stdout.decode('utf-8','ignore') + '\n'
else:
r = sp.run(['tesseract', tmp_path, '-', '-l','hrv+eng'], capture_output=True, timeout=30)
text = r.stdout.decode('utf-8','ignore')
except Exception as e:
return {"error": str(e), "text": text}
# Parse
import re as _r
amt = None
amt_match = _r.search(r'(?:UKUPNO|TOTAL|SVEUKUPNO|IZNOS|ZA UPLATU)[:\s]*?(\d+[,.]\d{2})\s*(?:EUR|HRK|kn|€)?', text, _r.IGNORECASE)
if not amt_match:
amt_match = _r.search(r'(\d+[,.]\d{2})\s*EUR\b', text, _r.IGNORECASE)
if amt_match:
try: amt = float(amt_match.group(1).replace(',','.'))
except: pass
date_match = _r.search(r'(\d{1,2})[./-](\d{1,2})[./-](\d{4}|\d{2})', text)
parsed_date = None
if date_match:
d, m, y = date_match.groups()
if len(y) == 2: y = '20' + y
try: parsed_date = f"{y}-{int(m):02d}-{int(d):02d}"
except: pass
vendor = None
for line in (text or '').split('\n')[:10]:
line = line.strip()
if line and not _r.match(r'^[\d\s.,/-]+$', line) and len(line) > 5 and len(line) < 80:
vendor = line
break
oib_match = _r.search(r'(?:OIB|VAT)[:\s]+(\d{11})', text)
oib = oib_match.group(1) if oib_match else None
import os as _os
try: _os.unlink(tmp_path)
except: pass
return {
"tip": tip,
"ai_amount": amt,
"ai_date": parsed_date,
"ai_vendor": vendor,
"ai_oib": oib,
"raw_text": text[:1500],
"filename": file.filename,
}
# ──────── /V6 ────────
@app.get("/api/admin/permissions-matrix")
def admin_perm_matrix():
with db() as conn:
cur = conn.cursor()
cur.execute("""SELECT DISTINCT user_type FROM pgz_sport.sys_role_permissions ORDER BY user_type""")
types = [r[0] for r in cur.fetchall()]
cur.execute("""SELECT p.code, p.naziv, p.kategorija, ARRAY_AGG(rp.user_type) granted_to
FROM pgz_sport.sys_permissions p
LEFT JOIN pgz_sport.sys_role_permissions rp ON rp.permission_code=p.code
GROUP BY p.code, p.naziv, p.kategorija
ORDER BY p.kategorija, p.code""")
matrix = []
for r in cur.fetchall():
matrix.append({
"code": r[0], "naziv": r[1], "kategorija": r[2],
"granted_to": [g for g in (r[3] or []) if g]
})
return {"user_types": types, "matrix": matrix}
# ──────── /V5 ────────
# Sprint 3 routers
import sys
sys.path.insert(0, '/opt/pgz-sport/routers')
try:
from img_proxy_router import router as img_proxy_router
from audit_coverage_router import router as audit_coverage_router
HAS_S3_ROUTERS = True
except Exception as e:
print(f'WARN: sprint3 routers not loaded: {e}')
HAS_S3_ROUTERS = False
app.include_router(v2_router)
# Stats router (live system counts for hero stats)
try:
from routers import stats_router
app.include_router(stats_router.router, tags=["stats"])
print("[STATS] router loaded")
except Exception as e:
print(f"[STATS] router fail: {e}")
# Admin Dashboard router (ERP/CRM/Tenants)
try:
from admin_router import router as admin_router
app.include_router(admin_router)
print('[ADMIN] router loaded')
except Exception as e:
print(f'[ADMIN] router fail: {e}')
# Sprint 3 includes
if HAS_S3_ROUTERS:
app.include_router(img_proxy_router, prefix='/api/v2')
app.include_router(audit_coverage_router, prefix='/api/v2')
# Round-2 enrichment endpoint
try:
from enrich_router import router as enrich_router
app.include_router(enrich_router, prefix='/api/v2')
print('[ENRICH] router loaded')
except Exception as e:
print(f'[ENRICH] router fail: {e}')
# === Round 3 / CC4 — ERP (M5: OCR + Invoices, M6: Putni nalozi) ===
sys.path.insert(0, '/opt/pgz-sport')
try:
from erp.ocr import router as erp_ocr_router
app.include_router(erp_ocr_router)
print('[ERP/OCR] router loaded')
except Exception as e:
print(f'[ERP/OCR] router fail: {e}')
try:
from routers.ocr_router import router as ocr_router
app.include_router(ocr_router)
print('[startup] ocr_router mounted')
except Exception as e:
print(f'[startup] ocr_router skipped: {e}')
try:
from erp.putni_nalozi import router as erp_putni_router
app.include_router(erp_putni_router)
print('[ERP/PUTNI] router loaded')
except Exception as e:
print(f'[ERP/PUTNI] router fail: {e}')
# === Round 3 / CC5 — CRM (M7 Članarine, M8 Liječnički, M9 Obrasci) ===
try:
from clanarine_router import router as clanarine_router
app.include_router(clanarine_router)
print('[CRM/M7] clanarine router loaded')
except Exception as e:
print(f'[CRM/M7] clanarine router fail: {e}')
try:
from lijecnicki_router import router as lijecnicki_router
app.include_router(lijecnicki_router)
print('[CRM/M8] lijecnicki router loaded')
except Exception as e:
print(f'[CRM/M8] lijecnicki router fail: {e}')
try:
from obrasci_router import router as obrasci_router
app.include_router(obrasci_router)
print('[CRM/M9] obrasci router loaded')
except Exception as e:
print(f'[CRM/M9] obrasci router fail: {e}')
try:
from clan_panel_router import router as clan_panel_router
app.include_router(clan_panel_router)
print('[CRM/PANEL] clan_panel router loaded (/api/crm/clanovi/{id}/full|avatar)')
except Exception as e:
print(f'[CRM/PANEL] clan_panel router fail: {e}')
try:
from crm_extras_router import router as crm_extras_router, alias_router as crm_extras_alias_router
app.include_router(crm_extras_router)
app.include_router(crm_extras_alias_router)
print('[CRM/R5] extras router loaded (bulk + xlsx + stats + notifications + ZIP + email tpl + /me)')
except Exception as e:
print(f'[CRM/R5] extras router fail: {e}')
# === W5 / Notification Center — /api/v2/notif/* ===
try:
from routers.notif_router import router as notif_router
app.include_router(notif_router)
print('[NOTIF] notif_router loaded (/api/v2/notif/list|count|{id}/read|mark-all-read|DELETE)')
except Exception as e:
print(f'[NOTIF] notif_router fail: {e}')
# === CRM v2 — Salesforce-Lite (Accounts/Contacts/Leads/Opportunities/Activities/Cases) ===
try:
from crm_router import router as crm_v2_router
app.include_router(crm_v2_router)
print('[CRM/v2] Salesforce-Lite router loaded (/api/v2/crm/*)')
except Exception as e:
print(f'[CRM/v2] router fail: {e}')
# === Round 3 / CC2 — M1 Auth + M2 Admin Users + M10 GDPR ===
try:
from auth.auth_v2 import router as auth_v2_router
app.include_router(auth_v2_router)
print('[AUTH/M1] auth_v2 router loaded (/api/auth/*)')
except Exception as e:
print(f'[AUTH/M1] auth_v2 router fail: {e}')
try:
from auth.admin_users import router as admin_users_router
app.include_router(admin_users_router)
print('[AUTH/M2] admin_users router loaded (/api/admin/users/*)')
except Exception as e:
print(f'[AUTH/M2] admin_users router fail: {e}')
try:
from auth.gdpr import router as gdpr_router, admin_router as gdpr_admin_router, me_router as gdpr_me_router
app.include_router(gdpr_router)
app.include_router(gdpr_admin_router)
app.include_router(gdpr_me_router)
print('[AUTH/M10] gdpr routers loaded (/api/gdpr/*, /api/admin/gdpr/*, /api/users/me/gdpr-*)')
except Exception as e:
print(f'[AUTH/M10] gdpr routers fail: {e}')
# === Round 3 / CC6 — M11 Blockchain audit (Polygon PoS sealing) ===
try:
from audit_seal_router import router as audit_seal_router
app.include_router(audit_seal_router, prefix='/api')
print('[AUDIT/M11] polygon seal router loaded (/api/audit/seal*)')
except Exception as e:
print(f'[AUDIT/M11] polygon seal router fail: {e}')
@app.get("/sport-3d")
@app.get("/3d")
def serve_sport_3d():
p = HTML_DIR / "sport_3d.html"
if p.exists():
return FileResponse(p)
return {"error": "sport_3d.html not found"}
@app.get("/admin")
@app.get("/admin/")
@app.get("/sport/sportas/{sid:int}", include_in_schema=False)
def sport_sportas_page(sid: int):
p = "/opt/pgz-sport/static/sportas_profile.html"
if os.path.exists(p):
return FileResponse(p)
return HTMLResponse("<h1>sportas profile page missing</h1>", status_code=404)
@app.get("/sport/admin")
@app.get("/sport/admin/")
def serve_admin():
p = HTML_DIR / "admin.html"
if p.exists():
return FileResponse(p)
return {"error": "admin.html not found"}
@app.get("/erp")
@app.get("/erp/")
@app.get("/app/erp")
@app.get("/app/erp/")
def serve_erp():
p = HTML_DIR / "erp.html"
if p.exists():
return FileResponse(p)
return {"error": "erp.html not found"}
@app.get("/erp/full")
@app.get("/erp/full/")
@app.get("/sport/erp/full")
@app.get("/sport/erp")
def serve_erp_full():
p = HTML_DIR / "erp_full.html"
if p.exists():
return FileResponse(p)
return {"error": "erp_full.html not found"}
# ═══ ERP FULL (SAP-Lite) router — kontni plan, dnevnik, glavna knjiga, partneri,
# racuni ulazni/izlazni, e-Račun XML, PDV, plaće, izvještaji, export
try:
from routers.erp_full_router import router as erp_full_router
app.include_router(erp_full_router)
print('[ERP-FULL] router loaded (/api/v2/erp/*)')
except Exception as e:
print(f'[ERP-FULL] router fail: {e}')
# ═══ KALENDAR (CRUD events) router — /api/v2/kalendar/events
try:
from routers.kalendar_router import router as kalendar_router
app.include_router(kalendar_router)
print('[KALENDAR] router loaded (/api/v2/kalendar/*)')
except Exception as e:
print(f'[KALENDAR] router fail: {e}')
# ═══ EXPORT (univerzalni CSV / XLSX / PDF) router — /api/v2/export/*
try:
from routers.export_router import router as export_router
app.include_router(export_router)
print('[EXPORT] router loaded (/api/v2/export/*)')
except Exception as e:
print(f'[EXPORT] router fail: {e}')
@app.get("/crm")
@app.get("/crm/")
@app.get("/sport/crm")
@app.get("/sport/crm/")
def serve_crm():
p = HTML_DIR / "crm.html"
if p.exists():
return FileResponse(p)
return {"error": "crm.html not found"}
@app.get("/crm-v2")
@app.get("/crm-v2/")
@app.get("/crm_v2")
@app.get("/crm_v2/")
@app.get("/crm/v2")
@app.get("/sport/crm/v2")
@app.get("/sport/crm_v2")
def serve_crm_v2():
p = HTML_DIR / "crm_v2.html"
if p.exists():
return FileResponse(p)
return {"error": "crm_v2.html not found"}
@app.get("/login")
@app.get("/login/")
def serve_login():
p = HTML_DIR / "login.html"
if p.exists():
return FileResponse(p)
return {"error": "login.html not found"}
@app.get("/admin/users")
@app.get("/admin/users/")
@app.get("/sport/admin/users")
@app.get("/sport/admin/users/")
def serve_admin_users():
p = HTML_DIR / "admin_users.html"
if p.exists():
return FileResponse(p)
return {"error": "admin_users.html not found"}
@app.get("/api/sportski-objekti")
def list_sportski_objekti(q=None,tip=None,grad=None):
w=["aktivan=TRUE"]; p=[]
if q: w.append("(public.f_unaccent(naziv) ILIKE public.f_unaccent(%s) OR public.f_unaccent(adresa) ILIKE public.f_unaccent(%s) OR public.f_unaccent(grad) ILIKE public.f_unaccent(%s))"); p+=["%"+q+"%"]*3
if tip: w.append("tip ILIKE %s"); p.append("%"+tip+"%")
if grad: w.append("public.f_unaccent(grad) ILIKE public.f_unaccent(%s)"); p.append("%"+grad+"%")
rows=fetch("SELECT * FROM pgz_sport.sportski_objekti WHERE "+" AND ".join(w)+" ORDER BY grad,naziv",p)
return {"count":len(rows),"rows":rows}
@app.get("/api/clanovi-full")
def list_clanovi_full(q=None,hoo=None,reprezentativac=None,klub_id=None,
sport=None,kategorija=None,godina_rodenja=None,status=None,
limit=80,authorization=None):
w=["aktivan=TRUE"]; p=[]
if q: w.append("(public.f_unaccent(ime) ILIKE public.f_unaccent(%s) OR public.f_unaccent(prezime) ILIKE public.f_unaccent(%s) OR klub_naziv_godisnjak ILIKE %s)"); p+=["%"+q+"%"]*3
if hoo: w.append("hoo_kategorija=%s"); p.append(hoo)
if reprezentativac is not None: w.append("reprezentativac="+(("TRUE") if str(reprezentativac).lower()=="true" else "FALSE"))
if klub_id: w.append("klub_id=%s"); p.append(int(klub_id))
if sport: w.append("sport ILIKE %s"); p.append(f"%{sport}%")
if kategorija:
w.append("(kategorija ILIKE %s OR %s = ANY(COALESCE(kategorije,ARRAY[]::text[])))")
p.extend([f"%{kategorija}%", kategorija])
if godina_rodenja:
try:
w.append("EXTRACT(YEAR FROM datum_rodenja)=%s"); p.append(int(godina_rodenja))
except (TypeError, ValueError):
pass
if status:
s = str(status).strip().lower()
if s in ("reprezentativac","reprezentativci"): w.append("reprezentativac=TRUE")
elif s in ("kategoriziran","kategorizirani"): w.append("kategoriziran=TRUE")
elif s in ("stipendiran","stipendirani"): w.append("stipendiran=TRUE")
elif s in ("aktivan","aktivni"): w.append("aktivan=TRUE")
elif s in ("neaktivan","neaktivni","arhivirani"): w.append("aktivan=FALSE")
lim=min(int(limit or 80),500)
sql="SELECT id,ime,prezime,oib,datum_rodenja,spol,sport,pozicija,reprezentativac,kategoriziran,stipendiran,kategorija,kategorije,kategorija_hoo,hoo_kategorija,aktivan,klub_id,klub_naziv_godisnjak,slika_url,profile_url,hns_igrac_id,visina_cm,tezina_kg,broj_dresa,uloga,godisnjak_godine,godisnjak_prvi,godisnjak_zadnji,napomena FROM pgz_sport.clanovi WHERE "+" AND ".join(w)+" ORDER BY prezime,ime LIMIT "+str(lim)
rows=fetch(sql,p)
return {"count":len(rows),"rows":rows}
@app.get("/api/gradovi")
def list_gradovi():
rows=fetch("SELECT DISTINCT grad FROM pgz_sport.klubovi WHERE aktivan=TRUE AND grad IS NOT NULL AND grad<>'' AND grad NOT SIMILAR TO '[0-9]+%%' ORDER BY grad",[])
return [r["grad"] for r in rows]
@app.get("/api/manifestacije-full")
def list_manifestacije_full(q=None,razina=None):
w=["aktivna=TRUE"]; p=[]
if q: w.append("(public.f_unaccent(naziv) ILIKE public.f_unaccent(%s) OR mjesto ILIKE %s)"); p+=["%"+q+"%"]*2
rows=fetch("SELECT id,naziv,mjesto,organizator,razina,broj_ucesnika,godina_od,spol_kategorija,napomena,source_url FROM pgz_sport.manifestacije WHERE "+" AND ".join(w)+" ORDER BY naziv",p)
return {"count":len(rows),"rows":rows}
# ── SUFINANCIRANJE-ALL v1.0 dradulic@outlook.com 2026-05-04
@app.get("/api/sufinanciranje")
def list_sufinanciranje(q=None, godina=None, razina=None, sport=None, limit=500):
w=["iznos_eur > 0"]; p=[]
if q: w.append("(LOWER(korisnik) LIKE %s OR LOWER(sport) LIKE %s)"); p+=[f"%{q.lower()}%"]*2
if godina: w.append("godina=%s"); p.append(int(godina))
if razina: w.append("razina ILIKE %s"); p.append(f"%{razina}%")
if sport: w.append("sport ILIKE %s"); p.append(f"%{sport}%")
sql=f"SELECT korisnik,sport,iznos_eur,vrsta,razina,izvor,source_url,godina FROM pgz_sport.sufinanciranje_sport WHERE {' AND '.join(w)} ORDER BY iznos_eur DESC LIMIT {min(int(limit),1000)}"
rows=fetch(sql,p)
total=sum(float(r.get('iznos_eur') or 0) for r in rows)
years=sorted(set(r.get('godina') for r in rows if r.get('godina')),reverse=True)
return {"count":len(rows),"total":total,"years":years,"rows":rows}
# ══════════════════════════════════════════════════════════════════
# ERP PLATFORM ROUTES v2.0 — dradulic@outlook.com — 2026-05-04
# ══════════════════════════════════════════════════════════════════
import hashlib
def hash_pwd(pwd): return hashlib.sha256(pwd.encode()).hexdigest()
def get_user(token):
if not token: return None
try:
payload = _jwt.decode(token.replace("Bearer ",""), JWT_SECRET, algorithms=["HS256"])
uid = payload.get("uid")
if uid:
rows = fetch("SELECT * FROM pgz_sport.users WHERE id=%s AND aktivan=TRUE", [uid])
return rows[0] if rows else None
return payload
except: return None
# ── AUTH: Email/Password login — handled by auth.auth_v2 router (M1) ──
# ── SPORTAS FULL PROFILE ─────────────────────────────────────────
@app.get("/api/sportas/{clan_id}/profil")
def sportas_profil(clan_id: int):
clan = fetch("""SELECT c.*, k.naziv AS klub_naziv_full, k.sport AS klub_sport,
k.grad, k.logo_url FROM pgz_sport.clanovi c
LEFT JOIN pgz_sport.klubovi k ON k.id=c.klub_id WHERE c.id=%s""", [clan_id])
if not clan: raise HTTPException(404,"Nije pronađen")
c = clan[0]
sezona = fetch("""SELECT * FROM pgz_sport.clan_sezona WHERE clan_id=%s ORDER BY sezona DESC""", [clan_id])
utakmice = fetch("""SELECT * FROM pgz_sport.utakmice_log WHERE clan_id=%s ORDER BY datum DESC LIMIT 30""", [clan_id])
nagrade = fetch("SELECT * FROM pgz_sport.clan_nagrada WHERE clan_id=%s ORDER BY godina DESC", [clan_id])
godisnjaci = fetch("SELECT * FROM pgz_sport.clan_godisnjak WHERE clan_id=%s ORDER BY godina DESC", [clan_id])
stats = {}
if sezona:
stats = {"ukupno_nastupa": sum((r.get("nastupi") or 0) for r in sezona),
"ukupno_pogodaka": sum((r.get("pogoci") or 0) for r in sezona),
"ukupno_asistencija": sum((r.get("asistencije") or 0) for r in sezona),
"ukupno_zutih": sum((r.get("zuti_kartoni") or 0) for r in sezona),
"ukupno_crvenih": sum((r.get("crveni_kartoni") or 0) for r in sezona),
"ukupno_minuta": sum((r.get("minute_total") or 0) for r in sezona),
"sezone_aktivne": len(sezona)}
return {**c,"clan_sezona":sezona,"utakmice":utakmice,"nagrade":nagrade,
"godisnjaci":godisnjaci,"stats":stats}
# ── SAVEZ FULL DETAIL ────────────────────────────────────────────
@app.get("/api/savezi/{savez_id}/full")
def savez_full(savez_id: int):
s = fetch("SELECT * FROM pgz_sport.savezi WHERE id=%s",[savez_id])
if not s: raise HTTPException(404,"Savez nije pronađen")
klubovi = fetch("""SELECT id,naziv,sport,grad,predsjednik,tajnik,nositelj_kvalitete,
aktivan,oib,razina,broj_clanova FROM pgz_sport.klubovi WHERE savez_id=%s AND aktivan=TRUE ORDER BY naziv""",[savez_id])
clanovi = fetch("""SELECT c.id,c.ime,c.prezime,c.sport,c.pozicija,c.kategorija,
c.reprezentativac,c.kategoriziran,c.slika_url,c.hoo_kategorija,c.klub_naziv_godisnjak,c.aktivan
FROM pgz_sport.clanovi c WHERE c.savez_kod=(SELECT kod FROM pgz_sport.savezi WHERE id=%s) LIMIT 200""",[savez_id])
if not clanovi:
clanovi = fetch("""SELECT c.id,c.ime,c.prezime,c.sport,c.pozicija,c.kategorija,
c.reprezentativac,c.kategoriziran,c.slika_url,c.hoo_kategorija,c.klub_naziv_godisnjak,c.aktivan
FROM pgz_sport.clanovi c WHERE c.aktivan=TRUE AND c.sport ILIKE %s LIMIT 200""",
[f'%{s[0].get("sport","") or ""}%'])
treneri = fetch("""SELECT * FROM pgz_sport.treneri WHERE savez_id=%s""",[savez_id])
return {**s[0],"klubovi":klubovi,"clanovi":clanovi[:100],"treneri":treneri}
# ── KLUB ERP: CLANARINE ──────────────────────────────────────────
@app.get("/api/klub/{klub_id}/clanarine")
def klub_clanarine(klub_id: int, godina: int=None, status: str=None):
w=["c.klub_id=%s"]; p=[klub_id]
if godina: w.append("cl.godina=%s"); p.append(godina)
if status: w.append("cl.status=%s"); p.append(status)
rows = fetch(f"""SELECT cl.*,c.ime,c.prezime,c.oib,c.spol,c.kategorija,c.hoo_kategorija,c.slika_url
FROM pgz_sport.clanarine cl JOIN pgz_sport.clanovi c ON c.id=cl.clan_id
WHERE {" AND ".join(w)} ORDER BY cl.godina DESC, c.prezime""", p)
total_p = sum(float(r.get("iznos_placen") or 0) for r in rows)
total_d = sum(float(r.get("iznos_propisan") or 0) - float(r.get("iznos_placen") or 0) for r in rows)
return {"count":len(rows),"naplaceno":total_p,"dug":total_d,"rows":rows}
# ── KLUB ERP: LIJECNICKI ─────────────────────────────────────────
@app.get("/api/klub/{klub_id}/lijecnicki")
def klub_lijecnicki(klub_id: int):
import datetime; today = datetime.date.today()
rows = fetch("""SELECT lp.*,c.ime,c.prezime,c.oib,c.kategorija,c.slika_url,
CASE WHEN lp.vrijedi_do IS NULL THEN 'nepoznato'
WHEN lp.vrijedi_do < CURRENT_DATE THEN 'istekao'
WHEN lp.vrijedi_do < CURRENT_DATE + 30 THEN 'uskoro_istece'
ELSE 'validan' END AS status_pregled
FROM pgz_sport.lijecnicki_pregledi lp JOIN pgz_sport.clanovi c ON c.id=lp.clan_id
WHERE c.klub_id=%s ORDER BY lp.vrijedi_do ASC NULLS LAST""", [klub_id])
alert_istekli = [r for r in rows if r.get("status_pregled")=="istekao"]
alert_uskoro = [r for r in rows if r.get("status_pregled")=="uskoro_istece"]
return {"count":len(rows),"istekli":len(alert_istekli),"uskoro":len(alert_uskoro),"rows":rows}
# ── NETWORK GRAPH DATA ───────────────────────────────────────────
@app.get("/api/network/pgz")
def network_pgz(q: str=None, entity_type: str=None, max_nodes: int=80):
FORENSIC_NAMES = {"SAMIR BARAĆ","MIROSLAV MARIĆ","VELIMIR LIVERIĆ","DOROTEA PESIC-BUKOVAC"}
nodes,edges,seen_nodes,seen_edges = [],[],set(),set()
def add_node(nid, label, ntype, meta=None):
if nid not in seen_nodes:
seen_nodes.add(nid)
nodes.append({"id":nid,"label":label,"type":ntype,"forensic":label.upper() in FORENSIC_NAMES,"meta":meta or {}})
def add_edge(s,t,rel=""):
k=f"{s}-{t}"
if k not in seen_edges:
seen_edges.add(k); edges.append({"source":s,"target":t,"rel":rel})
if q:
# Person search
persons = fetch("""SELECT p.id,p.name,p.function,e.name as ent,e.id as eid,e.entity_type,e.city
FROM civic.persons p JOIN civic.entities e ON e.id=p.entity_id
WHERE public.f_unaccent(p.name) ILIKE public.f_unaccent(%s) OR public.f_unaccent(e.name) ILIKE public.f_unaccent(%s) LIMIT 60""",[f"%{q}%",f"%{q}%"])
for r in persons:
pid=f"p_{r['id']}"; eid=f"e_{r['eid']}"
add_node(pid,r.get("name","?")[:30],"person")
add_node(eid,r.get("ent","?")[:30],"club" if "Udruga" in (r.get("entity_type") or "") else "company")
add_edge(pid,eid,r.get("function",""))
else:
# Default: top connected persons
rels = fetch("""SELECT p.id,p.name,e.id as eid,e.name as ent,e.entity_type,p.function
FROM civic.persons p JOIN civic.entities e ON e.id=p.entity_id
WHERE e.county ILIKE '%%goranska%%' OR e.county ILIKE '%%primorska%%'
ORDER BY p.id LIMIT %s""",[max_nodes])
for r in rels:
pid=f"p_{r['id']}"; eid=f"e_{r['eid']}"
add_node(pid,r.get("name","?")[:25],"person")
add_node(eid,r.get("ent","?")[:25],"club" if "Udruga" in (r.get("entity_type") or "") else "company",
{"city":r.get("city"),"type":r.get("entity_type")})
add_edge(pid,eid,r.get("function",""))
return {"nodes":nodes[:200],"edges":edges[:400],"query":q}
@app.get("/platform")
@app.get("/platform/")
@app.get("/sport/platform")
@app.get("/sport/platform/")
def serve_platform():
p = HTML_DIR / "platform.html"
if p.exists(): return FileResponse(p)
return {"error": "platform.html not found"}
@app.get("/app")
@app.get("/app/")
def serve_app():
p = HTML_DIR / "app.html"
return FileResponse(p) if p.exists() else {"error":"app.html not found"}
@app.get("/audit")
@app.get("/audit/")
def serve_audit():
p = HTML_DIR / "audit.html"
return FileResponse(p) if p.exists() else {"error":"audit.html not found"}
@app.get("/kpi")
@app.get("/kpi/")
def serve_kpi():
p = HTML_DIR / "kpi.html"
return FileResponse(p) if p.exists() else {"error":"kpi.html not found"}
@app.get("/dokumenti")
@app.get("/dokumenti/")
@app.get("/sport/dokumenti")
@app.get("/sport/dokumenti/")
def serve_dokumenti():
p = HTML_DIR / "dokumenti.html"
return FileResponse(p) if p.exists() else {"error":"dokumenti.html not found"}
# ─── Avatar fallback (CC26) ─────────────────────────────────────────────
# Routes registered BEFORE the StaticFiles mount so they intercept /static/...
# for the avatar subpaths. When the requested file is missing on disk we
# 302-redirect to /static/avatars/default.png instead of returning 404 — that
# keeps <img> tags rendering and lets the FE onError handler show initials.
import re as _re_av
_AVATAR_NAME_RE = _re_av.compile(r"^[A-Za-z0-9._-]+\.(png|jpe?g|webp|gif)$")
_DEFAULT_AVATAR_URL = "/static/avatars/default.png"
@app.get("/static/uploads/avatars/{name}")
def _serve_clan_avatar(name: str):
if not _AVATAR_NAME_RE.match(name or ""):
from fastapi.responses import RedirectResponse
return RedirectResponse(_DEFAULT_AVATAR_URL, status_code=302)
p = HTML_DIR / "uploads" / "avatars" / name
if p.exists() and p.is_file():
return FileResponse(p)
from fastapi.responses import RedirectResponse
return RedirectResponse(_DEFAULT_AVATAR_URL, status_code=302)
@app.get("/uploads/avatars/{name}")
def _serve_user_avatar(name: str):
if not _AVATAR_NAME_RE.match(name or ""):
from fastapi.responses import RedirectResponse
return RedirectResponse(_DEFAULT_AVATAR_URL, status_code=302)
import pathlib as _pl_av
p = _pl_av.Path("/opt/pgz-sport/uploads/avatars") / name
if p.exists() and p.is_file():
return FileResponse(p)
from fastapi.responses import RedirectResponse
return RedirectResponse(_DEFAULT_AVATAR_URL, status_code=302)
# ─────────────────────────────────────────────────────────────────────────
app.mount("/static", StaticFiles(directory=str(HTML_DIR)), name="static")
# User-uploaded files (avatars, etc.) — served at /uploads/*
import pathlib as _pl
_UPLOAD_DIR = _pl.Path("/opt/pgz-sport/uploads")
_UPLOAD_DIR.mkdir(parents=True, exist_ok=True)
(_UPLOAD_DIR / "avatars").mkdir(parents=True, exist_ok=True)
app.mount("/uploads", StaticFiles(directory=str(_UPLOAD_DIR)), name="uploads")
# === DEBUG observability router (live dashboard) ===
try:
from routers.debug_router import router as debug_router
app.include_router(debug_router)
print('[DEBUG] observability router loaded (/api/debug/*)')
except Exception as e:
print(f'[DEBUG] router fail: {e}')
@app.get("/api/v2/clan/{clan_id}/hns-career")
def clan_hns_career(clan_id: int):
"""HNS karijera za sportaša: sezone + utakmice."""
seasons = fetch("""
SELECT sezona, klub_naziv, natjecanje, nastupi, startna, zamjena, golovi, asistencije, zuti, crveni, minute
FROM pgz_sport.hns_player_seasons
WHERE clan_id = %s
ORDER BY sezona DESC
""", (clan_id,))
matches = fetch("""
SELECT datum, natjecanje, domacin, gost, rezultat, pozicija, startna, golovi, asistencije, zuti, crveni
FROM pgz_sport.hns_player_matches
WHERE clan_id = %s
ORDER BY datum DESC NULLS LAST
LIMIT 50
""", (clan_id,))
# Stats roll-up
summary = fetch("""
SELECT
count(DISTINCT sezona) AS sezona_broj,
sum(nastupi) AS ukupno_nastupi,
sum(golovi) AS ukupno_golovi,
sum(asistencije) AS ukupno_asistencije,
sum(zuti) AS ukupno_zuti,
sum(crveni) AS ukupno_crveni
FROM pgz_sport.hns_player_seasons WHERE clan_id = %s
""", (clan_id,))
return {
"clan_id": clan_id,
"summary": summary[0] if summary else {},
"seasons": seasons,
"matches": matches,
"total_seasons": len(seasons),
"total_matches": len(matches),
}
@app.get("/api/v2/klubovi/pgz-financirani")
def klubovi_pgz_financirani(sport: str = None, limit: int = 500):
"""PGŽ financirani klubovi — koji su primili novce iz potpora."""
where_extra = ""
params = []
if sport:
where_extra = " WHERE sport = %s"
params.append(sport)
rows = fetch(f"""
SELECT k.*,
(SELECT count(*) FROM pgz_sport.clanovi WHERE klub_id = k.id) AS sportasa_count,
(SELECT count(*) FROM pgz_sport.hns_klub_roster WHERE klub_id = k.id) AS hns_roster_count,
(SELECT count(*) FROM pgz_sport.potpore_nositelji WHERE klub_id = k.id OR naziv_kluba ILIKE k.naziv) AS potpora_count,
(SELECT sum(iznos) FROM pgz_sport.potpore_nositelji WHERE klub_id = k.id OR naziv_kluba ILIKE k.naziv) AS potpora_ukupno
FROM pgz_sport.v_pgz_financirani_klubovi k
{where_extra}
ORDER BY potpora_ukupno DESC NULLS LAST
LIMIT %s
""", tuple(params) + (limit,))
return {"count": len(rows), "rows": rows}
@app.get("/api/v2/dashboard/hns-coverage")
def dashboard_hns_coverage():
"""HNS Coverage widget data."""
stats = fetch("""
SELECT
(SELECT count(*) FROM pgz_sport.v_pgz_financirani_klubovi WHERE sport='nogomet' AND source_url LIKE %s) AS klubova_target,
(SELECT count(DISTINCT klub_id) FROM pgz_sport.hns_klub_roster) AS klubova_scraped,
(SELECT count(*) FROM pgz_sport.clanovi WHERE hns_igrac_id IS NOT NULL) AS sportasa_s_hns,
(SELECT count(*) FROM pgz_sport.hns_klub_roster) AS roster_total,
(SELECT count(*) FROM pgz_sport.hns_player_seasons) AS seasons_total,
(SELECT max(scraped_at) FROM pgz_sport.hns_klub_roster) AS last_sync
""", ('%semafor.hns.family/klubovi%',))
return stats[0] if stats else {}
@app.get("/api/v2/enrich-sources")
def enrich_sources():
"""Sport→source mapping za frontend Obogati podatke dugme."""
rows = fetch("SELECT * FROM pgz_sport.enrichment_sources ORDER BY sport")
return {"sources": rows}
@app.get("/api/v2/clan/{clan_id}/kategorije")
def clan_kategorije(clan_id: int):
"""Kategorije igrača (M2M)."""
rows = fetch("""
SELECT kategorija, sezona, klub_id, source, source_url, scraped_at
FROM pgz_sport.clan_kategorije WHERE clan_id = %s
ORDER BY sezona DESC, kategorija
""", (clan_id,))
return {"clan_id": clan_id, "kategorije": rows}
@app.get("/api/v2/klubovi/priority-sort")
def klubovi_priority_sort(sport: str = None, limit: int = 500):
"""Klubovi sortirani: priority (financirani || godišnjak) prvi."""
where = ""
params = []
if sport:
where = " WHERE sport = %s"
params.append(sport)
rows = fetch(f"""
SELECT k.*, k.priority_label,
(SELECT count(*) FROM pgz_sport.clanovi WHERE klub_id = k.id) AS sportasa,
(SELECT count(*) FROM pgz_sport.hns_klub_roster WHERE klub_id = k.id) AS hns_roster,
(SELECT sum(iznos) FROM pgz_sport.potpore_nositelji WHERE klub_id = k.id OR naziv_kluba ILIKE k.naziv) AS potpora_ukupno
FROM pgz_sport.v_klubovi_priority_sort k
{where}
ORDER BY priority, potpora_ukupno DESC NULLS LAST, naziv
LIMIT %s
""", tuple(params) + (limit,))
return {"count": len(rows), "rows": rows}
@app.get("/api/v2/clan/{clan_id}/full")
def clan_full(clan_id: int):
"""Punu sliku igrača: profil + kategorije + sezone + utakmice + potpore.
SUB6 (2026-05-05): join klub_naziv u kategorije za drill-down panel."""
profile = fetch("""
SELECT c.*, k.naziv AS klub_naziv
FROM pgz_sport.clanovi c
LEFT JOIN pgz_sport.klubovi k ON k.id = c.klub_id
WHERE c.id = %s
""", (clan_id,))
if not profile: return {"error": "not_found"}
p = profile[0]
kategorije = fetch("""
SELECT ck.id, ck.clan_id, ck.kategorija, ck.sezona, ck.klub_id,
ck.source, ck.source_url, ck.scraped_at,
k.naziv AS klub_naziv
FROM pgz_sport.clan_kategorije ck
LEFT JOIN pgz_sport.klubovi k ON k.id = ck.klub_id
WHERE ck.clan_id = %s
ORDER BY ck.sezona DESC NULLS LAST, ck.kategorija
""", (clan_id,))
seasons = fetch("SELECT * FROM pgz_sport.hns_player_seasons WHERE clan_id = %s ORDER BY sezona DESC", (clan_id,))
matches = fetch("SELECT * FROM pgz_sport.hns_player_matches WHERE clan_id = %s ORDER BY datum DESC NULLS LAST LIMIT 30", (clan_id,))
multi_stats = fetch("SELECT * FROM pgz_sport.player_stats WHERE clan_id = %s ORDER BY sezona DESC", (clan_id,))
return {
"profile": p,
"kategorije": kategorije,
"hns_seasons": seasons,
"hns_matches": matches,
"multi_sport_stats": multi_stats,
"stats": {
"total_seasons": len(seasons),
"total_matches": len(matches),
"total_kategorije": len(kategorije),
}
}
@app.post("/api/v2/export/klubovi")
def export_klubovi(req: dict):
"""Export klubova kao XLSX."""
import io
try:
from openpyxl import Workbook
except ImportError:
return {"error": "openpyxl not installed"}
ids = req.get("ids", [])
if not ids:
return {"error": "no ids"}
rows = fetch("""
SELECT k.id, k.naziv, k.sport, k.razina, k.oib, k.grad,
k.financiran, k.u_godisnjaku, k.priority_label,
(SELECT count(*) FROM pgz_sport.clanovi WHERE klub_id = k.id) AS sportasa,
(SELECT sum(iznos) FROM pgz_sport.potpore_nositelji WHERE klub_id = k.id OR naziv_kluba ILIKE k.naziv) AS potpora
FROM pgz_sport.v_klubovi_priority_sort k
WHERE k.id = ANY(%s)
ORDER BY k.priority, k.naziv
""", (ids,))
wb = Workbook()
ws = wb.active
ws.title = "Klubovi"
if rows:
headers = list(rows[0].keys())
ws.append([h.replace('_',' ').title() for h in headers])
for r in rows:
ws.append([r.get(h) for h in headers])
buf = io.BytesIO()
wb.save(buf)
buf.seek(0)
from fastapi.responses import StreamingResponse
return StreamingResponse(
buf,
media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
headers={"Content-Disposition": f"attachment; filename=klubovi_export_{int(time.time())}.xlsx"}
)
@app.get("/api/v2/sportasi/financirani")
def sportasi_financirani(sport: str = None, kategorija: str = None, godiste: int = None, limit: int = 1000):
"""Sportaši koji pripadaju klubovima financiranim od PGŽ."""
where = ["c.klub_id IN (SELECT id FROM pgz_sport.v_pgz_priority_klubovi WHERE financiran)"]
params = []
if sport:
where.append("c.sport = %s"); params.append(sport)
if kategorija:
where.append("c.kategorija = %s"); params.append(kategorija)
if godiste:
where.append("EXTRACT(YEAR FROM c.datum_rodenja) = %s OR c.godina_rodenja = %s")
params.extend([godiste, godiste])
rows = fetch(f"""
SELECT c.id, c.ime, c.prezime, c.sport, c.kategorija, c.klub_id,
k.naziv AS klub_naziv, k.financiran, k.u_godisnjaku, k.priority_label,
c.datum_rodenja, c.godina_rodenja, c.hns_igrac_id, c.source_url,
(SELECT count(*) FROM pgz_sport.hns_player_seasons WHERE clan_id = c.id) AS sezone,
(SELECT count(*) FROM pgz_sport.clan_kategorije WHERE clan_id = c.id) AS kategorije_count
FROM pgz_sport.clanovi c
JOIN pgz_sport.v_klubovi_priority_sort k ON k.id = c.klub_id
WHERE {' AND '.join(where)}
ORDER BY c.prezime, c.ime
LIMIT %s
""", tuple(params) + (limit,))
return {"count": len(rows), "rows": rows}
@app.get("/api/v2/savezi/financirani")
def savezi_financirani(sport: str = None):
"""Savezi (i njihovi klubovi) koji su PGŽ financirani."""
where = ""
params = []
if sport:
where = " AND s.sport = %s"
params.append(sport)
rows = fetch(f"""
SELECT s.id, s.naziv, s.sport, s.razina, s.oib,
(SELECT count(*) FROM pgz_sport.klubovi k WHERE k.savez_id = s.id) AS klubovi,
(SELECT count(*) FROM pgz_sport.v_pgz_priority_klubovi k WHERE k.savez_id = s.id AND k.financiran) AS klubovi_financirani,
(SELECT count(*) FROM pgz_sport.clanovi c JOIN pgz_sport.klubovi k ON k.id = c.klub_id WHERE k.savez_id = s.id) AS sportasa,
(SELECT sum(iznos) FROM pgz_sport.potpore_nositelji p
JOIN pgz_sport.klubovi k ON k.id = p.klub_id WHERE k.savez_id = s.id) AS potpora_ukupno
FROM pgz_sport.savezi s
WHERE 1=1 {where}
ORDER BY potpora_ukupno DESC NULLS LAST
""", tuple(params))
return {"count": len(rows), "rows": rows}
# ═══════════════════════════════════════════════════════════════════
# DOKUMENTI ENDPOINTS — godišnjaci, publikacije, sport-savez izdanja
# ═══════════════════════════════════════════════════════════════════
@app.get("/api/v2/dokumenti")
def dokumenti_list(vrsta: str = None, sport: str = None, godina: int = None, q: str = None, organizacija: str = None, izvor: str = None, limit: int = 100):
"""Lista dokumenata: godišnjaci, publikacije, etc."""
where = ["aktivan = true"]
params = []
if vrsta:
where.append("vrsta = %s"); params.append(vrsta)
if sport:
where.append("sport = %s"); params.append(sport)
if godina:
where.append("godina = %s"); params.append(godina)
if organizacija:
where.append("organizacija ILIKE %s"); params.append(f"%%{organizacija}%%")
if izvor:
where.append("organizacija ILIKE %s"); params.append(f"%%{izvor}%%")
if q:
where.append("(public.f_unaccent(title) ILIKE public.f_unaccent(%s) OR sadrzaj_summary ILIKE %s OR organizacija ILIKE %s)")
params.extend([f"%%{q}%%"]*3)
rows = fetch(f"""
SELECT id, title, fname, vrsta, sport, godina, organizacija,
izvor_url, izdano_datum, kratak_opis, kljucne_rijeci, scraped_at,
LENGTH(sadrzaj) AS sadrzaj_size
FROM pgz_sport.dokumenti
WHERE {' AND '.join(where)}
ORDER BY godina DESC NULLS LAST, izdano_datum DESC NULLS LAST, id DESC
LIMIT %s
""", tuple(params) + (limit,))
return {"count": len(rows), "rows": rows}
@app.get("/api/v2/dokumenti/godisnjaci/list")
def dokumenti_godisnjaci_list():
"""Lista svih godišnjaka."""
rows = fetch("""
SELECT id, godina, title, fname, izdano_datum, organizacija, kratak_opis,
LENGTH(sadrzaj) AS sadrzaj_size
FROM pgz_sport.dokumenti
WHERE vrsta = 'godisnjak'
ORDER BY godina DESC
""")
return {"count": len(rows), "godisnjaci": rows}
@app.get("/api/v2/dokumenti/godisnjak/{godina}")
def dokument_godisnjak_pdf(godina: int):
"""PDF godišnjaka."""
from fastapi.responses import FileResponse
import os
pdf_path = f"/opt/pgz-sport/_data/godisnjaci/godisnjak_{godina}.pdf"
if os.path.exists(pdf_path):
# inline disposition → render in-tab; not download
return FileResponse(pdf_path, media_type="application/pdf",
headers={"Content-Disposition": f'inline; filename="godisnjak_{godina}.pdf"'})
return {"error": "not_found", "godina": godina}
@app.get("/api/v2/dokumenti/{doc_id}")
def dokument_detail(doc_id: int):
"""Detaljan pregled dokumenta."""
rows = fetch("SELECT id, title, vrsta, sport, godina, organizacija, izvor_url, kratak_opis, sadrzaj_summary, kljucne_rijeci, scraped_at FROM pgz_sport.dokumenti WHERE id = %s", (doc_id,))
if not rows: return {"error": "not_found"}
return rows[0]
@app.get("/favicon.ico")
def serve_favicon():
from fastapi.responses import FileResponse
return FileResponse("/opt/pgz-sport/static/favicon.ico", media_type="image/x-icon")
@app.get("/api/v2/klubovi/financirani")
def klubovi_financirani(sport: str = None, davatelj: str = None, godina: int = None, with_data: bool = False, limit: int = 1000):
"""Klubovi koji su primili novac od PGŽ/RSS/Grad Rijeka. davatelj: pgz|rss|grad_rijeka|any"""
where = []
params = []
if sport:
where.append("k.sport = %s")
params.append(sport)
if davatelj == 'pgz':
where.append("k.prima_pgz = true")
elif davatelj == 'rss':
where.append("k.prima_rss = true")
elif davatelj == 'grad_rijeka':
where.append("k.prima_grad_rijeka = true")
elif davatelj == 'any':
where.append("(k.prima_pgz OR k.prima_rss OR k.prima_grad_rijeka)")
if with_data:
# Default for demo: only klubovi koji imaju nešto (potpore || godišnjak || HNS roster)
where.append("(k.prima_pgz OR k.prima_rss OR k.prima_grad_rijeka OR k.u_godisnjaku OR EXISTS (SELECT 1 FROM pgz_sport.hns_klub_roster WHERE klub_id = k.id))")
where_sql = "WHERE " + " AND ".join(where) if where else ""
rows = fetch(f"""
SELECT k.id, k.naziv, k.sport, k.razina, k.oib, k.grad, k.adresa,
k.prima_pgz, k.prima_rss, k.prima_grad_rijeka, k.u_godisnjaku,
k.broj_potpora, k.ukupno_potpora,
(SELECT count(*) FROM pgz_sport.clanovi WHERE klub_id = k.id) AS sportasa
FROM pgz_sport.v_klubovi_financiranje k
{where_sql}
ORDER BY k.ukupno_potpora DESC NULLS LAST, k.naziv
LIMIT %s
""", tuple(params) + (limit,))
return {"count": len(rows), "rows": rows, "filter": {"sport": sport, "davatelj": davatelj}}
@app.get("/api/v2/sportasi/filtered")
def sportasi_filtered(sport: str = None, klub_id: int = None, kategorija: str = None,
samo_priority: bool = False, samo_s_hns: bool = False,
godina_rod_od: int = None, godina_rod_do: int = None,
q: str = None, limit: int = 500):
"""Sportaši s range filter za godinu rođenja + kategorije."""
where = ["c.aktivan = true"]
params = []
if sport:
where.append("(c.sport = %s OR k.sport = %s)")
params.extend([sport, sport])
if klub_id:
where.append("c.klub_id = %s")
params.append(klub_id)
if kategorija:
where.append("(c.kategorija = %s OR EXISTS (SELECT 1 FROM pgz_sport.clan_kategorije ck WHERE ck.clan_id = c.id AND ck.kategorija = %s))")
params.extend([kategorija, kategorija])
if godina_rod_od:
where.append("(EXTRACT(YEAR FROM c.datum_rodenja) >= %s OR c.godina_rodenja >= %s)")
params.extend([godina_rod_od, godina_rod_od])
if godina_rod_do:
where.append("(EXTRACT(YEAR FROM c.datum_rodenja) <= %s OR c.godina_rodenja <= %s)")
params.extend([godina_rod_do, godina_rod_do])
if q:
where.append("(public.f_unaccent(c.ime) ILIKE public.f_unaccent(%s) OR public.f_unaccent(c.prezime) ILIKE public.f_unaccent(%s))")
params.extend([f"%{q}%", f"%{q}%"])
if samo_priority:
# Igrač iz kluba koji prima novac ili je u godišnjaku ili ima HNS roster
where.append("c.klub_id IN (SELECT id FROM pgz_sport.v_klubovi_financiranje WHERE prima_pgz OR prima_rss OR prima_grad_rijeka OR u_godisnjaku UNION SELECT klub_id FROM pgz_sport.hns_klub_roster)")
if samo_s_hns:
where.append("c.hns_igrac_id IS NOT NULL")
where_sql = "WHERE " + " AND ".join(where)
rows = fetch(f"""
SELECT c.id, c.ime, c.prezime, c.spol, c.datum_rodenja, c.godina_rodenja,
c.kategorija, c.pozicija, c.sport, c.klub_id, k.naziv AS klub_naziv,
c.hns_igrac_id, c.source, c.source_url, c.slika_url,
c.reprezentativac, c.kategoriziran, c.stipendiran, c.aktivan,
c.hoo_kategorija, c.broj_dresa
FROM pgz_sport.clanovi c
LEFT JOIN pgz_sport.klubovi k ON k.id = c.klub_id
{where_sql}
ORDER BY c.prezime, c.ime
LIMIT %s
""", tuple(params) + (limit,))
return {"count": len(rows), "rows": rows}
# ═══════════════════════════════════════════════════════════════════
# XLSX EXPORT — sportaši & klubovi-roster (SUB8)
# Author: Damir Radulić (damir@rinet.one / dradulic@outlook.com)
# Date: 2026-05-05 v1.0.0
# Description: openpyxl-based XLSX exports with HNS data,
# dark header (Palantir vibe) + auto-width columns.
# ═══════════════════════════════════════════════════════════════════
def _xlsx_style_header(ws, ncols):
"""Apply dark Palantir-style header styling to row 1."""
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
fill = PatternFill(start_color="1A1F2E", end_color="1A1F2E", fill_type="solid")
font = Font(bold=True, color="FFFFFF", name="Calibri", size=11)
align = Alignment(horizontal="left", vertical="center", wrap_text=False)
side = Side(style="thin", color="3A4356")
border = Border(left=side, right=side, top=side, bottom=side)
for col_idx in range(1, ncols + 1):
cell = ws.cell(row=1, column=col_idx)
cell.fill = fill
cell.font = font
cell.alignment = align
cell.border = border
ws.row_dimensions[1].height = 22
ws.freeze_panes = "A2"
def _xlsx_autosize(ws):
"""Approximate auto-width based on cell content length."""
from openpyxl.utils import get_column_letter
for col_idx in range(1, ws.max_column + 1):
max_len = 8
letter = get_column_letter(col_idx)
for row_idx in range(1, min(ws.max_row, 500) + 1):
v = ws.cell(row=row_idx, column=col_idx).value
if v is None:
continue
try:
ln = len(str(v))
except Exception:
ln = 8
if ln > max_len:
max_len = ln
ws.column_dimensions[letter].width = min(max_len + 2, 60)
_SPORTASI_COLS = [
("ime", "Ime"),
("prezime", "Prezime"),
("klub_naziv", "Klub"),
("datum_rodenja", "Datum rođenja"),
("visina_cm", "Visina (cm)"),
("tezina_kg", "Težina (kg)"),
("dominantna_noga", "Dominantna noga"),
("pozicija", "Pozicija"),
("hns_url", "HNS URL"),
("broj_sezona", "Broj sezona"),
("broj_utakmica", "Broj utakmica"),
]
@app.get("/api/v2/export/sportasi")
def export_sportasi_xlsx(klub_id: Optional[int] = None, sport: Optional[str] = None, limit: int = 10000):
"""XLSX export svih sportaša + HNS data.
Filter: klub_id (opcija), sport (opcija). Default vrača sve sportaše."""
import io
from openpyxl import Workbook
from fastapi.responses import StreamingResponse
where = ["1=1"]
params: list = []
if klub_id:
where.append("c.klub_id = %s"); params.append(klub_id)
if sport:
where.append("(c.sport = %s OR k.sport = %s)"); params.extend([sport, sport])
sql = f"""
SELECT c.id, c.ime, c.prezime,
COALESCE(k.naziv, '') AS klub_naziv,
c.datum_rodenja, c.visina_cm, c.tezina_kg,
c.dominantna_noga, c.pozicija,
COALESCE(c.source_url, '') AS hns_url,
(SELECT count(*) FROM pgz_sport.hns_player_seasons s WHERE s.clan_id = c.id) AS broj_sezona,
(SELECT count(*) FROM pgz_sport.hns_player_matches m WHERE m.clan_id = c.id) AS broj_utakmica
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
"""
rows = fetch(sql, tuple(params) + (limit,))
wb = Workbook()
ws = wb.active
ws.title = "Sportaši"
headers_hr = [lbl for _key, lbl in _SPORTASI_COLS]
ws.append(headers_hr)
for r in rows:
ws.append([r.get(key) for key, _ in _SPORTASI_COLS])
_xlsx_style_header(ws, len(headers_hr))
_xlsx_autosize(ws)
buf = io.BytesIO()
wb.save(buf)
buf.seek(0)
fname = f"pgz_sportasi_{datetime.now().strftime('%Y%m%d')}.xlsx"
return StreamingResponse(
buf,
media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
headers={
"Content-Disposition": f'attachment; filename="{fname}"',
"X-Row-Count": str(len(rows)),
},
)
@app.get("/api/v2/export/klubovi-roster")
def export_klubovi_roster_xlsx(klub_id: Optional[int] = None, sport: Optional[str] = None, limit_per_klub: int = 5000):
"""XLSX export rostera po klubu.
- Bez klub_id: jedan workbook s više sheet-ova (jedan po klubu).
- S klub_id: jedan sheet s rosterom kluba.
"""
import io
import re
from openpyxl import Workbook
from fastapi.responses import StreamingResponse
if klub_id:
klubovi_rows = fetch(
"SELECT id, naziv, sport FROM pgz_sport.klubovi WHERE id = %s",
(klub_id,),
)
else:
sport_where = ""
sport_params: tuple = ()
if sport:
sport_where = "AND k.sport = %s"
sport_params = (sport,)
klubovi_rows = fetch(
f"""
SELECT k.id, k.naziv, k.sport
FROM pgz_sport.klubovi k
WHERE EXISTS (SELECT 1 FROM pgz_sport.clanovi c WHERE c.klub_id = k.id)
{sport_where}
ORDER BY k.naziv
""",
sport_params,
)
wb = Workbook()
default_ws = wb.active
wb.remove(default_ws)
headers_hr = [lbl for _key, lbl in _SPORTASI_COLS]
used_titles: set = set()
total_rows = 0
def _safe_sheet_title(naziv: str, kid: int) -> str:
# Excel: max 31 chars, no : \ / ? * [ ]
t = re.sub(r"[:\\/\?\*\[\]]", " ", naziv or f"Klub_{kid}")
t = t.strip()[:28] or f"Klub_{kid}"
base = t
i = 2
while t in used_titles:
suf = f"_{i}"
t = (base[: 31 - len(suf)] + suf)
i += 1
used_titles.add(t)
return t
if not klubovi_rows:
ws = wb.create_sheet(title="Prazno")
ws.append(headers_hr)
_xlsx_style_header(ws, len(headers_hr))
else:
for kr in klubovi_rows:
kid = kr["id"]
kn = kr.get("naziv") or f"Klub {kid}"
title = _safe_sheet_title(kn, kid)
ws = wb.create_sheet(title=title)
ws.append(headers_hr)
roster = fetch(
"""
SELECT c.id, c.ime, c.prezime,
%s::text AS klub_naziv,
c.datum_rodenja, c.visina_cm, c.tezina_kg,
c.dominantna_noga, c.pozicija,
COALESCE(c.source_url, '') AS hns_url,
(SELECT count(*) FROM pgz_sport.hns_player_seasons s WHERE s.clan_id = c.id) AS broj_sezona,
(SELECT count(*) FROM pgz_sport.hns_player_matches m WHERE m.clan_id = c.id) AS broj_utakmica
FROM pgz_sport.clanovi c
WHERE c.klub_id = %s
ORDER BY c.prezime, c.ime
LIMIT %s
""",
(kn, kid, limit_per_klub),
)
for r in roster:
ws.append([r.get(key) for key, _ in _SPORTASI_COLS])
total_rows += len(roster)
_xlsx_style_header(ws, len(headers_hr))
_xlsx_autosize(ws)
buf = io.BytesIO()
wb.save(buf)
buf.seek(0)
suffix = f"klub{klub_id}" if klub_id else "svi"
fname = f"pgz_klubovi_roster_{suffix}_{datetime.now().strftime('%Y%m%d')}.xlsx"
return StreamingResponse(
buf,
media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
headers={
"Content-Disposition": f'attachment; filename="{fname}"',
"X-Klubovi-Count": str(len(klubovi_rows)),
"X-Row-Count": str(total_rows),
},
)
@app.get("/api/v2/savez/{savez_id}/kpi")
def savez_kpi(savez_id: int, godina: int = None):
"""KPI metrike za savez: ukupna potpora, broj sportaša, najviši rang."""
rows = fetch("""
SELECT
(SELECT COALESCE(SUM(pn.iznos), 0)::numeric(12,2) FROM pgz_sport.potpore_nositelji pn
JOIN pgz_sport.klubovi k ON k.id = pn.klub_id
WHERE k.savez_id = %s) AS ukupna_potpora,
(SELECT count(DISTINCT c.id) FROM pgz_sport.clanovi c
JOIN pgz_sport.klubovi k ON k.id = c.klub_id
WHERE k.savez_id = %s AND c.aktivan = true) AS broj_sportasa,
(SELECT count(DISTINCT k.id) FROM pgz_sport.klubovi k WHERE k.savez_id = %s AND k.aktivan = true) AS broj_klubova,
(SELECT COALESCE(MIN(k.razina), 'n/a') FROM pgz_sport.klubovi k
WHERE k.savez_id = %s AND k.razina IS NOT NULL AND k.razina <> '') AS najvisi_rang,
(SELECT count(*) FROM pgz_sport.dokumenti d WHERE d.savez_id = %s) AS broj_dokumenata,
(SELECT count(*) FROM pgz_sport.manifestacije m WHERE m.savez_id = %s) AS broj_manifestacija
""", (savez_id, savez_id, savez_id, savez_id, savez_id, savez_id))
return rows[0] if rows else {}
@app.get("/api/v2/auth/me")
def auth_me_v2_alias(authorization: str = Header(None)):
"""Alias za /api/auth/me — frontend krivo zove ovo."""
from fastapi import HTTPException
if not authorization or not authorization.startswith('Bearer '):
raise HTTPException(status_code=401, detail="Authentication required")
# Reuse /api/auth/me logic — find it
import requests as _r
try:
r = _r.get('http://127.0.0.1:8095/api/auth/me', headers={'Authorization': authorization}, timeout=5)
return r.json()
except Exception as e:
raise HTTPException(status_code=500, detail=str(e))
@app.get("/api/v2/sportski-objekti")
def sportski_objekti_v2_list(tip: str = None, grad: str = None, sport: str = None, q: str = None, limit: int = 500):
"""Sportski objekti PGŽ s filterima."""
where = ["aktivan = true"]
params = []
if tip:
where.append("tip = %s"); params.append(tip)
if grad:
where.append("grad = %s"); params.append(grad)
if sport:
where.append("%s = ANY(sportovi)"); params.append(sport)
if q:
where.append("(public.f_unaccent(naziv) ILIKE public.f_unaccent(%s) OR public.f_unaccent(adresa) ILIKE public.f_unaccent(%s) OR upravitelj ILIKE %s)")
params.extend([f"%{q}%"]*3)
rows = fetch(f"""
SELECT id, naziv, tip, grad, adresa, lat, lng, upravitelj, kapacitet,
sportovi, izgradeno, obnovljeno_god, "veličina" AS velicina, natkrita,
napomena, web
FROM pgz_sport.sportski_objekti
WHERE {' AND '.join(where)}
ORDER BY grad, naziv
LIMIT %s
""", tuple(params) + (limit,))
return {"count": len(rows), "rows": rows}
@app.get("/api/v2/sportski-objekti/meta")
def sportski_objekti_meta():
"""Dropdown options za filter."""
tipovi = fetch("SELECT tip, count(*) AS broj FROM pgz_sport.sportski_objekti WHERE aktivan = true AND tip IS NOT NULL GROUP BY tip ORDER BY broj DESC")
gradovi = fetch("SELECT grad, count(*) AS broj FROM pgz_sport.sportski_objekti WHERE aktivan = true AND grad IS NOT NULL GROUP BY grad ORDER BY broj DESC")
sportovi = fetch("SELECT DISTINCT unnest(sportovi) AS sport, count(*) AS broj FROM pgz_sport.sportski_objekti WHERE aktivan = true AND sportovi IS NOT NULL GROUP BY sport ORDER BY broj DESC LIMIT 50")
return {
"tipovi": tipovi,
"gradovi": gradovi,
"sportovi": sportovi,
"ukupno": (fetch("SELECT count(*) AS n FROM pgz_sport.sportski_objekti WHERE aktivan = true")[0])["n"]
}
@app.get("/objekti")
@app.get("/objekti/")
@app.get("/sport/objekti")
@app.get("/sport/objekti/")
def serve_objekti():
from fastapi.responses import FileResponse
return FileResponse("/opt/pgz-sport/static/objekti.html")
@app.get("/")
def root(request: Request):
host = request.headers.get("host", "")
if "sport.rinet.one" in host:
p = HTML_DIR / "sport2.html"
if p.exists():
return FileResponse(p)
idx = HTML_DIR / "index.html"
if idx.exists():
return FileResponse(idx)
return {"service": "PGŽ Sport", "version": "2.0"}
@app.get("/v2")
def portal_v2():
p = HTML_DIR / "sport2.html"
if p.exists():
return FileResponse(p)
return {"error": "sport2.html not found"}
if __name__ == "__main__":
import uvicorn
uvicorn.run(app, host="0.0.0.0", port=8095)
# === AI STUB (privremeni) ===
try:
from routers.ai_stub_router import router as ai_stub_router
app.include_router(ai_stub_router, prefix='/api/v2')
print('[AI-STUB] router loaded (/api/v2/ai/ask)')
except Exception as e:
print(f'[AI-STUB] router fail: {e}')
Reference in New Issue View Git Blame Copy Permalink