"""Grant rosters for the reused-row types, and the approval-chain lookup.

Two claims carry the weight here:

* A cloud grant that was revoked and re-requested is **several**
  holdings, not one. The row is reused under
  ``UNIQUE(scope_id, person_id, role_key)``, so a roster reading the
  live columns reports the last cycle and silently loses the rest.
* Cloud grants have **no** ``approval_request_id`` — they are decided in
  Helix's own inbox — so their authority record is an audit row. That is
  an equivalent citation, not a degraded one, and the evidence must say
  which path it came from rather than flatten the two together.
"""
from __future__ import annotations

import uuid
from datetime import UTC, datetime, timedelta

import pytest
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from app.approval.models import ApprovalRequest, ApprovalStep
from app.audit.models import AuditLog
from app.core.access_cycle_listener import register_listener
from app.domains.asset.models import AssetSoftwareCatalog
from app.domains.auditor.evidence_query.service import (
    list_bundle_rows,
    run_evidence_query,
)
from app.domains.cmdb.models import CmdbCloudAccessGrant, CmdbCloudScope
from app.domains.permission.models import (
    PERM_ACTIVE,
    PERM_PENDING,
    PERM_REVOKED,
    SoftwarePermission,
    UserPermission,
)
from app.integrations.models import Integration

register_listener()


async def _payloads(db: AsyncSession, bundle_id) -> list[dict]:
    rows, _total = await list_bundle_rows(db, bundle_id, page_size=200)
    return [r.payload for r in rows]


async def _mk_scope(db: AsyncSession) -> CmdbCloudScope:
    integration = Integration(
        id=str(uuid.uuid4()),
        kind="cloud",
        provider="aws",
        name="evidence test",
        slug=f"ev-{uuid.uuid4().hex[:8]}",
        enabled=True,
    )
    db.add(integration)
    await db.flush()
    scope = CmdbCloudScope(
        id=str(uuid.uuid4()),
        integration_id=str(integration.id),
        scope_type="aws_account",
        external_id=uuid.uuid4().hex[:12],
        display_name=f"Evidence scope {uuid.uuid4().hex[:4]}",
        status="active",
    )
    db.add(scope)
    await db.flush()
    return scope


# ── cloud grants: the resurrected row ─────────────────────────────────


@pytest.mark.asyncio
async def test_resurrected_cloud_grant_yields_one_row_per_holding(
    db_session, employee_user, admin_user,
):
    """The defect this handler exists for.

    One grant row, three holdings. Reading ``granted_at`` / ``revoked_at``
    off the live row reports only the most recent one — and an auditor
    sampling "who had prod access in March" would be told nobody did.
    """
    employee, _ = employee_user
    admin, _ = admin_user
    scope = await _mk_scope(db_session)

    grant = CmdbCloudAccessGrant(
        scope_id=scope.id,
        person_id=str(employee.id),
        role_key="AdministratorAccess",
        role_display_name="Administrator",
        status="active",
        granted_at=datetime(2026, 7, 1, tzinfo=UTC),
        created_at=datetime(2026, 1, 1, tzinfo=UTC),
        granted_by_person_id=str(admin.id),
        source="helix",
        detail={
            "_resurrection_history": [
                # First cycle ran 2026-01-01 → 2026-03-01.
                {
                    "at": "2026-04-01T00:00:00+00:00",
                    "from_status": "revoked",
                    "from_revoked_at": "2026-03-01T00:00:00+00:00",
                    "from_revoke_reason": "offboarding",
                },
                # Second ran 2026-04-01 → 2026-06-01.
                {
                    "at": "2026-07-01T00:00:00+00:00",
                    "from_status": "revoked",
                    "from_revoked_at": "2026-06-01T00:00:00+00:00",
                    "from_revoke_reason": "expired",
                },
            ],
        },
    )
    db_session.add(grant)
    await db_session.flush()

    bundle = await run_evidence_query(
        db_session, query_key="cloud_grant_roster",
        params={
            "scope_id": scope.id,
            "period_start": "2026-01-01",
            "period_end": "2026-12-31",
        },
        actor_user_id=admin.id,
    )
    payloads = await _payloads(db_session, bundle.id)
    assert len(payloads) == 3, "one row per holding, not per grant row"
    assert [p["cycle"] for p in payloads] == [
        "historical", "historical", "current",
    ]
    assert payloads[0]["revoke_reason"] == "offboarding"
    assert payloads[2]["still_held"] is True

    # The reconstruction is declared, because the reconstructed start
    # times come from adjacent history entries rather than a column.
    assert any(
        gap["code"] == "cloud_grant_cycles_reconstructed_from_json"
        for gap in bundle.data_gaps
    )


@pytest.mark.asyncio
async def test_march_window_finds_only_the_first_cloud_holding(
    db_session, employee_user, admin_user,
):
    """The concrete audit question the live row answers wrongly."""
    employee, _ = employee_user
    admin, _ = admin_user
    scope = await _mk_scope(db_session)
    db_session.add(CmdbCloudAccessGrant(
        scope_id=scope.id,
        person_id=str(employee.id),
        role_key="AdministratorAccess",
        status="active",
        granted_at=datetime(2026, 7, 1, tzinfo=UTC),
        created_at=datetime(2026, 1, 1, tzinfo=UTC),
        source="helix",
        detail={
            "_resurrection_history": [
                {
                    "at": "2026-04-01T00:00:00+00:00",
                    "from_status": "revoked",
                    "from_revoked_at": "2026-03-01T00:00:00+00:00",
                },
            ],
        },
    ))
    await db_session.flush()

    bundle = await run_evidence_query(
        db_session, query_key="cloud_grant_roster",
        params={
            "scope_id": scope.id,
            "period_start": "2026-02-01",
            "period_end": "2026-02-28",
        },
        actor_user_id=admin.id,
    )
    payloads = await _payloads(db_session, bundle.id)
    assert len(payloads) == 1
    assert payloads[0]["cycle"] == "historical"


@pytest.mark.asyncio
async def test_cloud_window_after_all_holdings_is_empty(
    db_session, employee_user, admin_user,
):
    employee, _ = employee_user
    admin, _ = admin_user
    scope = await _mk_scope(db_session)
    db_session.add(CmdbCloudAccessGrant(
        scope_id=scope.id,
        person_id=str(employee.id),
        role_key="ReadOnly",
        status="revoked",
        granted_at=datetime(2025, 1, 1, tzinfo=UTC),
        revoked_at=datetime(2025, 3, 1, tzinfo=UTC),
        created_at=datetime(2025, 1, 1, tzinfo=UTC),
        source="helix",
    ))
    await db_session.flush()

    bundle = await run_evidence_query(
        db_session, query_key="cloud_grant_roster",
        params={
            "scope_id": scope.id,
            "period_start": "2026-01-01",
            "period_end": "2026-12-31",
        },
        actor_user_id=admin.id,
    )
    assert bundle.row_count == 0


# ── software permissions: the interval ledger ─────────────────────────


@pytest.mark.asyncio
async def test_permission_roster_separates_the_two_holdings(
    db_session, employee_user, admin_user,
):
    employee, _ = employee_user
    admin, _ = admin_user
    software = AssetSoftwareCatalog(name=f"PermSW {uuid.uuid4().hex[:6]}")
    db_session.add(software)
    await db_session.flush()
    perm = SoftwarePermission(
        software_id=software.id,
        permission_key=f"k_{uuid.uuid4().hex[:8]}",
        display_name="Admin console",
    )
    db_session.add(perm)
    await db_session.flush()

    row = UserPermission(
        user_id=employee.id, permission_id=perm.id, status=PERM_PENDING,
    )
    db_session.add(row)
    await db_session.flush()

    row.status = PERM_ACTIVE
    row.granted_at = datetime.now(UTC) - timedelta(days=60)
    await db_session.flush()
    row.status = PERM_REVOKED
    row.revoked_at = datetime.now(UTC) - timedelta(days=40)
    await db_session.flush()
    # Re-request resets the SAME row in place.
    row.status = PERM_PENDING
    row.granted_at = None
    row.revoked_at = None
    await db_session.flush()
    row.status = PERM_ACTIVE
    row.granted_at = datetime.now(UTC) - timedelta(days=5)
    await db_session.flush()

    bundle = await run_evidence_query(
        db_session, query_key="software_permission_roster",
        params={"permission_id": str(perm.id)},
        actor_user_id=admin.id,
    )
    payloads = await _payloads(db_session, bundle.id)
    assert len(payloads) == 2, (
        "the live user_permissions row shows only the current holding"
    )
    assert payloads[0]["still_held"] is False
    assert payloads[1]["still_held"] is True


@pytest.mark.asyncio
async def test_permission_request_that_never_activated_is_not_access(
    db_session, employee_user, admin_user,
):
    """A pending request is not a holding. Counting it would overstate
    who had access — the direction of error an audit is looking for."""
    employee, _ = employee_user
    admin, _ = admin_user
    software = AssetSoftwareCatalog(name=f"PermSW {uuid.uuid4().hex[:6]}")
    db_session.add(software)
    await db_session.flush()
    perm = SoftwarePermission(
        software_id=software.id,
        permission_key=f"k_{uuid.uuid4().hex[:8]}",
        display_name="Never granted",
    )
    db_session.add(perm)
    await db_session.flush()
    db_session.add(UserPermission(
        user_id=employee.id, permission_id=perm.id, status=PERM_PENDING,
    ))
    await db_session.flush()

    bundle = await run_evidence_query(
        db_session, query_key="software_permission_roster",
        params={"permission_id": str(perm.id)},
        actor_user_id=admin.id,
    )
    assert bundle.row_count == 0


# ── approval chain: two authority paths ───────────────────────────────


@pytest.mark.asyncio
async def test_approval_chain_returns_approver_and_decision_time(
    db_session, employee_user, admin_user,
):
    employee, _ = employee_user
    admin, _ = admin_user
    request = ApprovalRequest(
        external_id=f"ext-{uuid.uuid4().hex[:10]}",
        approval_type="software_seat",
        applicant_id=employee.id,
        status="approved",
    )
    db_session.add(request)
    await db_session.flush()
    decided = datetime.now(UTC) - timedelta(days=3)
    db_session.add(ApprovalStep(
        approval_id=request.id,
        step_name="manager",
        approver_id=admin.id,
        status="approved",
        acted_at=decided,
    ))
    # A step nobody has acted on yet must not read as authorisation.
    db_session.add(ApprovalStep(
        approval_id=request.id,
        step_name="it_admin",
        approver_id=admin.id,
        status="pending",
    ))
    await db_session.flush()

    bundle = await run_evidence_query(
        db_session, query_key="approval_chain",
        params={"grant_id": str(request.id)},
        actor_user_id=admin.id,
    )
    payloads = await _payloads(db_session, bundle.id)
    assert len(payloads) == 1
    assert payloads[0]["authority_path"] == "approval_request"
    assert payloads[0]["decision"] == "approved"
    assert payloads[0]["approver"]
    assert payloads[0]["step_name"] == "manager"

    # Called directly, with no parameters at all, so the assertion rests
    # on the handler's own ``acted_at IS NOT NULL`` predicate rather than
    # on the service auto-filling ``period_end`` (which would exclude a
    # NULL ``acted_at`` incidentally and make this look proven when it
    # was not — the version of this test that went through the service
    # stayed green under mutation).
    from app.domains.auditor.evidence_query.handlers.approval_chain import (
        approval_chain as run_handler,
    )
    direct = await run_handler(db_session, {"grant_id": str(request.id)})
    assert len(direct.rows) == 1, (
        "a step nobody has acted on is a queue entry, not authorisation; "
        "counting it would overstate the control's effectiveness"
    )


@pytest.mark.asyncio
async def test_cloud_grant_authority_comes_from_the_audit_trail(
    db_session, employee_user, admin_user,
):
    """Cloud grants never create an approval_requests row.

    Their decision lives only in ``cloud.grant.approve``. The evidence
    row must cite the audit entry (which carries actor, time and trace)
    rather than point at the grant row, which records no decision.
    """
    employee, _ = employee_user
    admin, _ = admin_user
    grant_id = str(uuid.uuid4())
    trace = uuid.uuid4()
    db_session.add(AuditLog(
        trace_id=trace,
        actor_user_id=admin.id,
        actor_type="user",
        action="cloud.grant.approve",
        status="success",
        resource_type="cmdb_cloud_access_grant",
        resource_id=grant_id,
        detail={"role_key": "AdministratorAccess"},
    ))
    await db_session.flush()

    bundle = await run_evidence_query(
        db_session, query_key="approval_chain",
        params={"grant_id": grant_id},
        actor_user_id=admin.id,
    )
    rows, _ = await list_bundle_rows(db_session, bundle.id)
    assert len(rows) == 1
    assert rows[0].audit_log_id is not None, (
        "the audit row IS the authority record; citing the grant row "
        "would point at something that does not carry the decision"
    )
    assert rows[0].source_trace_id == trace
    assert rows[0].payload["authority_path"] == "audit_log"
    assert rows[0].payload["decision"] == "approved"

    assert any(
        gap["code"] == "cloud_grant_authority_from_audit_trail"
        for gap in bundle.data_gaps
    ), "the reader must be told why there is no approval chain to show"


@pytest.mark.asyncio
async def test_approval_chain_period_covers_both_paths(
    db_session, employee_user, admin_user,
):
    employee, _ = employee_user
    admin, _ = admin_user
    decided = datetime.now(UTC) - timedelta(days=2)

    request = ApprovalRequest(
        external_id=f"ext-{uuid.uuid4().hex[:10]}",
        approval_type="sso",
        applicant_id=employee.id,
        status="approved",
    )
    db_session.add(request)
    await db_session.flush()
    db_session.add(ApprovalStep(
        approval_id=request.id,
        step_name="owner",
        approver_id=admin.id,
        status="approved",
        acted_at=decided,
    ))
    db_session.add(AuditLog(
        trace_id=uuid.uuid4(),
        actor_user_id=admin.id,
        actor_type="user",
        action="cloud.grant.reject",
        status="success",
        resource_type="cmdb_cloud_access_grant",
        resource_id=str(uuid.uuid4()),
        detail={},
    ))
    await db_session.flush()

    bundle = await run_evidence_query(
        db_session, query_key="approval_chain",
        params={
            "period_start": (decided - timedelta(days=5)).date().isoformat(),
        },
        actor_user_id=admin.id,
    )
    paths = {p["authority_path"] for p in await _payloads(db_session, bundle.id)}
    assert paths == {"approval_request", "audit_log"}
    # Refusals are authority events too — an auditor tracing "who
    # decided" needs the rejections, not only the approvals.
    decisions = {
        p["decision"] for p in await _payloads(db_session, bundle.id)
    }
    assert "rejected" in decisions


@pytest.mark.asyncio
async def test_failed_audit_rows_are_not_decisions(
    db_session, admin_user,
):
    """``status='failed'`` means the approve attempt did not land."""
    admin, _ = admin_user
    grant_id = str(uuid.uuid4())
    db_session.add(AuditLog(
        trace_id=uuid.uuid4(),
        actor_user_id=admin.id,
        actor_type="user",
        action="cloud.grant.approve",
        status="failed",
        resource_type="cmdb_cloud_access_grant",
        resource_id=grant_id,
        detail={"error": "driver call failed"},
    ))
    await db_session.flush()

    bundle = await run_evidence_query(
        db_session, query_key="approval_chain",
        params={"grant_id": grant_id},
        actor_user_id=admin.id,
    )
    assert bundle.row_count == 0


@pytest.mark.asyncio
async def test_every_new_query_is_registered_and_runnable(
    db_session, admin_user,
):
    """Smoke every registered query end to end.

    A handler that raises on an empty database would only be discovered
    by the first auditor to ask that question during an audit.
    """
    admin, _ = admin_user
    from app.domains.auditor.evidence_query.catalog import EVIDENCE_QUERIES

    for definition in EVIDENCE_QUERIES:
        # Fill EVERY required parameter, whatever its kind. Filling only
        # the kinds this test happened to know about is how a newly
        # registered query slips through unexercised.
        params = {
            p.name: str(uuid.uuid4())
            for p in definition.params
            if p.required
        }
        bundle = await run_evidence_query(
            db_session, query_key=definition.key,
            params=params, actor_user_id=admin.id,
        )
        assert bundle.query_version == definition.version
        assert bundle.content_hash


@pytest.mark.asyncio
async def test_bundle_rows_survive_deletion_of_the_approval(
    db_session, employee_user, admin_user,
):
    employee, _ = employee_user
    admin, _ = admin_user
    request = ApprovalRequest(
        external_id=f"ext-{uuid.uuid4().hex[:10]}",
        approval_type="apikey",
        applicant_id=employee.id,
        status="approved",
    )
    db_session.add(request)
    await db_session.flush()
    step = ApprovalStep(
        approval_id=request.id,
        step_name="finance",
        approver_id=admin.id,
        status="approved",
        acted_at=datetime.now(UTC) - timedelta(days=1),
    )
    db_session.add(step)
    await db_session.flush()

    bundle = await run_evidence_query(
        db_session, query_key="approval_chain",
        params={"grant_id": str(request.id)},
        actor_user_id=admin.id,
    )
    await db_session.delete(step)
    await db_session.delete(request)
    await db_session.flush()

    payloads = await _payloads(db_session, bundle.id)
    assert len(payloads) == 1
    assert payloads[0]["approver"]


@pytest.mark.asyncio
async def test_approval_steps_are_matched_without_a_cross_type_join(
    db_session, employee_user, admin_user,
):
    """Regression guard for the asyncmy silent-zero-rows trap.

    ``software_seat_allocations.approval_request_id`` is a bare Uuid with
    no FK, and person ids are ``String(36)`` in some tables. Joining
    across that boundary returns zero rows with no error, which would
    make an approval chain look empty rather than broken.
    """
    employee, _ = employee_user
    admin, _ = admin_user
    request = ApprovalRequest(
        external_id=f"ext-{uuid.uuid4().hex[:10]}",
        approval_type="software_seat",
        applicant_id=employee.id,
        status="approved",
    )
    db_session.add(request)
    await db_session.flush()
    db_session.add(ApprovalStep(
        approval_id=request.id,
        step_name="manager",
        approver_id=admin.id,
        status="approved",
        acted_at=datetime.now(UTC),
    ))
    await db_session.flush()

    bundle = await run_evidence_query(
        db_session, query_key="approval_chain",
        params={"approval_type": "software_seat"},
        actor_user_id=admin.id,
    )
    assert bundle.row_count >= 1
    rows, _ = await list_bundle_rows(db_session, bundle.id)
    assert all(r.subject_label for r in rows), (
        "labels resolved via a Python map, not a SQL join"
    )


@pytest.mark.asyncio
async def test_audit_log_written_for_each_new_query(db_session, admin_user):
    admin, _ = admin_user
    bundle = await run_evidence_query(
        db_session, query_key="cloud_grant_roster",
        params={"scope_id": "nonexistent-scope"},
        actor_user_id=admin.id,
    )
    log = (
        await db_session.execute(
            select(AuditLog).where(
                AuditLog.action == "auditor.evidence_query.run",
                AuditLog.resource_id == str(bundle.id),
            )
        )
    ).scalar_one()
    assert log.detail["query_key"] == "cloud_grant_roster"