from collections.abc import Sequence

import sqlalchemy as sa

from ows_text_campaigns.adapters.db import Repository
from ows_text_campaigns.campaigns.enums import MessageChannel
from ows_text_campaigns.twilio.models import TwilioMessagePricingByCountry


class TwilioMessagePricingByCountryRepository(
    Repository[TwilioMessagePricingByCountry]
):
    def find_by_countries_and_channels(
        self, countries: list[str], channels: list[MessageChannel]
    ) -> Sequence[TwilioMessagePricingByCountry]:
        if not countries:
            return []
        query = self.db.query_from_template(
            "twilio/find_by_countries_channels.sql",
            context={
                "criteria": {
                    "countries": countries,
                    "message_type": channels,
                }
            },
        )

        return (
            self.db.session.execute(
                sa.select(TwilioMessagePricingByCountry).from_statement(query)
            )
            .scalars()
            .all()
        )