backend/database.py

import aiosqlite
import os
DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "data.db")
async def init_db():
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute("CREATE TABLE IF NOT EXISTS sources (id TEXT PRIMARY KEY, name TEXT, type TEXT, url TEXT, api_key TEXT)")
        await db.execute("CREATE TABLE IF NOT EXISTS recordings (id TEXT PRIMARY KEY, source_id TEXT, dataset_repo TEXT, original_name TEXT, md5_name TEXT, status TEXT, start_time REAL, end_time REAL, file_path TEXT, remote_url TEXT, remote_id TEXT)")
        await db.execute("CREATE TABLE IF NOT EXISTS epg_cache (id TEXT PRIMARY KEY, source_id TEXT, data TEXT, cached_at REAL)")
        await db.commit()
async def sync_sources(sources):
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute("DELETE FROM sources")
        await db.executemany("INSERT OR IGNORE INTO sources VALUES (?,?,?,?,?)", [(s["id"], s["name"], s["type"], s["url"], s.get("api_key","")) for s in sources])
        await db.commit()
async def get_sources():
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        async with db.execute("SELECT * FROM sources") as cursor:
            return [dict(r) for r in await cursor.fetchall()]
async def upsert_recording(data):
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute("INSERT OR REPLACE INTO recordings VALUES (?,?,?,?,?,?,?,?,?,?,?)", (data["id"], data["source_id"], data.get("dataset_repo",""), data["original_name"], data["md5_name"], data["status"], data["start_time"], data.get("end_time",0), data.get("file_path",""), data.get("remote_url",""), data.get("remote_id","")))
        await db.commit()
async def get_recording(rec_id):
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        async with db.execute("SELECT * FROM recordings WHERE id=?", (rec_id,)) as cursor:
            row = await cursor.fetchone()
            return dict(row) if row else None
async def list_recordings():
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        async with db.execute("SELECT * FROM recordings ORDER BY start_time DESC") as cursor:
            return [dict(r) for r in await cursor.fetchall()]
async def cache_epg(source_id, data, timestamp):
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute("INSERT OR REPLACE INTO epg_cache VALUES (?,?,?,?)", (source_id, source_id, data, timestamp))
        await db.commit()