from collections.abc import Sequence

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

from dmp.adapters.db import Repository
from dmp.meta.models import MetaAdAccount, MetaUserAdAccount


class MetaUserAdAccountRepository(Repository[MetaUserAdAccount]):
    default_options = (joinedload(MetaUserAdAccount.ad_account),)

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

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

    def find_by_identity_id(self, identity_id: str) -> Sequence[MetaUserAdAccount]:
        query = (
            sa.select(MetaUserAdAccount)
            .where(MetaUserAdAccount.identity_id == identity_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[MetaUserAdAccount]:
        query = self.db.query_from_template(
            "meta-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(MetaUserAdAccount)
                .from_statement(query)
                .options(selectinload(MetaUserAdAccount.ad_account))
            )
            .scalars()
            .all()
        )

    def get_by_ad_account_id_and_identity_id(
        self, ad_account_id: str, identity_id: str
    ) -> MetaUserAdAccount | None:
        query = (
            sa.select(MetaUserAdAccount)
            .where(
                sa.and_(
                    MetaUserAdAccount.ad_account_id == ad_account_id,
                    MetaUserAdAccount.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 | None
    ) -> Sequence[MetaUserAdAccount]:
        query = (
            sa.select(MetaUserAdAccount)
            .where(
                sa.and_(
                    MetaUserAdAccount.ad_account_id == ad_account_id,
                    MetaUserAdAccount.vendor_id == vendor_id,
                    MetaUserAdAccount.subaccount_id == subaccount_id,
                )
            )
            .options(*self.default_options)
        )
        result = self.db.session.execute(query)
        return result.scalars().all()