Record Query Utility

var QueryUtils = Class.create();
QueryUtils.prototype = {

  /**
   * Get single record by sys_id with error handling
   * @param {string} table - Table name
   * @param {string} sysId - Record sys_id
   * @returns {GlideRecord|null} Record or null if not found
   */
  getById: function(table, sysId) {
    if (!table || !sysId) {
      gs.warn('QueryUtils.getById: Missing table or sys_id');
      return null;
    }

    var gr = new GlideRecord(table);
    if (gr.get(sysId)) {
      return gr;
    }

    return null;
  },

  /**
   * Get records with query string
   * @param {string} table - Table name
   * @param {string} query - Encoded query string
   * @param {number} limit - Maximum results (default: no limit)
   * @returns {array} Array of GlideRecord objects
   */
  getRecords: function(table, query, limit) {
    var records = [];

    var gr = new GlideRecord(table);
    if (query) {
      gr.addEncodedQuery(query);
    }

    if (limit) {
      gr.setLimit(limit);
    }

    gr.query();

    while (gr.next()) {
      // Push a copy of the record
      var record = new GlideRecord(table);
      record.get(gr.sys_id);
      records.push(record);
    }

    return records;
  },

  /**
   * Check if record exists
   * @param {string} table - Table name
   * @param {string} field - Field name
   * @param {string} value - Field value
   * @returns {boolean} True if record exists
   */
  exists: function(table, field, value) {
    var gr = new GlideRecord(table);
    gr.addQuery(field, value);
    gr.query();

    return gr.hasNext();
  },

  /**
   * Count records matching query
   * @param {string} table - Table name
   * @param {string} query - Encoded query string (optional)
   * @returns {number} Count of matching records
   */
  count: function(table, query) {
    var ga = new GlideAggregate(table);

    if (query) {
      ga.addEncodedQuery(query);
    }

    ga.addAggregate('COUNT');
    ga.query();

    if (ga.next()) {
      return parseInt(ga.getAggregate('COUNT'));
    }

    return 0;
  },

  /**
   * Get distinct values for a field
   * @param {string} table - Table name
   * @param {string} field - Field name
   * @param {string} query - Optional encoded query
   * @returns {array} Array of distinct values
   */
  getDistinctValues: function(table, field, query) {
    var values = [];

    var ga = new GlideAggregate(table);

    if (query) {
      ga.addEncodedQuery(query);
    }

    ga.groupBy(field);
    ga.query();

    while (ga.next()) {
      var value = ga.getValue(field);
      if (value) {
        values.push(value);
      }
    }

    return values;
  },

  /**
   * Batch update records
   * @param {string} table - Table name
   * @param {string} query - Encoded query string
   * @param {object} updates - Object with field: value pairs
   * @param {boolean} skipWorkflow - Skip business rules (default: false)
   * @returns {number} Number of updated records
   */
  batchUpdate: function(table, query, updates, skipWorkflow) {
    var count = 0;

    var gr = new GlideRecord(table);
    if (query) {
      gr.addEncodedQuery(query);
    }

    gr.query();

    while (gr.next()) {
      // Apply updates
      for (var field in updates) {
        if (updates.hasOwnProperty(field)) {
          gr.setValue(field, updates[field]);
        }
      }

      // Set workflow flag if requested
      if (skipWorkflow) {
        gr.setWorkflow(false);
      }

      gr.update();
      count++;
    }

    gs.info('QueryUtils.batchUpdate: Updated ' + count + ' records in ' + table);
    return count;
  },

  /**
   * Delete records matching query
   * @param {string} table - Table name
   * @param {string} query - Encoded query string
   * @param {boolean} skipWorkflow - Skip business rules (default: false)
   * @returns {number} Number of deleted records
   */
  batchDelete: function(table, query, skipWorkflow) {
    var count = 0;

    var gr = new GlideRecord(table);
    if (query) {
      gr.addEncodedQuery(query);
    }

    gr.query();

    while (gr.next()) {
      if (skipWorkflow) {
        gr.setWorkflow(false);
      }

      gr.deleteRecord();
      count++;
    }

    gs.info('QueryUtils.batchDelete: Deleted ' + count + ' records from ' + table);
    return count;
  },

  /**
   * Copy record to another table or same table
   * @param {string} sourceTable - Source table name
   * @param {string} sourceSysId - Source record sys_id
   * @param {string} targetTable - Target table name (can be same as source)
   * @param {array} excludeFields - Fields to exclude from copy
   * @returns {string|null} New record sys_id or null if failed
   */
  copyRecord: function(sourceTable, sourceSysId, targetTable, excludeFields) {
    excludeFields = excludeFields || ['sys_id', 'sys_created_on', 'sys_created_by', 
                                       'sys_updated_on', 'sys_updated_by'];

    var source = new GlideRecord(sourceTable);
    if (!source.get(sourceSysId)) {
      gs.warn('QueryUtils.copyRecord: Source record not found');
      return null;
    }

    var target = new GlideRecord(targetTable);
    target.initialize();

    // Copy all fields except excluded ones
    var fieldNames = source.getFields();
    for (var i = 0; i < fieldNames.size(); i++) {
      var fieldName = fieldNames.get(i).getName();

      if (excludeFields.indexOf(fieldName) === -1) {
        target.setValue(fieldName, source.getValue(fieldName));
      }
    }

    var newSysId = target.insert();

    if (newSysId) {
      gs.info('QueryUtils.copyRecord: Created new record ' + newSysId);
      return newSysId;
    }

    return null;
  },

  /**
   * Get records with pagination
   * @param {string} table - Table name
   * @param {string} query - Encoded query string
   * @param {number} page - Page number (1-indexed)
   * @param {number} pageSize - Records per page
   * @returns {object} {records: array, total: number, page: number, totalPages: number}
   */
  getPaginated: function(table, query, page, pageSize) {
    page = page || 1;
    pageSize = pageSize || 10;

    // Get total count
    var total = this.count(table, query);
    var totalPages = Math.ceil(total / pageSize);

    // Get paginated results
    var gr = new GlideRecord(table);
    if (query) {
      gr.addEncodedQuery(query);
    }

    // Calculate offset
    var offset = (page - 1) * pageSize;
    gr.chooseWindow(offset, offset + pageSize);
    gr.query();

    var records = [];
    while (gr.next()) {
      var record = new GlideRecord(table);
      record.get(gr.sys_id);
      records.push(record);
    }

    return {
      records: records,
      total: total,
      page: page,
      pageSize: pageSize,
      totalPages: totalPages
    };
  },

  type: 'QueryUtils'
};