/**
 * approval.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 DError = require('../../core/exception.js');
var utils = require('../../lib/utils.js');

/**
 * @module models/approval
 */

/**
 * @callback callback_err_approvalid
 * @param {error|undefined} error
 * @param {string} approval_id - approval id
 * @memberOf module:models/approval
 */

/**
 *
 * @constructor
 */

function Approval(email, subject, contents, summary, approvalRoutes, type) {
    /** @type {number} */
    this.id = null;
    /** @type {string} */
    this.userId = null;
    /** @type {string} */
    this.email = email;
    /** @type {string} */
    this.subject = subject;
    /** @type {string} */
    this.contents = contents;
    /** @type {string} */
    this.summary = summary;
    /** @type {string} */
    this.submissionDate = new Date();
    /**
     * submission status: (SUBMITTED/PROCESSING/APPROVED/REJECTED)
     * @type {string}
     */
    this.submissionStatus = 'SUBMITTED';
    /**
     * @type list of route
     * route : {id, approvalId, email, order, status, date, message}
     */
    this.approvalRoutes = approvalRoutes;
    /** @type {string}*/
    this.type = type;
}

/**
 * @function create
 * @param {string} email
 * @param {string} subject
 * @param {string} contents
 * @param {string} summary
 * @param {list} approvalRoutes
 * @param {string} type
 * @returns {module:models/approval~Approval}
 * @memberOf module:models/approval
 */

function create(email, subject, contents, summary, approvalRoutes, type) {
    return new Approval(email, subject, contents, summary, approvalRoutes, type);
}
module.exports.create = create;


module.exports.insert = function (connection, approval, callback) {
    var sql;
    var approvalObj = _.clone(approval);
    // excute transaction sql
    async.waterfall([
        function (cb) {
            connection.query('START TRANSACTION', function (err) {
                if (err) {
                    cb(new DError('MODEL001', err));
                } else {
                    cb(null);
                }
            });
        },
        function (cb) {
            // get user id
            sql = 'SELECT * FROM users WHERE email = ' + utils.DBStr(approvalObj.email);
            connection.query(sql, function (err, userInformations) {
                if (err) {
                    cb(new DError('MODEL003', {
                        sql: sql
                    }, err), null);
                } else if (userInformations.length === 0) {
                    cb(new DError('APPR001', {
                        email: approvalObj.email
                    }), null);
                } else {
                    cb(err, userInformations[0].id);
                }
            });
        },
        function (userId, cb) {
            // insert approval
            sql = 'INSERT INTO approvals ( user_id' +
                ', subject' +
                ', contents' +
                ', summary' +
                ', submission_date' +
                ', submission_status' +
                ', approval_type ) ' +
                'VALUES (' + userId +
                ', ' + utils.DBStr(approvalObj.subject) +
                ', ' + utils.DBStr(approvalObj.contents) +
                ', ' + utils.DBStr(approvalObj.summary) +
                ', ' + JSON.stringify(approvalObj.submissionDate) +
                ', ' + utils.DBStr(approvalObj.submissionStatus) +
                ', ' + utils.DBStr(approvalObj.type) + ')';
            connection.query(sql, function (err, result) {
                if (err) {
                    cb(new DError('MODEL003', {
                        sql: sql
                    }, err), null);
                } else {
                    approvalObj.id = result.insertId;
                    cb(null);
                }
            });
        },
        function (cb) {
            // insert approval routes
            async.each(approvalObj.approvalRoutes, function (route, cb1) {
                route.approvalId = approvalObj.id;
                route.status = (route.type == 'DRAFT') ? 'SUBMITTED' : 'WAIT';
                route.date = null;
                route.message = null;
                // get userId
                var sql1 = 'SELECT * FROM users WHERE email = ' + utils.DBStr(route.email);
                connection.query(sql1, function (err, userInformations) {
                    if (err) {
                        cb1(new DError('MODEL003', {
                            sql: sql1
                        }, err), null);
                    } else if (userInformations.length === 0) {
                        cb(new DError('APPR001', {
                            email: approvalObj.email
                        }), null);
                    } else {
                        // insert approval route
                        var sql3 = 'INSERT INTO approval_route ( approval_id' +
                            ', user_id' +
                            ', approval_order' +
                            ', approval_type' +
                            ', approval_status' +
                            ', approval_date' +
                            ', approval_message ) ' +
                            'VALUES (' + approvalObj.id +
                            ', ' + userInformations[0].id +
                            ', ' + route.order +
                            ', ' + utils.DBStr(route.type) +
                            ', ' + utils.DBStr(route.status) +
                            ', ' + utils.DBStr(route.date) +
                            ', ' + route.message + ')';
                        connection.query(sql3, function (err) {
                            if (err) {
                                cb1(new DError('MODEL003', {
                                    sql: sql3
                                }, err), null);
                            } else {
                                cb1(null);
                            }
                        });
                    }
                });
            }, cb);
        },
        function (cb) {
            connection.query('COMMIT', function (err) {
                if (err) {
                    cb(new DError('MODEL002', err));
                } else {
                    cb(err);
                }
            });
        }
    ],
        function (err1) {
            if (err1) {
                connection.query('ROLLBACK', function () {});
                callback(err1, null);
            } else {
                callback(err1, approvalObj);
            }
        });
};


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

    var includeRoutes = false;
    // chekck includeRoutes option
    if (_.has(condition, 'includeRoutes') && (true === _.property('includeRoutes')(condition))) {
        // includeRoutes : true
        includeRoutes = true;
    }

    var keyList = ['id', 'email', 'subject', 'contents', 'submissionStatus', 'submissionDate', 'summary', 'type'];
    for (var i = 0; i < keyList.length; i++) {
        var key = keyList[i];
        if (condition[key] !== undefined) {
            if (key === 'email' && includeRoutes === false) {
                whereList.push('users.' + key + ' = ' + utils.DBStr(condition[key]));
            } else if (key === 'email' && includeRoutes === true) {
                whereList.push('approvals.id IN (' +
                    'SELECT DISTINCT approval_id ' +
                    'FROM approval_route, users ' +
                    'WHERE approval_route.user_id = users.id ' +
                    'AND users.email = ' + utils.DBStr(condition[key]) +
                    ')');
            } else if (key === 'type') {
                whereList.push('approvals.approval_type = ' + utils.DBStr(condition[key]));
            } else {
                whereList.push('approvals.' + key + ' = ' + utils.DBStr(condition[key]));
            }
        }
    }

    // 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 = ' AND ' + whereList.join(' AND ');
    }

    var sql = '';
    async.waterfall([
        function (cb) {
            // select approvals
            sql = 'SELECT approvals.*, users.email, users.name  FROM approvals, users WHERE approvals.user_id = users.id ' + where + limit;
            connection.query(sql, function (err1, approvals) {
                cb(err1, approvals);
            });
        },
        function (approvals, cb) {
            // add approval route information
            async.map(approvals, function (approval, cb1) {
                sql = 'SELECT * FROM approval_route, users ' +
                    'WHERE approval_route.user_id = users.id ' +
                    'AND approval_id = ' + utils.DBStr(approval.id);
                connection.query(sql, function (err1, routes) {
                    var approvalRoutes = [];
                    if (routes) {
                        _.each(routes, function (route) {
                            // get approval route user info
                            var approvalRoute = {
                                id: route.id,
                                approvalId: route.approval_id,
                                userId: route.user_id,
                                email: route.email,
                                order: route.approval_order,
                                type: route.approval_type,
                                status: route.approval_status,
                                date: route.approval_date,
                                message: route.approval_message
                            };
                            approvalRoutes.push(approvalRoute);
                        });
                    }
                    approval.approvalRoutes = _.sortBy(approvalRoutes, 'order');
                    cb1(err1, approval);
                });
            }, cb);
        }
    ],
        function (err, approvals) {
            if (err) {
                callback(new DError('MODEL003', {
                    sql: sql
                }, err), null);
            } else {
                var newApprovalList = _.map(approvals, function (approval) {
                    var newObject = create(approval.email, approval.subject, approval.contents, approval.summary, approval.approvalRoutes, approval.approval_type);
                    newObject.id = approval.id;
                    newObject.userId = approval.user_id;
                    newObject.submissionDate = approval.submission_date;
                    newObject.submissionStatus = approval.submission_status;
                    return newObject;
                });
                callback(null, newApprovalList);
            }
        });
};

/**
 * @function update
 * @param {object} connection - db connection object
 * @param {module:models/approvals} approval
 * @param {module:models/approval.callback_err_distributionid} callback - callback
 * @memberOf module:models/approvals
 */

module.exports.update = function (connection, approval, callback) {
    async.waterfall([
        function (cb) {
            connection.query('START TRANSACTION', function (err) {
                if (err) {
                    cb(new DError('MODEL001', err));
                } else {
                    cb(null);
                }
            });
        },
        function (cb) {
            // update approvals table
            var sql = 'UPDATE approvals SET' +
                ' subject = ' + utils.DBStr(approval.subject) +
                ', contents = ' + utils.DBStr(approval.contents) +
                ', summary = ' + utils.DBStr(approval.summary) +
                ', submission_status = ' + utils.DBStr(approval.submissionStatus) +
                ' WHERE id = ' + approval.id;
            connection.query(sql, function (err) {
                if (err) {
                    cb(new DError('MODEL003', {
                        sql: sql
                    }, err), null);
                } else {
                    cb(err);
                }
            });
        },
        function (cb) {
            // update approval_route table
            updateRoute(connection, approval.id, approval.approvalRoutes, cb);
        },
        function (cb) {
            connection.query('COMMIT', function (err) {
                if (err) {
                    cb(new DError('MODEL002', err));
                } else {
                    cb(err);
                }
            });
        }
    ],
        function (err) {
            if (err) {
                connection.query('ROLLBACK', function () {});
            }
            callback(err);
        });
};

function updateRoute(connection, approvalId, approvalRoutes, callback) {
    // update approval_route table
    async.each(approvalRoutes, function (route, cb) {
        async.waterfall([
            function (cb1) {
                // check route exists
                var sql = 'SELECT * FROM approval_route' +
                    ' WHERE approval_id = ' + route.approvalId +
                    ' AND approval_order = ' + route.order;
                connection.query(sql, function (err1, results) {
                    if (err1) {
                        cb1(new DError('MODEL003', {
                            sql: sql
                        }, err1), null);
                    } else {
                        cb1(err1, results);
                    }
                });
            },
            function (results, cb1) {
                var sql2 = 'SELECT * FROM users WHERE email = ' + utils.DBStr(route.email);
                if (results.length === 1) {
                    // update
                    connection.query(sql2, function (err1, userInformations) {
                        if (err1) {
                            cb1(new DError('MODEL003', {
                                sql: sql2
                            }, err1), null);
                        } else {
                            // update approval route
                            var sql3 = 'UPDATE approval_route SET' +
                                ' user_id = ' + userInformations[0].id +
                                ', approval_type = ' + utils.DBStr(route.type) +
                                ', approval_status = ' + utils.DBStr(route.status) +
                                ', approval_date = ' + ((route.date === null) ? route.date : utils.DBStr(route.date)) +
                                ', approval_message = ' + ((route.message === null) ? route.message : utils.DBStr(route.message)) +
                                ' WHERE approval_id =' + route.approvalId +
                                ' AND approval_order = ' + route.order;
                            connection.query(sql3, function (err4) {
                                if (err4) {
                                    cb1(new DError('MODEL003', {
                                        sql: sql3
                                    }, err4));
                                } else {
                                    cb1(null);
                                }
                            });
                        }
                    });
                } else {
                    cb1(new Error('too many or empty'));
                }
            }
        ],
            function (err1) {
                cb(err1);
            });
    }, callback);
}


module.exports.delete = function (connection, approvalId, callback) {
    connection.query('START TRANSACTION', function (err) {
        if (err) {
            callback(new DError('MODEL001', err));
            return;
        }
        async.waterfall([
            function (cb) {
                // delete approval_route
                var sql = 'DELETE FROM approval_route WHERE approval_id = ' + approvalId;
                connection.query(sql, function (err) {
                    if (err) {
                        cb(new DError('MODEL003', {
                            sql: sql
                        }, err), null);
                    } else {
                        cb(null);
                    }
                });
            },
            function (cb) {
                // delete approval
                var sql1 = 'DELETE FROM approvals WHERE id = ' + approvalId;
                connection.query(sql1, function (err) {
                    if (err) {
                        cb(new DError('MODEL003', {
                            sql: sql1
                        }, err), null);
                    } else {
                        cb(null);
                    }
                });
            },
            function (cb) {
                connection.query('COMMIT', function (err) {
                    if (err) {
                        cb(new DError('MODEL002', err));
                    } else {
                        cb(err);
                    }
                });
            }
        ],
            function (err) {
                if (err) {
                    connection.query('ROLLBACK', function () {
                        callback(err);
                    });
                } else {
                    callback(err);
                }
            });
    });
};
