"""Regression: category registry bulk_replace must tolerate duplicate Magento ids."""

from datetime import datetime, timezone

from sqlalchemy import select

from db.magento_repositories import SqlAlchemyCategoryRegistryRepository
from db.models import MagentoCategoryRegistry, MagentoConnection


def test_category_registry_bulk_replace_dedupes_and_updates(catalog_intent_session):
    session = catalog_intent_session
    conn = MagentoConnection(
        store_base_url="https://example.test",
        environment="staging",
        consumer_key="k",
        consumer_secret="s",
        access_token="t",
        access_token_secret="ts",
        status="active",
    )
    session.add(conn)
    session.flush()

    now = datetime.now(timezone.utc)
    repo = SqlAlchemyCategoryRegistryRepository(session)

    # Pre-existing row that must be updated, not re-inserted.
    session.add(
        MagentoCategoryRegistry(
            connection_id=conn.id,
            category_id=386,
            parent_id=5,
            name="Old Name",
            path="1/2/5/386",
            path_names="Default Category/Kitchen Cabinets/Old Name",
            level=3,
            is_active=True,
            fetched_at=now,
        )
    )
    session.flush()

    cats = [
        {
            "category_id": 386,
            "parent_id": 5,
            "name": "Anna Caramel Harvest",
            "path": "1/2/5/386",
            "path_names": "Default Category/Kitchen Cabinets/Anna Caramel Harvest",
            "level": 3,
            "is_active": True,
        },
        # Duplicate id in same payload (Magento tree quirk) — must not UniqueViolation.
        {
            "category_id": 386,
            "parent_id": 5,
            "name": "Anna Caramel Harvest Updated",
            "path": "1/2/5/386",
            "path_names": "Default Category/Kitchen Cabinets/Anna Caramel Harvest Updated",
            "level": 3,
            "is_active": True,
        },
        {
            "category_id": 387,
            "parent_id": 5,
            "name": "Anna Natural Rift White Oak",
            "path": "1/2/5/387",
            "path_names": "Default Category/Kitchen Cabinets/Anna Natural Rift White Oak",
            "level": 3,
            "is_active": True,
        },
    ]

    count = repo.bulk_replace(conn.id, cats, now)
    session.commit()

    assert count == 2
    from sqlalchemy import select

    rows = list(
        session.scalars(
            select(MagentoCategoryRegistry)
            .where(MagentoCategoryRegistry.connection_id == conn.id)
            .order_by(MagentoCategoryRegistry.category_id)
        ).all()
    )
    assert [r.category_id for r in rows] == [386, 387]
    assert rows[0].name == "Anna Caramel Harvest Updated"


def test_category_registry_bulk_replace_deactivates_missing_ids(catalog_intent_session):
    session = catalog_intent_session
    conn = MagentoConnection(
        store_base_url="https://example.test",
        environment="staging",
        consumer_key="k",
        consumer_secret="s",
        access_token="t",
        access_token_secret="ts",
        status="active",
    )
    session.add(conn)
    session.flush()
    now = datetime.now(timezone.utc)
    session.add(
        MagentoCategoryRegistry(
            connection_id=conn.id,
            category_id=300,
            parent_id=36,
            name="Deleted Child",
            path="1/2/5/36/300",
            path_names="Default Category/Kitchen Cabinets/Base Cabinets/Deleted Child",
            level=4,
            is_active=True,
            fetched_at=now,
        )
    )
    session.flush()

    repo = SqlAlchemyCategoryRegistryRepository(session)
    count = repo.bulk_replace(
        conn.id,
        [
            {
                "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,
            }
        ],
        now,
    )
    session.flush()

    assert count == 1
    stale = session.scalar(
        select(MagentoCategoryRegistry)
        .where(MagentoCategoryRegistry.connection_id == conn.id)
        .where(MagentoCategoryRegistry.category_id == 300)
        .limit(1)
    )
    assert stale is not None
    assert stale.is_active is False
    live = session.scalar(
        select(MagentoCategoryRegistry)
        .where(MagentoCategoryRegistry.connection_id == conn.id)
        .where(MagentoCategoryRegistry.category_id == 36)
        .limit(1)
    )
    assert live is not None
    assert live.is_active is True


def test_flatten_category_tree_skips_duplicate_ids():
    from app.jobs.magento_baseline_sync import _flatten_category_tree

    tree = {
        "id": 1,
        "name": "Root",
        "path": "1",
        "level": 0,
        "is_active": True,
        "children_data": [
            {
                "id": 386,
                "parent_id": 1,
                "name": "A",
                "path": "1/386",
                "level": 1,
                "is_active": True,
                "children_data": [],
            },
            {
                "id": 386,
                "parent_id": 1,
                "name": "A duplicate",
                "path": "1/386",
                "level": 1,
                "is_active": True,
                "children_data": [],
            },
        ],
    }
    out: list = []
    _flatten_category_tree(tree, out, parent_path_names="")
    assert [row["category_id"] for row in out] == [1, 386]
    assert out[1]["name"] == "A"