"""add template market tables

Revision ID: 002
Revises: 001
Create Date: 2024-01-17 12:00:00.000000

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql

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


def upgrade() -> None:
    # 创建工作流模板表
    op.create_table(
        'workflow_templates',
        sa.Column('id', mysql.CHAR(length=36), nullable=False, comment='模板ID'),
        sa.Column('name', sa.String(length=100), nullable=False, comment='模板名称'),
        sa.Column('description', sa.Text(), nullable=True, comment='模板描述'),
        sa.Column('category', sa.String(length=50), nullable=True, comment='分类: llm/data_processing/automation/integration/other'),
        sa.Column('tags', sa.JSON(), nullable=True, comment='标签列表'),
        sa.Column('nodes', sa.JSON(), nullable=False, comment='节点配置'),
        sa.Column('edges', sa.JSON(), nullable=False, comment='边配置'),
        sa.Column('thumbnail', sa.String(length=500), nullable=True, comment='缩略图URL'),
        sa.Column('is_public', sa.Boolean(), server_default='1', nullable=True, comment='是否公开'),
        sa.Column('is_featured', sa.Boolean(), server_default='0', nullable=True, comment='是否精选'),
        sa.Column('view_count', sa.Integer(), server_default='0', nullable=True, comment='查看次数'),
        sa.Column('use_count', sa.Integer(), server_default='0', nullable=True, comment='使用次数'),
        sa.Column('rating_count', sa.Integer(), server_default='0', nullable=True, comment='评分次数'),
        sa.Column('rating_avg', sa.Float(), server_default='0.0', nullable=True, comment='平均评分'),
        sa.Column('user_id', mysql.CHAR(length=36), nullable=False, comment='创建者ID'),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=True, comment='创建时间'),
        sa.Column('updated_at', sa.DateTime(), server_default=sa.text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), nullable=True, comment='更新时间'),
        sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
        sa.PrimaryKeyConstraint('id'),
        mysql_charset='utf8mb4',
        mysql_collate='utf8mb4_unicode_ci'
    )
    
    # 创建模板评分表
    op.create_table(
        'template_ratings',
        sa.Column('id', mysql.CHAR(length=36), nullable=False, comment='评分ID'),
        sa.Column('template_id', mysql.CHAR(length=36), nullable=False, comment='模板ID'),
        sa.Column('user_id', mysql.CHAR(length=36), nullable=False, comment='用户ID'),
        sa.Column('rating', sa.Integer(), nullable=False, comment='评分: 1-5'),
        sa.Column('comment', sa.Text(), nullable=True, comment='评论'),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=True, comment='创建时间'),
        sa.Column('updated_at', sa.DateTime(), server_default=sa.text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), nullable=True, comment='更新时间'),
        sa.ForeignKeyConstraint(['template_id'], ['workflow_templates.id'], ),
        sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('template_id', 'user_id', name='uq_template_user_rating'),
        mysql_charset='utf8mb4',
        mysql_collate='utf8mb4_unicode_ci'
    )
    
    # 创建模板收藏表
    op.create_table(
        'template_favorites',
        sa.Column('id', mysql.CHAR(length=36), nullable=False, comment='收藏ID'),
        sa.Column('template_id', mysql.CHAR(length=36), nullable=False, comment='模板ID'),
        sa.Column('user_id', mysql.CHAR(length=36), nullable=False, comment='用户ID'),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=True, comment='创建时间'),
        sa.ForeignKeyConstraint(['template_id'], ['workflow_templates.id'], ),
        sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('template_id', 'user_id', name='uq_template_user_favorite'),
        mysql_charset='utf8mb4',
        mysql_collate='utf8mb4_unicode_ci'
    )


def downgrade() -> None:
    op.drop_table('template_favorites')
    op.drop_table('template_ratings')
    op.drop_table('workflow_templates')