import mysql.connector

# Establish connection to the database
conn = mysql.connector.connect(
    host="vsrv681.armadaservers.com",
    user="mint303_change_stock",
    password="Plmokn123@",
    database="mint303_change_stock"
)
cursor = conn.cursor()

# Execute the SQL statement
sql_statement = """
UPDATE RFIDScanData AS main
JOIN (
    SELECT
        rfidScanDataId,
        epc,
        ROW_NUMBER() OVER (PARTITION BY epc ORDER BY readTimestamp DESC) AS RowNum
    FROM RFIDScanData  
) AS LatestScanData ON main.rfidScanDataId = LatestScanData.rfidScanDataId
SET main.Status = CASE WHEN LatestScanData.RowNum = 1 THEN 'Updated' ELSE 'Old' END;
"""
cursor.execute(sql_statement)

# Commit changes and close connection
conn.commit()
conn.close()
