path: root/src/main/java/com/juick/server/UserQueries.java

/*
 * Juick
 * Copyright (C) 2008-2011, Ugnich Anton
 *
 * 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.server;

import com.juick.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
import java.util.UUID;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Ugnich Anton
 */
public class UserQueries {

    static final String ABCDEF = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";

    private static Logger logger = Logger.getLogger(UserQueries.class.getName());

    public static String getSignUpHashByJID(Connection sql, String jid) {
        String hash = SQLHelpers.getString(sql, "SELECT loginhash FROM jids WHERE jid=? AND user_id IS NULL", jid);

        if (hash == null) {
            hash = UUID.randomUUID().toString();
            PreparedStatement stmt = null;
            try {
                stmt = sql.prepareStatement("INSERT INTO jids(jid,loginhash) VALUES (?,?)");
                stmt.setString(1, jid);
                stmt.setString(2, hash);
                stmt.executeUpdate();
            } catch (SQLException e) {
                logger.log(Level.SEVERE, "sql exception", e);
            } finally {
                Utils.finishSQL(null, stmt);
            }
        }

        return hash;
    }

    public static int createUser(Connection sql, String username, String password) {
        int uid = 0;

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("INSERT INTO users(nick,passw) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS);
            stmt.setString(1, username);
            stmt.setString(2, password);
            stmt.executeUpdate();
            rs = stmt.getGeneratedKeys();
            if (rs.first()) {
                uid = rs.getInt(1);
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }

        SQLHelpers.executeInt(sql, "INSERT INTO useroptions(user_id) VALUES (?)", uid);
        SQLHelpers.executeInt(sql, "INSERT INTO subscr_users(user_id,suser_id) VALUES (2,?)", uid);

        return uid;
    }

    public static com.juick.User getUserByUID(Connection sql, int uid) {
        com.juick.User user = null;

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT nick,banned FROM users WHERE id=?");
            stmt.setInt(1, uid);
            rs = stmt.executeQuery();
            if (rs.first()) {
                user = new com.juick.User();
                user.UID = uid;
                user.UName = rs.getString(1);
                user.Banned = rs.getBoolean(2);
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return user;
    }

    public static com.juick.User getUserByName(Connection sql, String username) {
        com.juick.User user = null;

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT id,nick,banned FROM users WHERE nick=?");
            stmt.setString(1, username);
            rs = stmt.executeQuery();
            if (rs.first()) {
                user = new com.juick.User();
                user.UID = rs.getInt(1);
                user.UName = rs.getString(2);
                user.Banned = rs.getBoolean(3);
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return user;
    }

    public static com.juick.User getUserByJID(Connection sql, String jid) {
        com.juick.User user = null;

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT id,nick FROM users WHERE id=(SELECT user_id FROM jids WHERE jid=?)");
            stmt.setString(1, jid);
            rs = stmt.executeQuery();
            if (rs.first()) {
                user = new com.juick.User();
                user.UID = rs.getInt(1);
                user.UName = rs.getString(2);
                user.JID = jid;
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return user;
    }

    public static List<com.juick.User> getUsersByName(Connection sql, List<String> unames) {
        List<com.juick.User> users = new ArrayList<>();

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT id,nick FROM users WHERE nick IN (" + Utils.convertArrayString2String(unames) + ")");
            rs = stmt.executeQuery();
            rs.beforeFirst();
            while (rs.next()) {
                com.juick.User user = new com.juick.User();
                user.UID = rs.getInt(1);
                user.UName = rs.getString(2);
                users.add(user);
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return users;
    }

    public static List<com.juick.User> getUsersByID(Connection sql, List<Integer> uids) {
        List<com.juick.User> users = new ArrayList<>();

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT id,nick FROM users WHERE id IN (" + Utils.convertArrayInt2String(uids) + ")");
            rs = stmt.executeQuery();
            rs.beforeFirst();
            while (rs.next()) {
                com.juick.User u = new com.juick.User();
                u.UID = rs.getInt(1);
                u.UName = rs.getString(2);
                users.add(u);
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }

        return users;
    }

    public static boolean fillUsersByID(Connection sql, List<com.juick.User> users) {
        boolean ret = false;

        String uids = "";
        final int usersSize = users.size();
        for (int i = 0; i < usersSize; i++) {
            if (i > 0) {
                uids += ",";
            }
            uids += users.get(i).UID;
        }

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT id,nick FROM users WHERE id IN (" + uids + ")");
            rs = stmt.executeQuery();
            rs.beforeFirst();
            while (rs.next()) {
                int uid = rs.getInt(1);
                for (int i = 0; i < usersSize; i++) {
                    if (users.get(i).UID == uid) {
                        users.get(i).UName = rs.getString(2);
                        ret = true;
                    }
                }
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }

        return ret;
    }

    public static List<com.juick.User> getUsersByJID(Connection sql, List<String> jids) {
        List<com.juick.User> users = new ArrayList<>();

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT users.id,users.nick,jids.jid FROM users INNER JOIN jids ON jids.user_id=users.id WHERE jids.jid IN (" + Utils.convertArrayString2String(jids) + ")");
            rs = stmt.executeQuery();
            rs.beforeFirst();
            while (rs.next()) {
                com.juick.User user = new com.juick.User();
                user.UID = rs.getInt(1);
                user.UName = rs.getString(2);
                user.JID = rs.getString(3);
                users.add(user);
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return users;
    }

    public static String getJIDbyUID(Connection sql, int uid) {
        return SQLHelpers.getString(sql, "SELECT jid FROM jids WHERE user_id=? AND active=1", uid);
    }

    public static int getUIDbyJID(Connection sql, String jid) {
        return SQLHelpers.getInt(sql, "SELECT user_id FROM jids WHERE jid=?", jid, 0);
    }

    public static int getUIDbyName(Connection sql, String uname) {
        return SQLHelpers.getInt(sql, "SELECT id FROM users WHERE nick=?", uname, 0);
    }

    public static int getUIDbyHash(Connection sql, String hash) {
        return SQLHelpers.getInt(sql, "SELECT user_id FROM logins WHERE hash=?", hash, 0);
    }

    public static com.juick.User getUserByHash(Connection sql, String hash) {
        com.juick.User user = null;

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT logins.user_id,users.nick, users.banned FROM logins INNER JOIN users ON logins.user_id=users.id WHERE logins.hash=?");
            stmt.setString(1, hash);
            rs = stmt.executeQuery();
            if (rs.first()) {
                user = new com.juick.User();
                user.UID = rs.getInt(1);
                user.UName = rs.getString(2);
                user.Banned = rs.getBoolean(3);
                user.AuthHash = hash;
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return user;
    }

    public static String getHashByUID(Connection sql, int uid) {
        String hash = SQLHelpers.getString(sql, "SELECT hash FROM logins WHERE user_id=?", uid);

        if (hash == null) {
            hash = generateHash(16);
            PreparedStatement stmt = null;
            try {
                stmt = sql.prepareStatement("INSERT INTO logins(user_id,hash) VALUES (?,?)");
                stmt.setInt(1, uid);
                stmt.setString(2, hash);
                stmt.executeUpdate();
            } catch (SQLException e) {
                logger.log(Level.SEVERE, "sql exception", e);
            } finally {
                Utils.finishSQL(null, stmt);
            }
        }

        return hash;
    }

    public static String generateHash(int len) {
        Random rnd = new Random();
        StringBuilder sb = new StringBuilder(len);
        for (int i = 0; i < len; i++) {
            sb.append(ABCDEF.charAt(rnd.nextInt(ABCDEF.length())));
        }
        return sb.toString();
    }

    public static boolean checkUserNameValid(String uname) {
        return uname != null && uname.length() >= 2 && uname.length() <= 16 && uname.matches("[a-zA-Z0-9\\-]+");
    }

    public static int checkPassword(Connection sql, String username, String password) {
        int uid = 0;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT id,passw FROM users WHERE nick=?");
            stmt.setString(1, username);
            rs = stmt.executeQuery();
            if (rs.first()) {
                if (password.equals(rs.getString(2))) {
                    uid = rs.getInt(1);
                } else {
                    uid = -1;
                }
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return uid;
    }

    public static int getUserOptionInt(Connection sql, int uid, String option, int defaultValue) {
        int ret = defaultValue;

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT " + option + " FROM useroptions WHERE user_id=?");
            stmt.setInt(1, uid);
            rs = stmt.executeQuery();
            if (rs.first()) {
                ret = rs.getInt(1);
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return ret;
    }

    public static void setUserOptionInt(Connection sql, int uid, String option, int value) {
        PreparedStatement stmt = null;
        try {
            stmt = sql.prepareStatement("UPDATE useroptions SET " + option + "=? WHERE user_id=?");
            stmt.setInt(1, value);
            stmt.setInt(2, uid);
            stmt.executeUpdate();
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(null, stmt);
        }
    }

    public static boolean getCanMedia(Connection sql, int uid) {
        boolean ret = false;

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT users.lastphoto-UNIX_TIMESTAMP() FROM users WHERE id=?");
            stmt.setInt(1, uid);
            rs = stmt.executeQuery();
            if (rs.first()) {
                ret = rs.getInt(1) < 3600;
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return ret;
    }

    public static boolean isInWL(Connection sql, int uid, int check) {
        boolean ret = false;

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT 1 FROM wl_users WHERE user_id=? AND wl_user_id=?");
            stmt.setInt(1, uid);
            stmt.setInt(2, check);
            rs = stmt.executeQuery();
            if (rs.first()) {
                ret = rs.getInt(1) == 1;
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return ret;
    }

    public static boolean isInBL(Connection sql, int uid, int check) {
        boolean ret = false;

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT 1 FROM bl_users WHERE user_id=? AND bl_user_id=?");
            stmt.setInt(1, uid);
            stmt.setInt(2, check);
            rs = stmt.executeQuery();
            if (rs.first()) {
                ret = rs.getInt(1) == 1;
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return ret;
    }

    public static boolean isInBLAny(Connection sql, int uid, int uid2) {
        boolean ret = false;

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT 1 FROM bl_users WHERE (user_id=? AND bl_user_id=?) OR (user_id=? AND bl_user_id=?)");
            stmt.setInt(1, uid);
            stmt.setInt(2, uid2);
            stmt.setInt(3, uid2);
            stmt.setInt(4, uid);
            rs = stmt.executeQuery();
            if (rs.first()) {
                ret = rs.getInt(1) == 1;
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return ret;
    }

    public static List<Integer> checkBL(Connection sql, int visitor, List<Integer> uids) {
        List<Integer> ret = new ArrayList<>();

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT user_id FROM bl_users WHERE bl_user_id=? and user_id IN (" + Utils.convertArrayInt2String(uids) + ")");
            stmt.setInt(1, visitor);
            rs = stmt.executeQuery();
            rs.beforeFirst();
            while (rs.next()) {
                ret.add(rs.getInt(1));
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }

        return ret;
    }

    public static boolean isSubscribed(Connection sql, int uid, int check) {
        boolean ret = false;

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("SELECT 1 FROM subscr_users WHERE suser_id=? AND user_id=?");
            stmt.setInt(1, uid);
            stmt.setInt(2, check);
            rs = stmt.executeQuery();
            if (rs.first()) {
                ret = rs.getInt(1) == 1;
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return ret;
    }

    public static List<Integer> getUserRead(Connection sql, int uid) {
        return SQLHelpers.getArrayInteger(sql, "SELECT user_id FROM subscr_users WHERE suser_id=?", uid);
    }

    public static List<com.juick.User> getUserReadLeastPopular(Connection sql, int uid, int cnt) {
        List<com.juick.User> users = new ArrayList<>(cnt);

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("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 ?");
            stmt.setInt(1, uid);
            stmt.setInt(2, cnt);
            rs = stmt.executeQuery();
            rs.beforeFirst();
            while (rs.next()) {
                com.juick.User u = new com.juick.User();
                u.UID = rs.getInt(1);
                u.UName = rs.getString(2);
                users.add(u);
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }

        return users;
    }

    public static List<Integer> getUserReaders(Connection sql, int uid) {
        return SQLHelpers.getArrayInteger(sql, "SELECT suser_id FROM subscr_users WHERE user_id=?", uid);
    }

    public static List<com.juick.User> getUserBLUsers(Connection sql, int uid) {
        List<com.juick.User> users = new ArrayList<>();

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement("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");
            stmt.setInt(1, uid);
            rs = stmt.executeQuery();
            rs.beforeFirst();
            while (rs.next()) {
                com.juick.User u = new com.juick.User();
                u.UID = rs.getInt(1);
                u.UName = rs.getString(2);
                users.add(u);
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }

        return users;
    }

    public static boolean linkTwitterAccount(Connection sql, User user, String accessToken,
                                             String accessTokenSecret, String screenName) {
        try {
            PreparedStatement stmt =
                    sql.prepareStatement("INSERT INTO twitter(user_id,access_token,access_token_secret,uname) " +
                            "VALUES (?,?,?,?)" +
                            " ON DUPLICATE KEY UPDATE access_token=?,access_token_secret=?,uname=?");
            stmt.setInt(1, user.UID);
            stmt.setString(2, accessToken);
            stmt.setString(3, accessTokenSecret);
            stmt.setString(4, screenName);
            stmt.setString(5, accessToken);
            stmt.setString(6, accessTokenSecret);
            stmt.setString(7, screenName);
            if (stmt.execute()) {
                PreparedStatement stmt2 =
                        sql.prepareStatement("INSERT INTO subscr_users(user_id,suser_id,jid) " +
                                "VALUES (?,1741,'juick\\@twitter.juick.com')");
                stmt2.setInt(1, user.UID);
                return stmt2.execute();
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        }
        return false;
    }

    public static int getStatsIRead(Connection sql, int uid) {
        return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM subscr_users WHERE suser_id=?", uid, 0);
    }

    public static int getStatsMyReaders(Connection sql, int uid) {
        return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM subscr_users WHERE user_id=?", uid, 0);
    }

    public static int getStatsMessages(Connection sql, int uid) {
        return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM messages WHERE user_id=?", uid, 0);
    }

    public static int getStatsReplies(Connection sql, int uid) {
        return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM replies WHERE user_id=?", uid, 0);
    }

    public enum ActiveStatus {
        Inactive,
        Active
    }

    public static boolean setActiveStatusForJID(Connection sql, String JID, ActiveStatus jidStatus) {
        User user = getUserByJID(sql, JID);
        if (user != null) {
            PreparedStatement preparedStatement = null;
            try {
                preparedStatement = sql.prepareStatement(
                        "UPDATE jids SET active=? WHERE user_id=? AND jid=?");
                int newStatus = jidStatus == ActiveStatus.Active ? 1 : 0;
                preparedStatement.setInt(1, newStatus);
                preparedStatement.setInt(2, user.UID);
                preparedStatement.setString(3, JID);
                return preparedStatement.executeUpdate() >= 0;
            } catch (SQLException e) {
                logger.log(Level.SEVERE, "sql exception", e);
            } finally {
                Utils.finishSQL(null, preparedStatement);
            }
        }
        return false;
    }
}