"""Append-only provenance baseline and immediate-chain constraints."""

from __future__ import annotations

from datetime import UTC, datetime, timedelta

import pytest
from sqlalchemy import Connection, text
from sqlalchemy.exc import DBAPIError


def mapping(db: Connection, record: int = 44, osm_id: int = 123) -> int:
    return db.execute(
        text(
            "INSERT INTO osm_lead_source.noco_lead_mapping "
            "(noco_table_id, noco_record_id, osm_type, osm_id, adoption_outcome, adoption_rule, "
            "evidence_json) VALUES ('scraper', :record, 'node', :osm_id, 'exact', 'test', "
            "jsonb_build_object('match', true)) RETURNING id"
        ),
        {"record": record, "osm_id": osm_id},
    ).scalar_one()


def baseline(
    db: Connection,
    mapping_id: int,
    snapshot: int,
    previous: int | None = None,
    table: str = "scraper",
    record: int = 44,
    valid_from: datetime | None = None,
) -> int:
    return db.execute(
        text(
            "INSERT INTO osm_lead_source.noco_source_baseline "
            "(noco_table_id, noco_record_id, mapping_id, source_snapshot_id, previous_baseline_id, "
            "source_payload_json, source_payload_hash, source_owned_field_hashes, valid_from, code_version, "
            "rule_version, provenance_evidence_json) VALUES "
            "(:table, :record, :mapping, :snapshot, :previous, jsonb_build_object('name', 'A'), :hash, "
            "jsonb_build_object('name', 'hash'), COALESCE(:valid_from, CURRENT_TIMESTAMP), 'test', 'test', "
            "jsonb_build_object('adopted', true)) RETURNING id"
        ),
        {
            "table": table,
            "record": record,
            "mapping": mapping_id,
            "snapshot": snapshot,
            "previous": previous,
            "hash": f"{mapping_id:064x}",
            "valid_from": valid_from,
        },
    ).scalar_one()


def supersede(db: Connection, baseline_id: int, at: datetime | None = None) -> None:
    db.execute(
        text("UPDATE osm_lead_source.noco_source_baseline SET superseded_at = :at WHERE id = :id"),
        {"id": baseline_id, "at": at or datetime.now(UTC)},
    )


@pytest.mark.database
@pytest.mark.integration
def test_mismatched_noco_identity_is_rejected(db: Connection, seed: dict[str, int]) -> None:
    mapping_id = mapping(db)
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            baseline(db, mapping_id, seed["snapshot"], table="wrong-table")
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            baseline(db, mapping_id, seed["snapshot"], record=45)


@pytest.mark.database
@pytest.mark.integration
def test_first_baseline_and_three_version_immediate_chain(
    db: Connection, seed: dict[str, int]
) -> None:
    mapping_id = mapping(db)
    first = baseline(db, mapping_id, seed["snapshot"])
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            baseline(
                db, mapping_id, seed["snapshot"], valid_from=datetime.now(UTC) + timedelta(hours=1)
            )

    first_superseded = datetime.now(UTC)
    supersede(db, first, first_superseded)
    second = baseline(
        db,
        mapping_id,
        seed["snapshot"],
        first,
        valid_from=first_superseded + timedelta(hours=1),
    )
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            baseline(
                db,
                mapping_id,
                seed["snapshot"],
                first,
                valid_from=first_superseded + timedelta(hours=2),
            )
    second_superseded = first_superseded + timedelta(hours=2)
    supersede(db, second, second_superseded)
    third = baseline(
        db,
        mapping_id,
        seed["snapshot"],
        second,
        valid_from=second_superseded + timedelta(hours=1),
    )
    assert (
        db.execute(
            text(
                "SELECT count(*) FROM osm_lead_source.noco_source_baseline WHERE mapping_id = :mapping"
            ),
            {"mapping": mapping_id},
        ).scalar_one()
        == 3
    )
    assert (
        db.execute(
            text(
                "SELECT previous_baseline_id FROM osm_lead_source.noco_source_baseline WHERE id = :id"
            ),
            {"id": third},
        ).scalar_one()
        == second
    )


@pytest.mark.database
@pytest.mark.integration
def test_baseline_requires_valid_chronology_and_previous_when_history_exists(
    db: Connection, seed: dict[str, int]
) -> None:
    mapping_id = mapping(db)
    first = baseline(db, mapping_id, seed["snapshot"])
    supersede_at = datetime.now(UTC)
    supersede(db, first, supersede_at)
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            baseline(db, mapping_id, seed["snapshot"], valid_from=supersede_at)
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            baseline(
                db,
                mapping_id,
                seed["snapshot"],
                first,
                valid_from=supersede_at - timedelta(seconds=1),
            )


@pytest.mark.database
@pytest.mark.integration
def test_baselines_are_immutable_and_historical_rows_remain_readable(
    db: Connection, seed: dict[str, int]
) -> None:
    mapping_id = mapping(db)
    first = baseline(db, mapping_id, seed["snapshot"])
    supersede_at = datetime.now(UTC)
    supersede(db, first, supersede_at)
    second = baseline(
        db,
        mapping_id,
        seed["snapshot"],
        first,
        valid_from=supersede_at + timedelta(hours=1),
    )
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            db.execute(
                text(
                    "UPDATE osm_lead_source.noco_source_baseline "
                    "SET source_payload_json = jsonb_build_object('name', 'edited') WHERE id = :id"
                ),
                {"id": second},
            )
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            db.execute(
                text(
                    "UPDATE osm_lead_source.noco_source_baseline SET superseded_at = NULL WHERE id = :id"
                ),
                {"id": first},
            )
    assert (
        db.execute(
            text("SELECT count(*) FROM osm_lead_source.noco_source_baseline WHERE id = :id"),
            {"id": first},
        ).scalar_one()
        == 1
    )