/**
 * db-migration.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 mysql = require('mysql');
var async = require('async');
var optimist = require('optimist');
var _ = require('underscore');
var utils = require('../../../lib/utils');

var argv = optimist.usage('Usage: $0  [--host <Host>] [--port <Port>] [--user <userId>] [--password <password>] <--source <source>> <--target <target>>')
    .default('host', '127.0.0.1')
    .default('port', '3306')
    .default('user', 'root')
    .default('password', 'password')
    .describe('host', 'DB HOST')
    .describe('port', 'DB PORT')
    .describe('user', 'DB USER')
    .describe('password', 'DB PASSWORD')
    .describe('source', 'SOURCE DB NAME')
    .describe('target', 'TARGET DB NAME')
    .argv;

var connection;
var src = argv.source;
var tar = argv.target;

async.series([
    function (cb) {
        connection = mysql.createConnection({
            host: argv.host,
            port: argv.port,
            user: argv.user,
            password: argv.password
        });
        console.log('Connect database...');
        console.log('host: ' + argv.host);
        console.log('port: ' + argv.port);
        connection.connect(function (err) {
            if (err === null) {
                cb(null);
            } else {
                optimist.showHelp();
            }
        });
    },
    function (cb) {
        console.log('\nDelete target tables');
        var sql = 'DELETE FROM ' + tar + '.group_project';
        connection.query(sql, function (err, result) {
            if (err) {
                console.error('ERROR: ' + sql);
                console.error(err);
            }
            console.log('\tDelete table: ' + tar + '.group_project... ' + result.affectedRows + ' deleted');
            cb(err);
        });
    },
    function (cb) {
        var sql = 'DELETE FROM ' + tar + '.project_env';
        connection.query(sql, function (err, result) {
            if (err) {
                console.error('ERROR: ' + sql);
                console.error(err);
            }
            console.log('\tDelete table: ' + tar + '.project_env... ' + result.affectedRows + ' deleted');
            cb(err);
        });
    },
    function (cb) {
        var sql = 'DELETE FROM ' + tar + '.project_info';
        connection.query(sql, function (err, result) {
            if (err) {
                console.error('ERROR: ' + sql);
                console.error(err);
            }
            console.log('\tDelete table: ' + tar + '.project_info... ' + result.affectedRows + ' deleted');
            cb(err);
        });
    },
    function (cb) {
        var sql = 'DELETE FROM ' + tar + '.projects';
        connection.query(sql, function (err, result) {
            if (err) {
                console.error('ERROR: ' + sql);
                console.error(err);
            }
            console.log('\tDelete table: ' + tar + '.projects... ' + result.affectedRows + ' deleted');
            cb(err);
        });
    },
    function (cb) {
        var sql = 'DELETE FROM ' + tar + '.distribution_info';
        connection.query(sql, function (err, result) {
            if (err) {
                console.error('ERROR: ' + sql);
                console.error(err);
            }
            console.log('\tDelete table: ' + tar + '.distribution_info... ' + result.affectedRows + ' deleted');
            cb(err);
        });
    },
    function (cb) {
        var sql = 'DELETE FROM ' + tar + '.distributions';
        connection.query(sql, function (err, result) {
            if (err) {
                console.error('ERROR: ' + sql);
                console.error(err);
            }
            console.log('\tDelete table: ' + tar + '.distributions... ' + result.affectedRows + ' deleted');
            cb(err);
        });
    },
    function (cb) {
        var sql = 'DELETE FROM ' + tar + '.user_group WHERE user_id > 2 AND group_id <> 1';
        connection.query(sql, function (err, result) {
            if (err) {
                console.error('ERROR: ' + sql);
                console.error(err);
            }
            console.log('\tDelete table: ' + tar + '.user_group... ' + result.affectedRows + ' deleted except administrator');
            cb(err);
        });
    },
    function (cb) {
        var sql = 'DELETE FROM ' + tar + '.group_info WHERE group_id <> 1';
        connection.query(sql, function (err, result) {
            if (err) {
                console.error('ERROR: ' + sql);
                console.error(err);
            }
            console.log('\tDelete table: ' + tar + '.group_info... ' + result.affectedRows + ' deleted except admin');
            cb(err);
        });
    },
    function (cb) {
        var sql = 'DELETE FROM ' + tar + '.groups WHERE id <> 1';
        connection.query(sql, function (err, result) {
            if (err) {
                console.error('ERROR: ' + sql);
                console.error(err);
            }
            console.log('\tDelete table: ' + tar + '.groups... ' + result.affectedRows + ' deleted except admin');
            cb(err);
        });
    },
    function (cb) {
        var sql = 'DELETE FROM ' + tar + '.user_info WHERE user_id > 2';
        connection.query(sql, function (err, result) {
            if (err) {
                console.error('ERROR: ' + sql);
                console.error(err);
            }
            console.log('\tDelete table: ' + tar + '.user_info... ' + result.affectedRows + ' deleted except administrator');
            cb(err);
        });
    },
    function (cb) {
        var sql = 'DELETE FROM ' + tar + '.users WHERE id > 2';
        connection.query(sql, function (err, result) {
            if (err) {
                console.error('ERROR: ' + sql);
                console.error(err);
            }
            console.log('\tDelete table: ' + tar + '.users... ' + result.affectedRows + ' deleted except administrator');
            cb(err);
        });
    },
    function (cb) {
        console.log('\n' + src + '.distributions => ' + tar + '.distributions');
        var distributionCnt = 0;
        var distributionInfoCnt = 0;
        var sql = 'SELECT A.id ' +
            ', A.name' +
            ', A.pkgsvr_url' +
            ', A.pkgsvr_addr' +
            ', A.status' +
            ', A.description' +
            ', A.pkgsvr_password' +
            ', B.distribution_id' +
            ', B.pkgsvr_url AS sync_pkgsvr_url' +
            ', B.period' +
            ', B.description AS sync_description' +
            ' FROM ' + src + '.distributions A' +
            ' LEFT OUTER JOIN ' + src + '.sync_pkg_servers B ' +
            ' ON A.id = B.distribution_id';
        connection.query(sql, function (err, rows) {
            if (err) {
                console.error('ERROR: ' + sql);
                cb(err);
                return;
            }
            async.eachSeries(rows, function (row, ecb1) {
                sql = 'INSERT INTO ' + tar + '.distributions (group_id' +
                    ', name' +
                    ', type' +
                    ', status' +
                    ', access' +
                    ', description )' +
                    ' values ( 1' +
                    ', ' + utils.DBStr(row.name) +
                    ', \'tizen\'' +
                    ', ' + utils.DBStr(row.status) +
                    ', \'PUBLIC\'' +
                    ', ' + utils.DBStr(row.description) + ')';
                connection.query(sql, function (err, insertInfo) {
                    if (err) {
                        console.error('ERROR: ' + sql);
                        ecb1(err);
                        return;
                    }
                    distributionCnt++;
                    var distributionId = insertInfo.insertId;
                    if (false) { // manualy
                        async.eachSeries(['pkgsvr_url', 'pkgsvr_addr', 'sync_pkgsvr_url'],
                            function (item, ecb2) {
                                if (row[item]) {
                                    var obj = utils.objectToStringAndType(row[item]);
                                    var type = obj.type;
                                    var value = obj.string;
                                    sql = 'INSERT INTO ' + tar + '.distribution_info (distribution_id' +
                                        ', property' +
                                        ', value' +
                                        ', type)' +
                                        ' values ( ' + distributionId +
                                        ', ' + utils.DBStr(item) +
                                        ', ' + utils.DBStr(value) +
                                        ', ' + utils.DBStr(type) + ')';
                                    connection.query(sql, function (err, insertInfo) {
                                        if (err) {
                                            console.error('ERROR: ' + sql);
                                        }
                                        distributionInfoCnt++;
                                        ecb2(err);
                                    });
                                } else {
                                    ecb2(null);
                                }
                            },
                            function (err) {
                                ecb1(err);
                            }
                        );
                    } else {
                        ecb1(null);
                    }
                });
            }, function (err) {
                if (!err) {
                    console.log('\tsource distribution count: ' + rows.length);
                    console.log('\ttarget distribution count: ' + distributionCnt);
                    console.log('\ttarget distribution_info count: ' + distributionInfoCnt);
                }
                cb(err);
            });
        });
    },
    function (cb) {
        console.log('\n' + src + '.projects => ' + tar + '.projects');
        var sourceProjectCnt = 0;
        var targetProjectCnt = 0;
        var targetProjectInfoCnt = 0;
        var targetProjectEnvCnt = 0;
        async.waterfall([
            function (wcb) {
                var sql = 'SELECT A.id' +
                    ', B.name distribution_name' +
                    ', A.name ' +
                    ', A.user_id ' +
                    ', A.ptype type' +
                    ', A.status ' +
                    ', W.os_name ' +
                    ', X.git_repos GIT_REPO ' +
                    ', X.git_branch GIT_BRANCH ' +
                    ', Y.pkg_name PKG_NAME ' +
                    'FROM ' + src + '.projects A ' +
                    'LEFT OUTER JOIN (SELECT W1.project_id ' +
                    ', group_concat(W2.name) os_name ' +
                    'FROM ' + src + '.project_os W1, ' + src + '.supported_os W2 ' +
                    'WHERE W1.supported_os_id = W2.id ' +
                    'GROUP BY project_id) W ' +
                    'ON A.id = W.project_id ' +
                    'LEFT OUTER JOIN ' + src + '.project_gits X ' +
                    'ON A.id = X.project_id ' +
                    'LEFT OUTER JOIN ' + src + '.project_bins Y ' +
                    'ON A.id = Y.project_id ' +
                    ', ' + src + '.distributions B ' +
                    'WHERE A.distribution_id = B.id ' +
                    'ORDER BY distribution_name, name';
                connection.query(sql, function (err, rows) {
                    if (err) {
                        console.error('ERROR: ' + sql);
                    }
                    sourceProjectCnt = rows.length;
                    wcb(err, rows);
                });
            },
            function (projects, wcb) {
                async.mapSeries(projects, function (project, mcb) {
                    if (project.type !== 'GIT') {
                        mcb(null, project);
                        return;
                    }

                    // get last build informations.
                    var sql = 'SELECT B.project_id ' +
                        ', C.pkg_ver ' +
                        ', C.location ' +
                        ', D.name os_name ' +
                        ', E.email ' +
                        ', (SELECT group_concat(pkg_name) pkgs ' +
                        'FROM ' + src + '.packages Z ' +
                        'WHERE B.source_id = Z.source_id ' +
                        'AND B.supported_os_id = Z.supported_os_id ' +
                        'group by source_id, supported_os_id) packages ' +
                        'FROM (SELECT MAX(A1.id) id ' +
                        ', A1.project_id ' +
                        ', A1.supported_os_id ' +
                        'FROM ' + src + '.jobs A1 ' +
                        ', ' + src + '.sources A2 ' +
                        'WHERE A1.jtype = \'BUILD\' ' +
                        'AND A1.status = \'FINISHED\' ' +
                        'AND A1.source_id = A2.id ' +
                        'AND A1.project_id = ' + project.id + ' ' +
                        'GROUP BY A1.project_id, A1.supported_os_id) A ' +
                        ', ' + src + '.jobs B ' +
                        ', ' + src + '.sources C ' +
                        ', ' + src + '.supported_os D ' +
                        ', ' + src + '.users E ' +
                        'WHERE A.id = B.id ' +
                        'AND B.project_id = C.project_id ' +
                        'AND B.source_id = C.id  ' +
                        'AND B.supported_os_id = D.id ' +
                        'AND B.user_id = E.id ';
                    connection.query(sql, function (err, rows) {
                        if (err) {
                            console.error('ERROR: ' + sql);
                        }
                        if (rows !== undefined && rows.length !== 0) {
                            var buildInfo = {};
                            _.each(rows, function (row) {
                                var osBuildInfo = {};
                                var packages;
                                if (row.packages) {
                                    packages = row.packages.split(',');
                                } else {
                                    packages = [];
                                }
                                osBuildInfo[row.os_name] = {
                                    version: row.pkg_ver,
                                    gitRepo: project.GIT_REPO,
                                    gitCommit: row.location,
                                    packageNames: row.packages
                                };
                                _.extend(buildInfo, osBuildInfo);
                            });
                            project.LAST_BUILD_INFO = buildInfo;
                        }
                        mcb(err, project);
                    });
                },
                    function (err) {
                        wcb(err, projects);
                    });
            },
            function (projects, wcb) {
                async.eachSeries(projects, function (project, ecb1) {
                    var projectId;
                    async.series([
                        function (scb) {
                            var projectType;
                            if (project.type === 'GIT') {
                                projectType = 'Tizen-Source';
                            } else if (project.type === 'BINARY') {
                                projectType = 'Tizen-Binary';
                            } else {
                                projectType = project.type;
                            }
                            sql = 'INSERT INTO ' + tar + '.projects (distribution_id' +
                                ', name' +
                                ', type' +
                                ', status' +
                                ', notification_group_id )' +
                                ' values ( (SELECT MIN(id) id  FROM ' + tar + '.distributions WHERE name = ' + utils.DBStr(project.distribution_name) + ') ' +
                                ', ' + utils.DBStr(project.name) +
                                ', ' + utils.DBStr(projectType) +
                                ', ' + utils.DBStr(project.status) +
                                ', NULL )';
                            connection.query(sql, function (err, insertInfo) {
                                if (err) {
                                    console.error('ERROR: ' + sql);
                                }
                                projectId = insertInfo.insertId;
                                targetProjectCnt++;
                                scb(err);
                            });
                        },
                        function (scb) {
                            if (project.type === 'GIT' || project.type === 'BINARY') {
                                async.eachSeries(['GIT_REPO', 'GIT_BRANCH', 'LAST_BUILD_INFO', 'PKG_NAME'],
                                    function (property, ecb2) {
                                        if (project[property] ||
                                            (project.type === 'GIT' && property === 'GIT_REPO') ||
                                            (project.type === 'GIT' && property === 'GIT_BRANCH') ||
                                            (project.type === 'BINARY' && property === 'PKG_NAME')) {
                                            var obj = utils.objectToStringAndType(project[property]);
                                            var type = obj.type;
                                            var value = obj.string;
                                            sql = 'INSERT INTO ' + tar + '.project_info (project_id ' +
                                                ', property' +
                                                ', value' +
                                                ', type)' +
                                                ' values ( ' + projectId +
                                                ', ' + utils.DBStr(property) +
                                                ', ' + utils.DBStr(value) +
                                                ', ' + utils.DBStr(type) + ')';
                                            connection.query(sql, function (err, insertInfo) {
                                                if (err) {
                                                    console.error('ERROR: ' + sql);
                                                }
                                                targetProjectInfoCnt++;
                                                ecb2(err);
                                            });
                                        } else {
                                            ecb2(null);
                                        }
                                    },
                                    function (err) {
                                        scb(err);
                                    }
                                );
                            } else {
                                scb(null);
                            }
                        },
                        function (scb) {
                            if (project.os_name) {
                                var envs = project.os_name.split(',');
                                async.eachSeries(envs, function (env, ecb2) {
                                    sql = 'INSERT INTO ' + tar + '.project_env (project_id' +
                                        ', environment_id)' +
                                        ' values (' + projectId +
                                        ', (SELECT MIN(id) id FROM ' + tar + '.environments WHERE name = ' + utils.DBStr(env) + ') )';
                                    connection.query(sql, function (err, insertInfo) {
                                        if (err) {
                                            console.error('ERROR: ' + sql);
                                        }
                                        targetProjectEnvCnt++;
                                        ecb2(err);
                                    });
                                }, function (err) {
                                    scb(err);
                                });
                            } else {
                                scb(null);
                            }
                        }
                    ], function (err) {
                        ecb1(err, projects);
                    });
                }, function (err) {
                    wcb(err, projects);
                });
            }],
            function (err) {
                if (!err) {
                    console.log('\tsource project count: ' + sourceProjectCnt);
                    console.log('\ttarget project count: ' + targetProjectCnt);
                    console.log('\ttarget project_info count: ' + targetProjectInfoCnt);
                    console.log('\ttarget project_env count: ' + targetProjectEnvCnt);
                }
                cb(err);
            }
        );
    },
    function (cb) {
        console.log('\n' + src + '.users => ' + tar + '.users');
        var userCnt = 0;
        var sql = 'SELECT * FROM ' + src + '.users WHERE id <> 1';
        connection.query(sql, function (err, rows) {
            if (err) {
                console.error('ERROR: ' + sql);
            }
            async.eachSeries(rows, function (row, ecb1) {
                sql = 'INSERT INTO ' + tar + '.users(name ' +
                    ', email ' +
                    ', password_hash ' +
                    ', password_salt ' +
                    ', status ' +
                    ', image_id )' +
                    ' values ( ' + utils.DBStr(row.name) +
                    ', ' + utils.DBStr(row.email) +
                    ', ' + utils.DBStr(row.password_hash) +
                    ', ' + utils.DBStr(row.password_salt) +
                    ', \'OPEN\'' +
                    ', NULL )';
                connection.query(sql, function (err, insertInfo) {
                    if (err) {
                        console.error('ERROR: ' + sql);
                    }
                    userCnt++;
                    ecb1(err);
                });
            }, function (err) {
                console.log('\tsource user count: ' + rows.length);
                console.log('\ttarget user count: ' + userCnt);
                cb(err);
            });
        });
    },
    function (cb) {
        console.log('\n' + src + '.groups => ' + tar + '.groups ');
        var groupCnt = 0;
        var userGroupCnt = 0;
        var groupProjectCnt = 0;
        var sql = 'SELECT * FROM ' + src + '.groups WHERE id <> 1';
        connection.query(sql, function (err, groups) {
            if (err) {
                console.error('ERROR: ' + sql);
                cb(err);
                return;
            }
            async.eachSeries(groups, function (group, ecb1) {
                async.waterfall([
                    function (wcb) {
                        sql = 'INSERT INTO ' + tar + '.groups (name ' +
                            ', status ' +
                            ', description)' +
                            ' values ( ' + utils.DBStr(group.name) +
                            ', \'OPEN\'' +
                            ', ' + utils.DBStr(group.description) + ')';
                        connection.query(sql, function (err, insertInfo) {
                            if (err) {
                                console.error('ERROR: ' + sql);
                            }
                            groupCnt++;
                            wcb(err, insertInfo.insertId);
                        });
                    },
                    function (groupId, wcb) {
                        sql = 'SELECT A.*, B.email FROM ' + src + '.user_groups A, ' + src + '.users B ' +
                            'WHERE A.group_id = ' + group.id + ' AND A.user_id = B.id ';
                        connection.query(sql, function (err, users) {
                            if (err) {
                                console.error('ERROR: ' + sql);
                            }
                            wcb(err, groupId, users);
                        });
                    },
                    function (groupId, users, wcb) {
                        async.eachSeries(users, function (user, ecb2) {
                            sql = 'INSERT INTO ' + tar + '.user_group ( user_id , group_id) ' +
                                'VALUES ( (SELECT MIN(id) id FROM ' + tar + '.users WHERE email = ' + utils.DBStr(user.email) + '), ' + groupId + ')';
                            connection.query(sql, function (err, insertInfo) {
                                if (err) {
                                    console.error('ERROR: ' + sql);
                                }
                                userGroupCnt++;
                                ecb2(err);
                            });
                        }, function (err) {
                            wcb(err, groupId);
                        });
                    },
                    function (groupId, wcb) {
                        sql = 'SELECT B.*, C.name distribution_name ' +
                            'FROM ' + src + '.group_project_accesses A, ' + src + '.projects B, ' + src + '.distributions C ' +
                            'WHERE A.group_id = ' + group.id + ' AND A.build = \'TRUE\' AND A.project_id = B.id AND B.distribution_id = C.id';
                        connection.query(sql, function (err, projects) {
                            if (err) {
                                console.error('ERROR: ' + sql);
                            }
                            wcb(err, groupId, projects);
                        });
                    },
                    function (groupId, projects, wcb) {
                        async.eachSeries(projects, function (project, ecb2) {
                            sql = 'INSERT INTO ' + tar + '.group_project ( group_id, project_id) ' +
                                'VALUES ( ' + groupId + ', (SELECT MIN(A.id) id FROM ' + tar + '.projects A, ' + tar + '.distributions B ' +
                                'WHERE A.name = ' + utils.DBStr(project.name) +
                                ' AND B.name = ' + utils.DBStr(project.distribution_name) +
                                ' AND A.distribution_id = B.id) )';
                            connection.query(sql, function (err, insertInfo) {
                                if (err) {
                                    console.error('ERROR: ' + sql);
                                }
                                groupProjectCnt++;
                                ecb2(err);
                            });
                        }, function (err) {
                            wcb(err);
                        });
                    }],
                    function (err) {
                        ecb1(err);
                    }
                );
            }, function (err) {
                cb(err);
                console.log('\tsource group count: ' + groups.length);
                console.log('\ttarget group count: ' + groupCnt);
                console.log('\ttarget user_group count: ' + userGroupCnt);
                console.log('\ttarget group_project count: ' + groupProjectCnt);
            });
        });
    }
], function (err) {
    if (err) {
        console.error(err);
    }
    connection.end();
}
);
