import QtQuick 2.5
import QtQuick.LocalStorage 2.0

Item {
    id : localStorage

    property var doctors : []
    property var aoe : []   //areas of expertise

    readonly property int newData: 1
    readonly property int updateData: 2
    readonly property int sameData: 0

    function fetchData(url) {
        request(url, function (resp) {
            if (resp.responseText.length > 0) {
                var md5Hash = resp.getResponseHeader("Content-MD5");

                //strip from html tags (ez regex)
                var clean = resp.responseText.replace(/<\/?[^>]+(>|$)/g, "");

                try {
                    //translate response into object
                    var response = JSON.parse(clean);
                    try {
                        switch(dbCheckHash(md5Hash)) {
                        case newData:
                            //insert md5 hash into hash table
                            dbInsertHash(md5Hash);

                            //insert data into docs table
                            for(var i = 0; i < response.aerzte.length; i++) {
                                dbInsert('Doctors', response.aerzte[i]);
                            }

                            //insert data into aoe table
                            for(i = 0; i < response.fachgebiete.length; i++) {
                                dbInsert('AreaOfExpertise',
                                         response.fachgebiete[i]);
                            }
                            break;
                        case updateData:
                            dbCheckUpdate('Doctors', response.aerzte);
                            dbCheckUpdate('AreaOfExpertise',
                                          response.fachgebiete);
                            dbUpdate('md5hash', md5Hash);
                            break;
                        default:    //sameData
                            break;
                        }
                        template_.readDB();
                    }
                    catch(err) {
                        console.log("Error during inserting! Err.: " + err)
                        template_.readDB();
                    }
                }
                catch (err) {
                    //db finished populating -> fill arrays
                    template_.readDB();
                }
            }
        });
    }

    function request(url, callback) {
        var xhr = new XMLHttpRequest();
        xhr.onreadystatechange = (function(myxhr) {
            return function() {
                callback(myxhr);
            }
        })(xhr);
        xhr.open("GET", url + ((/\?/).test(url) ? "&" : "?") +
                 (new Date()).getTime());
        xhr.send('');
    }

    function dbInit(tableName) {
        var db = dbGetHandle();

        try {
            if (tableName === "Doctors") {
                db.transaction(function (sqlExec) {
                    sqlExec.executeSql('CREATE TABLE IF NOT EXISTS ' +
                                       tableName + ' (
                                        id text,
                                        vorname text,
                                        nachname text,
                                        titel text,
                                        titel_nachstehend text,
                                        geschlecht text,
                                        foto text,
                                        fachgebiete text,
                                        ebene_de text,
                                        ebene_en text,
                                        website text,
                                        email text,
                                        telefon text,
                                        mobil text,
                                        fax text)');
                })
            }
            else if (tableName === "AreaOfExpertise") {  //AreaOfExpertise
                db.transaction(function (sqlExec) {
                    sqlExec.executeSql('CREATE TABLE IF NOT EXISTS ' +
                                       tableName + ' (
                                        id text,
                                        name_de text,
                                        name_en text,
                                        name_ro text,
                                        name_ru text,
                                        subfachgebiete text,
                                        anzeigen text)');
                })
            }
            else {  //md5hash
                db.transaction(function (sqlExec) {
                    sqlExec.executeSql('CREATE TABLE IF NOT EXISTS ' +
                                       tableName + ' (
                                        hash text)');
                })
            }
        }
        catch(err) {
            console.log("Error creating table in database: " + err)
        }
    }

    function dbGetHandle()
    {
        try {
            var db = LocalStorage.openDatabaseSync("DoctorsList",
                                                   "0.1",
                                                   "List of available Doctors and AOEs",
                                                   20000)
        } catch (err) {
            console.log("Error opening database: " + err)
        }
        return db
    }

    function dbInsert(tableName, item)
    {
        if (!dbCheckContent(tableName, item.id))
            return;

        var db = dbGetHandle()
        var rowid = 0;

        if (tableName === 'Doctors') {
            db.transaction(function (sqlExec) {
                sqlExec.executeSql('INSERT INTO ' + tableName +
                                   ' VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
                                   [item.id,
                                    item.vorname,
                                    item.nachname,
                                    item.titel,
                                    item.titel_nachstehend,
                                    item.geschlecht,
                                    item.foto,
                                    convertArrayToString(item.fachgebiete),
                                    item.ebene_de,
                                    item.ebene_en,
                                    item.website,
                                    item.email,
                                    item.telefon,
                                    item.mobil,
                                    item.fax])

                var result = sqlExec.executeSql('SELECT last_insert_rowid()')
                rowid = result.insertId
            })
        }
        else {  //AreasOfExpertise
            db.transaction(function (sqlExec) {
                sqlExec.executeSql('INSERT INTO ' + tableName +
                                   ' VALUES(?, ?, ?, ?, ?, ?, ?)',
                                   [item.id,
                                    item.name_de,
                                    item.name_en,
                                    item.name_ro,
                                    item.name_ru,
                                    JSON.stringify(item.subfachgebiete),
                                    item.anzeigen])

                var result = sqlExec.executeSql('SELECT last_insert_rowid()')
                rowid = result.insertId
            })
        }

        return rowid;
    }

    function dbCheckUpdate(tableName, data) {
        var db = dbGetHandle();
        var results;

        //check if something new or changed
        for(var i = 0; i < data.length; i++) {
            db.transaction(function (sqlExec) {
                results = sqlExec.executeSql('SELECT * FROM ' + tableName +
                                             ' WHERE id LIKE ' + data[i].id);
            })

            if (results.rows.length === 0) { //new entry
                console.log("New Entry " + tableName);
                dbInsert(tableName, data[i]);
            }
            else {  //check if changed
                if (!itemExists(tableName, data, results.rows.item(0))) {
                    console.log("Update " + tableName);
                    dbUpdate(tableName, data[i]);
                }
            }
        }

        db.transaction(function (sqlExec) {
            results = sqlExec.executeSql('SELECT * FROM ' + tableName);
        })

        //check if something to delete
        for(var j = 0; j < results.rows.length; j++) {
            if (!itemToDelete(data, results.rows.item(j))) {
                console.log("Delete " + tableName);
                dbDelete(tableName, results.rows.item(j));
            }
        }
    }

    function itemExists(tableName, newData, refData) {
        var found = false;
        for(var i = 0; i < newData.length; i++) {
            if (sameItem(tableName, newData[i], refData)) {
                found = true;
                break;
            }
        }

        return found;
    }

    function sameItem(tableName, newData, refData) {
        //strip null to be able to compare with ""
        for(var key in refData) {
            if (!refData[key]) refData[key] = '';
        }

        if (tableName === 'Doctors') {
            if (newData.vorname === refData.vorname &&
                    newData.nachname === refData.nachname &&
                    newData.titel === refData.titel &&
                    newData.titel_nachstehend === refData.titel_nachstehend &&
                    newData.geschlecht === refData.geschlecht &&
                    newData.foto === refData.foto &&
                    convertArrayToString(newData.fachgebiete) ===
                    refData.fachgebiete &&
                    newData.ebene_de === refData.ebene_de &&
                    newData.ebene_en === refData.ebene_en &&
                    newData.website === refData.website &&
                    newData.email === refData.email &&
                    newData.telefon === refData.telefon &&
                    newData.mobil === refData.mobil &&
                    newData.fax === refData.fax) {
                return true
            }
            return false;
        }
        else if (tableName === 'AreaOfExpertise') {
            if (newData.name_de === refData.name_de &&
                    newData.name_en === refData.name_en &&
                    newData.name_ro === refData.name_ro &&
                    newData.name_ru === refData.name_ru &&
                    newData.subfachgebiete === refData.subfachgebiete &&
                    newData.anzeigen === refData.anzeigen) {
                return true
            }
            return false;
        }
    }

    function dbUpdate(tableName, data) {
        var db = dbGetHandle();
        var results;

        if (tableName === "md5hash") {
            db.transaction(function (sqlExec) {
                sqlExec.executeSql('UPDATE ' + tableName +
                                   ' SET hash = ' + "'" + data + "'")
            })
        }
        else if (tableName === "Doctors") {
            db.transaction(function (sqlExec) {
                sqlExec.executeSql('UPDATE ' + tableName +
                       ' SET vorname = ' + "'" + data.vorname + "', " +
                       'nachname = ' + "'" + data.nachname + "', " +
                       'titel = ' + "'" + data.titel + "', " +
                       'titel_nachstehend = ' + "'" + data.titel_nachstehend +
                       "', " +
                       'geschlecht = ' + "'" + data.geschlecht + "', " +
                       'foto = ' + "'" + data.foto + "', " +
                       'fachgebiete = ' + "'" + data.fachgebiete + "', " +
                       'ebene_de = ' + "'" + data.ebene_de + "', " +
                       'ebene_en = ' + "'" + data.ebene_en + "', " +
                       'website = ' + "'" + data.website + "', " +
                       'email = ' + "'" + data.email + "', " +
                       'telefon = ' + "'" + data.telefon + "', " +
                       'mobil = ' + "'" + data.mobil + "', " +
                       'fax = ' + "'" + data.fax + "'" +
                       " WHERE id LIKE " + data.id);
            })
        }
        else {  //update aoe
            db.transaction(function (sqlExec) {
                sqlExec.executeSql('UPDATE ' + tableName +
                       ' SET name_de = ' + "'" + data.name_de + "', " +
                       'name_en = ' + "'" + data.name_en + "', " +
                       'name_ro = ' + "'" + data.name_ro + "', " +
                       'name_ru = ' + "'" + data.name_ru + "', " +
                       'subfachgebiete = ' + "'" + data.subfachgebiete + "', " +
                       'anzeigen = ' + "'" + data.anzeigen + "'" +
                       " WHERE id LIKE " + data.id);
            })
        }
    }

    function itemToDelete(refData, dbItem) {
        var found = false;
        for(var i = 0; i < refData.length; i++) {
            if (refData[i].id === dbItem.id) {
                found = true;
                break;
            }
        }

        return found
    }

    function dbDelete(tableName, data) {
        var db = dbGetHandle();

        console.log("Delete: " + data.name_de);
        db.transaction(function (sqlExec) {
            sqlExec.executeSql('DELETE FROM ' + tableName +
                               ' WHERE id LIKE ' + data.id);
        })
    }

    function dbInsertHash(hash) {
        var db = dbGetHandle()
        db.transaction(function (sqlExec) {
            sqlExec.executeSql('INSERT INTO md5hash VALUES(?)', [hash])
        })
    }

    //check if item.id already exists in table
    function dbCheckContent(tableName, id) {
        var db = dbGetHandle();
        var length = 0;

        db.transaction(function (sqlExec) {
            var results = sqlExec.executeSql('SELECT id FROM ' + tableName +
                                             ' WHERE id LIKE ' + id)
            length = results.rows.length
        })

        if (length > 0)
            return false;   //already in DB

        return true;
    }

    function dbCheckHash(hash) {
        var db = dbGetHandle();
        var results;

        db.transaction(function (sqlExec) {
            results = sqlExec.executeSql('SELECT hash FROM md5hash')
        })

        if (results.rows.length === 0)
            return newData;   //no hash stored -> insert first time

        //hash already stored -> check if update is needed
        if (results.rows.length > 0) {
            if (results.rows.item(0).hash !== hash)
                return updateData;   //new hash -> update db
        }

        return sameData;   //nothing to do. same hash
    }

    function dbReadAll(tableName)
    {
        var db = dbGetHandle()
        var results;
        db.transaction(function (sqlExec) {
            if (tableName === 'Doctors') {
                results = sqlExec.executeSql('SELECT * FROM ' + tableName +
                                             ' order by nachname asc')

                var lastLetter = "";
                var first = "1";

                doctors.splice(0, doctors.length);
                for (var i = 0; i < results.rows.length; i++) {

                    //new first letter
                    if (results.rows.item(i).nachname[0] !== lastLetter)
                        first = results.rows.item(i).nachname[0];
                    else
                        first = "0";

                    doctors.push({"first" : first,
                                     "id" : results.rows.item(i).id,
                                     "vorname" : results.rows.item(i).vorname,
                                     "nachname" : results.rows.item(i).nachname,
                                     "titel" : results.rows.item(i).titel,
                                     "titel_nachstehend" :
                                     results.rows.item(i).titel_nachstehend,
                                     "geschlecht" : results.rows.item(i).geschlecht,
                                     "foto" : results.rows.item(i).foto,
                                     "fachgebiete" : results.rows.item(i).fachgebiete,
                                     "ebene_de" : results.rows.item(i).ebene_de,
                                     "ebene_en" : results.rows.item(i).ebene_en,
                                     "website" : results.rows.item(i).website,
                                     "email" : results.rows.item(i).email,
                                     "telefon" : results.rows.item(i).telefon,
                                     "mobil" : results.rows.item(i).mobil,
                                     "fax" : results.rows.item(i).fax});

                    lastLetter = results.rows.item(i).nachname[0];
                }
            }
            else {  //AreasOfExpertise
                results = sqlExec.executeSql('SELECT * FROM ' + tableName)

                aoe.splice(0, aoe.length);
                for (var j = 0; j < results.rows.length; j++) {
                    aoe.push(results.rows.item(j));
                }
            }

            //console.log(tableName + " entries: " + results.rows.length);
        })
    }

    function dbDropTable(dbTableName) {
        var db = dbGetHandle()
        db.transaction(function (sqlExec) {
            var results = sqlExec.executeSql('DROP TABLE IF EXISTS ' +
                                             dbTableName)

            console.log("DELETED DB " + dbTableName);
        })
    }

    function dbDrop(dbName) {
        var db = dbGetHandle()
        db.transaction(function (sqlExec) {
            var results = sqlExec.executeSql('DROP DATABASE IF EXISTS ' +
                                             dbName)

            console.log("DROP DB " + dbName);
        })
    }

    //convert array to string/text; sqlite does not support arrays
    function convertArrayToString(array) {
        var result = "";
        for(var i = 0; i < (array.length - 1); i++) {
            result += array[i] + ",";
        }

        result += array[array.length - 1];

        return result;
    }
}