add verified field for User

1 files changed, 50 insertions, 10 deletions

diff --git a/src/main/java/com/juick/service/UserServiceImpl.java b/src/main/java/com/juick/service/UserServiceImpl.java
index f05fc93f..4d370631 100644
--- a/src/main/java/com/juick/service/UserServiceImpl.java
+++ b/src/main/java/com/juick/service/UserServiceImpl.java
@@ -55,7 +55,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
 
     private class UserMapper implements RowMapper<User> {
         @Override
-        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
+        public User mapRow(@Nonnull ResultSet rs, int rowNum) throws SQLException {
             User user = new User();
 
             user.setUid(rs.getInt(1));
@@ -66,6 +66,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
             if (seen != null) {
                 user.setSeen(seen.toInstant());
             }
+            user.setVerified(rs.getInt(6) > 0);
             return user;
         }
     }
@@ -132,7 +133,11 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
     @Override
     public Optional<User> getUserByUID(final int uid) {
         List<User> list = getJdbcTemplate().query(
-                "SELECT id, nick, passw, banned, last_seen FROM users WHERE id = ?", new UserMapper(), uid);
+                "SELECT u.id, u.nick, u.passw, u.banned, u.last_seen, " +
+                        "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " +
+                        "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " +
+                        "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " +
+                        "LEFT JOIN emails e ON e.user_id = u.id WHERE u.id = ?", new UserMapper(), uid);
 
         return list.isEmpty() ? Optional.empty() : Optional.of(list.get(0));
     }
@@ -143,7 +148,12 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
     public User getUserByName(final String username) {
         if (StringUtils.isNotBlank(username)) {
             List<User> list = getJdbcTemplate().query(
-                    "SELECT id, nick, passw, banned, last_seen FROM users WHERE nick = ?", new UserMapper(), username);
+                    "SELECT u.id, u.nick, u.passw, u.banned, u.last_seen, " +
+                            "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " +
+                            "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " +
+                            "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " +
+                            "LEFT JOIN emails e ON e.user_id = u.id " +
+                            "WHERE u.nick = ?", new UserMapper(), username);
 
             if (!list.isEmpty())
                 return list.get(0);
@@ -157,7 +167,12 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
     public User getUserByEmail(String email) {
         if (StringUtils.isNotBlank(email)) {
             List<User> list = getJdbcTemplate().query(
-                    "SELECT id, nick, passw, banned, last_seen FROM users WHERE id = (SELECT DISTINCT user_id FROM emails WHERE email = ?)",
+                    "SELECT u.id, u.nick, u.passw, u.banned, u.last_seen, " +
+                            "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " +
+                            "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " +
+                            "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " +
+                            "LEFT JOIN emails e ON e.user_id = u.id " +
+                            "WHERE u.id = (SELECT DISTINCT user_id FROM emails WHERE email = ?)",
                     new UserMapper(),
                     email);
 
@@ -174,7 +189,12 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
 
         if (StringUtils.isNotBlank(jid)) {
             List<User> list = getJdbcTemplate().query(
-                    "SELECT id, nick, passw, banned, last_seen FROM users WHERE id = (SELECT user_id FROM jids WHERE jid = ?)",
+                    "SELECT u.id, u.nick, u.passw, u.banned, u.last_seen," +
+                            "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " +
+                            "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " +
+                            "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " +
+                            "LEFT JOIN emails e ON e.user_id = u.id " +
+                            "WHERE u.id = (SELECT user_id FROM jids WHERE jid = ?)",
                     new UserMapper(),
                     jid);
 
@@ -191,7 +211,12 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
             return Collections.emptyList();
 
         return getNamedParameterJdbcTemplate().query(
-                "SELECT id, nick, passw, banned, last_seen FROM users WHERE nick IN (:unames)",
+                "SELECT u.id, u.nick, u.passw, u.banned, u.last_seen," +
+                        "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " +
+                        "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " +
+                        "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " +
+                        "LEFT JOIN emails e ON e.user_id = u.id " +
+                        "WHERE u.nick IN (:unames)",
                 new MapSqlParameterSource("unames", unames),
                 new UserMapper());
     }
@@ -203,7 +228,12 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
             return Collections.emptyList();
 
         return getNamedParameterJdbcTemplate().query(
-                "SELECT id, nick, passw, banned, last_seen FROM users WHERE id IN (:ids)",
+                "SELECT u.id, u.nick, u.passw, u.banned, u.last_seen," +
+                        "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " +
+                        "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " +
+                        "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " +
+                        "LEFT JOIN emails e ON e.user_id = u.id " +
+                        "WHERE u.id IN (:ids)",
                 new MapSqlParameterSource("ids", uids),
                 new UserMapper());
     }
@@ -258,8 +288,13 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
     public com.juick.User getUserByHash(final String hash) {
         if (StringUtils.isNotBlank(hash)) {
             List<User> list = getJdbcTemplate().query(
-                    "SELECT logins.user_id, users.nick, users.passw, users.banned, last_seen FROM logins " +
-                            "INNER JOIN users ON logins.user_id = users.id WHERE logins.hash = ?",
+                    "SELECT logins.user_id, u.nick, u.passw, u.banned, u.last_seen," +
+                            "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " +
+                            "FROM logins INNER JOIN users u ON logins.user_id = u.id " +
+                            "LEFT JOIN facebook f ON f.user_id = u.id " +
+                            "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " +
+                            "LEFT JOIN emails e ON e.user_id = u.id " +
+                            "WHERE logins.hash = ?",
                     new UserMapper(),
                     hash);
 
@@ -291,7 +326,12 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
     public int checkPassword(final String username, final String password) {
         if (StringUtils.isNotBlank(username)) {
             List<User> list = getJdbcTemplate().query(
-                    "SELECT id, nick, passw, banned, last_seen FROM users WHERE nick = ?",
+                    "SELECT u.id, u.nick, u.passw, u.banned, u.last_seen," +
+                            "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " +
+                            "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " +
+                            "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " +
+                            "LEFT JOIN emails e ON e.user_id = u.id " +
+                            "WHERE nick = ?",
                     new UserMapper(),
                     username);