import sqlalchemy as sa
from sqlalchemy.orm import joinedload

from campaigns.connectors.db import Repository
from campaigns.meta.models import FacebookAssociation, FacebookPage, FacebookPageLabel


class FacebookPageRepository(Repository[FacebookPage]):
    default_options = (joinedload(FacebookPage.association, innerjoin=True),)

    async def get_or_none(self, *, page_id: str) -> FacebookPage | None:
        query = (
            sa.select(FacebookPage)
            .where(
                sa.and_(
                    FacebookPage.id == page_id,
                    FacebookAssociation.is_connected.is_(True),
                )
            )
            .join(FacebookAssociation)
            .options(*self.default_options)
        )
        result = await self.db.session.execute(query)
        return result.scalar_one_or_none()

    async def find_by_identity_id(self, *, identity_id: str) -> list[FacebookPage]:
        clauses = [
            FacebookAssociation.orchard_identity_id == identity_id,
            FacebookAssociation.is_connected.is_(True),
        ]
        query = (
            sa.select(FacebookPage).join(FacebookAssociation).where(sa.and_(*clauses))
        )
        result = await self.db.session.execute(query)
        return list(result.scalars().all())

    async def find_by_vendor_id_subaccount_id(
        self,
        *,
        vendor_id: int,
        subaccount_id: int,
        is_manageable: bool | None = None,
    ) -> list[FacebookPage]:
        clauses = [
            FacebookPageLabel.vendor_id == vendor_id,
            FacebookAssociation.is_connected.is_(True),
        ]
        if subaccount_id:
            clauses.append(FacebookPageLabel.subaccount_id == subaccount_id)
        if is_manageable is not None:
            clauses.append(
                FacebookPage.is_manageable.is_(is_manageable),
            )
        query = (
            sa.select(FacebookPage)
            .distinct(FacebookPage.id)
            .join(FacebookAssociation)
            .join(FacebookPageLabel)
            .where(sa.and_(*clauses))
        )
        result = await self.db.session.execute(query)
        return list(result.scalars().all())

    async def get_by_vendor_id_subaccount_id_or_none(
        self,
        *,
        page_id: str,
        vendor_id: int,
        subaccount_id: int,
    ) -> FacebookPage | None:
        clauses = [
            FacebookPage.id == page_id,
            FacebookPageLabel.vendor_id == vendor_id,
            FacebookAssociation.is_connected.is_(True),
        ]
        if subaccount_id:
            clauses.append(FacebookPageLabel.subaccount_id == subaccount_id)
        query = (
            sa.select(FacebookPage)
            .distinct(FacebookPage.id)
            .join(FacebookPageLabel)
            .join(FacebookAssociation)
            .where(sa.and_(*clauses))
        )
        result = await self.db.session.execute(query)
        return result.scalar_one_or_none()