"""add_messaging_tables

Revision ID: e3f4a5b6c7d8
Revises: c9e22eadf533
Create Date: 2025-12-21

This migration adds the messaging system tables:
- conversations: Threaded conversation threads
- conversation_participants: Links users/customers to conversations
- messages: Individual messages within conversations
- message_attachments: File attachments for messages

Supports three communication channels:
- Admin <-> Vendor
- Vendor <-> Customer
- Admin <-> Customer
"""

from collections.abc import Sequence

import sqlalchemy as sa
from sqlalchemy import inspect

from alembic import op

# revision identifiers, used by Alembic.
revision: str = "e3f4a5b6c7d8"
down_revision: str | None = "c9e22eadf533"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def table_exists(table_name: str) -> bool:
    """Check if a table exists in the database."""
    bind = op.get_bind()
    inspector = inspect(bind)
    return table_name in inspector.get_table_names()


def index_exists(index_name: str, table_name: str) -> bool:
    """Check if an index exists on a table."""
    bind = op.get_bind()
    inspector = inspect(bind)
    try:
        indexes = inspector.get_indexes(table_name)
        return any(idx["name"] == index_name for idx in indexes)
    except Exception:
        return False


def upgrade() -> None:
    # =========================================================================
    # Step 1: Create conversations table
    # =========================================================================
    if not table_exists("conversations"):
        op.create_table(
            "conversations",
            sa.Column("id", sa.Integer(), nullable=False),
            sa.Column(
                "conversation_type",
                sa.Enum(
                    "admin_vendor",
                    "vendor_customer",
                    "admin_customer",
                    name="conversationtype",
                ),
                nullable=False,
            ),
            sa.Column("subject", sa.String(length=500), nullable=False),
            sa.Column("vendor_id", sa.Integer(), nullable=True),
            sa.Column("is_closed", sa.Boolean(), nullable=False, server_default="0"),
            sa.Column("closed_at", sa.DateTime(), nullable=True),
            sa.Column(
                "closed_by_type",
                sa.Enum("admin", "vendor", "customer", name="participanttype"),
                nullable=True,
            ),
            sa.Column("closed_by_id", sa.Integer(), nullable=True),
            sa.Column("last_message_at", sa.DateTime(), nullable=True),
            sa.Column("message_count", sa.Integer(), nullable=False, server_default="0"),
            sa.Column(
                "created_at",
                sa.DateTime(),
                nullable=False,
                server_default=sa.text("CURRENT_TIMESTAMP"),
            ),
            sa.Column(
                "updated_at",
                sa.DateTime(),
                nullable=False,
                server_default=sa.text("CURRENT_TIMESTAMP"),
            ),
            sa.ForeignKeyConstraint(
                ["vendor_id"],
                ["vendors.id"],
            ),
            sa.PrimaryKeyConstraint("id"),
        )
        op.create_index(
            op.f("ix_conversations_id"), "conversations", ["id"], unique=False
        )
        op.create_index(
            op.f("ix_conversations_conversation_type"),
            "conversations",
            ["conversation_type"],
            unique=False,
        )
        op.create_index(
            op.f("ix_conversations_vendor_id"),
            "conversations",
            ["vendor_id"],
            unique=False,
        )
        op.create_index(
            op.f("ix_conversations_last_message_at"),
            "conversations",
            ["last_message_at"],
            unique=False,
        )
        op.create_index(
            "ix_conversations_type_vendor",
            "conversations",
            ["conversation_type", "vendor_id"],
            unique=False,
        )

    # =========================================================================
    # Step 2: Create conversation_participants table
    # =========================================================================
    if not table_exists("conversation_participants"):
        op.create_table(
            "conversation_participants",
            sa.Column("id", sa.Integer(), nullable=False),
            sa.Column("conversation_id", sa.Integer(), nullable=False),
            sa.Column(
                "participant_type",
                sa.Enum("admin", "vendor", "customer", name="participanttype"),
                nullable=False,
            ),
            sa.Column("participant_id", sa.Integer(), nullable=False),
            sa.Column("vendor_id", sa.Integer(), nullable=True),
            sa.Column("unread_count", sa.Integer(), nullable=False, server_default="0"),
            sa.Column("last_read_at", sa.DateTime(), nullable=True),
            sa.Column(
                "email_notifications", sa.Boolean(), nullable=False, server_default="1"
            ),
            sa.Column("muted", sa.Boolean(), nullable=False, server_default="0"),
            sa.Column(
                "created_at",
                sa.DateTime(),
                nullable=False,
                server_default=sa.text("CURRENT_TIMESTAMP"),
            ),
            sa.Column(
                "updated_at",
                sa.DateTime(),
                nullable=False,
                server_default=sa.text("CURRENT_TIMESTAMP"),
            ),
            sa.ForeignKeyConstraint(
                ["conversation_id"],
                ["conversations.id"],
                ondelete="CASCADE",
            ),
            sa.ForeignKeyConstraint(
                ["vendor_id"],
                ["vendors.id"],
            ),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint(
                "conversation_id",
                "participant_type",
                "participant_id",
                name="uq_conversation_participant",
            ),
        )
        op.create_index(
            op.f("ix_conversation_participants_id"),
            "conversation_participants",
            ["id"],
            unique=False,
        )
        op.create_index(
            op.f("ix_conversation_participants_conversation_id"),
            "conversation_participants",
            ["conversation_id"],
            unique=False,
        )
        op.create_index(
            op.f("ix_conversation_participants_participant_id"),
            "conversation_participants",
            ["participant_id"],
            unique=False,
        )
        op.create_index(
            "ix_participant_lookup",
            "conversation_participants",
            ["participant_type", "participant_id"],
            unique=False,
        )

    # =========================================================================
    # Step 3: Create messages table
    # =========================================================================
    if not table_exists("messages"):
        op.create_table(
            "messages",
            sa.Column("id", sa.Integer(), nullable=False),
            sa.Column("conversation_id", sa.Integer(), nullable=False),
            sa.Column(
                "sender_type",
                sa.Enum("admin", "vendor", "customer", name="participanttype"),
                nullable=False,
            ),
            sa.Column("sender_id", sa.Integer(), nullable=False),
            sa.Column("content", sa.Text(), nullable=False),
            sa.Column(
                "is_system_message", sa.Boolean(), nullable=False, server_default="0"
            ),
            sa.Column("is_deleted", sa.Boolean(), nullable=False, server_default="0"),
            sa.Column("deleted_at", sa.DateTime(), nullable=True),
            sa.Column(
                "deleted_by_type",
                sa.Enum("admin", "vendor", "customer", name="participanttype"),
                nullable=True,
            ),
            sa.Column("deleted_by_id", sa.Integer(), nullable=True),
            sa.Column(
                "created_at",
                sa.DateTime(),
                nullable=False,
                server_default=sa.text("CURRENT_TIMESTAMP"),
            ),
            sa.Column(
                "updated_at",
                sa.DateTime(),
                nullable=False,
                server_default=sa.text("CURRENT_TIMESTAMP"),
            ),
            sa.ForeignKeyConstraint(
                ["conversation_id"],
                ["conversations.id"],
                ondelete="CASCADE",
            ),
            sa.PrimaryKeyConstraint("id"),
        )
        op.create_index(op.f("ix_messages_id"), "messages", ["id"], unique=False)
        op.create_index(
            op.f("ix_messages_conversation_id"),
            "messages",
            ["conversation_id"],
            unique=False,
        )
        op.create_index(
            op.f("ix_messages_sender_id"), "messages", ["sender_id"], unique=False
        )
        op.create_index(
            "ix_messages_conversation_created",
            "messages",
            ["conversation_id", "created_at"],
            unique=False,
        )

    # =========================================================================
    # Step 4: Create message_attachments table
    # =========================================================================
    if not table_exists("message_attachments"):
        op.create_table(
            "message_attachments",
            sa.Column("id", sa.Integer(), nullable=False),
            sa.Column("message_id", sa.Integer(), nullable=False),
            sa.Column("filename", sa.String(length=255), nullable=False),
            sa.Column("original_filename", sa.String(length=255), nullable=False),
            sa.Column("file_path", sa.String(length=1000), nullable=False),
            sa.Column("file_size", sa.Integer(), nullable=False),
            sa.Column("mime_type", sa.String(length=100), nullable=False),
            sa.Column("is_image", sa.Boolean(), nullable=False, server_default="0"),
            sa.Column("image_width", sa.Integer(), nullable=True),
            sa.Column("image_height", sa.Integer(), nullable=True),
            sa.Column("thumbnail_path", sa.String(length=1000), nullable=True),
            sa.Column(
                "created_at",
                sa.DateTime(),
                nullable=False,
                server_default=sa.text("CURRENT_TIMESTAMP"),
            ),
            sa.Column(
                "updated_at",
                sa.DateTime(),
                nullable=False,
                server_default=sa.text("CURRENT_TIMESTAMP"),
            ),
            sa.ForeignKeyConstraint(
                ["message_id"],
                ["messages.id"],
                ondelete="CASCADE",
            ),
            sa.PrimaryKeyConstraint("id"),
        )
        op.create_index(
            op.f("ix_message_attachments_id"),
            "message_attachments",
            ["id"],
            unique=False,
        )
        op.create_index(
            op.f("ix_message_attachments_message_id"),
            "message_attachments",
            ["message_id"],
            unique=False,
        )

    # =========================================================================
    # Step 5: Add platform setting for attachment size limit
    # =========================================================================
    # Note: This will be added via seed script or manually
    # Key: message_attachment_max_size_mb
    # Value: 10
    # Category: messaging


def downgrade() -> None:
    # Drop tables in reverse order (respecting foreign keys)
    if table_exists("message_attachments"):
        op.drop_table("message_attachments")

    if table_exists("messages"):
        op.drop_table("messages")

    if table_exists("conversation_participants"):
        op.drop_table("conversation_participants")

    if table_exists("conversations"):
        op.drop_table("conversations")

    # Note: Enum types are not dropped automatically
    # They can be manually dropped with:
    # op.execute("DROP TYPE IF EXISTS conversationtype")
    # op.execute("DROP TYPE IF EXISTS participanttype")