movie-night/app/database.py

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()