"""
SQLAlchemy model for Reddit posts.

This module defines database models for storing posts and comments
collected from Reddit API.
"""

from datetime import datetime
from sqlalchemy import Column, Integer, String, DateTime, Index, Text, ForeignKey
from sqlalchemy.orm import relationship

from app.database import Base


class RedditPost(Base):
    """
    Model for storing Reddit posts about football matches.
    
    Attributes:
        id: Primary key
        post_id: Unique identifier from Reddit
        title: Post title
        text: Post content
        upvotes: Number of upvotes
        created_at: Timestamp when post was created
        match_id: Foreign key to matches table
        subreddit: Subreddit name
        source: Source platform (reddit)
    """
    __tablename__ = "posts_reddit"
    
    id = Column(Integer, primary_key=True, index=True)
    post_id = Column(String(255), unique=True, nullable=False, index=True)
    title = Column(String(500), nullable=False)
    text = Column(Text, nullable=True)
    upvotes = Column(Integer, default=0)
    created_at = Column(DateTime, nullable=False, index=True)
    match_id = Column(Integer, ForeignKey('matches.id'), nullable=True, index=True)
    subreddit = Column(String(100), nullable=False)
    source = Column(String(50), default="reddit")
    
    # Indexes for performance
    __table_args__ = (
        Index('idx_posts_reddit_match_id', 'match_id'),
        Index('idx_posts_reddit_created_at', 'created_at'),
        Index('idx_posts_reddit_subreddit', 'subreddit'),
    )
    
    # Relationship with match
    match = relationship("Match", back_populates="posts_reddit")
    
    # Relationship with comments
    comments = relationship("RedditComment", back_populates="post", cascade="all, delete-orphan")
    
    def __repr__(self) -> str:
        return f"<RedditPost(id={self.id}, post_id={self.post_id}, subreddit={self.subreddit})>"
    
    def to_dict(self) -> dict:
        """Convert Reddit post model to dictionary."""
        return {
            'id': self.id,
            'post_id': self.post_id,
            'title': self.title,
            'text': self.text,
            'upvotes': self.upvotes,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'match_id': self.match_id,
            'subreddit': self.subreddit,
            'source': self.source
        }


class RedditComment(Base):
    """
    Model for storing Reddit comments.
    
    Attributes:
        id: Primary key
        comment_id: Unique identifier from Reddit
        post_id: Foreign key to posts_reddit table
        text: Comment content
        upvotes: Number of upvotes
        created_at: Timestamp when comment was created
        source: Source platform (reddit)
    """
    __tablename__ = "comments_reddit"
    
    id = Column(Integer, primary_key=True, index=True)
    comment_id = Column(String(255), unique=True, nullable=False, index=True)
    post_id = Column(Integer, ForeignKey('posts_reddit.id'), nullable=False, index=True)
    text = Column(Text, nullable=False)
    upvotes = Column(Integer, default=0)
    created_at = Column(DateTime, nullable=False, index=True)
    source = Column(String(50), default="reddit")
    
    # Indexes for performance
    __table_args__ = (
        Index('idx_comments_reddit_post_id', 'post_id'),
        Index('idx_comments_reddit_created_at', 'created_at'),
    )
    
    # Relationship with post
    post = relationship("RedditPost", back_populates="comments")
    
    def __repr__(self) -> str:
        return f"<RedditComment(id={self.id}, comment_id={self.comment_id}, post_id={self.post_id})>"
    
    def to_dict(self) -> dict:
        """Convert Reddit comment model to dictionary."""
        return {
            'id': self.id,
            'comment_id': self.comment_id,
            'post_id': self.post_id,
            'text': self.text,
            'upvotes': self.upvotes,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'source': self.source
        }