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 {
    /*
     * maja evo probaj ovaj dio ---pretrazivanje baze po imenu/broju lične
     * karte/usernameu searchDatabase(String s); imas cak primjer na github
     * linku ali jedino kod "na suho" mozes radit
     */
    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
            // prepare statement
            PreparedStatement statement = (PreparedStatement) getConnection()
                    .prepareStatement(
                            "SELECT * FROM  information WHERE FirstName = ? OR IdentityCard= ? OR UserName = ?");

            // 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 "information" table to the "archive"
     * table and removes user from "information"
     * @throws Exception
     */
    public static void userCheckOut(String name) throws Exception {

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

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

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

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

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

            // set values (get values from one table 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.setInt(5, result.getInt(6));

            // execute query to insert values in the archive to the information
            stInsert.executeQuery();

            // 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.");
            conn.close();

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            if (conn != null) {
                // close connection with the database
                conn.close();
            }
        }
    }

    /** establish connection with database */
    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;
    }
}