SELECT
  WC.id,
  WC.title,
  WC.template,
  WC.step,
  WC.status,
  WC.created,
  WC.lastUpdated,
  WC.agreementDate,
  WC.webhookEventTime,
  JSON_VALUE(WC.attributes, '$.contractExecutionDate') as contractExecutionDate,
  JSON_VALUE(WC.attributes, '$.paperSource') as paperSource,
  WLC.templateName
FROM YOUR_BIGQUERY_PROJECT_ID.YOUR_DATASET_ID.workflows_completed WC
JOIN YOUR_BIGQUERY_PROJECT_ID.YOUR_DATASET_ID.workflows_launch_configuration WLC
ON WC.id = WLC.workflowId

SELECT
  id,
  type,
  name,
  lastUpdated,
  agreementDate,
  workflowId,
  webhookEventTime,
  JSON_VALUE(properties, '$.department.value') as department,
  JSON_VALUE(properties, '$.counterpartyName.value') as counterpartyName
FROM YOUR_BIGQUERY_PROJECT_ID.YOUR_DATASET_ID.records

SELECT
  WA.workflowId,
  WA.event,
  WA.status,
  WA.userId,
  WA.userEmail,
  WA.approvalName,
  WA.approvalId,
  WA.fullApprovalData,
  WA.webhookEventTime,
  WLC.templateName
FROM YOUR_BIGQUERY_PROJECT_ID.YOUR_DATASET_ID.workflows_approvals WA
LEFT JOIN YOUR_BIGQUERY_PROJECT_ID.YOUR_DATASET_ID.workflows_launch_configuration WLC
ON WA.workflowId = WLC.workflowId

SELECT
  workflowId,
  title,
  JSON_VALUE(signer, '$.signer.roleName') as roleName,
  JSON_VALUE(signer, '$.signer.name') as name,
  JSON_VALUE(signer, '$.signer.email') as email,
  JSON_VALUE(signer, '$.signer.signatureStatus.status') as signerStatus,
  webhookEventTime
FROM YOUR_BIGQUERY_PROJECT_ID.YOUR_DATASET_ID.workflows_signer

// Used to standardize date formatting in some edge cases.
const formatDate = (dateString) => {
  const date = new Date(dateString);
  return date.toISOString().slice(0, -1);
};

// Checks and returns key value or returns null if none found.
const keyObjCheck = (key, obj) => {
  if (key in obj) return obj[key];
  return null;
};

// BigQuery requires data to be sent as Newline Delimited JSON.
// This flattens an object for BigQuery to accept the data.
const formatObjectToColumn = (obj) => {
  try {
    const jsonString = JSON.stringify(obj);
    return jsonString.replace(/\n/g, '');
  } catch (error) {
    console.error(error);
    return null;
  }
};

// Additional check and formatting for dates.
const checkAndFormatDateIfExists = (key, obj) => {
  try {
    if (key in obj) return formatDate(obj[key]);
    else return null;
  } catch (error) {
    console.error(error);
    return null;
  }
};

module.exports = {
  formatDate,
  keyObjCheck,
  formatObjectToColumn,
  checkAndFormatDateIfExists,
};

const {
  formatObjectToColumn,
  checkAndFormatDateIfExists,
} = require('../helpers');

class Workflow {
  constructor(
    workflowData,
    timestamp,
  ) {
    this.id = workflowData.id;
    this.title = workflowData.title;
    this.template = workflowData.template;
    this.step = workflowData.step;
    this.status = workflowData.status;
    this.created = checkAndFormatDateIfExists(
      'created',
      workflowData,
    );
    this.lastUpdated = checkAndFormatDateIfExists(
      'lastUpdated',
      workflowData,
    );
    this.agreementDate = checkAndFormatDateIfExists(
      'agreementDate',
      workflowData.attributes,
    );
    this.attributes = formatObjectToColumn(workflowData.attributes);
    this.webhookEventTime = timestamp;
  }
}

module.exports = { Workflow };

const { keyObjCheck, formatObjectToColumn } = require('../helpers');

class WorkflowApproval {
  constructor(webhookData, timestamp, fullApprovalData) {
    this.workflowId = webhookData.workflowID;
    this.event = webhookData.event;
    this.status = webhookData.status;
    this.userId = keyObjCheck('userID', webhookData);
    this.userEmail = keyObjCheck('userEmail', webhookData);
    this.approvalName = webhookData.approvalName;
    this.approvalId = webhookData.approvalID;
    this.fullApprovalData = formatObjectToColumn(fullApprovalData);
    this.webhookEventTime = timestamp;
  }
}

module.exports = { WorkflowApproval };

const {formatObjectToColumn,} = require('../helpers');

class WorkflowSigner {
  constructor(workflowId, title, signer, timestamp,) {
    this.workflowId = workflowId;
    this.title = title;
    this.signer = formatObjectToColumn(signer);
    this.webhookEventTime = timestamp;
  }
}

module.exports = { WorkflowSigner };

const { formatObjectToColumn } = require('../helpers');

class WorkflowConfigSnapshot {
  constructor(
    templateId,
    workflowId,
    configData,
    timestamp,
  ) {
    this.workflowId = workflowId;
    this.templateId = templateId;
    this.templateName = configData.name;
    this.templateSchema = formatObjectToColumn(configData.schema);
    this.webhookEventTime = timestamp;
  }
}

module.exports = { WorkflowConfigSnapshot };

const {
  formatObjectToColumn,
  checkAndFormatDateIfExists,
} = require('../helpers');

class Record {
  constructor(
    recordData,
    timestamp,
  ) {
    this.id = recordData.id;
    this.type = recordData.type;
    this.name = recordData.name;
    this.lastUpdated = checkAndFormatDateIfExists('lastUpdated', recordData);
    this.agreementDate = ('agreementDate' in recordData.properties) ?
      checkAndFormatDateIfExists('value', recordData.properties.agreementDate) :
      null;
    this.properties = formatObjectToColumn(recordData.properties);
    this.workflowId = recordData.source.workflowId;
    this.webhookEventTime = timestamp;
  }
}

module.exports = { Record };

const axios = require('axios').default;
const API_KEY = process.env.IRONCLAD_API_KEY;

class IroncladAPI {
  constructor(apiEnvironment = 'demo') {
    this.apiVersion = 'v1';
    this.apiEnvironment = apiEnvironment;
    this.requestConfig = { headers: { 'Authorization': `Bearer ${API_KEY}`} };
  }

  async retrieveWorkflowData(workflowID) {
    const response = await axios.get(
      `${this.apiUrl()}/workflows/${workflowID}`,
      this.requestConfig,
    );
    return response.data;
  }

  async retrieveWorkflowApprovers(workflowID) {
    const response = await axios.get(
      `${this.apiUrl()}/workflows/${workflowID}/approvals`,
      this.requestConfig,
    );
    return response.data;
  }

  async retrieveWorkflowSigners(workflowID) {
    const response = await axios.get(
      `${this.apiUrl()}/workflows/${workflowID}/signatures`,
      this.requestConfig,
    );
    return response.data;
  }

  async retrieveRecordData(recordID) {
    const response = await axios.get(
      `${this.apiUrl()}/records/${recordID}`,
      this.requestConfig,
    );
    return response.data;
  }

  async retrieveWorkflowConfig(templateId) {
    const response = await axios.get(
      `${this.apiUrl()}/workflow-schemas/${templateId}?form=launch`,
      this.requestConfig,
    );
    return response.data;
  }

  apiUrl() {
    switch (this.apiEnvironment) {
      case 'demo':
        return `https://demo.ironcladapp.com/public/api/${this.apiVersion}`;
      case 'production':
        return `https://ironcladapp.com/public/api/${this.apiVersion}`;
      default:
        return `https://ironcladapp.com/public/api/${this.apiVersion}`;
    }
  }
}

module.exports = { IroncladAPI };

require('dotenv').config();
const BIGQUERY_DATASET_ID = process.env.BIGQUERY_DATASET_ID;

class BigQueryStatics {

  // Returns the ID of the BigQuery Dataset, which is specified
  // in the .env file of thie example.
  static get datasetId() {
    return BIGQUERY_DATASET_ID;
  }

  // Method used for conveniently retrieving the identifier or schema of a BigQuery
  // table based on the specified table name.
  static get tables() {
    return {
      workflowsLaunchConfiguration: this.workflowsLaunchConfigurationTable,
      workflowsLaunched: this.workflowsLaunchedTable,
      workflowsApprovals: this.workflowsApprovalsTable,
      workflowsSigner: this.workflowsSignerTable,
      workflowsCompleted: this.workflowsCompletedTable,
      workflowsCancelled: this.workflowsCancelledTable,
      workflowsUpdated: this.workflowsUpdatedTable,
      records: this.recordsTable,
    };
  }

  // The query used when creating the "Completed Workflows View".
  static completedWorkflowsViewQuery(
    projectIdentifier,
    datasetIdentifier,
    sourceTableIdentifier,
  ) {
    return `
    SELECT
      WC.id,
      WC.title,
      WC.template,
      WC.step,
      WC.status,
      WC.created,
      WC.lastUpdated,
      WC.agreementDate,
      WC.webhookEventTime,
      WLC.templateName
    FROM ${projectIdentifier}.${datasetIdentifier}.${sourceTableIdentifier} WC
    LEFT JOIN ${projectIdentifier}.${datasetIdentifier}.${sourceTableIdentifier} WLC
    ON WC.template = WLC.templateId
    `;
  }

  // The query used when creating the "Records View".
  static recordsViewQuery(
    projectIdentifier,
    datasetIdentifier,
    sourceTableIdentifier,
  ) {
    return `
    SELECT
      id,
      type,
      name,
      lastUpdated,
      agreementDate,
      workflowId,
      webhookEventTime,
      JSON_VALUE(properties, '$.department.value') as department,
      JSON_VALUE(properties, '$.counterpartyName.value') as counterpartyName
    FROM ${projectIdentifier}.${datasetIdentifier}.${sourceTableIdentifier}
    `;
  }

  static workflowsApprovalsQuery(
    projectIdentifier,
    datasetIdentifier,
    sourceTableIdentifier,
  ) {
    return `
    SELECT
      WA.workflowId,
      WA.event,
      WA.status,
      WA.userId,
      WA.userEmail,
      WA.approvalName,
      WA.approvalId,
      WA.fullApprovalData,
      WA.webhookEventTime,
      WLC.templateName
    FROM ${projectIdentifier}.${datasetIdentifier}.${sourceTableIdentifier} WA
    LEFT JOIN ${projectIdentifier}.${datasetIdentifier}.workflows_launch_configuration WLC
    ON WA.workflowId = WLC.workflowId
    `;
  }

  static workflowSignerQuery(
    projectIdentifier,
    datasetIdentifier,
    sourceTableIdentifier,
  ) {
    return `
    SELECT
      workflowId,
      title,
      JSON_VALUE(signer, '$.signer.roleName') as roleName,
      JSON_VALUE(signer, '$.signer.name') as name,
      JSON_VALUE(signer, '$.signer.email') as email,
      JSON_VALUE(signer, '$.signer.signatureStatus.status') as signerStatus,
      webhookEventTime      
    FROM ${projectIdentifier}.${datasetIdentifier}.${sourceTableIdentifier}
    `;
  }

  // The schema used for workflows_launched, workflows_completed, workflows_cancelled,
  // and workflows_updated tables.
  static _workflowTableSchema() {
    return [
      { name: 'id', type: 'STRING' },
      { name: 'title', type: 'STRING' },
      { name: 'template', type: 'STRING' },
      { name: 'step', type: 'STRING' },
      { name: 'status', type: 'STRING' },
      { name: 'created', type: 'DATETIME' },
      { name: 'lastUpdated', type: 'DATETIME' },
      { name: 'agreementDate', type: 'DATETIME' },
      { name: 'attributes', type: 'STRING' },
      { name: 'webhookEventTime', type: 'TIMESTAMP' },
    ];
  }

  // The schema used for the workflows_approvals table.
  static _workflowApprovalsTableSchema() {
    return [
      { name: 'workflowId', type: 'STRING' },
      { name: 'event', type: 'STRING' },
      { name: 'status', type: 'STRING' },
      { name: 'userId', type: 'STRING' },
      { name: 'userEmail', type: 'STRING' },
      { name: 'approvalName', type: 'STRING' },
      { name: 'approvalId', type: 'STRING' },
      { name: 'fullApprovalData', type: 'STRING' },
      { name: 'webhookEventTime', type: 'TIMESTAMP' },
    ];
  }

  // The schema used for the records table.
  static _recordsTableSchema() {
    return [
      { name: 'id', type: 'STRING' },
      { name: 'type', type: 'STRING' },
      { name: 'name', type: 'STRING' },
      { name: 'lastUpdated', type: 'DATETIME' },
      { name: 'agreementDate', type: 'DATETIME' },
      { name: 'properties', type: 'STRING' },
      { name: 'workflowId', type: 'STRING' },
      { name: 'webhookEventTime', type: 'TIMESTAMP' },
    ];
  }

  // The schema used the workflows_launch_configuration table.
  static _workflowsLaunchConfigurationSchema() {
    return [
      { name: 'workflowId', type: 'STRING' },
      { name: 'templateId', type: 'STRING' },
      { name: 'templateName', type: 'STRING' },
      { name: 'templateSchema', type: 'STRING' },
      { name: 'webhookEventTime', type: 'TIMESTAMP' },
    ];
  }

  static _workflowsSignerSchema() {
    return [
      { name: 'workflowId', type: 'STRING' },
      { name: 'title', type: 'STRING' },
      { name: 'signer', type: 'STRING' },
      { name: 'webhookEventTime', type: 'TIMESTAMP' },
    ];
  }
  
  // The identifier and schema of the workflows_launched table.
  static get workflowsLaunchedTable() {
    return Object.freeze({
      tableIdentifier: 'workflows_launched',
      schema: this._workflowTableSchema(),
    });
  }

  // The identifier and schema of the workflows_launch_configuration table.
  static get workflowsLaunchConfigurationTable() {
    return Object.freeze({
      tableIdentifier: 'workflows_launch_configuration',
      schema: this._workflowsLaunchConfigurationSchema(),
    });
  }

  // The identifier and schema of the workflows_approvals table.
  static get workflowsApprovalsTable() {
    return Object.freeze({
      tableIdentifier: 'workflows_approvals',
      schema: this._workflowApprovalsTableSchema(),
    });
  }

  // The identifier and schema of the workflows_signer table.
  static get workflowsSignerTable() {
    return Object.freeze({
      tableIdentifier: 'workflows_signer',
      schema: this._workflowsSignerSchema(),
    });
  }

  // The identifier and schema of the workflows_completed table.
  static get workflowsCompletedTable() {
    return Object.freeze({
      tableIdentifier: 'workflows_completed',
      schema: this._workflowTableSchema(),
    });
  }

  // The identifier and schema of the workflows_cancelled table.
  static get workflowsCancelledTable() {
    return Object.freeze({
      tableIdentifier: 'workflows_cancelled',
      schema: this._workflowTableSchema(),
    });
  }

  // The identifier and schema of the workflows_updated table.
  static get workflowsUpdatedTable() {
    return Object.freeze({
      tableIdentifier: 'workflows_updated',
      schema: this._workflowTableSchema(),
    });
  }

  // The identifier and schema of the records table.
  static get recordsTable() {
    return Object.freeze({
      tableIdentifier: 'records',
      schema: this._recordsTableSchema(),
    });
  }
}

module.exports = { BigQueryStatics };

const { BigQuery } = require('@google-cloud/bigquery');

const BIGQUERY_PROJECT_ID = process.env.BIGQUERY_PROJECT_ID;

// Options used for the BigQuery Node.js SDK.
const options = {
  keyFilename: `${__dirname}/service-account-file.json`,
  projectId: BIGQUERY_PROJECT_ID,
};

// Used to prepared data into a specified dataset ID and table ID.
const insertStreamingData = async ({
  datasetId = '',
  tableId = '',
  dataRows = [],
}) => {
  if (datasetId == '' || tableId == '') {
    throw new Error('Dataset ID and Table ID are required.');
  }
  const bigquery = new BigQuery(options);

  return await bigquery
    .dataset(datasetId)
    .table(tableId)
    .insert(dataRows);
};

module.exports = { insertStreamingData };

const { BigQuery } = require('@google-cloud/bigquery');
const { IroncladAPI } = require('./IroncladAPI');
const {
  Workflow,
  Record,
  WorkflowApproval,
  WorkflowConfigSnapshot,
} = require('./models');
const {
  insertStreamingData,
  BigQueryStatics,
} = require('./BigQuery');

class WebhookHandler {
  constructor(requestBody) {
    this.requestBody = requestBody;
    this.timestamp = BigQuery.timestamp(new Date());
    this.icApi = new IroncladAPI();
  }

  // Used for preparing and storing data into both the workflows_launched
  // and workflows_launch_configuration BigQuery tables.
  async workflowLaunched() {
    const { templateID, workflowID } = this.requestBody.payload;
    await this._handleWorkflowLaunchConfig(templateID, workflowID);
    return await this._handleWorkflowLaunched(workflowID);
  }

  // Used for preparing and storing data into both the workflows_completed and
  // records BigQuery tables.
  async workflowCompleted() {
    const { workflowID, recordIDs } = this.requestBody.payload;
    const workflowData = await this.icApi.retrieveWorkflowData(workflowID);
    const workflowRowData = [new Workflow(workflowData, this.timestamp)];
    await this._insertDataToBigQuery(
      workflowRowData,
      BigQueryStatics.tables.workflowsCompleted.tableIdentifier,
    );
    return await this._processRecordsData(recordIDs);
  }

  // Used for preparing and storing data into the workflows_cancelled
  // BigQuery table.
  async workflowCancelled() {
    const { workflowID } = this.requestBody.payload;
    const workflowData = await this.icApi.retrieveWorkflowData(workflowID);
    const workflowRowData = [new Workflow(workflowData, this.timestamp)];
    return await this._insertDataToBigQuery(
      workflowRowData,
      BigQueryStatics.tables.workflowsCancelled.tableIdentifier,
    );
  }

  // Used for preparing and storing data into the workflows_updated
  // BigQuery table.
  async workflowUpdated() {
    const { workflowID } = this.requestBody.payload;
    const workflowData = await this.icApi.retrieveWorkflowData(workflowID);
    const workflowRowData = [new Workflow(workflowData, this.timestamp)];
    const bigQueryUpdated = await this._insertDataToBigQuery(
      workflowRowData,
      BigQueryStatics.tables.workflowsUpdated.tableIdentifier,
    );
    if (workflowData.step === 'Sign') {
      await this.workflowSigners();
    }
    return bigQueryUpdated;
  }

  /**
   * Handles and inserts signers data into BigQuery from the
   * workflow_updated webhook event type.
   * @return {Promise} The response from BigQuery insert.
   */
  async workflowSigners() {
    const { workflowID } = this.requestBody.payload;
    const workflowSignersData = await this.icApi.retrieveWorkflowSigners(
      workflowID,
    );

    const workflowSignersRowData = [];

    for (const signerData of workflowSignersData.signers) {
      workflowSignersRowData.push(
        new WorkflowSigner(
          workflowSignersData.workflowId,
          workflowSignersData.title,
          signerData,
          this.timestamp),
      );
    }

    await this._insertDataToBigQuery(
      workflowSignersRowData,
      BigQueryStatics.tables.workflowsSigner.tableIdentifier,
    );
  }

  // Used for preparing and storing data into the workflow workflow_approval_status_changed
  // BigQuery table.
  async workflowApprovalStatusChange() {
    const fullApprovalData = await this.icApi.retrieveWorkflowApprovers(this.requestBody.payload.workflowID);
    const rowData = [new WorkflowApproval(
      this.requestBody.payload,
      this.timestamp,
      fullApprovalData,
    )];
    return await this._insertDataToBigQuery(
      rowData,
      BigQueryStatics.tables.workflowsApprovals.tableIdentifier,
    );
  }

  // Used for preparing and storing data into the workflow_launch_configuration
  // BigQuery table.
  async _handleWorkflowLaunchConfig(templateId, workflowId) {
    const configData = await this.icApi.retrieveWorkflowConfig(
      templateId,
    );
    const rowData = [new WorkflowConfigSnapshot(
      templateId,
      workflowId,
      configData,
      this.timestamp,
    )];
    return await this._insertDataToBigQuery(
      rowData,
      BigQueryStatics.tables.workflowsLaunchConfiguration.tableIdentifier,
    );
  }

  // Used for preparing and storing data into the workflows_launched
  // BigQuery table.
  async _handleWorkflowLaunched(workflowId) {
    const workflowData = await this.icApi.retrieveWorkflowData(workflowId);
    const workflowRowData = [new Workflow(workflowData, this.timestamp)];
    return await this._insertDataToBigQuery(
      workflowRowData,
      BigQueryStatics.tables.workflowsLaunched.tableIdentifier,
    );
  }

  // Used to handle processing and storing data into the records
  // BigQuery table.
  async _processRecordsData(recordIds = []) {
    if (recordIds.length === 0) return;
    const recordsToGet = recordIds.map(
      (recordId) => this.icApi.retrieveRecordData(recordId),
    );
    const recordsData = await Promise.all(recordsToGet);
    const processedRecords = recordsData.map(
      (recordData) => new Record(recordData, this.timestamp),
    );
    return await this._insertDataToBigQuery(
      processedRecords,
      BigQueryStatics.tables.records.tableIdentifier,
    );
  }

  // Used to call the insertStreamingData method for inserting
  // data into BigQuery.
  async _insertDataToBigQuery(rowData, tableIdentifier) {
    return await insertStreamingData({
      datasetId: BigQueryStatics.datasetId,
      tableId: tableIdentifier,
      dataRows: rowData,
    });
  }
}

module.exports = { WebhookHandler };

require('dotenv').config();
const { WebhookHandler } = require('./src/WebhookHandler');

/**
 * HTTP Cloud Function.
 *
 * @param {Object} req Cloud Function request context.
 * @param {Object} res Cloud Function response context.
 */
exports.handleWebhook = async (req, res) => {
  try {
    if (
      !req.body &&
      !req.body.payload &&
      !req.body.payload.event
    ) return res.status(200).end();
    console.log(
      `Received ${req.body.payload.event} for workflow ${req.body.payload.workflowID}`,
    );
    const webhookHandler = new WebhookHandler(req.body);

    // Used to trigger the appropriate webhookHandler method based on
    // the incoming event type from the webhook.
    switch (req.body.payload.event) {
    case 'workflow_launched':
      await webhookHandler.workflowLaunched();
      break;
    case 'workflow_approval_status_changed':
      await webhookHandler.workflowApprovalStatusChange();
      break;
    case 'workflow_completed':
      await webhookHandler.workflowCompleted();
      break;
    case 'workflow_cancelled':
      await webhookHandler.workflowCancelled();
      break;
    case 'workflow_updated':
      await webhookHandler.workflowUpdated();
      break;
    default:
      break;
    }
    console.log('finished');
    res.status(200).end();
  } catch (error) {
    console.log(error);
    console.log('error handling webhook', JSON.stringify(error));
    res.status(500).end();
  }
};

DATEDIFF('minute', [Created], DATETIME([Contract Execution Date]))