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

public class MyMain {
    static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/mydb";
    static final String DB_USER = "root";
    static final String DB_PASSWORD = "Tochilka1";

    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);
                initDB();

                while((true)){
                    System.out.println("1: add flat");
                    System.out.println("2: view district's catalog");
                    System.out.println("3: select flats by district");
                    System.out.println("4: select flats by district & price");
                    System.out.println("5: view all flats");
                    System.out.println("6: test");
                    String s = sc.nextLine();

                    switch(s) {
                        case "1" :
                            addFlat(sc);
                            break;
                        case "2" :
                            loadCatalog();
                            break;
                        case "3" :
                            selectD(sc);
                            break;
                        case "4" :
                            selectDandP(sc);
                            break;
                        case "5" :
                            viewFlats();
                            break;
                        case "6":
                            test();
                            break;
                        default:
                            return;
                    }
                    System.out.println();
                }

            } finally {
                sc.close();
                if (conn != null) conn.close();
            }
        } catch (SQLException e)
        {
            e.printStackTrace();
            return;
        }


    }

    private static void initDB() throws SQLException {
        Statement st = conn.createStatement();
        try {
            st.execute("Drop table if EXISTS Flats");
            st.execute("CREATE TABLE Flats (idFlat INT NOT NULL AUTO_INCREMENT PRIMARY KEY, idDistrict INT NOT NULL, addresses VARCHAR(45) NOT NULL, area DECIMAL, rooms INT, price DECIMAL NOT NULL)");
        }
        finally {
            st.close();
        }
    }

    private static void loadCatalog() throws SQLException{
        System.out.println("District's catalog :\n1: Schevchenko district\n2: Solomenskiy\n3: Svyatoshinskiy\n4: Obolonskiy\n5: Pechersk");
    }

    private static void selectD(Scanner sc) throws SQLException{
        System.out.println("Choose district, you want to look:");
        String sdist = sc.nextLine();
        Integer dist = Integer.parseInt(sdist);

        StringBuilder sb = new StringBuilder();
        sb.append("Select * From Flats Where idDistrict = ");
        sb.append(dist);
        viewFlats(sb.toString());
    }

    private static void selectDandP(Scanner sc) throws SQLException{
        System.out.println("Choose district, you want to look:");
        String sdist = sc.nextLine();
        Integer dist = Integer.parseInt(sdist);

        System.out.println("Enter price min:");
        String smin = sc.nextLine();
        Double pmin = Double.parseDouble(smin);

        System.out.println("and price max:");
        String smax = sc.nextLine();
        Double pmax = Double.parseDouble(smax);

        StringBuilder sb = new StringBuilder();
        sb.append("Select * From Flats Where idDistrict = ");
        sb.append(dist);
        sb.append(" and price BETWEEN ").append(pmin).append(" and ").append(pmax);

        viewFlats(sb.toString());
    }

    private static void test() throws SQLException{
        PreparedStatement ps = conn.prepareStatement("INSERT INTO Flats (idDistrict, addresses, area, rooms, price) VALUES (?, ?, ?, ?, ?)");
        try {
            ps.setInt(1, 4);
            ps.setString(2, "Kopernika");
            ps.setDouble(3, 50);
            ps.setInt(4, 3);
            ps.setDouble(5, 500);
            ps.executeUpdate();
        }
        finally {
            ps.close();
        }
    }

    private static void addFlat(Scanner sc) throws SQLException{
        loadCatalog();
        System.out.println("Enter district:");
        String sidDistrict = sc.nextLine();
        int idDistrict = Integer.parseInt(sidDistrict);

        System.out.println("Enter flat's address, please:");
        String address = sc.nextLine();

        System.out.println("Enter flat's area:");
        String sarea = sc.nextLine();
        double area = Double.parseDouble(sarea);

        System.out.println("Enter number of rooms:");
        String sRooms = sc.nextLine();
        int rooms = Integer.parseInt(sRooms);

        System.out.println("Enter price ($):");
        String sPrice = sc.nextLine();
        double price = Double.parseDouble(sPrice);

        PreparedStatement ps = conn.prepareStatement("INSERT INTO Flats (idDistrict, addresses, area, rooms, price) VALUES (?, ?, ?, ?, ?)");
        try {
            ps.setInt(1, idDistrict);
            ps.setString(2, address);
            ps.setDouble(3, area);
            ps.setInt(4, rooms);
            ps.setDouble(5, price);
            ps.executeUpdate();
        }
        finally {
            ps.close();
        }
    }

    private static void viewFlats(String sql ) throws SQLException{
        PreparedStatement ps = conn.prepareStatement(sql);
        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\t");
                    System.out.println();
                }
                System.out.println();
            }
            finally {
                rs.close();
            }
        }
        finally {
            ps.close();
        }
    }


    private static void viewFlats() throws SQLException{
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM Flats");
        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\t");
                    System.out.println();
                }
                System.out.println();
            }
            finally {
                rs.close();
            }
        }
        finally {
            ps.close();
        }
    }

}