from api.resources import AuthenticatedResource
from tracker import db
from api import api_errors


TheSession = db.ROSession


class SoundCloudActivity(AuthenticatedResource):
    def get(self):
        activity_rows = [
            r[0]
            for r in TheSession.execute(
                """
with
likes as (
  select
    seen_at as "time",
    influencer_scid as "influencer_id",
    artist_scid as "artist_id",
    'Like'::text as "type",
    track_scid as track_id
  from sc_likes 
  join sc_tracks on sc_tracks.scid = track_scid
  join users_sc_influencers influencer on influencer.scid = influencer_scid and influencer.username = :username
),

followings as (
  select
     seen_at as "time",
    influencer_scid as influencer_id,
    artist_scid as artist_id,
    'Follow'::text as "type",
    null::bigint as track_id
  from sc_followings 
  join sc_users on sc_users.scid = artist_scid
  join users_sc_influencers influencer on influencer.scid = influencer_scid and influencer.username = :username
),

events as (
  select * from likes
  UNION ALL 
  select * from followings
),

activity as (
  select  
    newer.time as "time",
    newer.artist_id as "artist_id",
    newer.influencer_id as "influencer_id",
    newer.type,
    COALESCE(
      json_agg(
        json_build_object('time', older.time, 'influencer_id', older.influencer_id, 'type', older.type)
          order by older.time desc)
          filter (where older.time is not null),
      '[]'::json
    ) as "previous"
  from events newer
  left join events older on newer.artist_id = older.artist_id
    and newer.time > older.time
  where newer.time > now() - interval '120 days'
  group by 1, 2, 3, 4
  order by 1 desc, 2 asc
)
select row_to_json(activity.*) from activity limit 30000
            
            """,
                params=dict(username=self.user.name),
            )
        ]

        influencers = {
            row[0]: row[1]
            for row in TheSession.execute(
                """
with 

influencers as (
  select
    su.scid as "id",
    su.name as "name",
    su.data->>'username' as "username",
    su.data->>'avatar_url' as "thumbnail",
    trim(BOTH from su.data->>'description') as "description",
    influencer.category
  from sc_users su 
  join users_sc_influencers influencer on influencer.scid = su.scid and influencer.username = :username
)
select id, row_to_json(influencers.*) from influencers
            
            """,
                params=dict(username=self.user.name),
            ).fetchall()
        }

        artists = {
            row[0]: row[1]
            for row in TheSession.execute(
                """
with 
likes as (
  select
    seen_at as "time",
    influencer_scid as "influencer_id",
    artist_scid as "artist_id",
    'Like'::text as "type",
    track_scid as track_id
  from sc_likes 
  join sc_tracks on sc_tracks.scid = track_scid
  join users_sc_influencers influencer on influencer.scid = influencer_scid and influencer.username = :username
  where sc_likes.seen_at > now() - interval '90 days'
),

followings as (
  select
     seen_at as "time",
    influencer_scid as influencer_id,
    artist_scid as artist_id,
    'Follow'::text as "type",
    null::bigint as track_id
  from sc_followings 
  join sc_users on sc_users.scid = artist_scid
  join users_sc_influencers influencer on influencer.scid = influencer_scid and influencer.username = :username
  where sc_followings.seen_at > now() - interval '90 days'
),

events as (
  select distinct artist_id from likes
  UNION 
  select distinct artist_id from followings
),

artists as (
  select
    su.scid as "id",
    su.name as "name",
    su.data->>'username' as "username",
    su.data->>'avatar_url' as "thumbnail",
    su.data->>'permalink_url' as "url",
    trim(BOTH from su.data->>'description') as "description",
    su.first_seen,
    su.data->>'followers_count' as "total_followers",
    COALESCE (countries.iso2, '00') as country_code
  from sc_users su 
  left join isrc_country_codes countries on countries.lower_sc_name = lower(su.data->>'country')
  where su.scid = any ((select array_agg(artist_id)::int[] from events)::int[])
)
select id, row_to_json(artists.*) from artists limit 30000

                    """,
                params=dict(username=self.user.name),
            ).fetchall()
        }

        return {
            "activity": activity_rows,
            "influencers": influencers,
            "artists": artists,
        }


"""

{"id": 193940126, 
"uri": "https://api.soundcloud.com/users/193940126", 
"city": "Bistrica ob Dravi", 
"kind": "user", 
"plan": "Free", 
"online": false, 
"country": "Slovenia", 
"website": null, 
"username": "Emiljo A.C. - Remixes & Sh1t", 
"full_name": "Fck Copyrights", 
"last_name": "Copyrights", 
"permalink": "user-259475109", 
"avatar_url": "https://i1.sndcdn.com/avatars-000289890527-ehk8i1-large.jpg", 
"first_name": "Fck", 
"description": "My remixes were feelin sad on my hard drive so I've decided to make their dreams come true and bless them with their very own Soundcloud profile!", 
"likes_count": 0, "track_count": 34, "discogs_name": null, 
"myspace_name": null, "last_modified": "2017/01/19 06:30:53 +0000", 
"permalink_url": "http://soundcloud.com/user-259475109", 
"reposts_count": 2, "website_title": null, "comments_count": 0, 
"playlist_count": 1, "followers_count": 307, "followings_count": 4, "public_favorites_count": 0}
"""

from flask import request
from itertools import chain


def _expanding_query(sql, term_no_percents, enough=30):
    term = term_no_percents.lower()
    total_count = 0
    for o in db.execute_to_dicts(sql, params=dict(query=term)):
        total_count = total_count + 1
        yield o
    if total_count <= enough:
        for o in db.execute_to_dicts(sql, params=dict(query=term + "%")):
            total_count = total_count + 1
            yield o
    if total_count <= enough:
        for o in db.execute_to_dicts(sql, params=dict(query="% " + term + "%")):
            total_count = total_count + 1
            yield o
    if total_count <= enough:
        for o in db.execute_to_dicts(sql, params=dict(query="%" + term + "%")):
            total_count = total_count + 1
            yield o


from tracker import search


class SoundCloudTrackSearch(AuthenticatedResource):
    def get(self):
        query = request.args.get("q")
        if not query:
            return {"results": []}

        return {
            "results": [
                {
                    "scid": hit["id"],
                    "title": hit["title"],
                    "username": hit.get("artist") or None,
                }
                for hit in search.query(query=query, doc_type="sc_track")
            ]
        }


class SoundCloudTrackDetails(AuthenticatedResource):
    def get(self, scid):

        print(f"Getting track details {scid}")

        return next(
            db.execute_to_dicts(
                """
  with brains_style_series as (
  select
    track_scid,
    jsonb_agg_uniq_by_first_item(
      jsonb_build_array(to_stat_date(sc_track_stats.as_of), sc_track_stats.playback_count)
      order by sc_track_stats.as_of asc
    ) filter (
      where sc_track_stats.playback_count is not null and (sc_track_stats.playback_count)::int <> 0
    ) as playback_count,
     jsonb_agg_uniq_by_first_item(
      jsonb_build_array(to_stat_date(sc_track_stats.as_of), sc_track_stats.comment_count)
      order by sc_track_stats.as_of asc
    ) filter (
      where sc_track_stats.comment_count is not null and (sc_track_stats.comment_count)::int <> 0
    ) as comment_count,
     jsonb_agg_uniq_by_first_item(
      jsonb_build_array(to_stat_date(sc_track_stats.as_of), sc_track_stats.favoritings_count)
      order by sc_track_stats.as_of asc
    ) filter (
      where sc_track_stats.favoritings_count is not null and (sc_track_stats.favoritings_count)::int <> 0
    ) as favoritings_count,
     jsonb_agg_uniq_by_first_item(
      jsonb_build_array(to_stat_date(sc_track_stats.as_of), sc_track_stats.download_count)
      order by sc_track_stats.as_of asc
    ) filter (
      where sc_track_stats.download_count is not null and (sc_track_stats.download_count)::int <> 0
    ) as download_count
  from sc_track_stats
  where track_scid = :scid
  group by track_scid
    
)
select
  t.scid as track_scid,
  to_jsonb(t.*) as track_info,
  jsonb_build_object('series', b.playback_count) as playback_stats,
  jsonb_build_object('series', b.comment_count) as comment_stats,
  jsonb_build_object('series', b.download_count) as download_stats,
  jsonb_build_object('series', b.favoritings_count) as favoritings_stats
from sc_tracks_all t
left join brains_style_series b on b.track_scid = t.scid
where t.scid = :scid;
                        """,
                params=dict(scid=scid),
            ),
            None,
        )