from datetime import datetime, timezone

from db.channel_listing_path import upsert_listing_path, upsert_listing_path_target
from db.models import (
    MagentoCategoryRegistry,
    MagentoConnection,
    ProductChannelTaxonomyAssignment,
    ProductListingPathAssignment,
)
from db.repair_magento_listing_path_targets import repair_magento_listing_path_targets


def _seed_connection(session) -> int:
    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",
            status="active",
        )
    )
    session.flush()
    return 1


def test_repair_relinks_leaf_collision_and_prunes_stale_assignment(catalog_intent_session):
    session = catalog_intent_session
    _seed_connection(session)
    now = datetime.now(timezone.utc)
    session.add_all(
        [
            MagentoCategoryRegistry(
                connection_id=1,
                category_id=142,
                parent_id=130,
                name="Single Door",
                path="1/2/5/130/142",
                path_names="Default Category/Kitchen Cabinets/Wall Cabinets/Single Door",
                level=4,
                is_active=True,
                fetched_at=now,
            ),
            MagentoCategoryRegistry(
                connection_id=1,
                category_id=307,
                parent_id=36,
                name="Single Door Base Cabinet",
                path="1/2/5/36/307",
                path_names="Default Category/Kitchen Cabinets/Base Cabinets/Single Door Base Cabinet",
                level=4,
                is_active=True,
                fetched_at=now,
            ),
            MagentoCategoryRegistry(
                connection_id=1,
                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=now,
            ),
        ]
    )
    path = upsert_listing_path(
        session,
        path_slug="kitchen-cabinets/base-cabinets/single-door",
        title="Kitchen Cabinets / Base Cabinets / Single Door",
        path_kind="category",
    )
    upsert_listing_path_target(
        session,
        path_slug=path["path_slug"],
        channel_code="magento",
        remote_id="142",
        connection_id=1,
        remote_path="Default Category/Kitchen Cabinets/Wall Cabinets/Single Door",
    )
    session.add(
        ProductListingPathAssignment(
            master_sku="ACH-B12",
            listing_path_id=path["id"],
            assignment_status="active",
        )
    )
    session.add(
        ProductChannelTaxonomyAssignment(
            master_sku="ACH-B12",
            channel_code="magento",
            connection_id=1,
            taxonomy_kind="category",
            remote_id="999",
            remote_path="Default Category/Kitchen Cabinets/Frost",
            assignment_status="active",
            match_source="manual",
        )
    )
    session.commit()

    preview = repair_magento_listing_path_targets(
        session,
        connection_id=1,
        dry_run=True,
        prune_stale_assignments=True,
    )
    assert preview["mismatch_count"] == 1
    assert preview["relink_count"] == 1
    assert preview["sample_mismatches"][0]["suggested_remote_id"] == "307"
    assert preview["stale_assignments"]["would_deactivate"] == 1

    applied = repair_magento_listing_path_targets(
        session,
        connection_id=1,
        dry_run=False,
        prune_stale_assignments=True,
    )
    session.commit()
    assert applied["upserted_targets"] == 1
    assert applied["stale_assignments"]["deactivated"] == 1

    from db.models import ChannelListingPathTarget
    from sqlalchemy import select

    targets = list(
        session.scalars(
            select(ChannelListingPathTarget).where(ChannelListingPathTarget.listing_path_id == path["id"])
        ).all()
    )
    active = [t for t in targets if t.is_active]
    assert len(active) == 1
    assert active[0].remote_id == "307"

    stale = session.scalar(
        select(ProductChannelTaxonomyAssignment).where(ProductChannelTaxonomyAssignment.master_sku == "ACH-B12")
    )
    assert stale.assignment_status == "inactive"


def test_repair_skips_collection_facet_seo_paths(catalog_intent_session):
    session = catalog_intent_session
    _seed_connection(session)
    now = datetime.now(timezone.utc)
    session.add(
        MagentoCategoryRegistry(
            connection_id=1,
            category_id=296,
            parent_id=5,
            name="Anna Caramel Harvest",
            path="1/2/5/296",
            path_names="Default Category/Kitchen Cabinets/Anna Caramel Harvest",
            level=3,
            is_active=True,
            fetched_at=now,
        )
    )
    path = upsert_listing_path(
        session,
        path_slug="kitchen-cabinets/anna-caramel-harvest/base-cabinets",
        title="Anna Caramel Harvest / Base Cabinets",
        path_kind="intersection",
    )
    upsert_listing_path_target(
        session,
        path_slug=path["path_slug"],
        channel_code="magento",
        remote_id="296",
        taxonomy_kind="category_filter",
        connection_id=1,
        remote_path="Default Category/Kitchen Cabinets/Anna Caramel Harvest",
    )
    session.commit()

    preview = repair_magento_listing_path_targets(session, connection_id=1, dry_run=True)
    assert preview["skipped_intersection_count"] == 1
    assert preview["mismatch_count"] == 0
    assert preview["relink_count"] == 0


def test_repair_deactivates_wrong_hub_collection_facet_master(catalog_intent_session):
    session = catalog_intent_session
    _seed_connection(session)
    wrong = upsert_listing_path(
        session,
        path_slug="kitchen-cabinets/anna-caramel-harvest/panels-and-fillers",
        title="Wrong master hub/collection/facet",
        path_kind="category",
    )
    session.add(
        ProductListingPathAssignment(
            master_sku="ACH-B12",
            listing_path_id=wrong["id"],
            assignment_status="active",
        )
    )
    session.commit()

    preview = repair_magento_listing_path_targets(
        session,
        connection_id=1,
        dry_run=True,
        prune_stale_assignments=False,
    )
    assert preview["invalid_collection_facet_masters"]["would_deactivate_paths"] >= 1

    applied = repair_magento_listing_path_targets(
        session,
        connection_id=1,
        dry_run=False,
        prune_stale_assignments=False,
    )
    session.commit()
    assert applied["invalid_collection_facet_masters"]["deactivated_paths"] >= 1

    from db.models import ChannelListingPath
    from sqlalchemy import select

    path = session.scalar(
        select(ChannelListingPath).where(
            ChannelListingPath.path_slug == "kitchen-cabinets/anna-caramel-harvest/panels-and-fillers"
        )
    )
    assert path is not None
    assert path.is_active is False


def test_plp_slug_to_magento_path_names_for_mouldings():
    from db.repair_magento_listing_path_targets import _plp_slug_to_magento_path_names

    assert (
        _plp_slug_to_magento_path_names("kitchen-cabinets/mouldings/bead-molding")
        == "Kitchen Cabinets/Mouldings/Bead Molding"
    )
    assert (
        _plp_slug_to_magento_path_names("kitchen-cabinets/mouldings/additional-moldings")
        == "Kitchen Cabinets/Mouldings/Additional Moldings"
    )
    assert (
        _plp_slug_to_magento_path_names("kitchen-cabinets/tall/oven-cabinet")
        == "Kitchen Cabinets/Tall Cabinets/Oven Cabinet"
    )


def test_repair_keeps_existing_tall_cabinets_match(catalog_intent_session):
    session = catalog_intent_session
    _seed_connection(session)
    now = datetime.now(timezone.utc)
    session.add_all(
        [
            MagentoCategoryRegistry(
                connection_id=1,
                category_id=421,
                parent_id=99,
                name="Oven Cabinet",
                path="1/2/5/99/421",
                path_names="Default Category/Kitchen Cabinets/Tall Cabinets/Oven Cabinet",
                level=4,
                is_active=True,
                fetched_at=now,
            ),
            MagentoCategoryRegistry(
                connection_id=1,
                category_id=247,
                parent_id=98,
                name="Oven Cabinet",
                path="1/2/5/98/247",
                path_names="Default Category/Kitchen Cabinets/Tall/Oven Cabinet",
                level=4,
                is_active=True,
                fetched_at=now,
            ),
        ]
    )
    path = upsert_listing_path(
        session,
        path_slug="kitchen-cabinets/tall/oven-cabinet",
        title="Kitchen Cabinets / Tall / Oven Cabinet",
        path_kind="category",
    )
    upsert_listing_path_target(
        session,
        path_slug=path["path_slug"],
        channel_code="magento",
        remote_id="421",
        connection_id=1,
        remote_path="Default Category/Kitchen Cabinets/Tall Cabinets/Oven Cabinet",
    )
    session.commit()

    preview = repair_magento_listing_path_targets(
        session,
        connection_id=1,
        dry_run=True,
        prune_stale_assignments=False,
        deactivate_invalid_collection_facet_masters=False,
    )

    assert preview["mismatch_count"] == 0
    assert preview["relink_count"] == 0


def test_suggest_does_not_cross_hub_for_panels_and_fillers(catalog_intent_session):
    """kitchen-cabinets/panels-and-fillers must not relink to Bathroom Vanities/..."""
    session = catalog_intent_session
    _seed_connection(session)
    now = datetime.now(timezone.utc)
    session.add_all(
        [
            MagentoCategoryRegistry(
                connection_id=1,
                category_id=373,
                parent_id=10,
                name="Panels And Fillers",
                path="1/2/10/373",
                path_names="Default Category/Bathroom Vanities/Panels And Fillers",
                level=3,
                is_active=True,
                fetched_at=now,
            ),
            MagentoCategoryRegistry(
                connection_id=1,
                category_id=370,
                parent_id=5,
                name="Panels And Fillers",
                path="1/2/5/370",
                path_names="Default Category/Kitchen Cabinets/Panels And Fillers",
                level=3,
                is_active=True,
                fetched_at=now,
            ),
            MagentoCategoryRegistry(
                connection_id=1,
                category_id=363,
                parent_id=5,
                name="Frost",
                path="1/2/5/363",
                path_names="Default Category/Kitchen Cabinets/Frost",
                level=3,
                is_active=True,
                fetched_at=now,
            ),
        ]
    )
    path = upsert_listing_path(
        session,
        path_slug="kitchen-cabinets/panels-and-fillers",
        title="Panels And Fillers",
        path_kind="category",
    )
    upsert_listing_path_target(
        session,
        path_slug=path["path_slug"],
        channel_code="magento",
        remote_id="363",
        connection_id=1,
        remote_path="Default Category/Kitchen Cabinets/Frost",
    )
    session.commit()

    preview = repair_magento_listing_path_targets(
        session,
        connection_id=1,
        dry_run=True,
        prune_stale_assignments=False,
        deactivate_invalid_collection_facet_masters=False,
    )
    assert preview["relink_count"] == 1
    assert preview["sample_mismatches"][0]["suggested_remote_id"] == "370"
    assert "Bathroom" not in (preview["sample_mismatches"][0]["suggested_remote_path"] or "")


def test_suggest_rejects_other_hub_when_kitchen_missing(catalog_intent_session):
    session = catalog_intent_session
    _seed_connection(session)
    now = datetime.now(timezone.utc)
    session.add(
        MagentoCategoryRegistry(
            connection_id=1,
            category_id=373,
            parent_id=10,
            name="Panels And Fillers",
            path="1/2/10/373",
            path_names="Default Category/Bathroom Vanities/Panels And Fillers",
            level=3,
            is_active=True,
            fetched_at=now,
        )
    )
    path = upsert_listing_path(
        session,
        path_slug="kitchen-cabinets/panels-and-fillers",
        title="Panels And Fillers",
        path_kind="category",
    )
    upsert_listing_path_target(
        session,
        path_slug=path["path_slug"],
        channel_code="magento",
        remote_id="363",
        connection_id=1,
        remote_path="Default Category/Kitchen Cabinets/Frost",
    )
    session.commit()

    preview = repair_magento_listing_path_targets(
        session,
        connection_id=1,
        dry_run=True,
        provision_unmatched=True,
        prune_stale_assignments=False,
        deactivate_invalid_collection_facet_masters=False,
    )
    assert preview["relink_count"] == 0
    assert preview["unmatched_count"] == 1
    assert preview["provision_unmatched"]["would_provision"] == 1
    assert (
        preview["provision_unmatched"]["samples"][0]["planned_magento_path"]
        == "Kitchen Cabinets/Panels And Fillers"
    )


def test_provision_unmatched_dry_run_plans_correct_tree(catalog_intent_session):
    session = catalog_intent_session
    _seed_connection(session)
    now = datetime.now(timezone.utc)
    session.add(
        MagentoCategoryRegistry(
            connection_id=1,
            category_id=223,
            parent_id=221,
            name="Bead Molding",
            path="1/2/5/221/223",
            path_names="Default Category/Kitchen Cabinets/Accessories/Bead Molding",
            level=4,
            is_active=True,
            fetched_at=now,
        )
    )
    path = upsert_listing_path(
        session,
        path_slug="kitchen-cabinets/mouldings/bead-molding",
        title="Bead Molding",
        path_kind="category",
    )
    upsert_listing_path_target(
        session,
        path_slug=path["path_slug"],
        channel_code="magento",
        remote_id="223",
        connection_id=1,
        remote_path="Default Category/Kitchen Cabinets/Accessories/Bead Molding",
    )
    session.commit()

    preview = repair_magento_listing_path_targets(
        session,
        connection_id=1,
        dry_run=True,
        provision_unmatched=True,
        prune_stale_assignments=False,
        deactivate_invalid_collection_facet_masters=False,
    )
    assert preview["unmatched_count"] == 1
    assert preview["provision_unmatched"]["would_provision"] == 1
    sample = preview["provision_unmatched"]["samples"][0]
    assert sample["planned_magento_path"] == "Kitchen Cabinets/Mouldings/Bead Molding"
    assert "Accessories" not in sample["planned_magento_path"]


def test_doors_flat_magento_categories_are_not_unmatched(catalog_intent_session):
    """doors/exterior-doors ↔ Exterior Doors (no Doors/ hub folder on Magento)."""
    session = catalog_intent_session
    _seed_connection(session)
    now = datetime.now(timezone.utc)
    session.add_all(
        [
            MagentoCategoryRegistry(
                connection_id=1,
                category_id=294,
                parent_id=2,
                name="Exterior Doors",
                path="1/2/294",
                path_names="Exterior Doors",
                level=2,
                is_active=True,
                fetched_at=now,
            ),
            MagentoCategoryRegistry(
                connection_id=1,
                category_id=293,
                parent_id=2,
                name="Interior Doors",
                path="1/2/293",
                path_names="Interior Doors/Interior Doors",
                level=3,
                is_active=True,
                fetched_at=now,
            ),
        ]
    )
    for slug, remote_id, remote_path in (
        ("doors/exterior-doors", "294", "Exterior Doors"),
        ("doors/interior-doors", "293", "Interior Doors/Interior Doors"),
    ):
        path = upsert_listing_path(session, path_slug=slug, title=slug, path_kind="category")
        upsert_listing_path_target(
            session,
            path_slug=path["path_slug"],
            channel_code="magento",
            remote_id=remote_id,
            connection_id=1,
            remote_path=remote_path,
        )
    session.commit()

    preview = repair_magento_listing_path_targets(
        session,
        connection_id=1,
        dry_run=True,
        provision_unmatched=True,
        prune_stale_assignments=False,
        deactivate_invalid_collection_facet_masters=False,
    )
    assert preview["mismatch_count"] == 0
    assert preview["unmatched_count"] == 0


def test_stale_accessories_toe_kick_keeps_mouldings_target(catalog_intent_session):
    """Stale accessories/toe-kick must not relink away from Magento Mouldings/Toe Kick."""
    session = catalog_intent_session
    _seed_connection(session)
    now = datetime.now(timezone.utc)
    session.add_all(
        [
            MagentoCategoryRegistry(
                connection_id=1,
                category_id=470,
                parent_id=99,
                name="Toe Kick",
                path="1/2/5/99/470",
                path_names="Kitchen Cabinets/Mouldings/Toe Kick",
                level=4,
                is_active=True,
                fetched_at=now,
            ),
            MagentoCategoryRegistry(
                connection_id=1,
                category_id=251,
                parent_id=98,
                name="Toe Kick",
                path="1/2/5/98/251",
                path_names="Default Category/Kitchen Cabinets/Accessories/Toe Kick",
                level=4,
                is_active=True,
                fetched_at=now,
            ),
        ]
    )
    path = upsert_listing_path(
        session,
        path_slug="kitchen-cabinets/accessories/toe-kick",
        title="Accessories / Toe Kick",
        path_kind="category",
    )
    upsert_listing_path_target(
        session,
        path_slug=path["path_slug"],
        channel_code="magento",
        remote_id="470",
        connection_id=1,
        remote_path="Kitchen Cabinets/Mouldings/Toe Kick",
    )
    session.commit()

    preview = repair_magento_listing_path_targets(
        session,
        connection_id=1,
        dry_run=True,
        provision_unmatched=False,
        prune_stale_assignments=False,
        deactivate_invalid_collection_facet_masters=False,
    )
    assert preview["mismatch_count"] == 0
    assert preview["relink_count"] == 0


def test_suggest_prefers_mouldings_over_accessories_for_toe_kick(catalog_intent_session):
    session = catalog_intent_session
    _seed_connection(session)
    now = datetime.now(timezone.utc)
    session.add_all(
        [
            MagentoCategoryRegistry(
                connection_id=1,
                category_id=470,
                parent_id=99,
                name="Toe Kick",
                path="1/2/5/99/470",
                path_names="Kitchen Cabinets/Mouldings/Toe Kick",
                level=4,
                is_active=True,
                fetched_at=now,
            ),
            MagentoCategoryRegistry(
                connection_id=1,
                category_id=251,
                parent_id=98,
                name="Toe Kick",
                path="1/2/5/98/251",
                path_names="Default Category/Kitchen Cabinets/Accessories/Toe Kick",
                level=4,
                is_active=True,
                fetched_at=now,
            ),
        ]
    )
    path = upsert_listing_path(
        session,
        path_slug="kitchen-cabinets/accessories/toe-kick",
        title="Accessories / Toe Kick",
        path_kind="category",
    )
    upsert_listing_path_target(
        session,
        path_slug=path["path_slug"],
        channel_code="magento",
        remote_id="2",
        connection_id=1,
        remote_path="Default Category",
    )
    session.commit()

    preview = repair_magento_listing_path_targets(
        session,
        connection_id=1,
        dry_run=True,
        prune_stale_assignments=False,
        deactivate_invalid_collection_facet_masters=False,
    )
    assert preview["relink_count"] == 1
    assert preview["sample_mismatches"][0]["suggested_remote_id"] == "470"
    assert "Mouldings" in (preview["sample_mismatches"][0]["suggested_remote_path"] or "")