<?php
class Room {

    private $conn;
    private $table_name = "vehicle";

    public $id;
    public $room_number; 
    public $room_type; 
    public $bed_type; 
    public $max_occupancy; 
    public $room_rate; 
    public $extra_bed; 
    public $extra_person; 
    public $remark; 
    public $entry_by; 
    public $update_by; 
    
    public $start_date; 
    public $end_date; 

    public function __construct($db) {
        $this->conn = $db;
    }

    public function create() {
        $query = "INSERT INTO " . $this->table_name . " 
                  SET vehicle_no = :room_number, vehicle_category = :room_type, 
                      vehicle_note = :bed_type, seats = :max_occupancy,
                      rental_rate = :room_rate, extra_driver = :extra_bed,
                      extra_passenger = :extra_person, remark = :remark,
                      entry_by = :entry_by";

        $stmt = $this->conn->prepare($query);

        $stmt->bindParam(":room_number", $this->room_number);
        $stmt->bindParam(":room_type", $this->room_type); 
        $stmt->bindParam(":bed_type", $this->bed_type);
        $stmt->bindParam(":max_occupancy", $this->max_occupancy);
        $stmt->bindParam(":room_rate", $this->room_rate);
        $stmt->bindParam(":extra_bed", $this->extra_bed);
        $stmt->bindParam(":extra_person", $this->extra_person);
        $stmt->bindParam(":remark", $this->remark);
        $stmt->bindParam(":entry_by", $this->entry_by);

        if ($stmt->execute()) {
            return true;
        }
        return false;
    }

    function update() {
        $query = "UPDATE " . $this->table_name . " 
                  SET vehicle_category = :room_type, 
                      vehicle_note = :bed_type, seats = :max_occupancy,
                      rental_rate = :room_rate, extra_driver = :extra_bed,
                      extra_passenger = :extra_person, remark = :remark, update_by=:update_by  
                  WHERE id=:id";
        $stmt = $this->conn->prepare($query);

        $stmt->bindParam(":id", $this->id);
        $stmt->bindParam(":room_type", $this->room_type); 
        $stmt->bindParam(":bed_type", $this->bed_type);
        $stmt->bindParam(":max_occupancy", $this->max_occupancy);
        $stmt->bindParam(":room_rate", $this->room_rate);
        $stmt->bindParam(":extra_bed", $this->extra_bed);
        $stmt->bindParam(":extra_person", $this->extra_person);
        $stmt->bindParam(":remark", $this->remark);
        $stmt->bindParam(":update_by", $this->update_by);

        if ($stmt->execute()) {
            return true;
        }
        return false;
    }

    function delete() {
        $query = "DELETE FROM " . $this->table_name . " WHERE id = ?";
        $stmt = $this->conn->prepare($query);
        $this->id = htmlspecialchars(strip_tags($this->id));
        $stmt->bindParam(1, $this->id);
        if ($stmt->execute()) {
            return true;
        }
        return false;
    }

    function getOneRow() {
        $query = "SELECT id,
                    vehicle_no AS room_number,
                    vehicle_category AS room_type,
                    vehicle_note AS bed_type,
                    seats AS max_occupancy,
                    rental_rate AS room_rate,
                    extra_driver AS extra_bed,
                    extra_passenger AS extra_person,
                    remark
                  FROM " . $this->table_name . "  WHERE id = ?";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $this->id);
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($row) {
            $this->id = $row['id'];
            $this->room_number = $row['room_number'];
            $this->room_type = $row['room_type'];
            $this->bed_type = $row['bed_type'];
            $this->max_occupancy = $row['max_occupancy'];
            $this->room_rate = $row['room_rate'];
            $this->extra_bed = $row['extra_bed'];
            $this->extra_person = $row['extra_person'];
            $this->remark = $row['remark'];
        }
    }

    function getAllRows() {
        $query = "SELECT id,
                    vehicle_no AS room_number,
                    vehicle_category AS room_type,
                    vehicle_note AS bed_type,
                    seats AS max_occupancy,
                    rental_rate AS room_rate,
                    extra_driver AS extra_bed,
                    extra_passenger AS extra_person,
                    remark
                  FROM " . $this->table_name . " ORDER BY vehicle_no";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt;
    }

    function getAvalRoom() {
        $query = "SELECT
            r.id,
            r.vehicle_no AS room_number,
            r.vehicle_category AS room_type,
            r.vehicle_note AS bed_type,
            r.seats AS max_occupancy,
            r.rental_rate AS room_rate,
            r.extra_driver AS extra_bed,
            r.extra_passenger AS extra_person,
            r.remark,
            'Available for selected period' AS current_plan
        FROM vehicle r
        WHERE NOT EXISTS (
            SELECT 1
            FROM (
                SELECT
                    rv.vehicle_no,
                    COALESCE(NULLIF(rv.pickup_datetime, ''), MIN(rd.date)) AS start_dt,
                    COALESCE(NULLIF(rv.return_datetime, ''), DATE_ADD(MAX(rd.date), INTERVAL 1 DAY)) AS end_dt
                FROM booking rv
                INNER JOIN booking_detail rd ON rd.booking_no = rv.booking_no
                WHERE rv.returned = 0
                GROUP BY rv.id, rv.vehicle_no, rv.pickup_datetime, rv.return_datetime
            ) busy
            WHERE busy.vehicle_no = r.vehicle_no
              AND busy.start_dt < :end_date
              AND busy.end_dt > :start_date
        )
        ORDER BY r.vehicle_no;

        ";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":start_date", $this->start_date);
        $stmt->bindParam(":end_date", $this->end_date); 
        $stmt->execute();
        return $stmt;
    }

    function getAvalRoomForEditReservation() {
        $query = "SELECT
            r.id,
            r.vehicle_no AS room_number,
            r.vehicle_category AS room_type,
            r.vehicle_note AS bed_type,
            r.seats AS max_occupancy,
            r.rental_rate AS room_rate,
            r.extra_driver AS extra_bed,
            r.extra_passenger AS extra_person,
            r.remark,
            CASE
                WHEN r.vehicle_no = :current_room_no THEN 'Current booking vehicle'
                ELSE 'Available for selected period'
            END AS current_plan
        FROM vehicle r
        WHERE r.vehicle_no = :room_no
           OR NOT EXISTS (
                SELECT 1
                FROM (
                    SELECT
                        rv.vehicle_no,
                        COALESCE(NULLIF(rv.pickup_datetime, ''), MIN(rd.date)) AS start_dt,
                        COALESCE(NULLIF(rv.return_datetime, ''), DATE_ADD(MAX(rd.date), INTERVAL 1 DAY)) AS end_dt
                    FROM booking rv
                    INNER JOIN booking_detail rd ON rd.booking_no = rv.booking_no
                    WHERE rv.returned = 0
                    GROUP BY rv.id, rv.vehicle_no, rv.pickup_datetime, rv.return_datetime
                ) busy
                WHERE busy.vehicle_no = r.vehicle_no
                  AND busy.start_dt < :end_date
                  AND busy.end_dt > :start_date
           )
        ORDER BY r.vehicle_no;

        ";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":start_date", $this->start_date);
        $stmt->bindParam(":end_date", $this->end_date); 
        $stmt->bindParam(":current_room_no", $this->room_no); 
        $stmt->bindParam(":room_no", $this->room_no); 
        $stmt->execute();
        return $stmt;
    }

    // public function getRoomByType($roomTypeId) {
    //     $query = "SELECT room_number, room_rate, bed_type
    //               FROM " . $this->table_name . " 
    //               LEFT JOIN room_type ON room.room_type_id = room_type.id 
    //               WHERE room.room_type_id = :room_type_id 
    //               ORDER BY room_number";
    
    //     $stmt = $this->conn->prepare($query);
    //     $stmt->bindParam(':room_type_id', $roomTypeId);
    //     $stmt->execute();
    //     return $stmt;
    // }

    function exist() {
        $query = "SELECT * FROM " . $this->table_name . " WHERE vehicle_no = ? LIMIT 0,1";
        $stmt = $this->conn->prepare($query);
        $this->room_number = htmlspecialchars(strip_tags($this->room_number));

        $stmt->bindParam(1, $this->room_number);
        $stmt->execute();
        if ($stmt->rowCount() > 0) {
            return true;
        }
        return false;
    }

    // function getRoomStatusByDate(){
	// 	$query = "SELECT room.room_number,name,
    //     CASE 
    //     WHEN v.checkin_id > 0 THEN v.checkin_id 
    //     ELSE v.reservation_no 
    //     END AS id,DATE,
    //     CASE 
    //     WHEN dm > 0 AND status = 'reserve' THEN 'confirm' 
    //     ELSE status 
    //     END AS c_status
    //     from room left JOIN (SELECT validate.reservation_no,validate.room_no,checkin_id,date,status,
    //     CASE 
    //         WHEN checkin.guest_name IS NULL THEN reservation.guest_name 
    //         ELSE checkin.guest_name 
    //     END AS name,dm FROM validate 
    //     LEFT JOIN checkin ON validate.checkin_id=checkin.id
    //     LEFT JOIN reservation ON validate.reservation_no=reservation.reservation_no
    //     LEFT JOIN (SELECT 
    //                     reservation_no,
    //                     room_no,
    //                     SUM(deposit_amount) AS dm 
    //                     FROM deposit 
    //                     WHERE deposit_type <> 'Paid Out' 
    //                     GROUP BY reservation_no, room_no) AS d ON validate.reservation_no=d.reservation_no AND validate.room_no=d.room_no
    //     where date BETWEEN :start_date AND :end_date) AS v ON room.room_number=v.room_no";
	// 	$stmt = $this->conn->prepare( $query );

	// 	$stmt->bindParam(":start_date", $this->start_date);
	// 	$stmt->bindParam(":end_date", $this->end_date);
	// 	$stmt->execute();
	// 	return $stmt;
	// }

    function getRoomStatusByDate(){
		$query = "
        select a.*,from_to_date,total_days,total_amount from (
        select vehicle_no AS room_number,'' AS name,'' as checkin_id,'' as reservation_no,'' as folio_no,'' as date,'' AS c_status,'' as agent_name,0 AS room_income,vehicle_category AS room_type,vehicle_note AS bed_type,seats AS max_occupancy,rental_rate AS room_rate,remark from vehicle 
        union ALL
        SELECT vehicle_no AS room_number,customer_name AS name,c.id as checkin_id,c.booking_no AS reservation_no,c.folio_no,date,'checkout' AS c_status,agent_name,
        rental_rate + (extra_driver * extra_driver_rate) + (extra_passenger * extra_passenger_rate) AS room_income,'' as room_type,'' as bed_type,'' as max_occupancy,'' as room_rate,'' as remark
        FROM booking_detail cd LEFT JOIN booking c ON cd.booking_no=c.booking_no WHERE returned=1 and date BETWEEN :df AND :dt
        UNION ALL
        SELECT vehicle_no AS room_number,customer_name AS name,c.id as checkin_id,c.booking_no AS reservation_no,c.folio_no,date,'checkin' AS c_status,agent_name,
        rental_rate + (extra_driver * extra_driver_rate) + (extra_passenger * extra_passenger_rate) AS room_income ,'' as room_type,'' as bed_type,'' as max_occupancy,'' as room_rate,'' as remark
        FROM booking_detail cd LEFT JOIN booking c ON cd.booking_no=c.booking_no WHERE picked_up=1 AND returned=0 and date BETWEEN :df AND :dt
        UNION ALL
        SELECT vehicle_no AS room_number,customer_name AS name,c.id as checkin_id,c.booking_no AS reservation_no,c.folio_no,date,'reserve' AS c_status,agent_name,
        rental_rate + (extra_driver * extra_driver_rate) + (extra_passenger * extra_passenger_rate) AS room_income ,'' as room_type,'' as bed_type,'' as max_occupancy,'' as room_rate,'' as remark
        FROM booking_detail cd LEFT JOIN booking c ON cd.booking_no=c.booking_no WHERE picked_up=0 AND returned=0 and date BETWEEN :df AND :dt) as a
        left join (SELECT 
            booking_no AS reservation_no,
            CONCAT(MIN(date), ' to ', DATE_ADD(MAX(date), INTERVAL 1 DAY)) AS from_to_date,
            DATEDIFF(MAX(date), MIN(date)) + 1 AS total_days,
            rental_rate + (extra_driver * extra_driver_rate) + (extra_passenger * extra_passenger_rate) AS room_total_per_day,
            (rental_rate + (extra_driver * extra_driver_rate) + (extra_passenger * extra_passenger_rate)) * 
            (DATEDIFF(MAX(date), MIN(date)) + 1) AS total_amount
        FROM booking_detail
        GROUP BY booking_no, rental_rate, extra_driver, extra_driver_rate, extra_passenger, extra_passenger_rate
        ) as b on a.reservation_no=b.reservation_no
";
		$stmt = $this->conn->prepare( $query );

		$stmt->bindParam(":df", $this->start_date);
		$stmt->bindParam(":dt", $this->end_date);
		$stmt->execute();
		return $stmt;
	}

    function getRoomCount() {
        $query = "SELECT COUNT(*) AS room_counts FROM vehicle"; // Query to count all vehicles
    
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
    
        if ($stmt->rowCount() > 0) {
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            return $row['room_counts']; // Return the room count
        }
    
        return 0; // Return 0 if no rooms are found
    }

    function updateFromOccupied() {
        $query = "Update " . $this->table_name . " set vehicle_status='vacant' WHERE vehicle_no = :room_number";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":room_number", $this->room_number);
        if ($stmt->execute()) {
            return true;
        }
        return false;
    }
    


}
?>
