"""ISRC cache service.

Manages the tblPlaylistSynchronizationTrack table — a cache of ISRC → platform
track ID mappings to avoid redundant API lookups on every sync run.

Mirrors Sony.Filtr.PlaylistSynchronization.SynchronizationTrackManager from .NET.

Key behaviors mirrored from the .NET implementation:
- ``get_synchronized_tracks``: batch query by ISRC list + service type
- ``save_synchronized_track``: upsert — if a row exists for this ISRC+ServiceType,
  update its MatchDate and delete duplicates; otherwise insert a new row.
- Callers filter by age (e.g. 7-day expiry for Deezer) *after* fetching.
"""

import logging
from datetime import datetime

from sqlalchemy.ext.asyncio import AsyncSession
from sqlmodel import select

from playlist_sync.models.sync_track import SynchronizationTrack

logger = logging.getLogger("playlist_sync.isrc_cache")


async def get_synchronized_tracks(
    isrcs: list[str],
    service_type: int,
    session: AsyncSession,
) -> list[SynchronizationTrack]:
    """Return cached ISRC→TrackId mappings for the given ISRCs and service type.

    Mirrors: SynchronizationTrackManager.GetSynchronizedTracks()
    """
    if not isrcs:
        return []

    statement = select(SynchronizationTrack).where(
        SynchronizationTrack.service_type == service_type,
        SynchronizationTrack.isrc.in_(isrcs),  # type: ignore[attr-defined]
    )
    result = await session.execute(statement)
    return list(result.scalars().all())


async def save_synchronized_track(
    isrc: str,
    service_type: int,
    track_id: str,
    session: AsyncSession,
) -> SynchronizationTrack:
    """Upsert an ISRC→TrackId mapping.

    Logic (mirrors .NET SaveSynchronizationTrackAsync):
    - If no existing row: insert new.
    - If one or more rows exist (ordered by MatchDate desc):
        - Update the newest row's MatchDate to now.
        - Delete all older duplicate rows.

    Mirrors: SynchronizationTrackManager.SaveSynchronizationTrackAsync()
    """
    statement = (
        select(SynchronizationTrack)
        .where(
            SynchronizationTrack.isrc == isrc,
            SynchronizationTrack.service_type == service_type,
        )
        .order_by(SynchronizationTrack.match_date.desc())  # type: ignore[attr-defined]
    )
    result = await session.execute(statement)
    existing = list(result.scalars().all())

    if not existing:
        new_track = SynchronizationTrack(
            isrc=isrc,
            service_type=service_type,
            track_id=track_id,
            match_date=datetime.utcnow(),
        )
        session.add(new_track)
        await session.flush()
        logger.debug(
            "Inserted ISRC cache entry for %s (service %s)", isrc, service_type
        )
        return new_track

    # Update the most-recent entry's MatchDate
    newest = existing[0]
    newest.match_date = datetime.utcnow()
    newest.track_id = track_id  # Update track ID in case it changed
    session.add(newest)

    # Delete duplicates (all but the newest)
    for duplicate in existing[1:]:
        await session.delete(duplicate)

    await session.flush()
    logger.debug("Updated ISRC cache entry for %s (service %s)", isrc, service_type)
    return newest