package service;

import bl.Util;
import dao.IUserDAO;
import entity.User;

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

public class UserService extends Util implements IUserDAO {

    Connection connection = getConnection();

    @Override
    public void insertUSer(User user) throws SQLException {
        PreparedStatement preparedStatement =null;
        String sql = "INSERT INTO user(user_name, user_lastname, email, password, user_dob, access_access_id)" +
                "VALUES(?, ?, ?, ?, ?, ?)";
        try{
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,user.getUserName());
            preparedStatement.setString(2,user.getUserLastName());
            preparedStatement.setString(3,user.getEmailUser());
            preparedStatement.setString(4,user.getPassword());
            preparedStatement.setDate(5,user.getUserDOB());
            preparedStatement.setInt(6,user.getIdAccess());

            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(preparedStatement != null)
                preparedStatement.close();
            if(connection != null)
                connection.close();
        }
    }

    @Override
    public void deleteUser(User user) {
        String sql ="DELETE FROM user WHERE user_id=?";
        try(PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setInt(1,user.getUserID());
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    @Override
    public User findUserID(int id) {
        String sql = "SELECT user_id, user_name, user_lastname, email, password, user_dob, access_access_id FROM user WHERE user_id = ?";
        User user = new User();
        try(PreparedStatement preparedStatement = connection.prepareStatement(sql)){
            preparedStatement.setInt(1, id);

            ResultSet resultSet = preparedStatement.executeQuery();

            user.setUserID(resultSet.getInt("user_id"));
            user.setUserName(resultSet.getString("user_name"));
            user.setUserLastName(resultSet.getString("user_lastname"));
            user.setEmailUser(resultSet.getString("email"));
            user.setPassword(resultSet.getString("password"));
            user.setUserDOB(resultSet.getDate("user_dob"));
            user.setIdAccess(resultSet.getInt("access_access_id"));

            preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    @Override
    public Collection<User> getUserAll() throws SQLException {
        Collection<User> userList = new ArrayList<>();
        String sql ="SELECT user_id, user_name, user_lastname, email, password, user_dob, access_access_id" +
                "FROM user";
        Statement statement = null;
        try {
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                User user = new User();
                user.setUserID(resultSet.getInt("user_id"));
                user.setUserName(resultSet.getString("user_name"));
                user.setUserLastName(resultSet.getString("user_lastname"));
                user.setEmailUser(resultSet.getString("email"));
                user.setPassword(resultSet.getString("password"));
                user.setUserDOB(resultSet.getDate("user_dob"));
                user.setIdAccess(resultSet.getInt("acees_accees_id"));
                userList.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(statement != null)
                statement.close();
            if(connection != null)
                connection.close();
        }
        return userList;
    }

    @Override
    public void updateUser(User user) {
        String sql = "UPDATE user SET user_name=?, user_lastname=?, email=?, password=?, user_dob=?, access_access_id=?" +
                "WHERE user_id=?";
        try(PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setString(1,user.getUserName());
            preparedStatement.setString(2,user.getUserLastName());
            preparedStatement.setString(3, user.getEmailUser());
            preparedStatement.setString(4, user.getPassword());
            preparedStatement.setDate(5,user.getUserDOB());
            preparedStatement.setInt(6,user.getIdAccess());

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}