import uuid
from collections.abc import Mapping, Sequence
from typing import NamedTuple

from sqlalchemy import String, cast, delete, func, select, text
from sqlalchemy.orm import joinedload, selectinload

from app.common.data.interfaces.exceptions import flush_and_rollback_on_exceptions
from app.common.data.interfaces.organisations import get_organisations
from app.common.data.models import Grant, GrantRecipient, Organisation, Submission
from app.common.data.models_user import User, UserRole
from app.common.data.types import (
    GrantRecipientModeEnum,
    GrantRecipientStatusEnum,
    OrganisationModeEnum,
    RoleEnum,
    SubmissionModeEnum,
)
from app.extensions import db


def get_grant_recipients(
    grant: Grant,
    *,
    mode: GrantRecipientModeEnum = GrantRecipientModeEnum.LIVE,
    with_data_providers: bool = False,
    with_certifiers: bool = False,
    with_organisations: bool = False,
) -> Sequence[GrantRecipient]:
    stmt = select(GrantRecipient).where(GrantRecipient.grant_id == grant.id, GrantRecipient.mode == mode)

    if with_data_providers:
        stmt = stmt.options(joinedload(GrantRecipient.data_providers))

    if with_certifiers:
        stmt = stmt.options(joinedload(GrantRecipient._all_certifiers).joinedload(User.roles))

    if with_organisations:
        stmt = stmt.options(selectinload(GrantRecipient.organisation))

    stmt = stmt.join(Organisation, GrantRecipient.organisation_id == Organisation.id).order_by(Organisation.name)

    return db.session.scalars(stmt).unique().all()


def get_grant_recipients_with_outstanding_submissions_for_collection(
    grant: Grant, *, collection_id: uuid.UUID, with_data_providers: bool = False, with_certifiers: bool = False
) -> list[GrantRecipient]:
    """
    Gets all the grant recipients who have not submitted their submission for the given collection.

    They are considered not to have submitted if either:
    - They have a submission that is not in the SUBMITTED state
    - They do not have a submission for this collection

    """
    from app.common.data.interfaces.collections import get_all_submissions_with_mode_for_collection

    all_grant_recipients = get_grant_recipients(
        grant, with_data_providers=with_data_providers, with_certifiers=with_certifiers
    )
    submissions = get_all_submissions_with_mode_for_collection(
        grant_recipient_ids=[gr.id for gr in all_grant_recipients],
        collection_id=collection_id,
        submission_mode=SubmissionModeEnum.LIVE,
    )
    grant_recipients_with_outstanding_submissions = []
    for gr in all_grant_recipients:
        submission = next((s for s in submissions if s.grant_recipient_id == gr.id), None)
        if not submission or not submission.is_submitted:
            grant_recipients_with_outstanding_submissions.append(gr)

    return grant_recipients_with_outstanding_submissions


def get_grant_recipients_for_collection_with_locked_submissions(
    grant: Grant, *, collection_id: uuid.UUID, submission_mode: SubmissionModeEnum = SubmissionModeEnum.LIVE
) -> Sequence[GrantRecipient]:
    """
    Gets all the grant recipients who have a submission whose data should no longer be changed by replacing a data set.

    Submissions are treated as locked once they have been submitted or are awaiting sign off, as grant recipient users
    cannot update their answers in those states.

    """
    return db.session.scalars(
        select(GrantRecipient)
        .join(Submission)
        .where(
            GrantRecipient.grant_id == grant.id,
            Submission.collection_id == collection_id,
            Submission.mode == submission_mode,
            Submission.has_answers_locked_status,
        )
        .options(selectinload(GrantRecipient.organisation))
    ).all()


def get_grant_recipients_for_organisation(
    organisation_id: uuid.UUID,
    *,
    mode: GrantRecipientModeEnum = GrantRecipientModeEnum.LIVE,
) -> Sequence[GrantRecipient]:
    stmt = (
        select(GrantRecipient)
        .where(GrantRecipient.organisation_id == organisation_id, GrantRecipient.mode == mode)
        .options(joinedload(GrantRecipient.grant))
    )
    return db.session.scalars(stmt).all()


def get_grant_recipient(grant_id: uuid.UUID, organisation_id: uuid.UUID) -> GrantRecipient:
    statement = (
        select(GrantRecipient)
        .join(Organisation, GrantRecipient.organisation_id == Organisation.id)
        .where(
            GrantRecipient.grant_id == grant_id,
            GrantRecipient.organisation_id == organisation_id,
            cast(GrantRecipient.mode, String) == cast(Organisation.mode, String),
        )
        .options(joinedload(GrantRecipient.grant), joinedload(GrantRecipient.organisation))
    )
    return db.session.scalars(statement).one()


def get_grant_recipient_or_none(grant_id: uuid.UUID, organisation_id: uuid.UUID) -> GrantRecipient | None:
    statement = (
        select(GrantRecipient)
        .join(Organisation, GrantRecipient.organisation_id == Organisation.id)
        .where(
            GrantRecipient.grant_id == grant_id,
            GrantRecipient.organisation_id == organisation_id,
            cast(GrantRecipient.mode, String) == cast(Organisation.mode, String),
        )
        .options(joinedload(GrantRecipient.grant), joinedload(GrantRecipient.organisation))
    )
    return db.session.scalars(statement).one_or_none()


def get_or_create_grant_recipient(
    grant: Grant,
    organisation: Organisation,
    status: GrantRecipientStatusEnum,
    mode: GrantRecipientModeEnum = GrantRecipientModeEnum.LIVE,
) -> GrantRecipient:
    # Acquire a transaction-scoped advisory lock keyed on (grant, organisation) BEFORE reading, to
    # prevent the double-click race condition: without the lock, two concurrent requests can both
    # read no existing grant recipient and each proceed to create one before either commits. The
    # lock serialises them so the second request re-reads after the first commits and finds the
    # grant recipient the first request created, rather than creating a duplicate. See
    # get_or_create_submission for the same pattern applied to submissions.
    db.session.execute(
        text("SELECT pg_advisory_xact_lock(hashtext(:key))"),
        {"key": f"{grant.id}:{organisation.id}"},
    )

    if existing := get_grant_recipient_or_none(grant.id, organisation.id):
        return existing

    return create_grant_recipient(grant=grant, organisation=organisation, status=status, mode=mode)


def get_or_create_grant_recipient_pair(
    grant: Grant, organisation: Organisation, status: GrantRecipientStatusEnum
) -> dict[OrganisationModeEnum, GrantRecipient | None]:
    """
    Get_or_create a GrantRecipient for the given organisation, along with the equivalent GrantRecipient for its
    LIVE/TEST counterpart organisation (same external_id, opposite mode), if that counterpart organisation exists.
    """

    is_mode_live = organisation.mode == OrganisationModeEnum.LIVE
    other_mode = OrganisationModeEnum.TEST if is_mode_live else OrganisationModeEnum.LIVE

    other_mode_organisations = get_organisations(with_external_ids=[organisation.external_id], mode=other_mode)
    other_mode_organisation = other_mode_organisations[0] if other_mode_organisations else None

    grant_recipient = get_or_create_grant_recipient(
        grant=grant,
        organisation=organisation,
        status=status,
        mode=GrantRecipientModeEnum.from_similar(organisation.mode),
    )

    other_mode_grant_recipient = (
        get_or_create_grant_recipient(
            grant=grant,
            organisation=other_mode_organisation,
            status=status,
            mode=GrantRecipientModeEnum.from_similar(other_mode),
        )
        if other_mode_organisation
        else None
    )

    return {organisation.mode: grant_recipient, other_mode: other_mode_grant_recipient}


def get_grant_recipients_count(grant: Grant, mode: GrantRecipientModeEnum = GrantRecipientModeEnum.LIVE) -> int:
    statement = (
        select(func.count())
        .select_from(GrantRecipient)
        .where(GrantRecipient.grant_id == grant.id, GrantRecipient.mode == mode)
    )
    return db.session.scalar(statement) or 0


@flush_and_rollback_on_exceptions
def delete_grant_recipients(grant: Grant) -> None:
    db.session.execute(delete(GrantRecipient).where(GrantRecipient.grant_id == grant.id))


@flush_and_rollback_on_exceptions()
def create_grant_recipient(
    grant: Grant,
    organisation: Organisation,
    status: GrantRecipientStatusEnum,
    mode: GrantRecipientModeEnum = GrantRecipientModeEnum.LIVE,
) -> GrantRecipient:
    grant_recipient = GrantRecipient(grant_id=grant.id, organisation_id=organisation.id, mode=mode, status=status)
    db.session.add(grant_recipient)
    return grant_recipient


@flush_and_rollback_on_exceptions()
def create_grant_recipients(
    grant: Grant,
    organisation_ids: list[uuid.UUID],
    status: GrantRecipientStatusEnum,
    mode: GrantRecipientModeEnum = GrantRecipientModeEnum.LIVE,
) -> None:
    grant_recipients = []

    for organisation_id in organisation_ids:
        grant_recipients.append(
            GrantRecipient(grant_id=grant.id, organisation_id=organisation_id, mode=mode, status=status)
        )

    db.session.add_all(grant_recipients)


class GrantRecipientDataProvidersCountData(NamedTuple):
    count: int
    recipients_missing_data_providers: list[str]


def get_grant_recipient_data_providers_count(
    grant: Grant, mode: GrantRecipientModeEnum = GrantRecipientModeEnum.LIVE
) -> GrantRecipientDataProvidersCountData:
    data_providers = set()
    recipients_missing_data_providers = []

    for grant_recipient in get_grant_recipients(grant, mode=mode, with_data_providers=True):
        if not grant_recipient.data_providers:
            recipients_missing_data_providers.append(grant_recipient.organisation.name)
        else:
            data_providers.update(grant_recipient.data_providers)

    return GrantRecipientDataProvidersCountData(len(data_providers), recipients_missing_data_providers)


def get_grant_recipient_data_provider_roles(grant: Grant) -> Sequence[UserRole]:
    """Get all grant recipient data provider roles for a grant."""
    statement = (
        select(UserRole)
        .join(User, UserRole.user_id == User.id)
        .join(Organisation, Organisation.id == UserRole.organisation_id)
        .where(
            UserRole.grant_id == grant.id,
            UserRole.permissions.contains([RoleEnum.DATA_PROVIDER]),
        )
    )

    return db.session.scalars(statement).all()


def get_grant_recipient_data_providers(grant: Grant) -> Mapping[GrantRecipient, Sequence[User]]:
    grant_recipients = get_grant_recipients(grant, with_data_providers=True)

    data_providers = {}
    for grant_recipient in grant_recipients:
        data_providers[grant_recipient] = grant_recipient.data_providers

    return data_providers
