"""Consolidate duplicate shopify_attribute_registry shop_code aliases.

Revision ID: 0054_consolidate_shopify_registry_shop_code
Revises: 0053_add_listing_intersection_rule
"""

from __future__ import annotations

from alembic import op
import sqlalchemy as sa


revision = "0054_consolidate_shopify_registry_shop_code"
down_revision = "0053_add_listing_intersection_rule"
branch_labels = None
depends_on = None


def upgrade() -> None:
    bind = op.get_bind()
    connections = bind.execute(
        sa.text(
            """
            SELECT id, shop_code, shop_domain
            FROM shopify_connections
            ORDER BY id
            """
        )
    ).mappings().all()
    if not connections:
        return

    from db.session import get_session
    from db.shopify_registry import alternate_registry_shop_codes
    from db.shopify_repositories import SqlAlchemyShopifyAttributeRegistryRepository

    with get_session() as session:
        repo = SqlAlchemyShopifyAttributeRegistryRepository(session)
        for row in connections:
            canonical = str(row["shop_code"] or "").strip()
            if not canonical:
                continue
            from db.models import ShopifyConnection

            connection = session.get(ShopifyConnection, int(row["id"]))
            alternates = alternate_registry_shop_codes(
                connection=connection,
                canonical_shop_code=canonical,
            )
            repo.consolidate_shop_codes(canonical, alternates)
        session.commit()


def downgrade() -> None:
    # Data consolidation is not reversible.
    pass