package com.gmail.shvetsova2015.inna;

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

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

    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: view all flats");
                    System.out.println("2: add flat");
                    System.out.println("3: delete flat");
                    System.out.println("4: change price");
                    System.out.println("5: view flats according to roomcount");
                    System.out.println("6: view flats according to price");
                    System.out.println("7: view flats according to district");
                    System.out.print("-> ");

                    String s = sc.nextLine();
                    switch (s) {
                        case "1":
                            viewFlats(sc);
                            break;
                        case "2":
                            addFlat(sc);
                            break;
                        case "3":
                            deleteFlat(sc);
                            break;
                        case "4":
                            changePrice(sc);
                            break;
                        case "5":
                           viewFlatRoomCount(sc);
                            break;
                        case "6":
                            viewFlatPrice(sc);
                            break;
                        case "7":
                            viewFlatDistrict(sc);
                            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 Flats");
            st.execute("CREATE TABLE flats (\n" +
                    "id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,\n" +
                    "district CHAR(30) DEFAULT NULL,\n" +
                    "street CHAR (30) DEFAULT NULL,\n" +
                    "building INT(5) DEFAULT NULL,\n" +
                    "flat INT(5) DEFAULT NULL,\n" +
                    "square DOUBLE(6,2) DEFAULT NULL,\n" +
                    "roomcount INT(2) DEFAULT NULL,\n" +
                    "price DOUBLE(10,2) DEFAULT NULL\n" +
                    ")");
        } finally {
            st.close();
        }
    }

    private static void viewFlats(Scanner sc) throws SQLException {
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM Flats");
        try {
            ResultSet rs = ps.executeQuery();
            print(rs);
        } finally {
            ps.close();
        }
    }

    private static void addFlat(Scanner sc) throws SQLException {
        System.out.print("Enter district: ");
        String district = sc.nextLine();
        System.out.print("Enter street: ");
        String street = sc.nextLine();
        System.out.print("Enter building number: ");
        String sBuilding = sc.nextLine();
        int building = Integer.parseInt(sBuilding);
        System.out.print("Enter flat number: ");
        String sFlat = sc.nextLine();
        int flat = Integer.parseInt(sFlat);
        System.out.print("Enter square: ");
        String sSquare = sc.nextLine();
        double square = Double.parseDouble(sSquare);
        System.out.print("Enter room count: ");
        String sRoomCount = sc.nextLine();
        int roomcount = Integer.parseInt(sRoomCount);
        System.out.print("Enter price: ");
        String sPrice = sc.nextLine();
        double price = Double.parseDouble(sPrice);

        PreparedStatement ps = conn.prepareStatement("INSERT INTO Flats (district, street, building, flat, square, roomcount, price) VALUES(?, ?, ?, ?, ?, ?, ?)");
        try {
            ps.setString(1, district);
            ps.setString(2, street);
            ps.setInt(3, building);
            ps.setInt(4, flat);
            ps.setDouble(5, square);
            ps.setInt(6, roomcount);
            ps.setDouble(7, price);
            ps.executeUpdate();
        } finally {
            ps.close();
        }
    }

    private static void deleteFlat(Scanner sc) throws SQLException {
        System.out.println("Enter information about flat you want to delete:");
        System.out.print("Enter flat street: ");
        String street = sc.nextLine();
        System.out.print("Enter flat building: ");
        String sBuilding = sc.nextLine();
        int building = Integer.parseInt(sBuilding);
        System.out.print("Enter flat number: ");
        String sFlat = sc.nextLine();
        int flat = Integer.parseInt(sFlat);

        PreparedStatement ps = conn.prepareStatement("DELETE FROM Flats WHERE street = ? AND building = ? AND flat = ?");
        try {
            ps.setString(1, street);
            ps.setInt(2, building);
            ps.setInt(3, flat);
            ps.executeUpdate();
        } finally {
            ps.close();
        }
    }

    private static void changePrice(Scanner sc) throws SQLException { // change age
        System.out.println("Enter information about flat you want to change:");
        System.out.print("Enter flat street: ");
        String street = sc.nextLine();
        System.out.print("Enter flat building: ");
        String sBuilding = sc.nextLine();
        int building = Integer.parseInt(sBuilding);
        System.out.print("Enter flat number: ");
        String sFlat = sc.nextLine();
        int flat = Integer.parseInt(sFlat);
        System.out.print("Enter NEW price: ");
        String sPrice = sc.nextLine();
        int price = Integer.parseInt(sPrice);

        PreparedStatement ps = conn.prepareStatement("UPDATE Flats SET price = ? WHERE street = ? AND building = ? AND flat = ?");
        try {
            ps.setInt(1, price);
            ps.setString(2, street);
            ps.setInt(3, building);
            ps.setInt(4, flat);
            ps.executeUpdate();
        } finally {
            ps.close();
        }
    }

    private static void viewFlatRoomCount(Scanner sc) throws SQLException {
        System.out.print("Enter room count: ");
        String sRoomcount = sc.nextLine();
        int roomcount = Integer.parseInt(sRoomcount);

        PreparedStatement ps = conn.prepareStatement("SELECT * FROM Flats WHERE roomcount = ?");
        try {
            ps.setInt(1, roomcount);
            ResultSet rs = ps.executeQuery();
            print(rs);
        } finally {
            ps.close();
        }
    }

    private static void viewFlatPrice(Scanner sc) throws SQLException {
        System.out.println("Enter price: ");
        System.out.print("from: ");
        String sPrice1 = sc.nextLine();
        int price1 = Integer.parseInt(sPrice1);
        System.out.print("to: ");
        String sPrice2 = sc.nextLine();
        int price2 = Integer.parseInt(sPrice2);

        PreparedStatement ps = conn.prepareStatement("SELECT * FROM Flats WHERE price > ? AND price < ?");
        try {
            ps.setInt(1, price1);
            ps.setInt(2, price2);
            ResultSet rs = ps.executeQuery();
            print(rs);
            } finally {
            ps.close();
        }
    }

    private static void viewFlatDistrict(Scanner sc) throws SQLException {
        System.out.print("Enter district: ");
        String district = sc.nextLine();

        PreparedStatement ps = conn.prepareStatement("SELECT * FROM Flats WHERE district = ?");
        try {
            ps.setString(1, district);
            ResultSet rs = ps.executeQuery();
            print(rs);
            } finally {
            ps.close();
        }
    }

    private static void print(ResultSet rs) throws SQLException {
            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();
            }
    }
}