"""
Temporary migration script to update Lineup table schema.

This script updates the lineups table to support polymorphic card_id/player_id
structure for PD and SBA leagues.

Run this once to migrate the database schema.

Author: Claude
Date: 2025-10-23
"""

import asyncio
from sqlalchemy import text
from app.database.session import AsyncSessionLocal


async def migrate_lineup_table():
    """Update lineups table schema for polymorphic support"""

    async with AsyncSessionLocal() as session:
        try:
            print("Starting lineup table migration...")

            # Step 1: Drop existing constraint (if it exists)
            print("1. Dropping existing constraints...")
            await session.execute(text("""
                ALTER TABLE lineups
                DROP CONSTRAINT IF EXISTS lineup_one_id_required;
            """))

            # Step 2: Add player_id column (nullable)
            print("2. Adding player_id column...")
            await session.execute(text("""
                ALTER TABLE lineups
                ADD COLUMN IF NOT EXISTS player_id INTEGER;
            """))

            # Step 3: Make card_id nullable
            print("3. Making card_id nullable...")
            await session.execute(text("""
                ALTER TABLE lineups
                ALTER COLUMN card_id DROP NOT NULL;
            """))

            # Step 4: Add CHECK constraint for XOR logic
            print("4. Adding XOR constraint...")
            await session.execute(text("""
                ALTER TABLE lineups
                ADD CONSTRAINT lineup_one_id_required
                CHECK ((card_id IS NOT NULL)::int + (player_id IS NOT NULL)::int = 1);
            """))

            await session.commit()
            print("✓ Migration completed successfully!")

        except Exception as e:
            await session.rollback()
            print(f"✗ Migration failed: {e}")
            raise


if __name__ == "__main__":
    asyncio.run(migrate_lineup_table())