var db;
var error = false;
var debug = false;
var gg_installed = false;

function getResult(area) {
	if(!gg_installed) return;
		
	var sql = area.getElementsByTagName("textarea")[0].value;
	var result_area = area.getElementsByTagName("div")[0];
	result_area.innerHTML = '';

	var result_data = execQuery(sql);
	if(result_data[1]) {
		result_area.innerHTML = '<div class="error-message">' + result_data[1] + '</div>';
	} else {
		showResults(result_data[0], result_area);
	}
}

function execQuery(query) {
	if(!gg_installed) return;
	
	var error = false;
	var result = {};
	try {
		var result = db.execute(query);
	} catch(E) {
		var error = E.message || E.description || String(E) || true;
		if(debug) alert("Error : " + error);
	}
	
	return [result, error];
}

function showResults(result, area) {
	if(!gg_installed) {
		area.innerHTML = "<div class='no-google-gears-notice'>Please install Google Gears for this feature</div>";
		return;
	}
	
	area.innerHTML = "<span class='message-success'>Query executed successfully.</span>";
	if(!result.isValidRow()) return;
	
	var table = "<table class='db-results'>";
	
	table += "<tr>";
	table += "<th class='result-count'>#</th>";
	var field_count = result.fieldCount();
	for(var i=0; i<field_count; i++) {
		table += "<th>"+ result.fieldName(i) + "</th>";
	}
	table += "</tr>";
	
	var class = 'odd';
	var result_count = 1;
	while (result.isValidRow()) {
		class = (class=='even') ? 'odd' : 'even';
		table += "<tr class='" + class + "'>";
		table += "<td class='result-count'>"+ result_count + "</td>";
		for(var i=0; i<field_count; i++) {
			table += "<td>"+ result.field(i) + "</td>";
		}
		table += "</tr>";

		result_count++;
		result.next();
	}
	result.close();
	
	area.innerHTML = "<span class='message-success'>Query executed successfully. Result...</span>" + table;
}

function execAndShowQuery(query, display_area) {
	if(!gg_installed) {
		$(display_area).innerHTML = "<div class='no-google-gears-notice'>Please install Google Gears for this feature</div>";
		return;
	}
	
	var results_array = execQuery(query);
	
	if(typeof display_area == 'string') display_area = $(display_area);

	if(!results_array[1]) {
		showResults(results_array[0], display_area);
		return results_array[0];
	}
	else alert("Error : " + results_array[1]);
}

function tryQueryBox(area) {
	if(!gg_installed) return;
	
	var query = area.getElementsByTagName("TEXTAREA")[0].value;
	var result_area = area.getElementsByTagName("DIV")[0];
	
	execAndShowQuery(query, result_area);
}

function initilizePage(current_page) {
	if(!gg_installed) return;
	
	for(var page=1; page <= current_page; page++) { //Do the initilization for all pages before the current one
		if(page == 1) {
			if(!checkDataExists("SELECT id FROM Character")) {
				//Create the table if it does not exist
				execQuery("CREATE TABLE IF NOT EXISTS Character ("
					+ "id INTEGER PRIMARY KEY,"
					+ "name TEXT,"
					+ "description TEXT,"
					+ "job TEXT,"
					+ "iq INTEGER,"
					+ "birthday TEXT,"
					+ "sex TEXT,"
					+ "status INTEGER)");
			}
			
			//Insert the data - only if there is nothing inside it
			result = db.execute("SELECT COUNT(*) FROM Character WHERE name LIKE 'Homer%'");
			if(result.field(0) === 0)  execQuery("INSERT INTO Character(id, name,job,iq,sex,status) VALUES(1, 'Homer Simpson','Nuclear Power Plant Safety Inspector',80,'m',1)");
			
			result = db.execute("SELECT COUNT(*) FROM Character WHERE name LIKE 'Marge%'");
			if(result.field(0) === 0) execQuery("INSERT INTO Character(id, name,job,iq,sex,status) VALUES(2, 'Marge Simpson','Housewife',110,'f',1)");
			
			result = db.execute("SELECT COUNT(*) FROM Character WHERE name LIKE 'Bart%'");
			if(result.field(0) === 0) execQuery("INSERT INTO Character(id, name,job,iq,sex,status) VALUES(3, 'Bart Simpson','Student',100,'m',1)");
			
			result = db.execute("SELECT COUNT(*) FROM Character WHERE name LIKE 'Lisa%'");
			if(result.field(0) === 0) execQuery("INSERT INTO Character(id, name,job,iq,sex,status) VALUES(4, 'Lisa Simpson','Student',159,'f',1)");
			
			result = db.execute("SELECT COUNT(*) FROM Character WHERE name LIKE 'Maggie%'");
			if(result.field(0) === 0) execQuery("INSERT INTO Character(id, name,job,iq,sex,status) VALUES(5, 'Maggie Simpson','Baby',100,'f',1)");

		} else if(page == 2) {
			if(checkDataExists("SELECT id FROM Quote")) continue;

			execQuery("CREATE TABLE IF NOT EXISTS Quote ("
				+ "id INTEGER PRIMARY KEY,"
				+ "character_id INTEGER,"
				+ "is_catch_phrase INTEGER,"
				+ "quote TEXT)");
			var result = db.execute("SELECT COUNT(*) FROM Quote");
			if(result.field(0) === 0) {
				execQuery("INSERT INTO Quote(character_id,quote) VALUES(3,\"Aren't we forgeting the true meaning of Christmas? You know, the birth of Santa.\")");
				execQuery("INSERT INTO Quote(character_id,quote) VALUES(1,\"What's a wedding?  Webster's dictionary describes it as the act of removing weeds from one's garden.\")");
			}
		
		} else if(page == 6) {
			result = db.execute("SELECT COUNT(*) FROM Character WHERE name LIKE '%Chief Wiggum%'");
			if(result.field(0) === 0) execQuery("INSERT INTO Character(id, name,job,iq,sex,status) VALUES(6, 'Chief Wiggum','Cop',90,'m',1)");
			
			result = db.execute("SELECT COUNT(*) FROM Quote");
			if(Number(result.field(0)) < 3) {
				execQuery("INSERT INTO Quote(character_id,quote) VALUES(6, \"No, you got the wrong number. This is nine-one... two.\")");
				execQuery("INSERT INTO Quote(character_id,quote) VALUES(1, \"Ah, beer. The cause of and the solution to all of life's problems. \")");
				execQuery("INSERT INTO Quote(character_id,quote) VALUES(1, \"Maybe, just once, someone will call me 'Sir' without adding, 'You/re making a scene'. \")");
				execQuery("INSERT INTO Quote(character_id,quote) VALUES(1, \"It's not easy to juggle a pregnant wife and a troubled child, but somehow I managed to fit in eight hours of TV a day\")");
				execQuery("INSERT INTO Quote(character_id,quote) VALUES(1, \"Trying is the first step toward failure.\")");
				execQuery("INSERT INTO Quote(character_id,quote) VALUES(1, \"America's health care system is second only to Japan, Canada, Sweden, Great Britain, well...all of Europe. But you can thank your lucky stars we don't live in Paraguay! \")");
				execQuery("INSERT INTO Quote(character_id,quote) VALUES(1, \"English? Who needs that? I'm never going to England! \")");
				execQuery("INSERT INTO Quote(character_id,quote) VALUES(1, \"Well, let's just call them, uh, Mr. X and Mrs. Y. So anyway, Mr. X would say, 'Marge, if this doesn't get your motor running, my name isn't Homer J. Simpson.' \")");
				execQuery("INSERT INTO Quote(character_id,quote) VALUES(1, \"Now, Marge, don't discourage the boy. Weaseling out of things is what separates us from the animals. Except the weasel. \")");
				execQuery("INSERT INTO Quote(character_id,quote) VALUES(1, \"I don't have to be careful, I've got a gun! \")");
				execQuery("INSERT INTO Quote(character_id,quote) VALUES(3, \"If TV has taught me anything, it's that miracles always happen to poor kids at Christmas.\")");
				execQuery("INSERT INTO Quote(character_id,quote) VALUES(3, \"There are no good wars, with the following exceptions: the American Revolution, World War II, and the Star Wars Trilogy.\")");
				execQuery("INSERT INTO Quote(character_id,is_catch_phrase, quote) VALUES(3,1, \"I didn't do it. No one saw me do it. No one can prove anything.\")");
			}		
		} else if(page == 10) {
			if(current_page == 10) { //This is only needed in the current page.
				result = db.execute("SELECT COUNT(*) FROM Character WHERE name LIKE '%Griffin%'");
				if(result.field(0) === 0) execQuery("INSERT INTO Character(name,job,iq,sex,status) VALUES('Peter Griffin', 'Toy Company Employee', 80, 'm', 0)");
			}
			
		}  else if(page == 12) { //Database Relations
			if(!checkDataExists("SELECT id FROM Episode")) {
				execQuery("CREATE TABLE IF NOT EXISTS Episode ("
					+ "id INTEGER PRIMARY KEY,"
					+ "name TEXT,"
					+ "season INTEGER)");
				
				execQuery("INSERT INTO Episode(name, season) VALUES('Simpsons Roasting on an Open Fire',1)");
				execQuery("INSERT INTO Episode(name, season) VALUES('Bart the Genius',1)");
				execQuery("INSERT INTO Episode(name, season) VALUES('Homer's Odyssey',1)");
				execQuery("INSERT INTO Episode(name, season) VALUES('Life on the Fast Lane',1)");
				execQuery("INSERT INTO Episode(name, season) VALUES('Bart Gets an F',2)");
				execQuery("INSERT INTO Episode(name, season) VALUES('Dead Putting Society',2)");
				execQuery("INSERT INTO Episode(name, season) VALUES('Bart's Dog Gets an F',2)");
				execQuery("INSERT INTO Episode(name, season) VALUES('Homer Defined',3)");
				execQuery("INSERT INTO Episode(name, season) VALUES('Lisa the Greek',3)");
				execQuery("INSERT INTO Episode(name, season) VALUES('Marge Gets a Job',4)");
				execQuery("INSERT INTO Episode(name, season) VALUES('Bart's Inner Child',5)");
				execQuery("INSERT INTO Episode(name, season) VALUES('Bart's Girlfriend',6)");
				execQuery("INSERT INTO Episode(name, season) VALUES('Bart Sells His Soul',1)");
			}
			if(!checkDataExists("SELECT character_id FROM CharacterEpisode")) {	
				//The Reference Table
				execQuery("CREATE TABLE IF NOT EXISTS CharacterEpisode ("
					+ "character_id INTEGER,"
					+ "episode_id INTEGER)");
				execQuery("INSERT INTO CharacterEpisode(character_id, episode_id) VALUES(1,1)");
				execQuery("INSERT INTO CharacterEpisode(character_id, episode_id) VALUES(2,1)");
				execQuery("INSERT INTO CharacterEpisode(character_id, episode_id) VALUES(3,1)");
				execQuery("INSERT INTO CharacterEpisode(character_id, episode_id) VALUES(4,1)");
				execQuery("INSERT INTO CharacterEpisode(character_id, episode_id) VALUES(5,1)");
				execQuery("INSERT INTO CharacterEpisode(character_id, episode_id) VALUES(1,2)");
				execQuery("INSERT INTO CharacterEpisode(character_id, episode_id) VALUES(2,2)");
				execQuery("INSERT INTO CharacterEpisode(character_id, episode_id) VALUES(3,2)");
				execQuery("INSERT INTO CharacterEpisode(character_id, episode_id) VALUES(4,3)");
				execQuery("INSERT INTO CharacterEpisode(character_id, episode_id) VALUES(5,3)");
				execQuery("INSERT INTO CharacterEpisode(character_id, episode_id) VALUES(1,3)");
				execQuery("INSERT INTO CharacterEpisode(character_id, episode_id) VALUES(2,3)");
				execQuery("INSERT INTO CharacterEpisode(character_id, episode_id) VALUES(3,4)");
				execQuery("INSERT INTO CharacterEpisode(character_id, episode_id) VALUES(4,4)");
				execQuery("INSERT INTO CharacterEpisode(character_id, episode_id) VALUES(5,4)");
				execQuery("INSERT INTO CharacterEpisode(character_id, episode_id) VALUES(1,4)");
				execQuery("INSERT INTO CharacterEpisode(character_id, episode_id) VALUES(1,5)");
				execQuery("INSERT INTO CharacterEpisode(character_id, episode_id) VALUES(3,5)");
				execQuery("INSERT INTO CharacterEpisode(character_id, episode_id) VALUES(1,6)");
				execQuery("INSERT INTO CharacterEpisode(character_id, episode_id) VALUES(2,6)");
			}
		}
	}
}

function checkDataExists(query) {
	if(!gg_installed) return;
	var result_area = execQuery(query);
	if(result_area[1]) return false; //Error
	if(!result_area[0].isValidRow()) return false;
	
	return true;
}

function showNoGearsError(ele, query) {
	ele.innerHTML = query + "<br /><div class='no-google-gears-notice'>Google gears not installed. You cannot view the interactive version "
		+ "of the tutorial without it. Please <a href='http://gears.google.com/?action=install&message=Install Google Gears for the interactive SQL tutorial&return="+escape(location.href)+"'>"
		+ "install google gears</a> if you want to try out the SQL queries as you learn(highly recommended)</div>";
}

function tutorialInit() {
	if (window.google && google.gears) gg_installed = true;
	if(gg_installed) {
		db = google.gears.factory.create('beta.database');
		db.open('sql_tutorial_binco');
	}
	
	var qboxs = getElementsByClassName("query-box");
	for(var i=0; box=qboxs[i], i<qboxs.length; i++) {
		var query = box.innerHTML;
		if(gg_installed) {
			box.innerHTML = "<form  class='db-viewer' action=''>"
				+ "<fieldset><legend>SQL Test Area</legend>"
				+ "<textarea name='sql' class='sql' rows='3' cols='70'>"+query+"</textarea><br />"
				+ "<input type='button' value='Execute' id='exec' onclick='tryQueryBox(this.parentNode.parentNode)' />"
				+ "<div class='results-display'></div>"
				+ "</fieldset></form>"
		} else {
			showNoGearsError(box, query);
		}
	}
	
	var execs = getElementsByClassName("exec-query");
	for(var i=0; ex=execs[i], i<execs.length; i++) {
		var query = ex.innerHTML;
		if(!gg_installed) {
			showNoGearsError(ex, query);
			continue;
		}
		
		execAndShowQuery(query, ex);
	}
	
	if(typeof window.init == "function") init();
}
addEvent(window,'load',tutorialInit);