#!/usr/bin/env python3
"""
MTG Card Search Script

Searches for Magic: The Gathering cards in the mtgdata database.
Supports searching by card name, partial name, and filters by set.
"""
import sys
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker


def create_connection(db_url: str):
    """Create database connection."""
    engine = create_engine(db_url)
    session_factory = sessionmaker(bind=engine)
    return engine, session_factory


def search_cards_by_name(session, card_name: str, set_code: str = None, limit: int = 10) -> list:
    """
    Search for cards by name.
    
    Args:
        session: Database session
        card_name: Card name to search for (partial matches supported)
        set_code: Optional set code to filter by
        limit: Maximum number of results
        
    Returns:
        List of card dictionaries
    """
    # Build query based on whether set_code is provided
    if set_code:
        query = """
            SELECT 
                c.id,
                c.name,
                c.type_line,
                c.mana_cost,
                c.oracle_text,
                c.power,
                c.toughness,
                c.rarity,
                c.layout,
                c.artist,
                c.flavor_text,
                s.code as set_code,
                s.name as set_name,
                s.release_date as set_release_date
            FROM mtg_cards c
            JOIN mtg_sets s ON c.set_id = s.id
            WHERE c.name LIKE :name
                AND s.code = :set_code
            ORDER BY c.name
            LIMIT :limit
        """
        params = {
            "name": f"%{card_name}%",
            "set_code": set_code.upper(),
            "limit": limit
        }
    else:
        query = """
            SELECT 
                c.id,
                c.name,
                c.type_line,
                c.mana_cost,
                c.oracle_text,
                c.power,
                c.toughness,
                c.rarity,
                c.layout,
                c.artist,
                c.flavor_text,
                s.code as set_code,
                s.name as set_name,
                s.release_date as set_release_date
            FROM mtg_cards c
            JOIN mtg_sets s ON c.set_id = s.id
            WHERE c.name LIKE :name
            ORDER BY c.name
            LIMIT :limit
        """
        params = {
            "name": f"%{card_name}%",
            "limit": limit
        }
    
    result = session.execute(text(query), params)
    
    cards = []
    for row in result:
        card = {
            "id": row[0],
            "name": row[1],
            "type_line": row[2],
            "mana_cost": row[3],
            "oracle_text": row[4],
            "power": row[5],
            "toughness": row[6],
            "rarity": row[7],
            "layout": row[8],
            "artist": row[9],
            "flavor_text": row[10],
            "set_code": row[11],
            "set_name": row[12],
            "set_release_date": row[13]
        }
        cards.append(card)
    
    return cards


def search_cards_by_set(session, set_code: str, limit: int = 100) -> list:
    """
    Get all cards from a specific set.
    
    Args:
        session: Database session
        set_code: Set code (e.g., '10E', '2ED')
        limit: Maximum number of cards to return
        
    Returns:
        List of card dictionaries
    """
    query = """
        SELECT 
            c.id,
            c.name,
            c.type_line,
            c.mana_cost,
            c.oracle_text,
            c.power,
            c.toughness,
            c.rarity,
            c.layout,
            c.artist,
            c.flavor_text,
            s.code as set_code,
            s.name as set_name,
            s.release_date as set_release_date
        FROM mtg_cards c
        JOIN mtg_sets s ON c.set_id = s.id
        WHERE s.code = :set_code
        ORDER BY c.name
        LIMIT :limit
    """
    
    params = {
        "set_code": set_code.upper(),
        "limit": limit
    }
    
    result = session.execute(text(query), params)
    
    cards = []
    for row in result:
        card = {
            "id": row[0],
            "name": row[1],
            "type_line": row[2],
            "mana_cost": row[3],
            "oracle_text": row[4],
            "power": row[5],
            "toughness": row[6],
            "rarity": row[7],
            "layout": row[8],
            "artist": row[9],
            "flavor_text": row[10],
            "set_code": row[11],
            "set_name": row[12],
            "set_release_date": row[13]
        }
        cards.append(card)
    
    return cards


def get_card_count(session, card_name: str = None, set_code: str = None) -> int:
    """
    Get count of cards matching search criteria.
    
    Args:
        session: Database session
        card_name: Optional card name filter
        set_code: Optional set code filter
        
    Returns:
        Number of matching cards
    """
    if card_name and set_code:
        query = """
            SELECT COUNT(*)
            FROM mtg_cards c
            JOIN mtg_sets s ON c.set_id = s.id
            WHERE c.name LIKE :name
                AND s.code = :set_code
        """
        params = {
            "name": f"%{card_name}%",
            "set_code": set_code.upper()
        }
    elif card_name:
        query = """
            SELECT COUNT(*)
            FROM mtg_cards
            WHERE name LIKE :name
        """
        params = {
            "name": f"%{card_name}%"
        }
    elif set_code:
        query = """
            SELECT COUNT(*)
            FROM mtg_cards
            WHERE set_id = (SELECT id FROM mtg_sets WHERE code = :set_code)
        """
        params = {
            "set_code": set_code.upper()
        }
    else:
        query = "SELECT COUNT(*) FROM mtg_cards"
        params = {}
    
    result = session.execute(text(query), params)
    return result.fetchone()[0]


def format_card(card: dict) -> str:
    """Format a card dictionary for display."""
    lines = [
        f"Name: {card['name']}",
        f"Type: {card['type_line'] or 'Unknown'}",
        f"Mana Cost: {card['mana_cost'] or 'N/A'}",
        f"Rarity: {card['rarity'] or 'N/A'}",
        f"Set: {card['set_code']} - {card['set_name']}",
    ]
    
    if card['oracle_text']:
        lines.append(f"Oracle Text: {card['oracle_text']}")
    
    if card['power'] and card['toughness']:
        lines.append(f"Power/Toughness: {card['power']}/{card['toughness']}")
    
    if card['artist']:
        lines.append(f"Artist: {card['artist']}")
    
    return "\n".join(lines)


def main():
    """Main entry point."""
    # Database connection
    db_url = "postgresql+psycopg2://mtgonline:mtgonline_pass@172.18.0.2:5432/mtgdata"
    engine, session_factory = create_connection(db_url)
    session = session_factory()
    
    try:
        # Get search parameters from command line
        if len(sys.argv) < 2:
            print("Usage: python search_cards.py <card_name> [set_code]")
            print("       python search_cards.py --set <set_code>")
            print("       python search_cards.py --count [card_name] [set_code]")
            print("\nExamples:")
            print("  python search_cards.py Lightning Bolt")
            print("  python search_cards.py Lightning Bolt 10E")
            print("  python search_cards.py --set 10E")
            print("  python search_cards.py --count Lightning")
            print("  python search_cards.py --count Lightning 10E")
            return
        
        # Parse arguments
        if sys.argv[1] == "--set":
            if len(sys.argv) < 3:
                print("Error: --set requires a set code")
                return
            set_code = sys.argv[2]
            print(f"Searching cards in set: {set_code}")
            cards = search_cards_by_set(session, set_code)
            print(f"\nFound {len(cards)} cards:\n")
            for card in cards:
                print(format_card(card))
                print("-" * 60)
        
        elif sys.argv[1] == "--count":
            card_name = sys.argv[2] if len(sys.argv) > 2 else None
            set_code = sys.argv[3] if len(sys.argv) > 3 else None
            
            count = get_card_count(session, card_name, set_code)
            print(f"Total cards matching criteria: {count}")
        
        else:
            card_name = sys.argv[1]
            set_code = sys.argv[2] if len(sys.argv) > 2 else None
            
            if set_code:
                print(f"Searching for '{card_name}' in set {set_code}")
            else:
                print(f"Searching for '{card_name}'")
            
            cards = search_cards_by_name(session, card_name, set_code)
            
            if not cards:
                print("No cards found.")
                return
            
            print(f"\nFound {len(cards)} card(s):\n")
            for card in cards:
                print(format_card(card))
                print("-" * 60)
    
    finally:
        session.close()
        engine.dispose()


if __name__ == "__main__":
    main()