mysql.js

const database = require('mysql');
const Events = require('events');
const Parser = require('url');
const queries = {};
const columns_cache = {};
const pools_cache = {};
const REG_COLUMN = /^(\!{1,}|\s)*/;
const REG_COLUMN_CAST = /\`]/g;
const REG_ARGUMENT = /\?/g;
const REG_PARAMS = /\#\d+\#/g;
const REG_QUERY = /\*/i;

require('./index');

function SqlBuilder(skip, take) {
	this.builder = [];
	this._order = null;
	this._skip = skip >= 0 ? skip : 0;
	this._take = take >= 0 ? take : 0;
	this._set = null;
	this._fn;
	this._join;
	this._fields;
	this._schema;
	this._primary;
	this._group;
	this._having;
	this._is = false;
	this.hasOperator = false;
}

SqlBuilder.prototype = {
	get data() {
		return this._set;
	}
};

SqlBuilder.prototype.replace = function(builder, reference) {
	var self = this;

	self.builder = reference ? builder.builder : builder.builder.slice(0);

	if (builder._order)
		self._order = reference ? builder._order : builder._order.slice(0);

	self._skip = builder._skip;
	self._take = builder._take;

	if (builder._set)
		self._set = reference ? builder._set : copy(builder._set);

	if (builder._fn)
		self._fn = reference ? builder._fn : copy(builder._fn);

	if (builder._join)
		self._join = reference ? builder._join : builder._join.slice(0);

	if (builder._fields)
		self._fields = builder._fields;

	if (builder._schema)
		self._schema = builder._schema;

	if (builder._primary)
		self._primary = builder._primary;

	self._is = builder._is;
	self.hasOperator = builder.hasOperator;
	return self;
};

function copy(source) {

	var keys = Object.keys(source);
	var i = keys.length;
	var target = {};

	while (i--) {
		var key = keys[i];
		target[key] = source[key];
	}

	return target;
};

SqlBuilder.prototype.clone = function() {
	var builder = new SqlBuilder(0, 0, this);
	return builder.replace(this);
};

SqlBuilder.prototype.join = function(name, on, type) {
	var self = this;
	if (!self._join)
		self._join = [];

	if (!type)
		type = 'left';

	self._join.push(type + ' join ' + name + ' on ' + on);
	return self;
};

SqlBuilder.prototype.set = function(name, value) {
	var self = this;
	if (!self._set)
		self._set = {};

	if (typeof(name) === 'string') {
		self._set[name] = value === '$' ? '#00#' : value;
		return self;
	}

	var keys = Object.keys(name);

	for (var i = 0, length = keys.length; i < length; i++) {
		var key = keys[i];
		var val = name[key];
		if (val !== undefined)
			self._set[key] = val === '$' ? '#00#' : val;
	}

	return self;
};

SqlBuilder.prototype.primary = SqlBuilder.prototype.primaryKey = function(name) {
	this._primary = name;
	return this;
};

SqlBuilder.prototype.remove = SqlBuilder.prototype.rem = function(name) {
	if (this._set)
		this._set[name] = undefined;
	return this;
};

SqlBuilder.prototype.raw = function(name, value) {
	var self = this;
	if (!self._set)
		self._set = {};
	self._set['!' + name] = value;
	return self;
};

SqlBuilder.prototype.inc = function(name, type, value) {

	var self = this;
	var can = false;

	if (!self._set)
		self._set = {};

	if (value === undefined) {
		value = type;
		type = '+';
		can = true;
	}

	if (typeof(name) === 'string') {

		if (can && typeof(value) === 'string') {
			type = value[0];
			switch (type) {
				case '+':
				case '-':
				case '*':
				case '/':
					value = value.substring(1).parseFloat();
					break;
				default:
					type = '+';
					value = value.parseFloat();
					break;
			}
 		} else {
 			type = '+';
 			if (value == null)
 				value = 1;
 		}

		if (!value)
			return self;

		name = type + name;
		self._set[name] = value === '$' ? '#00#' : value;
		return self;
	}

	var keys = Object.keys(name);

	for (var i = 0, length = keys.length; i < length; i++) {
		var key = keys[i];
		name[key] && self.inc(key, name[key]);
	}

	return self;
};

SqlBuilder.prototype.sort = function(name, desc) {
	return this.order(name, desc);
};

SqlBuilder.prototype.order = function(name, desc) {

	var self = this;
	if (!self._order)
		self._order = [];

	var key = '<' + name + '.' + self._schema + '.' + (desc || 'false') + '>';
	if (columns_cache[key]) {
		self._order.push(columns_cache[key]);
		return self;
	}

	var lowered = name.toLowerCase();

	if (lowered.lastIndexOf('desc') !== -1 || lowered.lastIndexOf('asc') !== -1) {
		columns_cache[key] = SqlBuilder.column(name, self._schema);
		self._order.push(columns_cache[key]);
		return self;
	} else if (typeof(desc) === 'boolean')
		desc = desc === true ? 'DESC' : 'ASC';
	else
		desc = 'ASC';

	columns_cache[key] = SqlBuilder.column(name, self._schema) + ' ' + desc;
	self._order.push(columns_cache[key]);
	return self;
};

SqlBuilder.prototype.skip = function(value) {
	var self = this;
	self._skip = self.parseInt(value);
	return self;
};

SqlBuilder.prototype.limit = function(value) {
	return this.take(value);
};

SqlBuilder.prototype.page = function(value, max) {
	var self = this;
	value = self.parseInt(value) - 1;
	max = self.parseInt(max);
	if (value < 0)
		value = 0;
	self._skip = value * max;
	self._take = max;
	return self;
};

SqlBuilder.prototype.parseInt = function(num) {
	if (typeof(num) === 'number')
		return num;
	if (!num)
		return 0;
	num = parseInt(num);
	if (isNaN(num))
		num = 0;
	return num;
};

SqlBuilder.prototype.take = function(value) {
	var self = this;
	self._take = self.parseInt(value);
	return self;
};

SqlBuilder.prototype.first = function() {
	var self = this;
	self._skip = 0;
	self._take = 1;
	return self;
};

SqlBuilder.prototype.where = function(name, operator, value) {
	return this.push(name, operator, value);
};

SqlBuilder.prototype.push = function(name, operator, value) {
	var self = this;

	if (value === undefined) {
		value = operator;
		operator = '=';
	} else if (operator === '!=')
		operator = '<>';

	var is = false;

	// I expect Agent.$$
	if (typeof(value) === 'function') {
		if (!self._fn)
			self._fn = {};
		var key = Math.floor(Math.random() * 1000000);
		self._fn[key] = value;
		value = '#' + key + '#';
		is = true;
	}

	self.checkOperator();
	self.builder.push(SqlBuilder.column(name, self._schema) + operator + (is ? value : SqlBuilder.escape(value)));
	self._is = true;
	return self;
};

SqlBuilder.prototype.random = function() {
	var self = this;
	if (!self._order)
		self._order = [];
	self._order.push('RAND()');
	return self;
};

SqlBuilder.prototype.checkOperator = function() {
	var self = this;
	!self.hasOperator && self.and();
	self.hasOperator = false;
	return self;
};

SqlBuilder.prototype.clear = function() {
	this._take = 0;
	this._skip = 0;
	this._order = null;
	this._set = null;
	this.builder = [];
	this.hasOperator = false;
	return this;
};

SqlBuilder.prototype.schema = function(name) {
	this._schema = name;
	return this;
};

SqlBuilder.prototype.fields = function() {
	var self = this;
	if (!self._fields)
		self._fields = '';

	if (arguments[0] instanceof Array) {
		var arr = arguments[0];
		for (var i = 0, length = arr.length; i < length; i++)
			self._fields += (self._fields ? ',' : '') + SqlBuilder.column(arr[i], self._schema);
	} else {
		for (var i = 0; i < arguments.length; i++)
			self._fields += (self._fields ? ',' : '') + SqlBuilder.column(arguments[i], self._schema);
	}

	return self;
};

SqlBuilder.prototype.field = function(name) {
	var self = this;
	if (!self._fields)
		self._fields = '';
	self._fields += (self._fields ? ',' : '') + SqlBuilder.column(name, self._schema);
	return self;
};

SqlBuilder.column = function(name, schema) {

	var cachekey = (schema ? schema + '.' : '') + name;
	var val = columns_cache[cachekey];
	if (val)
		return val;

	var raw = false;

	if (name[0] === '!') {
		raw = true;
		name = name.replace(REG_COLUMN, '');
	}

	var index = name.lastIndexOf('-->');
	var cast = '';
	var casting = function(value) {
		if (!cast)
			return value;
		return 'CAST(' + value + cast + ')';
	};

	if (index !== -1) {
		cast = name.substring(index).replace('-->', '').trim();
		name = name.substring(0, index).trim();
		switch (cast) {
			case 'integer':
			case 'int':
			case 'byte':
			case 'smallint':
			case 'number':
				cast = 'UNSIGNED';
				break;
		}
		cast = ' AS ' + cast;
	}

	var indexAS = name.toLowerCase().indexOf(' as');
	var plus = '';

	if (indexAS !== -1) {
		plus = name.substring(indexAS);
		name = name.substring(0, indexAS);
	} else if (cast)
		plus = ' as `' + name + '`';

	if (raw)
		return columns_cache[cachekey] = casting(name) + plus;

	name = name.replace(REG_COLUMN_CAST, '');
	var index = name.indexOf('.');
	if (index === -1)
		return columns_cache[cachekey] = casting((schema ? schema + '.' : '') + '`' + name + '`') + plus;
	return columns_cache[cachekey] = casting(name.substring(0, index) + '.`' + name.substring(index + 1) + '`') + plus;
};

SqlBuilder.prototype.group = function(names) {
	var self = this;

	if (names instanceof Array) {
		for (var i = 0, length = names.length; i < length; i++)
			names[i] = SqlBuilder.column(names[i], self._schema);
		self._group = 'GROUP BY ' + names.join(',');
	} else if (names) {
		var arr = new Array(arguments.length);
		for (var i = 0; i < arguments.length; i++)
			arr[i] = SqlBuilder.column(arguments[i.toString()], self._schema);
		self._group = 'GROUP BY ' + arr.join(',');
	} else
		self._group = undefined;

	return self;
};

SqlBuilder.prototype.having = function(condition) {
	var self = this;

	if (condition)
		self._having = 'HAVING ' + condition;
	else
		self._having = undefined;

	return self;
};

SqlBuilder.prototype.and = function() {
	var self = this;
	if (!self.builder.length)
		return self;
	self.hasOperator = true;
	self.builder.push('AND');
	return self;
};

SqlBuilder.prototype.or = function() {
	var self = this;
	if (!self.builder.length)
		return self;
	self.hasOperator = true;
	self.builder.push('OR');
	return self;
};

SqlBuilder.prototype.scope = function(fn) {
	var self = this;
	self.checkOperator();
	self.builder.push('(');
	self.hasOperator = true;
	fn.call(self);
	self.builder.push(')');
	return self;
};

SqlBuilder.prototype.in = function(name, value) {
	var self = this;
	if (!(value instanceof Array)) {
		self.where(name, value);
		return self;
	}
	self.checkOperator();
	var values = [];
	for (var i = 0, length = value.length; i < length; i++)
		values.push(SqlBuilder.escape(value[i]));
	self.builder.push(SqlBuilder.column(name, self._schema) + ' IN (' + values.join(',') + ')');
	self._is = true;
	return self;
};

SqlBuilder.prototype.like = function(name, value, where) {
	var self = this;
	var search;

	self.checkOperator();

	switch (where) {
		case 'beg':
		case 'begin':
			search = SqlBuilder.escape('%' + value);
			break;
		case '*':
			search = SqlBuilder.escape('%' + value + '%');
			break;
		case 'end':
			search = SqlBuilder.escape(value + '%');
			break;
		default:
			search = SqlBuilder.escape(value);
			break;
	}

	self.builder.push(SqlBuilder.column(name, self._schema) + ' LIKE ' + search);
	self._is = true;
	return self;
};

SqlBuilder.prototype.between = function(name, valueA, valueB) {
	var self = this;
	self.checkOperator();
	self.builder.push(SqlBuilder.column(name, self._schema) + ' BETWEEN ' + SqlBuilder.escape(valueA) + ' AND ' + SqlBuilder.escape(valueB));
	self._is = true;
	return self;
};

SqlBuilder.prototype.query = SqlBuilder.prototype.sql = function(sql) {
	var self = this;
	self.checkOperator();

	if (arguments.length > 1) {
		var indexer = 1;
		var argv = arguments;
		sql = sql.replace(REG_ARGUMENT, () => SqlBuilder.escape(argv[indexer++]));
	}

	self.builder.push(sql);
	self._is = true;
	return self;
};

SqlBuilder.prototype.toString = function(id, isCounter) {

	var self = this;
	var plus = '';
	var order = '';
	var join = '';

	if (self._join)
		join = self._join.join(' ');

	if (!isCounter) {
		if (self._order)
			order = ' ORDER BY ' + self._order.join(',');
		if (self._skip && self._take)
			plus = ' LIMIT ' + self._skip + ',' + self._take;
		else if (self._take)
			plus = ' LIMIT ' + self._take;
		else if (self._skip)
			plus = ' LIMIT ' + self._skip + ',row_count';
	}

	if (!self.builder.length)
		return (join ? ' ' + join : '') + (self._group ? ' ' + self._group : '') + (self._having ? ' ' + self._having : '') + order + plus;

	var where = self.builder.join(' ');

	if (id === undefined)
		id = null;

	if (self._fn)
		where = where.replace(REG_PARAMS, text => text === '#00#' ? SqlBuilder.escape(id) : SqlBuilder.escape(self._fn[parseInt(text.substring(1, text.length - 1))]));

	return (join ? ' ' + join : '') + (self._is ? ' WHERE ' : ' ') + where + (self._group ? ' ' + self._group : '') + (self._having ? ' ' + self._having : '') + order + plus;
};

SqlBuilder.prototype.make = function(fn) {
	var self = this;
	fn.call(self, self)
	return self.agent || self;
};

SqlBuilder.prototype.toQuery = function(query) {
	var self = this;
	return self._fields ? query.replace(REG_QUERY, self._fields) : query;
};

function Agent(options, error, id) {
	this.$conn = id === undefined ? JSON.stringify(options) : id;
	this.options = options;
	this.isErrorBuilder = typeof(global.ErrorBuilder) !== 'undefined' ? true : false;
	this.errors = this.isErrorBuilder ? error : null;
	this.clear();
	// Hidden:
	// this.time
	// this.$when;
}

Agent.prototype = {
	get $() {
		return new SqlBuilder(0, 0, this);
	},
	get $$() {
		var self = this;
		return function() {
			return self.$id;
		};
	}
};

Agent.prototype.__proto__ = Object.create(Events.EventEmitter.prototype, {
	constructor: {
		value: Agent,
		enumberable: false
	}
});

// Debug mode (output to console)
Agent.debug = false;

Agent.connect = function(conn, callback) {
	callback && callback(null);
	var id = (Math.random() * 1000000) >> 0;
	return function(error) {
		return new Agent(conn, error, id);
	};
};

Agent.prototype.clear = function() {
	this.db = null;
	this.command = [];
	this.done = null;
	this.last = null;
	this.id = null;
	this.$id = null;
	this.isCanceled = false;
	this.index = 0;
	this.isPut = false;
	this.skipCount = 0;
	this.skips = {};
	this.results = {};
	this.builders = {};

	if (this.$when)
		this.$when = undefined;

	if (this.errors && this.isErrorBuilder)
		this.errors.clear();
	else if (this.errors)
		this.errors = null;

	return this;
};

Agent.prototype.when = function(name, fn) {

	if (!this.$when)
		this.$when = {};

	if (!this.$when[name])
		this.$when[name] = [fn];
	else
		this.$when[name].push(fn);

	return this;
};

Agent.prototype.priority = function() {
	var self = this;
	var length = self.command.length - 1;

	if (!length)
		return self;

	var last = self.command[length];

	for (var i = length; i > -1; i--)
		self.command[i] = self.command[i - 1];

	self.command[0] = last;
	return self;
};

Agent.prototype.default = function(fn) {
	fn.call(this.results, this.results);
	return this;
};

Agent.query = function(name, query) {
	queries[name] = query;
	return Agent;
};

Agent.prototype.skip = function(name) {
	var self = this;
	if (name)
		self.skips[name] = true;
	else
		self.skipCount++;
	return self;
};

Agent.prototype.primaryKey = Agent.prototype.primary = function(name) {
	// compatibility with PG
	return this;
};

Agent.prototype.prepare = function(fn) {
	var self = this;
	self.command.push({ type: 'prepare', fn: fn });
	return self;
};

Agent.prototype.ifnot = function(name, fn) {
	var self = this;
	self.prepare(function(error, response, resume) {
		var value = response[name];
		if (value instanceof Array) {
			if (value.length)
				return resume();
		} else if (value)
			return resume();
		fn.call(self, error, response);
		setImmediate(resume);
	});
	return self;
};

Agent.prototype.ifexists = function(name, fn) {
	var self = this;
	self.prepare(function(error, response, resume) {

		var value = response[name];
		if (value instanceof Array) {
			if (!value.length)
				return resume();
		} else if (!value)
			return resume();

		fn.call(self, error, response);
		setImmediate(resume);
	});
	return self;
};

Agent.prototype.modify = function(fn) {
	var self = this;
	self.command.push({ type: 'modify', fn: fn });
	return self;
};

Agent.prototype.bookmark = function(fn) {
	var self = this;
	self.command.push({ type: 'bookmark', fn: fn });
	return self;
};

Agent.prototype.put = function(value) {
	var self = this;
	self.command.push({ type: 'put', value: value, disable: value == null });
	return self;
};

Agent.prototype.lock = function() {
	return this.put(this.$$);
};

Agent.prototype.unlock = function() {
	this.command.push({ 'type': 'unput' });
	return this;
};

Agent.prototype.query = function(name, query, params) {
	return this.push(name, query, params);
};

Agent.prototype.push = function(name, query, params) {
	var self = this;

	if (typeof(query) !== 'string') {
		params = query;
		query = name;
		name = self.index++;
	}

	var is = false;

	if (!params) {
		is = true;
		params = new SqlBuilder(0, 0, self);
	}

	if (queries[query])
		query = queries[query];

	self.command.push({ type: 'push', name: name, query: query, condition: params, first: isFIRST(query) });
	self.builders[name] = params;
	return is ? params : self;
};

Agent.prototype.validate = function(fn, error, reverse) {
	var self = this;
	var type = typeof(fn);

	if (typeof(error) === 'boolean') {
		reverse = error;
		error = undefined;
	}

	if (type === 'string' && error === undefined) {
		// checks the last result
		error = fn;
		fn = undefined;
	}

	if (type === 'function') {
		self.command.push({ type: 'validate', fn: fn, error: error });
		return self;
	}

	if (type === 'string' && typeof(error) === 'function' && typeof(reverse) === 'string')
		return self.validate2(fn, error, reverse);

	var exec;

	if (reverse) {
		exec = function(err, results, next) {
			var id = fn == null ? self.last : fn;
			if (id == null)
				return next(true);
			var r = results[id];
			if (r instanceof Array)
				return next(r.length === 0);
			if (r)
				return next(false);
			next(true);
		};
	} else {
		exec = function(err, results, next) {
			var id = fn == null ? self.last : fn;
			if (id == null)
				return next(false);
			var r = results[id];
			if (r instanceof Array)
				return next(r.length > 0);
			if (r)
				return next(true);
			next(false);
		};
	}

	self.command.push({ type: 'validate', fn: exec, error: error });
	return self;
};

// validate2('result', n => n.length > 0, 'error');
Agent.prototype.validate2 = function(name, fn, err) {
	var self = this;
	var type = typeof(fn);

	if (type === 'string') {
		type = err;
		err = fn;
		fn = type;
	}

	var validator = function(err, results, next) {
		if (fn(results[name]))
			return next(true);
		err.push(err || name);
		next(false);
	};

	self.command.push({ type: 'validate', fn: validator, error: error });
	return self;
};

Agent.prototype.cancel = function(fn) {
	return this.validate(fn);
};

Agent.prototype.begin = function() {
	var self = this;
	self.command.push({ type: 'begin' });
	return self;
};

Agent.prototype.end = function() {
	var self = this;
	self.command.push({ type: 'end' });
	return self;
};

Agent.prototype.commit = function() {
	return this.end();
};

function prepareValue(value) {
	return value == null ? null : typeof(value) === 'function' ? value() : value;
}

Agent.prototype._insert = function(item) {

	var self = this;
	var values = item.condition._set;
	var keys = Object.keys(values);

	var columns = [];
	var columns_values = [];
	var params = [];

	for (var i = 0, length = keys.length; i < length; i++) {
		var key = keys[i];
		var value = values[key];

		var isRAW = key[0] === '!';
		if (isRAW)
			key = key.substring(1);

		if (key[0] === '$' || value === undefined)
			continue;

		switch (key[0]) {
			case '+':
			case '-':
			case '*':
			case '/':
				key = key.substring(1);
				if (!value)
					value = 1;
				break;
			default:
				break;
		}

		columns.push('`' + key + '`');

		if (isRAW) {
			columns_values.push(value);
			continue;
		}

		columns_values.push('?');

		var type = typeof(value);
		if (type === 'function')
			value = value();

		if (type === 'string')
			value = value.trim();

		params.push(value === undefined ? null : value);
	}

	item.$query = 'INSERT INTO ' + item.table + ' (' + columns.join(',') + ') VALUES(' + columns_values.join(',') + ')';
	item.$params = params;
	item.first = true;

	return item;
};

Agent.prototype._update = function(item) {

	var values = item.condition._set;
	var keys = Object.keys(values);

	var columns = [];
	var params = [];

	for (var i = 0, length = keys.length; i < length; i++) {
		var key = keys[i];
		var value = values[key];

		var isRAW = key[0] === '!';
		if (isRAW)
			key = key.substring(1);

		if (key[0] === '$' || value === undefined)
			continue;

		var type = typeof(value);
		if (type === 'function')
			value = value();

		if (type === 'string')
			value = value.trim();

		switch (key[0]) {
			case '+':
				key = key.substring(1);
				columns.push('`' + key + '`=COALESCE(`' + key + '`,0)+?');
				if (!value)
					value = 1;
				break;
			case '-':
				key = key.substring(1);
				columns.push('`' + key + '`=COALESCE(`' + key + '`,0)-?');
				if (!value)
					value = 1;
				break;
			case '*':
				key = key.substring(1);
				columns.push('`' + key + '`=COALESCE(`' + key + '`,0)*?');
				if (!value)
					value = 1;
				break;
			case '/':
				key = key.substring(1);
				columns.push('`' + key + '`=COALESCE(`' + key + '`,0)/?');
				if (!value)
					value = 1;
				break;
			default:
				if (isRAW)
					columns.push('`' + key + '`=' + value);
				else
					columns.push('`' + key + '`=?');
				break;
		}

		!isRAW && params.push(value === undefined ? null : value);
	}

	item.$query = 'UPDATE ' + item.table + ' SET ' + columns.join(',') + item.condition.toString(this.id);
	item.$params = params;
	item.column = 'affectedRows';
	item.first = true;

	return item;
};

Agent.prototype._select = function(item) {
	item.query = 'SELECT * FROM ' + item.table;
	item.$query = item.condition.toQuery(item.query) + item.condition.toString(this.id);
	item.first = item.condition._take === 1;
	return item;
};

Agent.prototype._compare = function(item) {
	var keys = item.keys ? item.keys : item.condition._fields ? item.condition._fields.split(',') : Object.keys(item.value);

	if (!item.condition._fields)
		item.condition.fields.apply(item.condition, keys);

	item.query = 'SELECT * FROM ' + item.table;
	item.$query = item.condition.toQuery(item.query) + item.condition.toString(this.id);
	item.first = item.condition._take === 1;
	return item;
};

Agent.prototype._delete = function(item) {
	item.$query = item.query + item.condition.toString(this.id);
	item.first = true;
	item.column = 'affectedRows';
	return item;
};

Agent.prototype._query = function(item) {
	if (item.condition instanceof SqlBuilder) {
		item.$query = (item.scalar ? item.query : item.condition.toQuery(item.query)) + item.condition.toString(this.id, item.scalar);
		return item;
	}
	item.$query = item.query;
	item.$params = item.condition;
	return item;
};

Agent.prototype.save = function(name, table, insert, maker) {

	if (typeof(table) === 'boolean') {
		maker = insert;
		insert = table;
		table = name;
		name = undefined;
	}

	var self = this;
	if (insert)
		maker(self.insert(name, table), true);
	else
		maker(self.update(name, table), false);

	return self;
};

Agent.prototype.insert = function(name, table) {

	var self = this;

	if (typeof(table) !== 'string') {
		table = name;
		name = self.index++;
	}

	var condition = new SqlBuilder(0, 0, self);
	self.command.push({ type: 'insert', table: table, name: name, condition: condition });
	self.builders[name] = condition;
	return condition;
};

Agent.prototype.select = function(name, table) {

	var self = this;

	if (typeof(table) !== 'string') {
		table = name;
		name = self.index++;
	}

	var condition = new SqlBuilder(0, 0, self);
	self.command.push({ type: 'select', name: name, table: table, condition: condition });
	self.builders[name] = condition;
	return condition;
};

Agent.prototype.compare = function(name, table, obj, keys) {

	var self = this;

	if (typeof(table) !== 'string') {
		keys = obj;
		obj = table;
		table = name;
		name = self.index++;
	}

	var condition = new SqlBuilder(0, 0, self);
	condition.first();
	self.command.push({ type: 'compare', name: name, table: table, condition: condition, value: obj, keys: keys });
	self.builders[name] = condition;
	return condition;
};

Agent.prototype.listing = function(name, table, column) {

	var self = this;
	if (typeof(table) !== 'string') {
		table = name;
		name = self.index++;
	}

	var key ='$listing_' + name;
	var condition = new SqlBuilder(0, 0, self);
	self.command.push({ type: 'query', query: 'SELECT COUNT(' + (column || '*') + ') as sqlagentcolumn FROM ' + table, name: key + '_count', condition: condition, first: true, column: 'sqlagentcolumn', datatype: 1, scalar: true });
	self.command.push({ type: 'select', name: key + '_items', table: table, condition: condition, listing: key, target: name });
	self.builders[name] = condition;
	return condition;
};

Agent.prototype.find = Agent.prototype.builder = function(name) {
	return this.builders[name];
};

Agent.prototype.exists = function(name, table) {
	var self = this;

	if (typeof(table) !== 'string') {
		table = name;
		name = self.index++;
	}

	var condition = new SqlBuilder(0, 0, self);
	condition.first();
	self.command.push({ type: 'query', query: 'SELECT 1 as sqlagentcolumn_e FROM ' + table, name: name, condition: condition, first: true, column: 'sqlagentcolumn_e', scalar: true });
	self.builders[name] = condition;
	return condition;
};

Agent.prototype.count = function(name, table, column) {
	var self = this;

	if (typeof(table) !== 'string') {
		table = name;
		name = self.index++;
	}

	var condition = new SqlBuilder(0, 0, self);
	self.command.push({ type: 'query', query: 'SELECT COUNT(' + (column || '*') + ') as sqlagentcolumn FROM ' + table, name: name, condition: condition, first: true, column: 'sqlagentcolumn', datatype: 1, scalar: true });
	self.builders[name] = condition;
	return condition;
};

Agent.prototype.max = function(name, table, column) {
	var self = this;

	if (typeof(table) !== 'string') {
		table = name;
		name = self.index++;
	}

	var condition = new SqlBuilder(0, 0, self);
	self.command.push({ type: 'query', query: 'SELECT MAX(' + column + ') as sqlagentcolumn FROM ' + table, name: name, condition: condition, first: true, column: 'sqlagentcolumn', datatype: 1, scalar: true });
	self.builders[name] = condition;
	return condition;
};

Agent.prototype.min = function(name, table, column) {
	var self = this;
	if (typeof(table) !== 'string') {
		table = name;
		name = self.index++;
	}

	var condition = new SqlBuilder(0, 0, self);
	self.command.push({ type: 'query', query: 'SELECT MIN(' + column + ') as sqlagentcolumn FROM ' + table, name: name, condition: condition, first: true, column: 'sqlagentcolumn', datatype: 1, scalar: true });
	self.builders[name] = condition;
	return condition;
};

Agent.prototype.avg = function(name, table, column) {
	var self = this;
	if (typeof(table) !== 'string') {
		table = name;
		name = self.index++;
	}

	var condition = new SqlBuilder(0, 0, self);
	self.command.push({ type: 'query', query: 'SELECT AVG(' + column + ') as sqlagentcolumn FROM ' + table, name: name, condition: condition, first: true, column: 'sqlagentcolumn', datatype: 1, scalar: true });
	self.builders[name] = condition;
	return condition;
};

Agent.prototype.update = function(name, table) {

	var self = this;

	if (typeof(table) !== 'string') {
		table = name;
		name = self.index++;
	}

	var condition = new SqlBuilder(0, 0, self);
	self.command.push({ type: 'update', table: table, name: name, condition: condition });
	self.builders[name] = condition;
	return condition;
};

Agent.prototype.delete = function(name, table) {

	var self = this;

	if (typeof(table) !== 'string') {
		table = name;
		name = self.index++;
	}

	var condition = new SqlBuilder(0, 0, self);
	self.command.push({ type: 'delete', query: 'DELETE FROM ' + table, name: name, condition: condition });
	self.builders[name] = condition;
	return condition;
};

Agent.prototype.remove = function(name, table) {
	return this.delete(name, table);
};

Agent.prototype.destroy = function(name) {
	var self = this;
	for (var i = 0, length = self.command.length; i < length; i++) {
		var item = self.command[i];
		if (item.name !== name)
			continue;
		self.command.splice(i, 1);
		delete self.builders[name];
		return true;
	}
	return false;
};

Agent.prototype.expected = function(name, index, property) {

	var self = this;

	if (typeof(index) === 'string') {
		property = index;
		index = undefined;
	}

	return function() {
		var output = self.results[name];
		if (!output)
			return null;
		if (index === undefined)
			return property === undefined ? output : output[property];
		output = output[index];
		return output ? output[property] : null;
	};
};

Agent.prototype.close = function() {
	var self = this;
	self.done && self.done();
	self.done = null;
	return self;
};

Agent.prototype.rollback = function(where, e, next) {
	var self = this;
	self.errors && self.errors.push(e);
	self.command.length = 0;
	if (!self.isTransaction)
		return next();
	self.isRollback = true;
	self.end();
	next();
};

Agent.prototype._prepare = function(callback) {

	var self = this;

	self.isRollback = false;
	self.isTransaction = false;

	if (!self.errors)
		self.errors = self.isErrorBuilder ? new global.ErrorBuilder() : [];

	self.command.sqlagent(function(item, next) {

		if (item.type === 'validate') {
			try {
				var tmp = item.fn(self.errors, self.results, function(output) {
					if (output === true || output === undefined)
						return next();
					// reason
					if (typeof(output) === 'string')
						self.errors.push(output);
					else if (item.error)
						self.errors.push(item.error);
					// we have error
					if (self.isTransaction) {
						self.command.length = 0;
						self.isRollback = true;
						self.end();
						next();
					} else
						next(false);
				});

				var type = typeof(tmp);
				if (type !== 'boolean' && type !== 'string')
					return;
				if (tmp === true || tmp === undefined)
					return next();
				// reason
				if (typeof(tmp) === 'string')
					self.errors.push(tmp);
				else if (item.error)
					self.errors.push(item.error);
				// we have error
				if (self.isTransaction) {
					self.command.length = 0;
					self.isRollback = true;
					self.end();
					next();
				} else
					next(false);
				return;
			} catch (e) {
				self.rollback('validate', e, next);
			}
			return;
		}

		if (item.type === 'bookmark') {
			try {
				item.fn(self.errors, self.results);
				return next();
			} catch (e) {
				self.rollback('bookmark', e, next);
			}
		}

		if (item.type === 'modify') {
			try {
				item.fn(self.results);
				next();
			} catch (e) {
				self.rollback('modify', e, next);
			}
			return;
		}

		if (item.type === 'prepare') {
			try {
				item.fn(self.errors, self.results, function() {
					next();
				});
			} catch (e) {
				self.rollback('prepare', e, next);
			}
			return;
		}

		if (item.type === 'unput') {
			self.isPut = false;
			next();
			return;
		}

		if (item.type === 'put') {
			if (item.disable)
				self.$id = null;
			else
				self.$id = typeof(item.value) === 'function' ? item.value() : item.value;
			self.isPut = !self.disable;
			next();
			return;
		}

		if (self.skipCount) {
			self.skipCount--;
			next();
			return;
		}

		if (typeof(item.name) === 'string') {
			if (self.skips[item.name] === true) {
				next();
				return;
			}
		}

		switch (item.type) {
			case 'select':
				self._select(item);
				break;
			case 'update':
				self._update(item);
				break;
			case 'insert':
				self._insert(item);
				break;
			case 'delete':
				self._delete(item);
				break;
			case 'compare':
				self._compare(item);
				break;
			default:
				self._query(item);
				break;
		}

		if (item.type !== 'begin' && item.type !== 'end') {

			if (!item.first)
				item.first = isFIRST(item.$query);

			Agent.debug && console.log(self.debugname, item.name, item.$query);
			self.emit('query', item.name, item.$query, item.$params);

			self.db.query(item.$query, item.$params, function(err, rows) {
				self.$bind(item, err, rows);
				next();
			});

			return;
		}

		if (item.type === 'begin') {
			Agent.debug && console.log(self.debugname, 'begin transaction');
			self.db.query('BEGIN', function(err) {
				if (err) {
					self.errors.push(err.message);
					self.command.length = 0;
					next(false);
					return;
				}
				self.isTransaction = true;
				self.isRollback = false;
				next();
			});
			return;
		}

		if (item.type === 'end') {
			self.isTransaction = false;
			if (self.isRollback) {
				Agent.debug && console.log(self.debugname, 'rollback transaction');
				self.db.query('ROLLBACK', function(err) {
					if (!err)
						return next();
					self.command.length = 0;
					self.push(err.message);
					next(false);
				});
				return;
			}

			Agent.debug && console.log(self.debugname, 'commit transaction');

			self.db.query('COMMIT', function(err) {
				if (!err)
					return next();
				self.errors.push(err.message);
				self.command.length = 0;
				self.db.query('ROLLBACK', function(err) {
					err && self.errors.push(err.message);
					next();
				});
			});
			return;
		}

	}, function() {

		if (Agent.debug) {
			self.time = Date.now() - self.debugtime;
			console.log(self.debugname, '----- done (' + self.time + ' ms)');
		}

		self.index = 0;
		self.done && self.done();
		self.done = null;
		var err = null;

		if (self.isErrorBuilder) {
			if (self.errors.hasError())
				err = self.errors;
		} else if (self.errors.length)
			err = self.errors;

		self.emit('end', err, self.results, self.time);
		callback && callback(err, self.returnIndex !== undefined ? self.results[self.returnIndex] : self.results);
	});

	return self;
};

Agent.prototype.$bindwhen = function(name) {
	var self = this;
	if (!self.$when)
		return self;
	var tmp = self.$when[name];
	if (!tmp)
		return self;
	for (var i = 0, length = tmp.length; i < length; i++)
		tmp[i](self.errors, self.results, self.results[name]);
	return self;
};

Agent.prototype.$bind = function(item, err, rows) {

	var self = this;

	if (err) {
		self.errors.push(item.name + ': ' + err.message);
		if (self.isTransaction)
			self.isRollback = true;
		self.last = item.name;
		return;
	}

	if (item.type === 'insert') {
		self.id = rows.insertId;
		if (!self.isPut)
			self.$id = self.id;
		self.results[item.name] = { identity: self.id };
	} else if (item.first && item.column) {
		var tmp = rows instanceof Array ? rows.length ? rows[0] : null : rows;
		if (tmp)
			self.results[item.name] = item.column === 'sqlagentcolumn_e' ? true : item.datatype === 1 ? parseFloat(tmp[item.column] || 0) : tmp[item.column];
	} else if (item.first)
		self.results[item.name] = rows instanceof Array ? rows[0] : rows;
	else
		self.results[item.name] = rows;

	if (item.listing) {
		var obj = {};
		obj.count = self.results[item.listing + '_count'];
		obj.items = self.results[item.listing + '_items'];
		self.results[item.target] = obj;
		self.results[item.listing + '_count'] = null;
		self.results[item.listing + '_items'] = null;
	} else if (item.type === 'compare') {

		var keys = item.keys;
		var val = self.results[item.name];
		var diff;

		if (val) {
			diff = [];
			for (var i = 0, length = keys.length; i < length; i++) {
				var key = keys[i];
				var a = val[key];
				var b = item.value[key];
				a != b && diff.push(key);
			}
		} else
			diff = keys;

		self.results[item.name] = diff.length ? { diff: diff, record: val, value: item.value } : false;
	}

	self.emit('data', item.target || item.name, self.results);
	self.last = item.name;
	self.$bindwhen(item.name);
};

Agent.prototype.exec = function(callback, returnIndex) {

	var self = this;

	if (Agent.debug) {
		self.debugname = 'sqlagent/mysql (' + Math.floor(Math.random() * 1000) + ')';
		self.debugtime = Date.now();
	}

	if (returnIndex !== undefined && typeof(returnIndex) !== 'boolean')
		self.returnIndex = returnIndex;
	else
		self.returnIndex = undefined;

	if (!self.command.length) {
		callback && callback.call(self, null, {});
		return self;
	}

	Agent.debug && console.log(self.debugname, '----- exec');

	if (!pools_cache[self.$conn]) {
		if (typeof(self.options) === 'string') {
			var options = Parser.parse(self.options);
			self.options = {};
			self.options.host = options.host.split(':')[0];
			if (options.pathname && options.pathname.length > 1)
				self.options.database = options.pathname.substring(1);
			if (options.port)
				self.options.port = options.port;
			var auth = options.auth;
			if (auth) {
				auth = auth.split(':');
				self.options.user = auth[0];
				self.options.password = auth[1];
			}
		}
		pools_cache[self.$conn] = database.createPool(self.options);
	}

	pools_cache[self.$conn].getConnection(function(err, connection) {

		if (err) {
			if (!self.errors)
				self.errors = self.isErrorBuilder ? new global.ErrorBuilder() : [];
			self.errors.push(err);
			callback.call(self, self.errors);
			return;
		}

		self.done = () => connection.release();
		self.db = connection;
		self._prepare(callback);
	});

	return self;
};

Agent.destroy = function() {
	var keys = Object.keys(pools_cache);
	for (var i = 0, length = keys.length; i < length; i++)
		pools_cache[keys[i]].end(function(){});
};

Agent.prototype.$$exec = function(returnIndex) {
	var self = this;
	return function(callback) {
		return self.exec(callback, returnIndex);
	}
};

Agent.escape = Agent.prototype.escape = SqlBuilder.escape = SqlBuilder.prototype.escape = function(value) {

	if (value == null)
		return 'null';

	var type = typeof(value);

	if (type === 'function') {
		value = value();

		if (value == null)
			return 'null';

		type = typeof(value);
	}

	if (type === 'boolean')
		return value ? '1' : '0';

	if (type === 'number')
		return value.toString();

	if (type === 'string')
		return database.escape(value);

	if (value instanceof Array)
		return database.escape(value.join(','));

	if (value instanceof Date)
		return database.escape(dateToString(value));

	return database.escape(value.toString());
};

function dateToString(dt) {
	var arr = [];
	arr.push(dt.getFullYear().toString());
	arr.push((dt.getMonth() + 1).toString());
	arr.push(dt.getDate().toString());
	arr.push(dt.getHours().toString());
	arr.push(dt.getMinutes().toString());
	arr.push(dt.getSeconds().toString());
	for (var i = 1, length = arr.length; i < length; i++) {
		if (arr[i].length === 1)
			arr[i] = '0' + arr[i];
	}
	return arr[0] + '-' + arr[1] + '-' + arr[2] + ' ' + arr[3] + ':' + arr[4] + ':' + arr[5];
}

function prepare_params(params) {
	if (!params)
		return params;
	for (var i = 0, length = params.length; i < length; i++) {
		var param = params[i];
		if (typeof(param) === 'function')
			params[i] = param(params);
	}
	return params;
}

function isFIRST(query) {
	return query ? query.substring(query.length - 7).toLowerCase() === 'limit 1' : false;
}

Agent.init = function(conn, debug) {
	Agent.debug = debug ? true : false;
	var id = (Math.random() * 100000) >> 0;
	framework.database = function(errorBuilder) {
		return new Agent(conn, errorBuilder, id);
	};
};

module.exports = Agent;
global.SqlBuilder = SqlBuilder;