"""Scope feature_packs to the owning project for project_local rows.

feature_packs.requirement_fingerprint (migration 128) had no project scoping at all --
its dedup lookup in compose_and_register_pack matched purely on
(requirement_fingerprint, scope='project_local'), so two different projects composing
the same requirement shape (e.g. two "quote_request" packs) collided on the same row:
the second project's compose_and_register_pack call would find the first project's
row, reuse its slug (which has the *first* project's ref baked into the string), and
overwrite its composition/access_policy. project_id closes that -- NULL for shared
'catalog' rows, always set for 'project_local' rows, and the dedup lookup/unique index
now include it.

Revision ID: 131
Revises: 130
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy import inspect
from sqlalchemy.dialects import postgresql

revision = "131"
down_revision = "130"
branch_labels = None
depends_on = None


def upgrade() -> None:
    conn = op.get_bind()
    insp = inspect(conn)
    cols = {c["name"] for c in insp.get_columns("feature_packs")}
    indexes = {i["name"] for i in insp.get_indexes("feature_packs")}

    if "project_id" not in cols:
        op.add_column(
            "feature_packs",
            sa.Column(
                "project_id",
                postgresql.UUID(as_uuid=True),
                sa.ForeignKey("projects.id", ondelete="CASCADE"),
                nullable=True,
            ),
        )
    if "ux_feature_packs_project_fingerprint" not in indexes:
        op.create_index(
            "ux_feature_packs_project_fingerprint",
            "feature_packs",
            ["project_id", "requirement_fingerprint"],
            unique=True,
            postgresql_where=sa.text("scope = 'project_local'"),
        )


def downgrade() -> None:
    conn = op.get_bind()
    insp = inspect(conn)
    cols = {c["name"] for c in insp.get_columns("feature_packs")}
    indexes = {i["name"] for i in insp.get_indexes("feature_packs")}

    if "ux_feature_packs_project_fingerprint" in indexes:
        op.drop_index("ux_feature_packs_project_fingerprint", table_name="feature_packs")
    if "project_id" in cols:
        op.drop_column("feature_packs", "project_id")