"""Load-bearing ingest seams for the SQL source (ingest-spec §11) — D7 mirror of MAF I4.

Each test goes RED when its seam detaches (the method-spec §11 regime) — a grønn-men-død test
is the failure mode the rule exists for. The seams, proven THROUGH the SQL connector (a local
sqlite fixture — no network, no credentials):

- Typed-cell rendering (the NEW I5 seam) — SQL NULL renders as the empty string and a REAL keeps
  its shortest round-trip form; a naive ``str()`` coercion (NULL → ``"None"``) breaks this.
- Provenance stamping — a sql-generated file carries the §7 provenance layer, ``source_system``
  = the sql source id.
- Navigability — the sql bundle is consumable by the UNCHANGED ``okf`` navigation.
- Verdict reservation — a sql manifest mapping to ``type: verdict`` is rejected fail-fast,
  BEFORE the database is ever opened.
- Re-ingest layer safety — re-materialization over a sql bundle carrying a promoted verdict
  preserves the verdict file AND its index link.
"""

from __future__ import annotations

import json
from pathlib import Path

import pytest
from pydantic import ValidationError

from portfolio_optimiser_claude import okf
from portfolio_optimiser_claude.ingest import ManifestContract, load_manifest, materialize

GOLDEN = Path(__file__).resolve().parents[1] / "examples" / "ingest-golden-sql"
CONNECTION_REF = "PORTEFOLJE_SQL_DSN"
INGESTED_AT = (GOLDEN / "ingested-at.txt").read_text(encoding="utf-8").strip()
_FIXTURE = GOLDEN / "fixture" / "portefolje.sqlite"

_PROVENANCE_KEYS = ("source_system", "source_query", "ingested_at", "ingest_manifest", "generated")


def _materialized(tmp_path: Path, monkeypatch: pytest.MonkeyPatch) -> Path:
    monkeypatch.setenv(CONNECTION_REF, str(_FIXTURE))
    bundle = tmp_path / "bundle"
    bundle.mkdir()
    materialize(GOLDEN / "manifest.json", bundle, INGESTED_AT)
    return bundle


class TestTypedCellSeam:
    """NEW I5 seam: SQL typed-cell rendering (RED if it degrades to a bare ``str()``)."""

    def test_null_cell_renders_as_empty_string(
        self, tmp_path: Path, monkeypatch: pytest.MonkeyPatch
    ) -> None:
        bundle = _materialized(tmp_path, monkeypatch)
        costs = (bundle / "ingest-costs.md").read_text(encoding="utf-8")
        # costs row id=2 has note = SQL NULL → an empty cell, NOT the string "None".
        assert "| 2 | sensor | 89.9 |  |" in costs
        assert "None" not in costs

    def test_real_keeps_shortest_round_trip(
        self, tmp_path: Path, monkeypatch: pytest.MonkeyPatch
    ) -> None:
        bundle = _materialized(tmp_path, monkeypatch)
        costs = (bundle / "ingest-costs.md").read_text(encoding="utf-8")
        assert "| 3 | kabel | 4200.0 | rest |" in costs  # a whole-valued REAL keeps its .0


class TestProvenanceStamping:
    """Seam: a sql-generated file carries the §7 provenance layer (RED if it stops)."""

    def test_generated_sql_file_carries_the_provenance_layer(
        self, tmp_path: Path, monkeypatch: pytest.MonkeyPatch
    ) -> None:
        bundle = _materialized(tmp_path, monkeypatch)
        concept = okf.parse_concept_file(bundle / "ingest-costs.md")
        for key in _PROVENANCE_KEYS:
            assert key in concept.frontmatter, f"provenance key {key} detached"
        assert concept.frontmatter["source_system"] == "portefolje-db"
        assert concept.frontmatter["ingested_at"] == INGESTED_AT
        assert concept.frontmatter["generated"] == "true"
        stamp = load_manifest(GOLDEN / "manifest.json").stamp
        assert concept.frontmatter["ingest_manifest"] == stamp


class TestNavigability:
    """Seam: the generated sql bundle is consumable by the UNCHANGED okf navigation."""

    def test_sql_bundle_navigates_via_unchanged_okf(
        self, tmp_path: Path, monkeypatch: pytest.MonkeyPatch
    ) -> None:
        bundle = _materialized(tmp_path, monkeypatch)
        names = [c.path.name for c in okf.navigate_bundle(bundle)]
        # Every generated file must be REACHABLE via index cross-links (RED if linking detaches).
        assert names == ["index.md", "ingest-costs.md", "ingest-meta.md"]

    def test_sql_context_renders_the_extracted_rows(
        self, tmp_path: Path, monkeypatch: pytest.MonkeyPatch
    ) -> None:
        bundle = _materialized(tmp_path, monkeypatch)
        context = okf.bundle_context(bundle)
        assert "LED-armatur" in context  # a costs body cell
        assert "owner" in context  # a meta body cell


class TestVerdictReservation:
    """Seam: a sql manifest mapping to the verdict layer is rejected fail-fast (§3)."""

    def _sql_manifest(self, okf_type: str) -> dict:
        return {
            "manifest_version": 1,
            "source": {"type": "sql", "id": "db", "connection_ref": CONNECTION_REF},
            "bundle_summary": "s",
            "extractions": [
                {"id": "e", "title": "T", "query": "SELECT 1", "okf_type": okf_type, "max_rows": 1}
            ],
        }

    def test_verdict_okf_type_is_rejected(self) -> None:
        with pytest.raises(ValidationError):
            ManifestContract(**self._sql_manifest("verdict"))

    def test_rejection_is_fail_fast_before_the_db_is_opened(
        self, tmp_path: Path, monkeypatch: pytest.MonkeyPatch
    ) -> None:
        # With the connection_ref env var UNSET, a run that got past validation would fail with a
        # plain ValueError trying to open the db. Rejection is a ValidationError at validation —
        # so the db is never opened (RED if the reservation stops firing first).
        monkeypatch.delenv(CONNECTION_REF, raising=False)
        manifest = tmp_path / "manifest.json"
        manifest.write_text(json.dumps(self._sql_manifest("verdict")), encoding="utf-8")
        bundle = tmp_path / "bundle"
        with pytest.raises(ValidationError):
            materialize(manifest, bundle, INGESTED_AT)
        assert not bundle.exists() or not any(bundle.iterdir())


class TestReingestLayerSafety:
    """Seam: re-ingest over a sql bundle preserves a promoted verdict AND its link (§3, §6)."""

    def test_promoted_verdict_and_link_survive_sql_reingest(
        self, tmp_path: Path, monkeypatch: pytest.MonkeyPatch
    ) -> None:
        bundle = _materialized(tmp_path, monkeypatch)
        (bundle / "promoted-verdict-led.md").write_text(
            "---\ntype: verdict\ndecision: approved\ndescription: LED holds.\n---\n\nBody.\n",
            encoding="utf-8",
        )
        index = bundle / "index.md"
        index.write_text(
            index.read_text(encoding="utf-8") + "- [promoted](promoted-verdict-led.md)\n",
            encoding="utf-8",
        )
        materialize(GOLDEN / "manifest.json", bundle, INGESTED_AT)  # re-ingest

        assert (bundle / "promoted-verdict-led.md").is_file()  # verdict file survives
        index_text = (bundle / "index.md").read_text(encoding="utf-8")
        assert "- [promoted](promoted-verdict-led.md)" in index_text  # verdict link survives
        assert (bundle / "ingest-costs.md").is_file()  # ingest files still refreshed
        assert "](ingest-costs.md)" in index_text