package OrdersDatabaseHomework;

import java.sql.*;
import java.util.Scanner;

public class Main {
    static final String HOST = "jdbc:mysql://localhost:3306/OrderDataBase";
    static final String USERNAME = "root";
    static final String PASSWORD = "root";

    static Connection connection;

    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        try {
            try {
                //create connection
                connection = DriverManager.getConnection(HOST, USERNAME, PASSWORD);
                initDB();

                while (true) {
                    System.out.println("1: add client");
                    System.out.println("2: view all clients");
                    System.out.println("3: add goods");
                    System.out.println("4: view all added goods");
                    System.out.println("5: add order by client id");
                    System.out.println("6: view all orders");
                    System.out.println("-->");

                    String s = scanner.nextLine();
                    switch (s) {
                        case "1":
                            addClient(scanner);
                            break;
                        case "2":
                            viewClients(scanner);
                        case "3":
                            addGoods(scanner);
                            break;
                        case "4":
                            viewGoods(scanner);
                            break;
                        case "5":
                            addOrder(scanner);
                            break;
                        case "6":
                            viewOrder(scanner);
                            break;
                        default:
                            return;
                    }
                }
            } finally {
                scanner.close();
                if (connection != null) connection.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return;
        }
    }

    private static void initDB() throws SQLException {
        Statement st = connection.createStatement();
        try {
            st.execute("DROP TABLE IF EXISTS Clients");
            st.execute("CREATE TABLE Clients (id_clients INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, age INT)");

            st.execute("DROP TABLE IF EXISTS Goods");
            st.execute("CREATE TABLE Goods (id_goods INT NOT NULL AUTO_INCREMENT PRIMARY KEY, gname VARCHAR(20) NOT NULL, quantity INT, price INT)");

            st.execute("DROP TABLE IF EXISTS Orders");

            st.execute("CREATE TABLE Orders (order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, id_clients INT, id_goods INT, date DATETIME NOT NULL , FOREIGN KEY (id_clients) REFERENCES Clients (id_clients), FOREIGN KEY (id_goods) REFERENCES Goods (id_goods))");
            //st.execute("CREATE TABLE Orders( id_clients INT , id_goods INT, CONSTRAINT order_pk PRIMARY KEY(id_clients, id_goods), CONSTRAINT clients_pk FOREIGN KEY (id_clients) REFERENCES clients (id_clients), CONSTRAINT goods_pk FOREIGN KEY  (id_goods) REFERENCES goods(id_goods), dateofOrder VARCHAR(20) NOT NULL,)");

        } finally {
            st.close();
        }
    }

    private static void addClient(Scanner scanner) throws SQLException {
        System.out.println("Enter client name: ");
        String name = scanner.nextLine();
        System.out.println("Enter client age: ");
        String Sage = scanner.nextLine();
        int age = Integer.parseInt(Sage);
        System.out.println("Client added");
        System.out.println("------");

        PreparedStatement ps = connection.prepareStatement("INSERT  INTO Clients (name, age) VALUES (?,?)");

        try {
            ps.setString(1, name);
            ps.setInt(2, age);
           ps.executeUpdate();
        } finally {
            ps.close();
        }

    }

    private static void viewClients(Scanner scanner) throws SQLException {
        PreparedStatement ps = connection.prepareStatement("SELECT * FROM Clients");

        try {
            ResultSet rs = ps.executeQuery();
            try {
                ResultSetMetaData md = rs.getMetaData();

                for (int i = 1; i <= md.getColumnCount(); i++)
                    System.out.print(md.getColumnName(i) + "\t\t");
                System.out.println();

                while (rs.next()) {

                    for (int j = 1; j <= md.getColumnCount(); j++) {
                        System.out.print(rs.getString(j) + "\t\t");
                    }
                    System.out.println();
                }
            } finally {
                rs.close();
            }
        } finally {
            ps.close();
        }

    }

    private static void addGoods(Scanner scanner) throws SQLException {
        System.out.println("Enter goods name: ");
        String gname = scanner.nextLine();
        System.out.println("Enter goods quantity: ");
        String squantity = scanner.nextLine();
        int quantity = Integer.parseInt(squantity);
        System.out.println("Enter total goods price: ");
        String sprice = scanner.nextLine();
        int price = Integer.parseInt(sprice);
        System.out.println("Goods added");
        System.out.println("------");

        PreparedStatement ps = connection.prepareStatement("INSERT  INTO Goods (gname, quantity, price) VALUES (?,?,?)");

        try {
            ps.setString(1, gname);
            ps.setInt(2, quantity);
            ps.setInt(3, price);
            ps.executeUpdate();
        } finally {
            ps.close();
        }
    }

    private static void viewGoods(Scanner scanner) throws SQLException {
        PreparedStatement ps2 = connection.prepareStatement("SELECT * FROM Goods");

        try {
            ResultSet rs2 = ps2.executeQuery();
            try {
                ResultSetMetaData md = rs2.getMetaData();

                for (int i = 1; i <= md.getColumnCount(); i++)
                    System.out.print(md.getColumnName(i) + "\t\t");
                System.out.println();

                while (rs2.next()) {

                    for (int j = 1; j <= md.getColumnCount(); j++) {
                        System.out.print(rs2.getString(j) + "\t\t");
                    }
                    System.out.println();
                }
            } finally {
                rs2.close();
            }
        } finally {
            ps2.close();
        }

    }

    private static void addOrder(Scanner scanner) throws SQLException {

        System.out.println("Enter client's id: ");
        String sid_clients = scanner.nextLine();
        int id_clients = Integer.parseInt(sid_clients);
        System.out.println("Enter good's id: ");
        String sid_goods = scanner.nextLine();
        int id_goods = Integer.parseInt(sid_goods);
        System.out.println("Order added by client's id and good's id");
        System.out.println("---------------------------");

        PreparedStatement ps = connection.prepareStatement("INSERT  INTO Orders (id_clients, id_goods, date) VALUES (?,?,NOW())");

        try {
            ps.setInt(1, id_clients);
            ps.setInt(2,id_goods);

            ps.executeUpdate();
        } finally {
            ps.close();
        }
    }


    private static void viewOrder(Scanner scanner)throws SQLException{
        PreparedStatement ps2 = connection.prepareStatement("SELECT * FROM Orders");

        try {
            ResultSet rs2 = ps2.executeQuery();
            try {
                while (rs2.next()){
                    int id = rs2.getInt("order_id");
                    int idclient = rs2.getInt("id_clients");
                    int idgoods = rs2.getInt("id_goods");
                    Date date = rs2.getDate("date");

                    System.out.println("id = "+ id+" id client = "+idclient + " good id = "
                            +idgoods+ " date of order = "+ date);
                }

            } finally {
                rs2.close();
            }
        } finally {
            ps2.close();
        }
    }
}