from sqlalchemy import asc
from sqlalchemy import desc

from project_name import mysql
from project_name.model.country import Country
from project_name.model.genre import Genre
from project_name.model.spotify_trend import SpotifyTrend
from project_name.model.subgenre import Subgenre


def get_spotify_trend_from_db(
        country_code, top_n_tracks, lower_threshold, genre_id, subgenre_id):
    params = {}
    if country_code:
        params = {'country_code': country_code}
    if genre_id:
        params.update({'genre_id': genre_id})
    if subgenre_id:
        params.update({'subgenre_id': subgenre_id})
    session = mysql.project_name_session()

    if country_code:
        db_recs = session.query(
            SpotifyTrend.track_uri,
            SpotifyTrend.last_count,
            SpotifyTrend.trend_pct
        ).filter_by(**params)\
            .filter(SpotifyTrend.last_count >= lower_threshold)\
            .order_by(desc(SpotifyTrend.trend_pct)).limit(top_n_tracks)
    else:
        genre_clause = 'AND spotify_trend.genre_id = {}'\
            .format(genre_id) if genre_id else ''
        subgenre_clause = 'AND spotify_trend.subgenre_id = {}'\
            .format(subgenre_id) if subgenre_id else ''

        sql = """
            SELECT
                spotify_trend.track_uri,
                SUM(spotify_trend.last_count) AS last_count,
                sum(spotify_trend.trend_pct) AS trend_pct
            FROM
                spotify_trend
            WHERE
                spotify_trend.last_count >= {lower_threshold}
                {genre_clause}
                {subgenre_clause}
                GROUP BY spotify_trend.track_uri
                ORDER BY spotify_trend.trend_pct DESC
                LIMIT {top_n_tracks}
        """.format(
            genre_clause=genre_clause,
            subgenre_clause=subgenre_clause,
            lower_threshold=lower_threshold,
            top_n_tracks=top_n_tracks
        )
        db_recs = session.execute(sql)

    ret_list = [{
        'track_uri': track_uri,
        'last_count': last_count,
        'trend_pct': trend_pct
    } for track_uri, trend_pct, last_count in db_recs]

    session.close()
    return ret_list


def get_countries_from_db():
    session = mysql.project_name_session()
    db_recs = session.query(Country)
    ret_list = [u.__dict__ for u in db_recs]
    for d in ret_list:
        d.pop('_sa_instance_state')
    session.close()
    return ret_list


def get_genres():
    session = mysql.project_name_session()
    db_recs = session.query(Genre).order_by(asc(Genre.name))
    ret_list = [u.__dict__ for u in db_recs]
    session.close()
    return ret_list


def get_subgenres(genre_id=None):
    params = {}
    if genre_id is not None:
        params = {'genre_id': genre_id}
    session = mysql.project_name_session()
    db_recs = session.query(Subgenre).filter_by(**params)\
        .order_by(asc(Subgenre.name))
    ret_list = [u.__dict__ for u in db_recs]
    session.close()
    return ret_list