Damir Radulic
61 lines
2.6 KiB
Plaintext
61 lines
2.6 KiB
Plaintext
"""Audit coverage matrix endpoint - colored heat-map data per klub."""
|
||
import os
|
||
from fastapi import APIRouter
|
||
import psycopg2
|
||
|
||
DB = dict(host='localhost', port=5432, dbname='rinet_v3',
|
||
user='rinet', password=os.environ["DB_PASSWORD"])
|
||
|
||
router = APIRouter()
|
||
|
||
@router.get("/audit/coverage-matrix")
|
||
def coverage_matrix(min_size: int = 5, sport: str = None):
|
||
"""Vrati matricu pokrivenosti po klubu × tipu podatka."""
|
||
conn = psycopg2.connect(**DB); conn.autocommit = True
|
||
cu = conn.cursor()
|
||
|
||
where = "WHERE k.aktivan AND k.region IN ('PGŽ', 'Rijeka', 'Primorje', 'Otoci')"
|
||
if sport:
|
||
where += f" AND k.sport ILIKE '%{sport}%'"
|
||
|
||
cu.execute(f"""
|
||
SELECT
|
||
k.id, k.naziv, k.sport, k.grad,
|
||
(SELECT count(*) FROM pgz_sport.clanovi c WHERE c.klub_id=k.id) AS sportasa,
|
||
(SELECT count(*) FROM pgz_sport.clanovi c WHERE c.klub_id=k.id AND c.verified) AS verified,
|
||
(SELECT count(*) FROM pgz_sport.utakmice_log u WHERE u.za_klub_id=k.id) AS utakmica,
|
||
(SELECT count(*) FROM pgz_sport.clan_sezona cs JOIN pgz_sport.clanovi c2 ON c2.id=cs.clan_id WHERE c2.klub_id=k.id) AS sezona,
|
||
(SELECT count(*) FROM pgz_sport.klub_sezona ks WHERE ks.klub_id=k.id) AS trofeja,
|
||
(SELECT count(*) FROM pgz_sport.clan_nagrada cn JOIN pgz_sport.clanovi c3 ON c3.id=cn.clan_id WHERE c3.klub_id=k.id) AS nagrada,
|
||
CASE WHEN k.godisnjak_godine IS NOT NULL THEN array_length(k.godisnjak_godine, 1) ELSE 0 END AS god_hits
|
||
FROM pgz_sport.klubovi k
|
||
{where}
|
||
ORDER BY (SELECT count(*) FROM pgz_sport.clanovi c WHERE c.klub_id=k.id) DESC
|
||
LIMIT 200
|
||
""")
|
||
rows = []
|
||
for r in cu.fetchall():
|
||
sportasa = r[4] or 0
|
||
if sportasa < min_size: continue
|
||
verified = r[5] or 0
|
||
rows.append({
|
||
"klub_id": r[0], "naziv": r[1], "sport": r[2], "grad": r[3],
|
||
"sportasa": sportasa, "verified": verified,
|
||
"verified_pct": round(verified*100.0/sportasa) if sportasa else 0,
|
||
"utakmica": r[6] or 0,
|
||
"sezona": r[7] or 0,
|
||
"trofeja": r[8] or 0,
|
||
"nagrada": r[9] or 0,
|
||
"god_hits": r[10] or 0,
|
||
"score": min(100, (
|
||
(verified*100/sportasa if sportasa else 0)*0.4 +
|
||
(min(r[6] or 0, 50) * 2)*0.2 +
|
||
(min(r[7] or 0, 30) * 3.3)*0.15 +
|
||
(min(r[8] or 0, 20) * 5)*0.15 +
|
||
(min(r[10] or 0, 10) * 10)*0.10
|
||
))
|
||
})
|
||
|
||
conn.close()
|
||
return {"klubovi": rows, "count": len(rows), "filter_min_size": min_size, "filter_sport": sport}
|