<?php

namespace App\Services\Cartables\Mission\Report;

use App\Facades\DataTable\DataTableFacade;
use App\Models\MissionViolation;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class MachinMissionService
{
    public function Activity(Request $request): array
    {
        $provinceId = $request->province_id ?? auth()->user()->province_id;
        $from = $request->from_date ?? today()->startOfDay();
        $to = $request->to_date ? Carbon::parse($request->to_date)->endOfDay() : today()->endOfDay();

        $sql = "
            SELECT
              cm.machine_code,
              cm.car_name,
              COALESCE(c.name_fa, '---') AS city_name,
              COUNT(m.id) AS missions_count,
              COALESCE(SUM(m.in_area_duration), 0)  AS gps,
              COALESCE(SUM(m.mission_duration), 0)  AS durations
            
            FROM cmms_machines cm
                
            LEFT JOIN missions m
              ON m.machine_code = cm.machine_code
             AND m.created_at >= :from
             AND m.created_at <= :to
            
            LEFT JOIN cities c
              ON c.id = cm.city_id
            
            WHERE (:car_group IS NULL OR cm.car_group = :car_group)
              AND (:province_id IS NULL OR cm.province_id = :province_id)
            
            GROUP BY
              cm.machine_code,
              cm.car_name,
              c.name_fa
            
            ORDER BY
              missions_count DESC,
              durations DESC,
              cm.machine_code;

        ";

        return DB::select($sql, [
            'province_id' => $provinceId,
            'from'      => $from,
            'to'        => $to,
        ]);
    }
}