"""DB based alerts

This Lambda function runs queries on our MySQL databases and based on 
a defined set of business logic sends notifications via Slack. This can
be modified to integrate other 3rd party solutions.
"""

import sys
import logging
import pymysql
import boto3
import base64
import pycurl
import json
import datetime

logger = logging.getLogger()
logger.setLevel(logging.INFO)

#kms
boto3.set_stream_logger(name='botocore')
kms = boto3.client('kms')
#token = kms.encrypt(KeyId='alias/test-reportsar-ro', Plaintext='testdata')
#cipherblob = base64.b64encode(token.get('CiphertextBlob'))
#logger.info(cipherblob)

#db settings
db_host = "dd-db.theorchard.com"
name = "vector-alerts"
db_name = "direct_delivery"
port = 3306
cipherblob='AQECAHiLZYTaJnyoHVVDx8RQ8s3QlgXyUI3uRLU7LXD9V830jAAAAGowaAYJKoZIhvcNAQcGoFswWQIBADBUBgkqhkiG9w0BBwEwHgYJYIZIAWUDBAEuMBEEDHmrRFm6ho3BAeuhjwIBEIAnd8yQr3M4hBPmGGO+GhW1wY+2JYf0VghQgz3Z3jfr9N4XdGuMLuJd'
kms_response = kms.decrypt(CiphertextBlob=base64.b64decode(cipherblob))
password = kms_response['Plaintext']

#slack url
slack_cipher = 'AQECAHiLZYTaJnyoHVVDx8RQ8s3QlgXyUI3uRLU7LXD9V830jAAAAK8wgawGCSqGSIb3DQEHBqCBnjCBmwIBADCBlQYJKoZIhvcNAQcBMB4GCWCGSAFlAwQBLjARBAxzHlhNpV0zsE3yNegCARCAaE9Pbjt14Qq6kxIUGLGgopzCnsxK0Ed2aZBp8iqMTgXjmejFggkRjwXKWRfPRvJJOm+B59pJ4ojx2dBzn3RiHiMEKthcCMjjrXUvygip4oKhSJsYDn0EMLd7OhsvTimPiz8I9JErFTz7'
slack_kms_response = kms.decrypt(CiphertextBlob=base64.b64decode(slack_cipher))
slack_url = slack_kms_response['Plaintext']

def handler(event, context):
    """Get a user's username by its id.

    Args:
        event: Lambda's event object
        context: No speific context is provided since this is hooked into a Cloudwatch timer

    Returns:
        send_alert (bool): notification flag
    """
    send_alert = False
    logger.info("Connecting to DB.")
    try:
        conn = pymysql.connect(db_host, user=name, passwd=password, db=db_name, connect_timeout=5)
    except:
        logger.error("ERROR: Unexpected error: Could not connect to MySql instance %s." % db_host)
        sys.exit()
    logger.info("SUCCESS: Connection to %s mysql instance succeeded" % db_host)
    with conn.cursor() as cursor:
        sql_check_open_jobs = "SELECT COUNT(*) FROM encoding_queue_detail eqd WHERE eqd.`dms_master_master_id` = 1 AND eqd.`status` IN ('new','ready_to_encode','encoding','ready_to_deliver','delivering','encoded','queued_for_delivery')"
        cursor.execute(sql_check_open_jobs)
        result_check_open_jobs = cursor.fetchone()
        logger.info("%d iTunes jobs found" % result_check_open_jobs[0])
        if(result_check_open_jobs[0] > 0):
            sql = "SELECT TIMESTAMPDIFF(MINUTE,eqd.`delivery_ended`,NOW()) FROM encoding_queue_detail eqd WHERE eqd.`dms_master_master_id` = 1 AND eqd.`status` = 'delivered' ORDER BY eqd.`delivery_ended` DESC LIMIT 1"
            cursor.execute(sql)
            result = cursor.fetchone()
            if(result[0] > 60):
                send_alert = True
        conn.close()
    if send_alert:
        logger.info("Sending Slack message")
        oa_url = 'http://bit.ly/2deiqvy'

        data = {"text": "No iTunes deliveries in over %d minutes. Please check the queue here: %s" % (result[0], oa_url),
                "channel": "#vector-system-alerts",
                "icon_emoji": ":monkey_face:"}

        curl = pycurl.Curl()
        curl.setopt(curl.HTTPHEADER, [
            'Content-Type: application/json'
        ])
        curl.setopt(curl.URL, slack_url)
        curl.setopt(curl.POSTFIELDS, json.dumps(data))
        curl.setopt(curl.VERBOSE, True)
        curl.perform()
        logger.info('Status: %d' % curl.getinfo(curl.RESPONSE_CODE))
    return send_alert