-- Add payment form step after invoice creation in workflows
-- Insert open_form step using Case Payment form (id 50)

-- Shift subsequent steps to make room for the new step
WITH ci AS (
    SELECT workflow_id, step_order
    FROM workflow_steps
    WHERE step_type = 'create_invoice'
), moved AS (
    UPDATE workflow_steps ws
    SET step_order = step_order + 1
    FROM ci
    WHERE ws.workflow_id = ci.workflow_id
      AND ws.step_order > ci.step_order
      AND NOT EXISTS (
          SELECT 1 FROM workflow_steps wf
          WHERE wf.workflow_id = ci.workflow_id
            AND wf.step_type = 'open_form'
            AND wf.step_order = ci.step_order + 1
      )
    RETURNING ws.workflow_id
)
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT ci.workflow_id, ci.step_order + 1, 'open_form', '{"form_id":50}'
FROM ci
WHERE NOT EXISTS (
    SELECT 1 FROM workflow_steps wf
    WHERE wf.workflow_id = ci.workflow_id
      AND wf.step_type = 'open_form'
      AND wf.step_order = ci.step_order + 1
);
