package com.flats;

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

/**
 * Created by Andrey on 07.02.2016.
 */
public class Main {
    static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/mydb";
    static final String DB_USER = "root";
    static final String DB_PASSWORD = "admin";

    static Connection conn;

    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        try {
            try{
               conn = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
                while (true){
                    System.out.println("1: search flats by district");
                    System.out.println("2: search flats by address");
                    System.out.println("3: search flats by area");
                    System.out.println("4: search flats by rooms quantity");
                    System.out.println("5: search flats by prices");
                    System.out.println("->");
                    String command = scanner.nextLine();
                    switch (command){
                        case "1":
                            searchByString(scanner, command);
                            break;
                        case "2":
                            searchByString(scanner, command);
                            break;
                        case "3":
                            searchByNumeric(scanner, command);
                            break;
                        case "4":
                            searchByNumeric(scanner, command);
                            break;
                        case "5":
                            searchByNumeric(scanner, command);
                            break;
                        default: return;
                    }
                }
            }
            finally {
                conn.close();
            }

        }
        catch (SQLException ex){
            ex.printStackTrace();
            return;
        }
    }

    private static void searchByString(Scanner scanner, String option) {
        String startStatement = "SELECT * FROM Flats WHERE "+ (option.equals("1") ? "district " : "address ");
        String condition, endStatement;
        System.out.println("Select the search option:");
        System.out.println("1: exact match");
        System.out.println("2: starting from...");
        System.out.println("3: by entry");
        String select = scanner.nextLine();
        switch (select){
            case "1":
                System.out.println("Input exact "+(option.equals("1") ? " name of district:" : "address:"));
                condition = scanner.nextLine();
                endStatement = "= '"+condition+"'";
                break;
            case "2":
                System.out.println("Input starting of "+(option.equals("1") ? " name of district:" : "address:"));
                condition = scanner.nextLine();
                endStatement = "LIKE '"+condition+"%'";
                break;
            case "3":
                System.out.println("Input part of "+(option.equals("1") ? " name of district:" : "address:"));
                condition = scanner.nextLine();
                endStatement = "LIKE '%"+condition+"%'";
                break;
            default: return;
        }
        String statement = startStatement.concat(endStatement);
        try {
            showResult(statement);
        }
        catch (SQLException ex){
            ex.printStackTrace();
        }
    }

    private static void searchByNumeric (Scanner scanner, String option) {
        String startStatement = "SELECT * FROM Flats WHERE "+ (option.equals("3") ? "square " : (option.equals("4") ? "rooms " : "price "));
        String condition, endStatement;
        System.out.println("Select the search option:");
        System.out.println("1: equals");
        System.out.println("2: more then");
        System.out.println("3: less then");
        System.out.println("4: between");
        String select = scanner.nextLine();
        switch (select){
            case "1":
                System.out.println("Input exact "+(option.equals("3") ? "area:" : (option.equals("4") ? "rooms:" : "price:")));
                condition = scanner.nextLine();
                endStatement = "= "+condition;
                break;
            case "2":
                System.out.println("More then ...");
                condition = scanner.nextLine();
                endStatement = " > "+condition;
                break;
            case "3":
                System.out.println("Less then...");
                condition = scanner.nextLine();
                endStatement = " < "+condition;
                break;
            case "4":
                System.out.println("More then...");
                condition = scanner.nextLine();
                System.out.println("and less then...");
                String condition2 = scanner.nextLine();
                endStatement = " BETWEEN "+condition+" AND "+condition2;
                break;
            default: return;
        }
        String statement = startStatement.concat(endStatement);
        try {
            showResult(statement);
        }
        catch (SQLException ex){
            ex.printStackTrace();
        }
    }
    static void showResult(String statement) throws SQLException{
        PreparedStatement ps = conn.prepareStatement(statement);
        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();
        }

    }

}