"""Fix Shopify SEO and short-description static mappings.

Revision ID: 0050_fix_shopify_static_content_mappings
Revises: 0049_add_master_taxonomy_path_alias
"""

from alembic import op


revision = "0050_fix_shopify_static_content_mappings"
down_revision = "0049_add_master_taxonomy_path_alias"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        UPDATE master_static_field_mapping
        SET shopify_attribute_code = CASE master_attribute_code
            WHEN 'meta_title' THEN 'seo_title'
            WHEN 'meta_description' THEN 'seo_description'
            WHEN 'short_description' THEN 'custom_short_description'
        END,
        updated_at = now()
        WHERE master_attribute_code IN ('meta_title', 'meta_description', 'short_description')
        """
    )
    op.execute(
        """
        DELETE FROM channel_attribute_alias
        WHERE channel_code = 'shopify'
          AND mapping_scope = 'static'
          AND canonical_code IN ('meta_title', 'meta_description', 'short_description')
        """
    )
    op.execute(
        """
        INSERT INTO channel_attribute_alias (
            channel_code, canonical_code, channel_attribute_code, mapping_scope,
            action, data_type, is_active, notes, created_at, updated_at
        )
        SELECT
            'shopify', master_attribute_code, shopify_attribute_code, 'static',
            'use_existing', data_type, true,
            'static_field_map:' || master_attribute_code, now(), now()
        FROM master_static_field_mapping
        WHERE master_attribute_code IN ('meta_title', 'meta_description', 'short_description')
          AND shopify_attribute_code IS NOT NULL
        ON CONFLICT ON CONSTRAINT uq_channel_attribute_alias_channel_canonical_attr
        DO UPDATE SET
            mapping_scope = EXCLUDED.mapping_scope,
            action = EXCLUDED.action,
            data_type = EXCLUDED.data_type,
            is_active = EXCLUDED.is_active,
            notes = EXCLUDED.notes,
            updated_at = now()
        """
    )


def downgrade() -> None:
    op.execute(
        """
        DELETE FROM channel_attribute_alias
        WHERE channel_code = 'shopify'
          AND mapping_scope = 'static'
          AND canonical_code IN ('meta_title', 'meta_description', 'short_description')
        """
    )
    op.execute(
        """
        UPDATE master_static_field_mapping
        SET shopify_attribute_code = CASE master_attribute_code
            WHEN 'meta_title' THEN 'meta_title'
            WHEN 'meta_description' THEN 'meta_description'
            WHEN 'short_description' THEN NULL
        END,
        updated_at = now()
        WHERE master_attribute_code IN ('meta_title', 'meta_description', 'short_description')
        """
    )
    op.execute(
        """
        INSERT INTO channel_attribute_alias (
            channel_code, canonical_code, channel_attribute_code, mapping_scope,
            action, data_type, is_active, notes, created_at, updated_at
        )
        SELECT
            'shopify', master_attribute_code, shopify_attribute_code, 'static',
            'use_existing', data_type, true,
            'static_field_map:' || master_attribute_code, now(), now()
        FROM master_static_field_mapping
        WHERE master_attribute_code IN ('meta_title', 'meta_description')
          AND shopify_attribute_code IS NOT NULL
        ON CONFLICT ON CONSTRAINT uq_channel_attribute_alias_channel_canonical_attr
        DO UPDATE SET
            mapping_scope = EXCLUDED.mapping_scope,
            action = EXCLUDED.action,
            data_type = EXCLUDED.data_type,
            is_active = EXCLUDED.is_active,
            notes = EXCLUDED.notes,
            updated_at = now()
        """
    )