"""add support tickets

Revision ID: 026_tickets
Revises: 025_spend_events
Create Date: 2026-06-14
"""

from alembic import op
import sqlalchemy as sa

revision = "026_tickets"
down_revision = "025_spend_events"
branch_labels = None
depends_on = None


DEFAULT_DEPARTMENTS = [
    {
        "name": "售前咨询",
        "description": "产品选型、购买流程、配置建议",
        "is_enabled": True,
        "sort_order": 10,
        "auto_reply_enabled": True,
        "auto_reply_content": "您好，已收到您的咨询。请尽量补充预算、地区、用途和期望配置，我们会尽快给出建议。",
    },
    {
        "name": "技术支持",
        "description": "实例连接、系统、网络和配置问题",
        "is_enabled": True,
        "sort_order": 20,
        "auto_reply_enabled": True,
        "auto_reply_content": "您好，技术支持已收到工单。若问题与服务器有关，请补充实例、报错截图、操作时间和复现步骤。",
    },
    {
        "name": "财务账单",
        "description": "充值、订单、发票、扣费和续费问题",
        "is_enabled": True,
        "sort_order": 30,
        "auto_reply_enabled": True,
        "auto_reply_content": "您好，财务工单已收到。请提供订单号、充值流水或扣费时间，方便我们核对。",
    },
    {
        "name": "会员与伙伴",
        "description": "会员等级、积分、代理伙伴和客户绑定",
        "is_enabled": True,
        "sort_order": 40,
        "auto_reply_enabled": True,
        "auto_reply_content": "您好，会员与伙伴相关问题已收到。请说明您的会员等级、代理诉求或需要核对的客户信息。",
    },
]


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

    if "ticket_departments" not in tables:
        op.create_table(
            "ticket_departments",
            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("name", sa.String(80), nullable=False),
            sa.Column("description", sa.String(255), nullable=True),
            sa.Column("is_enabled", sa.Boolean(), nullable=False, server_default=sa.text("1")),
            sa.Column("sort_order", sa.Integer(), nullable=False, server_default="0"),
            sa.Column("auto_reply_enabled", sa.Boolean(), nullable=False, server_default=sa.text("0")),
            sa.Column("auto_reply_content", sa.Text(), nullable=True),
        )
        op.create_index("ix_ticket_departments_is_enabled", "ticket_departments", ["is_enabled"])

        department_table = sa.table(
            "ticket_departments",
            sa.column("name", sa.String),
            sa.column("description", sa.String),
            sa.column("is_enabled", sa.Boolean),
            sa.column("sort_order", sa.Integer),
            sa.column("auto_reply_enabled", sa.Boolean),
            sa.column("auto_reply_content", sa.Text),
        )
        op.bulk_insert(department_table, DEFAULT_DEPARTMENTS)

    if "tickets" not in tables:
        op.create_table(
            "tickets",
            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("ticket_no", sa.String(32), nullable=False),
            sa.Column("user_id", sa.BigInteger(), sa.ForeignKey("users.id", ondelete="CASCADE"), nullable=False),
            sa.Column("department_id", sa.Integer(), sa.ForeignKey("ticket_departments.id", ondelete="SET NULL"), nullable=True),
            sa.Column("department_name", sa.String(80), nullable=True),
            sa.Column("instance_id", sa.Integer(), sa.ForeignKey("instances.id", ondelete="SET NULL"), nullable=True),
            sa.Column("subject", sa.String(160), nullable=False),
            sa.Column("priority", sa.Enum("low", "normal", "high", "urgent", name="ticketpriority"), nullable=False, server_default="normal"),
            sa.Column("status", sa.Enum("open", "pending", "answered", "closed", name="ticketstatus"), nullable=False, server_default="open"),
            sa.Column("last_message_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("last_reply_by", sa.Enum("user", "admin", "system", name="ticketsendertype"), nullable=True),
            sa.Column("user_unread_count", sa.Integer(), nullable=False, server_default="0"),
            sa.Column("admin_unread_count", sa.Integer(), nullable=False, server_default="0"),
            sa.Column("closed_at", sa.DateTime(timezone=True), nullable=True),
            sa.UniqueConstraint("ticket_no", name="uq_tickets_ticket_no"),
        )
        op.create_index("ix_tickets_ticket_no", "tickets", ["ticket_no"])
        op.create_index("ix_tickets_user_id", "tickets", ["user_id"])
        op.create_index("ix_tickets_department_id", "tickets", ["department_id"])
        op.create_index("ix_tickets_instance_id", "tickets", ["instance_id"])
        op.create_index("ix_tickets_priority", "tickets", ["priority"])
        op.create_index("ix_tickets_status", "tickets", ["status"])
        op.create_index("ix_tickets_last_message_at", "tickets", ["last_message_at"])

    if "ticket_messages" not in tables:
        op.create_table(
            "ticket_messages",
            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("ticket_id", sa.Integer(), sa.ForeignKey("tickets.id", ondelete="CASCADE"), nullable=False),
            sa.Column("sender_type", sa.Enum("user", "admin", "system", name="ticketsendertype"), nullable=False),
            sa.Column("sender_id", sa.BigInteger(), sa.ForeignKey("users.id", ondelete="SET NULL"), nullable=True),
            sa.Column("content", sa.Text(), nullable=False),
            sa.Column("attachments", sa.JSON(), nullable=True),
            sa.Column("is_internal", sa.Boolean(), nullable=False, server_default=sa.text("0")),
        )
        op.create_index("ix_ticket_messages_ticket_id", "ticket_messages", ["ticket_id"])
        op.create_index("ix_ticket_messages_sender_type", "ticket_messages", ["sender_type"])
        op.create_index("ix_ticket_messages_sender_id", "ticket_messages", ["sender_id"])


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

    if "ticket_messages" in tables:
        op.drop_index("ix_ticket_messages_sender_id", table_name="ticket_messages")
        op.drop_index("ix_ticket_messages_sender_type", table_name="ticket_messages")
        op.drop_index("ix_ticket_messages_ticket_id", table_name="ticket_messages")
        op.drop_table("ticket_messages")

    if "tickets" in tables:
        op.drop_index("ix_tickets_last_message_at", table_name="tickets")
        op.drop_index("ix_tickets_status", table_name="tickets")
        op.drop_index("ix_tickets_priority", table_name="tickets")
        op.drop_index("ix_tickets_instance_id", table_name="tickets")
        op.drop_index("ix_tickets_department_id", table_name="tickets")
        op.drop_index("ix_tickets_user_id", table_name="tickets")
        op.drop_index("ix_tickets_ticket_no", table_name="tickets")
        op.drop_table("tickets")

    if "ticket_departments" in tables:
        op.drop_index("ix_ticket_departments_is_enabled", table_name="ticket_departments")
        op.drop_table("ticket_departments")
