from api import models
from api.api_errors import NotFoundError
from tracker.db import Session as db
from tracker import spotify

from flask import Response

from . import AuthenticatedResource

def execute_to_dicts(query, params=None):
    result_proxy = db.execute(query, params)
    names = [a.name for a in result_proxy.cursor.description]
    return (dict(zip(names, row)) for row in result_proxy.cursor)


from tracker import json_utils


def add_track_tags(track, tags_map):
    track['tags'] = tags_map.get(track['spyid']) or []
    return track


new_sql = """
select
  spy_tracks.spyid,
  first(spy_tracks.primary_artist_spyid) as artist_id,
  first(spy_tracks.data->'name') tune,
  first(spy_tracks.data->'artists'->0->'name') artist,
  first(spy_tracks.data->'external_urls'->>'spotify') link,
  first(pop.vals) as vals,
  COALESCE(first(countries.iso2), '00') as country_code,
  current_date as_of,
  first(spy_tracks.first_seen) first_seen,
  json_agg(distinct playlist_spyid) as lists,
  coalesce(first(spy_tracks.tags), '[]'::jsonb) as "track_tags",
  coalesce(first(sa.tags), '[]'::jsonb) as "artist_tags",
  coalesce(first(sa.data->'genres'), '[]'::jsonb) as "artist_genres",
  coalesce(first((sa.data->'followers'->>'total')::int), 0) as "artist_followers",
  first(spy_tracks.album_data->>'label') as label_name,
  first(spy_tracks.album_data->'copyrights') as copyrights,
  round(exp(-0.00115*first(pop.d5)*first(pop.d5) + 0.3*first(pop.d5) + 2.1))::int as streams_100,
  first(pop.d5) as pop5,
--   first(p5.as_of) as pop5asof,
  first(spy_tracks.first_seen + interval '100 days') as streams_100_date,
  first(spy_tracks.first_seen + interval '100 days')::date - current_date as streams_100_days
from spy_tracks
join all_popularity pop on pop.track_spyid = spy_tracks.spyid
join spy_playlist_track spt on spt.track_spyid = spy_tracks.spyid
join users_spy_playlists usp
      on usp.spyid = spt.playlist_spyid
      and usp.username = :username
left join isrc_country_codes countries 
      on countries.isrc = upper(substring(spy_tracks.data->'external_ids'->>'isrc', 1, 2))
left join spy_artists sa on sa.spyid = spy_tracks.primary_artist_spyid

-- left join spy_track_popularity p5 on p5.track_spyid = spy_tracks.spyid
--   and spy_tracks.first_seen::date + interval '5 days' = p5.as_of::date
--   and p5.value > 30
--   and spy_tracks.first_seen::date > current_date - interval '60 days'
where spt.track_spyid is not null
  and spy_tracks.first_seen::date > current_date - interval '180 days'

group by 1
order by spy_tracks.first_seen desc
limit 5000
"""

class Spotify(AuthenticatedResource):
    def get(self):

        import time
        start_clock = time.clock()
        print("Starting...")

        initial_string = (
            '{"summary":"", '
            '"playlists":%s,'
            '"tracks":[' % json_utils.dumps(list(
                execute_to_dicts("select spy_playlist.spyid, name, category, active from spy_playlist "
                                 "join users_spy_playlists usp on usp.spyid = spy_playlist.spyid "
                                 " and usp.username = :username", params=dict(username=self.user.name))))
        )
        #
        # sql_query = sql_query_template.format(where=' where  playlists.track_spyid is not null ',
        #                                       popularity_filter='')  # needs fixing
        sql_query = new_sql  # needs fixing

        result_proxy = db.execute(sql_query, params=dict(username=self.user.name))

        print("Executed", time.clock() - start_clock)

        def generate():
            """
            A lagging generator to stream JSON so we don't have to hold everything in memory
            This is a little tricky, as we need to omit the last comma to make valid JSON,
            thus we use a lagging generator, similar to http://stackoverflow.com/questions/1630320/
            """
            artists = result_proxy.__iter__()
            print("Streaming", time.clock() - start_clock)
            try:
                prev_artist = next(artists)  # get first result

            except StopIteration:
                # StopIteration here means the length was zero, so yield a valid releases doc and stop
                yield initial_string + ']}'
                raise StopIteration

            # We have some artists. First, yield the opening json
            print("First data...", time.clock() - start_clock)
            yield initial_string

            # Iterate over the artists
            print("rest...", time.clock() - start_clock)
            for artist in artists:
                yield json_utils.dumps(dict(prev_artist)) + ', '
                prev_artist = artist

            # Now yield the last iteration without comma but with the closing brackets
            print('Last row', time.clock() - start_clock)
            yield json_utils.dumps(dict(prev_artist)) + ']}'

        return Response(generate(), content_type='application/json')

    def _get_original(self):

        import time
        start_clock = time.clock()
        print("Starting...")
        sql_query = """
with


all_popularity as (
  select
    track_spyid,
    json_agg(
     ('['||current_date::date - as_of::date||','||value||']')::json order by as_of) vals
  from spy_track_popularity
  group by 1
),

playlists as (
  select track_spyid, json_agg(distinct playlist_spyid) lists
  from spy_playlist_track
  group by 1
)

select
  spy_tracks.spyid,
  spy_tracks.data->'name' tune,
  spy_tracks.data->'artists'->0->'name' artist,
  spy_tracks.data->'external_urls'->>'spotify' link,
  all_popularity.vals,
  current_date as_of,
  spy_tracks.first_seen,
  playlists.lists,
  coalesce(spy_tracks.tags, '[]'::jsonb) as "track_tags",
  coalesce(sa.tags, '[]'::jsonb) as "artist_tags",
  coalesce(sa.data->'genres', '[]'::jsonb) as "artist_genres",
  coalesce((sa.data->'followers'->>'total')::int, 0) as "artist_followers",
  spy_tracks.album_data->'label' as label_name,
  spy_tracks.album_data->'copyrights' as copyrights,
  round(exp(-0.00115*p5.value*p5.value + 0.3*p5.value + 2.1))::int as streams_100,
  p5.value as pop5,
  p5.as_of as pop5asof,
  (spy_tracks.first_seen + interval '100 days') as streams_100_date,
  (spy_tracks.first_seen + interval '100 days')::date - current_date as streams_100_days


from spy_tracks
join all_popularity on all_popularity.track_spyid = spy_tracks.spyid
join playlists on playlists.track_spyid = spy_tracks.spyid
left join spy_artists sa on sa.spyid = spy_tracks.primary_artist_spyid
left join spy_track_popularity p5 on p5.track_spyid = spy_tracks.spyid
  and spy_tracks.first_seen::date + interval '5 days' = p5.as_of::date
  and p5.value > 30
  and spy_tracks.first_seen::date > current_date - interval '60 days'

order by spy_tracks.first_seen desc
limit 10000
        """
        initial_string = (
            '{"summary":"", '
            '"playlists":%s,'
            '"tracks":[' % json_utils.dumps(list(execute_to_dicts("""select spyid, name, category, active from spy_playlist""")))
        )

        result_proxy = db.execute(sql_query)

        print("Executed", time.clock() - start_clock)

        def generate():
            """
            A lagging generator to stream JSON so we don't have to hold everything in memory
            This is a little tricky, as we need to omit the last comma to make valid JSON,
            thus we use a lagging generator, similar to http://stackoverflow.com/questions/1630320/
            """
            artists = result_proxy.__iter__()
            print("Streaming", time.clock() - start_clock)
            try:
                prev_artist = next(artists)  # get first result

            except StopIteration:
                # StopIteration here means the length was zero, so yield a valid releases doc and stop
                yield initial_string + ']}'
                raise StopIteration

            # We have some artists. First, yield the opening json
            print("First data...", time.clock() - start_clock)
            yield initial_string

            # Iterate over the artists
            print("rest...", time.clock() - start_clock)
            for artist in artists:
                yield json_utils.dumps(dict(prev_artist)) + ', '
                prev_artist = artist

            # Now yield the last iteration without comma but with the closing brackets
            print('Last row', time.clock() - start_clock)
            yield json_utils.dumps(dict(prev_artist)) + ']}'

        return Response(generate(), content_type='application/json')



class SampleSpotify(AuthenticatedResource):
    def get(self):
        import os
        import json
        with open(os.path.join(os.path.dirname(__file__), 'samples', 'spotify.json')) as f:
            return json.load(f)


class SpotifyTrackTags(AuthenticatedResource):
    def put(self, track_spyid, tag):
        spotify.tag_track(track_spyid, tag)
        return ''

    def delete(self, track_spyid, tag):
        spotify.untag_track(track_spyid, tag)
        return ''


class SpotifyTrack(AuthenticatedResource):
    def get(self, track_spyid):
        '''Returns {spyid, track: {..}, artist: {..}} to match what's returned from chart requests.'''
        mdls = list(models.spotify_tracks([track_spyid]))
        if not mdls:
            raise NotFoundError("No track " + track_spyid)

        data = mdls[0]
        artist_spyid = data['track']['artist_spyid']
        artist = models.artists(source='spy', ids=[artist_spyid])[0]['artist']
        data['artist'] = artist
        return data