import psycopg2
import pandas as pd
import pandas.io.sql as sqlio

pd.set_option('display.max_columns', None)
pd.set_option('display.max_row', 200)
    
MAX_AS_OF = '2018-07-20'

bb = '2VYF0qtX4pNmMhCgiiajeF'

def getConnection():
    connect_str = "dbname='wl_us_ultra' user='svenski' host='whitelist.cbn1zk7uet6r.eu-west-1.rds.amazonaws.com' password='6y96u8hnbugexa2xfmz4' port='5432'"
    # use our connection values to establish a connection
    conn = psycopg2.connect(connect_str)

    return conn

def work():
    raw_data = sqlio.read_sql_query(f'''
    SELECT * FROM spy_track_streams ss where ss.spyid ='{bb}'
    ''', conn)

    qq = "SELECT distinct t.spyid, album_data->>'release_date' as release_date, first_seen from spy_tracks t limit 3"
    qq = f"SELECT * from spy_tracks t where spyid = '{bb}'"
    release_dates  = sqlio.read_sql_query(qq, conn)


def spotify_queries():
    # tt = sqlio.read_sql(f'SELECT * FROM pg_catalog.pg_tables;', conn)

    raw_data = sqlio.read_sql_query(f'SELECT * FROM spy_track_popularity sp join spy_track_streams ss on sp.track_spyid = ss.spyid  where ss.as_of <= \'{MAX_AS_OF}\'', conn)
    raw_popularity = sqlio.read_sql_query(f'SELECT * FROM spy_track_popularity  where as_of <= \'{MAX_AS_OF}\'', conn)
    raw_streams = sqlio.read_sql_query(f'SELECT * FROM spy_track_streams where as_of <= \'{MAX_AS_OF}\'', conn)
    pop_with_streams = raw_popularity[raw_popularity['track_spyid'].isin(raw_streams['spyid'])]

    raw_popularity.to_feather('../data/raw_popularity.feather')
    raw_streams.to_feather('../data/raw_stream.feather')
    pop_with_streams.reset_index().to_feather('../data/pop_with_streams.feather')

    qq = "SELECT distinct t.spyid, (album_data->>'release_date')::timestamp as release_date, first_seen from spy_tracks t join spy_track_streams s on s.spyid = t.spyid"

    release_dates  = sqlio.read_sql_query(qq, conn)
    release_dates.to_feather('../data/release_dates.feather')

    playlist_data = sqlio.read_sql(f'select * from spy_playlist_track', conn)
    playlist_data.to_feather('../data/playlist_data.feather')


def sound_cloud_queries():
    sound_cloud_track_stats = sqlio.read_sql_query(f'select * from sc_track_stats_27', conn)
    sound_cloud_track_stats.to_feather('../data/sound_cloud_streams.feather')

    # scstats =sqlio.read_sql_query('select track_scid, count(*), min(as_of), max(as_of) from sc_track_stats_27 group by 1 limit 10;', conn)

    sound_cloud_track_info = sqlio.read_sql_query(f'select scid, artist_scid, name, first_seen, created_at, genre from sc_tracks', conn)
    sound_cloud_track_info.to_feather('../data/sound_cloud_info.feather')

    # isrc
    sound_cloud_isrc = sqlio.read_sql_query("select scid, data->>'isrc' as isrc from sc_tracks limit 4", conn)
    spotify_isrc = sqlio.read_sql_query("select data->'external_ids'->>'isrc' as isrc from spy_tracks limit 10;", conn)

    sqlio.read_sql_query('select * from pg_catalog.pg_tables', conn)

        


# Join to remove non-followed tracks
    sc_cloud_stats = """
        select
      track_scid,
      first(data->>'permalink_url') as link,
      max(as_of) last_date,
      first(playback_count order by as_of) first_plays,
      first(playback_count order by as_of desc) last_plays,
      jsonb_agg(playback_count order by as_of)
    from sc_track_stats_27 stats
    join sc_tracks t on t.scid = stats.track_scid and t.artist_scid <> 0

    where track_scid <> 0
    group by 1
    limit 10;
    """

def yt_queries():

    yt_track_stats = sqlio.read_sql_query(f'select * from yt_video_statistics', conn)
    yt_track_stats.to_feather('../data/yt_streams.feather')

    yt_track_info = sqlio.read_sql_query(f'select * from yt_videos', conn)
    yt_track_info.drop('tags', axis=1).to_feather('../data/yt_info.feather')