"""add_member_levels_user_memberships_point_logs

Revision ID: 8fed5c0135f1
Revises: 024_instance_disk_addon_resize
Create Date: 2026-06-14 10:55:21.422165

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision: str = '8fed5c0135f1'
down_revision: Union[str, None] = '024_instance_disk_addon_resize'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # 创建会员等级表
    op.create_table('member_levels',
        sa.Column('name', sa.String(length=50), nullable=False, comment='等级名称，如：普通会员、黄金会员'),
        sa.Column('level', sa.Integer(), nullable=False, comment='等级序号，1=最低'),
        sa.Column('min_total_spent', sa.Integer(), nullable=False, comment='升级所需累计消费金额，单位分'),
        sa.Column('self_discount_rate', sa.Integer(), nullable=False, comment='自购折扣率，万分比，10000=原价'),
        sa.Column('points_multiplier', sa.Integer(), nullable=False, comment='积分倍率，万分比，10000=1.0x'),
        sa.Column('max_points_discount_rate', sa.Integer(), nullable=False, comment='积分抵扣上限，万分比，如3500=35%'),
        sa.Column('can_apply_agent', sa.Boolean(), nullable=False, comment='是否允许申请代理'),
        sa.Column('sort_order', sa.Integer(), nullable=False, comment='排序权重'),
        sa.Column('enabled', sa.Boolean(), nullable=False, comment='是否启用'),
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('level')
    )
    op.create_index(op.f('ix_member_levels_level'), 'member_levels', ['level'], unique=False)

    # 创建用户会员信息表
    op.create_table('user_memberships',
        sa.Column('user_id', sa.BigInteger(), nullable=False, comment='用户ID'),
        sa.Column('member_level_id', sa.Integer(), nullable=True, comment='当前会员等级ID'),
        sa.Column('total_spent', sa.Integer(), nullable=False, comment='累计消费金额，单位分'),
        sa.Column('total_points', sa.Integer(), nullable=False, comment='累计获得积分'),
        sa.Column('available_points', sa.Integer(), nullable=False, comment='当前可用积分'),
        sa.Column('upgraded_at', sa.DateTime(), nullable=True, comment='等级升级时间'),
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
        sa.ForeignKeyConstraint(['member_level_id'], ['member_levels.id'], ondelete='SET NULL'),
        sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_user_memberships_member_level_id'), 'user_memberships', ['member_level_id'], unique=False)
    op.create_index(op.f('ix_user_memberships_user_id'), 'user_memberships', ['user_id'], unique=True)

    # 创建积分流水表
    op.create_table('point_logs',
        sa.Column('user_id', sa.BigInteger(), nullable=False),
        sa.Column('change_type', sa.Enum('order_reward', 'order_refund', 'points_use', 'points_refund', 'admin_adjust', 'activity_gift', name='pointchangetype'), nullable=False),
        sa.Column('delta', sa.Integer(), nullable=False, comment='变动积分，正入负出'),
        sa.Column('balance_after', sa.Integer(), nullable=False, comment='变动后可用积分'),
        sa.Column('order_id', sa.Integer(), nullable=True),
        sa.Column('ref_no', sa.String(length=64), nullable=True),
        sa.Column('operator_id', sa.BigInteger(), nullable=True),
        sa.Column('remark', sa.String(length=255), nullable=True),
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
        sa.ForeignKeyConstraint(['order_id'], ['orders.id'], ondelete='SET NULL'),
        sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_point_logs_change_type'), 'point_logs', ['change_type'], unique=False)
    op.create_index(op.f('ix_point_logs_order_id'), 'point_logs', ['order_id'], unique=False)
    op.create_index(op.f('ix_point_logs_ref_no'), 'point_logs', ['ref_no'], unique=False)
    op.create_index(op.f('ix_point_logs_user_id'), 'point_logs', ['user_id'], unique=False)


def downgrade() -> None:
    """Downgrade schema."""
    op.drop_index(op.f('ix_point_logs_user_id'), table_name='point_logs')
    op.drop_index(op.f('ix_point_logs_ref_no'), table_name='point_logs')
    op.drop_index(op.f('ix_point_logs_order_id'), table_name='point_logs')
    op.drop_index(op.f('ix_point_logs_change_type'), table_name='point_logs')
    op.drop_table('point_logs')
    op.drop_index(op.f('ix_user_memberships_user_id'), table_name='user_memberships')
    op.drop_index(op.f('ix_user_memberships_member_level_id'), table_name='user_memberships')
    op.drop_table('user_memberships')
    op.drop_index(op.f('ix_member_levels_level'), table_name='member_levels')
    op.drop_table('member_levels')
