from __future__ import annotations

from typing import Sequence, cast

from sqlalchemy import Select, and_, exists, func, select
from sqlalchemy.orm import aliased, contains_eager, joinedload, subqueryload

from src.core.services.base import BaseService

from ..models import PlaylistSynchronization, PlaylistSynchronizationLog


__all__ = ["PlaylistSyncService"]


class PlaylistSyncService(BaseService[PlaylistSynchronization]):
    model = PlaylistSynchronization

    @staticmethod
    def _get_with_log_query(app_id: int) -> Select:

        latest_log_subquery = (
            select(
                PlaylistSynchronizationLog.playlist_synchronization_id.label("id"),
                func.max(PlaylistSynchronizationLog.timestamp).label("max_timestamp"),
            )
            .group_by(PlaylistSynchronizationLog.playlist_synchronization_id)
            .subquery()
        )

        log_subquery = (
            select(PlaylistSynchronizationLog)
            .join(
                latest_log_subquery,
                and_(
                    PlaylistSynchronizationLog.playlist_synchronization_id == latest_log_subquery.c.id,
                    PlaylistSynchronizationLog.timestamp == latest_log_subquery.c.max_timestamp,
                ),
            )
            .subquery()
        )

        aliased_log_subquery = aliased(PlaylistSynchronizationLog, log_subquery, name="latest_log")

        query = (
            select(PlaylistSynchronization)
            .options(
                subqueryload(PlaylistSynchronization.insert_media),
                joinedload(PlaylistSynchronization.service_account),
                contains_eager(PlaylistSynchronization.latest_log.of_type(aliased_log_subquery)),
            )
            .outerjoin(
                aliased_log_subquery, PlaylistSynchronization.id == aliased_log_subquery.playlist_synchronization_id
            )
            .where(PlaylistSynchronization.application_id == app_id)
        )

        return query

    def select_many_with_log(self, app_id: int) -> Sequence[PlaylistSynchronization]:
        return self._select_many(self._get_with_log_query(app_id))

    def select_one_with_log(self, app_id: int, sync_id: int) -> PlaylistSynchronization | None:
        stmt = self._get_with_log_query(app_id).where(PlaylistSynchronization.id == sync_id)
        return self._select_one(stmt)

    def check_unique(self, to_playlist_id: str, excluded_id: int | None = None) -> bool:
        # TODO: it should be a DB constraint, but there is no one
        stmt = select(exists(self.model)).where(self.model.to_playlist_id == to_playlist_id)

        if excluded_id is not None:
            stmt = stmt.where(self.model.id != excluded_id)

        return cast(bool, self._execute(stmt).scalar())

    def get_with_service_account(self, id_: int) -> PlaylistSynchronization | None:
        stmt = select(self.model).options(joinedload(self.model.service_account)).where(self.model.id == id_)
        return self._select_one(stmt)

    def get_to_sync(self) -> Sequence[PlaylistSynchronization]:
        stmt = select(self.model).options(joinedload(self.model.service_account)).where(self.model.active.is_(True))
        return self._select_many(stmt)