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

import sqlalchemy as sa
from sqlalchemy.orm import with_expression

from email_campaigns.adapters.db import Repository
from email_campaigns.automated.models import SourceCampaign
from email_campaigns.automated.types import (
    SourceCampaignCIFields,
    SourceCampaignCriteria,
    SourceCampaignOrderBy,
)


class SourceCampaignRepository(Repository[SourceCampaign]):
    def find_by_ids(
        self, source_campaign_ids: Sequence[str]
    ) -> Sequence[SourceCampaign]:
        if not source_campaign_ids:
            return []
        query = sa.select(SourceCampaign).where(
            SourceCampaign.id.in_(source_campaign_ids)
        )
        result = self.db.session.execute(query)
        return result.scalars().all()

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

    def find_by_source_and_campaign_id(
        self, source: str, campaign_id: str
    ) -> SourceCampaign | None:
        query = sa.select(SourceCampaign).where(
            sa.func.lower(SourceCampaign.source) == source.lower(),
            SourceCampaign.campaign_id == campaign_id,
        )
        return self.db.session.execute(query).scalar_one_or_none()

    def find_potential_mailing_list_ids(
        self,
        source: str,
        campaign_id: str,
    ) -> dict[str, list[str]]:
        query = self.db.query_from_template(
            "source_campaign/find-potential-mailing-list-ids.sql",
            context={
                "source": source,
                "campaign_id": campaign_id,
            },
        )
        rows = self.db.session.execute(query).fetchall()
        result: dict[str, list[str]] = {}
        for row in rows:
            if row.automated_email_id is None:
                continue
            mailing_list_ids = row.mailing_list_ids
            if mailing_list_ids is None:
                result[row.automated_email_id] = []
                continue
            if isinstance(mailing_list_ids, str):
                mailing_list_ids = json.loads(mailing_list_ids)
            result[row.automated_email_id] = [
                mid for mid in mailing_list_ids if mid is not None
            ]
        return result

    def find_by_criteria(
        self,
        criteria: SourceCampaignCriteria,
        order_by: list[SourceCampaignOrderBy],
        limit: int,
        offset: int,
    ) -> Sequence[SourceCampaign]:
        query = self.db.query_from_template(
            "source_campaign/find-by-criteria.sql",
            context={
                "criteria": criteria,
                "order_by": order_by,
                "limit": limit,
                "offset": offset,
            },
        )
        return (
            self.db.session.execute(
                sa.select(SourceCampaign)
                .from_statement(query)
                .options(
                    with_expression(
                        SourceCampaign.has_other_connections,
                        sa.literal_column("has_other_connections"),
                    )
                )
            )
            .scalars()
            .all()
        )