51 lines
1.6 KiB
Python
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"),
|
|
)
|