from datetime import datetime, timedelta
import json
import os

from boto.s3.bucket import Bucket
from boto.s3.connection import S3Connection
from boto.s3.key import Key
import psycopg2


s3conn = S3Connection()
db_config = {
  'user':     os.environ.get('REDSHIFT_READ_WRITE_USER'),
  'password': os.environ.get('REDSHIFT_READ_WRITE_PASSWORD'),
  'host':     os.environ.get('REDSHIFT_READ_WRITE_HOST'),
  'port':     os.environ.get('REDSHIFT_READ_WRITE_PORT'),
  'database': os.environ.get('REDSHIFT_READ_WRITE_DB')
}

start = datetime(2015, 9, 1)
end = datetime.today()

interval = timedelta(days=1)

current = start


query_genreless = '''
    select count(*) as everything, aa.track_uri, min(aa.track_name) as track_name, min(aa.user_country) as country, min(g.genrename) as genrename
        from production.staging_raw_spotify_v2 aa
        inner join production.dim_release dr on dr.releaseid = aa.upc
        inner join production.dim_genre g on g.genreid = dr.genreid
        where aa.download_date between '{start}' and '{end}' and aa.user_country = '{country}'
        group by aa.track_uri
        order by count(*) desc
    '''

query_genre = '''
    select count(*) as everything, aa.track_uri, min(aa.track_name) as track_name, min(aa.user_country) as country, min(g.genrename) as genrename
        from production.staging_raw_spotify_v2 aa
        inner join production.dim_release dr on dr.releaseid = aa.upc
        inner join production.dim_genre g on g.genreid = dr.genreid
        where aa.download_date between '{start}' and '{end}' and aa.user_country = '{country}' and g.genreid = {genreid} 
        group by aa.track_uri
        order by count(*) desc
    '''

columns = [
    'everything',
    'track_uri',
    'track_name',
    'country',
    'genrename'
]

countries = ['US', 'GB']
genres = [
    (0, 'all'),
    (1, 'rock'),
    (7, 'pop'),
    (6, 'hiphop')
]

pair = []
while current < end:
    # sunday
    if current.isoweekday() == 7:
        pair.append(current.strftime('%Y-%m-%d'))

    if current.isoweekday() == 6:
        pair.append(current.strftime('%Y-%m-%d'))
        if len(pair) == 2:
            for country in countries:
                for genreid, genrename in genres:
                    print(pair, country, genrename)
                    if genrename == 'all':
                        sql = query_genreless.format(
                            start=pair[0], end=pair[1],
                            country=country)
                    else:
                        sql = query_genre.format(
                            start=pair[0], end=pair[1],
                            country=country, genreid=genreid)

                    print('Connecting to db')
                    with psycopg2.connect(**db_config) as conn:
                        print('Connected to db')
                        with conn.cursor() as cur:
                            print('Running query: {sql}'.format(sql=sql))
                            cur.execute(sql)
                            rows = cur.fetchall()
                            data = []
                            print('Formatting data to json')
                            for row in rows:
                                data.append(dict(zip(columns, row)))
                            bucketname = 'dev-hackathon'
                            keyname = '/hotness/charts/{start}-{end}-{country}-{genre}.json'.format(
                                start=''.join(pair[0].split('-')),
                                end=''.join(pair[1].split('-')),
                                country=country,
                                genre=genrename)
                            print('Saving to s3://{bucket}{key}'.format(bucket=bucketname, key=keyname))
                            bucket = Bucket(s3conn, bucketname)
                            key = Key(bucket, keyname)
                            key.set_contents_from_string(json.dumps(data))

        
        # reset
        pair = []

    current += timedelta(days=1)