<?php

namespace App\Models;

use CodeIgniter\Model;

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

    protected $allowedFields = [
        'plan_route_id', 'stop_order', 'employee_id', 'latitude', 'longitude',
        'address', 'pickup_time', 'leg_distance_km', 'leg_duration_minutes',
    ];

    public function getForRoute(int $planRouteId): array
    {
        return $this->select(
            'transport_ai_plan_stops.*,
             employees.full_name AS employee_name,
             employees.employee_number,
             employees.phone AS employee_phone'
        )
            ->join('employees', 'employees.id = transport_ai_plan_stops.employee_id', 'left')
            ->where('plan_route_id', $planRouteId)
            ->orderBy('stop_order', 'ASC')
            ->findAll();
    }

    /**
     * All stops for a plan, keyed by plan route id.
     *
     * @return array<int, array[]>
     */
    public function getForPlanGrouped(int $planId): array
    {
        $rows = $this->select(
            'transport_ai_plan_stops.*,
             employees.full_name AS employee_name,
             employees.employee_number,
             employees.phone AS employee_phone'
        )
            ->join('transport_ai_plan_routes AS plan_routes', 'plan_routes.id = transport_ai_plan_stops.plan_route_id')
            ->join('employees', 'employees.id = transport_ai_plan_stops.employee_id', 'left')
            ->where('plan_routes.plan_id', $planId)
            ->orderBy('transport_ai_plan_stops.plan_route_id', 'ASC')
            ->orderBy('transport_ai_plan_stops.stop_order', 'ASC')
            ->findAll();

        $grouped = [];
        foreach ($rows as $row) {
            $grouped[(int) $row['plan_route_id']][] = $row;
        }

        return $grouped;
    }

    public function deleteForPlanRoutes(array $planRouteIds): void
    {
        if ($planRouteIds === []) {
            return;
        }

        $this->whereIn('plan_route_id', $planRouteIds)->delete();
    }
}
