var tables = @tables

//#import @services/sqlstring.jsx
//#import dbutil.js

function escapeSQL(fieldType, value){
}

function getValue(fieldName, obj, parentKey){
    if(parentKey){
        fieldName = parentKey + "__" + fieldName;
    }
    var fieldPath = fieldName.split("__");
    var curData = obj;
    for(var i=0; i<fieldPath.length; i++){
        var key = fieldPath[i];
        curData = curData[key];
        if(!curData){
            return null;
        }
    }
    return curData;
}

function escapeField(fd,value){
    if (value == null) {
        return null
    }
    switch (fd.fieldType) {
        case 'string':
            return SqlString.escape(value)
        case 'number':
            if (isNaN(value)) {
                return null
            }
            else {
                value = Number(value)
                return value
            }
        case 'date':
            return SqlString.dateToString(value)
        case 'choice':
            return SqlString.escape(value)
        default:
            return SqlString.escape(value)
    }
}


function insertTable(table, obj){
    var tableName = table.tableName;
    var sqls = [];
    var parentKey = null;
    if(tableName.indexOf("__")>0){
        parentKey = tableName.substring(tableName.indexOf("__")+2);
    }
    var _v = obj._v;
    if(parentKey){
        var curObj= getValue(parentKey,obj);
        var idx = parentKey.lastIndexOf("__");
        var parentId = null;
        if(idx>-1){
            var ppkey = parentKey.substring(0,idx);
            var ppobj = getValue(ppkey,obj);
            parentId = ppobj.id;
        }
        else{
            parentId = obj.id;
        }
        if(curObj && curObj.length>0){
            for(var i=0; i<curObj.length; i++){
                var sql = "replace into " + table.tableName  + " (";
                var eobj = curObj[i];
                var fieldDescriptions = table.fieldDescriptions;
                var fieldNames = fieldDescriptions.map(function(fd){return fd.fieldName});
                fieldNames.push("parentId");
                fieldNames.push("id");
                fieldNames.push("deleted");
                fieldNames.push("_v");
                sql = sql + fieldNames.join(",") + ") values(";
                for(var j=0; j<table.fieldDescriptions.length; j++){
                    var fd = table.fieldDescriptions[j];
                    if(j>0){
                        sql = sql + ","
                    }
                    sql = sql + escapeField(fd,getValue(fd.fieldName,eobj));
                }
                sql = sql + ", " + SqlString.escape(parentId);
                sql = sql + ", " + SqlString.escape(eobj.id);
                sql = sql + ",0"
                sql = sql + "," + SqlString.escape(_v);
                sql = sql + ");";
                sqls.push(sql);
            }

        }
        return sqls;
    }
    else{
        var sql = "replace into " + table.tableName  + " (";
        var fieldDescriptions = table.fieldDescriptions;
        var fieldNames = fieldDescriptions.map(function(fd){return fd.fieldName});
        fieldNames.push("deleted");
        fieldNames.push("_v");
        sql = sql + fieldNames.join(",") + ") values(";
        for(var i=0; i<table.fieldDescriptions.length; i++){
            var fd = table.fieldDescriptions[i];
            if(i>0){
                sql = sql + ","
            }
            sql = sql + escapeField(fd,getValue(fd.fieldName,obj,parentKey));
        }
        sql = sql + ",0"
        sql = sql + "," +  SqlString.escape(_v);
        sql = sql + ");";
        return [sql];
    }

}

function insertObject(obj){
    var sqls = [];
    for(var i=0; i<tables.length; i++){
        var table = tables[i];
        var sqlarr = insertTable(table, obj);
        sqls = sqls.concat(sqlarr);
    }
    DbUtil.execSQLs(sqls);
    return sqls;
}

function updateTable(table,obj){
    var tableName = table.tableName;
    var sqls = [];
    var parentKey = null;

    var _v = obj._v;
    if(tableName.indexOf("__")>0){
        parentKey = tableName.substring(tableName.indexOf("__")+2);
    }
    if(parentKey){
        var curObj= getValue(parentKey,obj);
        var idx = parentKey.lastIndexOf("__");
        var parentId = null;
        if(idx>-1){
            var ppkey = parentKey.substring(0,idx);
            var ppobj = getValue(ppkey,obj);
            parentId = ppobj.id;
        }
        else{
            parentId = obj.id;
        }
        //首先需要删除旧数据
        var sql = 'delete from ' + table.tableName + ' where parentId=' + SqlString.escape(parentId) + ';' ;
        sqls.push(sql);
        if(curObj && curObj.length>0){
            for(var i=0; i<curObj.length; i++){
                var sql = "replace into " + table.tableName  + " (";
                var eobj = curObj[i];
                var fieldDescriptions = table.fieldDescriptions;
                var fieldNames = fieldDescriptions.map(function(fd){return fd.fieldName});
                fieldNames.push("parentId");
                fieldNames.push("id");
                fieldNames.push("_v");

                sql = sql + fieldNames.join(",") + ") values(";
                for(var j=0; j<table.fieldDescriptions.length; j++){
                    var fd = table.fieldDescriptions[j];
                    if(j>0){
                        sql = sql + ","
                    }
                    sql = sql + escapeField(fd,getValue(fd.fieldName,eobj));
                }
                sql = sql + ", " + SqlString.escape(parentId);
                sql = sql + ", " + SqlString.escape(eobj.id);
                sql = sql + "," +  SqlString.escape(_v);
                sql = sql + ");";
                sqls.push(sql);
            }

        }
        return sqls;
    }
    else{
        // var sql = "update " + table.tableName  + " ";
        // for(var i=0; i<table.fieldDescriptions.length; i++){
        //     var fd = table.fieldDescriptions[i];
        //     if(i>0){
        //         sql = sql + ","
        //     }
        //     sql = sql + "set " + fd.fieldName + "=" + escapeField(fd,getValue(fd.fieldName,obj,parentKey));
        // }
        // sql = sql + " where id=" + SqlString.escape(getValue("id", obj , parentKey));
        // return [sql];

        var sql = "replace into " + table.tableName  + " (";
        var fieldDescriptions = table.fieldDescriptions;
        var fieldNames = fieldDescriptions.map(function(fd){return fd.fieldName});
        fieldNames.push("_v");
        sql = sql + fieldNames.join(",") + ") values(";
        for(var i=0; i<table.fieldDescriptions.length; i++){
            var fd = table.fieldDescriptions[i];
            if(i>0){
                sql = sql + ","
            }
            sql = sql + escapeField(fd,getValue(fd.fieldName,obj,parentKey));
        }
        sql = "," +  SqlString.escape(_v);
        sql = sql + ");";
        return [sql];
    }

}

function updateObject(obj){
    var sqls = [];
    for(var i=0; i<tables.length; i++){
        var table = tables[i];
        var sqlarr = updateTable(table, obj);
        sqls = sqls.concat(sqlarr);
    }
    DbUtil.execSQLs(sqls);
    return sqls;
}

function deleteTable(table,obj){
    var tableName = table.tableName;
    var parentKey = null;
    if(tableName.indexOf("__")>0){
        parentKey = tableName.substring(tableName.indexOf("__")+2);
    }

    if(!parentKey){
        var sql = "update  " + tableName + " set deleted=true where id=" + SqlString.escape(obj.id);
        return sql;
    }
    else{
        var sql = "update  " + tableName  + " set deleted=true where parentId=" + SqlString.escape(obj.id);
        return sql;
    }
}

function deleteObject(obj){
    var sqls = [];
    for(var i=0; i<tables.length; i++){
        var table = tables[i];
        var sql = deleteTable(table, obj);
        sqls.push(sql);
    }
    DbUtil.execSQLs(sqls);
    return sqls;
}