import imaplib
import email
import mysql.connector
import subprocess
import time
import datetime
import subprocess
import os

# اسم الملف الذي تريد كتابة المخرجات فيه
output_file = "output.txt"
# معلومات الاتصال بخادم البريد الإلكتروني
imap_server = 'vsrv681.armadaservers.com'
username = 'sultan@prod.nhcops.com'
password = 'Sultan135@'
file_name = 'SO_STOCKEMAIL25.csv'

# معلومات الاتصال بقاعدة البيانات
db_host = 'vsrv681.armadaservers.com'
db_user = 'mint303_change_stock'
db_password = 'Plmokn123@'
db_name = 'mint303_change_stock'
conn = True
current_directory = os.path.dirname(os.path.realpath(__file__))
python_path = "/usr/local/bin/python3"  # Use the full path to python3
script_pathGet_Emaile_95 = os.path.join(current_directory, "/home/mint303/public_html/sultan/python/Get_Emaile_96.py")
script_pathGet_Emaile_96 = os.path.join(current_directory, "/home/mint303/public_html/sultan/python/Get_Emaile_96.py")

# حلقة التنفيذ المستمرة
while True:
    try:
        if conn:
            print("I will try counected")
            mail = imaplib.IMAP4(imap_server)
            # تسجيل الدخول إلى حساب البريد الإلكتروني
            mail.login(username, password)
            # اختيار صندوق الوارد
            conn = False

            # إنشاء اتصال بقاعدة البيانات
            db_connection = mysql.connector.connect(host=db_host, user=db_user, password=db_password, database=db_name)
            db_cursor = db_connection.cursor()
            mail.select('INBOX')

        # تاريخ اليوم
        today = datetime.date.today()
        # today = datetime.datetime(2024, 4, 24)

        # تاريخ أمس
        yesterday = today - datetime.timedelta(days=1)
        
        # تحويل تاريخ أمس إلى تنسيق مقبول للاستخدام في الاستعلام
        start_date_str = yesterday.strftime('%d-%b-%Y')
        print(start_date_str)
        current_time = datetime.datetime.now().time()
        print("Current time:", current_time)
   
        # استعلام البحث في رسائل البريد الوارد بعد تاريخ معين
        status, message_ids = mail.search(None, '(SINCE "{}") SUBJECT "Import Assets"'.format(start_date_str))

        # تحويل نتائج البحث إلى قائمة من رموز تعريف الرسائل
        message_ids = message_ids[0].split()

        # طباعة معرّف كل رسالة واسم المرسل والتحقق من وجوده في قاعدة البيانات
        for message_id in message_ids:
            status, msg_data = mail.fetch(message_id, '(BODY.PEEK[HEADER.FIELDS (FROM)])')
            raw_email = msg_data[0][1]
            msg = email.message_from_bytes(raw_email)
            sender = msg['From']

            # التحقق من وجود Message ID في جدول القاعدة
            db_cursor.execute("SELECT COUNT(*) FROM messages WHERE MessageID = %s", (message_id,))
            result = db_cursor.fetchone()[0]
            if sender == 'Sultan Aldawsari <Saldawsari@nhcholding.com>':
                if result > 0:
                    print(message_id, "Recorded")
                    status, message_data = mail.fetch(message_id, '(RFC822)')
                    email_message = email.message_from_bytes(message_data[0][1])
                    attachments = []
                    for part in email_message.walk():
                        if part.get_content_maintype() == 'multipart':
                            continue
                        if part.get('Content-Disposition') is None:
                            continue
                        filename = part.get_filename()
                        if not filename:
                            continue
                    attachments.append(part)
                    for attachment in attachments:
                        if attachment.get_filename():
                            print("File Name:",attachment.get_filename())
                            # if attachment.get_filename()== 'SO_STOCKEMAIL25.csv':
                            #     print("File Name:",attachment.get_filename())
                            
                               
                else:
                    print("Not registered")
                    print("Message ID:", message_id)
                    print("Sender:", sender)
                    status, message_data = mail.fetch(message_id, '(RFC822)')
                    email_message = email.message_from_bytes(message_data[0][1])
                    attachments = []
                    for part in email_message.walk():
                        if part.get_content_maintype() == 'multipart':
                            continue
                        if part.get('Content-Disposition') is None:
                            continue
                        filename = part.get_filename()
                        if not filename:
                            continue
                    attachments.append(part)
                    for attachment in attachments:
                        if attachment.get_filename():
                            print("File Name:",attachment.get_filename())
                            if attachment.get_filename()== 'SO_STOCKEMAIL25.csv':
                                print("File Name:",attachment.get_filename())
                    
                                # إضافة Message ID إلى جدول القاعدة
                                insert_query = "INSERT INTO messages (MessageID , Status , Status1,FileName,sender) VALUES (%s , %s, %s,%s,%s)"
                                db_cursor.execute(insert_query, (message_id, 'new', 'new',attachment.get_filename(),sender))
                                db_connection.commit()
                                print("I Will send this message id to Get_Emaile_96.py:", message_id)
                                subprocess.Popen([python_path, script_pathGet_Emaile_96, message_id])
                                print("I Will send this message id to Get_Emaile_95.py:", message_id)
                                #subprocess.Popen([python_path, script_pathGet_Emaile_95, message_id])
                            else:
                                insert_query = "INSERT INTO messages (MessageID , Status , Status1,FileName,sender) VALUES (%s , %s, %s,%s,%s)"
                                db_cursor.execute(insert_query, (message_id, 'new', 'new',attachment.get_filename(),sender))
                                db_connection.commit()

            elif sender == 'Najeeb Auto <inquiry@najeeb-auto.com>':
                if result > 0:
                    print(message_id, "Recorded")
                    status, message_data = mail.fetch(message_id, '(RFC822)')
                    email_message = email.message_from_bytes(message_data[0][1])
                    attachments = []
                    for part in email_message.walk():
                        if part.get_content_maintype() == 'multipart':
                            continue
                        if part.get('Content-Disposition') is None:
                            continue
                        filename = part.get_filename()
                        if not filename:
                            continue
                    attachments.append(part)
                    for attachment in attachments:
                        if attachment.get_filename():
                            print("File Name:",attachment.get_filename())
                            # if attachment.get_filename()== 'SO_STOCKEMAIL25.csv':
                            #     print("File Name:",attachment.get_filename())
                               
                else:
                    print("Not registered")
                    print("Message ID:", message_id)
                    print("Sender:", sender)
                    status, message_data = mail.fetch(message_id, '(RFC822)')
                    email_message = email.message_from_bytes(message_data[0][1])
                    attachments = []
                    for part in email_message.walk():
                        if part.get_content_maintype() == 'multipart':
                            continue
                        if part.get('Content-Disposition') is None:
                            continue
                        filename = part.get_filename()
                        if not filename:
                            continue
                    attachments.append(part)
                    for attachment in attachments:
                        if attachment.get_filename():
                            print("File Name:",attachment.get_filename())
                            if attachment.get_filename()== 'SO_STOCKEMAIL25.csv':
                                # print("File Name:",attachment.get_filename())
                    
                                # إضافة Message ID إلى جدول القاعدة
                                insert_query = "INSERT INTO messages (MessageID , Status , Status1,FileName,sender) VALUES (%s , %s, %s,%s,%s)"
                                db_cursor.execute(insert_query, (message_id, 'new', 'new',attachment.get_filename(),sender))
                                db_connection.commit()
                                print("I Will send this message id to Get_Emaile_96.py:", message_id)
                                subprocess.Popen([python_path, script_pathGet_Emaile_96, message_id])
                                print("I Will send this message id to Get_Emaile_95.py:", message_id)
                                #subprocess.Popen([python_path, script_pathGet_Emaile_95, message_id])
                            else:
                                insert_query = "INSERT INTO messages (MessageID , Status , Status1,FileName,sender) VALUES (%s , %s, %s,%s,%s)"
                                db_cursor.execute(insert_query, (message_id, 'new', 'new',attachment.get_filename(),sender))
                                db_connection.commit()  
        # db_cursor.close()
        # db_connection.close()


        break
        time.sleep(1)
    except mysql.connector.Error as e:
        print("Database error:", e)
        conn = True
        time.sleep(1)
        
    except imaplib.IMAP4.error as e:
        print("Mail server error:", e)
        print("Retrying in 5 seconds...")
        conn = True
        time.sleep(1)
    except Exception as ex:
        print("An unexpected error occurred:", str(ex))
        conn = True
        time.sleep(1)

mail.logout()
