#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
@author: pmctaggart
"""

import pymysql
from pymysql import OperationalError
from pymysql import Error

import OpenSSL.crypto as crypto
import requests
import base64
import time

###############################################################################
##############################  API requests  #################################
###############################################################################

def patch_response_api(api_url, payload):
    import requests
#    api_url = 'https://api.staging.mymoneyjar.com/internal-api/1.0/aisp'  
    app_id = 'api'
    secret = 'polls'
    requests.patch(api_url, data=payload)#, auth=(app_id, secret))
    return "Done"

###############################################################################
##########################  Saltedge Functions  ###############################
###############################################################################

def get_app(clientID, serviceSECRET):
        return {
            'Accept': 'application/json',
            'Content-type': 'application/json',
            'Expires-at': str(time.time() + 60),
            'App-id': clientID,
            'Secret': serviceSECRET
        }
      #################################################################
      
## Step one of customer setup, send user email and get back customer_id.

def fetch_leads(uuid): 
    payload  = '{"data":{"email":"' + str(uuid) + '@MMJ.com"}}'
    post_url = 'https://www.saltedge.com/api/partners/v1/leads'
    data = post_response(post_url, payload)
    return data

      #################################################################
      
## Step one of customer setup, send user email and get back customer_id.

def fetch_connections(customer_id, connection_id):
    data_out    = get_response('https://www.saltedge.com/api/partners/v1/connections?customer_id=' + str(customer_id))
    return data_out

      #################################################################
      
## Step one of customer setup, send user email and get back customer_id.

def fetch_reconnect(customer_id, connection_id):
    payload  = '{"data": {"customer_id": "222222222222222222", \
                "connection_id": "111111111111111111", \
                "consent": { \
                  "from_date": "2020-01-01", \
                  "period_days": 90, \
                  "scopes": [ \
                    "account_details", \
                    "transactions_details" \
                  ] \
                }, \
                "attempt": { \
                  "from_date": "2020-02-01", \
                  "fetch_scopes": [ \
                    "accounts", \
                    "transactions" \
                  ], \
                  "custom_fields": { \
                    "test": true \
                  }}}}'
                
    data_out    = get_response('https://www.saltedge.com/api/partners/v1/connections?customer_id=' + str(customer_id))
    return data_out
                                      
                                      
      #################################################################
      
## Step one of customer setup, send user email and get back customer_id.

def fetch_refresh(customer_id):
    payload = '{"data\": {"customer_id": "222222222222222222", \
                "connection_id": "111111111111111111", \
                "attempt": { \
                  "from_date": "2020-02-01", \
                  "fetch_scopes": [ \
                    "accounts", \
                    "transactions" \
                  ], \
                  "custom_fields": { \
                    "test": true \
                  }}}}'
    
    data_out    = get_response('https://www.saltedge.com/api/partners/v1/connections?customer_id=' + str(customer_id))
    return data_out


      #################################################################
      
## Step one of customer setup, send user email and get back customer_id.

def fetch_consent(customer_id):
    data_out    = get_response('https://www.saltedge.com/api/partners/v1/partner_consents?customer_id=' + str(customer_id))
    return data_out

      #################################################################
      
## Send data for individual user and get their request URL

def create_lead_session(customer_id, from_date, period_days):                  #### Check dates here
    payload  = '{"data":{"customer_id":"' + str(customer_id) + '",\
                "consent":{"from_date":"' + str(from_date) + '",\
                           "period_days":' + str(period_days) + ',\
                "scopes":["account_details","transactions_details"]},\
                "attempt":{"from_date":"2020-02-01",\
                "fetch_scopes":["accounts","transactions"]}}}'

    post_url = 'https://www.saltedge.com/api/partners/v1/lead_sessions/create'
    data = post_response(post_url, payload)
    return data   

      #################################################################
     
    ## ADD SALTEDGE DELETE USER

      #################################################################
     
    ## ADD SALTEDGE refresh USER
    
      #################################################################
     
    ## ADD SALTEDGE reconnect USER
    
      #################################################################

    ## ADD SALTEDGE revoke USER

###############################################################################
#############################  MYSQL Queries  #################################
###############################################################################


def get_status_update(uuid, customer_id, status, connections):
    status_query = "INSERT INTO aispDB.user_status values (\
                            '" + uuid + "',\
                            '" + customer_id + "',\
                            '" + status + "',\
                            '" + str(connections) + "') \
                    ON DUPLICATE KEY UPDATE \
                            uuid =        uuid,\
                            customer_id = customer_id,\
                            status = '" + status + "',\
                            connections =  connections;"
    return status_query

      #################################################################
      
def get_status_full_update(uuid, customer_id, status, connections):
    status_query = "INSERT INTO aispDB.user_status values (\
                            '" + uuid + "',\
                            '" + customer_id + "',\
                            '" + status + "', \
                            '" + str(connections) + "') \
                    ON DUPLICATE KEY UPDATE \
                            uuid =        '" + uuid + "',\
                            customer_id = '" + customer_id + "',\
                            status = '" + status + "',\
                            connections = '" + str(connections) + "';"
    return status_query

      #################################################################
     
def get_callback_update(type_1, payload, customer_id, connection_id, error_class, created_at):
    callback_query = "INSERT INTO aispDB.callbacks values (\
                            DEFAULT, \
                            '" + type_1 + "',\
                            '" + payload + "',\
                            '" + customer_id + "',\
                            '" + connection_id + "',\
                            '" + error_class + "',\
                            '" + created_at + "');"
    return callback_query


      #################################################################
      
def get_status_pull(uuid, columns):
    callback_query = "Select " + str(columns) + " from aispDB.user_status where uuid = '" + str(uuid) + "';"
    return callback_query


###############################################################################
############################  Production DB  ##################################
###############################################################################

#ssh_host = '52.30.45.126'                                                      ## ssh_hostname
#ssh_user = 'www-data'                                                          ## ssh_username
#ssh_password = ''                                                              ## 
#
#sql_hostname= 'production-tracker.cwfqsdukfwmb.eu-west-1.rds.amazonaws.com'    ## sql_hostname
#sql_port = 3306                                                                ## 
#sql_username = 'admin'                                                         ## sql_username
#sql_password = '5vj689W97pff8nn'                                               ## sql_password Xg9sV4bE4AoEJKUf1nd2
#
#sql_main_database = 'aispDB'                                                   ## db_name
#ssh_port = 22                                                                  ##

###############################################################################
##############################  Staging DB  ###################################
###############################################################################

ssh_host = '54.220.72.82'                                                      ## ssh_hostname
ssh_user = 'www-data'                                                          ## ssh_username
ssh_password = ''                                                              ## 

sql_hostname= 'staging-tracker.cwfqsdukfwmb.eu-west-1.rds.amazonaws.com'       ## sql_hostname
sql_port = 3306                                                                ## 
sql_username = 'aisp'                                                         ## sql_username
sql_password = 'gE6yY3jV3yX6eW9x'                                              ## sql_password Xg9sV4bE4AoEJKUf1nd2

sql_main_database = 'aispDB'                                                   ## db_name
ssh_port = 22                                                                  ##   

###############################################################################
##############################  Journey DB  ###################################
###############################################################################

#ssh_host = '52.30.45.126'                                                      ## ssh_hostname
#ssh_user = 'www-data'                                                          ## ssh_username
#ssh_password = ''                                                              ## 
#
#sql_hostname= '127.0.0.1'                                                      ## sql_hostname
#sql_port = 3306                                                                ## 
#sql_username = 'pmctaggart'                                                    ## sql_username
#sql_password = 'eSF3jj4g9jZ827Q'                                               ## sql_password Xg9sV4bE4AoEJKUf1nd2
#
#sql_main_database = 'aispDB'                                                   ## db_name
#ssh_port = 22                                                                  ##

###############################################################################
###########################  Database Queries  ################################
###############################################################################

import os
#import paramiko
#from paramiko import SSHClient
#from sshtunnel import SSHTunnelForwarder
local_dir = os.path.dirname(os.path.realpath(__file__)) + '/'
#local_dir = '/Users/pmctaggart/Work/AISP/Python2/Working/Test_Version_v1/'
#mypkey = paramiko.RSAKey.from_private_key_file(local_dir + 'mmj-www-data.pem')

 
## Query data base with single execution  

 
def aisp_query(query):
    i = 1
    while i <= 1:
        print('Running query, attempt: ' + str(i))
        try:
            conn = pymysql.connect(host=sql_hostname, user=sql_username,
                   passwd=sql_password, db=sql_main_database,
                   port=sql_port)           
            cur = conn.cursor()
            cur.execute(query)
            conn.commit()
            cur.close() 
            conn.close() 
            print(' - Complete')
            return None
        except:
#            time.sleep(1)
            i += 1        
            try:
                cur.close()
            except: 
                pass     
            try:
                conn.close()
            except: 
                pass       
    print('AA - Failed after ' + str(i - 1) + ' attempts')

      #################################################################

def aisp_query_one(query):
    i = 1
    while i <= 1:
#        print('Running query, attempt: ' + str(i))
        try:
            conn = pymysql.connect(host=sql_hostname, user=sql_username,
                    passwd=sql_password, db=sql_main_database,
                    port=sql_port)
            cur = conn.cursor()
            cur.execute(query)
            result = cur.fetchone()
            output = result[0]
#            conn.commit()
            cur.close()
            conn.close()
            print('Complete after ' + str(i) + ' attempts')
            return output
        except:
#            time.sleep(1)
            i += 1        
            try:
                cur.close()
            except: 
                pass     
            try:
                conn.close()
            except: 
                pass      
    print('BB - Failed after ' + str(i - 1) + ' attempts')

      #################################################################
      
def aisp_query_safe(query): # Simple version of below, seems to work better
    aisp_query("SET SQL_SAFE_UPDATES = 0;")
    aisp_query(query)
    aisp_query("SET SQL_SAFE_UPDATES = 1;")
    
      #################################################################

def aisp_query_all(query):
    i = 1
    while i <= 1:
        print('Running query, attempt: ' + str(i))
        try:  
            conn = pymysql.connect(host=sql_hostname, user=sql_username,
                    passwd=sql_password, db=sql_main_database,
                    port=sql_port)
            cur = conn.cursor()
            cur.execute(query)
            result=cur.fetchall()
            output = result#[0]
#            conn.commit()
            cur.close()
            conn.close()
            print(' - Complete')
            return output
        except:
#            time.sleep(1)
            i += 1        
            try:
                cur.close()
            except: 
                pass     
            try:
                conn.close()
            except: 
                pass       
    print('CC - Failed after ' + str(i - 1) + ' attempts')


     
###############################################################################
################## Saltedge GET & POST - with Signiture #######################
###############################################################################

clientID      = 'GzrhYdPiBqNNA86EB2raWw7UmgcpUQc7By9xOCmCsyQ'
serviceSECRET = 'cRTPXyIOOkC2iwVb1LCarrVmRiEFkhJ5QJCq4J0EzFE' 
pem_path      = local_dir + 'saltedge_private.pem'
digest        = 'sha256'

      #################################################################
      
def sign_message(method, expire, some_url, payload=""):
    with open(pem_path, "rb") as private_key:
        keydata = private_key.read()
    private_key_1 = crypto.load_privatekey(crypto.FILETYPE_PEM, keydata)
    message = "{expire}|{method}|{some_url}|{payload}".format(**locals())
    signiture = base64.b64encode(crypto.sign(private_key_1, message, digest))
    return signiture

      #################################################################
      
def get_response(get_url):
    app_id = clientID
    secret = serviceSECRET
    expire = str(time.time() + 60)
    headers = {
        "Accept": "application/json",
        "Content-type": "application/json",
        "Expires-at": expire,
        "App-id": app_id,
        "Secret": secret
    }
    headers["Signature"] = sign_message("GET", expire, get_url)

    response = requests.get(get_url, headers=headers)
    data = response.json()
    return data

      #################################################################

def post_response(post_url, payload):
    app_id = clientID
    secret = serviceSECRET
    expire = str(time.time() + 60)
    headers = {
        "Accept": "application/json",
        "Content-type": "application/json",
        "Expires-at": expire,
        "App-id": app_id,
        "Secret": secret
    }
    headers["Signature"] = sign_message("POST", expire, post_url, payload)

    response = requests.post(post_url, data=payload, headers=headers)
    data = response.json()
    return data

###############################################################################
###############################################################################
###############################################################################
         





