models/db_models.py

from sqlalchemy import Column, Integer, String, Boolean, DateTime, ForeignKey, Float, Text, JSON, Unicode, UnicodeText
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from core.database import Base

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(Unicode(255), unique=True, index=True, nullable=False)
    supabase_id = Column(String(255), unique=True, index=True, nullable=True) # Linked Supabase UID
    hashed_password = Column(String(255), nullable=True) # Set to nullable since Supabase handles auth
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    sources = relationship("Source", back_populates="owner")
    podcasts = relationship("Podcast", back_populates="owner")
    flashcard_sets = relationship("FlashcardSet", back_populates="owner")
    mind_maps = relationship("MindMap", back_populates="owner")
    quiz_sets = relationship("QuizSet", back_populates="owner")
    reports = relationship("Report", back_populates="owner")
    video_summaries = relationship("VideoSummary", back_populates="owner")
    rag_documents = relationship("RAGDocument", back_populates="owner")
    canvases = relationship("Canvas", back_populates="owner")
    chat_messages = relationship("ChatMessage", back_populates="owner", cascade="all, delete-orphan")

class Source(Base):
    __tablename__ = "sources"

    id = Column(Integer, primary_key=True, index=True)
    filename = Column(Unicode(255), nullable=False)
    s3_key = Column(String(512), nullable=False)
    s3_url = Column(String(1024), nullable=False)
    size = Column(Integer)
    user_id = Column(Integer, ForeignKey("users.id"))
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    owner = relationship("User", back_populates="sources")
    podcasts = relationship("Podcast", back_populates="source")
    flashcard_sets = relationship("FlashcardSet", back_populates="source")
    mind_maps = relationship("MindMap", back_populates="source")
    quiz_sets = relationship("QuizSet", back_populates="source")
    reports = relationship("Report", back_populates="source")
    video_summaries = relationship("VideoSummary", back_populates="source")
    rag_documents = relationship("RAGDocument", back_populates="source")
    canvases = relationship("Canvas", back_populates="source")

class Podcast(Base):
    __tablename__ = "podcasts"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(Unicode(255))
    s3_key = Column(String(512), nullable=True)
    s3_url = Column(String(1024), nullable=True)
    script = Column(UnicodeText, nullable=True)
    status = Column(String(50), default="processing") # pending, processing, completed, failed
    error_message = Column(UnicodeText, nullable=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    source_id = Column(Integer, ForeignKey("sources.id"), nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    owner = relationship("User", back_populates="podcasts")
    source = relationship("Source", back_populates="podcasts")

    @property
    def parent_file_key(self):
        return self.source.s3_key if self.source else None

    @property
    def public_url(self):
        return self.s3_url

    @property
    def private_url(self):
        from services.s3_service import s3_service
        return s3_service.get_presigned_url(self.s3_key) if self.s3_key else None


class FlashcardSet(Base):
    __tablename__ = "flashcard_sets"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(Unicode(255))
    difficulty = Column(String(50))
    user_id = Column(Integer, ForeignKey("users.id"))
    source_id = Column(Integer, ForeignKey("sources.id"), nullable=True)
    status = Column(String(50), default="processing")
    error_message = Column(UnicodeText, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    owner = relationship("User", back_populates="flashcard_sets")
    source = relationship("Source", back_populates="flashcard_sets")
    flashcards = relationship("Flashcard", back_populates="flashcard_set", cascade="all, delete-orphan")

    @property
    def parent_file_key(self):
        return self.source.s3_key if self.source else None

class MindMap(Base):
    __tablename__ = "mind_maps"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(Unicode(255))
    mermaid_code = Column(UnicodeText, nullable=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    source_id = Column(Integer, ForeignKey("sources.id"), nullable=True)
    status = Column(String(50), default="processing")
    error_message = Column(UnicodeText, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    owner = relationship("User", back_populates="mind_maps")
    source = relationship("Source", back_populates="mind_maps")

    @property
    def parent_file_key(self):
        return self.source.s3_key if self.source else None

class QuizSet(Base):
    __tablename__ = "quiz_sets"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(Unicode(255))
    difficulty = Column(String(50))
    user_id = Column(Integer, ForeignKey("users.id"))
    source_id = Column(Integer, ForeignKey("sources.id"), nullable=True)
    status = Column(String(50), default="processing")
    error_message = Column(UnicodeText, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    owner = relationship("User", back_populates="quiz_sets")
    source = relationship("Source", back_populates="quiz_sets")
    questions = relationship("QuizQuestion", back_populates="quiz_set", cascade="all, delete-orphan")

    @property
    def parent_file_key(self):
        return self.source.s3_key if self.source else None

class QuizQuestion(Base):
    __tablename__ = "quiz_questions"

    id = Column(Integer, primary_key=True, index=True)
    quiz_set_id = Column(Integer, ForeignKey("quiz_sets.id"))
    question = Column(UnicodeText, nullable=False)
    hint = Column(UnicodeText)
    choices = Column(JSON, nullable=False) # Store choices as a JSON object {"1": "...", "2": "...", ...}
    answer = Column(String(10), nullable=False) # Storing "1", "2", "3", or "4"
    explanation = Column(UnicodeText)

    quiz_set = relationship("QuizSet", back_populates="questions")

class Report(Base):
    __tablename__ = "reports"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(Unicode(255))
    content = Column(UnicodeText, nullable=True)
    format_key = Column(String(100))
    user_id = Column(Integer, ForeignKey("users.id"))
    source_id = Column(Integer, ForeignKey("sources.id"), nullable=True)
    status = Column(String(50), default="processing")
    error_message = Column(UnicodeText, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    owner = relationship("User", back_populates="reports")
    source = relationship("Source", back_populates="reports")

    @property
    def parent_file_key(self):
        return self.source.s3_key if self.source else None

class VideoSummary(Base):
    __tablename__ = "video_summaries"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(Unicode(255))
    s3_key = Column(String(512), nullable=True)
    s3_url = Column(String(1024), nullable=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    source_id = Column(Integer, ForeignKey("sources.id"), nullable=True)
    status = Column(String(50), default="processing")
    error_message = Column(UnicodeText, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    owner = relationship("User", back_populates="video_summaries")
    source = relationship("Source", back_populates="video_summaries")

    @property
    def parent_file_key(self):
        return self.source.s3_key if self.source else None

    @property
    def public_url(self):
        return self.s3_url

    @property
    def private_url(self):
        from services.s3_service import s3_service
        return s3_service.get_presigned_url(self.s3_key) if self.s3_key else None

class Flashcard(Base):
    __tablename__ = "flashcards"

    id = Column(Integer, primary_key=True, index=True)
    flashcard_set_id = Column(Integer, ForeignKey("flashcard_sets.id"))
    question = Column(UnicodeText, nullable=False)
    answer = Column(UnicodeText, nullable=False)

    flashcard_set = relationship("FlashcardSet", back_populates="flashcards")

class RAGDocument(Base):
    __tablename__ = "rag_documents"

    id = Column(Integer, primary_key=True, index=True)
    filename = Column(Unicode(255), nullable=False)
    azure_doc_id = Column(String(255), unique=True, index=True)
    chunk_count = Column(Integer, default=0)
    user_id = Column(Integer, ForeignKey("users.id"))
    source_id = Column(Integer, ForeignKey("sources.id"), nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    owner = relationship("User", back_populates="rag_documents")
    source = relationship("Source", back_populates="rag_documents")

class ChatMessage(Base):
    __tablename__ = "chat_messages"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    rag_doc_id = Column(Integer, ForeignKey("rag_documents.id"), nullable=True)
    role = Column(String(50)) # 'user' or 'assistant'
    content = Column(UnicodeText, nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    owner = relationship("User", back_populates="chat_messages")

class Canvas(Base) :
    __tablename__ = "canvases"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(Unicode(255))
    text = Column(UnicodeText, nullable=True) # Markdown text
    user_id = Column(Integer, ForeignKey("users.id"))
    source_id = Column(Integer, ForeignKey("sources.id"), nullable=True)
    status = Column(String(50), default="processing")
    error_message = Column(UnicodeText, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    owner = relationship("User", back_populates="canvases")
    source = relationship("Source", back_populates="canvases")

    @property
    def parent_file_key(self):
        return self.source.s3_key if self.source else None