package by.botyanov.library.dao;

import by.botyanov.library.dao.pool.ConnectionPool;
import by.botyanov.library.dao.pool.ProxyConnection;
import by.botyanov.library.domain.User;
import by.botyanov.library.domain.UserRole;
import org.apache.log4j.Logger;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class UserDao implements Dao<User> {
    private static final Logger LOG = Logger.getLogger(UserDao.class);

    private static final String REGISTER_USER = "INSERT INTO user (login, password, first_name, last_name, role) VALUES (?, ?, ?, ?, ?);";
    private static final String FETCH_ALL_USERS = "SELECT * FROM user;";
    private static final String FIND_USER_BY_LOGIN = "SELECT * FROM user WHERE login = ?;";
    private static final String FIND_USER_BY_ID = "SELECT * FROM user WHERE id = ?;";
    private static final String DELETE_USER_BY_ID = "DELETE FROM user WHERE id = ?";
    private static final String UPDATE_USER_INFO = "UPDATE user SET login = ?, password = ?, first_name = ?, last_name = ?, role = ?, blocked = ? WHERE id = ?;";
    private static final String BLOCK_USER_TOGGLE = "UPDATE user SET blocked = !blocked WHERE login = ?";

    private UserDao() {
    }

    private static class UserDaoHolder {
        private final static UserDao instance = new UserDao();
    }

    public static UserDao getInstance() {
        return UserDaoHolder.instance;
    }

    @Override
    public void add(User user) throws DAOException {
        String login = user.getLogin();
        String password = user.getPassword();
        String firstName = user.getFirstName();
        String lastName = user.getLastName();
        String role = user.getUserRole().toString().toLowerCase();
        try (ProxyConnection connection = ConnectionPool.getInstance().getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(REGISTER_USER)) {
            preparedStatement.setString(1, login);
            preparedStatement.setString(2, password);
            preparedStatement.setString(3, firstName);
            preparedStatement.setString(4, lastName);
            preparedStatement.setString(5, role);
            preparedStatement.executeUpdate();
            LOG.info("User " + login + " has been saved");
        } catch (SQLException e) {
            throw new DAOException(e);
        }
    }

    //UNUSED
    @Override
    public User findById(long id) throws DAOException {
        User user = null;
        try (ProxyConnection connection = ConnectionPool.getInstance().getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(FIND_USER_BY_ID)) {
            preparedStatement.setLong(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                user = new User();
                user.setId(id);
                user.setLogin(resultSet.getString("login"));
                user.setPassword(resultSet.getString("password"));
                user.setFirstName(resultSet.getString("first_name"));
                user.setLastName(resultSet.getString("last_name"));
                user.setUserRole(UserRole.valueOf(resultSet.getString("role")));
                user.setBlocked(resultSet.getBoolean("blocked"));
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        }
        return user;
    }

    @Override
    public List<User> getAll() throws DAOException {
        List<User> users = new ArrayList<>();
        User user;
        try (ProxyConnection connection = ConnectionPool.getInstance().getConnection();
             Statement statement = connection.createStatement()) {
            ResultSet resultSet = statement.executeQuery(FETCH_ALL_USERS);
            while (resultSet.next()) {
                user = new User(resultSet.getLong("id"), resultSet.getString("login"), resultSet.getString("password"),
                        resultSet.getString("first_name"), resultSet.getString("last_name"),
                        UserRole.valueOf(resultSet.getString("role").toUpperCase()), resultSet.getBoolean("blocked"));
                users.add(user);
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        }
        return users;
    }

    @Override
    public void update(User user) throws DAOException {
        try (ProxyConnection connection = ConnectionPool.getInstance().getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_USER_INFO)) {
            preparedStatement.setString(1, user.getLogin());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setString(3, user.getFirstName());
            preparedStatement.setString(4, user.getLastName());
            preparedStatement.setString(5, user.getUserRole().toString());
            preparedStatement.setBoolean(6, user.isBlocked());
            preparedStatement.setLong(7, user.getId());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new DAOException(e);
        }
    }

    //UNUSED
    @Override
    public void delete(long id) throws DAOException {
        try (ProxyConnection connection = ConnectionPool.getInstance().getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(DELETE_USER_BY_ID)) {
            preparedStatement.setLong(1, id);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new DAOException(e);
        }
    }

    public User findByLogin(String login) throws DAOException {
        LOG.debug("UserDao.findByLogin()");
        User user = null;
        try (ProxyConnection connection = ConnectionPool.getInstance().getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(FIND_USER_BY_LOGIN)) {
            preparedStatement.setString(1, login);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                user = new User(resultSet.getLong("id"),resultSet.getString("login"), resultSet.getString("password"),
                        resultSet.getString("first_name"), resultSet.getString("last_name"),
                        UserRole.valueOf(resultSet.getString("role").toUpperCase()),resultSet.getBoolean("blocked"));
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        }
        return user;
    }

    @Deprecated
    public void toggleBlockUser(String login) throws DAOException {
        LOG.debug("UserDao.toggleBlockUser()");
        try (ProxyConnection connection = ConnectionPool.getInstance().getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(BLOCK_USER_TOGGLE)) {
            preparedStatement.setString(1, login);
            LOG.debug(preparedStatement.toString());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new DAOException(e);
        }
    }

}