package ua.kiev.prog;

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

public class Main {
    static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/flats";
    static final String DB_USER = "Server";
    static final String DB_PASSWORD = "12345";

    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 client");
                    System.out.println("2: add random clients");
                    System.out.println("3: delete client");
                    System.out.println("4: change client");
                    System.out.println("5: view clients");
                    System.out.println("6: view clients of filter");
                    System.out.print("-> ");

                    String s = sc.nextLine();
                    switch (s) {
                        case "1":
                            addClient(sc);
                            break;
                        case "2":
                            insertRandomClients(sc);
                            break;
                        case "3":
                            deleteClient(sc);
                            break;
                        case "4":
                            changeClient(sc);
                            break;
                        case "5":
                            viewClients();
                            break;
                        case "6":
                            viewClientsFilter(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 Clients (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, age INT)");
            st.execute("CREATE TABLE flats (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, district VARCHAR(20) NOT NULL, address VARCHAR(50) NOT NULL, square INT NOT NULL, quantityRooms INT NOT NULL, price INT NOT NULL)");
        } finally {
            st.close();
        }
    }

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

        System.out.print("Enter client address: ");
        String address = sc.nextLine();

        System.out.print("Enter client square:");
        String sSquare = sc.nextLine();
        int square = Integer.parseInt(sSquare);

        System.out.print("Enter client quantityRooms:");
        String sQnt = sc.nextLine();
        int quantityRooms = Integer.parseInt(sQnt);

        System.out.print("Enter client price:");
        String sPrice = sc.nextLine();
        int price = Integer.parseInt(sPrice);


        PreparedStatement ps = conn.prepareStatement("INSERT INTO flats (district, address, square, quantityRooms, price) VALUES(?, ?, ?, ?, ?)");
        try {
            ps.setString(1, district);
            ps.setString(2, address);
            ps.setInt(3, square);
            ps.setInt(4, quantityRooms);
            ps.setInt(5, price);
            ps.executeUpdate(); // for INSERT, UPDATE & DELETE
        } finally {
            ps.close();
        }
    }

    private static void deleteClient(Scanner sc) throws SQLException {
        System.out.print("Enter flat id: ");
        String name = sc.nextLine();

        PreparedStatement ps = conn.prepareStatement("DELETE FROM flats WHERE id = ?");
        try {
            ps.setString(1, name);
            ps.executeUpdate(); // for INSERT, UPDATE & DELETE
        } finally {
            ps.close();
        }
    }

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

        System.out.print("Enter client address: ");
        String address = sc.nextLine();

        System.out.print("Enter client square:");
        String sSquare = sc.nextLine();
        int square = Integer.parseInt(sSquare);

        System.out.print("Enter client quantityRooms:");
        String sQnt = sc.nextLine();
        int quantityRooms = Integer.parseInt(sQnt);

        System.out.print("Enter client price:");
        String sPrice = sc.nextLine();
        int price = Integer.parseInt(sPrice);

        PreparedStatement ps = conn.prepareStatement("UPDATE district, address, square, quantityRooms, price WHERE id = ?");
        try {
            ps.executeUpdate(); // for INSERT, UPDATE & DELETE
        } finally {
            ps.close();
        }
    }

    private static void insertRandomClients(Scanner sc) throws SQLException {
        System.out.print("Enter flats count: ");
        String sCount = sc.nextLine();
        int count = Integer.parseInt(sCount);
        Random rnd = new Random();

        conn.setAutoCommit(false); // enable transactions
        try {
            try {
                PreparedStatement ps = conn.prepareStatement("INSERT INTO flats (district, address, square, quantityRooms, price) VALUES(?, ?, ?, ?, ?)");
                try {
                    for (int i = 0; i < count; i++) {
                        ps.setInt(2, rnd.nextInt(100));
                        ps.setString(1, "district" + i);
                        ps.setString(2, "address" + i);
                        ps.setInt(3, rnd.nextInt(100));
                        ps.setInt(4, rnd.nextInt(5));
                        ps.setInt(5, rnd.nextInt(100000));
                        ps.executeUpdate();
                    }
                    conn.commit();
                } finally {
                    ps.close();
                }
            } catch (Exception ex) {
                conn.rollback();
            }
        } finally {
            conn.setAutoCommit(true); // return to default mode
        }
    }

    private static void viewClients() throws SQLException {
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM flats");
        try {
            // table of data representing a database result set,
            ResultSet rs = ps.executeQuery();
            try {
                // can be used to get information about the types and properties of the columns in a ResultSet object
                ResultSetMetaData md = rs.getMetaData();

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

                while (rs.next()) {
                    for (int i = 1; i <= md.getColumnCount(); i++) {
                        System.out.print(rs.getString(i) + "\t\t\t");
                    }
                    System.out.println();
                }
            } finally {
                rs.close(); // rs can't be null according to the docs
            }
        } finally {
            ps.close();
        }
    }

    private static void viewClientsFilter(Scanner sc) throws SQLException {

        System.out.print("Enter low price:");
        String lPrice = sc.nextLine();
        int lP = Integer.parseInt(lPrice);
        System.out.print("Enter high price:");
        String hPrice = sc.nextLine();
        int hP = Integer.parseInt(hPrice);

        PreparedStatement ps = conn.prepareStatement("SELECT * FROM flats WHERE (price>? AND price<?)");
        try {
            ps.setInt(1, lP);
            ps.setInt(2, hP);
            // table of data representing a database result set,
            ResultSet rs = ps.executeQuery();
            try {
                // can be used to get information about the types and properties of the columns in a ResultSet object
                ResultSetMetaData md = rs.getMetaData();

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

                while (rs.next()) {
                    for (int i = 1; i <= md.getColumnCount(); i++) {
                        System.out.print(rs.getString(i) + "\t\t\t");
                    }
                    System.out.println();
                }
            } finally {
                rs.close(); // rs can't be null according to the docs
            }
        } finally {
            ps.close();
        }
    }
}