"""Accepted mappings are append-only lifecycle records."""

from __future__ import annotations

from datetime import UTC, datetime, timedelta

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


def insert_mapping(
    db: Connection,
    *,
    record: int,
    osm_id: int,
    active: bool = True,
    superseded_at: str | None = None,
    first_mapped_at: datetime | None = None,
    last_confirmed_at: datetime | None = None,
) -> 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, "
            "confidence, evidence_json, active, first_mapped_at, last_confirmed_at, superseded_at) VALUES "
            "('scraper', :record, 'node', :osm_id, 'exact', 'test', .99, "
            "jsonb_build_object('match', true), :active, COALESCE(:first_mapped_at, CURRENT_TIMESTAMP), "
            "COALESCE(:last_confirmed_at, CURRENT_TIMESTAMP), :superseded_at) RETURNING id"
        ),
        {
            "record": record,
            "osm_id": osm_id,
            "active": active,
            "superseded_at": superseded_at,
            "first_mapped_at": first_mapped_at,
            "last_confirmed_at": last_confirmed_at,
        },
    ).scalar_one()


@pytest.mark.database
@pytest.mark.integration
def test_active_mapping_can_be_deactivated_once_but_not_reactivated_or_deleted(
    db: Connection, seed: dict[str, int]
) -> None:
    mapping_id = insert_mapping(db, record=1, osm_id=123)
    db.execute(
        text(
            "UPDATE osm_lead_source.noco_lead_mapping "
            "SET active = false, superseded_at = CURRENT_TIMESTAMP WHERE id = :id"
        ),
        {"id": mapping_id},
    )
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            db.execute(
                text("UPDATE osm_lead_source.noco_lead_mapping SET active = true WHERE id = :id"),
                {"id": mapping_id},
            )
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            db.execute(
                text("DELETE FROM osm_lead_source.noco_lead_mapping WHERE id = :id"),
                {"id": mapping_id},
            )
    del seed


@pytest.mark.database
@pytest.mark.integration
def test_mapping_identity_and_evidence_are_immutable(db: Connection, seed: dict[str, int]) -> None:
    mapping_id = insert_mapping(db, record=1, osm_id=123)
    statements = [
        "UPDATE osm_lead_source.noco_lead_mapping SET noco_record_id = 2 WHERE id = :id",
        "UPDATE osm_lead_source.noco_lead_mapping SET osm_id = 456 WHERE id = :id",
        "UPDATE osm_lead_source.noco_lead_mapping SET adoption_outcome = 'remapped' WHERE id = :id",
        "UPDATE osm_lead_source.noco_lead_mapping SET evidence_json = jsonb_build_object('edited', true) WHERE id = :id",
    ]
    for statement in statements:
        with pytest.raises(DBAPIError):
            with db.begin_nested():
                db.execute(text(statement), {"id": mapping_id})
    del seed


@pytest.mark.database
@pytest.mark.integration
def test_contradictory_mapping_shapes_are_rejected(db: Connection, seed: dict[str, int]) -> None:
    with pytest.raises(IntegrityError):
        with db.begin_nested():
            insert_mapping(
                db, record=1, osm_id=123, active=True, superseded_at="2026-01-01T00:00:00Z"
            )
    with pytest.raises(IntegrityError):
        with db.begin_nested():
            insert_mapping(db, record=2, osm_id=123, active=False)
    del seed


@pytest.mark.database
@pytest.mark.integration
def test_supersession_targets_same_noco_row_and_historical_rows_remain_allowed(
    db: Connection, seed: dict[str, int]
) -> None:
    first = insert_mapping(db, record=1, osm_id=123)
    db.execute(
        text(
            "INSERT INTO osm_lead_source.osm_source_object (osm_type, osm_id) VALUES "
            "('node', 456), ('node', 789)"
        )
    )
    other_row = insert_mapping(db, record=2, osm_id=456)
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            db.execute(
                text(
                    "UPDATE osm_lead_source.noco_lead_mapping SET active = false, "
                    "superseded_at = CURRENT_TIMESTAMP, superseded_by_mapping_id = :target WHERE id = :id"
                ),
                {"id": first, "target": other_row},
            )
    db.execute(
        text(
            "UPDATE osm_lead_source.noco_lead_mapping "
            "SET active = false, superseded_at = CURRENT_TIMESTAMP WHERE id = :id"
        ),
        {"id": first},
    )
    replacement = insert_mapping(db, record=1, osm_id=789)
    db.execute(
        text(
            "UPDATE osm_lead_source.noco_lead_mapping SET superseded_by_mapping_id = :target WHERE id = :id"
        ),
        {"id": first, "target": replacement},
    )
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            db.execute(
                text(
                    "UPDATE osm_lead_source.noco_lead_mapping "
                    "SET superseded_by_mapping_id = :target WHERE id = :id"
                ),
                {"id": first, "target": other_row},
            )
    historical = insert_mapping(
        db,
        record=1,
        osm_id=123,
        active=False,
        first_mapped_at=datetime(2025, 12, 1, tzinfo=UTC),
        last_confirmed_at=datetime(2025, 12, 15, tzinfo=UTC),
        superseded_at="2026-01-01T00:00:00Z",
    )
    assert historical != first
    del seed


@pytest.mark.database
@pytest.mark.integration
def test_supersession_rejects_inactive_superseded_and_self_or_other_row_targets(
    db: Connection, seed: dict[str, int]
) -> None:
    db.execute(
        text(
            "INSERT INTO osm_lead_source.osm_source_object (osm_type, osm_id) VALUES "
            "('node', 456), ('node', 789), ('node', 790), ('node', 791)"
        )
    )
    old = insert_mapping(db, record=1, osm_id=123)
    other_row = insert_mapping(db, record=2, osm_id=456)
    inactive_target = insert_mapping(
        db,
        record=1,
        osm_id=789,
        active=False,
        first_mapped_at=datetime(2025, 12, 1, tzinfo=UTC),
        last_confirmed_at=datetime(2025, 12, 15, tzinfo=UTC),
        superseded_at="2026-01-01T00:00:00Z",
    )
    db.execute(
        text(
            "UPDATE osm_lead_source.noco_lead_mapping "
            "SET active = false, superseded_at = CURRENT_TIMESTAMP WHERE id = :id"
        ),
        {"id": old},
    )
    for target in (inactive_target, other_row, old):
        with pytest.raises(DBAPIError):
            with db.begin_nested():
                db.execute(
                    text(
                        "UPDATE osm_lead_source.noco_lead_mapping "
                        "SET superseded_by_mapping_id = :target WHERE id = :id"
                    ),
                    {"id": old, "target": target},
                )

    replacement = insert_mapping(db, record=1, osm_id=790)
    db.execute(
        text(
            "UPDATE osm_lead_source.noco_lead_mapping "
            "SET active = false, superseded_at = CURRENT_TIMESTAMP WHERE id = :id"
        ),
        {"id": replacement},
    )
    next_replacement = insert_mapping(db, record=1, osm_id=791)
    db.execute(
        text(
            "UPDATE osm_lead_source.noco_lead_mapping "
            "SET superseded_by_mapping_id = :target WHERE id = :id"
        ),
        {"id": replacement, "target": next_replacement},
    )
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            db.execute(
                text(
                    "UPDATE osm_lead_source.noco_lead_mapping "
                    "SET superseded_by_mapping_id = :target WHERE id = :id"
                ),
                {"id": replacement, "target": inactive_target},
            )
    del seed


@pytest.mark.database
@pytest.mark.integration
def test_mapping_timestamp_chronology_allows_valid_history_only(
    db: Connection, seed: dict[str, int]
) -> None:
    now = datetime.now(UTC)
    with pytest.raises(IntegrityError):
        with db.begin_nested():
            insert_mapping(
                db,
                record=1,
                osm_id=123,
                active=False,
                first_mapped_at=now - timedelta(days=3),
                last_confirmed_at=now - timedelta(days=2),
                superseded_at=now - timedelta(days=4),
            )
    with pytest.raises(IntegrityError):
        with db.begin_nested():
            insert_mapping(
                db,
                record=1,
                osm_id=123,
                active=False,
                first_mapped_at=now - timedelta(days=3),
                last_confirmed_at=now - timedelta(days=2),
                superseded_at=now - timedelta(days=3, hours=1),
            )
    first = insert_mapping(
        db,
        record=1,
        osm_id=123,
        active=False,
        first_mapped_at=now - timedelta(days=3),
        last_confirmed_at=now - timedelta(days=2),
        superseded_at=now - timedelta(days=1),
    )
    second = insert_mapping(
        db,
        record=1,
        osm_id=123,
        active=False,
        first_mapped_at=now - timedelta(days=5),
        last_confirmed_at=now - timedelta(days=4),
        superseded_at=now - timedelta(days=3),
    )
    assert first != second
    del seed