Groovy Incidents Import

incidents.groovy

@Grab(group='org.postgresql', module='postgresql', version='42.2.5')
@Grab(group='org.codehaus.groovy.modules.http-builder', module='http-builder', version='0.7.1')

import groovy.sql.Sql
import groovyx.net.http.RESTClient
import groovy.json.JsonOutput

// Set debug mode
def debug = true  // Set to false to make actual API calls

// Database connection string
def connectionString = '[CONNECTION STRING FROM DX]'  // Replace with actual connection string

// Create a new PostgreSQL connection
def sql = Sql.newInstance(connectionString, 'org.postgresql.Driver')

// Define the SQL query
def query = """
    SELECT DISTINCT
        ji.key AS reference_id,  
        cf_service.value as services,
        ji.summary as name,
        ji.created_at as started_at,
        ji.completed_at as finished_at,
        ji.source_url
    FROM 
        jira_issues ji
    JOIN 
        jira_issue_types jit ON ji.issue_type_id = jit.id
    JOIN 
        jira_projects jp ON ji.project_id = jp.id
    LEFT JOIN 
        jira_users ju ON ji.user_id = ju.id
    LEFT JOIN 
        jira_issue_custom_field_values cf_service ON cf_service.issue_id = ji.id 
        AND cf_service.custom_field_id = 406
    LEFT JOIN dx_users du ON LOWER(du.email) = LOWER(ju.email)
    WHERE jit.id = 1053
    AND ji.completed_at IS NOT NULL
    AND cf_service IS NOT NULL
    AND ji.key NOT IN (
        SELECT source_id
        FROM incidents
  )
    ORDER BY 
        ji.created_at DESC;
"""

// Function to run the query and send requests
def runQueryAndSendRequests() {
    try {
        sql.eachRow(query) { row ->
            // Create payload
            def apiPayload = [
                reference_id: row.reference_id,
                services: row.services ? row.services.split(',') : [], // Assuming services are comma-separated
                name: row.name,
                started_at: row.started_at,
                finished_at: row.finished_at,
                source_url: row.source_url
            ]

            // Log the payload for debugging
            println "Prepared payload for ${row.reference_id}:"
            println JsonOutput.prettyPrint(JsonOutput.toJson(apiPayload))

            if (!debug) {
                // Make POST request to DX Incidents API
                def client = new RESTClient('https://api.getdx.com/')
                def response = client.post(
                    path: 'incidents',
                    body: apiPayload,
                    requestContentType: 'application/json',
                    headers: [Authorization: 'Bearer YOUR_API_TOKEN'] // Replace with your actual API token
                )

                println "Successfully sent incident: ${row.reference_id}, Response: ${response.data}"
            } else {
                println "Debug mode is ON. No actual API call made for ${row.reference_id}."
            }
        }
    } catch (Exception e) {
        println "Error occurred: ${e.message}"
    } finally {
        // Close the database connection
        sql.close()
    }
}

// Run the function
runQueryAndSendRequests()