package ua.kiev.prog;

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

/**
 * Created by Zakusylo.p on 24.03.2017.
 */
public class ApartmentMain {
    private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/dbapartment";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "03051993_Zakusylo";

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

            while (true) {
                System.out.println("1: Delete load table of database and start new");
                System.out.println("2: Add new apartment");
                System.out.println("3: Delete apartment");
                System.out.println("4: Change price of apartment");
                System.out.println("5: View all apartments");
                System.out.println("6: View apartments by parameters");
                System.out.print("-> ");

                String s = sc.nextLine();
                switch (s) {
                    case "1":
                        deleteOldTableAndCreateNew();
                        break;
                    case "2":
                        addNewApartment(sc);
                        break;
                    case "3":
                        deleteApartment(sc);
                        break;
                    case "4":
                        changePriceOfApartment(sc);
                        break;
                    case "5":
                        showDataBase();
                        break;
                    case "6":
                        showDataBaseByParamets(sc);
                        break;
                    default:
                        return;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void deleteOldTableAndCreateNew() {
        try (Statement st = conn.createStatement()) {
            st.execute("DROP TABLE IF EXISTS Apartments");
            st.execute("CREATE TABLE Apartments(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, area VARCHAR(128) NOT NULL, address VARCHAR(128) NOT NULL," +
                    "square DOUBLE NOT NULL,rooms INT NOT NULL, price INT NOT NULL )");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void addNewApartment(Scanner sc) {
        System.out.print("1: Enter area: ");
        String area = sc.nextLine();
        System.out.print("2: Enter address: ");
        String address = sc.nextLine();
        System.out.print("3: Enter square (For ex. 55.55): ");
        double square = sc.nextDouble();
        System.out.print("4: Enter number of rooms:");
        int rooms = sc.nextInt();
        System.out.print("5: Enter price: ");
        int price = sc.nextInt();

        try (PreparedStatement ps = conn.prepareStatement("INSERT INTO Apartments (area,address,square,rooms, price) " +
                "VALUES (?,?,?,?,?)")) {
            ps.setString(1, area);
            ps.setString(2, address);
            ps.setDouble(3, square);
            ps.setInt(4, rooms);
            ps.setInt(5, price);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println();
    }

    private static void deleteApartment(Scanner sc) {
        System.out.print("1: Enter id of apartment: ");
        int id = sc.nextInt();

        try (PreparedStatement ps = conn.prepareStatement("DELETE FROM Apartments WHERE id=?")) {
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println();
    }

    private static void changePriceOfApartment(Scanner sc) {
        System.out.print("1: Enter id of apartment: ");
        int id = sc.nextInt();
        System.out.print("2: Enter new price: ");
        int price = sc.nextInt();

        try (PreparedStatement ps = conn.prepareStatement("UPDATE Apartments SET price=? WHERE id=?")) {
            ps.setInt(1, price);
            ps.setInt(2, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println();
    }

    private static void showDataBase() {
        try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM Apartments")) {

            try (ResultSet rs = ps.executeQuery()) {
                ResultSetMetaData rsmd = rs.getMetaData();

                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    System.out.print(rsmd.getColumnName(i) + "\t\t");
                }
                System.out.println();
                System.out.println();

                while (rs.next()){
                    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                        System.out.print("| " + rs.getString(i)+" ");
                    }
                    System.out.println();

                }

            }catch (SQLException e){
                e.printStackTrace();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println();
    }

    private static void showDataBaseByParamets(Scanner sc) {
        System.out.print("Enter one parameter of searching or more parameters separated by commas: ");
        String parametrs = sc.nextLine();

        String[] arr = parametrs.split(",");

        try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM Apartments")) {

            try (ResultSet rs = ps.executeQuery()) {
                ResultSetMetaData rsmd = rs.getMetaData();

                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    for (int j = 0; j < arr.length; j++) {
                        if (rsmd.getColumnName(i).equals(arr[j])) {
                            System.out.print(rsmd.getColumnName(i) + "\t\t\t");
                        }
                    }
                }
                System.out.println();

                while (rs.next()){
                    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                        for (int j = 0; j < arr.length; j++) {
                            if (rsmd.getColumnName(i).equals(arr[j])) {
                                System.out.print("| " + rs.getString(i) + "\t\t");
                            }
                        }
                    }
                    System.out.println();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println();
    }
}