package HotelInterface;

import java.math.BigInteger;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import static HotelInterface.CustomerDetails.customerID;


public class Jdbc {

    static String customerEmail;
    Connection myConn = null;
    Statement myStmt = null;
    ResultSet myRs = null;

    public Jdbc() throws SQLException{
        try {
//            go();
            myConn = DriverManager.getConnection("jdbc:mysql://localhost:2222/w1495485_0?zeroDateTimeBehavior=convertToNull", "w1495485", "GBlmPeUulqaX");
            myStmt = myConn.createStatement();
        } catch (Exception exc) {
            exc.printStackTrace();
        }
    }

public String getCustomerID(){
    return customerEmail;

}

    public void RegisterStaff(String FirstName, String LastName, String UserName, String Password, String StaffType) {
        try {
            String sql = "insert into Staff " + " (FirstName,LastName,UserName,Password,StaffType)" +
                    " values ('" + FirstName + "', '" + LastName + "','" + UserName + "','" + getMD5(Password) + "','" + StaffType + "')";
            myStmt.executeUpdate(sql);
            System.out.println("Inserted");

        } catch (Exception exc) {
            exc.printStackTrace();
        }
    }

    public Boolean StaffLogin(String user, String pass) {
        try {
            String sql = "SELECT UserName,Password FROM Staff where UserName=? and Password=?";
            PreparedStatement ps = null;
            ps = myConn.prepareStatement(sql);
            ps.setString(1, user);
            ps.setString(2, getMD5(pass));
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

//    public int getCustomerID(){}

    public Boolean CustomerLogin(String user, String pass) {
        try {
            String sql = "SELECT Email,Password FROM Customer  where Email=? and Password=?";
            PreparedStatement ps = null;
            ps = myConn.prepareStatement(sql);
            ps.setString(1, user);
            ps.setString(2, getMD5(pass));
            ResultSet rs = ps.executeQuery();




            if (rs.next()) {

                String cust = new String(rs.getObject(1).toString());
                customerEmail  =  cust;
                System.out.println("Customer ID in JDBC = " +customerEmail);
                return true;
            } else {
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }



    public void ShowTable() {
        try {
            myRs = myStmt.executeQuery("select * from Staff");
            int n = 0;
            while (myRs.next())   // while there's still some more results of the query...
            {
                int numColumns = myRs.getMetaData().getColumnCount();   // get how many cols in this entry
                n++;
                System.out.print("" + n);
                for (int i = 1; i <= numColumns; i++)       // loop through the cols to print them
                {                                               // Column numbers start at 1.
                    System.out.print("  " + myRs.getObject(i));
                }
                System.out.println("");   // print a new line at the end of the entry.
            }
        } catch (Exception exc) {
            exc.printStackTrace();
        }
    }

    public void Delete(String ID) {
        try {
            String sql = "delete from Staff where ID = '" + ID + "'";
            int rowsAffeted = myStmt.executeUpdate(sql);
        } catch (Exception exc) {
            exc.printStackTrace();
        }
    }

    public static String getMD5(String input) {
        try {
            MessageDigest md = MessageDigest.getInstance("MD5");
            byte[] messageDigest = md.digest(input.getBytes());
            BigInteger number = new BigInteger(1, messageDigest);
            String hashtext = number.toString(16);
            // Now we need to zero pad it if you actually want the full 32 chars.
            while (hashtext.length() < 32) {
                hashtext = "0" + hashtext;
            }
            return hashtext;
        } catch (NoSuchAlgorithmException e) {
            throw new RuntimeException(e);
        }
    }

    public void Update(String field, String value, int id) {
        try {
            String sql = "update Staff set " + field + "='" + value + " ' where id=" + id + "";
            int rowsAffected = myStmt.executeUpdate(sql);
            System.out.println("Rows affected: " + rowsAffected);
            System.out.println("Updated");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void addDate(String date, String date1) throws ParseException, SQLException {

        java.util.Date utilDate = new SimpleDateFormat("yyyy-MM-dd").parse(date);
        java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());

        java.util.Date utilDate1 = new SimpleDateFormat("yyyy-MM-dd").parse(date1);
        java.sql.Date sqlDate1 = new java.sql.Date(utilDate.getTime());

        PreparedStatement p = myConn.prepareStatement("INSERT INTO  `w1495485_0`.`Booking` (`BookingID` ,`StartDate` ,`EndDate` ,`CustomerID` ,`RoomID`)VALUES (NULL , ?, ?,  '1',  '1');");

        p.setDate(1, sqlDate);
        p.setDate(2, sqlDate1);
        p.execute();

//        try {
//            LocalDate date = checkin.getValue();
//            System.out.println(date);
//            db = new Jdbc();
//            db.addDate(date.toString());
//
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } catch (ParseException e) {
//            e.printStackTrace();
//        }
    }

    public List RoomCheck(String column, String roomSize) {
        List rowValues = null;
        try {
            rowValues = new ArrayList();
            myRs = null;
            myRs = myStmt.executeQuery("SELECT " + column + " FROM Booking,Room where Room.RoomSize = \"" + roomSize + "\"");
            //select startdate from booking where roomid = (select roomid from room where roomsize = large)
            while (myRs.next()) {
                rowValues.add(myRs.getString(1));
            }
//            for (int i = 0; i < rowValues.size(); i++) {
//                System.out.println(rowValues.get(i));
//            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rowValues;
    }

    public void addUserToDB(String fname, String lname, String password, String Address, String email, String Country, String postCode, String city){
        String addingSQL = "insert into Customer " + " (FirstName,LastName,CustomerAddress,City,Postcode,Country,Email,Password)"
                + " values ('" + fname + "', '" + lname + "', '" + Address + "', '" + city + "', '" + postCode + "', '" + Country + "', '" + email + "', '" + password + "')";
        try {System.out.println("GOING TO ADD TO DB");
            myStmt.execute(addingSQL);
            System.out.println("GOING TO ADD TO DB");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("--------- SOMETHIGN WRONG IN ADD QUERY");
        }




      /*  String FirstName, String LastName, String UserName, String Password, String StaffType) {
            try {
                String sql = "insert into Staff " + " (FirstName,LastName,UserName,Password,StaffType)" +
                        " values ('" + FirstName + "', '" + LastName + "','" + UserName + "','" + getMD5(Password) + "','" + StaffType + "')";
                myStmt.executeUpdate(sql);
                System.out.println("Inserted");*/
    }

    public List RoomCheck1(String column, String roomSize) {
        List rowValues = null;
        try {
            rowValues = new ArrayList();
            myRs = null;
            myRs = myStmt.executeQuery("SELECT RoomID FROM Room where RoomSize = \"" + roomSize + "\"");
            while (myRs.next()) {
                rowValues.add(myRs.getString(1));
            }
//            for (int i = 0; i < rowValues.size(); i++) {
//                System.out.println(rowValues.get(i));
//            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rowValues;
    }

    public void confirmPaymentAndBook(String issueda, String cardHolder, String cardType, String cardNumberId, String expiryDate, String issueNumber, String ccvcardnumber, String checkInDate, String checkOutDate, String typeOfRoom) {


        String addingSQL = "insert into Payment " + " (CardNumber,ExpiryDate,CardCCV,CardHolderName,CardType,IssueNumber,IssueDate)"
                + " values ('" + cardNumberId + "', '" + expiryDate + "', '" + ccvcardnumber + "', '" + cardHolder + "', '" + cardType + "', '" + issueNumber + "', '" + issueda + "')";
        try {System.out.println("GOING TO ADD CARD TO DB");
            myStmt.execute(addingSQL);
            System.out.println("ADDED CARD TO DB");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("--------- SOMETHIGN WRONG IN ADD QUERY");
        }
    }
}