/*
* Copyright (C) 2008-2023, 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 jakarta.annotation.Nonnull;
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.DataAccessException;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.UncategorizedSQLException;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import java.net.URI;
import java.sql.*;
import java.time.Instant;
import java.time.OffsetDateTime;
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);
OffsetDateTime seen = UserServiceImpl.this.getOffsetDateTime(rs, 5);
if (seen != null) {
user.setSeen(seen.toInstant());
}
user.setVerified(rs.getLong(6) > 0);
user.setPremium(rs.getInt(7) > 0);
user.setAdmin(adminUsers.contains(user.getName()));
return user;
}
}
private final UserMapper userMapper = new UserMapper();
@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 {
var params = new MapSqlParameterSource();
params.addValue("nick", username);
params.addValue("passw", password);
try {
Integer uid = getNamedParameterJdbcTemplate().execute(returningId("INSERT INTO users(nick, passw)", "VALUES (:nick, :passw)", "id"),
params, ps -> {
try (var resultSet = ps.executeQuery()) {
if (resultSet.next()) {
return resultSet.getInt(1);
}
return 0;
}
});
if (uid > 0) {
getJdbcTemplate().update("INSERT INTO subscr_users(user_id, suser_id) VALUES (2, ?)", uid);
return getUserByUID(uid);
}
} catch (Exception e) {
return Optional.empty();
}
return Optional.empty();
}
@Transactional(readOnly = true)
@Override
public Optional<User> getUserByUID(final int uid) {
var 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, premium
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 = ?""", 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)) {
var 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, premium
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 = ?""", 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, premium " +
"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 = ?)",
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, premium " +
"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 = ?)",
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, premium " +
"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),
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, premium " +
"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),
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)
@Nonnull
@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, premium " +
"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 = ?",
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, premium " +
"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 = ?",
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 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 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) {
if (check > 0) {
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;
}
return false;
}
@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) {
var replies = getNamedParameterJdbcTemplate().queryForList(withRecursive() + " 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);
return !replies.isEmpty() && replies.get(0) > 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, (select count(*) from subscr_users where user_id=users.id) cnt
FROM subscr_users INNER JOIN users
ON subscr_users.user_id=users.id WHERE subscr_users.suser_id=? ORDER by cnt
""" + limit(cnt),
(rs, num) -> {
User u = new User();
u.setUid(rs.getInt(1));
u.setName(rs.getString(2));
return u;
},
uid);
}
@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 List<User> getUserIgnoredUsers(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(readOnly = true)
@Override
public List<User> getUserVipUsers(final int uid) {
return getJdbcTemplate().query("SELECT users.id,users.nick FROM users INNER JOIN wl_users " +
"ON(wl_users.wl_user_id=users.id) WHERE wl_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 refreshToken, final String screenName) {
return getJdbcTemplate().update("INSERT INTO twitter(user_id,access_token,access_token_secret,refresh_token,uname) " +
"VALUES (?,?,'',?,?)",
user.getUid(), accessToken, refreshToken, screenName) > 0;
}
@Transactional
@Override
public boolean refreshTwitterToken(
final User user, final String accessToken, final String refreshToken) {
return getJdbcTemplate().update("UPDATE twitter SET access_token=?, refresh_token=?" +
" WHERE user_id=?",
accessToken, refreshToken, user.getUid()) > 0;
}
@Transactional(readOnly = true)
@Override
public boolean isTwitter1User(User user) {
return jdbcTemplate.queryForList("SELECT user_id FROM twitter WHERE user_id=? AND refresh_token=''", Integer.class, user.getUid()).size() > 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
@Override
public void logout(int uid) {
getJdbcTemplate().update("DELETE FROM logins WHERE user_id=?", uid);
}
@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
@Transactional
public void updateLastSeen(User user) {
getJdbcTemplate().update("UPDATE users SET last_seen=? WHERE id=?", toDateTime(Instant.now().atOffset(ZoneOffset.UTC)), user.getUid());
}
@Transactional(readOnly = true)
@Override
public Optional<ExternalToken> getTwitterToken(final int uid) {
List<ExternalToken> list = getJdbcTemplate().query(
"SELECT uname, access_token, refresh_token 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 = ?",
(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, premium
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 = ?""",
userMapper, id
);
return list.isEmpty() ? Optional.empty() : Optional.of(list.get(0));
}
@Transactional(readOnly = true)
@Override
public Pair<String, String> getVkTokens(int uid) {
var result = getNamedParameterJdbcTemplate().query(
"SELECT vk_id, access_token FROM vk WHERE user_id=:uid AND crosspost = 1 AND access_token <> ''",
new MapSqlParameterSource()
.addValue("uid", uid),
(rs, num) -> Pair.of(rs.getString(1), rs.getString(2)));
return result.isEmpty() ? null : result.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, premium " +
"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) {
var users = getJdbcTemplate().queryForList("""
SELECT user_id FROM vk WHERE vk_id=? AND user_id IS NOT NULL""", Integer.class, vkID);
return users.isEmpty() ? 0 : users.get(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
@Override
public boolean updateVkUser(long vkID, String token, String vkName, String vkLink) {
return getJdbcTemplate().update("UPDATE vk SET access_token=?,vk_name=?,vk_link=? WHERE vk_id=?",
token, vkName, vkLink, vkID) > 0;
}
@Transactional
@Override
public boolean updateVkToken(long userId, String token) {
return getJdbcTemplate().update("UPDATE vk SET access_token=? WHERE user_id=?",
token, userId) > 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()) != null;
}
private class TokenMapper implements RowMapper<ExternalToken> {
@Override
public ExternalToken mapRow(ResultSet rs, int rowNum) throws SQLException {
return new ExternalToken(
null,
rs.getString("service_type"),
rs.getString("regid"),
null
);
}
}
private final TokenMapper tokenMapper = new TokenMapper();
@Transactional(readOnly = true)
@Override
public Collection<ExternalToken> getToken(final int uid, final String serviceType) {
return getJdbcTemplate().query(
"SELECT regid, service_type FROM user_services WHERE user_id=? AND service_type=?",
tokenMapper,
uid, serviceType);
}
@Transactional(readOnly = true)
@Override
public Collection<ExternalToken> getTokens(final Collection<Integer> uids) {
if (CollectionUtils.isEmpty(uids))
return Collections.emptyList();
return getNamedParameterJdbcTemplate().query(
"""
SELECT regid, service_type FROM user_services INNER JOIN users
ON (users.id = user_services.user_id) WHERE users.id IN (:ids)""",
new MapSqlParameterSource("ids", uids),
tokenMapper);
}
@Transactional
@Override
public boolean addToken(final Integer uid, final String serviceType, final String token) {
try {
return getJdbcTemplate().update(
"INSERT INTO user_services(user_id, regid, service_type) VALUES (?, ?, ?)",
uid, token, serviceType) > 0;
} catch (DataIntegrityViolationException e) {
return false;
}
}
@Transactional
@Override
public boolean deleteToken(final String serviceType, final String token) {
return getJdbcTemplate().update("DELETE FROM user_services WHERE regid=? AND service_type=?", token, serviceType) > 0;
}
@Transactional
@Override
public void setPremium(final Integer uid, boolean isPremium) {
getJdbcTemplate().update("UPDATE users SET premium=? WHERE id=?", isPremium ? 1 : 0, uid);
}
}