-- Update Workplace Registration Review workflow to link invoice
-- Shift existing steps to make room for invoice update and notification
UPDATE workflow_steps
SET step_order = step_order + 2
WHERE workflow_id = (SELECT id FROM workflows WHERE name='Workplace Registration Review')
  AND step_order >= 4;

-- Save invoice_id to registration after invoice creation
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT w.id, 4, 'save_record', '{"table":"workplace_registrations","data":{"invoice_id":"{invoice_id}"},"match":{"id":"{id}"}}'
FROM workflows w
WHERE w.name='Workplace Registration Review'
  AND NOT EXISTS (
    SELECT 1 FROM workflow_steps ws
    WHERE ws.workflow_id = w.id AND ws.step_order = 4 AND ws.step_type='save_record'
  );

-- Queue update notification for registration
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT w.id, 5, 'queue_notification', '{"entity":"workplace_registrations","record_id":"{id}","event_type":"update","payload":{"invoice_id":"{invoice_id}"}}'
FROM workflows w
WHERE w.name='Workplace Registration Review'
  AND NOT EXISTS (
    SELECT 1 FROM workflow_steps ws
    WHERE ws.workflow_id = w.id AND ws.step_order = 5 AND ws.step_type='queue_notification'
  );

