import sqlalchemy as sa
from anydi import singleton
from fansifter_common.adapters.db import Database
from fansifter_common.utils.text import strip_whitespaces

from preference_center.profile.models import Profile


@singleton
class ProfileRepository:
    def __init__(self, db: Database) -> None:
        self.db = db

    def get_by_id(self, profile_id: str, /) -> Profile | None:
        query = sa.select(Profile).where(
            Profile.id == profile_id,
            Profile.deleted == sa.false(),
        )
        return self.db.session.execute(query).scalar_one_or_none()

    def get_by_crm_id(self, crm_id: str, /) -> Profile | None:
        query = sa.select(Profile).where(
            Profile.crm_id == crm_id,
            Profile.deleted == sa.false(),
        )
        return self.db.session.execute(query).scalars().first()

    def get_by_email(self, email: str, /) -> Profile | None:
        query = sa.select(Profile).where(
            sa.func.lower(Profile.email) == strip_whitespaces(email.lower()),
            Profile.deleted == sa.false(),
        )
        return self.db.session.execute(query).scalar_one_or_none()

    def exists_by_email(self, email: str, /, *, exclude_profile_id: str) -> bool:
        query = sa.select(
            sa.select(1)
            .exists()
            .where(
                Profile.id != exclude_profile_id,
                sa.func.lower(Profile.email) == strip_whitespaces(email.lower()),
                Profile.deleted == sa.false(),
            )
        )
        result = self.db.session.execute(query)
        return bool(result.scalar_one())