"""add_ai_plugins_broadcasts_notifications

Revision ID: 3716c5b0ab2a
Revises: 15e6777c1212
Create Date: 2026-11-18 08:48:32.326241

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


# revision identifiers, used by Alembic.
revision: str = '3716c5b0ab2a'
down_revision: Union[str, None] = '15e6777c1212'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('plugins',
    sa.Column('id', sa.String(length=50), nullable=False),
    sa.Column('name', sa.String(length=100), nullable=False),
    sa.Column('description', sa.String(length=500), nullable=False),
    sa.Column('version', sa.String(length=20), nullable=False),
    sa.Column('author', sa.String(length=100), nullable=False),
    sa.Column('category', sa.String(length=50), nullable=False),
    sa.Column('required_permissions', sa.JSON(), nullable=False),
    sa.Column('default_config', sa.JSON(), nullable=False),
    sa.Column('is_official', sa.Boolean(), nullable=False),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('notifications',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('user_id', sa.UUID(), nullable=True),
    sa.Column('title', sa.String(length=255), nullable=False),
    sa.Column('message', sa.Text(), nullable=False),
    sa.Column('category', sa.String(length=50), nullable=False),
    sa.Column('is_read', sa.Boolean(), nullable=False),
    sa.Column('action_url', sa.String(length=500), nullable=True),
    sa.Column('tenant_id', sa.UUID(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['tenant_id'], ['tenants.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_notifications_is_read'), 'notifications', ['is_read'], unique=False)
    op.create_index('ix_notifications_tenant_id', 'notifications', ['tenant_id'], unique=False)
    op.create_index(op.f('ix_notifications_user_id'), 'notifications', ['user_id'], unique=False)
    op.create_table('tenant_ai_keys',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('provider', sa.String(length=50), nullable=False),
    sa.Column('key_name', sa.String(length=100), nullable=False),
    sa.Column('encrypted_api_key', sa.String(length=500), nullable=False),
    sa.Column('key_version', sa.Integer(), nullable=False),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('masked_key', sa.String(length=50), nullable=False),
    sa.Column('tenant_id', sa.UUID(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['tenant_id'], ['tenants.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_tenant_ai_keys_tenant_id'), 'tenant_ai_keys', ['tenant_id'], unique=False)
    op.create_table('ai_agents',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('bot_id', sa.UUID(), nullable=True),
    sa.Column('name', sa.String(length=100), nullable=False),
    sa.Column('provider', sa.String(length=50), nullable=False),
    sa.Column('model', sa.String(length=100), nullable=False),
    sa.Column('system_prompt', sa.Text(), nullable=False),
    sa.Column('temperature', sa.Float(), nullable=False),
    sa.Column('max_tokens', sa.Integer(), nullable=False),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('tools_config', sa.JSON(), nullable=True),
    sa.Column('tenant_id', sa.UUID(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['bot_id'], ['bots.id'], ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['tenant_id'], ['tenants.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_ai_agents_bot_id'), 'ai_agents', ['bot_id'], unique=False)
    op.create_index(op.f('ix_ai_agents_tenant_id'), 'ai_agents', ['tenant_id'], unique=False)
    op.create_table('broadcast_campaigns',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('bot_id', sa.UUID(), nullable=False),
    sa.Column('title', sa.String(length=255), nullable=False),
    sa.Column('status', sa.String(length=50), nullable=False),
    sa.Column('target_filter', sa.JSON(), nullable=False),
    sa.Column('message_text', sa.Text(), nullable=False),
    sa.Column('inline_keyboard', sa.JSON(), nullable=True),
    sa.Column('media_type', sa.String(length=20), nullable=False),
    sa.Column('media_url', sa.String(length=500), nullable=True),
    sa.Column('scheduled_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('started_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('completed_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('total_recipients', sa.Integer(), nullable=False),
    sa.Column('sent_count', sa.Integer(), nullable=False),
    sa.Column('delivered_count', sa.Integer(), nullable=False),
    sa.Column('failed_count', sa.Integer(), nullable=False),
    sa.Column('rate_limit_per_second', sa.Integer(), nullable=False),
    sa.Column('error_summary', sa.Text(), nullable=True),
    sa.Column('tenant_id', sa.UUID(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['bot_id'], ['bots.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['tenant_id'], ['tenants.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_broadcast_campaigns_bot_id'), 'broadcast_campaigns', ['bot_id'], unique=False)
    op.create_index(op.f('ix_broadcast_campaigns_scheduled_at'), 'broadcast_campaigns', ['scheduled_at'], unique=False)
    op.create_index(op.f('ix_broadcast_campaigns_status'), 'broadcast_campaigns', ['status'], unique=False)
    op.create_index(op.f('ix_broadcast_campaigns_tenant_id'), 'broadcast_campaigns', ['tenant_id'], unique=False)
    op.create_table('tenant_plugins',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('plugin_id', sa.String(length=50), nullable=False),
    sa.Column('bot_id', sa.UUID(), nullable=True),
    sa.Column('is_enabled', sa.Boolean(), nullable=False),
    sa.Column('config', sa.JSON(), nullable=False),
    sa.Column('installed_version', sa.String(length=20), nullable=False),
    sa.Column('granted_permissions', sa.JSON(), nullable=False),
    sa.Column('tenant_id', sa.UUID(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['bot_id'], ['bots.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['plugin_id'], ['plugins.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['tenant_id'], ['tenants.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('tenant_id', 'bot_id', 'plugin_id', name='uq_tenant_bot_plugin')
    )
    op.create_index(op.f('ix_tenant_plugins_bot_id'), 'tenant_plugins', ['bot_id'], unique=False)
    op.create_index(op.f('ix_tenant_plugins_plugin_id'), 'tenant_plugins', ['plugin_id'], unique=False)
    op.create_index(op.f('ix_tenant_plugins_tenant_id'), 'tenant_plugins', ['tenant_id'], unique=False)
    op.create_table('ai_conversations',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('agent_id', sa.UUID(), nullable=False),
    sa.Column('bot_user_id', sa.UUID(), nullable=True),
    sa.Column('session_id', sa.String(length=100), nullable=False),
    sa.Column('title', sa.String(length=255), nullable=False),
    sa.Column('total_tokens', sa.Integer(), nullable=False),
    sa.Column('total_cost_microcents', sa.Integer(), nullable=False),
    sa.Column('tenant_id', sa.UUID(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['agent_id'], ['ai_agents.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['bot_user_id'], ['bot_users.id'], ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['tenant_id'], ['tenants.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_ai_conversations_agent_id'), 'ai_conversations', ['agent_id'], unique=False)
    op.create_index(op.f('ix_ai_conversations_bot_user_id'), 'ai_conversations', ['bot_user_id'], unique=False)
    op.create_index(op.f('ix_ai_conversations_session_id'), 'ai_conversations', ['session_id'], unique=False)
    op.create_index('ix_ai_conversations_tenant_id', 'ai_conversations', ['tenant_id'], unique=False)
    op.create_table('broadcast_recipients',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('campaign_id', sa.UUID(), nullable=False),
    sa.Column('bot_user_id', sa.UUID(), nullable=False),
    sa.Column('telegram_chat_id', sa.BigInteger(), nullable=False),
    sa.Column('status', sa.String(length=50), nullable=False),
    sa.Column('attempts', sa.Integer(), nullable=False),
    sa.Column('error_message', sa.String(length=500), nullable=True),
    sa.Column('delivered_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['bot_user_id'], ['bot_users.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['campaign_id'], ['broadcast_campaigns.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_broadcast_recipients_bot_user_id'), 'broadcast_recipients', ['bot_user_id'], unique=False)
    op.create_index(op.f('ix_broadcast_recipients_campaign_id'), 'broadcast_recipients', ['campaign_id'], unique=False)
    op.create_index(op.f('ix_broadcast_recipients_status'), 'broadcast_recipients', ['status'], unique=False)
    op.create_table('ai_messages',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('conversation_id', sa.UUID(), nullable=False),
    sa.Column('role', sa.String(length=20), nullable=False),
    sa.Column('content', sa.Text(), nullable=False),
    sa.Column('prompt_tokens', sa.Integer(), nullable=False),
    sa.Column('completion_tokens', sa.Integer(), nullable=False),
    sa.Column('total_tokens', sa.Integer(), nullable=False),
    sa.Column('latency_ms', sa.Integer(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['conversation_id'], ['ai_conversations.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_ai_messages_conversation_id'), 'ai_messages', ['conversation_id'], unique=False)
    op.create_table('ai_usage_logs',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('agent_id', sa.UUID(), nullable=False),
    sa.Column('conversation_id', sa.UUID(), nullable=True),
    sa.Column('provider', sa.String(length=50), nullable=False),
    sa.Column('model', sa.String(length=100), nullable=False),
    sa.Column('prompt_tokens', sa.Integer(), nullable=False),
    sa.Column('completion_tokens', sa.Integer(), nullable=False),
    sa.Column('total_tokens', sa.Integer(), nullable=False),
    sa.Column('cost_microcents', sa.Integer(), nullable=False),
    sa.Column('tenant_id', sa.UUID(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['agent_id'], ['ai_agents.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['conversation_id'], ['ai_conversations.id'], ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['tenant_id'], ['tenants.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_ai_usage_logs_agent_id'), 'ai_usage_logs', ['agent_id'], unique=False)
    op.create_index('ix_ai_usage_logs_tenant_id', 'ai_usage_logs', ['tenant_id'], unique=False)
    op.add_column('bot_users', sa.Column('is_opted_out', sa.Boolean(), server_default=sa.text('false'), nullable=False))
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('bot_users', 'is_opted_out')
    op.drop_index('ix_ai_usage_logs_tenant_id', table_name='ai_usage_logs')
    op.drop_index(op.f('ix_ai_usage_logs_agent_id'), table_name='ai_usage_logs')
    op.drop_table('ai_usage_logs')
    op.drop_index(op.f('ix_ai_messages_conversation_id'), table_name='ai_messages')
    op.drop_table('ai_messages')
    op.drop_index(op.f('ix_broadcast_recipients_status'), table_name='broadcast_recipients')
    op.drop_index(op.f('ix_broadcast_recipients_campaign_id'), table_name='broadcast_recipients')
    op.drop_index(op.f('ix_broadcast_recipients_bot_user_id'), table_name='broadcast_recipients')
    op.drop_table('broadcast_recipients')
    op.drop_index('ix_ai_conversations_tenant_id', table_name='ai_conversations')
    op.drop_index(op.f('ix_ai_conversations_session_id'), table_name='ai_conversations')
    op.drop_index(op.f('ix_ai_conversations_bot_user_id'), table_name='ai_conversations')
    op.drop_index(op.f('ix_ai_conversations_agent_id'), table_name='ai_conversations')
    op.drop_table('ai_conversations')
    op.drop_index(op.f('ix_tenant_plugins_tenant_id'), table_name='tenant_plugins')
    op.drop_index(op.f('ix_tenant_plugins_plugin_id'), table_name='tenant_plugins')
    op.drop_index(op.f('ix_tenant_plugins_bot_id'), table_name='tenant_plugins')
    op.drop_table('tenant_plugins')
    op.drop_index(op.f('ix_broadcast_campaigns_tenant_id'), table_name='broadcast_campaigns')
    op.drop_index(op.f('ix_broadcast_campaigns_status'), table_name='broadcast_campaigns')
    op.drop_index(op.f('ix_broadcast_campaigns_scheduled_at'), table_name='broadcast_campaigns')
    op.drop_index(op.f('ix_broadcast_campaigns_bot_id'), table_name='broadcast_campaigns')
    op.drop_table('broadcast_campaigns')
    op.drop_index(op.f('ix_ai_agents_tenant_id'), table_name='ai_agents')
    op.drop_index(op.f('ix_ai_agents_bot_id'), table_name='ai_agents')
    op.drop_table('ai_agents')
    op.drop_index(op.f('ix_tenant_ai_keys_tenant_id'), table_name='tenant_ai_keys')
    op.drop_table('tenant_ai_keys')
    op.drop_index(op.f('ix_notifications_user_id'), table_name='notifications')
    op.drop_index('ix_notifications_tenant_id', table_name='notifications')
    op.drop_index(op.f('ix_notifications_is_read'), table_name='notifications')
    op.drop_table('notifications')
    op.drop_table('plugins')
    # ### end Alembic commands ###
