from sqlalchemy import select

from db.channel_listing_path import upsert_listing_path, upsert_product_listing_path_assignment
from db.master_taxonomy_sync import upsert_taxonomy_node
from db.models import MasterProduct, ProductListingPathAssignment
from db.stale_listing_path_cleanup import prune_stale_listing_paths


def test_prune_stale_listing_paths_deactivates_wrong_rta_collection_assignment(catalog_intent_session):
    session = catalog_intent_session
    hub = 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": hub["id"],
            "node_kind": "collection",
        },
    )
    upsert_taxonomy_node(
        session,
        {
            "name": "Merrill Slate",
            "path_slug": "kitchen-cabinets/merrill-slate",
            "parent_id": hub["id"],
            "node_kind": "collection",
        },
    )
    session.add(
        MasterProduct(
            sku="RTA-ASW-BF3",
            name="Base Filler",
            category_l1="Kitchen Cabinets",
            collection="Anna Snow White",
            row_hash="rta-stale-1",
            is_active=True,
        )
    )
    session.flush()

    stale_path = upsert_listing_path(
        session,
        path_slug="kitchen-cabinets/merrill-slate",
        title="Merrill Slate",
        path_kind="collection",
        hub_path_slug="kitchen-cabinets",
    )
    good_path = upsert_listing_path(
        session,
        path_slug="kitchen-cabinets/anna-snow-white",
        title="Anna Snow White",
        path_kind="collection",
        hub_path_slug="kitchen-cabinets",
    )
    upsert_product_listing_path_assignment(
        session,
        master_sku="RTA-ASW-BF3",
        listing_path_id=stale_path["id"],
        match_source="stale",
    )
    upsert_product_listing_path_assignment(
        session,
        master_sku="RTA-ASW-BF3",
        listing_path_id=good_path["id"],
        match_source="canonical",
    )
    session.commit()

    preview = prune_stale_listing_paths(session, skus=["RTA-ASW-BF3"], dry_run=True)
    assert preview["stale_sku_count"] == 1
    assert preview["stale_assignment_count"] == 1
    assert preview["samples"][0]["stale_paths"] == ["kitchen-cabinets/merrill-slate"]

    applied = prune_stale_listing_paths(session, skus=["RTA-ASW-BF3"], dry_run=False)
    assert applied["deactivated_assignment_count"] == 1

    rows = session.query(MasterProduct).filter(MasterProduct.sku == "RTA-ASW-BF3").all()
    assert rows
    statuses = [
        row.assignment_status
        for row in session.execute(
            select(ProductListingPathAssignment.assignment_status).where(
                ProductListingPathAssignment.master_sku == "RTA-ASW-BF3"
            )
        ).all()
    ]
    assert sorted(statuses) == ["active", "inactive"]


def test_prune_stale_listing_paths_rta_only_scope_filters_non_rta(catalog_intent_session):
    session = catalog_intent_session
    session.add_all(
        [
            MasterProduct(
                sku="RTA-ASW-BF3",
                name="Base Filler",
                category_l1="Kitchen Cabinets",
                row_hash="rta-stale-2",
                is_active=True,
            ),
            MasterProduct(
                sku="ASW-BF3",
                name="Base Filler",
                category_l1="Kitchen Cabinets",
                row_hash="rta-stale-3",
                is_active=True,
            ),
        ]
    )
    session.flush()

    result = prune_stale_listing_paths(session, rta_only=True, all_products=False, dry_run=True)
    assert result["scope"]["target_skus"] == ["RTA-ASW-BF3"]