"""Collaborator Persister.

Handles doing CRUD operations on the collaborator table.
"""

from collections import defaultdict
from typing import Any, List, Optional

import sqlalchemy
from sqlalchemy import (
    String,
    and_,
    cast,
    func,
    or_,
    select,
)
from sqlalchemy.orm import QueryableAttribute
from sqlalchemy.orm.session import Session
from sqlalchemy.sql.functions import coalesce

from collaborator.connectors import mysql
from collaborator.constants import error
from collaborator.constants.collaborator import CollaboratorType
from collaborator.constants.features import NEW_COLLABORATOR_CTA
from collaborator.models.rds.collaborator import Collaborator
from collaborator.models.rds.recipient import Recipient
from collaborator.models.rds.split import Split
from collaborator.models.rds.transaction import Transaction
from collaborator.utils import api as api_utils
from collaborator.utils import features
from collaborator.utils.error import OwsError
from collaborator.utils.typing import Account


class CollaboratorPersister:
    """Handles high level operations for collaborators."""

    @classmethod
    @mysql.db_session
    def create_collaborator(
        cls,
        collaborator_name: str,
        account: Account,
        subaccount_id: int,
        participant_id: Optional[str],
        collaborator_type: Optional[str],
        currency: str,
        description: str,
        internal_id: str,
        performance_rights: bool,
        created_by: str,
        session: Session,
    ) -> tuple:
        """Create a collaborator.

        Args:
            collaborator_name (str): name of the collaborator.
            account (Account): Account which is creating the collaborator.
            subaccount_id (int): Optional subaccount for the collaborator.
            participant_id (str): Participant ID for the collaborator.
            currency (str): Three letter currency code.
            description (str): Collaborator description
            internal_id (str): Collaborator internal identifier
            performance_rights (bool): Collaborator performance rights.
            session (sqlalchemy.orm.session.Session): database session.

        Returns:
            tuple[dict, bool]: the collaborator data and a bool indicating
                whether it was newly created (True) or already existed (False)
        """
        # If subaccount / participant IDs were provided, we want to make sure
        # no collaborators already exist with those IDs. Otherwise we can just
        # go ahead and create them.
        if any([participant_id, subaccount_id]):
            filters = [
                Collaborator.vendor_id == account.id,
                Collaborator.collaborator_type == collaborator_type,
            ]

            if participant_id:
                if not features.is_feature_enabled(NEW_COLLABORATOR_CTA):
                    filters.append(Collaborator.participant_id == participant_id)

            if subaccount_id:
                filters.append(Collaborator.subaccount_id == subaccount_id)

            result = session.query(Collaborator).filter(*filters).first()
            if result:
                return result.to_dict(), False

        collaborator = Collaborator(
            name=collaborator_name,
            vendor_id=account.id,
            subaccount_id=subaccount_id,
            participant_id=participant_id,
            currency=currency,
            collaborator_type=collaborator_type,
            description=description,
            internal_id=internal_id,
            performance_rights=performance_rights,
            created_by=created_by,
        )

        session.add(collaborator)
        session.commit()  # so a newly created entry gets an ID

        return collaborator.to_dict(), True

    @classmethod
    @mysql.db_session
    def get_by_name(
        cls,
        collaborator_name: str,
        account: Account,
        collaborator_type: str,
        session: Session,
        **kwargs,
    ) -> dict | None:
        """Create a collaborator.

        Args:
            collaborator_name (str): name of the collaborator.
            account (Account): Account which is creating the collaborator.
            subaccount_id (int): Optional subaccount for the collaborator.
            participant_id (str): Participant ID for the collaborator.
            currency (str): Three letter currency code.
            description (str): Collaborator description
            internal_id (str): Collaborator internal identifier
            session (sqlalchemy.orm.session.Session): database session.

        Returns:
            dict with the collaborator metadata
        """
        result = (
            session.query(Collaborator)
            .filter_by(
                vendor_id=account.id,
                name=collaborator_name,
                collaborator_type=collaborator_type,
            )
            .first()
        )

        return result.to_dict() if result else None

    @classmethod
    @mysql.db_session
    def update_collaborator(cls, collaborator_id: int, payload: dict, session: Session):
        """Update a collaborator.

        Args:
            payload (dict): Payload with the collaborator's update attributes.
            user (User): User who is updating the collaborator.
            session (sqlalchemy.orm.session.Session): database session.

        Returns:
            Response: the updated collaborator
        """
        query = session.query(Collaborator).filter(
            Collaborator.collaborator_id == collaborator_id
        )
        query.update(payload)
        collaborator = query.first()

        if not collaborator:
            raise OwsError.not_found(
                message=error.ERROR_MESSAGE_COLLABORATOR_NOT_FOUND,
                code=error.ERROR_CODE_COLLABORATOR_NOT_FOUND,
            )

        return collaborator.to_dict()

    @classmethod
    @mysql.db_session
    def update_collaborators(cls, collaborators_data: dict, session: Session):
        """Update a series of collaborators.

        Args:
            collaborators_data (dict): Data to update

        The format of the data needs to be:
        {
            id1: {field: value, ...},
            id2: {field: value, ...},
            ...
        }

        Returns:
            list: the updated collaborators
        """
        for collaborator_id, data in collaborators_data.items():
            row = (
                session.query(Collaborator)
                .filter(Collaborator.collaborator_id == collaborator_id)
                .first()
            )

            if not row:
                raise OwsError.not_found(
                    code=error.ERROR_CODE_COLLABORATOR_NOT_FOUND,
                    message=error.ERROR_MESSAGE_COLLABORATOR_NOT_FOUND,
                )

            for key, value in data.items():
                setattr(row, key, value)

            session.add(row)
            session.flush()
        session.commit()

        collaborator_ids = collaborators_data.keys()
        updated_collaborators = (
            session.query(Collaborator)
            .filter(Collaborator.collaborator_id.in_(collaborator_ids))
            .all()
        )

        return [x.to_dict() for x in updated_collaborators]

    @classmethod
    @mysql.db_session
    def search(
        cls,
        vendor_ids: Optional[List[str]],
        collaborator_ids: Optional[List[int]],
        search_term: Optional[str],
        dimensions: List[QueryableAttribute[Any]],
        has_recipient: Optional[bool],
        collaborator_type: Optional[bool],
        offset: int,
        limit: Optional[int],
        session: Session,
        exactly_match_term: bool = False,
    ):
        """Search for collaborators based on search_term.

        Args:
            full_catalog_access (bool): If this profile has vendor * access.
            vendor_ids (List[str]): list of vendor IDs to filter the results by.
            collaborator_ids (List[int]): list of collaborator IDs to filter the results by.
            search_term (str): text to search for.
            dimensions (List[Column]): list of columns to apply the search term to.
            offset (int): offset of the query cursor.
            limit (Optional[int]): limit on the query result.
            session (sqlalchemy.orm.session.Session): database session.
            exactly_match_term (bool): If the search term should be exactly matched.

        Returns:
            Response: list of collaborators
        """
        filters = []

        if search_term:
            if exactly_match_term:
                # Our MySQL DB target is case-insensitive by default, but `ilike`
                # is used here to make that clear on reading.
                dim_filters = [
                    cast(dim, String).ilike(search_term) for dim in dimensions
                ]
                filters.append(or_(*dim_filters))
            else:
                dim_filters = [
                    cast(dim, String).ilike(f"%{search_term}%") for dim in dimensions
                ]
                filters.append(or_(*dim_filters))

        if vendor_ids:
            filters.append(Collaborator.vendor_id.in_(vendor_ids))

        if collaborator_ids:
            filters.append(Collaborator.collaborator_id.in_(collaborator_ids))

        if has_recipient:
            filters.append(Collaborator.recipient_id.isnot(None))

        if collaborator_type:
            filters.append(Collaborator.collaborator_type == collaborator_type)

        query = (
            session.query(Collaborator)
            .order_by(Collaborator.name.asc(), Collaborator.collaborator_id.asc())
            .filter(*filters)
        )

        total_records = query.count()

        query = (
            query.offset(offset) if limit is None else query.offset(offset).limit(limit)
        )
        results = query.all()
        items = [item.to_dict() for item in results]

        return api_utils.create_paginated_response(items, total_records)

    @classmethod
    @mysql.db_session
    def get_by_id(cls, collaborator_id: int, session: Session):
        """Get a single collaborator based on the ID.

        Args:
            collaborator_id (int): ID of the collaborator to get.
            session (sqlalchemy.orm.session.Session): database session.

        Returns:
            Response: a collaborator
        """
        result = (
            session.query(Collaborator)
            .filter_by(collaborator_id=collaborator_id)
            .first()
        )

        if not result:
            raise OwsError.not_found(
                code=error.ERROR_CODE_COLLABORATOR_NOT_FOUND,
                message=error.ERROR_MESSAGE_COLLABORATOR_NOT_FOUND,
            )

        return result.to_dict()

    @classmethod
    @mysql.db_session
    def get_by_id_and_account(cls, collaborator_id, account, session):
        """Get a single collaborator based on the ID and account.

        Args:
            collaborator_id (int): ID of the collaborator to get.
            account (Account): Account to limit by.
            session (sqlalchemy.orm.session.Session): database session.

        Returns:
            Response: a collaborator
        """
        result = (
            session.query(Collaborator)
            .filter_by(collaborator_id=collaborator_id, vendor_id=account.id)
            .first()
        )

        if result is None:
            raise OwsError.not_found(
                message=error.ERROR_MESSAGE_COLLABORATOR_NOT_FOUND,
                code=error.ERROR_CODE_COLLABORATOR_NOT_FOUND,
            )

        return result.to_dict()

    @classmethod
    @mysql.db_session
    def get_names_by_ids(
        cls, collaborator_ids: set[int], session: Session
    ) -> dict[int, str]:
        """Return {collaborator_id: name} for the given collaborator IDs."""
        if not collaborator_ids:
            return {}
        rows = session.execute(
            select(Collaborator.collaborator_id, Collaborator.name).where(
                Collaborator.collaborator_id.in_(collaborator_ids)
            )
        ).all()
        return {collaborator_id: name for collaborator_id, name in rows}

    @classmethod
    @mysql.db_session
    def get_by_ids(
        cls, collaborator_ids: list, throw_if_not_found: bool, session: Session
    ) -> list:
        """Get a list of collaborators from IDs.

        Args:
            collaborator_ids (list): IDs of the collaborator to get.
            session (sqlalchemy.orm.session.Session): database session.

        Returns:
            list: a list of collaborators
        """
        collaborator_ids = list(set(collaborator_ids))
        result = (
            session.query(Collaborator)
            .filter(Collaborator.collaborator_id.in_(collaborator_ids))
            .all()
        )

        if (not result or len(result) != len(collaborator_ids)) and throw_if_not_found:
            raise OwsError.not_found(
                code=error.ERROR_CODE_COLLABORATOR_NOT_FOUND,
                message=error.ERROR_MESSAGE_COLLABORATOR_NOT_FOUND,
            )

        return [c.to_dict() for c in (result or [])]

    @classmethod
    @mysql.db_session
    def get_by_transferwise_recipient_ids(
        cls, transferwise_ids: list, session: Session
    ) -> dict:
        """Get a list of collaborators based on their TW recipient IDs.

        Args:
            transferwise_ids (list): List of TW recipient IDs.
            session (sqlalchemy.orm.session.Session): database session.

        Returns:
            dict: mappings of transferwise recipeint IDs to collaborators
        """
        results = (
            session.query(Collaborator, Recipient.transferwise_id)
            .join(Recipient)
            .filter(Recipient.transferwise_id.in_(transferwise_ids))
            .all()
        )

        return {result[1]: result[0].to_dict() for result in results}

    @classmethod
    @mysql.db_session
    def get_by_recipient_ids(cls, recipient_ids: list[int], session: Session) -> list:
        """Get a list of collaborators based on their TW recipient IDs.

        Args:
            recipient_ids (list[int]): The recipient unique identifier
            session (sqlalchemy.orm.session.Session): database session.

        Returns:
            list: with the collaborators
        """
        results = (
            session.query(Collaborator)
            .join(Recipient)
            .filter(Recipient.recipient_id.in_(recipient_ids))
            .all()
        )

        return [item.to_dict() for item in results]

    @classmethod
    @mysql.db_session
    def get_for_account(cls, account, limit, offset, has_recipient, session):
        """Get collaborators based on an account.

        TODO: Handle account type.

        Args:
            account (Account): Account to get collaborators for.
            limit (int): how many collaborators to retrieve.
            offset (int): the offset (for pagination).
            has_recipient (bool): Flag in order to filter the results
            session (sqlalchemy.orm.session.Session): database session.

        Returns:
            Response: list of collaborators
        """
        filters = [
            (Collaborator.vendor_id == account.id),
        ]

        if has_recipient:
            filters.append(Collaborator.recipient_id.isnot(None))

        query = (
            session.query(Collaborator)
            .filter(*filters)
            .order_by(Collaborator.name.asc())
        )

        if limit != 0:
            limited_query = query.limit(limit).offset(offset)
        else:
            limited_query = query.offset(offset)

        results = limited_query.all()
        items = [item.to_dict() for item in results]

        total_records = query.count()

        return api_utils.create_paginated_response(items, total_records)

    @classmethod
    @mysql.db_session
    def get_subaccount_collaborator(
        cls, account: Account, subaccount_id: int, session: Session
    ) -> Optional[dict]:
        """Get a subaccount collaborator for an account by its ID.

        Args:
            account (Account): Account which owns the collaborator.
            subaccount_id (int): Subaccount ID to get collaborator for.
            session (sqlalchemy.orm.session.Session): database session.

        Returns:
            dict: Collaborator
        """
        filters = [
            (Collaborator.collaborator_type == CollaboratorType.SUBACCOUNT),
            (Collaborator.vendor_id == account.id),
            (Collaborator.subaccount_id == subaccount_id),
        ]

        result = session.query(Collaborator).filter(*filters).first()

        return result.to_dict() if result else None

    @classmethod
    @mysql.db_session
    def get_with_split_count(
        cls,
        session: Session,
        account: Optional[Account] = None,
        collaborator_ids: Optional[List[int]] = None,
    ) -> list:
        """Get a list of collaborators with the amount of splits they have.

        Args:
            session (sqlalchemy.orm.session.Session): database session.
            account (Account): Optional account to get collaborators for.
            collaborator_ids (list): Optional list of collaborator IDs to
                filter by.

        Returns:
            list: list of collaborators
        """
        split_query = (
            session.query(Split, sqlalchemy.func.count("*").label("split_count"))
            .group_by(Split.collaborator_id)
            .subquery()
        )

        filters = []
        if account:
            filters.append(Collaborator.vendor_id == account.id)
        if collaborator_ids:
            filters.append(Collaborator.collaborator_id.in_(collaborator_ids))

        results = (
            session.query(Collaborator, split_query.c.split_count)
            .join(
                split_query,
                Collaborator.collaborator_id == split_query.c.collaborator_id,
                isouter=True,
            )
            .filter(*filters)
            .group_by(Collaborator.collaborator_id)
            .all()
        )

        items = [
            {**collaborator.to_dict(), "splits": split_count or 0}
            for collaborator, split_count in results
        ]

        return items

    @classmethod
    @mysql.db_session
    def get_balances_for_ids(cls, collaborator_ids: List[int], session: Session):
        """Get balances for multiple collaborators.

        Args:
            session (Session): Database session
            collaborator_ids (List[int]): List of IDs of collaborator to get balances for
        """
        query = (
            select(
                Collaborator.collaborator_id,
                Collaborator.vendor_id,
                coalesce(func.sum(Transaction.chargeable_amount), 0).label("amount"),
                Collaborator.currency,
                Transaction.currency.label("transactions_currency"),
                func.count(Transaction.currency.distinct()).label(
                    "transactions_currencies_count"
                ),
            )
            .outerjoin(
                Transaction,
                and_(
                    Collaborator.collaborator_id == Transaction.collaborator_id,
                    Transaction.deleted_date == None,  # noqa
                ),
            )
            .where(Collaborator.collaborator_id.in_(collaborator_ids))
            .group_by(Collaborator.collaborator_id)
        )

        return session.execute(query).all()

    @classmethod
    @mysql.db_session
    def get_with_statement_activity(cls, vendor_id: int, session: Session):
        """Get collaborators with previous statement activity."""
        return (
            cls.bulk_get_with_statement_activity([vendor_id], session=session).get(
                vendor_id
            )
            or []
        )

    @classmethod
    @mysql.db_session
    def bulk_get_with_statement_activity(cls, vendor_ids: list[int], session: Session):
        """Get collaborators with previous statement activity for multiple vendors."""
        query = (
            select(Collaborator.vendor_id, Collaborator.collaborator_id)
            .distinct()
            .join(
                Transaction, Transaction.collaborator_id == Collaborator.collaborator_id
            )
            .filter(Collaborator.vendor_id.in_(vendor_ids))
        )

        rows = session.execute(query).all()

        result = defaultdict(list)
        for vendor_id, collaborator_id in rows:
            result[vendor_id].append(collaborator_id)

        return dict(result)

    @classmethod
    @mysql.db_session
    def get_vendor_map_by_ids(
        cls, collaborator_ids: set[int], session: Session
    ) -> dict[int, int]:
        """Return {collaborator_id: vendor_id} for the given IDs."""
        rows = session.execute(
            select(Collaborator.collaborator_id, Collaborator.vendor_id).where(
                Collaborator.collaborator_id.in_(collaborator_ids)
            )
        ).all()
        return {collaborator_id: vendor_id for collaborator_id, vendor_id in rows}

    @classmethod
    @mysql.db_session
    def get_or_create_by_names(
        cls,
        names: set[str],
        vendor_id: int,
        ticket_id: str,
        currency: str,
        session: Session,
    ) -> tuple[dict[str, int], int]:
        """Return (name_to_id, newly_created_count) for the given collaborator names.

        Idempotent: existing COLLABORATORs for the vendor are reused rather than
        duplicated. New ones are created and flushed within the same transaction.
        """
        if not names:
            return {}, 0

        name_to_id: dict[str, int] = {
            c.name: c.collaborator_id
            for c in session.execute(
                select(Collaborator).where(
                    Collaborator.vendor_id == vendor_id,
                    Collaborator.name.in_(names),
                    Collaborator.collaborator_type == CollaboratorType.COLLABORATOR,
                )
            )
            .scalars()
            .all()
            if c.name is not None
        }

        newly_created = 0
        for name in sorted(names - set(name_to_id)):
            collab = Collaborator(
                name=name,
                vendor_id=vendor_id,
                currency=currency,
                collaborator_type=CollaboratorType.COLLABORATOR,
                performance_rights=True,
                created_by=ticket_id,
            )
            session.add(collab)
            session.flush()  # populate collaborator_id before commit
            name_to_id[name] = collab.collaborator_id
            newly_created += 1

        session.commit()
        return name_to_id, newly_created