app/models.py

from datetime import datetime
from typing import Optional

from sqlalchemy import String, Integer, DateTime, ForeignKey
from sqlalchemy.orm import Mapped, mapped_column, relationship

from .db import Base


class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    spotify_user_id: Mapped[Optional[str]] = mapped_column(String(128), unique=True)

    moods: Mapped[list["MoodLog"]] = relationship(back_populates="user")
    recommendations: Mapped[list["RecommendationLog"]] = relationship(
        back_populates="user"
    )


class MoodLog(Base):
    __tablename__ = "mood_logs"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    user_id: Mapped[Optional[int]] = mapped_column(ForeignKey("users.id"))
    source: Mapped[str] = mapped_column(String(32))
    raw_input: Mapped[Optional[str]] = mapped_column(String(512))
    emotion_label: Mapped[str] = mapped_column(String(64))
    emotion_score: Mapped[float] = mapped_column()
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=datetime.utcnow
    )

    user: Mapped[Optional[User]] = relationship(back_populates="moods")
    recommendation: Mapped[Optional["RecommendationLog"]] = relationship(
        back_populates="mood", uselist=False
    )


class RecommendationLog(Base):
    __tablename__ = "recommendation_logs"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    user_id: Mapped[Optional[int]] = mapped_column(ForeignKey("users.id"))
    mood_id: Mapped[int] = mapped_column(ForeignKey("mood_logs.id"))
    spotify_track_id: Mapped[str] = mapped_column(String(64))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=datetime.utcnow
    )

    user: Mapped[Optional[User]] = relationship(back_populates="recommendations")
    mood: Mapped[MoodLog] = relationship(back_populates="recommendation")