"""add member_id to benefit_grants

Revision ID: c4a698f55b85
Revises: 4b9bb0cb2d44
Create Date: 2025-11-21 14:22:30.864674

"""

import sqlalchemy as sa
from alembic import op

# Polar Custom Imports

# revision identifiers, used by Alembic.
revision = "c4a698f55b85"
down_revision = "4b9bb0cb2d44"
branch_labels: tuple[str] | None = None
depends_on: tuple[str] | None = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    # Add member_id column as nullable
    op.add_column("benefit_grants", sa.Column("member_id", sa.Uuid(), nullable=True))

    # Create index on member_id for query performance
    op.create_index(
        op.f("ix_benefit_grants_member_id"),
        "benefit_grants",
        ["member_id"],
        unique=False,
    )

    # Create foreign key to members table with cascade delete
    op.create_foreign_key(
        op.f("benefit_grants_member_id_fkey"),
        "benefit_grants",
        "members",
        ["member_id"],
        ["id"],
        ondelete="cascade",
    )

    # Add new unique constraint for member-based grants
    # (subscription_id, member_id, benefit_id)
    # This coexists with the existing (subscription_id, customer_id, benefit_id) constraint
    # NULLs are treated as distinct, allowing multiple customer-level grants (member_id=NULL)
    # while enforcing uniqueness for member-specific grants (member_id IS NOT NULL)
    op.create_unique_constraint(
        "benefit_grants_smb_key",
        "benefit_grants",
        ["subscription_id", "member_id", "benefit_id"],
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    # Drop new unique constraint
    op.drop_constraint("benefit_grants_smb_key", "benefit_grants", type_="unique")

    # Drop foreign key constraint
    op.drop_constraint(
        op.f("benefit_grants_member_id_fkey"), "benefit_grants", type_="foreignkey"
    )

    # Drop index
    op.drop_index(op.f("ix_benefit_grants_member_id"), table_name="benefit_grants")

    # Drop column
    op.drop_column("benefit_grants", "member_id")
    # ### end Alembic commands ###
