<?php
namespace classs;
use PDO; // إضافة هذا السطر
class ManagerCars
{
    private $conn;

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

    public function getcarById($car_id)
    {
        // $sql = "SELECT * FROM reservations WHERE id = :reservation_id";
        $sql = "SELECT v.*, er.*
                FROM vehicles v
                LEFT JOIN car_revenues_expenses er ON er.id_cars = v.ID
                WHERE v.ID = :car_id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':car_id', $car_id);
        $stmt->execute();

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

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

        $sql = "SELECT v.ID AS vehicle_id, v.*, d.ID AS driver_id, d.*
                FROM vehicles v
                LEFT JOIN drivers d ON d.id_vehicles = v.ID";

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

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

    public function addDriver($name, $license_number, $contact_phone, $id_vehicles)
    {
        // Add your validation and sanitization here before executing the SQL query
        $sql = "INSERT INTO drivers (driver_name, contact_phone, license_number, id_vehicles, on_or_off)
                VALUES (:driver_name, :contact_phone, :license_number, :id_vehicles, 1)";

        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':driver_name', $name);
        $stmt->bindParam(':contact_phone', $contact_phone);
        $stmt->bindParam(':license_number', $license_number);
        $stmt->bindParam(':id_vehicles', $id_vehicles);

        return $stmt->execute();
    }
    public function addCars($cost_center_id, $name, $num, $number_passengers)
    {
        // Add your validation and sanitization here before executing the SQL query
        $sql = "INSERT INTO vehicles (id_cost_center, vehicle_model, vehicle_plate_number, seating_capacity)
                VALUES (:id_cost_center, :vehicle_model, :vehicle_plate_number, :seating_capacity)";

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

        // return $stmt->execute();
        $restmt = $stmt->execute();
        $get_id = $this->conn->lastInsertId();
        logToHistory('insert', 'vehicles', $get_id, '', '', 'ID');
        return $restmt;
    }
    
    public function updateCar($car_id, $name, $num, $number_passengers)
    {
        // Add your validation and sanitization here before executing the SQL query

        $sql = "UPDATE vehicles
                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 vehicles WHERE ID = :reservation_id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':reservation_id', $reservation_id);

        return $stmt->execute();
    }

    public function addRecord($data) {
        // Prepare the SQL query for adding a new record
        $stmt = $this->conn->prepare("
            INSERT INTO car_revenues_expenses 
            (operator, id_yomya, id_cars, date_operation, payan, id_driver, id_operator, id_currency_codes, amountSold, currencyExchangeRate, amountUSD, user_id_insert) 
            VALUES (:operator, :id_yomya, :id_cars, :date_operation, :payan, :id_driver, :id_operator, :id_currency_codes, :amountSold, :currencyExchangeRate, :amountUSD, :user_id_insert)
        ");

        // Bind parameters
        $stmt->bindParam(':operator', $data['operator']);
        $stmt->bindParam(':id_yomya', $data['id_yomya']);
        $stmt->bindParam(':id_cars', $data['id_cars']);
        $stmt->bindParam(':date_operation', $data['date_operation']);
        $stmt->bindParam(':payan', $data['payan']);
        $stmt->bindParam(':id_driver', $data['id_driver']);
        $stmt->bindParam(':id_operator', $data['id_operator']);
        $stmt->bindParam(':id_currency_codes', $data['id_currency_codes']);
        $stmt->bindParam(':amountSold', $data['amountSold']);
        $stmt->bindParam(':currencyExchangeRate', $data['currencyExchangeRate']);
        $stmt->bindParam(':amountUSD', $data['amountUSD']);
        $stmt->bindParam(':user_id_insert', $data['user_id_insert']);

        // Execute the query
        $stmt->execute();
    }

    public function updateRecord($id, $data) {
        // Prepare the SQL query for updating a record
        $stmt = $this->conn->prepare("
            UPDATE car_revenues_expenses 
            SET operator = :operator, id_yomya = :id_yomya, id_cars = :id_cars, date_operation = :date_operation,
                payan = :payan, id_driver = :id_driver, id_operator = :id_operator, id_currency_codes = :id_currency_codes,
                amountSold = :amountSold, currencyExchangeRate = :currencyExchangeRate, amountUSD = :amountUSD, user_id_update = :user_id_update
            WHERE ID = :id
        ");

        // Bind parameters
        $stmt->bindParam(':id', $id);
        $stmt->bindParam(':operator', $data['operator']);
        $stmt->bindParam(':id_yomya', $data['id_yomya']);
        $stmt->bindParam(':id_cars', $data['id_cars']);
        $stmt->bindParam(':date_operation', $data['date_operation']);
        $stmt->bindParam(':payan', $data['payan']);
        $stmt->bindParam(':id_driver', $data['id_driver']);
        $stmt->bindParam(':id_operator', $data['id_operator']);
        $stmt->bindParam(':id_currency_codes', $data['id_currency_codes']);
        $stmt->bindParam(':amountSold', $data['amountSold']);
        $stmt->bindParam(':currencyExchangeRate', $data['currencyExchangeRate']);
        $stmt->bindParam(':amountUSD', $data['amountUSD']);
        $stmt->bindParam(':user_id_update', $data['user_id_update']);

        // Execute the query
        $stmt->execute();
    }

    public function deleteRecord($id) {
        // Prepare the SQL query for deleting a record
        $stmt = $this->conn->prepare("DELETE FROM car_revenues_expenses WHERE ID = :id");

        // Bind parameters
        $stmt->bindParam(':id', $id);

        // Execute the query
        $stmt->execute();
    }

    public function getSum($id_car, $operator, $start_date_sql = null, $end_date_sql= null) {
        if (empty($start_date_sql) || empty($end_date_sql)) {
            $currentMonth = date('Y-m');
            $stmt = $this->conn->prepare("SELECT sum(amountEGP) AS 'amount'
                                            from car_revenues_expenses
                                            where id_cars = :id_cars
                                            AND operator = :operator
                                            AND DATE_FORMAT(date_operation, '%Y-%m') = :current_month");
            $stmt->bindParam(':current_month', $currentMonth);
        } else {
            $stmt = $this->conn->prepare("SELECT sum(amountEGP) AS 'amount'
                                            from car_revenues_expenses
                                            where id_cars = :id_cars
                                            AND operator = :operator
                                            AND date_operation
                                            BETWEEN :start_date_sq AND :end_date_sql ");
            
            $stmt->bindParam(':start_date_sq', $start_date_sql);
            $stmt->bindParam(':end_date_sql', $end_date_sql);
        }
        $stmt->bindParam(':id_cars', $id_car);
        $stmt->bindParam(':operator', $operator);
        $stmt->execute();
        // Fetch the result as an associative array
        // return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : array();
        return ($stmt->rowCount() > 0) ? $stmt->fetch(PDO::FETCH_ASSOC) : null;
        /* $result = $stmt->fetch(PDO::FETCH_ASSOC);

        return $result; */
    }
    public function getRecordsByIdCar($id, $operator, $start_date_sql = null, $end_date_sql= null) {
        // Prepare the SQL query for fetching a record by ID
        if (empty($start_date_sql) || empty($end_date_sql)) {
            $currentMonth = date('Y-m');
            $stmt = $this->conn->prepare("SELECT c.*, d.*
                                    FROM car_revenues_expenses c
                                    LEFT JOIN drivers d ON d.ID = c.id_driver
                                    WHERE c.id_cars = :id
                                    AND c.operator = :operator
                                    AND DATE_FORMAT(date_operation, '%Y-%m') = :current_month");
            $stmt->bindParam(':current_month', $currentMonth);
        } else {
            $stmt = $this->conn->prepare("SELECT c.*, d.*
                                    FROM car_revenues_expenses c
                                    LEFT JOIN drivers d ON d.ID = c.id_driver
                                    WHERE c.id_cars = :id
                                    AND c.operator = :operator
                                    AND date_operation BETWEEN :start_date_sql AND :end_date_sql ");
            $stmt->bindParam(':start_date_sql', $start_date_sql);
            $stmt->bindParam(':end_date_sql', $end_date_sql);
        }

        // Bind parameters
        $stmt->bindParam(':id', $id);
        $stmt->bindParam(':operator', $operator);

        // Execute the query
        $stmt->execute();

        // Fetch the result as an associative array
        return ($stmt->rowCount() > 0) ? $stmt->fetchAll(PDO::FETCH_ASSOC) : array();
        /* $result = $stmt->fetch(PDO::FETCH_ASSOC);

        return $result; */
    }
    public function getRecord($id) {
        // Prepare the SQL query for fetching a record by ID
        $stmt = $this->conn->prepare("SELECT * FROM car_revenues_expenses WHERE ID = :id");

        // Bind parameters
        $stmt->bindParam(':id', $id);

        // Execute the query
        $stmt->execute();

        // Fetch the result as an associative array
        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        return $result;
    }
}
?>