"""I4 golden regression — the §11 golden extraction case for `sql`, byte-for-byte.

Mirrors tests/test_ingest_golden.py for the second source type. The case pins the §5 SQL type
rules AS RULES: SQL NULL → empty cell (never ``None``), REAL 4200.0 → ``4200.0`` (float form
preserved, not integer-coerced), non-integral 1200.5/89.9, and the escape order on the sql path
(``|`` → ``\\|``, ``\\`` → ``\\\\``). RED when ANY byte of the expected bundle diverges (spec
§11 "Golden regression" seam).

The fixture db is committed under ``fixture/`` (rebuild:
``.claude/projects/2026-07-04-i4-ingest-sql-maf/build_fixture.py``); the ``connection_ref`` env
var is set to it (the §4 run-time resolution). The db PATH never enters the bundle, so the
golden is checkout-location independent.
"""

from __future__ import annotations

from pathlib import Path

import pytest

from portfolio_optimiser.ingest import materialize

GOLDEN = Path(__file__).resolve().parents[1] / "examples" / "ingest-golden-sql"


def _bundle_bytes(directory: Path) -> dict[str, bytes]:
    return {p.name: p.read_bytes() for p in sorted(directory.iterdir()) if p.is_file()}


def test_golden_sql_extraction_is_bit_deterministic(
    tmp_path: Path, monkeypatch: pytest.MonkeyPatch
) -> None:
    ingested_at = (GOLDEN / "ingested-at.txt").read_text(encoding="utf-8").strip()
    monkeypatch.setenv("PORTEFOLJE_SQL_DSN", str(GOLDEN / "fixture" / "portefolje.sqlite"))
    out = tmp_path / "bundle"
    materialize(GOLDEN / "manifest.json", out, ingested_at=ingested_at)

    expected = _bundle_bytes(GOLDEN / "expected-bundle")
    actual = _bundle_bytes(out)
    # File-SET equality first — catches extra AND missing files, not just diverging bytes.
    assert actual.keys() == expected.keys()
    for name, content in expected.items():
        assert actual[name] == content, f"golden byte divergence in {name}"

    # §10: a second run over the same output leaves every byte unchanged.
    materialize(GOLDEN / "manifest.json", out, ingested_at=ingested_at)
    assert _bundle_bytes(out) == expected