import pytest

from app.services.infra.database import bootstrap_tenant, get_db_connection
from app.services.pairing.decisions import APPROVAL_THRESHOLD, record
from app.services.pairing.queries import SERVED_PAIR_TYPES, servable_neighbours


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, in_stock=True, missing=None):
    _exec(schema,
          "INSERT INTO {S}.strategist_products (product_key, name, product_url, "
          "price_cents, currency, in_stock, missing_fields) "
          "VALUES (%s,%s,%s,1000,'USD',%s,%s)",
          (key, name, f"https://x.example/{key}", in_stock, missing or []))


def _pair(schema, anchor, neighbor, pair_type="similar", score=0.9,
          confidence=0.9):
    _exec(schema,
          "INSERT INTO {S}.strategist_product_neighbors (anchor_key, "
          "neighbor_key, pair_type, score, confidence, source, reasons) "
          "VALUES (%s,%s,%s,%s,%s,'embedding','[]')",
          (anchor, neighbor, pair_type, score, confidence))


@pytest.fixture
def graph(temp_tenant):
    bootstrap_tenant(temp_tenant)
    _product(temp_tenant, "a", "Anchor")
    _product(temp_tenant, "b", "Servable Neighbour")
    _pair(temp_tenant, "a", "b")
    return temp_tenant


def _keys(rows):
    return [r["product_key"] for r in rows]


def test_a_servable_neighbour_is_returned(graph):
    rows = servable_neighbours(graph, "a")
    assert _keys(rows) == ["b"]
    assert rows[0]["pair_type"] == "similar"
    assert rows[0]["pair_score"] == pytest.approx(0.9)


def test_a_rejected_pair_is_never_served(graph):
    # THE test of this phase. The approval queue means nothing if serving
    # ignores it.
    record(graph, [{"anchor_key": "a", "neighbor_key": "b",
                    "pair_type": "similar", "decision": "rejected"}])
    assert servable_neighbours(graph, "a") == []


def test_an_unapproved_low_confidence_pair_is_never_served(temp_tenant):
    bootstrap_tenant(temp_tenant)
    _product(temp_tenant, "a", "Anchor")
    _product(temp_tenant, "b", "Unsure Neighbour")
    _pair(temp_tenant, "a", "b", confidence=APPROVAL_THRESHOLD - 0.2)
    assert servable_neighbours(temp_tenant, "a") == []


def test_an_approved_low_confidence_pair_is_served(temp_tenant):
    bootstrap_tenant(temp_tenant)
    _product(temp_tenant, "a", "Anchor")
    _product(temp_tenant, "b", "Approved Neighbour")
    _pair(temp_tenant, "a", "b", confidence=APPROVAL_THRESHOLD - 0.2)
    record(temp_tenant, [{"anchor_key": "a", "neighbor_key": "b",
                          "pair_type": "similar", "decision": "approved"}])
    assert _keys(servable_neighbours(temp_tenant, "a")) == ["b"]


def test_an_out_of_stock_neighbour_is_never_served(temp_tenant):
    # Checked now rather than at pairing time: a product can sell out an hour
    # after the graph was built.
    bootstrap_tenant(temp_tenant)
    _product(temp_tenant, "a", "Anchor")
    _product(temp_tenant, "b", "Sold Out", in_stock=False)
    _pair(temp_tenant, "a", "b")
    assert servable_neighbours(temp_tenant, "a") == []


def test_a_flagged_neighbour_is_never_served(temp_tenant):
    bootstrap_tenant(temp_tenant)
    _product(temp_tenant, "a", "Anchor")
    _product(temp_tenant, "b", "No URL", missing=["product_url"])
    _pair(temp_tenant, "a", "b")
    assert servable_neighbours(temp_tenant, "a") == []


def test_only_the_served_types_come_back(temp_tenant):
    bootstrap_tenant(temp_tenant)
    _product(temp_tenant, "a", "Anchor")
    for i, pair_type in enumerate(("similar", "complement", "upsell")):
        _product(temp_tenant, f"n{i}", f"Neighbour {i}")
        _pair(temp_tenant, "a", f"n{i}", pair_type=pair_type)

    types = {r["pair_type"] for r in servable_neighbours(temp_tenant, "a", limit=10)}
    assert types == set(SERVED_PAIR_TYPES)
    assert "upsell" not in types


def test_results_are_ordered_by_score(temp_tenant):
    bootstrap_tenant(temp_tenant)
    _product(temp_tenant, "a", "Anchor")
    for key, score in (("low", 0.7), ("high", 0.95), ("mid", 0.8)):
        _product(temp_tenant, key, key)
        _pair(temp_tenant, "a", key, score=score)
    assert _keys(servable_neighbours(temp_tenant, "a", limit=3)) == \
        ["high", "mid", "low"]


def test_ordering_is_stable_when_scores_tie(temp_tenant):
    # An unstable order reads as a bug and makes Phase 5's measurement noisier.
    bootstrap_tenant(temp_tenant)
    _product(temp_tenant, "a", "Anchor")
    for key in ("n1", "n2", "n3"):
        _product(temp_tenant, key, key)
        _pair(temp_tenant, "a", key, score=0.8)
    assert _keys(servable_neighbours(temp_tenant, "a", limit=3)) == \
        _keys(servable_neighbours(temp_tenant, "a", limit=3))


def test_the_limit_is_respected(temp_tenant):
    bootstrap_tenant(temp_tenant)
    _product(temp_tenant, "a", "Anchor")
    for i in range(8):
        _product(temp_tenant, f"n{i}", f"N{i}")
        _pair(temp_tenant, "a", f"n{i}", score=0.9 - i * 0.01)
    assert len(servable_neighbours(temp_tenant, "a", limit=3)) == 3


def test_an_anchor_with_no_graph_returns_nothing(temp_tenant):
    bootstrap_tenant(temp_tenant)
    _product(temp_tenant, "a", "Anchor")
    assert servable_neighbours(temp_tenant, "a") == []


def test_a_missing_neighbour_row_is_dropped(temp_tenant):
    # The catalogue changes between pairing runs.
    bootstrap_tenant(temp_tenant)
    _product(temp_tenant, "a", "Anchor")
    _pair(temp_tenant, "a", "ghost")
    assert servable_neighbours(temp_tenant, "a") == []


def test_a_neighbour_with_no_reference_price_is_still_served(temp_tenant):
    # The currency-optional promise reaches the shopper: an unconverted price
    # affects cross-currency comparison, not whether a card can be shown.
    bootstrap_tenant(temp_tenant)
    _product(temp_tenant, "a", "Anchor")
    _product(temp_tenant, "b", "No Reference Price", missing=["price_reference"])
    _pair(temp_tenant, "a", "b")
    assert _keys(servable_neighbours(temp_tenant, "a")) == ["b"]