"""Ingest SQL unit + fail-fast contract tests (ingest-spec §4, §5, §8).

Pins the ``SqlSource`` manifest contract, the §5 typed-cell rendering (int/float/NULL/other),
the runtime ``connection_ref`` resolution, the §8 size cap, and read-only access enforcement.
Everything is local: a tmp sqlite fixture, no network, no credentials.
"""

from __future__ import annotations

import json
import sqlite3
from pathlib import Path

import pytest
from pydantic import ValidationError

from portfolio_optimiser_claude.ingest import (
    ManifestContract,
    SqlSource,
    _read_sql,
    _render_sql_cell,
    _resolve_connection_ref,
    materialize,
)


def _db(tmp_path: Path, rows: list[tuple[object, ...]]) -> Path:
    db = tmp_path / "src.sqlite"
    con = sqlite3.connect(db)
    con.execute("CREATE TABLE t (a INTEGER, b REAL, c TEXT, d BLOB)")
    con.executemany("INSERT INTO t VALUES (?, ?, ?, ?)", rows)
    con.commit()
    con.close()
    return db


def _sql_manifest() -> dict:
    return {
        "manifest_version": 1,
        "source": {"type": "sql", "id": "db", "connection_ref": "SRC_DSN"},
        "bundle_summary": "s",
        "extractions": [
            {
                "id": "e",
                "title": "T",
                "query": "SELECT a FROM t ORDER BY a",
                "okf_type": "dataset",
                "max_rows": 5,
            }
        ],
    }


class TestSqlSourceContract:
    """§4: the sql source is a valid discriminated variant; the reference is required."""

    def test_valid_sql_source_loads(self) -> None:
        contract = ManifestContract(**_sql_manifest())
        assert isinstance(contract.source, SqlSource)
        assert contract.source.connection_ref == "SRC_DSN"

    def test_connection_ref_is_required(self) -> None:
        manifest = _sql_manifest()
        del manifest["source"]["connection_ref"]
        with pytest.raises(ValidationError):
            ManifestContract(**manifest)

    def test_sql_source_id_grammar_enforced(self) -> None:
        manifest = _sql_manifest()
        manifest["source"]["id"] = "Bad Id"
        with pytest.raises(ValidationError):
            ManifestContract(**manifest)


class TestTypedCellRendering:
    """§5: None→'', int→decimal, float→shortest round-trip, str→verbatim, other→fail."""

    def test_null_is_empty_string(self) -> None:
        assert _render_sql_cell(None) == ""

    def test_int_is_plain_decimal(self) -> None:
        assert _render_sql_cell(3) == "3"
        assert _render_sql_cell(0) == "0"

    def test_float_is_shortest_round_trip(self) -> None:
        assert _render_sql_cell(1200.5) == "1200.5"
        assert _render_sql_cell(89.9) == "89.9"
        assert _render_sql_cell(4200.0) == "4200.0"  # a whole-valued REAL keeps its .0

    def test_str_is_returned_raw(self) -> None:
        # _render_sql_cell returns the RAW string; §5 escaping is _escape_cell's job.
        assert _render_sql_cell("a|b\\c") == "a|b\\c"

    def test_other_value_type_fails(self) -> None:
        with pytest.raises(ValueError, match="not a supported value type"):
            _render_sql_cell(b"\x00\x01")  # a BLOB — never a silent coercion


class TestConnectorRuntime:
    """§8: reference resolution, size cap, and read-only access — all fail-fast."""

    def test_unset_connection_ref_fails_fast(self, monkeypatch: pytest.MonkeyPatch) -> None:
        monkeypatch.delenv("SRC_DSN", raising=False)
        with pytest.raises(ValueError, match="not set in the environment"):
            _resolve_connection_ref("SRC_DSN")

    def test_max_rows_cap_enforced_fail_fast(
        self, tmp_path: Path, monkeypatch: pytest.MonkeyPatch
    ) -> None:
        db = _db(tmp_path, [(1, 1.0, "x", None), (2, 2.0, "y", None)])
        manifest = _sql_manifest()
        manifest["extractions"][0]["max_rows"] = 1  # 2 rows > 1
        manifest_path = tmp_path / "manifest.json"
        manifest_path.write_text(json.dumps(manifest), encoding="utf-8")
        monkeypatch.setenv("SRC_DSN", str(db))
        with pytest.raises(ValueError, match="max_rows"):
            materialize(manifest_path, tmp_path / "bundle", "2026-07-04T12:00:00Z")

    def test_connection_is_read_only(self, tmp_path: Path) -> None:
        db = _db(tmp_path, [(1, 1.0, "x", None)])
        # The connector opens read-only (§4 SHOULD): a write statement is refused.
        with pytest.raises(sqlite3.OperationalError):
            _read_sql(str(db), "UPDATE t SET a = 9", 5)