import requests
import zipfile
import json
import urllib2
import sys
import os
import pandas as pd
import shutil
import psycopg2
import boto3
import time
'''
The character formatting below is for Python 2.7.
Get rid of them for Python 3 and use encoding ='utf-8' when you open file.abs
The rest should work for both versions.
'''
reload(sys)
sys.setdefaultencoding('utf8')

# List of Argument taken from the command line

project_names = sys.argv[1]

lst = []
for i in project_names.split(','):
    lst.append(i)

target_date = sys.argv[2]
bucket_name = sys.argv[3]
dbname = sys.argv[4]
host = sys.argv[5]
port = sys.argv[6]
user = sys.argv[7]
password = sys.argv[8]
aws_access_key_id = sys.argv[9]  
aws_secret_access_key = sys.argv[10]
target_schema = sys.argv[11]
base_url = sys.argv[12]
secret_token = sys.argv[13]


# (1) Getting a list of ID

def get_project_id(names):
    '''The function takes a list of project names and return a list of IDs
    '''
    report_dic = {}
    url = base_url + '/surveys/'
    header = {'X-API-TOKEN': secret_token}

    # (1) generating the request object
    req = urllib2.Request(url,None,header)

    # (2) Make request
    response = urllib2.urlopen(req)
    data = json.load(response)
    # (3) Find Id for each project
    for name in names:

        # This is necessary because project names sometimes contain 2 spaces instead of 1
        target_name = name.replace(" ", "").lower()

        # It is better to create a table name list separately
        table_key = name.replace(" ", "_").replace("-", "_").lower()

        # print target_name
        for i in data['result']['elements']:
            if i['name'].replace(" ", "").lower() == target_name:
                report_dic[table_key] = i['id']

    return report_dic

# (2) Get Metadata

def get_survey_metadata(report_dic, target_dir):
    '''Takes survey ID and create json file in a specified directory'''

    for k, v in report_dic.items():

        url = base_url + '/surveys/' + v
        header = {'X-API-TOKEN': secret_token}

        req = urllib2.Request(url,None,header)
        response = urllib2.urlopen(req)

        data = json.load(response)
        pretty = json.dumps(data, sort_keys=False, indent=4)
        file = open('./' + target_dir + '/' + k + '_meta.json', 'w')
        file.write(pretty)
        print('Metadata File for %s Generated!' % (k))

# (3) Exporting reports

fileFormat = "csv"
baseUrl = base_url + "/responseexports/"
headers = {"content-type": "application/json","x-api-token": secret_token}

def bulk_exports(report_dic):
    '''This function takes a list of ids and create data export'''

    if os.path.exists('./Exported'):
        shutil.rmtree('./Exported')

    for key, val in report_dic.items():
        # Step 1: Creating Data Export
        print(key, val)

        downloadRequestUrl = baseUrl
        downloadRequestPayload = '{"format":"' + fileFormat + '","surveyId":"' + val + '"}'
        downloadRequestResponse = requests.request("POST", downloadRequestUrl, \
        data=downloadRequestPayload, headers=headers)
        progressId = downloadRequestResponse.json()["result"]["id"]

        # Step 3: Downloading file
        requestDownloadUrl = baseUrl + progressId + '/file'
        requestDownload = requests.request("GET", requestDownloadUrl, headers=headers, stream=True)
        for i in range(0, 200):
            print(str(requestDownload))
            if str(requestDownload) == '<Response [200]>':
                # Step 4: Unziping file
                with open("RequestFile.zip", "wb") as f:
                    for chunk in requestDownload.iter_content(chunk_size=1024):
                        f.write(chunk)
                    f.close()
                zipfile.ZipFile("RequestFile.zip").extractall('Exported')
                print('Completed Export for {}'.format(key))
                os.remove("./RequestFile.zip")
                break
            else:
                time.sleep(10)
                requestDownload = requests.request("GET", requestDownloadUrl, headers=headers, stream=True)
   
    for filename in os.listdir("Exported"):
        print(filename)
        os.rename('./Exported/'+filename, './Exported/'+filename.replace(" ", "_").replace("-", "_").lower())
        # os.rename('./'+filename, './'+filename.replace(" ", "_").replace("-", "_").lower())
   
# (4) Create the folder before moving to S3

def create_dir(target_date):
    direc = "./" + target_date

    if not os.path.exists(direc):
        os.makedirs(direc)
        print('New directory %s has been created' % (target_date))
    else:
        shutil.rmtree(direc)
        os.makedirs(direc)
        print('New directory %s has been created' % (target_date))

# (5) Reformat csv file and put into the right local folder created in (4)

def format_colnames(output_dir):
    '''This function takes the file and rename its columns with the right format,
    and generate csv file with the right column names'''

    for filename in os.listdir("./Exported"):
        # (1) Read csv file
        df = pd.read_csv("./Exported/" + filename, skiprows=[0,1], low_memory=False)

        columns = df.columns
        new_cols = []

        # (2) Reformat the column names
        for name in columns:
            new_name = name.replace('{', '').replace('}', '').split(':')[1].replace('\'', '').\
            replace('-', '_').replace(' ', '')
            new_cols.append(new_name)
       
        # print new_cols
        df.columns = new_cols

        # (3) Create CSV file into the output directory
        df.to_csv('./' + output_dir + '/' + filename, doublequote=True, sep='|', index=False)
        print('Reformateed and moved %s' % (filename))

# (6) Uploading to S3

def upload_files(local_path, s3_path, bucket_name):
    '''Search all the files from specified directory and push to S3'''
    s3 = boto3.resource('s3')

    for (root, dirs, files) in os.walk(local_path):
        for filename in files:
            print("File: {}".format(filename))
            s3_filename = s3_path + filename
            print('Uploading to %s...' % (s3_filename))
            s3.meta.client.upload_file(local_path + filename, bucket_name, s3_filename)
            print('Done')

# (7) Truncate & Load to Redshift

def truncate_load_tables(report_dict):
    con = psycopg2.connect(dbname=dbname, host=host, port=port, user=user, password=password)
    print("Connection to Redshift Successful!")
    cur = con.cursor()
    for k, v in report_dict.items():
        target_table = target_schema + '.' + k
        file_name = 's3://' + bucket_name + '/Qualtrics/data_export/' + k + '.csv'

        sql = """
        Truncate %s;Commit;
        copy %s from '%s'  dateformat 'auto' credentials
        'aws_access_key_id=%s;aws_secret_access_key=%s' CSV QUOTE '"' DELIMITER '|'
        ACCEPTINVCHARS EMPTYASNULL COMPUPDATE OFF IGNOREHEADER 1;
        Commit;
        """ % (target_table, target_table, file_name, aws_access_key_id, aws_secret_access_key)

        print(sql)
        cur.execute(sql)
        print("Copy Command executed successfully for %s" % (target_table))
    con.close()

# Execution #

# (1) get the dictionary with report name and report id to export
reports = get_project_id(lst)

# (2) Create a directory for S3 transfer
create_dir(target_date)

# (3) Do Bulk Export
bulk_exports(reports)

# (4) Get metadata and prep for S3 transfer
get_survey_metadata(reports, target_date)

# (5) Transfer
format_colnames(target_date)

# (6) Move to S3
upload_files('./' + target_date + '/', 'Qualtrics/data_export/', bucket_name)

# (7) Load Table
truncate_load_tables(reports)