"""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')