from __future__ import annotations

import json
import sqlite3
import sys
from pathlib import Path
from typing import Any

import pytest

ROOT = Path(__file__).resolve().parents[1]
HEALTH = ROOT / "scripts" / "health"
if str(HEALTH) not in sys.path:
    sys.path.insert(0, str(HEALTH))

import dashboard_v5.nutrition_mapping_review as mapping_review  # noqa: E402
from dashboard_v5.nutrition_mapping_review import (  # noqa: E402
    ReviewLimitError,
    build_review,
    resolve_group,
)


def database() -> sqlite3.Connection:
    connection = sqlite3.connect(":memory:")
    connection.row_factory = sqlite3.Row
    connection.executescript(
        """
        CREATE TABLE nutrition_items(
          id INTEGER PRIMARY KEY, datum TEXT NOT NULL, name TEXT NOT NULL,
          item_hash TEXT NOT NULL UNIQUE
        );
        CREATE TABLE nutrition_histamine_scores(
          item_id INTEGER PRIMARY KEY, canonical_food TEXT, sighi_score INTEGER,
          traffic_light TEXT, tags TEXT, confidence TEXT, reason TEXT, scored_at TEXT
        );
        CREATE TABLE nutrition_review_queue(
          id INTEGER PRIMARY KEY, normalized_name TEXT UNIQUE, example_name TEXT,
          occurrence_count INTEGER, first_seen TEXT, last_seen TEXT,
          suggested_canonical_food TEXT, suggested_score INTEGER, reason TEXT,
          status TEXT, updated_at TEXT
        );
        CREATE TABLE histamine_food_rules(
          id INTEGER PRIMARY KEY, canonical_food TEXT UNIQUE, sighi_score INTEGER,
          category TEXT, tags TEXT, notes TEXT, confidence TEXT, source TEXT,
          updated_at TEXT
        );
        CREATE TABLE histamine_food_aliases(alias TEXT PRIMARY KEY, canonical_food TEXT);
        CREATE TABLE nutrition_mapping_action_log(
          action_id TEXT PRIMARY KEY, action_hash TEXT, queue_key TEXT,
          decision TEXT, alias TEXT, canonical_food TEXT, sighi_score INTEGER,
          confidence TEXT, mapping_method TEXT, source_label TEXT,
          source_version TEXT, note TEXT, ingredient_review_required INTEGER,
          personal_tolerance_status TEXT, personal_tolerance_note TEXT,
          definition_version TEXT, applied_at TEXT
        );
        CREATE TABLE nutrition_composite_product_review(
          normalized_name TEXT PRIMARY KEY, canonical_food TEXT, status TEXT,
          updated_at TEXT
        );
        """
    )
    return connection


def add_item(connection: sqlite3.Connection, identifier: int, name: str, day: str = "2026-06-01") -> None:
    connection.execute(
        "INSERT INTO nutrition_items(id,datum,name,item_hash) VALUES(?,?,?,?)",
        (identifier, day, name, f"synthetic-hash-{identifier}"),
    )


def add_rule(connection: sqlite3.Connection, canonical: str, score: int, confidence: str = "high") -> None:
    connection.execute(
        """INSERT INTO histamine_food_rules
           (canonical_food,sighi_score,confidence,source,updated_at)
           VALUES(?,?,?,'synthetic-local-rule','fixture-v1')""",
        (canonical, score, confidence),
    )


def test_groups_all_exact_unicode_identities_without_an_artificial_limit() -> None:
    connection = database()
    add_item(connection, 1, "  CAFÉ\t au   lait ")
    add_item(connection, 2, "cafe\u0301 au lait", "2026-06-02")
    for identifier in range(3, 132):
        add_item(connection, identifier, f"Synthetic food {identifier}")

    before_changes = connection.total_changes
    review = build_review(connection)

    assert connection.total_changes == before_changes
    assert review["summary"]["total_entries"] == 131
    assert review["summary"]["total_groups"] == 130
    grouped = next(group for group in review["groups"] if "CAFÉ au lait" in group["examples"])
    assert grouped["entry_count"] == 2
    assert grouped["days"] == ["2026-06-01", "2026-06-02"]
    assert len(grouped["key"]) == 32
    assert all(character in "0123456789abcdef" for character in grouped["key"])
    assert resolve_group(connection, grouped["key"]) == grouped


def test_punctuation_variants_stay_separate_and_legacy_collision_is_conflict() -> None:
    connection = database()
    add_item(connection, 1, "Rice-Cake")
    add_item(connection, 2, "rice cake")

    review = build_review(connection)

    assert review["summary"]["total_groups"] == 2
    assert review["summary"]["conflict_groups"] == 2
    assert {group["status"] for group in review["groups"]} == {"conflict"}
    assert len({group["key"] for group in review["groups"]}) == 2


def test_validated_suggestion_is_not_confirmation_and_confidence_is_separate() -> None:
    connection = database()
    add_item(connection, 1, "Green Apple")
    add_rule(connection, "apple", 0, "medium")
    connection.execute("INSERT INTO histamine_food_aliases VALUES('Green Apple','apple')")
    connection.execute(
        """INSERT INTO nutrition_review_queue
           (normalized_name,example_name,occurrence_count,first_seen,last_seen,
            suggested_canonical_food,suggested_score,reason,status)
           VALUES('green apple','green apple',1,'2026-06-01','2026-06-01',
                  'apple',0,'synthetic suggestion','open')"""
    )

    suggested = build_review(connection)["groups"][0]
    assert suggested["status"] == "suggestion_available"
    assert suggested["recommendation"] == {
        "canonical_food": "apple", "sighi_score": 0, "source": "queue_suggestion",
        "method": "sighi_reference",
        "category": "unbekannt", "source_label": "Lokaler Lebensmittelkatalog",
    }
    assert suggested["confidence"] == "medium"

    connection.execute(
        """INSERT INTO nutrition_histamine_scores
           (item_id,canonical_food,sighi_score,traffic_light,confidence,reason)
           VALUES(1,'apple',0,'classified','low','synthetic confirmed mapping')"""
    )
    evidence = "fixture|" + mapping_review.assignment_evidence_token(
        [{"item_hash": "synthetic-hash-1", "datum": "2026-06-01"}], "apple", 0
    )
    connection.execute(
        """INSERT INTO nutrition_mapping_action_log
           (action_id,action_hash,queue_key,decision,alias,canonical_food,sighi_score,
            confidence,definition_version,applied_at)
           VALUES('confirmed-action','confirmed-hash',?,'assign','Green Apple','apple',0,
                  'medium',?,'2026-06-02')""",
        (suggested["key"], evidence),
    )
    confirmed = build_review(connection)["groups"][0]
    assert confirmed["status"] == "confirmed"
    assert confirmed["confidence"] == "low"
    assert confirmed["recommendation"] is None

    add_item(connection, 2, "green apple", "2026-06-03")
    connection.execute(
        """INSERT INTO nutrition_histamine_scores
           (item_id,canonical_food,sighi_score,traffic_light,confidence,reason)
           VALUES(2,'apple',0,'classified','low','later technical mapping')"""
    )
    expanded = build_review(connection)["groups"][0]
    assert expanded["entry_count"] == 2
    assert expanded["status"] == "mapped_unverified"


def test_target_revision_changes_when_a_target_mapping_changes() -> None:
    connection = database()
    add_item(connection, 1, "Pear")
    first = build_review(connection)["groups"][0]
    connection.execute(
        """INSERT INTO nutrition_histamine_scores
           (item_id,canonical_food,sighi_score,traffic_light,confidence,reason)
           VALUES(1,'pear',1,'classified','high','synthetic')"""
    )
    second = build_review(connection)["groups"][0]
    assert first["target_revision"] != second["target_revision"]


def test_latest_actions_drive_terminal_states_and_summary_open_counts() -> None:
    connection = database()
    for identifier, name in enumerate(("Mapped", "Later", "Skip", "No map", "Open"), 1):
        add_item(connection, identifier, name)
    add_rule(connection, "mapped", 1)
    connection.execute(
        """INSERT INTO nutrition_histamine_scores
           (item_id,canonical_food,sighi_score,traffic_light,confidence,reason)
           VALUES(1,'mapped',1,'classified','high','synthetic')"""
    )
    mapped_key = next(
        group["key"] for group in build_review(connection)["groups"]
        if group["examples"][0] == "Mapped"
    )
    evidence = "fixture|" + mapping_review.assignment_evidence_token(
        [{"item_hash": "synthetic-hash-1", "datum": "2026-06-01"}], "mapped", 1
    )
    connection.execute(
        """INSERT INTO nutrition_mapping_action_log
           (action_id,action_hash,queue_key,decision,alias,canonical_food,sighi_score,
            confidence,definition_version,applied_at)
           VALUES('mapped-action','mapped-hash',?,'assign','Mapped','mapped',1,
                  'high',?,'2026-06-09')""",
        (mapped_key, evidence),
    )
    for index, (alias, decision) in enumerate(
        (("Later", "defer"), ("Skip", "irrelevant"), ("No map", "not_assignable")), 1
    ):
        connection.execute(
            """INSERT INTO nutrition_mapping_action_log
               (action_id,action_hash,queue_key,decision,alias,confidence,applied_at)
               VALUES(?,?,?, ?,?,'low',?)""",
            (f"action-{index}", f"hash-{index}", "unused", decision, alias, f"2026-06-0{index}"),
        )

    review = build_review(connection)
    states = {group["examples"][0]: group["status"] for group in review["groups"]}
    assert states == {
        "Later": "deferred", "Mapped": "confirmed", "No map": "not_assignable",
        "Open": "unassigned", "Skip": "irrelevant",
    }
    assert review["summary"] == {
        "total_entries": 5,
        "mapped_entries": 1,
        "verified_entries": 1,
        "open_entries": 2,
        "coverage": 0.2,
        "conflict_groups": 0,
        "deferred_groups": 1,
        "total_groups": 5,
    }
    assert [group["examples"][0] for group in build_review(connection, status="open")["groups"]] == ["Open"]


def test_public_contract_contains_no_database_ids_or_paths() -> None:
    connection = database()
    add_item(connection, 8472, "Synthetic / food")
    payload = build_review(connection)
    serialized = json.dumps(payload, ensure_ascii=False).casefold()
    assert "item_hash" not in serialized
    assert "action_id" not in serialized
    assert "source_product_id" not in serialized
    assert "/home/" not in serialized and "/tmp/" not in serialized and ".hermes" not in serialized
    assert all("id" not in group for group in payload["groups"])


def test_conflicting_casefolded_catalog_rows_are_deterministic_and_fail_closed() -> None:
    def review_for(order: tuple[tuple[str, int], ...]) -> dict[str, Any]:
        connection = database()
        add_item(connection, 1, "APPLE")
        for canonical, score in order:
            add_rule(connection, canonical, score)
        return build_review(connection)

    first = review_for((("Apple", 0), ("apple", 2)))
    second = review_for((("apple", 2), ("Apple", 0)))
    for result in (first, second):
        assert result["groups"][0]["status"] == "conflict"
        assert result["groups"][0]["recommendation"] is None
        assert result["catalog"] == []


def test_catalog_metadata_with_paths_urls_or_traversal_is_not_exposed() -> None:
    connection = database()
    add_item(connection, 1, "Unsafe")
    unsafe = (
        ("/home/agent/private.db", "food"),
        ("unsafe-url", "https://private.example/source"),
        (r"C:\\private\\food", "food"),
        ("unsafe-traversal", "../private/source"),
    )
    for index, (canonical, category) in enumerate(unsafe):
        connection.execute(
            """INSERT INTO histamine_food_rules
               (canonical_food,sighi_score,category,confidence,source,updated_at)
               VALUES(?,1,?,'high',?,?)""",
            (canonical, category, f"fixture-source-{index}", f"fixture-{index}"),
        )
    serialized = json.dumps(build_review(connection), ensure_ascii=False)
    for forbidden in ("/home/", "https://", r"C:\\private", "../private"):
        assert forbidden not in serialized


def test_unsafe_item_name_or_catalog_provenance_fails_closed() -> None:
    connection = database()
    add_item(connection, 1, "Meal from /home/agent/private.db")
    with pytest.raises(mapping_review.UnsafeReviewMetadataError):
        build_review(connection)

    connection = database()
    add_item(connection, 1, "Safe meal")
    connection.execute(
        """INSERT INTO histamine_food_rules
           (canonical_food,sighi_score,category,confidence,source,updated_at)
           VALUES('safe meal',1,'food','high','https://internal.example/source','2026-01-01')"""
    )
    review = build_review(connection)
    assert review["catalog"] == []
    assert review["groups"][0]["recommendation"] is None

    connection = database()
    add_item(connection, 1, "Meal=/custom/private.db")
    with pytest.raises(mapping_review.UnsafeReviewMetadataError):
        build_review(connection)


def test_alias_canonical_namespace_collision_is_conflict_and_not_assignable() -> None:
    connection = database()
    add_item(connection, 1, "Apple")
    connection.executemany(
        """INSERT INTO histamine_food_rules
           (canonical_food,sighi_score,category,confidence,source,updated_at)
           VALUES(?,?,?,?,?,?)""",
        (
            ("apple", 0, "fruit", "high", "fixture", "2026-01-01"),
            ("banana", 2, "fruit", "high", "fixture", "2026-01-01"),
        ),
    )
    connection.execute(
        "INSERT INTO histamine_food_aliases(alias,canonical_food) VALUES('Apple','banana')"
    )
    review = build_review(connection)
    assert review["groups"][0]["status"] == "conflict"
    assert review["groups"][0]["recommendation"] is None
    assert mapping_review.resolve_catalog_assignment(
        connection, "banana", alias="Apple", require_alias=True
    ) is None
    assert {row["canonical_food"] for row in review["catalog"]} == {"banana"}


def test_raw_item_limit_is_enforced_before_full_materialization(monkeypatch: pytest.MonkeyPatch) -> None:
    connection = database()
    for identifier in range(1, 4):
        add_item(connection, identifier, f"Bounded {identifier}")
    monkeypatch.setattr(mapping_review, "MAX_ITEM_ROWS", 2)
    with pytest.raises(ReviewLimitError, match="row_limit"):
        build_review(connection)


def test_examples_and_days_disclose_bounded_truncation() -> None:
    connection = database()
    variants = ["caseword", "Caseword", "cAseword", "caSeword", "casEword",
                "caseWord", "casewOrd", "casewoRd", "caseworD"]
    for identifier, variant in enumerate(variants, 1):
        add_item(connection, identifier, variant, f"2026-05-{identifier:02d}")
    for identifier in range(10, 34):
        add_item(connection, identifier, "caseword", f"2026-06-{identifier - 9:02d}")
    group = build_review(connection)["groups"][0]
    assert group["example_count"] == 9 and len(group["examples"]) == 8
    assert group["examples"] == [
        "Caseword", "cAseword", "caSeword", "casEword",
        "caseWord", "casewOrd", "casewoRd", "caseworD",
    ]
    assert group["examples_truncated"] is True
    assert group["day_count"] == 33 and len(group["days"]) == 31
    assert group["days"] == sorted(group["days"])
    assert group["days_truncated"] is True