"""Add unique constraint on SubscriptionMeter

Revision ID: 8f5e1688ea9d
Revises: 2289e219f18f
Create Date: 2025-05-28 13:49:44.586511

"""

import sqlalchemy as sa
from alembic import op

# Polar Custom Imports

# revision identifiers, used by Alembic.
revision = "8f5e1688ea9d"
down_revision = "2289e219f18f"
branch_labels: tuple[str] | None = None
depends_on: tuple[str] | None = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.execute(
        """
        WITH numbered_duplicates AS (
            SELECT
                id, -- Assuming there's a primary key column named 'id'
                subscription_id,
                meter_id,
                ROW_NUMBER() OVER (
                    PARTITION BY subscription_id, meter_id
                    ORDER BY created_at -- Assuming there's a timestamp column to determine the oldest
                ) AS row_num
            FROM subscription_meters
        )
        DELETE FROM subscription_meters
        WHERE id IN (
            SELECT id FROM numbered_duplicates WHERE row_num > 1
        );
        """
    )
    op.create_unique_constraint(
        op.f("subscription_meters_subscription_id_meter_id_key"),
        "subscription_meters",
        ["subscription_id", "meter_id"],
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(
        op.f("subscription_meters_subscription_id_meter_id_key"),
        "subscription_meters",
        type_="unique",
    )
    # ### end Alembic commands ###
