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

class InstagramActivity(AuthenticatedResource):

    def get(self):
        activity_rows = [r[0] for r in db.ROSession.execute('''
with
activity as (
  select  
    newer.first_seen as "time",
    newer.followee_inid as "artist_id",
    newer.followed_by_inid as "influencer_id",
    'Follow' as "type",
    COALESCE(
      json_agg(json_build_object('time', older.first_seen, 'influencer_id', older.followed_by_inid)) filter (where older.first_seen is not null),
      '[]'::json
    ) as "previous"
  from in_follows newer
  join users_in_influencers uii on uii.inid = newer.followed_by_inid and username = :username
  -- join in_artists ia on ia.inid = newer.followee_inid
  join in_user u on u.inid = newer.followee_inid
  left join in_follows older on newer.followee_inid = older.followee_inid
    and newer.first_seen > older.first_seen
  where u.active
    and newer.first_seen::date > current_date - interval '90 days'
  group by 1, 2, 3
  order by 1 desc, 2 asc
  limit 5000
)

select row_to_json(activity.*) from activity

            
            ''', params=dict(username=self.user.name))]

        influencers = {
            row[0]: row[1] for row in db.ROSession.execute('''
with 

users_json as (
  SELECT 
    in_user.inid,
    first_seen,
    page_data->'entry_data'->'ProfilePage'->0->'user' as data,
    uii.category
  from in_user
  join users_in_influencers uii on uii.inid = in_user.inid and username = :username
  where data_last_updated is NOT NULL 
    and page_data::text <> 'null'
    and is_influencer
),

influencers as (
select 
  (data->>'full_name') as name, 
  inid as username,
  'https://www.instagram.com/'||(data->>'username')||'/' as url,
  (data->'followed_by'->>'count')::INTEGER as followed_count,
  (data->>'profile_pic_url') as thumbnail,
  (data->>'biography') as description,
  category
from users_json
)

select username as id, row_to_json(influencers.*) from influencers
            
            ''', params=dict(username=self.user.name)).fetchall()
        }

        artists = {
            row[0]: row[1] for row in db.ROSession.execute('''

with artists as (
select 
  (page_data->'entry_data'->'ProfilePage'->0->'user'->>'full_name') as name, 
  u.inid as username,
  u.first_seen,
  'https://www.instagram.com/'||(page_data->'entry_data'->'ProfilePage'->0->'user'->>'username')||'/' as url,
  (page_data->'entry_data'->'ProfilePage'->0->'user'->'followed_by'->>'count')::INTEGER as total_followers,
  (page_data->'entry_data'->'ProfilePage'->0->'user'->>'profile_pic_url') as thumbnail,
  (page_data->'entry_data'->'ProfilePage'->0->'user'->>'biography') as description
from in_user u
where u.inid = any((
    select array_agg(distinct followee_inid)
    from in_follows
    -- join in_artists ia on ia.inid = in_follows.followee_inid
    join users_in_influencers uii on uii.inid = in_follows.followed_by_inid and username = :username
    where in_follows.first_seen::date > current_date - interval '90 days'
  )::text[])
)


select username as id, row_to_json(artists.*) from artists limit 10000

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

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