Files
celtic-life-science/alembic/versions/s9c5e1a7b4d6_several_area_managers_per_hq.py
Shubhankar45 4e5e95775d --
2026-10-27 12:03:31 +00:00

51 lines
1.6 KiB
Python

"""a headquarters may have several Area Managers
The rule was one active Area Manager per headquarters. Now any number may manage one;
the same Area Manager still holds a headquarters only once at a time.
Revision ID: s9c5e1a7b4d6
Revises: r8b4d0f6a3c5
Create Date: 2026-10-26
"""
from collections.abc import Sequence
import sqlalchemy as sa
from alembic import op
revision: str = "s9c5e1a7b4d6"
down_revision: str | None = "r8b4d0f6a3c5"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None
def upgrade() -> None:
op.drop_index("uq_active_am_per_hq", table_name="am_headquarters")
op.create_index(
"uq_active_am_hq_pair",
"am_headquarters",
["headquarters_id", "area_manager_user_id"],
unique=True,
postgresql_where=sa.text("effective_to IS NULL"),
)
def downgrade() -> None:
op.drop_index("uq_active_am_hq_pair", table_name="am_headquarters")
# Back to one per headquarters: keep the longest-standing Area Manager of each.
op.execute(
"""UPDATE am_headquarters a SET effective_to = CURRENT_DATE
WHERE a.effective_to IS NULL AND EXISTS (
SELECT 1 FROM am_headquarters b
WHERE b.headquarters_id = a.headquarters_id AND b.effective_to IS NULL
AND (b.effective_from, b.id::text) < (a.effective_from, a.id::text))"""
)
op.create_index(
"uq_active_am_per_hq",
"am_headquarters",
["headquarters_id"],
unique=True,
postgresql_where=sa.text("effective_to IS NULL"),
)