from api.resources import AuthenticatedResource
from tracker import db
from api import api_errors
from datetime import datetime
from flask import request

from tracker.utils import a_datetime


TheSession = db.ROSession


class TwitterActivity(AuthenticatedResource):

    def get(self):
        if 'justArtists' in request.args:
            return self.get_artists()

        activity_rows = self.get_activity()

        influencers = self.get_influencers()

        artists = self.get_artists(eid_in_list=[row[1] for row in activity_rows[0:50]])

        return {
            "activity": activity_rows,
            "influencers": influencers,
            "artists": artists,
            "nextUrl": "/api/twitter_by_day?justArtists"
        }

    def get_influencers(self):
        return {
            row[0]: row[1] for row in TheSession.execute('''
with 

influencers as (
  select
    te.eid as "id",
    te.name,
    (te.twitter_data->>'screen_name')::text as username,
    (te.twitter_data->>'profile_image_url_https')::text as thumbnail,
    uti.category
  from tracked_entities te 
  join users_tw_influencers uti on uti.eid = te.eid and username = :username
)
select id, row_to_json(influencers.*) from influencers
            
            ''', params=dict(username=self.user.name)).fetchall()
        }


    def get_activity(view):
        return [list(r) for r in TheSession.execute('''
    with
    activity as (
      select
        extract(epoch from newer.seen_at)::int as "time",
        newer.followee_eid as "artist_id",
        newer.influencer_eid as "influencer_id",
        0 as "type",
        COALESCE(
          array_agg(
              array[extract(epoch from older.seen_at)::int, older.influencer_eid]
          ) filter (where older.seen_at is not null),
          array[]::integer[]
        ) as "previous"
      from followings newer
      join users_tw_influencers uti on uti.eid = newer.influencer_eid and username = :username
      join twitter_artist_activity te 
          on te.eid = newer.followee_eid 
          and te.last_followed = newer.seen_at 
          and newer.seen_at > now() - interval '90 days'
      left join followings older on newer.followee_eid = older.followee_eid
        and newer.seen_at > older.seen_at
      group by 1, 2, 3
      order by 1 desc, 2 asc
      limit 5000  -- this is a just in case situation where someone has added a few busy scouts
    )
    select * from activity
        ''', params=dict(username=view.user.name)).fetchall()]


    def get_artists(view, eid_in_list=None):
        if eid_in_list:
            whereclause = "where te.eid = ANY(:eid_in_list)"
        else:
            whereclause = """
            join followings fol on fol.followee_eid = te.eid and fol.seen_at > now() - interval '100 days'
            join users_tw_influencers uti on uti.eid = fol.influencer_eid and username = :username
            join twitter_artist_activity taa on taa.eid = te.eid
            """

        return {
                    row[0]: row[1] for row in TheSession.execute('''
    with 
    artists as (
      select distinct
        te.eid as "id",
        te.name,
        te.twitter_data->'screen_name'::text as username,
        (twitter_data->>'followers_count')::integer total_followers,
        concat('https://twitter.com/', te.twitter_data->>'screen_name') as url,
        te.twitter_data->>'profile_image_url_https' as thumbnail,
        te.first_seen
      from tracked_entities te
      {whereclause}
    )
    select id, to_jsonb(artists.*) from artists
    '''.format(whereclause=whereclause), params=dict(eid_in_list=eid_in_list, username=view.user.name))
            }