from collections.abc import Sequence
from typing import cast

import sqlalchemy as sa
from fansifter_common.auth.account import Account
from fansifter_common.utils.text import strip_whitespaces
from sqlalchemy.orm import selectinload

from dmp.adapters.db import Repository
from dmp.audiences.dtos import AudiencesQueryCriteria
from dmp.audiences.models import Audience
from dmp.audiences.types import AudiencesOrderBy


class AudienceRepository(Repository[Audience]):
    default_options = (
        selectinload(Audience.snapshots),
        selectinload(Audience.fandata_lists),
    )

    def find_by_criteria(
        self,
        criteria: AudiencesQueryCriteria,
        order_by: AudiencesOrderBy,
        limit: int,
        offset: int,
        identity_id: str,
    ) -> Sequence[Audience]:
        query = self.db.query_from_template(
            "audience/find-by-criteria.sql",
            context={
                "criteria": criteria,
                "order_by": order_by,
                "limit": limit,
                "offset": offset,
                "identity_id": identity_id,
            },
        )
        return (
            self.db.session.execute(
                sa.select(Audience).from_statement(query).options(*self.default_options)
            )
            .scalars()
            .all()
        )

    def count_by_criteria(
        self, criteria: AudiencesQueryCriteria, identity_id: str
    ) -> int:
        query = self.db.query_from_template(
            "audience/count-by-criteria.sql",
            context={
                "criteria": criteria,
                "identity_id": identity_id,
            },
        )
        result = self.db.session.execute(query)
        return cast(int, result.scalar_one())

    def exists_by_name_and_account(self, name: str, account: Account) -> bool:
        query = sa.select(
            sa.select(1)
            .exists()
            .where(
                sa.and_(
                    sa.func.lower(Audience.name) == strip_whitespaces(name.lower()),
                    Audience.vendor_id == account.vendor_id,
                    Audience.subaccount_id == account.subaccount_id,
                    Audience.archived_at.is_(None),  # noqa
                )
            )
        )
        result = self.db.session.execute(query)
        return bool(result.scalar_one())