from __future__ import annotations

import uuid
from datetime import UTC, datetime

import pytest
from sqlalchemy import select
from sqlalchemy.exc import IntegrityError

from app.domains.data_access import constants as c
from app.domains.data_access.models import (
    BINDING_STATUS_ACTIVE,
    BINDING_STATUS_REVOKED,
    ENTITLEMENT_STATUS_ACTIVE,
    ENTITLEMENT_VERSION_V1,
    OWNER_RESOLUTION_RESOLVED,
    OWNER_SOURCE_BOOTSTRAP,
    REQUEST_ITEM_STATUS_PENDING,
    REQUEST_STATUS_PENDING_FEISHU,
    RESOURCE_STATUS_ACTIVE,
    DataAccessBinding,
    DataAccessEntitlement,
    DataAccessRequest,
    DataAccessRequestItem,
    DataAccessResource,
)
from app.domains.email_group.models import EmailGroup
from app.integrations.models import Integration


async def _integration_id(db_session) -> str:
    row = Integration(
        kind=c.DOMAIN_DATA_ACCESS,
        provider=c.PROVIDER_BIGQUERY,
        name="Data Access BigQuery",
        slug=f"dax-bq-{uuid.uuid4().hex[:8]}",
        enabled=True,
        is_primary=False,
    )
    db_session.add(row)
    await db_session.flush()
    return str(row.id)


def _entitlement() -> DataAccessEntitlement:
    return DataAccessEntitlement(
        domain=c.DOMAIN_DATA_ACCESS,
        provider=c.PROVIDER_BIGQUERY,
        resource_type=c.RESOURCE_TYPE_DATASET,
        principal_type=c.PRINCIPAL_TYPE_GOOGLE_GROUP,
        entitlement=c.ENTITLEMENT_READ,
        version=ENTITLEMENT_VERSION_V1,
        provider_member_prefix=c.BIGQUERY_GROUP_MEMBER_PREFIX,
        provider_role=c.BIGQUERY_DATA_VIEWER_ROLE,
        requestable=True,
        status=ENTITLEMENT_STATUS_ACTIVE,
    )


def _resource(
    *,
    integration_id: str,
    owner_person_id: uuid.UUID,
    dataset_id: str = "finance",
) -> DataAccessResource:
    return DataAccessResource(
        integration_id=integration_id,
        provider=c.PROVIDER_BIGQUERY,
        resource_type=c.RESOURCE_TYPE_DATASET,
        project_id="helix-data-prod",
        dataset_id=dataset_id,
        display_name=f"helix-data-prod.{dataset_id}",
        requestable=True,
        status=RESOURCE_STATUS_ACTIVE,
        owner_person_id=str(owner_person_id),
        owner_resolution_status=OWNER_RESOLUTION_RESOLVED,
        owner_source=OWNER_SOURCE_BOOTSTRAP,
        source_version="bootstrap-2026-06-10",
        source_etag=f"etag-{dataset_id}",
    )


def _request(
    *,
    requester_id: uuid.UUID,
    group: EmailGroup,
    entitlement: DataAccessEntitlement,
    resource: DataAccessResource,
) -> tuple[DataAccessRequest, DataAccessRequestItem]:
    item = DataAccessRequestItem(
        resource_id=resource.id,
        line_no=1,
        resource_project_id_snapshot=resource.project_id,
        resource_dataset_id_snapshot=resource.dataset_id,
        resource_source_version_snapshot=resource.source_version,
        resource_source_etag_snapshot=resource.source_etag,
        resource_owner_person_id_snapshot=requester_id,
        status=REQUEST_ITEM_STATUS_PENDING,
    )
    request = DataAccessRequest(
        requester_person_id=requester_id,
        principal_email_group_id=group.id,
        principal_google_group_id_snapshot=group.google_group_id,
        principal_email_snapshot=group.email,
        entitlement_id=entitlement.id,
        entitlement_snapshot=c.ENTITLEMENT_READ,
        entitlement_version_snapshot=ENTITLEMENT_VERSION_V1,
        approval_flow_key=c.APPROVAL_FLOW_DATA_ACCESS_GROUP_GRANT,
        feishu_instance_code=f"dax-feishu-{uuid.uuid4().hex[:8]}",
        status=REQUEST_STATUS_PENDING_FEISHU,
        items=[item],
    )
    return request, item


def _binding(
    *,
    resource: DataAccessResource,
    group: EmailGroup,
    entitlement: DataAccessEntitlement,
    request: DataAccessRequest | None = None,
    item: DataAccessRequestItem | None = None,
    principal_email_snapshot: str | None = None,
    status: str = BINDING_STATUS_ACTIVE,
) -> DataAccessBinding:
    return DataAccessBinding(
        resource_id=resource.id,
        principal_email_group_id=group.id,
        principal_google_group_id_snapshot=group.google_group_id,
        principal_email_snapshot=principal_email_snapshot or group.email,
        entitlement_id=entitlement.id,
        entitlement_snapshot=c.ENTITLEMENT_READ,
        entitlement_version_snapshot=ENTITLEMENT_VERSION_V1,
        source_request_id=request.id if request else None,
        source_request_item_id=item.id if item else None,
        status=status,
        revoked_at=(
            datetime.now(UTC) if status == BINDING_STATUS_REVOKED else None
        ),
        revoke_reason=(
            "group_primary_email_rename"
            if status == BINDING_STATUS_REVOKED
            else None
        ),
    )


async def test_duplicate_active_binding_is_keyed_by_group_id_not_primary_email(
    db_session,
    employee_user,
) -> None:
    requester, _ = employee_user
    group = EmailGroup(
        email="analytics-readers@example.com",
        name="Analytics Readers",
        status="active",
        google_group_id="google-group-analytics",
    )
    entitlement = _entitlement()
    integration_id = await _integration_id(db_session)
    resource = _resource(
        integration_id=integration_id,
        owner_person_id=requester.id,
    )
    db_session.add_all([group, entitlement, resource])
    await db_session.flush()
    request, item = _request(
        requester_id=requester.id,
        group=group,
        entitlement=entitlement,
        resource=resource,
    )
    db_session.add(request)
    await db_session.flush()

    db_session.add(_binding(
        resource=resource,
        group=group,
        entitlement=entitlement,
        request=request,
        item=item,
    ))
    await db_session.flush()

    db_session.add(_binding(
        resource=resource,
        group=group,
        entitlement=entitlement,
        principal_email_snapshot="analytics-renamed@example.com",
    ))

    with pytest.raises(IntegrityError):
        await db_session.flush()


async def test_revoked_historical_binding_remains_queryable_with_old_email(
    db_session,
    employee_user,
) -> None:
    requester, _ = employee_user
    group = EmailGroup(
        email="analytics-readers@example.com",
        name="Analytics Readers",
        status="active",
        google_group_id="google-group-analytics",
    )
    entitlement = _entitlement()
    integration_id = await _integration_id(db_session)
    resource = _resource(
        integration_id=integration_id,
        owner_person_id=requester.id,
    )
    db_session.add_all([group, entitlement, resource])
    await db_session.flush()

    historical = _binding(
        resource=resource,
        group=group,
        entitlement=entitlement,
        principal_email_snapshot="old-analytics@example.com",
        status=BINDING_STATUS_REVOKED,
    )
    current = _binding(
        resource=resource,
        group=group,
        entitlement=entitlement,
        principal_email_snapshot=group.email,
    )
    db_session.add_all([historical, current])
    await db_session.flush()

    rows = (
        await db_session.scalars(
            select(DataAccessBinding)
            .where(
                DataAccessBinding.resource_id == resource.id,
                DataAccessBinding.principal_email_group_id == group.id,
                DataAccessBinding.entitlement_id == entitlement.id,
            )
            .order_by(DataAccessBinding.status),
        )
    ).all()

    assert {row.status for row in rows} == {
        BINDING_STATUS_ACTIVE,
        BINDING_STATUS_REVOKED,
    }
    assert any(
        row.status == BINDING_STATUS_REVOKED
        and row.principal_email_snapshot == "old-analytics@example.com"
        for row in rows
    )


def test_binding_table_has_no_alias_or_provider_mutation_override_path() -> None:
    columns = set(DataAccessBinding.__table__.columns.keys())

    assert "resource_id" in columns
    assert "principal_email_group_id" in columns
    assert "entitlement_id" in columns
    assert "principal_email_snapshot" in columns
    assert not any("alias" in column for column in columns)
    assert columns.isdisjoint({
        "provider_member",
        "provider_role",
        "provider_role_override",
        "project_iam_role",
        "bigquery_job_user",
        "policy_patch",
    })