package myGrId;

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

/**
 * Created by genalee on 04.02.16.
 */
public class MainClass {
    static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/mydbTest";
    static final String DB_USER = "root";
    static final String DB_PASSWORD = "*******";

    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 flat");
                    System.out.println("2: view flat with params");
                    System.out.print("-> ");

                    String s = sc.nextLine();
                    switch (s) {
                        case "1":
                            addFlat(sc);
                            break;
                        case "2":
                            viewFlats(sc);
                            break;
                        default:
                            return;
                    }
                }
            } finally {
                sc.close();
                if (conn != null) conn.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return;
        }
    }

    private static void viewFlats(Scanner scan) throws SQLException {

        while (true) {
            System.out.println("1: Выбор квартир по городу");
            System.out.println("2: Выбор квартир по адресу (по фрагменту)");
            System.out.println("3: Выбор по кол-ву комнат");
            System.out.println("4: Выбор по цене (больше или равно)");
            System.out.println("5: Выбор по цене (меньше или равно)");
            System.out.print("-> ");

            String s = scan.nextLine();
            String sql="";
            String price = "";
            switch (s) {
                case "1":
                    System.out.print("Введите город: ");
                    String city = scan.nextLine();
                    sql = "SELECT * FROM flats WHERE UPPER(city)=\""+city.toUpperCase()+"\"";
                    viewQuery(sql);
                    System.out.println();
                    break;
                case "2":
                    System.out.print("Введите фрагмент адреса: ");
                    String address = scan.nextLine();
                    sql = "SELECT * FROM flats WHERE UPPER(address) like \'%"+address.toUpperCase()+"%\'";
                    viewQuery(sql);
                    System.out.println();
                    break;
                case "3":
                    System.out.print("Введите кол-во комнат: ");
                    String rooms = scan.nextLine();
                    sql = "SELECT * FROM flats WHERE room_count="+rooms;
                    viewQuery(sql);
                    System.out.println();
                    break;
                case "4":
                    System.out.print("Введите минимальную цену: ");
                    price = scan.nextLine();
                    sql = "SELECT * FROM flats WHERE price >="+price;
                    viewQuery(sql);
                    System.out.println();
                    break;
                case "5":
                    System.out.print("Введите максимальную цену: ");
                    price = scan.nextLine();
                    sql = "SELECT * FROM flats WHERE price <="+price;
                    viewQuery(sql);
                    System.out.println();
                    break;
                default:
                    return;
            }
        }
    }

    private static void viewQuery(String sql) throws SQLException {
        PreparedStatement ps = conn.prepareStatement(sql);
        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(); // rs can't be null according to the docs
            }
        } finally {
            ps.close();
        }

    }
    private static void initDB() throws SQLException {
        Statement s = conn.createStatement();
        try{
            s.execute("CREATE TABLE IF NOT EXISTS flats (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, city VARCHAR(20) NOT NULL, " +
                    "address VARCHAR (30) NOT NULL, area FLOAT(10,2), room_count INTEGER, price FLOAT (15,3))");
        } finally {
            s.close();
        }


    }

    private static void addFlat(Scanner scan) throws SQLException {
        System.out.print("Введите город: ");
        String city = scan.nextLine();
        System.out.print("Введите адрес (улицу, номер дома, номер квартиры): ");
        String address = scan.nextLine();
        System.out.print("Введите площадь квартиры: ");
        String sArea = scan.nextLine();
        Float area = Float.parseFloat(sArea);
        System.out.print("Введите кол-во комнат: ");
        String sRooms = scan.nextLine();
        int rooms = Integer.parseInt(sRooms);
        System.out.print("Введите цену: ");
        String sPrice = scan.nextLine();
        Float price = Float.parseFloat(sPrice);

        String sql = "INSERT into flats (city, address, area, room_count, price) VALUES (?,?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        try {
            ps.setString(1,city);
            ps.setString(2,address);
            ps.setFloat(3,area);
            ps.setInt(4,rooms);
            ps.setFloat(5, price);
            System.out.println("Вставлено "+ ps.executeUpdate()+ " записей");
        } finally {
            ps.close();
        }


    }
}