from flask import request
import json

from api import api_errors
from api.resources import AuthenticatedResource

from tracker import db


def register_urls(api, root="/api/admin"):
    api.add_resource(Initial, root + "/initial")
    api.add_resource(TrackList, root + "/tracks")
    api.add_resource(AllEligibleTracks, root + "/eligible_tracks")
    api.add_resource(DealDoc, root + "/deal_docs")


def get_eligibile_tracks(inlist=None, limit=500):

    if not inlist:
        whereclause = '''
where length(album_data->>'release_date') >= 10 
  and current_date - (album_data->>'release_date')::date < 40
  and not spy_tracks.tags ?| array['label-sony', 'label-warner', 'label-universal', 'label-other']
  and coalesce(spy_tracks.data->>'popularity', '0')::int between 38 and 90
  and pl.pct_uk_streams is not null
  and COALESCE (pop.d5, 0) >= 38'''
    else:
        whereclause = '''
where spy_tracks.spyid in ('{}')
        '''.format("','".join(inlist))

    return list(db.execute_to_dicts(
        """
select
  spy_tracks.spyid as id,
  first('spotify:track:'||spy_tracks.spyid) "trackUri",
  (album_data->>'release_date') as "releaseDate",
  current_date - first((album_data->>'release_date'))::date as "releasedDaysAgo",
  first(spy_tracks.data->>'popularity') "currentPopularity",

  first(spy_tracks.primary_artist_spyid) as "artistId",
  first(spy_tracks.data->'name') "title",
  first(spy_tracks.data->'artists'->0->'name') "artistName",
  first(pop.vals) as vals,
  COALESCE(first(countries.iso2), '00') as "countryCode",
  current_date as_of,
  first(spy_tracks.first_seen) first_seen,
  json_agg(distinct playlist_spyid) as "playlistIds",
  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))*0.65)::int as "estStreams",
  (round(exp(-0.00115*first(pop.d5)*first(pop.d5) + 0.3*first(pop.d5) + 2.1))*0.65*0.0049)::float as "estGlobalRevenue",
  (round(exp(-0.00115*first(pop.d5)*first(pop.d5) + 0.3*first(pop.d5) + 2.1))*0.65*0.01)::int as "estDailyStreams",
  sum(COALESCE(pl.daily_streams, 0) * COALESCE(pl.pct_uk_streams, 0))::float as "plLocalDaily",
  sum(COALESCE(pl.daily_streams, 0))::float as "plGlobal",
  sum(COALESCE(pl.daily_streams, 0) * COALESCE(pl.pct_uk_streams, 0))::float / (sum(COALESCE(pl.daily_streams, 0))::float + 1.0) + 0.0005 as "localStreamRatio",
  
  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,
  jsonb_agg(
    jsonb_build_array(to_stat_date(stp.as_of::date), stp.value)
    order by stp.as_of asc
  ) filter (
    where stp.value is not null and stp.value::int <> 0
  ) as "popularitySeries"

from spy_tracks
join spy_artists sa on primary_artist_spyid = sa.spyid
join all_popularity pop on pop.track_spyid = spy_tracks.spyid
join spy_track_popularity stp on stp.track_spyid = spy_tracks.spyid
join spy_playlist_track spt on spt.track_spyid = spy_tracks.spyid
join spy_playlist pl on pl.spyid = spt.playlist_spyid
left join isrc_country_codes countries
      on countries.isrc = upper(substring(spy_tracks.data->'external_ids'->>'isrc', 1, 2))

-- 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'

{whereclause}
group by 1
limit {limit}
    """.format(whereclause=whereclause, limit=limit)
    ))


def get_playlists():
    return list(db.execute_to_dicts(
"""
select 
  spyid id,
  name,
  daily_streams as "streamsPerDay",
  country_code as "countryCode"
from spy_playlist
"""
    ))

class Initial(AuthenticatedResource):

    def get(self):
        return {
            "eligibleTracks": get_eligibile_tracks(limit=10),
            "playlists": get_playlists(),
        }


class AllEligibleTracks(AuthenticatedResource):

    def get(self):
        return {
            "tracks": get_eligibile_tracks(limit=500),
        }


class TrackList(AuthenticatedResource):
    def post(self):
        return self.get()
    
    def get(self):
        if request.json.get('ids'):
            ids = request.json['ids']
        else:
            ids = json.loads(request.args.get('ids'))

        if not ids or not isinstance(ids, list):
            raise api_errors.BadRequestError

        return {
            "tracks": get_eligibile_tracks(ids)
        }


class DealDoc(AuthenticatedResource):

    def post(self):
        offer = request.json
        for required in ('id',):
            if not offer.get(required):
                raise api_errors.BadRequestError(required + ' is required')

        from tracker.aws_utils import run_script
        return run_script('rt_create_deal_doc', env_dict={'WL_OFFER_ID': offer['id']})