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

def convert_date(date_str):
    date_str = date_str.strip()
    if not date_str:  # تحقق من أن السلسلة النصية غير فارغة بعد التقليم
        return None  # أو يمكنك رفع استثناء، حسب حاجتك
    try:
        # حاول تحويل النص إلى كائن datetime
        date_obj = datetime.strptime(date_str, "%d/%m/%Y")
        return date_obj.strftime("%Y-%m-%d")
    except ValueError as e:
        print(f"Error parsing date '{date_str}': {e}")
        return None  # أو يمكنك رفع استثناء، حسب حاجتك
added_count_row = 0
already_count_row = 0

# جلب معرف الرسالة
# message_id = '1216'
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 = 'ConfirmedSalesOrders.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 ConfirmedSalesOrders WHERE Vin = %s", (row['Vin'],))
                        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')
                           
                            EstimDateReceived = convert_date(row['Estim.date.received'])
                            if EstimDateReceived:
                                print(f"EstimDateReceived: {EstimDateReceived}")

                            DateReserved = convert_date(row['Date.reserved'])
                            if DateReserved:
                                print(f"DateReserved: {DateReserved}")

                            ShippingDate = convert_date(row['Shipping Date'])
                            if ShippingDate:
                                print(f"ShippingDate: {ShippingDate}")

                            AdoptionDate = convert_date(row['Adoption date'])
                            if AdoptionDate:
                                print(f"AdoptionDate: {AdoptionDate}")

                            SalesOrderConfirmedDate = convert_date(row['Sales Order confirmed Date'])
                            if SalesOrderConfirmedDate:
                                print(f"SalesOrderConfirmedDate: {SalesOrderConfirmedDate}")

                            EstimatedPortArrival = convert_date(row['Estimated Port Arrival'])
                            if EstimatedPortArrival:
                                print(f"EstimatedPortArrival: {EstimatedPortArrival}")



                            EnquiryCreationDate = convert_date(row['Enquiry creation date'])
                            if EnquiryCreationDate:
                                print(f"EnquiryCreationDate: {EnquiryCreationDate}")


                            QuotationDate = convert_date(row['Quotation Date'])
                            if QuotationDate:
                                print(f"QuotationDate: {QuotationDate}")



                            SalesOrderDate = convert_date(row['Sales Order Date'])
                            if SalesOrderDate:
                                print(f"SalesOrderDate: {SalesOrderDate}")


                            if row['Vin'] and row['Vin'].strip():

                                cursor.execute("""INSERT INTO ConfirmedSalesOrders 
                                               (
                                               StockType,
                                               ReservedFor,
                                               CustomerMobile,
                                               ReservedBy,
                                               ModelCode,
                                               VariantCode,
                                               Vin,
                                               ModelYear,
                                               EstimDateReceived,
                                               PhysicalBranch,
                                               DateReserved,
                                               ColourWheelbase,
                                               ACSStatus,
                                               ProgressCode,
                                               OrderRef,
                                               OrderNumber,
                                               EnquiryNumber,
                                               ShipName,
                                               ShipmentNo,
                                               ShippingDate,
                                               EstimatedPortArrival,
                                               AdoptionDate,
                                               ConfirmedOrd,
                                               SalesOrderConfirmedDate,
                                               StockAge,
                                               EnquiryCreationDate,
                                               QuotationDate,
                                               SalesOrderDate,
                                               DateAdded,
                                               DateLastAction,
                                               ColorDesc,
                                               PaymentMethod,
                                               SellingBranchCode,
                                               BranchName
                                               ) VALUES (%s,%s,%s,%s,%s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""", 
                                               (
                                               row['Stock Type'],
                                               row['Reserved For'],
                                               row['Customer Mobile'],
                                               row['Reserved By'],
                                               row['Model Code'],
                                               row['Variant.code'],
                                               row['Vin'],
                                               row['Model Year'],
                                               EstimDateReceived,
                                               row['Physical.Branch'],
                                               DateReserved,
                                               row['Colour/Wheelbase'],
                                               row['A/c.s.status'],
                                               row['Progress.code'],
                                               row['Order Ref'],
                                               row['Order.number'],
                                               row['Enquiry Number'],
                                               row['Ship Name'],
                                               row['Shipment No:'],
                                               ShippingDate,
                                               EstimatedPortArrival,
                                               AdoptionDate,
                                               row['Confirmed Ord'],
                                               SalesOrderConfirmedDate,
                                               row['StockAge'],
                                               EnquiryCreationDate,
                                               QuotationDate,
                                               SalesOrderDate,
                                               formatted_datetime,
                                               formatted_datetime,
                                               row['Color Desc'],
                                               row['Payment Method'],
                                               row['Selling Branch Code'],
                                               row['BRANCH NAME']
                                               ))
                                print(f"Added VIN to change_Stock_1 Table: {row['Vin']}")
                                cursor.execute("""INSERT INTO ConfirmedSalesOrdersLogs 
                                               (
                                               StockType,
                                               ReservedFor,
                                               CustomerMobile,
                                               ReservedBy,
                                               ModelCode,
                                               VariantCode,
                                               Vin,
                                               ModelYear,
                                               EstimDateReceived,
                                               PhysicalBranch,
                                               DateReserved,
                                               ColourWheelbase,
                                               ACSStatus,
                                               ProgressCode,
                                               OrderRef,
                                               OrderNumber,
                                               EnquiryNumber,
                                               ShipName,
                                               ShipmentNo,
                                               ShippingDate,
                                               EstimatedPortArrival,
                                               AdoptionDate,
                                               ConfirmedOrd,
                                               SalesOrderConfirmedDate,
                                               StockAge,
                                               EnquiryCreationDate,
                                               QuotationDate,
                                               SalesOrderDate,
                                               DateAdded,
                                               DateLastAction,
                                               ColorDesc,
                                               PaymentMethod,
                                               SellingBranchCode,
                                               BranchName
                                               ) VALUES (%s,%s,%s,%s,%s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""", 
                                               (
                                               row['Stock Type'],
                                               row['Reserved For'],
                                               row['Customer Mobile'],
                                               row['Reserved By'],
                                               row['Model Code'],
                                               row['Variant.code'],
                                               row['Vin'],
                                               row['Model Year'],
                                               EstimDateReceived,
                                               row['Physical.Branch'],
                                               DateReserved,
                                               row['Colour/Wheelbase'],
                                               row['A/c.s.status'],
                                               row['Progress.code'],
                                               row['Order Ref'],
                                               row['Order.number'],
                                               row['Enquiry Number'],
                                               row['Ship Name'],
                                               row['Shipment No:'],
                                               ShippingDate,
                                               EstimatedPortArrival,
                                               AdoptionDate,
                                               row['Confirmed Ord'],
                                               SalesOrderConfirmedDate,
                                               row['StockAge'],
                                               EnquiryCreationDate,
                                               QuotationDate,
                                               SalesOrderDate,
                                               formatted_datetime,
                                               formatted_datetime,
                                               row['Color Desc'],
                                               row['Payment Method'],
                                               row['Selling Branch Code'],
                                               row['BRANCH NAME']
                                               ))
                                # cursor.execute("INSERT INTO ConfirmedSalesOrdersLogs (StockType,ReservedFor,CustomerMobile,ReservedBy,ModelCode,VariantCode,Vin,ModelYear,EstimDateReceived,PhysicalBranch,DateReserved,ColourWheelbase,ACSStatus,ProgressCode,OrderRef,OrderNumber,EnquiryNumber,ShipName,ShipmentNo,ShippingDate,EstimatedPortArrival,AdoptionDate,ConfirmedOrd,SalesOrderConfirmedDate,StockAge,DateAdded,DateLastAction) VALUES (%s,%s,%s,%s,%s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", 
                                # (row['Stock Type'],row['Reserved For'],row['Customer Mobile'],row['Reserved By'],row['Model Code'],row['Variant.code'],row['Vin'],row['Model Year'],EstimDateReceived,row['Physical.Branch'],DateReserved,row['Colour/Wheelbase'],row['A/c.s.status'],row['Progress.code'],row['Order Ref'],row['Order.number'],row['Enquiry Number'],row['Ship Name'],row['Shipment No:'],ShippingDate,EstimatedPortArrival,AdoptionDate,row['Confirmed Ord'],SalesOrderConfirmedDate,row['StockAge'],formatted_datetime,formatted_datetime))
                                print(f"Added VIN to Operations Table: {row['Vin']}")
                                print(added_count_row)
                            
                            
                        else:

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

                        
                            EstimDateReceived = convert_date(row['Estim.date.received'])
                            if EstimDateReceived:
                                print(f"EstimDateReceived: {EstimDateReceived}")

                            DateReserved = convert_date(row['Date.reserved'])
                            if DateReserved:
                                print(f"DateReserved: {DateReserved}")

                            ShippingDate = convert_date(row['Shipping Date'])
                            if ShippingDate:
                                print(f"ShippingDate: {ShippingDate}")

                            AdoptionDate = convert_date(row['Adoption date'])
                            if AdoptionDate:
                                print(f"AdoptionDate: {AdoptionDate}")

                            SalesOrderConfirmedDate = convert_date(row['Sales Order confirmed Date'])
                            if SalesOrderConfirmedDate:
                                print(f"SalesOrderConfirmedDate: {SalesOrderConfirmedDate}")

                            EstimatedPortArrival = convert_date(row['Estimated Port Arrival'])
                            if EstimatedPortArrival:
                                print(f"EstimatedPortArrival: {EstimatedPortArrival}")

                            EnquiryCreationDate = convert_date(row['Enquiry creation date'])
                            if EnquiryCreationDate:
                                print(f"EnquiryCreationDate: {EnquiryCreationDate}")


                            QuotationDate = convert_date(row['Quotation Date'])
                            if QuotationDate:
                                print(f"QuotationDate: {QuotationDate}")



                            SalesOrderDate = convert_date(row['Sales Order Date'])
                            if SalesOrderDate:
                                print(f"SalesOrderDate: {SalesOrderDate}")

                            cursor.execute("""UPDATE ConfirmedSalesOrders SET 
                                           StockType = %s ,
                                           CustomerMobile = %s ,
                                           ReservedBy = %s ,
                                           ModelCode = %s ,
                                           VariantCode = %s ,
                                           ModelYear = %s ,
                                           EstimDateReceived = %s ,
                                           PhysicalBranch = %s ,
                                           DateReserved = %s ,
                                           ColourWheelbase	 = %s ,
                                           ACSStatus = %s ,
                                           ProgressCode = %s ,
                                           OrderRef = %s ,
                                           OrderNumber = %s ,
                                           EnquiryNumber = %s ,
                                           ShipName = %s ,
                                           ShipmentNo = %s ,
                                           ShippingDate	 = %s ,
                                           EstimatedPortArrival = %s ,
                                           AdoptionDate = %s ,
                                           ConfirmedOrd = %s ,
                                           SalesOrderConfirmedDate = %s ,
                                           StockAge = %s ,
                                           EnquiryCreationDate = %s ,
                                           QuotationDate = %s ,
                                           SalesOrderDate = %s ,
                                           DateLastAction = %s, 
                                           ColorDesc = %s ,
                                           PaymentMethod = %s ,
                                           SellingBranchCode = %s ,
                                           BranchName = %s
                                           WHERE Vin = %s""", (
                                            row['Stock Type'],
                                            row['Customer Mobile'],
                                            row['Reserved By'],
                                            row['Model Code'],
                                            row['Variant.code'],
                                            row['Model Year'],
                                            EstimDateReceived,
                                            row['Physical.Branch'],
                                            DateReserved,
                                            row['Colour/Wheelbase'],
                                            row['A/c.s.status'],
                                            row['Progress.code'],
                                            row['Order Ref'],
                                            row['Order.number'],
                                            row['Enquiry Number'],
                                            row['Ship Name'],
                                            row['Shipment No:'],
                                            ShippingDate,
                                            EstimatedPortArrival,
                                            AdoptionDate,
                                            row['Confirmed Ord'],
                                            SalesOrderConfirmedDate,
                                            row['StockAge'],
                                            EnquiryCreationDate,
                                            QuotationDate,
                                            SalesOrderDate,                                            
                                            formatted_datetime,
                                            row['Color Desc'],
                                            row['Payment Method'],
                                            row['Selling Branch Code'],
                                            row['BRANCH NAME'],
                                            row['Vin']))

                            print(f"VIN already exists: {row['Vin']}")
                            if row['Vin'] and row['Vin'].strip():
                                print(f"Added VIN to change_Stock_1 Table: {row['Vin']}")
                                cursor.execute("""INSERT INTO ConfirmedSalesOrdersLogs 
                                               (
                                               StockType,
                                               ReservedFor,
                                               CustomerMobile,
                                               ReservedBy,
                                               ModelCode,
                                               VariantCode,
                                               Vin,
                                               ModelYear,
                                               EstimDateReceived,
                                               PhysicalBranch,
                                               DateReserved,
                                               ColourWheelbase,
                                               ACSStatus,
                                               ProgressCode,
                                               OrderRef,
                                               OrderNumber,
                                               EnquiryNumber,
                                               ShipName,
                                               ShipmentNo,
                                               ShippingDate,
                                               EstimatedPortArrival,
                                               AdoptionDate,
                                               ConfirmedOrd,
                                               SalesOrderConfirmedDate,
                                               StockAge,
                                               EnquiryCreationDate,
                                               QuotationDate,
                                               SalesOrderDate,
                                               DateAdded,
                                               DateLastAction,
                                               ColorDesc,
                                               PaymentMethod,
                                               SellingBranchCode,
                                               BranchName
                                               ) VALUES (%s,%s,%s,%s,%s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""", 
                                               (
                                               row['Stock Type'],
                                               row['Reserved For'],
                                               row['Customer Mobile'],
                                               row['Reserved By'],
                                               row['Model Code'],
                                               row['Variant.code'],
                                               row['Vin'],
                                               row['Model Year'],
                                               EstimDateReceived,
                                               row['Physical.Branch'],
                                               DateReserved,
                                               row['Colour/Wheelbase'],
                                               row['A/c.s.status'],
                                               row['Progress.code'],
                                               row['Order Ref'],
                                               row['Order.number'],
                                               row['Enquiry Number'],
                                               row['Ship Name'],
                                               row['Shipment No:'],
                                               ShippingDate,
                                               EstimatedPortArrival,
                                               AdoptionDate,
                                               row['Confirmed Ord'],
                                               SalesOrderConfirmedDate,
                                               row['StockAge'],
                                               EnquiryCreationDate,
                                               QuotationDate,
                                               SalesOrderDate,
                                               formatted_datetime,
                                               formatted_datetime,
                                               row['Color Desc'],
                                               row['Payment Method'],
                                               row['Selling Branch Code'],
                                               row['BRANCH NAME']
                                               ))                            
                            print(already_count_row)






                        # تأكيد التغييرات
                        conn.commit()
            print("close") 
            # subprocess.Popen(["python3", "Get_Emaile_121.py", message_id])
            # 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...")
        print(e)
        connected = False
        time.sleep(1)

    except mysql.connector.Error as r:
        print("Failed to connect to db server, retrying in 5 seconds...")
        print(r)

        connected = False
        time.sleep(1)

    except Exception as ex:
        print("An unexpected error occurred:", str(ex))
        print(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()

