<?php

namespace App\Http\Controllers\API;

use App\Http\Controllers\Controller;
use App\Models\Adquisicion;
use App\Models\Docs;
use App\Models\VehiculoGeneral;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Http;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Storage;

class VehiculoController extends Controller
{
    var $timestampUnique = 0;
    var $meses = [];
    public function __construct()
    {
        //Los registros tengan la misma fecha y hora exacta
        $this->timestampUnique = Carbon::now('GMT-6');
        $this->meses    = ['Enero', 'Febrero', 'Marzo', 'Abril', 'Mayo', 'Junio', 'Julio', 'Agosto', 'Septiembre', 'Octubre', 'Noviembre', 'Diciembre'];
    }

    public function index()
    {
        $vehiculos = VehiculoGeneral::select('tms_am_vehicles.pk_vehicle', 'tms_am_vehicles.engine_number', 'tms_am_vehicles.serial_number'
            , 'tms_am_vehicles.plates', 'tms_am_vehicles.federal_plates', 'tms_am_colors.name AS color_name', 'tms_am_manufacturers.name AS manufacturer_name', 
            'tms_am_fuels.name AS fuel_name', 'tms_am_vehicle_models.name AS vehicle_model_name', 'tms_am_vehicle_models.year AS vehicle_model_year',
            'tms_am_fuels.name AS fuel_name', 'tms_am_entities.codename AS entity_name', 'tms_am_vehicle_status.name AS operative_name', 'tms_am_vehicle_status_groups.name AS status_name')
            ->join('tms_am_colors', 'tms_am_vehicles.fk_color', 'tms_am_colors.pk_color')
            ->join('tms_am_vehicle_catalogs', 'tms_am_vehicles.fk_vehicle_catalog', 'tms_am_vehicle_catalogs.pk_vehicle_catalog')
            ->join('tms_am_fuels', 'tms_am_vehicles.fk_fuel', 'tms_am_fuels.pk_fuel')
            ->join('tms_am_vehicle_models', 'tms_am_vehicle_catalogs.fk_vehicle_model', 'tms_am_vehicle_models.pk_vehicle_model')
            ->join('tms_am_manufacturers', 'tms_am_vehicle_models.fk_manufacturer', 'tms_am_manufacturers.pk_manufacturer')
            ->join('tms_am_entities', 'tms_am_vehicles.fk_entity_company', 'tms_am_entities.pk_entity')
            ->join('tms_am_vehicle_status', 'tms_am_vehicles.fk_vehicle_status', 'tms_am_vehicle_status.pk_vehicle_status')
            ->join('tms_am_vehicle_status_groups', 'tms_am_vehicles.fk_vehicle_status_group', 'tms_am_vehicle_status_groups.pk_vehicle_status_group')
            ->orderBy('pk_vehicle', 'ASC')
            ->get();

/*             $estacionamientos = VehiculoGeneral::select('vehicles.pk_vehicle', 'vehicles.engine_number', 'vehicles.serial_number'
            , 'vehicles.plates', 'vehicles.federal_plates', 'vehicle_status.name AS status_name', 'entities.codename AS code_entity')
            ->join('vehicles_types', 'vehicles_catalog.fk_vehicle_type', 'vehicles_types.pk_vehicle_type')
            ->join('manufacturers', 'vehicles_catalog.fk_manufacturer', 'manufacturers.pk_manufacturer')
            ->join('fuels', 'vehicles_catalog.fk_fuel', 'fuels.pk_fuel')
            ->join('vehicles_models', 'vehicles_catalog.fk_vehicle_model', 'vehicles_models.pk_vehicle_model')
            ->paginate(100); */

            return response()->json([
                "data" => $vehiculos
            ]); 
    }

    public function store(Request $request)
    {
        $request->validate([
            'file_tarjeta_circulacion' => 'required|mimes:pdf',
            'file_factura' => 'required|mimes:pdf',
            'monto_adquision' => 'required',
            'fecha_adquision' => 'required|before_or_equal:'.$this->timestampUnique->format('d-m-Y'),
            'observaciones_adquision' => 'required'
        ]);

        $ide_v = DB::transaction(function() use($request) {

            //ALTA EN TABLA VEHICULO
            $ultimo_vehiculo = VehiculoGeneral::orderBy('pk_vehicle', 'desc')->first();
            $vehiculo = new VehiculoGeneral();
            $vehiculo->pk_vehicle = $ultimo_vehiculo->pk_vehicle+1;
            $vehiculo->eco = $request->eco_vehiculo;
            $vehiculo->fk_vehicle_catalog = $request->id_catalogo;
            $vehiculo->fk_color = $request->color;
            if($request->placas == null || $request->placas == ""){
                $vehiculo->plates = "";
            }else{
                $vehiculo->plates = $request->placas;
            }
            if($request->placas_federales == null || $request->placas_federales == ""){
                $vehiculo->federal_plates = "";
            }else{
                $vehiculo->federal_plates = $request->placas_federales;
            }
            $vehiculo->fuel_consumption = $request->consumo;
            $vehiculo->engine_number = $request->no_motor;
            $vehiculo->serial_number = $request->no_serie;
            $vehiculo->fk_parkinglot = $request->encierro;
            $vehiculo->fk_vehicle_status = $request->status_vehiculo;
            $vehiculo->fk_entity_company = $request->corporativo;
            $vehiculo->fk_federal_entity = $request->entidad_federativa;
            $vehiculo->passengers = $request->no_asientos;
            $vehiculo->doors = $request->puertas;
            $vehiculo->fk_seat_type = $request->tipo_asiento;
            $vehiculo->fk_suspension_type = $request->tipo_suspension;
            $vehiculo->description = $request->descripcion;
            $vehiculo->authorized_fuel_consumption = $request->capacidad_combustible;
            $vehiculo->fk_vehicle_usage = $request->uso_vehiculo;
            $vehiculo->fk_vehicle_status_group = $request->status_vehiculo_grupal;
            $vehiculo->fk_vehicle_origin = $request->origen_vehiculo;
            $vehiculo->fk_vehicle_fleet = $request->flota_vehiculo;
            $vehiculo->fk_fuel = $request->gasolina;
            $vehiculo->save();
            //ALTA EN TABLA VEHICULO

            //ALTA EN TABLA DOCS TARJETAS DE CIRCULACION
            $docs = new Docs();
            $docs->fk_vehicle = $vehiculo->pk_vehicle;
            $docs->document_module = 'TARJETA DE CIRULACIÓN';
            if($request->file('file_tarjeta_circulacion')) {
                $doc_file = $request->file('file_tarjeta_circulacion');
                $docs->name_file = $doc_file->getClientOriginalName();
                $docs->created_at = Carbon::now();
                $filePath = env('FOLDER_DIRECTIVE_AWS').'/Activos/vehiculos/'.$vehiculo->pk_vehicle.'/TarjetasCirculacion/'.date("Y").'/'.$this->meses[date('m') - 1];
                $url_s3 = Storage::disk('s3')->put($filePath, $doc_file, 'public');
                Log::info("La url del vehiculo ".$vehiculo->pk_vehicle." es: ".$url_s3);
                $docs->url_file = $url_s3;
                
                //ALTA EN TABLA HISTORICO TARJETAS CIRCULACION
                DB::table('historico_tarjetas_circulacion')->insert([
                    'fk_vehicle' => $vehiculo->pk_vehicle,
                    'fk_user' => $request->id_usuario,
                    'url_file' => $url_s3,
                    'fecha_transaccion' => $this->timestampUnique,
                ]);
                //ALTA EN TABLA HISTORICO TARJETAS CIRCULACION
            }
            $docs->save();
            //ALTA EN TABLA DOCS TARJETA DE CIRULACION

            //ALTA EN TABLA DOCS FACTURA
            $docs = new Docs();
            $docs->fk_vehicle = $vehiculo->pk_vehicle;
            $docs->document_module = 'FACTURA';
            if($request->file('file_factura')) {
                $doc_file = $request->file('file_factura');
                $docs->name_file = $doc_file->getClientOriginalName();
                $docs->created_at = Carbon::now();
                $filePath = env('FOLDER_DIRECTIVE_AWS').'/Activos/vehiculos/'.$vehiculo->pk_vehicle.'/Factura/'.date("Y").'/'.$this->meses[date('m') - 1];
                $url_s3 = Storage::disk('s3')->put($filePath, $doc_file, 'public');
                Log::info("La url del vehiculo ".$vehiculo->pk_vehicle." es: ".$url_s3);
                $docs->url_file = $url_s3;
            }
            $docs->save();
            //ALTA EN TABLA DOCS

            //ALTA EN TABLA ADQUISIONES
            $adquision = new Adquisicion();
            $adquision->fk_vehicle = $vehiculo->pk_vehicle;
            $adquision->acquisition_mount = $request->monto_adquision;
            $adquision->acquisition_date = $request->fecha_adquision;
            $adquision->acquisition_observations = $request->observaciones_adquision;
            $adquision->save();
            //ALTA EN TABLA ADQUISIONES

            //ALTA EN TABLA HISTORICO ADQUISION VEHICULOS
            DB::table('historico_adquisicion_vehiculos')->insert([
                'fk_acq_vehicle' => $adquision->pk_acq_vehicle,
                'fk_vehicle' => $vehiculo->pk_vehicle,
                'fk_user' => $request->id_usuario,
                'monto_adquisicion' => $request->monto_adquision,
                'fecha_adquisicion' => $request->fecha_adquision,
                'observaciones_adquisicion' => $request->observaciones_adquision,
                'fecha_transaccion' => $this->timestampUnique,
            ]);
            //ALTA EN TABLA HISTORICO ADQUISION VEHICULOS

            return $vehiculo->pk_vehicle;
        });

        if($ide_v == null){
            return response("error",500);
        }else{
            return response()->json([
                'ide_returned' => $ide_v
            ]); 
        }
    }

    public function show($id)
    {
        //Datos rapidos del vehiculo
        $info = VehiculoGeneral::select('tms_am_vehicles.pk_vehicle', 'tms_am_vehicles.engine_number', 'tms_am_vehicles.serial_number',
        'tms_am_vehicles.plates', 'tms_am_vehicles.federal_plates', 'tms_am_vehicles.passengers',
        'tms_am_vehicles.doors', 'tms_am_manufacturers.name AS manufacturer_name', 
        'tms_am_fuels.name AS fuel_name', 'tms_am_vehicle_models.name AS vehicle_model_name',
        'tms_am_acquisition_vehicles.acquisition_date AS date_aqc', 'tms_am_acquisition_vehicles.acquisition_date AS date_aqc',
        'tms_am_vehicle_models.year AS vehicle_model_year',  'tms_am_vehicle_status.name AS operative_name', 
        'tms_am_vehicle_status_groups.name AS status_name')
        ->join('tms_am_vehicle_catalogs', 'tms_am_vehicles.fk_vehicle_catalog', 'tms_am_vehicle_catalogs.pk_vehicle_catalog')
        ->join('tms_am_fuels', 'tms_am_vehicles.fk_fuel', 'tms_am_fuels.pk_fuel')
        ->join('tms_am_vehicle_models', 'tms_am_vehicle_catalogs.fk_vehicle_model', 'tms_am_vehicle_models.pk_vehicle_model')
        ->join('tms_am_manufacturers', 'tms_am_vehicle_models.fk_manufacturer', 'tms_am_manufacturers.pk_manufacturer')
        ->leftJoin('tms_am_acquisition_vehicles', 'tms_am_vehicles.pk_vehicle', 'tms_am_acquisition_vehicles.fk_vehicle')
        ->join('tms_am_vehicle_status', 'tms_am_vehicles.fk_vehicle_status', 'tms_am_vehicle_status.pk_vehicle_status')
        ->join('tms_am_vehicle_status_groups', 'tms_am_vehicles.fk_vehicle_status_group', 'tms_am_vehicle_status_groups.pk_vehicle_status_group')
        ->with(['images'=>function($query){
            $query->where('status', true);
        }])
        ->with(['documents'=>function($query){
            $query->where('status', true);
        }])
        ->where('tms_am_vehicles.pk_vehicle', $id)
        ->get();

        $vehiculo = VehiculoGeneral::
        with(['documents'=>function($query){
            $query->where('status', true);
        }])
        ->with(['insurance_info'=>function($query){
            $query->where('status', true);
        }])
        ->with(['acquisition_info'=>function($query){
            $query->where('status', true);
        }])
        ->with(['images'=>function($query){
            $query->where('status', true);
        }])
        ->where('tms_am_vehicles.pk_vehicle', $id)
        ->get();

        $complements = DB::table('tms_am_complement_vehicle')->where('fk_vehicle', $id)->get();
        return response()->json([
            'vehicle_details' => $info,
            'vehicle' => $vehiculo,
            'complements' => $complements
        ]);
    }

    public function update(Request $request, $id)
    {
        $ide_v = DB::transaction(function() use($request, $id) {
        $updateVehiculo = VehiculoGeneral::where('pk_vehicle', $id)->update([
            'eco' => $request->edit_eco_vehiculo,
            'fk_vehicle_catalog' => $request->edit_id_catalogo,
            'fk_color' => $request->color,
            'plates' => $request->edit_placas,
            'federal_plates' => $request->edit_placas_federales,
            'fuel_consumption' => $request->edit_consumo,
            'engine_number' => $request->edit_no_motor,
            'serial_number' => $request->edit_no_serie,
            'fk_parkinglot' => $request->encierro,
            'fk_vehicle_status' => $request->status_vehiculo,
            'fk_entity_company' => $request->corporativo,
            'fk_federal_entity' => $request->entidad_federativa,
            'passengers' => $request->edit_no_asientos,
            'doors' =>  $request->edit_puertas,
            'fk_seat_type' => $request->tipo_asiento,
            'fk_suspension_type' => $request->tipo_suspension,
            'description' => $request->edit_descripcion,
            'authorized_fuel_consumption' => $request->edit_capacidad_combustible,
            'fk_vehicle_usage' => $request->uso_vehiculo,
            'fk_fuel' => $request->gasolina,
        ]);

        $ultimo_historico = DB::table('historico_update_vehiculos')->orderBy('pk_h_update_vehiculos', 'desc')->first();
        DB::table('historico_update_vehiculos')->insert([
            'pk_h_update_vehiculos' => $ultimo_historico->pk_h_update_vehiculos+1,
            'fk_vehicle' =>$id,
            'eco' => $request->edit_eco_vehiculo,
            'fk_vehicle_catalog' => $request->edit_id_catalogo,
            'fk_color' => $request->color,
            'plates' => $request->edit_placas,
            'federal_plates' => $request->edit_placas_federales,
            'fuel_consumption' => $request->edit_consumo,
            'engine_number' => $request->edit_no_motor,
            'serial_number' => $request->edit_no_serie,
            'fk_parkinglot' => $request->encierro,
            'fk_vehicle_status' => $request->status_vehiculo,
            'fk_entity_company' => $request->corporativo,
            'fk_federal_entity' => $request->entidad_federativa,
            'passengers' => $request->edit_no_asientos,
            'doors' =>  $request->edit_no_asientos,
            'fk_seat_type' => $request->tipo_asiento,
            'fk_suspension_type' => $request->tipo_suspension,
            'description' => $request->edit_descripcion,
            'authorized_fuel_consumption' => $request->edit_capacidad_combustible,
            'fk_vehicle_usage' => $request->uso_vehiculo,
            'fk_fuel' => $request->gasolina,
            'fk_user' => $request->id_usuario,
            'date_changed' => str_replace("-","",Carbon::now()->format('Y-m-d')),
            'time_changed' => str_replace(":","",Carbon::now()->format('H:i:s'))
        ]);
        if($request->file('fileTarjetaCir')) {
            $doc_file = $request->file('fileTarjetaCir');
            $filePath = env('FOLDER_DIRECTIVE_AWS').'/Activos/vehiculos/'.$id.'/TarjetasCirculacion/'.date("Y").'/'.$this->meses[date('m') - 1];
            $url_s3 = Storage::disk('s3')->put($filePath, $doc_file, 'public');
            
            //ALTA EN TABLA HISTORICO TARJETAS CIRCULACION
            DB::table('historico_tarjetas_circulacion')->insert([
                'fk_vehicle' => $id,
                'fk_user' => $request->id_usuario,
                'url_file' => $url_s3,
                'fecha_transaccion' => $this->timestampUnique,
            ]);
            //ALTA EN TABLA HISTORICO TARJETAS CIRCULACION
        }
        });

        return response("success", 200);
    }

    public function destroy($id)
    {
        VehiculoGeneral::findOrFail($id)->delete();
        return response('success', 200);
    }


    public function showVehiclesByEntity($entity_company){
        $vehiculo = VehiculoGeneral::select('tms_am_vehicles.pk_vehicle', 'tms_am_vehicles.status', 'tms_am_vehicles.eco',
        'tms_am_vehicles.fk_vehicle_catalog', 'tms_am_vehicles.plates', 'tms_am_vehicles.federal_plates',
        'tms_am_vehicles.fuel_consumption', 'tms_am_vehicles.engine_number', 'tms_am_vehicles.serial_number',
        'tms_am_vehicles.fk_entity_company', 'tms_am_vehicles.fk_federal_entity', 'tms_am_vehicles.passengers', 
        'tms_am_vehicles.doors', 'tms_am_vehicles.description', 'tms_am_vehicles.authorized_fuel_consumption',
        'tms_am_manufacturers.name AS manufacturer_name', 'tms_am_fuels.name AS fuel_name', 
        'tms_am_vehicle_models.name AS vehicle_model_name', 'tms_am_colors.name AS color_name',
        'tms_am_vehicle_status.name AS status_vehicle', 'tms_am_vehicle_status_groups.name AS status_group_vehicle',
        'tms_am_seat_types.description AS seat_type', 'tms_am_suspension_types.description AS suspension_type', 
        'tms_am_seat_types.description AS seat_type', 'tms_am_vehicle_usages.name AS usage_name',
        'tms_am_vehicle_fleets.name AS fleet_name', 'tms_am_vehicle_usages.name AS usage_name', 'tms_am_entities.name AS entity_company_name'
        )
        ->join('tms_am_colors', 'tms_am_vehicles.fk_color', 'tms_am_colors.pk_color')
        ->join('tms_am_vehicle_status', 'tms_am_vehicles.fk_vehicle_status', 'tms_am_vehicle_status.pk_vehicle_status')
        ->join('tms_am_vehicle_status_groups', 'tms_am_vehicles.fk_vehicle_status_group', 'tms_am_vehicle_status_groups.pk_vehicle_status_group')
        ->join('tms_am_seat_types', 'tms_am_vehicles.fk_seat_type', 'tms_am_seat_types.pk_seat_type')
        ->join('tms_am_suspension_types', 'tms_am_vehicles.fk_suspension_type', 'tms_am_suspension_types.pk_suspension_type')
        ->join('tms_am_vehicle_fleets', 'tms_am_vehicles.fk_vehicle_fleet', 'tms_am_vehicle_fleets.pk_vehicle_fleet')
        ->join('tms_am_vehicle_usages', 'tms_am_vehicles.fk_vehicle_usage', 'tms_am_vehicle_usages.pk_vehicle_usage')
        ->join('tms_am_vehicle_catalogs', 'tms_am_vehicles.fk_vehicle_catalog', 'tms_am_vehicle_catalogs.pk_vehicle_catalog')
        ->join('tms_am_fuels', 'tms_am_vehicles.fk_fuel', 'tms_am_fuels.pk_fuel')
        ->join('tms_am_entities', 'tms_am_vehicles.fk_entity_company', 'tms_am_entities.pk_entity')
        ->join('tms_am_vehicle_models', 'tms_am_vehicle_catalogs.fk_vehicle_model', 'tms_am_vehicle_models.pk_vehicle_model')
        ->join('tms_am_manufacturers', 'tms_am_vehicle_models.fk_manufacturer', 'tms_am_manufacturers.pk_manufacturer')
        ->with(['documents'=>function($query){
            $query->where('status', true);
          }])
        ->with(['images'=>function($query){
            $query->where('status', true);
          }])
        ->where('tms_am_vehicles.fk_entity_company', $entity_company)
        ->get();

        if($vehiculo->isEmpty()){
            return response()->json([
                'error' => "Esta unidad de negocio no tiene vehiculos"
            ], 404);
        }else{
            return response()->json([
                'data' => $vehiculo
            ]);
        }
    }

    public function showVehiclesByEntityAndManufacturer($entity_company, $manufacturer){
        $vehiculo = VehiculoGeneral::select('tms_am_vehicles.pk_vehicle', 'tms_am_vehicles.status', 'tms_am_vehicles.eco',
        'tms_am_vehicles.fk_vehicle_catalog', 'tms_am_vehicles.plates', 'tms_am_vehicles.federal_plates',
        'tms_am_vehicles.fuel_consumption', 'tms_am_vehicles.engine_number', 'tms_am_vehicles.serial_number',
        'tms_am_vehicles.fk_entity_company', 'tms_am_vehicles.fk_federal_entity', 'tms_am_vehicles.passengers', 
        'tms_am_vehicles.doors', 'tms_am_vehicles.description', 'tms_am_vehicles.authorized_fuel_consumption',
        'tms_am_manufacturers.name AS manufacturer_name', 'tms_am_fuels.name AS fuel_name', 
        'tms_am_vehicle_models.name AS vehicle_model_name', 'tms_am_colors.name AS color_name',
        'tms_am_vehicle_status.name AS status_vehicle', 'tms_am_vehicle_status_groups.name AS status_group_vehicle',
        'tms_am_seat_types.description AS seat_type', 'tms_am_suspension_types.description AS suspension_type', 
        'tms_am_seat_types.description AS seat_type', 'tms_am_vehicle_usages.name AS usage_name',
        'tms_am_vehicle_fleets.name AS fleet_name', 'tms_am_vehicle_usages.name AS usage_name', 'tms_am_entities.name AS entity_company_name'
        )
        ->join('tms_am_colors', 'tms_am_vehicles.fk_color', 'tms_am_colors.pk_color')
        ->join('tms_am_vehicle_status', 'tms_am_vehicles.fk_vehicle_status', 'tms_am_vehicle_status.pk_vehicle_status')
        ->join('tms_am_vehicle_status_groups', 'tms_am_vehicles.fk_vehicle_status_group', 'tms_am_vehicle_status_groups.pk_vehicle_status_group')
        ->join('tms_am_seat_types', 'tms_am_vehicles.fk_seat_type', 'tms_am_seat_types.pk_seat_type')
        ->join('tms_am_suspension_types', 'tms_am_vehicles.fk_suspension_type', 'tms_am_suspension_types.pk_suspension_type')
        ->join('tms_am_vehicle_fleets', 'tms_am_vehicles.fk_vehicle_fleet', 'tms_am_vehicle_fleets.pk_vehicle_fleet')
        ->join('tms_am_vehicle_usages', 'tms_am_vehicles.fk_vehicle_usage', 'tms_am_vehicle_usages.pk_vehicle_usage')
        ->join('tms_am_vehicle_catalogs', 'tms_am_vehicles.fk_vehicle_catalog', 'tms_am_vehicle_catalogs.pk_vehicle_catalog')
        ->join('tms_am_fuels', 'tms_am_vehicles.fk_fuel', 'tms_am_fuels.pk_fuel')
        ->join('tms_am_entities', 'tms_am_vehicles.fk_entity_company', 'tms_am_entities.pk_entity')
        ->join('tms_am_vehicle_models', 'tms_am_vehicle_catalogs.fk_vehicle_model', 'tms_am_vehicle_models.pk_vehicle_model')
        ->join('tms_am_manufacturers', 'tms_am_vehicle_models.fk_manufacturer', 'tms_am_manufacturers.pk_manufacturer')
        ->with(['documents'=>function($query){
            $query->where('status', true);
          }])
        ->with(['images'=>function($query){
            $query->where('status', true);
          }])
        ->where('tms_am_vehicles.fk_entity_company', $entity_company)
        ->where('tms_am_vehicle_models.fk_manufacturer', $manufacturer)
        ->get();

        if($vehiculo->isEmpty()){
            return response()->json([
                'error' => "Sin vehiculos asociados a la marca"
            ], 404);
        }else{
            return response()->json([
                'data' => $vehiculo
            ]);
        }
    }

    public function showVehiclesByEntityAndId($entity_company, $id_vehicle){
        $vehiculo = VehiculoGeneral::select('tms_am_vehicles.pk_vehicle', 'tms_am_vehicles.status', 'tms_am_vehicles.eco',
        'tms_am_vehicles.fk_vehicle_catalog', 'tms_am_vehicles.plates', 'tms_am_vehicles.federal_plates',
        'tms_am_vehicles.fuel_consumption', 'tms_am_vehicles.engine_number', 'tms_am_vehicles.serial_number',
        'tms_am_vehicles.fk_entity_company', 'tms_am_vehicles.fk_federal_entity', 'tms_am_vehicles.passengers', 
        'tms_am_vehicles.doors', 'tms_am_vehicles.description', 'tms_am_vehicles.authorized_fuel_consumption',
        'tms_am_manufacturers.name AS manufacturer_name', 'tms_am_fuels.name AS fuel_name', 
        'tms_am_vehicle_models.name AS vehicle_model_name', 'tms_am_colors.name AS color_name',
        'tms_am_vehicle_status.name AS status_vehicle', 'tms_am_vehicle_status_groups.name AS status_group_vehicle',
        'tms_am_seat_types.description AS seat_type', 'tms_am_suspension_types.description AS suspension_type', 
        'tms_am_seat_types.description AS seat_type', 'tms_am_vehicle_usages.name AS usage_name',
        'tms_am_vehicle_fleets.name AS fleet_name', 'tms_am_vehicle_usages.name AS usage_name', 'tms_am_entities.name AS entity_company_name'
        )
        ->join('tms_am_colors', 'tms_am_vehicles.fk_color', 'tms_am_colors.pk_color')
        ->join('tms_am_vehicle_status', 'tms_am_vehicles.fk_vehicle_status', 'tms_am_vehicle_status.pk_vehicle_status')
        ->join('tms_am_vehicle_status_groups', 'tms_am_vehicles.fk_vehicle_status_group', 'tms_am_vehicle_status_groups.pk_vehicle_status_group')
        ->join('tms_am_seat_types', 'tms_am_vehicles.fk_seat_type', 'tms_am_seat_types.pk_seat_type')
        ->join('tms_am_suspension_types', 'tms_am_vehicles.fk_suspension_type', 'tms_am_suspension_types.pk_suspension_type')
        ->join('tms_am_vehicle_fleets', 'tms_am_vehicles.fk_vehicle_fleet', 'tms_am_vehicle_fleets.pk_vehicle_fleet')
        ->join('tms_am_vehicle_usages', 'tms_am_vehicles.fk_vehicle_usage', 'tms_am_vehicle_usages.pk_vehicle_usage')
        ->join('tms_am_vehicle_catalogs', 'tms_am_vehicles.fk_vehicle_catalog', 'tms_am_vehicle_catalogs.pk_vehicle_catalog')
        ->join('tms_am_fuels', 'tms_am_vehicles.fk_fuel', 'tms_am_fuels.pk_fuel')
        ->join('tms_am_entities', 'tms_am_vehicles.fk_entity_company', 'tms_am_entities.pk_entity')
        ->join('tms_am_vehicle_models', 'tms_am_vehicle_catalogs.fk_vehicle_model', 'tms_am_vehicle_models.pk_vehicle_model')
        ->join('tms_am_manufacturers', 'tms_am_vehicle_models.fk_manufacturer', 'tms_am_manufacturers.pk_manufacturer')
        ->with(['documents'=>function($query){
            $query->where('status', true);
          }])
        ->with(['images'=>function($query){
            $query->where('status', true);
          }])
        ->where('tms_am_vehicles.fk_entity_company', $entity_company)
        ->where('tms_am_vehicles.pk_vehicle', $id_vehicle)
        ->get();

        if($vehiculo->isEmpty()){
            return response()->json([
                'error' => "Vehiculo no encontrado"
            ], 404);
        }else{
            return response()->json([
                'data' => $vehiculo
            ]);
        }
    }

    public function showVehiclesByEntityAndDescription($entity_company, $description){
        $vehiculo = VehiculoGeneral::select('tms_am_vehicles.pk_vehicle', 'tms_am_vehicles.status', 'tms_am_vehicles.eco',
        'tms_am_vehicles.fk_vehicle_catalog', 'tms_am_vehicles.plates', 'tms_am_vehicles.federal_plates',
        'tms_am_vehicles.fuel_consumption', 'tms_am_vehicles.engine_number', 'tms_am_vehicles.serial_number',
        'tms_am_vehicles.fk_entity_company', 'tms_am_vehicles.fk_federal_entity', 'tms_am_vehicles.passengers', 
        'tms_am_vehicles.doors', 'tms_am_vehicles.description', 'tms_am_vehicles.authorized_fuel_consumption',
        'tms_am_manufacturers.name AS manufacturer_name', 'tms_am_fuels.name AS fuel_name', 
        'tms_am_vehicle_models.name AS vehicle_model_name', 'tms_am_colors.name AS color_name',
        'tms_am_vehicle_status.name AS status_vehicle', 'tms_am_vehicle_status_groups.name AS status_group_vehicle',
        'tms_am_seat_types.description AS seat_type', 'tms_am_suspension_types.description AS suspension_type', 
        'tms_am_seat_types.description AS seat_type', 'tms_am_vehicle_usages.name AS usage_name',
        'tms_am_vehicle_fleets.name AS fleet_name', 'tms_am_vehicle_usages.name AS usage_name', 'tms_am_entities.name AS entity_company_name'
        )
        ->join('tms_am_colors', 'tms_am_vehicles.fk_color', 'tms_am_colors.pk_color')
        ->join('tms_am_vehicle_status', 'tms_am_vehicles.fk_vehicle_status', 'tms_am_vehicle_status.pk_vehicle_status')
        ->join('tms_am_vehicle_status_groups', 'tms_am_vehicles.fk_vehicle_status_group', 'tms_am_vehicle_status_groups.pk_vehicle_status_group')
        ->join('tms_am_seat_types', 'tms_am_vehicles.fk_seat_type', 'tms_am_seat_types.pk_seat_type')
        ->join('tms_am_suspension_types', 'tms_am_vehicles.fk_suspension_type', 'tms_am_suspension_types.pk_suspension_type')
        ->join('tms_am_vehicle_fleets', 'tms_am_vehicles.fk_vehicle_fleet', 'tms_am_vehicle_fleets.pk_vehicle_fleet')
        ->join('tms_am_vehicle_usages', 'tms_am_vehicles.fk_vehicle_usage', 'tms_am_vehicle_usages.pk_vehicle_usage')
        ->join('tms_am_vehicle_catalogs', 'tms_am_vehicles.fk_vehicle_catalog', 'tms_am_vehicle_catalogs.pk_vehicle_catalog')
        ->join('tms_am_fuels', 'tms_am_vehicles.fk_fuel', 'tms_am_fuels.pk_fuel')
        ->join('tms_am_entities', 'tms_am_vehicles.fk_entity_company', 'tms_am_entities.pk_entity')
        ->join('tms_am_vehicle_models', 'tms_am_vehicle_catalogs.fk_vehicle_model', 'tms_am_vehicle_models.pk_vehicle_model')
        ->join('tms_am_manufacturers', 'tms_am_vehicle_models.fk_manufacturer', 'tms_am_manufacturers.pk_manufacturer')
        ->with(['documents'=>function($query){
            $query->where('status', true);
          }])
        ->with(['images'=>function($query){
            $query->where('status', true);
          }])
        //->where('tms_am_vehicles.fk_entity_company', $entity_company)
        ->where('tms_am_vehicles.description', 'LIKE', "%".$description."%")
        ->get();

        if($vehiculo->isEmpty()){
            return response()->json([
                'error' => "Vehiculos no encontrados en esta unidad de negocio"
            ], 404);
        }else{
            return response()->json([
                'data' => $vehiculo
            ]);
        }
    }

    //Tanques modificados en sigo
    public function showCapacityFuelCurrentByVehicle(Request $request){
        $vehiculo = DB::connection('pgsqlsigo')->table("vehicle_tank_capacity_modified")
        ->where('vehicle_tank_capacity_modified.fk_vehicle', $request->id_vehicle)
        ->get();

        if($vehiculo->isEmpty()){
            return response()->json([
                'error' => "No se encontro información del vehiculo"
            ], 404);
        }else{
            return response()->json([
                'data' => $vehiculo
            ]);
        }
    }


    //Tanques modificados en sigo
    //Version de desarrollo para traer datos de Odoo
    public function showCapacityFuelCurrentByVehicleDev(Request $request){
        
        
        $id_vehicle = $request->id_vehicle;
        $session_token = "";
        $url = "https://stagemovilidadpersonas.odoo.com/vehicles/$id_vehicle";

        
        $ch = curl_init($url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        //curl_setopt($ch, CURLOPT_POST, true);
        curl_setopt($ch, CURLOPT_HTTPHEADER, [
            'Content-Type: application/json'
        ]);                
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
        

        $response = curl_exec($ch);
        if ($response === false) 
                {
                    echo 'Error CURL: ' . curl_error($ch);
                }
        curl_close($ch);
        
        
        $json = json_decode($response, true);
        
        //cho $response;

        $fk_vehicle = intval($json['pk_vehicle']);
        $tank_capacity_current = intval($json['tank_capacity_current']);
        //echo"<br> $fk_vehicle";

        
        $array_data=[
                "data" => [array(
                "pk_vehicle_tank_capacity_modified" =>"",
                "fk_vehicle" =>$fk_vehicle,
                "tank_capacity_current" =>$tank_capacity_current,
                "update_date" =>"")
                 ]
            ] ;
        return $array_data;

    }    




    
    public function updateCapacityFuelCurrentByVehicle(Request $request){
        try {
            $vehiculo = DB::connection('pgsqlsigo')->table("vehicle_tank_capacity_modified")
            ->where('vehicle_tank_capacity_modified.fk_vehicle', $request->id_vehicle)
            ->update([
                'tank_capacity_current' => $request->capacidad_tanque,
                'update_date' => Carbon::now(),
            ]);
    
            return response()->json([
                'success' => $vehiculo
            ]);
        } catch (\Throwable $th) {
            return response()->json([
                'failed' => $th
            ], 500);
        }
    }

    public function selectVehiclesWithFuelModified(){



        $vehiculos =  DB::connection('pgsqlsigo')->table("vehicles")->select("vehicles.*", "vehicle_tank_capacity_modified.tank_capacity_current", 
        "vehicle_tank_capacity_modified.update_date AS last_updated_capacity")
        ->join('vehicle_tank_capacity_modified', "vehicles.pk_vehicle", "vehicle_tank_capacity_modified.fk_vehicle")
        ->get();

        if($vehiculos->isEmpty()){
            return response()->json([
                'error' => "No existen vehiculos con tanque modificado"
            ], 404);
        }else{
            return response()->json([
                'count' => $vehiculos->count(),
                'data' => $vehiculos
            ]);
        }
    }

    public function selectVehiclesWithFuelModifiedOdoo(){

        $vehiculos =  DB::connection('pgsqlsigo')->table("vehicles")->select("vehicles.*", "vehicle_tank_capacity_modified.tank_capacity_current", 
        "vehicle_tank_capacity_modified.update_date AS last_updated_capacity")
        ->join('vehicle_tank_capacity_modified', "vehicles.pk_vehicle", "vehicle_tank_capacity_modified.fk_vehicle")
        ->get();

        if($vehiculos->isEmpty()){
            return response()->json([
                'error' => "No existen vehiculos con tanque modificado"
            ], 404);
        }else{
            return response()->json([
                'count' => $vehiculos->count(),
                'data' => $vehiculos
            ]);
        }
    }


    public function selectVehiclesWithFuelModifiedDev(){        

    for($i=0; $i<10; $i++){
        $array_data[]=[
                "pk_vehicle" =>$i,
                "eco" =>$i,
                "fk_vehicle_catalog" =>1,
                "fk_color" =>1,
                "plates" =>"0",
                "federal_plates" =>"0",
                "fuel_consumption" =>"",
                "engine_number" =>"1",
                "serial_number" =>"1",
                "fk_insurance_contract" =>null,
                "insurance_subsection" =>null,
                "fk_lease_contract" =>null,
                "fk_parkinglot" =>null,
                "insurance_startup_date" =>null,
                "insurance_expiration_date" =>null,
                "fk_vehicle_status" =>1, ///-------------
                "fk_entity_company" =>1, //-------------
                "fk_federal_entity" =>1,
                "passengers" =>1,
                "fk_seat_type" =>1,
                "doors" =>null,
                "fk_suspension_type" =>null,
                "fk_branch" =>null,
                "description" =>"ZF FORANEO",
                "authorized_fuel_consumption" =>"0",
                "fk_vehicle_usage" =>1,
                "bathroom" =>false,
                "curtains" =>false,
                "monitors" =>false,
                "air_conditioning" =>true,
                "trunk" =>false,
                "seatbelt" =>false,
                "boombox" =>false,
                "advertisement" =>"",
                "iave" =>"0",
                "radio_band" =>"0",
                "sim" =>0,
                "activation_cost" =>"0",
                "last_odometer_fuel_ticket" =>"1",
                "imei" =>null,
                "fk_vehicle_status_group" =>1, //-------------
                "fk_gps_provider" =>null,
                "fk_vehicle_origin" =>1,
                "fk_vehicle_fleet" =>null,
                "tank_capacity_current" =>1, //--------
                "last_updated_capacity" =>""   
            ] ;
    }
    $arreglo_base = [
        "count"=>10,
        "data" => $array_data 
    ];
    return $arreglo_base;



    $session_token = "";
    $url = "https://api.traffilog.mx/clients/json";
        $data = [
            "action" => [
                "name" => "user_login",
                "parameters" => [
                    "login_name" => "Proveedor1",
                    "password" => "6kd20RG2"
                ]
            ]
        ];

        $ch = curl_init($url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_POST, true);
        curl_setopt($ch, CURLOPT_HTTPHEADER, [
            'Content-Type: application/json'
        ]);
        curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($data));
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);

        $response = curl_exec($ch);
        if ($response === false) 
                {
                    echo 'Error CURL: ' . curl_error($ch);
                }
        curl_close($ch);

        
        $json = json_decode($response, true);
        print_r($response);
        
        //exit();
        $session_token = @$json['response']['properties']['data'][0]['session_token'];
        



        //echo $session_token;

//---------------------------------------- Respuesta

       /* $url2 = "https://api.traffilog.mx/clients/json";
        $data = [
        "action" => [
                    "name" => "api_get_data",
                    "parameters" => [
                        [
                            "last_time" => "",
                            "license_nmbr" => "15012",
                            "group_id" => "",
                            "version" => "4"
                        ]
                    ],
                    "session_token" => "$session_token"
                    ]
                ];        
        $ch = curl_init($url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_POST, true);
        curl_setopt($ch, CURLOPT_HTTPHEADER, [
            'Content-Type: application/json'
        ]);
        curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($data));

        $response2 = curl_exec($ch);
        curl_close($ch);

        $json = json_decode($response2, true);
        $longitude = $json['response']['properties']['data'][0]['longitude'];
        $latitude = $json['response']['properties']['data'][0]['latitude'];*/





        echo "1  <br>";
        


        return response()->json([
            'mensaje' => "Desarrollo"
        ], 404);        


        /*
        if($vehiculos->isEmpty()){
            return response()->json([
                'error' => "No existen vehiculos con tanque modificado"
            ], 404);
        }else{
            return response()->json([
                'count' => $vehiculos->count(),
                'data' => $vehiculos
            ]);
        }*/
    }    


    
    //Tanques modificados en activos
/*     public function showCapacityFuelCurrentByVehicle(Request $request){
        $vehiculo = VehiculoGeneral::select('tms_am_vehicles.capacity_tank_current','tms_am_vehicles.last_updated_capacity_tank_current'
        )
        ->where('tms_am_vehicles.pk_vehicle', $request->id_vehicle)
        ->get();

        if($vehiculo->isEmpty()){
            return response()->json([
                'error' => "No se encontro información del vehiculo"
            ], 404);
        }else{
            return response()->json([
                'data' => $vehiculo
            ]);
        }
    }

    
    public function updateCapacityFuelCurrentByVehicle(Request $request){
        $vehiculo = VehiculoGeneral::where('tms_am_vehicles.pk_vehicle', $request->id_vehicle)
        ->update([
            'capacity_tank_current' => $request->capacidad_tanque,
            'last_updated_capacity_tank_current' => Carbon::now(),
        ]);

        if($vehiculo->isEmpty()){
            return response()->json([
                'error' => "No se actualizo el tanque del vehiculo"
            ], 404);
        }else{
            return response()->json([
                'success' => $vehiculo
            ]);
        }
    }

    public function selectVehiclesWithFuelModified(){
        $vehiculos = VehiculoGeneral::whereRaw('tms_am_vehicles.capacity_tank_current IS NOT NULL')->get();

        if($vehiculos->isEmpty()){
            return response()->json([
                'error' => "No existen vehiculos con tanque modificado"
            ], 404);
        }else{
            return response()->json([
                'count' => $vehiculos->count(),
                'data' => $vehiculos
            ]);
        }
    } */

    public function getVehiclesTraffilog(){
        $vehiculos_totales = DB::table('tms_am_vehicles')
        ->where("tms_am_vehicles.fk_vehicle_status", "!=", 8)
        ->where("tms_am_vehicles.fk_vehicle_status", "!=", 18)
        ->where("tms_am_vehicles.fk_vehicle_status", "!=", 2)
        ->where("tms_am_vehicles.fk_vehicle_status", "!=", 7)
        ->count();
        $vehiculos_traffilog_vinculados = DB::table('tms_am_vehicles')
        ->join("tms_am_vehicle_location_traffilog", "tms_am_vehicles.pk_vehicle", DB::raw("CAST(tms_am_vehicle_location_traffilog.fk_vehicle AS INTEGER)"))
        ->where("tms_am_vehicle_location_traffilog.existiendo_activos", true)
        ->where("tms_am_vehicles.fk_vehicle_status", "!=", 8)
        ->where("tms_am_vehicles.fk_vehicle_status", "!=", 18)
        ->where("tms_am_vehicles.fk_vehicle_status", "!=", 2)
        ->where("tms_am_vehicles.fk_vehicle_status", "!=", 7)
        ->get();
        $vehiculos_traffilog_sindato = DB::table('tms_am_vehicle_location_traffilog')
        ->where("tms_am_vehicle_location_traffilog.existiendo_activos", false)
        ->get();

        return response([
            "vehiculos_tms_total" => $vehiculos_totales,
            "vehiculos_vinculados" => $vehiculos_traffilog_vinculados,
            "vehiculos_noencontrados" => $vehiculos_traffilog_sindato
        ]);
    }
}
