-- Insert workflow for WorkCOM payment verification
INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES ('WorkCOM Payment Verification', 'invoice.payment.verified', '{"conditions": []}', TRUE)
ON CONFLICT (name) DO NOTHING;

-- 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":{"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
  );


-- 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","company_name":"registration.0.company_name","contact_person":"registration.0.contact_person","contact_email":"registration.0.contact_email","contact_phone":"registration.0.contact_phone","contact_telephone_number":"registration.0.contact_telephone_number","branch_id":"registration.0.branch_id","tenant_id":"registration.0.tenant_id"}}'

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
  );

-- Step 3: find inspector assignment rules
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT w.id, 3, 'query_records', '{"table":"inspection_assignment_rules","where":{"district_id":"{district_id}"},"context_key":"rules"}'
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=3
  );

-- Step 4: map inspector id
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT w.id, 4, 'map_fields', '{"map":{"inspector_id":"rules.0.inspector_id","assignee_id":"rules.0.inspector_id"}}'
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=4
  );

-- 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
  );

-- Step 6: create kanban task
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT w.id, 6, 'create_task',
       json_build_object(
         'column_id', kc.id,
         'title', 'Workplace Inspection - {company_name}',
         'description', 'Inspection for {company_name}',
         'assignee_id', '{assignee_id}',
         'due_date', '{due_date}'
       )
FROM workflows w
CROSS JOIN LATERAL (
  SELECT kc.id
  FROM kanban_columns kc
  JOIN kanban_boards kb ON kc.board_id = kb.id
  WHERE kb.name='WorkCOM Inspections' AND kc.title='Pending'
  LIMIT 1
) kc
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=6
  );

-- Step 7: add calendar event
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT w.id, 7, 'add_calendar_event',
       json_build_object(
         'title', 'Workplace Inspection',
         'start_time', '{due_date}',
         'category_id', kb.calendar_category_id,
         'user_id', '{assignee_id}'
       )
FROM workflows w
CROSS JOIN LATERAL (
  SELECT kb.calendar_category_id
  FROM kanban_boards kb
  WHERE kb.name='WorkCOM Inspections'
  LIMIT 1
) kb
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=7
  );
