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.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * Created by vt on 10/01/16.
 */
public class ShowQueries {

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

    public static List<String> getRecommendedUsers(Connection sql, User forUser) {
        List<String> result = new ArrayList<>();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        try {
            preparedStatement = sql.prepareStatement("SELECT users.nick FROM subscr_users INNER JOIN users " +
                    "ON subscr_users.user_id=users.id " +
                    "WHERE subscr_users.user_id NOT IN (SELECT user_id FROM subscr_users WHERE suser_id=?) " +
                    "AND subscr_users.suser_id IN (SELECT user_id FROM subscr_users WHERE suser_id=?) " +
                    "AND subscr_users.user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?) " +
                    "AND subscr_users.user_id!=? AND users.lastmessage>UNIX_TIMESTAMP()-259200 " +
                    "GROUP BY subscr_users.user_id ORDER BY count(*) DESC LIMIT 10");
            preparedStatement.setInt(1, forUser.UID);
            preparedStatement.setInt(2, forUser.UID);
            preparedStatement.setInt(3, forUser.UID);
            preparedStatement.setInt(4, forUser.UID);
            rs = preparedStatement.executeQuery();
            rs.beforeFirst();
            while (rs.next()) {
                result.add(rs.getString(1));
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql error", e);
        } finally {
            Utils.finishSQL(rs, preparedStatement);
        }
        return result;
    }

    public static List<String> getTopUsers(Connection sql) {
        List<String> result = new ArrayList<>();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        try {
            preparedStatement = sql.prepareStatement("SELECT users.nick,COUNT(subscr_users.suser_id) AS cnt " +
                    "FROM (subscr_users INNER JOIN users ON subscr_users.user_id=users.id) " +
                    "INNER JOIN useroptions ON users.id=useroptions.user_id " +
                    "WHERE useroptions.privacy_view>0 AND users.lastmessage>UNIX_TIMESTAMP()-259200 " +
                    "AND users.id!=2 GROUP BY subscr_users.user_id ORDER BY cnt DESC LIMIT 10");
            rs = preparedStatement.executeQuery();
            rs.beforeFirst();
            while (rs.next()) {
                result.add(rs.getString(1));
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql error", e);
        } finally {
            Utils.finishSQL(rs, preparedStatement);
        }
        return result;
    }
}