dbController.ts

import { dbConnection } from '../config/dbconn'

const dbController = {
  // ===================== user db =====================
  registerNewUser: function (data: any, callback: any) {
    dbConnection.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
            })
          }
          const newUserId: number = results.insertId
          // set password
          const 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
            const 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
              const 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 {
      const rows: any = await dbConnection.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 {
      const rows: any = await dbConnection.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 {
      const rows: any = await dbConnection.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 {
      const rows: any = await dbConnection.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 {
      const result: any = await dbConnection.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 {
      const result: any = await dbConnection.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) {
    dbConnection.user.query('INSERT INTO user_project_role SET ?', data, function (err: any) {
      if (err) throw err
      callback(err)
    })
  },
  getVerificationTokenByUserId: async function (userId: number) {
    try {
      const rows: any = await dbConnection.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 {
      const rows: any = await dbConnection.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) {
    dbConnection.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 {
      const rows: any = await dbConnection.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) {
    dbConnection.user.query('INSERT INTO user_gitlab SET ?', data, function (err: any) {
      if (err) throw err
      callback(err)
    })
  }
}

export { dbController }