<?php
// Tablo 1: Spool Bazlı Takip (SHOP) - Optimize: only get necessary fields
$shopQuery = db("paint_follow_ups")
    ->select(
        "cycle",
        "brend_name_1",
        "brend_name_2",
        "brend_name_3",
        "akt_date_1",
        "akt_date_2",
        "akt_date_3",
        "spool_no_joint_no"
    )
    ->where("location", "SHOP")
    ->whereNotNull("cycle")
    ->orderBy("cycle");

$shopPaintFollowUps = method_exists($shopQuery, 'cursor') ? $shopQuery->cursor() : $shopQuery->get();

$spoolResults = [];
$spoolProcessed = []; // Track unique cycle+proses combinations

foreach ($shopPaintFollowUps as $row) {
    $row = (array) $row;
    $cycle = $row['cycle'] ?? '';
    $spoolNo = $row['spool_no_joint_no'] ?? '';
    
    if (empty($spoolNo)) {
        continue;
    }
    
    // Check if painted (any akt_date is not null and not rejected)
    $isPainted = false;
    for ($dateIdx = 1; $dateIdx <= 3; $dateIdx++) {
        $aktDate = $row['akt_date_' . $dateIdx] ?? null;
        if (!rejected_date($aktDate)) {
            $isPainted = true;
            break;
        }
    }
    
    // Process each brand for this spool
    for ($k = 1; $k <= 3; $k++) {
        $brand = $row['brend_name_' . $k] ?? null;
        if (empty($brand)) {
            continue;
        }
        
        $key = $cycle . '|' . $brand;
        if (!isset($spoolProcessed[$key])) {
            $spoolProcessed[$key] = [
                'spools' => [], // Track unique spools
                'painted_spools' => [] // Track unique painted spools
            ];
        }
        
        // Add spool to set (unique)
        if (!in_array($spoolNo, $spoolProcessed[$key]['spools'])) {
            $spoolProcessed[$key]['spools'][] = $spoolNo;
        }
        
        // Add to painted if painted
        if ($isPainted && !in_array($spoolNo, $spoolProcessed[$key]['painted_spools'])) {
            $spoolProcessed[$key]['painted_spools'][] = $spoolNo;
        }
    }
}

// Convert to results array for Tablo 1
foreach ($spoolProcessed as $key => $data) {
    list($cycle, $brand) = explode('|', $key);
    $spoolResults[] = [
        "Paint System" => $cycle,
        "Proses" => $brand,
        "Spool Quantity" => count($data['spools']),
        "Painted Spool Quantity" => count($data['painted_spools'])
    ];
}

// Tablo 2: Joint Bazlı Takip (Touch-up) - FIELD - Optimize: only get necessary fields
$fieldQuery = db("paint_follow_ups")
    ->select(
        "cycle",
        "brend_name_1",
        "brend_name_2",
        "brend_name_3",
        "akt_date_1",
        "akt_date_2",
        "akt_date_3",
        "spool_no_joint_no"
    )
    ->where("location", "FIELD")
    ->whereNotNull("cycle")
    ->orderBy("cycle");

$fieldPaintFollowUps = method_exists($fieldQuery, 'cursor') ? $fieldQuery->cursor() : $fieldQuery->get();

$jointResults = [];
$jointProcessed = []; // Track unique cycle+proses combinations

foreach ($fieldPaintFollowUps as $row) {
    $row = (array) $row;
    $cycle = $row['cycle'] ?? '';
    $jointNo = $row['spool_no_joint_no'] ?? '';
    
    if (empty($jointNo)) {
        continue;
    }
    
    // Process each brand for this joint
    for ($k = 1; $k <= 3; $k++) {
        $brand = $row['brend_name_' . $k] ?? null;
        if (empty($brand)) {
            continue;
        }
        
        $key = $cycle . '|' . $brand;
        if (!isset($jointProcessed[$key])) {
            $jointProcessed[$key] = [
                'joints' => [], // Track unique joints
                'total_joints_set' => [] // Track all joints for total count
            ];
        }
        
        // Add joint to set (unique) for touch-up count
        if (!in_array($jointNo, $jointProcessed[$key]['joints'])) {
            $jointProcessed[$key]['joints'][] = $jointNo;
        }
        
        // Add to total joints set
        if (!in_array($jointNo, $jointProcessed[$key]['total_joints_set'])) {
            $jointProcessed[$key]['total_joints_set'][] = $jointNo;
        }
    }
}

// Convert to results array for Tablo 2
foreach ($jointProcessed as $key => $data) {
    list($cycle, $brand) = explode('|', $key);
    
    // Total joints for this cycle (all joints in FIELD location for this cycle)
    $totalJoints = db("paint_follow_ups")
        ->where("location", "FIELD")
        ->where("cycle", $cycle)
        ->distinct()
        ->count("spool_no_joint_no");
    
    $jointResults[] = [
        "Paint System" => $cycle,
        "Proses" => $brand,
        "Touch-up Joints Quantity" => count($data['joints']),
        "Joints Quantity" => $totalJoints
    ];
}

// Return both tables
$response = [
    'spool_data' => $spoolResults,
    'joint_data' => $jointResults
];

echo json_encode_tr($response);
?>