CREATE PROCEDURE UpdatePricesAndQuantities()
BEGIN

    -- Drop the temporary table if it exists
    DROP TEMPORARY TABLE IF EXISTS temp_calculated_prices;
    
    -- Create Temporary Table with Calculated Prices and Quantities
    CREATE TEMPORARY TABLE temp_calculated_prices AS
    SELECT 
        inventory.inventorydata_id as inventoryID,
        inventory.cost as cost,
        inventory.coreprice as coreprice,
        cpe.entity_id AS productID,
        JSON_UNQUOTE(JSON_EXTRACT(calculatePricesAndQty(inventory.cost, inventory.coreprice, products.unit_weight, IFNULL(sumPriceTable.value,0), IF(numberOfPiecesTable.value = 0, 1, numberOfPiecesTable.value), inventory.qtyonhand), '$.ebay_price')) AS ebay_price,
        JSON_UNQUOTE(JSON_EXTRACT(calculatePricesAndQty(inventory.cost, inventory.coreprice, products.unit_weight, IFNULL(sumPriceTable.value,0), IF(numberOfPiecesTable.value = 0, 1, numberOfPiecesTable.value), inventory.qtyonhand), '$.magento_price')) AS magento_price,
        JSON_UNQUOTE(JSON_EXTRACT(calculatePricesAndQty(inventory.cost, inventory.coreprice, products.unit_weight, IFNULL(sumPriceTable.value,0), IF(numberOfPiecesTable.value = 0, 1, numberOfPiecesTable.value), inventory.qtyonhand), '$.magento_qty')) AS magento_qty,
        JSON_UNQUOTE(JSON_EXTRACT(calculatePricesAndQty(inventory.cost, inventory.coreprice, products.unit_weight, IFNULL(sumPriceTable.value,0), IF(numberOfPiecesTable.value = 0, 1, numberOfPiecesTable.value), inventory.qtyonhand), '$.ebay_qty')) AS ebay_qty
    FROM catalog_product_entity cpe
    LEFT JOIN catalog_product_entity_varchar AS lineTable ON lineTable.entity_id = cpe.entity_id AND lineTable.attribute_id = 175
    LEFT JOIN catalog_product_entity_varchar AS partTable ON partTable.entity_id = cpe.entity_id AND partTable.attribute_id = 166
    LEFT JOIN catalog_product_entity_varchar AS sumPriceTable ON sumPriceTable.entity_id = cpe.entity_id AND sumPriceTable.attribute_id = 183
    LEFT JOIN catalog_product_entity_varchar AS numberOfPiecesTable ON numberOfPiecesTable.entity_id = cpe.entity_id AND numberOfPiecesTable.attribute_id = 178
    LEFT JOIN xlentparts_partsauthority_inventorydata AS inventory ON partTable.value = inventory.part AND lineTable.value = inventory.line
    LEFT JOIN xlentparts_partsauthority_products AS products ON partTable.value = products.inventory_part AND lineTable.value = products.inventory_line
    WHERE 
        cpe.entity_id IS NOT NULL
        AND lineTable.entity_id IS NOT NULL
        AND partTable.entity_id IS NOT NULL;
    
    -- Update Magento Prices in catalog_product_entity_decimal Table
    UPDATE catalog_product_entity_decimal cped
    LEFT JOIN temp_calculated_prices tcp ON tcp.productID = cped.entity_id
    JOIN eav_attribute ea ON ea.attribute_id = cped.attribute_id AND ea.attribute_code = 'price'
    SET cped.value = tcp.magento_price
    WHERE tcp.productID IS NOT NULL AND tcp.inventoryID IS NOT NULL;
    
    -- Update Magento Prices in catalog_product_entity_decimal Table
    UPDATE catalog_product_entity_decimal cped
    LEFT JOIN temp_calculated_prices tcp ON tcp.productID = cped.entity_id
    JOIN eav_attribute ea ON ea.attribute_id = cped.attribute_id AND ea.attribute_code = 'cost'
    SET cped.value = tcp.cost + tcp.coreprice
    WHERE tcp.productID IS NOT NULL AND tcp.inventoryID IS NOT NULL;

    -- Delete all records where customer_group_id is 4 in catalog_product_entity_tier_price table
    DELETE cped FROM catalog_product_entity_tier_price cped LEFT JOIN temp_calculated_prices tcp ON tcp.productID = cped.entity_id WHERE cped.customer_group_id = 4 AND tcp.inventoryID IS NOT NULL;
    
   -- Insert eBay Prices in catalog_product_entity_tier_price table
    INSERT INTO catalog_product_entity_tier_price (
        entity_id, 
        customer_group_id, 
        qty, 
        value, 
        website_id, 
        all_groups, 
        percentage_value
    )
    SELECT 
        tcp.productID, 
        4 as customer_group_id, 
        tcp.ebay_qty as qty, 
        tcp.ebay_price as value, 
        0 as website_id,
        0 as all_groups, 
        NULL as percentage_value 
    FROM temp_calculated_prices tcp WHERE tcp.inventoryID IS NOT NULL;

    UPDATE catalog_product_entity_tier_price cped LEFT JOIN temp_calculated_prices tcp ON tcp.productID = cped.entity_id SET cped.qty = 0 WHERE tcp.inventoryID IS NULL  ;
    
    -- Update Magento Quantities in cataloginventory_stock_item Table and Set Stock Status
    UPDATE cataloginventory_stock_item csi
    LEFT JOIN temp_calculated_prices tcp ON tcp.productID = csi.product_id
    SET 
        csi.qty = tcp.magento_qty,
        csi.is_in_stock = CASE WHEN tcp.magento_qty <= 0 THEN 0 ELSE 1 END
    WHERE tcp.productID IS NOT NULL  AND tcp.inventoryID IS NOT NULL;
    
    -- Update Quantities in inventory_source_item Table
    UPDATE inventory_source_item isi
    LEFT JOIN temp_calculated_prices tcp ON tcp.productID = isi.source_item_id
    SET isi.quantity = tcp.magento_qty
    WHERE tcp.productID IS NOT NULL  AND tcp.inventoryID IS NOT NULL;

        -- Update Magento Quantities in cataloginventory_stock_item Table and Set Stock Status
    UPDATE cataloginventory_stock_item csi
    LEFT JOIN temp_calculated_prices tcp ON tcp.productID = csi.product_id
    SET 
        csi.qty = 0,
        csi.is_in_stock = 0
    WHERE tcp.productID IS NOT NULL AND tcp.inventoryID IS NULL;
    
    -- Update Quantities in inventory_source_item Table
    UPDATE inventory_source_item isi
    LEFT JOIN temp_calculated_prices tcp ON tcp.productID = isi.source_item_id
    SET isi.quantity = 0
    WHERE tcp.productID IS NOT NULL  AND tcp.inventoryID IS NULL;
    
END;
