"""Database check for the Cloud projection source (Workstream E1). Needs the scratch URL."""

import json
import os
from uuid import uuid4

import pytest
import pytest_asyncio
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from src.domain.project_auth import permissions as perms
from src.domain.project_auth.cloud_projection import build_project_projections
from src.domain.project_auth.cloud_projection_source import load_project_snapshot
from src.domain.project_auth.membership_projection import reconcile_project_memberships
from src.domain.project_auth.staff_directory import WorkspaceAppIdentity
from src.domain.projects import models as _project_models  # noqa: F401
from src.domain.workspaces import models as _workspace_models  # noqa: F401

URL = os.environ.get("P2HS_AUTHZ_TEST_DATABASE_URL", "")
pytestmark = pytest.mark.skipif(not URL, reason="P2HS_AUTHZ_TEST_DATABASE_URL not set")


@pytest_asyncio.fixture
async def db():
    engine = create_async_engine(URL)
    async with engine.connect() as conn:
        trans = await conn.begin()
        session = AsyncSession(bind=conn, expire_on_commit=False)
        try:
            yield session
        finally:
            await session.close()
            await trans.rollback()
    await engine.dispose()


@pytest.mark.asyncio
async def test_loaded_state_projects_owner_admin_and_suspended_correctly(db):
    ws, project = uuid4(), uuid4()
    await db.execute(text("insert into workspaces (id, name, slug) values (:id, 'ws', :s)"),
                     {"id": ws, "s": f"ws-{ws}"})
    ptype = (await db.execute(text(
        "select enumlabel from pg_enum e join pg_type t on t.oid = e.enumtypid "
        "where t.typname = (select udt_name from information_schema.columns "
        "where table_name = 'projects' and column_name = 'type') order by enumsortorder limit 1"
    ))).scalar_one()
    await db.execute(text(f"insert into projects (id, name, slug, type, workspace_id) "
                          f"values (:id, 'p', :s, '{ptype}', :ws)"),
                     {"id": project, "s": f"p-{project}", "ws": ws})
    await db.execute(text(
        "insert into project_auth_roles (id, project_id, name, created_by, permissions_json, "
        "assignment_tier, protected) values "
        "(:id, :p, 'super_admin', 'codegen', cast(:perms as jsonb), "
        "NULL, true)"),
        {"id": uuid4(), "p": project, "perms": json.dumps(list(perms.OWNER_PRESET))})
    await db.execute(text(
        "insert into project_auth_roles (id, project_id, name, created_by, permissions_json, "
        "assignment_tier, protected) values (:id, :p, 'admin', 'codegen', cast(:perms as jsonb), "
        "'admin', false)"),
        {"id": uuid4(), "p": project, "perms": json.dumps(list(perms.ADMIN_PRESET))})

    def ws_identity(email, *, owner=False):
        if owner:
            return WorkspaceAppIdentity(email, "", "owner", "super_admin", True)
        return WorkspaceAppIdentity(email, "", "admin", "admin", False)

    await reconcile_project_memberships(db, project, [
        ws_identity("owner@x.test", owner=True),
        ws_identity("admin@x.test"),
        ws_identity("held@x.test"),
    ])
    held = (await db.execute(text(
        "select id from project_memberships where project_id = :p "
        "and email_normalized = 'held@x.test'"),
        {"p": project})).scalar_one()
    await db.execute(text(
        "insert into project_membership_suspensions (membership_id, source, reason_code) "
        "values (:m, 'local', 'review')"), {"m": held})

    snapshot = await load_project_snapshot(db, project)
    projections = {p.email: p for p in build_project_projections(snapshot)}

    owner = projections["owner@x.test"]
    assert owner.is_owner and owner.legacy_role == "super_admin"
    assert set(perms.OWNER_PRESET) <= set(owner.project_permissions)

    admin = projections["admin@x.test"]
    assert admin.project_roles == ("admin",) and admin.legacy_role == "admin"

    suspended = projections["held@x.test"]
    assert suspended.status == "suspended" and suspended.project_permissions == ()
    assert suspended.legacy_role == "member"