fetch_jira_issues_dx_incidents_api.js

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

// Toggle debug mode
const debug = true; // Set to false to send real API requests

// DX DB connection string
const connectionString = 'YOUR_DX_CONNECTION_STRING';

// DX API config
const DX_INSTANCE = 'https://YOUR_INSTANCE.getdx.net';
const DX_TOKEN = 'YOUR_API_TOKEN';

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

async function runQueryAndSendRequests() {
  try {
    await client.connect();

    const query = `
      SELECT 
        ji.key AS reference_id,
        STRING_AGG(jc.name, ', ') AS service_names,
        ji.summary as name,
        jpt.name as priority,
        COALESCE(cf_start_date.value::timestamp, ji.created_at) as started_at,
        COALESCE(cf_end_date.value::timestamp, 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 AND jp.id = 3
        JOIN jira_priorities jpt ON jpt.id = ji.priority_id
        LEFT JOIN jira_users ju ON ji.user_id = ju.id
        LEFT JOIN jira_issue_custom_field_values cf_start_date 
          ON cf_start_date.issue_id = ji.id AND cf_start_date.custom_field_id = 132
        LEFT JOIN jira_issue_custom_field_values cf_end_date 
          ON cf_end_date.issue_id = ji.id AND cf_end_date.custom_field_id = 136
        LEFT JOIN jira_component_issues cp_service ON cp_service.issue_id = ji.id
        LEFT JOIN jira_components jc ON jc.id = cp_service.component_id
        LEFT JOIN dx_users du ON LOWER(du.email) = LOWER(ju.email)
      WHERE jit.id = 22
        AND ji.completed_at IS NOT NULL
        AND ji.key NOT IN (
          SELECT source_id FROM incidents
        )
      GROUP BY 
        ji.id,
        ji.key,
        ji.summary,
        jpt.name,
        cf_start_date.value,
        cf_end_date.value,
        ji.source_url,
        ji.created_at,
        ji.completed_at
      ORDER BY ji.created_at DESC;
    `;

    const res = await client.query(query);

    for (const row of res.rows) {

      // Convert comma-separated services to DX expected object format
      const servicesArray = row.service_names
        ? row.service_names.split(',').map(name => ({
            name: name.trim(),
            identifier: name.trim().toLowerCase().replace(/\s+/g, '-')
          }))
        : [];

      const apiPayload = {
        reference_id: row.reference_id,
        name: row.name,
        priority: row.priority ? row.priority.toLowerCase() : null,
        source_url: row.source_url,
        services: servicesArray,
        started_at: new Date(row.started_at).toISOString(),
        resolved_at: row.finished_at ? new Date(row.finished_at).toISOString() : null,
        metadata: {
          source: "jira",
        }
      };

      console.log(`Prepared payload for ${row.reference_id}:`);
      console.log(JSON.stringify(apiPayload, null, 2));

      if (!debug) {
        try {
          const response = await axios.post(
            `${DX_INSTANCE}/api/incidents.upsert`,
            apiPayload,
            {
              headers: {
                Authorization: `Bearer ${DX_TOKEN}`,
                'Content-Type': 'application/json',
                'Accept': 'application/json'
              }
            }
          );

          console.log(`Successfully upserted incident: ${row.reference_id}`, response.data);
        } catch (error) {
          console.error(`Error sending incident ${row.reference_id}:`,
            error.response?.data || error.message
          );
        }
      } else {
        console.log(`Debug mode ON. No API call made.\n`);
      }
    }

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

runQueryAndSendRequests();