dewey-oracle/app/models.py

from sqlalchemy import Column, String, Float, DateTime, Integer, Text, Boolean, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from datetime import datetime

Base = declarative_base()


class User(Base):
    """User account with Audiobookshelf credentials."""
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String, unique=True, nullable=False, index=True)
    email = Column(String, unique=True, nullable=False, index=True)
    hashed_password = Column(String, nullable=False)

    # Per-user Audiobookshelf credentials
    abs_url = Column(String, nullable=False)
    abs_api_token = Column(String, nullable=False)  # Encrypted with Fernet

    # Profile information
    display_name = Column(String)
    created_at = Column(DateTime, default=func.now())
    last_login = Column(DateTime)
    is_active = Column(Boolean, default=True)
    is_admin = Column(Boolean, default=False)

    # Relationships
    listening_sessions = relationship("ListeningSession", back_populates="user", cascade="all, delete-orphan")
    recommendations = relationship("Recommendation", back_populates="user", cascade="all, delete-orphan")


class Book(Base):
    """Book information from Audiobookshelf."""
    __tablename__ = "books"

    id = Column(String, primary_key=True)  # Audiobookshelf book ID
    title = Column(String, nullable=False)
    author = Column(String)
    narrator = Column(String)
    description = Column(Text)
    genres = Column(String)  # JSON string of genres
    tags = Column(String)  # JSON string of tags
    duration = Column(Float)  # Duration in seconds
    cover_url = Column(String)

    created_at = Column(DateTime, default=func.now())
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now())


class ListeningSession(Base):
    """User listening sessions and progress."""
    __tablename__ = "listening_sessions"

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
    book_id = Column(String, nullable=False)

    # Progress tracking
    progress = Column(Float, default=0.0)  # 0.0 to 1.0
    current_time = Column(Float, default=0.0)  # Current position in seconds
    is_finished = Column(Boolean, default=False)

    # Timestamps
    started_at = Column(DateTime)
    finished_at = Column(DateTime, nullable=True)
    last_update = Column(DateTime, default=func.now(), onupdate=func.now())

    # Ratings and preferences
    rating = Column(Integer, nullable=True)  # 1-5 stars, optional

    # Relationships
    user = relationship("User", back_populates="listening_sessions")


class Recommendation(Base):
    """AI-generated book recommendations."""
    __tablename__ = "recommendations"

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)

    # Recommendation details
    title = Column(String, nullable=False)
    author = Column(String)
    description = Column(Text)
    reason = Column(Text)  # Why this book was recommended

    # Metadata
    genres = Column(String)  # JSON string
    created_at = Column(DateTime, default=func.now())
    dismissed = Column(Boolean, default=False)

    # Relationships
    user = relationship("User", back_populates="recommendations")


class AppSettings(Base):
    """Application-wide settings."""
    __tablename__ = "app_settings"

    id = Column(Integer, primary_key=True, autoincrement=True)
    key = Column(String, unique=True, nullable=False, index=True)
    value = Column(String, nullable=False)
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now())