"""Reporting-test harness: its OWN scratch database on the sidecar
Postgres (name `with_reporting_test`, independent of the transform
tests' scratch DB), migrated with the real alembic chain, loaded with
the fixture legacy dataset, and transformed once — the reporting
services then run over the same migrated shape production has.

Plus the REAL sidecar (read-only) for the T-RPT oracle-equivalence
tests against harness/out/e2e-new.json.
"""

import os
from pathlib import Path

import pytest
from sqlalchemy import create_engine, select, text
from sqlalchemy.orm import Session

from app.models import User
from app.services.authz.context import resolve_actor
from tests.authz.conftest import fake_request

ROOT = Path(__file__).resolve().parents[2]
FIXTURE_SQL = ROOT / "tests" / "migrate" / "fixtures" / "legacy_fixture.sql"
TEST_DB = "with_reporting_test"


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(
            "reporting tests need the sidecar Postgres — set WITH_DB_URL_HOST"
            " in .env (127.0.0.1:54317)"
        )
    return url


@pytest.fixture(scope="session")
def rpt_url() -> str:
    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:
        pytest.fail(f"cannot reach the sidecar Postgres for reporting 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
    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 rpt_engine(rpt_url):
    engine = create_engine(rpt_url)

    # load fixture legacy + run the transforms ONCE
    with engine.begin() as conn:
        conn.exec_driver_sql(FIXTURE_SQL.read_text(encoding="utf-8"))
    from app.services.migrate import run_all

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

    yield engine
    engine.dispose()


@pytest.fixture()
def rpt_session(rpt_engine):
    with Session(rpt_engine) as session:
        yield session


def actor_named(session: Session, username: str, cookies: dict | None = None):
    """Resolve a REAL actor from the migrated users/engagement graph."""
    return resolve_actor(fake_request(username, cookies), session)


@pytest.fixture()
def rian(rpt_session):
    return actor_named(rpt_session, "rian")


@pytest.fixture()
def adi(rpt_session):
    return actor_named(rpt_session, "adi")


# ---------------------------------------------------------- real sidecar

@pytest.fixture(scope="session")
def real_engine():
    """The REAL migrated sidecar data, read-only at the session level —
    the T-RPT equivalence oracle target."""
    engine = create_engine(
        _sidecar_url(),
        connect_args={
            "connect_timeout": 3,
            "options": "-c default_transaction_read_only=on",
        },
    )
    yield engine
    engine.dispose()


@pytest.fixture()
def real_session(real_engine):
    with Session(real_engine) as session:
        yield session


@pytest.fixture()
def real_rian(real_session):
    actor = actor_named(real_session, "rian")
    assert actor.role == "owner" and actor.real_is_super_admin
    return actor


def user_id(session: Session, username: str):
    return session.scalar(select(User.id).where(User.idauth_username == username))