"""add_files_support_and_gamification

Revision ID: 53545a112c5f
Revises: 10ea1bc43b8f
Create Date: 2026-11-19 05:48:27.632091

"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql


# revision identifiers, used by Alembic.
revision: str = '53545a112c5f'
down_revision: Union[str, None] = '10ea1bc43b8f'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # 1. Add verification and gamification streak fields to bot_users
    op.add_column('bot_users', sa.Column('is_verified', sa.Boolean(), server_default='false', nullable=False))
    op.add_column('bot_users', sa.Column('last_checkin_at', sa.DateTime(timezone=True), nullable=True))
    op.add_column('bot_users', sa.Column('streak_count', sa.Integer(), server_default='0', nullable=False))

    # 2. Create bot_files table for Digital Store & File Delivery Module
    op.create_table(
        'bot_files',
        sa.Column('id', postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column('bot_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('bots.id', ondelete='CASCADE'), nullable=False),
        sa.Column('file_id', sa.String(length=255), nullable=False),
        sa.Column('file_name', sa.String(length=255), nullable=False),
        sa.Column('file_type', sa.String(length=32), server_default='document', nullable=False),
        sa.Column('file_size', sa.BigInteger(), server_default='0', nullable=False),
        sa.Column('access_type', sa.String(length=32), server_default='free', nullable=False),
        sa.Column('cost', sa.Integer(), server_default='0', nullable=False),
        sa.Column('unlock_code', sa.String(length=64), nullable=False),
        sa.Column('downloads_count', sa.Integer(), server_default='0', nullable=False),
        sa.Column('is_active', sa.Boolean(), server_default='true', nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.UniqueConstraint('bot_id', 'unlock_code', name='uq_bot_file_unlock_code'),
    )
    op.create_index('ix_bot_files_bot_id', 'bot_files', ['bot_id'])
    op.create_index('ix_bot_files_unlock_code', 'bot_files', ['unlock_code'])

    # 3. Create bot_support_tickets table for 2-Way Live Support Relay
    op.create_table(
        'bot_support_tickets',
        sa.Column('id', postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column('bot_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('bots.id', ondelete='CASCADE'), nullable=False),
        sa.Column('telegram_user_id', sa.BigInteger(), nullable=False),
        sa.Column('topic_id', sa.BigInteger(), nullable=True),
        sa.Column('subject', sa.String(length=255), server_default='General Query', nullable=False),
        sa.Column('status', sa.String(length=32), server_default='open', nullable=False),
        sa.Column('last_message_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
    )
    op.create_index('ix_bot_support_tickets_bot_id', 'bot_support_tickets', ['bot_id'])
    op.create_index('ix_bot_support_tickets_telegram_user_id', 'bot_support_tickets', ['telegram_user_id'])


def downgrade() -> None:
    op.drop_table('bot_support_tickets')
    op.drop_table('bot_files')
    op.drop_column('bot_users', 'streak_count')
    op.drop_column('bot_users', 'last_checkin_at')
    op.drop_column('bot_users', 'is_verified')
