package com.hotelmanagement.dao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
import java.time.temporal.ChronoUnit;
import java.util.ArrayList;
import java.util.Locale;
import java.util.UUID;

import com.hotel.bean.Admin;
import com.hotel.bean.Billing;
import com.hotel.bean.Booking;
import com.hotel.bean.Customer;
import com.hotel.bean.Payment;
import com.hotel.bean.Reservation;
import com.hotelmanagement.util.DBConnection;
public class UserDAODerbyDatabaseImplementation implements UserDao {
	@Override
    public Customer validateUser(String CustomerId, String password) {
        Connection connection = null;
        Customer user = null;

        try {
            connection = DBConnection.getDBConnection();

            String sql = "SELECT * FROM Customers WHERE CustomerId = ? AND password = ?";

            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, CustomerId);
            pstmt.setString(2, password);

            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                user = new Customer();
                user.setCustomerId(rs.getString("CustomerId"));
                user.setCustomerName(rs.getString("CustomerName"));
                user.setPassword(rs.getString("password"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.closeDBConnection(connection);
        }

        return user;
    }
	@Override
	public boolean registerUser(Customer user) {
		 Connection connection = null;
	        PreparedStatement pstmt = null;
	        boolean isRegistered = false;

	        try {
	            connection = DBConnection.getDBConnection();

	            String sql = "INSERT INTO Customers (CustomerName, Email,MobileNo,CustomerId, password) VALUES (?, ?, ?,?,?)";

	            pstmt = connection.prepareStatement(sql);
	            pstmt.setString(1, user.getCustomerName());
	            pstmt.setString(2, user.getEmail());
	            pstmt.setString(3, user.getMobileNo());
	            pstmt.setString(4, user.getCustomerId());
	      
	            pstmt.setString(5, user.getPassword());
	            



	            int rowsAffected = pstmt.executeUpdate();
	            isRegistered = rowsAffected > 0;

	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            // Close PreparedStatement
	            if (pstmt != null) {
	                try {
	                    pstmt.close();
	                } catch (SQLException e) {
	                    e.printStackTrace();
	                }
	            }

	            // Close Connection
	            DBConnection.closeDBConnection(connection);
	        }

	        return isRegistered;
	    }
	@Override
	public Customer getUserById(String CustomerId) {
		 Connection conn = null;
	        PreparedStatement stmt = null;
	        ResultSet rs=null;
	        Customer user=null;
	        
	        try {
	            conn = DBConnection.getDBConnection();

	            String sql ="SELECT * FROM customers where CustomerId=?";
	            stmt=conn.prepareStatement(sql);
	            stmt.setString(1,CustomerId);
	            rs=stmt.executeQuery();
	            if(rs.next()) {
	            	user=new Customer();
	            	 
	            	user.setCustomerId(rs.getString("CustomerId"));
	            	user.setEmail(rs.getString("Email"));
	            	user.setAddress(rs.getString("Address"));
	            	user.setMobileNo(rs.getString("MobileNo"));
	            	user.setCustomerName(rs.getString("CustomerName"));
	             	user.setPassword(rs.getString("password"));
	            	
	            }
	        }
	        catch(SQLException ex) {
	        	ex.printStackTrace();
	        }finally {
	        	try {
	        		if(rs!=null) rs.close();
	        		if(stmt!=null) stmt.close();
	        		if(conn!=null) conn.close();
	        	}catch(SQLException ex) {
	        		ex.printStackTrace();
	        	}
	        }
	        return user;
	       
	        
	        
	}
	@Override
	public Admin validateAdmin(String userId, String password1) {
		 Connection connection = null;
	        Admin user = null;

	        try {
	            connection = DBConnection.getDBConnection();

	            String sql = "SELECT * FROM Admins WHERE userId = ? AND password1 = ?";

	            PreparedStatement pstmt = connection.prepareStatement(sql);
	            pstmt.setString(1, userId);
	            pstmt.setString(2, password1);

	            ResultSet rs = pstmt.executeQuery();

	            if (rs.next()) {
	                user = new Admin();
	                user.setUserId(rs.getString("userId"));
	                
	                user.setPassword1(rs.getString("password1"));
	            }

	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            DBConnection.closeDBConnection(connection);
	        }

	        return user;
	    }
	@Override
	public boolean reserve(Reservation reservation) {
		Connection conn = null;
	    PreparedStatement stmt = null;
	    try {
	        conn = DBConnection.getDBConnection();
	        String query = "INSERT INTO reservations (check_in_date, check_out_date, room_preference, name, contact) VALUES (?, ?, ?, ?, ?)";
	        stmt = conn.prepareStatement(query);
	        stmt.setString(1, reservation.getCheck_in_date());
	        stmt.setString(2, reservation.getCheck_out_date());
	        stmt.setString(3, reservation.getPreferences());
	        stmt.setString(4, reservation.getName());
	        stmt.setString(5, reservation.getContact());

	        int rowsAffected = stmt.executeUpdate();
	        return rowsAffected > 0; // Returns true if at least one row was affected (successful registration)
	    } catch (SQLException ex) {
	        ex.printStackTrace();
	        return false; // Registration failed
	    } finally {
	        try {
	            if (stmt != null) stmt.close();
	            if (conn != null) conn.close();
	        } catch (SQLException ex) {
	            ex.printStackTrace();
	        }
	    }
	}
	@Override
	public double calculatePrice(String roomPreference, String checkindate, String checkoutdate) {
		   double pricePerNight;
	        if (roomPreference.equalsIgnoreCase("AC")) {
	            pricePerNight = 2000;
	        } else {
	            pricePerNight = 1500;
	        }

	        // Calculate the number of nights
	        int numberOfNights = calculateNumberOfNights(checkindate, checkoutdate);

	        // Calculate total price
	        double totalPrice = pricePerNight * numberOfNights;

	        return totalPrice;
	    }
	private int calculateNumberOfNights(String checkindate, String checkoutdate) {
		// TODO Auto-generated method stub	
		DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd");
		 LocalDate startDate = LocalDate.parse(checkindate);
		    LocalDate endDate = LocalDate.parse(checkoutdate);

		   
		    return (int) ChronoUnit.DAYS.between(startDate, endDate) + 1;
		

		
		  
	}
	@Override
	public Billing getBillingDetails(String BillingId) {
		// TODO Auto-generated method stub
		 Billing billing = null;
	        Connection connection = null;
	        PreparedStatement statement = null;
	        ResultSet resultSet = null;

	        try {
	            // Get connection and prepare statement
	        	 connection = DBConnection.getDBConnection();
	            String query = "SELECT * FROM billings WHERE BillingId=?";
	            statement = connection.prepareStatement(query);
	            statement.setString(1, BillingId);

	            // Execute query
	            resultSet = statement.executeQuery();

	            // Process result set
	            if (resultSet.next()) {
	                billing = new Billing();
	                billing.setBillingId(resultSet.getString("BillingId"));
	                billing.setRoomCharges(resultSet.getDouble("roomCharges"));
	                billing.setAdditionalServices(resultSet.getDouble("additionalServices"));
	                billing.setTotalCharges(resultSet.getDouble("totalCharges"));
	                // Populate other user properties if needed
	            }
	        } catch (SQLException e) {
	            e.printStackTrace();
	            // Handle exceptions appropriately
	        } finally {
	            // Close resources
	            try {
	                if (resultSet != null) resultSet.close();
	                if (statement != null) statement.close();
	                if (connection != null) connection.close();
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	        return billing;
	    }
	@Override
	public ArrayList<Booking> viewByUserId(int userId) {
		  Booking book=new Booking();
	        try {
	           
	        	Connection connection = DBConnection.getDBConnection();
	        	String sql = "SELECT * FROM bookings WHERE reservationid = ?";
	            PreparedStatement ps = connection.prepareStatement(sql);
	            ps.setInt(1, userId);
	            ResultSet rs = ps.executeQuery();
	            System.out.println(rs);
	            while (rs.next()) {
	             
	                book.setReservationId(rs.getInt(1));
	                book.setCheckInDate(rs.getDate(2));
	                book.setCheckOutDate(rs.getDate(3));
	                book.setRoomNumber(rs.getInt(4));
	                book.setBill(rs.getDouble(5));
	                book.setBookingDate(rs.getDate(6));
	                
	            }
	        } catch (Exception e) {
	            e.printStackTrace();
	        }
	      ArrayList<Booking> cus=new ArrayList<Booking>();
	      cus.add(book);
	      return cus;
	    }
	@Override
	public boolean isRoomAvailable(String preference, Date check_in_date1, Date check_out_date2) {
		Connection conn = null;
	       
		try {
				 conn = DBConnection.getDBConnection();
				 String query="SELECT COUNT(*) FROM rooms WHERE preference = ? AND  ((check_in_date1 >= ? AND check_in_date1 < ?) OR (check_out_date2 > ? AND check_out_date2 <= ?) OR (check_in_date1 < ? AND check_out_date2 > ?))";
	            PreparedStatement ps = conn.prepareStatement(query);
	            
	            ps.setString(1,preference);
	            ps.setDate(2, check_in_date1);
	            ps.setDate(3, check_in_date1);
	            ps.setDate(4, check_out_date2);
	            ps.setDate(5, check_out_date2);
	            ps.setDate(6, check_in_date1);
	            ps.setDate(7, check_out_date2);
	            ResultSet rs = ps.executeQuery();
	            if (rs.next()) {
	                int count = rs.getInt(1);
	                if(count==0) {
	                	return true;
	                }
	            }
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	        return false;

	}
	@Override
	public boolean processPayment(Payment payment) {
		Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
        	connection = DBConnection.getDBConnection();
            
            String transactionId = generateTransactionId();
            payment.setTransactionId(transactionId); // set the transaction ID in the payment bean

            String sql = "INSERT INTO payments (transaction_id, customer_id, bill_amount, payment_mode, card_number, card_holder_name, expiry_date, cvv) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, payment.getTransactionId());
            preparedStatement.setString(2, payment.getCustomerId());
            preparedStatement.setDouble(3, payment.getBillAmount());
            preparedStatement.setString(4, payment.getPaymentMode());
            preparedStatement.setString(5, payment.getCardNumber());
            preparedStatement.setString(6, payment.getCardHolderName());
            preparedStatement.setString(7, payment.getExpiryDate());
            preparedStatement.setString(8, payment.getCvv());

            int rowsInserted = preparedStatement.executeUpdate();
            return rowsInserted > 0;

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                if (preparedStatement != null) preparedStatement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public String generateTransactionId() {
        return UUID.randomUUID().toString();
    }


}
	
	 

 


       
	
