/* * Copyright (C) 2008-2020, Juick * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU Affero General Public License as * published by the Free Software Foundation, either version 3 of the * License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU Affero General Public License for more details. * * You should have received a copy of the GNU Affero General Public License * along with this program. If not, see <http://www.gnu.org/licenses/>. */ package com.juick.service; import com.juick.model.*; import com.juick.util.UsernameTakenException; import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.lang3.RandomStringUtils; import org.apache.commons.lang3.StringUtils; import org.apache.commons.lang3.tuple.Pair; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Value; import org.springframework.dao.DataIntegrityViolationException; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; import javax.annotation.Nonnull; import java.net.URI; import java.sql.*; import java.time.Instant; import java.time.ZoneOffset; import java.util.*; /** * Created by aalexeev on 11/13/16. */ @Repository public class UserServiceImpl extends BaseJdbcService implements UserService { private static final Logger logger = LoggerFactory.getLogger("UserService"); @Value("${juick.admin_users:}") List<String> adminUsers; private class UserMapper implements RowMapper<User> { @Override public User mapRow(@Nonnull ResultSet rs, int rowNum) throws SQLException { User user = new User(); user.setUid(rs.getInt(1)); user.setName(rs.getString(2)); user.setCredentials(rs.getString(3)); user.setBanned(rs.getInt(4) > 0); Timestamp seen = rs.getTimestamp(5); if (seen != null) { user.setSeen(seen.toInstant()); } user.setVerified(rs.getLong(6) > 0); return user; } } @Transactional @Override public String getSignUpHashByJID(final String jid) { List<String> list = getJdbcTemplate().queryForList( "SELECT loginhash FROM jids WHERE jid = ? AND user_id IS NULL", String.class, jid); if (list.isEmpty()) { String hash = UUID.randomUUID().toString(); getJdbcTemplate().update("INSERT INTO jids(jid, loginhash) VALUES (?, ?)", jid, hash); return hash; } return list.get(0); } @Transactional @Override public String getSignUpHashByTelegramID(final Long telegramId, final String username) { List<String> list = getJdbcTemplate().queryForList( "SELECT loginhash FROM telegram WHERE tg_id = ? AND user_id IS NULL", String.class, telegramId); if (list.isEmpty()) { String hash = UUID.randomUUID().toString(); getJdbcTemplate().update( "INSERT INTO telegram(tg_id, loginhash, tg_name) VALUES (?, ?, ?)", telegramId, hash, username); return hash; } return list.get(0); } @Transactional @Override public Optional<User> createUser(final String username, final String password) throws UsernameTakenException { KeyHolder holder = new GeneratedKeyHolder(); try { getJdbcTemplate().update( con -> { PreparedStatement stmt = con.prepareStatement( "INSERT INTO users(nick,passw) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS); stmt.setString(1, username); stmt.setString(2, password); return stmt; }, holder); } catch (DataIntegrityViolationException e) { throw new UsernameTakenException(); } int uid = holder.getKeys().size() > 1 ? ((Number)holder.getKeys().get("id")).intValue() : holder.getKey().intValue(); getJdbcTemplate().update("INSERT INTO useroptions(user_id) VALUES (?)", uid); getJdbcTemplate().update("INSERT INTO subscr_users(user_id, suser_id) VALUES (2, ?)", uid); return getUserByUID(uid); } @Transactional(readOnly = true) @Override public Optional<User> getUserByUID(final int uid) { List<User> list = getJdbcTemplate().query( "SELECT DISTINCT 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)); } @Transactional(readOnly = true) @Nonnull @Override public User getUserByName(final String username) { if (StringUtils.isNotBlank(username)) { List<User> list = getJdbcTemplate().query( "SELECT DISTINCT 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); } return AnonymousUser.INSTANCE; } @Transactional(readOnly = true) @Override public List<String> getUsernames() { return getJdbcTemplate().queryForList("SELECT nick FROM users", String.class); } @Override @Transactional(readOnly = true) @Nonnull public User getUserByEmail(String email) { if (StringUtils.isNotBlank(email)) { try { List<User> list = getJdbcTemplate().query( "SELECT DISTINCT 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); if (!list.isEmpty()) return list.get(0); } catch (DataIntegrityViolationException e) { logger.warn("Data exception with email {}", email); return AnonymousUser.INSTANCE; } } return AnonymousUser.INSTANCE; } @Transactional(readOnly = true) @Override public User getUserByJID(final String jid) { User result = null; if (StringUtils.isNotBlank(jid)) { List<User> list = getJdbcTemplate().query( "SELECT DISTINCT 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); if (!list.isEmpty()) result = list.get(0); } return result; } @Transactional(readOnly = true) @Override public List<User> getUsersByName(final Collection<String> unames) { if (CollectionUtils.isEmpty(unames)) return Collections.emptyList(); return getNamedParameterJdbcTemplate().query( "SELECT DISTINCT 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()); } @Transactional(readOnly = true) @Override public List<User> getUsersByID(final Collection<Integer> uids) { if (CollectionUtils.isEmpty(uids)) return Collections.emptyList(); return getNamedParameterJdbcTemplate().query( "SELECT DISTINCT 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()); } @Transactional(readOnly = true) @Override public List<String> getJIDsbyUID(final int uid) { return getJdbcTemplate().queryForList("SELECT jid FROM jids WHERE user_id = ? AND active = 1", String.class, uid); } @Transactional(readOnly = true) @Override public int getUIDbyName(final String uname) { if (StringUtils.isNotBlank(uname)) { List<Integer> list = getJdbcTemplate().queryForList( "SELECT id FROM users WHERE nick = ?", Integer.class, uname); if (!list.isEmpty()) return list.get(0); } return 0; } @Transactional(readOnly = true) @Override public User getUserByHash(final String hash) { if (StringUtils.isNotBlank(hash)) { List<User> list = getJdbcTemplate().query( "SELECT DISTINCT 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); if (!list.isEmpty()) { User user = list.get(0); user.setAuthHash(hash); return user; } } return AnonymousUser.INSTANCE; } @Transactional @Override public String getHashByUID(final int uid) { List<String> list = getJdbcTemplate().queryForList( "SELECT hash FROM logins WHERE user_id = ?", String.class, uid); if (list.isEmpty()) { String hash = RandomStringUtils.randomAlphanumeric(16).toUpperCase(); getJdbcTemplate().update("INSERT INTO logins(user_id, hash) VALUES (?, ?)", uid, hash); return hash; } return list.get(0); } @Transactional(readOnly = true) @Override public Optional<User> checkPassword(final String username, final String password) { if (StringUtils.isNotBlank(username)) { List<User> list = getJdbcTemplate().query( "SELECT DISTINCT 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); if (!list.isEmpty()) { User user = list.get(0); if (Objects.equals(password, user.getCredentials())) return Optional.of(user); } } return Optional.empty(); } @Transactional @Override public boolean updatePassword(final User user, final String newPassword) { return user != null && user.getUid() > 0 && getJdbcTemplate().update("UPDATE users SET passw = ? WHERE id = ?", newPassword, user.getUid()) > 0; } @Transactional(readOnly = true) @Override public int getUserOptionInt(final int uid, final String option, final int defaultValue) { if (StringUtils.isBlank(option)) return defaultValue; List<Integer> list = getJdbcTemplate().queryForList( "SELECT " + option + " FROM useroptions WHERE user_id = ?", Integer.class, uid); return list.isEmpty() ? defaultValue : list.get(0); } @Transactional @Override public int setUserOptionInt(final int uid, final String option, final int value) { if (StringUtils.isBlank(option)) return 0; return getJdbcTemplate().update("UPDATE useroptions SET " + option + "= ? WHERE user_id = ?", value, uid); } @Transactional(readOnly = true) @Override public User getUserInfo(final User user) { try { getJdbcTemplate().queryForObject( "SELECT fullname, country, url, descr FROM usersinfo WHERE user_id = ?", ((rs, rowNum) -> { user.setFullName(rs.getString(1)); user.setCountry(rs.getString(2)); user.setUrl(rs.getString(3)); user.setDescription(rs.getString(4)); return user; }), user.getUid()); } catch (EmptyResultDataAccessException e) { return user; } return user; } @Override public boolean updateUserInfo(final User info) { try { return getJdbcTemplate().update( "INSERT INTO usersinfo(user_id, fullname, country, url, descr) VALUES (?, ?, ?, ?, ?)", info.getUid(), info.getFullName(), info.getCountry(), info.getUrl(), info.getDescription()) > 0; } catch (Exception e) { // pgjdbc 42.x do not use SQLException return getJdbcTemplate().update("UPDATE usersinfo SET fullname = ?, country = ?, url = ?, descr = ? WHERE user_id = ?", info.getFullName(), info.getCountry(), info.getUrl(), info.getDescription(), info.getUid()) > 0; } } @Transactional(readOnly = true) @Override public boolean getCanMedia(final int uid) { List<Integer> list = getJdbcTemplate().queryForList( "SELECT users.lastphoto - UNIX_TIMESTAMP() FROM users WHERE id = ?", Integer.class, uid); return !list.isEmpty() && list.get(0) < 3600; } @Transactional(readOnly = true) @Override public boolean isInWL(final int uid, final int check) { List<Integer> list = getJdbcTemplate().queryForList( "SELECT 1 FROM wl_users WHERE user_id = ? AND wl_user_id = ?", Integer.class, uid, check); return !list.isEmpty() && list.get(0) == 1; } @Transactional(readOnly = true) @Override public boolean isInBL(final int uid, final int check) { List<Integer> list = getJdbcTemplate().queryForList( "SELECT 1 FROM bl_users WHERE user_id = ? AND bl_user_id = ?", Integer.class, uid, check); return !list.isEmpty() && list.get(0) == 1; } @Transactional(readOnly = true) @Override public boolean isInBLAny(final int uid, final int uid2) { List<Integer> list = getJdbcTemplate().queryForList( "SELECT 1 FROM bl_users WHERE (user_id = ? AND bl_user_id = ?) " + "OR (user_id = ? AND bl_user_id = ?)", Integer.class, uid, uid2, uid2, uid); return !list.isEmpty() && list.get(0) == 1; } @Transactional(readOnly = true) @Override public boolean isReplyToBL(final User user, final Message reply) { return getNamedParameterJdbcTemplate().queryForObject("WITH RECURSIVE banned(reply_id, user_id) AS (" + "SELECT reply_id, user_id FROM replies " + "WHERE replies.message_id = :mid " + "AND EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) " + "UNION ALL SELECT replies.reply_id, replies.user_id FROM replies " + "INNER JOIN banned ON banned.reply_id = replies.replyto " + "WHERE replies.message_id = :mid) " + "SELECT COUNT(reply_id) from replies " + "INNER JOIN messages m ON m.message_id = replies.message_id " + "WHERE replies.message_id = :mid " + "AND replies.reply_id = :rid " + "AND (EXISTS (SELECT 1 FROM banned WHERE banned.reply_id = replies.reply_id) " + "OR EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = m.user_id)" + "OR EXISTS (SELECT 1 FROM bl_users b WHERE b.bl_user_id = :uid AND b.user_id = m.user_id))", new MapSqlParameterSource("uid", user.getUid()) .addValue("mid", reply.getMid()) .addValue("rid", reply.getRid()), Integer.class) > 0; } @Transactional(readOnly = true) @Override public List<Integer> checkBL(final int visitor, final Collection<Integer> uids) { if (CollectionUtils.isEmpty(uids)) return Collections.emptyList(); return getNamedParameterJdbcTemplate().queryForList( "SELECT user_id FROM bl_users WHERE user_id = :visitor and bl_user_id IN (:ids)", new MapSqlParameterSource() .addValue("visitor", visitor) .addValue("ids", uids), Integer.class); } @Transactional(readOnly = true) @Override public boolean isSubscribed(final int uid, final int check) { List<Integer> list = getJdbcTemplate().queryForList( "SELECT 1 FROM subscr_users WHERE suser_id = ? AND user_id = ?", Integer.class, uid, check); return !list.isEmpty() && list.get(0) == 1; } @Transactional(readOnly = true) @Override public List<User> getUserReadLeastPopular(final int uid, final int cnt) { return getJdbcTemplate().query( "SELECT users.id,users.nick FROM (subscr_users " + "INNER JOIN users_subscr ON (subscr_users.suser_id=? " + "AND subscr_users.user_id=users_subscr.user_id)) INNER JOIN users " + "ON subscr_users.user_id=users.id ORDER BY cnt LIMIT ?", (rs, num) -> { User u = new User(); u.setUid(rs.getInt(1)); u.setName(rs.getString(2)); return u; }, uid, cnt); } @Transactional(readOnly = true) @Override public List<User> getUserReaders(final int uid) { return getNamedParameterJdbcTemplate().query( """ SELECT users.id, users.nick nick, '' user_uri FROM subscr_users INNER JOIN users ON subscr_users.suser_id=users.id WHERE subscr_users.user_id=:user_id UNION ALL SELECT 0, '', acct user_uri from followers WHERE user_id=:user_id ORDER BY nick """, new MapSqlParameterSource() .addValue("user_id", uid), (rs, num) -> { User u = new User(); u.setUid(rs.getInt(1)); u.setName(rs.getString(2)); u.setUri(URI.create(rs.getString(3))); return u; }); } @Transactional(readOnly = true) @Override public List<User> getUserFriends(final int uid) { return getJdbcTemplate().query( "SELECT users.id,users.nick FROM subscr_users " + "INNER JOIN users ON subscr_users.user_id=users.id " + "WHERE subscr_users.suser_id=? AND users.id!=? " + "ORDER BY users.nick", (rs, num) -> { User u = new User(); u.setUid(rs.getInt(1)); u.setName(rs.getString(2)); return u; }, uid, uid); } @Transactional(readOnly = true) @Override public Integer getUserRecommendations(User user) { try { return jdbcTemplate.queryForObject("SELECT COUNT(*) FROM favorites WHERE user_id=?", Integer.class, user.getUid()); } catch (EmptyResultDataAccessException e) { return 0; } } @Transactional(readOnly = true) @Override public List<User> getUserBLUsers(final int uid) { return getJdbcTemplate().query("SELECT users.id,users.nick FROM users INNER JOIN bl_users " + "ON(bl_users.bl_user_id=users.id) WHERE bl_users.user_id=? ORDER BY users.nick", (rs, num) -> { User u = new User(); u.setUid(rs.getInt(1)); u.setName(rs.getString(2)); return u; }, uid); } @Transactional @Override public boolean linkTwitterAccount( final User user, final String accessToken, final String accessTokenSecret, final String screenName) { return getJdbcTemplate().update("INSERT INTO twitter(user_id,access_token,access_token_secret,uname) " + "VALUES (?,?,?,?)", user.getUid(), accessToken, accessTokenSecret, screenName) > 0; } @Transactional(readOnly = true) @Override public int getStatsMessages(final int uid) { List<Integer> list = getJdbcTemplate().queryForList("SELECT COUNT(*) FROM messages WHERE user_id = ?", Integer.class, uid); return list.isEmpty() ? 0 : list.get(0); } @Transactional(readOnly = true) @Override public int getStatsReplies(final int uid) { List<Integer> list = getJdbcTemplate().queryForList("SELECT COUNT(*) FROM replies WHERE user_id = ?", Integer.class, uid); return list.isEmpty() ? 0 : list.get(0); } @Transactional @Override public boolean setActiveStatusForJID(final String JID, final UserService.ActiveStatus jidStatus) { User user = getUserByJID(JID); if (user != null) { int newStatus = jidStatus == UserService.ActiveStatus.Active ? 1 : 0; return getJdbcTemplate().update( "UPDATE jids SET active = ? WHERE user_id = ? AND jid = ?", newStatus, user.getUid(), JID) >= 0; } return false; } @Transactional(readOnly = true) @Override public List<String> getAllJIDs(final User user) { return getJdbcTemplate().queryForList( "SELECT jid FROM jids WHERE user_id=?", String.class, user.getUid()); } @Transactional(readOnly = true) @Override public List<AuthResponse> getAuthCodes(final User user) { return getJdbcTemplate().query( "SELECT account,authcode FROM auth WHERE user_id=? AND protocol='xmpp'", (rs, num) -> new AuthResponse(null, rs.getString(1), rs.getString(2)), user.getUid()); } @Transactional(readOnly = true) @Override public List<String> getEmails(final User user) { return getJdbcTemplate().queryForList("SELECT email FROM emails WHERE user_id=?", String.class, user.getUid()); } @Transactional(readOnly = true) @Override public String getEmailHash(final User user) { List<String> list = getJdbcTemplate().queryForList( "SELECT hash FROM mail WHERE user_id = ?", String.class, user.getUid()); return list.isEmpty() ? StringUtils.EMPTY : list.get(0) + "@mail.juick.com"; } @Transactional @Override public boolean deleteJID(int uid, String jid) { return getNamedParameterJdbcTemplate().update("DELETE FROM jids " + "WHERE (SELECT COUNT(*) cnt FROM (select user_id, jid FROM jids j) c WHERE user_id=:uid) > 1 " + "AND user_id=:uid AND jid=:jid", new MapSqlParameterSource() .addValue("uid", uid) .addValue("jid", jid)) > 0; } @Transactional @Override public boolean unauthJID(int uid, String jid) { return getJdbcTemplate() .update("DELETE FROM auth WHERE user_id=? AND protocol='xmpp' AND account=?", uid, jid) > 0; } @Transactional(readOnly = true) @Override public List<String> getActiveJIDs() { return getJdbcTemplate().queryForList("SELECT jid FROM jids WHERE active=1 AND loginhash IS NULL", String.class); } @Override public void updateLastSeen(User user) { getJdbcTemplate().update("UPDATE users SET last_seen=? WHERE id=?", Instant.now().atOffset(ZoneOffset.UTC), user.getUid()); } @Override public boolean isAdminUser(User user) { return adminUsers.contains(user.getName()); } @Transactional(readOnly = true) @Override public Optional<ExternalToken> getTwitterToken(final int uid) { List<ExternalToken> list = getJdbcTemplate().query( "SELECT uname, access_token, access_token_secret FROM twitter WHERE user_id = ? AND crosspost = true", (rs, num) -> new ExternalToken(rs.getString(1), "twitter", rs.getString(2), rs.getString(3)), uid); return list.isEmpty() ? Optional.empty() : Optional.of(list.get(0)); } @Transactional @Override public boolean deleteTwitterToken(Integer uid) { return getJdbcTemplate().update("DELETE FROM twitter WHERE user_id=?", uid) > 0; } @Override public void addFacebookState(String state, String redirectUri) { jdbcTemplate.update("INSERT INTO facebook(loginhash, fb_link) VALUES(?, ?)", state, redirectUri); } @Override public void addVKState(String state, String redirectUri) { jdbcTemplate.update("INSERT INTO vk(loginhash, vk_link) VALUES(?, ?)", state, redirectUri); } @Override public String verifyFacebookState(String state) { try { return jdbcTemplate.queryForObject("SELECT fb_link FROM facebook WHERE loginhash=?", String.class, state); } catch (EmptyResultDataAccessException e) { return StringUtils.EMPTY; } } @Override public String verifyVKState(String state) { try { return jdbcTemplate.queryForObject("SELECT vk_link FROM vk WHERE loginhash=?", String.class, state); } catch (EmptyResultDataAccessException e) { return StringUtils.EMPTY; } } @Transactional(readOnly = true) @Override public Optional<Pair<String, String>> getFacebookTokens(final int uid) { List<Optional<Pair<String, String>>> list = getJdbcTemplate().query( "SELECT fb_id, access_token FROM facebook WHERE user_id = ? AND access_token IS NOT NULL AND crosspost = true", (rs, num) -> Optional.of(Pair.of(rs.getString(1), rs.getString(2))), uid); return list.isEmpty() ? Optional.empty() : list.get(0); } @Transactional(readOnly = true) @Override public ApplicationStatus getFbCrossPostStatus(final int uid) { List<ApplicationStatus> list = getJdbcTemplate().query( "SELECT 1, crosspost FROM facebook WHERE user_id = ? LIMIT 1", (rs, num) -> { ApplicationStatus status = new ApplicationStatus(); status.setConnected(rs.getInt(1) > 0); status.setCrosspostEnabled(rs.getBoolean(2)); return status; }, uid); return list.isEmpty() ? new ApplicationStatus() : list.get(0); } @Transactional @Override public boolean enableFBCrosspost(Integer uid) { return getJdbcTemplate().update("UPDATE facebook SET crosspost=true WHERE user_id=?", uid) > 0; } @Transactional @Override public void disableFBCrosspost(Integer uid) { getJdbcTemplate().update("UPDATE facebook SET crosspost=false WHERE user_id=?", uid); } @Transactional(readOnly = true) @Override public String getTwitterName(final int uid) { List<String> list = getJdbcTemplate().queryForList( "SELECT uname FROM twitter WHERE user_id = ?", String.class, uid); return list.isEmpty() ? StringUtils.EMPTY : list.get(0); } @Transactional(readOnly = true) @Override public String getTelegramName(final int uid) { List<String> list = getJdbcTemplate().queryForList( "SELECT tg_name FROM telegram WHERE user_id = ?", String.class, uid); return list.isEmpty() ? StringUtils.EMPTY : list.get(0); } @Override @Transactional(readOnly = true) public Optional<User> getUserByTelegramId(Long id) { List<User> list = getJdbcTemplate().query( """ SELECT DISTINCT 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 t.tg_id = ?""", new UserMapper(), id ); return list.isEmpty() ? Optional.empty() : Optional.of(list.get(0)); } @Transactional(readOnly = true) @Override public Optional<Pair<String, String>> getVkTokens(final int uid) { List<Optional<Pair<String, String>>> list = getJdbcTemplate().query( "SELECT vk_id, access_token FROM vk WHERE user_id = ? AND crosspost = 1", (rs, num) -> Optional.of(Pair.of(rs.getString(1), rs.getString(2))), uid); return list.isEmpty() ? Optional.empty() : list.get(0); } @Transactional @Override public void deleteVKUser(Integer uid) { getJdbcTemplate().update("DELETE FROM vk WHERE user_id=?", uid); } @Transactional(readOnly = true) @Override public Optional<User> getUserByFacebookId(long facebookId) { List<User> list = getJdbcTemplate().query( "SELECT DISTINCT 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 f.fb_id = ?", new UserMapper(), facebookId); return list.isEmpty() ? Optional.empty() : Optional.of(list.get(0)); } @Transactional @Override public boolean createFacebookUser(long fbID, String loginhash, String token, String fbName) { return getJdbcTemplate().update("UPDATE facebook SET fb_id=?, access_token=?, fb_name=? WHERE loginhash=?", fbID, token, fbName, loginhash) > 0; } @Transactional @Override public boolean updateFacebookUser(long fbID, String token, String fbName) { return getJdbcTemplate().update("UPDATE facebook SET access_token=?,fb_name=? WHERE fb_id=?", token, fbName, fbID) > 0; } @Transactional(readOnly = true) @Override public int getUIDbyVKID(long vkID) { try { return getJdbcTemplate().queryForObject("SELECT user_id FROM vk WHERE vk_id=? AND user_id IS NOT NULL", Integer.class, vkID); } catch (EmptyResultDataAccessException e) { return 0; } } @Transactional @Override public boolean createVKUser(long vkID, String loginhash, String token, String vkName, String vkLink) { return getJdbcTemplate().update("INSERT INTO vk(vk_id,loginhash,access_token,vk_name,vk_link) VALUES (?,?,?,?,?)", vkID, loginhash, token, vkName, vkLink) > 0; } @Transactional(readOnly = true) @Override public String getFacebookNameByHash(String hash) { try { List<Pair<String, String>> fb = getJdbcTemplate().query("SELECT fb_name,fb_link FROM facebook WHERE loginhash=?", (rs, num) -> Pair.of(rs.getString(1), rs.getString(2)), hash); if (fb.size() > 0) { return "<a href=\"" + fb.get(0).getRight() + "\" rel=\"nofollow\">" + fb.get(0).getLeft() + "</a>"; } return null; } catch (EmptyResultDataAccessException e) { return null; } } @Transactional @Override public String getTelegramNameByHash(String hash) { try { String name = getJdbcTemplate().queryForObject("SELECT tg_name FROM telegram WHERE loginhash=?", String.class, hash); return "<a href=\"https://telegram.me/" + name + "\" rel=\"nofollow\">" + name + "</a>"; } catch (EmptyResultDataAccessException e) { return null; } } @Transactional @Override public boolean setFacebookUser(String hash, int uid) { return getJdbcTemplate().update("UPDATE facebook SET user_id=?,loginhash=NULL WHERE loginhash=?", uid, hash) > 0; } @Transactional @Override public String getVKNameByHash(String hash) { List<Pair<String, String>> logins = getJdbcTemplate().query("SELECT vk_name,vk_link FROM vk WHERE loginhash=?", (rs, num) -> Pair.of(rs.getString(1), rs.getString(2)), hash); if (logins.size() > 0) { return "<a href=\"http://vk.com/" + logins.get(0).getRight() + "\" rel=\"nofollow\">" + logins.get(0).getLeft() + "</a>"; } return null; } @Transactional @Override public boolean setVKUser(String hash, int uid) { return getJdbcTemplate().update("UPDATE vk SET user_id=?,loginhash=NULL WHERE loginhash=?", uid, hash) > 0; } @Transactional @Override public boolean setTelegramUser(String hash, int uid) { return getJdbcTemplate().update("UPDATE telegram SET user_id=?,loginhash=NULL WHERE loginhash=?", uid, hash) > 0; } @Transactional(readOnly = true) @Override public String getJIDByHash(String hash) { try { return getJdbcTemplate().queryForObject("SELECT jid FROM jids WHERE loginhash=?", String.class, hash); } catch (EmptyResultDataAccessException e) { return null; } } @Transactional @Override public boolean setJIDUser(String hash, int uid) { return getJdbcTemplate().update("UPDATE jids SET user_id=?,loginhash=NULL WHERE loginhash=?", uid, hash) > 0; } @Transactional(readOnly = true) @Override public boolean canDeleteTelegramUser(User user) { return getEmails(user).size() > 0 || getFbCrossPostStatus(user.getUid()).isConnected() || getVkTokens(user.getUid()).isPresent(); } }