"""Requester-scoped read model for employee-owned dataset grants."""
from __future__ import annotations

import uuid
from dataclasses import dataclass

from sqlalchemy import func, or_, select
from sqlalchemy.ext.asyncio import AsyncSession

from app.domains.data_access import constants as c
from app.domains.data_access.models import (
    DataAccessBinding,
    DataAccessCompiledEffect,
    DataAccessEntitlement,
    DataAccessRequest,
    DataAccessResource,
)

MY_GRANT_PRINCIPAL_TYPES = frozenset({
    c.PRINCIPAL_TYPE_GOOGLE_USER,
    c.PRINCIPAL_TYPE_GOOGLE_GROUP,
    c.PRINCIPAL_TYPE_GCP_SERVICE_ACCOUNT,
})


@dataclass(frozen=True)
class DataAccessMyGrantRecord:
    binding: DataAccessBinding
    resource: DataAccessResource
    entitlement: DataAccessEntitlement
    request: DataAccessRequest
    effect: DataAccessCompiledEffect | None


@dataclass(frozen=True)
class DataAccessMyGrantPage:
    items: list[DataAccessMyGrantRecord]
    total: int
    page: int
    page_size: int


async def list_my_data_access_grants(
    db: AsyncSession,
    *,
    requester_person_id: uuid.UUID,
    search: str | None,
    principal_type: str | None,
    page: int,
    page_size: int,
) -> DataAccessMyGrantPage:
    """Return bindings created by requests submitted by one employee.

    This intentionally does not infer grants merely inherited through current
    Google Group membership. Those are effective access, but not grants owned
    by the requester and therefore have a different governance lifecycle.
    """
    conditions = [
        DataAccessRequest.requester_person_id == requester_person_id,
        DataAccessResource.provider == c.PROVIDER_BIGQUERY,
        DataAccessResource.resource_type.in_((
            c.RESOURCE_TYPE_DATASET,
            c.RESOURCE_TYPE_TABLE,
        )),
        DataAccessEntitlement.domain == c.DOMAIN_DATA_ACCESS,
        DataAccessEntitlement.provider == c.PROVIDER_BIGQUERY,
        DataAccessEntitlement.resource_type == c.RESOURCE_TYPE_DATASET,
        DataAccessBinding.principal_type_snapshot.in_(MY_GRANT_PRINCIPAL_TYPES),
    ]
    if principal_type:
        conditions.append(DataAccessBinding.principal_type_snapshot == principal_type)
    if search and search.strip():
        like = f"%{search.strip()}%"
        conditions.append(or_(
            DataAccessResource.project_id.ilike(like),
            DataAccessResource.dataset_id.ilike(like),
            DataAccessResource.table_id.ilike(like),
            DataAccessResource.display_name.ilike(like),
            DataAccessResource.notes.ilike(like),
            DataAccessBinding.principal_email_snapshot.ilike(like),
        ))

    joins = (
        (DataAccessRequest, DataAccessRequest.id == DataAccessBinding.source_request_id),
        (DataAccessResource, DataAccessResource.id == DataAccessBinding.resource_id),
        (
            DataAccessEntitlement,
            DataAccessEntitlement.id == DataAccessBinding.entitlement_id,
        ),
    )
    count_stmt = select(func.count(DataAccessBinding.id)).select_from(
        DataAccessBinding,
    )
    rows_stmt = select(
        DataAccessBinding,
        DataAccessResource,
        DataAccessEntitlement,
        DataAccessRequest,
    ).select_from(DataAccessBinding)
    for target, on_clause in joins:
        count_stmt = count_stmt.join(target, on_clause)
        rows_stmt = rows_stmt.join(target, on_clause)

    total = int(await db.scalar(count_stmt.where(*conditions)) or 0)
    rows = (
        await db.execute(
            rows_stmt.where(*conditions)
            .order_by(
                DataAccessBinding.granted_at.desc(),
                DataAccessBinding.id.desc(),
            )
            .offset((page - 1) * page_size)
            .limit(page_size),
        )
    ).all()
    binding_ids = [binding.id for binding, _, _, _ in rows]
    effects_by_binding: dict[uuid.UUID, DataAccessCompiledEffect] = {}
    if binding_ids:
        effects = (
            await db.scalars(
                select(DataAccessCompiledEffect)
                .where(DataAccessCompiledEffect.binding_id.in_(binding_ids))
                .order_by(
                    DataAccessCompiledEffect.updated_at.desc(),
                    DataAccessCompiledEffect.id.desc(),
                ),
            )
        ).all()
        for effect in effects:
            effects_by_binding.setdefault(effect.binding_id, effect)

    return DataAccessMyGrantPage(
        items=[
            DataAccessMyGrantRecord(
                binding=binding,
                resource=resource,
                entitlement=entitlement,
                request=request_row,
                effect=effects_by_binding.get(binding.id),
            )
            for binding, resource, entitlement, request_row in rows
        ],
        total=total,
        page=page,
        page_size=page_size,
    )