51 lines
1.8 KiB
Python
51 lines
1.8 KiB
Python
"""add user predictions tracking
|
|
|
|
Revision ID: add_user_predictions_tracking
|
|
Revises:
|
|
Create Date: 2026-01-18 10:00:00.000000
|
|
|
|
"""
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
from sqlalchemy.dialects import sqlite
|
|
|
|
# revision identifiers, used by Alembic.
|
|
revision = 'add_user_predictions_tracking'
|
|
down_revision = None
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade():
|
|
"""Create user_predictions table to track predictions viewed by users."""
|
|
op.create_table(
|
|
'user_predictions',
|
|
sa.Column('id', sa.Integer(), primary_key=True, autoincrement=True),
|
|
sa.Column('user_id', sa.Integer(), nullable=False),
|
|
sa.Column('prediction_id', sa.Integer(), nullable=False),
|
|
sa.Column('viewed_at', sa.DateTime(), nullable=False),
|
|
sa.Column('was_correct', sa.Boolean(), nullable=True, comment='True if prediction was correct, False if incorrect, NULL if match not completed'),
|
|
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['prediction_id'], ['predictions.id'], ondelete='CASCADE'),
|
|
sa.Index('idx_user_predictions_user_id', 'user_id'),
|
|
sa.Index('idx_user_predictions_prediction_id', 'prediction_id'),
|
|
sa.Index('idx_user_predictions_viewed_at', 'viewed_at'),
|
|
sa.UniqueConstraint('user_id', 'prediction_id', name='uq_user_predictions_user_prediction')
|
|
)
|
|
|
|
# Create view timestamp in ISO format
|
|
op.execute('''
|
|
CREATE TRIGGER update_user_predictions_viewed_at
|
|
BEFORE INSERT ON user_predictions
|
|
FOR EACH ROW
|
|
BEGIN
|
|
SELECT datetime('now') INTO NEW.viewed_at;
|
|
END;
|
|
''')
|
|
|
|
|
|
def downgrade():
|
|
"""Drop user_predictions table."""
|
|
op.execute('DROP TRIGGER IF EXISTS update_user_predictions_viewed_at')
|
|
op.drop_table('user_predictions')
|