from collections.abc import Sequence

import sqlalchemy as sa

from dmp.ad_reporting.dtos import (
    AdReportingCampaignBenchmarkByAccountCriteria,
)
from dmp.ad_reporting.models import AdReportingCampaignBenchmarkByAccountDbt
from dmp.adapters.db import ReportingRepository


class AdReportingCampaignBenchmarkByAccountRepository(
    ReportingRepository[AdReportingCampaignBenchmarkByAccountDbt]
):
    def find_by_account_id(
        self, vendor_id: int, subaccount_id: int
    ) -> Sequence[AdReportingCampaignBenchmarkByAccountDbt]:
        query = sa.select(AdReportingCampaignBenchmarkByAccountDbt).where(
            AdReportingCampaignBenchmarkByAccountDbt.vendor_id == vendor_id,
            AdReportingCampaignBenchmarkByAccountDbt.subaccount_id == subaccount_id,
        )
        return self.db.session.execute(query).scalars().all()

    def find_unique_by_criteria(
        self, criteria: AdReportingCampaignBenchmarkByAccountCriteria
    ) -> Sequence[AdReportingCampaignBenchmarkByAccountDbt]:
        if criteria.countries:
            template_name = "ad-reporting/benchmark/find-unique-campaign-benchmarks-by-account-country-criteria.sql"
        else:
            template_name = "ad-reporting/benchmark/find-unique-campaign-benchmarks-by-account-criteria.sql"

        query = self.db.query_from_template(
            template_name,
            context={"criteria": criteria},
        )

        result = self.db.session.execute(
            sa.select(AdReportingCampaignBenchmarkByAccountDbt).from_statement(query)
        )
        return result.scalars().all()