"""Every featured sort ranks through one path (Stream AW2.2, D4): `catalog_queries.
featured_records` reads each `SavingRecord` field from its column, `list_product_variants(
sort="featured")` and `featured_savings` run `featured.order` over those records and fetch by
id. What it cost before: a SQL ORDER BY that read no saving fed every category, family, brand
and airport rail while the home page ran the agreed rules, so one question had two answers
(a Whisky rail led by two cards with no saving at all on the 19 Sep copy). Pinned here: the
seven picture tiers reach a record through `pictures_of`; a pin reads from the variant and
from its line, and a hidden line's pin is inert; `total` and the ranked ids come from one
statement; `only_ids` is an order; an id the fetch does not return is skipped, never a 500;
the home page and the list give one answer; `shop_count` is the airport count. SQLite kit."""
from __future__ import annotations

import pytest

from app.models import ProductLine, ProductVariant
from app.services import catalog_queries as cq, featured
from tests.featured_kit import NOW, build


@pytest.fixture
def db():
    s = build()
    yield s
    s.close()


def records_by_id(db, **kw):
    return {r.variant_id: r for r in cq.featured_records(db, now=NOW, **kw)}


def test_the_seven_tiers_reach_a_record_through_the_one_cascade(db):
    by_id = records_by_id(db, multi_only=False)
    assert {vid: by_id[vid].picture_tier for vid in range(1, 8)} == {1: 1, 2: 2, 3: 3, 4: 4, 5: 5, 6: 6, 7: 0}
    assert by_id[3].has_image and by_id[6].has_image and not by_id[2].has_image, "the v2 flag is the variant's own column only"
    assert by_id[3].awarded and not by_id[2].awarded


def test_each_field_is_read_from_its_column(db):
    r = records_by_id(db, multi_only=False)[3]
    assert (r.saving_usd, round(r.saving_pct, 4), r.shop_count, r.compared) == (3.0, round(3 / 103, 4), 2, True)
    assert (r.brand_id, r.product_line_id, r.category, r.is_exclusive, r.name) == (1, 12, "Whisky", False, "Tier three (own public photo)")
    assert (r.age_days, r.oldest_age_days, r.in_stock, r.sized, r.pinned, r.line_pinned) == (0, 0, True, True, False, False)
    assert featured.eligible(r)


def test_the_gates_read_stock_size_and_freshness(db):
    from tests.featured_kit import CDG, LHR, price, variant

    variant(db, 20, 3, 16, "Sold out everywhere"); price(db, 20, LHR, 10, in_stock=False); price(db, 20, CDG, 15, in_stock=False)
    variant(db, 21, 3, 16, "No size", quantity_value=None); price(db, 21, LHR, 10); price(db, 21, CDG, 15)
    variant(db, 22, 3, 16, "Stale"); price(db, 22, LHR, 10, days_ago=40); price(db, 22, CDG, 15, days_ago=20)
    variant(db, 23, 3, 16, "Expired leg"); price(db, 23, LHR, 10, days_ago=70); price(db, 23, CDG, 15)
    db.commit()
    by_id = records_by_id(db)
    assert featured.gates(by_id[20]) == ("out_of_stock",) and featured.gates(by_id[21]) == ("unsized",)
    assert featured.gates(by_id[22]) == ("stale",) and (by_id[22].age_days, by_id[22].oldest_age_days) == (20, 40)
    assert featured.gates(by_id[23]) == ("expired",)


def test_the_gates_read_airport_shops_only(db):
    """D10, the review of AW2.4: the three gate columns aggregated over every joined shop, so
    an online catalogue's observation decided whether a two-airport comparison was fresh,
    expired or in stock. A variant fresh at both airports but last seen online 70 days ago read
    as expired; one stale at both airports but read online today as fresh; one sold out at both
    airports but in stock online as in stock. The after evidence's home pool (1,089) was
    measured with the catalogue in the gates. (`cheapest_usd` and `dearest_usd` still coalesce
    to the in-stock price wherever it is, so the sold-out row also reads `no_saving`; the
    saving columns are v2's and outside this fix.)"""
    from tests.featured_kit import CDG, LHR, ONLINE, price, variant

    variant(db, 30, 3, 16, "Fresh at the airports, old online"); price(db, 30, LHR, 10); price(db, 30, CDG, 15); price(db, 30, ONLINE, 12, days_ago=70)
    variant(db, 31, 3, 16, "Stale at the airports, fresh online"); price(db, 31, LHR, 10, days_ago=20); price(db, 31, CDG, 15, days_ago=20); price(db, 31, ONLINE, 12)
    variant(db, 32, 3, 16, "Sold out at the airports, in stock online")
    price(db, 32, LHR, 10, in_stock=False); price(db, 32, CDG, 15, in_stock=False); price(db, 32, ONLINE, 12, in_stock=True)
    db.commit()
    by_id = records_by_id(db)
    assert featured.gates(by_id[30]) == () and (by_id[30].age_days, by_id[30].oldest_age_days) == (0, 0)
    assert featured.gates(by_id[31]) == ("stale",) and by_id[31].age_days == 20
    assert "out_of_stock" in featured.gates(by_id[32])


def test_shop_count_is_the_airport_count_and_the_catalogue_never_compares(db):
    """D10: one airport plus the online catalogue is identified, never compared; before, the
    catalogue counted as a shop and could corroborate a spread it never saw."""
    by_id = records_by_id(db, multi_only=False)
    assert by_id[8].shop_count == 1 and not by_id[8].compared and by_id[8].saving_usd == 0.0
    assert by_id[1].shop_count == 2
    assert 8 not in records_by_id(db, multi_only=True)


def test_a_pin_reads_from_the_variant_and_from_its_line_and_a_hidden_line_s_pin_is_inert(db):
    db.get(ProductVariant, 1).featured = True
    db.get(ProductLine, 11).featured = True
    db.get(ProductLine, 15).featured = True  # hidden: inert
    db.flush()
    by_id = records_by_id(db)
    assert by_id[1].pinned and not by_id[1].line_pinned
    assert by_id[2].line_pinned and not by_id[2].pinned
    assert not by_id[9].line_pinned
    assert featured.order(cq.featured_records(db, now=NOW))[:2] == [1, 2] or featured.order(cq.featured_records(db, now=NOW))[:2] == [2, 1]


def test_total_and_the_ranked_ids_come_from_one_statement(db):
    total, items = cq.list_product_variants(db, sort="featured", limit=100)
    ids = [i.id for i in items]
    assert total == len(ids) == 9
    assert ids == featured.order(cq.featured_records(db, multi_only=False))
    assert ids[0] == 6 and ids[-1] == 8, "the supplied bottle leads on equal-ish savings; the uncompared one is the tail"
    # Paging slices the same order.
    _, page = cq.list_product_variants(db, sort="featured", limit=2, offset=2)
    assert [i.id for i in page] == ids[2:4]
    # A filter changes the pool and the count together.
    total, items = cq.list_product_variants(db, sort="featured", multi_only=True, limit=100)
    assert total == len(items) == 8 and 8 not in {i.id for i in items}


def test_the_first_page_ranks_the_comparisons_alone_and_agrees_with_the_whole_list(db, monkeypatch):
    """The unfiltered `/products` first page ranked 11,252 records to show 24 (1,125 ms on the
    19 Sep copy against D4's 800 ms): the comparisons are ranked first (`multi_only`, the
    records `compared` names) and the whole list only when the page reaches its uncompared
    tail. The bounded order is the whole order's prefix, exactly."""
    everything = featured.order(cq.featured_records(db, multi_only=False))
    compared = featured.order(cq.featured_records(db, multi_only=True))
    assert everything[:len(compared)] == compared and len(everything) == len(compared) + 1

    calls = []
    real = cq.featured_records

    def spy(db_, **kw):
        calls.append(kw["multi_only"])
        return real(db_, **kw)

    monkeypatch.setattr(cq, "featured_records", spy)
    _, page = cq.list_product_variants(db, sort="featured", limit=4)
    assert [i.id for i in page] == everything[:4] and calls == [True]
    # A page that reaches the tail pays the full ranking, and shows the tail.
    calls.clear()
    _, page = cq.list_product_variants(db, sort="featured", limit=4, offset=6)
    assert [i.id for i in page] == everything[6:10] and 8 in {i.id for i in page} and calls == [True, False]
    # A list already confined to the comparisons ranks once.
    calls.clear()
    cq.list_product_variants(db, sort="featured", multi_only=True, limit=4)
    assert calls == [True]


def test_only_ids_is_an_order_and_no_ranking_runs(db, monkeypatch):
    monkeypatch.setattr(cq.featured_rules, "order", lambda *a, **k: (_ for _ in ()).throw(AssertionError("ranked")))
    _, items = cq.list_product_variants(db, only_ids=[3, 1, 2], limit=3)
    assert [i.id for i in items] == [3, 1, 2]


def test_an_id_the_fetch_does_not_return_is_skipped(db, monkeypatch):
    """Merged or hidden between the ranking and the fetch: the card is dropped, the page renders."""
    real = cq.featured_rules.order
    monkeypatch.setattr(cq.featured_rules, "order", lambda records, first=featured.FIRST: [999, *real(records, first)])
    total, items = cq.list_product_variants(db, sort="featured", limit=3)
    assert total == 9 and 999 not in {i.id for i in items} and len(items) == 2


def test_the_home_page_and_the_list_give_one_answer(db):
    home = [i.id for i in cq.featured_savings(db, total=8)]
    listed = [i.id for i in cq.list_product_variants(db, multi_only=True, sort="featured", limit=8)[1]]
    assert home == listed and len(home) == 8
    assert [i.id for i in cq.featured_savings(db, total=4)] == home[:4]
    assert cq.featured_savings(db, at_codes=["XXX"], total=8) == []


def test_the_airport_rows_carry_the_featured_columns(db):
    from tests.featured_kit import LHR

    rows = cq._airport_rows(db, [LHR])
    row = next(r for r in rows if r.id == 3)
    assert row.newest_observed_at is not None and row.cheapest_available is not None and row.quantity_value is not None
    assert (row.pinned, row.line_pinned) == (False, 0)