kbondelie
d8c8b473ad
Saves each mood search and its recommendations to SQLite per user. Recent searches appear below the results area with mood text, top movie titles, and timestamp. Click any entry to reload those results. Entries can be deleted individually. History toggleable via show/hide. Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
100 lines
2.9 KiB
Python
100 lines
2.9 KiB
Python
import aiosqlite
|
|
import os
|
|
from app.config import settings
|
|
|
|
_db_path = settings.db_path
|
|
|
|
|
|
def _ensure_db_dir():
|
|
os.makedirs(os.path.dirname(_db_path), exist_ok=True)
|
|
|
|
|
|
async def get_db() -> aiosqlite.Connection:
|
|
_ensure_db_dir()
|
|
db = await aiosqlite.connect(_db_path)
|
|
db.row_factory = aiosqlite.Row
|
|
await db.execute("PRAGMA journal_mode=WAL")
|
|
return db
|
|
|
|
|
|
async def init_database():
|
|
db = await get_db()
|
|
try:
|
|
await db.executescript("""
|
|
CREATE TABLE IF NOT EXISTS movies (
|
|
jellyfin_id TEXT PRIMARY KEY,
|
|
title TEXT NOT NULL,
|
|
sort_title TEXT,
|
|
year INTEGER,
|
|
genres TEXT DEFAULT '[]',
|
|
overview TEXT,
|
|
community_rating REAL,
|
|
critic_rating REAL,
|
|
runtime_minutes INTEGER,
|
|
content_rating TEXT,
|
|
studios TEXT DEFAULT '[]',
|
|
people TEXT DEFAULT '[]',
|
|
tags TEXT DEFAULT '[]',
|
|
synced_at TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS watch_state (
|
|
jellyfin_id TEXT NOT NULL,
|
|
user_id TEXT NOT NULL,
|
|
is_played INTEGER DEFAULT 0,
|
|
synced_at TEXT,
|
|
PRIMARY KEY (jellyfin_id, user_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
session_id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL,
|
|
username TEXT NOT NULL,
|
|
jellyfin_token TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
expires_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sync_status (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS search_history (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id TEXT NOT NULL,
|
|
mood TEXT NOT NULL,
|
|
results TEXT NOT NULL,
|
|
meta TEXT,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
""")
|
|
await db.commit()
|
|
finally:
|
|
await db.close()
|
|
|
|
|
|
async def get_unwatched_movies(user_ids: list[str]) -> list[dict]:
|
|
db = await get_db()
|
|
try:
|
|
if not user_ids:
|
|
return []
|
|
|
|
# Get movies that are unwatched by ALL specified users
|
|
placeholders = ",".join("?" for _ in user_ids)
|
|
query = f"""
|
|
SELECT m.* FROM movies m
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM watch_state ws
|
|
WHERE ws.jellyfin_id = m.jellyfin_id
|
|
AND ws.user_id IN ({placeholders})
|
|
AND ws.is_played = 1
|
|
)
|
|
ORDER BY m.community_rating DESC NULLS LAST
|
|
"""
|
|
cursor = await db.execute(query, user_ids)
|
|
rows = await cursor.fetchall()
|
|
return [dict(row) for row in rows]
|
|
finally:
|
|
await db.close()
|