from collections.abc import Sequence
from typing import cast

import sqlalchemy as sa
from pydantic import TypeAdapter
from sqlalchemy.orm import joinedload

from dmp.ad_reporting.dtos import (
    AdReportingCampaign,
    AdReportingCampaignCriteria,
    AdReportingCampaignIdName,
    AdReportingCampaignObjective,
    AdReportingCampaignObjectiveCriteria,
    AdReportingCampaignToplinePerformance,
    AdReportingSummary,
)
from dmp.ad_reporting.models import (
    AdReportingCampaignCountryDbt,
    AdReportingCampaignDbt,
)
from dmp.ad_reporting.types import (
    AdReportingCampaignCIFields,
    AdReportingCampaignOrderBy,
)
from dmp.adapters.db import ReportingRepository

AdReportingCampaignList = TypeAdapter(list[AdReportingCampaign])
AdReportingCampaignToplinePerformanceList = TypeAdapter(
    list[AdReportingCampaignToplinePerformance]
)
AdReportingCampaignObjectiveList = TypeAdapter(list[AdReportingCampaignObjective])
AdReportingCampaignIdNameList = TypeAdapter(list[AdReportingCampaignIdName])


class AdReportingCampaignRepository(ReportingRepository[AdReportingCampaignDbt]):
    def get_by_id_and_account_ids(
        self, campaign_id: str, account_ids: set[str]
    ) -> AdReportingCampaignDbt | None:
        if not account_ids:
            return None
        query = (
            sa.select(AdReportingCampaignDbt)
            .where(
                sa.and_(
                    AdReportingCampaignDbt.id == campaign_id,
                    AdReportingCampaignDbt.account_id.in_(account_ids),
                )
            )
            .options(joinedload(AdReportingCampaignDbt.artist_associations))
            .order_by(AdReportingCampaignDbt.synced_at.desc())
            .limit(1)
        )
        result = self.db.session.execute(query)
        return result.unique().scalar_one_or_none()

    def find_by_criteria(
        self,
        criteria: AdReportingCampaignCriteria,
        *,
        order_by: list[AdReportingCampaignOrderBy] | None = None,
        limit: int | None = None,
        offset: int | None = None,
    ) -> list[AdReportingCampaign]:
        if criteria.countries:
            template_name = "ad-reporting/campaign/find-by-criteria-country.sql"
        else:
            template_name = "ad-reporting/campaign/find-by-criteria.sql"

        query = self.db.query_from_template(
            template_name,
            context={
                "criteria": criteria,
                "order_by": order_by,
                "ci_fields": AdReportingCampaignCIFields,
                "limit": limit,
                "offset": offset,
            },
        )
        result = self.db.session.execute(query)
        return AdReportingCampaignList.validate_python(result.mappings())

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

    def summary_by_criteria(
        self, criteria: AdReportingCampaignCriteria
    ) -> AdReportingSummary:
        if criteria.countries:
            data_table_name = AdReportingCampaignCountryDbt.__tablename__
            data_campaign_id_column = AdReportingCampaignCountryDbt.campaign_id.name
            data_id_column = AdReportingCampaignCountryDbt.campaign_id.name
        else:
            data_table_name = AdReportingCampaignDbt.__tablename__
            data_campaign_id_column = AdReportingCampaignDbt.id.name
            data_id_column = AdReportingCampaignDbt.id.name

        query = self.db.query_from_template(
            "ad-reporting/summary.sql",
            context={
                "criteria": criteria,
                "id_column": AdReportingCampaignDbt.id.name,
                "data_id_column": data_id_column,
                "table_name": AdReportingCampaignDbt.__tablename__,
                "data_table_name": data_table_name,
                "campaign_id_column": AdReportingCampaignDbt.id.name,
                "global_participant_id_column": AdReportingCampaignDbt.global_participant_id.name,
                "data_campaign_id_column": data_campaign_id_column,
                "objective_column": AdReportingCampaignDbt.objective.name,
                "budget_type_for": "campaign",
            },
        )

        result = self.db.session.execute(query)
        return AdReportingSummary.model_validate(result.mappings().one())

    def find_by_ids_group_by_platform_and_objective(
        self, campaign_ids: set[str]
    ) -> list[AdReportingCampaignToplinePerformance]:
        if not campaign_ids:
            return []
        query = self.db.query_from_template(
            "ad-reporting/campaign/find-by-ids-group-by-platform-and-objective.sql",
            context={"campaign_ids": campaign_ids},
        )
        return AdReportingCampaignToplinePerformanceList.validate_python(
            self.db.session.execute(query).mappings()
        )

    def find_unique_global_participant_ids_by_identity_id(
        self, identity_id: str, report_id: str | None = None
    ) -> Sequence[str]:
        query = self.db.query_from_template(
            "ad-reporting/campaign/find-unique-global-participant-ids-by-identity-id.sql",
            context={
                "identity_id": identity_id,
                "report_id": report_id,
            },
        )
        result = self.db.session.execute(query)
        return result.scalars().all()

    def find_unique_global_participant_ids_by_account_ids(
        self, account_ids: set[str]
    ) -> Sequence[str]:
        if not account_ids:
            return []
        query = self.db.query_from_template(
            "ad-reporting/campaign/find-unique-global-participant-ids-by-account-ids.sql",
            context={"account_ids": account_ids},
        )
        result = self.db.session.execute(query)
        return result.scalars().all()

    def find_ids_by_criteria(
        self, criteria: AdReportingCampaignCriteria
    ) -> Sequence[str]:
        query = self.db.query_from_template(
            "ad-reporting/campaign/find-ids-by-criteria.sql",
            context={"criteria": criteria},
        )
        result = self.db.session.execute(query)
        return result.scalars().all()

    def find_unique_campaigns_objectives_by_criteria(
        self, criteria: AdReportingCampaignObjectiveCriteria
    ) -> list[AdReportingCampaignObjective]:
        query = self.db.query_from_template(
            "ad-reporting/campaign/find-unique-campaigns-objectives-by-criteria.sql",
            context={"criteria": criteria},
        )
        results = AdReportingCampaignObjectiveList.validate_python(
            self.db.session.execute(query).mappings()
        )
        return sorted(
            set(results), key=lambda result: (result.platform, result.objective)
        )  # removing duplicate objectives due to "OTHER" logic

    def find_id_names_by_criteria(
        self,
        criteria: AdReportingCampaignCriteria,
        *,
        limit: int | None = None,
        offset: int | None = None,
    ) -> Sequence[AdReportingCampaignIdName]:
        query = self.db.query_from_template(
            "ad-reporting/campaign/find-id-names-by-criteria.sql",
            context={"criteria": criteria, "limit": limit, "offset": offset},
        )
        result = self.db.session.execute(query)
        return AdReportingCampaignIdNameList.validate_python(result.mappings())