-- Add email notifications for Workplace Registration workflow

-- Ensure contact_email is captured when the record is created
UPDATE workflow_event_triggers
SET field_map = '{"id":"id","assigned_reviewer_id":"assigned_reviewer_id","contact_email":"contact_email"}'
WHERE event_name='workplace.registration.created'
  AND table_name='workplace_registrations';

-- Reorder existing steps
UPDATE workflow_steps SET step_order = 3
WHERE workflow_id = (SELECT id FROM workflows WHERE name='Workplace Registration Review')
  AND step_type='save_record';
UPDATE workflow_steps SET step_order = 5
WHERE workflow_id = (SELECT id FROM workflows WHERE name='Workplace Registration Review')
  AND step_type='create_certificate';
UPDATE workflow_steps SET step_order = 6
WHERE workflow_id = (SELECT id FROM workflows WHERE name='Workplace Registration Review')
  AND step_type='generate_certificate_document';
UPDATE workflow_steps SET step_order = 7
WHERE workflow_id = (SELECT id FROM workflows WHERE name='Workplace Registration Review')
  AND step_type='create_invoice';
UPDATE workflow_steps SET step_order = 8
WHERE workflow_id = (SELECT id FROM workflows WHERE name='Workplace Registration Review')
  AND step_type='open_modal' AND config->>'modal'='Workplace Invoice Preview';
UPDATE workflow_steps SET step_order = 9
WHERE workflow_id = (SELECT id FROM workflows WHERE name='Workplace Registration Review')
  AND step_type='open_modal' AND config->>'modal'='Workplace Notice Preview';

-- Reviewer notification
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT w.id, 2, 'email', '{"to":"{reviewer_email}","message":"A new workplace registration requires review."}'
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=2 AND ws.step_type='email'
  );

-- Thank you email to registrant
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT w.id, 4, 'email', '{"to":"{contact_email}","message":"Thank you for registering your workplace."}'
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='email'
  );
