<?php
namespace classs;
use PDO;
use PDOException;
class ManagerTransportation
{
    private $conn;

    public function __construct($conn)
    {
        $this->conn = $conn;
    }
    public function getAll()
    {
        $sql = "SELECT * FROM companies_transportation";
        $stmt = $this->conn->query($sql);
        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : array();
    }
    
    public function getById($id)
    {
        // $sql = "SELECT * FROM reservations WHERE id = :reservation_id";
        $sql = "SELECT *
                FROM hotels 
                WHERE ID = :id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->execute();

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

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

        $sql = "SELECT h.*, d.*
                FROM hotels h
                LEFT JOIN drivers d ON d.id_hotels = h.ID";

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

        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : array();
    }
    public function insertData($client_or_supplier, $agent_name, $phone, $email, $address_companie, $id_level_acc) {
    
        try {
            // اتصل بالدالة لإدخال البيانات في جدول agents
            $stmt = $this->conn->prepare("INSERT INTO companies_transportation (client_or_supplier, id_level_acc, name_companie, phone, email, address_companie, user_id_insert) VALUES (:client_or_supplier, :id_level_acc, :name_companie, :phone, :email, :address_companie, :user_id_insert)");
    
            // قم بربط القيم باستخدام bindParam
            $stmt->bindParam(':client_or_supplier', $client_or_supplier);
            $stmt->bindParam(':id_level_acc', $id_level_acc);
            $stmt->bindParam(':name_companie', $agent_name);
            $stmt->bindParam(':phone', $phone);
            $stmt->bindParam(':email', $email);
            $stmt->bindParam(':address_companie', $address_companie);
            $stmt->bindParam(':user_id_insert', $_SESSION['admin']);
    
            // قم بتنفيذ الاستعلام
            // $stmt->execute();
            $restmt = $stmt->execute();
            $get_id = $this->conn->lastInsertId();
            logToHistory('insert', 'companies_transportation', $get_id, '', '', 'ID');
            return $restmt;
    
            // echo "تم إضافة الوكيل بنجاح!";
        } catch (PDOException $e) {
            echo "فشل في إضافة الوكيل: " . $e->getMessage();
        }
    }

    public function insert($table_name, $col, $name, $col2 = NULL, $name2 = NULL) {
    
        try {
            if ($col2 === NULL) {
                $stmt = $this->conn->prepare("INSERT INTO $table_name ($col) VALUES (:col)");
                $stmt->bindParam(':col', $name);
            } else {
                $stmt = $this->conn->prepare("INSERT INTO $table_name ($col, $col2) VALUES (:col, :col2)");
                $stmt->bindParam(':col', $name);
                $stmt->bindParam(':col2', $name2);
            }
    
            // قم بتنفيذ الاستعلام
            // $stmt->execute();
            $restmt = $stmt->execute();
            $get_id = $this->conn->lastInsertId();
            logToHistory('insert', $table_name, $get_id, '', '', 'ID');
            return $restmt;
    
            // echo "تم إضافة البيان بنجاح!";
        } catch (PDOException $e) {
            echo "فشل في إضافة البيان: " . $e->getMessage();
        }
    }
    
    public function addRunCommand(
        $date_run, 
        $id_driver, 
        $id_companies_transportation, 
        $id_vehicles, 
        $no_car, 
        $location_now, 
        $vehicle_capacity, 
        $operation_statement, 
        $client_or_file, 
        $id_operator, 
        $id_employee_M, 
        $number_individuals, 
        $NO_run_command, 
        $hour_doing, 
        $hour_return, 
        $counter_doing, 
        $counter_return, 
        $distance_travelled, 
        $id_employee_mang, 
        $nots_run, 
        $sessionUser
    )
    {
        // Use generateFileNumber function
        // $file_number = $this->generateFileNumber($departure_date);

        // Add your validation and sanitization here before executing the SQL query
        try {
            $sql = "INSERT INTO run_command (date_run, id_driver, id_companies_transportation, id_vehicles, no_car, location_now, vehicle_capacity, operation_statement, client_or_file, id_operator, id_employee_M, number_individuals, NO_run_command, hour_doing, hour_return, counter_doing, counter_return, distance_travelled, id_employee_mang, nots_run, user_id_insert)
                            VALUES (:date_run, :id_driver, :id_companies_transportation, :id_vehicles, :no_car, :location_now, :vehicle_capacity, :operation_statement, :client_or_file, :id_operator, :id_employee_M, :number_individuals, :NO_run_command, :hour_doing, :hour_return, :counter_doing, :counter_return, :distance_travelled, :id_employee_mang, :nots_run, :user_id_insert)";

            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':date_run', $date_run);
            $stmt->bindParam(':id_driver', $id_driver);
            $stmt->bindParam(':id_companies_transportation', $id_companies_transportation);
            $stmt->bindParam(':id_vehicles', $id_vehicles);
            $stmt->bindParam(':no_car', $no_car);
            $stmt->bindParam(':location_now', $location_now);
            $stmt->bindParam(':vehicle_capacity', $vehicle_capacity);
            $stmt->bindParam(':operation_statement', $operation_statement);
            $stmt->bindParam(':client_or_file', $client_or_file);
            $stmt->bindParam(':id_operator', $id_operator);
            $stmt->bindParam(':id_employee_M', $id_employee_M);
            $stmt->bindParam(':number_individuals', $number_individuals);
            $stmt->bindParam(':NO_run_command', $NO_run_command);
            $stmt->bindParam(':hour_doing', $hour_doing);
            $stmt->bindParam(':hour_return', $hour_return);
            $stmt->bindParam(':counter_doing', $counter_doing);
            $stmt->bindParam(':counter_return', $counter_return);
            $stmt->bindParam(':distance_travelled', $distance_travelled);
            $stmt->bindParam(':id_employee_mang', $id_employee_mang);
            $stmt->bindParam(':nots_run', $nots_run);
            $stmt->bindParam(':user_id_insert', $sessionUser);
            $restmt = $stmt->execute();

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

            logToHistory('insert', 'run_command', $reservations_id, '', '', 'id');

            
            return $reservations_id;
        } catch (PDOException $e) {
            error_log("حدث خطأ فى السيرفر أثناء إضافة الملف: " . $e->getMessage(), 0);
            return false;
        }
    }
    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 generateNoRunCommand()
    {
        // echo "Formatted Departure Date: $formatted_departure_date<br>";

        $sql_search = "SELECT MAX(NO_run_command) AS max_NO_run
                    FROM run_command ";

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

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

        return $new_NO_run;
    }
    public function updateCar($car_id, $name, $num, $number_passengers)
    {
        // Add your validation and sanitization here before executing the SQL query

        $sql = "UPDATE hotels
                SET vehicle_model = :vehicle_model, vehicle_plate_number = :vehicle_plate_number, seating_capacity = :seating_capacity
                WHERE ID = :car_id";

        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':vehicle_model', $name);
        $stmt->bindParam(':vehicle_plate_number', $num);
        $stmt->bindParam(':seating_capacity', $number_passengers);
        $stmt->bindParam(':car_id', $car_id);

        return $stmt->execute();
    }

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

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

        return $stmt->execute();
    }

}

?>