"""fix family history share constraint to allow multiple inactive shares

Revision ID: 26ce48bf1215
Revises: 14cd756b39ad
Create Date: 2025-07-24 20:40:04.311925

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '26ce48bf1215'
down_revision = '14cd756b39ad'
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    # Drop the existing constraint that includes is_active (if it exists)
    op.execute("""
        DO $$ 
        BEGIN 
            IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'unique_active_family_history_share') THEN
                ALTER TABLE family_history_shares DROP CONSTRAINT unique_active_family_history_share;
            END IF;
        END $$;
    """)
    
    # Create a partial unique constraint that only applies to active shares (if it doesn't exist)
    # This allows multiple inactive shares but only one active share per (family_member_id, shared_with_user_id)
    op.execute("""
        DO $$ 
        BEGIN 
            IF NOT EXISTS (
                SELECT 1 FROM pg_indexes 
                WHERE indexname = 'unique_active_family_history_share_partial'
            ) THEN
                CREATE UNIQUE INDEX unique_active_family_history_share_partial 
                ON family_history_shares (family_member_id, shared_with_user_id) 
                WHERE is_active = true;
            END IF;
        END $$;
    """)
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    # Drop the partial constraint
    op.execute("DROP INDEX IF EXISTS unique_active_family_history_share_partial")
    
    # Recreate the original constraint (this might fail if there are duplicate inactive shares)
    op.create_unique_constraint('unique_active_family_history_share', 'family_history_shares', 
                              ['family_member_id', 'shared_with_user_id', 'is_active'])
    # ### end Alembic commands ###
