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

header('Content-Type: application/json');
error_reporting(E_ALL);
ini_set('display_errors',1);


function scheduleNewTaskInline($conn, $machineId, $tableId, $hoursToSchedule, $shiftsByDay) {
    $currentDateTime = strtotime(date('Y-m-d') . ' 00:00:00');
    $dateHoursMap = [];
    $taskStartTime = null;
    $taskEndTime = null;
    $firstShiftUsed = null;

    $hoursRemaining = $hoursToSchedule;

    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, ',');

    return [
        'start_date'      => date('Y-m-d H:i:s', $taskStartTime),
        'end_date'        => date('Y-m-d H:i:s', $taskEndTime),
        'shift_ids'       => $firstShiftUsed,
        'remaining_hours' => $remainingHoursStr
    ];
}


function scheduleNewTaskInlineoriginal($conn, $machineId, $tableId, $hoursToSchedule, $shiftsByDay) {
    $currentDateTime = strtotime(date('Y-m-d') . ' 00:00:00');
    $dateHoursMap = [];
    $taskStartTime = null;
    $taskEndTime = null;
    $firstShiftUsed = null;

    $hoursRemaining = $hoursToSchedule;

    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, ',');

    return [
        'start_date'      => date('Y-m-d H:i:s', $taskStartTime),
        'end_date'        => date('Y-m-d H:i:s', $taskEndTime),
        'shift_ids'       => $firstShiftUsed,
        'remaining_hours' => $remainingHoursStr
    ];
}
#########################################################################################################################################

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;
    }

    // Get current date
    $currentDateStr = date('Y-m-d');
    $tasks = [];

    // Get 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;
    }

    // Get current date
    $currentDateStr = date('Y-m-d');
    $tasks = [];

    // Get 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);



    }
}
/////////////////////////////////////////////////////////////  Overlapping cnc Functions Start /////////////////////////////////////

$updatedOrders = [];

function displayOrder($order) {
    // Removed display output
}

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);
    
    // First, update remaing_hours for all orders (using shifts)
    foreach ($orders as $order) {
        $orderId = $order['id'];
        $originalHours = (float)$order['original_total_hours'];
        $remainingHours = generateRemainingHours($order, strtotime($order['end_date']), $conn); 
        
        // Store in updatedOrders for consistency
        $updatedOrders[$orderId] = [
            'end_date' => $order['end_date'],
            'total_hours' => $originalHours,
            'remaing_hours' => $remainingHours
        ];
        
        // Update database (keeps existing behaviour)
        $sql = "UPDATE cnc_machine_table 
                SET total_hours = $originalHours, 
                    remaing_hours = '$remainingHours'
                WHERE id = $orderId";
        $conn->query($sql);
    }
    
    // If less than 2 orders, no need to check for overlaps
    if (count($orders) < 2) {
        return;
    }
    
    $potentialOverlaps = findPotentialOverlaps($orders);
    
    // If no overlaps found, we're done (already updated remaing_hours above)
    if (empty($potentialOverlaps)) {
        return;
    }
    
    // Process overlaps
    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];
            
            // Apply setup hours offset to start times
            //$setupHours1 = (float)$order1['setup_hours'];
			$setupHours1 = (float)$order1['setup_hours'] + (float)$order1['breakdown_hours'];

            //$setupHours2 = (float)$order2['setup_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) {
    // Apply setup hours offset to start times
    //$setupHours1 = (float)$order1['setup_hours'];
	$setupHours1 = (float)$order1['setup_hours'] + (float)$order1['breakdown_hours'];
	
   // $setupHours2 = (float)$order2['setup_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) {
        // no overlap: keep original
        $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
            ]
        ];
    }

    // Compute available shift-hours (in hours) in the overlap window (PRODUCTION ONLY, no setup/breakdown)
    $overlapAvailableHours = computeAvailableHoursBetween($overlapStart, $overlapEnd, $conn);

    // cycles (in minutes) for both orders
    $cycle1_min = (float)$order1['cycle'];
    $cycle2_min = (float)$order2['cycle'];

    // How many cycles fit into the overlap available hours
    $total_cycles1 = floor(($overlapAvailableHours * 60) / max(0.0001, $cycle1_min)); // protect /0
    $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; // in hours

    $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;

    // Get production-only hours (EXCLUDING setup/breakdown) from original schedule
    $originalStart1 = strtotime($order1['start_date']);
    $originalStart2 = strtotime($order2['start_date']);
    
    // Production work starts AFTER setup/breakdown
    $productionStart1 = $originalStart1 + ($setupHours1 * 3600);
    $productionStart2 = $originalStart2 + ($setupHours2 * 3600);
    
    // Calculate production-only hours (from after setup to end)
    $production_hours1 = computeAvailableHoursBetween($productionStart1, $end1, $conn);
    $production_hours2 = computeAvailableHoursBetween($productionStart2, $end2, $conn);

    // Non-overlap production hours (production work outside the overlap window)
    $production_non_overlap1 = max(0, $production_hours1 - $overlapAvailableHours);
    $production_non_overlap2 = max(0, $production_hours2 - $overlapAvailableHours);

    // New total required working hours = setup + non-overlap production + alternating + remaining
    // Setup hours are separate and happen first, then production work
    $new_total1 = $setupHours1 + $production_non_overlap1 + $alternating_time + $remaining_time1;
    $new_total2 = $setupHours2 + $production_non_overlap2 + $alternating_time + $remaining_time2;

    // Find new calendar end timestamps by advancing through shifts from original start
    $newEnd1 = advanceTimestampByWorkHours($originalStart1, $new_total1, $conn);
    $newEnd2 = advanceTimestampByWorkHours($originalStart2, $new_total2, $conn);

    // Generate per-date remaining-hours strings (respecting shifts)
    $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
        ]
    ];
}

/**
 * Generate remaining hours per date based on shifts between order start and provided new end timestamp.
 * Returns string like: "2025-01-09=8,2025-01-10=4"
 */
function generateRemainingHours($order, $newEndTimestamp, $conn) {
   // $setupHours = (float)$order['setup_hours'];
	$setupHours = (float)$order['setup_hours'] + (float)$order['breakdown_hours'];
    $start = strtotime($order['start_date']);
    $end = $newEndTimestamp;
    $remainingHours = [];
    $current = $start;

    // Handle setup hours - first period might be partially consumed by setup
    $setupSecondsRemaining = $setupHours * 3600;
    
    // iterate date by date
    while ($current < $end) {
        $currentDate = date('Y-m-d', $current);
        $dayStartTs = strtotime($currentDate . ' 00:00:00');
        $nextDay = strtotime('tomorrow', $current);
        $periodEnd = min($nextDay, $end);

        // For this date, compute total available shift-hours overlapping [current, periodEnd)
        $dateAvailable = 0.0;
        $shifts = getShiftsForDate($currentDate, $conn);
        if (!empty($shifts)) {
            foreach ($shifts as $shift) {
                $shiftStart = $shift['start_ts'];
                $shiftEnd = $shift['end_ts'];

                // Clamp to current -> periodEnd
                $segmentStart = max($current, $shiftStart);
                $segmentEnd = min($periodEnd, $shiftEnd);
                if ($segmentEnd > $segmentStart) {
                    $segmentHours = ($segmentEnd - $segmentStart) / 3600.0;
                    
                    // Deduct setup hours from the first available segment
                    if ($setupSecondsRemaining > 0) {
                        $setupHoursToDeduct = min($segmentHours, $setupSecondsRemaining / 3600);
                        $segmentHours -= $setupHoursToDeduct;
                        $setupSecondsRemaining -= $setupHoursToDeduct * 3600;
                    }
                    
                    $dateAvailable += $segmentHours;
                }
            }
            // round to 2 decimals like before
            if ($dateAvailable > 0) {
                $remainingHours[$currentDate] = round($dateAvailable, 2);
            }
        } else {
            // No shift defined for this date: treat as 0 available hours
            $remainingHours[$currentDate] = 0.00;
        }

        $current = $nextDay;
    }

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

    return implode(',', $formatted);
}

/**
 * Store updated schedule in the in-memory map (no DB effect here).
 */
function storeUpdatedSchedule($orderId, $data) {
    global $updatedOrders;
    $updatedOrders[$orderId] = $data;
}

/**
 * Update order hours in DB (keeps existing behaviour)
 */
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);
}

/* ---------------------------
   Helper functions for shift handling
   --------------------------- */

/**
 * Returns an array of shifts for the given date.
 * Each shift is an associative array with keys 'start_ts' and 'end_ts' (timestamps).
 *
 * Assumptions:
 * - working_hours.day_shifts stores a weekday number (1 = Monday, 2 = Tuesday, ... 7 = Sunday).
 * - If end_time <= start_time, it's treated as an overnight shift ending on the next day.
 * - If no shifts for that weekday, returns empty array.
 */
function getShiftsForDate($date, $conn) {
    // Determine weekday number for the date: 1 (Mon) - 7 (Sun)
    $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_time_str = $row['start_time']; // HH:MM:SS
        $end_time_str = $row['end_time'];

        $start_ts = strtotime($date . ' ' . $start_time_str);
        $end_ts = strtotime($date . ' ' . $end_time_str);

        // If end is less-than-or-equal to start, treat as overnight into next day
        if ($end_ts <= $start_ts) {
            $end_ts = strtotime('tomorrow ' . $end_time_str, $start_ts);
        }

        $shifts[] = [
            'shift_name' => $row['shift_name'],
            'start_ts' => $start_ts,
            'end_ts' => $end_ts
        ];
    }
    return $shifts;
}

/**
 * Compute total available shift-hours (in hours, float) between two timestamps (start exclusive/inclusive consistent with prior code).
 * It sums overlap of provided interval with all shifts on each day.
 */
function computeAvailableHoursBetween($startTs, $endTs, $conn) {
    if ($endTs <= $startTs) return 0.0;

    $totalHours = 0.0;
    $current = $startTs;

    // iterate date by date
    while ($current < $endTs) {
        $currentDate = date('Y-m-d', $current);
        $dayStartTs = strtotime($currentDate . ' 00:00:00');
        $nextDay = strtotime('tomorrow', $current);
        $periodEnd = min($nextDay, $endTs);

        $shifts = getShiftsForDate($currentDate, $conn);
        if (!empty($shifts)) {
            foreach ($shifts as $shift) {
                $shiftStart = $shift['start_ts'];
                $shiftEnd = $shift['end_ts'];

                $segmentStart = max($current, $shiftStart);
                $segmentEnd = min($periodEnd, $shiftEnd);
                if ($segmentEnd > $segmentStart) {
                    $totalHours += ($segmentEnd - $segmentStart) / 3600.0;
                }
            }
        } else {
            // No shifts: add zero (honor shift system)
        }

        $current = $nextDay;
    }

    return round($totalHours, 6); // keep some precision
}

/**
 * Advance from a start timestamp through shift periods until the specified number of working hours
 * have been consumed. Returns the calendar timestamp where the work completes.
 *
 * If shifts are sparse and you cannot complete hours (no shifts found at all in future days),
 * this will continue scanning days forward indefinitely until hours are filled — BE SURE your working_hours
 * contain the appropriate future shifts for this to finish. (This matches expected scheduler behaviour.)
 */
function advanceTimestampByWorkHours($startTs, $hoursNeeded, $conn) {
    if ($hoursNeeded <= 0) return $startTs;

    $remainingSecondsNeeded = $hoursNeeded * 3600.0;
    $current = $startTs;

    // We'll loop day by day until we've consumed required seconds.
    // Safety: avoid infinite loops by setting a high day limit (e.g., 3650 days ~ 10 years).
    $maxDays = 3650;
    $daysLooped = 0;

    while ($remainingSecondsNeeded > 0 && $daysLooped < $maxDays) {
        $currentDate = date('Y-m-d', $current);
        $dayStartTs = strtotime($currentDate . ' 00:00:00');
        $nextDay = strtotime('tomorrow', $current);

        $shifts = getShiftsForDate($currentDate, $conn);

        if (!empty($shifts)) {
            // For each shift in that date, we attempt to take available seconds
            foreach ($shifts as $shift) {
                $shiftStart = $shift['start_ts'];
                $shiftEnd = $shift['end_ts'];

                // If current is after shift end, skip
                if ($current >= $shiftEnd) continue;

                // Determine where we can start within this shift
                $segmentStart = max($current, $shiftStart);
                $availableInShift = $shiftEnd - $segmentStart;
                if ($availableInShift <= 0) continue;

                if ($availableInShift >= $remainingSecondsNeeded) {
                    // we finish inside this shift
                    $finishTs = $segmentStart + $remainingSecondsNeeded;
                    return (int)$finishTs;
                } else {
                    // consume the whole availableInShift and continue
                    $remainingSecondsNeeded -= $availableInShift;
                    $current = $shiftEnd; // move to end of this shift
                }
            }
        }

        // Move to next day start
        $current = $nextDay;
        $daysLooped++;
    }

    // If we've hit the day limit or couldn't find enough shifts, we return current (best-effort).
    return (int)$current;
}
//////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
function applyMachineBreakdown($conn, $machineId) {
    // 1️⃣ Check for active breakdown
    $breakdownQuery = "
        SELECT start_time, COALESCE(end_time, NOW()) AS end_time
        FROM machine_breakdown_log
        WHERE machine_id = $machineId
          AND (end_time IS NULL OR end_time >= NOW())
        ORDER BY start_time DESC LIMIT 1
    ";
    $breakdownResult = mysqli_query($conn, $breakdownQuery);
    if (!$breakdownResult || mysqli_num_rows($breakdownResult) === 0) {
        // No breakdown → reset affected tasks if needed
        mysqli_query($conn, "
            UPDATE vac_machine_table 
            SET breakdown_hours = 0 
            WHERE machine_id = $machineId
        ");
        return;
    }

    $breakdown = mysqli_fetch_assoc($breakdownResult);
    $breakdownStart = strtotime($breakdown['start_time']);
    $breakdownEnd = strtotime($breakdown['end_time']);
    $breakdownHours = ($breakdownEnd - $breakdownStart) / 3600.0;
    if ($breakdownHours < 0) $breakdownHours = 0;

    // 2️⃣ Find tasks affected by this breakdown
    $taskQuery = "
        SELECT id, start_date, end_date, total_hours, total_hours_update
        FROM vac_machine_table
        WHERE machine_id = $machineId
          AND start_date <= '{$breakdown['end_time']}'
          AND end_date >= '{$breakdown['start_time']}'
    ";
    $taskResult = mysqli_query($conn, $taskQuery);

    while ($task = mysqli_fetch_assoc($taskResult)) {
        $id = $task['id'];
        $newTotalHours = $task['total_hours'] + $breakdownHours;

        mysqli_query($conn, "
            UPDATE vac_machine_table
            SET 
                breakdown_hours = $breakdownHours,
                total_hours = $newTotalHours,
                total_hours_update = $newTotalHours
            WHERE id = $id
        ");
    }
}

/////////////////////////////////////////////////////////////  Overlapping cnc Functions Ends /////////////////////////////////////

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

function swapPriorityWithDualTask($conn, $machineId, $tableId, $dualPriority) {

    // Find the alignment break (latest inserted)
    $breakQuery = mysqli_query($conn, "
        SELECT id, priority 
        FROM cnc_machine_table
        WHERE machine_id = $machineId
        AND table_id = $tableId
        AND is_break = 1
        AND break_reason = 'Table alignment break'
        ORDER BY id DESC
        LIMIT 1
    ");

    if(!$breakQuery || mysqli_num_rows($breakQuery) == 0){
        return;
    }

    $breakRow = mysqli_fetch_assoc($breakQuery);
    $breakId = $breakRow['id'];
    $breakPriority = $breakRow['priority'];

    // Swap priorities
    mysqli_query($conn,"
        UPDATE cnc_machine_table
        SET priority = $breakPriority
        WHERE machine_id = $machineId
        AND table_id = $tableId
        AND priority = $dualPriority
    ");

    mysqli_query($conn,"
        UPDATE cnc_machine_table
        SET priority = $dualPriority
        WHERE id = $breakId
    ");
}
function getLastEndDateForTable($conn, $machineId, $tableId) {

    $q = mysqli_query($conn, "
        SELECT MAX(end_date) AS last_end
        FROM cnc_machine_table
        WHERE machine_id = $machineId
        AND table_id = $tableId
    ");

    $r = mysqli_fetch_assoc($q);

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

    // If no record or end_date is older than today, start from today
    if (!$r['last_end'] || $r['last_end'] < $today) {
        return $today;
    }

    return $r['last_end'];
}

function alignTablesBeforeDual($conn, $machineId, $parentTaskId) {
    $end1 = getLastEndDateForTable($conn, $machineId, 1);
    $end2 = getLastEndDateForTable($conn, $machineId, 2);

    $t1 = strtotime($end1);
    $t2 = strtotime($end2);

    // Add a small tolerance (1 second) to avoid floating-point/timestamp drift
    if (abs($t1 - $t2) <= 1) return;

    if ($t1 < $t2) {
        createShiftAwareGapBreak($conn, $machineId, 1, $end1, $end2, $parentTaskId);
    } else {
        createShiftAwareGapBreak($conn, $machineId, 2, $end2, $end1, $parentTaskId);
    }
}

function createShiftAwareGapBreak($conn, $machineId, $tableId, $start, $end, $parentTaskId) {
    $startTs = strtotime($start);
    $endTs   = strtotime($end);

    if ($endTs <= $startTs) return;

    // Use working hours within the gap as the task's hour value
    // so the scheduler correctly accounts for it in remaining_hours
    $hours = computeAvailableHoursBetween($startTs, $endTs, $conn);

    if ($hours <= 0) return;

    // Use the ACTUAL $end (not re-advanced), so the break
    // blocks the calendar up to exactly when the leading table ends
    $actualEnd = $end; // Keep original end — don't re-advance

    $priorityQuery = mysqli_query($conn, "
        SELECT MAX(priority) AS p
        FROM cnc_machine_table
        WHERE machine_id = $machineId
        AND table_id = $tableId
    ");

    $pRow     = mysqli_fetch_assoc($priorityQuery);
    $priority = ($pRow['p'] ?? 0) + 1;
    $reason   = "Table alignment break";

    mysqli_query($conn, "
        INSERT INTO cnc_machine_table
        (machine_id, table_id, start_date, end_date,
         original_start_date, original_end_date,
         total_hours, original_total_hours,
         is_break, break_reason, priority, parent_task_id)
        VALUES
        ($machineId, $tableId,
         '$start', '$actualEnd',
         '$start', '$actualEnd',
         $hours, $hours,
         1, '$reason', $priority, $parentTaskId)
    ");
}
function shouldOccupyBothTables($conn, $partId, $totalHours) {
    $query = "SELECT use_both_tables FROM item_management WHERE id = $partId LIMIT 1";
    $result = mysqli_query($conn, $query);

    if ($result && mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_assoc($result);
        return (bool)$row['use_both_tables'];
    }

    return false;
}


/**
 * Fast: Create simple break on opposite table
 */
function createSimpleBreak(
    $conn,
    $machineId,
    $oppositeTableId,
    $startDate,
    $endDate,
    $totalHours,
    $priority,
    $parentTaskId
) {

    $breakReason = "Work order occupy both tables";
    
    $insertQuery = "INSERT INTO cnc_machine_table 
                   (machine_id, table_id, start_date, end_date,
                    original_start_date, original_end_date,
                    total_hours, original_total_hours,
                    is_break, break_reason,
                    priority, is_dual_table, parent_task_id)
                   VALUES ($machineId, $oppositeTableId,
                           '$startDate', '$endDate',
                           '$startDate', '$endDate',
                           $totalHours, $totalHours,
                           1, '$breakReason',
                           $priority, 1, $parentTaskId)";

    return mysqli_query($conn, $insertQuery);
}

/**
 * Fast: Delete all dual-table breaks for a machine/opposite-table
 */
function deleteDualTableBreaks($conn, $machineId, $tableId) {
    // Delete breaks on opposite table that are dual-table breaks
    $oppositeTableId = ($tableId == 1) ? 2 : 1;
    
    mysqli_query($conn, "
        DELETE FROM cnc_machine_table 
        WHERE machine_id = $machineId 
          AND table_id = $oppositeTableId 
          AND is_break = 1 
          AND is_dual_table = 1
    ");
}
/**
 * Create dual-table break EXACTLY at the same priority as main task
 */
function createSimpleBreakAtPriority(
    $conn,
    $machineId,
    $oppositeTableId,
    $startDate,
    $endDate,
    $totalHours,
    $priority,
    $parentTaskId
) {

    mysqli_query($conn, "
        UPDATE cnc_machine_table
        SET priority = priority + 1
        WHERE machine_id = $machineId
        AND table_id = $oppositeTableId
        AND priority >= $priority
    ");

    $breakReason = "Work order occupy both tables";

    mysqli_query($conn,"
        INSERT INTO cnc_machine_table
        (
            machine_id,
            table_id,
            start_date,
            end_date,
            original_start_date,
            original_end_date,
            total_hours,
            original_total_hours,
            is_break,
            break_reason,
            priority,
            is_dual_table,
            parent_task_id
        )
        VALUES
        (
            $machineId,
            $oppositeTableId,
            '$startDate',
            '$endDate',
            '$startDate',
            '$endDate',
            $totalHours,
            $totalHours,
            1,
            '$breakReason',
            $priority,
            1,
            $parentTaskId
        )
    ");
}


if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'select_order_for_cnc') {
    $json_data = $_REQUEST['work_orders'];
    $work_orders = json_decode($json_data, true);

    $inserted = false;
    $processed_machines = [];

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

    foreach ($work_orders as $item) {
        $id = (int)$item['id'];
        $work_order_id = (int)$item['work_order_id'];
        $part_id = (int)$item['part_id'];
        $machine_id = (int)$item['machine_id'];
        $table_id = (int)$item['table_id'];

        $processed_machines[$machine_id] = true;

        $selectQuery = "SELECT part_id, end_date, quantity, product_name, total_hours, set_id 
                        FROM manufacture_machine_table 
                        WHERE id = $id";
        $result = $conn->query($selectQuery);

        if ($result && $result->num_rows > 0) {
            $row = $result->fetch_assoc();

            $quantity     = (int)$row['quantity'];
            $product_name = $row['product_name'];
            $total_hoursss  = (float)$row['total_hours'];
            $set_id       = $row['set_id'];

            // Get route details
            if ($set_id > 0) {
                $query_route = "SELECT operation_time, set_time, parts_per_operation 
                                FROM set_route_details WHERE set_id = $set_id";
            } else {
                $query_route = "SELECT operation_time, set_time, parts_per_operation 
                                FROM route_details WHERE item_id = $part_id";
            }
            $result_route = $conn->query($query_route);

            if ($result_route && $result_route->num_rows > 0) {
                $row_route = $result_route->fetch_assoc();
                $set_time = $row_route['set_time'];
                
                list($hours, $minutes, $seconds) = explode(':', $set_time);
                $setup_hours = $hours + ($minutes / 60) + ($seconds / 3600);

                if (!empty($row_route['operation_time'])) {
                    list($h, $m, $s) = explode(":", $row_route['operation_time']);
                    $operation_time = ($h * 60) + $m + ($s / 60);
                } else {
                    $operation_time = 1;
                }

                $parts_per_operation = (!empty($row_route['parts_per_operation']) && $row_route['parts_per_operation'] > 0) 
                    ? (float)$row_route['parts_per_operation'] 
                    : 1;

                $cycle = (int) round($operation_time / $parts_per_operation);
            } else {
                $cycle = 1;
                $setup_hours = 0;
            }
            
            $total_hours = $total_hoursss + $setup_hours;

            // Check if dual-table using database flag with fallback
            $isDualTable = shouldOccupyBothTables($conn, $part_id, $total_hours);

            // Get priority
            $query_priority = "SELECT MAX(priority) AS priority FROM cnc_machine_table 
                               WHERE machine_id = $machine_id AND table_id = $table_id";
            $result_priority = mysqli_query($conn, $query_priority);
            if ($result_priority && mysqli_num_rows($result_priority) > 0) {
                $rowp = mysqli_fetch_assoc($result_priority);
                $new_priority = ($rowp['priority'] ?? 0) + 1;
            } else {
                $new_priority = 1;
            }

            // Schedule
            $scheduleData = scheduleNewTaskInline($conn, $machine_id, $table_id, $total_hours, $shiftsByDay);

            $startStr = $scheduleData['start_date'];
            $endStr   = $scheduleData['end_date'];
            $firstShiftUsed = $scheduleData['shift_ids'];
            $remainingHoursStr = $scheduleData['remaining_hours'];

            // Insert main task
            $insertQuery = "
                INSERT INTO cnc_machine_table 
                (machine_id, table_id, work_order_id, part_id, end_date, start_date, quantity, shift_ids, 
                 product_name, total_hours, total_hours_update, cycle, set_id, priority, remaing_hours, 
                 original_end_date, original_start_date, setup_hours, original_total_hours, is_dual_table)
                VALUES (
                    $machine_id, $table_id, $work_order_id, $part_id, '$endStr', '$startStr', $quantity,
                    '$firstShiftUsed', '$product_name', '$total_hours', '$total_hoursss', '$cycle', '$set_id',
                    '$new_priority', '$remainingHoursStr', '$endStr', '$startStr', '$setup_hours',
                    '$total_hours', " . ($isDualTable ? "1" : "0") . "
                )";

            if ($conn->query($insertQuery)) {
				$mainTaskId = $conn->insert_id;
                $inserted = true;
                
                // If dual-table, create break on opposite table
    if ($isDualTable) {
        // ✅ Align AFTER main task is inserted so MAX(end_date) is current
        alignTablesBeforeDual($conn, $machine_id, $mainTaskId);

        $oppositeTableId = ($table_id == 1) ? 2 : 1;
        createSimpleBreak(
            $conn,
            $machine_id,
            $oppositeTableId,
            $startStr,
            $endStr,
            $total_hours,
            $new_priority,
            $mainTaskId
        );
        swapPriorityWithDualTask($conn, $machine_id, $table_id, $new_priority);
    }              
                $updateQuery = "UPDATE manufacture_machine_table SET in_scheduler_check = 1 WHERE id = $id";
                $conn->query($updateQuery);
            }
        }
    }

    // Recalculate (existing code - unchanged)
    foreach (array_keys($processed_machines) as $machine_id) {
        recalculateMachineDates_fororiginal($conn, $machine_id, 1);
        recalculateMachineDates_fororiginal($conn, $machine_id, 2);
        processMachineScheduling($machine_id, $conn);
        recalculateMachineDates($conn, $machine_id, 2);
        recalculateMachineDates($conn, $machine_id, 1);
    }

    if ($inserted) {
        echo json_encode(['status' => 'success']);
    } else {
        echo json_encode(['status' => 'failed', 'message' => 'No rows inserted']);
    }
}

else 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 = $_REQUEST['left_neighbor_priority'] ?? null;

    // Fetch dragged task
    $draggedTask = mysqli_fetch_assoc(
        mysqli_query($conn, "SELECT * FROM cnc_machine_table WHERE id = $taskId")
    );

    $isDualTable = (bool)$draggedTask['is_dual_table'];
    $oldMachineId = $draggedTask['machine_id'];
    $oldTableId   = $draggedTask['table_id'];

    $today = date('Y-m-d');


    $p = 1;
    $res = mysqli_query($conn, "
        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 = mysqli_fetch_assoc($res)) {
        mysqli_query($conn, "UPDATE cnc_machine_table SET priority = $p WHERE id = {$r['id']}");
        $p++;
    }


    $insertPriority = ($leftNeighborPriority !== null)
        ? ((int)$leftNeighborPriority + 1)
        : 1;

    mysqli_query($conn, "
        UPDATE cnc_machine_table
        SET priority = priority + 1
        WHERE machine_id = $newMachineId
          AND table_id = $newTableId
          AND priority >= $insertPriority
    ");


    mysqli_query($conn, "
        UPDATE cnc_machine_table SET
            machine_id = $newMachineId,
            table_id   = $newTableId,
            priority   = $insertPriority
        WHERE id = $taskId
    ");


if ($isDualTable) {

    // 1️⃣ Delete old breaks linked to this task
    mysqli_query($conn,"
        DELETE FROM cnc_machine_table
        WHERE parent_task_id = $taskId
    ");

    // 2️⃣ Align tables
    alignTablesBeforeDual($conn, $newMachineId, $taskId);

    // 3️⃣ Create new break on opposite table
    $newOppositeTableId = ($newTableId == 1) ? 2 : 1;

    createSimpleBreakAtPriority(
        $conn,
        $newMachineId,
        $newOppositeTableId,
        $draggedTask['start_date'],
        $draggedTask['end_date'],
        $draggedTask['total_hours'],
        $insertPriority,
        $taskId
    );

    // 4️⃣ Swap priority with alignment break
    swapPriorityWithDualTask($conn, $newMachineId, $newTableId, $insertPriority);
}

 
    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);

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

else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'delete_cnc_task') {
    $task_id = mysqli_real_escape_string($conn, $_REQUEST['task_id']);
    $work_order_id = mysqli_real_escape_string($conn, $_REQUEST['work_order_id']);
    $part_id = mysqli_real_escape_string($conn, $_REQUEST['part_id']);
    
    // Get task info
    $taskQuery = "SELECT machine_id, table_id, is_dual_table 
                  FROM cnc_machine_table WHERE id = '$task_id' LIMIT 1";
    $taskResult = mysqli_query($conn, $taskQuery);
    $taskData = mysqli_fetch_assoc($taskResult);
    
    $machine_id = $taskData['machine_id'];
    $table_id = $taskData['table_id'];
    $isDualTable = (bool)$taskData['is_dual_table'];
    
    // If dual-table, delete breaks on opposite table
   if ($isDualTable) {

    mysqli_query($conn,"
        DELETE FROM cnc_machine_table
        WHERE parent_task_id = $task_id
    ");

}
    
    // Delete main task
    $delete_query = "DELETE FROM cnc_machine_table WHERE id = '$task_id'";
    
    if (mysqli_query($conn, $delete_query)) {
        $updateQuery = "UPDATE manufacture_machine_table 
                        SET in_scheduler_check = 0 
                        WHERE work_order_id = '$work_order_id'
                          AND part_id = '$part_id'";
        $conn->query($updateQuery);
        
        recalculateMachineDates_fororiginal($conn, $machine_id, 1);
        recalculateMachineDates_fororiginal($conn, $machine_id, 2);
        processMachineScheduling($machine_id, $conn);
        recalculateMachineDates($conn, $machine_id, 1);
        recalculateMachineDates($conn, $machine_id, 2);
        
        echo json_encode([
            'success' => true,
            'message' => 'Work order deleted successfully'
        ]);
    } else {
        echo json_encode([
            'success' => false,
            'message' => 'Failed to delete work order: ' . mysqli_error($conn)
        ]);
    }
    exit;
}

else if(isset($_REQUEST['action']) && $_REQUEST['action']=='add_cnc_break'){
    $machineId = intval($_REQUEST['machine_id']);
    $tableId = intval($_REQUEST['table_id']);
    $duration = floatval($_REQUEST['duration']);
    $reason = mysqli_real_escape_string($conn, $_REQUEST['reason']);
    $highestPriorityTaskId = isset($_REQUEST['highest_priority_task_id']) ? intval($_REQUEST['highest_priority_task_id']) : 0;
    $highestPriority = isset($_REQUEST['highest_priority']) ? intval($_REQUEST['highest_priority']) : 0;
    
    // Validate inputs
    if ($machineId <= 0 || $tableId <= 0 || $duration < 0.5 || $duration > 10) {
        echo json_encode(['status' => 'error', 'message' => 'Invalid input values']);
        exit;
    }
    
    // Start transaction
    mysqli_autocommit($conn, false);
    $error = false;
    
    // Get current schedule for the machine/table
    $query = "SELECT MAX(end_date) as last_end_date FROM cnc_machine_table 
             WHERE machine_id = $machineId AND table_id = $tableId";
    $result = mysqli_query($conn, $query);
	
	$query_priority = "SELECT MAX(priority) AS priority FROM cnc_machine_table 
                   WHERE machine_id = $machineId AND table_id = $tableId";
    $result_priority = mysqli_query($conn, $query_priority);

    if ($result_priority && mysqli_num_rows($result_priority) > 0) {
        $row = mysqli_fetch_assoc($result_priority);
        $currentPriority = $row['priority'] ?? 0;
        $new_priority = $currentPriority + 1;
    } else {
        // No rows found, start from 1
        $new_priority = 1;
    }
    
    if (!$result) {
        $error = true;
    } else {
        $row = mysqli_fetch_assoc($result);
        $startDate = $row['last_end_date'] ? $row['last_end_date'] : date('Y-m-d H:i:s');
        $endDate = date('Y-m-d H:i:s', strtotime($startDate) + ($duration * 3600));
        
        // If break spans multiple days, calculate remaining hours
        $startDay = date('Y-m-d', strtotime($startDate));
        $endDay = date('Y-m-d', strtotime($endDate));
        
        $remainingHours = $duration;
        if ($startDay != $endDay) {
            // Break spans multiple days
            $endOfFirstDay = date('Y-m-d 23:59:59', strtotime($startDate));
            $firstDayHours = (strtotime($endOfFirstDay) - strtotime($startDate)) / 3600;
            $remainingHours = $duration - $firstDayHours;
        }
        
        // Insert break as a special task
        $priority = $highestPriority ? $highestPriority - 1 : 1; // Set higher priority than existing tasks
        $insertQuery = "INSERT INTO cnc_machine_table 
                       (machine_id, table_id, start_date, end_date, original_end_date, total_hours, original_total_hours, remaing_hours, 
                        is_break, break_reason, priority)
                       VALUES ($machineId, $tableId, '$startDate', '$endDate', '$endDate', 
                               $duration, $duration, $remainingHours, 1, '$reason', $new_priority)";
        
        if (!mysqli_query($conn, $insertQuery)) {
            $error = true;
        }
        
        // Update priorities of existing tasks if needed
        if ($highestPriorityTaskId && !$error) {
            $newPriority = $priority + 1;
            $updateQuery = "UPDATE cnc_machine_table 
                           SET priority = priority + 1 
                           WHERE machine_id = $machineId AND table_id = $tableId AND priority < $newPriority";
            
            if (!mysqli_query($conn, $updateQuery)) {
                $error = true;
            }
        }
    }
    
    // Commit or rollback transaction
    if ($error) {
        mysqli_rollback($conn);
        echo json_encode(['status' => 'error', 'message' => mysqli_error($conn)]);
    } else {
        mysqli_commit($conn);
        echo json_encode(['status' => 'success']);
    }
    
    mysqli_autocommit($conn, true);
}
else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'get_cnc_task_details') {

    $task_id = mysqli_real_escape_string($conn, $_REQUEST['task_id']);
    $work_order_id = mysqli_real_escape_string($conn, $_REQUEST['work_order_id']);
    $part_id = mysqli_real_escape_string($conn, $_REQUEST['part_id']);
    $machine_id = mysqli_real_escape_string($conn, $_REQUEST['machine_id']);
    $query = "SELECT 
    mmt.machine_id AS task, 
    mmt.id as task_id, 
    wo.work_order_no, 
    mmt.part_id,
	mmt.table_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, 
    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,
    im.part_number as parent_part_number,
    GROUP_CONCAT(DISTINCT wocr.work_order_no ORDER BY wocr.work_order_no SEPARATOR ', ') as customer_order_nos
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 (
    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 mmt.id = '$task_id'
    LIMIT 1
";
    $result = mysqli_query($conn, $query);
    
    if ($result && mysqli_num_rows($result) > 0) {
        $data = mysqli_fetch_assoc($result);
        

        if ($data['set_id'] != 0) {
            $route_query = "SELECT route_desc FROM set_route_details WHERE set_id = '{$data['set_id']}' LIMIT 1";
        } else {
            $route_query = "SELECT route_desc FROM route_details WHERE item_id = '{$data['part_id']}' LIMIT 1";
        }
        $route_result = mysqli_query($conn, $route_query);
        if ($route_result && mysqli_num_rows($route_result) > 0) {
            $route_data = mysqli_fetch_assoc($route_result);
            $data['route_desc'] = $route_data['route_desc'];
        } else {
            $data['route_desc'] = '-';
        } 
         
      
		$data['is_late'] = false;
    if (!empty($row['manufacture_end_date']) && $row['end_date'] > $row['manufacture_end_date']) {
        $data['is_late'] = true;
    }
	$query = "
    SELECT 
        customer_reff,
        GROUP_CONCAT(DISTINCT RefSalesCode ORDER BY RefSalesCode SEPARATOR ', ') AS RefSalesCode
    FROM production_planning
    WHERE work_order_id = $work_order_id
";

$result = mysqli_query($conn, $query);

$row = mysqli_fetch_assoc($result);
$customer_reff = $row['customer_reff'] ?? '';

$data['customer_code'] = utility::getTableFieldById("client_management", "customer_code", "id", $customer_reff);
$data['RefSalesCode'] = $row['RefSalesCode'] ?? '';

    

        
        echo json_encode([
            'success' => true,
            'data' => $data
        ]);
    } else {
        echo json_encode([
            'success' => false,
            'message' => 'Task not found'
        ]);
    }
    exit;
}

else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'update_cnc_task') {

    $task_id = mysqli_real_escape_string($conn, $_POST['task_id']);
    $priority = mysqli_real_escape_string($conn, $_POST['priority']);
    $machine_id = mysqli_real_escape_string($conn, $_POST['machine_id']);
    $setup_hours = mysqli_real_escape_string($conn, $_POST['setup_hours']);
    $total_hours = mysqli_real_escape_string($conn, $_POST['total_hours']);
    $setting_instruction = mysqli_real_escape_string($conn, $_POST['setting_instruction']);
    $start_date = mysqli_real_escape_string($conn, $_POST['start_date']);
    $quantity = mysqli_real_escape_string($conn, $_POST['quantity']);
    
    // Update query
    $update_query = "UPDATE cnc_machine_table SET 
        priority = '$priority',
        machine_id = '$machine_id',
        setup_hours = '$setup_hours',
        total_hours = '$total_hours',
        setting_instruction = '$setting_instruction',
        start_date = '$start_date',
        quantity = '$quantity',
        updated_at = NOW()
    WHERE id = '$task_id'";
    
    if (mysqli_query($conn, $update_query)) {
        // Recalculate priorities if machine changed
        $recalculate_query = "SELECT id, priority FROM cnc_machine_table 
                             WHERE machine_id = '$machine_id' AND finished = 0 
                             ORDER BY priority";
        $result = mysqli_query($conn, $recalculate_query);
        
        $new_priority = 1;
        while ($row = mysqli_fetch_assoc($result)) {
            mysqli_query($conn, "UPDATE cnc_machine_table SET priority = $new_priority WHERE id = {$row['id']}");
            $new_priority++;
        }
        
        echo json_encode([
            'success' => true,
            'message' => 'Work order updated successfully'
        ]);
    } else {
        echo json_encode([
            'success' => false,
            'message' => 'Failed to update work order: ' . mysqli_error($conn)
        ]);
    }
    exit;
}
else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'delete_cnc_task') {

    $task_id = mysqli_real_escape_string($conn, $_REQUEST['task_id']);
    $work_order_id = mysqli_real_escape_string($conn, $_REQUEST['work_order_id']);
    $part_id = mysqli_real_escape_string($conn, $_REQUEST['part_id']);
    
    // Get machine_id before deletion for priority recalculation
    $machine_query = "SELECT machine_id FROM cnc_machine_table WHERE id = '$task_id' LIMIT 1";
    $machine_result = mysqli_query($conn, $machine_query);
    $machine_data = mysqli_fetch_assoc($machine_result);
    $machine_id = $machine_data['machine_id'];
    
    // Delete the task
    $delete_query = "DELETE FROM cnc_machine_table WHERE id = '$task_id'";
    
    if (mysqli_query($conn, $delete_query)) {
		$updateQuery = "
			UPDATE manufacture_machine_table 
			SET in_scheduler_check = 0 
			WHERE work_order_id = '$work_order_id'
			  AND part_id = '$part_id'
		";

		$conn->query($updateQuery);
		recalculateMachineDates_fororiginal($conn, $machine_id, 1); 
        recalculateMachineDates_fororiginal($conn, $machine_id, 2);
		processMachineScheduling($machine_id, $conn);
		recalculateMachineDates($conn, $machine_id, 1);
        recalculateMachineDates($conn, $machine_id, 2);
        
        echo json_encode([
            'success' => true,
            'message' => 'Work order deleted successfully'
        ]);
    } else {
        echo json_encode([
            'success' => false,
            'message' => 'Failed to delete work order: ' . mysqli_error($conn)
        ]);
    }
    exit;
}else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'start_machine_breakdown') {
    $machine_id = intval($_REQUEST['machine_id']);
    $breakdown_reason_id = mysqli_real_escape_string($conn, $_REQUEST['breakdown_reason']);
    $now = date('Y-m-d H:i:s');
    $reason_query = "SELECT code FROM keycode_table WHERE id = '$breakdown_reason_id'";
    $reason_result = mysqli_query($conn, $reason_query);
    
    if ($reason_result && mysqli_num_rows($reason_result) > 0) {
        $reason_data = mysqli_fetch_assoc($reason_result);
        $reason_text = $reason_data['code'];
    } else {
        $reason_text = 'Unknown Reason';
    }
    // Stop any ongoing breakdowns before starting a new one
    mysqli_query($conn, "UPDATE machine_breakdown 
                         SET is_active = 0, breakdown_end = '$now', 
                             breakdown_hours = TIMESTAMPDIFF(MINUTE, breakdown_start, '$now') / 60,
                             updated_at = '$now' 
                         WHERE machine_id = $machine_id AND is_active = 1");

    // Start new breakdown
    $sql = "INSERT INTO machine_breakdown (machine_id, breakdown_start, breakdown_reason, is_active, created_at, updated_at)
            VALUES ($machine_id, '$now', '$reason_text', 1, '$now', '$now')";
    mysqli_query($conn, $sql);

    // Mark affected work orders
    markBreakdownAffectedWorkOrders($conn, $machine_id, $now, null);

    echo json_encode(['status' => 'success']);
    exit;
    
} else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'stop_machine_breakdown') {
    $machine_id = intval($_POST['machine_id']);
    $now = date('Y-m-d H:i:s');

    // Stop the breakdown
    $sql = "UPDATE machine_breakdown 
            SET is_active = 0, breakdown_end = '$now',
                breakdown_hours = TIMESTAMPDIFF(MINUTE, breakdown_start, '$now') / 60,
                updated_at = '$now'
            WHERE machine_id = $machine_id AND is_active = 1";
    mysqli_query($conn, $sql);

    // Reset breakdown_affected status for all work orders of this machine
    mysqli_query($conn, "
        UPDATE cnc_machine_table 
        SET is_breakdown_affected = 0 
        WHERE machine_id = $machine_id AND is_breakdown_affected = 1
    ");

    echo json_encode(['status' => 'success']);
    exit;
}else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'get_machine_breakdown_status') {
   $sql = "SELECT mb.*, sd.resource_code AS machine_name
            FROM machine_breakdown mb
            LEFT JOIN station_detail_table sd ON mb.machine_id = sd.id
            ORDER BY mb.created_at DESC LIMIT 10";
    $res = mysqli_query($conn, $sql);

    $data = [];
    while ($row = mysqli_fetch_assoc($res)) {
        $data[] = $row;
    }

    echo json_encode(['status' => 'success', 'data' => $data]);
    exit;
    
}


function markBreakdownAffectedWorkOrders($conn, $machineId, $breakdownStart, $breakdownEnd = null) {
    $endCondition = $breakdownEnd ? "'$breakdownEnd'" : "NOW()";
    
    // Fixed query - removed echo and fixed syntax
    $overlapQuery = "
        SELECT id, start_date, end_date
        FROM cnc_machine_table
        WHERE machine_id = $machineId
          AND is_break = 0
          AND start_date < '$breakdownStart'
          AND end_date > '$breakdownStart'
    ";

    $overlapResult = mysqli_query($conn, $overlapQuery);
    $affectedCount = 0;

    while ($row = mysqli_fetch_assoc($overlapResult)) {
        $taskId = $row['id'];
        mysqli_query($conn, "
            UPDATE cnc_machine_table 
            SET is_breakdown_affected = 1
            WHERE id = $taskId
        ");
        $affectedCount++;
    }

    // Optional: Log this for debugging
    error_log("Marked $affectedCount overlapping work orders as breakdown-affected for Machine #$machineId");
    
    return $affectedCount;
}

function getSupplierIdByName($name) {
    global $conn;
    $name = $conn->real_escape_string($name);
    $query = "SELECT id FROM suppliers WHERE name = '$name' LIMIT 1";
    $result = $conn->query($query);
    return $result && $result->num_rows ? $result->fetch_assoc()['id'] : 0;
}

function getItemIdByPartNumber($partNumber) {
    global $conn;
    $partNumber = $conn->real_escape_string($partNumber);
    $query = "SELECT id FROM item_management WHERE part_number = '$partNumber' LIMIT 1";
    $result = $conn->query($query);
    return $result && $result->num_rows ? $result->fetch_assoc()['id'] : 0;
}

function generateNextPoRef() {
    global $conn;
    $query = "SELECT MAX(order_ref) as max_ref FROM purchase_order";
    $result = $conn->query($query);
    $row = $result->fetch_assoc();
    return $row['max_ref'] ? $row['max_ref'] + 1 : 1000;
}

// Split CNC Work Order Action
if (isset($_GET['action']) && $_GET['action'] == 'split_cnc_work_order') {
    $taskId = intval($_POST['task_id']);
    $splitQty = floatval($_POST['split_quantity']);
    
    // Validate inputs
    if ($taskId <= 0 || $splitQty <= 0) {
        echo json_encode([
            'success' => false,
            'message' => 'Invalid task ID or split quantity'
        ]);
        exit;
    }
    
    // Get original task details
    $query = "SELECT * FROM cnc_machine_table WHERE id = $taskId";
    $result = mysqli_query($conn, $query);
    
    if (!$result || mysqli_num_rows($result) == 0) {
        echo json_encode([
            'success' => false,
            'message' => 'Task not found'
        ]);
        exit;
    }
    
    $originalTask = mysqli_fetch_assoc($result);
    $currentQty = floatval($originalTask['quantity']);
    $setupHours = floatval($originalTask['setup_hours']);
    $totalHours = floatval($originalTask['total_hours']);
    $machineId = intval($originalTask['machine_id']);
    $tableId = intval($originalTask['table_id']);
    $priority = intval($originalTask['priority']);
    
    // Validate split quantity
    if ($splitQty >= $currentQty || $splitQty < 1) {
        echo json_encode([
            'success' => false,
            'message' => 'Split quantity must be between 1 and ' . ($currentQty - 1)
        ]);
        exit;
    }
    
    // Calculate hours for both work orders
    $hoursWithoutSetup = $totalHours - $setupHours;
    $hoursPerUnit = $hoursWithoutSetup / $currentQty;
    
    $remainingQty = $currentQty - $splitQty;
    $remainingHours = ($remainingQty * $hoursPerUnit) + $setupHours;
    $splitHours = ($splitQty * $hoursPerUnit) + $setupHours;
    
    // Start transaction
    mysqli_begin_transaction($conn);
    
    try {
        // Update original task with reduced quantity and hours
        $updateQuery = "UPDATE cnc_machine_table SET 
            quantity = $remainingQty,
            total_hours = $remainingHours,
            total_hours_update = $remainingHours,
            original_total_hours = $remainingHours
            WHERE id = $taskId";
        
        if (!mysqli_query($conn, $updateQuery)) {
            throw new Exception('Failed to update original task');
        }
        
        // Increment priorities for tasks after this one
        $incrementQuery = "UPDATE cnc_machine_table SET 
            priority = priority + 1 
            WHERE machine_id = $machineId 
            AND table_id = $tableId 
            AND priority > $priority";
        mysqli_query($conn, $incrementQuery);
        
        // Insert new task with split quantity
        $insertQuery = "INSERT INTO cnc_machine_table (
            machine_id, table_id, work_order_id, part_id, quantity,
            setup_hours, total_hours, total_hours_update, original_total_hours,
            priority, start_date, end_date, shift_ids, cycle, is_break,
            break_reason, finished, remaing_hours, original_start_date,
            original_end_date, original_remaing_hours, breakdown_hours,
            is_breakdown_affected
        ) VALUES (
            {$originalTask['machine_id']},
            {$originalTask['table_id']},
            {$originalTask['work_order_id']},
            {$originalTask['part_id']},
            $splitQty,
            $setupHours,
            $splitHours,
            $splitHours,
            $splitHours,
            " . ($priority + 1) . ",
            '{$originalTask['start_date']}',
            '{$originalTask['end_date']}',
            '{$originalTask['shift_ids']}',
            {$originalTask['cycle']},
            0,
            '',
            0,
            '',
            '{$originalTask['original_start_date']}',
            '{$originalTask['original_end_date']}',
            '',
            0,
            0
        )";
        
        if (!mysqli_query($conn, $insertQuery)) {
            throw new Exception('Failed to insert new task');
        }
        
        // Commit transaction
        mysqli_commit($conn);
        
        // Recalculate schedules for both tables
        recalculateMachineDates_fororiginal($conn, $machineId, 1);
		recalculateMachineDates_fororiginal($conn, $machineId, 2);
        processMachineScheduling($machineId, $conn);
        recalculateMachineDates($conn, $machineId, 1);
		recalculateMachineDates($conn, $machineId, 2);
        
        echo json_encode([
            'success' => true,
            'message' => 'Work order split successfully',
            'original_quantity' => $remainingQty,
            'original_hours' => round($remainingHours, 2),
            'split_quantity' => $splitQty,
            'split_hours' => round($splitHours, 2)
        ]);
        
    } catch (Exception $e) {
        mysqli_rollback($conn);
        echo json_encode([
            'success' => false,
            'message' => $e->getMessage()
        ]);
    }
    exit;
}
?>