"""
SQLAlchemy ORM models for per-user deck building.

These models live in the primary mtgonline database and support
the deckbuilding feature with DRAFT/FINAL status, card management,
deck precedents, and card suggestions.
"""
from sqlalchemy import (
    Column, Integer, String, BigInteger, Boolean, DateTime, Text,
    ForeignKey, UniqueConstraint, Index, Float
)
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from app.core.database import Base
from app.models.models import MtgonlineCard


class UserDeck(Base):
    """Per-user deck storage in the primary mtgonline database."""
    __tablename__ = "user_decks"

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("mtgonline_users.id", ondelete="CASCADE"), nullable=False, index=True)
    name = Column(String(255), nullable=False, index=True)
    status = Column(String(20), nullable=False, default="DRAFT", index=True)  # DRAFT or FINAL
    folder_id = Column(Integer, ForeignKey("mtgonline_decklist_folders.id"), nullable=True)
    format = Column(String(50), nullable=True, default="standard")  # Standard, Modern, etc.
    notes = Column(Text, nullable=True)
    is_precedent = Column(Boolean, default=False, index=True)  # Mark as deck precedent/template
    precedent_name = Column(String(255), nullable=True)  # Name for precedent (if applicable)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    # Relationships
    user = relationship("User", backref="user_decks")
    folder = relationship("DecklistFolder", backref="user_decks")
    cards = relationship(
        "UserDeckCard",
        back_populates="deck",
        cascade="all, delete-orphan",
        order_by="UserDeckCard.id"
    )

    def __repr__(self) -> str:
        return f"<UserDeck {self.name} (user={self.user_id}, status={self.status})>"


class UserDeckCard(Base):
    """
    Junction table for user deck cards.

    Stores individual card entries in a user's deck with quantity and zone.
    Card references point to mtg_cards.id in the mtgdata database.
    """
    __tablename__ = "user_deck_cards"

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    deck_id = Column(BigInteger, ForeignKey("user_decks.id", ondelete="CASCADE"), nullable=False, index=True)
    card_id = Column(Integer, ForeignKey("mtgonline_cards.id"), nullable=False, index=True)
    quantity = Column(Integer, nullable=False, default=1)
    zone = Column(String(20), nullable=False, default="main")  # 'main' or 'sideboard'
    position = Column(Integer, nullable=True)  # Optional ordering within zone

    __table_args__ = (
        UniqueConstraint('deck_id', 'card_id', 'zone', name='uq_deck_card_unique'),
        Index('idx_deck_cards_deck', 'deck_id'),
        Index('idx_deck_cards_card', 'card_id'),
    )

    # Relationships
    deck = relationship("UserDeck", back_populates="cards")
    card = relationship("MtgonlineCard", back_populates="deck_cards")

    def __repr__(self) -> str:
        return f"<UserDeckCard deck={self.deck_id} card={self.card_id} qty={self.quantity}>"


class DeckPrecedent(Base):
    """
    Deck precedent (template) storage.

    Precedents are pre-built deck templates that users can clone
    to start building their own decks.
    """
    __tablename__ = "deck_precedents"

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    name = Column(String(255), nullable=False, index=True)
    description = Column(Text, nullable=True)
    format = Column(String(50), nullable=True, default="standard")
    is_public = Column(Boolean, default=True, index=True)
    created_by = Column(Integer, ForeignKey("mtgonline_users.id"), nullable=True)  # None = system
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    # Relationships
    creator = relationship("User", foreign_keys=[created_by])
    cards = relationship(
        "DeckPrecedentCard",
        back_populates="precedent",
        cascade="all, delete-orphan"
    )

    def __repr__(self) -> str:
        return f"<DeckPrecedent {self.name} (format={self.format})>"


class DeckPrecedentCard(Base):
    """Junction table for deck precedent cards."""
    __tablename__ = "deck_precedent_cards"

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    precedent_id = Column(BigInteger, ForeignKey("deck_precedents.id", ondelete="CASCADE"), nullable=False, index=True)
    card_id = Column(Integer, nullable=False, index=True)
    quantity = Column(Integer, nullable=False, default=1)
    zone = Column(String(20), nullable=False, default="main")

    __table_args__ = (
        UniqueConstraint('precedent_id', 'card_id', 'zone', name='uq_precedent_card_unique'),
    )

    precedent = relationship("DeckPrecedent", back_populates="cards")

    def __repr__(self) -> str:
        return f"<DeckPrecedentCard precedent={self.precedent_id} card={self.card_id}>"


class CardSuggestion(Base):
    """
    Card suggestion storage.

    Stores suggested cards for a deck based on similarity,
    pairing patterns, or manual curation.
    """
    __tablename__ = "card_suggestions"

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    deck_id = Column(BigInteger, ForeignKey("user_decks.id", ondelete="CASCADE"), nullable=False, index=True)
    card_id = Column(Integer, nullable=False, index=True)  # Suggested card
    source_card_id = Column(Integer, nullable=True)  # Card that triggered the suggestion
    suggestion_type = Column(String(50), nullable=False, default="SIMILAR")  # SIMILAR, PAIRING, ALTERNATIVE
    confidence = Column(Float, nullable=True)  # 0.0 to 1.0
    notes = Column(Text, nullable=True)
    created_at = Column(DateTime, server_default=func.now())

    deck = relationship("UserDeck", backref="suggestions")

    __table_args__ = (
        UniqueConstraint('deck_id', 'card_id', 'source_card_id', name='uq_suggestion_unique'),
    )

    def __repr__(self) -> str:
        return f"<CardSuggestion deck={self.deck_id} card={self.card_id} type={self.suggestion_type}>"