"""empty message

Revision ID: 3b18d4bfc6ac
Revises: 
Create Date: 2024-12-10 16:43:29.354244

"""
from alembic import op
import sqlalchemy as sa
import sqlalchemy_utc


# revision identifiers, used by Alembic.
revision = '3b18d4bfc6ac'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('matches',
    sa.Column('logs_tf_id', sa.Integer(), nullable=False),
    sa.Column('logs_tf_title', sa.String(length=255), nullable=False),
    sa.Column('duration', sa.Integer(), nullable=False),
    sa.Column('match_time', sa.TIMESTAMP(), nullable=False),
    sa.Column('blue_score', sa.Integer(), nullable=False),
    sa.Column('red_score', sa.Integer(), nullable=False),
    sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
    sa.PrimaryKeyConstraint('logs_tf_id')
    )
    op.create_table('players',
    sa.Column('steam_id', sa.BigInteger(), nullable=False),
    sa.Column('username', sa.String(length=63), nullable=False),
    sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
    sa.PrimaryKeyConstraint('steam_id')
    )
    op.create_table('teams',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('team_name', sa.String(length=63), nullable=False),
    sa.Column('discord_webhook_url', sa.String(length=255), nullable=True),
    sa.Column('tz_timezone', sa.String(length=31), nullable=False),
    sa.Column('minute_offset', sa.SmallInteger(), nullable=False),
    sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('team_name')
    )
    op.create_table('auth_sessions',
    sa.Column('key', sa.String(length=31), nullable=False),
    sa.Column('player_id', sa.BigInteger(), nullable=False),
    sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
    sa.ForeignKeyConstraint(['player_id'], ['players.steam_id'], ),
    sa.PrimaryKeyConstraint('key')
    )
    op.create_table('events',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('team_id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.Column('start_time', sqlalchemy_utc.sqltypes.UtcDateTime(timezone=True), nullable=False),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
    sa.Column('discord_message_id', sa.BigInteger(), nullable=True),
    sa.ForeignKeyConstraint(['team_id'], ['teams.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('team_id', 'name', 'start_time')
    )
    op.create_table('players_matches',
    sa.Column('player_id', sa.BigInteger(), nullable=False),
    sa.Column('match_id', sa.Integer(), nullable=False),
    sa.Column('kills', sa.Integer(), nullable=False),
    sa.Column('deaths', sa.Integer(), nullable=False),
    sa.Column('assists', sa.Integer(), nullable=False),
    sa.Column('damage', sa.BigInteger(), nullable=False),
    sa.Column('damage_taken', sa.BigInteger(), nullable=False),
    sa.ForeignKeyConstraint(['match_id'], ['matches.logs_tf_id'], ),
    sa.ForeignKeyConstraint(['player_id'], ['players.steam_id'], ),
    sa.PrimaryKeyConstraint('player_id', 'match_id')
    )
    op.create_table('players_teams',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('player_id', sa.BigInteger(), nullable=False),
    sa.Column('team_id', sa.Integer(), nullable=False),
    sa.Column('team_role', sa.Enum('Player', 'CoachMentor', name='teamrole'), nullable=False),
    sa.Column('playtime', sa.Integer(), nullable=False),
    sa.Column('is_team_leader', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
    sa.ForeignKeyConstraint(['player_id'], ['players.steam_id'], ),
    sa.ForeignKeyConstraint(['team_id'], ['teams.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('team_discord_integrations',
    sa.Column('team_id', sa.Integer(), nullable=False),
    sa.Column('webhook_url', sa.String(), nullable=False),
    sa.Column('webhook_bot_name', sa.String(), nullable=False),
    sa.Column('webhook_bot_profile_picture', sa.String(length=255), nullable=True),
    sa.ForeignKeyConstraint(['team_id'], ['teams.id'], ),
    sa.PrimaryKeyConstraint('team_id')
    )
    op.create_table('team_invites',
    sa.Column('key', sa.String(length=31), nullable=False),
    sa.Column('team_id', sa.Integer(), nullable=False),
    sa.Column('delete_on_use', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
    sa.ForeignKeyConstraint(['team_id'], ['teams.id'], ),
    sa.PrimaryKeyConstraint('key')
    )
    op.create_table('team_logs_tf_integrations',
    sa.Column('team_id', sa.Integer(), nullable=False),
    sa.Column('logs_tf_api_key', sa.String(), nullable=True),
    sa.Column('min_team_member_count', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['team_id'], ['teams.id'], ),
    sa.PrimaryKeyConstraint('team_id')
    )
    op.create_table('teams_matches',
    sa.Column('team_id', sa.Integer(), nullable=False),
    sa.Column('match_id', sa.Integer(), nullable=False),
    sa.Column('team_color', sa.String(length=4), nullable=False),
    sa.ForeignKeyConstraint(['match_id'], ['matches.logs_tf_id'], ),
    sa.ForeignKeyConstraint(['team_id'], ['teams.id'], ),
    sa.PrimaryKeyConstraint('team_id', 'match_id')
    )
    op.create_table('players_teams_availability',
    sa.Column('player_team_id', sa.Integer(), nullable=False),
    sa.Column('start_time', sqlalchemy_utc.sqltypes.UtcDateTime(timezone=True), nullable=False),
    sa.Column('availability', sa.Integer(), nullable=False),
    sa.Column('end_time', sqlalchemy_utc.sqltypes.UtcDateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['player_team_id'], ['players_teams.id'], ),
    sa.PrimaryKeyConstraint('player_team_id', 'start_time')
    )
    with op.batch_alter_table('players_teams_availability', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_players_teams_availability_end_time'), ['end_time'], unique=False)
        batch_op.create_index(batch_op.f('ix_players_teams_availability_start_time'), ['start_time'], unique=False)

    op.create_table('players_teams_roles',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('player_team_id', sa.Integer(), nullable=False),
    sa.Column('role', sa.Enum('Unknown', 'Scout', 'PocketScout', 'FlankScout', 'Soldier', 'PocketSoldier', 'Roamer', 'Pyro', 'Demoman', 'HeavyWeapons', 'Engineer', 'Medic', 'Sniper', 'Spy', name='role'), nullable=False),
    sa.Column('is_main', sa.Boolean(), nullable=False),
    sa.ForeignKeyConstraint(['player_team_id'], ['players_teams.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('player_team_id', 'role')
    )
    op.create_table('players_events',
    sa.Column('event_id', sa.Integer(), nullable=False),
    sa.Column('player_id', sa.BigInteger(), nullable=False),
    sa.Column('player_team_role_id', sa.Integer(), nullable=True),
    sa.Column('has_confirmed', sa.Boolean(), nullable=False),
    sa.ForeignKeyConstraint(['event_id'], ['events.id'], ),
    sa.ForeignKeyConstraint(['player_id'], ['players.steam_id'], ),
    sa.ForeignKeyConstraint(['player_team_role_id'], ['players_teams_roles.id'], ),
    sa.PrimaryKeyConstraint('event_id', 'player_id')
    )
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('players_events')
    op.drop_table('players_teams_roles')
    with op.batch_alter_table('players_teams_availability', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_players_teams_availability_start_time'))
        batch_op.drop_index(batch_op.f('ix_players_teams_availability_end_time'))

    op.drop_table('players_teams_availability')
    op.drop_table('teams_matches')
    op.drop_table('team_logs_tf_integrations')
    op.drop_table('team_invites')
    op.drop_table('team_discord_integrations')
    op.drop_table('players_teams')
    op.drop_table('players_matches')
    op.drop_table('events')
    op.drop_table('auth_sessions')
    op.drop_table('teams')
    op.drop_table('players')
    op.drop_table('matches')
    # ### end Alembic commands ###