"""Initial database schema

Revision ID: 001
Revises:
Create Date: 2025-01-27

Creates the core tables for the Paper Dynasty Real-Time Game Engine:
- games: Game container with status, scores, AI configuration
- plays: At-bat records with 30+ statistical fields
- lineups: Player assignments and substitution tracking
- game_sessions: WebSocket state tracking
- rolls: Dice roll audit trail
- roster_links: Eligible cards (PD) or players (SBA) per game
- game_cardset_links: PD league cardset configuration
"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision: str = '001'
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # === GAMES TABLE ===
    op.create_table(
        'games',
        sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('league_id', sa.String(50), nullable=False),
        sa.Column('home_team_id', sa.Integer(), nullable=False),
        sa.Column('away_team_id', sa.Integer(), nullable=False),
        sa.Column('status', sa.String(20), nullable=False, server_default='pending'),
        sa.Column('game_mode', sa.String(20), nullable=False),
        sa.Column('visibility', sa.String(20), nullable=False),
        sa.Column('current_inning', sa.Integer(), nullable=True),
        sa.Column('current_half', sa.String(10), nullable=True),
        sa.Column('home_score', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('away_score', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('home_team_is_ai', sa.Boolean(), nullable=True, server_default='false'),
        sa.Column('away_team_is_ai', sa.Boolean(), nullable=True, server_default='false'),
        sa.Column('ai_difficulty', sa.String(20), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=True),
        sa.Column('started_at', sa.DateTime(), nullable=True),
        sa.Column('completed_at', sa.DateTime(), nullable=True),
        sa.Column('winner_team_id', sa.Integer(), nullable=True),
        sa.Column('game_metadata', sa.JSON(), nullable=True),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index('ix_games_created_at', 'games', ['created_at'])
    op.create_index('ix_games_league_id', 'games', ['league_id'])
    op.create_index('ix_games_status', 'games', ['status'])

    # === LINEUPS TABLE ===
    op.create_table(
        'lineups',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('game_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('team_id', sa.Integer(), nullable=False),
        sa.Column('card_id', sa.Integer(), nullable=True),
        sa.Column('player_id', sa.Integer(), nullable=True),
        sa.Column('position', sa.String(10), nullable=False),
        sa.Column('batting_order', sa.Integer(), nullable=True),
        sa.Column('is_starter', sa.Boolean(), nullable=True, server_default='true'),
        sa.Column('is_active', sa.Boolean(), nullable=True, server_default='true'),
        sa.Column('entered_inning', sa.Integer(), nullable=True, server_default='1'),
        sa.Column('replacing_id', sa.Integer(), nullable=True),
        sa.Column('after_play', sa.Integer(), nullable=True),
        sa.Column('is_fatigued', sa.Boolean(), nullable=True),
        sa.Column('lineup_metadata', sa.JSON(), nullable=True),
        sa.ForeignKeyConstraint(['game_id'], ['games.id'], ondelete='CASCADE'),
        sa.CheckConstraint(
            '(card_id IS NOT NULL)::int + (player_id IS NOT NULL)::int = 1',
            name='lineup_one_id_required'
        ),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index('ix_lineups_game_id', 'lineups', ['game_id'])
    op.create_index('ix_lineups_is_active', 'lineups', ['is_active'])
    op.create_index('ix_lineups_team_id', 'lineups', ['team_id'])

    # === PLAYS TABLE ===
    op.create_table(
        'plays',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('game_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('play_number', sa.Integer(), nullable=False),
        sa.Column('inning', sa.Integer(), nullable=False, server_default='1'),
        sa.Column('half', sa.String(10), nullable=False),
        sa.Column('outs_before', sa.Integer(), nullable=False, server_default='0'),
        sa.Column('batting_order', sa.Integer(), nullable=False, server_default='1'),
        sa.Column('away_score', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('home_score', sa.Integer(), nullable=True, server_default='0'),
        # Players involved
        sa.Column('batter_id', sa.Integer(), nullable=False),
        sa.Column('pitcher_id', sa.Integer(), nullable=False),
        sa.Column('catcher_id', sa.Integer(), nullable=False),
        sa.Column('defender_id', sa.Integer(), nullable=True),
        sa.Column('runner_id', sa.Integer(), nullable=True),
        # Base runners
        sa.Column('on_first_id', sa.Integer(), nullable=True),
        sa.Column('on_second_id', sa.Integer(), nullable=True),
        sa.Column('on_third_id', sa.Integer(), nullable=True),
        # Runner final positions
        sa.Column('on_first_final', sa.Integer(), nullable=True),
        sa.Column('on_second_final', sa.Integer(), nullable=True),
        sa.Column('on_third_final', sa.Integer(), nullable=True),
        sa.Column('batter_final', sa.Integer(), nullable=True),
        # Base state code
        sa.Column('on_base_code', sa.Integer(), nullable=True, server_default='0'),
        # Strategic decisions
        sa.Column('defensive_choices', sa.JSON(), nullable=True),
        sa.Column('offensive_choices', sa.JSON(), nullable=True),
        # Play result
        sa.Column('dice_roll', sa.String(50), nullable=True),
        sa.Column('hit_type', sa.String(50), nullable=True),
        sa.Column('result_description', sa.Text(), nullable=True),
        sa.Column('outs_recorded', sa.Integer(), nullable=False, server_default='0'),
        sa.Column('runs_scored', sa.Integer(), nullable=True, server_default='0'),
        # Defensive details
        sa.Column('check_pos', sa.String(10), nullable=True),
        sa.Column('error', sa.Integer(), nullable=True, server_default='0'),
        # Batting statistics
        sa.Column('pa', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('ab', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('hit', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('double', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('triple', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('homerun', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('bb', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('so', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('hbp', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('rbi', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('sac', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('ibb', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('gidp', sa.Integer(), nullable=True, server_default='0'),
        # Baserunning statistics
        sa.Column('sb', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('cs', sa.Integer(), nullable=True, server_default='0'),
        # Pitching events
        sa.Column('wild_pitch', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('passed_ball', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('pick_off', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('balk', sa.Integer(), nullable=True, server_default='0'),
        # Ballpark power events
        sa.Column('bphr', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('bpfo', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('bp1b', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('bplo', sa.Integer(), nullable=True, server_default='0'),
        # Advanced analytics
        sa.Column('wpa', sa.Float(), nullable=True, server_default='0.0'),
        sa.Column('re24', sa.Float(), nullable=True, server_default='0.0'),
        # Earned/unearned runs
        sa.Column('run', sa.Integer(), nullable=True, server_default='0'),
        sa.Column('e_run', sa.Integer(), nullable=True, server_default='0'),
        # Game situation flags
        sa.Column('is_tied', sa.Boolean(), nullable=True, server_default='false'),
        sa.Column('is_go_ahead', sa.Boolean(), nullable=True, server_default='false'),
        sa.Column('is_new_inning', sa.Boolean(), nullable=True, server_default='false'),
        sa.Column('in_pow', sa.Boolean(), nullable=True, server_default='false'),
        # Play workflow
        sa.Column('complete', sa.Boolean(), nullable=True, server_default='false'),
        sa.Column('locked', sa.Boolean(), nullable=True, server_default='false'),
        # Timestamps
        sa.Column('created_at', sa.DateTime(), nullable=True),
        # Extensibility
        sa.Column('play_metadata', sa.JSON(), nullable=True),
        # Foreign keys
        sa.ForeignKeyConstraint(['game_id'], ['games.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['batter_id'], ['lineups.id']),
        sa.ForeignKeyConstraint(['pitcher_id'], ['lineups.id']),
        sa.ForeignKeyConstraint(['catcher_id'], ['lineups.id']),
        sa.ForeignKeyConstraint(['defender_id'], ['lineups.id']),
        sa.ForeignKeyConstraint(['runner_id'], ['lineups.id']),
        sa.ForeignKeyConstraint(['on_first_id'], ['lineups.id']),
        sa.ForeignKeyConstraint(['on_second_id'], ['lineups.id']),
        sa.ForeignKeyConstraint(['on_third_id'], ['lineups.id']),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index('ix_plays_complete', 'plays', ['complete'])
    op.create_index('ix_plays_created_at', 'plays', ['created_at'])
    op.create_index('ix_plays_game_id', 'plays', ['game_id'])

    # === GAME_SESSIONS TABLE ===
    op.create_table(
        'game_sessions',
        sa.Column('game_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('connected_users', sa.JSON(), nullable=True),
        sa.Column('last_action_at', sa.DateTime(), nullable=True),
        sa.Column('state_snapshot', sa.JSON(), nullable=True),
        sa.ForeignKeyConstraint(['game_id'], ['games.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('game_id')
    )
    op.create_index('ix_game_sessions_last_action_at', 'game_sessions', ['last_action_at'])

    # === ROLLS TABLE ===
    op.create_table(
        'rolls',
        sa.Column('roll_id', sa.String(), nullable=False),
        sa.Column('game_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('roll_type', sa.String(), nullable=False),
        sa.Column('league_id', sa.String(), nullable=False),
        sa.Column('team_id', sa.Integer(), nullable=True),
        sa.Column('player_id', sa.Integer(), nullable=True),
        sa.Column('roll_data', postgresql.JSONB(astext_type=sa.Text()), nullable=False),
        sa.Column('context', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
        sa.Column('timestamp', sa.DateTime(timezone=True), nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()')),
        sa.ForeignKeyConstraint(['game_id'], ['games.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('roll_id')
    )
    op.create_index('ix_rolls_game_id', 'rolls', ['game_id'])
    op.create_index('ix_rolls_league_id', 'rolls', ['league_id'])
    op.create_index('ix_rolls_player_id', 'rolls', ['player_id'])
    op.create_index('ix_rolls_roll_type', 'rolls', ['roll_type'])
    op.create_index('ix_rolls_team_id', 'rolls', ['team_id'])
    op.create_index('ix_rolls_timestamp', 'rolls', ['timestamp'])

    # === ROSTER_LINKS TABLE ===
    op.create_table(
        'roster_links',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('game_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('card_id', sa.Integer(), nullable=True),
        sa.Column('player_id', sa.Integer(), nullable=True),
        sa.Column('team_id', sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(['game_id'], ['games.id'], ondelete='CASCADE'),
        sa.CheckConstraint(
            '(card_id IS NOT NULL)::int + (player_id IS NOT NULL)::int = 1',
            name='roster_link_one_id_required'
        ),
        sa.UniqueConstraint('game_id', 'card_id', name='uq_game_card'),
        sa.UniqueConstraint('game_id', 'player_id', name='uq_game_player'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index('ix_roster_links_game_id', 'roster_links', ['game_id'])
    op.create_index('ix_roster_links_team_id', 'roster_links', ['team_id'])

    # === GAME_CARDSET_LINKS TABLE ===
    op.create_table(
        'game_cardset_links',
        sa.Column('game_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('cardset_id', sa.Integer(), nullable=False),
        sa.Column('priority', sa.Integer(), nullable=True, server_default='1'),
        sa.ForeignKeyConstraint(['game_id'], ['games.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('game_id', 'cardset_id')
    )
    op.create_index('ix_game_cardset_links_priority', 'game_cardset_links', ['priority'])


def downgrade() -> None:
    # Drop tables in reverse order (respecting foreign key constraints)
    op.drop_index('ix_game_cardset_links_priority', table_name='game_cardset_links')
    op.drop_table('game_cardset_links')

    op.drop_index('ix_roster_links_team_id', table_name='roster_links')
    op.drop_index('ix_roster_links_game_id', table_name='roster_links')
    op.drop_table('roster_links')

    op.drop_index('ix_rolls_timestamp', table_name='rolls')
    op.drop_index('ix_rolls_team_id', table_name='rolls')
    op.drop_index('ix_rolls_roll_type', table_name='rolls')
    op.drop_index('ix_rolls_player_id', table_name='rolls')
    op.drop_index('ix_rolls_league_id', table_name='rolls')
    op.drop_index('ix_rolls_game_id', table_name='rolls')
    op.drop_table('rolls')

    op.drop_index('ix_game_sessions_last_action_at', table_name='game_sessions')
    op.drop_table('game_sessions')

    op.drop_index('ix_plays_game_id', table_name='plays')
    op.drop_index('ix_plays_created_at', table_name='plays')
    op.drop_index('ix_plays_complete', table_name='plays')
    op.drop_table('plays')

    op.drop_index('ix_lineups_team_id', table_name='lineups')
    op.drop_index('ix_lineups_is_active', table_name='lineups')
    op.drop_index('ix_lineups_game_id', table_name='lineups')
    op.drop_table('lineups')

    op.drop_index('ix_games_status', table_name='games')
    op.drop_index('ix_games_league_id', table_name='games')
    op.drop_index('ix_games_created_at', table_name='games')
    op.drop_table('games')
