<?php 
/**
 * @api-readonly
 * Returns crew performance statistics
 */
$year = isset($_GET['year']) ? (int)$_GET['year'] : date('Y');

// Get crew performance data by month
$crewQuery = db("weld_logs")
    ->select([
        "ste_subcontractor",
        "mechanic_supervisor",
        DB::raw('MONTH(mechanic_supervisor_control_date) as month'),
        DB::raw('ROUND(SUM(nps_1), 2) as nps1'),
        DB::raw('COUNT(*) as joint_count')
    ])
    ->whereYear("mechanic_supervisor_control_date", $year)
    ->whereNotNull("mechanic_supervisor")
    ->whereNotNull("mechanic_supervisor_control_date")
    ->groupBy("ste_subcontractor", "mechanic_supervisor", DB::raw('MONTH(mechanic_supervisor_control_date)'))
    ->orderBy("ste_subcontractor")
    ->orderBy("mechanic_supervisor")
    ->get();

// Restructure data for DataGrid
$crewData = [];
foreach($crewQuery as $row) {
    $key = $row->ste_subcontractor . '|' . $row->mechanic_supervisor;
    
    if(!isset($crewData[$key])) {
        $crewData[$key] = [
            'ste_subcontractor' => $row->ste_subcontractor,
            'mechanic_supervisor' => $row->mechanic_supervisor,
        ];
        // Initialize all 12 months
        for($i = 1; $i <= 12; $i++) {
            $crewData[$key]['month_' . $i . '_nps1'] = 0;
            $crewData[$key]['month_' . $i . '_count'] = 0;
        }
    }
    
    // Set month data
    $crewData[$key]['month_' . $row->month . '_nps1'] = $row->nps1;
    $crewData[$key]['month_' . $row->month . '_count'] = $row->joint_count;
}

echo json_encode_tr(array_values($crewData));
?>