/**
 * project.js
 * Copyright (c) 2000 - 2015 Samsung Electronics Co., Ltd. All rights reserved.
 *
 * Contact:
 * DongHee Yang <donghee.yang@samsung.com>
 * Sungmin Kim <sm.art.kim@samsung.com>
 * Jiil Hyoun <jiil.hyoun@samsung.com>
 * Jonghwan Park <iwin100.park@samsung.com>
 * Kitae Kim <kt920.kim@samsung.com>
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 * Contributors:
 * - S-Core Co., Ltd
**/

var async = require('async');
var _ = require('underscore');
var Group = require('./group');
var Job = require('./job');
var Trigger = require('./trigger');
var dibs = require('../../core/dibs');
var DError = require('../../core/exception');
var utils = require('../../lib/utils.js');

/**
 * @module models/project
 */

/**
 * @typedef {object} options
 * @property {string} key - key : value format Hash object for project option
 * @memberOf module:models/project
 */

/**
 * @constructor
 * @param {string} name - project name
 * @param {string} type - project type
 * @param {module:models/project.options} options - project options
 * @param {module:models/project.options} environments - environment
 * @param {string} ptype - base project type
 * @param {string} distName - distribution name
 */
function Project(id, name, type, status, options, environments, triggers, notifications, ptype, distName) {
    /** @type {module:models/project~Project} */
    var self = this;
    /** @type {number} */
    this.id = id || -1;
    /** @type {string} */
    this.name = name;
    /** @type {string} */
    this.type = type;
    /** @type {string} */
    this.status = status;
    /** @type {string} */
    this.distName = distName;
    /** @type {module:models/project.options} */
    this.options = options;
    /** @type {module:models/project.environments} */
    this.environments = environments;
    /** @type {module:models/project.notifications} */
    this.notifications = notifications;
    /** @type {module:models/project.triggers} */
    this.triggers = triggers;

    /**
     * @method checkPrivilege
     * @param {module:models/project.options} opts
     * @param {module:models/job~Job} basJob
     * @returns {module:models/job~Job}
     * @memberOf module:models/project~Project
     */
    this.checkJobPrivilege = function (user, groups, distribution, jobOptions, callback) {
        if (ptype.module.checkJobPrivilege) {
            ptype.module.checkJobPrivilege(user, groups, distribution, self, jobOptions, callback);
        } else {
            var privilege = Job.privilege(user, groups, distribution, self, jobOptions);
            var error = null;
            if (!privilege.user) {
                error = new DError('PRJ004');
            }
            if (!privilege.group) {
                error = new DError('PRJ005');
            }
            if (!privilege.distribution) {
                error = new DError('PRJ006');
            }
            if (!privilege.project) {
                error = new DError('PRJ007');
            }
            callback(error);
        }
    };


    /**
     * @method createJob
     * @param {module:models/project.options} opts
     * @param {module:models/job~Job} basJob
     * @returns {module:models/job~Job}
     * @memberOf module:models/project~Project
     */
    this.createJob = function (user_email, environmentName, parentId, distType, opts, callback) {
        var newOpts = (self.options) ? _.clone(self.options) : {};
        _.extend(newOpts, opts);

        if (ptype) {
            ptype.module.createJob(user_email, self.distName, self.name, self.type, environmentName, parentId, distType, newOpts, callback);
        } else {
            Job.create(user_email, self.distName, distType, self.name, self.type, environmentName, parentId, null, newOpts, callback);
        }
    };
}

/**
 * @callback callback_err_project
 * @param {error|undefined} error
 * @param {module:models/project~Project} project
 * @memberOf module:models/project
 */

/**
 * @function create
 * @param {string} name - project name
 * @param {string} type - project type
 * @param {module:models/project.options} options - project options
 * @param {string} distName - distribution name
 * @param {module:models/project.callback_err_project} callback - callback
 * @memberOf module:models/project
 */

function create(id, name, type, status, options, environments, triggers, notifications, distName, callback) {
    var error;
    var prj = null;

    //default status
    status = status || 'OPEN';
    // verify
    if (!name || !type || !options) {
        error = new DError('PRJ001', {
            name: name,
            type: type,
            options: options
        });
        if (callback !== undefined) {
            callback(error, null);
        }
    } else {
        prj = createInternal(id, name, type, status, options, environments, triggers, notifications, distName, callback);
    }
    return prj;
}
module.exports.create = create;


function createInternal(id, name, type, status, options, environments, triggers, notifications, distName, callback) {
    var error;
    var ptype = null;
    var prj = null;
    if (dibs.projectTypes[type] !== undefined) {
        ptype = dibs.projectTypes[type];
        //verification
        var propertyNames = _.keys(ptype.property);
        var verificate = propertyNames.filter(function (propertyName) {

            if (ptype.property[propertyName].required === undefined ||
                ptype.property[propertyName].required) {

                return (options[propertyName] === undefined);
            } else {
                return false;
            }
        });

        if (verificate.length === 0) {
            prj = new Project(id, name, type, status, options, environments, triggers, notifications, ptype, distName);
            if (callback !== undefined) {
                callback(null, prj);
            }
        } else {
            error = new DError('PRJ002', {
                type: type,
                property: propertyNames.join('/'),
                prjName: name
            });
            if (callback !== undefined) {
                callback(error, null);
            }
        }
    } else {
        prj = new Project(id, name, type, status, options, environments, triggers, notifications, ptype, distName);
        if (callback !== undefined) {
            callback(null, prj);
        }
    }
    return prj;
}


function createAnonymous(id, type, status, options, environments, triggers, notifications, distName, callback) {
    var ptype = null;
    var error;
    var prj = null;

    //default status
    status = status || 'OPEN';
    // verify
    if (!type || !options) {
        error = new DError('PRJ001', {
            type: type,
            options: options
        });
        if (callback !== undefined) {
            callback(error, null);
        }
    } else {
        prj = createInternal(id, null, type, status, options, environments, triggers, notifications, distName, callback);
    }

    return prj;
}
module.exports.createAnonymous = createAnonymous;


/**
 * @callback callback_err_projectid
 * @param {error|undefined} error
 * @param {string} project id - project id
 * @memberOf module:models/project
 */

/**
 * @function insert
 * @param {object} conn - db connection object
 * @param {module:models/project~Project} project - project
 * @param {module:models/project.callback_err_projectid} callback - callback
 * @memberOf module:models/project
 */


module.exports.insert = insert;
function insert(conn, prj, callback) {
    var id = null;
    var distribution;
    async.waterfall([
        function (wcb) {
            conn.query('START TRANSACTION', function (err) {
                wcb(err);
            });
        },
        function (wcb) {
            conn.query('SELECT * FROM distributions ' +
            'WHERE name = ' + utils.DBStr(prj.distName),
                function (err, rows) {
                    if (err) {
                        wcb(err, null);
                    } else if (rows.length !== 1) {
                        wcb(new DError('JOBMODEL001', {
                            dist: prj.distName
                        }), null);
                    } else {
                        distribution = rows[0];
                        wcb(err, rows[0].id);
                    }
                });
        },
        function (distId, wcb) {
            var notificationGroupId = null;
            if (prj.notifications.length > 0) {
                notificationGroupId = prj.notifications[0].groupId;
            }
            conn.query('INSERT INTO projects SET ' +
                (prj.name ? ('name =' + utils.DBStr(prj.name) + ',') : ('name = NULL,')) +
                'type = ' + utils.DBStr(prj.type) + ',' +
                'distribution_id = ' + distId + ',' +
                'notification_group_id = ' + notificationGroupId, function (err, result) {
                    if (err) {
                        wcb(err, null);
                    } else {
                        wcb(err, result.insertId);
                    }
                });
        },
        function (id, wcb) {
            if (_.size(prj.options) !== 0) {
                async.eachSeries(_.keys(prj.options),
                    function (index, mcb) {
                        var strNtype = utils.objectToStringAndType(prj.options[index]);
                        var sql = 'INSERT INTO project_info SET ' +
                            'project_id = ' + id +
                            ', property = ' + utils.DBStr(index) +
                            ', value = ' + utils.DBStr(strNtype.string) +
                            ', type = ' + utils.DBStr(strNtype.type);
                        conn.query(sql, mcb);
                    },
                    function (err, result) {
                        wcb(err, id);
                    });
            } else {
                wcb(null, id);
            }
        },
        function (id, wcb) {
            if (_.size(prj.environments) !== 0) {
                async.map(prj.environments,
                    function (env, mcb) {
                        var envId = utils.getEnvironmentIdFromName(env, prj.type);
                        if (envId) {
                            var sql = 'INSERT INTO project_env ( project_id, environment_id) ' +
                                'SELECT ' + id + ', (SELECT id FROM environments WHERE id = ' + utils.DBStr(envId) + ') FROM DUAL';
                            conn.query(sql, mcb);
                        } else {
                            mcb(new DError('ENV008', {id: envId}), null);
                        }
                    },
                    function (err, result) {
                        wcb(err, id);
                    });
            } else {
                wcb(null, id);
            }
        },
        function (id, wcb) {
            if (_.size(prj.triggers) !== 0) {
                async.eachSeries(prj.triggers,
                    function (trigger, ecb) {
                        trigger.data.projectId = id;

                        Trigger.create(null, trigger.name, trigger.type, trigger.status, trigger.serverType, trigger.data, function (err, triggerObj) {
                            if (err) {
                                return ecb(err);
                            }

                            Trigger.insert(conn, triggerObj, function (err, triggerId) {
                                if (err) {
                                    return ecb(err);
                                }
                                var sql = 'INSERT INTO project_trigger SET project_id = ' + id +
                                    ', trigger_id = ' + triggerId;
                                conn.query(sql, function (err) {
                                    ecb(err);
                                });
                            });
                        });
                    },
                    function (err, result) {
                        wcb(err, id);
                    });
            } else {
                wcb(null, id);
            }
        },
        function (id, wcb) {
            var sql = 'INSERT INTO group_project SET group_id = 1, project_id = ' + id;
            conn.query(sql, function (err) {
                wcb(err, id);
            });
        },
        function (projectId, wcb) {
            conn.query('COMMIT', function (err) {
                if (!err) {
                    prj.i = projectId;
                    wcb(err, projectId);
                } else {
                    wcb(err);
                }
            });
        }
    ], function (err, projectId) {
        if (err) {
            conn.query('ROLLBACK', function () {});
            callback(err);
        }
        callback(err, projectId);
    });
}

/**
 * @callback callback_err_projectid
 * @param {error|undefined} error
 * @param {string} project id - project id
 * @memberOf module:models/project
 */

/**
 * @function update
 * @param {object} conn - db connection object
 * @param {module:models/project~Project} project - project
 * @param {module:models/project.callback_err_projectid} callback - callback
 * @memberOf module:models/project
 */
module.exports.update = function (conn, project, callback) {
    var i;
    conn.query('START TRANSACTION', function (err) {
        if (err) {
            callback(new DError('MODEL001', err), null);
            return;
        }

        // Check condition of key
        if (project.id === undefined) {
            callback(new DError('PRJ009', err), null);
            return;
        }

        // excute transaction sql
        async.waterfall(
            [
                // Find distribution
                function (cb) {
                    var sql = 'SELECT * FROM distributions ' +
                        'WHERE name = ' + utils.DBStr(project.distName);
                    conn.query(sql, function (err, results) {
                        if (err) {
                            cb(new DError('MODEL003', {
                                sql: sql
                            }, err), null);
                            return;
                        }
                        project.distribution_id = results[0].id;
                        cb(err, project);
                    });
                },
                // TABLE: projects
                function (project, cb) {
                    var notiGroupId = null;
                    if (project.notifications && project.notifications.length > 0) {
                        notiGroupId = project.notifications[0].groupId;
                    }
                    var update_list = [];
                    var update_key_list = ['distribution_id', 'name', 'type', 'status'];
                    for (i = 0; i < update_key_list.length; i++) {
                        var key = update_key_list[i];
                        if (project[key] === undefined) {
                            cb(new DError('ENV005', {
                                id: project.id
                            }, err), null);
                            return;
                        }
                        update_list.push(key + ' = ' + utils.DBStr(project[key]));
                    }

                    var sql = 'UPDATE projects SET notification_group_id = ' + notiGroupId + ', ' + update_list.join(', ') + ' WHERE id = ' + project.id;
                    conn.query(sql, function (err, result) {
                        if (err) {
                            cb(new DError('MODEL003', {
                                sql: sql
                            }, err), null);
                            return;
                        }
                        cb(null, project);
                    });
                },
                // TABLE: project_info
                function (project, cb) {
                    var sql;
                    var options = project.options;
                    var insertList = [];
                    var updateList = [];
                    var deleteList = [];

                    async.series([
                        function (scb) {
                            sql = 'SELECT * FROM project_info WHERE project_id = ' + project.id;
                            conn.query(sql, function (err, results) {
                                if (err) {
                                    scb(new DError('MODEL003', {
                                        sql: sql
                                    }, err), null);
                                    return;
                                }
                                var newProperties = _.keys(options);
                                var oldProperties = [];

                                _.each(results, function (row) {
                                    var id = row.id;
                                    var property = row.property;
                                    var value = row.value;
                                    oldProperties.push(property);

                                    if (newProperties.indexOf(property) >= 0) {
                                        if (options[property] !== value) {
                                            updateList.push({
                                                id: id,
                                                property: property,
                                                value: options[property]
                                            });
                                        }
                                    } else {
                                        deleteList.push(id);
                                    }

                                });

                                _.each(newProperties, function (property) {
                                    if (oldProperties.indexOf(property) < 0) {
                                        insertList.push({
                                            property: property,
                                            value: options[property]
                                        });
                                    }
                                });
                                scb(null);
                            });
                        },
                        function (scb) {
                            async.eachSeries(insertList, function (data, ecb) {
                                var strNtype = utils.objectToStringAndType(data.value);
                                var value = strNtype.string;
                                var type = strNtype.type;

                                sql = 'INSERT INTO project_info ( project_id' +
                                    ', property' +
                                    ', value ' +
                                    ', type) ' +
                                    'VALUES (' + project.id +
                                    ', ' + utils.DBStr(data.property) +
                                    ', ' + utils.DBStr(value) +
                                    ', ' + utils.DBStr(type) + ')';
                                conn.query(sql, function (err, result) {
                                    if (err) {
                                        ecb(new DError('MODEL003', {
                                            sql: sql
                                        }, err));
                                        return;
                                    }
                                    ecb(null);
                                });
                            },
                                function (err) {
                                    scb(err);
                                });
                        },
                        function (scb) {
                            async.eachSeries(updateList, function (data, ecb) {
                                var strNtype = utils.objectToStringAndType(data.value);
                                var value = strNtype.string;
                                var type = strNtype.type;

                                sql = 'UPDATE project_info ' +
                                    'SET value = ' + utils.DBStr(value) + ', type = ' + utils.DBStr(type) +
                                    ' WHERE id = ' + data.id;
                                conn.query(sql, function (err, result) {
                                    if (err) {
                                        ecb(new DError('MODEL003', {
                                            sql: sql
                                        }, err));
                                        return;
                                    }
                                    ecb(null);
                                });
                            },
                                function (err) {
                                    scb(err);
                                });
                        },
                        function (scb) {
                            async.eachSeries(deleteList, function (id, ecb) {
                                sql = 'DELETE FROM project_info ' +
                                    ' WHERE id = ' + id;
                                conn.query(sql, function (err, result) {
                                    if (err) {
                                        ecb(new DError('MODEL003', {
                                            sql: sql
                                        }, err));
                                        return;
                                    }
                                    ecb(null);
                                });
                            },
                                function (err) {
                                    scb(err);
                                });
                        }],
                        function (err, results) {
                            if (err) {
                                cb(new DError('MODEL002', err), null);
                                return;
                            }
                            cb(null, project);
                        });
                },
                // TABLE: project_env
                function (project, cb) {
                    var sql;
                    var newEnvironments = _.uniq(project.environments);
                    var insertList = [];
                    var deleteList = [];

                    async.series([
                        function (scb) {
                            sql = 'SELECT project_env.*, environments.name FROM project_env, environments ' +
                                'WHERE project_env.environment_id = environments.id ' +
                                'AND project_id = ' + project.id;
                            conn.query(sql, function (err, results) {
                                if (err) {
                                    scb(new DError('MODEL003', {
                                        sql: sql
                                    }, err), null);
                                    return;
                                }
                                var oldEnvironments = [];

                                _.each(results, function (row) {
                                    var id = row.id;
                                    var envId = row.environment_id;
                                    var envName = row.name;
                                    oldEnvironments.push(envName);

                                    if (newEnvironments.indexOf(envName) < 0) {
                                        deleteList.push(id);
                                    }

                                });

                                _.each(newEnvironments, function (envName) {
                                    if (oldEnvironments.indexOf(envName) < 0) {
                                        insertList.push(envName);
                                    }
                                });
                                scb(null);
                            });
                        },
                        function (scb) {
                            async.eachSeries(insertList, function (envName, ecb) {
                                var envId = utils.getEnvironmentIdFromName(envName, project.type);
                                if (envId) {
                                    var sql = 'INSERT INTO project_env ( project_id, environment_id) ' +
                                        'SELECT ' + project.id + ', ' +
                                        '(SELECT id FROM environments WHERE id = ' + utils.DBStr(envId) + ') ' +
                                        'FROM DUAL';
                                    conn.query(sql, function (err, result) {
                                        if (err) {
                                            ecb(new DError('MODEL003', {
                                                sql: sql
                                            }, err));
                                            return;
                                        }
                                        ecb(null);
                                    });
                                } else {
                                    ecb(new DError('ENV008', {id: envId}));
                                }
                            },
                            function (err) {
                                scb(err);
                            });
                        },
                        function (scb) {
                            async.eachSeries(deleteList, function (id, ecb) {
                                sql = 'DELETE FROM project_env' +
                                    ' WHERE id = ' + id;
                                conn.query(sql, function (err, result) {
                                    if (err) {
                                        ecb(new DError('MODEL003', {
                                            sql: sql
                                        }, err));
                                        return;
                                    }
                                    ecb(null);
                                });
                            },
                                function (err) {
                                    scb(err);
                                });
                        }],
                        function (err) {
                            if (err) {
                                cb(new DError('MODEL002', err), null);
                                return;
                            }
                            cb(null, project);
                        });
                },
                // TABLE: project_trigger
                function (project, cb) {
                    var sql;
                    var newTriggers = project.triggers;
                    var insertList = [];
                    var deleteList = [];
                    var updateList = [];

                    if (!newTriggers) {
                        cb(null, project);
                        return;
                    }

                    async.series([
                        function (scb) {
                            sql = 'SELECT * FROM project_trigger WHERE project_id = ' + project.id;
                            conn.query(sql, function (err, oldTriggers) {
                                if (err) {
                                    scb(new DError('MODEL003', {
                                        sql: sql
                                    }, err), null);
                                    return;
                                }

                                _.each(oldTriggers, function (trigger) {
                                    var findTrigger = _.findWhere(newTriggers, {
                                        id: trigger.trigger_id
                                    });
                                    if (findTrigger !== undefined) {
                                        updateList.push(findTrigger);
                                    } else {
                                        deleteList.push(trigger);
                                    }
                                });

                                _.each(newTriggers, function (trigger) {
                                    if (_.findWhere(oldTriggers, {
                                            trigger_id: trigger.id
                                        }) === undefined) {
                                        insertList.push(trigger);
                                    }
                                });

                                scb(null);
                            });
                        },
                        function (scb) {
                            async.eachSeries(insertList, function (trigger, ecb) {
                                async.waterfall([
                                    function (cb2) {
                                        trigger.data.projectId = project.id;
                                        Trigger.create(null, trigger.name, trigger.type, trigger.status, trigger.serverType, trigger.data, cb2);
                                    },
                                    function (newTrigger, cb2) {
                                        Trigger.insert(conn, newTrigger, cb2);
                                    },
                                    function (triggerId, cb2) {
                                        var sql = 'INSERT INTO project_trigger SET project_id = ' + project.id +
                                            ', trigger_id = ' + triggerId;
                                        conn.query(sql, function (err) {
                                            cb2(err);
                                        });
                                    }
                                ], function (err) {
                                    ecb(err);
                                });
                            },
                                function (err) {
                                    scb(err);
                                });
                        },
                        function (scb) {
                            async.eachSeries(updateList, function (trigger, ecb) {
                                Trigger.update(conn, trigger, ecb);
                            },
                                function (err) {
                                    scb(err);
                                });
                        },
                        function (scb) {
                            async.eachSeries(deleteList, function (trigger, ecb) {
                                var sql = 'DELETE FROM project_trigger WHERE id = ' + trigger.id;
                                conn.query(sql, function (err, result) {
                                    if (err) {
                                        return ecb(new DError('MODEL003', {
                                            sql: sql
                                        }, err));
                                    }
                                    Trigger.delete(conn, trigger.trigger_id, ecb);
                                });
                            },
                                function (err) {
                                    scb(err);
                                });
                        }
                    ],
                        function (err, results) {
                            if (err) {
                                cb(new DError('MODEL002', err), null);
                            } else {
                                cb(null, project);
                            }
                        });
                },
                // group_project
                function (project, cb) {
                    if (!project.groups) {
                        cb(null, project);
                        return;
                    }

                    var sql;
                    var newGroups = _.uniq(project.groups);
                    var insertList = [];
                    var deleteList = [];

                    async.series([
                        function (scb) {
                            sql = 'SELECT group_project.id, groups.name FROM groups, group_project ' +
                                'WHERE groups.id = group_project.group_id AND group_project.project_id = ' + project.id;
                            conn.query(sql, function (err, results) {
                                if (err) {
                                    scb(new DError('MODEL003', {
                                        sql: sql
                                    }, err), null);
                                    return;
                                }
                                var oldGroups = [];

                                _.each(results, function (row) {
                                    var id = row.id;
                                    var groupName = row.name;
                                    oldGroups.push(groupName);

                                    if (newGroups.indexOf(groupName) < 0) {
                                        deleteList.push(id);
                                    }

                                });

                                _.each(newGroups, function (groupName) {
                                    if (oldGroups.indexOf(groupName) < 0) {
                                        insertList.push(groupName);
                                    }
                                });
                                scb(null);
                            });
                        },
                        function (scb) {
                            async.eachSeries(insertList, function (groupName, ecb) {
                                var sql = 'INSERT INTO group_project ( project_id, group_id) ' +
                                    'SELECT ' + project.id + ', ' +
                                    '(SELECT id FROM groups WHERE name = ' + utils.DBStr(groupName) + ') ' +
                                    'FROM DUAL';
                                conn.query(sql, function (err, result) {
                                    if (err) {
                                        ecb(new DError('MODEL003', {
                                            sql: sql
                                        }, err));
                                        return;
                                    }
                                    ecb(null);
                                });
                            },
                                function (err) {
                                    scb(err);
                                });
                        },
                        function (scb) {
                            async.eachSeries(deleteList, function (groupProjectId, ecb) {
                                sql = 'DELETE FROM group_project ' +
                                    ' WHERE id = ' + groupProjectId;
                                conn.query(sql, function (err, result) {
                                    if (err) {
                                        ecb(new DError('MODEL003', {
                                            sql: sql
                                        }, err));
                                        return;
                                    }
                                    ecb(null);
                                });
                            },
                                function (err) {
                                    scb(err);
                                });
                        }],
                        function (err) {
                            if (err) {
                                cb(new DError('MODEL002', err), null);
                                return;
                            }
                            cb(null, project);
                        });
                },
                // commit sql
                function (project, cb) {
                    conn.query('COMMIT', function (err) {
                        if (err) {
                            cb(new DError('MODEL002', err), null);
                            return;
                        }
                        cb(null, project);
                    });
                }
            ],
            function (err, project) {
                if (err) {
                    conn.query('ROLLBACK', function () {});
                    callback(new DError('ENV006', err), null);
                    return;
                }
                callback(err, project);
            });
    });
};

/**
 * @function select
 * @param {object} conn - db connection object
 * @param {module:models/user.jobQuerySql} condition - condition
 * @param {module:lib/utils.callback_error} callback - callback
 * @memberOf module:models/user
 */

module.exports.select = select;
function select(conn, condition, callback) {
    var i;
    var where = '';
    var whereList = [];
    var order = '';

    var availableKeyList = _.select(['id', 'distribution_id', 'name', 'type', 'status'],
        function (key) {
            return (condition[key] !== undefined);
        });

    whereList = _.map(availableKeyList, function (key) {
        var query = '';
        var value = condition[key];
        if (value === 'NULL') {
            query = ('projects.' + key + ' is NULL ');
        } else if ((new RegExp('^[=><]+')).test(value)) {
            query = ('projects.' + key + ' ' + value + ' ');
        } else if (_.isArray(value)) {
            query = ('projects.' + key + ' in (' + _.map(value, function (val) {
                    return utils.DBStr(val);
                }).join(',') + ') ');
        } else {
            query = ('projects.' + key + ' = ' + utils.DBStr(condition[key]));
        }
        return query;
    });

    if (condition.distName !== undefined) {
        whereList.push('distributions.name = ' + utils.DBStr(condition.distName));
    }
    if (condition.distType !== undefined) {
        whereList.push('distributions.type = ' + utils.DBStr(condition.distType));
    }

    // order by info
    if (condition.order !== undefined) {
        order = ' ORDER BY ' + condition.order;

        if (condition.arrange !== undefined) {
            order = order + ' ' + condition.arrange;
        }
    }

    // limit info
    var limit = '';
    if (condition.count !== undefined) {
        if (condition.offset !== undefined) {
            limit += (' LIMIT ' + condition.offset + ' , ' + condition.count);
        } else {
            limit += (' LIMIT ' + condition.count);
        }
    }

    if (whereList.length > 0) {
        where = ' WHERE projects.distribution_id = distributions.id AND ' +
            whereList.join(' AND ');
    } else {
        where = ' WHERE projects.distribution_id = distributions.id ';
    }

    var sql = 'SELECT projects.id' +
        ', projects.distribution_id' +
        ', projects.name' +
        ', projects.type' +
        ', projects.status' +
        ', distributions.name AS distName' +
        ', projects.notification_group_id' +
        ' FROM projects, distributions ' +
        where + order + limit;

    conn.query(sql, function (err, projects) {
        if (err) {
            callback(new DError('MODEL003', {
                sql: sql
            }, err));
            return;
        }

        async.map(projects, function (project, mcb) {
            var options = {};
            var environments = [];
            var groups = [];
            var notifications = [];
            var triggers = [];
            async.series([
                // project_info
                function (scb) {
                    var sql = 'SELECT * FROM project_info WHERE project_id =' + project.id;
                    conn.query(sql, function (err, project_info) {
                        if (err) {
                            scb(new DError('MODEL003', {
                                sql: sql
                            }, err));
                            return;
                        }
                        for (i = 0; i < project_info.length; i++) {
                            options[project_info[i].property] = utils.stringAndTypeToObj(project_info[i].value, project_info[i].type);
                        }
                        scb(null);
                    });
                },
                // project_env
                function (scb) {
                    var sql = 'SELECT environments.id, environments.name, environments.description ' +
                        'FROM environments, project_env ' +
                        'WHERE environments.id = project_env.environment_id ' +
                        'AND project_env.project_id = ' + utils.DBStr(project.id);
                    conn.query(sql, function (err, results) {
                        if (err) {
                            scb(new DError('MODEL003', {
                                sql: sql
                            }, err));
                            return;
                        }
                        for (var i = 0; i < results.length; i++) {
                            environments.push(results[i].name);
                        }
                        scb(null);
                    });
                },
                // project_trigger
                function (scb) {
                    async.waterfall([
                        function (cb1) {
                            var sql = 'SELECT trigger_id FROM project_trigger' +
                                ' WHERE project_id = ' + project.id;
                            conn.query(sql, function (err, results) {
                                cb1(err, results);
                            });
                        },
                        function (data, cb1) {
                            async.eachSeries(data, function (d, cb2) {
                                Trigger.select(conn, {
                                    id: d.trigger_id
                                }, function (err, results) {
                                    if (err) {
                                        cb2(err);
                                    } else if (!results || results.length === 0) {
                                        cb2(new DError('PRJ010', {
                                            id: d.trigger_id
                                        }, err));
                                    } else {
                                        triggers.push(results[0]);
                                        cb2(null);
                                    }
                                });
                            }, function (err) {
                                cb1(err);
                            });
                        }
                    ],
                        function (err) {
                            scb(null);
                        });
                },
                // group_project
                function (scb) {
                    var sql = 'SELECT groups.id, groups.name, groups.parent_id, groups.type, groups.status ' +
                        '  FROM groups, group_project' +
                        ' WHERE groups.id = group_project.group_id' +
                        '   AND group_project.project_id = ' + utils.DBStr(project.id);
                    conn.query(sql, function (err, results) {
                        if (err) {
                            scb(new DError('MODEL003', {
                                sql: sql
                            }, err));
                            return;
                        }
                        for (var i = 0; i < results.length; i++) {
                            groups.push(results[i].name);
                        }
                        scb(null);
                    });
                },
                // project notificatioin_group_id
                function (scb) {
                    if (project.notification_group_id) {
                        sql = 'SELECT * FROM notifications WHERE notification_group_id =' + project.notification_group_id;
                        conn.query(sql, function (err, notificationArray) {
                            if (err) {
                                scb(new DError('MODEL003', {
                                    sql: sql
                                }, err));
                                return;
                            }

                            for (i = 0; i < notificationArray.length; i++) {
                                notifications.push({
                                    id: notificationArray[i].id,
                                    groupId: notificationArray[i].notification_group_id,
                                    event: notificationArray[i].event,
                                    method: notificationArray[i].method,
                                    targetType: notificationArray[i].target_type,
                                    targetUserId: notificationArray[i].target_user_id,
                                    targetGroupId: notificationArray[i].target_group_id,
                                    targetData: notificationArray[i].target_data
                                });
                            }
                            scb(null);
                        });
                    } else {
                        scb(null);
                    }
                }
            ],
                function (err) {
                    create(project.id, project.name, project.type, project.status, options, environments, triggers, notifications, project.distName, function (err, projectObj) {
                        projectObj.groups = groups;
                        mcb(err, projectObj);
                    });
                });
        },
            function (err, results) {
                if (err) {
                    callback(new DError('MODEL002', {
                        condition: condition
                    }, err));
                    return;
                }
                callback(null, results);
            });
    });
}
/**
 * @callback callback_err_projectList
 * @param {error|undefined} error
 * @param {Array.<module:models/project~Project>} projectList
 * @memberOf module:models/project
 */

/**
 * @function selectAll
 * @param {object} conn - db connection object
 * @param {module:models/project.callback_err_projectList} callback - callback
 * @memberOf module:models/project
 */
module.exports.selectAll = selectAll;
function selectAll(conn, callback) {
    async.waterfall([
        function (cb1) {
            conn.query('SELECT projects.id, projects.name, projects.type, distributions.name as distName ' +
            'FROM projects,distributions ' +
            'WHERE projects.distribution_id = distributions.id ',
                function (err, projects) {
                    cb1(err, projects);
                });
        },
        function (projects, cb1) {
            //Get project_info
            async.map(projects, function (prj, cb2) {
                conn.query('SELECT project_info.property,project_info.value,project_info.type ' +
                    'FROM project_info ' +
                    'WHERE project_id = ' + utils.DBStr(prj.id), function (err, optionList) {
                        if (!err) {
                            var options = {};
                            for (var idx in optionList) {
                                options[optionList[idx].property] = utils.stringAndTypeToObj(optionList[idx].value, optionList[idx].type);
                            }
                            create(prj.id, prj.name, prj.type, prj.status, options, [], [], [], prj.distName, function (err1, newPrj) {
                                cb2(err1, newPrj);
                            });
                        } else {
                            cb2(err, null);
                        }
                    });
            },
                function (error, projects) {
                    cb1(error, projects);
                });
        },
        function (projects, cb1) {
            //Get project_env
            async.map(projects,
                function (prj, cb2) {
                    conn.query('SELECT environments.id, environments.name, environments.description ' +
                    'FROM environments, project_env ' +
                    'WHERE environments.id = project_env.environment_id ' +
                    'AND project_env.project_id = ' + utils.DBStr(prj.id),
                        function (err, data) {
                            if (!err) {
                                var environments = [];
                                for (var i = 0; i < data.length; i++) {
                                    environments.push(data[i].name);
                                }
                                create(prj.id, prj.name, prj.type, prj.status, prj.options, environments, [], [], prj.distName, function (err1, newPrj) {
                                    cb2(null, newPrj);
                                });
                            } else {
                                cb2(err, null);
                            }
                        });
                },
                function (error, projects) {
                    cb1(error, projects);
                });
        },
        function (projects, cb1) {
            // get project_trigger
            async.map(projects, function (prj, cb2) {
                var sql = 'SELECT trigger_id FROM project_trigger WHERE project_id = ' + prj.id;
                var triggers = [];

                conn.query(sql, function (err, results) {
                    async.eachSeries(results,
                        function (result, cb3) {
                            Trigger.select(conn, {
                                id: result.trigger_id
                            }, function (err1, trigger) {
                                if (err1) {
                                    cb3(err1);
                                } else if (!trigger || trigger.length === 0) {
                                    cb3(new DError('PRJ010', {
                                        id: result.trigger_id
                                    }, err1));
                                } else {
                                    triggers.push(trigger[0]);
                                    cb3(null);
                                }
                            });
                        }, function (err) {
                            if (err) {
                                cb2(err);
                            } else {
                                create(prj.id, prj.name, prj.type, prj.status, prj.options,
                                    prj.envrionments, triggers, [], prj.distName, cb2);
                            }
                        });
                });
            },
                function (error, projects) {
                    cb1(error, projects);
                });
        },
        function (projects, cb1) {
            //Get notification_info
            async.map(projects,
                function (prj, cb2) {
                    conn.query('SELECT * ' +
                    'FROM notifications' +
                    ' where notification_group_id in (SELECT notification_group_id FROM projects where id = ' + utils.DBStr(prj.id) + ');',
                        function (err, data) {
                            if (!err) {
                                var notifications = [];
                                for (var i = 0; i < data.length; i++) {
                                    notifications.push({
                                        id: data[i].id,
                                        groupId: data[i].notification_group_id,
                                        event: data[i].event,
                                        method: data[i].method,
                                        targetType: data[i].target_type,
                                        targetUserId: data[i].target_user_id,
                                        targetGroupId: data[i].target_group_id,
                                        targetData: data[i].target_data
                                    });
                                }
                                create(prj.id, prj.name, prj.type, prj.status, prj.options, prj.environments, prj.triggers, notifications, prj.distName, function (err1, newPrj) {
                                    cb2(null, newPrj);
                                });
                            } else {
                                cb2(err, null);
                            }
                        });
                },
                function (error, projects) {
                    cb1(error, projects);
                });
        }
    ],
        function (err, result) {
            callback(err, result);
        });
}

/**
 * @function selectAllWithLastJob
 * @param {object} conn - db connection object
 * @param {module:models/project.callback_err_projectList} callback - callback
 * @memberOf module:models/project
 */
module.exports.selectAllWithTreeModel = function (conn, cond, callback) {
    async.waterfall([
        function (cb1) {
            select(conn, cond, cb1);
        },
        function (projects, cb1) {
            // skip if there is no projects
            if (projects.length === 0) {
                cb1(null, []); return;
            }

            //Get project_info
            async.map(projects,
                function (project, cb2) {
                    conn.query('SELECT environments.id, environments.name, environments.description ' +
                    'FROM environments, project_env ' +
                    'WHERE environments.id = project_env.environment_id ' +
                    'AND project_env.project_id = ' + utils.DBStr(project.id),
                        function (err, data) {
                            if (!err) {
                                // skip if there is no multiple environments
                                if (data.length < 2) {
                                    cb2(null, project); return;
                                }

                                var children = [];
                                for (var i = 0; i < data.length; i++) {
                                    var child = {};
                                    child.id = project.id + '-' + data[i].id;
                                    child.name = project.name;
                                    child.type = project.type;
                                    child.distName = project.distName;
                                    child.environments = data[i].name;
                                    children.push(child);
                                }

                                project.children = children;
                                cb2(null, project);
                            } else {
                                cb2(err, null);
                            }
                        });
                },
                function (error, projects) {
                    cb1(error, projects);
                });
        }
    ],
        function (err, result) {
            callback(err, result);
        });
};


module.exports.delete = function (conn, condition, callback) {
    conn.query('START TRANSACTION', function (err) {
        if (err) {
            callback(new DError('MODEL001', err), null);
            return;
        }
        async.series([
            function (cb) {
                deleteInternal(conn, condition, cb);
            }
        ],
            function (err, result) {
                if (err) {
                    conn.query('ROLLBACK', function () {});
                    callback(new DError('PRJ003', {
                        condition: condition
                    }, err));
                } else {
                    conn.query('COMMIT', function (err) {
                        if (err) {
                            callback(new DError('MODEL002', err), null);
                        } else {
                            callback(err, result);
                        }
                    });
                }
            });
    });
};

module.exports.deleteInternal = deleteInternal;
function deleteInternal(conn, condition, callback) {
    var projectTriggers = [];

    // Check condition of key
    if (!condition.id) {
        callback(new DError('PRJ003', {
            condition: condition
        }, null));
        return;
    }
    select(conn, condition, function (err, items) {
        if (err) {
            callback(err, null);
            return;
        }
        if (items.length === 0) {
            callback(new DError('PRJ004', {
                condition: condition
            }), null);
            return;
        }
        async.eachSeries(items, function (item, ecb) {
            async.series(
                [
                    function (cb) {
                        conn.query('START TRANSACTION', function (err) {
                            cb(err);
                        });
                    },
                    // TABLE: project_info
                    function (cb) {
                        var sql = 'DELETE FROM project_info WHERE project_id = ' + item.id;
                        conn.query(sql, function (err, result) {
                            if (err) {
                                cb(new DError('MODEL003', {
                                    sql: sql
                                }, err));
                                return;
                            }
                            cb(null);
                        });
                    },
                    // TABLE: group_project
                    function (cb) {
                        var sql = 'DELETE FROM group_project WHERE project_id = ' + item.id;
                        conn.query(sql, function (err, result) {
                            if (err) {
                                cb(new DError('MODEL003', {
                                    sql: sql
                                }, err));
                                return;
                            }
                            cb(null);
                        });
                    },
                    // TABLE: project_env
                    function (cb) {
                        var sql = 'DELETE FROM project_env WHERE project_id = ' + item.id;
                        conn.query(sql, function (err, result) {
                            if (err) {
                                cb(new DError('MODEL003', {
                                    sql: sql
                                }, err));
                                return;
                            }
                            cb(null);
                        });
                    },
                    // TABLE: get triggers
                    function (cb) {
                        var sql = 'SELECT * FROM project_trigger WHERE project_id = ' + item.id;
                        conn.query(sql, function (err, results) {
                            if (err) {
                                cb(new DError('MODEL003', {
                                    sql: sql
                                }, err));
                                return;
                            }
                            projectTriggers = results;
                            cb(null);
                        });
                    },
                    // TABLE: project_trigger
                    function (cb) {
                        var sql = 'DELETE FROM project_trigger WHERE project_id = ' + item.id;
                        conn.query(sql, function (err, result) {
                            if (err) {
                                cb(new DError('MODEL003', {
                                    sql: sql
                                }, err));
                                return;
                            }
                            cb(null);
                        });
                    },
                    // TABLE: delete triggers
                    function (cb) {
                        async.eachSeries(projectTriggers, function (projectTrigger, cb2) {
                            Trigger.delete(conn, projectTrigger.trigger_id, cb2);
                        }, cb);
                    },
                    // TABLE: projects
                    function (cb) {
                        var sql = 'DELETE FROM projects WHERE id = ' + item.id;
                        conn.query(sql, function (err, result) {
                            if (err) {
                                cb(new DError('MODEL003', {
                                    sql: sql
                                }));
                                return;
                            }
                            cb(null);
                        });
                    },
                    function (cb) {
                        conn.query('COMMIT', cb);
                    }
                ],
                function (err) {
                    if (err) {
                        conn.query('ROLLBACK', function () {});
                    }
                    ecb(err);
                });
        },
            function (err) {
                callback(err, projectTriggers);
            });

    });
}

module.exports.clone = clone;
function clone(conn, sourceDistName, sourcePrjName, targetDistName, targetPrjName, callback) {
    async.waterfall([
        function (cb1) {
            select(conn, {
                distName: sourceDistName,
                name: sourcePrjName
            }, function (err, result) {
                if (err) {
                    cb1(err, null);
                } else {
                    if (result.length === 0) {
                        cb1(new DError('PRJ008', {
                            condition: {
                                distName: sourceDistName,
                                name: sourcePrjName
                            }
                        }));
                    } else {
                        cb1(null, result[0]);
                    }
                }
            });
        },
        function (sourcePrj, cb1) {
            var targetPrj = _.clone(sourcePrj);
            targetPrj.distName = targetDistName;
            targetPrj.name = targetPrjName;

            insert(conn, targetPrj, function (err, id) {
                cb1(err, sourcePrj, id);
            });
        },
        function (sourcePrj, projectId, cb1) {
            var groupList = [];

            async.eachSeries(sourcePrj.groups,
                function (groupName, cb2) {
                    Group.select(conn, {
                        name: groupName
                    }, function (err, results) {
                        if (err) {
                            cb2(err);
                        } else {
                            groupList.push(results[0]);
                            cb2(null);
                        }
                    });
                },
                function (err) {
                    cb1(err, groupList, projectId);
                });
        },
        function (groupList, projectId, cb1) {
            var groupIdList = _.pluck(groupList, 'id');
            //Filter admin, already inserted admin id
            groupIdList = _.without(groupIdList, 1);

            async.eachSeries(groupIdList,
                function (groupId, cb2) {
                    var sql = 'INSERT INTO group_project (group_id, project_id) ' +
                        'SELECT ' + groupId + ', ' + projectId + ' FROM DUAL';
                    conn.query(sql, function (err, result) {
                        cb2(err);
                    });
                },
                function (err) {
                    cb1(err);
                });
        }
    ], function (err) {
        callback(err);
    });
}
