import imaplib
import email
import csv
import io
import time
import mysql.connector
import pytz
from datetime import datetime
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders

added_count_row = 0
already_count_row = 0

# تفاصيل البريد الإلكتروني
username = 'sultan@prod.nhcops.com'
password = 'Sultan135@'
mail_server = 'vsrv681.armadaservers.com'
mail_port = 993

from_email = 'helpdesk@nhcholding.com'
to_email = ['Saldawsari@nhcholding.com','adosri@nhcholding.com','Turkiq@nhcholding.com']
server = smtplib.SMTP('smtp.office365.com', 587)
server.starttls()
server.login(from_email, 'Qox30885')

connected = False
while not connected:
    try:
        # إنشاء اتصال بخادم البريد الإلكتروني
        mail = imaplib.IMAP4_SSL(mail_server, mail_port)
        # تسجيل الدخول إلى حساب البريد الإلكتروني
        mail.login(username, password)

        conn = mysql.connector.connect(
            host="vsrv681.armadaservers.com",
            user="mint303_change_stock",
            password="Plmokn123@",
            database="mint303_change_stock"
        )
        mail.select('inbox')
        cursor = conn.cursor()

        query = "SELECT MessageID FROM messages WHERE `FileName`='SO_STOCKEMAIL25.csv' ORDER BY DateSend DESC LIMIT 1;"
        cursor.execute(query)

        # جلب النتيجة
        result = cursor.fetchone()

        if result:
            print("MessageID:", result[0])
            message_id = result[0]
            print("this is message id => ("+ message_id +")")
        else:
            print("No results found.")

        # جلب الرسالة وطباعة محتواها
        status, message_data = mail.fetch(message_id, '(RFC822)')
        if status == 'OK':
            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)
            
            file_name = 'SO_STOCKEMAIL25.csv'
            collected_records = []

            # فحص المرفقات للعثور على ملف CSV
            for attachment in attachments:
                if attachment.get_filename() == file_name:
                    # قراءة الملف CSV وطباعة العمود الذي عنوانه "VIN" فقط
                    csv_data = attachment.get_payload(decode=True).decode()
                    csv_file = io.StringIO(csv_data)
                    csv_reader = csv.DictReader(csv_file)
                    for row in csv_reader:
                        # جلب السجلات التي تحتوي على VIN المطلوب إدخاله
                        cursor.execute("SELECT * FROM change_stock_1 WHERE vin = %s AND Stock_Type != %s", (row['Chassis No.'], row['Stock Description']))
                        existing_records = cursor.fetchall()

                        if not existing_records:
                            saudi_timezone = pytz.timezone('Asia/Riyadh')
                            current_datetime = datetime.now(saudi_timezone)
                            formatted_datetime = current_datetime.strftime('%Y-%m-%d %H:%M:%S')
                        else:
                            already_count_row += 1
                            print(f"VIN already exists: {row['Chassis No.']}")
                            print(already_count_row)
                            print(existing_records)

                            # Add Stock Type and Stock Description to collected records
                            for record in existing_records:
                                record_data = list(record)
                                Chassis_No = row['Chassis No.']
                                stock_description = row['Stock Description']
                                stock_type = row['Stock Type']  # Add Stock Type from CSV file
                                record_data.append(Chassis_No)  # Append Stock Type to the new column
                                record_data.append(stock_type)  # Append Stock Type to the new column
                                record_data.append(stock_description)  # Append Stock Description to the new column
                                collected_records.append(record_data)

            # إذا تم جمع سجلات، قم بإنشاء ملف CSV وإرساله
            if collected_records:
                filename = 'Stock changes of Autoline.csv'
                with open(filename, 'w', newline='') as csvfile:
                    csvwriter = csv.writer(csvfile)

                    # Writing headers with the additional columns
                    headers = [field[0] for field in cursor.description]
                    headers.append('Chassis No. From Autoline')  # اسم العمود الجديد هنا
                    headers.append('Stock Type From Autoline')  # اسم العمود الجديد هنا
                    headers.append('Stock Description From Autoline')  # اسم العمود الجديد هنا
                    csvwriter.writerow(headers)

                    # Writing data with the additional columns values
                    for record in collected_records:
                        csvwriter.writerow(record)

                # إعداد البريد الإلكتروني
                msg = MIMEMultipart()
                msg['From'] = from_email
                msg['To'] = ", ".join(to_email)
                msg['Subject'] = 'Stock changes of Autoline'

                body = 'Inventory changes from outside the inventory management system.'
                msg.attach(MIMEText(body, 'plain'))

                attachment = open(filename, 'rb')
                part = MIMEBase('application', 'octet-stream')
                part.set_payload(attachment.read())
                encoders.encode_base64(part)
                part.add_header('Content-Disposition', f'attachment; filename= {filename}')
                msg.attach(part)

                text = msg.as_string()
                server.sendmail(from_email, to_email, text)

            print("close")
            conn.close()
            cursor.close()
        else:
            print("Failed to fetch message")

        connected = True  # تم الاتصال بنجاح
    except imaplib.IMAP4.error as e:
        print("Failed to connect to mail server, retrying in 5 seconds...")
        connected = False
        time.sleep(5)
    except mysql.connector.Error as r:
        print("Failed to connect to db server, retrying in 5 seconds...")
        connected = False
        time.sleep(5)
    except Exception as ex:
        print("An unexpected error occurred:", str(ex))
        connected = False
        time.sleep(5)

mail.logout()
