from collections.abc import Sequence
from typing import Any, cast

import sqlalchemy as sa
from fansifter_common.auth.account import Account
from fansifter_common.utils.text import strip_whitespaces
from sqlalchemy.orm import joinedload

from email_campaigns.adapters.db import Repository
from email_campaigns.campaigns.enums import EmailCampaignStatus
from email_campaigns.campaigns.models import EmailCampaign
from email_campaigns.campaigns.types import (
    EmailCampaignCIFields,
    EmailCampaignCriteria,
    EmailCampaignOrderBy,
)


class EmailCampaignRepository(Repository[EmailCampaign]):
    default_options = (joinedload(EmailCampaign.email_domain),)

    def get(self, ident: Any) -> EmailCampaign | None:
        stmt = (
            sa.select(EmailCampaign)
            .where(
                EmailCampaign.id == ident,
                EmailCampaign.deleted_at.is_(None),
            )
            .options(*self.default_options)
        )
        return self.db.session.execute(stmt).scalar_one_or_none()

    def get_many(self, ids: list[str]) -> list[EmailCampaign]:
        if not ids:
            return []
        stmt = (
            sa.select(EmailCampaign)
            .where(
                EmailCampaign.id.in_(ids),
                EmailCampaign.deleted_at.is_(None),
            )
            .options(*self.default_options)
        )
        return list(self.db.session.execute(stmt).scalars().all())

    def count_by_criteria(self, criteria: EmailCampaignCriteria) -> int:
        query = self.db.query_from_template(
            "email-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: EmailCampaignCriteria,
        order_by: list[EmailCampaignOrderBy],
        limit: int,
        offset: int,
    ) -> Sequence[EmailCampaign]:
        query = self.db.query_from_template(
            "email-campaign/find-by-criteria.sql",
            context={
                "criteria": criteria,
                "order_by": order_by,
                "ci_fields": EmailCampaignCIFields,
                "limit": limit,
                "offset": offset,
            },
        )
        return (
            self.db.session.execute(
                sa.select(EmailCampaign)
                .from_statement(query)
                .options(*self.default_options)
            )
            .scalars()
            .all()
        )

    def search_by_criteria(
        self,
        criteria: EmailCampaignCriteria,
        order_by: list[EmailCampaignOrderBy],
        limit: int,
    ) -> Sequence[EmailCampaign]:
        query = self.db.query_from_template(
            "email-campaign/search-by-criteria.sql",
            context={
                "criteria": criteria,
                "order_by": order_by,
                "ci_fields": EmailCampaignCIFields,
                "limit": limit,
            },
        )
        return (
            self.db.session.execute(
                sa.select(EmailCampaign)
                .from_statement(query)
                .options(*self.default_options)
            )
            .scalars()
            .all()
        )

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

    def find_by_filters(
        self,
        *,
        email_ids: list[str] | None = None,
        global_participant_id: str | None = None,
        vendor_ids: list[int] | None = None,
        subaccount_ids: list[int] | None = None,
    ) -> Sequence[EmailCampaign]:
        stmt = sa.select(EmailCampaign).where(EmailCampaign.deleted_at.is_(None))

        if email_ids is not None:
            stmt = stmt.where(EmailCampaign.id.in_(email_ids))

        if global_participant_id is not None:
            stmt = stmt.where(
                EmailCampaign.global_participant_id == global_participant_id
            )

        if vendor_ids and subaccount_ids:
            stmt = stmt.where(
                sa.or_(
                    EmailCampaign.vendor_id.in_(vendor_ids),
                    EmailCampaign.subaccount_id.in_(subaccount_ids),
                )
            )
        elif vendor_ids:
            stmt = stmt.where(EmailCampaign.vendor_id.in_(vendor_ids))
        elif subaccount_ids:
            stmt = stmt.where(EmailCampaign.subaccount_id.in_(subaccount_ids))

        stmt = stmt.options(*self.default_options)
        return self.db.session.execute(stmt).scalars().all()

    def find_by_ids(self, campaign_ids: list[str]) -> Sequence[EmailCampaign]:
        if not campaign_ids:
            return []

        query = (
            sa.select(EmailCampaign)
            .where(
                EmailCampaign.id.in_(campaign_ids),
                EmailCampaign.deleted_at.is_(None),
            )
            .options(*self.default_options)
        )
        return self.db.session.execute(query).scalars().all()

    def find_pending_campaigns(self, domain_id: str) -> Sequence[EmailCampaign]:
        query = (
            sa.select(EmailCampaign)
            .where(
                EmailCampaign.email_domain_id == domain_id,
                EmailCampaign.status.in_(
                    [
                        EmailCampaignStatus.SCHEDULED,
                        EmailCampaignStatus.IN_PROGRESS,
                    ]
                ),
                EmailCampaign.cancelled_at.is_(None),
                EmailCampaign.deleted_at.is_(None),
            )
            .options(*self.default_options)
        )
        return self.db.session.execute(query).scalars().all()

    def find_pending_campaigns_for_domains(
        self, domain_ids: list[str]
    ) -> Sequence[EmailCampaign]:
        if not domain_ids:
            return []

        query = (
            sa.select(EmailCampaign)
            .where(
                EmailCampaign.email_domain_id.in_(domain_ids),
                EmailCampaign.status.in_(
                    [
                        EmailCampaignStatus.SCHEDULED,
                        EmailCampaignStatus.IN_PROGRESS,
                    ]
                ),
                EmailCampaign.cancelled_at.is_(None),
                EmailCampaign.deleted_at.is_(None),
            )
            .options(*self.default_options)
        )
        return self.db.session.execute(query).scalars().all()