package app;

import java.sql.*;
import java.util.Scanner;
/**2. Спроектировать базу «Квартиры». Каждая запись
*в базе содержит данные о квартире (район,
*адрес, площадь, кол. комнат, цена). Сделать
*возможность выборки квартир из списка по
параметрам.
*/
/**
 * Created by Aleksey on 06.08.2016.
 */
public class Main {
    static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/flatdb";
    static final String DB_USER = "root";
    static final String DB_PASSWORD = "eniva1989";

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

                while (true) {
                    System.out.println("1: add apartment");
                    System.out.println("2: delete apartment");
                    System.out.println("3: change apartment");
                    System.out.println("4: view apartments");
                    System.out.println("5: view apartments for parameters");
                    System.out.print("-> ");

                    String s = sc.nextLine();
                    switch (s) {
                        case "1":
                            addApartment(sc);
                            break;
                        case "2":
                            deleteApartment(sc);
                            break;
                        case "3":
                            changeApartment(sc);
                            break;
                        case "4":
                            viewApartments();
                            break;
                        case "5":
                            viewApartmentsForParameters(sc);
                            break;
                        default:
                            return;
                    }
                }
            } finally {
                sc.close();
                if (conn != null) conn.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }


    }

    private static void initDB() 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, district VARCHAR(20) NOT NULL," +
                    "address VARCHAR(20) NOT NULL, area DOUBLE, countRooms INT, price DOUBLE)");
        } finally {
            st.close();
        }
    }

    private static void addApartment(Scanner sc) throws SQLException {
        System.out.print("Enter apartment district: ");
        String district = sc.nextLine();
        System.out.print("Enter apartment address: ");
        String address = sc.nextLine();
        System.out.print("Enter apartment area(m^2): ");
        String sArea = sc.nextLine();
        double area = Double.parseDouble(sArea);
        System.out.print("Enter apartment countRooms: ");
        String sCountRooms = sc.nextLine();
        int countRooms = Integer.parseInt(sCountRooms);
        System.out.print("Enter apartment price($): ");
        String sPrice = sc.nextLine();
        double price = Double.parseDouble(sPrice);

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

    private static void deleteApartment(Scanner sc) throws SQLException {

        System.out.println("Select the option for which will remove");
        System.out.println("1: id");
        System.out.println("2: district");
        System.out.println("3: address");
        System.out.println("4: area");
        System.out.println("5: countRooms");
        System.out.println("6: price");
        System.out.print("-> ");

        String s = sc.nextLine();
        switch (s) {

            case "1":
                System.out.print("Enter apartment id: ");
                String sId = sc.nextLine();
                int id = Integer.parseInt(sId);

                PreparedStatement ps = conn.prepareStatement("DELETE FROM Apartments WHERE id = ?");
                try {
                    ps.setInt(1, id);
                    ps.executeUpdate();
                } finally {
                    ps.close();
                }
                break;
            case "2":
                System.out.print("Enter apartment district: ");
                String district = sc.nextLine();

                ps = conn.prepareStatement("DELETE FROM Apartments WHERE district = ?");
                try {
                    ps.setString(1, district);
                    ps.executeUpdate();

                } finally {
                    ps.close();
                }
                break;
            case "3":
                System.out.print("Enter apartment address: ");
                String address = sc.nextLine();

                ps = conn.prepareStatement("DELETE FROM Apartments WHERE address = ?");
                try {
                    ps.setString(1, address);
                    ps.executeUpdate();
                } finally {
                    ps.close();
                }
                break;
            case "4":
                System.out.print("Enter apartment area(m^2): ");
                String sArea = sc.nextLine();
                Double area = Double.parseDouble(sArea);

                ps = conn.prepareStatement("DELETE FROM Apartments WHERE area = ?");
                try {
                    ps.setDouble(1, area);
                    ps.executeUpdate();
                } finally {
                    ps.close();
                }
                break;
            case "5":
                System.out.print("Enter apartment countRooms: ");
                String sCountRooms = sc.nextLine();
                int countRooms = Integer.parseInt(sCountRooms);

                ps = conn.prepareStatement("DELETE FROM Apartments WHERE countRooms = ?");
                try {
                    ps.setInt(1, countRooms);
                    ps.executeUpdate();
                } finally {
                    ps.close();
                }
                break;
            case "6":
                System.out.print("Enter apartment price($): ");
                String sPrice = sc.nextLine();
                Double price = Double.parseDouble(sPrice);

                ps = conn.prepareStatement("DELETE FROM Apartments WHERE price = ?");
                try {
                    ps.setDouble(1, price);
                    ps.executeUpdate();
                } finally {
                    ps.close();
                }
                break;
            default:
                return;
        }
    }

    private static void changeApartment(Scanner sc) throws SQLException {
        System.out.print("Enter apartment id: ");
        String sId = sc.nextLine();
        int id = Integer.parseInt(sId);

        System.out.println("Select the option you want to change");
        System.out.println("1: district");
        System.out.println("2: address");
        System.out.println("3: area");
        System.out.println("4: countRooms");
        System.out.println("5: price");
        System.out.print("-> ");

        String s = sc.nextLine();
        switch (s) {

            case "1":
                System.out.print("Enter apartment district: ");
                String district = sc.nextLine();

                PreparedStatement ps = conn.prepareStatement("UPDATE Apartments SET district = ? WHERE id = ?");
                try {
                    ps.setString(1, district);
                    ps.setInt(2, id);
                    ps.executeUpdate();
                } finally {
                    ps.close();
                }
                break;
            case "2":
                System.out.print("Enter apartment address: ");
                String address = sc.nextLine();

                ps = conn.prepareStatement("UPDATE Apartments SET address = ? WHERE id = ?");
                try {
                    ps.setString(1, address);
                    ps.setInt(2, id);
                    ps.executeUpdate();
                } finally {
                    ps.close();
                }
                break;
            case "3":
                System.out.print("Enter apartment area(m^2): ");
                String sArea = sc.nextLine();
                Double area = Double.parseDouble(sArea);

                ps = conn.prepareStatement("UPDATE Apartments SET area = ? WHERE id = ?");
                try {
                    ps.setDouble(1, area);
                    ps.setInt(2, id);
                    ps.executeUpdate();
                } finally {
                    ps.close();
                }
                break;
            case "4":
                System.out.print("Enter apartment countRooms: ");
                String sCountRooms = sc.nextLine();
                int countRooms = Integer.parseInt(sCountRooms);

                ps = conn.prepareStatement("UPDATE Apartments SET countRooms = ? WHERE id = ?");
                try {
                    ps.setInt(1, countRooms);
                    ps.setInt(2, id);
                    ps.executeUpdate();
                } finally {
                    ps.close();
                }
                break;
            case "5":
                System.out.print("Enter apartment price($): ");
                String sPrice = sc.nextLine();
                Double price = Double.parseDouble(sPrice);

                ps = conn.prepareStatement("UPDATE Apartments SET price = ? WHERE id = ?");
                try {
                    ps.setDouble(1, price);
                    ps.setInt(2, id);
                    ps.executeUpdate();
                } finally {
                    ps.close();
                }
                break;
            default:
                return;
        }
    }

    private static void viewApartments() throws SQLException {
        viewApartments("SELECT * FROM Apartments;");
    }

    private static void viewApartments(String str) throws SQLException {
        PreparedStatement ps = conn.prepareStatement(str);
        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();
        }
    }

    private static void viewApartmentsForParameters(Scanner sc) throws SQLException {

        System.out.println("Select the option by which will search");
        System.out.println("1: district");
        System.out.println("2: address");
        System.out.println("3: area");
        System.out.println("4: countRooms");
        System.out.println("5: price");
        System.out.print("-> ");

        String s = sc.nextLine();
        switch (s) {

            case "1":
                System.out.print("Enter apartment district: ");
                String district = sc.nextLine();

                String text = "SELECT * FROM Apartments WHERE district = " + district + ";";
                viewApartments(text);

                break;
            case "2":
                System.out.print("Enter apartment address: ");
                String address = sc.nextLine();

                text = "SELECT * FROM Apartments WHERE address = " + address + ";";
                viewApartments(text);

                break;
            case "3":
                System.out.print("Enter apartment area(m^2): ");
                String sArea = sc.nextLine();
                Double area = Double.parseDouble(sArea);

                text = "SELECT * FROM Apartments WHERE area = " + area + ";";
                viewApartments(text);

                break;
            case "4":
                System.out.print("Enter apartment countRooms: ");
                String sCountRooms = sc.nextLine();
                int countRooms = Integer.parseInt(sCountRooms);

                text = "SELECT * FROM Apartments WHERE countRooms = " + countRooms + ";";
                viewApartments(text);

                break;
            case "5":
                System.out.print("Enter apartment price($): ");
                String sPrice = sc.nextLine();
                Double price = Double.parseDouble(sPrice);

                text = "SELECT * FROM Apartments WHERE price = " + price + ";";
                viewApartments(text);

                break;
            default:
                return;
        }

    }

}