-- Update WorkCOM Payment Verification workflow to load registration details

-- Shift existing steps to make room for new ones
UPDATE workflow_steps
SET step_order = step_order + 2
WHERE workflow_id = (SELECT id FROM workflows WHERE name='WorkCOM Payment Verification')
  AND NOT EXISTS (
    SELECT 1 FROM workflow_steps ws2
    WHERE ws2.workflow_id = (SELECT id FROM workflows WHERE name='WorkCOM Payment Verification')
      AND ws2.step_order >= 7
  );

-- Step 1: load workplace registration
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT w.id, 1, 'query_records', '{"table":"workplace_registrations","where":{"workplace_id":"{workplace_id}"},"context_key":"registration"}'
FROM workflows w
WHERE w.name='WorkCOM Payment Verification'
  AND NOT EXISTS (
    SELECT 1 FROM workflow_steps ws
    WHERE ws.workflow_id=w.id AND ws.step_order=1 AND ws.step_type='query_records'
      AND ws.config->>'table'='workplace_registrations'
  );

-- Step 2: map registration fields
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT w.id, 2, 'map_fields', '{"map":{"district_id":"registration.0.district_id","company_name":"registration.0.company_name","contact_person":"registration.0.contact_person"}}'
FROM workflows w
WHERE w.name='WorkCOM Payment Verification'
  AND NOT EXISTS (
    SELECT 1 FROM workflow_steps ws
    WHERE ws.workflow_id=w.id AND ws.step_order=2 AND ws.step_type='map_fields'
      AND ws.config->'map'->>'district_id'='registration.0.district_id'
  );

-- Shift steps to make room for inspection record save
UPDATE workflow_steps
SET step_order = step_order + 1
WHERE workflow_id = (SELECT id FROM workflows WHERE name='WorkCOM Payment Verification')
  AND step_order >= 5
  AND NOT EXISTS (
    SELECT 1 FROM workflow_steps ws2
    WHERE ws2.workflow_id = (SELECT id FROM workflows WHERE name='WorkCOM Payment Verification')
      AND ws2.step_order = 7
  );

-- Step 5: create inspection record
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT w.id, 5, 'save_record', '{"table":"workplace_inspections","data":{"inspector_id":"{assignee_id}","workplace_id":"{workplace_id}","date":"{due_date}"}}'
FROM workflows w
WHERE w.name='WorkCOM Payment Verification'
  AND NOT EXISTS (
    SELECT 1 FROM workflow_steps ws
    WHERE ws.workflow_id=w.id AND ws.step_order=5 AND ws.step_type='save_record'
  );

-- Step 4: add assignee mapping
UPDATE workflow_steps
SET config = jsonb_set(config::jsonb, '{map,assignee_id}', config::jsonb->'map'->'inspector_id')
WHERE workflow_id = (SELECT id FROM workflows WHERE name='WorkCOM Payment Verification')
  AND step_order = 4
  AND step_type = 'map_fields'
  AND NOT (config::jsonb->'map' ? 'assignee_id');

-- Step 6: ensure kanban task uses assignee_id
UPDATE workflow_steps
SET config = jsonb_set(config::jsonb, '{assignee_id}', '"{assignee_id}"'::jsonb, false)
WHERE workflow_id = (SELECT id FROM workflows WHERE name='WorkCOM Payment Verification')
  AND step_order = 6
  AND step_type = 'create_task';

-- Step 7: ensure calendar event uses assignee_id
UPDATE workflow_steps
SET config = jsonb_set(config::jsonb, '{user_id}', '"{assignee_id}"'::jsonb, false)
WHERE workflow_id = (SELECT id FROM workflows WHERE name='WorkCOM Payment Verification')
  AND step_order = 7
  AND step_type = 'add_calendar_event';
