from __future__ import annotations import sqlite3 from pathlib import Path from typing import Any from iris.models.schemas import VideoStatus from iris.pipeline.chunk import TranscriptChunk class Database: def __init__(self, path: Path) -> None: self.path = path def init(self) -> None: self.path.parent.mkdir(parents=True, exist_ok=True) with self._connect() as connection: connection.executescript( """ PRAGMA journal_mode=WAL; CREATE TABLE IF NOT EXISTS videos ( id TEXT PRIMARY KEY, original_filename TEXT NOT NULL, video_path TEXT NOT NULL, audio_path TEXT, transcript_path TEXT, embeddings_path TEXT, status TEXT NOT NULL, error TEXT, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS transcript_chunks ( id INTEGER PRIMARY KEY AUTOINCREMENT, video_id TEXT NOT NULL, start REAL NOT NULL, end REAL NOT NULL, text TEXT NOT NULL, FOREIGN KEY(video_id) REFERENCES videos(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS searches ( id INTEGER PRIMARY KEY AUTOINCREMENT, video_id TEXT NOT NULL, query TEXT NOT NULL, include_visual INTEGER NOT NULL, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(video_id) REFERENCES videos(id) ON DELETE CASCADE ); """ ) def create_video(self, video_id: str, original_filename: str, video_path: Path) -> dict[str, Any]: with self._connect() as connection: connection.execute( """ INSERT INTO videos (id, original_filename, video_path, status) VALUES (?, ?, ?, 'queued') """, (video_id, original_filename, str(video_path)), ) return self.get_video(video_id) or {} def get_video(self, video_id: str) -> dict[str, Any] | None: with self._connect() as connection: row = connection.execute("SELECT * FROM videos WHERE id = ?", (video_id,)).fetchone() return dict(row) if row else None def update_video_status(self, video_id: str, status: VideoStatus, error: str | None = None) -> None: with self._connect() as connection: connection.execute( """ UPDATE videos SET status = ?, error = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ? """, (status, error, video_id), ) def set_artifact_paths( self, video_id: str, audio_path: Path | None = None, transcript_path: Path | None = None, embeddings_path: Path | None = None, ) -> None: with self._connect() as connection: connection.execute( """ UPDATE videos SET audio_path = COALESCE(?, audio_path), transcript_path = COALESCE(?, transcript_path), embeddings_path = COALESCE(?, embeddings_path), updated_at = CURRENT_TIMESTAMP WHERE id = ? """, ( str(audio_path) if audio_path else None, str(transcript_path) if transcript_path else None, str(embeddings_path) if embeddings_path else None, video_id, ), ) def replace_transcript_chunks(self, video_id: str, chunks: list[TranscriptChunk]) -> None: with self._connect() as connection: connection.execute("DELETE FROM transcript_chunks WHERE video_id = ?", (video_id,)) connection.executemany( """ INSERT INTO transcript_chunks (video_id, start, end, text) VALUES (?, ?, ?, ?) """, [(video_id, chunk.start, chunk.end, chunk.text) for chunk in chunks], ) def list_transcript_chunks(self, video_id: str) -> list[TranscriptChunk]: with self._connect() as connection: rows = connection.execute( """ SELECT start, end, text FROM transcript_chunks WHERE video_id = ? ORDER BY start, end, id """, (video_id,), ).fetchall() return [ TranscriptChunk(start=float(row["start"]), end=float(row["end"]), text=str(row["text"])) for row in rows ] def record_search(self, video_id: str, query: str, include_visual: bool) -> None: with self._connect() as connection: connection.execute( """ INSERT INTO searches (video_id, query, include_visual) VALUES (?, ?, ?) """, (video_id, query, int(include_visual)), ) def _connect(self) -> sqlite3.Connection: connection = sqlite3.connect(self.path) connection.row_factory = sqlite3.Row connection.execute("PRAGMA foreign_keys=ON;") return connection