dojo.require("dojox.sql");

dojox.sql.addOnLoad(function() {
	console.info('Provider has been loaded');
});

dojox.sql.addOnLoad(function() {
	console.debug('Creating table, catching onSubmit');
	window.sweetchDb = dojox.sql.get('sweetchCustomersDb');
	window.sweetchDb.execSync('CREATE TABLE IF NOT EXISTS CUSTOMERS(customer_id INTEGER PRIMARY KEY AUTOINCREMENT, last_name TEXT, first_name TEXT)');
	dojo.connect(
		dojo.byId('addCustomerForm'), 'onsubmit', function(e) {
			dojo.stopEvent(e);
			addCustomer(document.forms['addCustomerForm'].firstname.value, document.forms['addCustomerForm'].lastname.value);
		}
	);
});

dojo.connect(dojox.sql, "providerLoaded", createTable);

function createTable(){	
	var table = document.getElementById("dataTable");
	if(table){
		table.parentNode.removeChild(table);
	}
	
	table = document.createElement("table");
	table.setAttribute("id", "dataTable");
	table.setAttribute("border", 1);
	addTableRow(table, "ID", "Last Name", "First Name");
	
	var customers = window.sweetchDb.execSync('SELECT customer_id, last_name, first_name FROM customers');
	
	while(c = customers.next()) {
		addTableRow(table, c.customer_id, c.last_name, c.first_name);
	}
	
	document.body.appendChild(table);
}

function addCustomer(lastName, firstName) {	
	try {
		window.sweetchDb.execSync('INSERT INTO customers(last_name, first_name) VALUES(?, ?)', [lastName, firstName]);
		var table = document.getElementById("dataTable");
		console.info('Affected rows: ' + window.sweetchDb.affectedRows());
		addTableRow(table, window.sweetchDb.lastInsertId(), lastName, firstName);
	} catch(e) {
		reportException(e);
	}
	
}

function deleteCustomer(id) {
	try {
		window.sweetchDb.execSync('DELETE FROM customers WHERE customer_id = ?', [id]);
		dojo._destroyElement(dojo.byId('tr_customer_' + id));
	} catch(e) {
		reportException(e);
	}
}

function addTableRow(table, customerId, customerLastName, customerFirstName) {
	tr = (dojo.isIE) ? table.insertRow() : document.createElement("tr");
	tr.className = "data-item";
	tr.id = "tr_customer_" + customerId;
	
	addTableCell(tr, "id", customerId);
	addTableCell(tr, "last-name", customerLastName);
	addTableCell(tr, "first-name", customerFirstName);
	
	if(customerId == "ID") {
		// OK it's ugly, but hey, it's just a demo...
		addTableCell(tr, "delete", "Delete");
	} else {
		addTableCell(tr, "delete", '<button onclick="deleteCustomer(' + customerId + ')">X<\/button>', true);
	}
	
	if(!dojo.isIE){
		table.appendChild(tr);
	}
}

function addTableCell(tr, clazz, content, isHTML) {
	var elem = (dojo.isIE) ? tr.insertCell() : document.createElement("td");
	elem.className = clazz;	
	if(isHTML) {
		elem.innerHTML = content;
	} else {
		elem.appendChild(document.createTextNode(content));
	}
	if(!dojo.isIE){
		tr.appendChild(elem);
	}
}

function recreateTable() {
	try {
		window.sweetchDb.execSync('DROP TABLE IF EXISTS CUSTOMERS');
		window.sweetchDb.execSync('CREATE TABLE CUSTOMERS(customer_id INTEGER PRIMARY KEY AUTOINCREMENT, last_name TEXT, first_name TEXT)');
		createTable();
	} catch(e) {
		reportException(e);
	}
}

function reportException(e) {
	document.getElementById('feedbackPara').innerHTML = e.message || e;
	if(console) console.error(e);
}