import sqlalchemy as sa

from dmp.adapters.db import ReportingRepository
from dmp.audiences.dtos import AudienceCriteria
from dmp.audiences.models import AudienceEmailFan


class AudienceEmailFanRepository(ReportingRepository[AudienceEmailFan]):
    def delete_by_audience_id(self, audience_id: str) -> None:
        query = sa.delete(AudienceEmailFan).where(
            AudienceEmailFan.audience_id == audience_id
        )
        self.db.session.execute(query)

    def insert_bulk(self, audience_id: str, criteria: AudienceCriteria) -> int:
        query = self.db.query_from_template(
            "audience-email-fan/insert-bulk.sql",
            context={
                "audience_id": audience_id,
                **criteria.get_context(),
            },
        )
        result = self.db.session.connection().execute(query)
        return result.rowcount

    def insert_bulk_fansifter_only(self, *, audience_id: str) -> int:
        query = self.db.query_from_template(
            "audience-email-fan/insert-bulk-fansifter-only.sql",
            context={
                "audience_id": audience_id,
            },
        )
        result = self.db.session.connection().execute(query)
        return result.rowcount

    def get_recipients_count_by_email_provider(
        self, criteria: AudienceCriteria
    ) -> dict[str, int]:
        query = self.db.query_from_template(
            "audience-email-fan/count-by-email-provider.sql",
            context=criteria.get_context(),
        )
        result = self.db.session.execute(query)
        return {row.email_provider: row.fan_count for row in result}