from datetime import datetime, timezone
from typing import Dict, List, Optional, Tuple

from sqlalchemy import func
from sqlalchemy.ext.asyncio import AsyncSession
from sqlmodel import delete as sql_delete
from sqlmodel import select, text

from playlist_sync.models.insert_media import InsertMedia
from playlist_sync.models.service_account import ServiceAccount
from playlist_sync.models.sync_log import PlaylistSynchronizationLog
from playlist_sync.models.sync_task import PlaylistSynchronization

# Fields that may be modified via the update endpoint (mirrors .NET UpdatePlaylistSync)
_UPDATABLE_FIELDS = {
    "from_playlist_id",
    "to_playlist_id",
    "to_service_account_id",
    "title",
    "description",
    "active",
    "title_copy_mode",
    "description_copy_mode",
}


class SyncTaskService:
    def __init__(self, session: AsyncSession):
        self.session = session

    async def get_all_active(self) -> List[PlaylistSynchronization]:
        statement = select(PlaylistSynchronization).where(
            PlaylistSynchronization.active == True
        )  # noqa: E712
        result = await self.session.execute(statement)
        return list(result.scalars().all())

    async def get_all_no_filter(self) -> List[PlaylistSynchronization]:
        """Return all syncs globally (active and inactive)."""
        result = await self.session.execute(select(PlaylistSynchronization))
        return list(result.scalars().all())

    async def get_by_application_id(
        self, application_id: int
    ) -> List[PlaylistSynchronization]:
        statement = (
            select(PlaylistSynchronization)
            .where(PlaylistSynchronization.active == True)  # noqa: E712
            .where(PlaylistSynchronization.application_id == application_id)
        )
        result = await self.session.execute(statement)
        return list(result.scalars().all())

    async def get_by_application_id_all(
        self, application_id: int
    ) -> List[PlaylistSynchronization]:
        """Return ALL syncs for an application (active and inactive)."""
        statement = select(PlaylistSynchronization).where(
            PlaylistSynchronization.application_id == application_id
        )
        result = await self.session.execute(statement)
        return list(result.scalars().all())

    async def get_latest_logs_for_sync_ids(
        self, sync_ids: List[int]
    ) -> Dict[int, PlaylistSynchronizationLog]:
        """Return a sync_id → most recent PlaylistSynchronizationLog mapping."""
        if not sync_ids:
            return {}
        subq = (
            select(
                PlaylistSynchronizationLog.sync_id,
                func.max(PlaylistSynchronizationLog.time).label("max_time"),
            )
            .where(PlaylistSynchronizationLog.sync_id.in_(sync_ids))  # type: ignore[attr-defined]
            .group_by(PlaylistSynchronizationLog.sync_id)  # type: ignore[arg-type]
            .subquery()
        )
        stmt = select(PlaylistSynchronizationLog).join(
            subq,
            (PlaylistSynchronizationLog.sync_id == subq.c.sync_id)
            & (PlaylistSynchronizationLog.time == subq.c.max_time),  # type: ignore[arg-type]
        )
        result = await self.session.execute(stmt)
        return {log.sync_id: log for log in result.scalars().all()}

    async def get_insert_media_for_sync_ids(
        self, sync_ids: List[int]
    ) -> Dict[int, List[InsertMedia]]:
        """Return a mapping of sync_id → list of InsertMedia rows."""
        if not sync_ids:
            return {}
        stmt = select(InsertMedia).where(InsertMedia.playlist_sync_id.in_(sync_ids))  # type: ignore[attr-defined]
        result = await self.session.execute(stmt)
        mapping: Dict[int, List[InsertMedia]] = {}
        for item in result.scalars().all():
            mapping.setdefault(item.playlist_sync_id, []).append(item)
        return mapping

    async def get_by_id(self, sync_id: int) -> Optional[PlaylistSynchronization]:
        statement = select(PlaylistSynchronization).where(
            PlaylistSynchronization.id == sync_id
        )
        result = await self.session.execute(statement)
        return result.scalar_one_or_none()

    async def get_active_by_from_playlist_id(
        self, from_playlist_id: str
    ) -> List[PlaylistSynchronization]:
        """Return all active syncs that share the given source playlist.

        Used by the forceAllSources execute path to fan-out a manual trigger to every
        duplication record pointing at the same source.
        """
        statement = (
            select(PlaylistSynchronization)
            .where(PlaylistSynchronization.active == True)  # noqa: E712
            .where(PlaylistSynchronization.from_playlist_id == from_playlist_id)
        )
        result = await self.session.execute(statement)
        return list(result.scalars().all())

    async def get_by_to_playlist_id(
        self, to_playlist_id: str
    ) -> Optional[PlaylistSynchronization]:
        """Used to check for duplicate target playlist on create/update."""
        statement = select(PlaylistSynchronization).where(
            PlaylistSynchronization.to_playlist_id == to_playlist_id
        )
        result = await self.session.execute(statement)
        return result.scalar_one_or_none()

    async def create_sync(
        self, sync_data: PlaylistSynchronization
    ) -> PlaylistSynchronization:
        self.session.add(sync_data)
        await self.session.commit()
        await self.session.refresh(sync_data)
        return sync_data

    async def update_sync(
        self, sync_id: int, update_data: dict
    ) -> Optional[PlaylistSynchronization]:
        """Apply only _UPDATABLE_FIELDS keys; prevents id/application_id overwrites."""
        statement = select(PlaylistSynchronization).where(
            PlaylistSynchronization.id == sync_id
        )
        result = await self.session.execute(statement)
        sync = result.scalar_one_or_none()
        if sync:
            for key, value in update_data.items():
                if key in _UPDATABLE_FIELDS:
                    setattr(sync, key, value)
            sync.last_updated = datetime.now(timezone.utc)
            await self.session.commit()
            await self.session.refresh(sync)
        return sync

    async def update_sync_result(
        self,
        sync: PlaylistSynchronization,
    ) -> None:
        """Write back the post-sync fields.

        Updates: SourceTitle, SourceImage, SourceTrackCount, SynchronizedTrackCount,
        LastUpdated, Error.

        Mirrors: PlaylistSynchronizationManager.UpdatePlaylistSynchronizationResult()
        """
        statement = select(PlaylistSynchronization).where(
            PlaylistSynchronization.id == sync.id
        )
        result = await self.session.execute(statement)
        db_sync = result.scalar_one_or_none()
        if db_sync:
            db_sync.source_title = sync.source_title
            db_sync.source_image = sync.source_image
            db_sync.source_track_count = sync.source_track_count
            db_sync.synchronized_track_count = sync.synchronized_track_count
            db_sync.last_updated = sync.last_updated
            db_sync.error = sync.error
            db_sync.source_service_account_id = sync.source_service_account_id
            db_sync.source_service_account_name = sync.source_service_account_name
            db_sync.to_playlist_title = sync.to_playlist_title
            db_sync.to_playlist_image = sync.to_playlist_image
            await self.session.commit()

    async def delete_sync(self, sync_id: int) -> bool:
        statement = sql_delete(PlaylistSynchronization).where(
            PlaylistSynchronization.id == sync_id  # type: ignore[arg-type]
        )
        result = await self.session.execute(statement)
        await self.session.commit()
        return result.rowcount > 0  # type: ignore[attr-defined, no-any-return]


class SyncLogService:
    def __init__(self, session: AsyncSession):
        self.session = session

    async def add_log(
        self, log_data: PlaylistSynchronizationLog
    ) -> PlaylistSynchronizationLog:
        self.session.add(log_data)
        await self.session.commit()
        await self.session.refresh(log_data)
        return log_data

    async def get_logs_by_sync_id(
        self, sync_id: int, limit: int = 100, offset: int = 0
    ) -> List[PlaylistSynchronizationLog]:
        statement = (
            select(PlaylistSynchronizationLog)
            .where(PlaylistSynchronizationLog.sync_id == sync_id)
            .order_by(PlaylistSynchronizationLog.time.desc())  # type: ignore[attr-defined]
            .limit(limit)
            .offset(offset)
        )
        result = await self.session.execute(statement)
        return list(result.scalars().all())


class ServiceAccountService:
    def __init__(self, session: AsyncSession):
        self.session = session

    async def get_by_ids(self, account_ids: List[int]) -> Dict[int, "ServiceAccount"]:
        """Return a mapping of account_id → ServiceAccount for the given ids."""
        if not account_ids:
            return {}
        statement = select(ServiceAccount).where(ServiceAccount.id.in_(account_ids))  # type: ignore[union-attr]
        result = await self.session.execute(statement)
        return {a.id: a for a in result.scalars().all() if a.id is not None}

    async def get_by_id(self, account_id: int) -> Optional[ServiceAccount]:
        statement = select(ServiceAccount).where(ServiceAccount.id == account_id)
        result = await self.session.execute(statement)
        return result.scalar_one_or_none()

    async def get_by_application_id(self, application_id: int) -> List[ServiceAccount]:
        statement = select(ServiceAccount).where(
            ServiceAccount.application_id == application_id
        )
        result = await self.session.execute(statement)
        return list(result.scalars().all())

    async def get_all(self) -> List[ServiceAccount]:
        statement = select(ServiceAccount)
        result = await self.session.execute(statement)
        return list(result.scalars().all())

    async def update_tokens(
        self, account_id: int, access_token: str, expiry: int
    ) -> None:
        """Persist a refreshed access token and expiry (Unix ts) to tblServiceAccount.

        Called after a successful token refresh so the next sync starts with a valid
        token rather than always refreshing on every run.
        """
        statement = select(ServiceAccount).where(ServiceAccount.id == account_id)
        result = await self.session.execute(statement)
        account = result.scalar_one_or_none()
        if account:
            account.access_token = access_token
            account.access_token_expiry = expiry
            account.updated_date = datetime.now(timezone.utc)
            await self.session.flush()

    async def get_by_music_service_and_user_identifier(
        self, music_service_id: int, user_identifier: str
    ) -> Optional[ServiceAccount]:
        """Uniqueness check: (music_service_id, user_identifier) must be unique."""
        statement = select(ServiceAccount).where(
            ServiceAccount.music_service_id == music_service_id,
            ServiceAccount.user_identifier == user_identifier,
        )
        result = await self.session.execute(statement)
        return result.scalar_one_or_none()

    async def create(self, account: ServiceAccount) -> ServiceAccount:
        self.session.add(account)
        await self.session.commit()
        await self.session.refresh(account)
        return account

    async def save(self, account: ServiceAccount) -> ServiceAccount:
        self.session.add(account)
        await self.session.commit()
        await self.session.refresh(account)
        return account

    async def delete(self, account_id: int) -> bool:
        statement = sql_delete(ServiceAccount).where(ServiceAccount.id == account_id)  # type: ignore[arg-type]
        result = await self.session.execute(statement)
        await self.session.commit()
        return result.rowcount > 0  # type: ignore[attr-defined, no-any-return]

    async def get_spotify_api_keys(self, account_id: int) -> Optional[Tuple[str, str]]:
        """Return (ClientId, SecretId) from tblSpotifyApiKeys for a service account.

        Returns None if no custom Spotify API keys are configured for this account.
        Mirrors: ServiceAccountManager.GetServiceAccountSpotifyClientIdAndSecretAsync()
        """
        sql = text("""
            SELECT k.ClientId, k.SecretId
            FROM tblSpotifyApiKeys k
            LEFT JOIN tblServiceAccount acc ON k.Id = acc.SpotifyClientIdKey
            WHERE acc.Id = :account_id
        """)
        result = await self.session.execute(sql, {"account_id": account_id})
        row = result.first()
        if row and row[0] and row[1]:
            return (row[0], row[1])
        return None