import pandas as pd
from sqlalchemy import select

from db.master_catalog import _master_fields, _records, build_master_sku_import_preview, import_master_sku_dataframe
from db.manual_taxonomy_assignments import replace_manual_taxonomy_assignments
from db.models import (
    ManualAttributeLockRule,
    ManualAttributeLockTarget,
    MasterCollectionRegistry,
    MasterProductAttributeValue,
)


def test_master_fields_prefers_full_category_collection_over_short_series():
    record = {
        "sku": "ACH-B12",
        "payload": {
            "sku": "ACH-B12",
            "Display Name": "B12 - Anna Caramel Harvest - Port & Bell - Base -- Assembled --",
            "Category": "Kitchen Cabinets : Port & Bell : Anna Caramel Harvest",
            "Product Category": "Kitchen Cabinets",
            "Product Collection/Series Name": "Anna",
            "Cabinet Brand": "Port & Bell",
            "Item Style": "Anna Caramel Harvest - Assembled",
            "Cabinet Type (Sub-Category 1)": "Base",
            "Cabinet Type (Sub-Category 2)": "Single Door Base Cabinet",
            "Assembled or RTA": "Assembled",
        },
    }

    fields = _master_fields(record)

    assert fields["brand"] == "Port & Bell"
    assert fields["category_l1"] == "Kitchen Cabinets"
    assert fields["category_l2"] == "Base Cabinets"
    assert fields["category_l3"] == "Single Door"
    assert fields["collection"] == "Anna Caramel Harvest"
    assert fields["assembly_type"] == "Assembled"


def test_import_preview_flags_polluted_collection_series_names():
    preview = build_master_sku_import_preview(
        pd.DataFrame(
            [
                {
                    "sku": "ACH-B12",
                    "Category": "Kitchen Cabinets : Port & Bell : Anna Caramel Harvest",
                    "Product Category": "Kitchen Cabinets",
                    "Product Collection/Series Name": "Anna",
                    "Cabinet Brand": "Port & Bell",
                    "Item Style": "Anna Caramel Harvest - Assembled",
                },
                {
                    "sku": "BAD-1",
                    "Category": "",
                    "Product Category": "Kitchen Cabinets",
                    "Product Collection/Series Name": "Kitchen Cabinets : Port & Bell : Wrong",
                    "Cabinet Brand": "Port & Bell",
                },
            ]
        )
    )

    assert preview["collection_counts"]["Anna Caramel Harvest"] == 1
    assert preview["suspect_collections"]["count"] == 1
    assert preview["suspect_collections"]["samples"][0]["sku"] == "BAD-1"


def test_records_adds_collection_color_and_style_family_attributes():
    rows = _records(
        pd.DataFrame(
            [
                {
                    "sku": "MNRO-B12",
                    "Category": "Kitchen Cabinets : Port & Bell : Maya Natural Rift White Oak",
                    "Item Style": "Maya Natural Rift White Oak - Assembled",
                    "Cabinet Door Style": "Slim Shaker",
                }
            ]
        )
    )

    payload = rows[0]["payload"]

    assert payload["Master Collection"] == "Maya Natural Rift White Oak"
    assert payload["Collection Color"] == "Natural Oak"
    assert payload["Style Family"] == "Slim Shaker"


def test_black_rift_white_oak_color_is_black():
    rows = _records(
        pd.DataFrame(
            [
                {
                    "sku": "MBRO-B12",
                    "Category": "Kitchen Cabinets : Port & Bell : Maya Black Rift White Oak",
                    "Item Style": "Maya Black Rift White Oak - Assembled",
                }
            ]
        )
    )

    assert rows[0]["payload"]["Collection Color"] == "Black"


def test_brand_spelling_is_normalized_for_castlecraft():
    record = {
        "sku": "BTP-B12",
        "payload": {
            "sku": "BTP-B12",
            "Display Name": "Belmont Toffee Pecan Base Cabinet",
            "Category": "Kitchen Cabinets : Castle Craft : Belmont Toffee Pecan",
            "Product Category": "Kitchen Cabinets",
            "Manufacturer": "Castle Craft",
        },
    }

    fields = _master_fields(record)

    assert fields["brand"] == "CastleCraft"


def test_brand_derivation_does_not_treat_interior_door_material_as_brand():
    record = {
        "sku": "DFPR24",
        "payload": {
            "sku": "DFPR24",
            "Display Name": "Door French 10-Lite Primed 24x80x1-3/8",
            "Category": "Interior Doors : Primed Pine",
            "Manufacturer": "Bridgewater Wholesalers",
            "Product Category": "-",
        },
    }

    fields = _master_fields(record)

    assert fields["brand"] == "Bridgewater Wholesalers"
    assert fields["category_l1"] == "Interior Doors"
    assert fields["category_l2"] == "Primed Pine"
    assert fields["product_family"] == "Doors"
    assert fields["collection"] is None


def test_brand_derivation_does_not_treat_style_or_accessory_category_as_brand():
    shower = _master_fields(
        {
            "sku": "SHO-ARU-GLA-6062",
            "payload": {
                "Category": "Showers : Aruba",
                "Manufacturer": "Yi Jie Glass Bathroom Co Ltd",
                "Cabinet Brand": "-",
                "Product Category": "-",
                "Item Style": "Aruba",
            },
        }
    )
    accessory = _master_fields(
        {
            "sku": "HS-CT-S",
            "payload": {
                "Category": "Kitchen Cabinets : Cabinet Accessories",
                "Manufacturer": "Hardware Resources",
                "Cabinet Brand": "-",
                "Product Category": "",
                "Item Style": "Value Select",
            },
        }
    )

    assert shower["brand"] == "Yi Jie Glass"
    assert accessory["brand"] == "Hardware Resources"


def test_brand_manufacturer_suffixes_are_canonicalized():
    bridgewater = _master_fields(
        {
            "sku": "DFPR28",
            "payload": {
                "Category": "Interior Doors : Primed Pine",
                "Manufacturer": "Bridgewater Wholesalers Inc.",
            },
        }
    )
    kountry = _master_fields(
        {
            "sku": "LM-JS-AW1242",
            "payload": {
                "Category": "",
                "Manufacturer": "Kountry Wood Products",
            },
        }
    )

    assert bridgewater["brand"] == "Bridgewater Wholesalers"
    assert kountry["brand"] == "Kountry Wood"


def test_brand_derivation_uses_obvious_product_identity_for_crystal_windows():
    rows = _records(
        pd.DataFrame(
            [
                {
                    "Item": "CRY-DH-2375x3725",
                    "Category": "Windows",
                    "Display Name": '23-3/4" x 37-1/4" - Double Hung Replacement Window - Crystal 100 Series',
                    "Item Style": "Windows",
                }
            ]
        )
    )
    fields = _master_fields(rows[0])

    assert fields["brand"] == "CRYSTAL"
    assert rows[0]["payload"]["Frame"] == "Double Hung"
    assert rows[0]["payload"]["Glass"] == "Clear Glass"


def test_window_brand_and_frame_derivation_for_northeast_slider():
    rows = _records(
        pd.DataFrame(
            [
                {
                    "Item": "NE-SL-3020",
                    "Category": "Windows",
                    "Display Name": "30 x 20 Slider Replacement Window - Northeast Pro Series with Tempered Glass",
                    "Item Style": "Windows",
                }
            ]
        )
    )
    fields = _master_fields(rows[0])

    assert fields["brand"] == "Northeast Pro"
    assert rows[0]["payload"]["Frame"] == "Slider"
    assert rows[0]["payload"]["Glass"] == "Tempered Glass"


def test_window_glass_derivation_for_frosted_and_grids():
    rows = _records(
        pd.DataFrame(
            [
                {
                    "Item": "NE-DH-FROST",
                    "Category": "Windows",
                    "Display Name": "Northeast Pro Series Double Hung Frosted Glass",
                },
                {
                    "Item": "CRY-DH-GRID",
                    "Category": "Windows",
                    "Display Name": "Crystal 100 Series Double Hung with Grids",
                },
            ]
        )
    )

    assert rows[0]["payload"]["Glass"] == "Frosted Glass"
    assert rows[1]["payload"]["Glass"] == "Grids"


def test_vanity_tops_are_category_not_collection():
    record = _records(
        pd.DataFrame(
            [
                {
                    "Item": "VT-MCQ-25-BS",
                    "Category": "Bathroom Vanities : Port & Bell : Vanity Tops",
                    "Product Category": "Bathroom Vanities",
                    "Item Style": "Vanities - Port & Bell",
                }
            ]
        )
    )[0]
    fields = _master_fields(record)

    assert fields["brand"] == "Port & Bell"
    assert fields["category_l1"] == "Bathroom Vanities"
    assert fields["category_l2"] == "Vanity Tops"
    assert fields["category_l3"] is None
    assert fields["collection"] is None


def test_variation_dimensions_from_cabinet_description_width_only():
    rows = _records(
        pd.DataFrame(
            [
                {
                    "sku": "ACH-B12",
                    "Display Name": "B12 - Anna Caramel Harvest - Port & Bell - Base -- Assembled --",
                    "Cabinet Description": '12" wide single door base cabinet featuring one working drawer',
                    "Item Size": "B12",
                }
            ]
        )
    )

    payload = rows[0]["payload"]

    assert payload["Variation Width In"] == "12"
    assert payload["Variation Axis"] == "width"
    assert payload["Variation Option Label"] == "12 in W"


def test_variation_dimensions_from_glass_door_description_width_and_height():
    rows = _records(
        pd.DataFrame(
            [
                {
                    "sku": "ACH-GD-W1530",
                    "Display Name": "GD-W1530 - Anna Caramel Harvest - Port & Bell - Wall 30H Glass Door Only",
                    "Cabinet Description": '15" wide x 30" tall loose glass door',
                    "Item Size": "GD-W1530",
                }
            ]
        )
    )

    payload = rows[0]["payload"]

    assert payload["Variation Width In"] == "15"
    assert payload["Variation Height In"] == "30"
    assert payload["Variation Axis"] == "width_height"
    assert payload["Variation Option Label"] == "15 in W x 30 in H"
    assert payload["Variation Group Code"] == "GD-W"


def test_glass_door_wall_override_only_applies_to_compact_gdw_codes():
    rows = _records(
        pd.DataFrame(
            [
                {
                    "sku": "ACH-GD-W1530",
                    "Display Name": "GD-W1530 - Anna Caramel Harvest - Port & Bell - Wall 30H Glass Door Only",
                    "Cabinet Description": '15" wide x 30" tall loose glass door',
                    "Item Size": "GD-W1530",
                    "Cabinet Type (Sub-Category 1)": "Accessories",
                    "Cabinet Type (Sub-Category 2)": "Loose Glass Door",
                },
                {
                    "sku": "ACH-GDW3030",
                    "Display Name": "GDW3030 - Anna Caramel Harvest - Port & Bell - Wall 30H Double Door Glass Wall Cabinet",
                    "Cabinet Description": '30" wide x 30" tall loose glass door',
                    "Item Size": "GDW3030",
                    "Cabinet Type (Sub-Category 1)": "Accessories",
                    "Cabinet Type (Sub-Category 2)": "Loose Glass Door",
                },
            ]
        )
    )

    single_payload = rows[0]["payload"]
    double_payload = rows[1]["payload"]

    assert single_payload["Cabinet Type (Sub-Category 1)"] == "Accessories"
    assert single_payload["Cabinet Type (Sub-Category 2)"] == "Loose Glass Door"
    assert double_payload["Cabinet Type (Sub-Category 1)"] == "Wall"
    assert double_payload["Cabinet Type (Sub-Category 2)"] == "Double Door Glass Wall Cabinet"


def test_variation_group_code_separates_glass_corner_patterns():
    rows = _records(
        pd.DataFrame(
            [
                {"sku": "ACH-GDWCOR2415", "Item Size": "GDWCOR2415"},
                {"sku": "ACH-WCOR2430", "Item Size": "WCOR2430"},
                {"sku": "ACH-WDC2430", "Item Size": "WDC2430"},
            ]
        )
    )

    assert [row["payload"]["Variation Group Code"] for row in rows] == ["GDWCOR", "WCOR", "WDC"]


def test_variation_dimensions_from_compact_vanity_display_name():
    rows = _records(
        pd.DataFrame(
            [
                {
                    "sku": "PBV-DAY-24NB",
                    "Display Name": "Dayton Vanity Blue 24w-21.5d-33h 2-Door",
                    "Item Size": "",
                }
            ]
        )
    )

    payload = rows[0]["payload"]

    assert payload["Variation Width In"] == "24"
    assert payload["Variation Depth In"] == "21.5"
    assert payload["Variation Height In"] == "33"
    assert payload["Variation Axis"] == "width_height_depth"


def test_collection_from_category_brand_and_item_style_prefix():
    record = {
        "sku": "ACH-B12",
        "payload": {
            "Category": "Kitchen Cabinets : Port & Bell : Anna Caramel Harvest",
            "Product Category": "Kitchen Cabinets",
            "Item Style Prefix": "ACH",
            "Item Style": "Anna Caramel Harvest - Value Select - Baltimore - Assembled",
            "Cabinet Type (Sub-Category 1)": "Base",
            "Cabinet Type (Sub-Category 2)": "Single Door Base Cabinet",
        },
    }

    fields = _master_fields(record)

    assert fields["category_l1"] == "Kitchen Cabinets"
    assert fields["category_l2"] == "Base Cabinets"
    assert fields["category_l3"] == "Single Door"
    assert fields["brand"] == "Port & Bell"
    assert fields["collection"] == "Anna Caramel Harvest"
    assert fields["assembly_type"] == "Assembled"


def test_collection_from_item_style_prefix_when_category_missing_leaf():
    record = {
        "sku": "HPW-B12",
        "payload": {
            "Category": "Kitchen Cabinets : Port & Bell",
            "Product Category": "Kitchen Cabinets",
            "Item Style Prefix": "HPW",
            "Item Style": "Harriet Pearl White - Assembled",
        },
    }

    fields = _master_fields(record)

    assert fields["collection"] == "Harriet Pearl White"
    assert fields["brand"] == "Port & Bell"


def test_item_style_prefix_dash_means_no_collection_without_category_leaf():
    record = {
        "sku": "WIN-101",
        "payload": {
            "Category": "Windows : Legion Manor",
            "Product Category": "Windows",
            "Item Style Prefix": "-",
            "Item Style": "Windows",
        },
    }

    fields = _master_fields(record)

    assert fields["collection"] is None
    assert fields["category_l1"] == "Windows"


def test_accessory_item_style_derives_subcategory_not_collection():
    rows = _records(
        pd.DataFrame(
            [
                {
                    "sku": "ACC-1",
                    "Category": "Kitchen Cabinets : Port & Bell",
                    "Product Category": "Kitchen Cabinets",
                    "Item Style": "Accessories: Pearl White - Accessories",
                    "Item Style Prefix": "-",
                }
            ]
        )
    )

    payload = rows[0]["payload"]
    fields = _master_fields(rows[0])

    assert fields["collection"] is None
    assert payload["Cabinet Type (Sub-Category 1)"] == "Accessories"


def test_item_style_with_brand_only_is_not_collection():
    record = {
        "sku": "LM-1",
        "payload": {
            "Category": "Kitchen Cabinets : Legion Manor",
            "Product Category": "Kitchen Cabinets",
            "Item Style Prefix": "-",
            "Item Style": "Legion Manor - Assembled",
        },
    }

    fields = _master_fields(record)

    assert fields["collection"] is None
    assert fields["brand"] == "Legion Manor"


def test_short_category_leaf_no_longer_expands_collection_from_item_style():
    record = {
        "sku": "FMM-BF3",
        "payload": {
            "sku": "FMM-BF3",
            "Display Name": "BF3 - Metro Mist - Fabuwood - Filler",
            "Category": "Kitchen Cabinets : Fabuwood : Mist",
            "Product Category": "-",
            "Item Style Prefix": "-",
            "Item Style": "Metro Mist - Assembled",
        },
    }

    fields = _master_fields(record)

    assert fields["collection"] == "Mist"
    assert fields["category_l1"] == "Kitchen Cabinets"
    assert fields["category_l2"] == "Panels and Fillers"
    assert fields["category_l3"] is None
    assert fields["brand"] == "Fabuwood"


def test_short_slate_leaf_uses_full_slate_gray_collection():
    record = {
        "sku": "CSG-SCM8",
        "payload": {
            "sku": "CSG-SCM8",
            "Display Name": "SCM8 - Slate Gray - Molding Scribe",
            "Category": "Kitchen Cabinets : NorthPoint Cabinetry : Slate",
            "Product Category": "-",
            "Item Style Prefix": "-",
            "Item Style": "Cooper Slate Gray - Assembled",
        },
    }

    fields = _master_fields(record)

    assert fields["collection"] == "Slate"


def test_blank_cabinet_type_is_inferred_for_mouldings_and_fillers():
    rows = _records(
        pd.DataFrame(
            [
                {
                    "sku": "CSG-SCM8",
                    "Display Name": "SCM8 - Slate Gray - Molding Scribe",
                    "Category": "Kitchen Cabinets : NorthPoint Cabinetry : Slate",
                    "Item Size": "SM8",
                    "Item Style": "Cooper Slate Gray - Assembled",
                    "Cabinet Type (Sub-Category 1)": "-",
                },
                {
                    "sku": "FMM-BF3",
                    "Display Name": "BF3 - Metro Mist - Fabuwood - Filler",
                    "Category": "Kitchen Cabinets : Fabuwood : Mist",
                    "Item Size": "BF3",
                    "Item Style": "Metro Mist - Assembled",
                    "Cabinet Type (Sub-Category 1)": "-",
                },
            ]
        )
    )

    assert rows[0]["payload"]["Cabinet Type (Sub-Category 1)"] == "Mouldings"
    assert rows[1]["payload"]["Cabinet Type (Sub-Category 1)"] == "Panels and Fillers"


def test_blank_cabinet_type_is_inferred_for_base_and_wall_codes():
    rows = _records(
        pd.DataFrame(
            [
                {
                    "sku": "CSG-B12",
                    "Display Name": "B12 - Cooper Slate Gray - Base -- Assembled --",
                    "Category": "Kitchen Cabinets : NorthPoint Cabinetry : Cooper Slate",
                    "Item Size": "B12",
                    "Item Style": "Cooper Slate Gray - Assembled",
                    "Cabinet Type (Sub-Category 1)": "-",
                },
                {
                    "sku": "CSG-W1530",
                    "Display Name": "W1530 - Cooper Slate Gray - Wall 30H -- Assembled --",
                    "Category": "Kitchen Cabinets : NorthPoint Cabinetry : Cooper Slate",
                    "Item Size": "W1530",
                    "Item Style": "Cooper Slate Gray - Assembled",
                    "Cabinet Type (Sub-Category 1)": "-",
                },
            ]
        )
    )

    assert rows[0]["payload"]["Cabinet Type (Sub-Category 1)"] == "Base"
    assert rows[1]["payload"]["Cabinet Type (Sub-Category 1)"] == "Wall"


def test_master_fields_prefers_manual_taxonomy_exact_master_sku(catalog_intent_session):
    session = catalog_intent_session
    session.add(
        MasterCollectionRegistry(
            code="ACH",
            name="Anna Caramel Harvest",
            path_slug="kitchen-cabinets/anna-caramel-harvest",
            is_active=True,
            aliases={"source_collection_code": "ACH", "alias_codes": ["ACH"]},
        )
    )
    session.flush()
    replace_manual_taxonomy_assignments(
        session,
        {
            "templates": [
                {
                    "source_sku": "ACH-DWR3L/R",
                    "canonical_taxonomy_path_slug": "kitchen-cabinets/base-cabinets/lazy-susan",
                    "canonical_taxonomy_path_label": "Kitchen Cabinets / Base Cabinets / Lazy Susan",
                    "shopping_l1": "base-cabinets",
                    "shopping_l2": "base-cabinets/lazy-susan",
                    "l2_category": "Base Cabinets",
                    "l3_category": "Lazy Susan",
                    "collections": [
                        {
                            "collection_code": "ACH",
                            "source_collection_code": "ACH",
                            "sku_prefix": "ACH",
                            "collection_name": "Anna Caramel Harvest",
                            "collection_path_slug": "kitchen-cabinets/anna-caramel-harvest",
                            "shopping_collection": "Anna Caramel Harvest",
                            "master_sku": "ACH-DWR3L/R",
                        }
                    ],
                }
            ]
        },
    )

    fields = _master_fields(
        {
            "sku": "ACH-DWR3L/R",
            "payload": {
                "Item": "DWR3L/R",
                "Category": "Kitchen Cabinets : Port & Bell : Wrong Collection",
                "Product Category": "Kitchen Cabinets",
                "Item Style": "Wrong Collection - Assembled",
                "Cabinet Type (Sub-Category 1)": "Wall",
                "Cabinet Type (Sub-Category 2)": "Wrong Type",
            },
        },
        session=session,
    )

    assert fields["category_l1"] == "Kitchen Cabinets"
    assert fields["category_l2"] == "Base Cabinets"
    assert fields["category_l3"] == "Lazy Susan"
    assert fields["collection"] == "Anna Caramel Harvest"


def test_master_fields_prefers_manual_taxonomy_base_source_sku_match(catalog_intent_session):
    session = catalog_intent_session
    registry = MasterCollectionRegistry(
        code="ACH",
        name="Anna Caramel Harvest",
        path_slug="kitchen-cabinets/anna-caramel-harvest",
        is_active=True,
        aliases={"source_collection_code": "ACH", "alias_codes": ["ACH"]},
    )
    session.add(registry)
    session.flush()
    replace_manual_taxonomy_assignments(
        session,
        {
            "templates": [
                {
                    "source_sku": "W0930",
                    "canonical_taxonomy_path_slug": "kitchen-cabinets/wall-cabinets/basic-wall",
                    "canonical_taxonomy_path_label": "Kitchen Cabinets / Wall Cabinets / Basic Wall",
                    "shopping_l1": "wall-cabinets",
                    "shopping_l2": "wall-cabinets/basic-wall",
                    "l2_category": "Wall Cabinets",
                    "l3_category": "Basic Wall",
                    "collections": [
                        {
                            "collection_code": "ACH",
                            "source_collection_code": "ACH",
                            "sku_prefix": "ACH",
                            "collection_name": "Anna Caramel Harvest",
                            "collection_path_slug": "kitchen-cabinets/anna-caramel-harvest",
                            "shopping_collection": "Anna Caramel Harvest",
                            "master_sku": "ACH-W0930",
                        }
                    ],
                }
            ]
        },
    )

    fields = _master_fields(
        {
            "sku": "ACH-W0930",
            "payload": {
                "Item Size": "W0930",
                "Category": "Kitchen Cabinets : Port & Bell : Wrong Collection",
                "Product Category": "Kitchen Cabinets",
                "Item Style": "Wrong Collection - Assembled",
            },
        },
        session=session,
    )

    assert fields["category_l2"] == "Wall Cabinets"
    assert fields["category_l3"] == "Basic Wall"
    assert fields["collection"] == "Anna Caramel Harvest"
    assert fields["collection_registry_id"] == registry.id
    assert fields["source_item"] == "W0930"


def test_master_fields_resolves_collection_only_from_active_master_collection_registry(catalog_intent_session):
    session = catalog_intent_session
    registry = MasterCollectionRegistry(
        code="FMM",
        name="Quest Metro Mist",
        path_slug="kitchen-cabinets/metro-mist",
        is_active=True,
        aliases={"source_collection_code": "FMM", "alias_codes": ["FMM"]},
    )
    session.add(registry)
    session.flush()

    fields = _master_fields(
        {
            "sku": "FMM-BF3",
            "payload": {
                "Item Size": "BF3",
                "Category": "Kitchen Cabinets : Fabuwood : Mist",
                "Product Category": "Kitchen Cabinets",
                "Item Style Prefix": "FMM",
                "Item Style": "Metro Mist - Assembled",
            },
        },
        session=session,
    )

    assert fields["collection"] == "Quest Metro Mist"
    assert fields["collection_registry_id"] == registry.id


def test_import_master_sku_dataframe_applies_manual_attribute_locks_before_attr_write(catalog_intent_session):
    session = catalog_intent_session
    registry = MasterCollectionRegistry(
        code="ACH",
        name="Anna Caramel Harvest",
        path_slug="kitchen-cabinets/anna-caramel-harvest",
        is_active=True,
        aliases={"source_collection_code": "ACH", "alias_codes": ["ACH"]},
    )
    session.add(registry)
    session.flush()
    replace_manual_taxonomy_assignments(
        session,
        {
            "templates": [
                {
                    "source_sku": "W0930",
                    "canonical_taxonomy_path_slug": "kitchen-cabinets/wall-cabinets/basic-wall",
                    "canonical_taxonomy_path_label": "Kitchen Cabinets / Wall Cabinets / Basic Wall",
                    "shopping_l1": "wall-cabinets",
                    "shopping_l2": "wall-cabinets/basic-wall",
                    "l2_category": "Wall Cabinets",
                    "l3_category": "Basic Wall",
                    "collections": [
                        {
                            "collection_code": "ACH",
                            "source_collection_code": "ACH",
                            "sku_prefix": "ACH",
                            "collection_name": "Anna Caramel Harvest",
                            "collection_path_slug": "kitchen-cabinets/anna-caramel-harvest",
                            "shopping_collection": "Anna Caramel Harvest",
                            "master_sku": "ACH-W0930",
                        }
                    ],
                }
            ]
        },
    )
    rule = ManualAttributeLockRule(
        canonical_taxonomy_path_slug="kitchen-cabinets/wall-cabinets",
        path_match_kind="prefix",
        collection_registry_id=registry.id,
        collection_code="ACH",
        collection_name="Anna Caramel Harvest",
        priority=10,
        rule_status="active",
    )
    session.add(rule)
    session.flush()
    session.add_all(
        [
            ManualAttributeLockTarget(
                rule_id=rule.id,
                attribute_code="door_style",
                attribute_value="Slim Shaker",
                is_locked=True,
            ),
            ManualAttributeLockTarget(
                rule_id=rule.id,
                attribute_code="color_family",
                attribute_value="Brown",
                is_locked=True,
            ),
        ]
    )
    session.flush()

    result = import_master_sku_dataframe(
        session,
        pd.DataFrame(
            [
                {
                    "sku": "ACH-W0930",
                    "Item": "W0930",
                    "Category": "Kitchen Cabinets : Port & Bell : Wrong Collection",
                    "Product Category": "Kitchen Cabinets",
                    "Item Style": "Wrong Collection - Assembled",
                    "Door Style": "Polluted Style",
                    "Color Family": "Polluted Color",
                }
            ]
        ),
        source_label="test_manual_attribute_lock",
    )

    values = {
        row.attribute_code: row.value
        for row in session.scalars(
            select(MasterProductAttributeValue).where(MasterProductAttributeValue.sku == "ACH-W0930")
        ).all()
    }
    assert result["inserted"] == 1
    assert values["door_style"] == "Slim Shaker"
    assert values["color_family"] == "Brown"


def test_import_master_sku_dataframe_canonicalizes_and_skips_noisy_attribute_codes(catalog_intent_session):
    session = catalog_intent_session
    result = import_master_sku_dataframe(
        session,
        pd.DataFrame(
            [
                {
                    "sku": "ACH-B12",
                    "Item Size": "B12",
                    "Category": "Kitchen Cabinets : Port & Bell : Anna Caramel Harvest",
                    "Product Category": "Kitchen Cabinets",
                    "Cabinet Brand": "Port & Bell",
                    "Country of Origin (COO)": "Imported",
                    "Composite Wood Product (CWP)": "Yes",
                    "Grab-N-Go": "Yes",
                    "Item Style": "Anna Caramel Harvest - Assembled",
                }
            ]
        ),
        source_label="test",
    )

    values = {
        row.attribute_code: row.value
        for row in session.scalars(
            select(MasterProductAttributeValue).where(MasterProductAttributeValue.sku == "ACH-B12")
        ).all()
    }

    assert result["inserted"] == 1
    assert values["country_of_origin"] == "Imported"
    assert values["composite_wood_product"] == "Yes"
    assert "country_of_origin_coo" not in values
    assert "composite_wood_product_cwp" not in values
    assert "grab_n_go" not in values
    assert "item_style_1" not in values