from api.resources import AdminResource

from tracker import db

class AdministrateUsers(AdminResource):

    def get(self):
        '''
        COALESCE(
      json_agg(
        json_build_object('time', older.time, 'influencer_id', older.influencer_id, 'type', older.type)) 
          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
  group by 1, 2, 3, 4
  order by 1 desc, 2 asc
)
select row_to_json(activity.*) from activity limit 30000
        rows: [{ team: 'Epic', username: 'joel', influencer_source: 'spotify', influencer_identifier: 'SDF1231', influencer_name: '' }]
        
        { teams: [
        
          { name: 'Epic',
            users: [
              { 'username': 'epic1', 
                 influencers: { 
                   spotify: [ { spyid: 'spy1' } ]
                   soundcloud: [ { scid: 1 } ]
                   twitter: [ { eid: 'twt1' } ]
                   instagram: [ { inid: 'spy1' } ]
                   youtube: [ {ytid: 'yt1' } ]
                   
                 } 
                
            } 
            ]
        
        :return: 
        '''
        return {
            'users_influencers': list(db.execute_to_dicts('''
        
                select u.team, u.username, te.name influencer_name, ui.eid::text influencer_identifier, 'twitter' as influencer_source
                from users_tw_influencers ui join users u using (username) join tracked_entities te using (eid)
                
                union all
                
                select u.team, u.username, '@'||ui.inid influencer_name, ui.inid influencer_identifier, 'instagram' as influencer_source
                from users_in_influencers ui join users u using (username) 
                
                union all
                
                select u.team, u.username, sc.name influencer_name, ui.scid::text influencer_identifier, 'soundcloud' as influencer_source
                from users_sc_influencers ui join users u using (username) join sc_users sc using (scid)
                
                union all
                
                select u.team, u.username, pl.name influencer_name, ui.spyid::text influencer_identifier, 'spotify' as influencer_source
                from users_spy_playlists ui join users u using (username) join spy_playlist pl using (spyid)
                
                union all
                
                select u.team, u.username, ch.name influencer_name, ui.ytid::text influencer_identifier, 'youtube' as influencer_source
                from users_yt_channels ui join users u using (username) join yt_channels ch using (ytid)
            ''')),

            'users': list(db.execute_to_dicts('''
                select * from users    
            ''')),
        }