"""adjust lv1 threshold and six agent tiers

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


revision = "034_lv1_agent_tiers"
down_revision = "033_member_levels_sales_plan"
branch_labels = None
depends_on = None


AGENT_TIERS = [
    ("p1", "P1 伙伴", 7800, 1, "P1 伙伴：代理成本78%，适合初级推广合作"),
    ("p2", "P2 伙伴", 7200, 2, "P2 伙伴：代理成本72%，适合稳定推广合作"),
    ("p3", "P3 伙伴", 6600, 3, "P3 伙伴：代理成本66%，适合进阶代理合作"),
    ("p4", "P4 伙伴", 6000, 4, "P4 伙伴：代理成本60%，需更高销售质量"),
    ("p5", "P5 伙伴", 5600, 5, "P5 伙伴：代理成本56%，需风控复核"),
    ("p6", "P6 伙伴", 5000, 6, "P6 伙伴：代理成本50%，核心代理档位"),
]


def upgrade() -> None:
    bind = op.get_bind()

    bind.execute(sa.text("UPDATE member_levels SET min_total_spent = 1 WHERE level = 1"))

    legacy_map = {
        "yunxiang": AGENT_TIERS[0],
        "yunce": AGENT_TIERS[1],
        "yunling": AGENT_TIERS[2],
    }
    for old_code, (code, name, cost_rate, sort_order, description) in legacy_map.items():
        bind.execute(
            sa.text(
                """
                UPDATE agent_tiers
                SET code = :code,
                    name = :name,
                    cost_rate = :cost_rate,
                    min_member_level = 3,
                    sort_order = :sort_order,
                    enabled = 1,
                    description = :description
                WHERE code = :old_code
                """
            ),
            {
                "old_code": old_code,
                "code": code,
                "name": name,
                "cost_rate": cost_rate,
                "sort_order": sort_order,
                "description": description,
            },
        )

    bind.execute(sa.text("UPDATE user_memberships SET agent_tier = 'p1' WHERE agent_tier = 'yunxiang'"))
    bind.execute(sa.text("UPDATE user_memberships SET agent_tier = 'p2' WHERE agent_tier = 'yunce'"))
    bind.execute(sa.text("UPDATE user_memberships SET agent_tier = 'p3' WHERE agent_tier = 'yunling'"))
    bind.execute(sa.text("UPDATE user_agents SET apply_tier = 'p1' WHERE apply_tier = 'yunxiang'"))
    bind.execute(sa.text("UPDATE user_agents SET apply_tier = 'p2' WHERE apply_tier = 'yunce'"))
    bind.execute(sa.text("UPDATE user_agents SET apply_tier = 'p3' WHERE apply_tier = 'yunling'"))

    for code, name, cost_rate, sort_order, description in AGENT_TIERS:
        bind.execute(
            sa.text(
                """
                INSERT INTO agent_tiers
                    (name, code, cost_rate, min_member_level, min_total_spend,
                     max_coupon_discount_rate, max_monthly_coupon_count, sort_order, enabled, description)
                SELECT
                    :name, :code, :cost_rate, 3, 0,
                    5000, 10, :sort_order, 1, :description
                WHERE NOT EXISTS (SELECT 1 FROM agent_tiers WHERE code = :code)
                """
            ),
            {
                "code": code,
                "name": name,
                "cost_rate": cost_rate,
                "sort_order": sort_order,
                "description": description,
            },
        )


def downgrade() -> None:
    bind = op.get_bind()
    bind.execute(sa.text("UPDATE member_levels SET min_total_spent = 100 WHERE level = 1"))
