public class DataBase {
private Connection connection;
private Statement statement;
private PreparedStatement preparedStatement;
private ResultSet result;

public DataBase() throws SQLException {
    connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/rentalcar?autoReconnect=true&serverTimezone=" + TimeZone.getDefault().getID(), "root", "...");
    statement = connection.createStatement();
}

public void insertNewCustomer(Client client) throws SQLException {
    preparedStatement = connection.prepareStatement("insert into client" + "(namee, surname, street,houseNumber,city,peselNumber,rentDate, clientNumber)" + "values(?,?,?,?,?,?,?,?)");

    preparedStatement.setString(1, client.getName());
    preparedStatement.setString(2, client.getSurname());
    preparedStatement.setString(3, client.getStreet());
    preparedStatement.setInt(4, client.getHouseNumber());
    preparedStatement.setString(5, client.getCity());
    preparedStatement.setLong(6, client.getPeselNumber());
    preparedStatement.setString(7, client.getRentDate());
    preparedStatement.setInt(8, client.getClientNumber());

    preparedStatement.executeUpdate();
}

public void insertNewCar(Car car) throws SQLException {
    preparedStatement = connection.prepareStatement("insert into car" + "(brand, productionYear, engineCapacity,dayPrice,available)" + "values(?,?,?,?,?)");

    preparedStatement.setString(1, car.getBrand());
    preparedStatement.setString(2, car.getProductionYear());
    preparedStatement.setString(3, car.getEngineCapacity());
    preparedStatement.setInt(4, car.getDayPrice());
    preparedStatement.setString(5, car.getAvailable());

    preparedStatement.executeUpdate();
}

public void rentACar(RentingACar rentingACar) throws SQLException {
    int count = 0;
    boolean isAvailable = true;
    {
        preparedStatement = connection.prepareStatement("SELECT COUNT(0) FROM car WHERE available='1' AND brand=?");
        preparedStatement.setString(1, rentingACar.getBrand());
        result = preparedStatement.executeQuery();
    }
    while (result.next()) {
        count = result.getInt(1);
    }
    if (count < 1)
        isAvailable = false;

    if (isAvailable) {
        preparedStatement = connection.prepareStatement("insert into rentcar" + "(brand,namee,surname,rentDate,clientNumber)" + "values(?,?,?,?,?)");
        preparedStatement.setString(1, rentingACar.getBrand());
        preparedStatement.setString(2, rentingACar.getName());
        preparedStatement.setString(3, rentingACar.getSurname());
        preparedStatement.setString(4, rentingACar.getRentDate());
        preparedStatement.setInt(5, rentingACar.getClientNumber());
        preparedStatement.executeUpdate();

        preparedStatement = connection.prepareStatement("update car " + " set available='0'" + " where brand= ? ");
        preparedStatement.setString(1, rentingACar.getBrand());
        preparedStatement.executeUpdate();
        System.out.println("Car was rented!");
    } else {
        System.out.println("There is no " + rentingACar.getBrand() + " in our car or all types of this car are rented!");
    }
}

public void returnACar(Car car) throws SQLException {
    preparedStatement = connection.prepareStatement("DELETE from rentcar WHERE brand=? AND clientNumber=?");
    preparedStatement.setString(1, car.getBrand());
    preparedStatement.setInt(2, car.getClientNumber());
    preparedStatement.executeUpdate();

    preparedStatement = connection.prepareStatement("update car " + " set available='1'" + " where brand=?");
    preparedStatement.setString(1, car.getBrand());
    preparedStatement.executeUpdate();
}

public void makeCarUnavailable(Car car) throws SQLException {
    int count = 0;
    boolean isAvailable = true;
    {
        preparedStatement = connection.prepareStatement("SELECT COUNT(0) FROM car WHERE brand=? AND productionYear=? ");
        preparedStatement.setString(1, car.getBrand());
        preparedStatement.setString(2, car.getProductionYear());
        result = preparedStatement.executeQuery();
    }
    while (result.next()) {
        count = result.getInt(1);
    }
    if (count < 1)
        isAvailable = false;

    if (isAvailable) {
        preparedStatement = connection.prepareStatement("update car " + " set available='0'" + " where brand=? AND productionYear=?");
        preparedStatement.setString(1, car.getBrand());
        preparedStatement.setString(2, car.getProductionYear());
        preparedStatement.executeUpdate();
        System.out.println(car.getBrand() + " was made unavailable");
    } else {
        System.out.println("No " + car.getBrand() + " in system!");
    }
}

public void makeCarAvailable(Car car) throws SQLException {
    int count = 0;
    boolean isAvailable = true;
    {
        preparedStatement = connection.prepareStatement("SELECT COUNT(0) FROM car WHERE brand=? AND productionYear=? ");
        preparedStatement.setString(1, car.getBrand());
        preparedStatement.setString(2, car.getProductionYear());
        result = preparedStatement.executeQuery();
    }
    while (result.next()) {
        count = result.getInt(1);
    }
    if (count < 1)
        isAvailable = false;

    if (isAvailable) {
        preparedStatement = connection.prepareStatement("update car " + " set available='1'" + " where brand=? AND productionYear=?");
        preparedStatement.setString(1, car.getBrand());
        preparedStatement.setString(2, car.getProductionYear());
        preparedStatement.executeUpdate();
        System.out.println(car.getBrand() + " was made unavailable");
    } else {
        System.out.println("No " + car.getBrand() + " in system!");
    }
}

public void populateTableViewCars(Car car) throws SQLException {
    preparedStatement = connection.prepareStatement("SELECT * FROM car WHERE dayPrice > ?");
    preparedStatement.setDouble(1, car.getDayPrice());
    result = preparedStatement.executeQuery();

    while (result.next()) {

        String brand = result.getString("brand");
        String productionYear = result.getString("productionYear");
        String engineCapacity = result.getString("engineCapacity");
        String dayPrice = result.getString("dayPrice");
        String available = result.getString("available");
        System.out.println("----------------------------");
        System.out.printf("Brand:" + brand + "nEngine Capacity:" + engineCapacity + "nDayPrice:" + dayPrice + "nProduction Year:" + productionYear + "navailable:" + available + "n");
        System.out.println("----------------------------");
    }
}


public void populateTableRent(Client client) throws SQLException {
    preparedStatement = connection.prepareStatement("SELECT * FROM rentcar WHERE clientNumber=?");
    preparedStatement.setInt(1, client.getClientNumber());
    result = preparedStatement.executeQuery();

    while (result.next()) {

        String brand = result.getString("brand");
        String name = result.getString("namee");
        String surname = result.getString("surname");
        String rentDate = result.getString("rentDate");
        System.out.println("----------------------------");
        System.out.printf("Brand:" + brand + "nName:" + name + "nSurname:" + surname + "nDate of rental:" + rentDate + "n");
        System.out.println("----------------------------");
    }
}

public void populateTableViewClients() throws SQLException {
    String sql = "SELECT * FROM `client`";
    result = statement.executeQuery(sql);

    while (result.next()) {

        String namee = result.getString("namee");
        String surname = result.getString("surname");
        String street = result.getString("street");
        int houseNumber = result.getInt("houseNumber");
        long peselNumber = result.getLong("peselNumber");
        String rentDate = result.getString("rentDate");
        System.out.println("----------------------------");
        System.out.printf("Name:" + namee + "nSurname:" + surname + "nStreet:" + street + "nNumber of house:" + houseNumber + "nPesel number:" + peselNumber + "nDate of rental:" + rentDate + "n");
        System.out.println("----------------------------");
    }
}
}

public class WorkerDataGetter {
private Scanner input = new Scanner(System.in);

public Car createCar() {
    Car car = new Car();

    System.out.print("Brand: ");
    car.setBrand(input.next());
    System.out.print("Day price: ");
    car.setDayPrice(input.nextInt());
    System.out.print("Engine Capcity: ");
    car.setEngineCapacity(input.next());
    System.out.print("Production year: ");
    car.setProductionYear(input.next());
    System.out.print("available: ");
    car.setAvailable(input.next());

    return car;
}

public Car makeCarUnavailable() {
    Car car = new Car();

    System.out.print("Brand: ");
    car.setBrand(input.next());
    System.out.print("production year: ");
    car.setProductionYear(input.next());

    return car;
}

public Car makeCarAavailable() {
    Car car = new Car();

    System.out.print("Brand: ");
    car.setBrand(input.next());
    System.out.print("Production year : ");
    car.setProductionYear(input.next());

    return car;
}
}

public class ClientDataGetter {
private Scanner input = new Scanner(System.in);
private Random rand = new Random();

public Client createClient() {
    Client client = new Client();
    client.setClientNumber(rand.nextInt(999));

    System.out.print("name: ");
    client.setName(input.next());
    System.out.print("surname: ");
    client.setSurname(input.next());
    System.out.print("city: ");
    client.setCity(input.next());
    System.out.print("house number: ");
    client.setHouseNumber(input.nextInt());
    System.out.print("street: ");
    client.setStreet(input.next());
    System.out.print("pesel number: ");
    client.setPeselNumber(input.nextLong());
    System.out.print("rent date: ");
    client.setRentDate(input.next());
    System.out.println("Your client number is: " + client.getClientNumber());

    return client;
}

public RentingACar rentACar() {
    RentingACar rentingACar = new RentingACar();

    System.out.print("Brand: ");
    rentingACar.setBrand(input.next());
    System.out.print("Name: ");
    rentingACar.setName(input.next());
    System.out.print("Surname: ");
    rentingACar.setSurname(input.next());
    System.out.print("Rent Date: ");
    rentingACar.setRentDate(input.next());
    System.out.print("Client number: ");
    rentingACar.setClientNumber(input.nextInt());

    return rentingACar;
}

public Car populateTableViewCars() {
    Car car = new Car();

    System.out.println("Input minimum price per day. If you want to display all cars - input 0.nMinimum price: ");
    car.setDayPrice(input.nextInt());

    return car;
}

public Client populateTableRent() {
    Client client = new Client();

    System.out.println("Input your client number: ");
    client.setClientNumber(input.nextInt());

    return client;
}

public Car returnACar() {
    Car car = new Car();

    System.out.println("Input brand of car that you want to return: ");
    car.setBrand(input.next());
    System.out.println("Input your client number, otherwise car won't be removed from our DataBase!");
    car.setClientNumber(input.nextInt());

    return car;
}
}

public class CarRentalOptions {
private DataBase dataBase = new DataBase();

CarRentalOptions() throws SQLException {
}

void createNewCustomer(Client client) throws SQLException {
    dataBase.insertNewCustomer(client);

    System.out.println("Client added successfully!");
}

void createNewCar(Car car) throws SQLException {
    dataBase.insertNewCar(car);

    System.out.println("Car added successfully!");
}

void makeCarUnavailable(Car car) throws SQLException {
    dataBase.makeCarUnavailable(car);
}

void makeCarAvailable(Car car) throws SQLException {
    dataBase.makeCarAvailable(car);
}

void rentACar(RentingACar rentingACar) throws SQLException {
    dataBase.rentACar(rentingACar);
}

void populateTableViewCars(Car car) throws SQLException {
    dataBase.populateTableViewCars(car);
}

void populateTableRent(Client client) throws SQLException {
    dataBase.populateTableRent(client);
}

void populateTableViewClients() throws SQLException {
    dataBase.populateTableViewClients();
}

void returnACar(Car car) throws SQLException {
    dataBase.returnACar(car);
}
}

public class CarRentalEngine {
private int option;
private Scanner input = new Scanner(System.in);
private CarRentalOptions carRentalOptions = new CarRentalOptions();
private ClientDataGetter clientDataGetter = new ClientDataGetter();
private WorkerDataGetter workerDataGetter = new WorkerDataGetter();

CarRentalEngine() throws SQLException {
}

void startCarRental() throws SQLException {
    System.out.println("Who are you?n1. Customern2. Worker");
    try {
        switch (input.nextInt()) {
            case 1:
                executeClientCase();
                break;
            case 2:
                executeWorkerCase();
                break;
        }
    } catch (InputMismatchException e) {
        System.err.println("Your input is wrong!");
    }
}


private void executeOptionsForClient(int option) throws SQLException {
    switch (option) {
        case 1:
            carRentalOptions.rentACar(clientDataGetter.rentACar());
            break;
        case 2:
            carRentalOptions.returnACar(clientDataGetter.returnACar());
            break;
        case 3:
            carRentalOptions.populateTableRent(clientDataGetter.populateTableRent());
            break;
        case 4:
            carRentalOptions.populateTableViewCars(clientDataGetter.populateTableViewCars());
            break;
        case 5:
            break;
    }
}


private void executeOptionsForWorker(int option) throws SQLException {
    switch (option) {
        case 1:
            carRentalOptions.populateTableViewClients();
            break;
        case 2:
            carRentalOptions.populateTableViewCars(clientDataGetter.populateTableViewCars());
            break;
        case 3:
            carRentalOptions.makeCarAvailable(workerDataGetter.makeCarAavailable());
            break;
        case 4:
            carRentalOptions.makeCarUnavailable(workerDataGetter.makeCarUnavailable());
            break;
        case 5:
            carRentalOptions.createNewCar(workerDataGetter.createCar());
        case 6:
            break;
    }
}


private void executeClientCase() throws SQLException {
    System.out.println("1. Have you inputted your data before?nN/Y: ");
    if (input.next().toUpperCase().equals("N")) {
        carRentalOptions.createNewCustomer(clientDataGetter.createClient());
        System.out.println("Now you have your unique number clinet, use it where it is required!");
    } else {
        do {
            System.out.println("What do you want to do?");
            System.out.println("1. Rent a carn2. Return a carn3. Populate rented carsn4. Populate carsn5. Quit");
            option = input.nextInt();
            executeOptionsForClient(option);
        }
        while (option != 5);
    }
}

private void executeWorkerCase() throws SQLException {
    do {
        System.out.println("What do you want to do?");
        System.out.println("1. Populate clientsn2. Populate carsn3. Make car availablen4. Make car unavailablen5. Insert new carn6. Quit");
        option = input.nextInt();
        executeOptionsForWorker(option);
    }
    while (option != 6);
}
}