"""sync remaining model fields

Revision ID: 012_sync_remaining_fields
Revises: 011_add_conversation_impact
Create Date: 2026-06-16

v0.5.6: 补齐 dev-check-schema-drift 找到的 4 个漂移字段
- conversations.dify_conversation_id (VARCHAR(128), nullable)
- employees.it_level (VARCHAR(20), default 'silver')
- employees.it_level_source (VARCHAR(20), default 'system')
- employees.notes (JSON, default '{}')

为什么需要这个 migration:
  之前手动 011 只补了 NOT NULL 那些(坐席发消息会 500 的),
  但 dev-check-schema-drift.ps1 又发现 4 个字段也没建 migration。
  之前是 nullable 没立即暴露,运行 SELECT * FROM conversations 时
  PostgreSQL 会按顺序填,nullable 列缺不会立刻 500,但 INSERT/UPDATE
  涉及这些字段时会出错,或者 Alembic autogenerate 会持续报告漂移。
"""
from alembic import op
import sqlalchemy as sa


# revision identifiers
revision = '012_sync_remaining_fields'
down_revision = '011_add_conversation_impact'
branch_labels = None
depends_on = None


def upgrade() -> None:
    """加 4 个漂移字段"""

    # 1) conversations.dify_conversation_id - Dify 多轮对话上下文
    op.add_column(
        'conversations',
        sa.Column(
            'dify_conversation_id',
            sa.String(128),
            nullable=True,
            comment='Dify会话ID(多轮对话上下文)'
        )
    )

    # 2) employees.it_level - IT 技能等级
    op.add_column(
        'employees',
        sa.Column(
            'it_level',
            sa.String(20),
            nullable=False,
            server_default=sa.text("'silver'"),
            comment='IT技能等级(bronze/silver/gold/platinum/diamond/star/king)'
        )
    )

    # 3) employees.it_level_source - 等级来源
    op.add_column(
        'employees',
        sa.Column(
            'it_level_source',
            sa.String(20),
            nullable=False,
            server_default=sa.text("'system'"),
            comment='等级来源(system/manual/assessment)'
        )
    )

    # 4) employees.notes - 坐席备注 JSON
    op.add_column(
        'employees',
        sa.Column(
            'notes',
            sa.JSON(),
            nullable=False,
            server_default=sa.text("'{}'"),
            comment='坐席备注(JSON 格式)'
        )
    )


def downgrade() -> None:
    """删除 4 个字段"""
    op.drop_column('employees', 'notes')
    op.drop_column('employees', 'it_level_source')
    op.drop_column('employees', 'it_level')
    op.drop_column('conversations', 'dify_conversation_id')
