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


public class Main {
    static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/Flats";
    static final String DB_USER = "root";
    static final String DB_PASSWORD = "root";

    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 appartment");
                    System.out.println("2: filter appartments by area and rooms count");
                    System.out.println("3: delete appartment");
                    System.out.println("4: change appartment");
                    System.out.println("5: view appartments");
                    System.out.print("-> ");

                    String s = sc.nextLine();
                    switch (s) {
                        case "1":
                            addAppartments();
                            break;
                        case "2":
                            filterAppartmentsByAreaAndRooms();
                            break;
                        case "3":
                            deleteAppartment();
                            break;
                        case "4":
                            changeAppartment();
                            break;
                        case "5":
                            viewAllAppartments();
                            break;
                        default:
                            return;
                    }
                }
            } finally {
                sc.close();
                if (conn != null) conn.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return;
        }
    }


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

            st.execute("DROP TABLE IF EXISTS  Appartments");
            st.execute("DROP TABLE IF EXISTS Areas");
            st.execute("CREATE TABLE Areas (a_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                                            area VARCHAR(50) NOT NULL)");
            st.execute("CREATE TABLE Appartments (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                                                  address VARCHAR(50) NOT NULL,
                                                  square DOUBLE NOT NULL, rooms INT NOT NULL,
                                                  price DOUBLE NOT NULL, area_id INT NOT NULL ,
                                                  FOREIGN KEY (area_id) REFERENCES Areas(a_id))");

            PreparedStatement ps = conn.prepareStatement("INSERT INTO Areas (area) VALUES(?)");
            try {
                ps.setString(1, "Goloseevo");
                ps.executeUpdate();
                ps.setString(1, "Svyatoshin");
                ps.executeUpdate();
                ps.setString(1, "Obolon'");
                ps.executeUpdate();

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

    private static void addAppartments() throws SQLException {
        Scanner sc = new Scanner(System.in);
        System.out.println("For adding new appartment enter address:");
        String address = sc.nextLine();
        System.out.println("Enter square:");
        double square = sc.nextDouble();
        System.out.println("Enter count of rooms:");
        int rooms = sc.nextInt();
        System.out.println("Enter price:");
        double price = sc.nextDouble();
        System.out.println("1->Goloseevo\n2->Svyatoshin\n3->Obolon'");
        System.out.println("Enter id of area:");
        int area_id = sc.nextInt();

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

    private static void filterAppartmentsByAreaAndRooms() throws SQLException {
        Scanner sc = new Scanner(System.in);
        System.out.println("1->Goloseevo\n2->Svyatoshin\n3->Obolon'");
        System.out.println("Enter id of area:");
        int area_id = sc.nextInt();
        System.out.println("Enter count of rooms:");
        int rooms = sc.nextInt();

        PreparedStatement ps = conn.prepareStatement("SELECT ar.area, app.address, app.square, app.rooms, app.price
                                                      FROM Appartments app INNER JOIN Areas ar
                                                      ON app.area_id=ar.a_id WHERE app.rooms=? AND ar.a_id=?");
        try {
            ps.setInt(1, rooms);
            ps.setInt(2, area_id);
            ps.execute();

           ResultSet rs = ps.executeQuery();
            if (rs.getFetchSize() == 0) {
                System.out.println("There are no such appartments in this area. Try to change filter conditions!");
            } else {
                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 deleteAppartment() throws SQLException {
        Scanner sc = new Scanner(System.in);
        System.out.println("Enter id of appartment you need to delete");
        int deleteId = sc.nextInt();
        PreparedStatement ps = conn.prepareStatement("DELETE FROM Appartments WHERE id =?");
        try {
            ps.setInt(1, deleteId);
            ps.executeUpdate();
        } finally {
           ps.close();

        }

    }

    private static void changeAppartment() throws SQLException {
        Scanner sc = new Scanner(System.in);
        System.out.println("Enter id of appartment you need to change");
        int changeId = Integer.valueOf(sc.nextLine());
        System.out.println("Enter new address:");
        String address = sc.nextLine();
        System.out.println("Enter new square:");
        double square = sc.nextDouble();
        System.out.println("Enter new count of rooms:");
        int rooms = sc.nextInt();
        System.out.println("Enter new price:");
        double price = sc.nextDouble();

        PreparedStatement ps = conn.prepareStatement("UPDATE Appartments SET address = ?, square= ?, rooms = ?, price = ? WHERE id=?");
        try {
            ps.setString(1, address);
            ps.setDouble(2, square);
            ps.setInt(3, rooms);
            ps.setDouble(4, price);
            ps.setInt(5, changeId);
            ps.executeUpdate();
        } finally {
            ps.close();

        }
    }

    private static void viewAllAppartments() throws SQLException {
        PreparedStatement ps = conn.prepareStatement("SELECT ar.area, app.address, app.square, app.rooms, app.price FROM Appartments app INNER JOIN Areas ar ON app.area_id=ar.a_id");
                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();
        }
    }
}