<?php
    date_default_timezone_set('Asia/Bangkok');
class Reservation {

    private $conn;
    private $table_name = "booking"; 

    public $id;
    public $date_time;
    public $reservation_no;
    public $reservation_no_old;
    public $folio_no;
    public $discount_amount;
    public $agent_id;
    public $agent_name;
    public $guest_id;
    public $guest_name; 
    public $remark; 
    public $rental_mode;
    public $pickup_datetime;
    public $return_datetime;
    public $rental_hours;
    public $adult;
    public $child;
    public $room_type;
    public $bed_type;
    public $room_no;
    public $entry_by; 
    public $update_by; 
    public $created_at; 
    public $updated_at; 
    public $checkin; 
    public $checkin_created_at; 
    public $checkout; 
    public $checkout_created_at; 

    public $df; 
    public $dt; 
    public $status; 
    public $balance;
    // public $extra_bed; 
    // public $extra_person; 

    // public $old_room_no; 



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

    function generateNextBookingNo() {
        $query = "
            SELECT LPAD(COALESCE(MAX(res_no), 0) + 1, 4, '0') AS next_no FROM (
                SELECT CAST(booking_no AS UNSIGNED) AS res_no
                FROM booking
                WHERE booking_no REGEXP '^[0-9]+$'
                UNION ALL
                SELECT CAST(booking_no AS UNSIGNED) AS res_no
                FROM booking_delete
                WHERE booking_no REGEXP '^[0-9]+$'
            ) AS all_bookings
        ";

        $stmt = $this->conn->prepare($query);
        if ($stmt->execute()) {
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            return $row['next_no'] ?: '0001';
        }

        return '0001';
    }

    function create() {
        $query = "INSERT INTO " . $this->table_name . " 
                    SET date_time=:date_time, booking_no=:reservation_no, folio_no=:folio_no, agent_id=:agent_id, agent_name=:agent_name,
                    customer_id=:guest_id, customer_name=:guest_name, discount_amount=:discount_amount, remark=:remark, rental_mode=:rental_mode,
                    pickup_datetime=:pickup_datetime, return_datetime=:return_datetime, rental_hours=:rental_hours,
                    vehicle_category=:room_type, vehicle_note=:bed_type, vehicle_no=:room_no,
                    drivers=:adult, passengers=:child, entry_by=:entry_by";
        $stmt = $this->conn->prepare($query);

        $stmt->bindParam(":date_time", $this->date_time);
        $stmt->bindParam(":reservation_no", $this->reservation_no);
        $stmt->bindParam(":folio_no", $this->folio_no);
        $stmt->bindParam(":agent_id", $this->agent_id);
        $stmt->bindParam(":agent_name", $this->agent_name);
        $stmt->bindParam(":guest_id", $this->guest_id);
        $stmt->bindParam(":guest_name", $this->guest_name);
        $stmt->bindParam(":discount_amount", $this->discount_amount);
        $stmt->bindParam(":remark", $this->remark);
        $stmt->bindParam(":rental_mode", $this->rental_mode);
        $stmt->bindParam(":pickup_datetime", $this->pickup_datetime);
        $stmt->bindParam(":return_datetime", $this->return_datetime);
        $stmt->bindParam(":rental_hours", $this->rental_hours);
        $stmt->bindParam(":room_type", $this->room_type);
        $stmt->bindParam(":bed_type", $this->bed_type);
        $stmt->bindParam(":room_no", $this->room_no);
        $stmt->bindParam(":adult", $this->adult);
        $stmt->bindParam(":child", $this->child);
        $stmt->bindParam(":entry_by", $this->entry_by);

        if ($stmt->execute()) {
            $this->id = $this->conn->lastInsertId();
            return true;
        }
        return false;
    }

    function getReservationList() {
        $query = "SELECT r.id, r.booking_no AS reservation_no, r.folio_no, agent_name, customer_name AS guest_name,
            drivers AS adult, passengers AS child, vehicle_no AS room_no, date_time,
            MIN(rd.date) AS checkin_date, DATE_ADD(MAX(rd.date), INTERVAL 1 DAY) AS checkout_date,
            COUNT(rd.date) AS nights,
            SUM(rental_rate + (extra_driver * extra_driver_rate) + (extra_passenger * extra_passenger_rate)) AS total,
            IFNULL(r.discount_amount, 0) AS discount_amount,
            GREATEST(SUM(rental_rate + (extra_driver * extra_driver_rate) + (extra_passenger * extra_passenger_rate)) - IFNULL(r.discount_amount, 0), 0) AS net_amount,
            IFNULL(deposit, 0) AS deposit, remark
            FROM booking r
            LEFT JOIN booking_detail rd ON r.booking_no = rd.booking_no
            LEFT JOIN (SELECT booking_no, SUM(deposit_amount) AS deposit FROM deposit GROUP BY booking_no) AS d ON r.booking_no = d.booking_no
            WHERE picked_up = 0 AND returned = 0
            GROUP BY r.booking_no
            ORDER BY checkin_date, checkout_date";
    
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt;
    }

    function getReservationListDetail(){
        $query = "SELECT rd.booking_no AS reservation_no, rd.date,
            rd.rental_rate AS room_rate,
            rd.extra_driver AS extra_bed,
            rd.extra_driver_rate AS extra_bed_rate,
            rd.extra_passenger AS extra_person,
            rd.extra_passenger_rate AS extra_person_rate,
            r.vehicle_no AS room_no
            FROM booking_detail rd
            LEFT JOIN booking r ON rd.booking_no = r.booking_no
            WHERE picked_up = 0 AND returned = 0";

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

    function getReservationStayList() {

        $condition = "";

		if($this->status=="Reservation"){
			$condition .= " and picked_up = 0 and returned = 0 ";
		}else if($this->status=="Check In"){
            $condition .= " and picked_up = 1 and returned = 0 ";
        }else if($this->status=="Check Out"){
            $condition .= " and picked_up = 1 and returned = 1 ";
        }

		if($this->balance=="Yes"){
			$condition .= " and net_amount-deposit<>0";
		}else if($this->balance=="No"){
            $condition .= " and net_amount-deposit=0 ";
        }


        $query = "SELECT r.id, r.booking_no AS reservation_no, r.folio_no, agent_name, customer_name AS guest_name,
            drivers AS adult, passengers AS child, vehicle_no AS room_no, date_time,
            MIN(rd.date) AS checkin_date, DATE_ADD(MAX(rd.date), INTERVAL 1 DAY) AS checkout_date,
            picked_up AS checkin, returned AS checkout,
            COUNT(rd.date) AS nights,
            SUM(rental_rate + (extra_driver * extra_driver_rate) + (extra_passenger * extra_passenger_rate)) AS total,
            IFNULL(r.discount_amount, 0) AS discount_amount,
            GREATEST(SUM(rental_rate + (extra_driver * extra_driver_rate) + (extra_passenger * extra_passenger_rate)) - IFNULL(r.discount_amount, 0), 0) AS net_amount,
            IFNULL(deposit, 0) AS deposit, remark, r.created_at
            FROM booking r
            LEFT JOIN booking_detail rd ON r.booking_no = rd.booking_no
            LEFT JOIN (SELECT booking_no, SUM(deposit_amount) AS deposit FROM deposit GROUP BY booking_no) AS d ON r.booking_no = d.booking_no
            GROUP BY r.booking_no
            HAVING checkin_date >= :df AND checkin_date <= :dt " .  $condition . "
            ORDER BY checkin_date, checkout_date";
    
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":df", $this->df);
        $stmt->bindParam(":dt", $this->dt);
        $stmt->execute();
        return $stmt;
    }

    function getReservationStayListDetail(){
        $query = "SELECT rd.booking_no AS reservation_no, rd.date,
            rd.rental_rate AS room_rate,
            rd.extra_driver AS extra_bed,
            rd.extra_driver_rate AS extra_bed_rate,
            rd.extra_passenger AS extra_person,
            rd.extra_passenger_rate AS extra_person_rate,
            r.vehicle_no AS room_no
            FROM booking_detail rd
            LEFT JOIN booking r ON rd.booking_no = r.booking_no";

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

    function getOccupiedList() {
        $query = "
            SELECT r.id, r.booking_no AS reservation_no, r.folio_no, agent_name, customer_name AS guest_name,
            drivers AS adult, passengers AS child, vehicle_no AS room_no, date_time,
            MIN(rd.date) AS checkin_date, DATE_ADD(MAX(rd.date), INTERVAL 1 DAY) AS checkout_date,
            COUNT(rd.date) AS nights,
            SUM(rental_rate + (extra_driver * extra_driver_rate) + (extra_passenger * extra_passenger_rate)) AS total,
            IFNULL(r.discount_amount, 0) AS discount_amount,
            GREATEST(SUM(rental_rate + (extra_driver * extra_driver_rate) + (extra_passenger * extra_passenger_rate)) - IFNULL(r.discount_amount, 0), 0) AS net_amount,
            IFNULL(deposit, 0) AS deposit, remark
            FROM booking r
            LEFT JOIN booking_detail rd ON r.booking_no = rd.booking_no
            LEFT JOIN (SELECT booking_no, SUM(deposit_amount) AS deposit FROM deposit GROUP BY booking_no) AS d ON r.booking_no = d.booking_no
            WHERE picked_up = 1 AND returned = 0
            GROUP BY r.booking_no
            ORDER BY checkin_date, checkout_date ";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt;
    }

    function getOccupiedListDetail(){
        $query = "SELECT rd.booking_no AS reservation_no, rd.date,
            rd.rental_rate AS room_rate,
            rd.extra_driver AS extra_bed,
            rd.extra_driver_rate AS extra_bed_rate,
            rd.extra_passenger AS extra_person,
            rd.extra_passenger_rate AS extra_person_rate,
            r.vehicle_no AS room_no
            FROM booking_detail rd
            LEFT JOIN booking r ON rd.booking_no = r.booking_no
            WHERE picked_up = 1 AND returned = 0";

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

    function getCompletedDeparturesList() {
        $query = "
            SELECT r.id, r.booking_no AS reservation_no, r.folio_no, agent_name, customer_name AS guest_name,
            drivers AS adult, passengers AS child, vehicle_no AS room_no, date_time,
            MIN(rd.date) AS checkin_date, DATE_ADD(MAX(rd.date), INTERVAL 1 DAY) AS checkout_date,
            COUNT(rd.date) AS nights,
            SUM(rental_rate + (extra_driver * extra_driver_rate) + (extra_passenger * extra_passenger_rate)) AS total,
            IFNULL(r.discount_amount, 0) AS discount_amount,
            GREATEST(SUM(rental_rate + (extra_driver * extra_driver_rate) + (extra_passenger * extra_passenger_rate)) - IFNULL(r.discount_amount, 0), 0) AS net_amount,
            IFNULL(deposit, 0) AS deposit, remark
            FROM booking r
            LEFT JOIN booking_detail rd ON r.booking_no = rd.booking_no
            LEFT JOIN (SELECT booking_no, SUM(deposit_amount) AS deposit FROM deposit GROUP BY booking_no) AS d ON r.booking_no = d.booking_no
            WHERE picked_up = 1 AND returned = 1
            GROUP BY r.booking_no
            ORDER BY checkin_date, checkout_date ";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt;
    }

    function getCompletedDeparturesListDetail(){
        $query = "SELECT rd.booking_no AS reservation_no, rd.date,
            rd.rental_rate AS room_rate,
            rd.extra_driver AS extra_bed,
            rd.extra_driver_rate AS extra_bed_rate,
            rd.extra_passenger AS extra_person,
            rd.extra_passenger_rate AS extra_person_rate,
            r.vehicle_no AS room_no
            FROM booking_detail rd
            LEFT JOIN booking r ON rd.booking_no = r.booking_no
            WHERE picked_up = 1 AND returned = 1";

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

    function updateCheckIn() {
        $query = "UPDATE " . $this->table_name . " set picked_up=1,pickup_created_at=:checkin_created_at WHERE booking_no = :reservation_no and vehicle_no=:room_no";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":reservation_no", $this->reservation_no);
        $stmt->bindParam(":room_no", $this->room_no);
        $stmt->bindParam(":checkin_created_at", $this->checkin_created_at);
        if ($stmt->execute()) {
            return true;
        }
        return false;
    }

    function updateCheckInToReservation() {
        $query = "UPDATE " . $this->table_name . " set picked_up=0,pickup_created_at=:checkin_created_at WHERE booking_no = :reservation_no and vehicle_no=:room_no";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":reservation_no", $this->reservation_no);
        $stmt->bindParam(":room_no", $this->room_no);
        $stmt->bindParam(":checkin_created_at", $this->checkin_created_at);
        if ($stmt->execute()) {
            return true;
        }
        return false;
    }

    function updateCheckOutToCheckIn() {
        $query = "UPDATE " . $this->table_name . " set returned=0,return_created_at=:checkout_created_at WHERE booking_no = :reservation_no and vehicle_no=:room_no";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":reservation_no", $this->reservation_no);
        $stmt->bindParam(":room_no", $this->room_no);
        $stmt->bindParam(":checkout_created_at", $this->checkout_created_at);
        if ($stmt->execute()) {
            return true;
        }
        return false;
    }

    function updateCheckOut() {
        $query = "UPDATE " . $this->table_name . " set returned=1,return_created_at=:checkout_created_at WHERE booking_no = :reservation_no and vehicle_no=:room_no";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":reservation_no", $this->reservation_no);
        $stmt->bindParam(":room_no", $this->room_no);
        $stmt->bindParam(":checkout_created_at", $this->checkout_created_at);
        if ($stmt->execute()) {
            return true;
        }
        return false;
    }

//     function DeleteReservation(){
// 		$query = "Delete From " . $this->table_name . " WHERE reservation_no=:reservation_no";
// 		$stmt = $this->conn->prepare($query);

// 		$stmt->bindParam(":reservation_no", $this->reservation_no);
// 		if($stmt->execute()){
// 			return true;
// 		}
// 		return false;
// 	}

    function checkReservation() {
        $query = "SELECT COUNT(*) FROM " . $this->table_name . " WHERE booking_no = :reservation_no";
        $stmt = $this->conn->prepare($query);
    
        $stmt->bindParam(":reservation_no", $this->reservation_no);
    
        if ($stmt->execute()) {
            $count = $stmt->fetchColumn();
            if ($count > 0) {
                return true;
            } else {
                return false;
            }
        }
    
        return false;
    }

    function hasAvailabilityConflict() {
        if (empty($this->room_no) || empty($this->pickup_datetime) || empty($this->return_datetime)) {
            return false;
        }

        $query = "SELECT COUNT(*) FROM (
                    SELECT
                        rv.booking_no,
                        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.vehicle_no = :room_no
                      AND rv.returned = 0
                      AND rv.booking_no <> :reservation_no
                      AND rv.booking_no <> :reservation_no_old
                    GROUP BY rv.id, rv.booking_no, rv.vehicle_no, rv.pickup_datetime, rv.return_datetime
                ) busy
                WHERE busy.start_dt < :return_datetime
                  AND busy.end_dt > :pickup_datetime";

        $stmt = $this->conn->prepare($query);
        $reservation_no_old = $this->reservation_no_old ?: $this->reservation_no;
        $stmt->bindParam(":room_no", $this->room_no);
        $stmt->bindParam(":reservation_no", $this->reservation_no);
        $stmt->bindParam(":reservation_no_old", $reservation_no_old);
        $stmt->bindParam(":pickup_datetime", $this->pickup_datetime);
        $stmt->bindParam(":return_datetime", $this->return_datetime);

        if ($stmt->execute()) {
            return ((int)$stmt->fetchColumn()) > 0;
        }

        return true;
    }


    function DeleteReservation(){
        
        session_start();
    
        $deleted_by = $_SESSION['user'] ?? '';
        $deleted_date_time = date('Y-m-d H:i:s');
    
        // Step 1: Copy reservation to booking_delete with deleted_by & deleted_date_time
        $backupQuery = "
            INSERT INTO booking_delete (
                id, date_time, booking_no, folio_no, agent_id, agent_name,
                customer_id, customer_name, discount_amount, remark, rental_mode, pickup_datetime, return_datetime, rental_hours,
                vehicle_category, vehicle_note, vehicle_no, drivers, passengers,
                entry_by, update_by, created_at, updated_at, picked_up, pickup_created_at,
                returned, return_created_at, deleted_by, deleted_date_time
            )
            SELECT id, date_time, booking_no, folio_no, agent_id, agent_name,
                customer_id, customer_name, discount_amount, remark, rental_mode, pickup_datetime, return_datetime, rental_hours,
                vehicle_category, vehicle_note, vehicle_no, drivers, passengers,
                entry_by, update_by, created_at, updated_at, picked_up, pickup_created_at,
                returned, return_created_at, :deleted_by, :deleted_date_time
            FROM " . $this->table_name . "
            WHERE booking_no = :reservation_no";
    
        $backupStmt = $this->conn->prepare($backupQuery);
        $backupStmt->bindParam(":reservation_no", $this->reservation_no);
        $backupStmt->bindParam(":deleted_by", $deleted_by);
        $backupStmt->bindParam(":deleted_date_time", $deleted_date_time);
    
        if($backupStmt->execute()){
            // Step 2: Delete the original reservation
            $deleteQuery = "DELETE FROM " . $this->table_name . " 
                            WHERE booking_no = :reservation_no";
    
            $deleteStmt = $this->conn->prepare($deleteQuery);
            $deleteStmt->bindParam(":reservation_no", $this->reservation_no);
    
            if($deleteStmt->execute()){
                return true;
            }
        }

        return false;
    }


    function getRoomSales() {
        $query = "SELECT r.vehicle_no AS room_no, r.booking_no AS reservation_no, r.folio_no, agent_name,
        customer_name AS guest_name, drivers AS adult, passengers AS child, date AS reserve_date,
        date_time AS reserve_created_at, rental_rate AS room_rate,
        (extra_driver * extra_driver_rate) AS extra_bed_charges,
        (extra_passenger * extra_passenger_rate) AS extra_person_charges,
        rental_rate + (extra_driver * extra_driver_rate) + (extra_passenger * extra_passenger_rate) AS total_charges
        FROM booking_detail rd 
        LEFT JOIN booking r ON rd.booking_no = r.booking_no
        WHERE rd.date >= :df AND rd.date <= :dt
        ORDER BY reserve_created_at, room_no, date";
    
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":df", $this->df);
        $stmt->bindParam(":dt", $this->dt);
        $stmt->execute();
        return $stmt;
    }

    function searchGuest(){

		// $condition = "";

		// if($this->df){
		// 	if($condition!=""){
		// 		$condition .= " AND ";
		// 	}
		// 	$condition .= " checkin_date >=:df";
		// }
		// if($this->dt){
		// 	if($condition!=""){
		// 		$condition .= " AND ";
		// 	}
		// 	$condition .= " checkin_date <=:dt";
		// }

		// if($this->phone){
		// 	if($condition!=""){ $condition .= " AND "; }
		// 	$condition .= " phone =:phone ";
		// }

		// if($this->name){
		// 	if($condition!=""){ $condition .= " AND "; }
		// 	$condition .= " (name LIKE  :name '%' or name LIKE '%' :name '%' or name Like '%' :name ) ";
		// }

		// if($this->nrc_no){
		// 	if($condition!=""){ $condition .= " AND "; }
		// 	$condition .= " (nrc_no LIKE  :nrc_no '%' or nrc_no LIKE '%' :nrc_no '%' or nrc_no Like '%' :nrc_no ) ";
		// }
		
		// if($condition!=""){
		// 	$condition = " WHERE " . $condition;
		// }

		$query = "SELECT * FROM 
        (SELECT r.booking_no AS reservation_no, customer_id AS guest_id, vehicle_no AS room_no,
            MIN(date) AS checkin, MAX(date) AS checkout_date, customer.*
            FROM booking r 
            LEFT JOIN booking_detail rd ON r.booking_no = rd.booking_no 
            LEFT JOIN customer ON r.customer_id = customer.id
            GROUP BY r.booking_no) AS a RIGHT JOIN
        (SELECT booking_no AS reser_no FROM booking_detail WHERE date >= :df AND date <= :dt GROUP BY booking_no) AS b ON
        a.reservation_no = b.reser_no
        ";


		$stmt = $this->conn->prepare($query);
		$this->df=htmlspecialchars(strip_tags($this->df));
		$this->dt=htmlspecialchars(strip_tags($this->dt));
		

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

    function getAgentPie() {

        $query = "SELECT agent_name,COUNT(*) AS c FROM booking GROUP BY agent_id" ;
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt;
    }

    function getArrival($dateType) {
        $dateCondition = "";
    
        if ($dateType === 'yesterday') {
            $dateCondition = "cidate = DATE_SUB(CURDATE(), INTERVAL 1 DAY)";
        } elseif ($dateType === 'today') {
            $dateCondition = "cidate = CURDATE()";
        } elseif ($dateType === 'tomorrow') {
            $dateCondition = "cidate = DATE_ADD(CURDATE(), INTERVAL 1 DAY)";
        } else {
            return false; // Invalid dateType
        }
    
        $query = "select COUNT(*) as arrival FROM (SELECT rd.*, MIN(date) AS cidate FROM booking_detail rd LEFT JOIN booking r ON rd.booking_no = r.booking_no
        WHERE picked_up = 0 GROUP BY rd.booking_no) AS a
        WHERE $dateCondition";
        $stmt = $this->conn->prepare($query);
    
        if ($stmt->execute()) {
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            return $row['arrival'];
        }
        return false;
    }

    function getAttendance() {

        $query = "SELECT staff_id,name,duty_in AS din,duty_in_photo AS dp FROM attendance a
        left join staff s ON a.staff_id=s.id where att_date=CURDATE() AND duty_in<>'' AND duty_out=''
        UNION ALL
        SELECT staff_id,name,second_duty_in AS din,second_duty_in_photo AS dp FROM attendance a
        left join staff s ON a.staff_id=s.id where att_date=CURDATE() AND second_duty_in<>'' AND second_duty_out=''" ;
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt;
    }

    function getColumnChart() {
        $query = "SELECT 
        d,
        COALESCE(expense, 0) AS expense,
        (COALESCE(income, 0) + COALESCE(room_income, 0)) AS income,
        (COALESCE(income, 0) + COALESCE(room_income, 0)-COALESCE(expense, 0)) AS profit
    FROM 
        (
            -- Generate a sequence of dates within the desired range
            SELECT 
                ADDDATE('1970-01-01', t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) AS d
            FROM 
                (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
                (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
                (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
                (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
                (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
            WHERE 
                ADDDATE('1970-01-01', t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i)
                BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01') AND LAST_DAY(CURDATE())
            ORDER BY d
        ) AS a
    LEFT JOIN 
        (
            SELECT 
                date, 
                SUM(quantity * price) AS expense
            FROM 
                expense 
            WHERE 
                date BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01') AND LAST_DAY(CURDATE())
                AND is_deleted IS NULL 
            GROUP BY 
                date
        ) AS expense_ ON a.d = expense_.date
    LEFT JOIN 
        (
            SELECT 
                date, 
                SUM(quantity * price) AS income
            FROM 
                income 
            WHERE 
                date BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01') AND LAST_DAY(CURDATE())
                AND is_deleted IS NULL 
            GROUP BY 
                date
        ) AS income_ ON a.d = income_.date
    LEFT JOIN 
        (
            SELECT 
                deposit_date, 
                SUM(deposit_amount) AS room_income
            FROM 
                deposit 
            WHERE 
                deposit_date BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01') AND LAST_DAY(CURDATE())
            GROUP BY 
                deposit_date
        ) AS roomincome_ ON a.d = roomincome_.deposit_date;
    ;
    
           " ;
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        }

        function getDeparture($dateType) {
            $dateCondition = "";
        
            if ($dateType === 'yesterday') {
                $dateCondition = "cidate = DATE_SUB(CURDATE(), INTERVAL 1 DAY)";
            } elseif ($dateType === 'today') {
                $dateCondition = "cidate = CURDATE()";
            } elseif ($dateType === 'tomorrow') {
                $dateCondition = "cidate = DATE_ADD(CURDATE(), INTERVAL 1 DAY)";
            } else {
                return false; // Invalid dateType
            }
        
            $query = "select COUNT(*) as departure FROM (SELECT rd.*, DATE_ADD(MAX(date), INTERVAL 1 DAY) AS cidate FROM booking_detail rd LEFT JOIN booking r ON rd.booking_no = r.booking_no
            WHERE picked_up = 1 AND returned = 1 GROUP BY rd.booking_no) AS a
            WHERE $dateCondition";
            $stmt = $this->conn->prepare($query);
        
            if ($stmt->execute()) {
                $row = $stmt->fetch(PDO::FETCH_ASSOC);
                return $row['departure'];
            }
            return false;
        }

        function getExpense($dateType) {
            $dateCondition = "";
        
            if ($dateType === 'yesterday') {
                $dateCondition = "date = DATE_SUB(CURDATE(), INTERVAL 1 DAY)";
            } elseif ($dateType === 'today') {
                $dateCondition = "date = CURDATE()";
            } elseif ($dateType === 'tomorrow') {
                $dateCondition = "date = DATE_ADD(CURDATE(), INTERVAL 1 DAY)";
            } else {
                return false; 
            }
        
            $query = "SELECT ifnull(sum(quantity*price),0) AS expense 
                      FROM expense where is_deleted is null and " . $dateCondition ;
            $stmt = $this->conn->prepare($query);
        
            if ($stmt->execute()) {
                $row = $stmt->fetch(PDO::FETCH_ASSOC);
                return $row['expense'];
            }
            return false;
        }

        function getIncome($dateType) {
            $dateCondition = "";
        
            if ($dateType === 'yesterday') {
                $dateCondition = "date = DATE_SUB(CURDATE(), INTERVAL 1 DAY)";
            } elseif ($dateType === 'today') {
                $dateCondition = "date = CURDATE()";
            } elseif ($dateType === 'tomorrow') {
                $dateCondition = "date = DATE_ADD(CURDATE(), INTERVAL 1 DAY)";
            } else {
                return false; 
            }
        
            $query = "select sum(income) as income FROM (SELECT ifnull(sum(quantity*price),0) AS income 
                      FROM income where is_deleted is null and " . $dateCondition . "
                        UNION ALL
                        SELECT ifnull(sum(quantity*price),0) AS income 
                      FROM expense where is_deleted is null AND " . $dateCondition . " and account_code='1-2120') AS a " ;
            $stmt = $this->conn->prepare($query);
        
            if ($stmt->execute()) {
                $row = $stmt->fetch(PDO::FETCH_ASSOC);
                return $row['income'];
            }
            return false;
        }

        function getNightChart($y) {

            $query = "SELECT LEFT(date, 7) AS month,COUNT(id) AS c FROM booking_detail where LEFT(DATE, 4) =$y GROUP BY LEFT(date, 7) " ;
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        }

        function getOnBoard($dateType) {
            $dateCondition = "";
        
            if ($dateType === 'yesterday') {
                $dateCondition = "date = DATE_SUB(CURDATE(), INTERVAL 1 DAY)";
            } elseif ($dateType === 'today') {
                $dateCondition = "date = CURDATE()";
            } elseif ($dateType === 'tomorrow') {
                $dateCondition = "date = DATE_ADD(CURDATE(), INTERVAL 1 DAY)";
            } else {
                return false; // Invalid dateType
            }
        
            $query = "select COUNT(*) as onboard FROM (SELECT rd.* FROM booking_detail rd LEFT JOIN booking r ON rd.booking_no = r.booking_no
             WHERE returned = 0 AND picked_up = 1 AND $dateCondition GROUP BY rd.booking_no) AS a
            WHERE $dateCondition";
            $stmt = $this->conn->prepare($query);
        
            if ($stmt->execute()) {
                $row = $stmt->fetch(PDO::FETCH_ASSOC);
                return $row['onboard'];
            }
            return false;
        }

        function getOscIncome($dateType) {
            $dateCondition = "";
        
            if ($dateType === 'yesterday') {
                $dateCondition = "substring(payment_date_time,1,10) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)";
            } elseif ($dateType === 'today') {
                $dateCondition = "substring(payment_date_time,1,10) = CURDATE()";
            } elseif ($dateType === 'tomorrow') {
                $dateCondition = "substring(payment_date_time,1,10) = DATE_ADD(CURDATE(), INTERVAL 1 DAY)";
            } else {
                return false; 
            }
        
            $query = "SELECT ifnull(sum(quantity*price),0) AS osc_income 
                      FROM other_service_charges where foc=0 and payment_date_time is not null and " . $dateCondition ;
            $stmt = $this->conn->prepare($query);
        
            if ($stmt->execute()) {
                $row = $stmt->fetch(PDO::FETCH_ASSOC);
                return $row['osc_income'];
            }
            return false;
        }

        function getReservationDetailByDateType($dateType){
            $dateCondition = "";
        
            if ($dateType === 'yesterday') {
                $dateCondition = "substring(created_at,1,10) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)";
            } elseif ($dateType === 'today') {
                $dateCondition = "substring(created_at,1,10) = CURDATE()";
            } elseif ($dateType === 'tomorrow') {
                $dateCondition = "substring(created_at,1,10) = DATE_ADD(CURDATE(), INTERVAL 1 DAY)";
            } else {
                return false; // Invalid dateType
            }
            $query = "SELECT r.*, MIN(date) AS checkin_date, DATE_ADD(MAX(date), INTERVAL 1 DAY) AS checkout_date,
            DATEDIFF(DATE_ADD(MAX(date), INTERVAL 1 DAY), MIN(date)) AS nights FROM booking r
            LEFT JOIN booking_detail rd ON r.booking_no = rd.booking_no
            where $dateCondition GROUP BY r.booking_no";
    
            $stmt = $this->conn->prepare( $query );
            $stmt->execute();
            return $stmt;
        }

        function getReservation($dateType) {
            $dateCondition = "";
        
            if ($dateType === 'yesterday') {
                $dateCondition = "substring(date_time,1,10) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)";
            } elseif ($dateType === 'today') {
                $dateCondition = "substring(date_time,1,10) = CURDATE()";
            } elseif ($dateType === 'tomorrow') {
                $dateCondition = "substring(date_time,1,10) = DATE_ADD(CURDATE(), INTERVAL 1 DAY)";
            } else {
                return false; // Invalid dateType
            }
        
            $query = "select count(*) as reservation from booking where $dateCondition";
            $stmt = $this->conn->prepare($query);
        
            if ($stmt->execute()) {
                $row = $stmt->fetch(PDO::FETCH_ASSOC);
                return $row['reservation'];
            }
            return false;
        }

        function getRoomIncome($dateType) {
            $dateCondition = "";
        
            if ($dateType === 'yesterday') {
                $dateCondition = "deposit_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY)";
            } elseif ($dateType === 'today') {
                $dateCondition = "deposit_date = CURDATE()";
            } elseif ($dateType === 'tomorrow') {
                $dateCondition = "deposit_date = DATE_ADD(CURDATE(), INTERVAL 1 DAY)";
            } else {
                return false; 
            }
        
            $query = "SELECT ifnull(sum(deposit_amount),0) AS room_income 
                      FROM deposit where " . $dateCondition ;
            $stmt = $this->conn->prepare($query);
        
            if ($stmt->execute()) {
                $row = $stmt->fetch(PDO::FETCH_ASSOC);
                return $row['room_income'];
            }
            return false;
        }
    // // Update an existing reservation
    // function update() {
    //     $query = "UPDATE " . $this->table_name . " 
    //               SET reservation_no=:reservation_no, guest_name=:guest_name, 
    //                   contact_number=:contact_number, email=:email, 
    //                   deposit_date=:deposit_date, deposit_amount=:deposit_amount, 
    //                   payment_method=:payment_method, remark=:remark, 
    //                   update_by=:update_by  
    //               WHERE id=:id";
    //     $stmt = $this->conn->prepare($query);

    //     // Bind values
    //     $stmt->bindParam(":id", $this->id);
    //     $stmt->bindParam(":reservation_no", $this->reservation_no);
    //     $stmt->bindParam(":guest_name", $this->guest_name);
    //     $stmt->bindParam(":contact_number", $this->contact_number);
    //     $stmt->bindParam(":email", $this->email);
    //     $stmt->bindParam(":deposit_date", $this->deposit_date);
    //     $stmt->bindParam(":deposit_amount", $this->deposit_amount);
    //     $stmt->bindParam(":payment_method", $this->payment_method);
    //     $stmt->bindParam(":remark", $this->remark);
    //     $stmt->bindParam(":update_by", $this->update_by);

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

    // // Delete a reservation
    // 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 deleteFromReservation() {
    //     $query = "DELETE FROM " . $this->table_name . " WHERE reservation_no = :reservation_no and room_no_r=:room_no_r";
    //     $stmt = $this->conn->prepare($query);
    //     $stmt->bindParam(":reservation_no", $this->reservation_no);
    //     $stmt->bindParam(":room_no_r", $this->room_no_r);
    //     if ($stmt->execute()) {
    //         return true;
    //     }
    //     return false;
    // }

    // function editReservation() {
    //     $query = "DELETE FROM " . $this->table_name . " WHERE reservation_no = :reservation_no and room_no_r=:room_no_r";
    //     $stmt = $this->conn->prepare($query);
    //     $stmt->bindParam(":reservation_no", $this->reservation_no);
    //     $stmt->bindParam(":room_no_r", $this->old_room_no);
    //     if ($stmt->execute()) {
    //         return true;
    //     }
    //     return false;
    // }

    function updateReservation() {
        $query = "UPDATE booking set date_time=:date_time,booking_no=:reservation_no,agent_id=:agent_id,folio_no=:folio_no,agent_name=:agent_name,
                    customer_id=:guest_id, customer_name=:guest_name, discount_amount=:discount_amount,
                    remark=:remark,rental_mode=:rental_mode,pickup_datetime=:pickup_datetime,return_datetime=:return_datetime,rental_hours=:rental_hours,
                    drivers=:adult, passengers=:child, 
                    vehicle_category=:room_type,vehicle_note=:bed_type, vehicle_no=:room_no,   
                    update_by=:update_by where booking_no=:reservation_no_old ";
        $stmt = $this->conn->prepare($query);

        $stmt->bindParam(":reservation_no", $this->reservation_no);
        $stmt->bindParam(":reservation_no_old", $this->reservation_no_old);
        $stmt->bindParam(":date_time", $this->date_time);
        $stmt->bindParam(":folio_no", $this->folio_no);
        $stmt->bindParam(":agent_id", $this->agent_id);
        $stmt->bindParam(":agent_name", $this->agent_name);
        $stmt->bindParam(":guest_id", $this->guest_id);
        $stmt->bindParam(":guest_name", $this->guest_name);
        $stmt->bindParam(":discount_amount", $this->discount_amount);
        $stmt->bindParam(":adult", $this->adult);
        $stmt->bindParam(":child", $this->child);
        $stmt->bindParam(":remark", $this->remark);
        $stmt->bindParam(":rental_mode", $this->rental_mode);
        $stmt->bindParam(":pickup_datetime", $this->pickup_datetime);
        $stmt->bindParam(":return_datetime", $this->return_datetime);
        $stmt->bindParam(":rental_hours", $this->rental_hours);
        $stmt->bindParam(":room_type", $this->room_type);
        $stmt->bindParam(":bed_type", $this->bed_type);
        $stmt->bindParam(":room_no", $this->room_no);
        $stmt->bindParam(":update_by", $this->update_by);
        if ($stmt->execute()) {
            return true;
        }
        return false;
    }

    // // Get a single reservation by ID
    function getOneRow() {
        $query = "SELECT r.*, vehicle.extra_driver AS extra_bed, vehicle.extra_passenger AS extra_person,
        r.booking_no AS reservation_no, r.customer_id AS guest_id, r.customer_name AS guest_name,
        r.drivers AS adult, r.passengers AS child, r.vehicle_no AS room_no,
        r.vehicle_category AS room_type, r.vehicle_note AS bed_type
        FROM booking r left join booking_detail rd on 
        r.booking_no=rd.booking_no left join vehicle on r.vehicle_no=vehicle.vehicle_no WHERE r.booking_no = ? and r.vehicle_no=?  group by r.vehicle_no";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $this->reservation_no);
        $stmt->bindParam(2, $this->room_no);
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($row) {
            $this->id = $row['id'];
            $this->date_time = $row['date_time'];
            $this->reservation_no = $row['reservation_no'];
            $this->folio_no = $row['folio_no'];
            $this->discount_amount = $row['discount_amount'] ?? 0;
            $this->agent_id = $row['agent_id'];
            $this->agent_name = $row['agent_name'];
            $this->guest_id = $row['guest_id'];
            $this->guest_name = $row['guest_name'];
            $this->remark = $row['remark'];
            $this->adult = $row['adult'];
            $this->child = $row['child'];
            $this->room_no = $row['room_no'];
            $this->room_type = $row['room_type'];
            $this->bed_type = $row['bed_type'];
            $this->extra_bed = $row['extra_bed'];
            $this->extra_person = $row['extra_person'];
        }
    }



    // // Check if a reservation exists by reservation number
    function exist() {
        $query = "SELECT * FROM " . $this->table_name . " WHERE booking_no = ? LIMIT 0,1";
        $stmt = $this->conn->prepare($query);
        $this->reservation_no = htmlspecialchars(strip_tags($this->reservation_no));

        $stmt->bindParam(1, $this->reservation_no);
        $stmt->execute();
        if ($stmt->rowCount() > 0) {
            return true;
        }
        return false;
    }
    
    public function getMonthlySalesByChannel() {
    $query = "
        SELECT 
            MONTH(d.date) AS month,
            SUM(CASE WHEN t.agent_name = 'Walk-in' THEN rental_rate + (extra_driver * extra_driver_rate) + (extra_passenger * extra_passenger_rate) ELSE 0 END) AS WalkIn,
            SUM(CASE WHEN t.agent_name <> 'Walk-in' THEN rental_rate + (extra_driver * extra_driver_rate) + (extra_passenger * extra_passenger_rate) ELSE 0 END) AS OTA
        FROM booking t
        JOIN booking_detail d 
            ON t.booking_no = d.booking_no
        WHERE YEAR(d.date) = YEAR(CURDATE())
        GROUP BY MONTH(d.date)
        ORDER BY month";

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

    return $stmt;
}


}
?>

