#!/usr/bin/env python

#import sys

import mysql.connector
import pandas as pd
import time as time
import numpy as np
#import os.path
from os import path
import sys; from StringIO import StringIO
import requests
import base64





def safe_str(obj):
    # return the byte string representation of obj
    try:
        return str(obj)
    except UnicodeEncodeError:
        # obj is unicode
        return obj.encode('utf-8')

def get_session_key():
    # query = {'lat':'45', 'lon':'180'} - params=query
    headers = {'Content-Type': 'application/json'}
    insight_url = "https://insight.queritel.com/index.php/admin/remotecontrol"
    get_session_data = """{
        "method": "get_session_key",
        "params": {
            "username": "Queritel",
            "password": "sMCzVSeWpvCZ"
        },
        "id": 1
    }"""
    response = requests.post(insight_url, data=get_session_data, headers=headers)
    jsonRes = response.json()
    session_key = jsonRes["result"]
    return session_key

def get_insights_response_csv(survey_id,session_key):
    # query = {'lat':'45', 'lon':'180'} - params=query
    headers = {'Content-Type': 'application/json'}
    insight_url = "https://insight.queritel.com/index.php/admin/remotecontrol"
    get_response_data = """{
        "method": "export_responses",
        "params": {
            "sSessionKey": "%s",
            "iSurveyID": "%s",
            "sDocumentType": "csv-allanswer",
            "sLanguageCode": "en",
            "sHeadingType": "full",
            "sResponseType": "full",
            "iFromResponseID": "null",
            "iToResponseID": "null"
        },
        "id": 1
    }""" % (session_key, survey_id)
    response = requests.post(insight_url, data=get_response_data, headers=headers)
    jsonRes = response.json()
    base64_data = jsonRes["result"]

    base64_bytes = base64_data.encode('utf-8')
    message_bytes = base64.b64decode(base64_bytes)
    csv_response_ = message_bytes.decode('utf-8')
    csv_response = safe_str(csv_response_)

    return StringIO(csv_response)

#logic here
def is_table_exist(survey_id):
    return False

def rewrite_dashboard_table(survey_id,data_frame):
    #this is used to create the response

    table_response = "ins_response_{}".format(survey_id) #[variable]
    table_map = "ins_map_{}".format(survey_id) #column_code | column_name

    mydb = mysql.connector.connect(
        host="queritel.com",
        user="initia19_ansplex",
        password="QueritelMgmt2019!",
        database="initia19_ansplex"
    )

    mycursor = mydb.cursor()


    sql_drop_response = "DROP TABLE IF EXISTS `%s`;" % (table_response)
    sql_create_response = """
    CREATE TABLE %s (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""" % (table_response)



    sql_drop_map = "DROP TABLE IF EXISTS `%s`;" % (table_map)
    sql_create_map = """
    CREATE TABLE %s (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `column_code` VARCHAR(20) DEFAULT NULL,
    `column_name` varchar(2000) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""" % (table_map)


    mycursor.execute(sql_drop_response)
    mycursor.execute(sql_create_response)
    mydb.commit()

    mycursor.execute(sql_drop_map)
    mycursor.execute(sql_create_map)
    mydb.commit()



    # print(sql_create_response)
    # print(sql_create_map)

    row_total = len(data_frame.index)

    col_index = 0
    row_index = 0

    for col_name in data_frame.columns:
        col_index += 1
        col_code = "column_{}".format(col_index)
        sql_insert = "insert into %s (column_code, column_name) VALUES ('%s', '%s')" % (table_map,col_code, col_name)
        add_response_column = "ALTER TABLE %s ADD `%s` longtext DEFAULT NULL" % (table_response,col_code)

        mycursor.execute(sql_insert)
        mycursor.execute(add_response_column)
        mydb.commit()

        print("Commited Map : %s" % (col_code))
        # print("%s" % (sql_insert))
        # print("%s" % (add_response_column))

    for row in data_frame.itertuples(name=None):
        row_index += 1
        col_index = 0
        column_string = ""
        value_string = ""
        for col_name in data_frame.columns:
            col_index += 1
            col_code = "column_{}".format(col_index)

            # if col_index == 1:
            #     column_string += "{}".format(col_code)
            #     value_string += "'{}'".format(row[col_index])
            # else:
            #     column_string += ",{}".format(col_code)
            #     value_string += ",'{}'".format(row[col_index])

            if col_index == 1:
                column_string += "{}".format(col_code)


                if row[col_index] is np.nan :
                    value_string += "NULL"
                else:
                    value_string += "'{}'".format(row[col_index])

            else:
                column_string += ",{}".format(col_code)


                if row[col_index] is np.nan :
                    value_string += ",NULL"
                else:
                    value_string += ",'{}'".format(row[col_index])

        sql_insert_res = "insert into {} ({}) VALUES ({})".format(table_response,column_string, value_string)

        mycursor.execute(sql_insert_res)
        mydb.commit()

        print("{} of {} rows inserted".format(row_index,row_total) )




# CODE GOES HERE
# ---------------
argv_list = sys.argv[1:]
survey_id = argv_list[0] if argv_list else ""

# SURVEY_ID = "691672"
session_key = get_session_key()
# print(session_key)
RESPONSE_CSV = get_insights_response_csv(survey_id,session_key)
df = pd.read_csv(RESPONSE_CSV, sep=";")

rewrite_dashboard_table(survey_id,df)

#for row in df.itertuples(name=None):
#    col_count = len(row)
#    for i in range(1, col_count):
#        df_row_index = row[0]
#        col_name = "col_{}".format(i)
#        csv_col_map = row[i]
#        print("{} - {} - {}".format(df_row_index,col_name,csv_col_map))