492 lines
18 KiB
Python
492 lines
18 KiB
Python
"""REST API for the psppt server (JSON, token-authenticated).
|
|
|
|
Endpoints:
|
|
POST /api/auth/login -> {token, username, role}
|
|
GET /api/auth/me -> current user
|
|
POST /api/auth/logout -> revoke current token
|
|
GET /api/users -> list (admin)
|
|
POST /api/users -> create (admin)
|
|
PATCH /api/users/{id} -> update role/password/active (admin)
|
|
DELETE /api/users/{id} -> delete (admin)
|
|
GET /api/users/{id}/tokens -> list API tokens (admin or self)
|
|
DELETE /api/users/{id}/tokens/{tid} -> revoke a token (admin or self)
|
|
GET /api/releases?system=&q=&limit= -> catalog list
|
|
GET /api/releases/{id} -> detail + metadata + roms
|
|
PATCH /api/releases/{id} -> edit metadata (editor+)
|
|
GET /api/cover/{id} -> cover image
|
|
POST /api/cover/{id} -> upload/replace cover (editor+)
|
|
DELETE /api/cover/{id} -> delete cover (editor+)
|
|
GET /api/store?system=games|videos -> archive list
|
|
GET /api/store/download/{system}/{name} -> stream archive
|
|
POST /api/store/upload?system= -> upload archive (editor+)
|
|
DELETE /api/store/{system}/{name} -> delete archive (editor+)
|
|
GET /api/stats -> dashboard numbers (admin)
|
|
"""
|
|
import json
|
|
import re
|
|
from pathlib import Path
|
|
|
|
from fastapi import APIRouter, Depends, File, Header, HTTPException, Query, Request, UploadFile
|
|
from fastapi.responses import FileResponse, JSONResponse
|
|
|
|
from app import covers, store
|
|
from app.db import get_connection
|
|
|
|
from . import config
|
|
from .security import now_iso, require_role, revoke_token, get_current_user, hash_password, verify_password, issue_token
|
|
|
|
router = APIRouter(prefix="/api")
|
|
|
|
_META_FIELDS = ("release_date", "developer", "publisher", "genre", "synopsis", "title")
|
|
_SAFE_NAME_RE = re.compile(r"[^A-Za-z0-9._()\- ]")
|
|
|
|
|
|
# --------------------------------------------------------------------------- #
|
|
# helpers
|
|
# --------------------------------------------------------------------------- #
|
|
def _conn():
|
|
return get_connection()
|
|
|
|
|
|
def _release_system(conn, release_id):
|
|
row = conn.execute(
|
|
"SELECT d.system FROM releases r JOIN dats d ON d.id = r.dat_id WHERE r.id = ?",
|
|
(release_id,),
|
|
).fetchone()
|
|
return row["system"] if row else None
|
|
|
|
|
|
def _metadata(conn, release_id, system):
|
|
row = conn.execute(
|
|
"SELECT title, meta_json, manual FROM item_info WHERE release_id = ? AND system = ?",
|
|
(str(release_id), system),
|
|
).fetchone()
|
|
meta = {}
|
|
if row and row["meta_json"]:
|
|
try:
|
|
meta = json.loads(row["meta_json"])
|
|
except (TypeError, ValueError):
|
|
meta = {}
|
|
if row:
|
|
meta["title"] = row["title"]
|
|
meta["manual"] = bool(row["manual"])
|
|
return meta
|
|
|
|
|
|
def _save_metadata(conn, release_id, system, fields):
|
|
key = str(release_id)
|
|
row = conn.execute(
|
|
"SELECT meta_json FROM item_info WHERE release_id = ? AND system = ?", (key, system)
|
|
).fetchone()
|
|
meta = {}
|
|
if row and row["meta_json"]:
|
|
try:
|
|
meta = json.loads(row["meta_json"])
|
|
except (TypeError, ValueError):
|
|
meta = {}
|
|
meta.update(fields)
|
|
title = fields.get("title") or meta.get("title") or ""
|
|
conn.execute(
|
|
"INSERT INTO item_info (release_id, system, title, meta_json, manual, updated_at) "
|
|
"VALUES (?, ?, ?, ?, 1, ?) "
|
|
"ON CONFLICT(release_id, system) DO UPDATE SET "
|
|
"title = excluded.title, meta_json = excluded.meta_json, manual = 1, updated_at = excluded.updated_at",
|
|
(key, system, title, json.dumps(meta, ensure_ascii=False), now_iso()),
|
|
)
|
|
|
|
|
|
def _cover_paths(release_id, system):
|
|
base = config.COVERS_DIR / system / str(release_id)
|
|
return base, [base.with_suffix(ext) for ext in (".png", ".jpg", ".jpeg", ".webp")]
|
|
|
|
|
|
def _find_cover(release_id, system):
|
|
base, candidates = _cover_paths(release_id, system)
|
|
for p in candidates:
|
|
if p.exists():
|
|
return p
|
|
return base.with_suffix(".png")
|
|
|
|
|
|
def _save_cover(release_id, system, data):
|
|
base, candidates = _cover_paths(release_id, system)
|
|
base.parent.mkdir(parents=True, exist_ok=True)
|
|
ext = ".png"
|
|
if data[:3] == b"\xff\xd8\xff":
|
|
ext = ".jpg"
|
|
elif data[:4] == b"RIFF" and data[8:12] == b"WEBP":
|
|
ext = ".webp"
|
|
dest = base.with_suffix(ext)
|
|
for p in candidates:
|
|
if p.exists() and p != dest:
|
|
p.unlink()
|
|
tmp = dest.with_name(dest.name + ".tmp")
|
|
tmp.write_bytes(data)
|
|
tmp.replace(dest)
|
|
return dest
|
|
|
|
|
|
def _store_root(system):
|
|
return config.STORE_VIDEOS_ROOT if system == "videos" else config.STORE_ROOT
|
|
|
|
|
|
def _log_download(user, system, name, size):
|
|
conn = _conn()
|
|
try:
|
|
conn.execute(
|
|
"INSERT INTO download_events (user_id, system, name, bytes, created_at) VALUES (?, ?, ?, ?, ?)",
|
|
(user["id"] if user else None, system, name, size or 0, now_iso()),
|
|
)
|
|
conn.commit()
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
# --------------------------------------------------------------------------- #
|
|
# auth
|
|
# --------------------------------------------------------------------------- #
|
|
@router.post("/auth/login")
|
|
def auth_login(payload: dict):
|
|
username = (payload.get("username") or "").strip()
|
|
password = payload.get("password") or ""
|
|
conn = _conn()
|
|
try:
|
|
row = conn.execute(
|
|
"SELECT id, username, password_hash, role, is_active FROM users WHERE username = ?",
|
|
(username,),
|
|
).fetchone()
|
|
finally:
|
|
conn.close()
|
|
if row is None or not row["is_active"] or not verify_password(password, row["password_hash"]):
|
|
raise HTTPException(status_code=401, detail="Invalid username or password")
|
|
token = issue_token(row["id"], kind="api", label="desktop")
|
|
return {"token": token, "username": row["username"], "role": row["role"], "user_id": row["id"]}
|
|
|
|
|
|
@router.get("/auth/me")
|
|
def auth_me(user=Depends(get_current_user)):
|
|
return user
|
|
|
|
|
|
@router.post("/auth/logout")
|
|
def auth_logout(request: Request, authorization: str = Header(default="")):
|
|
token = ""
|
|
if authorization.startswith("Bearer "):
|
|
token = authorization[7:].strip()
|
|
else:
|
|
token = request.cookies.get("psppt_session", "")
|
|
if token:
|
|
revoke_token(token)
|
|
return {"ok": True}
|
|
|
|
|
|
# --------------------------------------------------------------------------- #
|
|
# users (admin)
|
|
# --------------------------------------------------------------------------- #
|
|
@router.get("/users")
|
|
def users_list(user=Depends(require_role("admin"))):
|
|
conn = _conn()
|
|
try:
|
|
rows = conn.execute(
|
|
"SELECT id, username, role, is_active, created_at, last_login_at FROM users ORDER BY username"
|
|
).fetchall()
|
|
return {"items": [dict(r) for r in rows]}
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
@router.post("/users")
|
|
def users_create(payload: dict, user=Depends(require_role("admin"))):
|
|
username = (payload.get("username") or "").strip()
|
|
password = payload.get("password") or ""
|
|
role = payload.get("role") or "user"
|
|
if not username or not password:
|
|
raise HTTPException(status_code=400, detail="username and password are required")
|
|
if role not in ("admin", "editor", "user"):
|
|
raise HTTPException(status_code=400, detail="invalid role")
|
|
conn = _conn()
|
|
try:
|
|
try:
|
|
conn.execute(
|
|
"INSERT INTO users (username, password_hash, role, is_active, created_at) VALUES (?, ?, ?, 1, ?)",
|
|
(username, hash_password(password), role, now_iso()),
|
|
)
|
|
conn.commit()
|
|
except Exception:
|
|
raise HTTPException(status_code=409, detail="username already exists")
|
|
row = conn.execute("SELECT id, username, role, is_active FROM users WHERE username = ?", (username,)).fetchone()
|
|
return dict(row)
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
@router.patch("/users/{user_id}")
|
|
def users_update(user_id: int, payload: dict, user=Depends(require_role("admin"))):
|
|
conn = _conn()
|
|
try:
|
|
row = conn.execute("SELECT id FROM users WHERE id = ?", (user_id,)).fetchone()
|
|
if row is None:
|
|
raise HTTPException(status_code=404, detail="not found")
|
|
if "role" in payload:
|
|
if payload["role"] not in ("admin", "editor", "user"):
|
|
raise HTTPException(status_code=400, detail="invalid role")
|
|
conn.execute("UPDATE users SET role = ? WHERE id = ?", (payload["role"], user_id))
|
|
if "is_active" in payload:
|
|
conn.execute("UPDATE users SET is_active = ? WHERE id = ?", (1 if payload["is_active"] else 0, user_id))
|
|
if payload.get("password"):
|
|
conn.execute("UPDATE users SET password_hash = ? WHERE id = ?", (hash_password(payload["password"]), user_id))
|
|
conn.commit()
|
|
row = conn.execute("SELECT id, username, role, is_active FROM users WHERE id = ?", (user_id,)).fetchone()
|
|
return dict(row)
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
@router.delete("/users/{user_id}")
|
|
def users_delete(user_id: int, user=Depends(require_role("admin"))):
|
|
if user_id == user["id"]:
|
|
raise HTTPException(status_code=400, detail="cannot delete yourself")
|
|
conn = _conn()
|
|
try:
|
|
conn.execute("DELETE FROM users WHERE id = ?", (user_id,))
|
|
conn.commit()
|
|
finally:
|
|
conn.close()
|
|
return {"ok": True}
|
|
|
|
|
|
@router.get("/users/{user_id}/tokens")
|
|
def users_tokens(user_id: int, user=Depends(require_role("admin"))):
|
|
conn = _conn()
|
|
try:
|
|
rows = conn.execute(
|
|
"SELECT id, label, kind, created_at, last_used_at, expires_at, revoked_at FROM api_tokens "
|
|
"WHERE user_id = ? ORDER BY id DESC",
|
|
(user_id,),
|
|
).fetchall()
|
|
return {"items": [dict(r) for r in rows]}
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
@router.delete("/users/{user_id}/tokens/{token_id}")
|
|
def users_revoke_token(user_id: int, token_id: int, user=Depends(require_role("admin"))):
|
|
conn = _conn()
|
|
try:
|
|
conn.execute(
|
|
"UPDATE api_tokens SET revoked_at = ? WHERE id = ? AND user_id = ?",
|
|
(now_iso(), token_id, user_id),
|
|
)
|
|
conn.commit()
|
|
finally:
|
|
conn.close()
|
|
return {"ok": True}
|
|
|
|
|
|
# --------------------------------------------------------------------------- #
|
|
# releases / catalog
|
|
# --------------------------------------------------------------------------- #
|
|
@router.get("/releases")
|
|
def releases_list(system: str = Query(default=""), q: str = Query(default=""), limit: int = 50, offset: int = 0):
|
|
limit = max(1, min(limit, 500))
|
|
offset = max(0, offset)
|
|
sql = "SELECT r.id, d.system, r.name, r.region, r.serial FROM releases r JOIN dats d ON d.id = r.dat_id"
|
|
where, params = [], []
|
|
if system:
|
|
where.append("d.system = ?")
|
|
params.append(system)
|
|
if q:
|
|
where.append("r.name LIKE ?")
|
|
params.append(f"%{q}%")
|
|
if where:
|
|
sql += " WHERE " + " AND ".join(where)
|
|
sql += " ORDER BY r.name LIMIT ? OFFSET ?"
|
|
params += [limit, offset]
|
|
conn = _conn()
|
|
try:
|
|
rows = conn.execute(sql, params).fetchall()
|
|
items = []
|
|
for r in rows:
|
|
d = dict(r)
|
|
d["cover"] = f"/api/cover/{d['id']}"
|
|
items.append(d)
|
|
return {"items": items, "limit": limit, "offset": offset}
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
@router.get("/releases/{release_id}")
|
|
def releases_detail(release_id: int):
|
|
conn = _conn()
|
|
try:
|
|
rel = conn.execute(
|
|
"SELECT r.id, d.system, r.name, r.description, r.region, r.serial "
|
|
"FROM releases r JOIN dats d ON d.id = r.dat_id WHERE r.id = ?",
|
|
(release_id,),
|
|
).fetchone()
|
|
if rel is None:
|
|
raise HTTPException(status_code=404, detail="not found")
|
|
roms = conn.execute(
|
|
"SELECT name, size, crc32, md5, sha1, serial FROM roms WHERE release_id = ?", (release_id,)
|
|
).fetchall()
|
|
result = dict(rel)
|
|
result["cover"] = f"/api/cover/{release_id}"
|
|
result["roms"] = [dict(r) for r in roms]
|
|
result["meta"] = _metadata(conn, release_id, rel["system"])
|
|
return result
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
@router.patch("/releases/{release_id}")
|
|
def releases_update(release_id: int, payload: dict, user=Depends(require_role("admin", "editor"))):
|
|
conn = _conn()
|
|
try:
|
|
rel = conn.execute(
|
|
"SELECT r.id, d.system FROM releases r JOIN dats d ON d.id = r.dat_id WHERE r.id = ?",
|
|
(release_id,),
|
|
).fetchone()
|
|
if rel is None:
|
|
raise HTTPException(status_code=404, detail="not found")
|
|
# release-table columns
|
|
for col in ("name", "region", "serial", "description"):
|
|
if col in payload:
|
|
conn.execute(f"UPDATE releases SET {col} = ? WHERE id = ?", (payload[col], release_id))
|
|
# metadata (item_info)
|
|
meta_fields = {k: payload[k] for k in _META_FIELDS if k in payload}
|
|
if meta_fields:
|
|
_save_metadata(conn, release_id, rel["system"], meta_fields)
|
|
conn.commit()
|
|
return {"ok": True}
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
# --------------------------------------------------------------------------- #
|
|
# covers
|
|
# --------------------------------------------------------------------------- #
|
|
@router.get("/cover/{release_id}")
|
|
def cover_get(release_id: int):
|
|
conn = _conn()
|
|
try:
|
|
system = _release_system(conn, release_id)
|
|
finally:
|
|
conn.close()
|
|
if system is None:
|
|
raise HTTPException(status_code=404, detail="not found")
|
|
path = _find_cover(release_id, system)
|
|
if not path.exists():
|
|
raise HTTPException(status_code=404, detail="no cover available")
|
|
media = "image/jpeg" if path.suffix.lower() in (".jpg", ".jpeg") else "image/png"
|
|
if path.suffix.lower() == ".webp":
|
|
media = "image/webp"
|
|
return FileResponse(path, media_type=media)
|
|
|
|
|
|
@router.post("/cover/{release_id}")
|
|
async def cover_upload(release_id: int, file: UploadFile = File(...), user=Depends(require_role("admin", "editor"))):
|
|
conn = _conn()
|
|
try:
|
|
system = _release_system(conn, release_id)
|
|
finally:
|
|
conn.close()
|
|
if system is None:
|
|
raise HTTPException(status_code=404, detail="not found")
|
|
data = await file.read()
|
|
if len(data) > 20 * (1 << 20):
|
|
raise HTTPException(status_code=400, detail="image too large")
|
|
_save_cover(release_id, system, data)
|
|
return {"ok": True, "url": f"/api/cover/{release_id}"}
|
|
|
|
|
|
@router.delete("/cover/{release_id}")
|
|
def cover_delete(release_id: int, user=Depends(require_role("admin", "editor"))):
|
|
conn = _conn()
|
|
try:
|
|
system = _release_system(conn, release_id)
|
|
finally:
|
|
conn.close()
|
|
if system is None:
|
|
raise HTTPException(status_code=404, detail="not found")
|
|
path = _find_cover(release_id, system)
|
|
if path.exists():
|
|
path.unlink()
|
|
return {"ok": True}
|
|
|
|
|
|
# --------------------------------------------------------------------------- #
|
|
# store
|
|
# --------------------------------------------------------------------------- #
|
|
@router.get("/store")
|
|
def store_list(system: str = Query(default="games")):
|
|
root = _store_root(system)
|
|
if not root.is_dir():
|
|
return {"items": [], "count": 0, "system": system}
|
|
items = [
|
|
{"name": it["name"], "filename": Path(it["path"]).name, "size": it["size"], "mtime_ns": it["mtime_ns"], "system": system}
|
|
for it in store.scan(root)
|
|
]
|
|
return {"items": items, "count": len(items), "system": system}
|
|
|
|
|
|
@router.get("/store/download/{system}/{name}")
|
|
def store_download(system: str, name: str, user=Depends(get_current_user)):
|
|
root = _store_root(system).resolve()
|
|
target = (root / name).resolve()
|
|
if not target.is_file() or root not in target.parents:
|
|
raise HTTPException(status_code=404, detail="not found")
|
|
_log_download(user, system, name, target.stat().st_size)
|
|
return FileResponse(target, filename=name, media_type="application/octet-stream")
|
|
|
|
|
|
@router.post("/store/upload")
|
|
async def store_upload(system: str = Query(default="games"), file: UploadFile = File(...), user=Depends(require_role("admin", "editor"))):
|
|
root = _store_root(system)
|
|
root.mkdir(parents=True, exist_ok=True)
|
|
safe = _SAFE_NAME_RE.sub("", file.filename or "")
|
|
if not safe:
|
|
raise HTTPException(status_code=400, detail="invalid filename")
|
|
dest = root / safe
|
|
data = await file.read()
|
|
dest.write_bytes(data)
|
|
return {"ok": True, "filename": safe, "size": len(data)}
|
|
|
|
|
|
@router.delete("/store/{system}/{name}")
|
|
def store_delete(system: str, name: str, user=Depends(require_role("admin", "editor"))):
|
|
root = _store_root(system).resolve()
|
|
target = (root / name).resolve()
|
|
if not target.is_file() or root not in target.parents:
|
|
raise HTTPException(status_code=404, detail="not found")
|
|
target.unlink()
|
|
return {"ok": True}
|
|
|
|
|
|
# --------------------------------------------------------------------------- #
|
|
# stats (admin)
|
|
# --------------------------------------------------------------------------- #
|
|
@router.get("/stats")
|
|
def stats(user=Depends(require_role("admin"))):
|
|
conn = _conn()
|
|
try:
|
|
systems = conn.execute(
|
|
"SELECT d.system AS system, COUNT(*) AS releases FROM releases r "
|
|
"JOIN dats d ON d.id = r.dat_id GROUP BY d.system ORDER BY d.system"
|
|
).fetchall()
|
|
users = conn.execute("SELECT COUNT(*) AS n FROM users").fetchone()["n"]
|
|
covers_count = sum(1 for _ in config.COVERS_DIR.rglob("*") if _.is_file()) if config.COVERS_DIR.is_dir() else 0
|
|
downloads = conn.execute("SELECT COUNT(*) AS n FROM download_events").fetchone()["n"]
|
|
recent = conn.execute(
|
|
"SELECT e.system, e.name, e.bytes, e.created_at, u.username FROM download_events e "
|
|
"LEFT JOIN users u ON u.id = e.user_id ORDER BY e.id DESC LIMIT 20"
|
|
).fetchall()
|
|
return {
|
|
"systems": [dict(r) for r in systems],
|
|
"users": users,
|
|
"covers": covers_count,
|
|
"downloads": downloads,
|
|
"recent": [dict(r) for r in recent],
|
|
}
|
|
finally:
|
|
conn.close()
|