"""DB utils."""

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

from apollo_main_db.apollo.models import ApolloGlobalTrackPrioritiesHistory
from apollo_main_db.spotify.models import SpotifyViewPlaylist, ViewPlaylistTypeEnum
from sqlalchemy import create_engine
from sqlalchemy.orm import Session, joinedload, scoped_session, sessionmaker

import config

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 Exception:
        _session.rollback()
        raise
    finally:
        _session.close()


def get_playlists() -> List[SpotifyViewPlaylist]:
    """Get all GTP playlists.

    Returns:
        Set of GTP playlists.

    """
    with session_scope() as session:
        results = session.query(SpotifyViewPlaylist).filter(SpotifyViewPlaylist.type == ViewPlaylistTypeEnum.GTP).all()
        session.expunge_all()
        return results


def get_history(session: Session, history_date: date, type_id: int) -> ApolloGlobalTrackPrioritiesHistory:
    """Get history record with all inner track objects by date and type ID.

    Args:
        session: DB session.
        history_date: History date.
        type_id: Type ID.

    Returns:
        History record.

    """
    return (
        session.query(ApolloGlobalTrackPrioritiesHistory)
        .options(joinedload(ApolloGlobalTrackPrioritiesHistory.tracks))
        .filter(ApolloGlobalTrackPrioritiesHistory.date == history_date)
        .filter(ApolloGlobalTrackPrioritiesHistory.type_id == type_id)
        .first()
    )