<?php

namespace App\Models;

use CodeIgniter\Model;

class TransportAiPlanRouteModel extends Model
{
    protected $table         = 'transport_ai_plan_routes';
    protected $primaryKey    = 'id';
    protected $returnType    = 'array';
    protected $useTimestamps = true;
    protected $dateFormat    = 'datetime';

    protected $allowedFields = [
        'plan_id', 'service_date', 'route_name', 'contract_id', 'shift_id',
        'origin_location_id', 'destination_location_id', 'vehicle_id', 'driver_id',
        'employee_count', 'total_distance_km', 'estimated_duration_minutes',
        'estimated_fuel_litres', 'estimated_cost',
        'departure_time', 'arrival_time', 'shift_start_time', 'shift_end_time',
        'return_departure_time', 'return_arrival_time',
        'route_geometry', 'optimization_notes', 'published_schedule_id',
    ];

    public function getForPlan(int $planId): array
    {
        return $this->select(
            'transport_ai_plan_routes.*,
             contracts.title AS contract_title,
             contracts.number AS contract_number,
             origin.name AS origin_name,
             origin.latitude AS origin_latitude,
             origin.longitude AS origin_longitude,
             destination.name AS destination_name,
             destination.latitude AS destination_latitude,
             destination.longitude AS destination_longitude,
             vehicles.registration_number AS vehicle_registration,
             vehicles.vehicle_type,
             vehicles.capacity AS vehicle_capacity,
             drivers.number AS driver_number,
             drivers.license_class AS driver_license_class,
             driver_employees.full_name AS driver_name,
             shifts.name AS shift_name'
        )
            ->join('contracts', 'contracts.id = transport_ai_plan_routes.contract_id', 'left')
            ->join('transport_locations AS origin', 'origin.id = transport_ai_plan_routes.origin_location_id', 'left')
            ->join('transport_locations AS destination', 'destination.id = transport_ai_plan_routes.destination_location_id', 'left')
            ->join('transport_vehicles AS vehicles', 'vehicles.id = transport_ai_plan_routes.vehicle_id', 'left')
            ->join('transport_drivers AS drivers', 'drivers.id = transport_ai_plan_routes.driver_id', 'left')
            ->join('employees AS driver_employees', 'driver_employees.id = drivers.employee_id', 'left')
            ->join('attendance_shifts AS shifts', 'shifts.id = transport_ai_plan_routes.shift_id', 'left')
            ->where('transport_ai_plan_routes.plan_id', $planId)
            ->orderBy('transport_ai_plan_routes.service_date', 'ASC')
            ->orderBy('transport_ai_plan_routes.departure_time', 'ASC')
            ->findAll();
    }

    public function deleteForPlan(int $planId): void
    {
        $this->where('plan_id', $planId)->delete();
    }
}
