<?php 
/**
 * @api-readonly
 * Returns welder qualification table
 */
use App\Models\WelderTest; 
use App\Models\MaterialGroupMap; 
use Carbon\Carbon;

$query = WelderTest::leftJoin('naks_welders', function($join) {
    $join->on('naks_welders.naks_certificate_no', '=', 'welder_tests.naks_no')
         ->orWhereRaw("FIND_IN_SET(naks_welders.naks_certificate_no, welder_tests.naks_no)");
})
->orderBy("id", "DESC")
->select(
    "welder_tests.id",
    "welder_tests.naks_id",
    "welder_tests.wpq_document_no",
    DB::raw("GROUP_CONCAT(kss_number SEPARATOR ', ') AS kss_number"),
    "welder_tests.material_group_1",
    "welder_tests.material_group_2",
    "welder_tests.grade_1",
    "welder_tests.grade_2",
    DB::raw("MIN(dia_min) AS dia_min"), // Get the smallest dia_min
    DB::raw("
        MAX(CASE 
            WHEN dia_max IS NULL OR dia_max = 0 THEN '♾️' 
            ELSE dia_max 
        END) AS dia_max
    "),
    DB::raw("MIN(thk_min) AS thk_min"), // Get the smallest thk_min
    DB::raw("
        MAX(CASE 
            WHEN thk_max IS NULL OR thk_max = 0 THEN '♾️' 
            ELSE thk_max 
        END) AS thk_max
    "),
    "welder_tests.welding_method",
    "welder_tests.naks_no",
    "welder_tests.welding_date",
    "welder_tests.naks_technology_group",                                                                             
    "welder_tests.approval_date",
    "welder_tests.naks_validity",
    DB::raw("MIN(naks_welders.period_of_validity) AS period_of_validity"), // Get the smallest period_of_validity
    "welder_tests.status",
)
->groupBy(
    "welder_tests.material_group_1",   
    "welder_tests.material_group_2",   
    "welder_tests.naks_id", 
    "welder_tests.wpq_document_no", 
    "welder_tests.welding_method", 
    "welder_tests.naks_no"
)
->get();


$materialGroupMap = MaterialGroupMap::all();
$materialGroupMap2 = [];

$welderQualitificationStatusExpiredDate = setting("WelderQualitificationStatusExpiredDate");

foreach($materialGroupMap AS $material) {
    $materialGroupMap2[$material->material_name] = $material->qualified_materials;
}
foreach($query AS $q) {
        $q->qualified_materials = "";
        if ($q->material_group_1 != $q->material_group_2) {
            
            // Check both orders: material_group_1+material_group_2 and material_group_2+material_group_1
            $key1 = $q->material_group_1 . "+" . $q->material_group_2;
            $key2 = $q->material_group_2 . "+" . $q->material_group_1;
            
            if(isset($materialGroupMap2[$key1]))
            {
                $q->qualified_materials = $materialGroupMap2[$key1];
            } elseif(isset($materialGroupMap2[$key2]))
            {
                $q->qualified_materials = $materialGroupMap2[$key2];
            } else {
                $materialGroups1 = explode(",", $q->material_group_1);
                $materialGroups2 = explode(",", $q->material_group_2);
            
                // Her iki grup için eşleşen materyalleri al
                $qualifiedMaterials1 = array_map(function ($group) use ($materialGroupMap2) {
                    return $materialGroupMap2[$group] ?? null;
                }, $materialGroups1);
            
                $qualifiedMaterials2 = array_map(function ($group) use ($materialGroupMap2) {
                    return $materialGroupMap2[$group] ?? null;
                }, $materialGroups2);
            
                // Her iki sonucu birleştir, benzersiz değerleri al ve null'ları filtrele
                $q->qualified_materials = implode(",", array_filter(array_unique(array_merge($qualifiedMaterials1, $qualifiedMaterials2))));
            }
            
        } else {
            $materialGroups = explode(",", $q->material_group_1);
        
            // Tek grup için eşleşen materyalleri al
            $qualifiedMaterials = array_map(function ($group) use ($materialGroupMap2) {
                return $materialGroupMap2[$group] ?? null;
            }, $materialGroups);
        
            // Benzersiz değerleri al ve null'ları filtrele
            $q->qualified_materials = implode(",", array_filter(array_unique($qualifiedMaterials)));
            

        }
        if(!rejected_date($q->welding_date))
        {
            $q->expired_date = Carbon::parse($q->welding_date)->addDays($welderQualitificationStatusExpiredDate);

        }
        
        
    }
        

echo json_encode_tr($query);

?>