"""Initial migration Revision ID: 001_initial Revises: Create Date: 2024-01-01 00:00:00.000000 """ from alembic import op import sqlalchemy as sa from sqlalchemy.dialects import postgresql # revision identifiers, used by Alembic. revision = '001_initial' down_revision = None branch_labels = None depends_on = None def upgrade() -> None: # Sports op.create_table( 'sports', sa.Column('id', sa.Integer(), nullable=False), sa.Column('name', sa.String(), nullable=False), sa.Column('code', sa.String(), nullable=False), sa.PrimaryKeyConstraint('id') ) op.create_index('ix_sports_id', 'sports', ['id'], unique=False) op.create_index('ix_sports_name', 'sports', ['name'], unique=True) op.create_index('ix_sports_code', 'sports', ['code'], unique=True) # Leagues op.create_table( 'leagues', sa.Column('id', sa.Integer(), nullable=False), sa.Column('sport_id', sa.Integer(), nullable=False), sa.Column('name', sa.String(), nullable=False), sa.Column('country', sa.String(), nullable=True), sa.Column('code', sa.String(), nullable=True), sa.Column('source_id', sa.String(), nullable=True), sa.ForeignKeyConstraint(['sport_id'], ['sports.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_index('ix_leagues_id', 'leagues', ['id'], unique=False) op.create_index('ix_leagues_code', 'leagues', ['code'], unique=True) op.create_index('ix_leagues_source_id', 'leagues', ['source_id'], unique=False) # Seasons op.create_table( 'seasons', sa.Column('id', sa.Integer(), nullable=False), sa.Column('league_id', sa.Integer(), nullable=False), sa.Column('name', sa.String(), nullable=False), sa.Column('start_date', sa.Date(), nullable=True), sa.Column('end_date', sa.Date(), nullable=True), sa.Column('is_current', sa.Integer(), nullable=True), sa.ForeignKeyConstraint(['league_id'], ['leagues.id'], ), sa.PrimaryKeyConstraint('id') ) # Teams op.create_table( 'teams', sa.Column('id', sa.Integer(), nullable=False), sa.Column('name', sa.String(), nullable=False), sa.Column('country', sa.String(), nullable=True), sa.Column('code', sa.String(), nullable=True), sa.Column('source_id', sa.String(), nullable=True), sa.PrimaryKeyConstraint('id') ) op.create_index('ix_teams_id', 'teams', ['id'], unique=False) op.create_index('ix_teams_name', 'teams', ['name'], unique=False) op.create_index('ix_teams_code', 'teams', ['code'], unique=True) op.create_index('ix_teams_source_id', 'teams', ['source_id'], unique=False) # Matches op.create_table( 'matches', sa.Column('id', sa.Integer(), nullable=False), sa.Column('league_id', sa.Integer(), nullable=False), sa.Column('season_id', sa.Integer(), nullable=True), sa.Column('home_team_id', sa.Integer(), nullable=False), sa.Column('away_team_id', sa.Integer(), nullable=False), sa.Column('match_date', sa.DateTime(), nullable=False), sa.Column('status', sa.String(), nullable=True), sa.Column('home_score', sa.Integer(), nullable=True), sa.Column('away_score', sa.Integer(), nullable=True), sa.Column('source_url', sa.String(), nullable=True), sa.Column('source_id', sa.String(), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=True), sa.Column('updated_at', sa.DateTime(), nullable=True), sa.ForeignKeyConstraint(['away_team_id'], ['teams.id'], ), sa.ForeignKeyConstraint(['home_team_id'], ['teams.id'], ), sa.ForeignKeyConstraint(['league_id'], ['leagues.id'], ), sa.ForeignKeyConstraint(['season_id'], ['seasons.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_index('ix_matches_id', 'matches', ['id'], unique=False) op.create_index('ix_matches_match_date', 'matches', ['match_date'], unique=False) op.create_index('ix_matches_status', 'matches', ['status'], unique=False) op.create_index('ix_matches_source_id', 'matches', ['source_id'], unique=True) # Match Events op.create_table( 'match_events', sa.Column('id', sa.Integer(), nullable=False), sa.Column('match_id', sa.Integer(), nullable=False), sa.Column('event_type', sa.String(), nullable=False), sa.Column('team_id', sa.Integer(), nullable=True), sa.Column('player_name', sa.String(), nullable=True), sa.Column('minute', sa.Integer(), nullable=True), sa.Column('description', sa.String(), nullable=True), sa.ForeignKeyConstraint(['match_id'], ['matches.id'], ), sa.ForeignKeyConstraint(['team_id'], ['teams.id'], ), sa.PrimaryKeyConstraint('id') ) # Team Form Daily op.create_table( 'team_form_daily', sa.Column('id', sa.Integer(), nullable=False), sa.Column('team_id', sa.Integer(), nullable=False), sa.Column('date', sa.Date(), nullable=False), sa.Column('matches_played', sa.Integer(), nullable=True), sa.Column('wins', sa.Integer(), nullable=True), sa.Column('draws', sa.Integer(), nullable=True), sa.Column('losses', sa.Integer(), nullable=True), sa.Column('goals_for', sa.Integer(), nullable=True), sa.Column('goals_against', sa.Integer(), nullable=True), sa.Column('points', sa.Integer(), nullable=True), sa.Column('home_matches', sa.Integer(), nullable=True), sa.Column('home_wins', sa.Integer(), nullable=True), sa.Column('away_matches', sa.Integer(), nullable=True), sa.Column('away_wins', sa.Integer(), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=True), sa.ForeignKeyConstraint(['team_id'], ['teams.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_index('ix_team_form_daily_id', 'team_form_daily', ['id'], unique=False) op.create_index('ix_team_form_daily_team_id', 'team_form_daily', ['team_id'], unique=False) op.create_index('ix_team_form_daily_date', 'team_form_daily', ['date'], unique=False) # Features op.create_table( 'features', sa.Column('id', sa.Integer(), nullable=False), sa.Column('match_id', sa.Integer(), nullable=False), sa.Column('calculated_at', sa.DateTime(), nullable=True), sa.Column('home_goals_for_avg', sa.Float(), nullable=True), sa.Column('home_goals_against_avg', sa.Float(), nullable=True), sa.Column('home_points_avg', sa.Float(), nullable=True), sa.Column('home_form_last_n', sa.Float(), nullable=True), sa.Column('home_elo', sa.Float(), nullable=True), sa.Column('home_days_rest', sa.Float(), nullable=True), sa.Column('away_goals_for_avg', sa.Float(), nullable=True), sa.Column('away_goals_against_avg', sa.Float(), nullable=True), sa.Column('away_points_avg', sa.Float(), nullable=True), sa.Column('away_form_last_n', sa.Float(), nullable=True), sa.Column('away_elo', sa.Float(), nullable=True), sa.Column('away_days_rest', sa.Float(), nullable=True), sa.Column('h2h_home_wins', sa.Integer(), nullable=True), sa.Column('h2h_draws', sa.Integer(), nullable=True), sa.Column('h2h_away_wins', sa.Integer(), nullable=True), sa.Column('feature_vector', sa.String(), nullable=True), sa.ForeignKeyConstraint(['match_id'], ['matches.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_index('ix_features_id', 'features', ['id'], unique=False) op.create_index('ix_features_match_id', 'features', ['match_id'], unique=False) op.create_index('ix_features_calculated_at', 'features', ['calculated_at'], unique=False) # Odds op.create_table( 'odds', sa.Column('id', sa.Integer(), nullable=False), sa.Column('match_id', sa.Integer(), nullable=False), sa.Column('bookmaker', sa.String(), nullable=True), sa.Column('home_win', sa.Float(), nullable=True), sa.Column('draw', sa.Float(), nullable=True), sa.Column('away_win', sa.Float(), nullable=True), sa.Column('over_2_5', sa.Float(), nullable=True), sa.Column('under_2_5', sa.Float(), nullable=True), sa.Column('both_score_yes', sa.Float(), nullable=True), sa.Column('both_score_no', sa.Float(), nullable=True), sa.Column('captured_at', sa.DateTime(), nullable=True), sa.ForeignKeyConstraint(['match_id'], ['matches.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_index('ix_odds_id', 'odds', ['id'], unique=False) op.create_index('ix_odds_match_id', 'odds', ['match_id'], unique=False) op.create_index('ix_odds_captured_at', 'odds', ['captured_at'], unique=False) # Models op.create_table( 'models', sa.Column('id', sa.Integer(), nullable=False), sa.Column('version', sa.String(), nullable=False), sa.Column('model_type', sa.String(), nullable=False), sa.Column('parameters', sa.JSON(), nullable=True), sa.Column('train_accuracy', sa.Float(), nullable=True), sa.Column('test_accuracy', sa.Float(), nullable=True), sa.Column('train_logloss', sa.Float(), nullable=True), sa.Column('test_logloss', sa.Float(), nullable=True), sa.Column('train_brier', sa.Float(), nullable=True), sa.Column('test_brier', sa.Float(), nullable=True), sa.Column('artifact_path', sa.String(), nullable=True), sa.Column('trained_at', sa.DateTime(), nullable=True), sa.Column('trained_on_matches', sa.Integer(), nullable=True), sa.Column('is_active', sa.Integer(), nullable=True), sa.Column('notes', sa.String(), nullable=True), sa.PrimaryKeyConstraint('id') ) op.create_index('ix_models_id', 'models', ['id'], unique=False) op.create_index('ix_models_version', 'models', ['version'], unique=True) op.create_index('ix_models_trained_at', 'models', ['trained_at'], unique=False) # Predictions op.create_table( 'predictions', sa.Column('id', sa.Integer(), nullable=False), sa.Column('match_id', sa.Integer(), nullable=False), sa.Column('model_id', sa.Integer(), nullable=False), sa.Column('prob_home_win', sa.Float(), nullable=False), sa.Column('prob_draw', sa.Float(), nullable=False), sa.Column('prob_away_win', sa.Float(), nullable=False), sa.Column('prob_over_2_5', sa.Float(), nullable=True), sa.Column('prob_under_2_5', sa.Float(), nullable=True), sa.Column('prob_both_score', sa.Float(), nullable=True), sa.Column('calculated_at', sa.DateTime(), nullable=True), sa.Column('confidence', sa.Float(), nullable=True), sa.ForeignKeyConstraint(['match_id'], ['matches.id'], ), sa.ForeignKeyConstraint(['model_id'], ['models.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_index('ix_predictions_id', 'predictions', ['id'], unique=False) op.create_index('ix_predictions_match_id', 'predictions', ['match_id'], unique=False) op.create_index('ix_predictions_calculated_at', 'predictions', ['calculated_at'], unique=False) # Agent Runs op.create_table( 'agent_runs', sa.Column('id', sa.Integer(), nullable=False), sa.Column('agent_name', sa.String(), nullable=False), sa.Column('status', sa.String(), nullable=True), sa.Column('started_at', sa.DateTime(), nullable=True), sa.Column('finished_at', sa.DateTime(), nullable=True), sa.Column('error', sa.Text(), nullable=True), sa.Column('logs', sa.Text(), nullable=True), sa.Column('matches_processed', sa.Integer(), nullable=True), sa.Column('records_created', sa.Integer(), nullable=True), sa.Column('records_updated', sa.Integer(), nullable=True), sa.PrimaryKeyConstraint('id') ) op.create_index('ix_agent_runs_id', 'agent_runs', ['id'], unique=False) op.create_index('ix_agent_runs_agent_name', 'agent_runs', ['agent_name'], unique=False) op.create_index('ix_agent_runs_status', 'agent_runs', ['status'], unique=False) op.create_index('ix_agent_runs_started_at', 'agent_runs', ['started_at'], unique=False) def downgrade() -> None: op.drop_index('ix_agent_runs_started_at', table_name='agent_runs') op.drop_index('ix_agent_runs_status', table_name='agent_runs') op.drop_index('ix_agent_runs_agent_name', table_name='agent_runs') op.drop_index('ix_agent_runs_id', table_name='agent_runs') op.drop_table('agent_runs') op.drop_index('ix_predictions_calculated_at', table_name='predictions') op.drop_index('ix_predictions_match_id', table_name='predictions') op.drop_index('ix_predictions_id', table_name='predictions') op.drop_table('predictions') op.drop_index('ix_models_trained_at', table_name='models') op.drop_index('ix_models_version', table_name='models') op.drop_index('ix_models_id', table_name='models') op.drop_table('models') op.drop_index('ix_odds_captured_at', table_name='odds') op.drop_index('ix_odds_match_id', table_name='odds') op.drop_index('ix_odds_id', table_name='odds') op.drop_table('odds') op.drop_index('ix_features_calculated_at', table_name='features') op.drop_index('ix_features_match_id', table_name='features') op.drop_index('ix_features_id', table_name='features') op.drop_table('features') op.drop_index('ix_team_form_daily_date', table_name='team_form_daily') op.drop_index('ix_team_form_daily_team_id', table_name='team_form_daily') op.drop_index('ix_team_form_daily_id', table_name='team_form_daily') op.drop_table('team_form_daily') op.drop_table('match_events') op.drop_index('ix_matches_source_id', table_name='matches') op.drop_index('ix_matches_status', table_name='matches') op.drop_index('ix_matches_match_date', table_name='matches') op.drop_index('ix_matches_id', table_name='matches') op.drop_table('matches') op.drop_index('ix_teams_source_id', table_name='teams') op.drop_index('ix_teams_code', table_name='teams') op.drop_index('ix_teams_name', table_name='teams') op.drop_index('ix_teams_id', table_name='teams') op.drop_table('teams') op.drop_table('seasons') op.drop_index('ix_leagues_source_id', table_name='leagues') op.drop_index('ix_leagues_code', table_name='leagues') op.drop_index('ix_leagues_id', table_name='leagues') op.drop_table('leagues') op.drop_index('ix_sports_code', table_name='sports') op.drop_index('ix_sports_name', table_name='sports') op.drop_index('ix_sports_id', table_name='sports') op.drop_table('sports')