<?php 
/**
 * @api-readonly
 * Returns repair table statistics
 */
use Carbon\Carbon;

// Increase memory and time limits for heavy queries
ini_set('memory_limit', '1024M');
set_time_limit(300);

function sortByWeekNumber($a, $b) {
    $dayOfWeek = Carbon::MONDAY;
    $aDate = Carbon::now()->setISODate((int) substr($a, 3), (int) substr($a, 0, 2), $dayOfWeek);
    $bDate = Carbon::now()->setISODate((int) substr($b, 3), (int) substr($b, 0, 2), $dayOfWeek);
    return $aDate->timestamp - $bDate->timestamp;
}

$logs = log_test_types();

$defectTypeByWelder = [];
$defectTypeByNPS = [];
$defectTypeByMaterial = [];
$mostCommonDefectType = [];
$weeklyStatusBWJoints = [];
$weeklyStatusSWJoints = [];
$weeklyResultByLogType = [];
$repairCount = [];
$notRepairCount = [];
$materialRepairCount = [];
$materialNotRepairCount = [];

// Load materials for short_name lookup
$materialsMap = [];
$materials = db("materials")->select("ru_group", "short_name")->get();
foreach($materials as $mat) {
    if(!empty($mat->ru_group)) {
        $materialsMap[$mat->ru_group] = $mat->short_name ?? $mat->ru_group;
    }
}

$welderLocations = welder_locations();

$defects = defects();
$defect_names = defect_names();
$data = [];
$total = 0;

$targetRate = (int) setting("repair_table_target_rate");


$results = db("results")->select("title");


$results = $results->get()->pluck("title")->toArray();

if(getisset("d1")) {
    $start = Carbon::parse(get("d1"));
    $end = Carbon::parse(get("d2"));
} else {
    $now = Carbon::now();
    $start = $now->subMonths(2)->startOfMonth();
    $end = Carbon::now()->endOfMonth();
}


foreach($logs AS $logType => $log) {
    $logQuery = db($log);

    
    $logQuery = $logQuery->get();

    foreach($logQuery AS $query) {

        
        $testDate = $logType . "_test_date";
        $testLabs = "test_laboratory_" . $logType;
        if(isset($query->$testDate)) 
        {
            if(!rejected_date($query->$testDate)) {
                $weekNumber = date("W/Y", strtotime($query->$testDate));
                $carbonTestDate = Carbon::parse($query->$testDate);
    
        
                if($carbonTestDate->between($start, $end)) {
                    foreach($results AS $result) {
                        if(!isset($weeklyStatusBWJoints[$weekNumber][$result]))
                            $weeklyStatusBWJoints[$weekNumber][$result] = 0;
            
                        if(!isset($weeklyStatusSWJoints[$weekNumber][$result]))
                            $weeklyStatusSWJoints[$weekNumber][$result] = 0;
                    }
                
                    $result = $logType . "_result";
    
                    if(isset($query->nps_1))
                    {
                        // Normalize NPS value (e.g., "0.5" and "0.50" should be treated as same)
                        $normalizedNps = is_numeric($query->nps_1) ? (string) floatval($query->nps_1) : $query->nps_1;
                        
                        if(!isset($repairCount[$normalizedNps]))
                            $repairCount[$normalizedNps] = 0;

                        if(!isset($notRepairCount[$normalizedNps]))
                            $notRepairCount[$normalizedNps] = 0;

                        if(!in_array($query->$result, ['', "Accept / Годен"]))
                        {
                            $repairCount[$normalizedNps]++;
                        }

                        if(in_array($query->$result, ["Accept / Годен"]))
                        {
                            $notRepairCount[$normalizedNps]++;
                        }
                    }

                    // Material group repair rate tracking
                    if(isset($query->ru_material_group_1) && !empty($query->ru_material_group_1)) {
                        $ruGroup = $query->ru_material_group_1;
                        
                        if(!isset($materialRepairCount[$ruGroup]))
                            $materialRepairCount[$ruGroup] = 0;
                        if(!isset($materialNotRepairCount[$ruGroup]))
                            $materialNotRepairCount[$ruGroup] = 0;

                        if(!in_array($query->$result, ['', "Accept / Годен"])) {
                            $materialRepairCount[$ruGroup]++;
                        }
                        if(in_array($query->$result, ["Accept / Годен"])) {
                            $materialNotRepairCount[$ruGroup]++;
                        }
                    }
                    
                    
                    if($query->type_of_welds == "BW") {
                        
                        if($query->$result != "") {
                            $weeklyStatusBWJoints[$weekNumber][$query->$result]++;
                        }
                    }
    
                    if($query->type_of_welds == "SW") {
                        
                        if($query->$result != "") {
                            $weeklyStatusSWJoints[$weekNumber][$query->$result]++;
                        }
                        
    
                    }
    
                }
            }
        }
        

        uksort($weeklyStatusBWJoints, 'sortByWeekNumber');
        uksort($weeklyStatusSWJoints, 'sortByWeekNumber');
        
        
        foreach($defects AS $defect) {

            if(!isset($mostCommonDefectType[$defect])) 
                $mostCommonDefectType[$defect] = 0;

                
            if(array_key_exists("real_welder_1", (Array) $query)) {
                if(strlen($query->real_welder_1) == 4) {
                    if(!isset($defectTypeByWelder["x"][$query->real_welder_1][$defect])) 
                        $defectTypeByWelder["x"][$query->real_welder_1][$defect] = 0;

                    if(isset($query->nps_1)) {
                        // Normalize NPS value for defect type grouping
                        $normalizedNpsDefect = is_numeric($query->nps_1) ? (string) floatval($query->nps_1) : $query->nps_1;
                        if(!isset($defectTypeByNPS["x"][$normalizedNpsDefect][$defect])) 
                            $defectTypeByNPS["x"][$normalizedNpsDefect][$defect] = 0;
                    }

                    // Material group (ru_material_group_1) tracking
                    if(isset($query->ru_material_group_1) && !empty($query->ru_material_group_1)) {
                        $ruGroup = $query->ru_material_group_1;
                        if(!isset($defectTypeByMaterial["x"][$ruGroup][$defect])) 
                            $defectTypeByMaterial["x"][$ruGroup][$defect] = 0;
                    }
    
                    
                    if(array_key_exists($defect, (Array) $query)) {
                        
                        if(!is_null($query->$defect)) {
                            $defectArray = explode(",", $query->$defect);
                            $defectCount = count($defectArray);
        
                            $defectTypeByWelder["x"][$query->real_welder_1][$defect] += $defectCount;
                            
                            if(isset($query->nps_1)) {
                                $normalizedNpsDefect = is_numeric($query->nps_1) ? (string) floatval($query->nps_1) : $query->nps_1;
                                $defectTypeByNPS["x"][$normalizedNpsDefect][$defect] += $defectCount;
                            }

                            // Material group defect count
                            if(isset($query->ru_material_group_1) && !empty($query->ru_material_group_1)) {
                                $ruGroup = $query->ru_material_group_1;
                                $defectTypeByMaterial["x"][$ruGroup][$defect] += $defectCount;
                            }
                               
                                
                            $mostCommonDefectType[$defect] += $defectCount;
        
                            $total += $defectCount;
                        }
                        
                    }
                }
            } else { // real_welder 
            }
            
            
        }
        
    }
}


$defectTypeByWelderRows = [];

foreach($defectTypeByWelder AS $contractor => $data) {
    foreach($data AS $welder => $subData) {
        $welderId = get_welder_id($welder);
        $welderInfo =  get_welder_info($welderId);
        if($welderInfo) 
        {
            $welderName = $welderInfo->welder_name_ru;
            $welderCompany = $welderInfo->company;
            $rowData = [
                'company' => $welderCompany,
                'welder' => $welderName,
                'naks_no' => $welderId,
                'status' => @$welderLocations[$welder],
            ];
            foreach($subData AS $defectName => $defectValue) {
                $rowData[$defectName] = $defectValue;
            }
            $defectTypeByWelderRows[] = $rowData;
        } 
        
    }
    
}

$defectTypeByNPSRows = [];

foreach($defectTypeByNPS AS $contractor => $data) {
    foreach($data AS $nps => $subData) {

        try {
            $repairRate = round($repairCount[$nps] * 100 / ($notRepairCount[$nps] + $repairCount[$nps]), 2);
        } catch (\Throwable $th) {
            $repairRate = 0;
        }
        
        $rowData = [
            'nps' => $nps,
            'repair_rate' => "$repairRate %"
        ]; 

        foreach($subData AS $defectName => $defectValue) {
            $rowData[$defectName] = $defectValue;
        }
        $defectTypeByNPSRows[] = $rowData;
        
        
    }
    
}

$defectTypeByMaterialRows = [];

foreach($defectTypeByMaterial AS $contractor => $data) {
    foreach($data AS $ruGroup => $subData) {
        try {
            $repairRate = round($materialRepairCount[$ruGroup] * 100 / ($materialNotRepairCount[$ruGroup] + $materialRepairCount[$ruGroup]), 2);
        } catch (\Throwable $th) {
            $repairRate = 0;
        }
        
        // Get short_name from materials map
        $shortName = $materialsMap[$ruGroup] ?? $ruGroup;
        
        $rowData = [
            'ru_group' => $ruGroup,
            'short_name' => $shortName,
            'repair_rate' => "$repairRate %"
        ]; 

        foreach($subData AS $defectName => $defectValue) {
            $rowData[$defectName] = $defectValue;
        }
        $defectTypeByMaterialRows[] = $rowData;
    }
}

$mostCommonDefectTypeRows = [];

foreach($mostCommonDefectType AS $type => $value) {
    try {
        $percent = round($value * 100 / $total, 2);
    } catch (\Throwable $th) {
        $percent = 0;
    }
    if($percent != 0 ) {
        $rowData = [
            'defect_exp' => $defect_names[array_search($type, $defects)],
            'defect_type' => ucfirst($type), 
            'qty' => $value,
            'percent' => $percent,
        ];
        $mostCommonDefectTypeRows[] = $rowData;
    }
    
}

// Sort by percent ascending (smallest to largest)
usort($mostCommonDefectTypeRows, function($a, $b) {
    return $a['percent'] <=> $b['percent'];
});

$weeklyStatusBWJointsRows = [];
$weeklyStatusBWJointsChart = [];

$weeklyStatusSWJointsRows = [];
$weeklyStatusSWJointsChart = [];

foreach($weeklyStatusBWJoints AS $weekNumber => $data) {


    $rowData = [
        'Week' => $weekNumber,
    ];

    foreach($data AS $result => $value) {
        $rowData[$result] = $value;
    }
    $pay = ($data['Reject / Reject'] + $data['Repair / Ремонт'] + $data['Cut / Резать']);
    $payda = ($data['Reject / Reject'] + $data['Repair / Ремонт'] + $data['Cut / Резать'] + $data['Accept / Годен']);
    try {
        $rejectRate = round($pay * 100 / $payda, 2);
    } catch (\Throwable $th) {
        $rejectRate = 0;
       
    }

    $rowData['REJ Rate'] = $rejectRate;
    

    $weeklyStatusBWJointsRows[] = $rowData;
    $weeklyStatusBWJointsChart[] = ['week' => $weekNumber, 'rate' => $rejectRate, 'default' => $targetRate];
}

foreach($weeklyStatusSWJoints AS $weekNumber => $data) {


    $rowData = [
        'Week' => $weekNumber,
    ];

    foreach($data AS $result => $value) {
        $rowData[$result] = $value;
    }

    try {
        $rejectRate = round($data['Repair / Ремонт'] * 100 / $data['Accept / Годен']);
    } catch (\Throwable $th) {
        $rejectRate = 0;
    }

    $rowData['REJ Rate'] = $rejectRate;
    

    $weeklyStatusSWJointsRows[] = $rowData;
    $weeklyStatusSWJointsChart[] = ['week' => $weekNumber, 'rate' => $rejectRate, 'default' => $targetRate];
}


$json['defectTypeByWelder'] = $defectTypeByWelderRows;
$json['defectTypeByNPS'] = $defectTypeByNPSRows;
$json['defectTypeByMaterial'] = $defectTypeByMaterialRows;
$json['mostCommonDefectType'] = $mostCommonDefectTypeRows;
$json['weeklyStatusBWJoints'] = $weeklyStatusBWJointsRows;
$json['weeklyStatusBWJointsChart'] = $weeklyStatusBWJointsChart;
$json['weeklyStatusSWJoints'] = $weeklyStatusSWJointsRows;
$json['weeklyStatusSWJointsChart'] = $weeklyStatusSWJointsChart;

echo json_encode_tr($json);
 ?>