"""I4 load-bearing seams — the ``sql`` path end-to-end (ingest spec §11's detach-RED regime).

The ``file`` seams (tests/test_ingest_loadbearing.py) are source-agnostic where they can be;
these tests prove the SEAMS the ``sql`` source introduces — typed §5 rendering (the ``file``
path is all-strings) and that a SQL-generated bundle flows through the SAME navigability and
provenance machinery. Each test is built so the seam under test is the ONLY thing preventing
the observable artifact (the Fase-2 green-but-dead trap).

- NAVIGABILITY (§2/§11): a SQL-generated bundle is consumable by the UNCHANGED
  ``okf.navigate_bundle``/``bundle_context`` — reachable via index cross-links, classified by
  ``okf_type``, provenance riding through. RED when index linking is detached.
- TYPED NULL (§5): SQL NULL renders as the empty string, NEVER ``"None"``. RED when the None
  branch degrades to ``str(value)``.
- TYPED REAL (§5): a REAL value keeps its shortest round-trip float form (4200.0 → ``4200.0``),
  NEVER integer-coerced. RED when the float branch degrades to ``str(int(value))``.
"""

from __future__ import annotations

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

from portfolio_optimiser import okf
from portfolio_optimiser.ingest import materialize

_INGESTED_AT = "2026-07-03T12:00:00Z"


def _make_db(path: Path, script: str) -> Path:
    conn = sqlite3.connect(path)
    try:
        conn.executescript(script)
        conn.commit()
    finally:
        conn.close()
    return path


def _sql_manifest(tmp_path: Path, extractions: list[dict[str, Any]]) -> Path:
    manifest = {
        "manifest_version": 1,
        "source": {"type": "sql", "id": "db", "connection_ref": "PROJ_DB"},
        "bundle_summary": "SQL extracts from the project database.",
        "extractions": extractions,
    }
    path = tmp_path / "manifest.json"
    path.write_text(json.dumps(manifest), encoding="utf-8")
    return path


def test_sql_generated_bundle_is_navigable_via_unchanged_okf(
    tmp_path: Path, monkeypatch: Any
) -> None:
    """LOAD-BEARING NAVIGABILITY: the SQL path produces a bundle the untouched OKF navigation
    reaches through index cross-links (classified by okf_type, provenance preserved) and
    ``bundle_context`` renders. RED when index linking is detached (file exists, unreachable)."""
    db = _make_db(
        tmp_path / "s.sqlite",
        "CREATE TABLE costs (item TEXT, amount REAL);"
        " INSERT INTO costs VALUES ('led', 120.0), ('hvac', 80.0);",
    )
    monkeypatch.setenv("PROJ_DB", str(db))
    manifest = _sql_manifest(
        tmp_path,
        [
            {
                "id": "costs",
                "title": "Project costs",
                "query": "SELECT item, amount FROM costs ORDER BY item",
                "okf_type": "dataset",
                "max_rows": 100,
            }
        ],
    )
    bundle_dir = tmp_path / "bundle"
    materialize(manifest, bundle_dir, ingested_at=_INGESTED_AT)

    bundle = okf.navigate_bundle(str(bundle_dir))
    by_name = {f.name: f for f in bundle.files}
    assert "ingest-costs.md" in by_name
    assert by_name["ingest-costs.md"].type == "dataset"
    assert by_name["ingest-costs.md"].frontmatter["ingest_manifest"]  # provenance rides through
    context = okf.bundle_context(bundle)
    assert "Project costs" in context and "| hvac | 80.0 |" in context


def test_sql_null_renders_as_empty_not_none(tmp_path: Path, monkeypatch: Any) -> None:
    """LOAD-BEARING TYPED NULL: detaching the None branch to ``str(value)`` would render the
    string ``None`` into the bundle; the empty cell is the ONLY correct §5 form."""
    db = _make_db(
        tmp_path / "s.sqlite",
        "CREATE TABLE costs (item TEXT, note TEXT); INSERT INTO costs VALUES ('sensor', NULL);",
    )
    monkeypatch.setenv("PROJ_DB", str(db))
    manifest = _sql_manifest(
        tmp_path,
        [
            {
                "id": "costs",
                "title": "Project costs",
                "query": "SELECT item, note FROM costs ORDER BY item",
                "okf_type": "dataset",
                "max_rows": 100,
            }
        ],
    )
    bundle_dir = tmp_path / "bundle"
    materialize(manifest, bundle_dir, ingested_at=_INGESTED_AT)
    body = (bundle_dir / "ingest-costs.md").read_text(encoding="utf-8")
    assert "| sensor |  |" in body  # NULL → empty cell
    assert "None" not in body  # RED if the None branch degrades to str()


def test_sql_real_preserves_float_form(tmp_path: Path, monkeypatch: Any) -> None:
    """LOAD-BEARING TYPED REAL: a REAL keeps its shortest round-trip float form; integer
    coercion (``4200``) is the detach this test catches."""
    db = _make_db(
        tmp_path / "s.sqlite",
        "CREATE TABLE costs (item TEXT, amount REAL); INSERT INTO costs VALUES ('kabel', 4200.0);",
    )
    monkeypatch.setenv("PROJ_DB", str(db))
    manifest = _sql_manifest(
        tmp_path,
        [
            {
                "id": "costs",
                "title": "Project costs",
                "query": "SELECT item, amount FROM costs ORDER BY item",
                "okf_type": "dataset",
                "max_rows": 100,
            }
        ],
    )
    bundle_dir = tmp_path / "bundle"
    materialize(manifest, bundle_dir, ingested_at=_INGESTED_AT)
    body = (bundle_dir / "ingest-costs.md").read_text(encoding="utf-8")
    assert "| kabel | 4200.0 |" in body  # float form preserved
    assert "| kabel | 4200 |" not in body  # RED if integer-coerced