//
var systemDB; 
/*! Initialize the systemDB global variable. */ 
//
// Tables: ownerData, formData, teachers, observations
//
//
function CreateRecordStore(){
	var shortName = 'observerDB'; 
	var version = '2.0'; //2.0
	var displayName = 'Observer Database'; 
	var maxSize = 65536; // in bytes
	
	return new RecordStore(shortName, version, displayName, maxSize);
}
function DeleteRecordStore(){
	// completely remove all adata
	// Tables: ownerData, formData, teachers, observations
	var myDB = systemDB;
	//	
	myDB.transaction( 
		function (transaction) { 
			transaction.executeSql('DROP TABLE ownerData;'); 
			transaction.executeSql('DROP TABLE formData;'); 
			transaction.executeSql('DROP TABLE teachers;'); 
			transaction.executeSql('DROP TABLE observations;'); 
            //transaction.executeSql('DROP TABLE settings;'); 
		} 
	); 
	// recreate them
	createTables(systemDB); 
}

function RecordStore(shortName, version, displayName, maxSize)
{
	// creates or opens a new database named 'observerDB'
	systemDB = this._init(shortName, version, displayName, maxSize);
	//
	if (useCookies)
		return; // don't bother continueing if no database support
	//
	if (!systemDB ){
		_alert("Javascript Database creation failed!",'Error');
		useCookies = true;//set flag
	} else
		createTables(systemDB); 
	//
}

RecordStore.prototype._init = function(shortName, version, displayName, maxSize){
	var mydb = null;
	try { 
		if (!window.openDatabase || useCookies) { 
			//alert('Javascript Database not supported...Using cookies instead.'); 
            _alert('Offline Database not supported!  Please upgrade to iPhone OS 2.0+ or Android OS 2.0+ in order to use LoTi Observer.','Error');
			useCookies = true;//set flag
		} else { 
        
            mydb = openDatabase(shortName, version, displayName, maxSize); 
			//mydb = openDatabase(shortName, "", displayName, maxSize); 
            
			// You should have a database instance in mydb. 
			//mydb
		} 
	} catch(e) { 
		// Error handling code goes here. 
        _alert("Database version mismatch.  The structure of the database has changed, Updating database to the new structure.",'Error'); 
        //
        mydb = openDatabase(shortName, "", displayName, maxSize); 
        var oldversion = mydb.version;
        console.log("oldversion: "+oldversion);    
        try {
            // comment out for crash recovery.
            mydb.changeVersion(oldversion, "2.0", 
                function (t) {
                    t.executeSql('ALTER TABLE formData ADD COLUMN name TEXT;');     
                },
            err_1_0_2_0, success_1_0_2_0);
        } catch(e) {
            _alert('Change version 1.0 -> 2.0 failed.  Resetting to default values','Error');
             myDB.transaction( 
                function (transaction) { 
                    transaction.executeSql('DROP TABLE ownerData;'); 
                    transaction.executeSql('DROP TABLE formData;'); 
                    transaction.executeSql('DROP TABLE teachers;'); 
                    transaction.executeSql('DROP TABLE observations;'); 
                    //transaction.executeSql('DROP TABLE settings;'); 
                } 
            )
        }
           
	}
	return mydb;//null on failure
}
/*    ************      */
function cv_1_0_2_0(transaction){
    myDB.transaction( 
        function (transaction) { transaction.executeSql('ALTER TABLE formData ADD COLUMN name TEXT;', [], nullDataHandler, errorHandler);}
    );
}
function err_1_0_2_0(error)
{
    _alert('Error in database 1.0 -> 2.0 conversion.  Error was '+error.message,'Error');
    return true; // treat all errors as fatal
}
function success_1_0_2_0()
{
    _alert("Database changed from version 1.0 to version 2.0.", 'Alert');
}

/*    ************      */
function nullDataHandler(transaction, results) { 

}
function errorHandler(transaction, error) { 
	// Error is a human-readable string. 
	_alert('Database Error: '+error.name+" "+error.message+' (Code '+error.code+')','Error'); 
	//
	// Handle errors here 
	var we_think_this_error_is_fatal = true; 
	if (we_think_this_error_is_fatal)
		return true; 
	//
	return false; 
} 
RecordStore.prototype.getRow = function(tableName, primarykey, keyvalue) { 
	var sqlString = 'SELECT * from '+tableName+' where '+primarykey+'='+keyvalue+';';
	var myDB = systemDB;
	var theResult = null;
	//
	myDB.transaction( 
		function (transaction) { 
			transaction.executeSql(sqlString,[],
				function (transaction, results) { 
					theResult = results;//capture results
				}, errorHandler); 
		}
	);
	return theResult;	
}
RecordStore.prototype.getAllRows = function(tableName) { 
	var sqlString = 'SELECT * from '+tableName+';';
	var myDB = systemDB;
	var theResult = null;
	//
	myDB.transaction( 
		function (transaction) { 
			transaction.executeSql(sqlString,[],
				function (transaction, results) { 
					alert("getAllRows result.rows.length="+results.rows.length);
					theResult = results;//capture results
				}, errorHandler); 
		}
	);
	return theResult;	
}
RecordStore.prototype.getOwnerData = function() {
	var myDB = systemDB;
	
	var sqlString = 'SELECT * from ownerData;';

	myDB.transaction( 
		function (transaction) { 
			transaction.executeSql(sqlString,[],
				function (transaction, results) { 
					//alert("_loadOwnerData results.rows.length="+results.rows.length);
					if (results.rows.length){ //if database not empty, load data
						var a = results.rows.item(0);
						//
						ownerID = new Number(a['id']);
						//
						//alert("_loadOwnerData ownerID ="+ownerID);
						//
						document.getElementById('userID').value = unescape(a["userid"]);
						document.getElementById('pswd').value = unescape(a["pswd"]);
						document.getElementById('rememberCheckbox').checked = new Boolean(a["rememberMe"] > 0);
					}
				}, errorHandler); 
		}
	);
		
}
RecordStore.prototype.getTeacherData = function(){
	var myDB = systemDB;
	var sqlString = 'SELECT * from teachers;';
	myDB.transaction(
		function (transaction) {
			transaction.executeSql(sqlString, [], teacherDataHandler, errorHandler); 
		}
	);
	
}
function teacherDataHandler(transaction, results) {
	//
	if (results.rows.length){ //if database not empty, load data
		teachers = [];//empty existing
		for (var i=0;i< results.rows.length;i++){
			var row = results.rows.item(i);
			var id = row['id'];
			var name = unescape(row['name']);
			var email = unescape(row['email']);
			teachers[i] = new teacher(name, email, id);//teacher(name, email, id)
		}
		// sort
		teachers.sort(sortTeacher);
		needsDisplay = true;
	}
}
RecordStore.prototype.getFormData = function(){
	var myDB = systemDB;
	var sqlString = 'SELECT * from formData;';	
    // get specific form.... set formID=null to avoid.
    if (formID)
        sqlString = 'SELECT * from formData WHERE id='+formID+';';
    //
	myDB.transaction(
		function (transaction) {
			transaction.executeSql(sqlString,[], 
			function (transaction, results) {
				/* ["id","titles","descriptions","lookfors"] */
                /* ["id","name", "titles","descriptions","lookfors"] */
				for (var i=0;i< results.rows.length;i++){
                    //
                    // get the row
					var row = results.rows.item(i);
					//
					var titles = unescape(row['titles']);
					var descriptions = unescape(row['descriptions']);
                    //
                    // issue lookfors may contain '~' or '&'
					var lookfors = unescape(row['lookfors']);
                    //
					var sectionArray = lookfors.split('&&');
                    if (lookfors.indexOf('&&') == -1)
                        sectionArray = lookfors.split('&');// in case it was stored with a single &
                    //
					var theData = [];
					for (var j=0;j<sectionArray.length;j++){
                        var lineString = sectionArray[j];
                        //var lineString = unescape(sectionArray[j]);
						theData[j] = lineString.split('~');
					}
                    //
                    // ** SET GLOBALS **
                    // formID
                    formID = new Number(row['id']);//set global ID
                    //
                    // formname
                    var aformName = row['name'];
                    if (aformName){
                        // if this record has a form name, set the global form to this formname
                        formName = decodeString(aformName);
                        // 
                    }
                    //
                    // formTemplate    
                    formTemplate = new observationTemplate(titles.split('~'), theData, descriptions.split('~'));
                    //formTemplate = new observationTemplate(row['id'], row['name'], titles.split('~'), theData, descriptions.split('~'));
                    //
                    formTemplateArray[i] = new Array(formID,formName,formTemplate);
                    //
					sectionListNeedsDisplay = true;
                    //
                    // set form display string
                    var formnameObject = document.getElementById("currentTemplateName");    // has name of selected form
                    if (formnameObject)
                        formnameObject.innerText = formName;
                    //
				}
			},  errorHandler); 
		}
	);
	
}
RecordStore.prototype.getObservationData = function(){
	var myDB = systemDB;
	var sqlString = 'SELECT * from observations;';	
	myDB.transaction(
		function (transaction) {
			transaction.executeSql(sqlString,[], 
			function (transaction, results) {
               
				if (results.rows.length){ //if database not empty, load data
					//
					//clear array
					observations = [];
					// for each observation
					var tempObject;
                    //
                    // save form setup
                    var saved_formID = formID;
                    var saved_formName = formName;
                    var saved_formTemplate = formTemplate;//set
                    //
					for (var i=0;i< results.rows.length;i++){
						//
						var row = results.rows.item(i);
                        
						//"observations",["formid","teacherid","packedvalues"]
						var formid = row['formid'];		
						var teacherid = row['teacherid'];	
						//
						var begindate= new Date();
						//begindate.setTime(Date.parse(row['begindate']));
						begindate.setTime(row['begindate']);
						var enddate = null;
						if (null != row['enddate'] && !(""==row['enddate'])){
							enddate = new Date();
							//enddate.setTime(Date.parse(row['enddate']));
							enddate.setTime(row['enddate']);
						}	
						//
						var packedValues = decodeString(row['packedvalues']);	
						//
                        
						// ** Create observation record
						tempObject = new observation(teacherid, formid);
						//	
						setPackedValues(tempObject,packedValues);
						// dates
						tempObject.beginDate = begindate;
						tempObject.endDate = enddate;
						if (null !== enddate){
							tempObject.done = true;
                        } 
						//
						// comment
						var comment = decodeString(row['comment']);	
						tempObject.comment = comment.cleanstring();
						//
                        // ** SET GLOBALS **
                        //
						observations[i] = tempObject;
						//
					}
                    // restore form setup
                    formID= saved_formID;
                    formName = saved_formName;
                    formTemplate = saved_formTemplate;//set
                    //

					//
				}
			},  errorHandler); 
		}
	);
	
}
function myDataHandler (transaction, results) { 
	//	
	var resultString = '';
	for (var i=0; i<results.rows.length; i++) { 
		var row = results.rows.item(i); 
		resultString += row['fieldname'] + '\n'; 
	} 
	alert("SELECT result ="+resultString);
	//
	if (row > 0){ doUpdate = true; } else { doUpdate = false;}
}
RecordStore.prototype.deleteRow = function(tableName,primaryKey, keyValue) { 
	var myDB = systemDB;
	//
	// delete if exists
	myDB.transaction( 
		function (transaction) {  
			var sqlString = 'DELETE from '+tableName+' where '+tableName+'.'+primaryKey+'='+keyValue+';';
			//
			transaction.executeSql(sqlString, [],nullDataHandler, errorHandler); 
		} 
	);
}			
RecordStore.prototype.addRow = function(tableName,fieldArray, valueArray) { 

	//alert("recordStore.addRow");

	// update or insert?  - assume first field/value pair is primary key
	var myDB = systemDB;
	var doUpdate = false;//do update or do insert
	//
	if (!myDB)
		alert("recordStore.addRow Error myDB is NULL!!");
	// delete if exists
	//this.deleteRow(tableName, fieldArray[0], valueArray[0]);
	myDB.transaction( 
		function (transaction) {  
			/* insertions will fail and rollback if a row with that primary key exists, so next line - update*/ 
			var sqlString = 'DELETE from '+tableName+' where '+tableName+'.'+fieldArray[0]+'='+valueArray[0]+';';
			//
			transaction.executeSql(sqlString, [],nullDataHandler, 
			  function (transaction, error) { 
				// Error is a human-readable string. 
                // code 2 is version changed - ignore since this is just the first run after an update
                if (error.code !== 2){
                    /* Handle errors here */
                    _alert('SQLight Database Error: '+error.message+' (Code '+error.code+') '+sqlString, 'Error'); 
                    //
                    var we_think_this_error_is_fatal = true; 
                    if (we_think_this_error_is_fatal)
                        return true; 
                }
				return false; }
			); 
		} 
	);
	if (!doUpdate){
		// insert
		// join field names together
		var setString = fieldArray.join(', ');	
		var valueString = valueArray.join(', ');
		//
		myDB.transaction( 
			function (transaction) {  
				/* insertions will fail and rollback if a row with that primary key exists, so next line - update*/ 
				var sqlString = 'insert into '+tableName+' ('+setString+') VALUES ('+valueString+');';
				//
				//alert(tableName+' insert record: '+sqlString);
				//
				transaction.executeSql(sqlString, [], nullDataHandler, function (transaction, error) { 
                    // Error is a human-readable string. 
                    if (error.code !== 2){
                        /* Handle errors here */
                        _alert('SQLight Database Error: '+error.message+' (Code '+error.code+') '+sqlString, 'Error'); 
                        /* Handle errors here */
                        var we_think_this_error_is_fatal = true; 
                        if (we_think_this_error_is_fatal)
                            return true; 
                    }    
                    return false; }); 
			} 
		);
	} else {
		// update
		myDB.transaction( 
			function (transaction) {  
				/* UPDATE will fail and rollback if a row with that primary key DOES NOT exist */ 
				var sqlString = 'UPDATE '+tableName+'  set ?=? where '+fieldArray[0]+'='+valueArray[0]+';';
				//
				alert('UPDATE record: '+sqlString);
				//
				transaction.executeSql(sqlString, [fieldArray, valueArray], nullDataHandler, errorHandler); 
			} 
		);

	}
}
//
function createTables(myDB) { 
	/*
	// Tables: ownerData, formData, teachers, observations
	//		ownerData:  id, userid, pswd, rememberMe
	//		formData:	id, [name], titles, descriptions, lookfors
	//		teachers:	id, name, email
	//		observations:	[id], formid, teacherid, packedvalues
	//
	*/
	//var myDB = systemDB;
    //
    //
	// owner data
	myDB.transaction( 
		function (transaction) { 
			/* The first query causes the transaction to (intentionally) fail if the table exists. */ 
			transaction.executeSql('CREATE TABLE IF NOT EXISTS ownerData(id INTEGER NOT NULL PRIMARY KEY, userid TEXT NOT NULL, pswd TEXT NOT NULL, rememberMe INTEGER DEFAULT 1);', [], nullDataHandler, errorHandler);
			/* These insertions will be skipped if the table already exists. */ 
			//transaction.executeSql('insert into ownerData (id, userid, pswd) VALUES ("lotiadmin", "lotiadmin", 1);', [], nullDataHandler, errorHandler); 
		} 
	);
	//
	// form data
	myDB.transaction( 
		function (transaction) { 
			/* The first query causes the transaction to (intentionally) fail if the table exists. */ 
			transaction.executeSql('CREATE TABLE IF NOT EXISTS formData(id INTEGER NOT NULL PRIMARY KEY, name TEXT, titles TEXT NOT NULL, descriptions TEXT NOT NULL, lookfors TEXT NOT NULL);', [], nullDataHandler, errorHandler); 
			//
            // transaction.executeSql('CREATE TABLE IF NOT EXISTS formData(id INTEGER NOT NULL PRIMARY KEY, titles TEXT NOT NULL, descriptions TEXT NOT NULL, lookfors TEXT NOT NULL);', [], nullDataHandler, errorHandler); 
		} 
	);
	// teachers
	myDB.transaction( 
		function (transaction) { 
			/* The first query causes the transaction to (intentionally) fail if the table exists. */ 
			transaction.executeSql('CREATE TABLE IF NOT EXISTS teachers(id INTEGER NOT NULL PRIMARY KEY, name TEXT, email TEXT NOT NULL);', [], nullDataHandler, errorHandler); 
			//
		} 
	); 
	//
	// observations
	myDB.transaction( 
		function (transaction) { 
			/* The first query causes the transaction to (intentionally) fail if the table exists. */ 
			transaction.executeSql('CREATE TABLE IF NOT EXISTS observations(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, formid INTEGER NOT NULL, teacherid INTEGER NOT NULL, begindate TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, enddate TEXT, packedvalues TEXT, comment TEXT);', [], nullDataHandler, errorHandler); 
			// 
			//transaction.executeSql('CREATE TABLE IF NOT EXISTS observations(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, formid INTEGER NOT NULL, teacherid INTEGER NOT NULL, begindate TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, enddate TEXT, packedvalues TEXT);', [], nullDataHandler, errorHandler); 
		} 
	); 
	// settings
    /*
	myDB.transaction( 
		function (transaction) { 
			// The first query causes the transaction to (intentionally) fail if the table exists. 
			transaction.executeSql('CREATE TABLE IF NOT EXISTS settings(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, lastupdate TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, currentFormName TEXT, currentAccountOwnerId INTEGER NOT NULL, fontSize INTEGER NOT NULL DEFAULT 14);', [], nullDataHandler, errorHandler); 
			// 
		} 
	); 
    */
}