"""Transform-test harness: a scratch database on the project's own
Postgres 17 sidecar (SQLite can't host the legacy schema or the
Postgres-only DDL), migrated with the REAL alembic chain, loaded with
the SQL fixture legacy dataset (fixtures/legacy_fixture.sql — never
live data)."""

import os
from pathlib import Path

import pytest
from sqlalchemy import create_engine, text

ROOT = Path(__file__).resolve().parents[2]
FIXTURE_SQL = Path(__file__).parent / "fixtures" / "legacy_fixture.sql"
TEST_DB = "with_migrate_test"

# Fixture ids the tests reference (see legacy_fixture.sql).
ORG = "01000000-0000-0000-0000-000000000001"
U_RIAN = "02000000-0000-0000-0000-000000000001"
U_ADI = "02000000-0000-0000-0000-000000000002"
U_GHOST = "02000000-0000-0000-0000-000000000003"
TM_RIAN_1 = "04000000-0000-0000-0000-000000000001"
TM_RIAN_2 = "04000000-0000-0000-0000-000000000002"
TM_ADI = "04000000-0000-0000-0000-000000000003"
TM_GARY = "04000000-0000-0000-0000-000000000004"
TM_MATT = "04000000-0000-0000-0000-000000000005"
PR_SITE = "07000000-0000-0000-0000-000000000001"
PR_ADMIN = "07000000-0000-0000-0000-000000000002"
PR_BLOCK = "07000000-0000-0000-0000-000000000003"
IMP_1 = "09000000-0000-0000-0000-000000000001"
IMP_3 = "09000000-0000-0000-0000-000000000003"
TE = {n: f"0a000000-0000-0000-0000-00000000000{n}" for n in range(1, 8)}
INV_MAY = "0b000000-0000-0000-0000-000000000001"
INV_LEGACY = "0b000000-0000-0000-0000-000000000002"
INV_OPEN = "0b000000-0000-0000-0000-000000000003"
RO_ABS = "08000000-0000-0000-0000-000000000001"
RO_PCT = "08000000-0000-0000-0000-000000000002"


def _sidecar_url() -> str:
    from app.config import settings

    url = settings.with_db_url_host or settings.with_db_url
    if not url:
        pytest.fail(
            "transform tests need the sidecar Postgres — set WITH_DB_URL_HOST"
            " in .env (127.0.0.1:54317)"
        )
    return url


@pytest.fixture(scope="session")
def mig_url() -> str:
    """Fresh scratch database, migrated to alembic head."""
    base = _sidecar_url()
    admin = create_engine(base, isolation_level="AUTOCOMMIT",
                          connect_args={"connect_timeout": 3})
    try:
        with admin.connect() as conn:
            conn.execute(text(f"DROP DATABASE IF EXISTS {TEST_DB} WITH (FORCE)"))
            conn.execute(text(f"CREATE DATABASE {TEST_DB}"))
    except Exception as exc:  # sidecar down beats a cryptic stack
        pytest.fail(f"cannot reach the sidecar Postgres for transform tests: {exc}")
    finally:
        admin.dispose()

    url = base.rsplit("/", 1)[0] + f"/{TEST_DB}"

    from alembic.config import Config

    from alembic import command

    cfg = Config(str(ROOT / "alembic.ini"))
    cfg.set_main_option("script_location", str(ROOT / "alembic"))
    previous = os.environ.get("WITH_DB_URL")
    os.environ["WITH_DB_URL"] = url  # alembic/env.py reads this first
    try:
        command.upgrade(cfg, "head")
    finally:
        os.environ["WITH_DB_URL"] = previous if previous is not None else ""
    return url


@pytest.fixture(scope="session")
def mig_engine(mig_url):
    engine = create_engine(mig_url)
    yield engine
    engine.dispose()


@pytest.fixture(scope="session")
def legacy_loaded(mig_engine):
    """Load (or reload) the fixture legacy schema."""
    sql = FIXTURE_SQL.read_text(encoding="utf-8")
    with mig_engine.begin() as conn:
        conn.exec_driver_sql(sql)
    return True


@pytest.fixture(scope="session")
def clean_report(mig_engine, legacy_loaded):
    """The canonical clean transform run every read-only test asserts on."""
    from app.services.migrate import run_all

    report = run_all(engine=mig_engine)
    assert report.ok, report.render()
    return report


@pytest.fixture()
def reload_fixture(mig_engine):
    """Function-scoped full legacy reset for tests that mutate legacy."""

    def _reload():
        with mig_engine.begin() as conn:
            conn.exec_driver_sql(FIXTURE_SQL.read_text(encoding="utf-8"))

    return _reload


def extract_both(mig_url: str) -> tuple[dict, dict]:
    """Run both harness extractors in-process against the scratch db."""
    import psycopg

    from harness import extract_legacy, extract_new

    dsn = mig_url.replace("postgresql+psycopg://", "postgresql://", 1)
    with psycopg.connect(dsn) as conn:
        old_doc = extract_legacy.extract(conn, "legacy")
    with psycopg.connect(dsn) as conn:
        new_doc = extract_new.extract(conn)
    return old_doc, new_doc


def scalar(engine, sql: str, **params):
    with engine.connect() as conn:
        return conn.execute(text(sql), params).scalar()


def rows(engine, sql: str, **params):
    with engine.connect() as conn:
        return conn.execute(text(sql), params).fetchall()