Deployed_at_Sha_array.mjs

import fs from 'fs';
import dotenv from 'dotenv';
import pkg from 'pg';
const { Client } = pkg;
import fetch from 'node-fetch';
import { DateTime } from 'luxon';

dotenv.config();

const DX_DB_CONNECTION = process.env.DX_DB_CONNECTION;
const DX_API_KEY = process.env.DX_API_KEY;
const DX_API_ENDPOINT = "https://<account>.getdx.net/api/deployments.create";
const VERBOSE = process.env.VERBOSE === 'true';
const MAX_RETRIES = 3;
const RETRY_DELAY = 2000; // 2 seconds between retries

// Validate required environment variables
const validateEnvVars = () => {
    const requiredVars = ["DX_DB_CONNECTION", "DX_API_KEY"];
    requiredVars.forEach((key) => {
        if (!process.env[key]) {
            console.error(`Missing required environment variable: ${key}`);
            process.exit(1);
        }
    });
};

validateEnvVars();

// Normalize PostgreSQL connection URL
const normalizePostgresURL = (url) => url.startsWith("postgres://") ? url.replace("postgres://", "postgresql://") : url;

// Convert ISO 8601 to UNIX timestamp
const isoToUnix = (isoString) => {
    return DateTime.fromISO(isoString, { zone: 'utc' }).toSeconds();
};

// Fetch script parameters
const getScriptParams = () => {
    if (process.argv.length < 4) {
        console.error("Usage: node script.mjs <deployed_at_iso> <code_freeze_iso> [patch_commit_shas]");
        process.exit(1);
    }
    return {
        deployedAtISO: new Date(process.argv[2]).toISOString(),
        deployedAtUnix: isoToUnix(process.argv[2]),
        codeFreezeISO: new Date(process.argv[3]).toISOString(),
        patchCommitShas: process.argv.length > 4 ? JSON.parse(process.argv[4]) : []
    };
};

// Query the database
const fetchPRData = async (deployedAtISO, codeFreezeISO) => {
    const client = new Client({ 
        connectionString: normalizePostgresURL(DX_DB_CONNECTION),
        ssl: { rejectUnauthorized: false } 
    });
    try {
        await client.connect();
        if (VERBOSE) console.log("Connected to the database.");
        if (VERBOSE) console.log("Query parameters:", { deployedAtISO, codeFreezeISO });

        // Explicitly cast both parameters as TIMESTAMP in the query
        const query = `
            SELECT 
                (SELECT merge_commit_sha
                 FROM pull_requests
                 JOIN repos ON repos.id = pull_requests.repo_id
                 AND repos.api_accessible = 't'
                 WHERE merged < CAST($1 AS TIMESTAMP)
                 AND pull_requests.bot_authored IS NOT TRUE
                 AND pull_requests.dx_user_id IN (
                     SELECT dxu.id
                     FROM dx_teams t
                     JOIN dx_team_hierarchies h ON t.id = h.ancestor_id
                     JOIN dx_users dxu ON h.descendant_id = dxu.team_id
                     WHERE t.source_id IN ('NjAwNDI')
                 )
                 AND pull_requests.draft IS NOT TRUE
                 ORDER BY merged DESC
                 LIMIT 1) AS reference_id,
                (SELECT array_agg(merge_commit_sha)
                 FROM pull_requests
                 JOIN repos ON repos.id = pull_requests.repo_id
                 AND repos.api_accessible = 't'
                 WHERE merged BETWEEN (
                     SELECT deployed_at
                     FROM deployments
                     WHERE service ILIKE '%name%'
                     ORDER BY deployed_at DESC
                     LIMIT 1
                 ) AND CAST($2 AS TIMESTAMP)
                 AND pull_requests.bot_authored IS NOT TRUE
                 AND pull_requests.dx_user_id IN (
                     SELECT dxu.id
                     FROM dx_teams t
                     JOIN dx_team_hierarchies h ON t.id = h.ancestor_id
                     JOIN dx_users dxu ON h.descendant_id = dxu.team_id
                     WHERE t.source_id IN ('NjAwNDI')
                 )
                 AND pull_requests.id NOT IN (SELECT pull_request_id FROM pull_request_deployments)
                 AND pull_requests.draft IS NOT TRUE) AS merge_request_sha_array;
        `;

        const formattedDeployedAtISO = new Date(deployedAtISO).toISOString().replace("T", " ").replace("Z", "");
        const formattedCodeFreezeISO = new Date(codeFreezeISO).toISOString().replace("T", " ").replace("Z", "");

        if (VERBOSE) console.log("Executing query with formatted timestamps:", formattedDeployedAtISO, formattedCodeFreezeISO);

        const result = await client.query(query, [formattedDeployedAtISO, formattedCodeFreezeISO]);
        if (VERBOSE) console.log("Query executed successfully.", result.rows);
        
        return result.rows.length > 0 
            ? { referenceId: result.rows[0].reference_id, mergeRequestShaArray: result.rows[0].merge_request_sha_array || [] } 
            : { referenceId: null, mergeRequestShaArray: [] };
    } catch (error) {
        console.error("Database query failed:", error);
        return { referenceId: null, mergeRequestShaArray: [] };
    } finally {
        await client.end();
        if (VERBOSE) console.log("Database connection closed.");
    }
};

// Send the deployment data to DX API
const sendDeploymentData = async (payload) => {
    for (let attempt = 1; attempt <= MAX_RETRIES; attempt++) {
        try {
            const response = await fetch(DX_API_ENDPOINT, {
                method: "POST",
                headers: {
                    "Content-Type": "application/json",
                    "Authorization": `Bearer ${DX_API_KEY}`
                },
                body: JSON.stringify(payload)
            });

            if (!response.ok) {
                throw new Error(`HTTP error! Status: ${response.status}`);
            }

            const data = await response.json();
            console.log("Successfully sent deployment data to DX:", data);
            return;
        } catch (error) {
            console.error(`Attempt ${attempt} failed:`, error);
            if (attempt < MAX_RETRIES) {
                console.log(`Retrying in ${RETRY_DELAY / 1000} seconds...`);
                await new Promise(res => setTimeout(res, RETRY_DELAY));
            } else {
                console.error("Max retries reached. Failed to send deployment data.");
            }
        }
    }
};

const main = async () => {
    try {
        const { deployedAtISO, deployedAtUnix, codeFreezeISO, patchCommitShas } = getScriptParams();
        if (VERBOSE) console.log("Deployed timestamp:", deployedAtISO, "(Unix:", deployedAtUnix,") Code freeze timestamp:", codeFreezeISO, "Patch commit SHAs:", patchCommitShas);

        const { referenceId, mergeRequestShaArray } = await fetchPRData(deployedAtISO, codeFreezeISO);
        
        if (referenceId) {
            const payload = {
                deployed_at: deployedAtUnix,
                reference_id: referenceId,
                service: "ChiroTouch Cloud",
                merge_commit_shas: [...mergeRequestShaArray, ...patchCommitShas]
            };

            console.log("Sending payload to DX...");
            await sendDeploymentData(payload); // Now actually sends the request
        } else {
            console.log("No matching pull request data found.");
        }
    } catch (error) {
        console.error("Unexpected error in main execution:", error);
    }
};

main().catch(console.error);