import imaplib
import email
import csv
import io
# import os
import time
import mysql.connector
import pytz
from datetime import datetime
import requests
import sys
import subprocess


added_count_row = 0
already_count_row = 0

# جلب معرف الرسالة
# message_id = '51'
message_id = sys.argv[1]

print("this is message id => ("+ message_id +")")
# تفاصيل البريد الإلكتروني
username = 'sultan@prod.nhcops.com'
password = 'Sultan135@'
mail_server = 'vsrv681.armadaservers.com'
mail_port = 993

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')

# جلب الرسالة وطباعة محتواها
        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'
            cursor = conn.cursor()
            cursor.execute("UPDATE change_stock_1 SET Status = %s", ("Out Stock",))

            # فحص المرفقات للعثور على ملف 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:
                        cursor = conn.cursor()
                    
                        # جلب السجلات التي تحتوي على VIN المطلوب إدخاله
                        cursor.execute("SELECT * FROM change_stock_1 WHERE vin = %s", (row['Chassis No.'],))
                        existing_records = cursor.fetchall()

                        if not existing_records:
                            added_count_row += 1
                            saudi_timezone = pytz.timezone('Asia/Riyadh')
                            current_datetime = datetime.now(saudi_timezone)
                            # تنسيق التاريخ والوقت بدون جزء الثواني
                            formatted_datetime = current_datetime.strftime('%Y-%m-%d %H:%M:%S')
                            # إذا لم يتم العثور على سجل موجود بنفس الـ VIN، يتم إضافة VIN
                            
                            cursor.execute("INSERT INTO change_stock_1 (vin,Model,Variant,Description,Color,Stock_Type_Code,Model_Year,Vehicle_Number,Stock_Sequence_Number,Account_Status,Vehicle_Location,Location_Description,Status,Date_Added,Date_last_action,Stock_Type) VALUES (%s,%s,%s,%s,%s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", 
                            (row['Chassis No.'],row['Model'],row['Variant'],row['Description'],row['Color'],row['Stock Type'],row['Model Year'],row['Vehicle Number'],row['Stock Sequence Number'],row['Account Status'],row['Vehicle Location'],row['Location Description'],'In Stock', formatted_datetime,formatted_datetime,row['Stock Description']))
                            print(f"Added VIN to change_Stock_1 Table: {row['Chassis No.']}")
                    

                    # إضافة السجل وتاريخ الإضافة إلى الجدول Operations
                            cursor.execute("INSERT INTO Operations (vin,Model,Variant,Description,Color,Stock_Type_Code,Model_Year,Vehicle_Number,Stock_Sequence_Number,Account_Status,Vehicle_Location,Location_Description,Status,Date_Added,Date_last_action,Stock_Type) VALUES (%s,%s,%s,%s,%s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", 
                            (row['Chassis No.'],row['Model'],row['Variant'],row['Description'],row['Color'],row['Stock Type'],row['Model Year'],row['Vehicle Number'],row['Stock Sequence Number'],row['Account Status'],row['Vehicle Location'],row['Location Description'],'In Stock', formatted_datetime,formatted_datetime,row['Stock Description']))
                            print(f"Added VIN to Operations Table: {row['Chassis No.']}")
                            print(added_count_row)
                            
                            
                        else:
                            already_count_row += 1
                            cursor.execute("UPDATE change_stock_1 SET Status = %s ,Account_Status = %s WHERE vin = %s", ("In Stock",row['Account Status'],row['Chassis No.']))
                            cursor.execute("UPDATE Operations SET Status = %s WHERE vin = %s", ("In Stock", row['Chassis No.']))
                            print(f"VIN already exists: {row['Chassis No.']}")
                            print(already_count_row)
                        # تأكيد التغييرات
                        conn.commit()
            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(1)

    except mysql.connector.Error as r:
        print("Failed to connect to db server, retrying in 5 seconds...")
        connected = False
        time.sleep(1)

    except Exception as ex:
        print("An unexpected error occurred:", str(ex))
        connected = False
        time.sleep(1)


# print("I Will send this message id to Get_Emaile_80.py:", message_id)
# subprocess.Popen(["python3", "Get_Emaile_80.py", message_id])

mail.logout()

