"""
添加站内信通知表

Revision ID: 20260806_0024
Revises: 20260803_0023
Create Date: 2026-08-06
"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '20260806_0024'
down_revision = '20260803_0023'
branch_labels = None
depends_on = None


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

    # 创建 notifications 表
    op.create_table('notifications',
        sa.Column('id', sa.String(36), nullable=False),
        sa.Column('recipient_user_id', sa.String(36), nullable=False),
        sa.Column('recipient_email', sa.String(255), nullable=True),
        sa.Column('title', sa.String(500), nullable=False),
        sa.Column('content', sa.Text(), nullable=False),
        sa.Column('content_text', sa.Text(), nullable=True),
        sa.Column('template_key', sa.String(100), nullable=True),
        sa.Column('source', sa.String(50), nullable=False),
        sa.Column('status', sa.Enum('pending', 'sent', 'failed', 'retrying', name='notificationstatus'), nullable=False),
        sa.Column('is_read', sa.Boolean(), nullable=False, default=False),
        sa.Column('read_at', sa.DateTime(), nullable=True),
        sa.Column('sent_at', sa.DateTime(), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=False),
        sa.Column('retry_count', sa.Integer(), nullable=False, default=0),
        sa.Column('error_message', sa.Text(), nullable=True),
        sa.Column('extra_metadata', sa.JSON(), nullable=True),
        sa.PrimaryKeyConstraint('id'),
        sa.ForeignKeyConstraint(['recipient_user_id'], ['users.id'])
    )

    # 创建索引
    op.create_index('ix_notifications_recipient_user_id', 'notifications', ['recipient_user_id'])
    op.create_index('ix_notifications_status', 'notifications', ['status'])
    op.create_index('ix_notifications_is_read', 'notifications', ['is_read'])
    op.create_index('ix_notifications_created_at', 'notifications', ['created_at'])
    op.create_index('ix_notifications_source', 'notifications', ['source'])

    # ### end Alembic commands ###


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

    # 删除索引
    op.drop_index('ix_notifications_source', table_name='notifications')
    op.drop_index('ix_notifications_created_at', table_name='notifications')
    op.drop_index('ix_notifications_is_read', table_name='notifications')
    op.drop_index('ix_notifications_status', table_name='notifications')
    op.drop_index('ix_notifications_recipient_user_id', table_name='notifications')

    # 删除表
    op.drop_table('notifications')

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

    # ### end Alembic commands ###
