Samir Boulahtit
f20266167d
- Auto-fixed 4,496 lint issues (import sorting, modern syntax, etc.) - Added ignore rules for patterns intentional in this codebase: E402 (late imports), E712 (SQLAlchemy filters), B904 (raise from), SIM108/SIM105/SIM117 (readability preferences) - Added per-file ignores for tests and scripts - Excluded broken scripts/rename_terminology.py (has curly quotes) Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
340 lines
12 KiB
Python
340 lines
12 KiB
Python
"""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")
|