"""rename agent tiers to cloud partner names

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


revision = "035_agent_tier_names"
down_revision = "034_lv1_agent_tiers"
branch_labels = None
depends_on = None


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


AGENT_DEPARTMENTS = [
    ("agent_yunqi", "云启伙伴申请", "云启伙伴资格申请，需达到 LV3 及以上会员等级"),
    ("agent_yunxiang", "云享伙伴申请", "云享伙伴资格申请，需达到 LV3 及以上会员等级"),
    ("agent_yunce", "云策伙伴申请", "云策伙伴资格申请，需达到 LV3 及以上会员等级"),
    ("agent_yunling", "云领伙伴申请", "云领伙伴资格申请，需达到 LV3 及以上会员等级"),
    ("agent_yunzun", "云尊伙伴申请", "云尊伙伴资格申请，需达到 LV3 及以上会员等级"),
    ("agent_yunguan", "云冠伙伴申请", "云冠伙伴资格申请，需达到 LV3 及以上会员等级"),
]


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

    for old_code, code, name, _old_name, cost_rate, sort_order, description, _old_description in AGENT_TIERS:
        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,
            },
        )

    for old_code, code, _name, _old_name, _cost_rate, _sort_order, _description, _old_description in AGENT_TIERS:
        bind.execute(
            sa.text("UPDATE user_memberships SET agent_tier = :code WHERE agent_tier = :old_code"),
            {"old_code": old_code, "code": code},
        )
        bind.execute(
            sa.text("UPDATE user_agents SET apply_tier = :code WHERE apply_tier = :old_code"),
            {"old_code": old_code, "code": code},
        )

    tables = set(sa.inspect(bind).get_table_names())
    if "ticket_departments" in tables:
        columns = {column["name"] for column in sa.inspect(bind).get_columns("ticket_departments")}
        if "code" in columns:
            for index, (code, name, description) in enumerate(AGENT_DEPARTMENTS, start=41):
                params = {
                    "code": code,
                    "name": name,
                    "description": description,
                    "sort_order": index,
                    "min_member_level": 3,
                    "auto_reply_content": f"您好，已收到您的{name}。我们会结合会员等级、累计有效消费和销售质量进行审核。",
                }
                existing_id = bind.execute(
                    sa.text("SELECT id FROM ticket_departments WHERE code = :code"),
                    {"code": code},
                ).scalar()
                if existing_id:
                    bind.execute(
                        sa.text(
                            """
                            UPDATE ticket_departments
                            SET name = :name,
                                description = :description,
                                sort_order = :sort_order,
                                min_member_level = :min_member_level,
                                auto_reply_enabled = 1,
                                auto_reply_content = :auto_reply_content
                            WHERE code = :code
                            """
                        ),
                        params,
                    )
                else:
                    bind.execute(
                        sa.text(
                            """
                            INSERT INTO ticket_departments
                                (code, name, description, is_enabled, sort_order, min_member_level,
                                 auto_reply_enabled, auto_reply_content)
                            VALUES
                                (:code, :name, :description, 1, :sort_order, :min_member_level,
                                 1, :auto_reply_content)
                            """
                        ),
                        params,
                    )


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

    tables = set(sa.inspect(bind).get_table_names())
    if "ticket_departments" in tables:
        bind.execute(
            sa.text(
                """
                DELETE FROM ticket_departments
                WHERE code IN ('agent_yunqi', 'agent_yunzun', 'agent_yunguan')
                """
            )
        )
        bind.execute(
            sa.text(
                """
                UPDATE ticket_departments
                SET description = '云策伙伴资格申请，需达到 LV4 及以上会员等级',
                    min_member_level = 4
                WHERE code = 'agent_yunce'
                """
            )
        )
        bind.execute(
            sa.text(
                """
                UPDATE ticket_departments
                SET description = '云领伙伴资格申请，需达到 LV5 会员等级',
                    min_member_level = 5
                WHERE code = 'agent_yunling'
                """
            )
        )

    for old_code, code, _name, old_name, cost_rate, sort_order, _description, old_description in AGENT_TIERS:
        bind.execute(
            sa.text(
                """
                UPDATE agent_tiers
                SET code = :old_code,
                    name = :old_name,
                    cost_rate = :cost_rate,
                    min_member_level = 3,
                    sort_order = :sort_order,
                    enabled = 1,
                    description = :old_description
                WHERE code = :code
                """
            ),
            {
                "old_code": old_code,
                "code": code,
                "old_name": old_name,
                "cost_rate": cost_rate,
                "sort_order": sort_order,
                "old_description": old_description,
            },
        )

    for old_code, code, _name, _old_name, _cost_rate, _sort_order, _description, _old_description in AGENT_TIERS:
        bind.execute(
            sa.text("UPDATE user_memberships SET agent_tier = :old_code WHERE agent_tier = :code"),
            {"old_code": old_code, "code": code},
        )
        bind.execute(
            sa.text("UPDATE user_agents SET apply_tier = :old_code WHERE apply_tier = :code"),
            {"old_code": old_code, "code": code},
        )
