text: 'use strict';
  
  /* jshint -W110 */
  const Utils = require('../../utils');
  const util = require('util');
  const DataTypes = require('../../data-types');
  const AbstractQueryGenerator = require('../abstract/query-generator');
  const semver = require('semver');
  const _ = require('lodash');
  
  const QueryGenerator = {
    /* jshint proto:true */
    __proto__: AbstractQueryGenerator,
    options: {},
    dialect: 'postgres',
  
    setSearchPath(searchPath) {
      return `SET search_path to ${searchPath};`;
    },
  
    createSchema(schema) {
      return `CREATE SCHEMA ${schema};`;
    },
  
    dropSchema(schema) {
      return `DROP SCHEMA IF EXISTS ${schema} CASCADE;`;
    },
  
    showSchemasQuery() {
      return "SELECT schema_name FROM information_schema.schemata WHERE schema_name <> 'information_schema' AND schema_name != 'public' AND schema_name !~ E'^pg_';";
    },
  
    versionQuery() {
      return 'SHOW SERVER_VERSION';
    },
  
    createTableQuery(tableName, attributes, options) {
  
      options = Utils._.extend({
      }, options || {});
  
      //Postgres 9.0 does not support CREATE TABLE IF NOT EXISTS, 9.1 and above do
      const databaseVersion = Utils._.get(this, 'sequelize.options.databaseVersion', 0);
      const attrStr = [];
      let comments = '';
  
      if (options.comment && Utils._.isString(options.comment)) {
        comments += '; COMMENT ON TABLE <%= table %> IS ' + this.escape(options.comment);
      }
  
      for (const attr in attributes) {
        const i = attributes[attr].indexOf('COMMENT');
        if (i !== -1) {
          // Move comment to a separate query
          comments += '; ' + attributes[attr].substring(i);
          attributes[attr] = attributes[attr].substring(0, i);
        }
  
        const dataType = this.dataTypeMapping(tableName, attr, attributes[attr]);
        attrStr.push(this.quoteIdentifier(attr) + ' ' + dataType);
      }
  
      const values = {
        table: this.quoteTable(tableName),
        attributes: attrStr.join(', '),
        comments: Utils._.template(comments)({ table: this.quoteTable(tableName)})
      };
  
      if (!!options.uniqueKeys) {
        Utils._.each(options.uniqueKeys, columns => {
          if (!columns.singleField) { // If it's a single field its handled in column def, not as an index
            values.attributes += ', UNIQUE (' + columns.fields.map(f => this.quoteIdentifiers(f)).join(', ') + ')';
          }
        });
      }
  
      const pks = _.reduce(attributes, (acc, attribute, key) => {
        if (_.includes(attribute, 'PRIMARY KEY')) {
          acc.push(this.quoteIdentifier(key));
        }
        return acc;
      }, []).join(',');
  
      if (pks.length > 0) {
        values.attributes += ', PRIMARY KEY (' + pks + ')';
      }
  
      return `CREATE TABLE ${databaseVersion === 0 || semver.gte(databaseVersion, '9.1.0') ? 'IF NOT EXISTS ' : ''}${values.table} (${values.attributes})${values.comments};`;
    },
  
    dropTableQuery(tableName, options) {
      options = options || {};
      return `DROP TABLE IF EXISTS ${this.quoteTable(tableName)}${options.cascade ? ' CASCADE' : ''};`;
    },
  
    showTablesQuery() {
      return "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type LIKE '%TABLE' AND table_name != 'spatial_ref_sys';";
    },
  
    describeTableQuery(tableName, schema) {
      if (!schema) {
        schema = 'public';
      }
  
      return 'SELECT tc.constraint_type as "Constraint", c.column_name as "Field", c.column_default as "Default", c.is_nullable as "Null", ' +
        "CASE WHEN c.udt_name = 'hstore' " +
        'THEN c.udt_name ELSE c.data_type END as "Type", (SELECT array_agg(e.enumlabel) ' +
        'FROM pg_catalog.pg_type t JOIN pg_catalog.pg_enum e ON t.oid=e.enumtypid WHERE t.typname=c.udt_name) AS "special" ' +
        'FROM information_schema.columns c ' +
        'LEFT JOIN information_schema.key_column_usage cu ON c.table_name = cu.table_name AND cu.column_name = c.column_name ' +
        'LEFT JOIN information_schema.table_constraints tc ON c.table_name = tc.table_name AND cu.column_name = c.column_name AND tc.constraint_type = \'PRIMARY KEY\' ' +
        `WHERE c.table_name = ${this.escape(tableName)} AND c.table_schema = ${this.escape(schema)} `;
    },
  
    // A recursive parser for nested where conditions
    parseConditionObject(_conditions, path) {
      path = path || [];
      return Utils._.reduce(_conditions, (r, v, k) => { // result, key, value
        if (Utils._.isObject(v)) {
          r = r.concat(this.parseConditionObject(v, path.concat(k))); // Recursively parse objects
        } else {
          r.push({ path: path.concat(k), value: v });
        }
        return r;
      }, []);
    },
  
    handleSequelizeMethod(smth, tableName, factory, options, prepend) {
      if (smth instanceof Utils.Json) {
        // Parse nested object
        if (smth.conditions) {
          const conditions = _.map(this.parseConditionObject(smth.conditions), condition =>
            `${this.quoteIdentifier(_.first(condition.path))}#>>'{${_.tail(condition.path).join(',')}}' = '${condition.value}'`
          );
  
          return conditions.join(' and ');
        } else if (smth.path) {
          let str;
  
          // Allow specifying conditions using the postgres json syntax
          if (_.some(['->', '->>', '#>'], _.partial(_.includes, smth.path))) {
            str = smth.path;
          } else {
            // Also support json dot notation
            const path = smth.path.split('.');
            str = `${this.quoteIdentifier(_.first(path))}#>>'{${_.tail(path).join(',')}}'`;
          }
  
          if (smth.value) {
            str += util.format(' = %s', this.escape(smth.value));
          }
  
          return str;
        }
      } else {
        return AbstractQueryGenerator.handleSequelizeMethod.call(this, smth, tableName, factory, options, prepend);
      }
    },
  
    addColumnQuery(table, key, dataType) {
  
      const dbDataType = this.attributeToSQL(dataType, {context: 'addColumn'});
      const definition = this.dataTypeMapping(table, key, dbDataType);
      const quotedKey = this.quoteIdentifier(key);
      const quotedTable = this.quoteTable(this.extractTableDetails(table));
  
      let query = `ALTER TABLE ${quotedTable} ADD COLUMN ${quotedKey} ${definition};`;
  
      if (dataType.type && dataType.type instanceof DataTypes.ENUM || dataType instanceof DataTypes.ENUM) {
        query = this.pgEnum(table, key, dataType) + query;
      }
  
      return query;
    },
  
    removeColumnQuery(tableName, attributeName) {
      const quotedTableName = this.quoteTable(this.extractTableDetails(tableName));
      const quotedAttributeName = this.quoteIdentifier(attributeName);
      return `ALTER TABLE ${quotedTableName} DROP COLUMN ${quotedAttributeName};`;
    },
  
    changeColumnQuery(tableName, attributes) {
      const query = 'ALTER TABLE <%= tableName %> ALTER COLUMN <%= query %>;';
      const sql = [];
  
      for (const attributeName in attributes) {
        let definition = this.dataTypeMapping(tableName, attributeName, attributes[attributeName]);
        let attrSql = '';
  
        if (definition.indexOf('NOT NULL') > 0) {
          attrSql += Utils._.template(query)({
            tableName: this.quoteTable(tableName),
            query: this.quoteIdentifier(attributeName) + ' SET NOT NULL'
          });
  
          definition = definition.replace('NOT NULL', '').trim();
        } else if (!definition.match(/REFERENCES/)) {
          attrSql += Utils._.template(query)({
            tableName: this.quoteTable(tableName),
            query: this.quoteIdentifier(attributeName) + ' DROP NOT NULL'
          });
        }
  
        if (definition.indexOf('DEFAULT') > 0) {
          attrSql += Utils._.template(query)({
            tableName: this.quoteTable(tableName),
            query: this.quoteIdentifier(attributeName) + ' SET DEFAULT ' + definition.match(/DEFAULT ([^;]+)/)[1]
          });
  
          definition = definition.replace(/(DEFAULT[^;]+)/, '').trim();
        } else if (!definition.match(/REFERENCES/)) {
          attrSql += Utils._.template(query)({
            tableName: this.quoteTable(tableName),
            query: this.quoteIdentifier(attributeName) + ' DROP DEFAULT'
          });
        }
  
        if (attributes[attributeName].match(/^ENUM\(/)) {
          attrSql += this.pgEnum(tableName, attributeName, attributes[attributeName]);
          definition = definition.replace(/^ENUM\(.+\)/, this.quoteIdentifier('enum_' + tableName + '_' + attributeName));
          definition += ' USING (' + this.quoteIdentifier(attributeName) + '::' + this.quoteIdentifier(definition) + ')';
        }
  
        if (definition.match(/UNIQUE;*$/)) {
          definition = definition.replace(/UNIQUE;*$/, '');
  
          attrSql += Utils._.template(query.replace('ALTER COLUMN', ''))({
            tableName: this.quoteTable(tableName),
            query: 'ADD CONSTRAINT ' + this.quoteIdentifier(attributeName + '_unique_idx') + ' UNIQUE (' + this.quoteIdentifier(attributeName) + ')'
          });
        }
  
        if (definition.match(/REFERENCES/)) {
          definition = definition.replace(/.+?(?=REFERENCES)/, '');
          attrSql += Utils._.template(query.replace('ALTER COLUMN', ''))({
            tableName: this.quoteTable(tableName),
            query: 'ADD CONSTRAINT ' + this.quoteIdentifier(attributeName + '_foreign_idx') + ' FOREIGN KEY (' + this.quoteIdentifier(attributeName) + ') ' + definition
          });
        } else {
          attrSql += Utils._.template(query)({
            tableName: this.quoteTable(tableName),
            query: this.quoteIdentifier(attributeName) + ' TYPE ' + definition
          });
        }
  
        sql.push(attrSql);
      }
  
      return sql.join('');
    },
  
    renameColumnQuery(tableName, attrBefore, attributes) {
  
      const attrString = [];
  
      for (const attributeName in attributes) {
        attrString.push(Utils._.template('<%= before %> TO <%= after %>')({
          before: this.quoteIdentifier(attrBefore),
          after: this.quoteIdentifier(attributeName)
        }));
      }
  
      return `ALTER TABLE ${this.quoteTable(tableName)} RENAME COLUMN ${attrString.join(', ')};`;
    },
  
    fn(fnName, tableName, body, returns, language) {
      fnName = fnName || 'testfunc';
      language = language || 'plpgsql';
      returns = returns || 'SETOF ' + this.quoteTable(tableName);
  
      return `CREATE OR REPLACE FUNCTION pg_temp.${fnName}() RETURNS ${returns} AS $func$ BEGIN ${body} END; $func$ LANGUAGE ${language}; SELECT * FROM pg_temp.${fnName}();`;
    },
  
    exceptionFn(fnName, tableName, main, then, when, returns, language) {
      when  = when || 'unique_violation';
  
      const body = `${main} EXCEPTION WHEN ${when} THEN ${then};`;
  
      return this.fn(fnName, tableName, body, returns, language);
    },
  
    upsertQuery(tableName, insertValues, updateValues, where, rawAttributes, options) {
      const insert = this.insertQuery(tableName, insertValues, rawAttributes, options);
      const update = this.updateQuery(tableName, updateValues, where, options, rawAttributes);
  
      // The numbers here are selected to match the number of affected rows returned by MySQL
      return this.exceptionFn(
        'sequelize_upsert',
        tableName,
        insert + ' RETURN 1;',
        update + '; RETURN 2',
        'unique_violation',
        'integer'
      );
    },
  
    deleteQuery(tableName, where, options, model) {
      let query;
  
      options = options || {};
  
      tableName = this.quoteTable(tableName);
  
      if (options.truncate === true) {
        query = 'TRUNCATE ' + tableName;
  
        if (options.restartIdentity) {
          query += ' RESTART IDENTITY';
        }
  
        if (options.cascade) {
          query += ' CASCADE';
        }
  
        return query;
      }
  
      if (Utils._.isUndefined(options.limit)) {
        options.limit = 1;
      }
  
      const replacements = {
        table: tableName,
        where: this.getWhereConditions(where, null, model, options),
        limit: !!options.limit ? ' LIMIT ' + this.escape(options.limit) : ''
      };
  
      if (options.limit) {
        if (!model) {
          throw new Error('Cannot LIMIT delete without a model.');
        }
  
        const pks = _.map(_.values(model.primaryKeys), pk => this.quoteIdentifier((pk.field))).join(',');
  
        replacements.primaryKeys = model.primaryKeyAttributes.length > 1 ? '(' + pks + ')' : pks;
        replacements.primaryKeysSelection = pks;
  
        query = 'DELETE FROM <%= table %> WHERE <%= primaryKeys %> IN (SELECT <%= primaryKeysSelection %> FROM <%= table %><%= where %><%= limit %>)';
      } else {
        query = 'DELETE FROM <%= table %><%= where %>';
      }
  
      if (replacements.where) {
        replacements.where = ' WHERE ' + replacements.where;
      }
  
      return Utils._.template(query)(replacements);
    },
  
    showIndexesQuery(tableName) {
      let schemaJoin = '';
      let schemaWhere = '';
      if (!Utils._.isString(tableName)) {
        schemaJoin = ', pg_namespace s';
        schemaWhere = ` AND s.oid = t.relnamespace AND s.nspname = '${tableName.schema}'`;
        tableName = tableName.tableName;
      }
  
      // This is ARCANE!
      return 'SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, ' +
        'array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) ' +
        `AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a${schemaJoin} ` +
        'WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND ' +
        `t.relkind = 'r' and t.relname = '${tableName}'${schemaWhere} ` +
        'GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;';
    },
  
    removeIndexQuery(tableName, indexNameOrAttributes) {
      let indexName = indexNameOrAttributes;
  
      if (typeof indexName !== 'string') {
        indexName = Utils.underscore(tableName + '_' + indexNameOrAttributes.join('_'));
      }
  
      return `DROP INDEX IF EXISTS ${this.quoteIdentifiers(indexName)}`;
    },
  
    addLimitAndOffset(options) {
      let fragment = '';
      /*jshint eqeqeq:false*/
      if (options.limit != null) {
        fragment += ' LIMIT ' + this.escape(options.limit);
      }
      if (options.offset != null) {
        fragment += ' OFFSET ' + this.escape(options.offset);
      }
  
      return fragment;
    },
  
    attributeToSQL(attribute) {
      if (!Utils._.isPlainObject(attribute)) {
        attribute = {
          type: attribute
        };
      }
  
      let type;
      if (attribute.type instanceof DataTypes.ENUM) {
        if (attribute.type.values && !attribute.values) attribute.values = attribute.type.values;
  
        if (Array.isArray(attribute.values) && (attribute.values.length > 0)) {
          type = 'ENUM(' + Utils._.map(attribute.values, value => this.escape(value)).join(', ') + ')';
        } else {
          throw new Error("Values for ENUM haven't been defined.");
        }
      }
  
      if (!type) {
        type = attribute.type;
      }
  
      let sql = type + '';
  
      if (attribute.hasOwnProperty('allowNull') && (!attribute.allowNull)) {
        sql += ' NOT NULL';
      }
  
      if (attribute.autoIncrement) {
        sql += ' SERIAL';
      }
  
      if (Utils.defaultValueSchemable(attribute.defaultValue)) {
        sql += ' DEFAULT ' + this.escape(attribute.defaultValue, attribute);
      }
  
      if (attribute.unique === true) {
        sql += ' UNIQUE';
      }
  
      if (attribute.primaryKey) {
        sql += ' PRIMARY KEY';
      }
  
      if (attribute.references) {
        const referencesTable = this.quoteTable(attribute.references.model);
        let referencesKey;
  
        if (attribute.references.key) {
          referencesKey = this.quoteIdentifiers(attribute.references.key);
        } else {
          referencesKey = this.quoteIdentifier('id');
        }
  
        sql += ` REFERENCES ${referencesTable} (${referencesKey})`;
  
        if (attribute.onDelete) {
          sql += ' ON DELETE ' + attribute.onDelete.toUpperCase();
        }
  
        if (attribute.onUpdate) {
          sql += ' ON UPDATE ' + attribute.onUpdate.toUpperCase();
        }
  
        if (attribute.references.deferrable) {
          sql += ' ' + attribute.references.deferrable.toString(this);
        }
      }
  
      return sql;
    },
  
    deferConstraintsQuery(options) {
      return options.deferrable.toString(this);
    },
  
    setConstraintQuery(columns, type) {
      let columnFragment = 'ALL';
  
      if (columns) {
        columnFragment = columns.map(column => this.quoteIdentifier(column)).join(', ');
      }
  
      return 'SET CONSTRAINTS ' + columnFragment + ' ' + type;
    },
  
    setDeferredQuery(columns) {
      return this.setConstraintQuery(columns, 'DEFERRED');
    },
  
    setImmediateQuery(columns) {
      return this.setConstraintQuery(columns, 'IMMEDIATE');
    },
  
    attributesToSQL(attributes, options) {
      const result = {};
  
      for (const key in attributes) {
        const attribute = attributes[key];
        result[attribute.field || key] = this.attributeToSQL(attribute, options);
      }
  
      return result;
    },
  
    findAutoIncrementField(factory) {
      const fields = [];
  
      for (const name in factory.attributes) {
        const definition = factory.attributes[name];
  
        if (definition && definition.autoIncrement) {
          fields.push(name);
        }
      }
  
      return fields;
    },
  
    createTrigger(tableName, triggerName, eventType, fireOnSpec, functionName, functionParams, optionsArray) {
  
      const decodedEventType = this.decodeTriggerEventType(eventType);
      const eventSpec = this.expandTriggerEventSpec(fireOnSpec);
      const expandedOptions = this.expandOptions(optionsArray);
      const paramList = this.expandFunctionParamList(functionParams);
  
      return `CREATE ${this.triggerEventTypeIsConstraint(eventType)}TRIGGER ${triggerName}\n`
        + `\t${decodedEventType} ${eventSpec}\n`
        + `\tON ${tableName}\n`
        + `\t${expandedOptions}\n`
        + `\tEXECUTE PROCEDURE ${functionName}(${paramList});`;
    },
  
    dropTrigger(tableName, triggerName) {
      return `DROP TRIGGER ${triggerName} ON ${tableName} RESTRICT;`;
    },
  
    renameTrigger(tableName, oldTriggerName, newTriggerName) {
      return `ALTER TRIGGER ${oldTriggerName} ON ${tableName} RENAME TO ${newTriggerName};`;
    },
  
    createFunction(functionName, params, returnType, language, body, options) {
      if (!functionName || !returnType || !language || !body) throw new Error('createFunction missing some parameters. Did you pass functionName, returnType, language and body?');
  
      const paramList = this.expandFunctionParamList(params);
      const indentedBody = body.replace('\n', '\n\t');
      const expandedOptions = this.expandOptions(options);
  
      return `CREATE FUNCTION ${functionName}(${paramList})\n`
        + `RETURNS ${returnType} AS $func$\n`
        + 'BEGIN\n'
        + `\t${indentedBody}\n`
        + 'END;\n'
        + `$func$ language '${language}'${expandedOptions};`;
    },
  
    dropFunction(functionName, params) {
      if (!functionName) throw new Error('requires functionName');
      // RESTRICT is (currently, as of 9.2) default but we'll be explicit
      const paramList = this.expandFunctionParamList(params);
      return `DROP FUNCTION ${functionName}(${paramList}) RESTRICT;`;
    },
  
    renameFunction(oldFunctionName, params, newFunctionName) {
      const paramList = this.expandFunctionParamList(params);
      return `ALTER FUNCTION ${oldFunctionName}(${paramList}) RENAME TO ${newFunctionName};`;
    },
  
    databaseConnectionUri(config) {
      let uri = config.protocol + '://' + config.user + ':' + config.password + '@' + config.host;
      if (config.port) {
        uri += ':' + config.port;
      }
      uri += '/' + config.database;
      if (config.ssl) {
        uri += '?ssl=' + config.ssl;
      }
      return uri;
    },
  
    pgEscapeAndQuote(val) {
      return this.quoteIdentifier(Utils.removeTicks(this.escape(val), "'"));
    },
  
    expandFunctionParamList(params) {
      if (Utils._.isUndefined(params) || !Utils._.isArray(params)) {
        throw new Error('expandFunctionParamList: function parameters array required, including an empty one for no arguments');
      }
  
      const paramList = [];
      Utils._.each(params, curParam => {
        const paramDef = [];
        if (Utils._.has(curParam, 'type')) {
          if (Utils._.has(curParam, 'direction')) { paramDef.push(curParam.direction); }
          if (Utils._.has(curParam, 'name')) { paramDef.push(curParam.name); }
          paramDef.push(curParam.type);
        } else {
          throw new Error('function or trigger used with a parameter without any type');
        }
  
        var joined = paramDef.join(' ');
        if (joined) paramList.push(joined);
  
      });
  
      return paramList.join(', ');
    },
    
  
    expandOptions(options) {
      return Utils._.isUndefined(options) || Utils._.isEmpty(options) ?
        '' : '\n\t' + options.join('\n\t');
    },
  
    decodeTriggerEventType(eventSpecifier) {
      const EVENT_DECODER = {
        'after': 'AFTER',
        'before': 'BEFORE',
        'instead_of': 'INSTEAD OF',
        'after_constraint': 'AFTER'
      };
  
      if (!Utils._.has(EVENT_DECODER, eventSpecifier)) {
        throw new Error('Invalid trigger event specified: ' + eventSpecifier);
      }
  
      return EVENT_DECODER[eventSpecifier];
    },
  
    triggerEventTypeIsConstraint(eventSpecifier) {
      return eventSpecifier === 'after_constraint' ? 'CONSTRAINT ' : '';
    },
  
    expandTriggerEventSpec(fireOnSpec) {
      if (Utils._.isEmpty(fireOnSpec)) {
        throw new Error('no table change events specified to trigger on');
      }
  
      return Utils._.map(fireOnSpec, (fireValue, fireKey) => {
        const EVENT_MAP = {
          'insert': 'INSERT',
          'update': 'UPDATE',
          'delete': 'DELETE',
          'truncate': 'TRUNCATE'
        };
  
        if (!Utils._.has(EVENT_MAP, fireKey)) {
          throw new Error('parseTriggerEventSpec: undefined trigger event ' + fireKey);
        }
  
        let eventSpec = EVENT_MAP[fireKey];
        if (eventSpec === 'UPDATE') {
          if (Utils._.isArray(fireValue) && fireValue.length > 0) {
            eventSpec += ' OF ' + fireValue.join(', ');
          }
        }
  
        return eventSpec;
      }).join(' OR ');
    },
  
    pgEnumName(tableName, attr, options) {
      options = options || {};
      const tableDetails = this.extractTableDetails(tableName, options);
      let enumName = '"enum_' + tableDetails.tableName + '_' + attr + '"';
  
      // pgListEnums requires the enum name only, without the schema
      if (options.schema !== false && tableDetails.schema) {
        enumName = this.quoteIdentifier(tableDetails.schema) + tableDetails.delimiter + enumName;
      }
  
      return enumName;
  
    },
  
    pgListEnums(tableName, attrName, options) {
      let enumName = '';
      const tableDetails = this.extractTableDetails(tableName, options);
  
      if (tableDetails.tableName && attrName) {
        enumName = ' AND t.typname=' + this.pgEnumName(tableDetails.tableName, attrName, { schema: false }).replace(/"/g, "'");
      }
  
      return 'SELECT t.typname enum_name, array_agg(e.enumlabel ORDER BY enumsortorder) enum_value FROM pg_type t ' +
        'JOIN pg_enum e ON t.oid = e.enumtypid ' +
        'JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace ' +
        `WHERE n.nspname = '${tableDetails.schema}'${enumName} GROUP BY 1`;
    },
  
    pgEnum(tableName, attr, dataType, options) {
      const enumName = this.pgEnumName(tableName, attr, options);
      let values;
  
      if (dataType.values) {
        values = "ENUM('" + dataType.values.join("', '") + "')";
      } else {
        values = dataType.toString().match(/^ENUM\(.+\)/)[0];
      }
  
      let sql = 'CREATE TYPE ' + enumName + ' AS ' + values + ';';
      if (!!options && options.force === true) {
        sql = this.pgEnumDrop(tableName, attr) + sql;
      }
      return sql;
    },
  
    pgEnumAdd(tableName, attr, value, options) {
      const enumName = this.pgEnumName(tableName, attr);
      let sql = 'ALTER TYPE ' + enumName + ' ADD VALUE ';
  
      if (semver.gte(this.sequelize.options.databaseVersion, '9.3.0')) {
        sql += 'IF NOT EXISTS ';
      }
      sql += this.escape(value);
  
      if (!!options.before) {
        sql += ' BEFORE ' + this.escape(options.before);
      } else if (!!options.after) {
        sql += ' AFTER ' + this.escape(options.after);
      }
  
      return sql;
    },
  
    pgEnumDrop(tableName, attr, enumName) {
      enumName = enumName || this.pgEnumName(tableName, attr);
      return 'DROP TYPE IF EXISTS ' + enumName + '; ';
    },
  
    fromArray(text) {
      text = text.replace(/^{/, '').replace(/}$/, '');
      let matches = text.match(/("(?:\\.|[^"\\\\])*"|[^,]*)(?:\s*,\s*|\s*$)/ig);
  
      if (matches.length < 1) {
        return [];
      }
  
      matches = matches.map(m => m.replace(/",$/, '').replace(/,$/, '').replace(/(^"|"$)/, ''));
  
      return matches.slice(0, -1);
    },
  
    padInt(i) {
      return (i < 10) ? '0' + i.toString() : i.toString();
    },
  
    dataTypeMapping(tableName, attr, dataType) {
      if (Utils._.includes(dataType, 'PRIMARY KEY')) {
        dataType = dataType.replace(/PRIMARY KEY/, '');
      }
  
      if (Utils._.includes(dataType, 'SERIAL')) {
        if (Utils._.includes(dataType, 'BIGINT')) {
          dataType = dataType.replace(/SERIAL/, 'BIGSERIAL');
          dataType = dataType.replace(/BIGINT/, '');
        } else {
          dataType = dataType.replace(/INTEGER/, '');
        }
        dataType = dataType.replace(/NOT NULL/, '');
      }
  
      if (dataType.match(/^ENUM\(/)) {
        dataType = dataType.replace(/^ENUM\(.+\)/, this.pgEnumName(tableName, attr));
      }
  
      return dataType;
    },
  
    quoteIdentifier(identifier, force) {
      if (identifier === '*') return identifier;
      if (!force && this.options && this.options.quoteIdentifiers === false && identifier.indexOf('.') === -1 && identifier.indexOf('->') === -1) { // default is `true`
        // In Postgres, if tables or attributes are created double-quoted,
        // they are also case sensitive. If they contain any uppercase
        // characters, they must always be double-quoted. This makes it
        // impossible to write queries in portable SQL if tables are created in
        // this way. Hence, we strip quotes if we don't want case sensitivity.
        return Utils.removeTicks(identifier, '"');
      } else {
        return Utils.addTicks(identifier, '"');
      }
    },
  
    /*
     /**
     * Generates an SQL query that returns all foreign keys of a table.
     *
     * @param  {String} tableName  The name of the table.
     * @param  {String} schemaName The name of the schema.
     * @return {String}            The generated sql query.
     * @private
     */
    getForeignKeysQuery(tableName, schemaName) {
      return 'SELECT conname as constraint_name, pg_catalog.pg_get_constraintdef(r.oid, true) as condef FROM pg_catalog.pg_constraint r ' +
        `WHERE r.conrelid = (SELECT oid FROM pg_class WHERE relname = '${tableName}' LIMIT 1) AND r.contype = 'f' ORDER BY 1;`;
    },
  
    /**
     * Generates an SQL query that removes a foreign key from a table.
     *
     * @param  {String} tableName  The name of the table.
     * @param  {String} foreignKey The name of the foreign key constraint.
     * @return {String}            The generated sql query.
     * @private
     */
    dropForeignKeyQuery(tableName, foreignKey) {
      return 'ALTER TABLE ' + this.quoteTable(tableName) + ' DROP CONSTRAINT ' + this.quoteIdentifier(foreignKey) + ';';
    },
  
  
    setAutocommitQuery(value, options) {
      if (options.parent) {
        return;
      }
  
      // POSTGRES does not support setting AUTOCOMMIT = OFF as of 9.4.0
      // Additionally it does not support AUTOCOMMIT at all starting at v9.5
      // The assumption is that it won't be returning in future versions either
      // If you are on a Pg version that is not semver compliant e.g. '9.5.0beta2', which fails due to the 'beta' qualification, then you need to pass
      // the database version as "9.5.0" explicitly through the options param passed when creating the Sequelize instance under the key "databaseVersion"
      // otherwise Pg version "9.4.0" is assumed by default as per Sequelize 3.14.2.
      // For Pg versions that are semver compliant, this is auto-detected upon the first connection.
      if (!value || semver.gte(this.sequelize.options.databaseVersion, '9.4.0')) {
        return;
      }
  
      return AbstractQueryGenerator.setAutocommitQuery.call(this, value, options);
    }
  };
  
  module.exports = QueryGenerator;
  