"""coin reward points phase 1 tables

Revision ID: coin_reward_phase1
Revises: pstat3growth
Create Date: 2026-06-07 15:30:00.000000

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


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


def upgrade() -> None:
    op.create_table(
        'signin_boost_record',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('user_id', sa.Integer(), nullable=False),
        sa.Column('signin_date', sa.Date(), nullable=False),
        sa.Column('coin_awarded', sa.Integer(), nullable=False),
        sa.Column('ad_ref_id', sa.String(length=64), nullable=True),
        sa.Column(
            'created_at', sa.DateTime(timezone=True),
            server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False,
        ),
        sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('user_id', 'signin_date', name='uq_signin_boost_user_date'),
    )
    with op.batch_alter_table('signin_boost_record', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_signin_boost_record_user_id'), ['user_id'], unique=False)

    op.create_table(
        'ad_feed_reward_record',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('client_event_id', sa.String(length=64), nullable=False),
        sa.Column('user_id', sa.Integer(), nullable=False),
        sa.Column('reward_date', sa.String(length=10), nullable=False),
        sa.Column('duration_seconds', sa.Integer(), nullable=False),
        sa.Column('unit_count', sa.Integer(), nullable=False),
        sa.Column('ecpm_raw', sa.String(length=32), nullable=False),
        sa.Column('adn', sa.String(length=32), nullable=True),
        sa.Column('slot_id', sa.String(length=64), nullable=True),
        sa.Column('coin', sa.Integer(), nullable=False),
        sa.Column('status', sa.String(length=16), nullable=False),
        sa.Column(
            'created_at', sa.DateTime(timezone=True),
            server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False,
        ),
        sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('client_event_id', name='uq_ad_feed_reward_client_event'),
    )
    with op.batch_alter_table('ad_feed_reward_record', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_ad_feed_reward_record_user_id'), ['user_id'], unique=False)
        batch_op.create_index(batch_op.f('ix_ad_feed_reward_record_reward_date'), ['reward_date'], unique=False)
        batch_op.create_index(batch_op.f('ix_ad_feed_reward_record_created_at'), ['created_at'], unique=False)


def downgrade() -> None:
    with op.batch_alter_table('ad_feed_reward_record', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_ad_feed_reward_record_created_at'))
        batch_op.drop_index(batch_op.f('ix_ad_feed_reward_record_reward_date'))
        batch_op.drop_index(batch_op.f('ix_ad_feed_reward_record_user_id'))
    op.drop_table('ad_feed_reward_record')

    with op.batch_alter_table('signin_boost_record', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_signin_boost_record_user_id'))
    op.drop_table('signin_boost_record')