import java.sql.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.Scanner;

public class Main {

    static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/sotdb";
    static final String DB_USER = "root";
    static final String DB_PASSWORD = "root";

    static Connection conn;

    public static void main(String[] args) {

        Scanner scan = new Scanner(System.in);

        try {
            try {


                conn = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
                //initDB();

                while (true) {

                    System.out.println("1: Add to db");
                    System.out.println("2: Remove from db");
                    System.out.println("3: Search by Parameter");
                    System.out.println("-> ");

                    String s = scan.nextLine();
                    switch (s) {
                        case "1":
                            addFlat(scan);
                            break;
                        case "2":
                            removeFlat(scan);
                            break;
                        case "3":
                            parameterViewer(scan);
                            break;
                    }
                }
            } finally {
                scan.close();
                if (conn != null) conn.close();
            }
        } catch (SQLException e) {
            e.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 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY," +
                    "district VARCHAR(20) DEFAULT NULL, address VARCHAR(20) " +
                    "DEFAULT NULL, area INT DEFAULT NULL, rooms INT DEFAULT NULL, price INT DEFAULT NULL)");
        }finally {
            st.close();
        }
    }

    private static void addFlat(Scanner scan) throws SQLException{
        System.out.println("Enter district: ");
        String district = scan.nextLine();
        System.out.println("Enter address: ");
        String address = scan.nextLine();
        System.out.println("Enter floor area: ");
        String sArea = scan.nextLine();
        int area = Integer.parseInt(sArea);
        System.out.println("Enter number of rooms: ");
        String sRooms = scan.nextLine();
        int rooms = Integer.parseInt(sRooms);
        System.out.println("Enter price: ");
        String sPrice = scan.nextLine();
        int price = Integer.parseInt(sPrice);

        PreparedStatement ps = conn.prepareStatement("INSERT INTO Flats (district, address, area, rooms, price)" +
                "VALUES (?, ?, ?, ?, ?)");
        try {
            ps.setString(1, district);
            ps.setString(2, address);
            ps.setInt(3, area);
            ps.setInt(4, rooms);
            ps.setInt(5, price);
            ps.executeUpdate();

        } finally {
            ps.close();
        }
    }

    private static void removeFlat(Scanner scan) throws SQLException{

        System.out.println("Which district are you deleting from?");
        String district = scan.nextLine();

        PreparedStatement ps = conn.prepareStatement("DELETE FROM Flats WHERE district = ?");
        try{

            ps.setString(1, district);
            ps.executeUpdate();
        }finally {
            ps.close();
        }
    }

    private static void parameterViewer(Scanner scan) throws SQLException{
        int intValue = 0;
        System.out.println("Search by district, address, area, rooms, or price?");
        String parameter = scan.nextLine();
        System.out.println("Enter value: ");
        String value = scan.nextLine();
        if (org.apache.commons.lang3.StringUtils.isNumeric(value)){
            intValue = Integer.parseInt(value);
        }

        PreparedStatement ps = conn.prepareStatement("select * from flats WHERE " + parameter + " = ?");

        if (intValue != 0){
            ps.setInt(1, intValue);
        } else {
            ps.setString(1, value);
        }

        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();
        }
    }
}