"""Database-level snapshot completeness and presence lifecycle gates."""

from __future__ import annotations

from datetime import UTC, datetime, timedelta

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

from conftest import add_observation, add_snapshot


def insert_presence(
    db: Connection,
    *,
    snapshot: int,
    state: str,
    completeness: str = "complete",
    missing: int = 0,
    first_missing_snapshot: int | None = None,
    first_missing_at: datetime | None = None,
    last_seen_snapshot: int | None = None,
    last_seen_at: datetime | None = None,
    boundary: bool = False,
) -> None:
    db.execute(
        text(
            "INSERT INTO osm_lead_source.osm_source_presence "
            "(region_id, osm_type, osm_id, source_presence_state, consecutive_missing_snapshots, "
            "first_missing_at, last_seen_at, first_missing_snapshot_id, last_seen_snapshot_id, "
            "latest_snapshot_id, completeness_status, boundary_ambiguity) VALUES "
            "('test-region', 'node', 123, :state, :missing, :first_at, :last_seen_at, "
            ":first_snapshot, :last_seen_snapshot, :snapshot, :completeness, :boundary)"
        ),
        {
            "state": state,
            "missing": missing,
            "first_at": first_missing_at,
            "last_seen_at": last_seen_at,
            "first_snapshot": first_missing_snapshot,
            "last_seen_snapshot": last_seen_snapshot,
            "snapshot": snapshot,
            "completeness": completeness,
            "boundary": boundary,
        },
    )


@pytest.mark.database
@pytest.mark.integration
@pytest.mark.parametrize(
    "column",
    [
        "content_sha256",
        "size_bytes",
        "downloaded_at",
        "validated_at",
        "parser_completed_at",
        "reconciliation_completed_at",
    ],
)
def test_complete_snapshot_requires_every_structural_field(
    db: Connection, seed: dict[str, int], column: str
) -> None:
    del seed
    values = {
        "content_sha256": ":hash",
        "size_bytes": "1",
        "downloaded_at": "CURRENT_TIMESTAMP",
        "validated_at": "CURRENT_TIMESTAMP",
        "parser_completed_at": "CURRENT_TIMESTAMP",
        "reconciliation_completed_at": "CURRENT_TIMESTAMP",
    }
    values[column] = "NULL"
    with pytest.raises(IntegrityError):
        db.execute(
            text(
                "INSERT INTO osm_lead_source.source_snapshot "
                "(region_id, source_url, content_sha256, size_bytes, downloaded_at, validated_at, "
                "parser_completed_at, reconciliation_completed_at, completeness_status) "
                "VALUES ('test-region', 'https://example.invalid/invalid.pbf', "
                f"{values['content_sha256']}, {values['size_bytes']}, {values['downloaded_at']}, "
                f"{values['validated_at']}, {values['parser_completed_at']}, "
                f"{values['reconciliation_completed_at']}, 'complete')"
            ),
            {"hash": "a" * 64},
        )


@pytest.mark.database
@pytest.mark.integration
def test_complete_snapshot_rejects_invalid_lifecycle_order(
    db: Connection, seed: dict[str, int]
) -> None:
    del seed
    with pytest.raises(IntegrityError):
        db.execute(
            text(
                "INSERT INTO osm_lead_source.source_snapshot "
                "(region_id, source_url, content_sha256, size_bytes, downloaded_at, validated_at, "
                "parser_completed_at, reconciliation_completed_at, completeness_status) VALUES "
                "('test-region', 'https://example.invalid/order.pbf', :hash, 1, "
                "CURRENT_TIMESTAMP, CURRENT_TIMESTAMP - INTERVAL '1 minute', CURRENT_TIMESTAMP, "
                "CURRENT_TIMESTAMP, 'complete')"
            ),
            {"hash": "b" * 64},
        )


@pytest.mark.database
@pytest.mark.integration
def test_structurally_valid_complete_snapshot_is_accepted(
    db: Connection, seed: dict[str, int]
) -> None:
    snapshot = add_snapshot(db, age_days=1)
    assert (
        db.execute(
            text("SELECT completeness_status FROM osm_lead_source.source_snapshot WHERE id = :id"),
            {"id": snapshot},
        ).scalar_one()
        == "complete"
    )


@pytest.mark.database
@pytest.mark.integration
@pytest.mark.parametrize("status", ["partial", "failed", "unknown"])
def test_noncomplete_snapshots_can_record_incomplete_processing_state(
    db: Connection, seed: dict[str, int], status: str
) -> None:
    snapshot = add_snapshot(db, status=status)
    assert (
        db.execute(
            text("SELECT completeness_status FROM osm_lead_source.source_snapshot WHERE id = :id"),
            {"id": snapshot},
        ).scalar_one()
        == status
    )


@pytest.mark.database
@pytest.mark.integration
def test_initial_present_requires_observation_and_initial_missing_is_exactly_one_snapshot(
    db: Connection, seed: dict[str, int]
) -> None:
    add_observation(db, seed["snapshot"])
    insert_presence(db, snapshot=seed["snapshot"], state="present")

    second = add_snapshot(db, age_days=2)
    db.execute(text("DELETE FROM osm_lead_source.osm_source_presence WHERE osm_id = 123"))
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            insert_presence(
                db,
                snapshot=second,
                state="missing",
                missing=2,
                first_missing_snapshot=second,
                first_missing_at=None,
            )
    insert_presence(
        db,
        snapshot=second,
        state="missing",
        missing=1,
        first_missing_snapshot=second,
        first_missing_at=None,
    )


@pytest.mark.database
@pytest.mark.integration
def test_direct_stale_and_same_snapshot_double_count_are_rejected(
    db: Connection, seed: dict[str, int]
) -> None:
    old_seen_at = datetime.now(UTC) - timedelta(days=61)
    add_observation(db, seed["snapshot"], observed_at=old_seen_at)
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            insert_presence(
                db,
                snapshot=seed["snapshot"],
                state="stale_confirmed",
                missing=2,
                first_missing_snapshot=seed["snapshot"],
                first_missing_at=None,
                last_seen_snapshot=seed["snapshot"],
                last_seen_at=old_seen_at,
            )

    first_missing = add_snapshot(db, age_days=2)
    insert_presence(
        db,
        snapshot=first_missing,
        state="missing",
        missing=1,
        first_missing_snapshot=first_missing,
        first_missing_at=None,
        last_seen_snapshot=seed["snapshot"],
        last_seen_at=old_seen_at,
    )
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            db.execute(
                text(
                    "UPDATE osm_lead_source.osm_source_presence "
                    "SET consecutive_missing_snapshots = 2 WHERE osm_id = 123"
                )
            )


@pytest.mark.database
@pytest.mark.integration
def test_two_distinct_complete_missing_snapshots_prove_stale_and_return_to_present(
    db: Connection, seed: dict[str, int]
) -> None:
    old_seen_at = datetime.now(UTC) - timedelta(days=61)
    add_observation(db, seed["snapshot"], observed_at=old_seen_at)
    first_missing = add_snapshot(db, age_days=2)
    second_missing = add_snapshot(db, age_days=1)
    insert_presence(
        db,
        snapshot=first_missing,
        state="missing",
        missing=1,
        first_missing_snapshot=first_missing,
        first_missing_at=None,
        last_seen_snapshot=seed["snapshot"],
        last_seen_at=old_seen_at,
    )
    db.execute(
        text(
            "UPDATE osm_lead_source.osm_source_presence SET source_presence_state = 'stale_confirmed', "
            "latest_snapshot_id = :latest, consecutive_missing_snapshots = 2 WHERE osm_id = 123"
        ),
        {"latest": second_missing},
    )

    present_snapshot = add_snapshot(db, age_days=0, status="partial")
    present_observed_at = datetime.now(UTC)
    add_observation(db, present_snapshot, observed_at=present_observed_at)
    db.execute(
        text(
            "UPDATE osm_lead_source.osm_source_presence SET source_presence_state = 'present', "
            "latest_snapshot_id = :latest, completeness_status = 'partial', "
            "consecutive_missing_snapshots = 0, first_missing_at = NULL, "
            "first_missing_snapshot_id = NULL, last_seen_snapshot_id = :latest, "
            "last_seen_at = :last_seen_at WHERE osm_id = 123"
        ),
        {"latest": present_snapshot, "last_seen_at": present_observed_at},
    )
    row = db.execute(
        text(
            "SELECT source_presence_state, consecutive_missing_snapshots, first_missing_snapshot_id "
            "FROM osm_lead_source.osm_source_presence WHERE osm_id = 123"
        )
    ).one()
    assert tuple(row) == ("present", 0, None)


@pytest.mark.database
@pytest.mark.integration
@pytest.mark.parametrize("status", ["partial", "failed", "unknown"])
def test_incomplete_absence_and_observation_contradictions_are_rejected(
    db: Connection, seed: dict[str, int], status: str
) -> None:
    add_observation(db, seed["snapshot"])
    incomplete = add_snapshot(db, age_days=2, status=status)
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            insert_presence(
                db,
                snapshot=incomplete,
                state="missing",
                missing=1,
                first_missing_snapshot=incomplete,
                first_missing_at=None,
            )

    complete = add_snapshot(db, age_days=1)
    add_observation(db, complete)
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            insert_presence(
                db,
                snapshot=complete,
                state="missing",
                missing=1,
                first_missing_snapshot=complete,
                first_missing_at=None,
            )


@pytest.mark.database
@pytest.mark.integration
@pytest.mark.parametrize("status", ["partial", "failed", "unknown"])
def test_incomplete_latest_snapshot_cannot_advance_existing_missing_state(
    db: Connection, seed: dict[str, int], status: str
) -> None:
    old_seen_at = datetime.now(UTC) - timedelta(days=61)
    add_observation(db, seed["snapshot"], observed_at=old_seen_at)
    first_missing = add_snapshot(db, age_days=2)
    insert_presence(
        db,
        snapshot=first_missing,
        state="missing",
        missing=1,
        first_missing_snapshot=first_missing,
        first_missing_at=None,
        last_seen_snapshot=seed["snapshot"],
        last_seen_at=old_seen_at,
    )
    later_incomplete = add_snapshot(db, age_days=1, status=status)

    with pytest.raises(DBAPIError):
        with db.begin_nested():
            db.execute(
                text(
                    "UPDATE osm_lead_source.osm_source_presence SET "
                    "latest_snapshot_id = :latest, completeness_status = :status, "
                    "consecutive_missing_snapshots = 2 WHERE osm_id = 123"
                ),
                {"latest": later_incomplete, "status": status},
            )


@pytest.mark.database
@pytest.mark.integration
@pytest.mark.parametrize("status", ["partial", "failed", "unknown"])
@pytest.mark.parametrize("state", ["stale_confirmed", "remap_candidate", "deleted_in_osm"])
def test_incomplete_latest_snapshot_cannot_transition_absence_state(
    db: Connection, seed: dict[str, int], status: str, state: str
) -> None:
    old_seen_at = datetime.now(UTC) - timedelta(days=61)
    add_observation(db, seed["snapshot"], observed_at=old_seen_at)
    first_missing = add_snapshot(db, age_days=2)
    insert_presence(
        db,
        snapshot=first_missing,
        state="missing",
        missing=1,
        first_missing_snapshot=first_missing,
        first_missing_at=None,
        last_seen_snapshot=seed["snapshot"],
        last_seen_at=old_seen_at,
    )
    later_incomplete = add_snapshot(db, age_days=1, status=status)

    with pytest.raises(DBAPIError):
        with db.begin_nested():
            db.execute(
                text(
                    "UPDATE osm_lead_source.osm_source_presence SET "
                    "source_presence_state = :state, latest_snapshot_id = :latest, "
                    "completeness_status = :status, consecutive_missing_snapshots = 2 "
                    "WHERE osm_id = 123"
                ),
                {"state": state, "latest": later_incomplete, "status": status},
            )


@pytest.mark.database
@pytest.mark.integration
def test_remap_and_deleted_states_require_nonambiguous_complete_absence(
    db: Connection, seed: dict[str, int]
) -> None:
    old_seen_at = datetime.now(UTC) - timedelta(days=61)
    add_observation(db, seed["snapshot"], observed_at=old_seen_at)
    first = add_snapshot(db, age_days=2)
    second = add_snapshot(db, age_days=1)
    insert_presence(
        db,
        snapshot=first,
        state="missing",
        missing=1,
        first_missing_snapshot=first,
        first_missing_at=None,
        last_seen_snapshot=seed["snapshot"],
        last_seen_at=old_seen_at,
    )
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            db.execute(
                text(
                    "UPDATE osm_lead_source.osm_source_presence SET latest_snapshot_id = :latest, "
                    "consecutive_missing_snapshots = 2, boundary_ambiguity = true, "
                    "source_presence_state = 'remap_candidate' WHERE osm_id = 123"
                ),
                {"latest": second},
            )


@pytest.mark.database
@pytest.mark.integration
def test_forged_old_last_seen_timestamp_is_rejected(db: Connection, seed: dict[str, int]) -> None:
    actual_seen_at = datetime.now(UTC)
    forged_old_at = actual_seen_at - timedelta(days=61)
    add_observation(db, seed["snapshot"], observed_at=actual_seen_at)
    first_missing = add_snapshot(db, age_days=1)
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            insert_presence(
                db,
                snapshot=first_missing,
                state="missing",
                missing=1,
                first_missing_snapshot=first_missing,
                first_missing_at=None,
                last_seen_snapshot=seed["snapshot"],
                last_seen_at=forged_old_at,
            )


@pytest.mark.database
@pytest.mark.integration
def test_forged_first_missing_timestamp_is_rejected(db: Connection, seed: dict[str, int]) -> None:
    add_observation(db, seed["snapshot"], observed_at=datetime.now(UTC) - timedelta(days=61))
    first_missing = add_snapshot(db, age_days=1)
    with pytest.raises(DBAPIError):
        with db.begin_nested():
            insert_presence(
                db,
                snapshot=first_missing,
                state="missing",
                missing=1,
                first_missing_snapshot=first_missing,
                first_missing_at=datetime.now(UTC),
            )


@pytest.mark.database
@pytest.mark.integration
def test_observation_backed_old_evidence_accepts_valid_stale_confirmation(
    db: Connection, seed: dict[str, int]
) -> None:
    actual_seen_at = datetime.now(UTC) - timedelta(days=61)
    add_observation(db, seed["snapshot"], observed_at=actual_seen_at)
    first_missing = add_snapshot(db, age_days=2)
    second_missing = add_snapshot(db, age_days=1)
    insert_presence(
        db,
        snapshot=first_missing,
        state="missing",
        missing=1,
        first_missing_snapshot=first_missing,
        first_missing_at=None,
        last_seen_snapshot=seed["snapshot"],
        last_seen_at=actual_seen_at,
    )
    db.execute(
        text(
            "UPDATE osm_lead_source.osm_source_presence SET source_presence_state = 'stale_confirmed', "
            "latest_snapshot_id = :latest, consecutive_missing_snapshots = 2 WHERE osm_id = 123"
        ),
        {"latest": second_missing},
    )
    assert (
        db.execute(
            text(
                "SELECT source_presence_state FROM osm_lead_source.osm_source_presence "
                "WHERE osm_id = 123"
            )
        ).scalar_one()
        == "stale_confirmed"
    )


@pytest.mark.database
@pytest.mark.integration
def test_absence_evidence_cannot_be_replaced_during_progression(
    db: Connection, seed: dict[str, int]
) -> None:
    actual_seen_at = datetime.now(UTC) - timedelta(days=61)
    add_observation(db, seed["snapshot"], observed_at=actual_seen_at)
    first_missing = add_snapshot(db, age_days=2)
    second_missing = add_snapshot(db, age_days=1)
    insert_presence(
        db,
        snapshot=first_missing,
        state="missing",
        missing=1,
        first_missing_snapshot=first_missing,
        first_missing_at=None,
        last_seen_snapshot=seed["snapshot"],
        last_seen_at=actual_seen_at,
    )
    statements = [
        "last_seen_at = last_seen_at - INTERVAL '1 day'",
        "last_seen_snapshot_id = :replacement",
        "first_missing_at = first_missing_at - INTERVAL '1 day'",
        "first_missing_snapshot_id = :replacement",
    ]
    for assignment in statements:
        with pytest.raises(DBAPIError):
            with db.begin_nested():
                db.execute(
                    text(
                        "UPDATE osm_lead_source.osm_source_presence SET "
                        f"{assignment}, latest_snapshot_id = :latest, "
                        "consecutive_missing_snapshots = 2 WHERE osm_id = 123"
                    ),
                    {"replacement": second_missing, "latest": second_missing},
                )