<?php
namespace classs;
use PDO;
use DateTime;
use PDOException;
// require_once('../fun/functions.php');
class ReservationsManager
{
    private $conn;

    public function __construct($conn)
    {
        $this->conn = $conn;
    }
    public function getAllReservations()
    {
        $sql = "SELECT * FROM reservations";
        $stmt = $this->conn->query($sql);

        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : array();
    }
    public function getAllHotels()
    {
        $sql = "SELECT * FROM hotels";
        $stmt = $this->conn->query($sql);

        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : array();
    }
    public function getHotelById($hotels_id)
    {
        // $sql = "SELECT * FROM reservations WHERE id = :reservation_id";
        $sql = "SELECT h.*, hb.*
                FROM hotels h
                LEFT JOIN hotel_bookings hb ON hb.id_hotel = h.ID
                WHERE h.ID = :hotels_id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':hotels_id', $hotels_id);
        $stmt->execute();

        return ($stmt->rowCount() > 0) ? $stmt->fetch(PDO::FETCH_ASSOC) : null;
    }
    public function addNewClient($newClientName, $nationality_id)
    {
        // $newClientName = $_POST['new_client_name'];
        $insertClientQuery = "INSERT INTO clients (client_name, id_nationality) VALUES (:client_name, :id_nationality)";
        $insertClientStmt = $this->conn->prepare($insertClientQuery);
        $insertClientStmt->bindParam(':client_name', $newClientName);
        $insertClientStmt->bindParam(':id_nationality', $nationality_id);
        $insertClientStmt->execute();
        // استخراج معرف العميل الجديد الذي تم إضافته
        // $datanew = "{'client_name': $newClientName, 'id_nationality': $nationality_id}";
        $client_id = $this->conn->lastInsertId();
        logToHistory('insert', 'clients', $client_id, '', '', 'ID');
        return $client_id ;
    }
    public function getReservationsForCurrentMonth()
    {
        $currentMonth = date('Y-m');

        $sql = "SELECT * FROM reservations
                WHERE DATE_FORMAT(departure_date, '%Y-%m') = :current_month
                OR DATE_FORMAT(arrival_date, '%Y-%m') = :current_month";

        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':current_month', $currentMonth);
        $stmt->execute();

        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : array();
    }

    public function getReservationsForCurrentMonthWithNames($id_management)
    {
        $currentMonth = date('Y-m');

        $sql = "SELECT r.*, a.*, c.*, n.*, r.date_insert AS reservations_date_insert
                FROM reservations r
                LEFT JOIN agents a ON r.agent_id = a.id
                LEFT JOIN clients c ON r.client_id = c.id
                LEFT JOIN nationality n ON c.id_nationality = n.id
                WHERE r.id_tourism_management = :id_tourism_management AND  (DATE_FORMAT(r.departure_date, '%Y-%m') = :current_month)";
                // OR DATE_FORMAT(r.arrival_date, '%Y-%m') = :current_month)";

        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':id_tourism_management', $id_management);
        $stmt->bindParam(':current_month', $currentMonth);
        $stmt->execute();

        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : array();
    }

    public function getReservationsByDateRange($id_management, $start_date, $end_date)
    {
        // Add your validation and sanitization here before executing the SQL query

        $sql = "SELECT r.*, a.*, c.*, n.*, r.date_insert AS reservations_date_insert
                FROM reservations r
                LEFT JOIN agents a ON r.agent_id = a.id
                LEFT JOIN clients c ON r.client_id = c.id
                LEFT JOIN nationality n ON c.id_nationality = n.id
                WHERE r.id_tourism_management = :id_tourism_management AND r.departure_date >= :start_date AND r.departure_date <= :end_date";
                // WHERE r.id_tourism_management = :id_tourism_management AND r.departure_date >= :start_date AND r.arrival_date <= :end_date";

        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':start_date', $start_date);
        $stmt->bindParam(':end_date', $end_date);
        $stmt->bindParam(':id_tourism_management', $id_management);
        $stmt->execute();

        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : array();
    }

    public function getReservationById($reservation_id)
    {
        // $sql = "SELECT * FROM reservations WHERE id = :reservation_id";
        $sql = "SELECT r.*, a.*, c.*, n.*
                FROM reservations r
                LEFT JOIN agents a ON r.agent_id = a.id
                LEFT JOIN clients c ON r.client_id = c.id
                LEFT JOIN nationality n ON c.id_nationality = n.id
                WHERE r.id = :reservation_id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':reservation_id', $reservation_id);
        $stmt->execute();

        return ($stmt->rowCount() > 0) ? $stmt->fetch(PDO::FETCH_ASSOC) : null;
    }
    public function getInvoicingById($id_invoicing)
    {
        // $sql = "SELECT * FROM reservations WHERE id = :reservation_id";
        $sql = "SELECT i.*
                FROM invoicing i
                WHERE i.ID = :id_invoicing";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':id_invoicing', $id_invoicing);
        $stmt->execute();

        return ($stmt->rowCount() > 0) ? $stmt->fetch(PDO::FETCH_ASSOC) : null;
    }
    public function getInvoicingDataById($id_invoicing)
    {
        $sql = "SELECT il.*
                FROM invoicing_line il
                WHERE il.id_invoicing = :id_invoicing";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':id_invoicing', $id_invoicing);
        $stmt->execute();

        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : null;
    }
    public function getInvoicingLineById($id_invoicing_line)
    {
        // $sql = "SELECT * FROM reservations WHERE id = :reservation_id";
        $sql = "SELECT il.*
                FROM invoicing_line il
                WHERE il.ID = :id_invoicing_line";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':id_invoicing_line', $id_invoicing_line);
        $stmt->execute();

        return ($stmt->rowCount() > 0) ? $stmt->fetch(PDO::FETCH_ASSOC) : null;
    }
    public function getInvoicingLinesById($id_invoicing, $idtype)
    {
        $sql = "SELECT il.*
                FROM invoicing_line il
                WHERE il.id_invoicing = :id_invoicing AND id_type = :id_type";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':id_invoicing', $id_invoicing);
        $stmt->bindParam(':id_type', $idtype);
        $stmt->execute();

        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : null;
    }
    public function getBookingFileById($id_reservation, $idtype)
    {
        $sql = "SELECT il.*
                FROM booking_file il
                WHERE il.id_reservation = :id_reservation AND id_line_type = :id_type";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':id_reservation', $id_reservation);
        $stmt->bindParam(':id_type', $idtype);
        $stmt->execute();

        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : null;
    }

    public function sumInvoicingLineById($id_invoicing, $idtype = NULL)
    {
        if($idtype === NULL){
            $sql = "SELECT sum(total_invoice) AS 'cash' 
                FROM invoicing_line 
                where id_invoicing = :id_invoicing ";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':id_invoicing', $id_invoicing);
        } else {
            $sql = "SELECT sum(total_invoice) AS 'cash' 
                    FROM invoicing_line 
                    where id_invoicing = :id_invoicing AND id_type = :id_type";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':id_invoicing', $id_invoicing);
            $stmt->bindParam(':id_type', $idtype);
        }
        $stmt->execute();
        // $stmts = $stmt->fetchAll();
        return ($stmt->rowCount() > 0) ? $stmt->fetch(PDO::FETCH_ASSOC) : null;
        // return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : null;
    }
    public function sumBookingFileById($id_reservation, $idtype = NULL)
    {
        if($idtype === NULL){
            $sql = "SELECT sum(total_cost) AS 'cash' 
                FROM booking_file 
                where id_reservation = :id_reservation ";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':id_reservation', $id_reservation);
        } else {
            $sql = "SELECT sum(total_cost) AS 'cash' 
                    FROM booking_file 
                    where id_reservation = :id_reservation AND id_line_type = :id_line_type";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':id_reservation', $id_reservation);
            $stmt->bindParam(':id_line_type', $idtype);
        }
        
        $stmt->execute();
        // $stmts = $stmt->fetchAll();
        
        return ($stmt->rowCount() > 0) ? $stmt->fetch(PDO::FETCH_ASSOC) : null;
        // return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : null;
    }
    public function getNumberIndividualsReservationById($reservation_id)
    {
        // $sql = "SELECT * FROM reservations WHERE id = :reservation_id";
        $sql = "SELECT n.*, p.*, n.ID as id_number_individuals
                FROM number_individuals n
                LEFT JOIN persons p ON n.id_persons = p.ID
                WHERE n.id_file = :reservation_id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':reservation_id', $reservation_id);
        $stmt->execute();

        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : null;
    }
    public function getHotelBookingsReservationById($reservation_id)
    {
        // $sql = "SELECT * FROM reservations WHERE id = :reservation_id";
        $sql = "SELECT hb.*, h.*, rp.*, rt.*, ms.*, hb.ID as id_hotel_bookings
                FROM hotel_bookings hb
                LEFT JOIN hotels h ON hb.id_hotel = h.ID
                LEFT JOIN room_prices rp ON hb.id_room_prices = rp.ID
                LEFT JOIN room_type rt ON rp.id_room_type = rt.ID
                LEFT JOIN meal_system ms ON rp.id_meal_system = ms.ID
                WHERE hb.id_reservation = :reservation_id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':reservation_id', $reservation_id);
        $stmt->execute();
        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : null;
    }
    public function getHotelBookingsById($hotel_bookings_id)
    {
        // $sql = "SELECT * FROM reservations WHERE id = :reservation_id";
        $sql = "SELECT hb.*, h.*, rp.*, rt.*, ms.*, hb.ID as id_hotel_bookings
                FROM hotel_bookings hb
                LEFT JOIN hotels h ON hb.id_hotel = h.ID
                LEFT JOIN room_prices rp ON hb.id_room_prices = rp.ID
                LEFT JOIN room_type rt ON rp.id_room_type = rt.ID
                LEFT JOIN meal_system ms ON rp.id_meal_system = ms.ID
                WHERE hb.ID = :hotel_bookings_id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':hotel_bookings_id', $hotel_bookings_id);
        $stmt->execute();
        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : null;
    }
    public function getHotelBookings($id_tourism_management)
    {
        // $sql = "SELECT * FROM reservations WHERE id = :reservation_id";
        $sql = "SELECT r.*, hb.*, h.*, rp.*, rt.*, ms.*, hb.ID as id_hotel_bookings
                FROM hotel_bookings hb
                LEFT JOIN hotels h ON hb.id_hotel = h.ID
                LEFT JOIN room_prices rp ON hb.id_room_prices = rp.ID
                LEFT JOIN room_type rt ON rp.id_room_type = rt.ID
                LEFT JOIN meal_system ms ON rp.id_meal_system = ms.ID
                LEFT JOIN reservations r ON hb.id_reservation = r.id
                WHERE r.id_tourism_management = :id_tourism_management";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':id_tourism_management', $id_tourism_management);
        $stmt->execute();
        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : null;
    }
    public function getHotelPayment($id_tourism_management, $day, $id_type)
    {
        // $sql = "SELECT * FROM reservations WHERE id = :reservation_id";
        $sql = "SELECT hbpa.*, r.*, hb.*, h.*, rp.*, rt.*, ms.*, hb.ID as id_hotel_bookings
                FROM hotel_booking_pay_arr hbpa
                LEFT JOIN hotel_bookings hb ON hbpa.id_hotel_booking = hb.ID
                LEFT JOIN hotels h ON hb.id_hotel = h.ID
                LEFT JOIN room_prices rp ON hb.id_room_prices = rp.ID
                LEFT JOIN room_type rt ON rp.id_room_type = rt.ID
                LEFT JOIN meal_system ms ON rp.id_meal_system = ms.ID
                LEFT JOIN reservations r ON hb.id_reservation = r.id
                WHERE hbpa.id_type = :id_type AND r.id_tourism_management = :id_tourism_management AND DATE_FORMAT(hbpa.date_go, '%Y-%m-%d') = :current_day";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':id_type', $id_type);
        $stmt->bindParam(':id_tourism_management', $id_tourism_management);
        $stmt->bindParam(':current_day', $day);
        $stmt->execute();
        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : null;
    }
    public function getFlightBookingsReservationById($reservation_id)
    {
        // $sql = "SELECT * FROM reservations WHERE id = :reservation_id";
        $sql = "SELECT fb.*, a.*
                FROM flight_bookings fb
                LEFT JOIN airline a ON fb.id_airline = a.ID
                WHERE fb.id_reservation = :reservation_id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':reservation_id', $reservation_id);
        $stmt->execute();
        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : null;
    }
    public function getTrainBookingsReservationById($reservation_id)
    {
        // $sql = "SELECT * FROM reservations WHERE id = :reservation_id";
        $sql = "SELECT *
                FROM train_bookings
                WHERE id_reservation = :reservation_id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':reservation_id', $reservation_id);
        $stmt->execute();
        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : null;
    }
    public function getBookingFileReservationById($reservation_id)
    {
        // $sql = "SELECT * FROM reservations WHERE id = :reservation_id";
        $sql = "SELECT *
                FROM booking_file
                WHERE id_reservation = :reservation_id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':reservation_id', $reservation_id);
        $stmt->execute();
        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : null;
    }
    public function getTransportationBookingsReservationById($reservation_id)
    {
        // $sql = "SELECT * FROM reservations WHERE id = :reservation_id";
        $sql = "SELECT *
                FROM run_command
                WHERE id_operator = :reservation_id AND client_or_file = 2";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':reservation_id', $reservation_id);
        $stmt->execute();
        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : null;
    }
    public function getEmployeeBookingsReservationById($reservation_id, $id_department_employees)
    {
        // $sql = "SELECT * FROM reservations WHERE id = :reservation_id";
        $sql = "SELECT eb.ID as id_booking ,eb.*, e.* 
                FROM employee_booking eb
                LEFT JOIN employees e ON eb.id_employee = e.ID
                WHERE eb.id_department_employees = :id_department_employees AND id_reservation = :reservation_id" ;
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':id_department_employees', $id_department_employees);
        $stmt->bindParam(':reservation_id', $reservation_id);
        $stmt->execute();
        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : null;
    }
    public function addReservation($tourism_management, $cost_center_id, $file_number, $agent_id, $client_id, $arrival_date, $departure_date, $remarks, $sessionUser, $nationality_id)
    {
        // Use generateFileNumber function
        // $file_number = $this->generateFileNumber($departure_date);

        // Add your validation and sanitization here before executing the SQL query
        try {
            $this->conn->beginTransaction();

            $sql = "INSERT INTO reservations (id_tourism_management, id_cost_center, file_number, agent_id, client_id, arrival_date, departure_date, remarks, user_id_insert)
                    VALUES (:id_tourism_management, :id_cost_center, :file_number, :agent_id, :client_id, :arrival_date, :departure_date, :remarks, :user_id_insert)";

            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':id_tourism_management', $tourism_management);
            $stmt->bindParam(':id_cost_center', $cost_center_id);
            $stmt->bindParam(':file_number', $file_number);
            $stmt->bindParam(':agent_id', $agent_id);
            $stmt->bindParam(':client_id', $client_id);
            $stmt->bindParam(':arrival_date', $arrival_date);
            $stmt->bindParam(':departure_date', $departure_date);
            $stmt->bindParam(':remarks', $remarks);
            $stmt->bindParam(':user_id_insert', $sessionUser);
            $restmt = $stmt->execute();

            $reservations_id = $this->conn->lastInsertId();

            $invoicing_no = $this->generateNoRunInvoce();

            $sql = "INSERT INTO invoicing (id_invoice_type, invoicing_no, id_reservation, file_no, id_agent, arrival_date, departure_date, id_nationality, invoice_status, user_id_insert)
                                VALUES (1, :invoicing_no, :id_reservation, :file_no, :id_agent, :arrival_date, :departure_date, :id_nationality, 0, :user_id_insert)";

            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':invoicing_no', $invoicing_no);
            $stmt->bindParam(':id_reservation', $reservations_id);
            $stmt->bindParam(':file_no', $file_number);
            $stmt->bindParam(':id_agent', $agent_id);
            $stmt->bindParam(':arrival_date', $arrival_date);
            $stmt->bindParam(':departure_date', $departure_date);
            $stmt->bindParam(':id_nationality', $nationality_id);
            $stmt->bindParam(':user_id_insert', $sessionUser);
            $restmt = $stmt->execute();

            $invoicing_id = $this->conn->lastInsertId();

            logToHistory('insert', 'reservations', $reservations_id, '', '', 'id');
            logToHistory('insert', 'invoicing', $invoicing_id, '', '', 'ID');

            $this->conn->commit();
            return $reservations_id;
        } catch (PDOException $e) {
            error_log("حدث خطأ فى السيرفر أثناء إضافة الملف: " . $e->getMessage(), 0);
            $this->conn->rollBack();
            return false;
        }
    }

    public function addInvoicingLine($booking_id, $id_invoice, $cost, $cost_child, $num_child, $nights, $sessionUser, $all_Individuals, $id_type, $disc)
    {
        try {
            $this->conn->beginTransaction();
            if (!empty($nights)) {
                $total_invoice = ($cost * $all_Individuals) * $nights;
            } else {
                $total_invoice = ($cost * $all_Individuals);
            }
            if ($num_child > 0 && $cost_child > 0) {
                if (!empty($nights)) {
                    $total_invoice += ($cost_child * $num_child) * $nights;
                } else {
                    $total_invoice += ($cost_child * $num_child);
                }
            }
            // $disc = 'مندوب مرافق للمجموعة';
            $sql = "INSERT INTO invoicing_line (id_invoicing, id_type, id_line_booking, no_of, no_CH, disc, rate_pp, rate_CH, nights, total_invoice, user_id_insert)
                                        VALUES (:id_invoicing, :id_type, :id_line_booking, :no_of, :no_CH, :disc, :rate_pp, :rate_CH, :nights, :total_invoice, :user_id_insert)";
                $stmt = $this->conn->prepare($sql);
                $stmt->bindParam(':id_invoicing', $id_invoice);
                $stmt->bindParam(':id_type', $id_type);
                $stmt->bindParam(':id_line_booking', $booking_id);
                $stmt->bindParam(':no_of', $all_Individuals);
                $stmt->bindParam(':no_CH', $num_child);
                $stmt->bindParam(':disc', $disc);
                $stmt->bindParam(':rate_pp', $cost);
                $stmt->bindParam(':rate_CH', $cost_child);
                $stmt->bindParam(':nights', $nights);
                $stmt->bindParam(':total_invoice', $total_invoice);
                $stmt->bindParam(':user_id_insert', $sessionUser);
                $restmt = $stmt->execute();
                $this->conn->commit();
            return $restmt;
        } catch (PDOException $e) {
            error_log("حدث خطأ فى السيرفر أثناء إضافة: " . $e->getMessage(), 0);
            $this->conn->rollBack();
            return false;
        }
        // استخراج معرف العميل الجديد الذي تم إضافته
        // return $client_id = $this->conn->lastInsertId();
    }

    public function updateInvoicingLine($invoicing_line_id, $booking_id, $id_invoice, $cost, $cost_child, $num_child, $nights, $sessionUser, $all_Individuals, $id_type, $disc)
{
    try {
        $this->conn->beginTransaction();

        if (!empty($nights)) {
            $total_invoice = ($cost * $all_Individuals) * $nights;
        } else {
            $total_invoice = ($cost * $all_Individuals);
        }
        if ($num_child > 0 && $cost_child > 0) {
            if (!empty($nights)) {
                $total_invoice += ($cost_child * $num_child) * $nights;
            } else {
                $total_invoice += ($cost_child * $num_child);
            }
        }

        $sql = "UPDATE invoicing_line 
                SET id_invoicing = :id_invoicing, 
                    id_type = :id_type, 
                    id_line_booking = :id_line_booking, 
                    no_of = :no_of, 
                    no_CH = :no_CH, 
                    disc = :disc, 
                    rate_pp = :rate_pp, 
                    rate_CH = :rate_CH, 
                    nights = :nights, 
                    total_invoice = :total_invoice, 
                    user_id_insert = :user_id_insert 
                WHERE ID = :invoicing_line_id";

        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':invoicing_line_id', $invoicing_line_id);
        $stmt->bindParam(':id_invoicing', $id_invoice);
        $stmt->bindParam(':id_type', $id_type);
        $stmt->bindParam(':id_line_booking', $booking_id);
        $stmt->bindParam(':no_of', $all_Individuals);
        $stmt->bindParam(':no_CH', $num_child);
        $stmt->bindParam(':disc', $disc);
        $stmt->bindParam(':rate_pp', $cost);
        $stmt->bindParam(':rate_CH', $cost_child);
        $stmt->bindParam(':nights', $nights);
        $stmt->bindParam(':total_invoice', $total_invoice);
        $stmt->bindParam(':user_id_insert', $sessionUser);

        $result = $stmt->execute();
        $this->conn->commit();

        return $result;
    } catch (PDOException $e) {
        error_log("حدث خطأ فى السيرفر أثناء التحديث: " . $e->getMessage(), 0);
        $this->conn->rollBack();
        return false;
    }
}

    function calculateNightAndDayCount($start_date, $end_date) {
        // تحويل التواريخ إلى كائنات DateTime
        $start_date = new DateTime($start_date);
        $end_date = new DateTime($end_date);
    
        // حساب الفارق بين التواريخ
        $interval = $start_date->diff($end_date);
    
        // الحصول على عدد الليالي
        $number_of_nights = $interval->days;
    
        // الحصول على عدد الأيام (بما في ذلك يوم البداية)
        $number_of_days = $interval->days + 1;
    
        // إعادة مصفوفة تحتوي على عدد الليالي وعدد الأيام
        return [
            'number_of_nights' => $number_of_nights,
            'number_of_days' => $number_of_days
        ];
    }

    // Implement update, delete, and display functions as needed
    public function generateFileNumber($departure_date)
    {
        $formatted_departure_date = date("n-Y", strtotime($departure_date));
        // echo "Formatted Departure Date: $formatted_departure_date<br>";

        $sql_search = "SELECT MAX(CAST(SUBSTRING_INDEX(file_number, '/', 1) AS SIGNED)) AS max_file_number
                    FROM reservations
                    WHERE SUBSTRING_INDEX(file_number, '/', -1) LIKE :formatted_departure_date";

        $stmt_search = $this->conn->prepare($sql_search);
        $stmt_search->bindParam(":formatted_departure_date", $formatted_departure_date);
        $stmt_search->execute();
        $row_search = $stmt_search->fetch(PDO::FETCH_ASSOC);
        $max_file_number = $row_search['max_file_number'];
        // echo "Max File Number: $max_file_number<br>";

        $file_number = ($max_file_number === null) ? 1 : ($max_file_number + 1);
        $file_number = $file_number . '/' . $formatted_departure_date;
        // echo "Generated File Number: $file_number<br>";

        return $file_number;
    }

    public function generateNoRunInvoce()
    {
        // echo "Formatted Departure Date: $formatted_departure_date<br>";

        $sql_search = "SELECT MAX(invoicing_no) AS max_invoicing_no
                    FROM invoicing ";

        $stmt_search = $this->conn->prepare($sql_search);
        $stmt_search->execute();
        $row_search = $stmt_search->fetch(PDO::FETCH_ASSOC);
        $max_invoicing_no = $row_search['max_invoicing_no'];
        // echo "Max File Number: $max_file_number<br>";

        $new_invoicing_no = ($max_invoicing_no === null) ? 1 : ($max_invoicing_no + 1);
        // echo "Generated File Number: $file_number<br>";

        return $new_invoicing_no;
    }
    public function updateReservation($reservation_id, $agent_id, $client_id, $arrival_date, $departure_date, $remarks)
    {
        // Add your validation and sanitization here before executing the SQL query

        $sql = "UPDATE reservations
                SET agent_id = :agent_id, client_id = :client_id, arrival_date = :arrival_date, departure_date = :departure_date, remarks = :remarks,
                    date_update = CURRENT_TIMESTAMP()
                WHERE id = :reservation_id";

        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':agent_id', $agent_id);
        $stmt->bindParam(':client_id', $client_id);
        $stmt->bindParam(':arrival_date', $arrival_date);
        $stmt->bindParam(':departure_date', $departure_date);
        $stmt->bindParam(':remarks', $remarks);
        $stmt->bindParam(':reservation_id', $reservation_id);

        $stmtSelectIns = $this->conn->prepare("SELECT * FROM reservations WHERE id = $reservation_id");
        $stmtSelectIns->execute();
        $dataOld = $stmtSelectIns->fetch(PDO::FETCH_ASSOC);

        $stmtret = $stmt->execute();

        $stmtSelectIns = $this->conn->prepare("SELECT * FROM reservations WHERE id = $reservation_id");
        $stmtSelectIns->execute();
        $dataNew = $stmtSelectIns->fetch(PDO::FETCH_ASSOC);

        logToHistory('update', 'reservations', $reservation_id, $dataOld, $dataNew, 'id');
        return $stmtret;
    }
    public function updatepayment_status($reservation_id)
    {
        // Add your validation and sanitization here before executing the SQL query

        $sql = "UPDATE reservations
                SET payment_status = 1
                WHERE id = :reservation_id";

        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':reservation_id', $reservation_id);

        $stmtret = $stmt->execute();

        return $stmtret;
    }

    public function deleteReservation($reservation_id)
    {
        // Add your validation and sanitization here before executing the SQL query

        $sql = "DELETE FROM reservations WHERE id = :reservation_id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':reservation_id', $reservation_id);

        $stmtSelectIns = $this->conn->prepare("SELECT * FROM reservations WHERE id = $reservation_id");
        $stmtSelectIns->execute();
        $dataOld = $stmtSelectIns->fetch(PDO::FETCH_ASSOC);

        logToHistory('delete', 'reservations', $reservation_id, $dataOld, '', 'id');

        return $stmt->execute();
    }
}

?>