-- Script to clean up existing Shopify order notes by removing the (ID: xxxx) part
-- This script should be run after updating the webhook code to remove ID from new notes

-- Update notes in the orders table
UPDATE orders 
SET note = REGEXP_REPLACE(note, ' \\(ID: [0-9]+\\)', '') 
WHERE note LIKE '%Order from Shopify:%' 
  AND note LIKE '%(ID: %';

-- Update notes in the middleware table (if any exist)
UPDATE middleware 
SET Note = REGEXP_REPLACE(Note, ' \\(ID: [0-9]+\\)', '') 
WHERE Note LIKE '%Order from Shopify:%' 
  AND Note LIKE '%(ID: %';

-- Show summary of changes
SELECT 
    'orders' as table_name,
    COUNT(*) as updated_count
FROM orders 
WHERE note LIKE '%Order from Shopify:%' 
  AND note NOT LIKE '%(ID: %'

UNION ALL

SELECT 
    'middleware' as table_name,
    COUNT(*) as updated_count
FROM middleware 
WHERE Note LIKE '%Order from Shopify:%' 
  AND Note NOT LIKE '%(ID: %';



