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

public class Main {

    private static final String URL = "jdbc:mysql://localhost:3306/flats";
    private static final String USER = "root";
    private static final String PASSWORD = "admin";

    static Connection connection;

    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        try {
            try {
                connection = DriverManager.getConnection(URL, USER, PASSWORD);
                initDB();

                while (true) {
                    System.out.println("1: add flat");
                    System.out.println("2: add random flat");
                    System.out.println("3: delete flat");
                    System.out.println("4: change flat prise");
                    System.out.println("5: view flat with X rooms");
                    System.out.println("6: view flat with X district");
                    System.out.println("7: view flat with X square");
                    System.out.println("8: view flat with X prise");
                    System.out.println("9: view all flats");
                    System.out.print("-> ");

                    String s = sc.nextLine();
                    switch (s) {
                        case "1":
                            addFlat(sc);
                            break;
                        case "2":
                            insertRandomFlat(sc);
                            break;
                        case "3":
                            deleteFlat(sc);
                            break;
                        case "4":
                            changeFlatPrise(sc);
                            break;
                        case "5":
                            viewFlatsXRoom(sc);
                            break;
                        case "6":
                            viewFlatsFromDisrtict(sc);
                            break;
                        case "7":
                            viewFlatsXSquare(sc);
                            break;
                        case "8":
                            viewFlatsXPrise(sc);
                            break;
                        case "9":
                            viewAllFlats();
                            break;
                        default:
                            return;
                    }
                }
            } finally {
                sc.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 FlatsTable");
            st.execute("CREATE TABLE FlatsTable (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY," +
                    " district VARCHAR(30) NOT NULL, adres VARCHAR(100) NOT NULL, square INT NOT NULL," +
                    " rooms INT NOT NULL, prise INT NOT NULL )  ");
        } finally {
            st.close();
        }
    }

    private static void addFlat(Scanner sc) throws SQLException {
        System.out.print("Enter flat district: ");
        String district = sc.nextLine();
        System.out.print("Enter flat adres: ");
        String adres = sc.nextLine();
        System.out.print("Enter flat square: ");
        String sq = sc.nextLine();
        int square = Integer.parseInt(sq);
        System.out.print("Enter flat rooms count: ");
        String room = sc.nextLine();
        int rooms = Integer.parseInt(room);
        System.out.print("Enter flat rooms prise: ");
        String pr = sc.nextLine();
        int prise = Integer.parseInt(pr);

        PreparedStatement ps = connection.prepareStatement("INSERT INTO FlatsTable (district, adres, square, rooms, prise ) VALUES(?, ?, ?, ?, ?)");
        try {
            ps.setString(1, district);
            ps.setString(2, adres);
            ps.setInt(3, square);
            ps.setInt(4, rooms);
            ps.setInt(5, prise);
            ps.executeUpdate(); // for INSERT, UPDATE & DELETE
        } finally {
            ps.close();
        }
    }

    private static void deleteFlat(Scanner sc) throws SQLException {
        System.out.print("Enter flat adress: ");
        String adres = sc.nextLine();

        PreparedStatement ps = connection.prepareStatement("DELETE FROM FlatsTable WHERE adres = ?");
        try {
            ps.setString(1, adres);
            ps.executeUpdate(); // for INSERT, UPDATE & DELETE
        } finally {
            ps.close();
        }
    }

    private static void changeFlatPrise(Scanner sc) throws SQLException {
        System.out.print("Enter flat adress: ");
        String adres = sc.nextLine();
        System.out.print("Enter new prise: ");
        String pr = sc.nextLine();
        int prise = Integer.parseInt(pr);

        PreparedStatement ps = connection.prepareStatement("UPDATE FlatsTable SET prise = ? WHERE adres = ?");
        try {
            ps.setString(1, adres);
            ps.setInt(2, prise);
            ps.executeUpdate(); // for INSERT, UPDATE & DELETE
        } finally {
            ps.close();
        }
    }

    private static void insertRandomFlat(Scanner sc) throws SQLException {
        System.out.print("Enter flats count: ");
        String sCount = sc.nextLine();
        int count = Integer.parseInt(sCount);
        Random rnd = new Random();

        connection.setAutoCommit(false); // enable transactions
        try {
            try {
                PreparedStatement ps = connection.prepareStatement("INSERT INTO FlatsTable (district, adres, square, rooms, prise ) VALUES(?, ?, ?, ?, ?)");
                try {
                    for (int i = 0; i < count; i++) {
                        ps.setString(1, "Suvorovskiy");
                        ps.setString(2, "Yshakova " + i);
                        ps.setInt(3, 30 + rnd.nextInt(10));
                        ps.setInt(4, 1 + rnd.nextInt(3));
                        ps.setInt(5, 20 + rnd.nextInt(20));
                        ps.executeUpdate();
                    }
                    connection.commit();
                } finally {
                    ps.close();
                }
            } catch (Exception ex) {
                connection.rollback();
            }
        } finally {
            connection.setAutoCommit(true); // return to default mode
        }
    }

    private static void viewFlatsFromDisrtict(Scanner sc) throws SQLException {
        System.out.print("Enter Disrtict: ");
        String distr = sc.nextLine();
        PreparedStatement ps = connection.prepareStatement("SELECT * FROM FlatsTable WHERE district = ?");

        try {
            ps.setString(1, distr);
            printSelect(ps);
        } finally {
            ps.close();
        }
    }

    private static void viewFlatsXRoom(Scanner sc) throws SQLException {
        System.out.print("Enter Rooms count: ");
        String rmCount = sc.nextLine();
        int rCount = Integer.parseInt(rmCount);
        PreparedStatement ps = connection.prepareStatement("SELECT * FROM FlatsTable WHERE rooms = ?");

        try {
            ps.setInt(1, rCount);
            printSelect(ps);
        } finally {
            ps.close();
        }
    }

    private static void viewFlatsXSquare(Scanner sc) throws SQLException {
        System.out.print("Enter Square: ");
        String sq = sc.nextLine();
        int square = Integer.parseInt(sq);
        PreparedStatement ps = connection.prepareStatement("SELECT * FROM FlatsTable WHERE square = ?");

        try {
            ps.setInt(1, square);
            printSelect(ps);
        } finally {
            ps.close();
        }
    }

    private static void viewFlatsXPrise(Scanner sc) throws SQLException {
        System.out.print("Enter Prise: ");
        String pr = sc.nextLine();
        int price = Integer.parseInt(pr);
        PreparedStatement ps = connection.prepareStatement("SELECT * FROM FlatsTable WHERE price = ?");
        try {
            ps.setInt(1, price);
            printSelect(ps);
        } finally {
            ps.close();
        }
    }

    private static void viewAllFlats() throws SQLException {
        PreparedStatement ps = connection.prepareStatement("SELECT * FROM FlatsTable");
        try {
            printSelect(ps);
        } finally {
            ps.close();
        }
    }

    private static void printSelect(PreparedStatement ps) throws SQLException {
        ResultSet rs = ps.executeQuery();
        try {
            // can be used to get information about the types and properties of the columns in a ResultSet object
            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(); // rs can't be null according to the docs
        }
    }
}