#!/usr/bin/env node

var program = require('commander');
var DB = require("./DB");

var db1, db2, all = {};
var db1Tip = '-1, --db1=dbuser:dbpassword@dbhost~database#dbport';
var db2Tip = '-2, --db2=dbuser:dbpassword@dbhost~database#dbport+sshuser:sshpassword@sshhost#sshport';
var typeTip = '-d, --diff=table|procedure|function|view|data';
var tableTip = '-t, --table=db1table:db2table';

program
	.on('--help', function(){
		console.log('  Examples:');
		console.log('');
		console.log('	mysqldiff --db1=root:password@127.0.0.1~database1 --db2=root:password@127.0.0.1~database2');
		console.log('	mysqldiff -1 root:password@127.0.0.1~database1 -2 root:password@127.0.0.1~database2');
		console.log('	mysqldiff -1 dbuser:dbpassword@dbhost~database -2 dbuser:dbpassword@dbhost~database:dbport+sshuser:sshpassword@sshhost#sshport');
		console.log('');
	})
	.version('0.0.1')
	.description('Mysql database table structure, stored procedures, functions, views, table data comparison tool, the difference generated SQL, support SSH connection.')
	.option('-1, --db1 <server>', db1Tip)
	.option('-2, --db2 <server>', db2Tip)
	.option('-d, --diff <type>', typeTip)
	.option('-t, --table <table>', tableTip)
	.option('-r, --reverse', '-r, --reverse=Exchange db1 and db2 configuration')
	.parse(process.argv);

function check() {
	if (!program.db1 || program.db1.indexOf(':') == -1 || program.db1.indexOf('@') == -1 || program.db1.indexOf('~') == -1) return console.log('--db1 Parameter error! ', db1Tip);
	if (!program.db2 || program.db2.indexOf(':') == -1 || program.db2.indexOf('@') == -1 || program.db2.indexOf('~') == -1) return console.log('--db2 Parameter error! ', db2Tip);
	if (program.diff && !/^(table|procedure|function|view|data)$/i.test(program.diff)) return console.log('--diff Parameter error! ', typeTip);
	if (program.diff == 'data' && !program.table) return console.log('--table Parameter error! ', tableTip);
	return true;
}
function start() {
	if (!check()) return;
	console.log('-- Connecting to the database: db1 & db2 ...');
	db1 = new DB(program.reverse ? program.db2 : program.db1);
	db2 = new DB(program.reverse ? program.db1 : program.db2);
	db1.connect().then(function(v){
		if (!v) return console.log('-- Database db1: connection successful!');
		console.error('-- Database db1 connection failed:', v.message);
		return exit();
	}).then(db2.connect).then(function(v){
		if (v) console.error('-- Database db2 connection failed:', v.message);
		else console.log('-- Database db2: connection success!');
		if (v) return exit();
		Promise.all([getTables(), getParameters(), getViews(), getDatas()]).then(allDesc).then(diff).then(function(v) {
			if (v.sqlUp && (program.diff == 'table' || !program.diff)) {
				console.log('-- Database db2 needs to update the table structure:');
				for(var k in v.sql) console.log(v.sql[k]);
			}
			if (v.procUp && (program.diff == 'procedure' || !program.diff)) {
				console.log('-- Database db2 need to update the stored procedure:');
				for(var k in v.proc) console.log(v.proc[k]);
			}
			if (v.funcUp && (program.diff == 'function' || !program.diff)) {
				console.log('-- Database db2 need to update the function:');
				for(var k in v.func) console.log(v.func[k]);
			}
			if (v.viewUp && (program.diff == 'view' || !program.diff)) {
				console.log('-- Database db2 needs to update the view:');
				for(var k in v.view) console.log(v.view[k]);
			}
			if (v.dataUp && program.diff && program.diff == 'data') {
				console.log('-- Database db2 needs to update the data:');
				for(var k in v.data) console.log(v.data[k]);
			}
			if (!v.sqlUp && !v.procUp && !v.funcUp && !v.viewUp && !v.dataUp)
				console.log('-- Is the latest, do not need to update!');
			exit();
		});
	}).catch(function(v){ });
}
function diff(v) {
	var d = { sql: {}, proc: {}, func: {}, view: {}, data: {}, sqlUp: 0, procUp: 0, funcUp: 0, viewUp: 0, dataUp: 0 };
	if (program.diff == 'table' || !program.diff) {
		for(var i = 0, len = v[0][0].length; i < len; i++) {
			var name = v[0][0][i];
			var table1 = v[0][1][name];
			var table2 = v[0][2][name];
			var sql = diffTable(name, table1, table2);
			if (sql && sql.length > 0) {
				d.sql[name] = sql;
				d.sqlUp = 1;
			}
		}
	}
	if (program.diff == 'procedure' || !program.diff) {
		for(var i = 0, len = v[1][0].length; i < len; i++) {
			var name = v[1][0][i];
			var proc1 = v[1][2][name];
			var proc2 = v[1][3][name];
			var sql = diffProcedure(name, proc1, proc2, 'PROCEDURE');
			if (sql && sql.length > 0) {
				d.proc[name] = sql;
				d.procUp = 1;
			}
		}
	}
	if (program.diff == 'function' || !program.diff) {
		for(var i = 0, len = v[1][1].length; i < len; i++) {
			var name = v[1][1][i];
			var proc1 = v[1][2][name];
			var proc2 = v[1][3][name];
			var sql = diffProcedure(name, proc1, proc2, 'FUNCTION');
			if (sql && sql.length > 0) {
				d.func[name] = sql;
				d.funcUp = 1;
			}
		}
	}
	if (program.diff == 'view' || !program.diff) {
		for(var i = 0, len = v[2][0].length; i < len; i++) {
			var name = v[2][0][i];
			var view1 = v[2][1][name];
			var view2 = v[2][2][name];
			var sql = diffProcedure(name, view1, view2, 'VIEW');
			if (sql && sql.length > 0) {
				d.view[name] = sql;
				d.viewUp = 1;
			}
		}
	}
	if (program.diff && program.diff == 'data') {
		var pk1 = getTablePK(v[0][1][v[3].table1]);
		var pk2 = getTablePK(v[0][2][v[3].table2]);
		if (pk1 == pk2) {
			var sql = diffData(v[3][0], v[3][1], pk1.split(','), v[3].table1);
			if (sql && sql.length > 0) {
				d.data[name] = sql;
				d.dataUp = 1;
			}
		}
	}
	return Promise.resolve(d);
}
function addslashes(str) {
	return (str + '').replace(/[\\"']/g, '\\$&').replace(/\u0000/g, '\\0')
}
function diffData(v1, v2, pk, table) {
	var v = [[],{},{}];
	for(var i=0; i<v1.length; i++) {
		var o = v1[i], k = getDataPKV(o, pk);
		if (v[0].indexOf(k) == -1) v[0].push(k);
		v[1][k] = o;
	}
	for(var i=0; i<v2.length; i++) {
		var o = v2[i], k = getDataPKV(o, pk);
		if (v[0].indexOf(k) == -1) v[0].push(k);
		v[2][k] = o;
	}
	var inserts = [], deletes = [], updates = [];
	for(var i = 0, len = v[0].length; i < len; i++) {
		var name = v[0][i];
		var d1 = v[1][name];
		var d2 = v[2][name];

		if (d1 && !d2) { inserts.push(d1); continue; }
		if (!d1 && d2) { deletes.push(d2); continue; }

		updates.push(name);
	}
	var sql = '';
	if (inserts.length > 0) {
		var insertSql = 'INSERT INTO `'+table+'` VALUES';
		for(var i = 0, len = inserts.length; i < len; i++) {
			insertSql += '(';
			for(var k in inserts[i]) {
				var oo = inserts[i][k];
				insertSql +=  (typeof oo === 'string' ? ('\''+addslashes(oo)+'\'') : oo) + ',';
			}
			insertSql = insertSql.substring(0, insertSql.length-1);
			insertSql += '),';
		}
		insertSql = insertSql.substring(0, insertSql.length-1);
		insertSql += ';\n';
		sql += insertSql;
	}
	if (deletes.length > 0) {
		for(var i = 0, len = deletes.length; i < len; i++) {
			var deleteSql = 'DELETE FROM `'+table+'` WHERE 1';
			var oo = deletes[i];
			for(var j in pk) {
				deleteSql += ' and `'+pk[j]+'`='+(typeof oo[pk[j]] === 'string' ? ('\''+addslashes(oo[pk[j]])+'\'') : oo[pk[j]]);
			}
			deleteSql += ';\n';
			sql += deleteSql.replace(' WHERE 1 and ', ' WHERE ');
		}
	}
	if (updates.length > 0) {
		for(var i = 0, len = updates.length; i < len; i++) {
			var updateSql = 'UPDATE `'+table+'` SET ', upS = '';
			for(var j in v[1][updates[i]]) {
				var oo = v[1][updates[i]][j];
				if (oo == v[2][updates[i]][j]) continue;
				upS += '`'+j+'`='+(typeof oo === 'string' ? ('\''+addslashes(oo)+'\'') : oo)+',';
			}
			if (upS.length == 0) continue;
			updateSql += upS.substring(0, upS.length - 1) + ' WHERE 1';
			var oo = v[2][updates[i]];
			for(var j in pk) {
				updateSql += ' and `'+pk[j]+'`='+(typeof oo[pk[j]] === 'string' ? ('\''+addslashes(oo[pk[j]])+'\'') : oo[pk[j]]);
			}
			updateSql += ';\n';
			sql += updateSql.replace(' WHERE 1 and ', ' WHERE ');
		}
	}
	return sql;
}
function getDataPKV(obj, pk) {
	var v = '';
	for(var i=0;i<pk.length; i++) v += obj[pk[i]] + '\0';
	return v ? v.substring(0, v.length - 1) : v;
}
function getTablePK(table) {
	var line = table.match(/PRIMARY KEY \(.*\)/i)[0];
	var pk = line.match(/`.*`/i)[0].replace(/`/g, '');
	return pk;
}
function diffProcedure(name, proc1, proc2, ex) {
	var p1 = filterProcedure(proc1);
	var p2 = filterProcedure(proc2);
	if (p1 == p2) return null;

	if (p1 != '' && p2 == '') return 'DELIMITER ;;\n'+changeProcedure(proc1, proc2, ex)+' ;;\nDELIMITER ;\n';
	if (p1 == '' && p2 != '') return 'DROP '+ex+' `' + name + '`;\n';

	if (ex == 'VIEW') {
		var proc = changeProcedure(proc1, proc2, ex);
		proc = proc.replace('CREATE', 'CREATE OR REPLACE');
		return proc+';\n';
	} else
		return 'DROP '+ex+' `' + name + '`;\nDELIMITER ;;\n'+changeProcedure(proc1, proc2, ex)+' ;;\nDELIMITER ;\n';
}
function changeProcedure(proc1, proc2, ex) {
	if (!proc2) {
		var user = db2.getUser();
		proc1 = proc1.replace(/ DEFINER=`.*`@/g, ' DEFINER=`'+user+'`@');
		return proc1;
	}
	var reg = new RegExp(' DEFINER=.* '+ex, 'i');
	var definer = proc2.match(reg)[0];
	reg = new RegExp(' DEFINER=.* '+ex, 'g');
	proc1 = proc1.replace(reg, definer);
	return proc1;
}
function filterProcedure(proc) {
	if (!proc) return '';
	proc = proc.replace(/ DEFINER=.* FUNCTION/g, ' FUNCTION');
	proc = proc.replace(/ DEFINER=.* PROCEDURE/g, ' PROCEDURE');
	proc = proc.replace(/ DEFINER=.* VIEW/g, ' VIEW');
	return proc.trim();
}
function diffTable(name, table1, table2) {
	var t1 = filterTable(table1);
	var t2 = filterTable(table2);
	if (t1 == t2) return null;

	if (t1 != '' && t2 == '') return table1 + ';\n';
	if (t1 == '' && t2 != '') return 'DROP TABLE `' + name + '`;\n';

	return diffTableField(name, table1, table2);
}
function diffTableField(name, table1, table2) {
	var t1 = table1.split('\n');
	var t2 = table2.split('\n');
	if (t1.length < 3 || t2.length < 3) return '';

	var col = [], col1 = {}, col2 = {};
	var key = [], key1 = {}, key2 = {};
	var pk1 = '', pk2 = '';

	for (var i=1, len = t1.length-1; i<len; i++) {
		var row = t1[i].trim(), l = row.length-1;
		if (row.lastIndexOf(',') == l) row = row.substring(0, l);
		var field = row.indexOf('`') == 0 ? row.split(' ')[0] : false;
		var pkey = row.indexOf('PRIMARY KEY ') == 0 ? row.substring(12) : false;
		var ikey = row.indexOf('KEY `') == 0 ? row.substring(4) : false;
		if (field) { col1[field] = row; if (col.indexOf(field) == -1) col.push(field); }
		if (pkey) pk1 = pkey;
		if (ikey) { key1[ikey] = 1; if (key.indexOf(ikey) == -1) key.push(ikey); }
	}

	for (var i=1, len = t2.length-1; i<len; i++) {
		var row = t2[i].trim(), l = row.length-1;
		if (row.lastIndexOf(',') == l) row = row.substring(0, l);
		var field = row.indexOf('`') == 0 ? row.split(' ')[0] : false;
		var pkey = row.indexOf('PRIMARY KEY ') == 0 ? row.substring(12) : false;
		var ikey = row.indexOf('KEY `') == 0 ? row.substring(4) : false;
		if (field) { col2[field] = row; if (col.indexOf(field) == -1) col.push(field); }
		if (pkey) pk2 = pkey;
		if (ikey) { key2[ikey] = 1; if (key.indexOf(ikey) == -1) key.push(ikey); }
	}

	var sql = '';
	for(var i=0, len = col.length; i<len; i++) {
		var c = col[i], c1 = col1[c], c2 = col2[c];
		var c3 = filterField(c1), c4 = filterField(c2);
		if (c3 == c4) continue;

		if (c3 != '' && c4 == '') {
			sql += 'ALTER TABLE `'+name+'` ADD COLUMN '+c1+';\n';
			continue;
		}
		if (c3 == '' && c4 != '') {
			sql += 'ALTER TABLE `'+name+'` DROP COLUMN '+c+';\n';
			continue;
		}
		sql += 'ALTER TABLE `'+name+'` CHANGE COLUMN '+c+' '+c1+';\n';
	}

	if (pk1 != '' && pk2 == '') sql += 'ALTER TABLE `'+name+'` ADD PRIMARY KEY '+pk1+';\n';
	if (pk1 == '' && pk2 != '') sql += 'ALTER TABLE `'+name+'` DROP PRIMARY KEY;\n';
	if (pk1 != '' && pk2 != '' && pk1 != pk2) sql += 'ALTER TABLE `'+name+'` DROP PRIMARY KEY,ADD PRIMARY KEY '+pk1+';\n';

	for(var i=0, len = key.length; i<len; i++) {
		var k = key[i], k1 = key1[k], k2 = key2[k];
		if (k1 == k2) continue;

		if (k1 && !k2) {
			sql += 'ALTER TABLE `'+name+'` ADD INDEX '+k+';\n';
			continue;
		}
		if (!k1 && k2) {
			var kname = k.split(' ')[0];
			sql += 'ALTER TABLE `'+name+'` DROP INDEX '+kname+';\n';
			continue;
		}
	}

	return sql;
}
function filterField(field) {
	if (!field) return '';
	field = field.replace(/ COMMENT '.*'/g, '');
	field = field.replace(/ COMMENT='.*'/g, '');
	return field.trim();
}
function filterTable(table) {
	if (!table) return '';
	table = table.replace(/AUTO_INCREMENT=.* /g, '');
	table = table.replace(/ CHECKSUM=1/g, '');
	table = table.replace(/ DELAY_KEY_WRITE=1/g, '');
	table = table.replace(/ ROW_FORMAT=DYNAMIC/g, '');
	table = table.replace(/ DEFAULT/g, '');
	table = filterField(table);
	return table.trim();
}
function allDesc(v) {
	console.log('-- Began to compare db1 & db2 differences ...');
	var list = [], objs = [];
	if (program.diff == 'table' || !program.diff) {
		for(var k in v[0][1]) {
			list.push(db1.showCreateTable(k));
			objs.push('0-' + 1 + '-' + k);
		}
		for(var k in v[0][2]) {
			list.push(db2.showCreateTable(k));
			objs.push('1-' + 1 + '-' + k);
		}
	}
	if (program.diff == 'procedure' || program.diff == 'function' || !program.diff) {
		for(var k in v[1][2]) {
			var proc = v[1][0].indexOf(k) != -1;
			var func = v[1][1].indexOf(k) != -1;
			if (proc) {
				list.push(db1.showCreateProcedure(k));
				objs.push('0-' + 2 + '-' + k);
			}
			if (func) {
				list.push(db1.showCreateFunction(k));
				objs.push('0-' + 3 + '-' + k);
			}
		}
		for(var k in v[1][3]) {
			var proc = v[1][0].indexOf(k) != -1;
			var func = v[1][1].indexOf(k) != -1;
			if (proc) {
				list.push(db2.showCreateProcedure(k));
				objs.push('1-' + 2 + '-' + k);
			}
			if (func) {
				list.push(db2.showCreateFunction(k));
				objs.push('1-' + 3 + '-' + k);
			}
		}
	}
	if (program.diff == 'view' || !program.diff) {
		for(var k in v[2][1]) {
			list.push(db1.showCreateView(k));
			objs.push('0-' + 4 + '-' + k);
		}
		for(var k in v[2][2]) {
			list.push(db2.showCreateView(k));
			objs.push('1-' + 4 + '-' + k);
		}
	}
	if (program.diff && program.diff == 'data') {
		list.push(db1.showCreateTable(v[3].table1));
		objs.push('0-' + 1 + '-' + v[3].table1);
		list.push(db2.showCreateTable(v[3].table2));
		objs.push('1-' + 1 + '-' + v[3].table2);
	}
	return Promise.all(list).then(function(v2){
		for(var i = 0, len = v2.length; i < len; i++) {
			var types = objs[i].split('-');
			var val = v2[i];
			if (types[1] == 1) {
				var s = parseInt(types[0]) + 1;
				v[0][s][types[2]] = val;
			} else if (types[1] == 2 || types[1] == 3) {
				var s = parseInt(types[0]) + 2;
				v[1][s][types[2]] = val;
			} else if (types[1] == 4) {
				var s = parseInt(types[0]) + 1;
				v[2][s][types[2]] = val;
			}
		}
		return v;
	});
}
function getDatas() {
	var data = [[], []];
	if (!program.diff || (program.diff && program.diff != 'data')) return Promise.resolve(data);
	var tables = program.table.split(':');
	var table1 = tables[0];
	var table2 = tables.length == 2 ? tables[1] : table1;
	return Promise.all([db1.getData(table1), db2.getData(table2)]).then(function(v){
		console.log('-- db1 table',table1,'data', v[0].length, 'records!');
		data[0] = v[0];
		console.log('-- db2 table',table2,'data', v[1].length, 'records!');
		data[1] = v[1];
		data.table1 = table1;
		data.table2 = table2;
		return data;
	});
}
function getTables() {
	var tables = [[], {}, {}];
	if (program.diff && program.diff != 'table') return Promise.resolve(tables);
	console.log('-- Getting table structure ...');
	return Promise.all([db1.tables(), db2.tables()]).then(function(v){
		for(var i = 0, len = v[0].length; i < len; i++) {
			var table = v[0][i].tabName;
			tables[0].push(table);
			tables[1][table] = {};
		}
		console.log('-- db1 get', v[0].length, 'tables!');
		for(var i = 0, len = v[1].length; i < len; i++) {
			var table = v[1][i].tabName;
			if (tables[0].indexOf(table) == -1) tables[0].push(table);
			tables[2][table] = {};
		}
		tables[0].sort();
		console.log('-- db2 get', v[1].length, 'tables!');
		console.log('-- db1 & db2 merge', tables[0].length, 'table structure!');
		return tables;
	});
}
function getParameters() {
	var parameters = [[], [], {}, {}];
	if (program.diff && program.diff != 'procedure' && program.diff != 'function') return Promise.resolve(parameters);
	console.log('-- Getting stored procedures and functions ...');
	return Promise.all([db1.parameters(), db2.parameters()]).then(function(v){
		for(var i = 0, len = v[0].length; i < len; i++) {
			var parameter = v[0][i].parName;
			var type = v[0][i].type;
			if (type == 'PROCEDURE') parameters[0].push(parameter);
			if (type == 'FUNCTION')  parameters[1].push(parameter);
			parameters[2][parameter] = {};
		}
		console.log('-- db1 get', v[0].length, 'stored procedures and functions!');
		for(var i = 0, len = v[1].length; i < len; i++) {
			var parameter = v[1][i].parName;
			var type = v[1][i].type;
			if (type == 'PROCEDURE' && parameters[0].indexOf(parameter) == -1)
				parameters[0].push(parameter);
			if (type == 'FUNCTION' && parameters[1].indexOf(parameter) == -1)
				parameters[1].push(parameter);
			parameters[3][parameter] = {};
		}
		console.log('-- db2 get', v[1].length, 'stored procedures and functions!');
		console.log('-- db1 & db2 merge', parameters[0].length, 'stored procedures!');
		console.log('-- db1 & db2 merge', parameters[1].length, 'functions!');
		return parameters;
	});
}
function getViews() {
	var views = [[], {}, {}];
	if (program.diff && program.diff != 'view') return Promise.resolve(views);
	console.log('-- Getting view ...');
	return Promise.all([db1.views(), db2.views()]).then(function(v){
		for(var i = 0, len = v[0].length; i < len; i++) {
			var view = v[0][i].viewName;
			views[0].push(view);
			views[1][view] = {};
		}
		console.log('-- db1 get', v[0].length, 'views!');
		for(var i = 0, len = v[1].length; i < len; i++) {
			var view = v[1][i].viewName;
			if (views[0].indexOf(view) == -1) views[0].push(view);
			views[2][view] = {};
		}
		console.log('-- db2 get', v[1].length, 'views!');
		console.log('-- db1 & db2 merge', views[0].length, 'views!');
		return views;
	});
}

function exit() {
	if (!db1 && !db2) process.exit();
	var list = [];
	if (db1) list.push(db1.close());
	if (db2) list.push(db2.close());
	Promise.all(list).then(function(v){
		setTimeout(process.exit, 1000);
	})
}

start();