"""DB utils."""

from contextlib import contextmanager
from datetime import date
from typing import List

from apollo_main_db.spotify.models import SpotifyPlaylistTrackListHistory2, SpotifyTrack2, \
    SpotifyTrackPlaylistLastWeekHistory
from sqlalchemy import create_engine, func
from sqlalchemy.dialects.mysql import insert
from sqlalchemy.orm import scoped_session, sessionmaker

import config
import utils


engine = create_engine(
    "mysql+pymysql://{user}:{password}@{host}:{port}/{database_name}".format(
        user=config.MySQL.USER,
        password=config.MySQL.PASSWORD,
        host=config.MySQL.HOST,
        port=config.MySQL.PORT,
        database_name=config.MySQL.DATABASE,
    ),
    pool_recycle=config.MySQL.POOL_RECYCLE,
    pool_size=config.MySQL.POOL_SIZE,
)
_session_factory = scoped_session(sessionmaker(bind=engine))
_session = _session_factory()


@contextmanager
def session_scope():
    """Provide a transactional scope around a series of operations.

    """
    try:
        yield _session
        _session.commit()
    except:
        _session.rollback()
        raise
    finally:
        _session.close()


@utils.handle_errors()
def delete_old_history(min_history_date: date) -> int:
    """Delete records older than some date.

    Args:
        min_history_date: Min date that should stay.

    Returns:
        Affected records count.

    """
    with session_scope() as session:
        return (
            session.query(SpotifyTrackPlaylistLastWeekHistory)
            .filter(SpotifyTrackPlaylistLastWeekHistory.date < min_history_date)
            .delete(synchronize_session=False)
        )


@utils.handle_errors()
def get_last_date() -> date:
    """Get copied history max date.

    Returns:
        Max processed/copied date.

    """
    with session_scope() as session:
        result = session.query(func.max(SpotifyTrackPlaylistLastWeekHistory.date)).one()
        return result[0]


@utils.handle_errors()
def get_playlist_id_chunk(previous_playlist_id: str, limit: int) -> List[str]:
    """Get next playlist ID chunk.

    Args:
        previous_playlist_id: Previous chunk max playlist ID.
        limit: Chunk size.

    Returns:
        Playlist ID chunk.

    """
    with session_scope() as session:
        result = (
            session.query(SpotifyPlaylistTrackListHistory2.playlist_id)
            .distinct()
            .filter(SpotifyPlaylistTrackListHistory2.playlist_id > previous_playlist_id)
            .limit(limit)
            .all()
        )

        return list(i[0] for i in result)


@utils.handle_errors()
def copy_history_chunk(history_date: date, playlist_ids: List[str]) -> int:
    """Copy track playlist history for playlist ID chunk and specific date.

    Args:
        history_date: Current processing date.
        playlist_ids: Playlist ID chunk.

    Returns:
        int: Row count.

    """
    with session_scope() as session:
        select_query = (
            session.query(
                SpotifyPlaylistTrackListHistory2.date, SpotifyTrack2.isrc, SpotifyPlaylistTrackListHistory2.playlist_id,
            )
            .select_from(SpotifyPlaylistTrackListHistory2)
            .join(SpotifyTrack2, SpotifyTrack2.id == SpotifyPlaylistTrackListHistory2.track_id)
            .filter(SpotifyPlaylistTrackListHistory2.playlist_id.in_(playlist_ids))
            .filter(SpotifyPlaylistTrackListHistory2.date == history_date)
            .filter(SpotifyTrack2.isrc != "")
            .subquery()
        )

        insert_query = insert(SpotifyTrackPlaylistLastWeekHistory).from_select(
            [select_query.c.Date, select_query.c.ISRC, select_query.c.PlaylistId], select_query)

        on_duplicate_key_query = insert_query.on_duplicate_key_update(PlaylistId=insert_query.inserted.PlaylistId)

        result = session.execute(on_duplicate_key_query)

        return result.rowcount