import pytest

from app.services.infra.database import bootstrap_tenant, get_db_connection
from app.services.pairing.decisions import record
from app.services.pairing.queries import (
    list_anchors, list_pairings, pairings_for,
)


def _exec(schema, statement, params=None):
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(statement.replace("{S}", f'"{schema}"'), params or ())
        conn.commit()
    finally:
        conn.close()


def _product(schema, key, name, category, price, image="https://x.example/i.png"):
    _exec(schema,
          "INSERT INTO {S}.strategist_products (product_key, name, category, "
          "price_cents, currency, image_url, product_url) "
          "VALUES (%s,%s,%s,%s,'USD',%s,%s)",
          (key, name, category, price, image, f"https://x.example/{key}"))


def _pair(schema, anchor, neighbor, pair_type, score=0.8, confidence=0.5):
    _exec(schema,
          "INSERT INTO {S}.strategist_product_neighbors (anchor_key, "
          "neighbor_key, pair_type, score, confidence, source, reasons) "
          "VALUES (%s,%s,%s,%s,%s,'attribute','[\"a reason\"]')",
          (anchor, neighbor, pair_type, score, confidence))


@pytest.fixture
def graph(temp_tenant):
    bootstrap_tenant(temp_tenant)
    _product(temp_tenant, "p:1", "iPad Pro", "tablets", 34999)
    _product(temp_tenant, "p:2", "Charging Cable", "mobile-accessories", 5897)
    _pair(temp_tenant, "p:1", "p:2", "complement")
    return temp_tenant


def test_the_product_list_carries_its_currency(graph):
    # Without this the page had no currency to show and fell back to "$" on a
    # catalogue priced in INR -- a 100x error presented as a default.
    from app.services.pairing.queries import list_products

    product = list_products(graph, category="tablets")["products"][0]
    assert product["currency"] == "USD"


def test_a_pairing_carries_the_neighbours_card(graph):
    # A merchant cannot judge "p:1 -> p:2". They need to see an iPad suggesting
    # a cable, with prices and images.
    pair = pairings_for(graph, "p:1")["complement"][0]
    assert pair["neighbor"]["name"] == "Charging Cable"
    assert pair["neighbor"]["price_cents"] == 5897
    assert pair["neighbor"]["currency"] == "USD"
    assert pair["neighbor"]["image_url"]
    assert pair["neighbor"]["category"] == "mobile-accessories"


def test_the_existing_keys_all_survive(graph):
    # The change is additive: something may already read these.
    pair = pairings_for(graph, "p:1")["complement"][0]
    for key in ("anchor_key", "neighbor_key", "pair_type", "score",
                "confidence", "source", "reasons", "servable"):
        assert key in pair


def test_the_queue_carries_both_sides(graph):
    row = list_pairings(graph)["pairings"][0]
    assert row["anchor"]["name"] == "iPad Pro"
    assert row["neighbor"]["name"] == "Charging Cable"


def test_the_queue_reports_how_many_rows_it_dropped(graph):
    # The catalog changes between pairing runs. A silently shorter list looks
    # like an empty queue rather than a stale graph.
    _pair(graph, "p:1", "ghost", "complement")
    result = list_pairings(graph)
    assert result["dropped"] == 1
    assert all(r["neighbor_key"] != "ghost" for r in result["pairings"])


def test_a_pairing_whose_neighbour_vanished_is_dropped(graph):
    _pair(graph, "p:1", "ghost", "similar")
    assert pairings_for(graph, "p:1")["similar"] == []


def test_resolving_does_not_query_once_per_pair(graph, monkeypatch):
    # The whole reason this lives in SQL rather than in the page. With 229
    # queued pairs an N+1 would be 460 round trips per screen load.
    import app.services.pairing.queries as mod

    counter = {"n": 0}
    real = mod.get_db_connection

    # psycopg2's connection and cursor objects are C types with no instance
    # __dict__, so their methods cannot be monkeypatched in place -- wrapping
    # objects that delegate everything except `execute` is the only way to
    # count queries without changing what actually runs.
    class _CountingCursor:
        def __init__(self, cur):
            self._cur = cur

        def execute(self, *a, **kw):
            counter["n"] += 1
            return self._cur.execute(*a, **kw)

        def __getattr__(self, name):
            return getattr(self._cur, name)

        def __enter__(self):
            self._cur.__enter__()
            return self

        def __exit__(self, *exc):
            return self._cur.__exit__(*exc)

        def __iter__(self):
            return iter(self._cur)

    class _CountingConnection:
        def __init__(self, conn):
            self._conn = conn

        def cursor(self, *a, **kw):
            return _CountingCursor(self._conn.cursor(*a, **kw))

        def __getattr__(self, name):
            return getattr(self._conn, name)

    def counting():
        return _CountingConnection(real())

    monkeypatch.setattr(mod, "get_db_connection", counting)

    for i in range(3, 30):
        _product(graph, f"p:{i}", f"Product {i}", "tablets", 1000 + i)
        _pair(graph, "p:1", f"p:{i}", "similar")

    counter["n"] = 0
    result = pairings_for(graph, "p:1")
    assert len(result["similar"]) == 27
    # One query for the pairs, one for the decisions. Never per pair.
    assert counter["n"] <= 3


def test_an_empty_queue_is_not_an_error(temp_tenant):
    bootstrap_tenant(temp_tenant)
    result = list_pairings(temp_tenant)
    assert result["pairings"] == []
    assert result["dropped"] == 0


# --- the review screen: one anchor at a time ---------------------------------

def _pending_fixture(schema):
    """One anchor with three pending pairs, another with one."""
    _product(schema, "dress", "Luna Silk Midi Dress", "womens-dresses", 13200)
    _product(schema, "earring", "Nova Pearl Drop Earrings", "jewellery", 4500)
    _product(schema, "bag", "Woven Tote", "womens-bags", 8900)
    _product(schema, "scarf", "Silk Scarf", "scarves", 3500)
    _product(schema, "shirt", "Oxford Shirt", "mens-shirts", 5900)
    _product(schema, "belt", "Leather Belt", "belts", 2900)
    for neighbor, score in (("earring", 0.9), ("bag", 0.8), ("scarf", 0.7)):
        _pair(schema, "dress", neighbor, "complement", score, 0.2)
    _pair(schema, "shirt", "belt", "complement", 0.6, 0.2)


def test_anchors_carry_their_pending_count(graph):
    _pending_fixture(graph)
    anchors = {a["anchor_key"]: a for a in list_anchors(graph)["anchors"]}
    assert anchors["dress"]["pending"] == 3
    assert anchors["shirt"]["pending"] == 1


def test_an_anchor_carries_a_card_for_the_screen(graph):
    _pending_fixture(graph)
    dress = next(a for a in list_anchors(graph)["anchors"] if a["anchor_key"] == "dress")
    assert dress["anchor"]["name"] == "Luna Silk Midi Dress"
    assert "price_cents" in dress["anchor"]


def test_anchors_are_ordered_by_their_best_candidate(graph):
    # A merchant working down the list should meet the most promising
    # decisions while they still have patience for them.
    _pending_fixture(graph)
    assert [a["anchor_key"] for a in list_anchors(graph)["anchors"]][0] == "dress"


def test_the_queue_can_be_filtered_to_one_anchor(graph):
    _pending_fixture(graph)
    result = list_pairings(graph, anchor_key="dress")
    assert len(result["pairings"]) == 3
    assert {r["anchor_key"] for r in result["pairings"]} == {"dress"}


def test_a_decided_pair_leaves_both_the_anchor_list_and_the_queue(graph):
    _pending_fixture(graph)
    record(graph, [{"anchor_key": "dress", "neighbor_key": "earring",
                    "pair_type": "complement", "decision": "approved"}])

    dress = next(a for a in list_anchors(graph)["anchors"] if a["anchor_key"] == "dress")
    assert dress["pending"] == 2
    assert len(list_pairings(graph, anchor_key="dress")["pairings"]) == 2


def test_an_anchor_with_nothing_pending_is_not_listed(graph):
    _pending_fixture(graph)
    for neighbor in ("earring", "bag", "scarf"):
        record(graph, [{"anchor_key": "dress", "neighbor_key": neighbor,
                        "pair_type": "complement", "decision": "rejected"}])
    assert "dress" not in {a["anchor_key"] for a in list_anchors(graph)["anchors"]}


def test_filtering_by_score_band(graph):
    _pending_fixture(graph)
    high = list_pairings(graph, status="all", score="high")["pairings"]
    low = list_pairings(graph, status="all", score="low")["pairings"]

    assert all(p["score"] >= 0.75 for p in high)
    assert all(p["score"] < 0.60 for p in low)
    assert not ({p["neighbor_key"] for p in high}
                & {p["neighbor_key"] for p in low})


def test_filtering_by_category(graph):
    _pending_fixture(graph)
    rows = list_pairings(graph, status="all", category="womens-dresses")["pairings"]
    assert rows
    assert all(r["anchor"]["category"] == "womens-dresses" for r in rows)


def test_approved_and_rejected_are_separately_addressable(graph):
    _pending_fixture(graph)
    record(graph, [{"anchor_key": "dress", "neighbor_key": "earring",
                    "pair_type": "complement", "decision": "approved"},
                   {"anchor_key": "dress", "neighbor_key": "bag",
                    "pair_type": "complement", "decision": "rejected"}])

    approved = list_pairings(graph, status="approved")["pairings"]
    rejected = list_pairings(graph, status="rejected")["pairings"]
    pending = list_pairings(graph, status="pending")["pairings"]

    assert [p["neighbor_key"] for p in approved] == ["earring"]
    assert [p["neighbor_key"] for p in rejected] == ["bag"]
    assert "earring" not in [p["neighbor_key"] for p in pending]


def test_an_anchor_carries_its_whole_breakdown(graph):
    # A screen shows "4 pending, 2 approved" without asking again per product.
    _pending_fixture(graph)
    record(graph, [{"anchor_key": "dress", "neighbor_key": "earring",
                    "pair_type": "complement", "decision": "approved"}])

    dress = next(a for a in list_anchors(graph, status="all")["anchors"]
                 if a["anchor_key"] == "dress")
    assert dress["approved"] == 1
    assert dress["pending"] == 2


def test_an_unknown_filter_is_rejected(graph):
    import pytest as _pytest

    with _pytest.raises(ValueError):
        list_pairings(graph, status="maybe")
    with _pytest.raises(ValueError):
        list_pairings(graph, score="enormous")