<?php
// Database connection


function displayOrder($order) {
    echo "Order ID: " . $order['id'] . "<br>";
    echo "Part ID: " . $order['part_id'] . "<br>";
    echo "Product: " . $order['product_name'] . "<br>";
    echo "Start: " . $order['start_date'] . "<br>";
    echo "End: " . $order['end_date'] . "<br>";
    echo "Cycle Time: " . $order['cycle'] . " minutes<br>";
    echo "Total Hours: " . $order['total_hours'] . "<br>";
    echo "Remaining Hours: " . $order['remaing_hours'] . "<br>";
    echo "Priority: " . $order['priority'] . "<br>";
    echo str_repeat("-", 50) . "<br>";
}

function getCurrentOrders($machineId, $conn) {
    $currentDate = date('Y-m-d');
    $sql = "SELECT * FROM cnc_machine_table 
            WHERE machine_id = ? 
            AND end_date >= ? 
            AND is_break = 0
            ORDER BY priority ASC, 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()) {
        $orders[] = $row;
    }
    return $orders;
}

function processMachineScheduling($machineId, $conn) {
    echo "<h3>Processing scheduling for Machine ID: $machineId</h3><br>";
    
    // Get initial orders
    $orders = getCurrentOrders($machineId, $conn);
    
    foreach ($orders as $order) {
        displayOrder($order);
    }
    
    if (count($orders) < 2) {
        echo "Only one order found - no scheduling conflicts.<br>";
        return;
    }
    
    // Find all potential overlaps
    $potentialOverlaps = findPotentialOverlaps($orders);
    
    if (empty($potentialOverlaps)) {
        echo "No overlapping orders found.<br>";
        return;
    }
    
    echo "<br>Found " . count($potentialOverlaps) . " overlapping order pairs:<br>";
    
    // Process each potential overlap with fresh data
    foreach ($potentialOverlaps as $overlapPair) {
        // Re-fetch both orders to get current data
        $order1 = fetchOrder($overlapPair[0]['id'], $conn);
        $order2 = fetchOrder($overlapPair[1]['id'], $conn);
        
        if (!$order1 || !$order2) {
            continue; // Skip if orders no longer exist
        }
        
        echo "<br><strong>Processing overlap between:</strong><br>";
        echo "Order " . $order1['id'] . " (" . $order1['product_name'] . ") and ";
        echo "Order " . $order2['id'] . " (" . $order2['product_name'] . ")<br>";
        
        $alternated = calculateAlternatingSchedule($order1, $order2);
        
        echo "<br>Calculation Details:<br>";
        echo "Order " . $order1['id'] . " cycle: " . $order1['cycle'] . " min<br>";
        echo "Order " . $order2['id'] . " cycle: " . $order2['cycle'] . " min<br>";
        echo "Overlap duration: " . $alternated['overlap_hours'] . " hours<br>";
        echo "Alternating cycles: " . $alternated['min_cycles'] . " cycles each<br>";
        echo "Alternating time: " . $alternated['alternating_time'] . " hours<br>";
        echo "Remaining Order " . $order1['id'] . ": " . $alternated['remaining1'] . " hours<br>";
        echo "Remaining Order " . $order2['id'] . ": " . $alternated['remaining2'] . " hours<br>";
        
        echo "<br>New Schedule for Order " . $order1['id'] . ":<br>";
        echo "End Date: " . $alternated['order1']['end_date'] . "<br>";
        echo "Total Hours: " . $alternated['order1']['total_hours'] . "<br>";
        
        echo "<br>New Schedule for Order " . $order2['id'] . ":<br>";
        echo "End Date: " . $alternated['order2']['end_date'] . "<br>";
        echo "Total Hours: " . $alternated['order2']['total_hours'] . "<br>";
        
        // Update the orders in database
        updateOrderSchedule($order1['id'], $alternated['order1'], $conn);
        updateOrderSchedule($order2['id'], $alternated['order2'], $conn);
        
        echo "<br>Schedule updated in database.<br>";
        echo str_repeat("=", 50) . "<br>";
    }
}

function fetchOrder($orderId, $conn) {
    $sql = "SELECT * FROM cnc_machine_table WHERE id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $orderId);
    $stmt->execute();
    $result = $stmt->get_result();
    return $result->fetch_assoc();
}

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];
            
            $start1 = strtotime($order1['start_date']);
            $end1 = strtotime($order1['end_date']);
            $start2 = strtotime($order2['start_date']);
            $end2 = strtotime($order2['end_date']);
            
            if (($start1 < $end2) && ($start2 < $end1)) {
                $overlaps[] = [$order1, $order2];
                echo "Potential overlap found between Order " . $order1['id'] . " and Order " . $order2['id'] . "<br>";
            }
        }
    }
    
    return $overlaps;
}

function calculateAlternatingSchedule($order1, $order2) {
    $start1 = strtotime($order1['start_date']);
    $end1 = strtotime($order1['end_date']);
    $start2 = strtotime($order2['start_date']);
    $end2 = strtotime($order2['end_date']);

    $overlapStart = max($start1, $start2);
    $overlapEnd = min($end1, $end2);
    $overlapDuration = ($overlapEnd - $overlapStart) / 3600;

    $cycle1_min = $order1['cycle'];
    $cycle2_min = $order2['cycle'];
    
    $total_cycles1 = floor(($overlapDuration * 60) / $cycle1_min);
    $total_cycles2 = floor(($overlapDuration * 60) / $cycle2_min);
    
    $alternating_cycles = min($total_cycles1, $total_cycles2);
    
    $alternating_time = ($alternating_cycles * $cycle1_min + $alternating_cycles * $cycle2_min) / 60;
    
    $remaining_cycles1 = $total_cycles1 - $alternating_cycles;
    $remaining_cycles2 = $total_cycles2 - $alternating_cycles;
    
    $remaining_time1 = $remaining_cycles1 * $cycle1_min / 60;
    $remaining_time2 = $remaining_cycles2 * $cycle2_min / 60;
    
    $original_non_overlap1 = (($end1 - $start1) / 3600) - $overlapDuration;
    $original_non_overlap2 = (($end2 - $start2) / 3600) - $overlapDuration;
    
    $new_total1 = $original_non_overlap1 + $alternating_time + $remaining_time1;
    $new_total2 = $original_non_overlap2 + $alternating_time + $remaining_time2;
    
    $newEnd1 = $start1 + ($new_total1 * 3600);
    $newEnd2 = $start2 + ($new_total2 * 3600);

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

    return [
        'overlap_hours' => $overlapDuration,
        'min_cycles' => $alternating_cycles,
        'alternating_time' => $alternating_time,
        'remaining1' => $remaining_time1,
        'remaining2' => $remaining_time2,
        '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) {
    $start = strtotime($order['start_date']);
    $end = $newEndTimestamp;
    $remainingHours = [];
    $current = $start;

    while ($current < $end) {
        $currentDate = date('Y-m-d', $current);
        $nextDay = strtotime('tomorrow', $current);

        if ($nextDay > $end) {
            $hours = ($end - $current) / 3600;
        } else {
            $hours = ($nextDay - $current) / 3600;
        }

        $remainingHours[$currentDate] = round($hours, 2);
        $current = $nextDay;
    }

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

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

function updateOrderSchedule($orderId, $data, $conn) {
    $end_date = $conn->real_escape_string($data['end_date']);
    $total_hours = (float)$data['total_hours'];
    $remaing_hours = $conn->real_escape_string($data['remaing_hours']);
    
    $sql = "UPDATE cnc_machine_table 
            SET end_date = '$end_date', 
                total_hours = $total_hours, 
                remaing_hours = '$remaing_hours'
            WHERE id = $orderId";
    
    echo "<br>Executing SQL: " . htmlspecialchars($sql) . "<br>";
    $conn->query($sql);
}

// Process scheduling for machine 7
processMachineScheduling(7, $conn);


echo "<br>Scheduling processing completed.";
?>


<?
  recalculateMachineDates($conn, 7, 2);
  recalculateMachineDates($conn, 7, 1);
function recalculateMachineDates($conn, $machineId, $tableId) {
    set_time_limit(300); // Avoid timeout

    // Load shifts grouped by day
    $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 end_date >= '$currentDateStr'
         ORDER BY priority"
    );

    while ($row = mysqli_fetch_assoc($taskResult)) {
        $tasks[] = $row;
    }

    // 👇 Start scheduling from now
    $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'];

                // Skip if this shift is already passed
                if ($shiftEnd <= $currentDateTime) continue;

                // Clamp start to now if mid-shift
                $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;

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

                // Update current pointer
                $currentDateTime = $taskEndTime;

                if ($hoursRemaining <= 0) break;
            }

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

        // Format remaining_hours like "2025-07-03=2,2025-07-04=6"
        $remainingHoursStr = '';
        foreach ($dateHoursMap as $date => $hrs) {
            $remainingHoursStr .= $date . '=' . round($hrs, 2) . ',';
        }
        $remainingHoursStr = rtrim($remainingHoursStr, ',');

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

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

echo $query . "<br>"; // For debugging

mysqli_query($conn, $query);



    }
}


?>