openfn-e5d0c9ec-bad5-48c8-add9-970b3e248d82-spec.yaml

name: social-sciences
description: |
  Data integration workflows for WCS social sciences data. Used to transfer BNS & NRGT data from KoboToolbox forms to the WCS Programs MS SQL database.

collections:
  mydata:
    name: mydata
credentials:
  aisha@openfn.org-AK-Asana:
    name: AK Asana
    owner: aisha@openfn.org
  aleksa@openfn.org-Asana-API-Token---AK-WCS-Grievances:
    name: Asana API Token - AK WCS Grievances
    owner: aleksa@openfn.org
  ddetoeuf@wcs.org-WCS-Kobo-account:
    name: WCS Kobo account
    owner: ddetoeuf@wcs.org
  rediet@openfn.org-Invalid-WCS-MSSQL-Test-DB:
    name: Invalid WCS MSSQL Test DB
    owner: rediet@openfn.org
  rediet@openfn.org-Rediet-Googlesheet:
    name: Rediet Googlesheet
    owner: rediet@openfn.org
  rediet@openfn.org-WCS-Kobo:
    name: WCS Kobo
    owner: rediet@openfn.org
  rediet@openfn.org-WCS-Kobo-Raw:
    name: WCS Kobo Raw
    owner: rediet@openfn.org
  rediet@openfn.org-WCS-Kobo-Raw-with-URL:
    name: WCS Kobo Raw with URL
    owner: rediet@openfn.org
  rediet@openfn.org-WCS-MSSQL-Test-DB:
    name: WCS MSSQL Test DB
    owner: rediet@openfn.org
workflows:
  "1.1-Get-BNS-FormsList-(Ongoing)":
    name: '1.1 Get BNS FormsList (Ongoing)'
    jobs:
      Get-Forms-List:
        name: Get Forms List
        adaptor: '@openfn/language-googlesheets@2.3.0'
        credential: rediet@openfn.org-Rediet-Googlesheet
        body: |
          //== Job to be used for getting a list of "deployed" Kobo forms from sheets to auto-sync  ==//
          // This can be run on-demand at any time by clicking "run" or modify manualCursor below //
          getValues(
             '1xexwj6HKJGHJM-sOzIOGqPHolgpdnp0GmjMIXwoV7OE',
            'wcs-bns-DEPLOYED!A:L', //get Deployed forms list from Sheet
            state => {
              const [headers, ...values] = state.data.values;

              const mapHeaderToValue = value => {
                return headers.reduce((obj, header) => {
                  obj[header] = value[headers.indexOf(header)];
                  return obj;
                }, {});
              };

              state.sheetsData = values
                .filter(
                  item =>
                    item.includes('TRUE') //return forms where auto-sync = TRUE
                    //&& item.includes('bns_survey', 'nrgt_current') 
                )
                .map(item => mapHeaderToValue(item));

              return state;
            }
          );

          fn(state => {
            const { sheetsData } = state;

            // Set a manual cursor if you'd like to only fetch data after this date...
            //e.g., '2023-01-01T23:51:45.491+01:00'
            const manualCursor = '2024-12-06T00:00:00.000Z';  //lastUsed: 2024-11-21T00:00:00.000Z
            console.log('manualCursor defined?', manualCursor);
            
            //...otherwise the job will use this dynamicCursor
            const dynamicCursor = getTodayISODate(); 

            function getTodayISODate() {
              const today = new Date();
              today.setUTCHours(0, 0, 0, 0); // Set hours, minutes, seconds, and milliseconds to 0
              return today.toISOString(); // Convert to ISO string
            }
            
            //UNCOMMENT FOR FUTURE
           // const cursorValue = dynamicCursor || manualCursor ; 
            const cursorValue = manualCursor ; 
           
            // const cursorValue = manualCursor ; 
            console.log('Cursor value to use in query:', cursorValue);

            const formsList = sheetsData.map(survey => ({
                formId: survey.uid,
                tag: survey.tag,
                name: survey.name 
            })); 
            
            console.log('# of deployed forms detected in Sheet:: ', formsList.length);
            console.log('List of forms to auto-sync:: ', JSON.stringify(formsList, null, 2)); 

            state.data = {
              surveys: sheetsData.map(survey => ({
                formId: survey.uid,
                tag: survey.tag,
                name: survey.name,
                owner: survey.owner,
                url: `https://kf.kobotoolbox.org/api/v2/assets/${survey.uid}/data/?format=json`,
                query: `&query={"start":{"$gte":"${cursorValue}"}}`,
              })),
            };
            return state;
          });

      Get-BNS-NRGT-Forms:
        name: Get BNS NRGT Forms
        adaptor: '@openfn/language-http@3.1.11'
        credential: ddetoeuf@wcs.org-WCS-Kobo-account
        body: |
          // Here we fetch submissions for all "Deployed" forms in GoogleSheet
          // NOTE: See linked job "[BNS-1A] 1.Get FormsList (Ongoing)" for cursor & GoogleSheet query logic
          //**********************************************************//

          fn(state => {
            state.surveySubmissions = [];
            state.errors = [];
            state.globalIndex = 0;
            console.log('surveys ::', JSON.stringify(state.data.surveys, null, 2));
            return state;
          });

          each('$.data.surveys[*]', state => {
            const { url, query, tag, formId, name, owner } = state.data;
            console.log('Sending GET to ::', `${url}${query}`);

            return get(`${url}${query}`)(state)
              .then(state => {
                const results = state.data.results.map(submission => {
                  const uniqueIndex = state.globalIndex++;
                  return {
                    i: uniqueIndex,
                    // Here we append the tags defined above to the Kobo form submission data
                    form: tag,
                    formName: name,
                    formOwner: owner,
                    body: submission,
                  };
                });

                state.surveySubmissions.push(...results);
                const count = results.length;
                console.log(`Fetched ${count} submissions from ${formId} (${tag}).`);
                //Once we fetch the data, we want to post each individual Kobo survey
                //back to the OpenFn inbox to run through the jobs =========================
                return state;
              })
              .catch(err => {
                state.errors.push({
                  formId,
                  message: err.message,
                });
                console.log(`Error fetching submissions from  ${formId}::`, err.message);
                return state;
              });
          });

          each(
            '$.surveySubmissions[*]',
            post(
              "https://app.openfn.org/i/b3f86593-f37e-4139-80b8-852b9d3c49f4",
              state => {
                const {i, formName} = state.data;
                const count = state.surveySubmissions.length;
                console.log(`Posting ${i} of ${count} from ${formName}`);
                return { body: state.data };
              }
            )
          );  

    triggers:
      cron:
        type: cron
        cron_expression: '0 */3 * * *'
        enabled: false
    edges:
      cron->Get-Forms-List:
        source_trigger: cron
        target_job: Get-Forms-List
        condition_type: always
        enabled: true
      Get-Forms-List->Get-BNS-NRGT-Forms:
        source_job: Get-Forms-List
        target_job: Get-BNS-NRGT-Forms
        condition_type: on_job_success
        enabled: true
  "1.2-Get-BNS-FormsList-(Historical)":
    name: '1.2 Get BNS FormsList (Historical)'
    jobs:
      Get-FormsList:
        name: Get FormsList
        adaptor: '@openfn/language-googlesheets@2.3.0'
        credential: rediet@openfn.org-Rediet-Googlesheet
        body: |
          getValues(
            '1s7K3kxzm5AlpwiALattyc7D9_aIyqWmo2ubcQIUlqlY',
            'wcs-bns-DEPLOYED!A:O', //get Deployed forms list from Sheet
            state => {
              const [headers, ...values] = state.data.values;

              const mapHeaderToValue = value => {
                return headers.reduce((obj, header) => {
                  obj[header] = value[headers.indexOf(header)];
                  return obj;
                }, {});
              };
          console.log("deployedData", values);

              state.deployedData = values
                .map(item => mapHeaderToValue(item))
                .filter(item => item['historical_sync'] === 'TRUE');
              return state;
            }
          );
          //== Job to be used for getting a list of "archived" Kobo forms from sheets to auto-sync  ==//
          // This can be run on-demand at any time by clicking "run" //
          getValues(
            '1s7K3kxzm5AlpwiALattyc7D9_aIyqWmo2ubcQIUlqlY',
            'wcs-bns-ARCHIVED!A:O', //get Deployed forms list from Sheet
            state => {
              const [headers, ...values] = state.data.values;

              const mapHeaderToValue = value => {
                return headers.reduce((obj, header) => {
                  obj[header] = value[headers.indexOf(header)];
                  return obj;
                }, {});
              };
          console.log("archivedData", values);


              state.archivedData = values
                .map(item => mapHeaderToValue(item))
                .filter(item => item['historical_sync'] === 'TRUE');

              return state;
            }
          );

          fn(state => {
            const { archivedData, deployedData } = state;

            // Set a manual cursor if you'd like to only fetch data after this date...
            //e.g., '2023-01-01T23:51:45.491+01:00'
            //   const manualCursor = ''; //lastUsed: 2024-04-01T00:00:00.000Z
            //   console.log('manualCursor defined?', manualCursor);
            //...otherwise the job will use this dynamicCursor
            // const dynamicCursor = getTodayISODate();

            //   function getTodayISODate() {
            //     const today = new Date();
            //     today.setUTCHours(0, 0, 0, 0); // Set hours, minutes, seconds, and milliseconds to 0
            //     return today.toISOString(); // Convert to ISO string
            //   }

            //   const cursorValue = manualCursor || dynamicCursor;
            //   console.log('Cursor value to use in query:', cursorValue);
            const combinedData = [...deployedData, ...archivedData];
            const formsList = combinedData.map(survey => ({
              formId: survey.uid,
              tag: survey.tag,
              name: survey.name,
            }));

            console.log('# of forms detected in Sheet:: ', formsList.length);
            console.log(
              'List of forms to re-sync:: ',
              JSON.stringify(formsList, null, 2)
            );

            state.data = {
              surveys: combinedData.map(survey => ({
                formId: survey.uid,
                tag: survey.tag,
                name: survey.name,
                owner: survey.owner,
                url: `https://kf.kobotoolbox.org/api/v2/assets/${survey.uid}/data/?format=json`,
                //query: `&query={"end":{"$gte":"${cursorValue}"}}`, //get ALL forms for historical job
              })),
            };
            return state;
          });

          //Clear final state
          fn(state => {
            delete state.references;
            delete state.response;
            return state;
          });     

      Get-Forms:
        name: Get Forms
        adaptor: '@openfn/language-http@3.1.11'
        credential: ddetoeuf@wcs.org-WCS-Kobo-account
        body: |
          // Here we fetch submissions for all "Archived" forms in GoogleSheet
          // NOTE: See linked job "[BNS-1B] 1.Get FormsList (Historical)" for GoogleSheet query logic
          //**********************************************************//
          each(dataPath('surveys[*]'), state => {
            const { url, tag, formId, name, owner } = state.data;
            const query = "&query={\"start\":{\"$gte\":\"2021-10-29T00:00:00.000Z\"}}"
            return get(`${url}${query}`, {}, state => {
              console.log(state.data.result)
              state.data.submissions = state.data.results.map((submission, i) => {
                return {
                  i,
                  // Here we append the tags defined above to the Kobo form submission data
                  form: tag,
                  formName: name,
                  formOwner: owner,
                  body: submission,
                };
              });
              const count = state.data.submissions.length;
              console.log('Finding historical forms to resync...');
              console.log(`Fetched ${count} submissions from ${formId} (${tag}).`);
              //Once we fetch the data, we want to post each individual Kobo survey
              //back to the OpenFn inbox to run through the jobs =========================
              return each(dataPath('submissions[*]'), state => {
                console.log(`Posting ${state.data.i + 1} of ${count}...`);
                return post("https://app.openfn.org/i/b3f86593-f37e-4139-80b8-852b9d3c49f4", {
                  body: state => state.data,
                })(state);
              })(state);
            })(state)
          });

    triggers:
      webhook:
        type: webhook
        enabled: false
    edges:
      Get-FormsList->Get-Forms:
        source_job: Get-FormsList
        target_job: Get-Forms
        condition_type: on_job_success
        enabled: true
      webhook->Get-FormsList:
        source_trigger: webhook
        target_job: Get-FormsList
        condition_type: always
        enabled: true
  2.-Sync-BNS-and-NRGT-Forms:
    name: 2. Sync BNS and NRGT Forms
    jobs:
      Triage-jobs:
        name: Triage jobs
        adaptor: '@openfn/language-common@2.3.1'
        credential: rediet@openfn.org-WCS-MSSQL-Test-DB
        body: |
          // Check out the Job Writing Guide for help getting started:
          // https://docs.openfn.org/documentation/jobs/job-writing-guide

      BNS-2A-BNS-Price:
        name: BNS-2A BNS Price
        adaptor: '@openfn/language-mssql@3.0.0'
        credential: rediet@openfn.org-WCS-MSSQL-Test-DB
        body: |
          // NOTE: This data cleaning operation returns state, modified as needed.
          fn(state => {
            //try {
            const { body, formName, instance, formOwner } = state.data;
            const { _submission_time, _id, _xform_id_string } = body;
            let cleanedSubmission = {};

            for (const key in body) {
              switch (body[key]) {
                case 'yes':
                  cleanedSubmission[key] = 1;
                  break;

                case 'no':
                  cleanedSubmission[key] = 0;
                  break;

                default:
                  cleanedSubmission[key] = body[key];
                  break;
              }
            }

            cleanedSubmission.instance = instance;

            const landscapeMap = {
              Ndoki: 'ndoki',
              'Lac Télé': 'lac_tele',
              Ituri: 'ituri',
              Kahuzi: 'kahuzi',
              MTKB: 'kahuzi',
              'Cross River': 'crossriver',
              Soariake: 'soariake',
              Ankarea: 'ankarea',
              ABS: 'baie_antongil',
              'Nosy Be': 'tandavandriva',
              Makira: 'makira',
              'BNS Ndoki Prix 2020': 'ndoki',
              PNMD: 'pnmd',

              //formName: landscapeValue,
              //other values
            };

            return {
              ...state,
              landscapeMap,
              formName,
              formOwner,
              data: {
                ...cleanedSubmission,
                durableUUID: `${_submission_time}-${_xform_id_string}-${_id}`,
                datasetId: `${formName}-${_xform_id_string}`,
                end: cleanedSubmission.end.slice(0, 10),
              },
            };
            /* } catch (error) {
              state.connection.close();
              throw error;
            }*/
          });

          // Refactor this for scale so it doesn't perform a no-op delete 9/10 times.
          // Maybe check result of previous op, then only delete if it was an update.
          sql({
            query: state =>
              `DELETE FROM WCSPROGRAMS_KoboBnsPrice where AnswerId = '${state.data._id}'`,
          });

          fn(state => {
            const { good } = state.data;
            if (!good || good.length === 0) {
              return state;
            }

            const data = good.map((g, i) => ({
              // Id: state.data._id,
              Id: i + 1,
              AnswerId: state.data._id,
              DatasetUuidId: state.data.datasetId,
              Surveyor: state.data.surveyor,
              Village: state.data.village,
              Gs: g[`good/name`],
              Price: g[`good/price`],
              LastUpdate: new Date().toISOString(),
              //Landscape: state.landscapeMap[state.data.formName], //see L24 for mappings. We want to use formName to look-up a new value
              Landscape: () => {
                for (let val in state.landscapeMap)
                  if (state.formName.includes(val)) return state.landscapeMap[val];
                return '';
              },
              SurveyDate: state.data.today,
            }));
            // console.log('data', data);
            return insertMany('WCSPROGRAMS_KoboBnsPrice', data)(state);
          });

          fn(state => {
            console.log('DatasetName ::', state.formName);
            console.log('DatasetOwner ::', state.formOwner);
            console.log('form submission id ::', state.data['_id']);
            console.log('DatasetUuidId ::', state.data['datasetId']);
            //console.log('data to upload ::', state.data);
            return state;
          });

          upsert('WCSPROGRAMS_KoboData', 'DatasetUuidId', {
            //AnswerId: dataValue('durableUUID'),
            DatasetName: state => state.formName,
            DatasetOwner: state => state.formOwner,
            Landscape: dataValue('landscape'),
            DatasetUuidId: dataValue('datasetId'),
            DatasetYear: new Date().getFullYear(),
            LastSubmissionTime: dataValue('_submission_time'),
            LastCheckedTime: dataValue('_submission_time'),
            LastUpdateTime: new Date().toISOString(),
            KoboManaged: true,
            Tags: dataValue('_tags'),
            Citation: dataValue('instance'),
          });

          fn(state => {
            console.log('data uploaded ::', state.data);
            return state;
          });

      BNS-2B-BNS-Survey:
        name: BNS 2B BNS Survey
        adaptor: '@openfn/language-mssql@2.6.1'
        credential: rediet@openfn.org-WCS-MSSQL-Test-DB
        body: |
          // NOTE: This data cleaning operation returns state, modified as needed.

          fn(state => {
            try {
              const { body, formName, formOwner, instance } = state.data;
              const { _submission_time, _id, _xform_id_string } = body;

              let cleanedSubmission = {};

              for (const key in body) {
                switch (body[key]) {
                  case 'yes':
                    cleanedSubmission[key] = 1;
                    break;

                  case 'no':
                    cleanedSubmission[key] = 0;
                    break;

                  default:
                    cleanedSubmission[key] = body[key];
                    break;
                }
              }

              // NOTE: This assumes all device-collected geo data follows specific lat, log data format
              if (cleanedSubmission.gps_method === 'device') {
                cleanedSubmission['gps/lat'] =
                  cleanedSubmission.geo && cleanedSubmission.geo.split(' ')[0];
                cleanedSubmission['gps/long'] =
                  cleanedSubmission.geo && cleanedSubmission.geo.split(' ')[1];
              } else if (
                Math.abs(parseFloat(cleanedSubmission['gps/lat'])) > 90 ||
                Math.abs(parseFloat(cleanedSubmission['gps/long'])) > 180
              ) {
                console.log(
                  `WARNING: Discarding invalid manual GPS entry: 'gps/lat': ${cleanedSubmission['gps/lat']}; 'gps/long': ${cleanedSubmission['gps/long']}`
                );
                delete cleanedSubmission['gps/lat'];
                delete cleanedSubmission['gps/long'];
              }

              cleanedSubmission.durableUUID = `${_submission_time}-${_xform_id_string}-${_id}`; //survey uuid
              cleanedSubmission.datasetId = `${formName}-${_xform_id_string}`; //dataset uuid
              cleanedSubmission.instance = instance;
              state.data = cleanedSubmission;

              state.landscapeMap = {
                tns: 'ndoki',
                ltlt: 'lac_tele',
                mamabay: 'makira',
                mtkb: 'kahuzi',
              };

              // Cleaning datasetId if formName is 'BNS Cross River 2017-2020'==============
              if (formName.replace(/\s/g, '') === 'BNSCrossRiver2017-2020') {
                state.data.datasetId = `${state.data.datasetId}${
                  body.today.split('-')[0]
                }`;
              }

              // ===========================================================================

              // ===========================================================================
              //  NOTE: These job mappings assume a specific Kobo form metadata naming syntax!
              //  'NR' and 'BNS matrix' questions should follow the naming conventions below
              //  See Docs to learn more about the assumptions made here.
              // ===========================================================================
              // If a partner creates a form with slightly different field names, this
              // section will need to be updated by WCS. If future forms are being designed,
              // we'd recommend using a repeat group that allows the partner to select the
              // type of 'nr' or 'matrix' they're reporting on. The current approach treats
              // the form field names in Kobo _AS_ data themselves.
              state.nr = Object.keys(state.data)
                .filter(key => key.startsWith('nr/'))
                .map(key => ({
                  DatasetUuidId: state.data.datasetId,
                  AnswerId: state.data._id,
                  Id: state.data._id,
                  LastUpdate: new Date().toISOString(),
                  Nr: key.substring(3),
                  NrCollect: state.data[key],
                }));

              const matrix = Object.keys(state.data)
                .filter(key => key.includes('bns_matrix_'))
                .map(key => {
                  const item = key.substring(
                    key.lastIndexOf('bns_matrix_') + 'bns_matrix_'.length,
                    key.lastIndexOf('_')
                  );
                  return {
                    Dataset_Id: state.data.datasetId, //DatasetUuidId
                    DatasetUuidId: state.data.datasetId,
                    //Id: state.data._id,
                    AnswerId: state.data._id,
                    gs: item.replace(/_/g, ' '),
                    have:
                      state.data[
                        `hh_assets/bns_matrix_${item}/bns_matrix_${item}_possess`
                      ] || state.data[`bns_matrix_${item}/bns_matrix_${item}_possess`],
                    necessary:
                      state.data[
                        `hh_assets/bns_matrix_${item}/bns_matrix_${item}_necessary`
                      ] || state.data[`bns_matrix_${item}/bns_matrix_${item}_necessary`],
                    quantity:
                      state.data[
                        `hh_assets/bns_matrix_${item}/bns_matrix_${item}_number`
                      ] || state.data[`bns_matrix_${item}/bns_matrix_${item}_number`],
                  };
                });

              state.matrix = matrix.filter(
                (x, i) => matrix.findIndex(y => y.gs == x.gs) == i
              );
              // ===========================================================================
              // console.log(
              //   'The bns_matrix',
              //   JSON.stringify(state.matrix, null, 2),
              //   `contains ${state.matrix.length} items.`
              // );
              console.log('instance: ', instance);
              return {...state, formOwner, formName};
            } catch (error) {
              state.connection.close();
              throw error;
            }
          });

          upsert('WCSPROGRAMS_KoboBnsAnswer', 'AnswerId', {
            DatasetUuidId: dataValue('datasetId'),
            //Id: dataValue('durableUUID'), //Q: does not exist, to add for consistency?
            SubmissionUuid: dataValue('_uuid'),
            AnswerId: dataValue('_id'),
            LastUpdate: new Date().toISOString(),
            SurveyDate: state => {
              const date = state.data.today || state.data._submission_time;
              const year = Number(date.trim().split('-')[0]);
              const formName = dataValue('formName');
              if (year <= 2010) return Number(formName.trim().split(' ').at(-1));
              return year;
            },
            Landscape: state => {
              var landscape = dataValue('landscape')(state);
              return state.landscapeMap[landscape] || landscape;
            },
            Surveyor: dataValue('surveyor'),
            Participant: dataValue('participant'),
            Arrival: dataValue('arrival'),
            District: dataValue('district'),
            Village: dataValue('village'),
            HhId: dataValue('hh_id'),
            BenefProject: dataValue('benef_project'),
            HhTypeControl: state => (state.data.hh_type === 'control' ? 1 : 0),
            HhTypeOrgBenef: state => (state.data.hh_type === 'wcs_benef' ? 1 : 0),
            HhTypeOtherBenef: state => (state.data.hh_type === 'other_benef' ? 1 : 0),
            ExplainProject: dataValue('explain_project'),
            KnowPa: dataValue('know_PA'),
            BenefPa: dataValue('benef_PA'),
            ExplainBenefPa: dataValue('explain_benef_PA'),
            Livelihood1: dataValue('livelihoods/l1'),
            Livelihood2: dataValue('livelihoods/l2'),
            Livelihood3: dataValue('livelihoods/l3'),
            Livelihood4: dataValue('livelihoods/l4'),
            BnsPlus: dataValue('bns_plus'),
          });

          // Refactor this for scale so it doesn't perform a no-op delete 9/10 times.
          // Maybe check result of previous op, then only delete if it was an update.
          sql({
            query: state =>
              `DELETE FROM WCSPROGRAMS_KoboBnsAnswerhhmembers where AnswerId = '${state.data._id}'`,
          });

          insert('WCSPROGRAMS_KoboBnsAnswerhhmembers', {
            //insert hh head first
            DatasetUuidId: dataValue('datasetId'),
            Id: '0',
            //Id: state => state.data.hh_members.length,
            AnswerId: dataValue('_id'),
            Head: dataValue('gender_head') ? '1' : '0',
            Gender: dataValue('gender_head'),
            Ethnicity: dataValue('ethnicity_head'),
            Birth: state => {
              var birth = dataValue('birth_head')(state);
              return birth ? parseInt(birth.substring(0, 4)) : null;
            },
            LastUpdate: new Date().toISOString(),
          });

          fn(state => {
            if (state.data.hh_members) {
              return insertMany(
                'WCSPROGRAMS_KoboBnsAnswerhhmembers',
                (
                  state //then insert other members
                ) =>
                  state.data.hh_members.map((member, i) => ({
                    DatasetUuidId: state.data.datasetId,
                    // Id: state.data._id,
                    Id: i + 1,
                    AnswerId: state.data._id,
                    Head: '0',
                    Gender:
                      member[`hh_members/gender`] || member[`hh_members/gender_001`],
                    Ethnicity: member[`hh_members/ethnicity`],
                    Birth: parseInt(member[`hh_members/birth`].substring(0, 4)),
                    LastUpdate: new Date().toISOString(),
                  }))
              )(state);
            }

            console.log('No household members found.');
            return state;
          });

          // Refactor this for scale so it doesn't perform a no-op delete 9/10 times.
          // Maybe check result of previous op, then only delete if it was an update.
          sql({
            query: state =>
              `DELETE FROM WCSPROGRAMS_KoboBnsAnswernr where AnswerId = '${state.data._id}'`,
          });

          fn(state => {
            if (state.nr && state.nr.length > 0) {
              return insertMany('WCSPROGRAMS_KoboBnsAnswernr', state => state.nr)(state);
            }

            console.log('No natural resource found.');
            return state;
          });

          // Refactor this for scale so it doesn't perform a no-op delete 9/10 times.
          // Maybe check result of previous op, then only delete if it was an update.
          //sql({ query: state => `DELETE FROM WCSPROGRAMS_KoboBnsAnswergs where AnswerId = '${state.data._id}'` }); //ERROR: AnswerId does not exist
          sql({
            query: state =>
              `DELETE FROM WCSPROGRAMS_KoboBnsAnswerGS where AnswerId = '${state.data._id}'`,
          });

          fn(state => {
            if (state.matrix && state.matrix.length > 0) {
              return insertMany(
                'WCSPROGRAMS_KoboBnsAnswerGS',
                state => state.matrix
              )(state);
            }

            console.log('No matrix found.');
            return state;
          });

          upsert('WCSPROGRAMS_KoboBnsAnswergps', 'AnswerId', {
            DatasetUuidId: dataValue('datasetId'), //Q: Add new column
            AnswerId: dataValue('_id'),
            Id: dataValue('_id'),
            Geom: dataValue('_geolocation'),
            Lat: state => {
              return dataValue('gps/lat')(state)
                ? dataValue('gps/lat')(state)
                : state.data._geolocation[0] || undefined;
            },
            Long: state => {
              return dataValue('gps/long')(state)
                ? dataValue('gps/long')(state)
                : state.data._geolocation[1] || undefined;
            },
            LastUpdate: new Date().toISOString(),
          });

          fn(state => {
            console.log('DatasetName ::', state.formName);
            console.log('DatasetOwner ::', state.formOwner);
            console.log('form submission id ::', state.data['_id']);
            console.log('DatasetUuidId ::', state.data['datasetId']);
            //console.log('data to upload ::', state.data);
            return state;
          })

          upsert('WCSPROGRAMS_KoboData', 'DatasetUuidId', {
            //renamed from DatasetUuid
            //AnswerId: dataValue('_id'), //KoboData = 1 Dataset (not 1 survey)
            DatasetName: state => state.formName,
            DatasetOwner: state => state.formOwner,
            Landscape: dataValue('landscape'),
            DatasetUuidId: dataValue('datasetId'),
            Citation: dataValue('instance'),
            DatasetYear: state => {
              const date = state.data.today || state.data._submission_time;
              const year = Number(date.trim().split('-')[0]);
              const formName = dataValue('formName')(state);
              const yearToReturn = year;
              if (year <= 2010) yearToReturn = Number(formName.trim().split(' ').at(-1));
              console.log(yearToReturn);
              return yearToReturn;
              // const formName = dataValue('formName')(state);
              // if (formName === 'BNS Cross River 2017-2020') {
              return state.data.body.today.split('-')[0];
              // }
              //const year = dataValue('body.today');
              //console.log(year);
              return new Date().getFullYear(); // Here we don't want the date of today we want the year of the value today
              //console.log(Date(year).getFullYear());
            },
            LastSubmissionTime: dataValue('_submission_time'),
            LastCheckedTime: dataValue('_submission_time'),
            LastUpdateTime: new Date().toISOString(),
            KoboManaged: true,
            Tags: dataValue('_tags'),
          });

      BNS-2C-NRGT---Historical-Version:
        name: BNS-2C NRGT - Historical Version
        adaptor: '@openfn/language-mssql@3.0.0'
        credential: rediet@openfn.org-WCS-MSSQL-Test-DB
        body: |
          // NOTE: This data cleaning operation returns state, modified as needed.
          alterState(state => {
            try {
              const { body, formName, instance } = state.data;
              const { _submission_time, _id, _xform_id_string } = body;
              let cleanedSubmission = {};

              for (const key in body) {
                switch (body[key]) {
                  case 'yes':
                    cleanedSubmission[key] = 1;
                    break;

                  case 'no':
                    cleanedSubmission[key] = 0;
                    break;

                  default:
                    cleanedSubmission[key] = body[key];
                    break;
                }
              }

              state.landscapeMap = {
                tns: 'ndoki',
                mamabay: 'makira',
                mtkb: 'kahuzi',
                lactele: 'lac_tele',
              };

              cleanedSubmission.durableUUID = `${_submission_time}-${_xform_id_string}-${_id}`;
              cleanedSubmission.datasetId = `${formName}-${_xform_id_string}`;
              cleanedSubmission.instance = instance;
              state.data = cleanedSubmission;
              return state;
            } catch (error) {
              state.connection.close();
              throw error;
            }
          });

          upsert('WCSPROGRAMS_KoboNrgtNrgtanswer', 'AnswerId', {
            DatasetUuidId: dataValue('datasetId'),
            AnswerId: dataValue('_id'),
            Landscape: state => {
              var landscape = dataValue('landscape')(state);
              return state.landscapeMap[landscape] || landscape;
            },
            GovGroup: dataValue('gov_group'),
            Jurisdiction: dataValue('jurisdiction'),
            Objective: dataValue('objective'),
            Members: dataValue('members'),
            Women: dataValue('women'),
            LastUpdate: new Date().toISOString(),
          });

          upsert('WCSPROGRAMS_KoboNrgtNrgtanswergs', 'AnswerId', {
            DatasetUuidId: dataValue('datasetId'),
            Id: dataValue('_id'),
            AnswerId: dataValue('_id'),
            SurveyDate: dataValue('today'),
            Code: dataValue('code'),
            Gender: dataValue('gender'),
            Member: dataValue('member'),
            Legitimacy: dataValue('legitimacy'),
            Accountability: dataValue('accountability'),
            Transparency: dataValue('transparency'),
            Participation: dataValue('participation'),
            Fairness: dataValue('fairness'),
            KnowledgeSkills: dataValue('knowledge_skills'),
            Resources: dataValue('resources'),
            InstutionalFramework: dataValue('institutional_framework'),
            Motivation: dataValue('motivation'),
            EnactDecision: dataValue('enact_decision'),
            HeldAccountable: dataValue('held_accountable'),
            Diversity: dataValue('diversity'),
            LastUpdate: new Date().toISOString(),
          });

          upsert('WCSPROGRAMS_KoboData', 'DatasetUuidId', {
            //AnswerId: dataValue('_id'),
            DatasetName: state.data.formName,
            DatasetOwner: state.data.formOwner,
            DatasetUuidId: dataValue('datasetId'),
            Citation: dataValue('instance'),
            DatasetYear: new Date().getFullYear(),
            LastSubmissionTime: dataValue('_submission_time'),
            LastCheckedTime: dataValue('_submission_time'),
            LastUpdateTime: new Date().toISOString(),
            KoboManaged: true,
            Tags: dataValue('_tags'),
          });

      BNS-2D-NRGT-2019:
        name: BNS-2D NRGT 2019
        adaptor: '@openfn/language-mssql@3.0.0'
        credential: rediet@openfn.org-WCS-MSSQL-Test-DB
        body: |
          // NOTE: This data cleaning operation returns state, modified as needed.
          alterState(state => {
            try {
              const { body, formName, instance } = state.data;
              const { _submission_time, _id, _xform_id_string } = body;
              let cleanedSubmission = {};

              for (const key in body) {
                switch (body[key]) {
                  case 'yes':
                    cleanedSubmission[key] = 1;
                    break;

                  case 'no':
                    cleanedSubmission[key] = 0;
                    break;

                  default:
                    cleanedSubmission[key] = body[key];
                    break;
                }
              }

              state.landscapeMap = {
                tns: 'ndoki',
                mamabay: 'makira',
                mtkb: 'kahuzi',
              };

              cleanedSubmission.durableUUID = `${_submission_time}-${_xform_id_string}-${_id}`;
              cleanedSubmission.datasetId = `${formName}-${_xform_id_string}`;
              cleanedSubmission.instance = instance;
              state.data = cleanedSubmission;
              return state;
            } catch (error) {
              state.connection.close();
              throw error;
            }
          });

          upsert('WCSPROGRAMS_KoboNrgtNrgtanswer', 'AnswerId', {
            DatasetUuidId: dataValue('datasetId'),
            AnswerId: dataValue('_id'),
            Landscape: state => {
              var landscape = dataValue('landscape')(state);
              return state.landscapeMap[landscape] || landscape;
            },
            Surveyor: dataValue('surveyor'),
            GovGroup: dataValue('gov_group'),
            SurveyDate: state => {
              const date = state.data.today || state.data._submission_time
              if (Number(date.split('-')[0]) >= 2014 ) {
                return date
              } 
              return 2019 
              // If the time/date is not properly set on the device used to collect the data, the year of "today" will be 2000. 
              // With the code above we are replacing any 2000 by 2019:
            },
            LastUpdate: new Date().toISOString(),
          });
          upsert('WCSPROGRAMS_KoboNrgtNrgtanswergs', 'AnswerId', {
            // upsert('WCSPROGRAMS_KoboNrgtNrgtanswergs', 'DatasetUuidId', {
            DatasetUuidId: dataValue('datasetId'),
            Id: dataValue('_id'),
            AnswerId: dataValue('_id'),
            SurveyDate: state => {
              const date = state.data.today || state.data._submission_time
              if (Number(date.split('-')[0]) >= 2014 ) {
                return date
              } 
              return 2019
              // If the time/date is not properly set on the device used to collect the data, the year of "today" will be 2000. 
              // With the code above we are replacing any 2000 by 2019:
            },
            Gender: dataValue('gender'),
            Member: dataValue('member'),
            Objective: dataValue('objective'),
            Legitimacy: dataValue('legitimacy'),
            Accountability: dataValue('accountability'),
            Transparency: dataValue('transparency'),
            Participation: dataValue('participation'),
            Fairness: dataValue('fairness'),
            Diversity: dataValue('diversity'),
            KnowledgeSkills: dataValue('knowledge_skills'),
            Resources: dataValue('resources'),
            InstutionalFramework: dataValue('framework'),
            Motivation: dataValue('motivation'),
            Power: dataValue('power'),
            LastUpdate: new Date().toISOString(),
          });

          upsert('WCSPROGRAMS_KoboData', 'DatasetUuidId', {
            //AnswerId: dataValue('_id'),
            DatasetName: state.data.formName,
            DatasetOwner: state.data.formOwner,
            DatasetUuidId: dataValue('datasetId'),
            Citation: dataValue('instance'),
            DatasetYear: new Date().getFullYear(),
            LastSubmissionTime: dataValue('_submission_time'),
            LastCheckedTime: dataValue('_submission_time'),
            LastUpdateTime: new Date().toISOString(),
            KoboManaged: true,
            Tags: dataValue('_tags'),
          });

      BNS-2E-NRGT---Historical-Version-2-2022:
        name: BNS-2E NRGT - Historical Version 2 2022
        adaptor: '@openfn/language-mssql@3.0.0'
        credential: rediet@openfn.org-WCS-MSSQL-Test-DB
        body: |
          fn(state => {
            try {
              const { body, formName, instance } = state.data;
              const { _submission_time, _id, _xform_id_string, group_scores } = body;
              let cleanedSubmission = {};

              for (const key in body) {
                switch (body[key]) {
                  case 'yes':
                    cleanedSubmission[key] = 1;
                    break;

                  case 'no':
                    cleanedSubmission[key] = 0;
                    break;

                  default:
                    cleanedSubmission[key] = body[key];
                    break;
                }
              }

              state.landscapeMap = {
                tns: 'ndoki',
                mamabay: 'makira',
                mtkb: 'kahuzi',
                lactele: 'lac_tele',
              };

              cleanedSubmission.durableUUID = `${_submission_time}-${_xform_id_string}-${_id}`;
              cleanedSubmission.datasetId = `${formName}-${_xform_id_string}`;
              cleanedSubmission.instance = instance;
              cleanedSubmission.group_scores = group_scores;
              
              state.data = cleanedSubmission;

              return state;
            } catch (error) {
              state.connection.close();
              throw error;
            }
          });


          upsert('WCSPROGRAMS_KoboNrgtNrgtanswer', 'AnswerId', {
            DatasetUuidId: dataValue('datasetId'),
            AnswerId: dataValue('_id'),
            Landscape: state => {
              var landscape = dataValue('landscape')(state);
              return state.landscapeMap[landscape] || landscape;
            },
            GovGroup: dataValue('gov_group'),
            Jurisdiction: dataValue('jurisdiction'),
            Objective: dataValue('objective'),
            Members: dataValue('members'),
            Women: dataValue('women'),
            LastUpdate: new Date().toISOString(),
          });

          /*
          upsert('WCSPROGRAMS_KoboNrgtNrgtanswergs', 'AnswerId', {
            DatasetUuidId: dataValue('datasetId'),
            Id: dataValue('_id'),
            AnswerId: dataValue('_id'),
            SurveyDate: dataValue('today'),
            Code: dataValue('code'),
            Gender: dataValue('gender'),
            Member: dataValue('member'),
            Legitimacy: dataValue('legitimacy'),
            Accountability: dataValue('accountability'),
            Transparency: dataValue('transparency'),
            Participation: dataValue('participation'),
            Fairness: dataValue('fairness'),
            KnowledgeSkills: dataValue('knowledge_skills'),
            Resources: dataValue('resources'),
            InstutionalFramework: dataValue('institutional_framework'),
            Motivation: dataValue('motivation'),
            EnactDecision: dataValue('enact_decision'),
            HeldAccountable: dataValue('held_accountable'),
            Diversity: dataValue('diversity'),
            LastUpdate: new Date().toISOString(),
          });
          */

          upsertMany(
            'WCSPROGRAMS_KoboNrgtNrgtanswergs', 
            'AnswerId', 
            state => state.data.group_scores.map(x => ({
                AnswerId: state.data._id,
                Id: state.data._id,
                DatasetUuidId: state.data.datasetId,
                Accountability: x["group_scores/accountability"],
                Code: x["group_scores/code"],
                Diversity: x["group_scores/diversity"],
                EnactDecision: x["group_scores/enact_decision"],
                Fairness: x["group_scores/fairness"],
                Gender: x["group_scores/gender"],
                HeldAccountable: x["group_scores/held_accountable"],
                InstutionalFramework: x["group_scores/institutional_framework"],
                KnowledgeSkills: x["group_scores/knowledge_skills"],
                Legitimacy: x["group_scores/legitimacy"],
                Member: (x["group_scores/member"] === "yes"),
                Motivation: x["group_scores/motivation"],
                Participation: x["group_scores/participation"],
                Resources: x["group_scores/resources"],
                SurveyDate: x["group_scores/survey_date"],
                Transparency: x["group_scores/transparency"],
                LastUpdate: new Date().toISOString()
              }))
          );


          upsert('WCSPROGRAMS_KoboData', 'DatasetUuidId', {
            DatasetName: state.data.formName,
            DatasetOwner: state.data.formOwner,
            DatasetUuidId: dataValue('datasetId'),
            Citation: dataValue('instance'),
            DatasetYear: new Date().getFullYear(),
            LastSubmissionTime: dataValue('_submission_time'),
            LastCheckedTime: dataValue('_submission_time'),
            LastUpdateTime: new Date().toISOString(),
            KoboManaged: true,
            Tags: dataValue('_tags'),
          });

    triggers:
      webhook:
        type: webhook
        enabled: true
    edges:
      webhook->Triage-jobs:
        source_trigger: webhook
        target_job: Triage-jobs
        condition_type: always
        enabled: true
      Triage-jobs->BNS-2A-BNS-Price:
        source_job: Triage-jobs
        target_job: BNS-2A-BNS-Price
        condition_type: js_expression
        condition_label: BNS Price
        condition_expression: |
          state.data.form === "bns_price" && 
          state.data.body?.survey_type && 
          state.data.body.survey_type !== "practice"
        enabled: true
      Triage-jobs->BNS-2B-BNS-Survey:
        source_job: Triage-jobs
        target_job: BNS-2B-BNS-Survey
        condition_type: js_expression
        condition_label: BNS Survey
        condition_expression: |
          state.data.form == "bns_survey" && 
          state.data.body?.survey_type && 
          state.data.body.survey_type !== "practice"
        enabled: true
      Triage-jobs->BNS-2C-NRGT---Historical-Version:
        source_job: Triage-jobs
        target_job: BNS-2C-NRGT---Historical-Version
        condition_type: js_expression
        condition_label: NRGT Historical
        condition_expression: |
          state.data.form == "nrgt_historical" && state.data.formName != "NRGT Kahuzi Biega 2019"
        enabled: true
      Triage-jobs->BNS-2D-NRGT-2019:
        source_job: Triage-jobs
        target_job: BNS-2D-NRGT-2019
        condition_type: js_expression
        condition_label: NRGT Current
        condition_expression: |
          state.data.form == "nrgt_current" && 
          state.data.body?.survey_type && 
          state.data.body.survey_type !== "practice"
        enabled: true
      Triage-jobs->BNS-2E-NRGT---Historical-Version-2-2022:
        source_job: Triage-jobs
        target_job: BNS-2E-NRGT---Historical-Version-2-2022
        condition_type: js_expression
        condition_label: NGRT Historical
        condition_expression: |
          state.data.form == "nrgt_historical" && state.data.formName == "NRGT Kahuzi Biega 2019"
        enabled: true
  Monitor-Forms-Shared:
    name: Monitor Forms Shared
    jobs:
      FS1---Get-Forms:
        name: FS1 - Get Forms
        adaptor: '@openfn/language-kobotoolbox@2.1.0'
        credential: rediet@openfn.org-WCS-Kobo-Raw-with-URL
        body: |
          //Check Kobo account for forms with these matching keywords
          getForms({}, state => {
            //ALL KEYWORDS:
            //const keywords = ['price', 'prix', 'bns', 'nrgt', 'grm', 'feedback'];

            //BNS KEYWORDS ONLY
            const keywords = ['price', 'prix', 'bns', 'nrgt'];

            const checkForKeyWords = name => {
              return keywords.some(keyword => name.toLowerCase().includes(keyword));
            };

            state.activeForms = state.data.results
              .filter(form => checkForKeyWords(form.name))
              .filter(form => form.deployment__active);

            state.archivedForms = state.data.results
              .filter(form => checkForKeyWords(form.name))
              .filter(form => !form.deployment__active);
              
            console.log('# of activeForms ::', state.activeForms ? state.activeForms.length : null );
            console.log('# of archivedForms ::', state.archivedForms ? state.archivedForms.length : null );

            state.data = {};
            state.references = [];
            return state;
          });

      FS2---Get-List-from-Sheets:
        name: FS2 - Get List from Sheets
        adaptor: '@openfn/language-googlesheets@2.4.0'
        credential: rediet@openfn.org-Rediet-Googlesheet
        body: |
          getValues(
            '1xexwj6HKJGHJM-sOzIOGqPHolgpdnp0GmjMIXwoV7OE', //googlesheet id
            'wcs-bns-DEPLOYED!A:O' //range of columns in sheet
          );
          fn(state => {
            const { activeForms, archivedForms, data } = state;
            const [headers, ...sheetsData] = data.values;
            const sheetsUids = sheetsData.map(row => row[0]);
            console.log('Ignoring headers', headers);

            state.formsToCreate = activeForms.filter(
              form => !sheetsUids.includes(form.uid)
            );

            state.formsToUpdate = archivedForms
              .filter(form => sheetsUids.includes(form.uid))
              .map(form => {
                const rowIndex = sheetsData.findIndex(row => {
                  return row[0] === form.uid;
                });
                if (rowIndex !== -1) {
                  return { ...form, rowIndex };
                }
                console.log(form.uid, 'Could not be found in google sheet');
              });

            return state;
          });

          fn(state => {
            const { data, references, response, ...remainingState } = state;

            return remainingState;
          });

      FS3---Update-Spreadsheet-with-New-Forms-Shared:
        name: FS3 - Update Spreadsheet with New Forms Shared
        adaptor: '@openfn/language-googlesheets@2.4.0'
        credential: rediet@openfn.org-Rediet-Googlesheet
        body: |
          //Compare new forms in Kobo with GoogleSheet list to see if new forms were shared in Kobo
          fn(state => {
            const { formsToCreate, formsToUpdate } = state;
            const keywords = ['price', 'prix', 'bns', 'nrgt', 'grm', 'feedback'];

            const tagMapping = {
              price: 'bns_price',
              prix: 'bns_price',
              bns: 'bns_survey',
              nrgt: 'nrgt_current',
              grm: 'grm',
              feedback: 'grm',
            };

            const createTagName = name => {
              let tag = '';
              const keyword = keywords.find(keyword =>
                name.toLowerCase().includes(keyword)
              );

              if (keyword) {
                tag = tagMapping[keyword] || keyword;
              }
              return tag;
            };

            const containsGRMFeedback = name =>
              !name.toLowerCase().includes('grm', 'feedback');

            const instance = name =>
              containsGRMFeedback(name) ? 'ADD MANUALLY @Admin!' : '';

            const projectId = name =>
              containsGRMFeedback(name) ? 'ADD MANUALLY @Admin!' : '';

            const grmID = name => (containsGRMFeedback(name) ? 'GRM ID. XX' : '');

            const workspaceName = name =>
              containsGRMFeedback(name) ? 'Grievances' : 'ConSoSci';
              
            state.formLastModified = form => form.date_modified; 

            const sheetRowMap = form => [
              form.uid,
              form.name,
              createTagName(form.name),
              form.owner__username,
              instance(form.name),
              //projectId(form.name), //for GRM only
              //grmID(form.name), //for GRM only
              form.deployment__active ? 'deployed' : 'archived', //deployment status //if we assume only deployed forms will be fetched
              'ConSoSci', //openfn project space --> OLD dynamic mapping: //workspaceName(form.name),
              `https://kf.kobotoolbox.org/#/forms/${form.url}/summary`, //form.url,
              form.date_modified, //kobo_form_date_modified
              form.date_created, //kobo_form_date_created
              new Date().toISOString(), //row_date_modified
              false, //auto_sync checkbox
              //job code template
              `"{id: '${form.uid}', tag: '${createTagName(form.name)}', name: '${
                form.name
              }', owner: '${form.owner__username}', instance: '${instance(form.name)}'},"`,
            ];

            state.rowValuesToCreate = formsToCreate.map(form => sheetRowMap(form));
            state.rowValuesToUpdate = formsToUpdate.map(form => ({
              range: `wcs-bns-DEPLOYED!A${form.rowIndex + 2}:N${form.rowIndex + 2}`,
              values: [sheetRowMap(form)],
            }));
            state.rowValuesToArchive = formsToUpdate.map(form => sheetRowMap(form));

            console.log('# of new forms detected:: ', state.rowValuesToCreate.length);
            console.log('Forms to add to the master sheet:: ', state.rowValuesToCreate);
            return state;
          });

          //if new Kobo form shared, adding to the "Deployed"" Sheet...
          appendValues({
            spreadsheetId: '1s7K3kxzm5AlpwiALattyc7D9_aIyqWmo2ubcQIUlqlY', //sheet id
            range: 'wcs-bns-DEPLOYED!A:O', //range of columns in sheet
            values: state => state.rowValuesToCreate,
          });

          //updating rows in Sheet where forms are archived
          each(
            '$.rowValuesToUpdate[*]',
            batchUpdateValues({
              spreadsheetId: '1s7K3kxzm5AlpwiALattyc7D9_aIyqWmo2ubcQIUlqlY', //sheet id
              range: state => state.data.range, //range of columns in sheet
              values: state => state.data.values,
            })
          );

          //also adding archived rows to "Archived" Sheet...
          appendValues({
            spreadsheetId: '1s7K3kxzm5AlpwiALattyc7D9_aIyqWmo2ubcQIUlqlY', //sheet id
            range: 'wcs-bns-ARCHIVED!A:O', //range of columns in sheet
            values: state => state.rowValuesToArchive,
          });

      FS4---Notify-in-Asana:
        name: FS4 - Notify in Asana
        adaptor: '@openfn/language-asana@4.0.9'
        credential: aleksa@openfn.org-Asana-API-Token---AK-WCS-Grievances
        body: |
          //This job will add a task to Asana if a new Kobo form was shared
          fn(state => {
            console.log('formLastModifiedDate:: ', state.formLastModified); 
            const dueDate = new Date(new Date().getTime() + 5 * 24 * 60 * 60 * 1000)
              .toISOString()
              .split('T')[0];

            state.asanaTasks = state.activeForms.map(form => {
              return {
                name: `New form added to OpenFn: ${form.name}`,
                approval_status: 'pending',
                projects: ['1198901998266253'],
                assignee_section: '1207247884457665', //OLD General Section: '1203181218738601',
                assignee: '1208302456826465',
                due_on: dueDate,
                notes: `New form added to OpenFn: ${form.name} (uid: ${form.uid}). Please review the Google Sheet to (1) update columns L, N, & O, and (2) update column E (look for cells where it says "ADD MANUALLY" to add any values missing e.g., "Instance"): https://docs.google.com/spreadsheets/d/1s7K3kxzm5AlpwiALattyc7D9_aIyqWmo2ubcQIUlqlY/edit#gid=1559623602`,
              };
            });

            state.archivedFormsTasks = state.archivedForms.map(form => {
              return {
                name: `Form archived: ${form.name}`,
                projects: ['1198901998266253'],
                assignee_section: '1207247884457665', //OLD General Section: '1203181218738601',
                assignee: '1208302456826465',
                due_on: dueDate,
                notes: `Kobo form was archived: ${form.name} (uid: ${form.uid}). Please review the Google Sheet to (1) confirm this is correct, (2) remove from the "Deployed" sheet if you want to remove from the OpenFn Sync, and (3) update notes in the "Archived" sheet: https://docs.google.com/spreadsheets/d/1s7K3kxzm5AlpwiALattyc7D9_aIyqWmo2ubcQIUlqlY/edit#gid=1559623602`,
              };
            });

            console.log('# of New Form Asana Tasks to add:: ', state.asanaTasks.length);
            console.log('New form alert tasks to upsert:: ', state.asanaTasks);
            console.log(
              '# of Archibed Form Asana Tasks to add:: ',
              state.archivedFormsTasks.length
            );
            console.log(
              'Archived form alert tasks to upsert:: ',
              state.archivedFormsTasks
            );
            return state;
          });

          //upsert Asana task if new form shared notification needed
          each(
            '$.asanaTasks[*]',
            upsertTask('1198901998266253', {
              //project_id
              externalId: 'name',
              data: state => state.data,
            })
          );

          each(
            '$.archivedFormsTasks[*]',
            upsertTask('1198901998266253', {
              //project_id
              externalId: 'name',
              data: state => state.data,
            })
          );

    triggers:
      cron:
        type: cron
        cron_expression: '0 0 * * *'
        enabled: false
    edges:
      cron->FS1---Get-Forms:
        source_trigger: cron
        target_job: FS1---Get-Forms
        condition_type: always
        enabled: true
      FS1---Get-Forms->FS2---Get-List-from-Sheets:
        source_job: FS1---Get-Forms
        target_job: FS2---Get-List-from-Sheets
        condition_type: on_job_success
        enabled: true
      FS2---Get-List-from-Sheets->FS3---Update-Spreadsheet-with-New-Forms-Shared:
        source_job: FS2---Get-List-from-Sheets
        target_job: FS3---Update-Spreadsheet-with-New-Forms-Shared
        condition_type: on_job_success
        enabled: true
      FS3---Update-Spreadsheet-with-New-Forms-Shared->FS4---Notify-in-Asana:
        source_job: FS3---Update-Spreadsheet-with-New-Forms-Shared
        target_job: FS4---Notify-in-Asana
        condition_type: on_job_success
        enabled: false
  Test-Martin:
    name: Test Martin
    jobs:
      New-job:
        name: New job
        adaptor: '@openfn/language-common@latest'
        credential: null
        body: |
          // Check out the Job Writing Guide for help getting started:
          // https://docs.openfn.org/documentation/jobs/job-writing-guide

    triggers:
      webhook:
        type: webhook
        enabled: false
    edges:
      webhook->New-job:
        source_trigger: webhook
        target_job: New-job
        condition_type: always
        enabled: true
  x_A1-Generate-Jobs-DB-Tables-and-Dictionary-AUTO:
    name: x_A1 Generate Jobs DB Tables and Dictionary AUTO
    jobs:
      A1-Generate-Jobs-DB-Tables-and-Dictionary-AUTO:
        name: A1 Generate Jobs DB Tables and Dictionary AUTO
        adaptor: '@openfn/language-http@6.5.4'
        credential: null
        body: |
          get('https://kf.kobotoolbox.org/api/v2/assets/?format=json', {}, state => {
            console.log(`Previous cursor: ${state.lastEnd}`);
            // Set a manual cursor if you'd like to only fetch form after a certain date
            const manualCursor = '2019-05-25T14:32:43.325+01:00';

            // ===========================================================================
            // == FOR ADMINS: Update the below `manualFormList` to designate which Kobo forms to sync ==//

            const manualFormList = [
              //==================== Forms must be shared with the account openfn_kobo====================//
              //=== WCS Camera trap metadata =====
              // {
              //     uid: 'axDXRTMWEkhrQDYQ9K3YdT', // Form name: 1. Project and cameras
              //     p1: 'WCSPROGRAMS',
              //     p2: 'CameraKobo',
              //     tableId: 'Project' // Result is Run 062f3e43-46fe-7332-99e3-07cce87ddbdf
              // },
              {
                  uid: 'axfD6ntJyhfD2mxAGuVRSE', // Form name: 2. Deployments
                  p1: 'WCSPROGRAMS',
                  p2: 'CameraKobo',
                  tableId: 'Deployment' // Result is Run 062f3e47-ed7f-7fc5-b367-4c0f9b530053
              },
              // {
              //     uid: 'a4yYjawjdbpHcckBx8m8AP', // Form name: 3. Retrieval
              //     p1: 'WCSPROGRAMS',
              //     p2: 'CameraKobo',
              //     tableId: 'Retrieval' // Result is Run 062f3e43-10bc-7697-9417-11c931b14c8f
              // },
              // {
              //     uid: 'a9F5e7wMMopSm85Abw3LTN', // Form name: 4. Images
              //     p1: 'WCSPROGRAMS',
              //     p2: 'CameraKobo',
              //     tableId: 'Image'
              // },
              //=== WCS Socio Economic Database =====
              // {
              //     uid: 'aukhdejQU76K33caCkF4rP',
              //     p1: 'WCSPROGRAMS',
              //     p2: 'SocioEco',
              //     tableId: 'SocioEcoSurvey'
              // },
              //==== SharksRays ===============//
              // {
              //   uid: 'aaknL3DQQgkgZ8iay89X5P',
              //   p1: 'WCSPROGRAMS',
              //   p2: '',
              //   tableId: 'SharksRays',
              // },
              // {
              //   uid: 'aStMvYShWXZsKYa7AyN6sr',
              //   p1: 'WCSPROGRAMS',
              //   p2: '',
              //   tableId: 'SharksRays',
              // },
              // {
              //   uid: 'aQeXAtEkgg8PGwxDiCUnPW',
              //   p1: 'WCSPROGRAMS',
              //   p2: '',
              //   tableId: 'SharksRays',
              // },
              //=== Trillion Trees forms =====
              // {
              //   uid: 'aHPGTtrrLB4k3xDA9UZipu',
              //   p1: 'WCSPROGRAMS',
              //   p2: '',
              //   tableId: 'Site',
              // },
              // {
              //   uid: 'a8ffyF7HgbFUEnYBppEL79',
              //   p1: 'WCSPROGRAMS',
              //   p2: '',
              //   tableId: 'Land',
              // },
              //=================================
              // {
              //   uid: 'avLpvrukkvuFzCHacjHdRs',
              //   p1: 'WCS',
              //   p2: 'Vegetation',
              //   tableId: 'VegetationClassficationAndTreeMeasurementForm'},

              //{ uid: 'apZrpKcK78xzrPcAfRrfac', p1: 'OpenFn', p2: 'Sharks', tableId: 'SharkRaysMay4Test'},
              //{ uid: 'azg4rJb2Kk8DT2upSPyYjB', p1: 'WCS', p2: 'Livestock', tableId: 'LivestockProduction'},
              //{ uid: 'aDgPJqN4SAYohZ4ZueEeYU', p1: 'WCS', p2: 'Arcadia', tableId: 'ArcadiaDataCollection'},
              //{ uid: 'a7Dx4vpFcj7ziwaKE4682U', p1: 'WCS', p2: 'Vegetation', tableId: 'VegetationClassficationAndTreeMeasurementForm'},
              //{ uid: 'apZrpKcK78xzrPcAfRrfac', p1: 'WCS', p2: 'SR', tableId: 'SharkAndRaysTraining'}
            ];

            state.data.forms = state.data.results
              // Filter the response from Kobo to show only those forms we want to update.
              .filter(form => manualFormList.map(x => x.uid).includes(form.uid))
              .filter(form => {
                // Note: If a form in manualFormList was not present in the list during
                // the last run of the job (formsWatched), then we always trigger an
                // update for that form.
                if (!state.formsWatched) {
                  return true;
                }
                if (!state.formsWatched.find(f => f.uid === form.uid)) {
                  console.log(`New form ${form.uid} (${form.name}) added to watch list.`);
                  return true;
                }
                // Note: If a form is not NEW to the watch list, then we only trigger an
                // update if it has been modified more recently than the greatest
                // last-modified date across all forms from our last run.
                return form.date_modified > (state.lastEnd || manualCursor);
              })
              // Map those forms so that we can post each to the inbox later.
              .map(form => {
                const url = form.url.split('?').join('?');
                const manualSpec = manualFormList.find(f => f.uid === form.uid);
                return {
                  formId: form.uid,
                  tag: manualSpec.surveyTable || form.name,
                  //tag: form.name,
                  url,
                  prefix1: manualSpec.p1,
                  prefix2: manualSpec.p2 || '',
                  tableId: manualSpec.tableId,
                  lastModified: form.date_modified,
                };
              });

            // Set lastEnd to the greatest date_modified value for all forms we care about.
            const lastEnd = state.data.results
              .filter(item => item.date_modified)
              .map(s => s.date_modified)
              .sort((a, b) => new Date(b.date) - new Date(a.date))[0];

            console.log(
              'Detected changes for:',
              JSON.stringify(
                state.data.forms.map(f => f.url),
                null,
                2
              )
            );

            return { ...state, lastEnd, formsWatched: manualFormList };
          });

          each(dataPath('forms[*]'), state => {
            const form = state.data;
            return post(
              state.configuration.openfnInboxUrl,
              { body: { ...form, formUpdate: true } },
              state => {
                console.log('Sent ', form.tag, ' for handling:');
                console.log(form);
                return state;
              }
            )(state);
          });

          // Clear everything from state but the required cursors.
          alterState(state => ({
            lastEnd: state.lastEnd,
            formsWatched: state.formsWatched,
          }));

    triggers:
      cron:
        type: cron
        cron_expression: '0 */3 * * *'
        enabled: false
    edges:
      cron->A1-Generate-Jobs-DB-Tables-and-Dictionary-AUTO:
        source_trigger: cron
        target_job: A1-Generate-Jobs-DB-Tables-and-Dictionary-AUTO
        condition_type: always
        enabled: true
  x_A2-Process-Forms-AUTO:
    name: x_A2 Process Forms AUTO
    jobs:
      A2-Process-Forms-AUTO:
        name: A2 Process Forms AUTO
        adaptor: '@openfn/language-http@v4.0.0'
        credential: null
        body: |
          get(`${state.data.url}`, {}, state => {
            state.formDefinition = state.data; // keeping form definition for data dictionary
            const tablesToBeCreated = [];
            const { survey, choices } = state.data.content;
            if (survey.length === 0) {
              console.log(
                'No survey available or defined to analyze. Please check the Kobo form deployment status'
              );
              return state;
            }
            // PREFIX HANDLER
            const prefix1 = state.references[0].prefix1 || 'WCS';
            const prefix2 = state.references[0].prefix2 || '';
            const tableId = state.references[0].tableId;
            const uuidColumnName = 'generated_uuid';
            const prefixes = [prefix1, prefix2].join('_');
            // END OF PREFIX HANDLER

            const multiSelectIds = [];

            const mapType = {
              calculate: 'varchar(100)',
              date: 'date',
              decimal: 'float4',
              end: 'date',
              integer: 'int4',
              select_one: 'select_one',
              start: 'date',
              text: 'text',
              today: 'date',
              jsonb: 'jsonb',
              select_multiple: 'select_multiple',
              geopoint: 'text',
            };

            const discards = [
              'begin_group',
              'begin_repeat',
              'end_group',
              'end_repeat',
              'note',
            ];

            // Camelize columns and table name
            function toCamelCase(str) {
              if (!str) return '';
              let underscores = [];
              let i = 0;
              while (str[i] === '_') {
                underscores.push(str[i]);
                i++;
              }
              let words = str.match(/[0-9a-zA-Z\u00C0-\u00FF]+/gi);
              if (!words) return '';
              words = words
                .map(word => {
                  return word.charAt(0).toUpperCase() + word.substr(1).toLowerCase();
                })
                .join('');
              return `${underscores.join('')}${words}${underscores.join('')}`;
            }

            function questionsToColumns(questions) {
              var form = questions.filter(elt => !discards.includes(elt.type));

              form.forEach(obj => (obj.type = mapType[obj.type] || 'text'));

              form.forEach(obj => {
                // List of reserved keys in postgresql and their transformations
                if (obj.name === 'group') {
                  obj.name = 'kobogroup';
                }
                if (obj.name == 'end') {
                  obj.name = 'form_date__end';
                }
                if (obj.name == 'column') {
                  obj.name = 'column_name';
                }
                if (obj.name == 'date') {
                  obj.name = 'date_value';
                }
                if (obj.type === 'select_one') {
                  obj.type = 'int4';
                  obj.select_one = true;
                  delete obj.default;
                }
              });

              form.forEach(q => {
                if (q.name === 'gps') {
                  form.push({ name: 'latitude', type: 'float4' });
                  form.push({ name: 'longitude', type: 'float4' });
                }
              });

              form = form.map(x => {
                let name = toCamelCase(x.name) || toCamelCase(x.$autoname);
                name = x.select_one
                  ? `${prefixes}${toCamelCase(x.select_from_list_name)}ID_${name}`
                  : name;
                return {
                  ...x,
                  name: `${name.split(/-/).join('_')}`,
                  findValue: x.select_one || x.type === 'select_multiple' || false,
                  required: x.required,
                };
              });

              const parentColumn =
                // questions[0].path.length > 1
                questions[0].depth > 1
                  ? `${questions[0].path.slice(-2, -1)[0]}_uuid`
                  : `${tableId}_uuid`;

              if (questions[0].depth > 0)
                form.push({ name: toCamelCase(parentColumn), type: 'text' });

              form.push(
                { name: 'AnswerId', type: 'text' },
                { name: toCamelCase(uuidColumnName), type: 'varchar(100)', unique: true }
              );

              return form;
            }

            function standardColumns(tableName) {
              // prettier-ignore
              return [
                // { name: `${prefix1}${tableName}ID`, type: 'int4', required: true, identity: true },
                // { name: `${prefix1}${tableName}Name`, type: 'varchar(255)', required: false },
                // { name: `${prefix1}${tableName}ExtCode`, type: 'varchar(50)', required: true, default: '' },
                { name: `${prefixes}${tableName}Code`, type: 'varchar(255)', required: false },
                { name: `${prefixes}${tableName}Description`, type: 'varchar(255)', required: false },
                { name: `${prefixes}OrganizationID_Owner`, type: 'int4', required: true, default: 1 },
                { name: `${prefixes}SecuritySettingID_Row`, type: 'int4', required: true, default: 1 },
                { name: 'Archive', type: 'BIT', required: true, default: '0' },
                { name: 'IsPublic', type: 'BIT', required: true, default: '0' },
                { name: 'CRDate', type: 'timestamp', required: true, default: 'NOW()' },
                { name: 'LMDate', type: 'timestamp', required: true, default: 'NOW()' },
                { name: 'UserID_CR', type: 'int4', required: true, default: -1 },
                { name: 'UserID_LM', type: 'int4', required: true, default: -1 },
                { name: 'CRIPAddress', type: 'varchar(32)', required: true, default: '' },
                { name: 'LMIPAddress', type: 'varchar(32)', required: true, default: '' },
              ];
            }

            function customColumns(tableName) {
              // prettier-ignore
              return [
                { name: `${prefixes}${tableName}ID`, type: 'int4', required: true, identity: true },
                { name: `${prefixes}${tableName}Name`, type: 'varchar(255)', required: false },
                { name: `${prefixes}${tableName}ExtCode`, type: 'varchar(50)', required: true, default: '' },
              ];
            }

            function processPath(question, i, arr) {
              let path = [];
              if (i === 0) {
                path = [];
              } else {
                let parent = arr.find(question => question.name === arr[i - 1].path[0]);
                if (parent && parent.type === 'begin_group') {
                  path = [[arr[i - 1].path, question.name].join('/')];
                } else {
                  path = i === 0 ? [] : [...arr[i - 1].path, question.name];
                }
              }
              return path;
            }

            function buildLookupTableColumns(prefixes, q, i, arr) {
              const path = processPath(q, i, arr);
              return [
                {
                  name: `${prefixes}${toCamelCase(q.select_from_list_name)}ID`,
                  type: 'int4',
                  identity: true,
                  required: q.required,
                  depth: path.length,
                  select_multiple: q.type === 'select_multiple' ? true : false,
                  path,
                  rule: 'DO_NOT_MAP',
                  parentColumn: q.name,
                },
                {
                  name: `${prefixes}${toCamelCase(q.select_from_list_name)}Name`,
                  type: 'varchar(100)',
                  required: q.required,
                  depth: path.length,
                  select_multiple: q.type === 'select_multiple' ? true : false,
                  path,
                  parentColumn: q.name,
                },
                {
                  name: `${prefixes}${toCamelCase(q.select_from_list_name)}ExtCode`,
                  type: 'varchar(100)',
                  required: q.required,
                  unique: true,
                  depth: q.type === 'select_multiple' ? 3 : 0,
                  select_multiple: q.type === 'select_multiple' ? true : false,
                  path: i === 0 ? [] : [...arr[i - 1].path, q.name],
                  parentColumn: q.name,
                },
              ];
            }

            // prettier-ignore
            function addLookupTable(tables, lookupTableName, prefixes, q, i, formName, arr) {
              tables.push({
                name: lookupTableName,
                columns: buildLookupTableColumns(prefixes, q, i, arr),
                defaultColumns: standardColumns(toCamelCase(q.select_from_list_name)),
                formName,
                depth: q.type === 'select_multiple' ? 1 : q.depth,
                lookupTable: q.type === 'select_multiple' ? true : undefined,
                select_from_list_name: toCamelCase(q.select_from_list_name),
                ReferenceUuid: `${prefixes}${toCamelCase(q.select_from_list_name)}ExtCode`,
              });
              tablesToBeCreated.push(lookupTableName)
            }

            function buildForeignTables(questions) {
              const foreignTables = [];
              questions.forEach(q => {
                if (q.select_one) {
                  foreignTables.push({
                    table: `${prefixes}${toCamelCase(q.select_from_list_name)}`,
                    id: `${prefixes}${toCamelCase(q.select_from_list_name)}ID`,
                    reference: `${prefixes}${toCamelCase(
                      q.select_from_list_name
                    )}ID_${toCamelCase(q.name)}`,
                  });
                }
              });
              return foreignTables;
            }

            function buildTablesFromSelect(questions, formName, tables) {
              questions.forEach((q, i, arr) => {
                if (q.type === 'select_multiple') {
                  multiSelectIds.push(q.name);
                  const getType = name => survey.find(s => s.name === name).type; // return the type of a question

                  let suffix = q.path.slice(-1)[0];
                  if (suffix && getType(suffix) === 'begin_group') suffix = undefined;

                  const lookupTableName = `${prefixes}${toCamelCase(
                    q.select_from_list_name
                  )}`;

                  const junctionTableName = `${prefixes}${toCamelCase(
                    suffix || tableId
                  )}${toCamelCase(q.select_from_list_name)}`;

                  // prettier-ignore
                  const parentTableName = `${prefixes}${tableId}${toCamelCase(suffix)}`;
                  // prettier-ignore
                  const parentTableReferenceColumn = `${prefixes}${toCamelCase(suffix || tableId)}ID`;

                  if (!tables.find(t => t.name === junctionTableName)) {
                    // console.log('junctiontable', junctionTableName);
                    const path = processPath(q, i, arr);
                    tables.push({
                      name: junctionTableName,
                      dependencies: 3,
                      columns: [
                        {
                          name: `${prefixes}${toCamelCase(q.select_from_list_name)}ID`,
                          type: 'select_multiple',
                          required: q.required,
                          referent: lookupTableName,
                          refersToLookup: true,
                          depth: path.length,
                          path,
                        },
                        {
                          name: parentTableReferenceColumn,
                          type: 'select_multiple',
                          required: q.required,
                          referent: parentTableName,
                          refersToLookup: false,
                          depth: path.length,
                          path,
                        },
                      ],
                      defaultColumns: [
                        // prettier-ignore
                        ...[
                          { name: `${prefixes}${toCamelCase(q.select_from_list_name)}Name`, type: 'varchar(255)', required: false },
                          { name: `${prefixes}${toCamelCase(q.select_from_list_name)}ExtCode`, type: 'varchar(50)', required: true, default: '' },
                        ],
                        ...standardColumns(toCamelCase(q.select_from_list_name)),
                      ],
                      foreignTables: [
                        {
                          table: lookupTableName,
                          id: `${lookupTableName}ID`,
                        },
                        {
                          table: parentTableName,
                          id: `${prefixes}${toCamelCase(suffix || tableId)}ID`,
                        },
                      ],
                      formName,
                      depth: 1,
                      select_multiple: true,
                      select_from_list_name: toCamelCase(q.select_from_list_name),
                    });
                    tablesToBeCreated.push(junctionTableName);
                  }
                }

                if (['select_one', 'select_multiple'].includes(q.type)) {
                  // Use list_name to name select_table
                  const lookupTableName = `${prefixes}${toCamelCase(
                    q.select_from_list_name
                  )}`;
                  if (!tablesToBeCreated.includes(lookupTableName)) {
                    // console.log('lookup', lookupTableName);
                    //prettier-ignore
                    addLookupTable(tables, lookupTableName, prefixes, q, i, formName, arr);
                  }
                }
              });
              return tables;
            }

            function tablesFromQuestions(questions, formName, tables) {
              const backwardsFirstBegin = questions
                .reverse()
                .findIndex(item => item.type === 'begin_repeat');

              const lastBegin =
                backwardsFirstBegin !== -1
                  ? questions.length - backwardsFirstBegin - 1
                  : false;

              const tName = `${prefixes}${tableId}`;

              if (lastBegin) {
                const firstEndAfterLastBegin =
                  questions
                    .reverse()
                    .slice(lastBegin)
                    .findIndex(item => item.type === 'end_repeat') + lastBegin;

                // Remove the deepest repeat group from the 'questions' array, parse it
                // and push it to the 'tables' array, and call tablesFromQuestions with
                // the remaining questions.
                const group = questions.splice(
                  lastBegin,
                  firstEndAfterLastBegin - lastBegin + 1
                );

                const tableName = toCamelCase(
                  group[0].path
                    .slice(-1)
                    .pop()
                    .split(/\s|-|'/)
                    .join('_')
                    .replace('.', '')
                );
                const name = `${prefixes}${tableId}${tableName}`;

                tables.push({
                  name,
                  dependencies: 2,
                  columns: questionsToColumns(
                    group.filter(q => q.type !== 'select_multiple')
                  ),
                  defaultColumns: [
                    // prettier-ignore
                    ...[ { name: `${tName}ID`, type: 'int4', required: false } ],
                    ...customColumns(tableName),
                    ...standardColumns(tableName),
                  ],
                  foreignTables: [
                    ...[
                      {
                        table: tName,
                        id: `${tName}ID`,
                      },
                    ],
                    ...buildForeignTables(group),
                  ],
                  formName,
                  depth: group[0].depth,
                });
                tablesToBeCreated.push(name);

                return tablesFromQuestions(questions, formName, tables);
              }

              tables.push(
                {
                  // This is the main table to hold submissions for this Kobo form.
                  name: tName,
                  dependencies: 1,
                  columns: [
                    // Note that we do not create columns for select multiple Qs. Answers
                    // to select multiple Qs will appear as records in a junction table.
                    ...questionsToColumns(
                      questions.filter(q => q.type !== 'select_multiple')
                    ),
                    ...[
                      {
                        name: 'Payload',
                        type: 'jsonb',
                        depth: 0,
                        path: [],
                      },
                    ],
                  ],
                  defaultColumns: [
                    ...customColumns(tableId),
                    ...standardColumns(tableId),
                  ],
                  foreignTables: buildForeignTables(questions),
                  formName,
                  depth: 0,
                },
                {
                  name: `${prefix1}_KoboDataset`,
                  // This is a table that must exist in all DBs that will hold submission data from any form.
                  columns: [
                    {
                      name: 'FormName',
                      type: 'text',
                      depth: 0,
                      path: [],
                    },
                    {
                      name: 'DatasetId',
                      type: 'varchar(100)',
                      depth: 0,
                      path: [],
                      unique: true,
                    },
                    {
                      name: 'LastUpdated',
                      type: 'timestamp',
                      depth: 0,
                      path: [],
                    },
                  ],
                  defaultColumns: [
                    {
                      name: `${prefix1}ID`,
                      type: 'int4',
                      required: true,
                      identity: true,
                    },
                    {
                      name: `${prefix1}Name`,
                      type: 'varchar(255)',
                      required: false,
                    },
                    {
                      name: `${prefix1}ExtCode`,
                      type: 'varchar(50)',
                      required: true,
                      default: '',
                    },
                    {
                      name: `${prefix1}Code`,
                      type: 'varchar(255)',
                      required: false,
                    },
                    {
                      name: `${prefix1}Description`,
                      type: 'varchar(255)',
                      required: false,
                    },
                    {
                      name: `${prefix1}OrganizationID_Owner`,
                      type: 'int4',
                      required: true,
                      default: 1,
                    },
                    {
                      name: `${prefix1}SecuritySettingID_Row`,
                      type: 'int4',
                      required: true,
                      default: 1,
                    },
                    { name: 'Archive', type: 'BIT', required: true, default: '0' },
                    { name: 'IsPublic', type: 'BIT', required: true, default: '0' },
                    {
                      name: 'CRDate',
                      type: 'timestamp',
                      required: true,
                      default: 'NOW()',
                    },
                    {
                      name: 'LMDate',
                      type: 'timestamp',
                      required: true,
                      default: 'NOW()',
                    },
                    { name: 'UserID_CR', type: 'int4', required: true, default: -1 },
                    { name: 'UserID_LM', type: 'int4', required: true, default: -1 },
                    {
                      name: 'CRIPAddress',
                      type: 'varchar(32)',
                      required: true,
                      default: '',
                    },
                    {
                      name: 'LMIPAddress',
                      type: 'varchar(32)',
                      required: true,
                      default: '',
                    },
                  ],
                  formName,
                  depth: 0,
                }
              );
              tablesToBeCreated.push(tName);

              return tables;
            }

            // We build a dictionary of different select_one/select_multiple questions
            // and the different values they hold ===================================
            function createSeeds(choicesArr) {
              const obj = {};

              choicesArr.forEach(c => {
                const table = `${prefixes}${toCamelCase(c.list_name)}`;
                if (!obj[table]) obj[table] = [];
                if (!obj[table].includes(c.name)) obj[table].push(c.name);
              });

              const arr = [];

              Object.keys(obj).forEach(table => {
                arr.push({
                  table: table,
                  externalId: `${table}ExtCode`,
                  records: [...obj[table]],
                });
              });

              return arr;
            }

            let depth = 0;

            survey.forEach((q, i, arr) => {
              switch (q.type) {
                case 'begin_group':
                  arr[i] = {
                    ...q,
                    depth,
                    path: i === 0 ? [] : [...arr[i - 1].path, q.name],
                  };
                  break;

                case 'begin_repeat':
                  depth++;
                  arr[i] = {
                    ...q,
                    depth,
                    path: i === 0 ? [] : [...arr[i - 1].path, q.name],
                  };
                  break;

                case 'end_repeat':
                  arr[i] = {
                    ...q,
                    depth,
                    path: i === 0 ? [] : [...arr[i - 1].path.slice(0, -1)],
                  };
                  depth--;
                  break;

                case 'end_group':
                  arr[i] = {
                    ...q,
                    depth,
                    path: i === 0 ? [] : [...arr[i - 1].path.slice(0, -1)],
                  };
                  break;

                default:
                  arr[i] = {
                    ...q,
                    depth,
                    path: i === 0 ? [] : [...arr[i - 1].path],
                  };
                  break;
              }
            });

            const seeds = createSeeds(choices);
            const lookupTables = buildTablesFromSelect(survey, state.data.name, []);
            let tables = tablesFromQuestions(survey, state.data.name, []).reverse();
            tables = lookupTables.concat(tables);

            // Given the initial input of a "Kobo form definition", we return...
            return {
              ...state,
              tableId, // this is unique per form and used to identify the main "submissions table" for the form
              tables, // this is a list of tables (main table, lookup tables, junction tables, etc.) to create in the db
              seeds, // this is a list of records (grouped by table) to insert at build time, not form submission time (runtime)
              prefix1, // this is a constant used in various places
              prefix2, // this is a constant used in various places
              prefixes, // this is `{prefix1}_{prefix2}`
              uuidColumnName, // this is a constant used identify unique ID columns in the db
              multiSelectIds, // this is an array of the 'list_name' of every select_multiple question
              data: {}, // we clear data
              response: {}, // we clear response
            };
          });

          // Sort the tables by dependencies so that we can create them in the correct order
          fn(state => ({
            ...state,
            tables: state.tables.sort((a, b) =>
              !b.hasOwnProperty('dependencies')
                ? 1
                : a.dependencies > b.dependencies
                ? 1
                : -1
            ),
          }));

          // Print out a "DROP STATEMENT" for each table in the list of tables.
          fn(state => {
            console.log('====================DROP STATEMENT====================');
            console.log('Use this to clean database from created tables...');

            const { tables } = state;

            const query = `DROP TABLE ${tables.map(t => t.name).reverse()};`;

            console.log(`query: ${query}`);
            console.log('====================END DROP STATEMENT====================');

            return state;
          });

      A4-Generate-OpenFn-Job-script-AUTO---OPENFN-JOB:
        name: A4 Generate OpenFn Job script AUTO - OPENFN JOB
        adaptor: '@openfn/language-openfn@v1.1.4'
        credential: null
        body: |
          // {
          //   ...state,
          //   tableId, // this is unique per form and used to identify the main "submissions table" for the form
          //   tables, // this is a list of tables (main table, lookup tables, junction tables, etc.) to create in the db
          //   seeds, // this is a list of records (grouped by table) to insert at build time, not form submission time (runtime)
          //   prefix1, // this is a constant used in various places
          //   prefix2, // this is a constant used in various places
          //   prefixes, // this is `{prefix1}_{prefix2}`
          //   uuidColumnName, // this is a constant used identify unique ID columns in the db
          //   multiSelectIds, // this is an array of the 'list_name' of every select_multiple question
          //   data: {}, // we clear data
          //   response: {}, // we clear response
          // };

          // Pluck projectId out of state for convenience, filter out tables that were populated at build time.
          fn(state => {
            const { projectId } = state.configuration;

            return {
              ...state,
              projectId,
              tables: state.tables
                .filter(t => !t.ReferenceUuid) // filter out tables that were seeded
                .filter(t => t.columns.length > 0) // filter out tables with no columns
                .filter(t => t.name !== `${state.prefixes}_Untitled`), // filter out bad test data
            };
          });

          fn(state => {
            // Create the first operation in our expression.
            var expression = `fn(state => {
            const multiSelectIds = ["${state.multiSelectIds.join('", "')}"];

            function generateUuid(body, uuid) {
              for (const property in body) {
                if (Array.isArray(body[property]) && body !== null) {
                  body['__generatedUuid'] = uuid;
                  body[property].forEach((thing, i, arr) => {
                    if (thing !== null) {
                      thing['__parentUuid'] = uuid;
                      let newUuid = uuid + '-' + (i + 1);
                      thing['__generatedUuid'] = newUuid;
                      for (const property in thing) {
                        if (Array.isArray(thing[property])) {
                          generateUuid(thing, newUuid);
                        }
                      }
                    }
                  });
                }
              }
            }

            generateUuid(
              state.data.body,
              state.data.body._id+'-'+state.data.body._xform_id_string
            );

            const { body } = state.data;
            const { _id, _xform_id_string } = body;
            state.data = { ...state.data, ...body };

            return { ...state, _id, _xform_id_string };
          }); \n`;

            function toCamelCase(str) {
              const words = str.split('_'); // we split using '_'. With regex we would use: "match(/[a-z]+/gi)"
              if (!words) return '';
              return words
                .map(word => {
                  return word.charAt(0).toUpperCase() + word.substr(1).toLowerCase();
                })
                .join('');
            }

            // Iterate through every table and create an operation to upsert (or upsertMany) records for that table.
            for (const table of state.tables) {
              const { columns, name, depth, select_multiple, lookupTable } = table;
              var paths = [];

              for (const column of columns) {
                // Handling master parent table
                if (name === `${state.prefix1}_KoboDataset`) {
                  const values = {
                    FormName: "dataValue('formName')",
                    DatasetId: "dataValue('_xform_id_string')",
                    LastUpdated: 'new Date().toISOString()',
                  };
                  for (x in values) paths.push(values[x]);
                  break;
                }
                // end of master parent table

                const currentPath = column.path;

                paths.push(
                  (currentPath && currentPath.length > 0
                    ? currentPath.join('/') + '/'
                    : '') + column.$autoname
                );
              }

              var mapKoboToPostgres = {}; // This is the jsonBody that should be given to our upsert

              // We generate findValue function (fn) for those that needs it.
              function generateFindValue(question, relation, searchAttr, searchVal) {
                const inferredUUid =
                  // if not junction, but yes select_one or many...
                  !question.referent && question.findValue
                    ? toCamelCase(
                        question.select_from_list_name.replace(`${state.tableId}_`, '')
                      )
                    : question.name;

                const suffixedUUid = !inferredUUid.endsWith('ID')
                  ? `${inferredUUid}ID`
                  : `${inferredUUid}`;

                const finalUUid = !suffixedUUid.startsWith(state.prefixes)
                  ? `${state.prefixes}${suffixedUUid}`
                  : suffixedUUid;

                searchAttr = searchAttr.replace('ID', '');

                if (question.referent) {
                  // if it's in a junction
                  if (question.refersToLookup) {
                    // and it refers to lookup
                    searchAttr = `${question.referent}ExtCode`;
                    searchVal = `x`;
                  }
                  // if it doesn't we don't touch it!
                } else {
                  searchAttr = !searchAttr.includes(state.prefixes)
                    ? `${state.prefixes}${searchAttr}ExtCode`
                    : `${searchAttr}ExtCode`;

                  searchVal =
                    question.depth > 0
                      ? `x['${searchVal}']`
                      : `dataValue('${searchVal}')`;
                }

                return `await findValue({uuid: '${finalUUid.toLowerCase()}', relation: '${relation.replace(
                  'ID',
                  ''
                )}', where: { ${searchAttr}: ${searchVal} }})(state)`;
              }

              // FROM HERE WE ARE BUILDING MAPPINGS
              columns.forEach((col, i) => {
                if (col.rule !== 'DO_NOT_MAP') {
                  if (col.findValue) {
                    mapKoboToPostgres[col.name] = generateFindValue(
                      col,
                      `${state.prefixes}${toCamelCase(col.select_from_list_name)}`,
                      `${toCamelCase(col.select_from_list_name)}`,
                      paths[i]
                    );
                  } else if (col.name === 'Latitude') {
                    mapKoboToPostgres[col.name] = `state => state.data.gps.split(' ')[0]`;
                  } else if (col.name === 'Longitude') {
                    mapKoboToPostgres[col.name] = `state => state.data.gps.split(' ')[1]`;
                  } else if (col.name === 'Payload') {
                    // Here we use an expression, rather than a function, to take the ======
                    // original, unaltered body of the Kobo submission as JSON.
                    mapKoboToPostgres.Payload = `state.data.body`;
                  } else if (col.referent) {
                    // If we see a referent, this is a column in a junction table.
                    if (col.refersToLookup) {
                      // If refersToLookup is true, then this column refers to the lookup table
                      mapKoboToPostgres[col.name] = generateFindValue(
                        col,
                        col.referent,
                        `${col.select_from_list_name}`,
                        'x'
                      );
                      // if If refersToLookup is false, this column refers to the main submission table
                      // TODO: Mamadou, please confirm the line below. Should it change to "findValue"?
                    } else {
                      mapKoboToPostgres[col.name] = generateFindValue(
                        col,
                        col.referent,
                        'AnswerId',
                        'state._id'
                      );
                    }
                  } else if (col.depth > 0) {
                    mapKoboToPostgres[col.name] = `x['${paths[i]}']`;
                  } else {
                    mapKoboToPostgres[col.name] =
                      name !== `${state.prefix1}_KoboDataset`
                        ? col.parentColumn
                          ? `dataValue('${col.path.join('/')}')`
                          : `dataValue('${paths[i]}')`
                        : `${paths[i]}`;
                  }

                  if (col.name === 'AnswerId') {
                    mapKoboToPostgres[col.name] = `state._id`;
                  }
                  if (col.name === 'GeneratedUuid') {
                    if (depth > 0) mapKoboToPostgres[col.name] = `x['__generatedUuid']`;
                    else mapKoboToPostgres[col.name] = `dataValue('__generatedUuid')`;
                  }
                }
              });

              // =====================================================================

              // We generate a mapping variable that we are going=======
              // to use inside our operation============================
              const mapObject = `const mapping = ${JSON.stringify(
                mapKoboToPostgres,
                null,
                2
              ).replace(/"/g, '')}`;
              // =======================================================

              // We build a set of statements for when depth > 0========
              const path = columns[0].path.join('/');

              let statements = null;
              // console.log('select', select_multiple);
              // console.log('name', depth);
              // if table is a table referencing a select multiple table.
              if (select_multiple || lookupTable) {
                statements = `if (state.data['${path}']) { \n
                          const array = state.data['${path}'].split(' '); \n
                          const mapping = []; \n 
                          for ( let x of array ) { \n
                            mapping.push(${JSON.stringify(
                              mapKoboToPostgres,
                              null,
                              2
                            ).replace(/"/g, '')}); \n
                          } \n
                      `;
              } else {
                //   statements = `if (state.data['${path}']) { \n
                //     const array = state.data['${path}'].split(' '); \n
                //     const mapping = []; \n
                //     for ( let x of array ) { \n
                //       mapping.push(${JSON.stringify(mapKoboToPostgres, null, 2).replace(
                //         /"/g,
                //         ''
                //       )}); \n
                //     } \n
                // }`;
                statements = `const dataArray = state.data.body['${path}'] || [] \n
                  const mapping = []; \n
                  for (let x of dataArray) { \n
                    mapping.push(${JSON.stringify(mapKoboToPostgres, null, 2).replace(
                      /"/g,
                      ''
                    )}) \n
                    }`;
              }
              // =======================================================

              const opFirstLineNoDepth = `fn(async state => {\n ${mapObject} \n`;
              const opFirstLineDepth = `fn(async state => {\n ${statements} \n`;
              // const alterSOpeningSelect = `fn(async state => {\n ${selectStatement} \n`;
              const opLastLine = `})`;

              function wrapper(column, mapping) {
                let prefix = '';
                const depth = column.depth;
                /*  if (select_multiple || lookupTable) {
                    prefix += mapping + `)(state); \n${alterSClosing} \n`;
                    return prefix;
                  } else */ if (depth > 1) {
                  // console.log('Im here');
                  let closingPar = 0; // hold how many brackets we need to close
                  for (var i = 0; i < depth - 1; i++) {
                    if (i === 0 && column.path[i]) {
                      // We generate "body.something" only for the first 'each'
                      prefix += `each('$.${column.path[i]}[*]', `;
                      closingPar++;
                    } else if (column.path[i]) {
                      prefix += `each(dataPath('${column.path[i]}[*]'), `;
                      closingPar++;
                    }
                  }
                  // prefix += mapping;
                  prefix +=
                    mapping +
                    (select_multiple || lookupTable
                      ? `)(state); } \n return state; \n${opLastLine} \n`
                      : `)(state); \n${opLastLine} \n`);
                  for (var i = 0; i < closingPar; i++) {
                    prefix += ')';
                  }

                  return prefix;
                }
                return mapping;
              }

              const operation = `return ${depth > 0 ? 'upsertMany' : 'upsert'}`;

              var uuid =
                name === `${state.prefix1}_KoboDataset`
                  ? '"DatasetId"'
                  : table.select_multiple
                  ? `["${columns[0].name}", "${columns[1].name}"]`
                  : `'${toCamelCase(state.uuidColumnName)}'`;

              let mapping =
                depth > 0 || select_multiple
                  ? `${opFirstLineDepth} ${operation}('${name}', ${uuid}, `
                  : `${opFirstLineNoDepth} ${operation}('${name}', ${uuid}, `;

              if (columns[0].depth > 0 || select_multiple) {
                mapping += `() => mapping, {setNull: ["''", "'undefined'"]}`;
              } else {
                mapping += `mapping, {setNull: ["''", "'undefined'"]}`;
              }
              // END OF BUILDING MAPPINGS (state)

              expression +=
                wrapper(columns[0], mapping) +
                (columns[0].depth > 1
                  ? '\n'
                  : select_multiple || lookupTable
                  ? `)(state); } \n return state; \n${opLastLine} \n`
                  : `)(state); \n${opLastLine} \n`);
            }

            state.triggerCriteria = {
              tableId: `${state.prefixes}${state.tableId}`,
            };

            return { ...state, expression };
          });

          // Get existing triggers for this project.
          fn(state => {
            return request(
              {
                method: 'get',
                path: 'triggers',
                params: {
                  project_id: state.projectId,
                },
              },
              next => ({ ...next, triggers: next.data })
            )(state);
          });

          // Get existing jobs for this project.
          fn(state => {
            return request(
              {
                method: 'get',
                path: 'jobs',
                params: {
                  project_id: state.projectId,
                },
              },
              next => ({ ...next, jobs: next.data.filter(job => !job.archived) })
            )(state);
          });

          // Create or update the trigger to detect submissions from this form.
          fn(state => {
            const { triggers, prefixes, tableId, triggerCriteria, projectId } = state;
            const triggerNames = triggers.map(t => t.name);

            const name = `auto/${prefixes}${tableId}`;
            const criteria = triggerCriteria;
            const triggerIndex = triggerNames.indexOf(name);

            const trigger = {
              project_id: projectId,
              name,
              type: 'message',
              criteria,
            };

            if (triggerIndex === -1) {
              console.log('Inserting trigger.');
              return request(
                {
                  method: 'post',
                  path: 'triggers',
                  data: { trigger },
                },
                next => ({ ...next, triggers: [...next.triggers, next.data] })
              )(state);
            }

            console.log('Trigger already existing.');
            return state;
          });

          // Create or update the job for handling submissions from this form.
          fn(state => {
            const { expression, prefixes, tableId, jobs, triggers, projectId } = state;

            console.log('Inserting/updating job: ', `auto/${prefixes}${tableId}`);

            const jobNames = jobs.map(j => j.name);
            const triggersName = triggers.map(t => t.name);
            const name = `auto/${prefixes}${tableId}`;
            const jobIndex = jobNames.indexOf(name); // We check if there is a job with that name.
            const triggerIndex = triggersName.indexOf(name);
            const triggerId = triggers[triggerIndex].id;

            const method = jobIndex !== -1 ? 'put' : 'post';
            const path = method === 'put' ? `jobs/${jobs[jobIndex].id}` : 'jobs/';

            const job = {
              adaptor: 'mssql',
              adaptor_version: 'v2.6.11',
              expression,
              name,
              project_id: projectId,
              trigger_id: triggerId, // we (1) create a trigger first; (2) get the id ; (3) assign it here!
            };

            return request({ method, path, data: { job } })(state);
          });

      A3-Generate-SQL-to-setup-DB-AUTO---MSSQL:
        name: A3 Generate SQL to setup DB AUTO - MSSQL
        adaptor: '@openfn/language-mssql@v3.0.0'
        credential: null
        body: |
          // Here we set default options for the SQL adaptor. Setting execute or writeSql
          // below will set the standard behavior of all SQL functions below unless overwritten.

          //SET execute: true  if you want to SQL script to be auto-executed in the DB linked to this job 
          //SET execute: false if you do NOT want to execure the SQL script, and only wnat to generate the script (see "writeSql")
          fn(state => ({ ...state, execute: false, writeSql: true }));

          // Creates tables in the db.
          each(
            '$.tables[*]',
            fn(state => {
              const { execute, writeSql } = state;
              const { name, defaultColumns } = state.data;

              function convertToMssqlTypes(col) {
                col.type = col.referent
                  ? 'int'
                  : col.type === 'select_one' ||
                    col.type === 'select_multiple' ||
                    col.type === 'text' ||
                    col.type === 'jsonb'
                  ? 'nvarchar(max)'
                  : col.type.includes('varchar')
                  ? col.type.replace('varchar', 'nvarchar')
                  : col.type === 'int4' || col.type === 'float4'
                  ? col.type.substring(0, col.type.length - 1)
                  : col.type === 'timestamp'
                  ? 'datetime'
                  : col.type;

                if (col.type === 'datetime') col.default = 'GETDATE()';
              }

              function insert(name, columns, execute, writeSql, state) {
                columns.forEach(col => convertToMssqlTypes(col));
                return insertTable(name, state => columns, {
                  writeSql,
                  execute,
                })(state).then(state => {
                  if (defaultColumns) {
                    let foreignKeyQueries = [];
                    if (state.data.foreignTables) {
                      const { foreignTables } = state.data;
                      for (let ft of foreignTables) {
                        const { table, id, reference } = ft;
                        foreignKeyQueries.push(`ALTER TABLE ${name} WITH CHECK ADD CONSTRAINT FK_${name}_${
                          reference ? reference : id
                        } FOREIGN KEY (${reference ? reference : id})
                        REFERENCES ${table} (${id});
                        ALTER TABLE ${name} CHECK CONSTRAINT FK_${name}_${
                          reference ? reference : id
                        };`);
                      }
                    }
                    // Creating foreign keys constraints to standard WCS DB and fields
                    return sql({
                      query: state =>
                        `ALTER TABLE ${name} WITH CHECK ADD CONSTRAINT FK_${name}_OrganizationID_Owner FOREIGN KEY (${
                          state.prefixes
                        }OrganizationID_Owner)
                      REFERENCES WCSPROGRAMS_Organization (WCSPROGRAMS_OrganizationID);
                      ALTER TABLE ${name} CHECK CONSTRAINT FK_${name}_OrganizationID_Owner;
                      ALTER TABLE ${name} WITH CHECK ADD CONSTRAINT FK_${name}_SecuritySettingID_Row FOREIGN KEY (${
                          state.prefixes
                        }SecuritySettingID_Row)
                      REFERENCES WCSPROGRAMS_SecuritySetting (WCSPROGRAMS_SecuritySettingID);
                      ALTER TABLE ${name} CHECK CONSTRAINT FK_${name}_SecuritySettingID_Row;
                      ${foreignKeyQueries.join('\n')}
                    `,
                      options: {
                        writeSql: true, // Keep to true to log query (otherwise make it false).
                        execute: false, // keep to false to not alter DB
                      },
                    })(state);
                  }
                  return state;
                });
              }

              function modify(name, newColumns, execute, writeSql, state) {
                if (newColumns && newColumns.length > 0) {
                  console.log('Existing table found in mssql --- Updating.');
                  // Note: Specify options here (e.g {writeSql: false, execute: true})
                  return modifyTable(name, state => newColumns, {
                    writeSql, // Keep to true to log query (otherwise make it false).
                    execute, // keep to false to not alter DB
                  })(state);
                } else {
                  console.log('No new columns to add.');
                  return state;
                }
              }

              if (name !== `${state.prefixes}_Untitled`) {
                let mergedColumns = state.data.columns;
                if (state.data.defaultColumns)
                  mergedColumns = [...state.data.columns, ...state.data.defaultColumns];

                return describeTable(name.toLowerCase(), {
                  writeSql: true, // Keep to true to log query.
                  execute, // Keep to true to execute query.
                })(state)
                  .then(resp => {
                    const { rows } = resp.response.body;
                    if (resp.response.body.rowCount === 0) {
                      console.log('No matching table found in mssql --- Inserting.');
                      const columns = mergedColumns.filter(x => x.name !== undefined);

                      // change this line to 'return insert(name, columns, true, writeSql, state);' to override 'execute: false' at top
                      return insert(name, columns, execute, writeSql, state);
                    } else {
                      const columnNames = rows.map(x => x.column_name.toLowerCase());

                      console.log('----------------------');
                      const newColumns = mergedColumns.filter(
                        x =>
                          x.name !== undefined &&
                          !columnNames.includes(x.name.toLowerCase())
                      );
                      newColumns.forEach(col => convertToMssqlTypes(col));
                      console.log(newColumns);

                      // change this line to 'return modify(name, newColumns, true, writeSql, state);' to override 'execute: false' at top
                      return modify(name, newColumns, execute, writeSql, state);
                    }
                  })
                  .catch(() => {
                    // If describeTable does NOT get executed because they've turned off execute,
                    // we should write the SQL for all the insert statements without executing them.
                    const columns = mergedColumns.filter(x => x.name !== undefined);
                    return insert(name, columns, execute, writeSql, state);
                  });
              }
              return state;
            })
          );

          // Adds "seeds" to the lookup tables—rows that can be referenced in submissions.
          each(
            '$.seeds[*]',
            fn(state => {
              const { writeSql, execute, data } = state;
              const { table, externalId, records } = data;
              return upsertMany(
                table, // table name
                externalId, // external ID column name
                state => {
                  // array of records to upsert
                  return records.map(r => ({
                    [externalId]: r,
                    [`${table}Name`]: r,
                  }));
                },
                { writeSql, execute, logValues: true } // options
              )(state);
            })
          );

          // Prints out SQL statements for manual inspection and work.
          fn(state => {
            console.log('----------------------');
            console.log('Logging queries.');
            for (query of state.queries) console.log(query);
            console.log('----------------------');

            return state;
          });

      A5-Prepare-Form-for-Data-Dictionary-AUTO---MSSQL:
        name: A5 Prepare Form for Data Dictionary AUTO - MSSQL
        adaptor: '@openfn/language-mssql@v2.6.4'
        credential: null
        body: |
          fn(state => {
            const KoboToolBox_Forms = [
              {
                name: 'form_name',
                type: 'nvarchar(100)',
              },
              {
                name: 'date_created',
                type: 'date',
              },
              {
                name: 'date_modified',
                type: 'date',
              },
              {
                name: 'form_owner',
                type: 'nvarchar(100)',
              },
              {
                name: 'languages',
                type: 'nvarchar(100)',
              },
              {
                name: 'form_id',
                type: 'nvarchar(100)',
                unique: true,
              },
              {
                name: 'form_group',
                type: 'nvarchar(100)',
              },
              {
                name: 'table_id',
                type: 'nvarchar(100)',
              },
            ];

            const KoboToolBox_Questions = [
              {
                name: 'question_id',
                type: 'nvarchar(100)',
                unique: true,
              },
              {
                name: 'form_id',
                type: 'nvarchar(100)',
              },
              {
                name: 'analytics_label',
                type: 'nvarchar(max)',
              },
              {
                name: 'question_name',
                type: 'nvarchar(max)',
              },
              {
                name: 'label',
                type: 'nvarchar(max)',
              },
              {
                name: 'question_type',
                type: 'nvarchar(100)',
              },
              {
                name: 'list_id',
                type: 'nvarchar(100)',
              },
              {
                name: 'question_constraint',
                type: 'nvarchar(max)',
              },
            ];

            const KoboToolBox_Choices = [
              {
                name: 'choice_id',
                type: 'nvarchar(100)',
                unique: true,
              },
              {
                name: 'list_id',
                type: 'nvarchar(100)',
              },
              {
                name: 'list_name',
                type: 'nvarchar(100)',
              },
              {
                name: 'choice_name',
                type: 'nvarchar(100)',
              },
              {
                name: 'choice_label',
                type: 'nvarchar(max)',
              },
              {
                name: 'form_uid',
                type: 'nvarchar(100)',
              },
            ];

            const MetadataForms = [
              {
                name: 'KoboToolBox_Forms',
                columns: KoboToolBox_Forms,
              },
              {
                name: 'KoboToolBox_Questions',
                columns: KoboToolBox_Questions,
              },
              {
                name: 'KoboToolBox_Choices',
                columns: KoboToolBox_Choices,
              },
            ];

            return { ...state, MetadataForms };
          });

          each(
            '$.MetadataForms[*]',
            fn(state => {
              const { name, columns } = state.data;
              
              function insert(name, columns, execute, writeSql, state) {
                columns.forEach(col =>
                  col.type === 'select_one' || col.type === 'select_multiple'
                    ? (col.type = 'nvarchar(max)')
                    : col.type
                );
                return insertTable(name, state => columns, {
                  writeSql,
                  execute,
                })(state);
              }

              function modify(name, newColumns, execute, writeSql, state) {
                newColumns.forEach(col =>
                  col.type === 'select_one' || col.type === 'select_multiple'
                    ? (col.type = 'nvarchar(max)')
                    : col.type
                );
                if (newColumns && newColumns.length > 0) {
                  console.log('Existing table found in mssql --- Updating.');
                  // Note: Specify options here (e.g {writeSql: false, execute: true})
                  return modifyTable(name, state => newColumns, {
                    writeSql, // Keep to true to log query (otherwise make it false).
                    execute, // keep to false to not alter DB
                  })(state);
                } else {
                  console.log('No new columns to add.');
                  return state;
                }
              }

              // Note: Specify options here
              const execute = false;
              const writeSql = true;

              return describeTable(name.toLowerCase(), {
                writeSql: true,
                execute,
              })(state)
                .then(mssqlColumn => {
                  const { rows } = mssqlColumn.response.body;
                  if (mssqlColumn.response.body.rowCount === 0) {
                    console.log('No matching table found in mssql --- Inserting.');

                    const cols = columns.filter(x => x.name !== undefined);
                    return insert(name, cols, execute, writeSql, state);
                  } else {
                    const columnNames = rows.map(x => x.column_name);

                    console.log('----------------------');
                    const newColumns = columns.filter(
                      x =>
                        x.name !== undefined &&
                        !columnNames.includes(x.name.toLowerCase())
                    );

                    console.log(newColumns);
                    return modify(name, newColumns, execute, writeSql, state);
                  }
                })
                .catch(() => {
                  return insert(name, columns, execute, writeSql, state);
                });
            })
          );

          fn(state => {
            const { openfnInboxUrl } = state.configuration;
            const data = {
              type: 'Form Definition',
              formDefinition: state.formDefinition,
              prefixes: state.prefixes,
              prefix2: state.prefix2,
              tableId: state.tableId,
            };
            console.log('Sending form definition to OpenFN inbox.');
            http.post({
              url: openfnInboxUrl,
              data,
              maxContentLength: Infinity,
              maxBodyLength: Infinity,
            })(state);

            return state;
          });

          fn(state => {
            console.log('----------------------');
            console.log('Logging queries.');
            for (query of state.queries) console.log(query);
            console.log('----------------------');
            return state;
          });

    triggers:
      webhook:
        type: webhook
        enabled: false
    edges:
      webhook->A2-Process-Forms-AUTO:
        source_trigger: webhook
        target_job: A2-Process-Forms-AUTO
        condition_type: js_expression
        condition_label: Form Changed
        condition_expression: |
          state.data.formUpdate == true
        enabled: true
      A2-Process-Forms-AUTO->A4-Generate-OpenFn-Job-script-AUTO---OPENFN-JOB:
        source_job: A2-Process-Forms-AUTO
        target_job: A4-Generate-OpenFn-Job-script-AUTO---OPENFN-JOB
        condition_type: on_job_success
        enabled: true
      A2-Process-Forms-AUTO->A3-Generate-SQL-to-setup-DB-AUTO---MSSQL:
        source_job: A2-Process-Forms-AUTO
        target_job: A3-Generate-SQL-to-setup-DB-AUTO---MSSQL
        condition_type: on_job_success
        enabled: true
      A2-Process-Forms-AUTO->A5-Prepare-Form-for-Data-Dictionary-AUTO---MSSQL:
        source_job: A2-Process-Forms-AUTO
        target_job: A5-Prepare-Form-for-Data-Dictionary-AUTO---MSSQL
        condition_type: on_job_success
        enabled: true