"""add_parameter_id_to_test_package_item

Revision ID: 8f9c02d4e5b3
Revises: 7e8b91c3d4a2
Create Date: 2026-09-26 02:20:00.000000+00:00

"""

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op

# revision identifiers, used by Alembic.
revision: str = "8f9c02d4e5b3"
down_revision: str | None = "7e8b91c3d4a2"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.add_column(
        "test_package_item",
        sa.Column(
            "parameter_id",
            sa.UUID(),
            sa.ForeignKey("test_parameter.id", ondelete="CASCADE"),
            nullable=True,
            comment="Optional FK to test_parameter.id if bundling individual analyte",
        ),
    )
    op.drop_constraint("uq_test_package_item_package_id_test_id", "test_package_item", type_="unique")
    op.create_index("ix_test_package_item_parameter_id", "test_package_item", ["parameter_id"], unique=False)
    op.execute(
        "CREATE UNIQUE INDEX uq_pkg_item_test_null ON test_package_item (package_id, test_id) WHERE parameter_id IS NULL"
    )
    op.execute(
        "CREATE UNIQUE INDEX uq_pkg_item_test_param ON test_package_item (package_id, test_id, parameter_id) WHERE parameter_id IS NOT NULL"
    )


def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS uq_pkg_item_test_param")
    op.execute("DROP INDEX IF EXISTS uq_pkg_item_test_null")
    op.drop_index("ix_test_package_item_parameter_id", table_name="test_package_item")
    op.create_unique_constraint(
        "uq_test_package_item_package_id_test_id", "test_package_item", ["package_id", "test_id"]
    )
    op.drop_column("test_package_item", "parameter_id")