package Hotel_Menagement;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

import com.mysql.jdbc.PreparedStatement;

public class Hotel {

    public static void main(String[] args) throws Exception {

        // create scanner object, message to user and take user input
        Scanner input = new Scanner(System.in);
        System.out
                .println("Enter your name, username or identity card number: ");
        String name = input.nextLine();
        searchDatabase(name);

        System.out
                .println(" Enter user name you want to check out form the hotel.");
        String nameCheckOut = input.nextLine();
        userCheckOut(nameCheckOut);

        input.close();
    }

    // ID, First Name, Last Name, Gender, Identity Card, Age, Room Number, Room
    // Type, CheckIn Time, User Name,Password

    /*
     * @author Maja Vasilic
     *
     * providing information from database based on entry
     */
    public static void searchDatabase(String s) throws Exception {
        try {
            // estalish connection to the database invoking method
            Connection con = getConnection();
            // var query with SQL query
            String query = "SELECT * FROM hotel.information WHERE FirstName = ? OR IdentityCard= ? OR UserName = ?";
            // create statement
            PreparedStatement statement = (PreparedStatement) con
                    .prepareStatement(query);

            // actual values are set to paramiters
            statement.setString(1, s);
            statement.setString(2, s);
            statement.setString(3, s);
            // query execution
            ResultSet result = statement.executeQuery();

            ResultSetMetaData rsmd = result.getMetaData();
            int columnCount = rsmd.getColumnCount();

            String format = "%-20s";

            // print column names
            for (int i = 1; i <= columnCount; i++) {
                System.out.printf(format, rsmd.getColumnLabel(i));
            }
            System.out.println();

            // print underline
            for (int i = 1; i <= columnCount * 30; i++) {
                System.out.print("_");
            }
            System.out.println();

            // print information from database
            while (result.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    System.out.printf(format, result.getString(i));
                }
                System.out.println();
            }
        } catch (Exception e) {
            System.out.println(e);
        }

    }

    /**
     * author Maja Vasilic
     *
     * Method copying information from current database (hotel) to the archive
     * (hotel_archive) and removes user from current
     */
    public static void userCheckOut(String name) {

        try {
            // estalish connection to the database hotel invoking method
            Connection conn = getConnection();

            // create statement variable
            Statement stSelect = conn.createStatement();

            // query execution (find row with specified name)
            ResultSet result = stSelect
                    .executeQuery("SELECT * FROM hotel.information WHERE FirstName = '"
                            + name + "'");

            System.out.println("Sending data to the archive.");

            // create connection with db hotel_archve
            Connection conn1 = getConnection1();

            // create prepared statement to insert values in
            // information.hotel_archive from information.hotel
            PreparedStatement stInsert = (PreparedStatement) conn1
                    .prepareStatement("INSERT INTO information.hotel_archive (FirstName, LastName,"
                            + " Gender, IdentityCard, Age) VALUES (?, ?, ?, ?, ?)");

            // set values from one db to another
            stInsert.setString(1, result.getString(2));
            stInsert.setString(2, result.getString(3));
            stInsert.setString(3, result.getString(4));
            stInsert.setString(4, result.getString(5));
            stInsert.setString(5, result.getString(6));

            // how we can remove user from current table for users
            stSelect.executeQuery("DELETE FROM information.hotel WHERE FirstName = '"
                    + name + "'");

            // print message
            System.out.println("User " + name + " is removed.");

        } catch (Exception e) {
            System.out.println(e);
        }

    }

    /** establish connection with database hotel */
    public static Connection getConnection() throws Exception {
        try {
            // load jdbc driver
            Class.forName("com.mysql.jdbc.Driver");

            // establish connection using url, username and password
            Connection con = DriverManager.getConnection(
                    "jdbc:mysql://localhost/hotel", "root", "maja");
            return con;

        } catch (Exception e) {
            System.out.println(e);
        }
        return null;
    }

    /** establish connection with database hotel_archive */
    public static Connection getConnection1() throws Exception {
        try {
            // load jdbc driver
            Class.forName("com.mysql.jdbc.Driver");

            // establish connection using url, username and password
            Connection con = DriverManager.getConnection(
                    "jdbc:mysql://localhost/hotel_archive", "root", "maja");
            return con;

        } catch (Exception e) {
            System.out.println(e);
        }
        return null;
    }
}