/**
 * Module dependencies
 */

var _ = require('lodash');

var SelectBuilder = require('./select');
var WhereBuilder = require('./where');
var utils = require('./lib/utils');


/**
 * Sequel generator
 *
 * Given a Waterline Query Object build a SQL query.
 */

var Sequel = module.exports = function(schema, options) {

  // Store the schema values for the database structure
  this.schema = schema || {};

  // To solve a query, multiple query strings may be needed.
  this.queries = [];

  // Flag whether queries should be built using parameterized queries or not.
  // Default is true.
  this.parameterized = options && utils.object.hasOwnProperty(options, 'parameterized') ? options.parameterized : true;

  // Flag if things should be cast, useful for averages
  this.cast = options && utils.object.hasOwnProperty(options, 'casting') ? options.casting : false;

  // Flag whether the database is case-sensitive or not.
  // Default is true.
  this.caseSensitive = options && utils.object.hasOwnProperty(options, 'caseSensitive') ? options.caseSensitive : true;

  // Set the escape character, default is "
  this.escapeCharacter = options && utils.object.hasOwnProperty(options, 'escapeCharacter') ? options.escapeCharacter : '"';

  // Set if the database can return values from things such as an insert
  this.canReturnValues = options && utils.object.hasOwnProperty(options, 'canReturnValues') ? options.canReturnValues : false;

  // Determine if insert values should be escaped or not
  this.escapeInserts = options && utils.object.hasOwnProperty(options, 'escapeInserts') ? options.escapeInserts : false;

  // Determine if aliased tablenames in DELETE queries need to be referenced before the FROM, e.g.
  // DELETE `tableName` FROM `tableName` as `otherTableName` WHERE `otherTableName`.`foo` = "bar"
  // MySQL and Oracle require this, but it doesn't work in Postgresql.
  this.declareDeleteAlias = options && utils.object.hasOwnProperty(options, 'declareDeleteAlias') ? options.declareDeleteAlias : true;

  this.values = [];

  return this;
};

/**
 * Build a SQL Find Query using the defined schema.
 */

Sequel.prototype.find = function find(currentTable, queryObject) {

  // Step 1:
  // Build out the Select statements
  var selectObject = this.select(currentTable, queryObject);
  this.queries = selectObject.select;

  var whereObject;
  var childQueries;
  var query;
  var values;

  /**
   * Step 2 - Build out the parent query.
   */

  whereObject = this.simpleWhere(currentTable, queryObject);

  this.queries[0] += ' ' + whereObject.query;
  this.values[0] = whereObject.values;

  /**
   * Step 3 - Build out the child query templates.
   */

  childQueries = this.complexWhere(currentTable, queryObject);
  this.queries = this.queries.concat(childQueries);

  return {
    query: this.queries,
    values: this.values
  };

};


/**
 * Build a SQL Create Query.
 *
 */

Sequel.prototype.create = function create(currentTable, data) {

  var options = {
    parameterized: this.parameterized,
    escapeCharacter: this.escapeCharacter,
    escapeInserts: this.escapeInserts
  };

  // Transform the Data object into arrays used in a parameterized query
  var attributes = utils.mapAttributes(data, options);
  var columnNames = attributes.keys.join(', ');
  var paramValues = attributes.params.join(', ');

  // Build Query
  var query = 'INSERT INTO ' + utils.escapeName(currentTable, this.escapeCharacter) + ' (' + columnNames + ') values (' + paramValues + ')';

  if(this.canReturnValues) {
    query += ' RETURNING *';
  }

  return { query: query, values: attributes.values };
};

/**
 * Build a SQL Update Query.
 *
 */

Sequel.prototype.update = function update(currentTable, queryObject, data) {

  var options = {
    parameterized: this.parameterized,
    escapeCharacter: this.escapeCharacter,
    escapeInserts: this.escapeInserts
  };

  // Get the attribute identity (as opposed to the table name)
  var identity = _.find(_.values(this.schema), {tableName: currentTable}).identity;
  // Create the query with the tablename aliased as the identity (in case they are different)
  var query = 'UPDATE ' + utils.escapeName(currentTable, this.escapeCharacter) + ' AS ' + utils.escapeName(identity, this.escapeCharacter) + ' ';

  // Transform the Data object into arrays used in a parameterized query
  var attributes = utils.mapAttributes(data, options);

  // Update the paramCount
  var paramCount = attributes.params.length + 1;

  // Build SET string
  var str = '';
  for(var i=0; i < attributes.keys.length; i++) {
    str += attributes.keys[i] + ' = ' + attributes.params[i] + ', ';
  }

  // Remove trailing comma
  str = str.slice(0, -2);

  query += 'SET ' + str + ' ';

  // Add data values to this._values
  var values = attributes.values;

  // Build Criteria clause
  var whereObject = this.simpleWhere(currentTable, queryObject, { paramCount: paramCount });

  query += ' ' + whereObject.query;
  values = values.concat(whereObject.values);

  if(this.canReturnValues) {
    query += ' RETURNING *';
  }

  return {
    query: query,
    values: values
  };
};


/**
 * Build Delete SQL query.
 *
 */

Sequel.prototype.destroy = function destroy(currentTable, queryObject) {

  // Get the attribute identity (as opposed to the table name)
  var identity = _.find(_.values(this.schema), {tableName: currentTable}).identity;

  var query = 'DELETE ' + (this.declareDeleteAlias ? utils.escapeName(identity, this.escapeCharacter) : '') + ' FROM ' + utils.escapeName(currentTable, this.escapeCharacter) + ' AS ' + utils.escapeName(identity, this.escapeCharacter) + ' ';

  // Build Criteria clause
  var whereObject = this.simpleWhere(currentTable, queryObject);

  query += ' ' + whereObject.query;
  var values = whereObject.values;

  if(this.canReturnValues) {
    query += ' RETURNING *';
  }

  return {
    query: query,
    values: values
  };
};


/**
 * Build the select statements for a query.
 */

Sequel.prototype.select = function select(currentTable, queryObject) {
  var options = {
    escapeCharacter: this.escapeCharacter,
    cast: this.cast
  };

  return new SelectBuilder(this.schema, currentTable, queryObject, options);
};

/**
 * Build the where statements for a query.
 */

Sequel.prototype.simpleWhere = function simpleWhere(currentTable, queryObject, options) {
  var _options = {
    parameterized: this.parameterized,
    caseSensitve: this.caseSensitive,
    escapeCharacter: this.escapeCharacter
  };

  var where = new WhereBuilder(this.schema, currentTable, _options);
  return where.single(queryObject, options);
};

Sequel.prototype.complexWhere = function complexWhere(currentTable, queryObject, options) {
  var _options = {
    parameterized: this.parameterized,
    caseSensitve: this.caseSensitive,
    escapeCharacter: this.escapeCharacter
  };

  var where = new WhereBuilder(this.schema, currentTable, _options);
  return where.complex(queryObject, options);
};

@@ -6086,24 +6086,63 @@
 eCharacter,%0A
+    caseSensitive: this.caseSensitive,%0A
     cast: th
@@ -6149,16 +6149,16 @@
 is.cast%0A
-
   %7D;%0A%0A  
@@ -6442,32 +6442,33 @@
 ,%0A    caseSensit
+i
 ve: this.caseSen
@@ -6783,32 +6783,32 @@
 .parameterized,%0A
-
     caseSensitve
@@ -6805,16 +6805,17 @@
 seSensit
+i
 ve: this

fix typo