from db.models import MasterProduct from db.variation_builder import commit_variation_plan, preview_variation_plan, suggest_variation_plan def _product(sku, name, collection="Arcadia", family="Cabinets", item_size=None): product = MasterProduct() product.id = abs(hash(sku)) % 10000 product.sku = sku product.name = name product.collection = collection product.product_family = family product.item_size = item_size product.is_active = True return product def test_preview_variation_plan_groups_selected_skus(monkeypatch): products = [ _product("ACH-GDW1215", "Wall 12x15", item_size="12x15"), _product("ACH-GDW1515", "Wall 15x15", item_size="15x15"), _product("ACH-B12", "Base 12", item_size="12"), ] import db.variation_builder as mod monkeypatch.setattr(mod, "_products", lambda _session, _skus: products) monkeypatch.setattr( mod, "_attrs_by_sku", lambda _session, _ids: { "ACH-GDW1215": {"cabinet_type": "Wall", "variation_width_in": "12"}, "ACH-GDW1515": {"cabinet_type": "Wall", "variation_width_in": "15"}, "ACH-B12": {"cabinet_type": "Base", "variation_width_in": "12"}, }, ) preview = preview_variation_plan( object(), skus=[p.sku for p in products], group_attrs=["collection", "cabinet_type"], option_attrs=["variation_width_in"], min_children=2, ) assert preview["group_count"] == 1 assert preview["relation_count"] == 2 assert preview["groups"][0]["parent_sku"] == "ACH-GDW-CONFIG" assert preview["groups"][0]["option_label"] == "Width" assert [child["sku"] for child in preview["groups"][0]["children"]] == ["ACH-GDW1215", "ACH-GDW1515"] assert preview["skipped"][0]["sku"] == "ACH-B12" def test_preview_variation_plan_never_uses_display_label_as_axis(monkeypatch): products = [ _product("ACH-B12", "Base 12"), _product("ACH-B15", "Base 15"), ] import db.variation_builder as mod monkeypatch.setattr(mod, "_products", lambda _session, _skus: products) monkeypatch.setattr( mod, "_attrs_by_sku", lambda _session, _ids: { "ACH-B12": {"variation_option_label": "12 in W"}, "ACH-B15": {"variation_option_label": "15 in W"}, }, ) preview = preview_variation_plan( object(), skus=[p.sku for p in products], group_attrs=["collection"], option_attrs=["variation_option_label"], min_children=2, ) assert preview["group_count"] == 0 assert {row["reason"] for row in preview["skipped"]} == {"no_varying_option_axis"} def test_preview_variation_plan_uses_only_varying_dimension_axis(monkeypatch): products = [ _product("ACH-B12", "Base 12"), _product("ACH-B15", "Base 15"), ] import db.variation_builder as mod monkeypatch.setattr(mod, "_products", lambda _session, _skus: products) monkeypatch.setattr( mod, "_attrs_by_sku", lambda _session, _ids: { "ACH-B12": { "cabinet_type_sub_category_1": "Base", "cabinet_type_sub_category_2": "Single Door Base Cabinet", "variation_group_code": "B", "variation_width_in": "12", "variation_height_in": "34.5", "variation_depth_in": "24", }, "ACH-B15": { "cabinet_type_sub_category_1": "Base", "cabinet_type_sub_category_2": "Single Door Base Cabinet", "variation_group_code": "B", "variation_width_in": "15", "variation_height_in": "34.5", "variation_depth_in": "24", }, }, ) preview = preview_variation_plan( object(), skus=[p.sku for p in products], group_attrs=["cabinet_type_sub_category_1", "cabinet_type_sub_category_2", "variation_group_code"], option_attrs=["variation_width_in", "variation_height_in", "variation_depth_in", "item_size"], ) assert preview["group_count"] == 1 assert preview["groups"][0]["option_attrs"] == ["variation_width_in"] assert preview["groups"][0]["option_label"] == "Width" assert preview["groups"][0]["channel"]["option_attrs"] == ["width"] assert preview["groups"][0]["children"][0]["option_mapping"] == {"variation_width_in": "12"} assert preview["groups"][0]["parent_name"] == "Arcadia Single Door Base Cabinet" def test_preview_variation_plan_skips_group_without_varying_axis(monkeypatch): products = [ _product("ACH-BEC1224L", "Angle End Left"), _product("ACH-BEC1224R", "Angle End Right"), ] import db.variation_builder as mod monkeypatch.setattr(mod, "_products", lambda _session, _skus: products) monkeypatch.setattr( mod, "_attrs_by_sku", lambda _session, _ids: { "ACH-BEC1224L": {"variation_group_code": "BEC", "variation_width_in": "12", "variation_height_in": "34.5", "variation_depth_in": "24"}, "ACH-BEC1224R": {"variation_group_code": "BEC", "variation_width_in": "12", "variation_height_in": "34.5", "variation_depth_in": "24"}, }, ) preview = preview_variation_plan( object(), skus=[p.sku for p in products], group_attrs=["variation_group_code"], option_attrs=["variation_width_in", "variation_height_in", "variation_depth_in", "item_size"], ) assert preview["group_count"] == 0 assert {row["reason"] for row in preview["skipped"]} == {"no_varying_option_axis"} def test_preview_variation_plan_derives_drawer_count_axis(monkeypatch): products = [ _product("GSW-DB30-2", "30 inch 2 Drawer Base"), _product("GSW-DB30-3", "30 inch 3 Drawer Base"), ] import db.variation_builder as mod monkeypatch.setattr(mod, "_products", lambda _session, _skus: products) monkeypatch.setattr( mod, "_attrs_by_sku", lambda _session, _ids: { "GSW-DB30-2": { "variation_group_code": "DB", "variation_width_in": "30", "variation_height_in": "34.5", "variation_depth_in": "24", }, "GSW-DB30-3": { "variation_group_code": "DB", "variation_width_in": "30", "variation_height_in": "34.5", "variation_depth_in": "24", }, }, ) preview = preview_variation_plan( object(), skus=[p.sku for p in products], group_attrs=["variation_group_code"], option_attrs=["variation_width_in", "variation_height_in", "variation_depth_in", "variation_drawer_count"], ) assert preview["group_count"] == 1 assert preview["groups"][0]["option_attrs"] == ["variation_drawer_count"] assert preview["groups"][0]["children"][0]["option_mapping"]["variation_drawer_count"] == "2" assert preview["groups"][0]["children"][1]["option_mapping"]["variation_drawer_count"] == "3" def test_preview_variation_plan_derives_door_handing_with_width_axis(monkeypatch): products = [ _product("MBRO-VSD3021DL", "Vanity Sink Door 30 Left"), _product("MBRO-VSD3021DR", "Vanity Sink Door 30 Right"), _product("MBRO-VSD3621DL", "Vanity Sink Door 36 Left"), _product("MBRO-VSD3621DR", "Vanity Sink Door 36 Right"), ] import db.variation_builder as mod monkeypatch.setattr(mod, "_products", lambda _session, _skus: products) monkeypatch.setattr( mod, "_attrs_by_sku", lambda _session, _ids: { "MBRO-VSD3021DL": {"variation_group_code": "VSD", "variation_width_in": "30", "variation_height_in": "21"}, "MBRO-VSD3021DR": {"variation_group_code": "VSD", "variation_width_in": "30", "variation_height_in": "21"}, "MBRO-VSD3621DL": {"variation_group_code": "VSD", "variation_width_in": "36", "variation_height_in": "21"}, "MBRO-VSD3621DR": {"variation_group_code": "VSD", "variation_width_in": "36", "variation_height_in": "21"}, }, ) preview = preview_variation_plan( object(), skus=[p.sku for p in products], group_attrs=["variation_group_code"], option_attrs=["variation_width_in", "variation_height_in", "variation_door_handing"], ) assert preview["group_count"] == 1 assert preview["groups"][0]["option_attrs"] == ["variation_width_in", "variation_door_handing"] first_child = preview["groups"][0]["children"][0]["option_mapping"] assert first_child["variation_width_in"] == "30" assert first_child["variation_door_handing"] == "Left" def test_preview_variation_plan_derives_panel_type_axis(monkeypatch): products = [ _product("MBRO-FPP9648 1/4", "Finished Plywood Panel"), _product("MBRO-FPP9648GL 1/4", "Finished Plywood Panel Glass"), ] import db.variation_builder as mod monkeypatch.setattr(mod, "_products", lambda _session, _skus: products) monkeypatch.setattr( mod, "_attrs_by_sku", lambda _session, _ids: { "MBRO-FPP9648 1/4": {"variation_group_code": "FPP", "variation_depth_in": "0.25"}, "MBRO-FPP9648GL 1/4": {"variation_group_code": "FPP", "variation_depth_in": "0.25"}, }, ) preview = preview_variation_plan( object(), skus=[p.sku for p in products], group_attrs=["variation_group_code"], option_attrs=["variation_depth_in", "variation_panel_type"], ) assert preview["group_count"] == 1 assert preview["groups"][0]["option_attrs"] == ["variation_panel_type"] assert [child["option_mapping"]["variation_panel_type"] for child in preview["groups"][0]["children"]] == [ "Standard", "Glass", ] def test_preview_variation_plan_derives_variant_code_axis(monkeypatch): products = [ _product("GSW-BBC45/48FHL/R", "Blind Corner Base Cabinet"), _product("GSW-BBC45/48FHL/R-MC", "Blind Corner Base Cabinet Multi"), _product("GSW-BBC45/48L/R", "Blind Corner Base Cabinet"), ] import db.variation_builder as mod monkeypatch.setattr(mod, "_products", lambda _session, _skus: products) monkeypatch.setattr( mod, "_attrs_by_sku", lambda _session, _ids: { "GSW-BBC45/48FHL/R": {"variation_group_code": "BBC", "variation_width_in": "45"}, "GSW-BBC45/48FHL/R-MC": {"variation_group_code": "BBC", "variation_width_in": "45"}, "GSW-BBC45/48L/R": {"variation_group_code": "BBC", "variation_width_in": "45"}, }, ) preview = preview_variation_plan( object(), skus=[p.sku for p in products], group_attrs=["variation_group_code"], option_attrs=["variation_width_in", "variation_variant_code"], ) assert preview["group_count"] == 1 assert preview["groups"][0]["option_attrs"] == ["variation_variant_code"] values = [child["option_mapping"]["variation_variant_code"] for child in preview["groups"][0]["children"]] assert values == ["FHL-R", "FHL-R-MC", "L-R"] def test_commit_variation_plan_writes_parent_and_relations(monkeypatch): captured = {} import db.variation_builder as mod monkeypatch.setattr(mod, "_first_child_product", lambda _session, _children: _product("ACH-GDW1215", "Wall 12x15")) monkeypatch.setattr(mod, "_upsert_parent_products", lambda _session, rows: captured.setdefault("parents", rows)) monkeypatch.setattr(mod, "_copy_collection_shareable_attrs_to_parents", lambda *_a, **_k: 0) def fake_assign_parent_skus(_session, skus, channels): captured["assign"] = (tuple(skus), tuple(channels)) return len(skus) def fake_upsert_identity_mappings(_session, skus, channels, mapping_connection_ids, source_label): captured["mapping"] = (tuple(skus), tuple(channels), dict(mapping_connection_ids), source_label) return len(skus) def fake_upsert_relations(_session, rows): captured["relations"] = rows return len(rows) monkeypatch.setattr(mod, "_assign_parent_skus", fake_assign_parent_skus) monkeypatch.setattr(mod, "_upsert_identity_mappings", fake_upsert_identity_mappings) monkeypatch.setattr(mod, "upsert_relations", fake_upsert_relations) result = commit_variation_plan( object(), groups=[ { "parent_sku": "ACH-GDW-CONFIG", "parent_name": "Arcadia Wall", "group_values": {"collection": "Arcadia", "cabinet_type": "Wall"}, "option_attrs": ["item_size"], "children": [ {"sku": "ACH-GDW1215", "option_mapping": {"item_size": "12x15"}}, {"sku": "ACH-GDW1515", "option_mapping": {"item_size": "15x15"}}, ], } ], ) assert result["parents_upserted"] == 1 assert result["relations_upserted"] == 2 assert result["assignments_upserted"] == 1 assert result["mappings_upserted"] == 1 assert captured["parents"][0]["sku"] == "ACH-GDW-CONFIG" assert captured["relations"][1]["child_sku"] == "ACH-GDW1515" assert captured["assign"] == (("ACH-GDW-CONFIG",), ("magento",)) assert captured["mapping"][1] == ("magento",) def test_suggest_variation_plan_filters_and_reuses_preview(monkeypatch): captured = {} class FakeResult: def all(self): return [("ACH-B12",), ("ACH-B15",)] class FakeSession: def execute(self, stmt): captured["stmt"] = stmt return FakeResult() import db.variation_builder as mod def fake_preview(_session, **kwargs): captured["preview_kwargs"] = kwargs return { "status": "ok", "input_skus": len(kwargs["skus"]), "matched_skus": len(kwargs["skus"]), "group_count": 1, "relation_count": 2, "groups": [], "skipped": [], } monkeypatch.setattr(mod, "preview_variation_plan", fake_preview) result = suggest_variation_plan( FakeSession(), category_l1="Kitchen Cabinets", assembly_type="Assembled", collection="Anna", group_attrs=["collection"], option_attrs=["variation_width_in"], ) assert captured["preview_kwargs"]["skus"] == ["ACH-B12", "ACH-B15"] assert captured["preview_kwargs"]["group_attrs"] == ["collection"] assert result["candidate_sku_count"] == 2 assert result["candidate_filter"]["category_l1"] == "Kitchen Cabinets" def test_preview_variation_plan_splits_mixed_sku_families(monkeypatch): products = [ _product("ASG-BCOR1230", "Corner Base 12"), _product("ASG-BCOR1530", "Corner Base 15"), _product("ASG-EZR33", "Easy Reach 33"), _product("ASG-EZR36", "Easy Reach 36"), ] import db.variation_builder as mod monkeypatch.setattr(mod, "_products", lambda _session, _skus: products) monkeypatch.setattr( mod, "_attrs_by_sku", lambda _session, _ids: { "ASG-BCOR1230": { "collection": "Anna Stone Gray", "cabinet_type_sub_category_1": "Base", "cabinet_type_sub_category_2": "Corner Base", "variation_width_in": "12", "variation_height_in": "34.5", "variation_depth_in": "24", }, "ASG-BCOR1530": { "collection": "Anna Stone Gray", "cabinet_type_sub_category_1": "Base", "cabinet_type_sub_category_2": "Corner Base", "variation_width_in": "15", "variation_height_in": "34.5", "variation_depth_in": "24", }, "ASG-EZR33": { "collection": "Anna Stone Gray", "cabinet_type_sub_category_1": "Base", "cabinet_type_sub_category_2": "Corner Base", "variation_width_in": "33", "variation_height_in": "34.5", "variation_depth_in": "24", }, "ASG-EZR36": { "collection": "Anna Stone Gray", "cabinet_type_sub_category_1": "Base", "cabinet_type_sub_category_2": "Corner Base", "variation_width_in": "36", "variation_height_in": "34.5", "variation_depth_in": "24", }, }, ) preview = preview_variation_plan( object(), skus=[p.sku for p in products], group_attrs=["collection", "cabinet_type_sub_category_1", "cabinet_type_sub_category_2"], option_attrs=["variation_width_in", "variation_height_in", "variation_depth_in"], ) assert preview["group_count"] == 2 families = {group["sku_family"] for group in preview["groups"]} assert families == {"ASG-BCOR", "ASG-EZR"} for group in preview["groups"]: assert len(group["children"]) == 2 assert group["dimension_axis_count"] == 1 def test_preview_variation_plan_filters_by_option_axis_count(monkeypatch): products = [ _product("ACH-B12", "Base 12"), _product("ACH-B15", "Base 15"), _product("ACH-WH12", "Wall 12"), _product("ACH-WH15", "Wall 15"), ] import db.variation_builder as mod monkeypatch.setattr(mod, "_products", lambda _session, _skus: products) monkeypatch.setattr( mod, "_attrs_by_sku", lambda _session, _ids: { "ACH-B12": { "variation_group_code": "B", "variation_width_in": "12", "variation_height_in": "34.5", "variation_depth_in": "24", }, "ACH-B15": { "variation_group_code": "B", "variation_width_in": "15", "variation_height_in": "34.5", "variation_depth_in": "24", }, "ACH-WH12": { "variation_group_code": "WH", "variation_width_in": "12", "variation_height_in": "30", "variation_depth_in": "12", }, "ACH-WH15": { "variation_group_code": "WH", "variation_width_in": "15", "variation_height_in": "36", "variation_depth_in": "12", }, }, ) one_axis = preview_variation_plan( object(), skus=[p.sku for p in products], group_attrs=["variation_group_code"], option_attrs=["variation_width_in", "variation_height_in", "variation_depth_in"], option_axis_count=1, ) two_axis = preview_variation_plan( object(), skus=[p.sku for p in products], group_attrs=["variation_group_code"], option_attrs=["variation_width_in", "variation_height_in", "variation_depth_in"], option_axis_count=2, ) assert one_axis["group_count"] == 1 assert one_axis["groups"][0]["sku_family"] == "B" assert one_axis["groups"][0]["dimension_axis_count"] == 1 assert two_axis["group_count"] == 1 assert two_axis["groups"][0]["sku_family"] == "WH" assert two_axis["groups"][0]["dimension_axis_count"] == 2 assert {row["reason"] for row in one_axis["skipped"]} == {"option_axis_count_mismatch"} def test_preview_variation_plan_excludes_already_linked_skus(monkeypatch): products = [ _product("ACH-B12", "Base 12"), _product("ACH-B15", "Base 15"), _product("ACH-B-PARENT", "Base Parent"), ] import db.variation_builder as mod monkeypatch.setattr(mod, "_products", lambda _session, _skus: products) monkeypatch.setattr(mod, "_relation_linked_skus", lambda _session, _skus: {"ACH-B12", "ACH-B15", "ACH-B-PARENT"}) monkeypatch.setattr( mod, "_attrs_by_sku", lambda _session, _ids: { "ACH-B12": { "variation_group_code": "B", "variation_width_in": "12", "variation_height_in": "34.5", "variation_depth_in": "24", }, "ACH-B15": { "variation_group_code": "B", "variation_width_in": "15", "variation_height_in": "34.5", "variation_depth_in": "24", }, "ACH-B-PARENT": {"variation_group_code": "B"}, }, ) excluded = preview_variation_plan( object(), skus=[p.sku for p in products], group_attrs=["variation_group_code"], option_attrs=["variation_width_in", "variation_height_in", "variation_depth_in"], exclude_linked_skus=True, ) included = preview_variation_plan( object(), skus=[p.sku for p in products], group_attrs=["variation_group_code"], option_attrs=["variation_width_in", "variation_height_in", "variation_depth_in"], exclude_linked_skus=False, ) assert excluded["group_count"] == 0 assert excluded["excluded_linked_skus"] == 3 assert {row["reason"] for row in excluded["skipped"]} == {"already_linked_relation"} assert included["group_count"] == 1 assert included["excluded_linked_skus"] == 0 def test_copy_collection_shareable_attrs_to_parents_fills_missing(catalog_intent_session): from db.models import MasterProductAttributeValue from db.variation_builder import _copy_collection_shareable_attrs_to_parents child_a = MasterProduct( sku="ASW-B12", name="Base 12", collection="Anna Snow White", status="active", row_hash="asw-b12", is_active=True, ) child_b = MasterProduct( sku="ASW-B15", name="Base 15", collection="Anna Snow White", status="active", row_hash="asw-b15", is_active=True, ) parent = MasterProduct( sku="ASW-B-PARENT", name="Anna Snow White Base", collection="Anna Snow White", status="active", row_hash="asw-b-parent", is_active=True, ) catalog_intent_session.add_all([child_a, child_b, parent]) catalog_intent_session.flush() catalog_intent_session.add_all( [ MasterProductAttributeValue( product_id=child_a.id, sku=child_a.sku, attribute_code="cabinet_door_style", value="shaker", ), MasterProductAttributeValue( product_id=child_b.id, sku=child_b.sku, attribute_code="door_style", value="Shaker", ), MasterProductAttributeValue( product_id=child_a.id, sku=child_a.sku, attribute_code="cabinet_construction_type", value="Framed Construction", ), MasterProductAttributeValue( product_id=child_b.id, sku=child_b.sku, attribute_code="cabinet_construction_type", value="Framed Construction", ), MasterProductAttributeValue( product_id=child_a.id, sku=child_a.sku, attribute_code="door_hinge_type", value="Concealed 6-Way Adjustable Soft Close", ), MasterProductAttributeValue( product_id=child_b.id, sku=child_b.sku, attribute_code="door_hinge_type", value="Concealed 6-Way Adjustable Soft Close", ), MasterProductAttributeValue( product_id=child_a.id, sku=child_a.sku, attribute_code="variation_width_in", value="12", ), MasterProductAttributeValue( product_id=child_b.id, sku=child_b.sku, attribute_code="variation_width_in", value="15", ), ] ) catalog_intent_session.commit() copied = _copy_collection_shareable_attrs_to_parents( catalog_intent_session, [ { "sku": parent.sku, "raw_payload": { "children": [child_a.sku, child_b.sku], "option_attrs": ["variation_width_in"], }, } ], ) catalog_intent_session.commit() assert copied >= 3 by_code = { row.attribute_code: row.value for row in catalog_intent_session.query(MasterProductAttributeValue) .filter_by(product_id=parent.id) .all() } assert by_code.get("door_style") == "Shaker" assert by_code.get("cabinet_construction") == "Framed" assert by_code.get("door_hinge_type") == "Concealed 6-Way Adjustable Soft Close" assert "variation_width_in" not in by_code def test_copy_shared_child_attrs_from_relations_dry_run(catalog_intent_session): from db.models import MasterProductAttributeValue, MasterProductRelation from db.variation_builder import copy_shared_child_attrs_to_parents_from_relations child = MasterProduct( sku="ACH-B12", name="B12", collection="Anna Caramel Harvest", status="active", row_hash="ach-b12", is_active=True, ) child2 = MasterProduct( sku="ACH-B15", name="B15", collection="Anna Caramel Harvest", status="active", row_hash="ach-b15", is_active=True, ) parent = MasterProduct( sku="ACH-B-PARENT", name="ACH Base Parent", collection="Anna Caramel Harvest", status="active", row_hash="ach-b-parent", is_active=True, ) catalog_intent_session.add_all([child, child2, parent]) catalog_intent_session.flush() for sku_row, code, value in ( (child, "cabinet_door_wood_species", "Birch"), (child2, "cabinet_door_wood_species", "Birch"), (child, "drawer_box_construction", "Dovetail"), (child2, "drawer_box_construction", "Dovetail"), ): catalog_intent_session.add( MasterProductAttributeValue( product_id=sku_row.id, sku=sku_row.sku, attribute_code=code, value=value, ) ) catalog_intent_session.add_all( [ MasterProductRelation( parent_sku=parent.sku, child_sku=child.sku, option_mapping={"width": "12"}, sort_order=0, source_label="test", ), MasterProductRelation( parent_sku=parent.sku, child_sku=child2.sku, option_mapping={"width": "15"}, sort_order=1, source_label="test", ), ] ) catalog_intent_session.commit() preview = copy_shared_child_attrs_to_parents_from_relations( catalog_intent_session, parent_skus=[parent.sku], dry_run=True, ) assert preview["would_copy"] >= 2 codes = {row["attribute"] for row in preview["sample"]} assert "cabinet_door_wood_species" in codes assert "drawer_box_construction" in codes assert "width" not in codes applied = copy_shared_child_attrs_to_parents_from_relations( catalog_intent_session, parent_skus=[parent.sku], dry_run=False, ) catalog_intent_session.commit() assert applied["copied"] >= 2 by_code = { row.attribute_code: row.value for row in catalog_intent_session.query(MasterProductAttributeValue) .filter_by(product_id=parent.id) .all() } assert by_code["cabinet_door_wood_species"] == "Birch" assert by_code["drawer_box_construction"] == "Dovetail"