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

name: social-sciences
description: |
  Workflows for WCS & Partners in Social Sciences

collections: null
credentials:
  aisha@openfn.org-AK-Asana:
    name: AK Asana
    owner: aisha@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:
  "1A.-Get-BNS-FormsList-(Ongoing)":
    name: '1A. Get BNS FormsList (Ongoing)'
    jobs:
      BNS-1A-1-Get-FormsList-Ongoing:
        name: BNS 1A 1 Get FormsList Ongoing
        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;
          });

      BNS-1A-Get-BNS-NRGT-Forms-Ongoing:
        name: BNS 1A Get BNS NRGT Forms Ongoing
        adaptor: '@openfn/language-http@6.1.0'
        credential: rediet@openfn.org-WCS-Kobo-Raw
        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(
          //     state => state.configuration.openfnInboxUrl,
          //     state => {
          //       const {i, formName} = state.data;
          //       const count = state.surveySubmissions.length;
          //       console.log(`Posting ${i} of ${count} from ${formName}`);
          //       return { body: state.data };
          //     }
          //   )
          // );  

      Trigger-WF-Inbox:
        name: Trigger WF Inbox
        adaptor: '@openfn/language-http@latest'
        credential: null
        body: |
          // Check out the Job Writing Guide for help getting started:
          // https://docs.openfn.org/documentation/jobs/job-writing-guide
          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->BNS-1A-1-Get-FormsList-Ongoing:
        source_trigger: cron
        target_job: BNS-1A-1-Get-FormsList-Ongoing
        condition_type: always
        enabled: true
      BNS-1A-1-Get-FormsList-Ongoing->BNS-1A-Get-BNS-NRGT-Forms-Ongoing:
        source_job: BNS-1A-1-Get-FormsList-Ongoing
        target_job: BNS-1A-Get-BNS-NRGT-Forms-Ongoing
        condition_type: on_job_success
        enabled: true
      BNS-1A-Get-BNS-NRGT-Forms-Ongoing->Trigger-WF-Inbox:
        source_job: BNS-1A-Get-BNS-NRGT-Forms-Ongoing
        target_job: Trigger-WF-Inbox
        condition_type: on_job_success
        enabled: true
  1B.-Get-BNS-FormsList-Historical-sync:
    name: 1B. Get BNS FormsList Historical sync
    jobs:
      BNS-1B-1-Get-FormsList-Historical-sync:
        name: BNS-1B 1 Get FormsList Historical sync
        adaptor: '@openfn/language-googlesheets@2.3.0'
        credential: rediet@openfn.org-Rediet-Googlesheet
        body: |
          getValues(
            '1xexwj6HKJGHJM-sOzIOGqPHolgpdnp0GmjMIXwoV7OE',
            '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(
            '1xexwj6HKJGHJM-sOzIOGqPHolgpdnp0GmjMIXwoV7OE',
            '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-BNS-NRGT-Forms-Historical:
        name: Get BNS NRGT Forms Historical
        adaptor: '@openfn/language-http@3.1.11'
        credential: rediet@openfn.org-WCS-Kobo-Raw
        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:
      cron:
        type: cron
        cron_expression: '0 22 1 * *'
        enabled: false
    edges:
      BNS-1B-1-Get-FormsList-Historical-sync->Get-BNS-NRGT-Forms-Historical:
        source_job: BNS-1B-1-Get-FormsList-Historical-sync
        target_job: Get-BNS-NRGT-Forms-Historical
        condition_type: on_job_success
        enabled: true
      cron->BNS-1B-1-Get-FormsList-Historical-sync:
        source_trigger: cron
        target_job: BNS-1B-1-Get-FormsList-Historical-sync
        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@latest'
        credential: null
        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.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.body.form == "bns_survey" && state.data.body.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.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
  3.-Get-all-BNS-Kobo-forms-on-demand:
    name: 3. Get all BNS Kobo forms on-demand
    jobs:
      BNS-3-Get-all-Kobo-forms-on-demand:
        name: BNS-3 Get all Kobo forms on-demand
        adaptor: '@openfn/language-http@3.1.11'
        credential: rediet@openfn.org-WCS-Kobo-Raw
        body: |
          //== Run this job on-demand to get ALL submissions for any Kobo form ==//

          alterState(state => {
            console.log(`Started at: ${new Date().toISOString()}`);
            state.submissions = [];
            state.data = {
              surveys: [
                //** Specify new forms to fetch here **//
                //** Tag options: bns_survey, bns_price, nrgt_current, nrgt_historical  **//
                // Historical  forms to migrate:
                //Make sure the tag matches the Trigger of the related Job

                //******* OPENFN TEST FORMS ******///
                // { id: 'aijj9FSpuCzMTRnMPGike8', tag: 'nrgt_historical', name: 'NRGT Makira 2017 - Test OpenFn', owner: 'bns' }, // TEST
                // { id: 'av4hQ37rMgwFoh9ogjeyKh', tag: 'nrgt_current', name: 'NRGT 2019 - Test OpenFn', owner: 'bns' }, // TEST
                // { id: 'azrSYMFpj3M8jdFkApT3o6/', tag: 'bns_price', name: 'BNS Prix Ndoki 2019 - Test OpenFn', owner: 'bns' }, // TEST
                { id: 'aUrUbD6C9hB3y8XjfQ9CLc', tag: 'bns_survey', name: 'BNS Ndoki 2019 - OpenFn Test', owner: 'bns' }, // TEST
                //****** Ongoing Forms to Rerun ****//
                //TODO: need to uncomment
                // {id: 'aJn5HK9fFFQt2BMUo5GW2s', tag: 'bns_survey', name: 'BNS menages Lac Tele 2024', owner: 'wcs_lactele', instance: 'WCS Congo unpublished data 2024' },


                //******* WCS HISTORICAL FORMS ******///

            // BNS HH 2024
            ////  { id: 'aEjwpqLhg4CQbPRuFQMCFj', tag: 'bns_survey', name: 'BNS household Yankari 2024', owner: 'wcs_yankari', instance: 'WCS Nigeria unpublished data 2024' }, // Added manually by DD on Aug 21, 2024
               // { id: 'aGfcUnks7JLms4YCGbHgwX', tag: 'bns_survey', name: 'BNS intervention Cross River 2023', owner: 'cemogor', instance: 'C. Emogor, 2024' },// 
               // { id: 'aJrXKdPgXfwSnam5iwaRHq', tag: 'bns_survey', name: 'BNS Ciblage 4e cohorte 2024', owner: 'wcs_poultry', instance: 'A. M. Boucka, A. Nkounkou, WCS Congo unpublished data 2024' },// New landscape: NdokiPeriphery

            // BNS Prices 2024
            ////  { id: 'aKh9KWTrqwXsjVfHeJG9sL', tag: 'bns_price', name: 'BNS Prices Yankari 2024', owner: 'wcs_yankari', instance: 'WCS Nigeria unpublished data 2024' }, // Added manually by DD on Aug 21, 2024

            // NRGT 2024
            //{ id: 'aKYgSnUiLd8KkevPV2ty6e', tag: 'nrgt_current', name: 'NRGT NW Janvier 2024', owner: 'bemahafaly_wcs', instance: 'WCS Madagascar unpublished data 2024'}, 


            // BNS HH 2023
               // { id: 'aNznavxK7BXXgtvLVY6wm2', tag: 'bns_survey', name: 'BNS household Crossriver 2023', owner: 'wcs_crossriver', instance: 'WCS Nigeria unpublished data 2023' }, 
               // { id: 'a5jucWV84nFeP3BeHtbRMU', tag: 'bns_survey', name: 'BNS Menage PNMD 2023', owner: 'wcs_paapnmd', instance: 'WCS Cameroon unpublished data 2023' }, 
               // { id: 'aJVZmRRfj442Li5F6r6M8y', tag: 'bns_survey', name: 'BNS ménage Kahuzi 2023', owner: 'wcs_mtkb', instance: 'WCS DRC unpublished data 2023' }, 
               // { id: 'aK3MS8ATY53KXsKCm7xzpY', tag: 'bns_survey', name: 'BNS Ituri : Enquête Ménages 2023', owner: 'wcs_ituri', instance: 'WCS DRC unpublished data 2023' }, 
               // { id: 'a9SgR3L9Vzn8CC5UPAa2ou', tag: 'bns_survey', name: 'BNS EPP 2e/3e cohortes 2023', owner: 'wcs_poultry', instance: 'WCS Congo unpublished data 2023' }, 
               // { id: 'aAGC9q7nwXPnVLP6bFNAEw', tag: 'bns_survey', name: 'BNS_Individual_Niassa_2023', owner: 'wcs_niassa', instance: 'WCS Niassa unpublished data 2023' }, 
                
            // BNS Prices 2023
               // { id: 'aQ5Q6iarRWtKp2dY2okbDe', tag: 'bns_price', name: 'BNS Prices Crossriver 2023',  owner: 'wcs_crossriver', instance: 'WCS Nigeria unpublished data 2023' }, 
               // { id: 'aKVvMRrvTTCc8j7d3EDh7a', tag: 'bns_price', name: 'BNS Prix PNMD 2023',  owner: 'wcs_paapnmd', instance: 'WCS Cameroon unpublished data 2023' }, 
               // { id: 'a4oeehbiGuXrEWK7rkUv82', tag: 'bns_price', name: 'BNS Prix Ituri 2023', owner: 'wcs_ituri', instance: 'WCS DRC unpublished data 2023' }, 
               // { id: 'aH2dvuU2G8wiVZQ3k4fiRE', tag: 'bns_price', name: 'BNS Prix Kabobo 2023', owner: 'wcs_pcbk', instance: 'WCS DRC unpublished data 2023' }, 
               // { id: 'a3kbAt2freW3q8Ht48V3q2', tag: 'bns_price', name: 'BNS Prix Kahuzi 2023', owner: 'wcs_mtkb', instance: 'WCS DRC unpublished data 2023' }, 
               // { id: 'aAKdquWgPSLjzB3UgGBcsW', tag: 'bns_price', name: 'BNS_Precos_Niassa_2023', owner: 'wcs_niassa', instance: 'WCS Niassa unpublished data 2023'}, 
            
            // NRGT 2023
               // { id: 'aqDKwe8AD3ykFeNEDuLSnv', tag: 'nrgt_current', name: 'Nosy Be NRGT 2023', owner: 'wcs_library', instance: 'WCS Madagascar unpublished data 2023' }, 
               // { id: 'aDEEXL9fXQhZXpdaKPnrcJ', tag: 'nrgt_current', name: 'NRGT_Makira_2023_revisé', owner: 'wcs_mamabaie', instance: 'WCS Madagascar unpublished data 2023'},      // { id: 'aqDKwe8AD3ykFeNEDuLSnv',  tag: 'nrgt_current', name: 'Nosy Be NRGT 2023', owner: 'wcs_library', instance: 'WCS Madagascar unpublished data 2023' }, 
               // { id: 'a33XvMuPQLpeygLURuNUBP', tag: 'nrgt_current', name: 'NRGT SWM 2023', owner: 'wcs_ndoki', instance: 'WCS Congo unpublished data 2023' }, //May 2023
               // { id: 'a8KBiBL44hEpNfkS4RmxeN', tag: 'nrgt_current', name: 'NRGT_Niassa_2023', owner: 'wcs_niassa', instance: 'WCS Niassa unpublished data 2023' }, 
                    
            // BNS HH 2022
               // { id: 'aXf5DPR25YsB8f3mXS7LDh', tag: 'bns_survey', name: 'BNS household Crossriver 2022', owner: 'wcs_crossriver', instance: 'WCS Cross River unpublished data 2022'}, 
               // { id: 'aH2cMdEFcpwjmtBpz7TnyH', tag: 'bns_survey', name: 'BNS_Nosy Be_2022', owner: 'wcs_soariake', instance: 'S. Rakotoharimalala, R. Ranaivoson, C. Razafindrakoto, D. Detoeuf, WCS Madagascar 2022'},
               // { id: 'aGT9DSs6zf6q54okHR6UxY', tag: 'bns_survey', name: 'BNS_ABS_2022', owner: 'wcs_antongil', instance: 'S. Rakotoharimalala, R. Ranaivoson, C. Razafindrakoto, D. Detoeuf, WCS Madagascar 2022'}, 
               // { id: 'aGKL3jhaGpvfCP7ekPNyr4', tag: 'bns_survey', name: 'BNS_Ankarea/Ankivonjy_2022', owner: 'wcs_soariake', instance: 'S. Rakotoharimalala, R. Ranaivoson, C. Razafindrakoto, D. Detoeuf, WCS Madagascar 2022'}, 
               // { id: 'a5MyeTAhZ5WHadabcNVMcU', tag: 'bns_survey', name: 'BNS_Soariake_2022_FINAL', owner: 'wcs_soariake', instance: 'S. Rakotoharimalala, R. Ranaivoson, C. Razafindrakoto, D. Detoeuf, WCS Madagascar 2022' }, 
               // { id: 'aXc8nMwPbqrKMDqrBPu4LW', tag: 'bns_survey', name: 'hunter BNS', owner: 'cemogor', instance: 'C. Emogor unpublished data 2022' },  // synced Jan 2023
               // { id: 'aRnpV9xNVcbqLPbmoKn9sR', tag: 'bns_survey', name: 'BNS NDOKI 2022', owner: 'wcs_ndoki', instance: 'SWM Ndoki unpublished data 2022' }, // synced 18 March 2022
               // { id: 'aF9PF9YUE5yBVsUvWUr2pV', tag: 'bns_survey', name: 'BNS_Individual_Niassa_2022', owner: 'wcs_niassa', instance: 'Niassa Special Reserve unpublished data 2022' }, // synced 18 April 2022 
               // { id: 'aDvmfKGNq6H2yhcMTbP5tB', tag: 'bns_survey', name: 'BNS ménage Kahuzi 2022', owner: 'wcs_mtkb', instance: 'Kahuzi Biega National Park unpublished data 2022' }, //resynced August 2022
               // { id: 'aLJLeHSYsN7DCLQmmYJR8w', tag: 'bns_survey', name: 'BNS EPP Poulet 2022-2023', owner: 'wcs_poultry', instance: 'WCS Congo - Environmental Partnership Program, Livelihood diversification and poultry production - unpublished data 2022' }, // Synced Sept 22, 2022
                
            // BNS Prices 2022
               // { id: 'aGBARLZxAd9zYZ37S8DZwj', tag: 'bns_price', name: 'BNS Prices Crossriver 2022', owner: 'wcs_crossriver', instance: 'WCS Cross River unpublished data 2022' }, 
               // { id: 'aBEqtVJto8GjzfgBzJBAis', tag: 'bns_price', name: 'Prix_BNS_Nosy Be_2022', owner: 'wcs_soariake', instance: 'S. Rakotoharimalala, R. Ranaivoson, C. Razafindrakoto, D. Detoeuf, WCS Madagascar 2022'}, 
               // { id: 'aLa2L2dNrkhceAsp2AWD4A', tag: 'bns_price', name: 'Prix_BNS_ABS_2022', owner: 'wcs_antongil', instance: 'S. Rakotoharimalala, R. Ranaivoson, C. Razafindrakoto, D. Detoeuf, WCS Madagascar 2022'}, 
               // { id: 'aSuJPLgRj4vTA8gMmDyKJK', tag: 'bns_price', name: 'Prix_BNS_Ankarea/Ankivony_2022', owner: 'wcs_soariake', instance: 'S. Rakotoharimalala, R. Ranaivoson, C. Razafindrakoto, D. Detoeuf, WCS Madagascar 2022'}, 
               // { id: 'a4pMJRa3jn264kvVSv3vm7', tag: 'bns_price', name: 'Prix_BNS_Soariake_2022', owner: 'wcs_soariake', instance: 'S. Rakotoharimalala, R. Ranaivoson, C. Razafindrakoto, D. Detoeuf, WCS Madagascar 2022' }, 
               // { id: 'aKTbms2Fw6fa2XS3rKMwxv', tag: 'bns_price', name: 'BNS Ndoki Prix 2022', owner: 'wcs_ndoki', instance: 'SWM Ndoki unpublished data 2022' }, // synced 18 March 2022
               // { id: 'aZgCs6vmSVdDMmYWoW9hfe', tag: 'bns_price', name: 'BNS_Precos_Niassa_2022', owner: 'wcs_niassa', instance: 'Niassa Special Reserve unpublished data 2022' }, // synced 18 April 2022 
               // { id: 'aaCTf3buZnjjQDu9wmyACF', tag: 'bns_price', name: 'BNS Prix Kahuzi 2022', owner: 'wcs_mtkb', instance: 'Kahuzi Biega National Park unpublished data 2022' }, //resynced August 2022 
             
            // NRGT 2022
               // { id: 'amaXEkoh4eNcKyjjys8jGG', tag: 'nrgt_current', name: 'NRGT Crossriver 2022', owner: 'wcs_crossriver', instance: 'WCS Cross River unpublished data 2022'}, 
               // { id: 'aXFnVax8EugC22oRJAnWoV', tag: 'nrgt_current', name: 'NRGT Kahuzi 2022', owner: 'wcs_mtkb', instance: 'A. Twendilonge, F. Kavuba, WCS Kahuzi unpublished data 2022' }, // resynced 2nd Nov 2022
               // { id: 'aBfgRPninKvZEtfpMMqchu', tag: 'nrgt_current', name: 'NRGT Ituri 2022', owner: 'wcs_ituri', instance: 'B. Ikati, D. Bilua, S. Ahasa, WCS Ituri unpublished data 2022' }, // synced Oct 2022
               // { id: 'aMBGNEH6BzFVjxSZ2zHegc', tag: 'nrgt_current', name: 'NRGT_Niassa_2022', owner: 'wcs_niassa', instance: 'Niassa Special Reserve unpublished data 2022'}, // synced Sept 21, 2022,
                
            // BNS HH 2021 
               // { id: 'aEMeCB6j4BfUVDF4FrGuKw', tag: 'bns_survey', name: 'Conso+BNS Bismarck 2021', owner: 'wcs_bismarck', instance: 'WCS Bismarck unpublished data 2021'  }, // synced August 2022
               // { id: 'aSgFKK6Ufq7a4qXDe5yshg', tag: 'bns_survey', name: 'BNS Ituri : Enquête Ménages 2021', owner: 'wcs_ituri', instance: 'WCS Ituri unpublished data 2021'  }, // synced Feb 24 2022
               // { id: 'aokQtdTGTnLW6omXRrf4ss', tag: 'bns_survey', name: 'BNS menages Lac Tele 2021', owner: 'wcs_lactele', instance: 'G. Bondeko, O. Mbala, L. Molouagna, R. Silaho. , WCS Lac Télé unpublished data 2021'  }, // synced August 2022
               // { id: 'aLe9gkcRNXbtNHF6m8tNn8', tag: 'bns_survey', name: 'Socio-economic survey-EU', owner: 'wcs_ug_eu', instance: 'M. Nyago, P. Hatanga, H. Musabende, S. Nampindo, S. Amoko, M. Busiinge, WCS Uganda unpublished data 2021'  }, // synced Feb 24 2022
               // { id: 'aVLz2FxFcw99cv89xNY46K', tag: 'bns_survey', name: 'BNS Makira 2021', owner: 'wcs_mamabaie', instance: 'C. Spira, C. Milina, WCS Madagascar unpublished data 2021' }, // synced Feb 24 2022

            // BNS Prices 2021
               // { id: 'atKMA7EXQWRKYTUVAi3JgZ', tag: 'bns_price', name: 'BNS Price Bismarck 2021', owner: 'wcs_bismarck', instance: 'WCS Bismarck unpublished data 2021'}, // synced August 2023
               // { id: 'arwWLVDnQkJkNV4HtzgmeX', tag: 'bns_price', name: 'BNS Prix Ituri 2021', owner: 'wcs_ituri', instance: 'WCS Ituri unpublished data 2021'}, // synced Feb 24 2022
               // { id: 'aKZCAWsMgUkJDcYKv2Dern', tag: 'bns_price', name: 'BNS Prix Lac Télé 2021', owner: 'wcs_lactele', instance: 'G. Bondeko, O. Mbala, L. Molouagna, R. Silaho. , WCS Lac Télé unpublished data 2021'},//rsynced Feb 24 2022
               // { id: 'aVcvLwLaG9ZCnQ9b7ACc2h', tag: 'bns_price', name: 'Prix Makira 2021', owner: 'wcs_mamabaie', instance: 'C. Spira, C. Milina, WCS Madagascar unpublished data 2021'}, // synced Feb 24 2022

            // NRGT 2021
               // { id: 'atB3SwmsxjhoYppdTiCxEw', tag: 'nrgt_current', name: 'NRGT Makira 2021', owner: 'wcs_mamabaie', instance:'WCS Madagascar unpublished data 2021'}, // synced Sept 23, 2022

            // BNS HH 2020
               // { id: 'aGUVyBVFK8eYiRZhKanq3V', tag: 'bns_survey', name: 'BNS Makira 2020 - cacao', owner: 'wcs_mamabaie', instance: 'WCS Madagascar unpublished data 2020' }, 
               // { id: 'ad7S4hPBN7qM4Ac3mpEdwZ', tag: 'bns_survey', name: 'BNS_Nosy Be_2020', owner: 'wcs_soariake', instance: 'S. Rakotoharimalala, R. Ranaivoson, C. Razafindrakoto, D. Detoeuf, C. Spira, WCS Madagascar unpublished data 2020' }, // resynced December 2021
               // { id: 'atyo55YdBdfxzXiaBdrbvr', tag: 'bns_survey', name: 'BNS Ndoki Parc 2020', owner: 'wcs_ndoki', instance: 'Y. Londza & Nouabalé-Ndoki National Park team' }, // resynced December 2021
               // { id: 'ar9wXnLW2sdaamGgJsUrjP', tag: 'bns_survey', name: 'Socio-Eco Uganda 2020', owner: 'wcs_uganda_carbon', instance: 'M. Nyago, H. Musabende & WCS Uganda Carbon team'}, // resynced December 2021
               // { id: 'amD3cUsR4Jurj3ZSUyQdBH', tag: 'bns_survey', name: 'BNS Cross River 2017 - 2020 ', owner: 'wcs_crossriver', instance: 'I. Imong, J. Ntui, O. Okagbare, S. Ova, L. Nkonyu, WCS Nigeria unpublished data 2017-2020'}, // resynced December 2021
               // { id: 'aPH34CUc7zGbzeowRALdTu', tag: 'bns_survey', name: 'Basic Necessity Survey Cross River', owner: 'cemogor', instance: 'C. Emogor unpublished data 2020'}, // Synced Sept 22, 2022
                
            // BNS Prices 2020
               // { id: 'aj67aaDZa52oLBFPVGWWwu', tag: 'bns_price', name: 'Prix_BNS_Nosy Be_2020', owner: 'wcs_soariake', instance: 'S. Rakotoharimalala, R. Ranaivoson, C. Razafindrakoto, D. Detoeuf, C. Spira, WCS Madagascar unpublished data 2020' }, // resynced December 2021 
               // { id: 'aTRKQW2b8TJGxF7DVPfjFv', tag: 'bns_price', name: 'BNS Ndoki Prix 2020', owner: 'wcs_ndoki', instance: 'Y. Londza & Nouabalé-Ndoki National Park team' }, // resynced December 2021

            // NRGT 2020
               // { id: 'axSXT4r6TkLxnr3CBggmzg', tag: 'nrgt_current', name: 'NRGT Ndoki 2020', owner: 'wcs_ndoki', instance: 'Y. Londza, WCS Congo unpublished data 2020' }, // synced Sept 07, 2021
                
            // BNS HH 2019
               // { id: 'ahz5DN45juUzp7eUfGS5QA', tag: 'bns_survey', name: 'BNS SWM Ndoki 2019', owner: 'wcs_ndoki', instance: 'G. Mavah, B. Avelino, G. Ngohouani, R. Mouanda, F. Mossoula, B. Ngampamou' }, // resynced December 2021
               // { id: 'acK4WZ2ueqk8NvUdwctxz8', tag: 'bns_survey', name: 'BNS_Soariake_2019', owner: 'wcs_soariake', instance: 'S. Rakotoharimalala, R. Ranaivoson, C. Razafindrakoto, D. Detoeuf, C. Spira, WCS Madagascar unpublished data 2019' }, // resynced December 2021
               // { id: 'auPGpyWbn4PhTuWFBfKYES', tag: 'bns_survey', name: 'BNS_ABS_2019_FINAL', owner: 'wcs_antongil', instance: 'S. Rakotoharimalala, R. Ranaivoson, C. Razafindrakoto, D. Detoeuf, C. Spira, WCS Madagascar unpublished data 2019' }, // resynced December 2021
               // { id: 'azepksQ62i8vETEzUfz8jj', tag: 'bns_survey', name: 'BNS_Ankarea/Ankivonjy_2019', owner: 'wcs_soariake', instance: 'S. Rakotoharimalala, R. Ranaivoson, C. Razafindrakoto, D. Detoeuf, C. Spira, WCS Madagascar unpublished data 2019' }, // resynced August 2022
               // { id: 'ahz5DN45juUzp7eUfGS5QA', tag: 'bns_survey', name: 'BNS Ndoki 2019', owner: 'wcs_ndoki', instance: 'G. Mavah, B. Avelino, G. Ngohouani, R. Mouanda, F. Mossoula, B. Ngampamou, WCS Congo unpublished data 2019' }, // resynced December 2021
               // { id: 'aCShrrKNApccvaAPzxzbxK', tag: 'bns_survey', name: 'BNS ménage Kahuzi 2019', owner: 'wcs_mtkb', instance: 'F. Kavuba, A. Twendilonge, R. Cito, WCS RDC unpublished data 2019' }, // resynced December 2021
               // { id: 'arJkDGmkhhCiJ2eYwRcCit', tag: 'bns_survey', name: 'BNS Makira 2019', owner: 'wcs_mamabay', instance: 'C. Spira, N. Dokolahy, J. Ranariniaina, M. Cournarie, L. Andriamampianina,  D. Detoeuf, WCS Madagascar unpublished data 2019' }, // resynced December 2021
               // { id: 'aFQWAYHXXfh8i2cmXw9BFi', tag: 'bns_survey', name: 'BNS Ituri: Enquête Ménages 2019', owner: 'wcs_ituri', instance: 'B. Ntumba, A. Ohole, B. Ikati, T. Muller, WCS RDC unpublished data 2019' }, // resynced December 2021

            // BNS Prices 2019
               // { id: 'aq5r9cKQYBRDT9SBqYanUP', tag: 'bns_price', name: 'BNS SWM Prix Ndoki 2019', owner: 'wcs_ndoki', citation: "G. Mavah, B. Avelino, G. Ngohouani, R. Mouanda, F. Mossoula, B. Ngampamou" }, // resynced January 2022
               // { id: 'aApqbThMPaMJhczK2QKVLD', tag: 'bns_price', name: 'BNS Prix Kahuzi 2019', owner: 'wcs_mtkb', instance: 'F. Kavuba, A. Twendilonge, R. Cito, WCS RDC unpublished data 2019' }, // resynced January 2022
               // { id: 'aJZxvpgS73vJu4NUxTtvwJ', tag: 'bns_price', name: 'Prix_BNS_ABS_2019', owner: 'wcs_antongil', instance: 'S. Rakotoharimalala, R. Ranaivoson, C. Razafindrakoto, D. Detoeuf, C. Spira, WCS Madagascar unpublished data 2019' }, // resynced January 2022
               // { id: 'av3SpGmYTBP9A6dLMbzhZR', tag: 'bns_price', name: 'Prix_BNS_Soariake_2019', owner: 'wcs_soariake', instance: 'S. Rakotoharimalala, R. Ranaivoson, C. Razafindrakoto, D. Detoeuf, C. Spira, WCS Madagascar unpublished data 2019' }, // resynced January 2022
               // { id: 'awAV28ebngN7GTV2nqmyKU', tag: 'bns_price', name: 'Price Makira 2019', owner: 'wcs_mamabaie', instance: 'C. Spira, N. Dokolahy, J. Ranariniaina, M. Cournarie, L. Andriamampianina,  D. Detoeuf, WCS Madagascar unpublished data 2019'  }, // resynced January 2022
               // { id: 'awQmCEf63g5KN2G4kcBWrc', tag: 'bns_price', name: 'BNS Prix Ituri 2019', owner: 'wcs_ituri', instance: 'B. Ntumba, A. Ohole, B. Ikati, T. Muller, WCS RDC unpublished data 2019' }, // resynced January 2022
               // { id: 'a2bwTreEbymbWD3JGJ2qXT', tag: 'bns_price', name: 'Prix_BNS_Ankarea/Ankivony_2019', owner: 'wcs_soariake', instance: 'S. Rakotoharimalala, R. Ranaivoson, C. Razafindrakoto, D. Detoeuf, C. Spira, WCS Madagascar unpublished data 2019' }, // resynced August 2022

            // NRGT 2019
               // { id: 'aZZV4KikgRKz79LqqQR5Ma', tag: 'nrgt_current', name: 'NRGT Ituri 2019', owner: 'wcs_ituri', instance: 'B. Ntumba, A. Ohole, B. Ikati, T. Muller, WCS RDC unpublished data 2019' }, // resynced January 2022
               // { id: 'anAcQ9on4inNnmtqFVpabh', tag: 'nrgt_historical', name: 'NRGT Kahuzi Biega 2019', owner: 'wcs_mtkb', instance:'F. Kavuba, A. Twendilonge, R. Cito, WCS RDC unpublished data 2019'}, // resynced Feb 2022
               // { id: 'ajDeQVDrz2AZxvxLLWjiBE', tag: 'nrgt_current', name: 'NRGT Makira 2019', owner: 'wcs_mamabaie', instance:'C. Spira, N. Dokolahy, J. Ranariniaina, M. Cournarie, L. Andriamampianina,  D. Detoeuf, WCS Madagascar unpublished data 2019'}, // resynced January 2022
               // { id: 'aotHau7krdjj2NFBNAJpZC', tag: 'nrgt_current', name: 'NRGT_ABS_2019_V1', owner: 'nrgtmada', instance:'WCS Madagascar unpublished data 2019'}, 
               // { id: 'a8yPcus6ggWBXK5iK8LpC7', tag: 'nrgt_current', name: 'NRGT_SW_2019_V1', owner: 'rchristelle', instance:'WCS Madagascar unpublished data 2019'}, 
               // { id: 'aGYzFZPUFfvCvnKG8gyfjG', tag: 'nrgt_current', name: 'NRGT_NW_2019_V1', owner: 'nrgtmada', instance:'WCS Madagascar unpublished data 2019'}, 

            // BNS HH 2018
               // { id: 'aQbjGLfvPTEUjdTmsdTu46', tag: 'bns_survey', name: 'BNS Ndoki 2018', owner: 'wcs_ndoki', instance: 'Y. Londza, F. Sellat, D. Detoeuf, WCS Congo unpublished data 2018' }, // resynced January 2022

            // BNS Prices 2018
               // { id: 'ao52kp6BgLgooE3MRsdy4B', tag: 'bns_price', name: 'BNS Prix Ndoki 2018', owner: 'wcs_ndoki', instance: 'Y. Londza, F. Sellat, D. Detoeuf, WCS Congo unpublished data 2018'  }, //  resynced January 2022

            // NRGT 2018
               //  { id: 'apFFEwXk38TQ2SCkV99bFY', tag: 'nrgt_historical', name: 'NRGT Kahuzi Biega 2018', owner: 'wcs_mtkb', instance:'' }, // resynced Fec 2022
               // { id: 'aLhFXhkmM4rZJvtqiSjChE', tag: 'nrgt_historical', name: 'NRGT Ndoki 2018', owner: 'wcs_ndoki', instance:'' }, // resynced January 2022
               // { id: 'awR6CQTvEqiL9PYvMic7dE', tag: 'nrgt_historical', name: 'NRGT Bateke 2018', owner: 'wcs_bateke', instance:'' }, // resynced January 2022

            // BNS HH 2017
               // { id: 'aEypYtcfNGvDtpkSUPXrJN', tag: 'bns_survey', name: 'BNS ménage Kahuzi 2018-2019', owner: 'wcs_mtkb', instance: 'C. Spira, A. Kirkby, F. Kavuba, D. Detoeuf, A. Twendilonge, WCS RDC unpublished data 2017' }, // resynced August 2022
               // { id: 'a9R68Er4oeDx6quZre2DM7', tag: 'bns_survey', name: 'BNS Makira 2017', owner: 'wcs_mamabaie', instance: 'M. Ravelona, WCS Madagascar unpublished data 2017' }, // resynced January 2022
               // { id: 'auqBASwJMLvkqtAJnZ8SXx', tag: 'bns_survey', name: 'BNS Lac Télé 2017', owner: 'wcs_lactele', instance: 'G. Bondeko, N. Loundou, R. Mouanda, R. Mossaba, M. Boboto, D. Detoeuf, WCS Congo unpublished data 2017' }, // resynced January 2022
               // { id: 'a2m5Hp4BoN956CBxfKHdJY', tag: 'bns_survey', name: 'BNS Ituri: Enquête Ménages 2017', owner: 'wcs_ituri', instance: 'M. Enduyi, A. Tsongo, J. Maneno, O. Angauko, A. Ohole, WCS RDC unpublished data 2017'}, // resynced January 2022
               // { id: 'amD3cUsR4Jurj3ZSUyQdBH', tag: 'bns_survey', name: 'BNS Crossriver 2017', owner: 'wcs_crossriver', instance: 'I. Imong, J. Ntui, O. Okagbare, S. Ova, L. Nkonyu, WCS Nigeria unpublished data 2017'}, // resynced January 2022

            // BNS Prices 2017
               // { id: 'aqwNyk7ikXxU9x4u77YfnS', tag: 'bns_price', name: 'Prices Crossriver 2017 ', owner: 'wcs_crossriver', instance: 'I. Imong, J. Ntui, O. Okagbare, S. Ova, L. Nkonyu, WCS Nigeria unpublished data 2017'}, // resynced January 2022
               // { id: 'apMTFWRd9fQWHvpJHLfvk6', tag: 'bns_price', name: 'BNS Prix Lac Télé 2017', owner: 'wcs_lactele' , instance: 'G. Bondeko, N. Loundou, R. Mouanda, R. Mossaba, M. Boboto, D. Detoeuf, WCS Congo unpublished data 2017' }, // resynced January 2022
               // { id: 'aTkDkjcfNN7vQJdZeJHkJq', tag: 'bns_price', name: 'Price Makira 2017', owner: 'wcs_mamabaie', instance: 'M. Ravelona, WCS Madagascar unpublished data 2017' }, // resynced January 2022
               // { id: 'a6BjZ8ncLtTtfBSGZ9PqoJ', tag: 'bns_price', name: 'BNS Prix Ituri 2017', owner: 'wcs_ituri', instance: 'M. Enduyi, A. Tsongo, J. Maneno, O. Angauko, A. Ohole, WCS RDC unpublished data 2017'}, // resynced January 2022

           // NRGT 2017
               // { id: 'apnzrb2RuoKu8Uxy2svwF6', tag: 'nrgt_historical', name: 'NRGT Crossriver 2017', owner: 'wcs_crossriver', instance:'I. Imong, J. Ntui, O. Okagbare, S. Ova, L. Nkonyu, WCS Nigeria unpublished data 2017' }, // resynced Oct 2022
               // { id: 'aeWW3VLbdMDgUHHdoRCUH9', tag: 'nrgt_historical', name: 'NRGT Ituri 2017', owner: 'wcs_ituri', instance:'B. Ntumba, A. Ngomba, A. Walanga, I. Liengola, WCS RDC unpublished data 2017' }, // resynced January 2022
               //  { id: 'aU6gbhMjfHyGekAma8wHhG', tag: 'nrgt_historical', name: 'NRGT Lac Télé 2017', owner: 'wcs_lactele', instance:'G. Bondeko, N. Loundou, R. Mouanda, R. Mossaba, M. Boboto, D. Detoeuf, WCS Congo unpublished data 2017' }, // resynced January 2022
               // { id: 'asRbCJuyX3KJMFkB2p9Hh7', tag: 'nrgt_historical', name: 'NRGT Makira 2017', owner: 'wcs_mamabaie', instance:'N. Dokolahy, M. Ravelona, D. Detoeuf, WCS Madagascar unpublished data 2017' }, // resynced January 2022

              ].map(survey => ({
                formId: survey.id,
                tag: survey.tag,
                name: survey.name,
                owner: survey.owner,
                instance: survey.instance,
                url: `https://kf.kobotoolbox.org/api/v2/assets/${survey.id}/data/?format=json`,
                //* REPLACE L157 w/ the below URL to sync only 1 submision for each form; see "limit=1" *//
                //url: `https://kf.kobotoolbox.org/api/v2/assets/${survey.id}/data/?format=json&limit=5`,
              })),
            };
            console.log(`Fetching data for ${state.data.surveys.length} surveys.`);
            return state;
          });

          each(dataPath('surveys[*]'), state => {
            const { url, tag, formId, name, instance, owner } = state.data;
            return get(url, {}, state => {
              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,
                  instance: instance,
                  body: submission,
                };
              });
              const count = state.data.submissions.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 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);
          });

          alterState(state => {
            console.log(`Finished at: ${new Date().toISOString()}`);
            return {
              data: {
                message: "No cursor required. Job fetches all submission for given forms."
              },
              references: []
            };
          });

    triggers:
      cron:
        type: cron
        cron_expression: '0 22 1 * *'
        enabled: false
    edges:
      cron->BNS-3-Get-all-Kobo-forms-on-demand:
        source_trigger: cron
        target_job: BNS-3-Get-all-Kobo-forms-on-demand
        condition_type: always
        enabled: true
  4.-FS1---Get-Forms:
    name: 4. FS1 - Get Forms
    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---Find-New-Forms-Shared:
        name: FS3 - Find 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@latest'
        credential: aisha@openfn.org-AK-Asana
        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 2 * * *'
        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---Find-New-Forms-Shared:
        source_job: FS2---Get-List-from-Sheets
        target_job: FS3---Find-New-Forms-Shared
        condition_type: on_job_success
        enabled: true
      FS3---Find-New-Forms-Shared->FS4---Notify-in-Asana:
        source_job: FS3---Find-New-Forms-Shared
        target_job: FS4---Notify-in-Asana
        condition_type: on_job_success
        enabled: false
  A1-Generate-Jobs-DB-Tables-and-Dictionary-AUTO:
    name: 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@v4.0.0'
        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
  A2-Process-Forms-AUTO:
    name: 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
  auto--WCSPROGRAMS-CameraKoboDeployment:
    name: auto  WCSPROGRAMS CameraKoboDeployment
    jobs:
      auto-WCSPROGRAMS-CameraKoboDeployment:
        name: auto WCSPROGRAMS CameraKoboDeployment
        adaptor: '@openfn/language-mssql@v2.6.11'
        credential: null
        body: |
          fn(state => {
              const multiSelectIds = [""];

              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 };
            }); 
          fn(async state => {
           const mapping = {
            FormName: dataValue('formName'),
            DatasetId: dataValue('_xform_id_string'),
            LastUpdated: new Date().toISOString()
          } 
           return upsert('WCSPROGRAMS_KoboDataset', "DatasetId", mapping, {setNull: ["''", "'undefined'"]})(state); 
          }) 
          fn(async state => {
           const mapping = {
            Note: dataValue('note'),
            Battery: dataValue('battery'),
            WCSPROGRAMS_CameraKoboYesNoID_KeyRetrieved: await findValue({uuid: 'wcsprograms_camerakoboyesnoid', relation: 'WCSPROGRAMS_CameraKoboYesNo', where: { WCSPROGRAMS_CameraKoboYesNoExtCode: dataValue('key_retrieved') }})(state),
            WCSPROGRAMS_CameraKoboYesNoID_MotionTest: await findValue({uuid: 'wcsprograms_camerakoboyesnoid', relation: 'WCSPROGRAMS_CameraKoboYesNo', where: { WCSPROGRAMS_CameraKoboYesNoExtCode: dataValue('motion_test') }})(state),
            WCSPROGRAMS_CameraKoboYesNoID_CameraOn: await findValue({uuid: 'wcsprograms_camerakoboyesnoid', relation: 'WCSPROGRAMS_CameraKoboYesNo', where: { WCSPROGRAMS_CameraKoboYesNoExtCode: dataValue('camera_on') }})(state),
            WCSPROGRAMS_CameraKoboYesNoID_SdInsert: await findValue({uuid: 'wcsprograms_camerakoboyesnoid', relation: 'WCSPROGRAMS_CameraKoboYesNo', where: { WCSPROGRAMS_CameraKoboYesNoExtCode: dataValue('sd_insert') }})(state),
            WCSPROGRAMS_CameraKoboYesNoID_SdUnlock: await findValue({uuid: 'wcsprograms_camerakoboyesnoid', relation: 'WCSPROGRAMS_CameraKoboYesNo', where: { WCSPROGRAMS_CameraKoboYesNoExtCode: dataValue('sd_unlock') }})(state),
            NoKey: dataValue('no_key'),
            SdCard: dataValue('sd_card'),
            DetectionDistance: dataValue('detection_distance'),
            PlotTreatmentDescription: dataValue('plot_treatment_description'),
            PlotTreatment: dataValue('plot_treatment'),
            RecordedBy: dataValue('recorded_by'),
            OrientationOther: dataValue('orientation_other'),
            Direction: dataValue('direction'),
            WCSPROGRAMS_CameraKoboAngleID_SensorOrientation: await findValue({uuid: 'wcsprograms_camerakoboangleid', relation: 'WCSPROGRAMS_CameraKoboAngle', where: { WCSPROGRAMS_CameraKoboAngleExtCode: dataValue('sensor_orientation') }})(state),
            HeightOther: dataValue('height_other'),
            CmHeight: dataValue('cm_height'),
            WCSPROGRAMS_CameraKoboHeightID_SensorHeight: await findValue({uuid: 'wcsprograms_camerakoboheightid', relation: 'WCSPROGRAMS_CameraKoboHeight', where: { WCSPROGRAMS_CameraKoboHeightExtCode: dataValue('sensor_height') }})(state),
            QuietPeriod: dataValue('quiet_period'),
            FeatureTypeMethodology: dataValue('feature_type_methodology'),
            WCSPROGRAMS_CameraKoboFeatureID_FeatureType: await findValue({uuid: 'wcsprograms_camerakobofeatureid', relation: 'WCSPROGRAMS_CameraKoboFeature', where: { WCSPROGRAMS_CameraKoboFeatureExtCode: dataValue('feature_type') }})(state),
            BaitDescription: dataValue('bait_description'),
            WCSPROGRAMS_CameraKoboBaitID_BaitType: await findValue({uuid: 'wcsprograms_camerakobobaitid', relation: 'WCSPROGRAMS_CameraKoboBait', where: { WCSPROGRAMS_CameraKoboBaitExtCode: dataValue('bait_type') }})(state),
            WCSPROGRAMS_CameraKoboEventID_EventType: await findValue({uuid: 'wcsprograms_camerakoboeventid', relation: 'WCSPROGRAMS_CameraKoboEvent', where: { WCSPROGRAMS_CameraKoboEventExtCode: dataValue('event_type') }})(state),
            EventDescription: dataValue('event_description'),
            EventName: dataValue('event_name'),
            StartDate: dataValue('start_date'),
            WCSPROGRAMS_CameraKoboCanopyID_Canopy: await findValue({uuid: 'wcsprograms_camerakobocanopyid', relation: 'WCSPROGRAMS_CameraKoboCanopy', where: { WCSPROGRAMS_CameraKoboCanopyExtCode: dataValue('canopy') }})(state),
            WCSPROGRAMS_CameraKoboVisibilityID_Visibility: await findValue({uuid: 'wcsprograms_camerakobovisibilityid', relation: 'WCSPROGRAMS_CameraKoboVisibility', where: { WCSPROGRAMS_CameraKoboVisibilityExtCode: dataValue('visibility') }})(state),
            WCSPROGRAMS_CameraKoboUnderwoodID_Underwood: await findValue({uuid: 'wcsprograms_camerakobounderwoodid', relation: 'WCSPROGRAMS_CameraKoboUnderwood', where: { WCSPROGRAMS_CameraKoboUnderwoodExtCode: dataValue('underwood') }})(state),
            WCSPROGRAMS_CameraKoboSlopeID_Slope: await findValue({uuid: 'wcsprograms_camerakoboslopeid', relation: 'WCSPROGRAMS_CameraKoboSlope', where: { WCSPROGRAMS_CameraKoboSlopeExtCode: dataValue('slope') }})(state),
            WCSPROGRAMS_CameraKoboVegID_Veg: await findValue({uuid: 'wcsprograms_camerakobovegid', relation: 'WCSPROGRAMS_CameraKoboVeg', where: { WCSPROGRAMS_CameraKoboVegExtCode: dataValue('veg') }})(state),
            Latitude: state => state.data.gps.split(' ')[0],
            Longitude: state => state.data.gps.split(' ')[1],
            WCSPROGRAMS_CameraKoboCameraID_CameraId: await findValue({uuid: 'wcsprograms_camerakobocameraid', relation: 'WCSPROGRAMS_CameraKoboCamera', where: { WCSPROGRAMS_CameraKoboCameraExtCode: dataValue('camera_id') }})(state),
            PointTransect: dataValue('point_transect'),
            TeamLead: dataValue('team_lead'),
            Placename: dataValue('placename'),
            SubprojectDesign: dataValue('subproject_design'),
            SubprojectName: dataValue('subproject_name'),
            ProjectId: dataValue('project_id'),
            DeploymentId: dataValue('deployment_id'),
            Deviceid: dataValue('deviceid'),
            Today: dataValue('today'),
            FormDateEnd: dataValue('end'),
            Start: dataValue('start'),
            AnswerId: state._id,
            GeneratedUuid: dataValue('__generatedUuid'),
            Payload: state.data.body
          } 
           return upsert('WCSPROGRAMS_CameraKoboDeployment', 'GeneratedUuid', mapping, {setNull: ["''", "'undefined'"]})(state); 
          }) 

    triggers:
      webhook:
        type: webhook
        enabled: false
    edges:
      webhook->auto-WCSPROGRAMS-CameraKoboDeployment:
        source_trigger: webhook
        target_job: auto-WCSPROGRAMS-CameraKoboDeployment
        condition_type: js_expression
        condition_label: 'auto/WCSPROGRAMS_CameraKoboDeployment'
        condition_expression: |
          state.data.tableId == "WCSPROGRAMS_CameraKoboDeployment"
        enabled: false
  "z[Archive]-Get-Kobo-Forms-Template":
    name: 'z[Archive] Get Kobo Forms Template'
    jobs:
      Get-Kobo-Forms-Template:
        name: Get Kobo Forms Template
        adaptor: '@openfn/language-http@v3.1.12'
        credential: null
        body: |
          fn(state => {
            console.log('Current cursor value:', state.lastEnd);

            // IF YOU CLEAR STATE...
            // Set this manual cursor to the earliest submission date you want fetch.
            const manualCursor = '2022-04-10T14:32:43.325+01:00';
            state.data = {
              surveys: [
              //==== GRIEVENCES FORM ===============//
               {
                 uid: 'aEQjRDMcEgLzRDQYcFqSCC',
                 formName: 'SSMT GRM Intake Form Template', //Kobo form name
                 projectid: '1201382240883590' //Asana project ID obtained from the Fetch Asana ID job.
               },
              // {
              //   uid: 'aEQjRDMcEgLzRDQYcFqSCC',
              //   formName: 'SSMT GRM Intake Form Template', //Kobo form name
              //   projectid: '1201871867457230' // Asana project ID: Indonesia GRM
              // },
               //==== OTHER FORMS ===============//
              // {
              //   uid: 'kobo-id',
              //   formName: 'Form Name', //Kobo form name
              //   projectid: 'asana-id-from-url' //Asana project ID
              // },
              //================================//
              ].map(survey => ({
                ...survey,
                formId: survey.uid,
                url: `https://kf.kobotoolbox.org/api/v2/assets/${survey.uid}/data/?format=json`,
                query: `&query={"_submission_time":{"$gte":"${
                   state.lastEnd || manualCursor
                }"}}`,
              })),
            };
            return state;
          });

          each(dataPath('surveys[*]'), state => {
            const { url, query, tag, formId, formType, formName, owner, projectid } = state.data;
            return get(`${url}${query}`, {}, state => {
              state.data.submissions = state.data.results.map(submission => {
                return {
                  // Here we append the tags defined above to the Kobo form submission data
                  projectid,
                  formName,
                  formOwner: owner,
                  formType,
                  body: submission,
                };
              });
              const count = state.data.submissions.length;
              console.log(`Fetched ${count} submissions from ${formName}.`);
              //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 1 of ${count}...`);
                return post(state.configuration.openfnInboxUrl, {
                  body: state => state.data,
                })(state);
              })(state);
              // =========================================================================
            })(state);
          });

          fn(state => {
            let lastEnd = state.references
              .filter(item => item.body)
              .map(s => s.body.end)
              .sort((a, b) => new Date(b.date) - new Date(a.date))[0];

            lastEnd = new Date(lastEnd) > new Date() ? lastEnd : new Date().toISOString();

            console.log('New cursor value:', lastEnd);
            return { ...state, data: {}, references: [], lastEnd };
          });

    triggers:
      cron:
        type: cron
        cron_expression: '0 */1 * * *'
        enabled: false
    edges:
      cron->Get-Kobo-Forms-Template:
        source_trigger: cron
        target_job: Get-Kobo-Forms-Template
        condition_type: always
        enabled: false
  "z[Archive]-Get-kobo-form---test":
    name: 'z[Archive] Get kobo form - test'
    jobs:
      Get-kobo-form---test:
        name: Get kobo form - test
        adaptor: '@openfn/language-http@v6.1.0'
        credential: null
        body: |
          get('https://kf.kobotoolbox.org/api/v2/assets/a4oeehbiGuXrEWK7rkUv82/?format=json', 
          {
            headers: {'content-type': 'application/json'},
          }, state => {
            console.log(state); 
            return state; 
            
            })

    triggers:
      cron:
        type: cron
        cron_expression: '0 2 * * *'
        enabled: false
    edges:
      cron->Get-kobo-form---test:
        source_trigger: cron
        target_job: Get-kobo-form---test
        condition_type: always
        enabled: true
  "z[Archive]-Query-DB-KoboData-Table---Test":
    name: 'z[Archive] Query DB KoboData Table - Test'
    jobs:
      Query-DB-KoboData-Table---Test:
        name: Query DB KoboData Table - Test
        adaptor: '@openfn/language-mssql@v4.0.7'
        credential: null
        body: |
          sql({ query: state =>
              `SELECT * FROM WCSPROGRAMS_KoboData where DatasetUuidId = 'BNS NDOKI 2022-aRnpV9xNVcbqLPbmoKn9sR'`,
          }, state => {
            console.log(state); 
            return state; 
          });

          fn(state => {
            console.log(JSON.stringify(state.response.rows, null,2)); 
            return state; 
          })

    triggers:
      cron:
        type: cron
        cron_expression: '0 */1 * * *'
        enabled: false
    edges:
      cron->Query-DB-KoboData-Table---Test:
        source_trigger: cron
        target_job: Query-DB-KoboData-Table---Test
        condition_type: always
        enabled: true
  "z[Archive]-Workflow-asana-test":
    name: 'z[Archive] Workflow asana test'
    jobs:
      asana-test:
        name: asana test
        adaptor: '@openfn/language-asana@latest'
        credential: null
        body: |
          getTasks("11989019982662",
           {
             opt_fields: "name,notes,assignee"
           })

    triggers:
      cron:
        type: cron
        cron_expression: '0 2 * * *'
        enabled: false
    edges:
      cron->asana-test:
        source_trigger: cron
        target_job: asana-test
        condition_type: always
        enabled: true