import mysql.connector

# إعداد الاتصال بقاعدة البيانات
conn = mysql.connector.connect(
    host='vsrv681.armadaservers.com',  # استبدل بالـhost الخاص بك
    user='mint303_change_stock',  # استبدل بالاسم المستخدم الخاص بك
    password='Plmokn123@',  # استبدل بكلمة المرور الخاصة بك
    database='mint303_change_stock'  # استبدل باسم قاعدة البيانات الخاصة بك
)

# إنشاء كيرسور لتنفيذ الاستعلامات
cursor = conn.cursor()

# كتابة الاستعلام مع إدخال المتغير
query = """
SELECT * 
FROM Vehicle_movement_requests 
WHERE VIN IN (
    SELECT VIN 
    FROM MasterConfirmedSalesOrders
) 
AND Request_id NOT IN (
    SELECT RequestId_VTR 
    FROM MasterConfirmedSalesOrders
    WHERE RequestId_VTR IS NOT NULL
);
"""

# تنفيذ الاستعلام الأول
cursor.execute(query)

# استهلاك جميع النتائج من الاستعلام الأول
results = cursor.fetchall()

# الأمر الثاني: تحديث MasterConfirmedSalesOrders
update_query_1 = """
UPDATE MasterConfirmedSalesOrders AS Target
JOIN ConfirmedSalesOrders AS Source
ON Target.Vin = Source.Vin
SET 
    Target.StockType = Source.StockType,
    Target.ReservedFor = Source.ReservedFor,
    Target.CustomerMobile = Source.CustomerMobile,
    Target.ReservedBy = Source.ReservedBy,
    Target.ModelCode = Source.ModelCode,
    Target.VariantCode = Source.VariantCode,
    Target.ModelYear = Source.ModelYear,
    Target.EstimDateReceived = Source.EstimDateReceived,
    Target.PhysicalBranch = Source.PhysicalBranch,
    Target.DateReserved = Source.DateReserved,
    Target.ColourWheelbase = Source.ColourWheelbase,
    Target.ACSStatus = Source.ACSStatus,
    Target.ProgressCode = Source.ProgressCode,
    Target.OrderRef = Source.OrderRef,
    Target.OrderNumber = Source.OrderNumber,
    Target.EnquiryNumber = Source.EnquiryNumber,
    Target.ShipName = Source.ShipName,
    Target.ShipmentNo = Source.ShipmentNo,
    Target.ShippingDate = Source.ShippingDate,
    Target.EstimatedPortArrival = Source.EstimatedPortArrival,
    Target.AdoptionDate = Source.AdoptionDate,
    Target.ConfirmedOrd = Source.ConfirmedOrd,
    Target.SalesOrderConfirmedDate = Source.SalesOrderConfirmedDate,
    Target.StockAge = Source.StockAge,
    Target.DateAdded = Source.DateAdded,
    Target.DateLastAction = Source.DateLastAction,
    Target.EnquiryCreationDate = Source.EnquiryCreationDate,
    Target.QuotationDate = Source.QuotationDate,
    Target.SalesOrderDate = Source.SalesOrderDate,
    Target.BranchName = Source.BranchName,
    Target.Payment = Source.PaymentMethod;
"""
cursor.execute(update_query_1)
conn.commit()

# الأمر الثاني: إدراج السجلات الجديدة التي لا توجد بالفعل
insert_query = """
INSERT INTO MasterConfirmedSalesOrders (
    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, EnquiryCreationDate, QuotationDate, SalesOrderDate
)
SELECT
    Source.StockType, Source.ReservedFor, Source.CustomerMobile, Source.ReservedBy, Source.ModelCode, Source.VariantCode, Source.Vin, Source.ModelYear, Source.EstimDateReceived, Source.PhysicalBranch, Source.DateReserved, Source.ColourWheelbase, Source.ACSStatus, Source.ProgressCode, Source.OrderRef, Source.OrderNumber, Source.EnquiryNumber, Source.ShipName, Source.ShipmentNo, Source.ShippingDate, Source.EstimatedPortArrival, Source.AdoptionDate, Source.ConfirmedOrd, Source.SalesOrderConfirmedDate, Source.StockAge, Source.DateAdded, Source.DateLastAction, Source.EnquiryCreationDate, Source.QuotationDate, Source.SalesOrderDate
FROM ConfirmedSalesOrders AS Source
LEFT JOIN MasterConfirmedSalesOrders AS Target
ON Target.Vin = Source.Vin
WHERE Target.Vin IS NULL AND Source.Vin NOT IN (SELECT Vin FROM MasterConfirmedSalesOrders);
"""
cursor.execute(insert_query)
conn.commit()

# الأمر الثالث: تحديث MasterConfirmedSalesOrders باستخدام بيانات InvoicedVehicles
update_query_2 = """
UPDATE MasterConfirmedSalesOrders AS m
JOIN InvoicedVehicles AS i
ON m.EnquiryNumber = i.EnquiryNumberRetail OR m.EnquiryNumber = i.EnquiryNumberFleetBroker
SET m.InvoiceDate = i.InvoiceDate, m.InvoiceNumber = i.InvoiceNumber;
"""
cursor.execute(update_query_2)
conn.commit()

# تنفيذ الاستعلام الداخلي بناءً على النتائج من الاستعلام الأول
for row in results:
    inner_query = """
    SELECT * 
    FROM MasterConfirmedSalesOrders 
    WHERE VIN = %s AND RequestId_VTR IS NULL;
    """
    cursor.execute(inner_query, (row[1],))

    # جلب جميع الصفوف من الاستعلام الداخلي
    inner_rows = cursor.fetchall()
    
    if inner_rows:
        update_query = """
        UPDATE MasterConfirmedSalesOrders 
        SET RequestId_VTR = %s ,Status_VTR = %s ,Destination_VTR = %s ,DateCreated_VTR = %s 
        WHERE ID = %s;
        """
        cursor.execute(update_query, (row[0], row[7], row[3], row[9], inner_rows[0][0]))
        conn.commit()
        print("تم التحديث بنجاح")
    else:
        insert_query = """
        INSERT INTO MasterConfirmedSalesOrders (
        Vin, 
        RequestId_VTR, 
        Status_VTR, 
        Destination_VTR, 
        DateCreated_VTR, 
        StockType, 
        ReservedFor, 
        CustomerMobile, 
        ReservedBy, 
        ModelCode, 
        VariantCode, 
        ModelYear,
        EstimDateReceived,
        PhysicalBranch,
        DateReserved,
        ColourWheelbase,
        ACSStatus,
        ProgressCode,
        OrderRef,
        OrderNumber,
        EnquiryNumber,
        ShipName,
        ShipmentNo,
        ShippingDate,
        EstimatedPortArrival,
        AdoptionDate,
        ConfirmedOrd,
        SalesOrderConfirmedDate,
        StockAge,
        DateAdded,
        DateLastAction,
        EnquiryCreationDate,
        QuotationDate,
        SalesOrderDate,
        InvoiceDate,
        InvoiceNumber
        )
        SELECT 
        Vin, 
        %s, 
        %s, 
        %s, 
        %s, 
        StockType, 
        ReservedFor, 
        CustomerMobile, 
        ReservedBy, 
        ModelCode, 
        VariantCode, 
        ModelYear,
        EstimDateReceived,
        PhysicalBranch,
        DateReserved,
        ColourWheelbase,
        ACSStatus,
        ProgressCode,
        OrderRef,
        OrderNumber,
        EnquiryNumber,
        ShipName,
        ShipmentNo,
        ShippingDate,
        EstimatedPortArrival,
        AdoptionDate,
        ConfirmedOrd,
        SalesOrderConfirmedDate,
        StockAge,
        DateAdded,
        DateLastAction,
        EnquiryCreationDate,
        QuotationDate,
        SalesOrderDate,
        InvoiceDate,
        InvoiceNumber
        FROM MasterConfirmedSalesOrders
        WHERE Vin = %s;
        """
        cursor.execute(insert_query, (row[0], row[7], row[3], row[9], row[1]))
        conn.commit()
        print("تم الإدخال بنجاح باستخدام البيانات من جدول آخر")

# تحديثات إضافية
update_query_3 = """
UPDATE MasterConfirmedSalesOrders m
SET m.DateVehicleTransferComplete_VTR = (
    SELECT a.DateAction 
    FROM AnyAction a 
    WHERE a.request_id = m.RequestId_VTR 
    AND a.NewValue = 'VEHICLE TRANSFER COMPLETE'
    LIMIT 1
)
WHERE EXISTS (
    SELECT 1 
    FROM AnyAction a 
    WHERE a.request_id = m.RequestId_VTR 
    AND a.NewValue = 'VEHICLE TRANSFER COMPLETE'
);
"""
cursor.execute(update_query_3)
conn.commit()

update_query_4 = """
UPDATE MasterConfirmedSalesOrders m
SET m.DateCustomerReceived_VTR = (
    SELECT a.DateAction 
    FROM AnyAction a 
    WHERE a.request_id = m.RequestId_VTR 
    AND a.NewValue = 'CUSTOMER RECEIVED'
    LIMIT 1
)
WHERE EXISTS (
    SELECT 1 
    FROM AnyAction a 
    WHERE a.request_id = m.RequestId_VTR 
    AND a.NewValue = 'CUSTOMER RECEIVED'
);
"""
cursor.execute(update_query_4)
conn.commit()

update_query_5 = """
UPDATE MasterConfirmedSalesOrders m
SET m.Status_VTR = (
    SELECT v.Status 
    FROM Vehicle_movement_requests v 
    WHERE v.request_id = m.RequestId_VTR
)
WHERE EXISTS (
    SELECT 1 
    FROM Vehicle_movement_requests v 
    WHERE v.request_id = m.RequestId_VTR
);
"""
cursor.execute(update_query_5)
conn.commit()


update_query_6 = """
UPDATE MasterConfirmedSalesOrders m
SET m.Color = (
    SELECT v.Color 
    FROM change_stock_1 v 
    WHERE v.VIN = m.Vin
)
WHERE EXISTS (
    SELECT 1 
    FROM Vehicle_movement_requests v 
    WHERE v.VIN = m.Vin
);
"""


update_query_7 = """
UPDATE MasterConfirmedSalesOrders m
SET m.Color = (
    SELECT v.ColorDesc 
    FROM ConfirmedSalesOrders v 
    WHERE v.Vin = m.Vin
)
WHERE EXISTS (
    SELECT 1 
    FROM ConfirmedSalesOrders v 
    WHERE v.Vin = m.Vin
);
"""
cursor.execute(update_query_6)
conn.commit()

# إغلاق الاتصال
cursor.close()
conn.close()
