"""ticket reply templates and satisfaction

Revision ID: 073_ticket_templates_satisfaction
Revises: 072_ticket_workflow_enhancements
Create Date: 2026-06-27
"""

from alembic import op
import sqlalchemy as sa


revision = "073_ticket_templates_satisfaction"
down_revision = "072_ticket_workflow_enhancements"
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 "satisfaction_rating" not in columns:
            op.add_column("tickets", sa.Column("satisfaction_rating", sa.Integer(), nullable=True))
        if "satisfaction_comment" not in columns:
            op.add_column("tickets", sa.Column("satisfaction_comment", sa.Text(), nullable=True))
        if "satisfaction_rated_at" not in columns:
            op.add_column("tickets", sa.Column("satisfaction_rated_at", sa.DateTime(timezone=True), nullable=True))

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

    if not _table_exists(bind, "ticket_reply_templates"):
        op.create_table(
            "ticket_reply_templates",
            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("title", sa.String(120), nullable=False),
            sa.Column("content", sa.Text(), nullable=False),
            sa.Column("department_id", sa.Integer(), 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("usage_count", sa.Integer(), nullable=False, server_default="0"),
            sa.Column("created_by_id", sa.BigInteger(), nullable=True),
            sa.Column("updated_by_id", sa.BigInteger(), nullable=True),
            sa.ForeignKeyConstraint(["department_id"], ["ticket_departments.id"], ondelete="SET NULL"),
            sa.ForeignKeyConstraint(["created_by_id"], ["users.id"], ondelete="SET NULL"),
            sa.ForeignKeyConstraint(["updated_by_id"], ["users.id"], ondelete="SET NULL"),
            sa.PrimaryKeyConstraint("id"),
        )
        op.create_index("ix_ticket_reply_templates_department_id", "ticket_reply_templates", ["department_id"])
        op.create_index("ix_ticket_reply_templates_is_enabled", "ticket_reply_templates", ["is_enabled"])
        op.create_index("ix_ticket_reply_templates_created_by_id", "ticket_reply_templates", ["created_by_id"])
        op.create_index("ix_ticket_reply_templates_updated_by_id", "ticket_reply_templates", ["updated_by_id"])


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

    if _table_exists(bind, "ticket_reply_templates"):
        indexes = _index_names(bind, "ticket_reply_templates")
        for name in [
            "ix_ticket_reply_templates_updated_by_id",
            "ix_ticket_reply_templates_created_by_id",
            "ix_ticket_reply_templates_is_enabled",
            "ix_ticket_reply_templates_department_id",
        ]:
            if name in indexes:
                op.drop_index(name, table_name="ticket_reply_templates")
        op.drop_table("ticket_reply_templates")

    if _table_exists(bind, "tickets"):
        indexes = _index_names(bind, "tickets")
        if "ix_tickets_satisfaction_rating" in indexes:
            op.drop_index("ix_tickets_satisfaction_rating", table_name="tickets")
        columns = _column_names(bind, "tickets")
        if "satisfaction_rated_at" in columns:
            op.drop_column("tickets", "satisfaction_rated_at")
        if "satisfaction_comment" in columns:
            op.drop_column("tickets", "satisfaction_comment")
        if "satisfaction_rating" in columns:
            op.drop_column("tickets", "satisfaction_rating")
