"""add composite indexes for ticket query performance

Revision ID: 029_ticket_indexes
Revises: 028_ticket_agent_departments
Create Date: 2026-06-15
"""

from alembic import op

revision = "029_ticket_indexes"
down_revision = "028_ticket_agent_departments"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # Composite index for user-facing ticket list: WHERE user_id ORDER BY last_message_at DESC, id DESC
    op.create_index(
        "ix_tickets_user_lastmsg",
        "tickets",
        ["user_id", "last_message_at", "id"],
    )
    # Composite index for message list: WHERE ticket_id ORDER BY id ASC
    op.create_index(
        "ix_ticket_messages_ticket_sender",
        "ticket_messages",
        ["ticket_id", "id"],
    )


def downgrade() -> None:
    op.drop_index("ix_tickets_user_lastmsg", table_name="tickets")
    op.drop_index("ix_ticket_messages_ticket_sender", table_name="ticket_messages")
