#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
创建扩展属性表

Revision ID: 002
Revises: 001
Create Date: 2024-12-20 10:00:00.000000
"""

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.mysql import VARCHAR, TEXT, DATETIME, ENUM


# revision identifiers, used by Alembic.
revision = '002'
down_revision = '001'
branch_labels = None
depends_on = None


def upgrade():
    """创建扩展属性表"""
    # 创建扩展属性表
    op.create_table(
        'extended_properties',
        sa.Column('id', sa.Integer, primary_key=True, autoincrement=True, comment='主键ID'),
        sa.Column('created_at', DATETIME(fsp=6), nullable=False, server_default=sa.text('CURRENT_TIMESTAMP(6)'), comment='创建时间'),
        sa.Column('updated_at', DATETIME(fsp=6), nullable=False, server_default=sa.text('CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)'), comment='更新时间'),
        sa.Column('is_deleted', sa.Boolean, nullable=False, default=False, comment='是否删除（软删除标记）'),
        
        # 基本信息
        sa.Column('property_key', VARCHAR(200), nullable=False, comment='属性键（与属性名称相同）'),
        sa.Column('property_name', VARCHAR(200), nullable=False, comment='属性名称（唯一标识）'),
        sa.Column('property_type', ENUM('string', 'integer', 'float', 'boolean', 'date', 'datetime', 'text', 'select', 'multiselect'), nullable=False, comment='属性类型'),
        
        # 属性设置
        sa.Column('is_required', sa.Boolean, nullable=False, default=False, comment='是否必填'),
        sa.Column('is_enabled', sa.Boolean, nullable=False, default=True, comment='是否启用'),
        
        # 描述信息
        sa.Column('description', TEXT, comment='属性描述'),
        sa.Column('placeholder', VARCHAR(500), comment='输入提示'),
        
        # 默认值和选项
        sa.Column('default_value', TEXT, comment='默认值'),
        sa.Column('options', TEXT, comment='选项值（JSON格式，用于select和multiselect类型）'),
        
        # 验证规则
        sa.Column('validation_rules', TEXT, comment='验证规则（JSON格式）'),
        
        # 分类和排序
        sa.Column('category', VARCHAR(100), comment='属性分类'),
        sa.Column('sort_order', sa.Integer, nullable=False, default=0, comment='排序顺序'),
        
        # 显示设置
        sa.Column('display_width', sa.Integer, nullable=False, default=200, comment='显示宽度（像素）'),
        sa.Column('display_format', VARCHAR(100), comment='显示格式'),
        
        mysql_engine='InnoDB',
        mysql_charset='utf8mb4',
        mysql_collate='utf8mb4_unicode_ci',
        comment='扩展属性表'
    )
    
    # 创建索引
    op.create_index('idx_extended_properties_property_key', 'extended_properties', ['property_key'])
    op.create_index('idx_extended_properties_property_name', 'extended_properties', ['property_name'])
    op.create_index('idx_extended_properties_property_type', 'extended_properties', ['property_type'])
    op.create_index('idx_extended_properties_category', 'extended_properties', ['category'])
    op.create_index('idx_extended_properties_is_enabled', 'extended_properties', ['is_enabled'])
    op.create_index('idx_extended_properties_is_deleted', 'extended_properties', ['is_deleted'])
    op.create_index('idx_extended_properties_sort_order', 'extended_properties', ['sort_order'])
    
    # 创建唯一约束
    op.create_unique_constraint('uq_extended_properties_property_key', 'extended_properties', ['property_key'])
    op.create_unique_constraint('uq_extended_properties_property_name', 'extended_properties', ['property_name'])


def downgrade():
    """删除扩展属性表"""
    # 删除索引
    op.drop_index('idx_extended_properties_property_key', 'extended_properties')
    op.drop_index('idx_extended_properties_property_name', 'extended_properties')
    op.drop_index('idx_extended_properties_property_type', 'extended_properties')
    op.drop_index('idx_extended_properties_category', 'extended_properties')
    op.drop_index('idx_extended_properties_is_enabled', 'extended_properties')
    op.drop_index('idx_extended_properties_is_deleted', 'extended_properties')
    op.drop_index('idx_extended_properties_sort_order', 'extended_properties')
    
    # 删除唯一约束
    op.drop_constraint('uq_extended_properties_property_key', 'extended_properties', type_='unique')
    op.drop_constraint('uq_extended_properties_property_name', 'extended_properties', type_='unique')
    
    # 删除表
    op.drop_table('extended_properties')