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 MetaUserConnection, MetaUserConnectionAdAccount


class MetaUserConnectionRepository(Repository[MetaUserConnection]):
    default_options = [
        selectinload(MetaUserConnection.ad_account_associations).options(
            joinedload(MetaUserConnectionAdAccount.ad_account),
        )
    ]

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

    def find_active_by_audience_and_ad_account_id(
        self,
        *,
        audience_id: str,
        ad_account_id: str,
        identity_id: str,
        vendor_ids: list[int] | None = None,
        subaccount_ids: list[int] | None = None,
    ) -> Sequence[MetaUserConnection]:
        query = self.db.query_from_template(
            "meta-user-connection/find-active-by-audience-id-and-ad-account-id.sql",
            context={
                "audience_id": audience_id,
                "ad_account_id": ad_account_id,
                "identity_id": identity_id,
                "vendor_ids": vendor_ids,
                "subaccount_ids": subaccount_ids,
            },
        )
        result = self.db.session.execute(
            sa.select(MetaUserConnection)
            .from_statement(query)
            .options(*self.default_options)
        )
        return result.scalars().all()