"""align member levels with sales system plan

Revision ID: 033_member_levels_sales_plan
Revises: 032_referral_reward_point_type
Create Date: 2026-06-16
"""
import sqlalchemy as sa
from alembic import op


revision = "033_member_levels_sales_plan"
down_revision = "032_referral_reward_point_type"
branch_labels = None
depends_on = None


LEVELS = [
    # level, name, threshold cents, points multiplier, can apply agent, self cashback rate
    (0, "注册会员", 0, 10000, 0, 0),
    (1, "初级会员", 1, 10000, 0, 500),
    (2, "成长会员", 5000, 11000, 0, 800),
    (3, "专业会员", 20000, 13000, 1, 1000),
    (4, "商务会员", 50000, 15000, 1, 1500),
    (5, "旗舰会员", 100000, 20000, 1, 2000),
]


def _column_names(bind, table_name: str) -> set[str]:
    inspector = sa.inspect(bind)
    return {column["name"] for column in inspector.get_columns(table_name)}


def upgrade() -> None:
    bind = op.get_bind()
    cols = _column_names(bind, "member_levels")
    if "points_cashback_rate" not in cols:
        op.add_column(
            "member_levels",
            sa.Column(
                "points_cashback_rate",
                sa.Integer(),
                nullable=False,
                server_default=sa.text("0"),
                comment="自购积分返现比例，万分比，如500=5%",
            ),
        )

    for level, name, threshold, multiplier, can_apply, cashback in LEVELS:
        row = bind.execute(
            sa.text("SELECT id FROM member_levels WHERE level = :level"),
            {"level": level},
        ).fetchone()
        values = {
            "level": level,
            "name": name,
            "threshold": threshold,
            "multiplier": multiplier,
            "can_apply": can_apply,
            "cashback": cashback,
            "sort_order": level,
        }
        if row:
            bind.execute(
                sa.text(
                    """
                    UPDATE member_levels
                    SET name = :name,
                        min_total_spent = :threshold,
                        self_discount_rate = 10000,
                        points_multiplier = :multiplier,
                        points_cashback_rate = :cashback,
                        can_apply_agent = :can_apply,
                        commission_rate = :cashback,
                        sort_order = :sort_order,
                        enabled = 1
                    WHERE level = :level
                    """
                ),
                values,
            )
        else:
            bind.execute(
                sa.text(
                    """
                    INSERT INTO member_levels
                      (name, level, min_total_spent, self_discount_rate, points_multiplier,
                       points_cashback_rate, max_points_discount_rate, can_apply_agent,
                       commission_rate, sort_order, enabled)
                    VALUES
                      (:name, :level, :threshold, 10000, :multiplier,
                       :cashback, 3500, :can_apply, :cashback, :sort_order, 1)
                    """
                ),
                values,
            )


def downgrade() -> None:
    bind = op.get_bind()
    cols = _column_names(bind, "member_levels")
    if "points_cashback_rate" in cols:
        op.drop_column("member_levels", "points_cashback_rate")
