"""ticket workflow enhancements

Revision ID: 072_ticket_workflow_enhancements
Revises: 071_secure_verification_identity
Create Date: 2026-06-27
"""

from alembic import op
import sqlalchemy as sa


revision = "072_ticket_workflow_enhancements"
down_revision = "071_secure_verification_identity"
branch_labels = None
depends_on = None


def _table_exists(bind: sa.engine.Connection, table_name: str) -> bool:
    return table_name in set(sa.inspect(bind).get_table_names())


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()

    if _table_exists(bind, "tickets"):
        columns = _column_names(bind, "tickets")
        if "assignee_id" not in columns:
            op.add_column("tickets", sa.Column("assignee_id", sa.BigInteger(), nullable=True))
        if "assigned_at" not in columns:
            op.add_column("tickets", sa.Column("assigned_at", sa.DateTime(timezone=True), nullable=True))

        indexes = _index_names(bind, "tickets")
        if "ix_tickets_assignee_id" not in indexes:
            op.create_index("ix_tickets_assignee_id", "tickets", ["assignee_id"])

        if bind.dialect.name != "sqlite":
            try:
                op.create_foreign_key(
                    "fk_tickets_assignee_id_users",
                    "tickets",
                    "users",
                    ["assignee_id"],
                    ["id"],
                    ondelete="SET NULL",
                )
            except Exception:
                pass

    if not _table_exists(bind, "ticket_events"):
        op.create_table(
            "ticket_events",
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            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(), nullable=False),
            sa.Column("actor_type", sa.String(32), nullable=False),
            sa.Column("actor_id", sa.BigInteger(), nullable=True),
            sa.Column("event_type", sa.String(64), nullable=False),
            sa.Column("from_value", sa.String(160), nullable=True),
            sa.Column("to_value", sa.String(160), nullable=True),
            sa.Column("message", sa.Text(), nullable=True),
            sa.Column("meta", sa.JSON(), nullable=True),
            sa.ForeignKeyConstraint(["ticket_id"], ["tickets.id"], ondelete="CASCADE"),
            sa.ForeignKeyConstraint(["actor_id"], ["users.id"], ondelete="SET NULL"),
            sa.PrimaryKeyConstraint("id"),
        )
        op.create_index("ix_ticket_events_ticket_id", "ticket_events", ["ticket_id"])
        op.create_index("ix_ticket_events_actor_type", "ticket_events", ["actor_type"])
        op.create_index("ix_ticket_events_actor_id", "ticket_events", ["actor_id"])
        op.create_index("ix_ticket_events_event_type", "ticket_events", ["event_type"])

    if not _table_exists(bind, "ticket_attachments"):
        op.create_table(
            "ticket_attachments",
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            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("token", sa.String(80), nullable=False),
            sa.Column("storage_path", sa.String(500), nullable=False),
            sa.Column("original_name", sa.String(160), nullable=True),
            sa.Column("content_type", sa.String(100), nullable=False),
            sa.Column("size", sa.Integer(), nullable=False, server_default="0"),
            sa.Column("uploaded_by_type", sa.String(32), nullable=False),
            sa.Column("uploaded_by_id", sa.BigInteger(), nullable=True),
            sa.Column("ticket_id", sa.Integer(), nullable=True),
            sa.Column("message_id", sa.Integer(), nullable=True),
            sa.Column("is_claimed", sa.Boolean(), nullable=False, server_default=sa.text("0")),
            sa.ForeignKeyConstraint(["uploaded_by_id"], ["users.id"], ondelete="SET NULL"),
            sa.ForeignKeyConstraint(["ticket_id"], ["tickets.id"], ondelete="CASCADE"),
            sa.ForeignKeyConstraint(["message_id"], ["ticket_messages.id"], ondelete="SET NULL"),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint("token", name="uq_ticket_attachments_token"),
        )
        op.create_index("ix_ticket_attachments_token", "ticket_attachments", ["token"], unique=True)
        op.create_index("ix_ticket_attachments_uploaded_by_type", "ticket_attachments", ["uploaded_by_type"])
        op.create_index("ix_ticket_attachments_uploaded_by_id", "ticket_attachments", ["uploaded_by_id"])
        op.create_index("ix_ticket_attachments_ticket_id", "ticket_attachments", ["ticket_id"])
        op.create_index("ix_ticket_attachments_message_id", "ticket_attachments", ["message_id"])
        op.create_index("ix_ticket_attachments_is_claimed", "ticket_attachments", ["is_claimed"])


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

    if _table_exists(bind, "ticket_attachments"):
        indexes = _index_names(bind, "ticket_attachments")
        for name in [
            "ix_ticket_attachments_is_claimed",
            "ix_ticket_attachments_message_id",
            "ix_ticket_attachments_ticket_id",
            "ix_ticket_attachments_uploaded_by_id",
            "ix_ticket_attachments_uploaded_by_type",
            "ix_ticket_attachments_token",
        ]:
            if name in indexes:
                op.drop_index(name, table_name="ticket_attachments")
        op.drop_table("ticket_attachments")

    if _table_exists(bind, "ticket_events"):
        indexes = _index_names(bind, "ticket_events")
        for name in [
            "ix_ticket_events_event_type",
            "ix_ticket_events_actor_id",
            "ix_ticket_events_actor_type",
            "ix_ticket_events_ticket_id",
        ]:
            if name in indexes:
                op.drop_index(name, table_name="ticket_events")
        op.drop_table("ticket_events")

    if _table_exists(bind, "tickets"):
        indexes = _index_names(bind, "tickets")
        if "ix_tickets_assignee_id" in indexes:
            op.drop_index("ix_tickets_assignee_id", table_name="tickets")
        columns = _column_names(bind, "tickets")
        if "assigned_at" in columns:
            op.drop_column("tickets", "assigned_at")
        if "assignee_id" in columns:
            op.drop_column("tickets", "assignee_id")
