src/db/models.py

"""
SQLModel database models for PostgreSQL (Supabase).
Optimized for cloud deployment and mobile app integration.
"""

from datetime import datetime
from typing import Optional, List
from sqlmodel import SQLModel, Field, Relationship


class User(SQLModel, table=True):
    """
    User model for authentication and note ownership.
    
    Attributes:
        id: Primary key, auto-incremented
        email: Unique email address for login
        username: Display name for the user
        password_hash: Bcrypt hashed password
        role: User role (default: "user")
        created_at: Account creation timestamp
        notes: Relationship to user's notes
    """
    __tablename__ = "users"
    
    id: Optional[int] = Field(default=None, primary_key=True)
    email: str = Field(unique=True, index=True, max_length=255, nullable=False)
    username: str = Field(max_length=100, nullable=False)
    password_hash: str = Field(max_length=255, nullable=False)
    role: str = Field(default="user", max_length=50, nullable=False)
    created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
    
    # Relationship to notes
    notes: List["Note"] = Relationship(back_populates="owner")
    
    class Config:
        """Pydantic configuration."""
        json_schema_extra = {
            "example": {
                "email": "student@example.com",
                "username": "Student123",
                "role": "user"
            }
        }


class Note(SQLModel, table=True):
    """
    Note model for storing generated study notes.
    
    Attributes:
        id: Primary key, auto-incremented
        user_id: Foreign key to the user who generated this note
        video_url: YouTube video URL
        video_title: Title of the processed video
        summary_content: Generated study notes content (markdown)
        created_at: Note generation timestamp
        owner: Relationship to the user who owns this note
    """
    __tablename__ = "notes"
    
    id: Optional[int] = Field(default=None, primary_key=True)
    user_id: int = Field(foreign_key="users.id", index=True, nullable=False)
    video_url: str = Field(index=True, max_length=500, nullable=False)
    video_title: str = Field(max_length=500, nullable=False)
    summary_content: str = Field(nullable=False)  # Full markdown content
    created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)
    
    # Relationship to user
    owner: Optional[User] = Relationship(back_populates="notes")
    
    class Config:
        """Pydantic configuration."""
        json_schema_extra = {
            "example": {
                "user_id": 1,
                "video_url": "https://www.youtube.com/watch?v=dQw4w9WgXcQ",
                "video_title": "Introduction to Python Programming",
                "summary_content": "# Study Notes\\n\\n## Key Concepts..."
            }
        }