from datetime import UTC, datetime

import sqlalchemy as sa

from app.adapters.briteverify.models import (
    BriteVerifyValidationList,
    BriteVerifyValidationResult,
)
from app.adapters.db.repository import Repository

from .models import BriteVerifyLists, FanProfile


class EmailsRepository(Repository[FanProfile]):
    def get_unverified_emails(self, start_date: str, limit: int = 10) -> list[str]:
        query = self.db.query_from_template(
            "get_unverified_email.sql",
            context={"start_date": start_date, "limit": limit},
        )

        return list(self.db.session.execute(query).scalars().all())

    def create_briteverify_list(
        self, validation_list: BriteVerifyValidationList
    ) -> None:
        now = datetime.now(UTC)
        statement = sa.insert(BriteVerifyLists)
        self.db.session.execute(
            statement,
            {
                "id": validation_list.id,
                "state": validation_list.state,
                "created_at": now,
                "updated_at": now,
            },
        )
        self.db.session.commit()

    def update_briteverify_list_state(
        self, list_id: str, state: str, processed: bool
    ) -> None:
        now = datetime.now(UTC)
        statement = (
            sa.update(BriteVerifyLists)
            .where(BriteVerifyLists.id == list_id)
            .values(state=state, processed=processed, updated_at=now)
        )
        self.db.session.execute(statement)
        self.db.session.commit()

    def get_unprocessed_verify_list_ids(self) -> list[str]:
        statement = (
            sa.select(BriteVerifyLists.id)
            .where(BriteVerifyLists.processed == sa.false())
            .order_by(BriteVerifyLists.updated_at.asc())
        )
        return list(self.db.session.execute(statement).scalars().all())

    def create_pending_briteverify_results(
        self, list_id: str, emails: list[str]
    ) -> None:
        if not emails:
            return

        rows = [
            {
                "email": email,
                "status": "pending",
                "secondary_status": None,
            }
            for email in emails
        ]
        query = self.db.query_from_template(
            "upsert_briteverify_results.sql",
            context={
                "rows": rows,
                "briteverify_list_id": list_id,
            },
        )
        self.db.session.execute(query)
        self.db.session.commit()

    def update_briteverify_results(
        self, list_id: str, results: list[BriteVerifyValidationResult]
    ) -> None:
        if not results:
            return

        rows = [result.email.model_dump() for result in results]

        query = self.db.query_from_template(
            "upsert_briteverify_results.sql",
            context={
                "rows": rows,
                "briteverify_list_id": list_id,
            },
        )
        self.db.session.execute(query)
        self.db.session.commit()