bdlg2023-sms-back/alembic/versions/2026072155_implement_commission_and_organisation_285f2f9c840a.py

"""implement commission and organisation

Revision ID: 285f2f9c840a
Revises: 4e48e5c634f0
Create Date: 2026-07-21 00:55:59.819037

"""
from uuid import uuid4

from alembic import op
import sqlalchemy as sa
from sqlalchemy import table, column, String
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision = '285f2f9c840a'
down_revision = '4e48e5c634f0'
branch_labels = None
depends_on = None




def upgrade():
    bind = op.get_bind()

    # ---- explicitly create enum types before referencing them anywhere ----
    org_role_enum = postgresql.ENUM(
        "ORG_ADMIN", "RESPO_BENEVOLE", "RESPO_COMMISSION", name="org_role", create_type=False
    )
    org_role_enum.create(bind, checkfirst=True)

    global_role_enum = postgresql.ENUM("SUPER_ADMIN", "USER", name="global_role", create_type=False)
    global_role_enum.create(bind, checkfirst=True)
    # ---- schema: new tables (unchanged from your version) ----
    op.create_table('organizations',
        sa.Column('id', sa.UUID(as_uuid=False), nullable=False),
        sa.Column('name', sa.String(length=128), nullable=False),
        sa.PrimaryKeyConstraint('id'),
    )
    op.create_index(op.f('ix_organizations_name'), 'organizations', ['name'], unique=True)

    op.create_table('user_organizations',
        sa.Column('user_id', sa.UUID(as_uuid=False), nullable=False),
        sa.Column('organization_id', sa.UUID(as_uuid=False), nullable=False),
        sa.Column('role', org_role_enum, nullable=False),
        sa.ForeignKeyConstraint(['organization_id'], ['organizations.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['user_id'], ['user_model.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('user_id', 'organization_id'),
    )

    op.create_table('commissions',
        sa.Column('id', sa.UUID(as_uuid=False), nullable=False),
        sa.Column('project_id', sa.UUID(as_uuid=False), nullable=False),
        sa.Column('name', sa.String(length=128), nullable=False),
        sa.ForeignKeyConstraint(['project_id'], ['projects.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id'),
    )

    op.create_table('volunteer_groups',
        sa.Column('id', sa.UUID(as_uuid=False), nullable=False),
        sa.Column('project_id', sa.UUID(as_uuid=False), nullable=False),
        sa.Column('name', sa.String(length=128), nullable=False),
        sa.Column('color', sa.String(length=16), nullable=True),
        sa.ForeignKeyConstraint(['project_id'], ['projects.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id'),
    )

    op.create_table('association_volunteer_group',
        sa.Column('volunteer_id', sa.UUID(as_uuid=False), nullable=False),
        sa.Column('group_id', sa.UUID(as_uuid=False), nullable=False),
        sa.ForeignKeyConstraint(['group_id'], ['volunteer_groups.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['volunteer_id'], ['volunteers.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('volunteer_id', 'group_id'),
    )

    op.create_table('commission_members',
        sa.Column('commission_id', sa.UUID(as_uuid=False), nullable=False),
        sa.Column('user_id', sa.UUID(as_uuid=False), nullable=False),
        sa.ForeignKeyConstraint(['commission_id'], ['commissions.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['user_id'], ['user_model.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('commission_id', 'user_id'),
    )

    # ---- schema: new columns, added NULLABLE first ----
    op.add_column('projects', sa.Column('organization_id', sa.UUID(as_uuid=False), nullable=True))
    op.add_column('user_model', sa.Column('name', sa.String(length=128), nullable=False, server_default=''))
    op.add_column('user_model', sa.Column('phone_number', sa.String(length=24), nullable=True))
    op.add_column(
        "user_model",
        sa.Column("global_role", global_role_enum, nullable=False, server_default="USER"),
    )

    ## ---- data migration ----
    organizations_t = table('organizations', column('id', String), column('name', String))
    user_organizations_t = table(
        'user_organizations', column('user_id', String), column('organization_id', String), column('role', String)
    )
    projects_t = table('projects', column('id', String), column('organization_id', String))
    users_t = table('user_model', column('id', String))

    default_org_id = str(uuid4())
    bind.execute(organizations_t.insert().values(id=default_org_id, name='Default'))
    bind.execute(projects_t.update().values(organization_id=default_org_id))

    existing_user_ids = [row[0] for row in bind.execute(sa.select(users_t.c.id))]
    if existing_user_ids:
        bind.execute(
            user_organizations_t.insert(),
            [
                {'user_id': uid, 'organization_id': default_org_id, 'role': 'ORG_ADMIN'}
                for uid in existing_user_ids
            ],
        )

    # ---- schema: tighten ----
    op.alter_column("projects", "organization_id", nullable=False)
    op.create_foreign_key(
        "fk_projects_organization_id",
        "projects",
        "organizations",
        ["organization_id"],
        ["id"],
        ondelete="CASCADE",
    )
    op.alter_column("user_model", "name", server_default=None)
    op.alter_column("user_model", "global_role", server_default=None)


def downgrade():
    bind = op.get_bind()

    op.drop_column('user_model', 'global_role')
    op.drop_column('user_model', 'phone_number')
    op.drop_column('user_model', 'name')
    op.drop_constraint('fk_projects_organization_id', 'projects', type_='foreignkey')
    op.drop_column('projects', 'organization_id')
    op.drop_table('commission_members')
    op.drop_table('association_volunteer_group')
    op.drop_table('volunteer_groups')
    op.drop_table('commissions')
    op.drop_table('user_organizations')
    op.drop_index(op.f('ix_organizations_name'), table_name='organizations')
    op.drop_table('organizations')


    # drop enum types last, after every column referencing them is gone
    postgresql.ENUM(name='global_role').drop(bind, checkfirst=True)
    postgresql.ENUM(name='org_role').drop(bind, checkfirst=True)