import dbconn = require('../config/dbconn')

var methods = {
    // ===================== user db =====================
    registerNewUser: function(data:any, callback:any) {
	dbconn.user.getConnection(function(err:any, thisconn){
            thisconn.beginTransaction(function(err:any) { // START TRANSACTION
                if (err) { throw err }
                 // insert profile
                thisconn.query('INSERT INTO user SET ?', data.profile, function (err:any, results:any, fields:any) {
                    if (err) {
                      return thisconn.rollback(function() {
                        throw err
                      });
                    }
                    let newUserId:number = results.insertId
                    // set password
                    var credentialData:any = {
                        user_id: newUserId,
                        password: data.password
                    }
                    thisconn.query('INSERT INTO credential SET ?', credentialData, function (err:any, results:any, fields:any) {
                        if (err) {
                            return thisconn.rollback(function() {
                                throw err
                            });
                        }
                        // set default user-project-role
                        var projectRoleData:any = {
                            project_id: 1,  //M4_LAB
                            role_id: 2,     // USER
                            user_id: newUserId
                        }
                        thisconn.query('INSERT INTO user_project_role SET ?', projectRoleData, function (err:any, results:any, fields:any) {
                            if (err) {
                                return thisconn.rollback(function() {
                                    throw err
                                });
                            }
                            // MLAB-129: INSERT verification token
                            let verificationData:any = {
                                user_id: newUserId,
                                token: data.verificationToken
                            }
                            thisconn.query('INSERT INTO verification SET ?', verificationData, function (err:any, results:any, fields:any) {
                                if (err) {
                                    return thisconn.rollback(function() {
                                        throw err
                                    });
                                }
                                // COMMIT
                                thisconn.commit(function(err:any) {
                                    if (err) {
                                        return thisconn.rollback(function() {
                                            throw err
                                        })
                                    }
                                })
                            })
                        })
                    });
                });
	    });
            callback(err)
        })
    },
    getUserByEmail: async function(email:any) {
        try {
            let rows:any = await dbconn.user.promise().query('SELECT id, verificationStatus, salutation, title, firstname, lastname, industry, organisation, speciality, m4lab_idp FROM user WHERE email = "' +email+'"')
            if (rows[0][0]) {
                return rows[0][0]
            }
            else { return null }
        } catch (err) {
            console.error(err)
        }
        return null
    },
    getUserEmailById: async function(userId:number) {
        try {
            let rows:any = await dbconn.user.promise().query('SELECT email FROM user WHERE id = ' +userId)
            if (rows[0][0]) {
                return rows[0][0].email
            }
            else { return null }
        } catch (err) {
            console.error(err)
        }
        return null
    },
    checkUserEmail: async function(email:any) {
        try {
            let rows:any = await dbconn.user.promise().query('SELECT id, email FROM user WHERE email = "' +email+'"')
            if (rows[0][0]) {
                return rows[0][0]
            }
            else { return null }
        } catch (err) {
            console.error(err)
        }
        return null
    },
    getUserByToken: async function(token:any) {
        try {
            let rows:any = await dbconn.user.promise().query('SELECT t1.user_id, t2.email FROM userdb.credential AS t1 INNER JOIN userdb.user AS t2 ON t1.user_id = t2.id AND t1.resetPasswordToken = "'
                +token+'" and resetPasswordExpires > '+Date.now())
            if (rows[0][0]) {
                return rows[0][0]
            }
            else { return null }
        } catch (err) {
            console.error(err)
        }
        return null
    },
    updateUserById: async function(userId:number, userData:any) {
        try {
            let result:any = await dbconn.user.promise().query('UPDATE user SET ? WHERE id = ' +userId, userData)
            return result
        } catch (err) {
            console.error(err)
        }
        return null
    },
    updateCredential: async function(data:any) {
        try {
            let result:any = await dbconn.user.promise().query('UPDATE credential SET ? WHERE user_id = ' +data.user_id, data)
            return result
        } catch (err) {
            console.error(err)
        }
        return null
    },
    addUserProjectRole_OBSOLETE: function(data:any, callback:any) {
        dbconn.user.query('INSERT INTO user_project_role SET ?', data, function (err:any){
            if (err) throw err
            callback(err)
        })
    },
    getVerificationTokenByUserId: async function(userId:number) {
        try {
            let rows:any = await dbconn.user.promise().query('SELECT token FROM verification WHERE user_id = "' +userId+'"')
            if (rows[0][0]) {
                return rows[0][0].token
            }
            else { return null }
        } catch (err) {
            console.error(err)
        }
        return null
    },
    getUserIdByVerificationToken: async function(token:any) {
        try {
            let rows:any = await dbconn.user.promise().query('SELECT user_id FROM verification WHERE token = "' +token+'"')
            if (rows[0][0]) {
                return rows[0][0].user_id
            }
            else {
                return null
            }
        } catch (err) {
            console.error(err)
        }
        return null
    },
    verifyUserAccount: function(userData:any, callback:any) {
	dbconn.user.getConnection(function(err:any, thisconn){
            thisconn.beginTransaction(function(err:any) { // START TRANSACTION
                if (err) { throw err }
                // update user status
                thisconn.query('UPDATE user SET ? WHERE id =' +userData.id, userData, function (err:any, rows:any, fields:any) {
                    if (err) {
                        return thisconn.rollback(function() { throw err })
                    }
                    // delete verification token
                    thisconn.query('DELETE FROM verification WHERE user_id = '+userData.id, function (err:any, rows:any, fields:any) {
                        if (err) {
                            return thisconn.rollback(function() { throw err })
                        }
                        // COMMIT
                        thisconn.commit(function(err:any) {
                            if (err) {
                                return thisconn.rollback(function() { throw err })
                            }
                        })
                    })
                })
            })
        callback(err)
        })
    },
    /* ===== GitLab ===== */
    getGitlabId: async function(userId:number) {
        try {
            let rows:any = await dbconn.user.promise().query('SELECT gu.gitlab_userId FROM user_gitlab gu, user u WHERE u.id = "' +userId+'" and gu.user_id = u.id')
            if (rows[0][0]) {
                return rows[0][0].gitlab_userId
            } else {
                return null
            }
        }
        catch(err) {
            console.error(err)
            return err
        }
    },
    addGitlabUser: function(data:any, callback:any){
        dbconn.user.query('INSERT INTO user_gitlab SET ?', data, function (err:any) {
            if (err) throw err
            callback(err)
        })
    }
};

export = methods