import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

/**
 * @author Ihor Solohub.
 * Создать проект «База данных заказов». Создать
   таблицы «Товары» , «Клиенты» и «Заказы».
   Написать код для добавления новых клиентов,
   товаров и оформления заказов.
 */
public class Main {
    static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/OrdersDatabase";
    static final String DB_USER = "root";
    static final String DB_PASSWORD = "15935724865";

    static Connection conn;

    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        try {
            try {
                conn = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);

                while (true) {
                    System.out.println("1: add new Product");
                    System.out.println("2: add new Client");
                    System.out.println("3: create Order");
                    System.out.print("->");

                    String choise = sc.nextLine();
                    switch (choise) {
                        case "1":
                            DbUtils.addProduct();
                            break;
                        case "2":
                            DbUtils.addClient(sc);
                            break;
                        case "3":
                            DbUtils.createOrder();
                            break;
                        default:
                            return;
                    }
                }
            } finally {
                sc.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static class DbUtils {

        /*------- Add product -------*/
        private static void addProduct() throws SQLException {
            Scanner sc = new Scanner(System.in);
            System.out.println("Enter product name: ");
            String productName = sc.nextLine();
            System.out.println("Enter price: ");
            int productPrice = sc.nextInt();

            PreparedStatement ps = conn.prepareStatement("INSERT INTO Products " +
                    "(Name, Price) VALUES (?,?)");
            try {
                ps.setString(1, productName);
                ps.setInt(2, productPrice);
                ps.executeUpdate();
            } finally {
                ps.close();
            }
        }

        /*------- Add client -------*/
        private static void addClient(Scanner scanner) throws SQLException {
            Scanner sc = new Scanner(System.in);
            System.out.println("Enter client's name: ");
            String clientsName = sc.nextLine();
            System.out.println("Enter phone: ");
            String clientsPhone = sc.nextLine();

            PreparedStatement ps = conn.prepareStatement("INSERT INTO Clients " +
                    "(Name, Phone) VALUES (?,?)");
            try {
                ps.setString(1, clientsName);
                ps.setString(2, clientsPhone);
                ps.executeUpdate();
            } finally {
                ps.close();
            }
        }

        /*------- Create order -------*/
        private static void createOrder() throws SQLException {
            Scanner sc = new Scanner(System.in);
            System.out.println("Enter product name: ");
            String productName = sc.nextLine();
            System.out.println("Enter client's phone: ");
            String clientsPhone = sc.nextLine();
            String info = "New order";
            PreparedStatement ps = conn.prepareStatement("INSERT INTO Orders (Pid, Cid, Info) VALUES " +
                    "((SELECT id FROM Products WHERE Name = ?), " +
                    "(SELECT Id FROM Clients WHERE Phone = ?), \"New order\");");
            try {
                ps.setString(1, productName);
                ps.setString(2, clientsPhone);
                ps.executeUpdate();
            } finally {
                ps.close();
            }
        }
    }
}