import pandas as pd

from db.channel_exports import build_channel_product_payloads
from db.master_catalog import import_master_sku_seo_dataframe
from db.master_static_field_mapping import bootstrap_static_field_rows
from db.models import MasterProduct, MasterProductAttributeValue, MasterProductRelation, MasterStaticFieldMapping


def test_product_seo_import_updates_title_slug_and_seo_attrs(catalog_intent_session):
    product = MasterProduct(
        sku="ACH-B12",
        name="Old Product Title",
        category_l1="Kitchen Cabinets",
        category_l2="Base Cabinets",
        collection="Anna Caramel Harvest",
        status="active",
        row_hash="old",
        is_active=True,
    )
    catalog_intent_session.add(product)
    catalog_intent_session.commit()

    df = pd.DataFrame(
        [
            {
                "SKU": "ACH-B12",
                "SEOfied Title": "Anna Caramel Harvest Single Door Base Cabinet",
                "Description": "SEO product description.",
                "Meta Keywords": "base cabinet, caramel cabinet",
                "Meta Description": "Shop Anna Caramel Harvest base cabinets.",
            }
        ]
    )

    result = import_master_sku_seo_dataframe(catalog_intent_session, df, source_label="seo_test")
    catalog_intent_session.commit()

    assert result["updated"] == 1
    assert product.name == "Anna Caramel Harvest Single Door Base Cabinet"
    assert product.category_l1 == "Kitchen Cabinets"
    assert product.category_l2 == "Base Cabinets"
    assert product.collection == "Anna Caramel Harvest"

    attrs = {
        row.attribute_code: row.value
        for row in catalog_intent_session.query(MasterProductAttributeValue).filter_by(product_id=product.id)
    }
    assert attrs["meta_title"] == "Anna Caramel Harvest Single Door Base Cabinet"
    assert attrs["description"] == "SEO product description."
    assert attrs["meta_keywords"] == "base cabinet, caramel cabinet"
    assert attrs["meta_description"] == "Shop Anna Caramel Harvest base cabinets."
    assert attrs["product_url_slug"] == "anna-caramel-harvest-single-door-base-cabinet-ach-b12"


def test_product_seo_import_flows_to_magento_and_shopify_payloads(catalog_intent_session):
    product = MasterProduct(
        sku="ACH-B12",
        name="Old Product Title",
        category_l1="Kitchen Cabinets",
        category_l2="Base Cabinets",
        collection="Anna Caramel Harvest",
        status="active",
        row_hash="old",
        is_active=True,
    )
    catalog_intent_session.add(product)
    catalog_intent_session.commit()
    bootstrap_static_field_rows(catalog_intent_session)
    _set_static_alias(catalog_intent_session, "title", magento="name", shopify="title")
    _set_static_alias(catalog_intent_session, "meta_title", magento="meta_title", shopify="seo_title")
    _set_static_alias(catalog_intent_session, "meta_description", magento="meta_description", shopify="seo_description")
    _set_static_alias(catalog_intent_session, "meta_keywords", magento="meta_keyword", shopify="meta_keywords")
    _set_static_alias(catalog_intent_session, "product_url_slug", magento="url_key", shopify="handle")
    import_master_sku_seo_dataframe(
        catalog_intent_session,
        pd.DataFrame(
            [
                {
                    "SKU": "ACH-B12",
                    "SEO Title": "Anna Caramel Harvest Single Door Base Cabinet",
                    "Meta Description": "Shop Anna Caramel Harvest base cabinets.",
                    "Meta Keywords": "base cabinet, caramel cabinet",
                }
            ]
        ),
    )

    magento_payload = build_channel_product_payloads(
        catalog_intent_session,
        "magento",
        skus=["ACH-B12"],
        only_assigned=False,
    )[0]
    shopify_payload = build_channel_product_payloads(
        catalog_intent_session,
        "shopify",
        skus=["ACH-B12"],
        only_assigned=False,
    )[0]

    assert magento_payload["fields"]["name"] == "Anna Caramel Harvest Single Door Base Cabinet"
    assert magento_payload["fields"]["url_key"] == "anna-caramel-harvest-single-door-base-cabinet-ach-b12"
    assert magento_payload["fields"]["meta_keyword"] == "base cabinet, caramel cabinet"
    assert shopify_payload["fields"]["title"] == "Anna Caramel Harvest Single Door Base Cabinet"
    assert shopify_payload["fields"]["handle"] == "anna-caramel-harvest-single-door-base-cabinet-ach-b12"
    assert shopify_payload["fields"]["seo_description"] == "Shop Anna Caramel Harvest base cabinets."


def test_product_seo_import_refreshes_variation_parent_title(catalog_intent_session):
    parent = MasterProduct(
        sku="ACH-B-PARENT",
        name="Anna Caramel Harvest",
        collection="Anna Caramel Harvest",
        status="active",
        row_hash="parent",
        raw_payload={"generated_by": "variation_builder", "group_values": {"collection": "Anna Caramel Harvest"}},
        is_active=True,
    )
    child_1 = MasterProduct(
        sku="ACH-B12",
        name="Old B12",
        collection="Anna Caramel Harvest",
        status="active",
        row_hash="child1",
        is_active=True,
    )
    child_2 = MasterProduct(
        sku="ACH-B15",
        name="Old B15",
        collection="Anna Caramel Harvest",
        status="active",
        row_hash="child2",
        is_active=True,
    )
    catalog_intent_session.add_all([parent, child_1, child_2])
    catalog_intent_session.flush()
    catalog_intent_session.add_all(
        [
            MasterProductAttributeValue(
                product_id=child_1.id,
                sku=child_1.sku,
                attribute_code="cabinet_type_sub_category_2",
                value="Single Door Base Cabinet",
            ),
            MasterProductAttributeValue(
                product_id=child_2.id,
                sku=child_2.sku,
                attribute_code="cabinet_type_sub_category_2",
                value="Single Door Base Cabinet",
            ),
            MasterProductRelation(
                parent_sku=parent.sku,
                child_sku=child_1.sku,
                option_mapping={"width": "12"},
                sort_order=0,
                source_label="variation_builder",
            ),
            MasterProductRelation(
                parent_sku=parent.sku,
                child_sku=child_2.sku,
                option_mapping={"width": "15"},
                sort_order=1,
                source_label="variation_builder",
            ),
        ]
    )
    catalog_intent_session.commit()

    result = import_master_sku_seo_dataframe(
        catalog_intent_session,
        pd.DataFrame(
            [
                {
                    "SKU": "ACH-B12",
                    "SEO Title": "12in Single Door Base Cabinet - Anna Caramel Harvest",
                    "SEO URL": "12in-single-door-base-cabinet-ach-b12",
                }
            ]
        ),
        source_label="seo_test",
    )

    assert result["variation_parent_titles"]["updated"] == 1
    assert result["variation_parent_titles"]["skipped_explicit_seo"] == 0
    assert parent.name == "Anna Caramel Harvest Single Door Base Cabinet"
    attrs = {
        row.attribute_code: row.value
        for row in catalog_intent_session.query(MasterProductAttributeValue).filter_by(product_id=parent.id)
    }
    assert attrs["meta_title"] == "Anna Caramel Harvest Single Door Base Cabinet"
    assert attrs["product_url_slug"] == "anna-caramel-harvest-single-door-base-cabinet-ach-b-parent"


def test_product_seo_import_refreshes_parent_title_from_builder_payload_without_relations(catalog_intent_session):
    parent = MasterProduct(
        sku="ACH-B-PARENT",
        name="Anna Caramel Harvest",
        collection="Anna Caramel Harvest",
        status="active",
        row_hash="parent",
        raw_payload={
            "generated_by": "variation_builder",
            "group_values": {"collection": "Anna Caramel Harvest"},
            "children": ["ACH-B12", "ACH-B15"],
        },
        is_active=True,
    )
    child_1 = MasterProduct(
        sku="ACH-B12",
        name="Old B12",
        collection="Anna Caramel Harvest",
        status="active",
        row_hash="child1",
        is_active=True,
    )
    child_2 = MasterProduct(
        sku="ACH-B15",
        name="Old B15",
        collection="Anna Caramel Harvest",
        status="active",
        row_hash="child2",
        is_active=True,
    )
    catalog_intent_session.add_all([parent, child_1, child_2])
    catalog_intent_session.flush()
    catalog_intent_session.add_all(
        [
            MasterProductAttributeValue(
                product_id=child_1.id,
                sku=child_1.sku,
                attribute_code="cabinet_type_sub_category_2",
                value="Single Door Base Cabinet",
            ),
            MasterProductAttributeValue(
                product_id=child_2.id,
                sku=child_2.sku,
                attribute_code="cabinet_type_sub_category_2",
                value="Single Door Base Cabinet",
            ),
        ]
    )
    catalog_intent_session.commit()

    result = import_master_sku_seo_dataframe(
        catalog_intent_session,
        pd.DataFrame(
            [
                {
                    "SKU": "ACH-B12",
                    "SEO Title": "12in Single Door Base Cabinet - Anna Caramel Harvest",
                    "SEO URL": "12in-single-door-base-cabinet-ach-b12",
                }
            ]
        ),
        source_label="seo_test",
    )

    assert result["variation_parent_titles"]["matched_parent_count"] == 1
    assert result["variation_parent_titles"]["relation_parent_count"] == 0
    assert result["variation_parent_titles"]["raw_payload_parent_count"] == 1
    assert result["variation_parent_titles"]["updated"] == 1
    assert parent.name == "Anna Caramel Harvest Single Door Base Cabinet"


def test_product_seo_import_keeps_explicit_parent_seo_over_derived_title(catalog_intent_session):
    parent = MasterProduct(
        sku="ACH-B-PARENT",
        name="Anna Caramel Harvest",
        collection="Anna Caramel Harvest",
        status="active",
        row_hash="parent",
        raw_payload={
            "generated_by": "variation_builder",
            "group_values": {"collection": "Anna Caramel Harvest"},
            "children": ["ACH-B12", "ACH-B15"],
            "option_attrs": ["variation_width_in"],
        },
        is_active=True,
    )
    child = MasterProduct(
        sku="ACH-B12",
        name="Old B12",
        collection="Anna Caramel Harvest",
        status="active",
        row_hash="child1",
        is_active=True,
    )
    catalog_intent_session.add_all([parent, child])
    catalog_intent_session.flush()
    catalog_intent_session.add(
        MasterProductAttributeValue(
            product_id=child.id,
            sku=child.sku,
            attribute_code="cabinet_type_sub_category_2",
            value="Single Door Base Cabinet",
        )
    )
    catalog_intent_session.commit()

    result = import_master_sku_seo_dataframe(
        catalog_intent_session,
        pd.DataFrame(
            [
                {
                    "SKU": "ACH-B-PARENT",
                    "SEO Title": "Anna Caramel Harvest Base Cabinets",
                    "Description": "Parent SEO description.",
                },
                {
                    "SKU": "ACH-B12",
                    "SEO Title": "12in Single Door Base Cabinet - Anna Caramel Harvest",
                },
            ]
        ),
        source_label="seo_test",
    )

    assert result["updated"] == 2
    assert result["variation_parent_titles"]["skipped_explicit_seo"] == 1
    assert result["variation_parent_titles"]["updated"] == 0
    assert parent.name == "Anna Caramel Harvest Base Cabinets"
    assert parent.raw_payload.get("explicit_seo_title") is True
    assert parent.raw_payload.get("description") == "Parent SEO description."


def test_product_seo_import_skips_locked_parent_on_later_child_import(catalog_intent_session):
    parent = MasterProduct(
        sku="ACH-B-PARENT",
        name="Anna Caramel Harvest Base Cabinets",
        collection="Anna Caramel Harvest",
        status="active",
        row_hash="parent",
        raw_payload={
            "generated_by": "variation_builder",
            "group_values": {"collection": "Anna Caramel Harvest"},
            "children": ["ACH-B12"],
            "explicit_seo_title": True,
            "meta_title": "Anna Caramel Harvest Base Cabinets",
        },
        is_active=True,
    )
    child = MasterProduct(
        sku="ACH-B12",
        name="Old B12",
        collection="Anna Caramel Harvest",
        status="active",
        row_hash="child1",
        is_active=True,
    )
    catalog_intent_session.add_all([parent, child])
    catalog_intent_session.flush()
    catalog_intent_session.add(
        MasterProductAttributeValue(
            product_id=child.id,
            sku=child.sku,
            attribute_code="cabinet_type_sub_category_2",
            value="Single Door Base Cabinet",
        )
    )
    catalog_intent_session.commit()

    result = import_master_sku_seo_dataframe(
        catalog_intent_session,
        pd.DataFrame(
            [
                {
                    "SKU": "ACH-B12",
                    "SEO Title": "12in Single Door Base Cabinet - Anna Caramel Harvest",
                }
            ]
        ),
        source_label="seo_test",
    )

    assert result["variation_parent_titles"]["skipped_explicit_seo"] == 1
    assert result["variation_parent_titles"]["updated"] == 0
    assert parent.name == "Anna Caramel Harvest Base Cabinets"


def _set_static_alias(catalog_intent_session, code: str, *, magento: str, shopify: str) -> None:
    row = catalog_intent_session.query(MasterStaticFieldMapping).filter_by(master_attribute_code=code).one()
    row.magento_attribute_code = magento
    row.shopify_attribute_code = shopify