from collections.abc import Sequence
from dataclasses import dataclass
from typing import Any, ClassVar, 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, selectinload
from sqlalchemy.orm.interfaces import ORMOption

from email_campaigns.adapters.db import Repository
from email_campaigns.automated.models import AutomatedEmail, AutomatedEmailTrigger
from email_campaigns.automated.types import (
    AutomatedEmailCIFields,
    AutomatedEmailCriteria,
    AutomatedEmailOrderBy,
)


@dataclass
class AutomatedEmailStatusRow:
    has_unapplied_changes: bool
    is_automated: bool
    vendor_id: int
    subaccount_id: int


class AutomatedEmailRepository(Repository[AutomatedEmail]):
    default_options: ClassVar[Sequence[ORMOption]] = [
        joinedload(AutomatedEmail.email_domain),
        selectinload(AutomatedEmail.source_campaign_connections).joinedload(
            AutomatedEmailTrigger.source_campaign
        ),
    ]

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

    def get_status(self, ident: str) -> AutomatedEmailStatusRow | None:
        is_automated = (
            sa.select(AutomatedEmailTrigger.automated_email_id)
            .where(AutomatedEmailTrigger.automated_email_id == AutomatedEmail.id)
            .exists()
        )
        stmt = sa.select(
            AutomatedEmail.has_unapplied_changes,
            is_automated.label("is_automated"),
            AutomatedEmail.vendor_id,
            AutomatedEmail.subaccount_id,
        ).where(
            AutomatedEmail.id == ident,
            AutomatedEmail.deleted_at.is_(None),
        )
        row = self.db.session.execute(stmt).one_or_none()
        if row is None:
            return None
        return AutomatedEmailStatusRow(
            has_unapplied_changes=row.has_unapplied_changes,
            is_automated=row.is_automated,
            vendor_id=row.vendor_id,
            subaccount_id=row.subaccount_id,
        )

    def find_by_source_campaign_id(
        self, source_campaign_id: str
    ) -> Sequence[AutomatedEmail]:
        query = (
            sa.select(AutomatedEmail)
            .join(
                AutomatedEmailTrigger,
                AutomatedEmail.id == AutomatedEmailTrigger.automated_email_id,
            )
            .where(
                AutomatedEmailTrigger.source_campaign_id == source_campaign_id,
                AutomatedEmail.deleted_at.is_(None),
            )
            .options(*self.default_options)
        )
        return self.db.session.execute(query).scalars().all()

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

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