from sqlalchemy import select

from db.master_taxonomy_hierarchy import (
    NODE_CATEGORY,
    NODE_COLLECTION,
    NODE_HUB,
    build_hierarchy_from_master_catalog,
    list_taxonomy_tree,
    sync_channel_links_from_registries,
)
from db.master_taxonomy_merge import register_taxonomy_path_alias
from db.models import (
    MagentoCategoryRegistry,
    MagentoConnection,
    MasterProduct,
    MasterTaxonomyChannelLink,
    MasterTaxonomyNode,
    ShopifyCollectionRegistry,
    ShopifyConnection,
)


def _seed_connections(session):
    session.add(
        MagentoConnection(
            id=1,
            store_base_url="https://magento.test",
            environment="test",
            consumer_key="ck",
            consumer_secret="cs",
            access_token="at",
            access_token_secret="ts",
        )
    )
    session.add(
        ShopifyConnection(
            id=10,
            shop_code="test",
            shop_domain="test.myshopify.com",
            status="active",
        )
    )


def test_build_hierarchy_creates_parent_child_nodes(catalog_intent_session):
    session = catalog_intent_session
    session.add(
        MasterProduct(
            sku="ASG-B15",
            name="Base",
            brand="Port & Bell",
            product_family="Cabinets",
            category_l1="Kitchen Cabinets",
            collection="Anna Snow White",
            row_hash="h1",
            is_active=True,
        )
    )
    session.flush()

    stats = build_hierarchy_from_master_catalog(session, dry_run=False, allow_invent=True)
    session.flush()

    assert stats["node_count"] >= 2
    tree = list_taxonomy_tree(session)
    hub = next(n for n in tree["nodes"] if n["node_kind"] == NODE_HUB)
    assert hub["parent_id"] == 0
    assert hub["name"] == "Kitchen Cabinets"
    collections = [n for n in tree["nodes"] if n["node_kind"] == NODE_COLLECTION]
    assert any("Anna Snow White" in n["name"] for n in collections)


def test_build_hierarchy_honors_canonical_path_alias_and_does_not_recreate_old_slug(catalog_intent_session):
    session = catalog_intent_session
    hub = MasterTaxonomyNode(
        node_kind=NODE_HUB,
        code="kitchen-cabinets",
        name="Kitchen Cabinets",
        path_slug="kitchen-cabinets",
        is_active=True,
    )
    renamed = MasterTaxonomyNode(
        node_kind=NODE_CATEGORY,
        code="moldings",
        name="Kitchen Cabinets / Moldings",
        path_slug="kitchen-cabinets/moldings",
        parent_id=None,
        is_active=True,
    )
    session.add_all([hub, renamed])
    session.flush()
    renamed.parent_id = hub.id
    session.flush()

    register_taxonomy_path_alias(
        session,
        alias_path_slug="kitchen-cabinets/mouldings",
        canonical_node_id=renamed.id,
    )

    session.add(
        MasterProduct(
            sku="TK8",
            name="Toe Kick Molding",
            category_l1="Kitchen Cabinets",
            collection="",
            row_hash="moldings-lock",
            is_active=True,
        )
    )
    session.flush()

    stats = build_hierarchy_from_master_catalog(session, dry_run=False, allow_invent=True)
    session.flush()

    canonical = session.scalar(
        select(MasterTaxonomyNode).where(MasterTaxonomyNode.path_slug == "kitchen-cabinets/moldings").limit(1)
    )
    legacy = session.scalar(
        select(MasterTaxonomyNode).where(MasterTaxonomyNode.path_slug == "kitchen-cabinets/mouldings").limit(1)
    )

    assert stats["node_count"] >= 1
    assert canonical is not None
    assert canonical.is_active is True
    assert legacy is None


def test_sync_channel_links_matches_pulled_registries(catalog_intent_session):
    session = catalog_intent_session
    _seed_connections(session)
    session.flush()
    session.add(
        MasterProduct(
            sku="ASG-B15",
            name="Base",
            category_l1="Kitchen Cabinets",
            collection="Anna Snow White",
            row_hash="h1",
            is_active=True,
        )
    )
    session.add(
        MagentoCategoryRegistry(
            connection_id=1,
            category_id=100,
            parent_id=2,
            name="Kitchen Cabinets",
            path="/1/2/100",
            path_names="Default Category/Kitchen Cabinets",
            level=2,
            is_active=True,
            fetched_at=__import__("datetime").datetime.now(__import__("datetime").timezone.utc),
        )
    )
    session.add(
        MagentoCategoryRegistry(
            connection_id=1,
            category_id=200,
            parent_id=100,
            name="Anna Snow White",
            path="/1/2/100/200",
            path_names="Default Category/Kitchen Cabinets/Anna Snow White",
            level=3,
            is_active=True,
            fetched_at=__import__("datetime").datetime.now(__import__("datetime").timezone.utc),
        )
    )
    session.add(
        ShopifyCollectionRegistry(
            connection_id=10,
            shop_code="test",
            collection_id="gid://shopify/Collection/1",
            handle="anna-snow-white",
            title="Anna Snow White",
            collection_type="manual",
            fetched_at=__import__("datetime").datetime.now(__import__("datetime").timezone.utc),
        )
    )
    session.flush()

    build_hierarchy_from_master_catalog(session, dry_run=False, allow_invent=True)
    stats = sync_channel_links_from_registries(
        session,
        magento_connection_id=1,
        shopify_connection_id=10,
        write_listing_path_targets=False,
    )
    session.flush()

    assert stats["magento_linked"] >= 1
    assert stats["shopify_linked"] >= 1
    tree = list_taxonomy_tree(session)
    collection = next(n for n in tree["collections"] if "Anna Snow White" in n["name"])
    assert collection["channels"]["magento"]["remote_id"] == "200"
    assert collection["channels"]["shopify"]["remote_id"] == "gid://shopify/Collection/1"
    assert collection["channels"]["shopify"]["remote_parent_id"] == "0"


def test_list_taxonomy_tree_filters_channel_links_by_selected_connection(catalog_intent_session):
    session = catalog_intent_session
    node = MasterTaxonomyNode(
        node_kind=NODE_COLLECTION,
        code="moulding",
        name="Moulding",
        path_slug="kitchen-cabinets/moulding",
    )
    session.add(node)
    session.flush()
    session.add_all(
        [
            MasterTaxonomyChannelLink(
                taxonomy_node_id=node.id,
                channel_code="magento",
                connection_id=1,
                taxonomy_kind="category",
                remote_id="101",
                is_active=True,
            ),
            MasterTaxonomyChannelLink(
                taxonomy_node_id=node.id,
                channel_code="magento",
                connection_id=2,
                taxonomy_kind="category",
                remote_id="202",
                is_active=True,
            ),
        ]
    )
    session.flush()

    conn1_tree = list_taxonomy_tree(session, magento_connection_id=1)
    conn1_node = next(row for row in conn1_tree["nodes"] if row["id"] == node.id)
    conn2_tree = list_taxonomy_tree(session, magento_connection_id=2)
    conn2_node = next(row for row in conn2_tree["nodes"] if row["id"] == node.id)

    assert conn1_node["channels"]["magento"]["remote_id"] == "101"
    assert conn2_node["channels"]["magento"]["remote_id"] == "202"


def test_list_taxonomy_tree_hides_stale_magento_remote_and_excludes_shopify(catalog_intent_session):
    from datetime import datetime, timezone

    from db.models import MagentoCategoryRegistry, MagentoConnection, ShopifyConnection

    session = catalog_intent_session
    for conn_id in (1, 4):
        if session.get(MagentoConnection, conn_id) is None:
            session.add(
                MagentoConnection(
                    id=conn_id,
                    store_base_url=f"https://magento{conn_id}.test",
                    environment="test",
                    consumer_key="ck",
                    consumer_secret="cs",
                    access_token="at",
                    access_token_secret="ts",
                    status="active",
                )
            )
    if session.get(ShopifyConnection, 1000001) is None:
        session.add(
            ShopifyConnection(
                id=1000001,
                shop_code="test",
                shop_domain="test.myshopify.com",
                status="active",
            )
        )
    session.flush()

    node = MasterTaxonomyNode(
        node_kind=NODE_CATEGORY,
        code="base-cabinets",
        name="Base Cabinets",
        path_slug="kitchen-cabinets/base-cabinets",
    )
    session.add(node)
    session.flush()
    session.add_all(
        [
            MasterTaxonomyChannelLink(
                taxonomy_node_id=node.id,
                channel_code="magento",
                connection_id=4,
                taxonomy_kind="category",
                remote_id="300",
                is_active=True,
            ),
            MasterTaxonomyChannelLink(
                taxonomy_node_id=node.id,
                channel_code="shopify",
                connection_id=1000001,
                taxonomy_kind="collection",
                remote_id="gid://shopify/Collection/999",
                is_active=True,
            ),
            MagentoCategoryRegistry(
                connection_id=4,
                category_id=36,
                parent_id=5,
                name="Base Cabinets",
                path="1/2/5/36",
                path_names="Default Category/Kitchen Cabinets/Base Cabinets",
                level=3,
                is_active=True,
                fetched_at=datetime.now(timezone.utc),
            ),
        ]
    )
    session.flush()

    # Magento-only selector must not leak Shopify GIDs.
    magento_only = list_taxonomy_tree(session, magento_connection_id=4)
    row = next(item for item in magento_only["nodes"] if item["id"] == node.id)
    assert "shopify" not in row["channels"]
    assert row["channels"]["magento"]["remote_id"] is None
    assert row["channels"]["magento"]["remote_missing"] is True
    assert row["channels"]["magento"]["linked"] is False
    assert row["channels"]["magento"]["stale_remote_id"] == "300"

    both = list_taxonomy_tree(
        session,
        magento_connection_id=4,
        shopify_connection_id=1000001,
    )
    both_row = next(item for item in both["nodes"] if item["id"] == node.id)
    assert both_row["channels"]["shopify"]["remote_id"] == "gid://shopify/Collection/999"
    assert both_row["channels"]["magento"]["remote_id"] is None


def test_reconcile_magento_channel_links_against_registry(catalog_intent_session):
    from datetime import datetime, timezone

    from db.master_taxonomy_hierarchy import reconcile_magento_channel_links_against_registry
    from db.models import MagentoCategoryRegistry, MagentoConnection

    session = catalog_intent_session
    if session.get(MagentoConnection, 4) is None:
        session.add(
            MagentoConnection(
                id=4,
                store_base_url="https://magento4.test",
                environment="test",
                consumer_key="ck",
                consumer_secret="cs",
                access_token="at",
                access_token_secret="ts",
                status="active",
            )
        )
        session.flush()

    node = MasterTaxonomyNode(
        node_kind=NODE_CATEGORY,
        code="angle-end",
        name="Angle End Base Cabinet",
        path_slug="kitchen-cabinets/base-cabinets/angle-end-base-cabinet",
    )
    session.add(node)
    session.flush()
    link = MasterTaxonomyChannelLink(
        taxonomy_node_id=node.id,
        channel_code="magento",
        connection_id=4,
        taxonomy_kind="category",
        remote_id="300",
        is_active=True,
    )
    session.add(link)
    session.add(
        MagentoCategoryRegistry(
            connection_id=4,
            category_id=36,
            parent_id=5,
            name="Base Cabinets",
            path="1/2/5/36",
            path_names="Default Category/Kitchen Cabinets/Base Cabinets",
            level=3,
            is_active=True,
            fetched_at=datetime.now(timezone.utc),
        )
    )
    session.flush()

    stats = reconcile_magento_channel_links_against_registry(session, 4)
    session.flush()
    assert stats["links_deactivated"] == 1


def test_magento_path_candidates_expand_tall_to_tall_cabinets():
    from db.master_taxonomy_hierarchy import _magento_path_candidates

    paths = _magento_path_candidates(
        "kitchen-cabinets/tall/pantry-cabinet",
        "Kitchen Cabinets / Tall / Pantry Cabinet",
    )
    assert paths[0] == "Kitchen Cabinets/Tall Cabinets/Pantry Cabinet"
    assert "Kitchen Cabinets/Tall/Pantry Cabinet" in paths


def test_magento_taxonomy_guidance_uses_tall_cabinets(catalog_intent_session):
    from db.master_taxonomy_sync import magento_taxonomy_channel_guidance
    from db.models import MasterTaxonomyNode

    session = catalog_intent_session
    hub = MasterTaxonomyNode(
        parent_id=None,
        node_kind="hub",
        code="kitchen-cabinets",
        name="Kitchen Cabinets",
        path_slug="kitchen-cabinets",
        is_active=True,
    )
    session.add(hub)
    session.flush()
    tall = MasterTaxonomyNode(
        parent_id=hub.id,
        node_kind="category",
        code="tall",
        name="Kitchen Cabinets / Tall",
        path_slug="kitchen-cabinets/tall",
        is_active=True,
    )
    session.add(tall)
    session.flush()
    pantry = MasterTaxonomyNode(
        parent_id=tall.id,
        node_kind="category",
        code="pantry-cabinet",
        name="Kitchen Cabinets / Tall / Pantry Cabinet",
        path_slug="kitchen-cabinets/tall/pantry-cabinet",
        is_active=True,
    )
    session.add(pantry)
    session.flush()

    guidance = magento_taxonomy_channel_guidance(session, pantry, connection_id=1)
    assert guidance["expected_magento_path"] == "Kitchen Cabinets/Tall Cabinets/Pantry Cabinet"
    assert guidance["parent_hub_name"] == "Kitchen Cabinets / Tall Cabinets"
    assert guidance["recommended_action"] == "link_parent_hub"


def test_list_taxonomy_tree_hides_active_children_of_inactive_parent(catalog_intent_session):
    session = catalog_intent_session
    hub = MasterTaxonomyNode(
        parent_id=None,
        node_kind=NODE_HUB,
        code="kitchen-cabinets",
        name="Kitchen Cabinets",
        path_slug="kitchen-cabinets",
        is_active=True,
    )
    session.add(hub)
    session.flush()
    tall = MasterTaxonomyNode(
        parent_id=hub.id,
        node_kind=NODE_CATEGORY,
        code="tall",
        name="Kitchen Cabinets / Tall",
        path_slug="kitchen-cabinets/tall",
        is_active=False,
    )
    session.add(tall)
    session.flush()
    pantry = MasterTaxonomyNode(
        parent_id=tall.id,
        node_kind=NODE_CATEGORY,
        code="pantry-cabinet",
        name="Kitchen Cabinets / Tall / Pantry Cabinet",
        path_slug="kitchen-cabinets/tall/pantry-cabinet",
        is_active=True,
    )
    session.add(pantry)
    session.flush()

    tree = list_taxonomy_tree(session)
    slugs = {row["path_slug"] for row in tree["nodes"]}

    assert "kitchen-cabinets" in slugs
    assert "kitchen-cabinets/tall" not in slugs
    assert "kitchen-cabinets/tall/pantry-cabinet" not in slugs