<?php
ob_start();
include("../../../include/startup.php");
ob_clean();

header('Content-Type: application/json');
error_reporting(0);

// =============================================
// ⭐ LIVE UPH CONFIGURATION
// =============================================
define('CNC_LIST_UPH_WINDOW_MINUTES', 60);

// =============================================
// Recursive BOM helper — collects Bought In
// materials with product_group = 17
// =============================================
function getAllBoughtInMaterials($conn, $part_id, &$visited = []) {
    if (in_array($part_id, $visited)) {
        return [];
    }
    $visited[] = $part_id;
    $materials = [];

    $query = "
        SELECT
            b.sub_part_id,
            b.sub_part_name,
            b.type,
            im.product_group
        FROM bom b
        LEFT JOIN item_management im
            ON im.id = b.sub_part_id
        WHERE b.main_product_id = '$part_id'
    ";

    $result = mysqli_query($conn, $query);
    if (!$result) {
        return [];
    }

    while ($row = mysqli_fetch_assoc($result)) {
        if (
            $row['type'] === 'Bought In' &&
            $row['product_group'] == 17 &&
            !empty($row['sub_part_name'])
        ) {
            $materials[] = $row['sub_part_name'];
        }

        if (
            $row['type'] === 'Manufactured' &&
            !empty($row['sub_part_id'])
        ) {
            $sub_materials = getAllBoughtInMaterials(
                $conn,
                $row['sub_part_id'],
                $visited
            );
            $materials = array_merge($materials, $sub_materials);
        }
    }

    return array_unique($materials);
}

// =============================================
// Scheduling helpers (unchanged)
// =============================================
function recalculateMachineDates($conn, $machineId, $tableId) {
    set_time_limit(300);

    $shiftsByDay = [];
    $shiftResult = mysqli_query($conn, "SELECT * FROM working_hours");
    while ($row = mysqli_fetch_assoc($shiftResult)) {
        $day = (int)$row['day_shifts'];
        $shiftsByDay[$day][] = $row;
    }

    $tasks = [];
    $taskResult = mysqli_query($conn,
        "SELECT id, total_hours AS hours_to_schedule
         FROM cnc_machine_table
         WHERE machine_id = $machineId
           AND table_id   = $tableId
           AND finished   = 0
         ORDER BY priority"
    );
    while ($row = mysqli_fetch_assoc($taskResult)) {
        $tasks[] = $row;
    }

    $currentDateTime = strtotime(date('Y-m-d') . ' 00:00:00');

    foreach ($tasks as $task) {
        $hoursRemaining = floatval($task['hours_to_schedule']);
        $dateHoursMap   = [];
        $taskStartTime  = null;
        $taskEndTime    = null;
        $firstShiftUsed = null;

        while ($hoursRemaining > 0) {
            $currentDate = date('Y-m-d', $currentDateTime);
            $dayOfWeek   = date('w', $currentDateTime);

            if (!isset($shiftsByDay[$dayOfWeek])) {
                $currentDateTime = strtotime($currentDate . ' +1 day');
                continue;
            }

            foreach ($shiftsByDay[$dayOfWeek] as $shift) {
                $shiftStart = strtotime($currentDate . ' ' . $shift['start_time']);
                $shiftEnd   = strtotime($currentDate . ' ' . $shift['end_time']);
                $shiftName  = $shift['shift_name'];

                if ($shiftEnd <= $currentDateTime) continue;

                $actualStart      = max($currentDateTime, $shiftStart);
                $availableSeconds = max(0, $shiftEnd - $actualStart);
                $availableHours   = $availableSeconds / 3600;

                if ($availableHours <= 0) continue;

                $allocatable = min($hoursRemaining, $availableHours);

                if (!$taskStartTime) {
                    $taskStartTime  = $actualStart;
                    $firstShiftUsed = $shiftName;
                }

                $taskEndTime     = $actualStart + ($allocatable * 3600);
                $hoursRemaining -= $allocatable;

                $dateOnly = date('Y-m-d', $actualStart);
                if (!isset($dateHoursMap[$dateOnly])) {
                    $dateHoursMap[$dateOnly] = 0;
                }
                $dateHoursMap[$dateOnly] += $allocatable;

                $currentDateTime = $taskEndTime;

                if ($hoursRemaining <= 0) break;
            }

            if ($hoursRemaining > 0) {
                $currentDateTime = strtotime(date('Y-m-d', $currentDateTime) . ' +1 day 00:00:00');
            }
        }

        $remainingHoursStr = '';
        foreach ($dateHoursMap as $date => $hrs) {
            $remainingHoursStr .= $date . '=' . round($hrs, 2) . ',';
        }
        $remainingHoursStr = rtrim($remainingHoursStr, ',');

        $startStr = date('Y-m-d H:i:s', $taskStartTime);
        $endStr   = date('Y-m-d H:i:s', $taskEndTime);

        $query = "UPDATE cnc_machine_table SET
            start_date     = '$startStr',
            end_date       = '$endStr',
            shift_ids      = '$firstShiftUsed',
            remaing_hours  = '$remainingHoursStr'
            WHERE id = {$task['id']}";

        mysqli_query($conn, $query);
    }
}

function recalculateMachineDates_fororiginal($conn, $machineId, $tableId) {
    set_time_limit(300);

    $shiftsByDay = [];
    $shiftResult = mysqli_query($conn, "SELECT * FROM working_hours");
    while ($row = mysqli_fetch_assoc($shiftResult)) {
        $day = (int)$row['day_shifts'];
        $shiftsByDay[$day][] = $row;
    }

    $tasks = [];
    $taskResult = mysqli_query($conn,
        "SELECT id, original_total_hours AS hours_to_schedule
         FROM cnc_machine_table
         WHERE machine_id = $machineId
           AND table_id   = $tableId
           AND finished   = 0
         ORDER BY priority"
    );
    while ($row = mysqli_fetch_assoc($taskResult)) {
        $tasks[] = $row;
    }

    $currentDateTime = strtotime(date('Y-m-d') . ' 00:00:00');

    foreach ($tasks as $task) {
        $hoursRemaining = floatval($task['hours_to_schedule']);
        $dateHoursMap   = [];
        $taskStartTime  = null;
        $taskEndTime    = null;
        $firstShiftUsed = null;

        while ($hoursRemaining > 0) {
            $currentDate = date('Y-m-d', $currentDateTime);
            $dayOfWeek   = date('w', $currentDateTime);

            if (!isset($shiftsByDay[$dayOfWeek])) {
                $currentDateTime = strtotime($currentDate . ' +1 day');
                continue;
            }

            foreach ($shiftsByDay[$dayOfWeek] as $shift) {
                $shiftStart = strtotime($currentDate . ' ' . $shift['start_time']);
                $shiftEnd   = strtotime($currentDate . ' ' . $shift['end_time']);
                $shiftName  = $shift['shift_name'];

                if ($shiftEnd <= $currentDateTime) continue;

                $actualStart      = max($currentDateTime, $shiftStart);
                $availableSeconds = max(0, $shiftEnd - $actualStart);
                $availableHours   = $availableSeconds / 3600;

                if ($availableHours <= 0) continue;

                $allocatable = min($hoursRemaining, $availableHours);

                if (!$taskStartTime) {
                    $taskStartTime  = $actualStart;
                    $firstShiftUsed = $shiftName;
                }

                $taskEndTime     = $actualStart + ($allocatable * 3600);
                $hoursRemaining -= $allocatable;

                $dateOnly = date('Y-m-d', $actualStart);
                if (!isset($dateHoursMap[$dateOnly])) {
                    $dateHoursMap[$dateOnly] = 0;
                }
                $dateHoursMap[$dateOnly] += $allocatable;

                $currentDateTime = $taskEndTime;

                if ($hoursRemaining <= 0) break;
            }

            if ($hoursRemaining > 0) {
                $currentDateTime = strtotime(date('Y-m-d', $currentDateTime) . ' +1 day 00:00:00');
            }
        }

        $remainingHoursStr = '';
        foreach ($dateHoursMap as $date => $hrs) {
            $remainingHoursStr .= $date . '=' . round($hrs, 2) . ',';
        }
        $remainingHoursStr = rtrim($remainingHoursStr, ',');

        $startStr = date('Y-m-d H:i:s', $taskStartTime);
        $endStr   = date('Y-m-d H:i:s', $taskEndTime);

        $query = "UPDATE cnc_machine_table SET
            original_start_date   = '$startStr',
            original_end_date     = '$endStr',
            shift_ids             = '$firstShiftUsed',
            original_remaing_hours = '$remainingHoursStr'
            WHERE id = {$task['id']}";

        mysqli_query($conn, $query);
    }
}

// =============================================
// Overlap / alternating schedule functions (unchanged)
// =============================================
$updatedOrders = [];

function getCurrentOrders($machineId, $conn) {
    global $updatedOrders;
    $currentDate = date('Y-m-d');
    $sql = "SELECT *,
                   original_start_date  AS start_date,
                   original_end_date    AS end_date,
                   original_total_hours AS total_hours
            FROM cnc_machine_table
            WHERE machine_id = ?
              AND original_end_date >= ?
              AND is_break = 0
            ORDER BY priority ASC, original_start_date ASC";

    $stmt = $conn->prepare($sql);
    $stmt->bind_param("is", $machineId, $currentDate);
    $stmt->execute();
    $result = $stmt->get_result();

    $orders = [];
    while ($row = $result->fetch_assoc()) {
        if (isset($updatedOrders[$row['id']]['end_date'])) {
            $row['end_date']      = $updatedOrders[$row['id']]['end_date'];
            $row['total_hours']   = $updatedOrders[$row['id']]['total_hours'];
            $row['remaing_hours'] = $updatedOrders[$row['id']]['remaing_hours'];
        }
        $orders[] = $row;
    }
    return $orders;
}

function processMachineScheduling($machineId, $conn) {
    global $updatedOrders;
    $orders = getCurrentOrders($machineId, $conn);

    foreach ($orders as $order) {
        $orderId        = $order['id'];
        $originalHours  = (float)$order['original_total_hours'];
        $remainingHours = generateRemainingHours($order, strtotime($order['end_date']), $conn);

        $updatedOrders[$orderId] = [
            'end_date'     => $order['end_date'],
            'total_hours'  => $originalHours,
            'remaing_hours'=> $remainingHours
        ];

        $sql = "UPDATE cnc_machine_table
                SET total_hours   = $originalHours,
                    remaing_hours = '$remainingHours'
                WHERE id = $orderId";
        $conn->query($sql);
    }

    if (count($orders) < 2) return;

    $potentialOverlaps = findPotentialOverlaps($orders);
    if (empty($potentialOverlaps)) return;

    foreach ($potentialOverlaps as $overlapPair) {
        $order1 = fetchOrder($overlapPair[0]['id'], $conn);
        $order2 = fetchOrder($overlapPair[1]['id'], $conn);
        if (!$order1 || !$order2) continue;

        $alternated = calculateAlternatingSchedule($order1, $order2, $conn);

        storeUpdatedSchedule($order1['id'], $alternated['order1']);
        storeUpdatedSchedule($order2['id'], $alternated['order2']);

        updateOrderHours($order1['id'], $alternated['order1'], $conn);
        updateOrderHours($order2['id'], $alternated['order2'], $conn);
    }
}

function fetchOrder($orderId, $conn) {
    global $updatedOrders;
    $sql = "SELECT *,
                   original_start_date  AS start_date,
                   original_end_date    AS end_date,
                   original_total_hours AS total_hours
            FROM cnc_machine_table WHERE id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $orderId);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();

    if ($row && isset($updatedOrders[$row['id']]['end_date'])) {
        $row['end_date']      = $updatedOrders[$row['id']]['end_date'];
        $row['total_hours']   = $updatedOrders[$row['id']]['total_hours'];
        $row['remaing_hours'] = $updatedOrders[$row['id']]['remaing_hours'];
    }
    return $row;
}

function findPotentialOverlaps($orders) {
    $overlaps = [];
    for ($i = 0; $i < count($orders) - 1; $i++) {
        for ($j = $i + 1; $j < count($orders); $j++) {
            $order1 = $orders[$i];
            $order2 = $orders[$j];

            $setupHours1 = (float)$order1['setup_hours'] + (float)$order1['breakdown_hours'];
            $setupHours2 = (float)$order2['setup_hours'] + (float)$order2['breakdown_hours'];

            $start1 = strtotime($order1['start_date']) + ($setupHours1 * 3600);
            $end1   = strtotime($order1['end_date']);
            $start2 = strtotime($order2['start_date']) + ($setupHours2 * 3600);
            $end2   = strtotime($order2['end_date']);

            if (($start1 < $end2) && ($start2 < $end1)) {
                $overlaps[] = [$order1, $order2];
            }
        }
    }
    return $overlaps;
}

function calculateAlternatingSchedule($order1, $order2, $conn) {
    $setupHours1 = (float)$order1['setup_hours'] + (float)$order1['breakdown_hours'];
    $setupHours2 = (float)$order2['setup_hours'] + (float)$order2['breakdown_hours'];

    $start1 = strtotime($order1['start_date']) + ($setupHours1 * 3600);
    $end1   = strtotime($order1['end_date']);
    $start2 = strtotime($order2['start_date']) + ($setupHours2 * 3600);
    $end2   = strtotime($order2['end_date']);

    $overlapStart = max($start1, $start2);
    $overlapEnd   = min($end1, $end2);

    if ($overlapStart >= $overlapEnd) {
        $remainingHours1 = generateRemainingHours($order1, $end1, $conn);
        $remainingHours2 = generateRemainingHours($order2, $end2, $conn);
        return [
            'order1' => ['end_date' => date('Y-m-d H:i:s', $end1), 'total_hours' => computeAvailableHoursBetween($start1, $end1, $conn), 'remaing_hours' => $remainingHours1],
            'order2' => ['end_date' => date('Y-m-d H:i:s', $end2), 'total_hours' => computeAvailableHoursBetween($start2, $end2, $conn), 'remaing_hours' => $remainingHours2]
        ];
    }

    $overlapAvailableHours = computeAvailableHoursBetween($overlapStart, $overlapEnd, $conn);

    $cycle1_min = (float)$order1['cycle'];
    $cycle2_min = (float)$order2['cycle'];

    $total_cycles1     = floor(($overlapAvailableHours * 60) / max(0.0001, $cycle1_min));
    $total_cycles2     = floor(($overlapAvailableHours * 60) / max(0.0001, $cycle2_min));
    $alternating_cycles = min($total_cycles1, $total_cycles2);
    $alternating_time  = ($alternating_cycles * $cycle1_min + $alternating_cycles * $cycle2_min) / 60.0;

    $remaining_cycles1 = max(0, $total_cycles1 - $alternating_cycles);
    $remaining_cycles2 = max(0, $total_cycles2 - $alternating_cycles);
    $remaining_time1   = ($remaining_cycles1 * $cycle1_min) / 60.0;
    $remaining_time2   = ($remaining_cycles2 * $cycle2_min) / 60.0;

    $originalStart1      = strtotime($order1['start_date']);
    $originalStart2      = strtotime($order2['start_date']);
    $productionStart1    = $originalStart1 + ($setupHours1 * 3600);
    $productionStart2    = $originalStart2 + ($setupHours2 * 3600);
    $production_hours1   = computeAvailableHoursBetween($productionStart1, $end1, $conn);
    $production_hours2   = computeAvailableHoursBetween($productionStart2, $end2, $conn);
    $production_non_overlap1 = max(0, $production_hours1 - $overlapAvailableHours);
    $production_non_overlap2 = max(0, $production_hours2 - $overlapAvailableHours);

    $new_total1 = $setupHours1 + $production_non_overlap1 + $alternating_time + $remaining_time1;
    $new_total2 = $setupHours2 + $production_non_overlap2 + $alternating_time + $remaining_time2;

    $newEnd1 = advanceTimestampByWorkHours($originalStart1, $new_total1, $conn);
    $newEnd2 = advanceTimestampByWorkHours($originalStart2, $new_total2, $conn);

    $remainingHours1 = generateRemainingHours($order1, $newEnd1, $conn);
    $remainingHours2 = generateRemainingHours($order2, $newEnd2, $conn);

    return [
        'order1' => ['end_date' => date('Y-m-d H:i:s', $newEnd1), 'total_hours' => $new_total1, 'remaing_hours' => $remainingHours1],
        'order2' => ['end_date' => date('Y-m-d H:i:s', $newEnd2), 'total_hours' => $new_total2, 'remaing_hours' => $remainingHours2]
    ];
}

function generateRemainingHours($order, $newEndTimestamp, $conn) {
    $setupHours = (float)$order['setup_hours'] + (float)$order['breakdown_hours'];
    $start      = strtotime($order['start_date']);
    $end        = $newEndTimestamp;
    $remainingHours = [];
    $current    = $start;
    $setupSecondsRemaining = $setupHours * 3600;

    while ($current < $end) {
        $currentDate = date('Y-m-d', $current);
        $nextDay     = strtotime('tomorrow', $current);
        $periodEnd   = min($nextDay, $end);
        $dateAvailable = 0.0;
        $shifts = getShiftsForDate($currentDate, $conn);

        if (!empty($shifts)) {
            foreach ($shifts as $shift) {
                $segmentStart = max($current, $shift['start_ts']);
                $segmentEnd   = min($periodEnd, $shift['end_ts']);
                if ($segmentEnd > $segmentStart) {
                    $segmentHours = ($segmentEnd - $segmentStart) / 3600.0;
                    if ($setupSecondsRemaining > 0) {
                        $deduct = min($segmentHours, $setupSecondsRemaining / 3600);
                        $segmentHours -= $deduct;
                        $setupSecondsRemaining -= $deduct * 3600;
                    }
                    $dateAvailable += $segmentHours;
                }
            }
            if ($dateAvailable > 0) {
                $remainingHours[$currentDate] = round($dateAvailable, 2);
            }
        }
        $current = $nextDay;
    }

    $formatted = [];
    foreach ($remainingHours as $date => $hours) {
        if ($hours > 0) $formatted[] = "$date=$hours";
    }
    return implode(',', $formatted);
}

function storeUpdatedSchedule($orderId, $data) {
    global $updatedOrders;
    $updatedOrders[$orderId] = $data;
}

function updateOrderHours($orderId, $data, $conn) {
    $total_hours   = (float)$data['total_hours'];
    $remaing_hours = $conn->real_escape_string($data['remaing_hours']);
    $sql = "UPDATE cnc_machine_table
            SET total_hours   = $total_hours,
                remaing_hours = '$remaing_hours'
            WHERE id = $orderId";
    $conn->query($sql);
}

function getShiftsForDate($date, $conn) {
    $weekdayNum = (int)date('N', strtotime($date));
    $shifts = [];
    $sql    = "SELECT shift_name, start_time, end_time FROM working_hours WHERE day_shifts = ?";
    $stmt   = $conn->prepare($sql);
    $stmt->bind_param("s", $weekdayNum);
    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        $start_ts = strtotime($date . ' ' . $row['start_time']);
        $end_ts   = strtotime($date . ' ' . $row['end_time']);
        if ($end_ts <= $start_ts) {
            $end_ts = strtotime('tomorrow ' . $row['end_time'], $start_ts);
        }
        $shifts[] = ['shift_name' => $row['shift_name'], 'start_ts' => $start_ts, 'end_ts' => $end_ts];
    }
    return $shifts;
}

function computeAvailableHoursBetween($startTs, $endTs, $conn) {
    if ($endTs <= $startTs) return 0.0;
    $totalHours = 0.0;
    $current    = $startTs;
    while ($current < $endTs) {
        $currentDate = date('Y-m-d', $current);
        $nextDay     = strtotime('tomorrow', $current);
        $periodEnd   = min($nextDay, $endTs);
        $shifts      = getShiftsForDate($currentDate, $conn);
        foreach ($shifts as $shift) {
            $segmentStart = max($current, $shift['start_ts']);
            $segmentEnd   = min($periodEnd, $shift['end_ts']);
            if ($segmentEnd > $segmentStart) {
                $totalHours += ($segmentEnd - $segmentStart) / 3600.0;
            }
        }
        $current = $nextDay;
    }
    return round($totalHours, 6);
}

function advanceTimestampByWorkHours($startTs, $hoursNeeded, $conn) {
    if ($hoursNeeded <= 0) return $startTs;
    $remainingSecondsNeeded = $hoursNeeded * 3600.0;
    $current  = $startTs;
    $maxDays  = 3650;
    $daysLooped = 0;
    while ($remainingSecondsNeeded > 0 && $daysLooped < $maxDays) {
        $currentDate = date('Y-m-d', $current);
        $nextDay     = strtotime('tomorrow', $current);
        $shifts      = getShiftsForDate($currentDate, $conn);
        if (!empty($shifts)) {
            foreach ($shifts as $shift) {
                if ($current >= $shift['end_ts']) continue;
                $segmentStart      = max($current, $shift['start_ts']);
                $availableInShift  = $shift['end_ts'] - $segmentStart;
                if ($availableInShift <= 0) continue;
                if ($availableInShift >= $remainingSecondsNeeded) {
                    return (int)($segmentStart + $remainingSecondsNeeded);
                } else {
                    $remainingSecondsNeeded -= $availableInShift;
                    $current = $shift['end_ts'];
                }
            }
        }
        $current = $nextDay;
        $daysLooped++;
    }
    return (int)$current;
}

// =============================================
// POST: Handle drag & drop priority update
// =============================================
if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'update_cnc_task_priority') {

    $taskId               = intval($_REQUEST['task_id']);
    $newMachineId         = intval($_REQUEST['new_machine_id']);
    $newTableId           = intval($_REQUEST['new_table_id']);
    $leftNeighborPriority = isset($_REQUEST['left_neighbor_priority']) ? intval($_REQUEST['left_neighbor_priority']) : null;

    $draggedTask = $conn->query("SELECT * FROM cnc_machine_table WHERE id = $taskId")->fetch_assoc();

    if (!$draggedTask) {
        ob_clean();
        echo json_encode(['status' => 'error', 'message' => 'Task not found']);
        ob_end_flush();
        exit;
    }

    $oldMachineId = (int)$draggedTask['machine_id'];
    $oldTableId   = (int)$draggedTask['table_id'];
    $today        = date('Y-m-d');

    // Step 1: Compact priorities on source machine+table
    $p   = 1;
    $res = $conn->query("
        SELECT id FROM cnc_machine_table
        WHERE machine_id = $oldMachineId
          AND table_id   = $oldTableId
          AND end_date  >= '$today'
          AND id        != $taskId
        ORDER BY priority
    ");
    while ($r = $res->fetch_assoc()) {
        $conn->query("UPDATE cnc_machine_table SET priority = $p WHERE id = {$r['id']}");
        $p++;
    }

    // Step 2: Make room on destination machine+table
    $insertPriority = ($leftNeighborPriority !== null) ? ($leftNeighborPriority + 1) : 1;
    $conn->query("
        UPDATE cnc_machine_table
        SET priority = priority + 1
        WHERE machine_id = $newMachineId
          AND table_id   = $newTableId
          AND priority  >= $insertPriority
          AND end_date  >= '$today'
    ");

    // Step 3: Move the dragged task
    $conn->query("
        UPDATE cnc_machine_table
        SET machine_id = $newMachineId,
            table_id   = $newTableId,
            priority   = $insertPriority
        WHERE id = $taskId
    ");

    // Step 4: Recalculate dates
    if ($oldMachineId != $newMachineId) {
        recalculateMachineDates_fororiginal($conn, $oldMachineId, 1);
        recalculateMachineDates_fororiginal($conn, $oldMachineId, 2);
        processMachineScheduling($oldMachineId, $conn);
        recalculateMachineDates($conn, $oldMachineId, 1);
        recalculateMachineDates($conn, $oldMachineId, 2);
    }

    recalculateMachineDates_fororiginal($conn, $newMachineId, 1);
    recalculateMachineDates_fororiginal($conn, $newMachineId, 2);
    processMachineScheduling($newMachineId, $conn);
    recalculateMachineDates($conn, $newMachineId, 1);
    recalculateMachineDates($conn, $newMachineId, 2);

    ob_clean();
    echo json_encode(['status' => 'success', 'message' => 'Task updated successfully']);
    ob_end_flush();
    exit;
}

// =============================================
// GET: Fetch paginated work order list with Live UPH
// =============================================
try {
    $current_page = isset($_GET['current_page']) ? max(1, (int)$_GET['current_page']) : 1;
    $per_page     = isset($_GET['per_page'])     ? max(1, (int)$_GET['per_page'])     : 50;
    $offset       = max(0, ($current_page - 1) * $per_page);

    $start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-d');
    $work_order = isset($_GET['work_order']) ? trim($_GET['work_order']) : '';
    $product    = isset($_GET['product'])    ? trim($_GET['product'])    : '';
    $machine_id = isset($_GET['machine'])    ? (int)$_GET['machine']    : 0;
    $priority   = isset($_GET['priority'])   ? (int)$_GET['priority']   : 0;

    // ⭐ Calculate window start using PHP
    $uphWindowMinutes = CNC_LIST_UPH_WINDOW_MINUTES;
    $windowStart = date('Y-m-d H:i:s', strtotime("-$uphWindowMinutes minutes"));

    $where_conditions = [
        "DATE(mmt.end_date) >= '$start_date'",
        "mmt.finished = 0",
        "(mmt.is_break = 0 OR mmt.is_break IS NULL)"
    ];

    if (!empty($work_order)) {
        $work_order_safe = $conn->real_escape_string($work_order);
        $where_conditions[] = "(wo.work_order_no LIKE '%$work_order_safe%' OR won.work_order_name LIKE '%$work_order_safe%')";
    }

    if (!empty($product)) {
        $product_safe = $conn->real_escape_string($product);
        $where_conditions[] = "(mmt.product_name LIKE '%$product_safe%' OR won.work_order_name LIKE '%$product_safe%')";
    }

    if ($machine_id > 0) {
        $where_conditions[] = "mmt.machine_id = $machine_id";
    }

    if ($priority > 0) {
        $where_conditions[] = "mmt.priority = $priority";
    }

    $where_clause = implode(' AND ', $where_conditions);

    // Count
    $count_query = "SELECT COUNT(DISTINCT mmt.id) as total
        FROM cnc_machine_table mmt
        LEFT JOIN work_order wo          ON mmt.work_order_id = wo.id
        LEFT JOIN work_order_name won    ON mmt.work_order_id = won.work_order_id AND mmt.part_id = won.part_id
        LEFT JOIN station_detail_table sd ON mmt.machine_id = sd.id
        WHERE $where_clause";

    $count_result = $conn->query($count_query);
    if (!$count_result) throw new Exception($conn->error);

    $total_records = $count_result->fetch_assoc()['total'];
    $total_pages   = ceil($total_records / $per_page);

    // ⭐ Main query with Live UPH subqueries
    $query = "SELECT
        mmt.machine_id AS task,
        mmt.id         AS task_id,
        mmt.table_id,
        wo.work_order_no,
        wo.require_date,
        mmt.part_id,
        mmt.setup_hours,
        mmt.priority,
        mmt.total_hours AS hours_to_schedule,
        mmt.total_hours,
        mmt.work_order_id,
        mmt.product_name,
        mmt.quantity,
        mmt.set_id,
        mmt.finished,
        won.work_order_name,
        mmt.shift_ids AS shift_name,
        DATE(mmt.start_date)  AS start_date,
        DATE(mmt.end_date)    AS end_date,
        TIME(mmt.start_date)  AS start_time,
        mmt.is_break,
        mmt.break_reason,
        mmt.is_breakdown_affected,
        mmt.breakdown_hours,
        mb.id               AS breakdown_id,
        mb.breakdown_reason AS machine_breakdown_reason,
        m.end_date          AS manufacture_end_date,
        m.description       AS manufacture_description,
        m.parent_ids,
        im.part_number      AS parent_part_number,
        sd.resource_code    AS machine_name,
        GROUP_CONCAT(DISTINCT wocr.work_order_no ORDER BY wocr.work_order_no SEPARATOR ', ') AS customer_order_nos,
        
        -- ⭐ Total OK units ever logged for this task (using manuf_id)
        COALESCE((
            SELECT SUM(cpcl.quantity)
            FROM cnc_part_count_log cpcl
            WHERE cpcl.work_order_id = wo.id
              AND cpcl.cnc_machine_table_id = mmt.manuf_id
              AND cpcl.part_id = mmt.part_id
              AND cpcl.type = 'ok'
        ), 0) AS quantity_made,
        
        -- ⭐ OK units in last 60 minutes (Live UPH)
        COALESCE((
            SELECT SUM(cpcl.quantity)
            FROM cnc_part_count_log cpcl
            WHERE cpcl.work_order_id = wo.id
              AND cpcl.cnc_machine_table_id = mmt.manuf_id
              AND cpcl.part_id = mmt.part_id
              AND cpcl.type = 'ok'
              AND cpcl.created_at >= '$windowStart'
        ), 0) AS ok_units_last_window

    FROM
        cnc_machine_table mmt
    LEFT JOIN work_order wo
        ON mmt.work_order_id = wo.id
    LEFT JOIN work_order_name won
        ON mmt.work_order_id = won.work_order_id AND mmt.part_id = won.part_id
    LEFT JOIN machine_breakdown mb
        ON mmt.machine_id = mb.machine_id AND mb.is_active = 1
    LEFT JOIN station_detail_table sd
        ON mmt.machine_id = sd.id
    LEFT JOIN (
        SELECT m1.part_id, m1.work_order_id, m1.end_date, m1.description, m1.parent_ids
        FROM manufacture_machine_table m1
        INNER JOIN (
            SELECT part_id, work_order_id, MAX(end_date) AS max_end_date
            FROM manufacture_machine_table
            GROUP BY part_id, work_order_id
        ) m2 ON m1.part_id = m2.part_id
            AND m1.work_order_id = m2.work_order_id
            AND m1.end_date = m2.max_end_date
    ) m ON mmt.part_id = m.part_id AND mmt.work_order_id = m.work_order_id
    LEFT JOIN item_management im
        ON m.parent_ids = im.id
    LEFT JOIN work_order_customer_ref_mapping wocr
        ON mmt.work_order_id = wocr.work_order_id
    WHERE $where_clause
    GROUP BY
        mmt.id, mmt.machine_id, mmt.table_id, wo.work_order_no, wo.require_date,
        mmt.part_id, mmt.setup_hours, mmt.priority, mmt.total_hours, mmt.work_order_id,
        mmt.product_name, mmt.quantity, mmt.set_id, mmt.finished, won.work_order_name,
        mmt.shift_ids, mmt.start_date, mmt.end_date, mmt.is_break, mmt.break_reason,
        mmt.is_breakdown_affected, mmt.breakdown_hours, mb.id, mb.breakdown_reason,
        m.end_date, m.description, im.part_number, sd.resource_code
    ORDER BY
        mmt.priority, mmt.machine_id, mmt.table_id
    LIMIT $per_page OFFSET $offset";

    $result = $conn->query($query);
    if (!$result) throw new Exception($conn->error);

    $work_orders = [];
    while ($row = $result->fetch_assoc()) {
        $part_id = intval($row['part_id']);

        // Bought-in materials (product_group = 17) — recursive BOM walk
        $row['bought_in_materials'] = getAllBoughtInMaterials($conn, $part_id);

        // Inventory dots: one per BOM sub-part → in stock / out of stock
        $invDots = [];
        if ($part_id > 0) {
            $invResult = $conn->query("
                SELECT im.part_number,
                       COALESCE(i.quantity_in_stock, 0) AS quantity_in_stock
                FROM bom b
                LEFT JOIN inventory i          ON b.sub_part_id = i.item_id
                LEFT JOIN item_management im   ON b.sub_part_id = im.id
                WHERE b.main_product_id = $part_id
            ");
            if ($invResult) {
                while ($inv = $invResult->fetch_assoc()) {
                    $invDots[] = [
                        'part_number' => $inv['part_number'],
                        'in_stock'    => (float)$inv['quantity_in_stock'] > 0
                    ];
                }
            }
        }
        $row['inventory_dots'] = $invDots;

        // ⭐ Calculate Planned UPH
        $plannedUph = ((float)$row['total_hours'] > 0)
            ? round((float)$row['quantity'] / (float)$row['total_hours'], 2)
            : 0;

        // ⭐ Calculate Live UPH
        $liveUph = round((float)($row['ok_units_last_window'] ?? 0) / ($uphWindowMinutes / 60), 2);

        // ⭐ Determine if Live UPH should be grey (no active production)
        $isFinished = (int)$row['finished'] == 1;
        $hasProductionStarted = (int)$row['quantity_made'] > 0;
        $isGrey = $isFinished || !$hasProductionStarted;

        $row['planned_uph']  = $plannedUph;
        $row['live_uph']     = $liveUph;
        $row['is_uph_green'] = (!$isGrey && $plannedUph > 0 && $liveUph >= $plannedUph);
        $row['is_uph_grey']  = $isGrey;

        $work_orders[] = $row;
    }

    ob_clean();
    echo json_encode([
        'success' => true,
        'data'    => $work_orders,
        'pagination' => [
            'current_page'  => $current_page,
            'per_page'      => $per_page,
            'total_records' => $total_records,
            'total_pages'   => $total_pages,
            'offset'        => $offset,
            'showing_from'  => $offset + 1,
            'showing_to'    => min($offset + $per_page, $total_records)
        ]
    ]);

} catch (Exception $e) {
    ob_clean();
    echo json_encode(['success' => false, 'error' => $e->getMessage()]);
}

ob_end_flush();
?>