Jira_Issues_to_DX_Incidents.js

onst { Client } = require('pg');
const axios = require('axios');

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

// Database connection string
const connectionString = [CONNECTION STRING FROM DX]

// Create a new PostgreSQL client
const client = new Client({
  connectionString: connectionString,
});

async function runQueryAndSendRequests() {
  try {
    // Connect to the database
    await client.connect();

    // SQL query
    const query = `
      select 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_projects jp ON jp.id = ji.project_id
  JOIN jira_priorities jpr ON jpr.id = ji.priority_id
  LEFT JOIN jira_issue_custom_field_values cf_service ON cf_service.issue_id = ji.id
  AND cf_service.custom_field_id = 15
where jp.id = 32
  AND (
    jpr.name ilike '%high%'
    OR jpr.name ilike '%Highest%'
  )
   AND cf_service IS NOT NULL
       AND ji.key NOT IN (
    SELECT source_id
    FROM incidents
  )
    `;

    // Execute the query
    const res = await client.query(query);

    // Iterate over each row in the result
    for (const row of res.rows) {
      const 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
      console.log(`Prepared payload for ${row.reference_id}:`, JSON.stringify(apiPayload, null, 2));

      if (!debug) {
        // Send POST request to the DX Incidents API
        try {
          const apiResponse = await axios.post('https://api.getdx.com/incidents', apiPayload, {
            headers: {
              Authorization: `Bearer YOUR_API_TOKEN`,  // Replace with your actual API token
              'Content-Type': 'application/json'
            }
          });

          console.log(`Successfully sent incident: ${row.reference_id}`, apiResponse.data);
        } catch (error) {
          console.error(`Error sending incident ${row.reference_id}:`, error.message);
        }
      } else {
        console.log(`Debug mode is ON. No actual API call made for ${row.reference_id}.`);
      }
    }

  } catch (err) {
    console.error('Database query error:', err.stack);
  } finally {
    // Close the database connection
    await client.end();
  }
}

// Run the function
runQueryAndSendRequests();