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

/**
 Спроектировать базу «Квартиры». Каждая запись
 в базе содержит данные о квартире (район,
 адрес, площадь, кол. комнат, цена). Сделать
 возможность выборки квартир из списка по
 параметрам.
 */
public class Main {
    static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/mydb1";
    static final String DB_USER = "Angrim90";
    static final String DB_PASSWORD = "Blacksun6237226";
    static Connection conn;

    public static void main(String[] args) {


        Scanner sc = new Scanner(System.in);

        try {
            conn = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
            initialization();
            for (; ; ) {
                System.out.println("1: add apartment");
                System.out.println("2: delete apartment");
                System.out.println("3: view apartments");
                System.out.println("4: search to parameter");
                System.out.println(" ___________ ");

                String s = sc.nextLine();

                switch (s) {
                    case "1":
                        addApart(sc);
                        break;
                    case "2":
                        delApart(sc);
                        break;
                    case "3":
                        viewApart();
                        break;
                    case "4":
                        System.out.println("Insert parameter, value in format \"param - value\"");
                        try {
                            String s1[] = sc.nextLine().split(" - ");
                            System.out.println(s1[0] + s1[1]);
                            searchToParam(s1[0], s1[1]);
                        } catch (ArrayIndexOutOfBoundsException e) {
                            System.out.println("Wrong input param :(\n");
                            return;
                        } finally {
                            break;
                        }
                    default:
                        System.out.println("Wrong input format :((\n try entering some value");
                        break;
                }
            }
        } catch (SQLException e) {


        }
    }
    private static void addApart(Scanner sc) throws SQLException {
        System.out.print("Enter apartments area: ");
        String area = sc.nextLine();
        System.out.print("Enter apartments adress: ");
        String adress = sc.nextLine();
        System.out.print("Enter apartments squar: ");
        String squares = sc.nextLine();
        System.out.print("Enter apartments rooms: ");
        String rooms = sc.nextLine();
        int roomsCount = 0;
        double d = 0;
        System.out.print("Enter apartments price: ");
        String price = sc.nextLine();
        try {
           roomsCount  = Integer.parseInt(rooms);
             d= Double.parseDouble(price);
        }
        catch (NumberFormatException e){
            System.out.println("Wrong entering format :(\n");
            return;
        }
        PreparedStatement ps = conn.prepareStatement("INSERT INTO Apartments (area, adress,squares,rooms,price) VALUES(?, ?,?,?,?)");
        try {
            ps.setString(1, area);
            ps.setString(2, adress);
            ps.setString(3, squares);
            ps.setInt(4, roomsCount);
            ps.setDouble(5, d);
            ps.executeUpdate();
        } finally {
            ps.close();
        }
    }
    private static void delApart(Scanner sc) throws SQLException {
        System.out.print("Enter apartments area: ");
        String area = sc.nextLine();
        PreparedStatement ps = conn.prepareStatement("DELETE FROM Apartments WHERE area = ?");
        try {
            ps.setString(1, area);
            ps.executeUpdate();

        } finally {
            ps.close();
        }
    }

    private static void viewApart() throws SQLException {
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM Apartments");

        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 i = 1; i <= md.getColumnCount(); i++) {
                        System.out.print(rs.getString(i) + "\t\t ");
                    }
                    System.out.println("____");
                }
            } finally {
                rs.close();
            }
        } finally {
            ps.close();
        }

    }

    public static void searchToParam(String param, Object val) throws SQLException {

        PreparedStatement ps = conn.prepareStatement("SELECT * FROM Apartments WHERE " + param + " = \""+ val+ "\" ;");
        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 i = 1; i <= md.getColumnCount(); i++) {
                    System.out.print(rs.getString(i) + "\t\t");
                }
                System.out.println("____");
            }
        } finally {
            rs.close();
            ps.close();
        }

    }

    private static void initialization() throws SQLException {
        Statement st = conn.createStatement();

        try {
            st.execute("DROP TABLE IF EXISTS Apartments;");

            st.execute("CREATE TABLE Apartments (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, area VARCHAR(20) NOT NULL, " +
                    "adress VARCHAR(20) NOT NULL, squares VARCHAR(10) NOT NULL, rooms INT NOT NULL , price DOUBLE (12,2)NOT NULL) ");


        } finally {
            st.close();
        }
    }

}