rene
de1677154f
Registrierung & Login: - E-Mail-Verifikation jetzt Pflicht vor erstem Login - Register gibt keinen Token mehr zurück → "Postfach prüfen"-Screen - Login blockt mit EMAIL_NOT_VERIFIED (403) wenn unverifiziert - Resend-Verification ohne Auth (email-basiert) - Frontend: _renderVerifyPending() nach Register und Login-Fehler - Account-Lockout: 5 Fehlversuche → 15 Min gesperrt (ratelimit.py) - Login Rate-Limit zusätzlich per E-Mail-Adresse (5/5 Min) - Fehler-Tracking wird bei erfolgreichem Login zurückgesetzt E-Mail-Templates (alle Mails jetzt HTML): - email_html() Shared-Template in mailer.py (Gradient-Header, Warm-Beige) - Verifikations-Mail, Passwort-Reset → HTML mit CTA-Button - Admin-Outreach: plain text auto-wrapped in HTML - Züchter-Mails (Antrag/Genehmigung/Ablehnung) → Template - Tierschutz-Alert (litters.py) → Template - send_support_mail → HTML - outreach._build_message() + _send_smtp() unterstützen jetzt html= Parameter Forum-Schutz: - Post-Cooldown: 30 Sek zwischen beliebigen Posts (DB-Check) - Stunden-Limit: 5 Threads / 20 Antworten pro User/Stunde - Duplikat-Erkennung: gleicher Text in 5 Min blockiert (in-memory) - content_filter.py: Spam-Keywords, URL-Sperre für Accounts < 7 Tage, Sonderzeichen-Ratio-Check Security-Headers: - HSTS: max-age=31536000; includeSubDomains - Content-Security-Policy: frame-ancestors none, base-uri self, … - X-Frame-Options entfernt (CSP frame-ancestors ist moderner) Honeypot-Fallen (13 Scanner-Pfade → 24h IP-Sperre): - /api/admin/users, /api/v1/users, /api/.env, /api/config, /api/setup, /api/install, /api/phpinfo, /api/debug, /api/actuator, /api/swagger, /api/graphql u.a. Quartalsbericht-System: - backend/scripts/generate_reports.py: 6 Sections (Sicherheit, Funktionsumfang, Dateien, Nutzer, Partner, Server) - make reports: generiert alle Berichte aus dem Container, committed - Scheduler: quarterly_report Job (1. Feb/Mai/Aug/Nov 07:00) → vollständige HTML-Mail an ADMIN_EMAIL - quarterly_report erscheint im täglichen Status-Report Admin-Panel: - "Forum & Meldungen" → "Forum"
725 lines
29 KiB
Python
725 lines
29 KiB
Python
#!/usr/bin/env python3
|
||
"""
|
||
BAN YARO — Quarterly Report Generator
|
||
Aufruf: python3 scripts/generate_reports.py <section>
|
||
Sections: sicherheit | funktionsumfang | dateien | nutzer | partner | server | all
|
||
"""
|
||
|
||
import os
|
||
import sys
|
||
import sqlite3
|
||
import subprocess
|
||
from datetime import datetime
|
||
from pathlib import Path
|
||
|
||
DB_PATH = os.getenv("DB_PATH", "/data/banyaro.db")
|
||
MEDIA_DIR = os.getenv("MEDIA_DIR", "/data/media")
|
||
APP_DIR = "/app"
|
||
NOW = datetime.now()
|
||
DATE_STR = NOW.strftime("%d.%m.%Y %H:%M")
|
||
ISO_DATE = NOW.strftime("%Y-%m-%d")
|
||
|
||
|
||
# ──────────────────────────────────────────────────────────────────────────────
|
||
# Hilfsfunktionen
|
||
# ──────────────────────────────────────────────────────────────────────────────
|
||
|
||
def db():
|
||
conn = sqlite3.connect(DB_PATH)
|
||
conn.row_factory = sqlite3.Row
|
||
return conn
|
||
|
||
|
||
def q(sql, params=()):
|
||
try:
|
||
with db() as conn:
|
||
return conn.execute(sql, params).fetchall()
|
||
except Exception as e:
|
||
return []
|
||
|
||
|
||
def q1(sql, params=()):
|
||
rows = q(sql, params)
|
||
return rows[0] if rows else None
|
||
|
||
|
||
def val(sql, params=(), default=0):
|
||
row = q1(sql, params)
|
||
if row is None:
|
||
return default
|
||
return row[0]
|
||
|
||
|
||
def sh(cmd):
|
||
try:
|
||
r = subprocess.run(cmd, shell=True, capture_output=True, text=True, timeout=10)
|
||
return r.stdout.strip()
|
||
except Exception:
|
||
return "(nicht verfügbar)"
|
||
|
||
|
||
def hr():
|
||
return "\n---\n"
|
||
|
||
|
||
def h(level, text):
|
||
return f"\n{'#' * level} {text}\n"
|
||
|
||
|
||
def table(headers, rows):
|
||
col_widths = [len(h) for h in headers]
|
||
for row in rows:
|
||
for i, cell in enumerate(row):
|
||
if i < len(col_widths):
|
||
col_widths[i] = max(col_widths[i], len(str(cell)))
|
||
sep = "| " + " | ".join("-" * w for w in col_widths) + " |"
|
||
hdr = "| " + " | ".join(str(h).ljust(col_widths[i]) for i, h in enumerate(headers)) + " |"
|
||
lines = [hdr, sep]
|
||
for row in rows:
|
||
line = "| " + " | ".join(str(row[i] if i < len(row) else "").ljust(col_widths[i]) for i in range(len(headers))) + " |"
|
||
lines.append(line)
|
||
return "\n".join(lines)
|
||
|
||
|
||
def bytes_human(b):
|
||
for unit in ("B", "KB", "MB", "GB"):
|
||
if b < 1024:
|
||
return f"{b:.1f} {unit}"
|
||
b /= 1024
|
||
return f"{b:.1f} TB"
|
||
|
||
|
||
# ──────────────────────────────────────────────────────────────────────────────
|
||
# 1 SICHERHEITSBERICHT
|
||
# ──────────────────────────────────────────────────────────────────────────────
|
||
|
||
def report_sicherheit():
|
||
# Aktive Bans aus DB
|
||
banned = val("SELECT COUNT(*) FROM users WHERE is_banned=1")
|
||
unverifiziert = val("SELECT COUNT(*) FROM users WHERE email_verified=0")
|
||
outreach_rows = q("SELECT COUNT(*) as n, from_account FROM outreach_log GROUP BY from_account")
|
||
|
||
lines = [
|
||
f"# Sicherheitsbericht — Ban Yaro",
|
||
f"\n_Erstellt: {DATE_STR}_\n",
|
||
hr(),
|
||
h(2, "Übersicht implementierter Schutzmaßnahmen"),
|
||
h(3, "1. Authentifizierung & Passwörter"),
|
||
"- **JWT** (HS256) mit 30-Tage-Ablauf, HttpOnly + Secure + SameSite=lax Cookie",
|
||
"- **Bcrypt**-Passwort-Hashing mit automatischem Salt",
|
||
"- Mindestlänge 8 Zeichen, serverseitig erzwungen",
|
||
"- Passwort-Reset: kryptographisches Token, 2 Stunden Ablauf",
|
||
"",
|
||
h(3, "2. Registrierung"),
|
||
"- **E-Mail-Verifikation** zwingend vor dem ersten Login",
|
||
"- Verifikationslink läuft nach 7 Tagen ab",
|
||
"- Rate Limit: 5 Registrierungen / Stunde / IP",
|
||
"- Username-Blocklist: >200 reservierte und unangemessene Begriffe",
|
||
"- Keine Doppelanmeldung (E-Mail und Username unique)",
|
||
"",
|
||
h(3, "3. Login-Schutz"),
|
||
"- **IP-Rate-Limit**: 10 Versuche / 5 Minuten",
|
||
"- **Email-Rate-Limit**: 5 Versuche / 5 Minuten pro E-Mail-Adresse",
|
||
"- **Account-Lockout**: 5 Fehlversuche → 15 Minuten gesperrt (in-memory)",
|
||
"- Fehlerzähler wird bei erfolgreichem Login zurückgesetzt",
|
||
"- Gleiche Fehlermeldung bei falschem Passwort UND unbekannter E-Mail (kein User-Enumeration)",
|
||
"",
|
||
h(3, "4. Forum-Schutz"),
|
||
"- E-Mail-Verifikation Pflicht zum Posten",
|
||
"- **Post-Cooldown**: 30 Sekunden zwischen beliebigen Beiträgen",
|
||
"- **Stunden-Limit Threads**: max. 5 neue Threads / Stunde / User",
|
||
"- **Stunden-Limit Antworten**: max. 20 Antworten / Stunde / User",
|
||
"- **Duplikat-Erkennung**: gleicher Text in 5 Minuten → blockiert",
|
||
"- **Content-Filter**: Spam-Keywords, URL-Sperre für Accounts < 7 Tage, Sonderzeichen-Ratio",
|
||
"- Moderatoren können Threads sperren, Beiträge löschen (Soft-Delete)",
|
||
"- Report-System: User können Beiträge melden",
|
||
"",
|
||
h(3, "5. HTTP-Security-Headers"),
|
||
"| Header | Wert |",
|
||
"|--------|------|",
|
||
"| `Strict-Transport-Security` | `max-age=31536000; includeSubDomains` |",
|
||
"| `Content-Security-Policy` | default-src 'self'; frame-ancestors 'none'; … |",
|
||
"| `X-Content-Type-Options` | `nosniff` |",
|
||
"| `Referrer-Policy` | `strict-origin-when-cross-origin` |",
|
||
"| `Permissions-Policy` | camera=(), microphone=(), geolocation=(self) |",
|
||
"",
|
||
h(3, "6. Rate Limiting (alle Endpunkte)"),
|
||
table(
|
||
["Endpunkt", "Limit", "Fenster"],
|
||
[
|
||
["/auth/register", "5 Req", "60 Min"],
|
||
["/auth/login (IP)", "10 Req", "5 Min"],
|
||
["/auth/login (Email)", "5 Req", "5 Min"],
|
||
["/auth/forgot-password", "3 Req", "60 Min"],
|
||
["/auth/resend-verification", "3 Req", "60 Min / Email"],
|
||
["/auth/reset-password", "5 Req", "60 Min"],
|
||
["KI-Features", "10 Req", "60 Min"],
|
||
["Poison-Reports", "3 Req", "60 Min"],
|
||
["Wiki-Liste", "60 Req", "60 Sek"],
|
||
["Wiki-Detail", "30 Req", "60 Sek"],
|
||
]
|
||
),
|
||
"",
|
||
h(3, "7. Honeypot-Fallen"),
|
||
"Folgende Pfade blockieren Scanner-IPs sofort für 24 Stunden:",
|
||
"",
|
||
"```",
|
||
"/api/admin/users /api/v1/users /api/users /api/.env",
|
||
"/api/config /api/setup /api/install /api/phpinfo",
|
||
"/api/debug /api/actuator /api/swagger /api/graphql",
|
||
"/api/wiki/trap",
|
||
"```",
|
||
"",
|
||
h(3, "8. Datei-Upload-Sicherheit"),
|
||
"- **Magic-Byte-Prüfung**: JPEG, PNG, GIF, WebP, MP4, WebM",
|
||
"- **Path-Traversal-Schutz**: alle Pfade bleiben innerhalb `MEDIA_DIR`",
|
||
"- **Größenbeschränkung**: 20 MB globales Limit (Middleware)",
|
||
"- Automatische Konvertierung: HEIC→JPEG, MOV/AVI→MP4",
|
||
"- Max. 5 Fotos pro Forum-Thread",
|
||
"",
|
||
h(3, "9. Admin & Moderation"),
|
||
"- Admin-Endpoints per `require_admin` Dependency geschützt",
|
||
"- Moderatoren-Rolle mit eingeschränkten Rechten",
|
||
"- User-Banning mit Sperrgrund, geprüft bei jedem Request",
|
||
"- Outreach-Mailing nur über Admin-Panel, vollständiges Log",
|
||
"",
|
||
h(2, "Aktuelle Kennzahlen"),
|
||
table(
|
||
["Metrik", "Wert"],
|
||
[
|
||
["Gesperrte Accounts", str(banned)],
|
||
["Unverifizierte Accounts", str(unverifiziert)],
|
||
["Gesendete Outreach-Mails", str(sum(r[0] for r in outreach_rows))],
|
||
]
|
||
),
|
||
"",
|
||
h(2, "Bekannte Einschränkungen"),
|
||
"- Rate-Limit-Daten und IP-Blocklist sind **in-memory** → Reset bei Container-Neustart",
|
||
"- Kein CAPTCHA (bewusst: Nutzerfreundlichkeit vs. Bot-Schutz)",
|
||
"- Keine Refresh-Token-Rotation (JWT ist 30 Tage gültig)",
|
||
"- Analytics (Besucher) extern über Umami — kein Zugriff aus dem Container",
|
||
"",
|
||
h(2, "Empfehlungen für nächste Überprüfung"),
|
||
"- [ ] Prüfen ob IP-Blocklist-Persistenz via DB sinnvoll wäre",
|
||
"- [ ] CSP weiter verschärfen (nonce-basiert statt unsafe-inline)",
|
||
"- [ ] Login-Logs in DB schreiben (für Audit-Trail)",
|
||
"- [ ] Zwei-Faktor-Authentifizierung für Admin-Accounts evaluieren",
|
||
]
|
||
return "\n".join(lines)
|
||
|
||
|
||
# ──────────────────────────────────────────────────────────────────────────────
|
||
# 2 FUNKTIONSUMFANG
|
||
# ──────────────────────────────────────────────────────────────────────────────
|
||
|
||
def report_funktionsumfang():
|
||
BEREICHE = [
|
||
("Authentifizierung", [
|
||
"Registrierung mit E-Mail-Verifikation",
|
||
"Login / Logout (JWT + HttpOnly-Cookie)",
|
||
"Passwort vergessen / zurücksetzen",
|
||
"Verifikations-Mail erneut senden",
|
||
"Referral-System (3 Stufen: 10/20/50 Refs → 20/30/50 % Rabatt)",
|
||
"Partner-Codes (Gründer-Slot, eigene Einladungen)",
|
||
]),
|
||
("Hunde-Profile", [
|
||
"Anlegen / Bearbeiten von Hunde-Profilen (Rasse, Geburtsdatum, Gewicht, …)",
|
||
"Avatar-Upload (JPEG/WebP-Konvertierung, Vorschau)",
|
||
"Öffentliches Profil mit QR-Code und Teilen-Link",
|
||
"Hunde-Ausweis (druckbares HTML-Dokument)",
|
||
"Mehrere Hunde pro Account",
|
||
]),
|
||
("Forum", [
|
||
"Thread erstellen mit Kategorien (allgemein, rasse, region, …)",
|
||
"Antworten, Likes, Foto-Anhänge (max. 5 pro Thread)",
|
||
"Moderatoren: Thread pinnen, sperren, löschen",
|
||
"Report-System: Beiträge melden",
|
||
"Push-Benachrichtigungen bei neuer Antwort",
|
||
"Öffentlich lesbar, Schreiben nur für verifizierte User",
|
||
]),
|
||
("Tagebuch", [
|
||
"Tageseinträge mit Freitext, Fotos, GPS-Koordinaten",
|
||
"EXIF-GPS-Extraktion aus Foto-Uploads",
|
||
"Kartenansicht aller Tagebuch-Pins",
|
||
"Kalenderansicht nach Datum",
|
||
"Medienansicht (Galerie aller Fotos)",
|
||
"Day-One-kompatibles Format",
|
||
]),
|
||
("Gesundheit & Training", [
|
||
"Gewichtsverlauf mit Diagramm",
|
||
"Gesundheits-Erinnerungen (Push, täglich 08:00)",
|
||
"104 Übungen (DB-basiert, KI-Trainingspläne)",
|
||
"Training-Logging mit Fortschrittsverfolgung",
|
||
"KI-Gesundheitsberichte (wöchentlich, cloud/lokal)",
|
||
]),
|
||
("Karte & POIs", [
|
||
"Leaflet-Karte mit Cluster-Markern",
|
||
"Nearby-Alerts: Giftköder, Vermisste Hunde in der Nähe",
|
||
"Overpass-API-Integration (Tierärzte, Hundewiesen, Parks, …)",
|
||
"90-Tage-Cache für Overpass-Abfragen",
|
||
"ORS-Routenvorschläge zu Hundeparks",
|
||
]),
|
||
("Wiki & Rassen", [
|
||
"Rassen-Datenbank (TheDogAPI + Wikidata-Enrichment)",
|
||
"Züchter-Verzeichnis mit Verifikation",
|
||
"Breed-Interest-Tracking ('So einen hab ich' / 'Interessiert mich')",
|
||
"KI-gestützte Rassen-Anreicherung",
|
||
"Wikipedia-basierte Beschreibungen",
|
||
]),
|
||
("Züchter-Features", [
|
||
"Züchter-Antrag mit Dokument-Upload",
|
||
"Admin-Prüfung und Freischaltung",
|
||
"Züchter-Profil (Zwingername, Rassen, VDH, Stadt)",
|
||
"Wurfverwaltung mit Elterntieren, Welpen, Fotos",
|
||
"Tierschutz-Check vor Wurf-Anlage",
|
||
"Stammbaum-Ansicht",
|
||
"Genetik-Tracking (Farbgene, Erbkrankheiten)",
|
||
"Kaufvertrags-Generator",
|
||
"Jahresbericht-Export",
|
||
]),
|
||
("Social Features", [
|
||
"Freundschaften (anfragen, annehmen, ablehnen)",
|
||
"Social-Media-Posts (Luna — KI-Social-Manager)",
|
||
"Lober: wöchentlicher KI-Lob-Push (Mo 09:00)",
|
||
"Benachrichtigungen (in-app + Push-Notifications)",
|
||
]),
|
||
("Admin & Moderation", [
|
||
"Admin-Dashboard: User-Verwaltung, Ban/Unban",
|
||
"Moderation-Queue: gemeldete Beiträge",
|
||
"Outreach-Mailing: Templates, Versand, Log",
|
||
"Statistiken: User-Wachstum, Aktivität",
|
||
"Züchter-Anträge prüfen",
|
||
"Partner-Codes verwalten",
|
||
"KI-Konfiguration (cloud/lokal, Limits)",
|
||
]),
|
||
("Infrastruktur", [
|
||
"Service Worker (Offline-Stufen 1–3)",
|
||
"Push-Notifications (VAPID)",
|
||
"APScheduler: 9 Hintergrund-Jobs (Gesundheit, Wetter, Events, …)",
|
||
"Brevo E-Mail-API + SMTP-Fallback",
|
||
"Analytics: Umami v2 (extern)",
|
||
"SEO: robots.txt, sitemap.xml, llms.txt",
|
||
"Landing Page + Widget",
|
||
]),
|
||
]
|
||
|
||
lines = [
|
||
"# Funktionsumfang — Ban Yaro",
|
||
f"\n_Erstellt: {DATE_STR}_\n",
|
||
hr(),
|
||
]
|
||
for bereich, features in BEREICHE:
|
||
lines.append(h(2, bereich))
|
||
for f in features:
|
||
lines.append(f"- {f}")
|
||
lines.append("")
|
||
|
||
# Anzahl Routes aus DB-Query-Kontext (statisch)
|
||
lines += [
|
||
hr(),
|
||
h(2, "Backend-Routers"),
|
||
table(
|
||
["Router", "Präfix"],
|
||
[
|
||
["auth", "/api/auth"],
|
||
["dogs", "/api/dogs"],
|
||
["diary", "/api/diary"],
|
||
["health", "/api/health"],
|
||
["forum", "/api/forum"],
|
||
["wiki", "/api/wiki"],
|
||
["map", "/api/map"],
|
||
["poison", "/api/poison"],
|
||
["lost", "/api/lost"],
|
||
["breeder", "/api/breeder"],
|
||
["litters", "/api/litters"],
|
||
["training", "/api/training"],
|
||
["outreach", "/api/outreach"],
|
||
["moderation", "/api/moderation"],
|
||
["notes", "/api/notes"],
|
||
["notifications", "/api/notifications"],
|
||
["push", "/api/push"],
|
||
["friends", "/api/friends"],
|
||
["profile", "/api/profile"],
|
||
["social", "/api/social"],
|
||
["sitting", "/api/sitting"],
|
||
["achievements", "/api/achievements"],
|
||
["stats", "/api/stats"],
|
||
["walks", "/api/walks"],
|
||
["events", "/api/events"],
|
||
["alerts", "/api/alerts"],
|
||
["ratings", "/api/ratings"],
|
||
]
|
||
),
|
||
]
|
||
return "\n".join(lines)
|
||
|
||
|
||
# ──────────────────────────────────────────────────────────────────────────────
|
||
# 3 DATEILISTE
|
||
# ──────────────────────────────────────────────────────────────────────────────
|
||
|
||
def report_dateien():
|
||
lines = [
|
||
"# Dateiliste — Ban Yaro",
|
||
f"\n_Erstellt: {DATE_STR}_\n",
|
||
hr(),
|
||
]
|
||
|
||
def scan_dir(title, path, ext):
|
||
lines.append(h(2, title))
|
||
files = sorted(Path(path).rglob(f"*.{ext}")) if Path(path).exists() else []
|
||
rows = []
|
||
total = 0
|
||
for f in files:
|
||
try:
|
||
size = f.stat().st_size
|
||
total += size
|
||
rows.append([str(f.relative_to(path)), bytes_human(size)])
|
||
except Exception:
|
||
pass
|
||
if rows:
|
||
lines.append(table(["Datei", "Größe"], rows))
|
||
lines.append(f"\n**Gesamt**: {len(rows)} Dateien, {bytes_human(total)}\n")
|
||
|
||
scan_dir("Backend — Python-Dateien", APP_DIR, "py")
|
||
scan_dir("Frontend — JavaScript", f"{APP_DIR}/static/js", "js")
|
||
scan_dir("Frontend — CSS", f"{APP_DIR}/static/css", "css")
|
||
|
||
# HTML-Templates
|
||
html_files = list(Path(f"{APP_DIR}/static").glob("*.html")) if Path(f"{APP_DIR}/static").exists() else []
|
||
if html_files:
|
||
lines.append(h(2, "Frontend — HTML"))
|
||
rows = [[f.name, bytes_human(f.stat().st_size)] for f in sorted(html_files)]
|
||
lines.append(table(["Datei", "Größe"], rows))
|
||
lines.append("")
|
||
|
||
return "\n".join(lines)
|
||
|
||
|
||
# ──────────────────────────────────────────────────────────────────────────────
|
||
# 4 NUTZERÜBERSICHT
|
||
# ──────────────────────────────────────────────────────────────────────────────
|
||
|
||
def report_nutzer():
|
||
lines = [
|
||
"# Nutzerübersicht — Ban Yaro",
|
||
f"\n_Erstellt: {DATE_STR}_\n",
|
||
hr(),
|
||
]
|
||
|
||
# Nutzer nach Rolle
|
||
lines.append(h(2, "Nutzer nach Rolle"))
|
||
total_users = val("SELECT COUNT(*) FROM users")
|
||
admins = val("SELECT COUNT(*) FROM users WHERE rolle='admin'")
|
||
mods = val("SELECT COUNT(*) FROM users WHERE rolle='moderator' OR is_moderator=1")
|
||
breeders = val("SELECT COUNT(*) FROM users WHERE rolle='breeder'")
|
||
founders = val("SELECT COUNT(*) FROM users WHERE is_founder=1")
|
||
partners = val("SELECT COUNT(*) FROM users WHERE is_partner=1")
|
||
banned = val("SELECT COUNT(*) FROM users WHERE is_banned=1")
|
||
unverifiziert = val("SELECT COUNT(*) FROM users WHERE email_verified=0")
|
||
premium = val("SELECT COUNT(*) FROM users WHERE is_premium=1")
|
||
|
||
lines.append(table(
|
||
["Gruppe", "Anzahl"],
|
||
[
|
||
["Gesamt Nutzer", str(total_users)],
|
||
["Admin", str(admins)],
|
||
["Moderatoren", str(mods)],
|
||
["Züchter", str(breeders)],
|
||
["Gründer (aktiv)", str(founders)],
|
||
["Partner", str(partners)],
|
||
["Premium", str(premium)],
|
||
["Gesperrt (banned)", str(banned)],
|
||
["E-Mail unverifiziert", str(unverifiziert)],
|
||
]
|
||
))
|
||
|
||
# Registrierungen pro Monat (letzte 6 Monate)
|
||
lines.append(h(2, "Registrierungen (letzte 6 Monate)"))
|
||
reg_rows = q("""
|
||
SELECT strftime('%Y-%m', created_at) as monat, COUNT(*) as n
|
||
FROM users
|
||
WHERE created_at >= date('now', '-6 months')
|
||
GROUP BY monat ORDER BY monat
|
||
""")
|
||
if reg_rows:
|
||
lines.append(table(["Monat", "Neue Nutzer"], [(r[0], r[1]) for r in reg_rows]))
|
||
else:
|
||
lines.append("_Keine Daten_")
|
||
lines.append("")
|
||
|
||
# Hunde
|
||
lines.append(h(2, "Hunde"))
|
||
dogs = val("SELECT COUNT(*) FROM dogs")
|
||
dogs_with_diary = val("SELECT COUNT(DISTINCT dog_id) FROM diary")
|
||
lines.append(table(
|
||
["Metrik", "Anzahl"],
|
||
[
|
||
["Hunde gesamt", str(dogs)],
|
||
["Hunde mit Tagebuch-Einträgen", str(dogs_with_diary)],
|
||
]
|
||
))
|
||
lines.append("")
|
||
|
||
# Forum
|
||
lines.append(h(2, "Forum"))
|
||
threads = val("SELECT COUNT(*) FROM forum_threads WHERE is_deleted=0")
|
||
posts = val("SELECT COUNT(*) FROM forum_posts WHERE is_deleted=0")
|
||
reports_open = val("SELECT COUNT(*) FROM forum_reports WHERE resolved=0", default=0)
|
||
lines.append(table(
|
||
["Metrik", "Anzahl"],
|
||
[
|
||
["Threads", str(threads)],
|
||
["Antworten", str(posts)],
|
||
["Offene Meldungen", str(reports_open)],
|
||
]
|
||
))
|
||
|
||
# Kategorie-Verteilung
|
||
kat_rows = q("""
|
||
SELECT kategorie, COUNT(*) as n
|
||
FROM forum_threads WHERE is_deleted=0
|
||
GROUP BY kategorie ORDER BY n DESC
|
||
""")
|
||
if kat_rows:
|
||
lines.append("\n**Threads nach Kategorie:**\n")
|
||
lines.append(table(["Kategorie", "Threads"], [(r[0], r[1]) for r in kat_rows]))
|
||
lines.append("")
|
||
|
||
# Tagebuch
|
||
lines.append(h(2, "Tagebuch"))
|
||
diary_total = val("SELECT COUNT(*) FROM diary")
|
||
diary_mit_foto = val("SELECT COUNT(*) FROM diary WHERE foto_url IS NOT NULL AND foto_url != ''")
|
||
diary_mit_gps = val("SELECT COUNT(*) FROM diary WHERE lat IS NOT NULL")
|
||
lines.append(table(
|
||
["Metrik", "Anzahl"],
|
||
[
|
||
["Einträge gesamt", str(diary_total)],
|
||
["Mit Foto", str(diary_mit_foto)],
|
||
["Mit GPS-Koordinaten", str(diary_mit_gps)],
|
||
]
|
||
))
|
||
lines.append("")
|
||
|
||
# Medien (Dateisystem)
|
||
lines.append(h(2, "Medien auf dem Server"))
|
||
media_root = Path(MEDIA_DIR)
|
||
if media_root.exists():
|
||
rows = []
|
||
total_size = 0
|
||
total_count = 0
|
||
for subdir in sorted(media_root.iterdir()):
|
||
if subdir.is_dir():
|
||
files = list(subdir.rglob("*"))
|
||
files = [f for f in files if f.is_file()]
|
||
size = sum(f.stat().st_size for f in files if f.is_file())
|
||
total_size += size
|
||
total_count += len(files)
|
||
rows.append([subdir.name, str(len(files)), bytes_human(size)])
|
||
rows.append(["**GESAMT**", str(total_count), bytes_human(total_size)])
|
||
lines.append(table(["Verzeichnis", "Dateien", "Größe"], rows))
|
||
else:
|
||
lines.append(f"_Media-Verzeichnis nicht gefunden: {MEDIA_DIR}_")
|
||
lines.append("")
|
||
|
||
# Outreach-Mails
|
||
lines.append(h(2, "Gesendete E-Mails"))
|
||
mail_rows = q("""
|
||
SELECT from_account, COUNT(*) as n,
|
||
MIN(sent_at) as erste, MAX(sent_at) as letzte
|
||
FROM outreach_log
|
||
GROUP BY from_account ORDER BY n DESC
|
||
""")
|
||
if mail_rows:
|
||
lines.append(table(
|
||
["Absender", "Anzahl", "Erste Mail", "Letzte Mail"],
|
||
[(r[0], r[1], r[2][:10] if r[2] else "—", r[3][:10] if r[3] else "—") for r in mail_rows]
|
||
))
|
||
total_mails = sum(r[1] for r in mail_rows)
|
||
lines.append(f"\n**Gesamt**: {total_mails} Mails gesendet\n")
|
||
else:
|
||
lines.append("_Noch keine Mails versendet_\n")
|
||
|
||
# Analytics-Hinweis
|
||
lines += [
|
||
h(2, "Besuche (Analytics)"),
|
||
"> **Hinweis:** Besucher-Statistiken (Besuche/Tag und Monat) werden extern "
|
||
"über **Umami** erfasst und sind nicht im Container verfügbar. "
|
||
"Bitte Umami-Dashboard direkt aufrufen.",
|
||
"",
|
||
]
|
||
|
||
return "\n".join(lines)
|
||
|
||
|
||
# ──────────────────────────────────────────────────────────────────────────────
|
||
# 5 PARTNERLISTE
|
||
# ──────────────────────────────────────────────────────────────────────────────
|
||
|
||
def report_partner():
|
||
lines = [
|
||
"# Partnerliste — Ban Yaro",
|
||
f"\n_Erstellt: {DATE_STR}_\n",
|
||
hr(),
|
||
]
|
||
|
||
# Partner-User
|
||
lines.append(h(2, "Partner-Accounts"))
|
||
partner_users = q("""
|
||
SELECT name, email, created_at, founder_number
|
||
FROM users WHERE is_partner=1
|
||
ORDER BY created_at
|
||
""")
|
||
if partner_users:
|
||
lines.append(table(
|
||
["Name", "E-Mail", "Partner seit", "Gründer-Nr."],
|
||
[(r[0], r[1], r[2][:10] if r[2] else "—", str(r[3]) if r[3] else "—") for r in partner_users]
|
||
))
|
||
else:
|
||
lines.append("_Keine Partner-Accounts_")
|
||
lines.append("")
|
||
|
||
# Partner-Codes
|
||
lines.append(h(2, "Partner-Codes"))
|
||
codes = q("""
|
||
SELECT code, grants_founder, max_uses, uses, created_at
|
||
FROM partner_codes ORDER BY created_at
|
||
""")
|
||
if codes:
|
||
lines.append(table(
|
||
["Code", "Gründer-Slot", "Max. Nutzungen", "Verwendet", "Erstellt"],
|
||
[(
|
||
r[0],
|
||
"Ja" if r[1] else "Nein",
|
||
str(r[2]) if r[2] else "∞",
|
||
str(r[3]),
|
||
r[4][:10] if r[4] else "—"
|
||
) for r in codes]
|
||
))
|
||
else:
|
||
lines.append("_Keine Partner-Codes_")
|
||
lines.append("")
|
||
|
||
# Gründer
|
||
lines.append(h(2, "Gründer"))
|
||
gruender = q("""
|
||
SELECT founder_number, name, email, created_at
|
||
FROM users WHERE is_founder=1
|
||
ORDER BY founder_number
|
||
""")
|
||
if gruender:
|
||
lines.append(table(
|
||
["Nr.", "Name", "E-Mail", "Registriert"],
|
||
[(r[0], r[1], r[2], r[3][:10] if r[3] else "—") for r in gruender]
|
||
))
|
||
lines.append(f"\n**{len(gruender)} von 100 Gründer-Plätzen belegt.**\n")
|
||
else:
|
||
lines.append("_Noch keine Gründer_")
|
||
lines.append("")
|
||
|
||
return "\n".join(lines)
|
||
|
||
|
||
# ──────────────────────────────────────────────────────────────────────────────
|
||
# 6 SERVER & SPEICHER
|
||
# ──────────────────────────────────────────────────────────────────────────────
|
||
|
||
def report_server():
|
||
lines = [
|
||
"# Server & Speicherbelegung — Ban Yaro",
|
||
f"\n_Erstellt: {DATE_STR}_\n",
|
||
hr(),
|
||
]
|
||
|
||
# Disk Usage
|
||
lines.append(h(2, "Festplattenbelegung"))
|
||
df_out = sh("df -h /data 2>/dev/null || df -h /")
|
||
lines.append(f"```\n{df_out}\n```\n")
|
||
|
||
# Media-Verzeichnisse
|
||
lines.append(h(2, "Media-Verzeichnisse"))
|
||
du_media = sh(f"du -sh {MEDIA_DIR}/* 2>/dev/null | sort -rh")
|
||
du_total = sh(f"du -sh {MEDIA_DIR} 2>/dev/null")
|
||
if du_media:
|
||
lines.append(f"```\n{du_media}\n\nGesamt: {du_total}\n```\n")
|
||
else:
|
||
lines.append("_Keine Media-Daten_\n")
|
||
|
||
# DB-Größe
|
||
lines.append(h(2, "Datenbank"))
|
||
db_size = sh(f"du -sh {DB_PATH} 2>/dev/null")
|
||
db_rows = {}
|
||
try:
|
||
with db() as conn:
|
||
tables = conn.execute(
|
||
"SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
|
||
).fetchall()
|
||
for t in tables:
|
||
name = t[0]
|
||
count = conn.execute(f"SELECT COUNT(*) FROM {name}").fetchone()[0]
|
||
db_rows[name] = count
|
||
except Exception:
|
||
pass
|
||
lines.append(f"**DB-Größe:** {db_size}\n")
|
||
if db_rows:
|
||
rows_sorted = sorted(db_rows.items(), key=lambda x: x[1], reverse=True)
|
||
lines.append(table(["Tabelle", "Zeilen"], [(k, f"{v:,}") for k, v in rows_sorted]))
|
||
lines.append("")
|
||
|
||
# App-Code Größe
|
||
lines.append(h(2, "App-Code"))
|
||
du_app = sh(f"du -sh {APP_DIR} 2>/dev/null")
|
||
lines.append(f"**App-Verzeichnis ({APP_DIR}):** {du_app}\n")
|
||
|
||
# Speicher-Kapazität (Warnung wenn >80 %)
|
||
lines.append(h(2, "Kapazitäts-Warnung"))
|
||
df_pct = sh("df /data 2>/dev/null | awk 'NR==2{print $5}' | tr -d '%' || df / | awk 'NR==2{print $5}' | tr -d '%'")
|
||
try:
|
||
pct = int(df_pct.strip())
|
||
if pct >= 90:
|
||
lines.append(f"> ⚠️ **KRITISCH: {pct} % Festplatte belegt!** Sofortige Maßnahmen nötig.")
|
||
elif pct >= 80:
|
||
lines.append(f"> ⚠️ **Warnung: {pct} % Festplatte belegt.** Bald aufrüsten.")
|
||
elif pct >= 70:
|
||
lines.append(f"> ℹ️ {pct} % Festplatte belegt — im Blick behalten.")
|
||
else:
|
||
lines.append(f"> ✅ {pct} % Festplatte belegt — ausreichend Kapazität.")
|
||
except (ValueError, TypeError):
|
||
lines.append(f"> Belegung: {df_pct}")
|
||
lines.append("")
|
||
|
||
# Python-Pakete
|
||
lines.append(h(2, "Installierte Python-Pakete"))
|
||
pip_list = sh("pip list --format=columns 2>/dev/null | head -40")
|
||
lines.append(f"```\n{pip_list}\n```\n")
|
||
|
||
return "\n".join(lines)
|
||
|
||
|
||
# ──────────────────────────────────────────────────────────────────────────────
|
||
# Main
|
||
# ──────────────────────────────────────────────────────────────────────────────
|
||
|
||
REPORTS = {
|
||
"sicherheit": report_sicherheit,
|
||
"funktionsumfang": report_funktionsumfang,
|
||
"dateien": report_dateien,
|
||
"nutzer": report_nutzer,
|
||
"partner": report_partner,
|
||
"server": report_server,
|
||
}
|
||
|
||
if __name__ == "__main__":
|
||
section = sys.argv[1] if len(sys.argv) > 1 else "all"
|
||
|
||
if section == "all":
|
||
for name, fn in REPORTS.items():
|
||
print(f"=== REPORT:{name} ===")
|
||
print(fn())
|
||
print()
|
||
elif section in REPORTS:
|
||
print(REPORTS[section]())
|
||
else:
|
||
print(f"Unbekannte Section: {section}", file=sys.stderr)
|
||
print(f"Verfügbar: {', '.join(REPORTS.keys())}", file=sys.stderr)
|
||
sys.exit(1)
|