"""initial_all_tables

Revision ID: 6d5520491644
Revises: 
Create Date: 2026-06-03 17:28:43.238581

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '6d5520491644'
down_revision: Union[str, None] = None
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('agents',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('user_id', sa.String(length=64), nullable=False, comment='企微用户ID（唯一）'),
    sa.Column('name', sa.String(length=128), nullable=False, comment='坐席姓名'),
    sa.Column('status', sa.String(length=20), nullable=False, comment='坐席状态: online/offline/busy'),
    sa.Column('current_load', sa.Integer(), nullable=False, comment='当前服务会话数'),
    sa.Column('max_load', sa.Integer(), nullable=False, comment='最大同时服务数'),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False, comment='更新时间'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('user_id')
    )
    op.create_table('approval_links',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('category', sa.String(length=64), nullable=False, comment='分类：IT/HR/行政/财务'),
    sa.Column('title', sa.String(length=128), nullable=False, comment='审批名称'),
    sa.Column('url', sa.Text(), nullable=False, comment='审批链接'),
    sa.Column('sort_order', sa.Integer(), nullable=False, comment='排序权重'),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False, comment='更新时间'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_al_category', 'approval_links', ['category'], unique=False)
    op.create_table('conversations',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('corp_id', sa.String(length=64), nullable=False, comment='企业微信企业ID（主企业或下游企业）'),
    sa.Column('employee_id', sa.String(length=64), nullable=False, comment='企微员工UserID'),
    sa.Column('employee_name', sa.String(length=128), nullable=False, comment='员工姓名'),
    sa.Column('department', sa.String(length=256), nullable=False, comment='部门'),
    sa.Column('position', sa.String(length=128), nullable=False, comment='岗位'),
    sa.Column('level', sa.String(length=64), nullable=False, comment='等级'),
    sa.Column('status', sa.String(length=20), nullable=False, comment='会话状态: ai_handling/queued/serving/resolved'),
    sa.Column('is_vip', sa.Boolean(), nullable=False, comment='VIP标记'),
    sa.Column('is_pinned', sa.Boolean(), nullable=False, comment='置顶标记'),
    sa.Column('is_todo', sa.Boolean(), nullable=False, comment='代办标记'),
    sa.Column('urgency_score', sa.Integer(), nullable=False, comment='紧急度1-5'),
    sa.Column('tags', sa.JSON(), nullable=False, comment='标签集合'),
    sa.Column('assigned_agent_id', sa.String(length=64), nullable=True, comment='分配的坐席ID'),
    sa.Column('collaborating_agent_ids', sa.JSON(), nullable=False, comment='协作坐席ID列表'),
    sa.Column('ai_substantive_reply_count', sa.Integer(), nullable=False, comment='AI实质性回复计数（满3次可呼叫坐席）'),
    sa.Column('last_message_at', sa.DateTime(timezone=True), nullable=True, comment='最后消息时间'),
    sa.Column('last_message_summary', sa.String(length=256), nullable=False, comment='最后消息摘要'),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False, comment='更新时间'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_conversations_assigned_agent', 'conversations', ['assigned_agent_id'], unique=False)
    op.create_index('idx_conversations_corp_id', 'conversations', ['corp_id'], unique=False)
    op.create_index('idx_conversations_employee_id', 'conversations', ['employee_id'], unique=False)
    op.create_index('idx_conversations_last_message_at', 'conversations', ['last_message_at'], unique=False)
    op.create_index('idx_conversations_status', 'conversations', ['status'], unique=False)
    op.create_index('idx_conversations_urgency_score', 'conversations', ['urgency_score'], unique=False)
    op.create_table('employees',
    sa.Column('id', sa.String(length=36), nullable=False, comment='员工记录唯一标识'),
    sa.Column('corp_id', sa.String(length=64), nullable=False, comment='企业微信企业ID'),
    sa.Column('employee_id', sa.String(length=64), nullable=False, comment='企微员工UserID（企业内唯一）'),
    sa.Column('name', sa.String(length=128), nullable=False, comment='员工姓名'),
    sa.Column('department', sa.String(length=512), nullable=False, comment='部门ID列表（JSON数组）'),
    sa.Column('position', sa.String(length=128), nullable=False, comment='岗位'),
    sa.Column('mobile', sa.String(length=32), nullable=False, comment='手机号'),
    sa.Column('email', sa.String(length=128), nullable=False, comment='邮箱'),
    sa.Column('avatar', sa.String(length=512), nullable=False, comment='头像URL'),
    sa.Column('status', sa.Integer(), nullable=False, comment='激活状态: 1=已激活, 2=已禁用, 4=未激活'),
    sa.Column('last_login_at', sa.DateTime(timezone=True), nullable=True, comment='最后登录时间'),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False, comment='更新时间'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('corp_id', 'employee_id', name='uq_employee_corp')
    )
    op.create_index('idx_employees_corp_id', 'employees', ['corp_id'], unique=False)
    op.create_index('idx_employees_employee_id', 'employees', ['employee_id'], unique=False)
    op.create_table('funny_phrases',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('scene', sa.String(length=64), nullable=False, comment='触发场景: shake/keyword/waiting/connected/timeout/vip'),
    sa.Column('content', sa.Text(), nullable=False, comment='话术内容'),
    sa.Column('tone', sa.String(length=32), nullable=False, comment='语气标签'),
    sa.Column('sort_order', sa.Integer(), nullable=False, comment='排序权重'),
    sa.Column('is_active', sa.Boolean(), nullable=False, comment='是否启用'),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False, comment='更新时间'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_fp_scene', 'funny_phrases', ['scene'], unique=False)
    op.create_table('quick_reply_templates',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('category', sa.String(length=64), nullable=False, comment='分类：账号/网络/软件/硬件/通用'),
    sa.Column('title', sa.String(length=128), nullable=False, comment='模板标题'),
    sa.Column('content', sa.Text(), nullable=False, comment='模板内容，支持变量如 {employee_name}'),
    sa.Column('variables', sa.JSON(), nullable=False, comment='可用变量列表'),
    sa.Column('sort_order', sa.Integer(), nullable=False, comment='排序权重'),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False, comment='更新时间'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_qr_category', 'quick_reply_templates', ['category'], unique=False)
    op.create_table('software_downloads',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('category', sa.String(length=64), nullable=False, comment='分类：办公/开发/安全/工具'),
    sa.Column('name', sa.String(length=128), nullable=False, comment='软件名称'),
    sa.Column('version', sa.String(length=32), nullable=False, comment='版本号'),
    sa.Column('platform', sa.String(length=32), nullable=False, comment='平台: Windows/Mac/Linux/全平台'),
    sa.Column('download_url', sa.Text(), nullable=False, comment='下载链接'),
    sa.Column('sort_order', sa.Integer(), nullable=False, comment='排序权重'),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False, comment='更新时间'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_sd_category', 'software_downloads', ['category'], unique=False)
    op.create_table('system_configs',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('config_key', sa.String(length=128), nullable=False, comment='配置键'),
    sa.Column('config_value', sa.Text(), nullable=False, comment='配置值（JSON字符串或纯文本）'),
    sa.Column('description', sa.String(length=256), nullable=False, comment='配置说明'),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False, comment='更新时间'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('config_key')
    )
    op.create_table('agent_notes',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('conversation_id', sa.String(length=36), nullable=False, comment='所属会话ID'),
    sa.Column('agent_id', sa.String(length=64), nullable=False, comment='坐席ID'),
    sa.Column('content', sa.Text(), nullable=False, comment='备注内容'),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False, comment='更新时间'),
    sa.ForeignKeyConstraint(['conversation_id'], ['conversations.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_an_conversation', 'agent_notes', ['conversation_id'], unique=False)
    op.create_table('messages',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('conversation_id', sa.String(length=36), nullable=False, comment='所属会话ID'),
    sa.Column('sender_type', sa.String(length=20), nullable=False, comment='发送者类型: employee/agent/ai/system'),
    sa.Column('sender_id', sa.String(length=64), nullable=False, comment='发送者ID'),
    sa.Column('sender_name', sa.String(length=128), nullable=False, comment='发送者姓名'),
    sa.Column('content', sa.Text(), nullable=False, comment='消息内容'),
    sa.Column('msg_type', sa.String(length=20), nullable=False, comment='消息类型: text/image/file/system'),
    sa.Column('ai_suggestion', sa.Boolean(), nullable=False, comment='是否为AI建议'),
    sa.Column('is_read', sa.Boolean(), nullable=False, comment='是否已读'),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False, comment='创建时间'),
    sa.ForeignKeyConstraint(['conversation_id'], ['conversations.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_messages_conversation_created', 'messages', ['conversation_id', 'created_at'], unique=False)
    op.create_index('idx_messages_conversation_id', 'messages', ['conversation_id'], unique=False)
    op.create_index('idx_messages_created_at', 'messages', ['created_at'], unique=False)
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('idx_messages_created_at', table_name='messages')
    op.drop_index('idx_messages_conversation_id', table_name='messages')
    op.drop_index('idx_messages_conversation_created', table_name='messages')
    op.drop_table('messages')
    op.drop_index('idx_an_conversation', table_name='agent_notes')
    op.drop_table('agent_notes')
    op.drop_table('system_configs')
    op.drop_index('idx_sd_category', table_name='software_downloads')
    op.drop_table('software_downloads')
    op.drop_index('idx_qr_category', table_name='quick_reply_templates')
    op.drop_table('quick_reply_templates')
    op.drop_index('idx_fp_scene', table_name='funny_phrases')
    op.drop_table('funny_phrases')
    op.drop_index('idx_employees_employee_id', table_name='employees')
    op.drop_index('idx_employees_corp_id', table_name='employees')
    op.drop_table('employees')
    op.drop_index('idx_conversations_urgency_score', table_name='conversations')
    op.drop_index('idx_conversations_status', table_name='conversations')
    op.drop_index('idx_conversations_last_message_at', table_name='conversations')
    op.drop_index('idx_conversations_employee_id', table_name='conversations')
    op.drop_index('idx_conversations_corp_id', table_name='conversations')
    op.drop_index('idx_conversations_assigned_agent', table_name='conversations')
    op.drop_table('conversations')
    op.drop_index('idx_al_category', table_name='approval_links')
    op.drop_table('approval_links')
    op.drop_table('agents')
    # ### end Alembic commands ###
