Mediatracker/backend/database.py

import logging
from datetime import datetime
from typing import Optional

from sqlalchemy import (
    create_engine,
    text,
    Column, Integer, SmallInteger, BigInteger, String, Text,
    DateTime, Enum, ForeignKey, UniqueConstraint, Index, and_,
)
from sqlalchemy.orm import (
    DeclarativeBase,
    relationship,
    Session,
    sessionmaker,
)


logger = logging.getLogger(__name__)


class Base(DeclarativeBase):
    pass


# ─────────────────────────────────────────
# ORM models
# ─────────────────────────────────────────

class NasSource(Base):
    __tablename__ = "nas_sources"

    id        = Column(Integer, primary_key=True, autoincrement=True)
    name      = Column(String(255), nullable=False)
    path      = Column(String(1024), nullable=False)
    last_scan = Column(DateTime, nullable=True)

    movies   = relationship("Movie",   back_populates="nas", cascade="all, delete-orphan")
    seasons  = relationship("Season",  back_populates="nas", cascade="all, delete-orphan")
    unmatched = relationship("UnmatchedFile", back_populates="nas", cascade="all, delete-orphan")


class Movie(Base):
    __tablename__ = "movies"
    __table_args__ = (
        UniqueConstraint("file_path", name="uq_movie_path"),
        Index("idx_movie_title", "title"),
    )

    id         = Column(Integer, primary_key=True, autoincrement=True)
    title      = Column(String(512), nullable=False)
    year       = Column(SmallInteger, nullable=True)
    quality    = Column(String(64), nullable=True)
    codec      = Column(String(64), nullable=True)
    file_path  = Column(String(2048), nullable=False)
    file_size  = Column(BigInteger, nullable=True)
    poster_url = Column(String(512), nullable=True)
    imdb_id    = Column(String(20), nullable=True)
    nas_id     = Column(Integer, ForeignKey("nas_sources.id", ondelete="CASCADE"), nullable=False)
    added_at   = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)

    nas = relationship("NasSource", back_populates="movies")


class Series(Base):
    __tablename__ = "series"
    __table_args__ = (
        UniqueConstraint("title_normalized", name="uq_series_normalized"),
        Index("idx_series_title", "title"),
    )

    id               = Column(Integer, primary_key=True, autoincrement=True)
    title            = Column(String(512), nullable=False)
    title_normalized = Column(String(512), nullable=False)
    year             = Column(SmallInteger, nullable=True)
    poster_url       = Column(String(512), nullable=True)
    imdb_id          = Column(String(20), nullable=True)
    added_at         = Column(DateTime, default=datetime.utcnow, nullable=False)

    seasons = relationship("Season", back_populates="series", cascade="all, delete-orphan")


class Season(Base):
    __tablename__ = "seasons"
    __table_args__ = (
        UniqueConstraint("series_id", "season_number", "nas_id", name="uq_season"),
    )

    id            = Column(Integer, primary_key=True, autoincrement=True)
    series_id     = Column(Integer, ForeignKey("series.id", ondelete="CASCADE"), nullable=False)
    season_number = Column(SmallInteger, nullable=False)
    nas_id        = Column(Integer, ForeignKey("nas_sources.id", ondelete="CASCADE"), nullable=False)

    series   = relationship("Series", back_populates="seasons")
    nas      = relationship("NasSource", back_populates="seasons")
    episodes = relationship("Episode", back_populates="season", cascade="all, delete-orphan")


class Episode(Base):
    __tablename__ = "episodes"
    __table_args__ = (
        UniqueConstraint("file_path", name="uq_episode_path"),
    )

    id             = Column(Integer, primary_key=True, autoincrement=True)
    season_id      = Column(Integer, ForeignKey("seasons.id", ondelete="CASCADE"), nullable=False)
    episode_number = Column(SmallInteger, nullable=False)
    title          = Column(String(512), nullable=True)
    quality        = Column(String(64), nullable=True)
    codec          = Column(String(64), nullable=True)
    file_path      = Column(String(2048), nullable=False)
    file_size      = Column(BigInteger, nullable=True)
    added_at       = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at     = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)

    season = relationship("Season", back_populates="episodes")


class UnmatchedFile(Base):
    __tablename__ = "unmatched_files"

    id         = Column(Integer, primary_key=True, autoincrement=True)
    file_path  = Column(String(2048), nullable=False)
    nas_id     = Column(Integer, ForeignKey("nas_sources.id", ondelete="CASCADE"), nullable=False)
    reason     = Column(String(512), nullable=False)
    scanned_at = Column(DateTime, default=datetime.utcnow, nullable=False)

    nas = relationship("NasSource", back_populates="unmatched")


class ScanHistory(Base):
    __tablename__ = "scan_history"

    id              = Column(Integer, primary_key=True, autoincrement=True)
    scan_type       = Column(Enum("full", "incremental"), nullable=False)
    status          = Column(Enum("running", "completed", "failed"), nullable=False, default="running")
    started_at      = Column(DateTime, default=datetime.utcnow, nullable=False)
    finished_at     = Column(DateTime, nullable=True)
    files_found     = Column(Integer, nullable=False, default=0)
    files_added     = Column(Integer, nullable=False, default=0)
    files_removed   = Column(Integer, nullable=False, default=0)
    files_unmatched = Column(Integer, nullable=False, default=0)
    error_message   = Column(Text, nullable=True)


class AltTitle(Base):
    __tablename__ = "alt_titles"
    __table_args__ = (
        UniqueConstraint("media_type", "media_id", "lang", "title", name="uq_alt_title"),
        Index("idx_alt_title_search", "title"),
    )

    id         = Column(Integer, primary_key=True, autoincrement=True)
    media_type = Column(Enum("movie", "series"), nullable=False)
    media_id   = Column(Integer, nullable=False)
    title      = Column(String(512), nullable=False)
    lang       = Column(String(10), nullable=False)


# ─────────────────────────────────────────
# Engine / session factory
# ─────────────────────────────────────────

_engine = None
_SessionLocal = None


def init_db(db_url: str) -> None:
    global _engine, _SessionLocal
    _engine = create_engine(
        db_url,
        pool_pre_ping=True,
        pool_recycle=3600,
        connect_args={"connect_timeout": 10},
    )
    _SessionLocal = sessionmaker(bind=_engine, autocommit=False, autoflush=False)
    logger.info("Database engine initialized")


def get_session() -> Session:
    if _SessionLocal is None:
        raise RuntimeError("Database not initialized — call init_db() first")
    return _SessionLocal()


def check_connection() -> bool:
    try:
        with _engine.connect() as conn:
            conn.execute(text("SELECT 1"))
        return True
    except Exception as exc:
        logger.error("Database connection check failed: %s", exc)
        return False