import sqlalchemy as sa

from email_campaigns.adapters.db import Repository
from email_campaigns.reporting.models import EmailReportingReport


class EmailReportingReportRepository(Repository[EmailReportingReport]):
    def get_by_id_and_identity_id(
        self, report_id: str, identity_id: str
    ) -> EmailReportingReport | None:
        stmt = sa.select(EmailReportingReport).where(
            EmailReportingReport.id == report_id,
            EmailReportingReport.identity_id == identity_id,
        )
        return self.db.session.execute(stmt).scalar_one_or_none()

    def count_by_identity_id(self, identity_id: str, name: str | None = None) -> int:
        stmt = sa.select(sa.func.count(EmailReportingReport.id)).where(
            EmailReportingReport.identity_id == identity_id,
        )
        if name is not None:
            stmt = stmt.where(
                EmailReportingReport.name.ilike(f"%{name}%"),
            )
        return self.db.session.execute(stmt).scalar_one()

    def find_by_identity_id(
        self,
        identity_id: str,
        limit: int,
        offset: int,
        name: str | None = None,
    ) -> list[EmailReportingReport]:
        stmt = (
            sa.select(EmailReportingReport)
            .where(EmailReportingReport.identity_id == identity_id)
            .order_by(EmailReportingReport.created_at.desc())
            .limit(limit)
            .offset(offset)
        )
        if name is not None:
            stmt = stmt.where(
                EmailReportingReport.name.ilike(f"%{name}%"),
            )
        return list(self.db.session.execute(stmt).scalars().all())