"""Fixtures bounded to the disposable local/CI PostGIS service."""

from __future__ import annotations

from collections.abc import Iterator
from datetime import datetime

import pytest
from sqlalchemy import Connection, create_engine, text
from sqlalchemy.engine import Engine

from osm_lead_source_service.sidecar.migration_config import load_test_migration_settings


def add_snapshot(
    db: Connection,
    *,
    region: str = "test-region",
    age_days: int = 0,
    status: str = "complete",
) -> int:
    if status == "complete":
        return db.execute(
            text(
                "INSERT INTO osm_lead_source.source_snapshot "
                "(region_id, source_url, content_sha256, size_bytes, completeness_status, "
                "downloaded_at, validated_at, parser_completed_at, reconciliation_completed_at) "
                "VALUES (:region, 'https://example.invalid/test.pbf', :hash, 1, 'complete', "
                "CURRENT_TIMESTAMP - (:age_days * INTERVAL '1 day'), "
                "CURRENT_TIMESTAMP - (:age_days * INTERVAL '1 day') + INTERVAL '1 hour', "
                "CURRENT_TIMESTAMP - (:age_days * INTERVAL '1 day') + INTERVAL '2 hours', "
                "CURRENT_TIMESTAMP - (:age_days * INTERVAL '1 day') + INTERVAL '3 hours') RETURNING id"
            ),
            {"region": region, "age_days": age_days, "hash": f"{age_days + 1:064x}"},
        ).scalar_one()
    return db.execute(
        text(
            "INSERT INTO osm_lead_source.source_snapshot "
            "(region_id, source_url, completeness_status) "
            "VALUES (:region, 'https://example.invalid/test.pbf', :status) RETURNING id"
        ),
        {"region": region, "status": status},
    ).scalar_one()


def add_observation(
    db: Connection,
    snapshot: int,
    osm_id: int = 123,
    osm_type: str = "node",
    observed_at: datetime | None = None,
) -> None:
    db.execute(
        text(
            "INSERT INTO osm_lead_source.source_snapshot_object "
            "(source_snapshot_id, osm_type, osm_id, object_version, source_payload_hash, observed_at) "
            "VALUES (:snapshot, :osm_type, :osm_id, 1, :hash, COALESCE(:observed_at, CURRENT_TIMESTAMP))"
        ),
        {
            "snapshot": snapshot,
            "osm_type": osm_type,
            "osm_id": osm_id,
            "hash": f"{snapshot:064x}",
            "observed_at": observed_at,
        },
    )


@pytest.fixture(scope="session")
def sidecar_engine() -> Iterator[Engine]:
    settings = load_test_migration_settings()
    engine = create_engine(settings.database_url, pool_pre_ping=True)
    with engine.connect() as connection:
        connection.execute(text("SELECT 1 FROM osm_lead_source.source_region WHERE false"))
    try:
        yield engine
    finally:
        engine.dispose()


@pytest.fixture()
def db(sidecar_engine: Engine) -> Iterator[Connection]:
    with sidecar_engine.connect() as connection:
        transaction = connection.begin()
        try:
            yield connection
        finally:
            transaction.rollback()


@pytest.fixture()
def seed(db: Connection) -> dict[str, int]:
    db.execute(
        text(
            "INSERT INTO osm_lead_source.source_region "
            "(region_id, display_name, extract_name) VALUES ('test-region', 'Test Region', 'test')"
        )
    )
    snapshot = add_snapshot(db, age_days=3)
    db.execute(
        text(
            "INSERT INTO osm_lead_source.osm_source_object "
            "(osm_type, osm_id, current_version) VALUES ('node', 123, 1)"
        )
    )
    return {"snapshot": snapshot}