import pymysql
from datetime import datetime
from urllib import request

import os
import json
import psutil

def visit_link(link):
    try:
        req = request.Request(link)
        response = request.urlopen(req)
        html_response = response.read()
        return True
    except HTTPError as e:
        print("HTTP ERROR: %s" % e)
        return False

# define process I/O niceness
p = psutil.Process()
p.ionice(psutil.IOPRIO_CLASS_IDLE)

# read JSON file which is in the next parent folder
file = '/opt/sisimai/bounces.json'
json_data= open(file).read()
json_obj = json.loads(json_data)


# connect to MySQL
ssl_ca_candidates = [
    os.getenv("MYSQL_SSL_CA", ""),
    "/etc/mysql/certs/rds-global-bundle.pem",
    "/etc/pki/tls/certs/rds-global-bundle.pem",
]
ssl_ca_path = next((p for p in ssl_ca_candidates if p and os.path.isfile(p)), None)
if ssl_ca_path is None:
    raise RuntimeError("No readable MySQL SSL CA bundle found for pymysql connection")

con = pymysql.connect(
    host='amazonaurora.cluster-cemzxojvmybt.us-east-1.rds.amazonaws.com',
    user='admin',
    passwd='xxatN6Lb8Kbwb9MiU1At',
    db='sisimai',
    port=3306,
    ssl={"ca": ssl_ca_path},
)
#openemmcon = pymysql.connect(host = 'amazonaurora.cluster-cemzxojvmybt.us-east-1.rds.amazonaws.com', user = 'admin', passwd = 'xxatN6Lb8Kbwb9MiU1At', db = 'openemm', port =3306)

cursor = con.cursor()
#openemmcursor = openemmcon.cursor()

# parse json data to SQL insert
try:
    for i, item in enumerate(json_obj):
        subject = item.get("subject", None)
        deliverystatus = item.get("deliverystatus", None)
        token = item.get("token", None)
        feedbacktype = item.get("feedbacktype", None)
        softbounce = item.get("softbounce", None)
        rhost = item.get("rhost", None)
        smtpcommand = item.get("smtpcommand", None)
        smtpagent = item.get("smtpagent", None)
        alias = item.get("alias", None)
        lhost = item.get("lhost", None)
        diagnostictype = item.get("diagnostictype", None)
        listid = item.get("listid", None)
        action = item.get("action", None)
        timestamp = datetime.fromtimestamp(item.get("timestamp", None))
        reason = item.get("reason", None)
        replycode = item.get("replycode", None)
        messageid = item.get("messageid", None)
        recipient = item.get("recipient", None)
        destination = item.get("destination", None)
        senderdomain = item.get("senderdomain", None)
        diagnosticcode = item.get("diagnosticcode", None)
        addresser = item.get("addresser", None)
        print(timestamp)
        print(recipient)
        
        cursor.execute("INSERT INTO `sisimai`.`bounces` (`id`, `reason`, `replycode`, `messageid`, `recipient`, `destination`, `senderdomain`, `diagnosticcode`, `addresser`, `subject`, `deliverystatus`, `token`, `feedbacktype`, `softbounce`, `rhost`, `smtpcommand`, `smtpagent`, `alias`, `lhost`, `diagnostictype`, `listid`, `action`, `timestamp`) VALUES (NULL, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);", (reason, replycode, messageid, recipient, destination, senderdomain, diagnosticcode, addresser, subject, deliverystatus, token, feedbacktype, softbounce, rhost, smtpcommand, smtpagent, alias, lhost, diagnostictype, listid, action, timestamp))
       
        # let's check if we need this user within OpenEMM 
     #   openemmcursor.execute("SELECT * FROM customer_1_tbl WHERE email = %s", (recipient))
     #   user = openemmcursor.fetchone()
     #   if user != None:
    #        openemmcursor.execute("SELECT * FROM softbounce_email_tbl WHERE email = %s", (recipient))
    #        record = openemmcursor.fetchone()
        
            # if we have a bounce entry, just bump it up
     #       if record != None:
      #          result = openemmcursor.execute("UPDATE `openemm`.`softbounce_email_tbl` SET bnccnt = %s WHERE email = %s", (record[1] + 1, recipient))
            
                # bounce count greater than 5? Not good, let's disable the user
     #           if record[1] + 1 > 5:
     #               openemmcursor.execute("UPDATE `openemm`.`customer_1_binding_tbl` SET user_status = 6 WHERE customer_id = %s", (user[0]))
     #       else:
                # no entry available? Let's create one
     #           result = openemmcursor.execute("INSERT INTO `openemm`.`softbounce_email_tbl` (bnccnt, email) VALUES(%s, %s)", (1, recipient))

#    openemmcon.commit()
#    openemmcon.close()

    con.commit()
    con.close()
except pymysql.InternalError as e:
    print('Got error {!r}, errno is {}'.format(e, e.args[0]))