"""add agent application ticket departments

Revision ID: 028_ticket_agent_departments
Revises: 027_ticket_statuses
Create Date: 2026-06-14
"""

from alembic import op
import sqlalchemy as sa

revision = "028_ticket_agent_departments"
down_revision = "027_ticket_statuses"
branch_labels = None
depends_on = None


AGENT_DEPARTMENTS = [
    {
        "code": "agent_yunxiang",
        "name": "云享伙伴申请",
        "description": "云享伙伴资格申请，需达到 LV3 及以上会员等级",
        "min_member_level": 3,
        "sort_order": 41,
        "auto_reply_content": "您好，已收到您的云享伙伴申请。我们会结合会员等级、累计有效消费和销售质量进行审核。",
    },
    {
        "code": "agent_yunce",
        "name": "云策伙伴申请",
        "description": "云策伙伴资格申请，需达到 LV4 及以上会员等级",
        "min_member_level": 4,
        "sort_order": 42,
        "auto_reply_content": "您好，已收到您的云策伙伴申请。我们会结合会员等级、累计有效消费和销售质量进行审核。",
    },
    {
        "code": "agent_yunling",
        "name": "云领伙伴申请",
        "description": "云领伙伴资格申请，需达到 LV5 会员等级",
        "min_member_level": 5,
        "sort_order": 43,
        "auto_reply_content": "您好，已收到您的云领伙伴申请。我们会结合会员等级、累计有效消费和销售质量进行审核。",
    },
]


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


def _index_names(bind: sa.engine.Connection, table_name: str) -> set[str]:
    return {index["name"] for index in sa.inspect(bind).get_indexes(table_name)}


def upgrade() -> None:
    bind = op.get_bind()
    tables = set(sa.inspect(bind).get_table_names())
    if "ticket_departments" not in tables:
        return

    columns = _column_names(bind, "ticket_departments")
    if "code" not in columns:
        op.add_column("ticket_departments", sa.Column("code", sa.String(64), nullable=True))
    if "min_member_level" not in columns:
        op.add_column("ticket_departments", sa.Column("min_member_level", sa.Integer(), nullable=True))

    indexes = _index_names(bind, "ticket_departments")
    if "ix_ticket_departments_code" not in indexes:
        op.create_index("ix_ticket_departments_code", "ticket_departments", ["code"], unique=True)

    for item in AGENT_DEPARTMENTS:
        existing_id = bind.execute(
            sa.text("SELECT id FROM ticket_departments WHERE code = :code"),
            {"code": item["code"]},
        ).scalar()
        params = {
            **item,
            "is_enabled": True,
            "auto_reply_enabled": True,
        }
        if existing_id:
            bind.execute(
                sa.text(
                    """
                    UPDATE ticket_departments
                    SET name = :name,
                        description = :description,
                        is_enabled = :is_enabled,
                        sort_order = :sort_order,
                        min_member_level = :min_member_level,
                        auto_reply_enabled = :auto_reply_enabled,
                        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, :is_enabled, :sort_order, :min_member_level,
                         :auto_reply_enabled, :auto_reply_content)
                    """
                ),
                params,
            )


def downgrade() -> None:
    bind = op.get_bind()
    tables = set(sa.inspect(bind).get_table_names())
    if "ticket_departments" not in tables:
        return

    bind.execute(
        sa.text("DELETE FROM ticket_departments WHERE code IN :codes").bindparams(
            sa.bindparam("codes", expanding=True)
        ),
        {"codes": tuple(item["code"] for item in AGENT_DEPARTMENTS)},
    )

    indexes = _index_names(bind, "ticket_departments")
    if "ix_ticket_departments_code" in indexes:
        op.drop_index("ix_ticket_departments_code", table_name="ticket_departments")

    columns = _column_names(bind, "ticket_departments")
    if "min_member_level" in columns:
        op.drop_column("ticket_departments", "min_member_level")
    if "code" in columns:
        op.drop_column("ticket_departments", "code")
