/*
Lil' dodgy entity mapper/DAL for HTML5 databases
by Mr Speaker
http://www.mrspeaker.net
mrspeaker@gmail.com
v0.00001

var db_settings = {
	name : "notes",
	size : 1 * 1024 * 1024,
	version : "1.0",
	description : "My notes database",
	tables : {
		notes : {
			description : 'TEXT',
			content : 'TEXT',
		},
		users : {
			username : 'TEXT',
			password : 'TEXT',
			level : 'INTEGER'
		}
	}
};

Initialise with:
	fivedb.init( db_settings ).

This will insert the tables into the database and add prototypes
	fivedb.cNotes
	fivedb.cUsers
	
The prototypes will have the following automatically created/updated properties:
	id (The object's id that is assigned when inserted)
	date_created (The date/time the object was inserted)
	date_updated (The date/time the object was last saved)
	
These can be instantiated and saved/loaded:
	var note = new fivedb.cNotes();
	note.description = "My first note";
	note.content = "Here is the content of my first note.\nIt's pretty crazy.";
	note.save();

Because the actions are asynchronous you use callbacks:
	note.save(function(){
		alert( 'note was saved to the database!' );
	});
	
	fivedb.LoadWhere( fivedb.cUser, {}, function( result ){
			for( var i = 0; i < result.length; i++ ){
				result[ i ].remove();
			}
		} );
		
Set "dropping" to true to erase the tables.

*/
var fivedb = {
	name : "default",
	size : 1 * 1024 * 1024,
	version : "1.0",
	description  : "default storage",

	db : null,	
	tables : [],
	dropping : false,
	hasDB : false,
	
	debug : false,
	/*
		The init function should be called with an object
		literal that describes the database and it's
		schema (see the docs for details).
	*/
	init : function( settings ){
		// Merge general database settings
		this.name = settings.name || this.name;
		this.size = settings.size || this.size;
		this.version = settings.version || this.version;
		this.description = settings.description || this.description;
		this.tables = settings.tables || this.tables;

		// Open the database
		this.open();
		
		// Create the entity classes
		this.createObjects();
		
		// Insert the tables
		this.createTables();
	},
	
	// Open the database
	open : function(){
		this.hasDB = true;
		if( !window.openDatabase ){
			this.hasDB = false;
			this.log( "ERROR", "environment does not support HTML5 databases." );
			return;
		}
		try {
			this.db = openDatabase( this.name, this.version, this.description, this.size );
		} catch( ex ){
			this.log( "ERROR", "unable to open database." );
		}
	},
	
	log : function(){
		if( this.debug ){
			console.log( 'LOG:', arguments[0], arguments[1], arguments );
		}
	},
	
	/*
		Create the entity prototypes from the database schema definition 
		and attach them to this object so they can be instantiated
	*/
	createObjects : function(){
		var _this = this;
		var name = "";
		for( table in this.tables ){
			var tmpObj = function( id, callback ){ 
				// If an ID is provided, add it
				if( id ){
					this.id = id;
				} 
			};
			var tmpTable = this.tables[ table ];
		/*	tmpTable.date_modified = "DATETIME";
			tmpTable.date_created = "DATETIME";*/
			var tmpFields = [];
			for( field in tmpTable ){
				tmpFields.push( field );
				switch( tmpTable[ field ].toLowerCase() ){
					case "integer":
						tmpObj.prototype[ field ] = -1;
						break;
					default:
						tmpObj.prototype[ field ] = "";
						break;
				}
			}
			
			// Save some meta data to the object
			tmpObj.prototype.__tableName = table;
			tmpObj.prototype.__fieldList = tmpFields;
			
			// Add id and CRUD methods
			tmpObj.prototype.id = null;
			tmpObj.prototype.load = function( callback, error ){ _this.load( this, callback, error ); }
			tmpObj.prototype.save = function( callback ){ _this.save( this, callback ); }
			tmpObj.prototype.remove = function( callback ){	_this.remove( this, callback ); }
						
			// Add the creation function to the main object
			this[ 'c' + table[ 0 ].toUpperCase() + table.substring( 1 ) ] = tmpObj;
		}
	},
	
	createTables : function(){
		if( !this.db ){
			return;
		}

		var _this = this;
		for( var table in this.tables ){
			/*
				// maybe chekc for changes...  but pragma directive seems to give "not authorized" errors...
				this.db.transaction( function( tx ) {
					tx.executeSql("PRAGMA TABLE_INFO("+table+")", [], function(tx, res){
						console.log('prag ', res)
					}, _this.onError); 
				});
			*/
			
			// Create or drop the table
			(function( table ){
				if( _this.dropping ){
					// Drop the table
					_this.execute( "DROP TABLE " + table, [] );
				} 
				else {
					// Create the table if it does not exist.
					var tmpTable = _this.tables[ table ];
					var sqlString = "CREATE TABLE IF NOT EXISTS " + table + " ( $1 )";
					var sqlFields = "id INTEGER PRIMARY KEY ASC, ";
					for( field in tmpTable ){
						sqlFields += field + " " + tmpTable[ field ] + ", ";
					}
					sqlFields += "date_created DATETIME, date_modified DATETIME "
					sqlString = sqlString.replace( "$1", sqlFields );
					_this.execute( sqlString, [] );
				}
			})( table );
		}
	},
	
	/*
		Save the entity to the database
	*/
	save : function( obj, callback ){
		var _this = this;
		var inserting = !obj.id || !(obj.id > 0) ? true : false;
		
		// Set up the SQL for inserting or updating the object
		if( inserting ){
			// create a new record
			var sqlString = "INSERT INTO " + obj.__tableName + "( $1date_created,date_modified ) VALUES ( $2?,? )";
			var sqlFields = "";
			var sqlPlaceHolders = ""
			var sqlValues = [];
			for( var i = 0; i < obj.__fieldList.length; i++ ){
				sqlFields += obj.__fieldList[ i ] + ",";
				sqlValues.push( obj[ obj.__fieldList[ i ] ] );
				sqlPlaceHolders += "?,";
			}
			sqlValues = sqlValues.concat( [ new Date(), new Date() ] ); // Add in dates
			sqlString = sqlString.replace( "$1", sqlFields );
			sqlString = sqlString.replace( "$2", sqlPlaceHolders );
		}
		else {
			// updating an existing record
			var sqlString = "UPDATE " + obj.__tableName + " SET $1date_modified=? WHERE ID=?";
			var sqlFields = "";
			var sqlValues = [];
			for( var i = 0; i < obj.__fieldList.length; i++ ){
				sqlFields += obj.__fieldList[ i ] + "=?,";
				sqlValues.push( obj[ obj.__fieldList[ i ] ] );
			}
			sqlValues = sqlValues.concat( [ new Date(), obj.id ] ); // Add in dates and id
			sqlString = sqlString.replace( "$1", sqlFields );		
		}
		
		// Do it!
		this.log( 'MSG', 'inserting/updating', sqlString);
		this.db.transaction( function( tx ){
	    	tx.executeSql( sqlString, 
	        	sqlValues,
	        	function( tx, result ){
					_this.log( 'MSG', 'record ' + inserting ? 'inserted.' : 'updated.', result );
					
					if( inserting ){
						obj.id = tx.insertId;
					}
					
					if(callback){
						callback();
					}
				},
	        	function( tx, e ){ _this.log( 'ERROR', e.message, tx, e ) }
			);
	    });
	},
	
	execute: function( sqlString, args, success, error ){
		var _this = this;
		this.db.transaction( function(tx) {
			tx.executeSql( sqlString, 
				args, 
				function( tx, r ){
					_this.log( 'SUCCESS', r, tx );
					if( success ){
						success( tx, r );
					}
				}, 
				function( tx, e ){
					_this.log( 'ERROR', e.message, tx, e );
					if( error ){
						error( tx, e );
					}
				}
			);	
		});
	},
	
	/* 
		Delete the object from the database
	*/
	remove : function( obj, callback, error ){
		var _this = this;
		var sqlString = "DELETE FROM " + obj.__tableName + " WHERE id=?";
		this.log( 'MSG', sqlString, obj.id );
		this.execute( sqlString, [ obj.id ], callback, error );
	},
	
	/*
		Load an object based on it's ID.
	*/
	load : function( obj, callback, error ){
		// Make sure object is ready to be loaded into...
		if( !obj || !obj.__tableName || !obj.id > 0 ){
			if( error ){
				error();
			}
			return;
		}
		var _this = this;
		var sqlString = "SELECT * FROM " + obj.__tableName + " WHERE id=?";
		this.log( 'MSG', 'load:', sqlString, obj.id );
		this.db.transaction( function(tx) {
		    tx.executeSql( sqlString, 
				[ obj.id ], 
				function( tx, result ){
					// Loop through fields and assign to object
					if( result.rows && result.rows.length === 1 ){
						obj = _this.mapFields( obj, result.rows.item( 0 ) );
						
						if( callback ){
							callback();
						}
					}
					else{
						_this.log( 'ERROR', "record not fount" );
						if( error ){
							error( "record not found" );
						}
					}					
				}, 
				function( tx, e ){ _this.log( 'ERROR', e.message, tx, e ) }
			);
		});
	},
	
	LoadWhere : function( protoObj, whereObj, callback, error ){
		var _this = this;
		var obj = new protoObj(); // Create tmp object
		
		var sqlString = "SELECT * FROM " + obj.__tableName;
		
		// Create WHERE clause
		var sqlWhereField = [];
		var sqlWhereValues = [];
		for( var whereField in whereObj ){
			sqlWhereField.push( whereField + "=?" );
			sqlWhereValues.push( whereObj[ whereField ] );
		}		
		var sqlWhere = sqlWhereField.length > 0 ? " WHERE " + sqlWhereField.join(" AND ") : ""
		
		this.log( 'MSG', 'load:', sqlString + sqlWhere, 1 );
		this.db.transaction( function(tx) {
		    tx.executeSql( sqlString + sqlWhere, 
				sqlWhereValues, 
				function( tx, result ){
					// Loop through fields and assign to object
					if( result.rows && result.rows.length > 0 ){
						var returnObj = [];
						for( var i = 0; i < result.rows.length; i++ ){
							var newObj = new protoObj();
							returnObj.push( _this.mapFields( newObj, result.rows.item(i) ) );
						}
						
						if( callback ){
							callback( returnObj );
							return returnObj;
						}
					}
					else{
						_this.log( "WARN", "record not found" );
						if( error ){
							error( "no rows found" );
						}
					}					
				}, 
				function( tx, e ){ _this.log( 'ERROR', e.message, tx, e ) }
			);
		});
	},
	
	mapFields : function( obj, record ){
		var fieldList = obj.__proto__.__fieldList;
		fieldList.push( "id", "date_created", "date_modified" );
		for( field in fieldList ){
			obj[ fieldList[ field ] ] = record[ fieldList[ field ] ];
		}
		return obj;
	}
};