from collections.abc import Sequence

import sqlalchemy as sa
from sqlalchemy.orm import joinedload, selectinload

from dmp.adapters.db import Repository
from dmp.google.models import GoogleAdAccount, GoogleUserAdAccount


class GoogleUserAdAccountRepository(Repository[GoogleUserAdAccount]):
    default_options = (joinedload(GoogleUserAdAccount.ad_account),)

    def find_by_identity_id(self, identity_id: str) -> Sequence[GoogleUserAdAccount]:
        query = (
            sa.select(GoogleUserAdAccount)
            .where(GoogleUserAdAccount.identity_id == identity_id)
            .options(*self.default_options)
        )
        result = self.db.session.execute(query)
        return result.scalars().all()

    def find_by_ad_accounts_ids_and_identity_id(
        self, ad_accounts_ids: list[str], identity_id: str
    ) -> Sequence[GoogleUserAdAccount]:
        query = (
            sa.select(GoogleUserAdAccount)
            .where(
                sa.and_(
                    GoogleUserAdAccount.ad_account_id.in_(ad_accounts_ids),
                    GoogleUserAdAccount.identity_id == identity_id,
                )
            )
            .options(*self.default_options)
        )
        result = self.db.session.execute(query)
        return result.scalars().all()

    def get_by_ad_account_id_and_identity_id(
        self, ad_account_id: str, identity_id: str
    ) -> GoogleUserAdAccount | None:
        query = (
            sa.select(GoogleUserAdAccount)
            .where(
                sa.and_(
                    GoogleUserAdAccount.ad_account_id == ad_account_id,
                    GoogleUserAdAccount.identity_id == identity_id,
                )
            )
            .options(*self.default_options)
        )
        result = self.db.session.execute(query)
        return result.scalar_one_or_none()

    def find_by_ad_account_id_and_account(
        self, ad_account_id: str, vendor_id: int, subaccount_id: int
    ) -> Sequence[GoogleUserAdAccount]:
        query = (
            sa.select(GoogleUserAdAccount)
            .where(
                sa.and_(
                    GoogleUserAdAccount.ad_account_id == ad_account_id,
                    GoogleUserAdAccount.vendor_id == vendor_id,
                    GoogleUserAdAccount.subaccount_id == subaccount_id,
                )
            )
            .options(*self.default_options)
        )
        result = self.db.session.execute(query)
        return result.scalars().all()

    def find_by_vendor_and_subaccount_ids(
        self, vendor_ids: list[int], subaccount_ids: list[int]
    ) -> Sequence[GoogleUserAdAccount]:
        query = self.db.query_from_template(
            "google-user-ad-account/find-by-vendor-and-subaccount-ids.sql",
            context={
                "vendor_ids": vendor_ids,
                "subaccount_ids": subaccount_ids,
            },
        )
        return (
            self.db.session.execute(
                sa.select(GoogleUserAdAccount)
                .from_statement(query)
                .options(selectinload(GoogleUserAdAccount.ad_account))
            )
            .scalars()
            .all()
        )

    def find_by_ad_accounts_external_ids(
        self, ad_accounts_ids: set[str]
    ) -> Sequence[GoogleUserAdAccount]:
        if not ad_accounts_ids:
            return []
        query = (
            sa.select(GoogleUserAdAccount)
            .join(GoogleAdAccount)
            .where(
                GoogleAdAccount.external_id.in_(ad_accounts_ids),
            )
            .options(*self.default_options)
        )
        result = self.db.session.execute(query)
        return result.scalars().all()