//1. initialization

var localDB = null;

function onInit(){
    try {
        if (!window.openDatabase) {
            updateStatus("Error: DB not supported");
        }
        else {
            initDB();
            createTables();
            queryAndUpdateOverview();
        }
    }
    catch (e) {
        if (e == 2) {
            updateStatus("Error: Invalid database version.");
        }
        else {
            updateStatus("Error: Unknown error " + e + ".");
        }
        return;
    }
}

function initDB(){
    var shortName = 'tripDb';
    var version = '1.0';
    var displayName = 'MyGCTripManager';
    var maxSize = 65536; // in bytes
    localDB = window.openDatabase(shortName, version, displayName, maxSize);
}

function createTables(){
    var query = 'CREATE TABLE IF NOT EXISTS gcTrip(gcId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, gcType VARCHAR NOT NULL, gcDate VARCHAR NOT NULL, gcContact VARCHAR, gcOdoStart INTEGER NOT NULL, gcOdoEnd INTEGER NOT NULL, gcComments VARCHAR, gcDistance INTEGER NOT NULL, gcRate FLOAT NOT NULL, gcAmount FLOAT NOT NULL);';
    try {
        localDB.transaction(function(transaction){
            transaction.executeSql(query, [], nullDataHandler, errorHandler);
            updateStatus("Table 'gcTrip' is present");
        });
    }
    catch (e) {
        updateStatus("Error: Unable to create table 'gcTrip' " + e + ".");
        return;
    }
}

function dropTable(){
    var query = 'DROP TABLE gcTrip;';
    try {
        localDB.transaction(function(transaction){
            transaction.executeSql(query, [], nullDataHandler, errorHandler);
            updateStatus("Table 'tsTrip' is dropped");
        });
    }
    catch (e) {
        updateStatus("Error: Unable to drop table 'tsTrip' " + e + ".");
        return;
    }
}

//2. query db and view update

// event handler start with on*


function onUpdate(){
    var gcId = document.tripForm.txtId.value;
    var gcType = document.tripForm.txtType.value;
    var gcContact = document.tripForm.txtContact.value;
    // adds for nulls here
    if (gcType == "") {
        updateStatus("'gcType' and put in date are required fields!");
    }
    else {
        var query = "update gcTrip set gcType=? where gcId=?;";
        try {
            localDB.transaction(function(transaction){
                transaction.executeSql(query, [gcType, gcContact, gcId], function(transaction, results){
                    if (!results.rowsAffected) {
                        updateStatus("Error: No rows affected");
                    }
                    else {
                        updateForm("", "", "");
                        updateStatus("Updated rows:" + results.rowsAffected);
                        queryAndUpdateOverview();
                    }
                }, errorHandler);
            });
        }
        catch (e) {
            updateStatus("Error: Unable to perform an UPDATE " + e + ".");
        }
    }
}

function onDelete(){
    var gcId = document.tripForm.gcId.value;

    var query = "delete from gcTrip where gcId=?;";
    try {
        localDB.transaction(function(transaction){

            transaction.executeSql(query, [gcId], function(transaction, results){
                if (!results.rowsAffected) {
                    updateStatus("Error: No rows affected.");
                }
                else {
                    updateForm("", "", "");
                    updateStatus("Deleted rows:" + results.rowsAffected);
                    queryAndUpdateOverview();
                }
            }, errorHandler);
        });
    }
    catch (e) {
        updateStatus("Error: Unable to perform an DELETE " + e + ".");
    }

}

function onCreate(){

    var type = document.tripForm.txtType.value;
    var date = document.tripForm.txtDate.value;
    var contact = document.tripForm.txtContact.value;
    var start = document.tripForm.txtStart.value;
    var end = document.tripForm.txtEnd.value;
    var comment = document.tripForm.txtComment.value;
    var distance = document.tripForm.txtDistance.value;
    var rate = document.tripForm.txtRate.value;
    var amount = document.tripForm.txtAmount.value;
    // more error handling needed
    if (gcType == "" ) {
        updateStatus("Error: 'gcType' and 'Name' are required fields!");
    }
    else {
        var query = "INSERT INTO gcTrip(gcType, gcDate, gcContact, gcOdoStart, gcOdoEnd, gcComments, gcDistance, gcRate, gcAmount) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
        try {
            localDB.transaction(function(transaction){
                transaction.executeSql(query, [type, date, contact, start, end, comment, distance, rate, amount], function(transaction, results){
                    if (!results.rowsAffected) {
                        updateStatus("Error: No rows affected.");
                    }
                    else {
                        updateForm("", "", "");
                        updateStatus("Inserted row with id " + results.insertId);
                        queryAndUpdateOverview();
                    }
                }, errorHandler);
            });
        }
        catch (e) {
            updateStatus("Error: Unable to perform an INSERT " + e + ".");
        }
    }
}

function onSelect(htmlLIElement){
    var id = htmlLIElement.getAttribute("gcId");

    query = "SELECT * FROM gcTrip where gcId=?;";
    try {
        localDB.transaction(function(transaction){

            transaction.executeSql(query, [id], function(transaction, results){

                var row = results.rows.item(0);

                updateForm(row['gcId'], row['gcType'], row['gcDate'], row['gcContact'], row['gcOdoStart'], row['gcOdoEnd'], row['gcComments'], row['gcDistance'], row['gcRate'], row['gcAmount']);

            }, function(transaction, error){
                updateStatus("Error: " + error.code + "<br>Message: " + error.message);
            });
        });
    }
    catch (e) {
        updateStatus("Error: Unable to select data from the db " + e + ".");
    }

}

function queryAndUpdateOverview(){

    //remove old table rows
    var dataRows = document.getElementById("itemData").getElementsByClassName("data");

    while (dataRows.length > 0) {
        row = dataRows[0];
        document.getElementById("itemData").removeChild(row);
    };

    //read db data and create new table rows
    var query = "SELECT * FROM gcTrip;";
    try {
        localDB.transaction(function(transaction){

            transaction.executeSql(query, [], function(transaction, results){
                for (var i = 0; i < results.rows.length; i++) {

                    var row = results.rows.item(i);
                    var li = document.createElement("li");
                    li.setAttribute("id", row['gcId']);
                    li.setAttribute("class", "data");
                    li.setAttribute("onclick", "onSelect(this)");

                    var liText = document.createTextNode(row['gcId'] + " x "+ row['gcType']);
                    li.appendChild(liText);

                    document.getElementById("itemData").appendChild(li);
                }
            }, function(transaction, error){
                updateStatus("Error: " + error.code + "<br>Message: " + error.message);
            });
        });
    }
    catch (e) {
        updateStatus("Error: Unable to select data from the db " + e + ".");
    }
}

// 3. misc utility functions

// db data handler

errorHandler = function(transaction, error){
    updateStatus("Error: " + error.message);
    return true;
}

nullDataHandler = function(transaction, results){
}

// update view functions

function updateForm(id, type, date, contact, start, end, comment, distance, rate, amount){
    document.tripForm.txtId.value = id;
    document.tripForm.txtType.value = type;
    document.tripForm.txtDate.value = date;
    document.tripForm.txtContact.value = contact;
    document.tripForm.txtStart.value = start;
    document.tripForm.txtEnd.value = end;
    document.tripForm.txtComment.value = comment;
    document.tripForm.txtDistance.value = distance;
    document.tripForm.txtRate.value = rate;
    document.tripForm.txtAmount.value = amount;
}

function updateStatus(status){
    document.getElementById('status').innerHTML = status;
}

// Total cost calculate function
function onCalc(){
    var qty = document.tripForm.gcType.value;
    var cost = document.tripForm.unitcost.value;
                var totalcost = qty * cost;
                updateCalcFields(totalcost);
}

function updateCalcFields(totalcost){
    document.tripForm.totalcost.value = totalcost;
}