from sqlalchemy import select

from db.collection_landing_pages import list_collection_landing_pages
from db.collection_sku_mapping import (
    deactivate_sku_from_other_collection_listing_paths,
    resolve_collection_skus,
)
from db.master_taxonomy_sync import upsert_taxonomy_node
from db.models import (
    ChannelListingPath,
    MasterProduct,
    MasterProductCollectionMembership,
    ProductListingPathAssignment,
)
from db.channel_listing_path import upsert_listing_path, _upsert_listing_assignment


def _add_product(session, *, sku: str, collection: str) -> None:
    session.add(
        MasterProduct(
            sku=sku,
            name=sku,
            category_l1="Kitchen Cabinets",
            collection=collection,
            membership_mode="native",
            row_hash=f"h-{sku}",
            is_active=True,
        )
    )


def test_resolve_collection_skus_excludes_wrong_style_code(catalog_intent_session):
    session = catalog_intent_session
    parent = upsert_taxonomy_node(
        session,
        {"name": "Kitchen Cabinets", "path_slug": "kitchen-cabinets", "node_kind": "hub"},
    )["node"]
    upsert_taxonomy_node(
        session,
        {
            "name": "Anna Snow White",
            "path_slug": "kitchen-cabinets/anna-snow-white",
            "parent_id": parent["id"],
            "node_kind": "collection",
        },
    )
    _add_product(session, sku="ASW-B15", collection="Anna Snow White")
    _add_product(session, sku="GSW-B15", collection="Grace Snow White")
    _add_product(session, sku="ESW-B15", collection="Eleanor Snow White")
    upsert_listing_path(session, path_slug="kitchen-cabinets/anna-snow-white", title="Anna Snow White", path_kind="collection")
    anna_path = session.scalar(
        select(ChannelListingPath).where(ChannelListingPath.path_slug == "kitchen-cabinets/anna-snow-white")
    )
    _upsert_listing_assignment(session, master_sku="GSW-B15", listing_path_id=anna_path.id, match_source="stale")
    _upsert_listing_assignment(session, master_sku="ESW-B15", listing_path_id=anna_path.id, match_source="stale")
    session.flush()

    resolved = resolve_collection_skus(
        session,
        path_slug="kitchen-cabinets/anna-snow-white",
        collection="Anna Snow White",
    )
    skus = {item["master_sku"] for item in resolved["skus"]}
    assert "ASW-B15" in skus
    assert "GSW-B15" not in skus
    assert "ESW-B15" not in skus


def test_inferred_collections_require_taxonomy_node(catalog_intent_session):
    session = catalog_intent_session
    parent = upsert_taxonomy_node(
        session,
        {"name": "Kitchen Cabinets", "path_slug": "kitchen-cabinets", "node_kind": "hub"},
    )["node"]
    upsert_taxonomy_node(
        session,
        {
            "name": "Anna Snow White",
            "path_slug": "kitchen-cabinets/anna-snow-white",
            "parent_id": parent["id"],
            "node_kind": "collection",
        },
    )
    _add_product(session, sku="MIST-1", collection="Mist")
    _add_product(session, sku="FROST-1", collection="Frost")
    _add_product(session, sku="ASW-B15", collection="Anna Snow White")
    session.flush()

    rows = list_collection_landing_pages(session, category_l1="Kitchen Cabinets")
    slugs = {row["path_slug"] for row in rows}
    assert "kitchen-cabinets/anna-snow-white" in slugs
    assert "kitchen-cabinets/mist" not in slugs
    assert "kitchen-cabinets/frost" not in slugs


def test_deactivate_sku_from_other_collection_paths(catalog_intent_session):
    session = catalog_intent_session
    upsert_listing_path(session, path_slug="kitchen-cabinets/anna-snow-white", title="Anna Snow White", path_kind="collection")
    upsert_listing_path(session, path_slug="kitchen-cabinets/grace-snow-white", title="Grace Snow White", path_kind="collection")
    anna = session.scalar(select(ChannelListingPath).where(ChannelListingPath.path_slug == "kitchen-cabinets/anna-snow-white"))
    grace = session.scalar(select(ChannelListingPath).where(ChannelListingPath.path_slug == "kitchen-cabinets/grace-snow-white"))
    _upsert_listing_assignment(session, master_sku="ASW-B15", listing_path_id=anna.id, match_source="test")
    _upsert_listing_assignment(session, master_sku="ASW-B15", listing_path_id=grace.id, match_source="test")
    session.flush()

    removed = deactivate_sku_from_other_collection_listing_paths(
        session,
        master_sku="ASW-B15",
        keep_path_slug="kitchen-cabinets/anna-snow-white",
    )
    assert removed == 1
    grace_rows = session.scalars(
        select(ProductListingPathAssignment).where(
            ProductListingPathAssignment.listing_path_id == grace.id
        )
    ).all()
    assert all(row.assignment_status == "inactive" for row in grace_rows)


def test_resolve_collection_skus_includes_explicit_shared_membership(catalog_intent_session):
    session = catalog_intent_session
    parent = upsert_taxonomy_node(
        session,
        {"name": "Kitchen Cabinets", "path_slug": "kitchen-cabinets", "node_kind": "hub"},
    )["node"]
    upsert_taxonomy_node(
        session,
        {
            "name": "Anna Snow White",
            "path_slug": "kitchen-cabinets/anna-snow-white",
            "parent_id": parent["id"],
            "node_kind": "collection",
        },
    )
    upsert_taxonomy_node(
        session,
        {
            "name": "Harriet Pearl White",
            "path_slug": "kitchen-cabinets/harriet-pearl-white",
            "parent_id": parent["id"],
            "node_kind": "collection",
        },
    )
    upsert_taxonomy_node(
        session,
        {
            "name": "Anna Stone Gray",
            "path_slug": "kitchen-cabinets/anna-stone-gray",
            "parent_id": parent["id"],
            "node_kind": "collection",
        },
    )
    session.add(
        MasterProduct(
            sku="PB-PW-BF3",
            name="Pearl White Base Filler",
            category_l1="Kitchen Cabinets",
            collection="Harriet Pearl White",
            membership_mode="shared",
            row_hash="h-shared",
            is_active=True,
        )
    )
    session.add(
        MasterProductCollectionMembership(
            master_sku="PB-PW-BF3",
            path_slug="kitchen-cabinets/anna-snow-white",
            collection_name="Anna Snow White",
            source_label="test",
            is_active=True,
        )
    )
    session.flush()

    white = resolve_collection_skus(
        session,
        path_slug="kitchen-cabinets/anna-snow-white",
        collection="Anna Snow White",
    )
    gray = resolve_collection_skus(
        session,
        path_slug="kitchen-cabinets/anna-stone-gray",
        collection="Anna Stone Gray",
    )

    white_skus = {item["master_sku"] for item in white["skus"]}
    gray_skus = {item["master_sku"] for item in gray["skus"]}
    assert "PB-PW-BF3" in white_skus
    assert "PB-PW-BF3" not in gray_skus


def test_deactivate_sku_from_other_collection_paths_keeps_explicit_shared_memberships(catalog_intent_session):
    session = catalog_intent_session
    session.add(
        MasterProduct(
            sku="PB-PW-BF3",
            name="Pearl White Base Filler",
            category_l1="Kitchen Cabinets",
            collection="Harriet Pearl White",
            membership_mode="shared",
            row_hash="h-shared",
            is_active=True,
        )
    )
    session.add(
        MasterProductCollectionMembership(
            master_sku="PB-PW-BF3",
            path_slug="kitchen-cabinets/anna-snow-white",
            collection_name="Anna Snow White",
            source_label="test",
            is_active=True,
        )
    )
    session.add(
        MasterProductCollectionMembership(
            master_sku="PB-PW-BF3",
            path_slug="kitchen-cabinets/grace-snow-white",
            collection_name="Grace Snow White",
            source_label="test",
            is_active=True,
        )
    )
    upsert_listing_path(session, path_slug="kitchen-cabinets/anna-snow-white", title="Anna Snow White", path_kind="collection")
    upsert_listing_path(session, path_slug="kitchen-cabinets/grace-snow-white", title="Grace Snow White", path_kind="collection")
    upsert_listing_path(session, path_slug="kitchen-cabinets/anna-stone-gray", title="Anna Stone Gray", path_kind="collection")
    anna = session.scalar(select(ChannelListingPath).where(ChannelListingPath.path_slug == "kitchen-cabinets/anna-snow-white"))
    grace = session.scalar(select(ChannelListingPath).where(ChannelListingPath.path_slug == "kitchen-cabinets/grace-snow-white"))
    gray = session.scalar(select(ChannelListingPath).where(ChannelListingPath.path_slug == "kitchen-cabinets/anna-stone-gray"))
    _upsert_listing_assignment(session, master_sku="PB-PW-BF3", listing_path_id=anna.id, match_source="test")
    _upsert_listing_assignment(session, master_sku="PB-PW-BF3", listing_path_id=grace.id, match_source="test")
    _upsert_listing_assignment(session, master_sku="PB-PW-BF3", listing_path_id=gray.id, match_source="test")
    session.flush()

    removed = deactivate_sku_from_other_collection_listing_paths(
        session,
        master_sku="PB-PW-BF3",
        keep_path_slug="kitchen-cabinets/anna-snow-white",
    )

    assert removed == 1
    assignments = {
        row.listing_path_id: row.assignment_status
        for row in session.scalars(select(ProductListingPathAssignment)).all()
    }
    assert assignments[anna.id] == "active"
    assert assignments[grace.id] == "active"
    assert assignments[gray.id] == "inactive"


def test_native_products_ignore_explicit_shared_membership_rows(catalog_intent_session):
    session = catalog_intent_session
    parent = upsert_taxonomy_node(
        session,
        {"name": "Kitchen Cabinets", "path_slug": "kitchen-cabinets", "node_kind": "hub"},
    )["node"]
    upsert_taxonomy_node(
        session,
        {
            "name": "Anna Snow White",
            "path_slug": "kitchen-cabinets/anna-snow-white",
            "parent_id": parent["id"],
            "node_kind": "collection",
        },
    )
    session.add(
        MasterProduct(
            sku="PB-PW-BF3",
            name="Pearl White Base Filler",
            category_l1="Kitchen Cabinets",
            collection="Harriet Pearl White",
            membership_mode="native",
            row_hash="h-native",
            is_active=True,
        )
    )
    session.add(
        MasterProductCollectionMembership(
            master_sku="PB-PW-BF3",
            path_slug="kitchen-cabinets/anna-snow-white",
            collection_name="Anna Snow White",
            source_label="test",
            is_active=True,
        )
    )
    session.flush()

    resolved = resolve_collection_skus(
        session,
        path_slug="kitchen-cabinets/anna-snow-white",
        collection="Anna Snow White",
    )
    assert {item["master_sku"] for item in resolved["skus"]} == set()