# Python 3.8.0
import smtplib
import time
import imaplib
import email
import traceback
import datetime
import re
import mysql.connector
from mysql.connector import Error
# -------------------------------------------------
#
# Utility to read email from Gmail Using Python
#
# ------------------------------------------------


def XoopsExecuteQuery(isql):
    connection = mysql.connector.connect(
        host='hhh-v57.cmab1ctkglka.ap-northeast-2.rds.amazonaws.com',
        database='xoops',
        user='hhh7796hhh',
        password='lYmWsu^ujcA1'
    )
    cursor = connection.cursor(dictionary=True)
    cursor.execute(isql)
    if cursor.rowcount == -1:
        return cursor.fetchall()
    else:
        connection.commit()
        return cursor.rowcount

def EdmExecuteQuery(isql):
    connection = mysql.connector.connect(
        host='edm.hhh.com.tw',
        database='hhh_edm',
        user='root',
        password='bimbothin'
    )
    cursor = connection.cursor(dictionary=True)
    cursor.execute(isql)
    if cursor.rowcount == -1:
        return cursor.fetchall()
    else:
        connection.commit()
        return cursor.rowcount

ORG_EMAIL = "noreply@hhh.com.tw"
FROM_EMAIL = "noreply@hhh.com.tw"
FROM_PWD = "Hhhn0123"
SMTP_SERVER = "outlook.office365.com"
SMTP_PORT = 993

mail = imaplib.IMAP4_SSL('outlook.office365.com')
mail.login(FROM_EMAIL, FROM_PWD)
""" for folder in mail.list():
    print(folder) """

mail.select('Inbox')

result, data = mail.uid('search', None, "ALL")  # (ALL/UNSEEN)
i = len(data[0].split())
print(i)

for x in range(i):
    latest_email_uid = data[0].split()[x]
    result, email_data = mail.uid('fetch', latest_email_uid, '(RFC822)')
    # result, email_data = conn.store(num,'-FLAGS','\\Seen')
    # this might work to set flag to seen, if it doesn't already

    raw_email = email_data[0][1]
    raw_email_string = raw_email.decode('ISO-8859-1')
    email_message = email.message_from_string(raw_email_string)

    # Header Details
    date_tuple = email.utils.parsedate_tz(email_message['Date'])
    if date_tuple:
        local_date = datetime.datetime.fromtimestamp(
            email.utils.mktime_tz(date_tuple))
        local_message_date = "%s" % (
            str(local_date.strftime("%a, %d %b %Y %H:%M:%S")))
    email_from = str(email.header.make_header(
        email.header.decode_header(email_message['From'])))
    email_to = str(email.header.make_header(
        email.header.decode_header(email_message['To'])))
    try:
        subject = str(email.header.make_header(
            email.header.decode_header(email_message['Subject'])))
    except:
        subject = ""


    lst = re.findall(r'To: [\w.+-]+@[\w-]+\.[\w.-]+', raw_email.decode('UTF-8'))
    
    if local_date + datetime.timedelta(days=8) > datetime.datetime.now():
        if email_from.find('complaint') >= 0:
            #print(local_date)
            print(lst)
            if len(lst) > 0:
                XoopsExecuteQuery("update _users set email_status='B' where email='"+ lst[0].replace('To: ','') +"'")
                XoopsExecuteQuery("update _hnewspaper set onoff='0' where email='"+ lst[0].replace('To: ','') +"'")
                EdmExecuteQuery("delete from subscriber where email='"+ lst[0].replace('To: ','') +"'")
                EdmExecuteQuery("delete from subscriber_for_all where email='"+ lst[0].replace('To: ','') +"'")

        if email_from.lower() == 'mailer-daemon@amazonses.com':
            print(lst)
            if len(lst) > 0:
                XoopsExecuteQuery("update _users set email_status='B' where email='"+ lst[0].replace('To: ','') +"'")
                XoopsExecuteQuery("update _hnewspaper set onoff='0' where email='"+ lst[0].replace('To: ','') +"'")
                EdmExecuteQuery("delete from subscriber where email='"+ lst[0].replace('To: ','') +"'")
                EdmExecuteQuery("delete from subscriber_for_all where email='"+ lst[0].replace('To: ','') +"'")

    """ if x == i-2:
        print(raw_email.decode('UTF-8')) """
        #print(email_from)
    #print(email_from)   

    # Body details
    """ for part in email_message.walk():
        if part.get_content_type() == "text/plain":
            # if subject == 'complaint':
            body = part.get_payload(decode=True)
            # print(email_from)
            try:
                dbody = body.decode('UTF-8')
            except:
                dbody = body.decode('ISO-8859-1')
            #lst = re.search(r'[\w.+-]+@[\w-]+\.[\w.-]+', raw_email.decode('utf-8'))
            lst = re.findall(r'[\w.+-]+@[\w-]+\.[\w.-]+', dbody)
            print(email_from)
            if len(lst) > 0 and email_from == "complaints@email-abuse.amazonses.com":
                if XoopsExecuteQuery("update _users set email_status='B' where email='"+ lst[0] +"'") > 0:
                    print(lst[0])
                XoopsExecuteQuery("update _hnewspaper set onoff='0' where email='"+ lst[0] +"'")
                EdmExecuteQuery("delete from subscriber where email='"+ lst[0] +"'")
                EdmExecuteQuery("delete from subscriber_for_all where email='"+ lst[0] +"'")
                print(lst[0])
            if x == i-2:
                print(subject)
                print(email_from)

        else:
            continue """