/*
 
  code for altering tables (but not indexes)
  
*/  
var KEEPSAMEVALUE = '** keep same **';

function AlterTablePanel(rdbAdmin, dbMgr, tablePanel, sqlPanel)
{
	this.rdbAdmin = rdbAdmin;
	this.dbMgr = dbMgr;
	this.tablePanel = tablePanel;
	this.sqlPanel = sqlPanel;
	this.panelId = 'alter-table-panel';
	this.tableId = 'alter-table-details';
	this.maxRowId = 0;
	this.mode = 'alter';
	//this.sqlTable = null;
	//this.tableName = null;
	
	// initialize click handlers
	//
	this.init_handlers = function () {
		var that = this;
		$('#createNewTableBtn').click(function () {
			that.show('create');
		});
		$('#alter-table-btn').click(function () {
			that.show('alter');
		});
		// add click handler to edit query
		$('#atable-sql-edit').click(function() {
			var query = $('#alter-table-sql-show').html();
			that.sqlPanel.showQuery(query);
		});
		$('#atable-column-comments').click(that.showCommentsColumn);
		$('#atable-column-defaults').click(that.showDefaultsColumn);
		$('#alter_table_save').click(function () {
			that.saveTable();
		});
		$('#alter_table_drop').click(function () {
			that.dropTable();
		});
		$('#alter_table_add_row').click(function () {
			that.addRowToTable(this);
		});
		// add live onclick handlers for rows (of varying number)
		// handlers for add and delete row butons
		$('#'+that.tableId+' input[id^="alter_table_del_row_"]').live('click',function() {
			that.delRowFromTable(this);
		});
		// handler updates sql display for any change in the data entry
		$('#'+that.panelId+' *:input').live('change',function() {
			that.onChange(this);
		});
	};

	this.show = function(mode)
	{
		this.rdbAdmin.resetMessages();
		$('#alter-table-comment').val('');
		this.mode = mode;

		this.buildTable();
		this.rdbAdmin.showPanel(this.panelId);
		if (this.mode === 'alter') {
			this.rdbAdmin.setHeading("Alter table: " +
									 this.tablePanel.sqlTable.tableName);
			$('#alter_table_drop').css('display', 'inline');
		}
		else {
			this.rdbAdmin.setHeading("Create new table");
			$('#alter_table_drop').css('display', 'none');
		}
	};
	
	this.showCommentsColumn = function()
	{
		var show = ($(this).attr('checked')) ? "" : "none";
		$('input[id^="alter-table-fcomment_"]').parent().css('display',show);
		$('th[id^="alter-table-fcomment"]').css('display',show);
	};
	
	this.showDefaultsColumn = function()
	{
		var show = ($(this).attr('checked')) ? "" : "none";
		$('input[id^="alter-table-fdefault_"]').parent().css('display',show);
		$('th[id^="alter-table-fdefault"]').css('display',show);
	};
	
	this.addRowToTable = function(domel)
	{
		var that = this;
		var $table = $('#'+this.tableId);
		// find row id
		var newrowIdx = parseInt(this.maxRowId,10) + 1;
		var $row = $('#atable_th').next().clone().show();
		var rowID = $(domel).parents('tr').first().attr('id');
		if(rowID === 'atable_th') { // if add button in header clicked
			rowID = $('#'+this.tableId+' tbody tr:last').attr('id');
		}
		// function to add row# to end of id values
		var reg = /(\S+)_(\d*)$/;
		function update_id_unique($el,i) {
			var id = $el.attr('id');
			if (id && id.length && reg.test(id)) { //substr(id.length-1)==='_')
				id = id.replace(reg,'$1_'+i);
				$el.attr('id',id);
			}
		}
		// update all *_ ids with iteration number *_#
		$row.attr('id','atable_tr_'+newrowIdx);
		$row.find('td')
			   .add('select',$row)
			   .add('input',$row)
			   .each(function () {
			update_id_unique($(this),newrowIdx);
		});
		// attach new row
		this.maxRowId = this.maxRowId + 1;
		// remove 'keep same' option
		var $nratf = $row.find('*[id^="alter-table-ftype_"]');
		if ( $nratf && $nratf.length && $nratf.val() === KEEPSAMEVALUE)  {
			$nratf.find('option:first').remove();
		}
        $('#'+rowID).after($row);
		$table.find('*:input:first').change();
	};
	
	this.delRowFromTable = function(domel)
	{
		var $table = $('#'+this.tableId);
		// if this is last row - don't delete
		if ($table.find('tbody tr').length === 2) {
			alert('Table must have at least one row!');
			return false;
		}
		// remove html
		var rowID = $(domel).parents().get(1).id;
		$table.find('#'+rowID).remove();
		$table.find('*:input:first').change();
		return false;
	};
	
	this.onChange = function(domel)
	{
		var $row = $(domel).closest('tr');
		var $typeSelect = $row.find('td:eq(1) select');
		// if selected elem is in row...
		if ( $typeSelect.length ) {
			var idre = /^alter-table-ftype_(\d+)$/;
			var tmpId = $typeSelect.attr('id');
			var id = idre.exec(tmpId)[1];
			// make size visible or not, depending on type
			var lenvis = takes_length($typeSelect.val()) ? 'visible' : 'hidden';
			$row.find('#alter-table-flength_'+id).css('visibility',lenvis);
		}
		this.updateSQLDisplay();
		return false;
	};
	
	this.updateSQLDisplay = function ()
	{
		this.dirty = true;
		var sql;
		if (this.mode === 'alter') {
			sql = this.createAlterQueryString();
		}
		else {
			sql = this.createCreateQueryString();
		}
		if ( !sql  ) {
			sql = '-- nothing to do yet';
		} 
		$('#'+this.panelId+' #alter-table-sql-show').html(sql);
	};
	
	this.buildTable = function(columnComments)
	{
		// function to add row# to end of id values
		function make_id_unique($el,i) {
			var id = $el.attr('id');
			if (id && id.length && id.substr(id.length-1)==='_') {
				$el.attr('id',id+i);
			}
		}
		var that = this;
		var $table = $('#'+this.tableId);

		function build(columnComments,tableComment,tablename) {
			// runs for every table, 'create' and 'alter'
			var $hdr = $table.find('tr:first').clone(true);
			var $colrow = $table.find('tr[id^="atable_tr"]:first').show();
			$table.empty().append($hdr);
			$table.append($colrow.clone().hide());
			//<tr id="atable_th"><th>Column Name</th><th>Type</th><th>Length</th>
			//<th>Options</th><th>NULL</th><th style="display: none;" id="alter_table_fdefault">Default</th>
			//<th style="display: none;" id="alter_table_fcomment">Comment</th>
			//<th><input type="image" title="Add next" src="css/plus.gif" id="alter_table_add_row"/></th>
			for (var i=0;i<that.tablePanel.sqlTable.fields.length;i+=1) {
				var col = that.tablePanel.sqlTable.fields[i];
				if (col.maxLength === null) {
					col.maxLength = '';
				}
				var $newrow = $colrow.clone().show();
				// put various values in the new row
				$newrow.find('#alter-table-fpos_').val(col.pos);
				$newrow.find('#alter-table-fname_')
				       .add('#alter-table-foldname_',$newrow)
					   .val(col.columnName);
				$newrow.find('#alter-table-flength_')
					   .add('#alter-table-foldlength_',$newrow).val(col.maxLength);
				$newrow.find('#alter-table-fdefault_')
				       .add('#alter-table-folddefault_',$newrow)
				       .val(col.columnDefault===null ? '' : col.columnDefault);
				$newrow.find('#alter-table-fcomment_')
				       .add('#alter-table-foldcomment_',$newrow)
				       .val(columnComments[i]===null ? '' : columnComments[i]);
				// set checkbox status according to field state - null/not null
				if (col.isNullable) {
					$newrow.find('#alter-table-fisnull_')
					       .add('#alter-table-foldisnull_',$newrow)
						   .attr('checked','checked');				
				}
				// set checkbox status according to field state - array
				if (parseInt(col.numDims,10) > 0) {
					$newrow.find('#alter-table-farray_')
					       .add('#alter-table-foldarray_',$newrow)
						   .attr('checked','checked');				
				}
/*				// set checkbox status according to field state - primarykey
				if ($.inArray(col.columnName,primaryKeyFields) > -1) {
					$newrow.find('#alter_table_isprimarykey_').attr('checked','checked');				
				}
				// set checkbox status according to field state - unique
				if ($.inArray(col.columnName,uniqueFields) > -1) {
					$newrow.find('#alter_table_isunique_').attr('checked','checked');				
				}
*/				// if mode is create - delete 'keep same' option
				if (that.mode !== 'alter') {
					var $ftopts = $newrow.find('#alter-table-ftype_ option');
					$($ftopts.get(0)).remove();
				}
				// set field type in select input
				// if there's no such type - select ** keep same ** option
				var $typefnd = $newrow.find('#alter-table-ftype_ option').filter(function(){ 
					return ($(this).val() === col.dataType);
				});
				if ($typefnd.length) {
					$newrow.find('#alter-table-foldtype_')
					       .add('#alter-table-ftype_',$newrow)
						   .val(col.dataType);
				}
				else { // is_customType
					$newrow.find('#alter-table-foldtype_')
					       .add('#alter-table-ftype_',$newrow)
						   .val(KEEPSAMEVALUE);
				}
				// update all *_ ids with iteration number *_#
				$newrow.find('td')
					   .add('select',$newrow)
					   .add('input',$newrow)
					   .each(function () {
					make_id_unique($(this),i);
					//if ( $(this).attr('id') ) alert( $(this).attr('id') );
				});
				make_id_unique($newrow,i);
				$table.append($newrow);
				that.maxRowId = i;
			}
			// save comments
			$('#atable-column-comments').attr('checked','');
			$('#atable-column-defaults').attr('checked','');
			$('#cap-tableName').add('#cap-oldTableName').val(tablename);
			$('#alter-table-comment')
				  .add('#alter-table-oldcomment')
				  .val(tableComment ? tableComment : '');
			$table.find('*:input:first').change();
		}			
		function buildAlterTable() {
			function withDetails(sT) {
				function withOID(table_oid) {
					var tableCmt = false, columnCmts = false;
					var tablename = that.tablePanel.sqlTable.tableName;
					function buildWhenReady() {
						if ( tableCmt !== false && columnCmts !== false ) {
							// pass column and table comments to build
							build(columnCmts,tableCmt,tablename);
						}
					}
					function withColComments(colcmts) {
						columnCmts = colcmts || [];
						buildWhenReady();
					}
					function withTableComments(tabcmt) {
						tableCmt = tabcmt || '';
						buildWhenReady();
					}
					that.dbMgr.getColumnComments(table_oid,
												 that.tablePanel.sqlTable.fields,
												 withColComments);
					that.dbMgr.getTableComment(table_oid,withTableComments);
				}
				that.tablePanel.sqlTable = sT;
				that.dbMgr.getTableOID(that.tablePanel.sqlTable.schemaName,
									   that.tablePanel.sqlTable.tableName,
									   withOID);
			}
			that.dbMgr.getTableDetails(that.tablePanel.sqlTable.schemaName,
										that.tablePanel.sqlTable.tableName,
										withDetails );
		}
		function buildCreateTable() {
			that.tablePanel.sqlTable = new SQLTable(that.dbMgr.sqlEngine);
			that.tablePanel.sqlTable.addColumn(new SQLColumn());
			build([],'','');
		}		
		// get table details
		if (this.mode==='alter') {
			buildAlterTable();
		}
		else {
			buildCreateTable();
		}
	};
	
	this.dropTable = function()
	{
		if (!confirm('Are you sure?')) {
			return false;
		}
		var that = this;
		function dropcb(json) {
			that.tablePanel.sqlTable.tableName = '';
			that.rdbAdmin.updateTableList();
			$('#mainPageLink').click();
		}
		this.dbMgr.dropTable(this.tablePanel.sqlTable.tableName,dropcb);
		return true;
	};
	
	this.createCreateQueryString = function ()
	{
		var that = this;
		var $panel = $('#'+this.panelId);
		var queryParts = [];
		function queryStart(tableName) {
			return 'CREATE TABLE '+quoteIdentifier(tableName)+' (';
		}
		function tableComment(tableName,tableCmt) {
			return 'COMMENT ON TABLE '+quoteIdentifier(tableName)+" IS '"+
			       tableCmt+"';";
		}
		function columnComment(tableName,columnName,colComment) {
			return 'COMMENT ON COLUMN '+quoteIdentifier(tableName)+'.'+
				   quoteIdentifier(columnName)+" IS '"+
			       colComment+"';";
		}
		function queryColumn(fname,ftype,flen,farray,fnull,fdefault) {
			var colParts = ['   '];
			colParts.push(fname);
			var typ = ftype;
			if (flen) {
				typ += '('+flen+')';
			}
			if (farray) {
				typ += '[]';
			}
			colParts.push(typ);
			if (fdefault) {
				colParts.push("DEFAULT '~~'".replace('~~',fdefault));
			}
			colParts.push(fnull ? 'NULL' : 'NOT NULL');
			return colParts.join(' ');
		}
		// retrieve elements from form, add to query parts list
		var tablename = $panel.find('#cap-tableName').val() || '~tablename~';
		queryParts.push(queryStart(tablename));

		var queryColumns = [],
			colComments = [];
		$panel.find('tbody tr:visible').each(function () {
			var $trow = $(this);
			if ($trow.attr('id') === 'atable_th') {
				return true; // skip header row
			}
			var fname = $trow.find('input[id^="alter-table-fname_"]').val();
			assert(fname !== undefined, 'bad fname '+fname);
			var ftype = $trow.find('*[id^="alter-table-ftype_"]').val();
			assert(ftype !== undefined, 'bad ftype '+ftype);
			var flen =  $trow.find('*[id^="alter-table-flength_"]:visible').val();
			var farray = $trow.find('*[id^="alter-table-farray_"]:checked').length;
			assert(farray !== undefined, 'bad farray '+farray);
			var fnull = $trow.find('*[id^="alter-table-fisnull_"]:checked').length;
			assert(fnull !== undefined, 'bad fnull '+fnull);
			var fdefault = $trow.find('*[id^="alter-table-fdefault_"]').val();
			assert(fdefault !== undefined, 'bad fdefault '+fdefault);
			var fcomment = $trow.find('*[id^="alter-table-fcomment_"]').val();
			assert(fcomment !== undefined, 'bad fcomment '+fcomment);
			if (fname) {
				queryColumns.push(queryColumn(fname,ftype,flen,farray,fnull,
											  fdefault));
			}
			if (fcomment) {
				colComments.push([tablename,fname,fcomment]);
			}
			return true;
		});
		queryParts.push(queryColumns.join(',\n'));
		//
		queryParts.push(');'); // close statement
		var tablecmt = $panel.find('#alter-table-comment').val();
		if (tablecmt) {
			queryParts.push(tableComment(tablename,tablecmt));
		}
		for (var ccn in colComments) {
			if (colComments.hasOwnProperty(ccn)) {
				var cc = colComments[ccn];
				var tname = cc[0], cname = cc[1], cmt = cc[2];
				queryParts.push(columnComment(tname,cname,cmt));
			}
		}
		return queryParts.join('\n');
	};	
		
	this.createAlterQueryString = function ()
	{
		var that = this;
		var $panel = $('#'+this.panelId);
		var tablename = $panel.find('#cap-tableName').val();
		var queryParts = [];
		function tableRename(oldTableName) {
			return 'ALTER TABLE '+quoteIdentifier(oldTableName)+
				   ' RENAME TO '+quoteIdentifier(tablename);
		}
		function columnRename(oldColName,colName) {
			return 'ALTER TABLE '+quoteIdentifier(tablename)+
				   ' RENAME '+quoteIdentifier(oldColName)+
				   ' TO '+quoteIdentifier(colName);
		}
		function newColumn(fname,ftype,flen,farray,fnull,fdefault) {
			var colParts = ['   ADD COLUMN'];
			colParts.push(quoteIdentifier(fname));
			var typ = ftype;
			if (flen) {
				typ += '('+flen+')';
			}
			if (farray) {
				typ += '[]';
			}
			colParts.push(typ);
			if (fdefault) {
				colParts.push("DEFAULT '~~'".replace('~~',fdefault));
			}
			colParts.push(fnull ? 'NULL' : 'NOT NULL');
			return colParts.join(' ');
		}
		function queryStart(tableName) {
			return 'ALTER TABLE '+quoteIdentifier(tableName);
		}
		function tableComment(tableName,tableCmt) {
			return "COMMENT ON TABLE ~tn~ IS '~tc~'"
			       .replace('~tn~',quoteIdentifier(tableName))
				   .replace('~tc~',tableCmt);
		}
		function columnComment(tableName,columnName,colComment) {
			return "COMMENT ON COLUMN ~tn~.~cn~ IS '~tc~'"
			       .replace('~tn~',quoteIdentifier(tableName))
			       .replace('~cn~',quoteIdentifier(columnName))
				   .replace('~tc~',colComment);
		}
		function alterColumnType(fname,ftype,flen,farray) {
			var colParts = ['   ALTER COLUMN'];
			colParts.push(quoteIdentifier(fname));
			colParts.push('TYPE');
			var typ = ftype;
			if (flen) {
				typ += '('+flen+')';
			}
			if (farray) {
				typ += '[]';
			}
			colParts.push(typ);
			return colParts.join(' ');
		}
		function alterColumnNull(fname,fnull) {
			var colParts = ['   ALTER'];
			colParts.push(quoteIdentifier(fname));
			colParts.push(fnull ? 'DROP' : 'SET');
			colParts.push('NOT NULL');
			return colParts.join(' ');
		}
		function alterColumnDefault(fname,fdefault) {
			var colParts = ['   ALTER'];
			colParts.push(quoteIdentifier(fname));
			colParts.push(fdefault
					? "SET DEFAULT '~d~'".replace('~d~',fdefault.replace("'","\\'"))
					: 'DROP DEFAULT' );
			return colParts.join(' ');
		}
		function dropColumn(fname) {
			var colParts = ['   DROP COLUMN'];
			colParts.push(quoteIdentifier(fname));
			return colParts.join(' ');
		}
		// retrieve old tablename from form, if name changed, add
		//  a rename to the queryParts list
		var oldTablename = $panel.find('#cap-oldTableName').val();
		if (oldTablename !== tablename) {
			queryParts.push(tableRename(oldTablename,tablename));
			//queryParts.push('-- rename ~0 TO ~1'.replace('~0',oldTablename)
			//				                    .replace('~1',tablename));
		}
		var colRenames = [],
			colComments = [], // tuples of (fieldname, columncomment)
			knownfields = [], // list of fields now in db
		    alterQueryParts = [];
		// iterate over rows of html table, and process each
		//  field/row
		$panel.find('tbody tr:visible').each(function () {
			var $trow = $(this);
			if ($trow.attr('id') === 'atable_th') {
				return true; // skip header row
			}
			// get old and new column names. if different, put a rename
			//   sql clip on colRenames list
			var fname = $trow.find('input[id^="alter-table-fname_"]').val();
			assert(fname !== undefined, 'bad fname '+fname);
			var foldname = $trow.find('input[id^="alter-table-foldname_"]').val();
			assert(foldname !== undefined, 'bad foldname '+foldname);
			knownfields.push(foldname);
			if (foldname && (foldname !== fname)) {
				colRenames.push(columnRename(foldname,fname));
			}
			// get old and new types. if different put a col change action on
			//  alterQueryParts.
			var ftype = $trow.find('*[id^="alter-table-ftype_"]').val();
			assert(ftype !== undefined, 'bad ftype '+ftype);
			var foldtype = $trow.find('*[id^="alter-table-foldtype_"]').val();
			assert(foldtype !== undefined, 'bad foldtype '+ftype);
			var $flen =  $trow.find('*[id^="alter-table-flength_"]');
			var flen = ($flen && $flen.css('visibility') === 'visible')
			             ? $flen.val()
					     : '';
			var foldlen =  $trow.find('*[id^="alter-table-foldlength_"]').val();
			var farray = $trow.find('*[id^="alter-table-farray_"]:checked').val() || 0;
			assert(farray !== undefined, 'bad farray '+farray);
			var foldarray = $trow.find('*[id^="alter-table-foldarray_"]:checked').val() || 0;
			assert(foldarray !== undefined, 'bad foldarray '+foldarray);
			var newForm = alterColumnType(fname,ftype,flen,farray);
			var oldForm = alterColumnType(foldname,foldtype,foldlen,foldarray);
			if (foldname && (newForm !== oldForm)) {
				alterQueryParts.push(newForm); 
			}
			// get old and new null status. if changed, add a col change action
			//  on alterQueryParts
			var fnull = $trow.find('*[id^="alter-table-fisnull_"]:checked').length;
			assert(fnull !== undefined, 'bad fnull '+fnull);
			var foldnull = $trow.find('*[id^="alter-table-foldisnull_"]:checked').length;
			assert(foldnull !== undefined, 'bad foldnull '+foldnull);
			if (foldname && (fnull !== foldnull)) {
				alterQueryParts.push(alterColumnNull(fname,fnull));
			}
			// get old and new default values. if changed, add a col change action
			//   to alterQueryParts
			var fdefault = $trow.find('*[id^="alter-table-fdefault_"]').val();
			assert(fdefault !== undefined, 'bad fdefault '+fdefault);
			var folddefault = $trow.find('*[id^="alter-table-folddefault_"]').val();
			assert(folddefault !== undefined, 'bad folddefault '+folddefault);
			if (foldname && (fdefault !== folddefault)) {
				alterQueryParts.push(alterColumnDefault(fname,fdefault));
			}
			// get old and new column comments. add col change action if necessary
			var fcomment = $trow.find('*[id^="alter-table-fcomment_"]').val();
			assert(fcomment !== undefined, 'bad fcomment '+fcomment);
			var foldcomment = $trow.find('*[id^="alter-table-foldcomment_"]').val();
			assert(foldcomment !== undefined, 'bad foldcomment '+foldcomment);
			if (fcomment !== foldcomment) {
				colComments.push(columnComment(tablename,fname,fcomment));
			}
			// handle new field
			if (!foldname) {
				alterQueryParts.push(newColumn(fname,ftype,flen,farray,fnull,fdefault));
			}
			return true;
		});
		// compare orig field list with knownfields, and drop un-kept fields
		for (var o in this.tablePanel.sqlTable.fields) {
			var ofname = this.tablePanel.sqlTable.fields[o].columnName;
			if ( $.inArray(ofname,knownfields) === -1 ) {
				alterQueryParts.push(dropColumn(ofname));
			}
		}
		// assemble various lists into one big query
		if (alterQueryParts.length) {
			var aq = [ queryStart(tablename),
				   alterQueryParts.join(',\n') ];
			queryParts.push(aq.join('\n'));
		}			
		queryParts = queryParts.concat(colComments);
		// if table comment change, add sql segment
		var tablecmt = $panel.find('#alter-table-comment').val();
		var oldtablecmt = $panel.find('#alter-table-oldcomment').val();
		if (tablecmt !== oldtablecmt) {
			queryParts.push(tableComment(tablename,tablecmt));
		}
		queryParts.push(''); // force trailing ';'
		return queryParts.join(';\n');
	};	
		
	this.saveTable = function ()
	{
		var that = this, successcb, sql;
		var $panel = $('#'+this.panelId);
		var tablename = $panel.find('#cap-tableName').val();
		successcb = function(json) {
			that.rdbAdmin.updateTableList();
			that.tablePanel.show(tablename);
		};
		if (this.mode === 'create') {
			sql = this.createCreateQueryString();
		}
		else {
			sql = this.createAlterQueryString();
		}
		if ( !sql  ) {
			alert('nothing to do!');
			return false;
		} 

		// functions to handle results of query submit
		function errback (err,msg) {
			that.rdbAdmin.showErrorMessage('<pre>'+err.toString()+': '+msg+'</pre>');
		}
		// send query to engine, feed results to callback
		this.dbMgr.sqlEngine.query( { 'q' : sql,
								      'callback' : successcb,
									  'errback' : errback } );
		return false;
	};
	
}