"""Persistence and the runtime lookup path, against the built database.

These run on `data/catalog.db`; they are skipped if the index has not been built.
"""

from pathlib import Path

import pytest

from spareparts import config, index_db

pytestmark = pytest.mark.skipif(
    not config.DB_PATH.exists(), reason="run `python -m spareparts.build_index` first"
)


@pytest.fixture(scope="module")
def conn():
    connection = index_db.connect()
    yield connection
    connection.close()


def _annotated(conn):
    return conn.execute("SELECT COUNT(*) FROM labels").fetchone()[0] > 0


def test_labels_and_components_are_paired(conn):
    if not _annotated(conn):
        pytest.skip("annotation stage has not been run")
    counts = index_db.annotation_counts(conn)
    assert counts["labels"] > 800
    # Every label gets exactly one component row, even when unresolved.
    assert conn.execute(
        "SELECT COUNT(*) FROM labels l"
        " LEFT JOIN components c ON c.label_id = l.id WHERE c.label_id IS NULL"
    ).fetchone()[0] == 0


def test_every_label_references_a_known_part_and_page(conn):
    if not _annotated(conn):
        pytest.skip("annotation stage has not been run")
    assert conn.execute(
        "SELECT COUNT(*) FROM labels l"
        " LEFT JOIN parts p ON p.part_no = l.part_no WHERE p.part_no IS NULL"
    ).fetchone()[0] == 0
    assert conn.execute(
        "SELECT COUNT(*) FROM labels l"
        " LEFT JOIN pages g ON g.pdf_page = l.pdf_page WHERE g.pdf_page IS NULL"
    ).fetchone()[0] == 0


def test_boxes_lie_inside_their_diagram(conn):
    if not _annotated(conn):
        pytest.skip("annotation stage has not been run")
    bad = conn.execute(
        """
        SELECT COUNT(*) FROM components c
        JOIN labels l ON l.id = c.label_id
        JOIN pages p ON p.pdf_page = l.pdf_page
        WHERE c.cx0 IS NOT NULL AND (
              c.cx0 < 0 OR c.cy0 < 0 OR c.cx1 > p.img_w OR c.cy1 > p.img_h
              OR c.cx1 <= c.cx0 OR c.cy1 <= c.cy0)
        """
    ).fetchone()[0]
    assert bad == 0


def test_every_method_is_a_known_tier(conn):
    if not _annotated(conn):
        pytest.skip("annotation stage has not been run")
    methods = set(index_db.method_counts(conn))
    assert methods <= {"leader", "assembly", "capped", "nearest", "override", "none"}


def test_lookup_answers_part_to_page_box_method_confidence(conn):
    """The one query the runtime needs, with no OCR or image work repeated."""
    if not _annotated(conn):
        pytest.skip("annotation stage has not been run")
    rows = index_db.lookup(conn, "30642")
    assert rows, "30642 is a real part with a callout on page 34"
    row = rows[0]
    assert row["pdf_page"] == 34
    assert row["section_code"] == "07"
    assert row["method"] in {"leader", "assembly", "capped", "nearest", "override"}
    assert row["cx0"] is not None and row["cx1"] > row["cx0"]
    assert 0.0 <= row["confidence"] <= 1.0
    assert Path(row["png_path"]).name == "p34.png"

    # The whole point of the phase: the box is the worm gear, not the digits.
    # A box may legitimately abut the callout it belongs to, so the test is that
    # it is a different, much larger region centred elsewhere -- not that the two
    # are disjoint.
    label_box = (row["lx0"], row["ly0"], row["lx1"], row["ly1"])
    comp_box = (row["cx0"], row["cy0"], row["cx1"], row["cy1"])
    label_area = (label_box[2] - label_box[0]) * (label_box[3] - label_box[1])
    comp_area = (comp_box[2] - comp_box[0]) * (comp_box[3] - comp_box[1])
    assert comp_area > 4 * label_area

    label_centre = ((label_box[0] + label_box[2]) / 2, (label_box[1] + label_box[3]) / 2)
    comp_centre = ((comp_box[0] + comp_box[2]) / 2, (comp_box[1] + comp_box[3]) / 2)
    assert not (
        label_box[0] <= comp_centre[0] <= label_box[2]
        and label_box[1] <= comp_centre[1] <= label_box[3]
    ), "component box is centred on the printed number"
    assert abs(comp_centre[1] - label_centre[1]) > 50


def test_multi_page_part_returns_every_page(conn):
    if not _annotated(conn):
        pytest.skip("annotation stage has not been run")
    # 73290 is listed on nine pages. Callouts exist on a subset of those, and
    # also on page 44, where the part is drawn but absent from that page's table
    # (PSD.md 4.10 records this happening). Such a callout is still valid -- it
    # is snapped against the global part set -- but must be flagged.
    rows = index_db.lookup(conn, "73290")
    pages = {r["pdf_page"] for r in rows}
    listed = {
        r[0] for r in conn.execute(
            "SELECT pdf_page FROM occurrences WHERE part_no = '73290'"
        )
    }
    assert pages
    for page in pages - listed:
        flag = conn.execute(
            "SELECT in_page_table FROM labels WHERE part_no = '73290' AND pdf_page = ?",
            (page,),
        ).fetchone()[0]
        assert flag == 0, f"page {page} is not in the table but was not flagged"

    # Every page it points at is a real catalogue page.
    catalogue = {r[0] for r in conn.execute("SELECT pdf_page FROM pages")}
    assert pages <= catalogue


def test_normalized_view_is_resolution_independent(conn):
    if not _annotated(conn):
        pytest.skip("annotation stage has not been run")
    row = conn.execute(
        "SELECT * FROM normalized_boxes WHERE part_no = '30642' AND pdf_page = 34"
    ).fetchone()
    assert row is not None
    for field in ("label_x0", "label_y1", "comp_x0", "comp_y1"):
        assert 0.0 <= row[field] <= 1.0

    page = conn.execute("SELECT img_w, img_h FROM pages WHERE pdf_page = 34").fetchone()
    native = conn.execute(
        "SELECT c.cx0 FROM labels l JOIN components c ON c.label_id = l.id"
        " WHERE l.part_no = '30642' AND l.pdf_page = 34"
    ).fetchone()[0]
    assert abs(row["comp_x0"] * page["img_w"] - native) < 1.0


def test_reannotation_does_not_duplicate(conn):
    if not _annotated(conn):
        pytest.skip("annotation stage has not been run")
    dupes = conn.execute(
        "SELECT COUNT(*) FROM (SELECT 1 FROM labels"
        " GROUP BY pdf_page, part_no, instance HAVING COUNT(*) > 1)"
    ).fetchone()[0]
    assert dupes == 0