"""add configurable spend events

Revision ID: 025_spend_events
Revises: 8fed5c0135f1
Create Date: 2026-06-14
"""

from alembic import op
import sqlalchemy as sa

revision = "025_spend_events"
down_revision = "8fed5c0135f1"
branch_labels = None
depends_on = None


DEFAULT_EVENT_TYPES = [
    {
        "code": "server_purchase",
        "label": "购买云服务器",
        "description": "用户购买云服务器产生的实付产品消费",
        "enabled": True,
        "sort_order": 10,
    },
    {
        "code": "server_renewal",
        "label": "云服务器续费",
        "description": "云服务器续费中的实例基础续费金额",
        "enabled": True,
        "sort_order": 20,
    },
    {
        "code": "package_upgrade",
        "label": "套餐升级/配置升级",
        "description": "切换到更高套餐或配置升级产生的补差金额",
        "enabled": True,
        "sort_order": 30,
    },
    {
        "code": "disk_create",
        "label": "添加弹性云盘",
        "description": "新增数据盘产生的本期扣费",
        "enabled": True,
        "sort_order": 40,
    },
    {
        "code": "disk_resize",
        "label": "磁盘扩容",
        "description": "数据盘扩容产生的本期扣费",
        "enabled": True,
        "sort_order": 50,
    },
    {
        "code": "disk_renewal",
        "label": "磁盘续费",
        "description": "云服务器续费时附带的数据盘续费金额",
        "enabled": True,
        "sort_order": 60,
    },
    {
        "code": "bandwidth_upgrade",
        "label": "带宽升级",
        "description": "独立带宽升级产生的扣费，预留",
        "enabled": True,
        "sort_order": 70,
    },
    {
        "code": "backup_addon",
        "label": "备份/快照包",
        "description": "备份、快照等增值服务消费，预留",
        "enabled": True,
        "sort_order": 80,
    },
]


def upgrade() -> None:
    bind = op.get_bind()
    tables = set(sa.inspect(bind).get_table_names())

    if "spend_event_type_configs" not in tables:
        op.create_table(
            "spend_event_type_configs",
            sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("code", sa.String(64), nullable=False),
            sa.Column("label", sa.String(64), nullable=False),
            sa.Column("description", sa.String(255), nullable=True),
            sa.Column("enabled", sa.Boolean(), nullable=False, server_default=sa.text("1")),
            sa.Column("sort_order", sa.Integer(), nullable=False, server_default="0"),
            sa.UniqueConstraint("code", name="uq_spend_event_type_configs_code"),
        )
        op.create_index("ix_spend_event_type_configs_code", "spend_event_type_configs", ["code"])
        op.create_index("ix_spend_event_type_configs_enabled", "spend_event_type_configs", ["enabled"])

        event_type_table = sa.table(
            "spend_event_type_configs",
            sa.column("code", sa.String),
            sa.column("label", sa.String),
            sa.column("description", sa.String),
            sa.column("enabled", sa.Boolean),
            sa.column("sort_order", sa.Integer),
        )
        op.bulk_insert(event_type_table, DEFAULT_EVENT_TYPES)

    if "user_spend_events" not in tables:
        op.create_table(
            "user_spend_events",
            sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("user_id", sa.BigInteger(), sa.ForeignKey("users.id", ondelete="CASCADE"), nullable=False),
            sa.Column("event_type", sa.String(64), nullable=False),
            sa.Column("event_key", sa.String(128), nullable=False),
            sa.Column("amount_cents", sa.Integer(), nullable=False, comment="Signed amount in cents"),
            sa.Column("order_id", sa.Integer(), sa.ForeignKey("orders.id", ondelete="SET NULL"), nullable=True),
            sa.Column("ref_no", sa.String(64), nullable=True),
            sa.Column("remark", sa.Text(), nullable=True),
            sa.Column("payload_snapshot", sa.JSON(), nullable=True),
            sa.UniqueConstraint("event_key", name="uq_user_spend_events_event_key"),
        )
        op.create_index("ix_user_spend_events_user_id", "user_spend_events", ["user_id"])
        op.create_index("ix_user_spend_events_event_type", "user_spend_events", ["event_type"])
        op.create_index("ix_user_spend_events_order_id", "user_spend_events", ["order_id"])
        op.create_index("ix_user_spend_events_ref_no", "user_spend_events", ["ref_no"])

        if "user_memberships" in tables:
            op.execute(
                """
                INSERT INTO user_spend_events (
                    created_at,
                    updated_at,
                    user_id,
                    event_type,
                    event_key,
                    amount_cents,
                    ref_no,
                    remark
                )
                SELECT
                    NOW(),
                    NOW(),
                    user_id,
                    'server_purchase',
                    CONCAT('legacy_membership_total_spent:', user_id),
                    total_spent,
                    CONCAT('legacy:', user_id),
                    '历史会员累计消费迁移'
                FROM user_memberships
                WHERE total_spent > 0
                """
            )


def downgrade() -> None:
    bind = op.get_bind()
    tables = set(sa.inspect(bind).get_table_names())

    if "user_spend_events" in tables:
        op.drop_index("ix_user_spend_events_ref_no", table_name="user_spend_events")
        op.drop_index("ix_user_spend_events_order_id", table_name="user_spend_events")
        op.drop_index("ix_user_spend_events_event_type", table_name="user_spend_events")
        op.drop_index("ix_user_spend_events_user_id", table_name="user_spend_events")
        op.drop_table("user_spend_events")

    if "spend_event_type_configs" in tables:
        op.drop_index("ix_spend_event_type_configs_enabled", table_name="spend_event_type_configs")
        op.drop_index("ix_spend_event_type_configs_code", table_name="spend_event_type_configs")
        op.drop_table("spend_event_type_configs")
