"""Пользователи, чаты, участники, сообщения и сессии.""" from alembic import op import sqlalchemy as sa revision = '001' down_revision = None branch_labels = None depends_on = None def upgrade(): op.create_table( 'users', sa.Column('id', sa.Uuid(), primary_key=True), sa.Column('username', sa.String(32), nullable=False, unique=True), sa.Column('display_name', sa.String(100), nullable=False), sa.Column('password_hash', sa.Text(), nullable=False), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), sa.CheckConstraint("username ~ '^[a-z0-9_]{3,32}$'"), sa.CheckConstraint('length(trim(display_name)) > 0'), ) op.create_table( 'chats', sa.Column('id', sa.Uuid(), primary_key=True), sa.Column('title', sa.String(100), nullable=False), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), sa.CheckConstraint('length(trim(title)) > 0'), ) op.create_table( 'chat_members', sa.Column('chat_id', sa.Uuid(), sa.ForeignKey('chats.id', ondelete='CASCADE'), primary_key=True), sa.Column('user_id', sa.Uuid(), sa.ForeignKey('users.id'), primary_key=True), ) op.create_index('chat_members_user_idx', 'chat_members', ['user_id', 'chat_id']) op.create_table( 'messages', sa.Column('id', sa.Uuid(), primary_key=True), sa.Column('chat_id', sa.Uuid(), sa.ForeignKey('chats.id'), nullable=False), sa.Column('sender_id', sa.Uuid(), sa.ForeignKey('users.id'), nullable=False), sa.Column('text', sa.String(4000), nullable=False), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), sa.ForeignKeyConstraint(['chat_id', 'sender_id'], ['chat_members.chat_id', 'chat_members.user_id']), sa.CheckConstraint('length(trim(text)) > 0'), ) op.create_index('messages_history_idx', 'messages', ['chat_id', 'created_at', 'id']) op.create_table( 'sessions', sa.Column('token_hash', sa.Text(), primary_key=True), sa.Column('user_id', sa.Uuid(), sa.ForeignKey('users.id', ondelete='CASCADE'), nullable=False), sa.Column('expires_at', sa.DateTime(timezone=True), nullable=False), ) op.create_index('sessions_expiry_idx', 'sessions', ['expires_at']) def downgrade(): op.drop_table('sessions') op.drop_table('messages') op.drop_table('chat_members') op.drop_table('chats') op.drop_table('users')