models.py

from datetime import datetime
from typing import Optional
from flask_login import UserMixin
from sqlalchemy import Integer, String, DateTime, Text, ForeignKey, Numeric,Float
from sqlalchemy.orm import Mapped, mapped_column, relationship,DeclarativeBase

class Base(DeclarativeBase):
    pass

class User(Base,UserMixin):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    username: Mapped[str] = mapped_column(String(100), unique=True, nullable=False)
    email: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)
    password: Mapped[str] = mapped_column(String(255), nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.now)

    history= relationship("ReadingHistory",back_populates="user", cascade="all, delete-orphan")
    preferences= relationship("UserPreferences",back_populates="user", uselist=False, cascade="all, delete-orphan")


class Book(Base,UserMixin):
    __tablename__ = "books"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    title: Mapped[str] = mapped_column(String(255), nullable=False)
    author: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    genre: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    description: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    published_date: Mapped[datetime] = mapped_column(DateTime, default=datetime.now)
    match_score: Mapped[float] = mapped_column(Float)
    history= relationship("ReadingHistory",back_populates="book", cascade="all, delete-orphan")


class ReadingHistory(Base,UserMixin):
    __tablename__ = "reading_history"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), nullable=False)
    book_id: Mapped[int] = mapped_column(ForeignKey("books.id"), nullable=False)
    progress: Mapped[float]= mapped_column(Numeric(5, 2), default=0.00)
    last_accessed: Mapped[datetime] = mapped_column(DateTime, default=datetime.now)

    user= relationship("User",back_populates="history")
    book= relationship("Book",back_populates="history")


class UserPreferences(Base,UserMixin):
    __tablename__ = "user_preferences"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), unique=True, nullable=False)
    favourite_genres: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    favourite_authors: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    last_updated: Mapped[datetime] = mapped_column(DateTime, default=datetime.now, onupdate=datetime.now)

    user= relationship("User",back_populates="preferences")