"""
添加邮件服务相关表

Revision ID: 20250414_0006
Revises: 20250412_0005
Create Date: 2025-04-14
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql

# revision identifiers, used by Alembic.
revision = '20250414_0006'
down_revision = '20250412_0005'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###

    # 创建 email_templates 表
    op.create_table('email_templates',
        sa.Column('id', sa.String(36), nullable=False),
        sa.Column('template_key', sa.String(100), nullable=False),
        sa.Column('name', sa.String(200), nullable=False),
        sa.Column('subject', sa.String(500), nullable=False),
        sa.Column('content_html', sa.Text(), nullable=False),
        sa.Column('content_text', sa.Text(), nullable=True),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('is_default', sa.Boolean(), nullable=False, default=False),
        sa.Column('created_at', sa.DateTime(), nullable=False),
        sa.Column('updated_at', sa.DateTime(), nullable=False),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('template_key')
    )

    # 创建索引
    op.create_index('ix_email_template_key', 'email_templates', ['template_key'])

    # 创建 email_logs 表
    op.create_table('email_logs',
        sa.Column('id', sa.String(36), nullable=False),
        sa.Column('recipient_email', sa.String(255), nullable=False),
        sa.Column('recipient_name', sa.String(100), nullable=True),
        sa.Column('template_key', sa.String(100), nullable=False),
        sa.Column('subject', sa.String(500), nullable=False),
        sa.Column('status', sa.Enum('pending', 'sent', 'failed', 'retrying', name='emailstatus'), nullable=False),
        sa.Column('retry_count', sa.Integer(), nullable=False, default=0),
        sa.Column('error_message', sa.Text(), nullable=True),
        sa.Column('sent_at', sa.DateTime(), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=False),
        sa.PrimaryKeyConstraint('id')
    )

    # 创建索引
    op.create_index('ix_email_log_recipient', 'email_logs', ['recipient_email'])
    op.create_index('ix_email_log_status', 'email_logs', ['status'])
    op.create_index('ix_email_log_template', 'email_logs', ['template_key'])
    op.create_index('ix_email_log_created', 'email_logs', ['created_at'])

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###

    # 删除索引
    op.drop_index('ix_email_log_created', table_name='email_logs')
    op.drop_index('ix_email_log_template', table_name='email_logs')
    op.drop_index('ix_email_log_status', table_name='email_logs')
    op.drop_index('ix_email_log_recipient', table_name='email_logs')

    op.drop_index('ix_email_template_key', table_name='email_templates')

    # 删除表
    op.drop_table('email_logs')
    op.drop_table('email_templates')

    # 删除枚举类型（PostgreSQL 需要）
    # op.execute('DROP TYPE IF EXISTS emailstatus')

    # ### end Alembic commands ###
