package dal;

import dal.dto.IUserDTO;
import dal.dto.UserDTO;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDAOImpls164863 implements IUserDAO {
    private Connection createConnection() throws SQLException {
        return  DriverManager.getConnection("jdbc:mysql://ec2-52-30-211-3.eu-west-1.compute.amazonaws.com/s164863?"
                + "user=s164863&password=LPUj5vpaQZepYQgtCtdWR");
    }


    @Override
    public void createUser(IUserDTO user) throws DALException {
        try (Connection c = createConnection()){

            String query = "INSERT INTO users (id, userName, ini) " +
                    "VALUES(?,?,?)";
            PreparedStatement PS = c.prepareStatement(query);
            PS.setInt(1, user.getUserId());
            PS.setString(2, user.getUserName());
            PS.setString(3, user.getIni());
            PS.executeUpdate();
            List<String> roles = user.getRoles();
            int i = 0;
            while(i < roles.size()){
                String query2 = "INSERT INTO userRoles (id, roles) " +
                        "VALUES(?,?)";
                PreparedStatement PS2 = c.prepareStatement(query2);
                PS2.setInt(1, user.getUserId());
                PS2.setString(2, roles.get(i));
                PS2.executeUpdate();
                i++;
            }
            c.close();


        } catch (SQLException e) {
            e.printStackTrace();
            throw new DALException("Error creating user");
        }
    }

    @Override
    public IUserDTO getUser(int userId) throws DALException {
        try{
            Connection c = createConnection();

           IUserDTO user = new UserDTO();
           String query = "SELECT * " +
                   "FROM users " +
                   "JOIN userRoles USING (id) " +
                   "WHERE id = ?";
           PreparedStatement psQuery = c.prepareStatement(query);
           psQuery.setInt(1, userId);
           ResultSet resultSet = psQuery.executeQuery();

           ArrayList<String> roleList = new ArrayList<>();
           if(resultSet.first()) {
               user.setUserId(resultSet.getInt("id"));
               user.setUserName(resultSet.getString("userName"));
               user.setIni(resultSet.getString("ini"));
               roleList.add(resultSet.getString("roles"));
           }
           while(resultSet.next()){
               roleList.add(resultSet.getString("roles"));
           }
           user.setRoles(roleList);
           c.close();
           return user;
        } catch (SQLException e) {
            throw new DALException(e.getMessage());
        }

    }



    @Override
    public List<IUserDTO> getUserList() throws DALException {
        List<IUserDTO> list = new ArrayList<>();
        try(Connection c = createConnection()){
            //Get all users data
            String resQuery = "SELECT * " +
                    "FROM users";
            PreparedStatement psResQ = c.prepareStatement(resQuery);
            ResultSet rs = psResQ.executeQuery();

            UserDTO user;
            if(rs.first()){
                do {
                    user = new UserDTO();
                    //Get user data
                    user.setUserId(rs.getInt("id"));
                    user.setUserName(rs.getString("userName"));
                    user.setIni(rs.getString("ini"));
                    //Get roles
                    List<String> roleList = new ArrayList<>();

                    String query = "SELECT * " +
                            "FROM userRoles " +
                            "WHERE id = ?";
                    PreparedStatement psQuery = c.prepareStatement(query);
                    psQuery.setInt(1, user.getUserId());
                    ResultSet rsRoles = psQuery.executeQuery();
                    //Add roles
                    rsRoles.beforeFirst();
                        while (rsRoles.next()) {
                            roleList.add(rsRoles.getString("roles"));
                        }
                        user.setRoles(roleList);

                    list.add(user);
                }while(rs.next());

            }
            c.close();
        }catch(SQLException e){
            throw new DALException("Error getting user list");
        }
        return list;
    }



    @Override
    public void updateUser(IUserDTO user) throws DALException {
        //For roles nuke all of the users roles then add new ones
        try(Connection c = createConnection()){
            String query = "UPDATE users " +
                    "SET id = ?, userName = ?, ini = ? " +
                    "WHERE id = ?";
            PreparedStatement psQuery = c.prepareStatement(query);
            psQuery.setInt(1, user.getUserId());
            psQuery.setString(2, user.getUserName());
            psQuery.setString(3, user.getIni());
            psQuery.setInt(4, user.getUserId());
            psQuery.executeUpdate();


            //Nuke all roles
            String nuke = "DELETE FROM userRoles " +
                    "WHERE id = ?";
            PreparedStatement psNuke = c.prepareStatement(nuke);
            psNuke.setInt(1, user.getUserId());
            psNuke.executeUpdate();
            //Add roles again
            int i = 0;
            List<String> roleList = user.getRoles();
            while(i < roleList.size()){
                String restore = "INSERT INTO userRoles(id, roles) " +
                        "VALUES(?,?)";
                PreparedStatement psRes = c.prepareStatement(restore);
                psRes.setInt(1, user.getUserId());
                psRes.setString(2, roleList.get(i));
                psRes.executeUpdate();
                i++;
            }
            c.close();
        }catch(SQLException e){
            throw new DALException("Error updating user");
        }
    }

    @Override
    public void deleteUser(int userId) throws DALException {
        try(Connection c = createConnection()){
            String query = "DELETE FROM users " +
                    "WHERE id = ?";
            PreparedStatement psQuery = c.prepareStatement(query);
            psQuery.setInt(1, userId);
            String query2 = "DELETE FROM userRoles " +
                    "WHERE id = ?";
            PreparedStatement psQuery2 = c.prepareStatement(query2);
            psQuery2.setInt(1, userId);
            psQuery.executeUpdate();
            psQuery2.executeUpdate();

        }catch (SQLException e){
            throw new DALException("Error deleting user");
        }
    }
}