ALTER TABLE items
  ADD COLUMN IF NOT EXISTS product_code VARCHAR(120) DEFAULT NULL AFTER item_name;

UPDATE items i
JOIN (
  SELECT
    item_name,
    CONCAT(
      'IMS-',
      UPPER(
        TRIM(BOTH '-' FROM REGEXP_REPLACE(item_name, '[^A-Za-z0-9]+', '-'))
      ),
      '-',
      LEFT(SHA1(UPPER(TRIM(item_name))), 8)
    ) AS generated_product_code
  FROM items
  WHERE item_name IS NOT NULL
    AND item_name != ''
  GROUP BY item_name
) product_groups ON product_groups.item_name = i.item_name
SET i.product_code = LEFT(product_groups.generated_product_code, 120)
WHERE i.product_code IS NULL
   OR i.product_code = '';

CREATE INDEX IF NOT EXISTS idx_items_product_code ON items (product_code);

CREATE TABLE IF NOT EXISTS mra_eis_product_code_map (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ims_product_code VARCHAR(120) NOT NULL,
  ims_item_name VARCHAR(255) NOT NULL,
  terminal_local_id INT DEFAULT NULL,
  site_product_id INT DEFAULT NULL,
  eis_product_code VARCHAR(120) NOT NULL,
  eis_product_name VARCHAR(255) NOT NULL,
  tax_rate_id VARCHAR(50) DEFAULT NULL,
  mapped_by INT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_mra_eis_product_code_map (ims_product_code),
  KEY idx_mra_eis_product_code_terminal (terminal_local_id),
  KEY idx_mra_eis_product_code_eis_product (eis_product_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO mra_eis_product_code_map (
  ims_product_code,
  ims_item_name,
  terminal_local_id,
  site_product_id,
  eis_product_code,
  eis_product_name,
  tax_rate_id,
  mapped_by
)
SELECT
  i.product_code,
  MIN(i.item_name) AS ims_item_name,
  MAX(map.terminal_local_id) AS terminal_local_id,
  MAX(map.site_product_id) AS site_product_id,
  MAX(map.product_code) AS eis_product_code,
  MAX(map.product_name) AS eis_product_name,
  MAX(map.tax_rate_id) AS tax_rate_id,
  MAX(map.mapped_by) AS mapped_by
FROM items i
INNER JOIN mra_eis_item_product_map map ON map.item_id = i.item_id
WHERE i.product_code IS NOT NULL
  AND i.product_code != ''
GROUP BY i.product_code
ON DUPLICATE KEY UPDATE
  ims_item_name = VALUES(ims_item_name),
  terminal_local_id = VALUES(terminal_local_id),
  site_product_id = VALUES(site_product_id),
  eis_product_code = VALUES(eis_product_code),
  eis_product_name = VALUES(eis_product_name),
  tax_rate_id = VALUES(tax_rate_id),
  mapped_by = VALUES(mapped_by);
