<?php 
/**
 * @api-readonly
 * Returns spool area release data (no cache)
 */
if(isset($loadOptions)) {
    $_GET = $loadOptions;
}

if(!isset($basic))  {
    $basic = false;
}

// Veriyi hesapla
$response = (function() use ($basic) {
    if(getisset("filter_iso_number")) {
        $type_of_joint = "type_of_joint";
    } else {
        $type_of_joint = "GROUP_CONCAT(DISTINCT type_of_joint SEPARATOR '/') as type_of_joint";
    }

    $users = usersArray();

    // Önce document_revisions'ları al
    $documentRevisions = [];
    $revisionQuery = db("document_revisions")
        ->select('drawing_no', 'revision_no')
        ->get();

    foreach($revisionQuery as $revision) {
        $documentRevisions[$revision->drawing_no] = $revision->revision_no;
    }

    // spool_status sıralaması
    $statusOrder = [
        'Waiting' => 1,
        'Manufacturing' => 2,
        'QC' => 3,
        'Paint' => 4,
        'Completed' => 5,
    ];

    $caseSql = "CASE spool_status ";
    foreach ($statusOrder as $status => $order) {
        $caseSql .= "WHEN '{$status}' THEN {$order} ";
    }
    $caseSql .= "ELSE 99 END";

    $minStatusSub = apply_welded_filter(db("weld_logs"))
        ->select(
            'iso_number',
            'spool_number',
            DB::raw("MIN(CASE WHEN type_of_joint = 'S' THEN {$caseSql} END) as min_status_order")
        )
        ->groupBy('iso_number', 'spool_number');

    // Filter durumuna göre farklı SELECT yapısı
    $isFiltered = getisset("filter_iso_number") || getisset("filter_spool_number");

    if($isFiltered) {
        // Detay sayfası - normal alanları çek
        $query = apply_welded_filter(db("weld_logs"))
            ->select(
                'weld_logs.id',
                'weld_logs.iso_number',
                'weld_logs.spool_number',
                'no_of_the_joint_as_per_as_built_survey AS joint_no',
                DB::raw($type_of_joint),
                'project',
                "iso_rev",
                'painting_cycle',
                'spool_status',      
                'clearance_no',
                'clearance_date',
                'ndt_release_date',
                'register_paint_no',
                'register_date',
                'ndt_release_no',
                'paint_release_date',
                'paint_release_no',
                'spool_zone',
                'spool_ndt_check',
                'spool_paint_check',
                'spool_ndt_check_no',
                'ndt_release_check',
                'spool_ndt_check_date',
                'paint_release_check',
                'spool_remarks',
                'min_status_order_tbl.min_status_order'
            );
    } else {
        // Ana liste - S tipinin verilerini çek
        $query = apply_welded_filter(db("weld_logs"))
            ->select(
                'weld_logs.id',
                'weld_logs.iso_number',
                'weld_logs.spool_number',
                'no_of_the_joint_as_per_as_built_survey AS joint_no',
                DB::raw($type_of_joint),
                'project',
                "iso_rev",
                'painting_cycle',
                'spool_status',      
                DB::raw("MAX(CASE WHEN type_of_joint = 'S' THEN clearance_no END) as clearance_no"),
                DB::raw("MIN(CASE WHEN type_of_joint = 'S' THEN clearance_date END) as min_clearance_date"),
                DB::raw("MAX(CASE WHEN type_of_joint = 'S' THEN clearance_date END) as clearance_date"),
                DB::raw("COUNT(DISTINCT CASE WHEN type_of_joint = 'S' THEN COALESCE(clearance_date, 'NULL_VALUE') END) as clearance_date_count"),
                DB::raw("MIN(CASE WHEN type_of_joint = 'S' THEN ndt_release_date END) as min_ndt_release_date"),
                DB::raw("MAX(CASE WHEN type_of_joint = 'S' THEN ndt_release_date END) as ndt_release_date"),
                DB::raw("COUNT(DISTINCT CASE WHEN type_of_joint = 'S' THEN COALESCE(ndt_release_date, 'NULL_VALUE') END) as ndt_release_date_count"),
                DB::raw("MAX(CASE WHEN type_of_joint = 'S' THEN register_paint_no END) as register_paint_no"),
                DB::raw("MIN(CASE WHEN type_of_joint = 'S' THEN register_date END) as min_register_date"),
                DB::raw("MAX(CASE WHEN type_of_joint = 'S' THEN register_date END) as register_date"),
                DB::raw("COUNT(DISTINCT CASE WHEN type_of_joint = 'S' THEN COALESCE(register_date, 'NULL_VALUE') END) as register_date_count"),
                DB::raw("MAX(CASE WHEN type_of_joint = 'S' THEN ndt_release_no END) as ndt_release_no"),
                DB::raw("MIN(CASE WHEN type_of_joint = 'S' THEN paint_release_date END) as min_paint_release_date"),
                DB::raw("MAX(CASE WHEN type_of_joint = 'S' THEN paint_release_date END) as paint_release_date"),
                DB::raw("COUNT(DISTINCT CASE WHEN type_of_joint = 'S' THEN COALESCE(paint_release_date, 'NULL_VALUE') END) as paint_release_date_count"),
                DB::raw("MAX(CASE WHEN type_of_joint = 'S' THEN paint_release_no END) as paint_release_no"),
                DB::raw("MAX(CASE WHEN type_of_joint = 'S' THEN spool_zone END) as spool_zone"),
                DB::raw("MAX(CASE WHEN type_of_joint = 'S' THEN spool_ndt_check END) as spool_ndt_check"),
                DB::raw("MAX(CASE WHEN type_of_joint = 'S' THEN spool_paint_check END) as spool_paint_check"),
                DB::raw("MAX(CASE WHEN type_of_joint = 'S' THEN spool_ndt_check_no END) as spool_ndt_check_no"),
                DB::raw("MAX(CASE WHEN type_of_joint = 'S' THEN ndt_release_check END) as ndt_release_check"),
                DB::raw("MIN(CASE WHEN type_of_joint = 'S' THEN spool_ndt_check_date END) as min_spool_ndt_check_date"),
                DB::raw("MAX(CASE WHEN type_of_joint = 'S' THEN spool_ndt_check_date END) as spool_ndt_check_date"),
                DB::raw("COUNT(DISTINCT CASE WHEN type_of_joint = 'S' THEN COALESCE(spool_ndt_check_date, 'NULL_VALUE') END) as spool_ndt_check_date_count"),
                DB::raw("MAX(CASE WHEN type_of_joint = 'S' THEN paint_release_check END) as paint_release_check"),
                DB::raw("MAX(CASE WHEN type_of_joint = 'S' THEN spool_remarks END) as spool_remarks"),
                'min_status_order_tbl.min_status_order'
            );
    }

    $query = $query->leftJoinSub($minStatusSub, 'min_status_order_tbl', function($join) {
            $join->on('weld_logs.iso_number', '=', 'min_status_order_tbl.iso_number')
                 ->on('weld_logs.spool_number', '=', 'min_status_order_tbl.spool_number');
        });

    if(getisset("filter_iso_number")) {
        $query = $query->where("weld_logs.iso_number", get("filter_iso_number"));
    }

    if(getisset("filter_spool_number")) {
        $query = $query->where("weld_logs.spool_number", get("filter_spool_number"));
    }

    if(!getisset("filter_iso_number") && !getisset("filter_spool_number")) {
        $query = $query
        ->groupBy("weld_logs.iso_number","weld_logs.spool_number");
    }

    if(getesit("module", "spool-release-paint")) {
        $query = $query->whereNotNull("painting_cycle");
    }

    $query = $query->get();

    $isDetail = getisset("filter_iso_number") || getisset("filter_spool_number");

    // min_status_order'u tekrar spool_status stringine çevir
    $orderToStatus = array_flip($statusOrder);

    if (!$isDetail) {
        // Sadece ana listede orderToStatus işlemi çalışsın
        foreach($query as $q) {
            if(strpos($q->type_of_joint, 'S') !== false && isset($q->min_status_order) && $q->min_status_order >= 1 && $q->min_status_order <= 5) {
                $q->spool_status = $orderToStatus[$q->min_status_order];
            }
            // F olanlar spool_status'u olduğu gibi kalacak
        }
    }

    $summary = [];
    $esummary = [];

    // Tüm spool_status değerlerini önceden al
    $allSpoolStatusesQuery = db("weld_logs")
        ->select('spool_status', 'iso_number', 'spool_number')->where("type_of_joint", "S")->get();

    $allSpoolStatuses = [];
    foreach($allSpoolStatusesQuery AS $statusQuery) {
        $allSpoolStatuses[$statusQuery->iso_number][$statusQuery->spool_number][] = $statusQuery->spool_status;
    }

    foreach($query AS $q) {
        // Revision numarasını ekle
        $q->revision_no = $documentRevisions[$q->iso_number] ?? null;
        
        $q->project = strtoupper($q->project);
        if($q->spool_ndt_check != "") {
            if(isset($users[$q->spool_ndt_check])) {
                $user = $users[$q->spool_ndt_check];
                $q->spool_ndt_check = "✒️{$user->name}";
            }

            if(isset($users[$q->spool_ndt_check])) {
                $user = $users[$q->spool_ndt_check];
                $q->spool_ndt_check = "✒️{$user->name}";
            }
            
            if(isset($users[$q->spool_paint_check])) {
                $user = $users[$q->spool_paint_check];
                $q->spool_paint_check = "✒️{$user->name}";
            }

            if(isset($users[$q->ndt_release_check])) {
                $user = $users[$q->ndt_release_check];
                $q->ndt_release_check = "✒️{$user->name}";
            }

            if(isset($users[$q->paint_release_check])) {
                $user = $users[$q->paint_release_check];
                $q->paint_release_check = "✒️{$user->name}";
            }

            
        }

        if(isset($q->spool_status)) {
            if (!isset($summary[$q->spool_status])) {
                $summary[$q->spool_status] = 0;
            }

            if($q->type_of_joint == "S" || $q->type_of_joint == "F/S") {
                $summary[$q->spool_status]++;
            }

            if (!isset($esummary[$q->spool_status])) {
                $esummary[$q->spool_status] = 0;
            }

            if($q->type_of_joint == "F") {
                $esummary[$q->spool_status]++;
            }
        }

        if(!getisset("filter_iso_number") && !getisset("filter_spool_number")) {
            // Önceden alınan spool_status değerlerini kontrol et
            $spoolStatuses = $allSpoolStatuses[$q->iso_number][$q->spool_number] ?? [];
                    

            // Eğer tüm spool_status değerleri "Completed" ise
            /*
                if(getLowestSpoolStatus($spoolStatuses) != $q->spool_status) {
                    $q->spool_status .= "⚠️";
                }
            */
            $q->spool_status = getLowestSpoolStatus($spoolStatuses);
            // Latest status hesaplama - önceden hesaplanmış değeri kullan
            $q->latest_status = getLatestSpoolStatus($spoolStatuses);
            
            // Check for date inconsistencies and add warning icon
            // If count > 1, it means there are different values (including NULL/empty)
            if($q->ndt_release_date_count > 1 || $q->min_ndt_release_date != $q->ndt_release_date) {
                $q->spool_status .= "⚠️";
            }
            
            if($q->clearance_date_count > 1 || $q->min_clearance_date != $q->clearance_date) {
                $q->spool_status .= "⚠️";
            }
            
            if($q->register_date_count > 1 || $q->min_register_date != $q->register_date) {
                $q->spool_status .= "⚠️";
            }
            
            if($q->paint_release_date_count > 1 || $q->min_paint_release_date != $q->paint_release_date) {
                $q->spool_status .= "⚠️";
            }
            
            if($q->spool_ndt_check_date_count > 1 || $q->min_spool_ndt_check_date != $q->spool_ndt_check_date) {
                $q->spool_status .= "⚠️";
            }
        }
            

        
        /*
        if($q->iso_number == "PG-900-AH01PG-FA00.030-HC") {
            if($q->spool_number == "SPL-0003") {
                dump($q->spool_status);
                dd($spoolStatuses);
            }
        }
        */
            
    }

    if($basic) {
        $filteredQuery = [];
        foreach($query as $q) {
            $filteredQuery[] = (object)[
                'iso_number' => $q->iso_number ?? null,
                'spool_number' => $q->spool_number ?? null,
                'painting_cycle' => $q->painting_cycle ?? null,
                'iso_rev' => $q->iso_rev ?? null,
                'revision_no' => $q->revision_no ?? null,
                'spool_status' => $q->spool_status ?? null,
            ];
        }
        $query = $filteredQuery;
    }

    $response = [];
    $response['summary'] = $summary;
    $response['esummary'] = $esummary;
    $response['datagrid'] = $query;

    return $response;
})();

echo json_encode_tr($response);

?>