index.js

import dotenv from 'dotenv' 
dotenv.config()
import sql from 'mssql'
import * as msal from "@azure/msal-node"


//CONSTANTS:
const CMS_DB = process.env.CMS_DB;
const CMS_USER = process.env.CMS_USER;
const CMS_PSWD = process.env.CMS_PSWD;
const CMS_SERVER = process.env.CMS_SERVER;

const PA_URL = process.env.PA_URL;
const DV_CLIENT_ID = process.env.DV_CLIENT_ID;
const DV_CLIENT_SECRET = process.env.DV_CLIENT_SECRET;
const DV_AUTHORITY = process.env.DV_AUTHORITY;

const cms_config = {
    user: CMS_USER,
    password: CMS_PSWD,
    server: CMS_SERVER,
    database: CMS_DB,
    options: {
        trustServerCertificate: true
    }
}

const CMS_QUERY = 'SELECT submissionid, MAX(modifiedon) AS modifiedon FROM dbo.credential_submissions WHERE conventionid = 79 GROUP BY submissionid';

const query_credential_submissions = async(config = cms_config) => {
       try{
           const pool = await sql.connect(config);
           const result = await pool.request().query(CMS_QUERY);
           return result.recordset;
       } catch(err) {
           console.error(`CMS database query error: `, err);
           throw err;
       } finally {
           await sql.close()
       }
}

const dv_config = {
    auth: {
        "clientId": DV_CLIENT_ID,
        "clientSecret": DV_CLIENT_SECRET,
        "authority": DV_AUTHORITY
    }
}

const connectToDV = async(dataverseCreds=dv_config, dataverseURL=PA_URL) => {
    const app = new msal.ConfidentialClientApplication(dataverseCreds);

    const request = {
        scopes: [`${dataverseURL}/.default`],
        skipCache: true
    }

    const token = await app.acquireTokenByClientCredential(request);

    if(!token?.accessToken){
        throw new Error('Failed to acquire DV Access Token')
    }

    const apiURL = `${dataverseURL}/api/data/v9.2`;
    const headers = {
        'Accept': 'application/json',
        'OData-MaxVersion': '4.0',
        'OData-Version': '4.0',
        'If-None-Match': '',
        'Authorization': `Bearer ${token.accessToken}`,
        //'Prefer': 'odata.include-annotations="OData.Community.Display.V1.FormattedValue"'
    }

    return {apiURL, headers}
}

const dvConn = await connectToDV();

const dvQueries = () => {
    const tables = [
        {table: 'cr8b8_councilsubmissions', idProp: 'cr8b8_councilsubmissionid'},
        {table: 'cr8b8_retireesubmissions', idProp: 'cr8b8_retireesubmissionid'},
        {table: 'cr8b8_delegatesubmissions', idProp: 'cr8b8_delegatesubmissionid'}
    ]

    const submissionTableQueries = {}

    tables.forEach(t => {
        const select = `modifiedon, ${t.idProp}`;
        const filter = `(cr8b8_status eq 2 or cr8b8_status eq 3) and cr8b8_conventionnumber eq '79'`;
        const orderBy = `modifiedon asc, ${t.idProp} asc`

        const encodedSelect = encodeURIComponent(select);
        const encodedFilter = encodeURIComponent(filter);
        const encodedOrderBy = encodeURIComponent(orderBy);

        
        submissionTableQueries[t.table] = `${t.table}?$select=${encodedSelect}&$filter=${encodedFilter}&$orderby=${encodedOrderBy}`
    })

    return submissionTableQueries;
}

const dvTableQueries = dvQueries();

const queryDataVerse = async(conn=dvConn, queries=dvTableQueries) => {
    const {apiURL, headers} = dvConn;

    const updatedHeaders = {
        ...headers,
        'Prefer': `${headers['Prefer']}, odata.maxpagesize=500`
    }

    let allRecords = []
    
    const queryStrings = Object.values(queries);

    for (const q of queryStrings){
        let url = `${apiURL}/${q}`;

        while(url){
            const response = await fetch(url, {headers: updatedHeaders});

            if(!response.ok){
                const errorData = await response.json().catch(() => ({}));
                console.error(`Dataverse error details: ${JSON.stringify(errorData, null, 2)}`)
                throw new Error(`DV API request failed with status ${response.status}`)
            }

            const data = await response.json();

            if(data.value && data.value.length > 0){
                const mappedRows = data.value.map(row => {
                    return {
                        modifiedon : row.modifiedon,
                        submissionid : row.cr8b8_delegatesubmissionid ??
                                       row.cr8b8_councilsubmissionid ??
                                       row.cr8b8_retireesubmissionid
                    }
                })

                allRecords.push(...mappedRows)
            }

            url = data["@odata.nextLink"] || null;
        }
    }

    return allRecords
}

(async() => {
    const dvFetch = queryDataVerse();
    const cmsFetch = query_credential_submissions();

    try {
        console.log("Initiating db fetches...");

        const [dvResult, cmsResult] = await Promise.all([
            dvFetch,
            cmsFetch
        ]);

        if((dvResult && dvResult.length !== 0) && (cmsResult && cmsResult.length !== 0)){
            const dvIdsSet = new Set(dvResult.map(it => it.submissionid?.toLowerCase()));
            const cmsIdsSet = new Set(cmsResult.map(it => it.submissionid?.toLowerCase()));

            const dvMissingInCMS = dvResult.filter(it => !cmsIdsSet.has(it.submissionid?.toLowerCase()));
            const cmsMissingInDV = cmsResult.filter(it => !dvIdsSet.has(it.submissionid?.toLowerCase()));
            console.log(`Dataverse submissions missing in CMS: ${JSON.stringify(dvMissingInCMS, null, 2)}`)
           
            //update the credential_submissions_deleted_ids with values that no longer exist in dv
            const submissionIdsDeletedFromDV = cmsMissingInDV.map(it => it.submissionid?.toUpperCase());

            if(submissionIdsDeletedFromDV  && submissionIdsDeletedFromDV .length > 0){
                try{
                    const conn = await sql.connect(cms_config);

                    const valueRows = submissionIdsDeletedFromDV .map(id => `('${id}')`).join(', ')

                    const updateQuery = `MERGE dbo.credential_submissions_deleted_ids AS target
                                         USING (VALUES ${valueRows}) AS source (submissionid)
                                         ON (target.submissionid = source.submissionid)
                                         WHEN NOT MATCHED THEN
                                            INSERT (submissionid) VALUES (source.submissionid);
                    `

                    await conn.request().query(updateQuery);
                    console.log(`Successfully added deleted ids to credential_submissions_deleted_ids`)
                } catch(dbErr){
                    console.error(`Failed to updated deleted IDs table: ${dbErr}`)
                } finally {
                    await sql.close()
                }
            }
        
            
        } else {
            console.log("One or both datasets returned empty results:")
        }
    } catch (err) {
        console.error(`One of the db ops failed: ${err}`)
    }
})();