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



update_query_1 = """
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_1)
conn.commit()
# ------------------------------------------------------
update_query_2 = """
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_2)
conn.commit()
# ------------------------------------------------------
update_query_3 = """
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_3)
conn.commit()
# ------------------------------------------------------
update_query_4 = """
UPDATE MasterConfirmedSalesOrders AS m JOIN ConfirmedSalesOrders AS i ON m.Vin = i.Vin SET m.Color = i.ColorDesc;
"""
cursor.execute(update_query_4)
conn.commit()
# ------------------------------------------------------
update_query_5 = """
UPDATE MasterConfirmedSalesOrders AS m JOIN Vehicle_movement_requests AS i ON m.RequestId_VTR = i.request_id SET m.Status_VTR = i.Status,m.LocationOrConditionVehicle_VTR = i.Location_or_condition_vehicle,m.Destination_VTR = i.Destination,m.DateCreated_VTR = i.Date_Created;
"""
cursor.execute(update_query_5)
conn.commit()
# ------------------------------------------------------
update_query_6 = """
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_6)
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, Payment, BranchName
)
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, Source.PaymentMethod, Source.BranchName
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()
# ------------------------------------------------------
update_query_7 = """
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_7)
conn.commit()
# ------------------------------------------------------
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()

for row in results:
    inner_query = """
    SELECT * 
    FROM MasterConfirmedSalesOrders 
    WHERE VIN = %s ;
    """
    cursor.execute(inner_query, (row[1],))

    inner_rows = cursor.fetchall()
    
    if inner_rows and len(inner_rows[0]) > 0 and inner_rows[0][36]:
        insert_query = """
  INSERT INTO MasterConfirmedSalesOrders (
    Vin, 
    RequestId_VTR, 
    Status_VTR, 
    Destination_VTR, 
    DateCreated_VTR,
    LocationOrConditionVehicle_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,
    Payment,
    BranchName
)
SELECT 
    Vin, 
    %s AS RequestId_VTR, 
    %s AS Status_VTR, 
    %s AS Destination_VTR, 
    %s AS DateCreated_VTR, 
    %s AS LocationOrConditionVehicle_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,
    Payment,
    BranchName
FROM 
    MasterConfirmedSalesOrders
WHERE 
    Vin = %s ;

        """
        cursor.execute(insert_query, (row[0], row[7], row[3], row[9], row[4], row[1]))
        conn.commit()
        print("تم الإدخال بنجاح باستخدام البيانات من جدول آخر")       

    else:
        update_query = """
        UPDATE MasterConfirmedSalesOrders 
        SET RequestId_VTR = %s
        WHERE ID = %s;
        """
        cursor.execute(update_query, (row[0], inner_rows[0][0]))
        conn.commit()
        print("تم التحديث بنجاح")
# ------------------------------------------------------
update_query_8 = """
DELETE FROM MasterConfirmedSalesOrders
WHERE ID NOT IN (
    SELECT * FROM (
        SELECT MIN(ID)
        FROM MasterConfirmedSalesOrders
        GROUP BY RequestId_VTR
    ) AS temp_table
)
AND RequestId_VTR IS NOT NULL;
"""
cursor.execute(update_query_8)
conn.commit()
# ------------------------------------------------------





cursor.close()
conn.close()
