from collections.abc import Sequence
from typing import cast

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

from dmp.ad_reporting.dtos import (
    AdReportingCampaignReport,
    AdReportingCampaignReportList,
    AdReportingReportCriteria,
    AdReportingReportView,
    AdReportingReportViewList,
)
from dmp.ad_reporting.models import AdReportingReport
from dmp.ad_reporting.types import (
    AdReportingReportCIFields,
    AdReportingReportOrderBy,
)
from dmp.adapters.db import ReportingRepository


class AdReportingReportRepository(ReportingRepository[AdReportingReport]):
    def find_by_criteria(
        self,
        criteria: AdReportingReportCriteria,
        *,
        order_by: list[AdReportingReportOrderBy] | None = None,
        limit: int | None = None,
        offset: int | None = None,
    ) -> Sequence[AdReportingReportView]:
        query = self.db.query_from_template(
            "ad-reporting/report/find-by-criteria.sql",
            context={
                "criteria": criteria,
                "order_by": order_by,
                "ci_fields": AdReportingReportCIFields,
                "limit": limit,
                "offset": offset,
            },
        )
        result = self.db.session.execute(query).mappings()
        return AdReportingReportViewList.validate_python(result)

    def count_by_criteria(self, criteria: AdReportingReportCriteria) -> int:
        query = self.db.query_from_template(
            "ad-reporting/report/count-by-criteria.sql",
            context={"criteria": criteria},
        )
        result = self.db.session.execute(query)
        return cast(int, result.scalar_one())

    def get_by_id_and_identity_id(
        self, report_id: str, identity_id: str
    ) -> AdReportingReport | None:
        query = (
            sa.select(AdReportingReport)
            .where(
                sa.and_(
                    AdReportingReport.id == report_id,
                    AdReportingReport.identity_id == identity_id,
                )
            )
            .options(selectinload(AdReportingReport.campaign_associations))
        )
        result = self.db.session.execute(query)
        return result.scalar_one_or_none()

    def exists_by_name_and_identity_id(
        self, name: str, identity_id: str, exclude_id: str | None = None
    ) -> bool:
        clauses = [
            sa.func.lower(AdReportingReport.name) == strip_whitespaces(name.lower()),
            AdReportingReport.identity_id == identity_id,
        ]
        if exclude_id:
            clauses.append(AdReportingReport.id != exclude_id)
        query = sa.select(sa.select(1).exists().where(sa.and_(*clauses)))
        result = self.db.session.execute(query)
        return bool(result.scalar_one())

    def find_by_identity_id_and_campaign_id(
        self,
        *,
        identity_id: str,
        campaign_id: str,
        search: str | None = None,
        limit: int = 100,
    ) -> Sequence[AdReportingCampaignReport]:
        query = self.db.query_from_template(
            "ad-reporting/report/find-by-identity-id-and-campaign-id.sql",
            context={
                "identity_id": identity_id,
                "campaign_id": campaign_id,
                "search": search,
                "limit": limit,
            },
        )
        result = self.db.session.execute(query).mappings()
        return AdReportingCampaignReportList.validate_python(result)

    def find_by_ids_and_identity_id(
        self, report_ids: set[str], identity_id: str
    ) -> Sequence[AdReportingReport]:
        query = (
            sa.select(AdReportingReport)
            .where(
                sa.and_(
                    AdReportingReport.id.in_(report_ids),
                    AdReportingReport.identity_id == identity_id,
                )
            )
            .options(selectinload(AdReportingReport.campaign_associations))
        )
        result = self.db.session.execute(query)
        return result.scalars().all()