-- items.product_code is the EIS-facing product identifier.
-- items.unique_code remains the local serialized-item reference.

UPDATE items i
INNER JOIN mra_eis_product_code_map pcm
    ON pcm.ims_product_code = i.product_code
SET i.product_code = pcm.eis_product_code
WHERE pcm.eis_product_code IS NOT NULL
  AND pcm.eis_product_code <> '';

UPDATE items i
INNER JOIN mra_eis_product_code_map pcm
    ON pcm.ims_product_code = TRIM(SUBSTRING_INDEX(i.technical_specs, ' barcode: ', -1))
SET i.product_code = pcm.eis_product_code
WHERE i.technical_specs LIKE '% barcode: %'
  AND pcm.eis_product_code IS NOT NULL
  AND pcm.eis_product_code <> '';

UPDATE items
SET product_code = TRIM(SUBSTRING_INDEX(technical_specs, ' barcode: ', -1))
WHERE technical_specs LIKE '% barcode: %'
  AND (product_code IS NULL OR product_code = '' OR product_code LIKE 'IMS-%');

UPDATE items i
SET i.product_code = (
    SELECT MIN(ps.barcode)
    FROM product_stock ps
    WHERE ps.product_name = i.item_name
      AND ps.barcode IS NOT NULL
      AND ps.barcode <> ''
      AND (ps.branch_id = i.current_location_id OR i.current_location_id IS NULL)
)
WHERE (i.product_code IS NULL OR i.product_code = '' OR i.product_code LIKE 'IMS-%')
  AND EXISTS (
      SELECT 1
      FROM product_stock ps
      WHERE ps.product_name = i.item_name
        AND ps.barcode IS NOT NULL
        AND ps.barcode <> ''
        AND (ps.branch_id = i.current_location_id OR i.current_location_id IS NULL)
  );