benevoles/backend/app/migrations/versions/0007_drop_skills_avail_windows.py

"""Retrait des compétences ; disponibilités en fenêtres horaires libres

Revision ID: 0007_drop_skills_avail_windows
Revises: 0006_artist_and_volunteer_wishes
Create Date: 2026-09-10

- Suppression des tables `skill`, `volunteer_skill`, `shift_required_skill`.
- `availability` : on abandonne (`day_part_id`, `status`) au profit de
  (`start_time`, `end_time`) — fenêtre horaire libre, plusieurs par jour.
  La table est recréée (les anciennes lignes ne sont pas convertibles).
"""

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op

revision: str = "0007_drop_skills_avail_windows"
down_revision: str | None = "0006_artist_and_volunteer_wishes"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None

_STATUS = sa.Enum(
    "AVAILABLE", "IF_NEEDED", "UNAVAILABLE",
    name="availabilitystatus", native_enum=False,
)


def upgrade() -> None:
    op.drop_table("shift_required_skill")
    op.drop_table("volunteer_skill")
    op.drop_index("ix_skill_edition_id", table_name="skill")
    op.drop_table("skill")

    op.drop_table("availability")
    op.create_table(
        "availability",
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("id", sa.Uuid(), nullable=False),
        sa.Column("edition_id", sa.Uuid(), nullable=False),
        sa.Column("volunteer_id", sa.Uuid(), nullable=False),
        sa.Column("date", sa.Date(), nullable=False),
        sa.Column("start_time", sa.Time(), nullable=False),
        sa.Column("end_time", sa.Time(), nullable=False),
        sa.ForeignKeyConstraint(
            ["edition_id"], ["edition.id"], ondelete="CASCADE"
        ),
        sa.ForeignKeyConstraint(
            ["volunteer_id"], ["volunteer.id"], ondelete="CASCADE"
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "volunteer_id", "date", "start_time", "end_time",
            name="uq_availability_window",
        ),
    )
    op.create_index(
        op.f("ix_availability_edition_id"),
        "availability",
        ["edition_id"],
        unique=False,
    )
    op.create_index(
        op.f("ix_availability_volunteer_id"),
        "availability",
        ["volunteer_id"],
        unique=False,
    )


def downgrade() -> None:
    op.drop_index(op.f("ix_availability_volunteer_id"), table_name="availability")
    op.drop_index(op.f("ix_availability_edition_id"), table_name="availability")
    op.drop_table("availability")
    op.create_table(
        "availability",
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("id", sa.Uuid(), nullable=False),
        sa.Column("edition_id", sa.Uuid(), nullable=False),
        sa.Column("volunteer_id", sa.Uuid(), nullable=False),
        sa.Column("date", sa.Date(), nullable=False),
        sa.Column("day_part_id", sa.Uuid(), nullable=False),
        sa.Column("status", _STATUS, nullable=False),
        sa.ForeignKeyConstraint(
            ["edition_id"], ["edition.id"], ondelete="CASCADE"
        ),
        sa.ForeignKeyConstraint(
            ["volunteer_id"], ["volunteer.id"], ondelete="CASCADE"
        ),
        sa.ForeignKeyConstraint(
            ["day_part_id"], ["day_part.id"], ondelete="CASCADE"
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "volunteer_id", "date", "day_part_id", name="uq_availability_slot"
        ),
    )
    op.create_index(
        op.f("ix_availability_edition_id"),
        "availability", ["edition_id"], unique=False,
    )
    op.create_index(
        op.f("ix_availability_volunteer_id"),
        "availability", ["volunteer_id"], unique=False,
    )
    op.create_index(
        op.f("ix_availability_day_part_id"),
        "availability", ["day_part_id"], unique=False,
    )

    op.create_table(
        "skill",
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("id", sa.Uuid(), nullable=False),
        sa.Column("edition_id", sa.Uuid(), nullable=False),
        sa.Column("code", sa.String(), nullable=False),
        sa.Column("label", sa.String(), nullable=False),
        sa.ForeignKeyConstraint(
            ["edition_id"], ["edition.id"], ondelete="CASCADE"
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "edition_id", "code", name="uq_skill_edition_code"
        ),
    )
    op.create_index(
        op.f("ix_skill_edition_id"), "skill", ["edition_id"], unique=False
    )
    op.create_table(
        "volunteer_skill",
        sa.Column("volunteer_id", sa.Uuid(), nullable=False),
        sa.Column("skill_id", sa.Uuid(), nullable=False),
        sa.ForeignKeyConstraint(
            ["skill_id"], ["skill.id"], ondelete="CASCADE"
        ),
        sa.ForeignKeyConstraint(
            ["volunteer_id"], ["volunteer.id"], ondelete="CASCADE"
        ),
        sa.PrimaryKeyConstraint("volunteer_id", "skill_id"),
    )
    op.create_table(
        "shift_required_skill",
        sa.Column("shift_id", sa.Uuid(), nullable=False),
        sa.Column("skill_id", sa.Uuid(), nullable=False),
        sa.ForeignKeyConstraint(
            ["shift_id"], ["shift.id"], ondelete="CASCADE"
        ),
        sa.ForeignKeyConstraint(
            ["skill_id"], ["skill.id"], ondelete="CASCADE"
        ),
        sa.PrimaryKeyConstraint("shift_id", "skill_id"),
    )