from datetime import datetime, timezone
from sqlalchemy import (
    Column,
    String,
    Integer,
    BigInteger,
    Boolean,
    DateTime,
    ForeignKey,
    Text,
    Index
)
from sqlalchemy.orm import relationship
from database.database import Base

def utc_now():
    """Helper to return current timezone-aware UTC datetime."""
    return datetime.now(timezone.utc)

class Guild(Base):
    __tablename__ = "guilds"

    guild_id = Column(BigInteger, primary_key=True)
    name = Column(String(100), nullable=False)
    created_at_db = Column(DateTime, default=utc_now)
    updated_at_db = Column(DateTime, default=utc_now, onupdate=utc_now)

    # Relationships
    channels = relationship("Channel", back_populates="guild", cascade="all, delete-orphan")
    messages = relationship("Message", back_populates="guild", cascade="all, delete-orphan")


class Channel(Base):
    __tablename__ = "channels"

    channel_id = Column(BigInteger, primary_key=True)
    guild_id = Column(BigInteger, ForeignKey("guilds.guild_id", ondelete="CASCADE"), nullable=False)
    name = Column(String(100), nullable=False)
    created_at_db = Column(DateTime, default=utc_now)
    updated_at_db = Column(DateTime, default=utc_now, onupdate=utc_now)

    # Relationships
    guild = relationship("Guild", back_populates="channels")
    messages = relationship("Message", back_populates="channel", cascade="all, delete-orphan")
    ingestion_state = relationship("IngestionState", back_populates="channel", uselist=False, cascade="all, delete-orphan")


class User(Base):
    __tablename__ = "users"

    author_id = Column(BigInteger, primary_key=True)
    name = Column(String(100), nullable=False)
    display_name = Column(String(100), nullable=True)
    discriminator = Column(String(10), nullable=True)
    avatar_url = Column(String(255), nullable=True)
    is_bot = Column(Boolean, default=False)
    created_at_db = Column(DateTime, default=utc_now)
    updated_at_db = Column(DateTime, default=utc_now, onupdate=utc_now)

    # Relationships
    messages = relationship("Message", back_populates="author")


class Message(Base):
    __tablename__ = "messages"

    message_id = Column(BigInteger, primary_key=True)
    guild_id = Column(BigInteger, ForeignKey("guilds.guild_id", ondelete="CASCADE"), nullable=True)
    channel_id = Column(BigInteger, ForeignKey("channels.channel_id", ondelete="CASCADE"), nullable=False)
    author_id = Column(BigInteger, ForeignKey("users.author_id", ondelete="RESTRICT"), nullable=False)
    content = Column(Text, nullable=False)
    created_at = Column(DateTime, nullable=False)  # Discord message creation time (UTC)
    edited_at = Column(DateTime, nullable=True)    # Discord message edit time (UTC)
    deleted_at = Column(DateTime, nullable=True)   # Soft delete timestamp (UTC)
    reply_to_message_id = Column(BigInteger, nullable=True)
    message_url = Column(String(255), nullable=True)
    is_deleted = Column(Boolean, default=False, nullable=False)
    created_at_db = Column(DateTime, default=utc_now)
    updated_at_db = Column(DateTime, default=utc_now, onupdate=utc_now)

    # Relationships
    guild = relationship("Guild", back_populates="messages")
    channel = relationship("Channel", back_populates="messages")
    author = relationship("User", back_populates="messages")
    attachments = relationship("Attachment", back_populates="message", cascade="all, delete-orphan")
    embedding = relationship("MessageEmbedding", back_populates="message", uselist=False, cascade="all, delete-orphan")

    # Indexes
    __table_args__ = (
        Index("idx_messages_guild_id", "guild_id"),
        Index("idx_messages_channel_id", "channel_id"),
        Index("idx_messages_author_id", "author_id"),
        Index("idx_messages_created_at", "created_at"),
        Index("idx_messages_reply_to_message_id", "reply_to_message_id"),
    )


class Attachment(Base):
    __tablename__ = "attachments"

    attachment_id = Column(BigInteger, primary_key=True)
    message_id = Column(BigInteger, ForeignKey("messages.message_id", ondelete="CASCADE"), nullable=False)
    filename = Column(String(255), nullable=False)
    url = Column(String(1024), nullable=False)
    content_type = Column(String(100), nullable=True)
    size = Column(Integer, nullable=False)
    created_at_db = Column(DateTime, default=utc_now)

    # Relationships
    message = relationship("Message", back_populates="attachments")


class IngestionState(Base):
    __tablename__ = "ingestion_state"

    channel_id = Column(BigInteger, ForeignKey("channels.channel_id", ondelete="CASCADE"), primary_key=True)
    last_processed_message_id = Column(BigInteger, nullable=True)
    last_processed_timestamp = Column(DateTime, nullable=True)
    last_sync_time = Column(DateTime, nullable=True)
    status = Column(String(20), default="PENDING", nullable=False)  # PENDING, SYNCING, COMPLETED, FAILED
    error = Column(Text, nullable=True)
    updated_at_db = Column(DateTime, default=utc_now, onupdate=utc_now)

    # Relationships
    channel = relationship("Channel", back_populates="ingestion_state")


class MessageEmbedding(Base):
    __tablename__ = "message_embeddings"

    message_id = Column(BigInteger, ForeignKey("messages.message_id", ondelete="CASCADE"), primary_key=True)
    embedding = Column(Text, nullable=False)  # Stored as serialized JSON list of 1024 floats
    created_at_db = Column(DateTime, default=utc_now)

    # Relationships
    message = relationship("Message", back_populates="embedding")