-- Create workflow to generate workplace registration certificates
INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES ('Workplace Registration Certificate', 'workplace.registration.certificate', '{"conditions": []}', TRUE)
ON CONFLICT (name) DO NOTHING;

-- Step 1: create certificate
WITH wf AS (
  SELECT id FROM workflows WHERE name='Workplace Registration Certificate'
)
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT wf.id, 1, 'create_certificate',
       json_build_object(
         'template_id', (SELECT id FROM certificate_templates WHERE name='Basic Certificate' LIMIT 1),
         'submission_id_key', 'id',
         'status', 'issued'
       )
FROM wf
WHERE NOT EXISTS (
  SELECT 1 FROM workflow_steps ws
  WHERE ws.workflow_id = wf.id AND ws.step_order = 1 AND ws.step_type='create_certificate'
);

UPDATE workflow_steps ws
SET config = json_build_object(
  'template_id', (SELECT id FROM certificate_templates WHERE name='Basic Certificate' LIMIT 1),
  'submission_id_key', 'id',
  'status', 'issued'
)
WHERE ws.workflow_id = (SELECT id FROM workflows WHERE name='Workplace Registration Certificate')
  AND ws.step_order = 1
  AND ws.step_type = 'create_certificate';

-- Step 2: generate certificate document
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT w.id, 2, 'generate_certificate_document', '{}'
FROM workflows w
WHERE w.name='Workplace Registration Certificate'
  AND NOT EXISTS (
    SELECT 1 FROM workflow_steps ws
    WHERE ws.workflow_id = w.id AND ws.step_order = 2 AND ws.step_type='generate_certificate_document'
  );

-- Step 3: queue notification
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT w.id, 3, 'queue_notification', '{"entity":"form_certificates","record_id":"{certificate_id}","event_type":"update"}'
FROM workflows w
WHERE w.name='Workplace Registration Certificate'
  AND NOT EXISTS (
    SELECT 1 FROM workflow_steps ws
    WHERE ws.workflow_id = w.id AND ws.step_order = 3 AND ws.step_type='queue_notification'
  );

-- Step 4: open Certificates modal
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT w.id, 4, 'open_modal', '{"modal":"Certificates"}'
FROM workflows w
WHERE w.name='Workplace Registration Certificate'
  AND NOT EXISTS (
    SELECT 1 FROM workflow_steps ws
    WHERE ws.workflow_id = w.id AND ws.step_order = 4 AND ws.step_type='open_modal'
      AND ws.config->>'modal'='Certificates'
  );

-- Event trigger for certificate workflow
INSERT INTO workflow_event_triggers (event_name, table_name, trigger_type, operations, field_map)
SELECT 'workplace.registration.certificate', 'workplace_registrations', 'update', '["update"]', '{"id":"id"}'
WHERE NOT EXISTS (
  SELECT 1 FROM workflow_event_triggers WHERE event_name='workplace.registration.certificate'
);

-- Remove certificate steps from Workplace Registration Review workflow if present
DELETE FROM workflow_steps
WHERE workflow_id = (SELECT id FROM workflows WHERE name='Workplace Registration Review' LIMIT 1)
  AND step_type IN ('create_certificate','generate_certificate_document');
