modbison/backend/alembic/versions/1a1983be633a_chat_data_models.py

"""chat data models

Revision ID: 1a1983be633a
Revises: 0001_initial
Create Date: 2026-08-18 15:06:05.948446
"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision: str = "1a1983be633a"
down_revision: Union[str, None] = "0001_initial"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Create chat tables: conversations, conversation_participants,
    messages, message_reports."""

    # ── ENUM types ──────────────────────────────────────────────────

    msg_report_reason = postgresql.ENUM(
        "spam", "inappropriate", "off_topic", "other",
        name="message_report_reason",
    )
    msg_report_reason.create(op.get_bind(), checkfirst=True)

    msg_report_status = postgresql.ENUM(
        "pending", "resolved", "dismissed",
        name="message_report_status",
    )
    msg_report_status.create(op.get_bind(), checkfirst=True)

    # ── conversations ───────────────────────────────────────────────

    op.create_table(
        "conversations",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column(
            "latest_message_id",
            sa.Integer(),
            sa.ForeignKey("messages.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.func.now(),
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.func.now(),
            onupdate=sa.func.now(),
        ),
    )
    op.create_index(op.f("ix_conversations_id"), "conversations", ["id"], unique=False)

    # Unique pair constraint — populated later via participants
    op.create_unique_constraint(
        "uq_conversation_participants",
        "conversations",
        ["participant_1_id", "participant_2_id"],
    )

    # ── conversation_participants ───────────────────────────────────

    op.create_table(
        "conversation_participants",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column(
            "conversation_id",
            sa.Integer(),
            sa.ForeignKey("conversations.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "user_id",
            sa.Integer(),
            sa.ForeignKey("users.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("left_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("last_read_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.func.now(),
        ),
    )
    op.create_index(
        op.f("ix_conversation_participants_id"),
        "conversation_participants",
        ["id"],
        unique=False,
    )
    op.create_index(
        op.f("ix_conversation_participants_user_id"),
        "conversation_participants",
        ["user_id"],
        unique=False,
    )
    op.create_unique_constraint(
        "uq_conversation_user",
        "conversation_participants",
        ["conversation_id", "user_id"],
    )

    # ── messages ────────────────────────────────────────────────────

    op.create_table(
        "messages",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column(
            "sender_id",
            sa.Integer(),
            sa.ForeignKey("users.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "conversation_id",
            sa.Integer(),
            sa.ForeignKey("conversations.id", ondelete="CASCADE"),
            nullable=True,
        ),
        sa.Column(
            "world_id",
            sa.Integer(),
            sa.ForeignKey("worlds.id", ondelete="CASCADE"),
            nullable=True,
        ),
        sa.Column("room_id", sa.String(length=36), nullable=True),
        sa.Column("body", sa.Text(), nullable=False),
        sa.Column("is_filtered", sa.Boolean(), nullable=False, server_default="false"),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.func.now(),
        ),
    )
    op.create_index(op.f("ix_messages_id"), "messages", ["id"], unique=False)
    op.create_index(
        "ix_messages_conversation_created",
        "messages",
        ["conversation_id", "created_at"],
    )
    op.create_index(
        "ix_messages_world_created",
        "messages",
        ["world_id", "created_at"],
    )

    # ── message_reports ─────────────────────────────────────────────

    op.create_table(
        "message_reports",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column(
            "message_id",
            sa.Integer(),
            sa.ForeignKey("messages.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "reporter_id",
            sa.Integer(),
            sa.ForeignKey("users.id", ondelete="SET NULL"),
            nullable=False,
        ),
        sa.Column(
            "reason",
            postgresql.ENUM(
                "spam", "inappropriate", "off_topic", "other",
                name="message_report_reason",
                create_type=False,
            ),
            nullable=False,
        ),
        sa.Column(
            "status",
            postgresql.ENUM(
                "pending", "resolved", "dismissed",
                name="message_report_status",
                create_type=False,
            ),
            nullable=False,
            server_default="pending",
        ),
        sa.Column(
            "moderator_id",
            sa.Integer(),
            sa.ForeignKey("users.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column("resolved_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.func.now(),
        ),
    )
    op.create_index(op.f("ix_message_reports_id"), "message_reports", ["id"], unique=False)


def downgrade() -> None:
    """Drop chat tables in reverse dependency order."""
    op.drop_index(op.f("ix_message_reports_id"), table_name="message_reports")
    op.drop_table("message_reports")

    op.drop_index(op.f("ix_messages_world_created"), table_name="messages")
    op.drop_index(op.f("ix_messages_conversation_created"), table_name="messages")
    op.drop_index(op.f("ix_messages_id"), table_name="messages")
    op.drop_table("messages")

    op.drop_index(op.f("ix_conversation_participants_user_id"), table_name="conversation_participants")
    op.drop_index(op.f("ix_conversation_participants_id"), table_name="conversation_participants")
    op.drop_table("conversation_participants")

    op.drop_index(op.f("ix_conversations_id"), table_name="conversations")
    op.drop_table("conversations")

    # Drop ENUM types
    postgresql.ENUM(name="message_report_status").drop(op.get_bind(), checkfirst=True)
    postgresql.ENUM(name="message_report_reason").drop(op.get_bind(), checkfirst=True)