"""
SQLAlchemy ORM models for the MTG database (mtgjson.com data).

Models mirror the mtg_cards and mtg_sets tables in the MTG PostgreSQL database.
"""
from sqlalchemy import Column, Integer, String, Text, DateTime, ForeignKey, Index
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from app.core.database import Base


class MtgSet(Base):
    """MTG Set model."""
    __tablename__ = "mtg_sets"

    id = Column(Integer, primary_key=True, index=True)
    code = Column(String(10), unique=True, nullable=False, index=True)
    name = Column(String(255), nullable=True)
    type = Column(String(100), nullable=True)
    release_date = Column(DateTime, nullable=True)
    base_set_size = Column(Integer, nullable=True)
    total_size = Column(Integer, nullable=True)
    is_foil_only = Column(Integer, nullable=True)
    is_non_foil_only = Column(Integer, nullable=True)
    digital = Column(Integer, nullable=True)
    icon_svg_url = Column(Text, nullable=True)
    parent_code = Column(String(10), nullable=True)
    mtgo_code = Column(String(10), nullable=True)
    image = Column(Text, nullable=True)  # Card image URL from MTGJSON
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    # Relationships
    cards = relationship("MtgCard", back_populates="set")

    def __repr__(self) -> str:
        return f"<MtgSet {self.code}: {self.name}>"


class MtgCard(Base):
    """MTG Card model."""
    __tablename__ = "mtg_cards"

    id = Column(Integer, primary_key=True, index=True)
    set_id = Column(Integer, ForeignKey("mtg_sets.id"), nullable=True, index=True)
    name = Column(String(255), nullable=True, index=True)
    mana_cost = Column(String(255), nullable=True, index=True)
    type_line = Column(String(255), nullable=True, index=True)
    oracle_text = Column(Text, nullable=True)
    power = Column(String(50), nullable=True)
    toughness = Column(String(50), nullable=True)
    rarity = Column(String(50), nullable=True, index=True)
    layout = Column(String(50), nullable=True)
    artist = Column(String(255), nullable=True)
    flavor_text = Column(Text, nullable=True)
    numbers = Column(String(100), nullable=True)
    identifiers = Column(Text, nullable=True)  # JSON string
    images = Column(Text, nullable=True)  # JSON string
    image = Column(Text, nullable=True)  # Card image URL from MTGJSON
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    # Relationships
    set = relationship("MtgSet", back_populates="cards")

    def __repr__(self) -> str:
        return f"<MtgCard {self.name} ({self.set_id})>"


# Indexes for performance
Index("idx_mtg_cards_name_set", MtgCard.name, MtgCard.set_id)
Index("idx_mtg_cards_type", MtgCard.type_line)
Index("idx_mtg_cards_rarity", MtgCard.rarity)