from datetime import date, timedelta
from typing import Mapping, Sequence

from sqlalchemy import delete, insert, select

from src.core.services.base import BaseService
from src.db.connector import db_session
from src.enums import ServiceType

from ..models import TracksCache


__all__ = ["TrackCacheService"]


class TrackCacheService(BaseService[TracksCache]):
    model = TracksCache
    cache_expiry_days: int = 7

    def get_tracks(
        self, service_type: ServiceType, isrcs: Sequence[str], expiry_days: int = cache_expiry_days
    ) -> Sequence[TracksCache]:
        stmt = select(self.model).where(
            self.model.service_type == service_type,
            self.model.isrc.in_(isrcs),
            self.model.match_date >= date.today() - timedelta(days=expiry_days),
        )
        return self._select_many(stmt)

    def upsert_tracks(self, service_type: ServiceType, tracks: Mapping[str, str]):
        # There is an index in tblPlaylistSynchronizationTrack table for service_type and isrc
        # but for some unknown reason this index is not unique, so we can't use on_duplicate_key_update here

        # from sqlalchemy.dialects.mysql import insert as mysql_insert
        #
        # stmt = mysql_insert(self.model)\
        #     .values([{
        #         "service_type": service_type,
        #         "isrc": isrc,
        #         "track_id": track_id,
        #     } for track_id, isrc in tracks.items()])
        #
        # do_update_stmt = stmt.on_duplicate_key_update(
        #     {
        #         "TrackId": stmt.inserted.TrackId,
        #         "MatchDate": stmt.inserted.MatchDate
        #     }
        # )
        # self._execute(do_update_stmt, commit=True)

        if not tracks:
            return

        delete_stmt = delete(self.model).where(
            self.model.service_type == service_type, self.model.isrc.in_(tracks.keys())
        )

        insert_stmt = insert(self.model).values(
            [
                {
                    "service_type": service_type,
                    "isrc": isrc,
                    "track_id": track_id,
                }
                for isrc, track_id in tracks.items()
            ]
        )

        with db_session() as session:
            session.execute(delete_stmt)
            session.execute(insert_stmt)