from collections.abc import Sequence
from datetime import datetime
from typing import cast

import sqlalchemy as sa
from fansifter_common.auth.account import Account
from fansifter_common.utils.text import strip_whitespaces
from pydantic import TypeAdapter

from ows_text_campaigns.adapters.db import Repository
from ows_text_campaigns.campaigns.dtos import (
    CampaignAnalytics,
    CampaignDetailedAnalytics,
    CampaignFanCountByCountryChannel,
    CampaignLinkAnalytics,
)
from ows_text_campaigns.campaigns.models import Campaign
from ows_text_campaigns.campaigns.types import (
    CampaignAnalyticsCriteria,
    CampaignCIFields,
    CampaignCriteria,
    CampaignOrderBy,
    SendTimezone,
    ShortenedUrl,
)

CampaignLinkAnalyticsList = TypeAdapter(list[CampaignLinkAnalytics])
CampaignAnalyticsList = TypeAdapter(list[CampaignAnalytics])
CampaignFanCountByCountryChannelList = TypeAdapter(
    list[CampaignFanCountByCountryChannel]
)
SendTimezoneList = TypeAdapter(list[SendTimezone])


class CampaignRepository(Repository[Campaign]):
    def get_by_id(self, campaign_id: str) -> Campaign | None:
        query = sa.select(Campaign).where(
            Campaign.id == campaign_id, Campaign.deleted_at.is_(None)
        )
        result = self.db.session.execute(query)
        return result.scalar_one_or_none()

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

    def find_by_criteria(
        self,
        criteria: CampaignCriteria,
        order_by: list[CampaignOrderBy],
        limit: int,
        offset: int,
    ) -> Sequence[Campaign]:
        query = self.db.query_from_template(
            "campaign/find-by-criteria.sql",
            context={
                "criteria": criteria,
                "order_by": order_by,
                "ci_fields": CampaignCIFields,
                "limit": limit,
                "offset": offset,
            },
        )
        return (
            self.db.session.execute(sa.select(Campaign).from_statement(query))
            .scalars()
            .all()
        )

    def get_link_analytics(
        self,
        criteria: CampaignAnalyticsCriteria,
        long_urls: list[str],
        short_urls: list[ShortenedUrl],
    ) -> list[CampaignLinkAnalytics]:
        query = self.db.query_from_template(
            "campaign/get-link-analytics.sql",
            context={
                "criteria": criteria,
                "long_urls": long_urls,
                "short_urls": short_urls,
            },
        )
        result = self.db.session.execute(query)
        return CampaignLinkAnalyticsList.validate_python(
            result.all(), from_attributes=True
        )

    def get_analytics(self, campaign_ids: Sequence[str]) -> list[CampaignAnalytics]:
        query = self.db.query_from_template(
            "campaign/get-analytics.sql",
            context={
                "campaign_ids": campaign_ids,
            },
        )
        result = self.db.session.execute(query)

        return CampaignAnalyticsList.validate_python(result.all(), from_attributes=True)

    def get_detailed_analytics(
        self, campaign_id: str, has_shortened_urls: bool
    ) -> CampaignDetailedAnalytics:
        query = self.db.query_from_template(
            "campaign/get-detailed-analytics.sql",
            context={
                "campaign_id": campaign_id,
                "has_shortened_urls": has_shortened_urls,
            },
        )
        result = self.db.session.execute(query)

        return CampaignDetailedAnalytics.model_validate(
            result.one(), from_attributes=True
        )

    def get_fan_count_by_country_channel(
        self, audience_id: str
    ) -> list[CampaignFanCountByCountryChannel]:
        query = self.db.query_from_template(
            "campaign/get-fan-count-by-country-channel.sql",
            context={"criteria": {"audience_id": audience_id}},
        )
        result = self.db.session.execute(query)
        return CampaignFanCountByCountryChannelList.validate_python(
            result.all(), from_attributes=True
        )

    def exists_by_name_and_account(
        self, name: str, account: Account, *, exclude: list[str] | None = None
    ) -> bool:
        clause = [
            sa.func.lower(Campaign.name) == strip_whitespaces(name.lower()),
            Campaign.vendor_id == account.vendor_id,
            Campaign.subaccount_id == account.subaccount_id,
            Campaign.deleted_at.is_(None),
        ]
        if exclude:
            clause.append(Campaign.id.notin_(exclude))
        query = sa.select(sa.select(1).exists().where(*clause))
        result = self.db.session.execute(query)
        return bool(result.scalar_one())

    def get_send_timezone(
        self, campaign_id: str, *, send_at: datetime
    ) -> list[SendTimezone]:
        query = self.db.query_from_template(
            "campaign/get-send-timezones.sql",
            context={
                "campaign_id": campaign_id,
                "send_at": send_at,
            },
        )
        result = self.db.session.execute(query)
        return SendTimezoneList.validate_python(map(dict, result.mappings()))