rene
e86d89f3d9
Wiederverwendbarer UI.noteMediaAttacher für beide Notiz-Stellen (UI.noteModal
+ Notizblock-Seite). note_media-Tabelle + POST/DELETE /api/notes/{id}/media
(vor der gierigen /{parent_type}/{parent_id}-Route). Audio per MediaRecorder,
serverseitig nach m4a/AAC transkodiert (ffmpeg) — iOS spielt Chrome-Opus-webm
nicht ab. UI.lightbox global eingeführt. Mikrofon-Policy microphone=(self) +
CSP media-src 'self' blob:, Datenschutz v6. Disk-Cleanup für note_media bei
Notiz-, Account- und Admin-User-Delete. Reine Medien-Notiz ohne Text erlaubt.
noteModal-Bug gefixt: notes.get() liefert Array -> existing[0] statt
existing?.id (verhinderte Bearbeiten, erzeugte Duplikate). 12 neue Tests.
admin.py enthält außerdem KI-Vision-Statusfelder aus paralleler Arbeit
(nicht sauber trennbar ohne interaktives Staging).
437 lines
16 KiB
Python
437 lines
16 KiB
Python
"""BAN YARO — Notizen Routes"""
|
|
|
|
import os
|
|
import json
|
|
import uuid
|
|
import asyncio
|
|
import logging
|
|
from datetime import datetime
|
|
from fastapi import APIRouter, Depends, HTTPException, Query, UploadFile, File
|
|
from pydantic import BaseModel, Field
|
|
from typing import Optional, Any, List
|
|
from database import db
|
|
from auth import get_current_user
|
|
from timeutils import safe_client_time
|
|
from media_utils import (convert_media, extract_video_thumb, safe_media_path,
|
|
validate_upload, validate_audio, to_m4a,
|
|
generate_preview, get_image_size)
|
|
|
|
router = APIRouter()
|
|
logger = logging.getLogger(__name__)
|
|
MEDIA_DIR = os.getenv("MEDIA_DIR", "/data/media")
|
|
|
|
|
|
# ------------------------------------------------------------------
|
|
# Schemas
|
|
# ------------------------------------------------------------------
|
|
class NoteCreate(BaseModel):
|
|
# Leerer Text erlaubt: eine reine Medien-Notiz (nur Foto/Sprachnachricht)
|
|
# wird zuerst leer angelegt, dann werden die Medien angehängt.
|
|
text: str = Field("", max_length=5000)
|
|
meta_json: Optional[Any] = None
|
|
location_name: Optional[str] = Field(None, max_length=300)
|
|
parent_label: Optional[str] = Field(None, max_length=200)
|
|
client_time: Optional[str] = Field(None, max_length=64)
|
|
|
|
|
|
class NoteUpdate(BaseModel):
|
|
text: Optional[str] = Field(None, max_length=5000)
|
|
meta_json: Optional[Any] = None
|
|
location_name: Optional[str] = Field(None, max_length=300)
|
|
parent_label: Optional[str] = Field(None, max_length=200)
|
|
|
|
|
|
# ------------------------------------------------------------------
|
|
# Hilfsfunktionen
|
|
# ------------------------------------------------------------------
|
|
def _serialize(row, media_map: Optional[dict] = None) -> dict:
|
|
d = dict(row)
|
|
if d.get("meta_json") and isinstance(d["meta_json"], str):
|
|
try:
|
|
d["meta_json"] = json.loads(d["meta_json"])
|
|
except Exception:
|
|
pass
|
|
if media_map is not None:
|
|
d["media_items"] = media_map.get(d["id"], [])
|
|
return d
|
|
|
|
|
|
def _fetch_note_media(conn, note_ids: list) -> dict:
|
|
"""Lädt alle Medien zu den gegebenen Notiz-IDs als {note_id: [items]}."""
|
|
if not note_ids:
|
|
return {}
|
|
placeholders = ",".join("?" * len(note_ids))
|
|
rows = conn.execute(
|
|
f"""SELECT id, note_id, url, media_type, sort_order, img_width, img_height, duration_s
|
|
FROM note_media WHERE note_id IN ({placeholders})
|
|
ORDER BY sort_order, id""",
|
|
note_ids
|
|
).fetchall()
|
|
out: dict = {}
|
|
for r in rows:
|
|
out.setdefault(r["note_id"], []).append(dict(r))
|
|
return out
|
|
|
|
|
|
def _guess_note_media_type(content_type: str, filename: str) -> str:
|
|
ct = (content_type or "").lower()
|
|
if ct == "application/pdf" or (filename or "").lower().endswith(".pdf"):
|
|
return "pdf"
|
|
if ct.startswith("audio/"):
|
|
return "audio"
|
|
if ct.startswith("video/"):
|
|
return "video"
|
|
if ct.startswith("image/"):
|
|
return "image"
|
|
ext = os.path.splitext(filename or "")[1].lower()
|
|
if ext in {".mp4", ".mov", ".webm", ".m4v", ".avi"}:
|
|
return "video"
|
|
if ext in {".m4a", ".aac", ".mp3", ".ogg", ".oga", ".wav", ".opus"}:
|
|
return "audio"
|
|
if ext in {".jpg", ".jpeg", ".png", ".gif", ".webp", ".heic", ".heif"}:
|
|
return "image"
|
|
return "file"
|
|
|
|
|
|
def _delete_note_media_file(url: str) -> None:
|
|
"""Löscht eine Mediendatei + zugehörige Preview/Thumb-Leichen von Disk."""
|
|
file_path = safe_media_path(MEDIA_DIR, url)
|
|
if not file_path:
|
|
return
|
|
try:
|
|
os.remove(file_path)
|
|
except OSError:
|
|
pass
|
|
base = os.path.splitext(file_path)[0]
|
|
for leftover in (base + "_preview.webp", base + "_thumb.jpg"):
|
|
try:
|
|
os.remove(leftover)
|
|
except OSError:
|
|
pass
|
|
|
|
|
|
def _own_note(note_id: int, user_id: int, conn):
|
|
row = conn.execute(
|
|
"SELECT id FROM notes WHERE id=? AND user_id=?", (note_id, user_id)
|
|
).fetchone()
|
|
if not row:
|
|
raise HTTPException(404, "Notiz nicht gefunden.")
|
|
return row
|
|
|
|
|
|
# ------------------------------------------------------------------
|
|
# GET /api/notes — Gesamt-Notizblock mit Filtern
|
|
# Alias: GET /api/notes/all/0 (Rückwärtskompatibilität)
|
|
# WICHTIG: Diese Route muss VOR /{parent_type}/{parent_id} stehen!
|
|
# ------------------------------------------------------------------
|
|
@router.get("")
|
|
async def list_all_notes_filtered(
|
|
parent_type: Optional[List[str]] = Query(default=None),
|
|
date_from: Optional[str] = Query(default=None),
|
|
date_to: Optional[str] = Query(default=None),
|
|
q: Optional[str] = Query(default=None),
|
|
sort: Optional[str] = Query(default="date_desc"),
|
|
user=Depends(get_current_user),
|
|
):
|
|
"""Alle Notizen des Users mit optionalen Filtern."""
|
|
conditions = ["user_id=?"]
|
|
params: list = [user["id"]]
|
|
|
|
if parent_type:
|
|
placeholders = ",".join("?" * len(parent_type))
|
|
conditions.append(f"parent_type IN ({placeholders})")
|
|
params.extend(parent_type)
|
|
|
|
if date_from:
|
|
conditions.append("DATE(created_at) >= ?")
|
|
params.append(date_from)
|
|
|
|
if date_to:
|
|
conditions.append("DATE(created_at) <= ?")
|
|
params.append(date_to)
|
|
|
|
if q:
|
|
conditions.append("(text LIKE ? OR COALESCE(parent_label,'') LIKE ?)")
|
|
like = f"%{q}%"
|
|
params.extend([like, like])
|
|
|
|
where = " AND ".join(conditions)
|
|
|
|
if sort == "rubrik":
|
|
order = "parent_type ASC, created_at DESC"
|
|
elif sort == "ort":
|
|
order = "CASE WHEN location_name IS NULL OR location_name='' THEN 1 ELSE 0 END ASC, location_name ASC, created_at DESC"
|
|
elif sort == "date_asc":
|
|
order = "created_at ASC"
|
|
else:
|
|
order = "created_at DESC"
|
|
|
|
with db() as conn:
|
|
rows = conn.execute(
|
|
f"SELECT * FROM notes WHERE {where} ORDER BY {order}",
|
|
params
|
|
).fetchall()
|
|
media_map = _fetch_note_media(conn, [r["id"] for r in rows])
|
|
|
|
return [_serialize(r, media_map) for r in rows]
|
|
|
|
|
|
@router.get("/all/0")
|
|
async def list_all_notes(user=Depends(get_current_user)):
|
|
"""Alias für Rückwärtskompatibilität."""
|
|
with db() as conn:
|
|
rows = conn.execute(
|
|
"SELECT * FROM notes WHERE user_id=? ORDER BY created_at DESC",
|
|
(user["id"],)
|
|
).fetchall()
|
|
media_map = _fetch_note_media(conn, [r["id"] for r in rows])
|
|
return [_serialize(r, media_map) for r in rows]
|
|
|
|
|
|
# ------------------------------------------------------------------
|
|
# POST /api/notes/ki-analyse
|
|
# WICHTIG: Fixe Route MUSS vor /{parent_type}/{parent_id} stehen!
|
|
# ------------------------------------------------------------------
|
|
@router.post("/ki-analyse")
|
|
async def ki_analyse(user=Depends(get_current_user)):
|
|
"""KI analysiert die Notizen des Users und gibt Muster/Vorschläge zurück."""
|
|
with db() as conn:
|
|
# User-Setting prüfen
|
|
setting = conn.execute(
|
|
"SELECT notes_ki_enabled FROM users WHERE id=?",
|
|
(user["id"],)
|
|
).fetchone()
|
|
|
|
if not setting or not setting["notes_ki_enabled"]:
|
|
raise HTTPException(403, "KI-Assistent ist deaktiviert.")
|
|
|
|
with db() as conn:
|
|
rows = conn.execute(
|
|
"""SELECT text, parent_type, parent_label, location_name, created_at
|
|
FROM notes
|
|
WHERE user_id=?
|
|
ORDER BY created_at DESC
|
|
LIMIT 50""",
|
|
(user["id"],)
|
|
).fetchall()
|
|
|
|
note_count = len(rows)
|
|
if note_count == 0:
|
|
return {"suggestions": "", "note_count": 0}
|
|
|
|
notes_data = [dict(r) for r in rows]
|
|
|
|
prompt = (
|
|
"Du bist ein freundlicher Assistent für Hundebesitzer. "
|
|
"Analysiere diese Notizen und erkenne Muster (Gesundheit, Training, Verhalten, "
|
|
"Lieblingsrouten, saisonale Besonderheiten). "
|
|
"Gib 2-4 kurze, konkrete Vorschläge auf Deutsch. "
|
|
"Keine langen Texte, bullet points. "
|
|
f"Daten: {json.dumps(notes_data, ensure_ascii=False)}"
|
|
)
|
|
|
|
try:
|
|
import ki as ki_module
|
|
suggestions = await ki_module.complete(
|
|
prompt,
|
|
requires_premium=False,
|
|
user_is_premium=False,
|
|
user_id=user["id"],
|
|
)
|
|
except Exception as e:
|
|
logger.warning("KI-Analyse fehlgeschlagen: %s", e)
|
|
suggestions = ""
|
|
|
|
return {"suggestions": suggestions, "note_count": note_count}
|
|
|
|
|
|
# ------------------------------------------------------------------
|
|
# Medien-Anhänge an Notizen (Bild/Video/Audio/Datei)
|
|
# WICHTIG: Diese Routen MÜSSEN vor /{parent_type}/{parent_id} stehen,
|
|
# sonst matcht POST /123/media als parent_type=123, parent_id="media"!
|
|
# ------------------------------------------------------------------
|
|
@router.post("/{note_id}/media")
|
|
async def upload_note_media(note_id: int,
|
|
file: UploadFile = File(...),
|
|
user=Depends(get_current_user)):
|
|
with db() as conn:
|
|
_own_note(note_id, user["id"], conn)
|
|
|
|
ct = (file.content_type or "").lower().split(";")[0].strip()
|
|
raw_data = await file.read()
|
|
loop = asyncio.get_event_loop()
|
|
|
|
if ct.startswith("audio/"):
|
|
media_type = "audio"
|
|
try:
|
|
validate_audio(raw_data, ct)
|
|
except ValueError as e:
|
|
raise HTTPException(415, str(e))
|
|
src_ext = os.path.splitext(file.filename or "")[1].lower() or ".webm"
|
|
raw_data, ext = await loop.run_in_executor(None, lambda: to_m4a(raw_data, src_ext))
|
|
else:
|
|
# Bild/Video/PDF/sonstige Datei — gleiche Pipeline wie Tagebuch.
|
|
try:
|
|
validate_upload(raw_data, file.filename or "")
|
|
except ValueError as e:
|
|
raise HTTPException(415, str(e))
|
|
media_type = _guess_note_media_type(ct, file.filename or "")
|
|
raw_data, ext = await loop.run_in_executor(
|
|
None, lambda: convert_media(raw_data, file.filename or "")
|
|
)
|
|
if not ext:
|
|
ext = ".bin"
|
|
|
|
filename = f"note_{note_id}_{uuid.uuid4().hex[:8]}{ext}"
|
|
path = os.path.join(MEDIA_DIR, "notes", filename)
|
|
os.makedirs(os.path.dirname(path), exist_ok=True)
|
|
|
|
def _write_bytes(p: str, data: bytes) -> None:
|
|
with open(p, "wb") as f:
|
|
f.write(data)
|
|
|
|
await loop.run_in_executor(None, lambda: _write_bytes(path, raw_data))
|
|
|
|
img_size = None
|
|
if media_type == "video":
|
|
await loop.run_in_executor(None, lambda: extract_video_thumb(path))
|
|
elif media_type == "image":
|
|
preview_bytes = await loop.run_in_executor(None, lambda: generate_preview(raw_data, ext))
|
|
if preview_bytes:
|
|
preview_path = os.path.splitext(path)[0] + "_preview.webp"
|
|
await loop.run_in_executor(None, lambda: _write_bytes(preview_path, preview_bytes))
|
|
img_size = await loop.run_in_executor(None, lambda: get_image_size(raw_data))
|
|
|
|
media_url = f"/media/notes/{filename}"
|
|
with db() as conn:
|
|
max_order = conn.execute(
|
|
"SELECT COALESCE(MAX(sort_order), -1) FROM note_media WHERE note_id=?",
|
|
(note_id,)
|
|
).fetchone()[0]
|
|
conn.execute(
|
|
"""INSERT INTO note_media (note_id, url, media_type, sort_order, img_width, img_height)
|
|
VALUES (?,?,?,?,?,?)""",
|
|
(note_id, media_url, media_type, max_order + 1,
|
|
img_size[0] if img_size else None, img_size[1] if img_size else None)
|
|
)
|
|
row = conn.execute(
|
|
"""SELECT id, note_id, url, media_type, sort_order, img_width, img_height, duration_s
|
|
FROM note_media WHERE note_id=? ORDER BY id DESC LIMIT 1""",
|
|
(note_id,)
|
|
).fetchone()
|
|
return dict(row)
|
|
|
|
|
|
@router.delete("/{note_id}/media/{media_id}", status_code=204)
|
|
async def delete_note_media(note_id: int, media_id: int,
|
|
user=Depends(get_current_user)):
|
|
with db() as conn:
|
|
_own_note(note_id, user["id"], conn)
|
|
row = conn.execute(
|
|
"SELECT id, url FROM note_media WHERE id=? AND note_id=?",
|
|
(media_id, note_id)
|
|
).fetchone()
|
|
if not row:
|
|
raise HTTPException(404, "Medium nicht gefunden.")
|
|
_delete_note_media_file(row["url"])
|
|
conn.execute("DELETE FROM note_media WHERE id=?", (media_id,))
|
|
return None
|
|
|
|
|
|
# ------------------------------------------------------------------
|
|
# GET /api/notes/{parent_type}/{parent_id}
|
|
# parent_id kann ein Integer oder ein String-Schlüssel sein.
|
|
# SQLite ist dynamisch getypt — wir übergeben den Wert als Text.
|
|
# ------------------------------------------------------------------
|
|
@router.get("/{parent_type}/{parent_id}")
|
|
async def list_notes(parent_type: str, parent_id: str,
|
|
user=Depends(get_current_user)):
|
|
with db() as conn:
|
|
rows = conn.execute(
|
|
"""SELECT * FROM notes
|
|
WHERE user_id=? AND parent_type=? AND CAST(parent_id AS TEXT)=?
|
|
ORDER BY created_at DESC""",
|
|
(user["id"], parent_type, parent_id)
|
|
).fetchall()
|
|
media_map = _fetch_note_media(conn, [r["id"] for r in rows])
|
|
return [_serialize(r, media_map) for r in rows]
|
|
|
|
|
|
# ------------------------------------------------------------------
|
|
# POST /api/notes/{parent_type}/{parent_id}
|
|
# ------------------------------------------------------------------
|
|
@router.post("/{parent_type}/{parent_id}", status_code=201)
|
|
async def create_note(parent_type: str, parent_id: str, data: NoteCreate,
|
|
user=Depends(get_current_user)):
|
|
meta_str = json.dumps(data.meta_json) if data.meta_json is not None else None
|
|
now = safe_client_time(data.client_time)
|
|
|
|
with db() as conn:
|
|
conn.execute(
|
|
"""INSERT INTO notes
|
|
(user_id, parent_type, parent_id, text, meta_json,
|
|
location_name, parent_label, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
|
|
(user["id"], parent_type, parent_id, data.text.strip(), meta_str,
|
|
data.location_name, data.parent_label, now, now)
|
|
)
|
|
row = conn.execute(
|
|
"SELECT * FROM notes WHERE user_id=? AND parent_type=? AND parent_id=? ORDER BY id DESC LIMIT 1",
|
|
(user["id"], parent_type, parent_id)
|
|
).fetchone()
|
|
return _serialize(row, {}) # frisch erstellt → media_items=[]; Upload folgt separat
|
|
|
|
|
|
# ------------------------------------------------------------------
|
|
# PATCH /api/notes/{id}
|
|
# ------------------------------------------------------------------
|
|
@router.patch("/{note_id}")
|
|
async def update_note(note_id: int, data: NoteUpdate,
|
|
user=Depends(get_current_user)):
|
|
with db() as conn:
|
|
note = conn.execute(
|
|
"SELECT * FROM notes WHERE id=? AND user_id=?", (note_id, user["id"])
|
|
).fetchone()
|
|
if not note:
|
|
raise HTTPException(404, "Notiz nicht gefunden.")
|
|
|
|
updates = {}
|
|
if data.text is not None:
|
|
# Leer erlaubt — Medien können die Notiz tragen.
|
|
updates["text"] = data.text.strip()
|
|
if data.meta_json is not None:
|
|
updates["meta_json"] = json.dumps(data.meta_json)
|
|
if data.location_name is not None:
|
|
updates["location_name"] = data.location_name
|
|
if data.parent_label is not None:
|
|
updates["parent_label"] = data.parent_label
|
|
|
|
if not updates:
|
|
media_map = _fetch_note_media(conn, [note_id])
|
|
return _serialize(note, media_map)
|
|
|
|
updates["updated_at"] = datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S")
|
|
set_clause = ", ".join(f"{k}=?" for k in updates)
|
|
values = list(updates.values()) + [note_id]
|
|
conn.execute(f"UPDATE notes SET {set_clause} WHERE id=?", values)
|
|
row = conn.execute("SELECT * FROM notes WHERE id=?", (note_id,)).fetchone()
|
|
media_map = _fetch_note_media(conn, [note_id])
|
|
return _serialize(row, media_map)
|
|
|
|
|
|
# ------------------------------------------------------------------
|
|
# DELETE /api/notes/{id}
|
|
# ------------------------------------------------------------------
|
|
@router.delete("/{note_id}", status_code=204)
|
|
async def delete_note(note_id: int, user=Depends(get_current_user)):
|
|
with db() as conn:
|
|
note = conn.execute(
|
|
"SELECT id FROM notes WHERE id=? AND user_id=?", (note_id, user["id"])
|
|
).fetchone()
|
|
if not note:
|
|
raise HTTPException(404, "Notiz nicht gefunden.")
|
|
# Medien-Dateien von Disk räumen (FK-Cascade löscht nur die DB-Zeilen).
|
|
for m in conn.execute("SELECT url FROM note_media WHERE note_id=?", (note_id,)).fetchall():
|
|
_delete_note_media_file(m["url"])
|
|
conn.execute("DELETE FROM notes WHERE id=?", (note_id,))
|
|
return None
|