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

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

function recalculateMachineDatesforvac($conn, $machineId) {

    set_time_limit(300);

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

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

    // ------------------------------------------------
    // Load started tasks from vac_part_count_log
    // ------------------------------------------------
    $startedTasks = [];

    $logResult = mysqli_query($conn,"
        SELECT DISTINCT vac_machine_table_id
        FROM vac_part_count_log
    ");

    while ($row = mysqli_fetch_assoc($logResult)) {
        $startedTasks[$row['vac_machine_table_id']] = true;
    }

    // ------------------------------------------------
    // Load machine tasks
    // ------------------------------------------------
    $taskResult = mysqli_query($conn,
        "SELECT 
            v.id,
            v.work_order_id,
            v.part_id,
            v.is_break,
            v.is_breakdown_affected,
            v.breakdown_hours,
            v.start_date,
            v.total_hours_update AS hours_to_schedule,
            v.total_hours AS hours,
            v.setting_instruction
        FROM vac_machine_table v
        LEFT JOIN manufacture_machine_table m
            ON v.work_order_id = m.work_order_id
            AND v.part_id = m.part_id
        WHERE v.machine_id = $machineId
        AND (v.finished = 0 OR v.is_breakdown_affected = 1)
        ORDER BY v.priority"
    );

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

    // Start scheduling from today
    $currentDateTime = strtotime(date('Y-m-d') . ' 00:00:00');

    $previous_setting_instruction = null;

    foreach ($tasks as $task) {

        $taskId = $task['id'];

        // Check if production already started
        $productionStarted = isset($startedTasks[$taskId]);

        $total_hours = (float)$task['hours_to_schedule'];
        $setting_instruction = $task['setting_instruction'];
        $start_date = $task['start_date'];
        $is_break = (int)$task['is_break'];
        $breakdown_hours = (float)$task['breakdown_hours'];

        $setup_hours = 0.00;

        if ($is_break === 1) {

            $total_hours = (float)$task['hours'];
            $setup_hours = 0.00;

        } else {

            // If production already started → no setup
            if ($productionStarted) {
                $setup_hours = 0.00;
            } else {

                if ($previous_setting_instruction !== null) {

                    if (strcasecmp($previous_setting_instruction, $setting_instruction) === 0) {
                        $setup_hours = 1.00;
                    } else {
                        $setup_hours = 2.00;
                    }

                } else {
                    $setup_hours = 2.00;
                }

                // If task started before today
                if (date('Y-m-d', strtotime($start_date)) < $currentDateStr) {
                    $setup_hours = 0.00;
                }
            }

            $total_hours = (float)($total_hours + $setup_hours + $breakdown_hours);
        }

        $hoursRemaining = $total_hours;

        $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');
            }
        }

        // Format remaining hours
        $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 vac_machine_table SET 
            start_date = '$startStr',
            end_date = '$endStr',
            shift_ids = '$firstShiftUsed',
            remaing_hours = '$remainingHoursStr',
            setup_hours = $setup_hours,
            total_hours = $total_hours
            WHERE id = {$task['id']}";

        mysqli_query($conn, $query);

        $previous_setting_instruction = $setting_instruction;
    }
}


//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'select_order_for_vac') {

    $json_data   = $_REQUEST['work_orders'] ?? '';
    $work_orders = json_decode($json_data, true);

    error_log("VAC DEBUG: Received work_orders=" . print_r($work_orders, true));

    $inserted           = false; 
    $processed_machines = []; // store unique machine IDs

    // Preload shift data for scheduling
    $shiftsByDay = [];
    $shiftResult = mysqli_query($conn, "SELECT * FROM working_hours");
    while ($row = mysqli_fetch_assoc($shiftResult)) {
        $day = (int)$row['day_shifts'];
        $shiftsByDay[$day][] = $row;
    }
    error_log("VAC DEBUG: Loaded shiftsByDay=" . print_r($shiftsByDay, true));

    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'];

        error_log("VAC DEBUG: Processing item id=$id, work_order_id=$work_order_id, part_id=$part_id, machine_id=$machine_id");

        if (!isset($processed_machines[$machine_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();
            error_log("VAC DEBUG: Found manufacture_machine_table row=" . print_r($row, true));
            $quantity     = (int)$row['quantity'];
            $product_name = $conn->real_escape_string($row['product_name']);
            $total_hours  = (float)$row['total_hours'];
            $set_id       = (int)$row['set_id'];

            // Get route info
            if ($set_id > 0) {
                $query_route = "SELECT operation_time, parts_per_operation, machine_high_priority, setting_instruction FROM set_route_details WHERE set_id = $set_id";
            } else {
                $query_route = "SELECT operation_time, parts_per_operation, machine_high_priority, setting_instruction FROM route_details WHERE item_id = $part_id";
            }
            error_log("VAC DEBUG: Running query_route=$query_route");

            $result_route = $conn->query($query_route);
            if ($result_route && $result_route->num_rows > 0) {
                $row_route = $result_route->fetch_assoc();
                error_log("VAC DEBUG: Found route row=" . print_r($row_route, true));

                $setting_instruction = $conn->real_escape_string($row_route['setting_instruction']);

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

                $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 {
                error_log("VAC DEBUG: No route details found for part_id=$part_id set_id=$set_id");
                $setting_instruction = ''; 
                $cycle = 1; 
            }

            // Get next priority
            $query_priority = "SELECT setting_instruction, MAX(priority) AS priority FROM vac_machine_table WHERE machine_id = $machine_id";
            $result_priority = mysqli_query($conn, $query_priority);
            if ($result_priority && mysqli_num_rows($result_priority) > 0) {
                $rowp = mysqli_fetch_assoc($result_priority);
                $currentPriority = $rowp['priority'] ?? 0;
                $previous_setting_instruction = $rowp['setting_instruction'] ?? null;
                $new_priority = $currentPriority + 1;
            } else {
                $previous_setting_instruction = null;
                $new_priority = 1;
            }
            error_log("VAC DEBUG: priority=$new_priority, prev_setting_instr=" . $previous_setting_instruction);

          $total_hourss = $total_hours ;
            if ($previous_setting_instruction !== null && strcasecmp($setting_instruction, $previous_setting_instruction) === 0) {
                $setup_hours = 1.00;
            } else {
                $setup_hours = 2.00;
            }
            $total_hourss += $setup_hours; 
            error_log("VAC DEBUG: setup_hours=$setup_hours, total_hourss=$total_hourss");

            // ---------------------------
            // Scheduling calculation before insert
            // ---------------------------
            $hoursRemaining = floatval($total_hourss);
            $dateHoursMap = [];
            $taskStartTime = null;
            $taskEndTime = null;
            $firstShiftUsed = null;
            $currentDateTime = strtotime(date('Y-m-d') . ' 00:00:00');

            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  = $conn->real_escape_string($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);

            error_log("VAC DEBUG: Scheduling result start=$startStr end=$endStr firstShift=$firstShiftUsed remaining=$remainingHoursStr");

            // ---------------------------
            // Insert directly with scheduling data
            // ---------------------------
            $insertQuery = "
                INSERT INTO vac_machine_table 
                (machine_id, setting_instruction, work_order_id, part_id, end_date, start_date, quantity, shift_ids, product_name, total_hours, total_hours_update, setup_hours, cycle, set_id, priority, remaing_hours)
                VALUES (
                    $machine_id,
                    '$setting_instruction',
                    $work_order_id,
                    $part_id,
                    '$endStr',
                    '$startStr',
                    $quantity,
                    '$firstShiftUsed',
                    '$product_name',
                    $total_hourss,
                    $total_hours,
                    $setup_hours,
                    $cycle,
                    $set_id,
                    $new_priority,
                    '$remainingHoursStr'
                )
            ";

            if ($conn->query($insertQuery)) {
                error_log("VAC DEBUG: Insert success for machine_id=$machine_id, work_order_id=$work_order_id");
                $inserted = true;
                $updateQuery = "UPDATE manufacture_machine_table SET in_scheduler_check = 1 WHERE id = $id";
                $conn->query($updateQuery);
            } else {
                error_log("VAC DEBUG: Insert failed: " . $conn->error . " | Query: " . $insertQuery);
            }
        } else {
            error_log("VAC DEBUG: No record in manufacture_machine_table for id=$id");
        }
    }

    foreach (array_keys($processed_machines) as $machine_id) {
        error_log("VAC DEBUG: Recalculating machine_id=$machine_id");
        recalculateMachineDatesforvac($conn, $machine_id);
    }

    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_vac_task_priority') {
    $taskId = intval($_REQUEST['task_id']);
    $newMachineId = intval($_REQUEST['new_machine_id']);
    $partId = intval($_REQUEST['part_id']);
    $workOrderId = intval($_REQUEST['work_order_id']);
    $leftNeighborPriority = isset($_REQUEST['left_neighbor_priority']) ? ($_REQUEST['left_neighbor_priority']) : null;
    $leftNeighborTaskId = isset($_REQUEST['left_neighbor_task_id']) ? ($_REQUEST['left_neighbor_task_id']) : null;


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

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

    $originalTasks = [];
    $originalResult = mysqli_query($conn,
		"SELECT id, priority 
		 FROM vac_machine_table 
		 WHERE machine_id = {$draggedTask['machine_id']}
		   AND end_date >= '$todays'
		 ORDER BY priority"
	);
    while ($row = mysqli_fetch_assoc($originalResult)) {
        $originalTasks[] = $row;
    }


    $newTasks = [];
    $newResult = mysqli_query($conn,
		"SELECT id, priority 
		 FROM vac_machine_table 
		 WHERE machine_id = $newMachineId
		   AND end_date >= '$todays'
		 ORDER BY priority"
	);

    while ($row = mysqli_fetch_assoc($newResult)) {
        $newTasks[] = $row;
    }


    $insertPriority = 1; // Default to first position
    if ($leftNeighborPriority !== null) {
        $insertPriority = (int)$leftNeighborPriority + 1;  
        // Increment priorities after insertion point
        foreach ($newTasks as $task) {
            if ($task['priority'] >= $insertPriority) {
                mysqli_query($conn,
                    "UPDATE vac_machine_table SET priority = priority + 1 
                     WHERE id = {$task['id']}"
                );
            }
        }
    }


    $draggedTaskDetails = $conn->query("SELECT setting_instruction, total_hours FROM vac_machine_table WHERE id = $taskId")->fetch_assoc();
    $setting_instruction = $draggedTaskDetails['setting_instruction'];
    $total_hours = $draggedTaskDetails['total_hours'];


    $previous_setting_instruction = null;
    if (!empty($leftNeighborTaskId)) {
        $neighborTaskDetails = $conn->query("SELECT setting_instruction FROM vac_machine_table WHERE id = $leftNeighborTaskId")->fetch_assoc();
        if ($neighborTaskDetails) {
            $previous_setting_instruction = $neighborTaskDetails['setting_instruction'];
        }
    }


    $total_hours = (float)$total_hours;  // cast once at the start
    $is_break = (int)$draggedTask['is_break']; // <-- get break flag

    if ($is_break === 1) {
        // Break → no setup hours added
        // Leave $total_hours as it is
    } else {
        if ($previous_setting_instruction !== null) {
            if (strcasecmp($previous_setting_instruction, $setting_instruction) === 0) {
                $total_hours += 1.00;
            } else {
                $total_hours += 2.00;
            }
        } else {
            // First task in machine, treat as requiring full setup
            $total_hours += 2.00;
        }
    }

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

 
    recalculateMachineDatesforvac($conn, $draggedTask['machine_id']);
    recalculateMachineDatesforvac($conn, $newMachineId);

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

else if(isset($_REQUEST['action']) && $_REQUEST['action']=='add_vac_break'){
    $machineId = intval($_REQUEST['machine_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 || $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 vac_machine_table 
             WHERE machine_id = $machineId";
    $result = mysqli_query($conn, $query);
	
	$query_priority = "SELECT MAX(priority) AS priority FROM vac_machine_table 
                   WHERE machine_id = $machineId";
    $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 vac_machine_table 
                       (machine_id, start_date, end_date, total_hours, remaing_hours, 
                        is_break, break_reason, priority)
                       VALUES ($machineId, '$startDate', '$endDate', 
                               $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 vac_machine_table 
                           SET priority = priority + 1 
                           WHERE machine_id = $machineId ";
            
            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'] == '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 vac_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;
    
}
else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'get_work_orders_vac') {
    $workOrders = [];
    
    $query = "
    SELECT 
        m.work_order_id,
        m.part_id,
        m.end_date,
        m.product_name,
        m.machine_id,
        m.quantity,
        m.description,
        m.total_hours,
        m.id,
        m.set_id,
        w.work_order_name
    FROM 
        manufacture_machine_table m
    LEFT JOIN 
        work_order_name w 
        ON m.work_order_id = w.work_order_id 
        AND m.part_id = w.part_id
    JOIN 
        station_detail_table s 
        ON m.machine_id = s.id
    WHERE 
        s.machine_type = 1
        AND m.in_scheduler_check = 0 ";
    
    $workOrderQuery = mysqli_query($conn, $query);
    
    if (!$workOrderQuery) {
        echo json_encode([
            'success' => false,
            'message' => 'Database query failed: ' . mysqli_error($conn)
        ]);
        exit;
    }
    
    while ($row = mysqli_fetch_assoc($workOrderQuery)) {
        $setting_instruction = '';
        $part_id = $row['part_id'];
        $set_id = isset($row['set_id']) ? $row['set_id'] : 0;
        
        // Get setting instruction
        if ($set_id > 0) {
            $query_route = "SELECT setting_instruction FROM set_route_details WHERE set_id = $set_id LIMIT 1";
        } else {
            $query_route = "SELECT setting_instruction FROM route_details WHERE item_id = $part_id LIMIT 1";
        }
        
        $routeResult = mysqli_query($conn, $query_route);
        if ($routeResult && mysqli_num_rows($routeResult) > 0) {
            $routeRow = mysqli_fetch_assoc($routeResult);
            $setting_instruction = $routeRow['setting_instruction'];
        }
        
        // ADD THIS: Get bought-in materials
        $boughtInMaterials = getAllBoughtInMaterials($conn, $part_id);
        $materialsText = !empty($boughtInMaterials) ? implode(', ', $boughtInMaterials) : '';
        
        // Generate machine dropdown HTML
        $machine_dropdown = $gsi->getvacStationList(
            "machine_id_" . $row['id'],
            $row['machine_id'],
            "100%",
            false,
            "form-control select2 machine-select",
            ""
        );
        
        $workOrders[] = [
            'work_order_id' => $row['work_order_id'],
            'part_id' => $row['part_id'],
            'end_date' => $row['end_date'],
            'product_name' => $row['product_name'],
            'machine_id' => $row['machine_id'],
            'quantity' => $row['quantity'],
            'total_hours' => $row['total_hours'],
            'id' => $row['id'],
            'set_id' => $set_id,
            'work_order_name' => $row['work_order_name'],
            'product_description' => $row['description'],
            'setting_instruction' => $setting_instruction,
            'bought_in_materials' => $materialsText,  // ADD THIS
            'machine_dropdown' => $machine_dropdown
        ];
    }
    
    if (empty($workOrders)) {
        echo json_encode([
            'success' => false,
            'message' => 'No work orders found'
        ]);
    } else {
        echo json_encode([
            'success' => true,
            'data' => $workOrders,
            'count' => count($workOrders)
        ]);
    }
    exit;
}

else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'process_bulk_order_old') {
  
    
    try {
        // Validate input
        if (!isset($_POST['order_data'])) {
            throw new Exception('No order data received');
        }
        
        $orderData = json_decode($_POST['order_data'], true);
        if (json_last_error() !== JSON_ERROR_NONE) {
            throw new Exception('Invalid JSON data');
        }
        
        // Validate required fields
        if (empty($orderData['orders'])) {
            throw new Exception('No items to order');
        }
        
        $response = ['success' => false];
        $conn->autocommit(false); // Start transaction
        
        foreach ($orderData['orders'] as $supplierOrder) {
            // Get supplier ID
            $supplierId = 15; 
			//$supplierId = getSupplierIdByName($supplierOrder['supplier']);
            if (!$supplierId) {
                throw new Exception("Supplier not found: " . $supplierOrder['supplier']);
            }
            
            // Create purchase order
            $poRef = generateNextPoRef();
            $notes = !empty($orderData['notes']) ? $conn->real_escape_string($orderData['notes']) : '';
            session_start();
            $userId = (int)$_SESSION['user_id'];
            $createdOn = date('Y-m-d H:i:s');
			$deliverTo = "Thermotec Plastics Ltd, Electra Park Industrial Estate, Electric Avenue, Witton, Birmingham, B6 7EB";
			$poQuery = "
				INSERT INTO purchase_order 
					(order_ref, order_status, supplier_id, deliver_to, order_notes, created_by, created_on) 
				VALUES 
					('$poRef', 1, '$supplierId', '$deliverTo', '$notes', '$userId', '$createdOn')
			";

            
            if (!$conn->query($poQuery)) {
                throw new Exception("PO creation failed: " . $conn->error);
            }
            
            $poId = $conn->insert_id;
            $ordering = 1;
            
            foreach ($supplierOrder['items'] as $item) {
                $itemId = getItemIdByPartNumber($item['partNumber']);
                if (!$itemId) {
                    throw new Exception("Part not found: " . $item['partNumber']);
                }
                
                // Convert weekDate to proper MySQL date format
                $expectedDate = !empty($item['weekDate']) ? 
                    date('Y-m-d', strtotime($item['weekDate'])) : 
                    date('Y-m-d', strtotime('+1 week'));
                
                $qty = (int)$item['qty'];
                $unitPrice = (float)$item['unitPrice'];
                $total = $qty * $unitPrice;
                
                $insertQuery = "INSERT INTO purchase_order_items_mapping 
                               (po_id, ordering, item_type, part_number, description, quantity, 
                                unit_price, total, expected_date, created_on)
                               VALUES 
                               ('$poId', '$ordering', '1', '$itemId', '{$conn->real_escape_string($item['partNumber'])}', 
                                '$qty', '$unitPrice', '$total', '$expectedDate', '$createdOn')";
                
                if (!$conn->query($insertQuery)) {
                    throw new Exception("Item insertion failed: " . $conn->error);
                }
                
                $ordering++;
            }
        }
        
        $conn->commit();
        $response = ['success' => true, 'message' => 'Orders processed successfully'];
        
    } catch (Exception $e) {
        $conn->rollback();
        $response = ['success' => false, 'message' => $e->getMessage()];
    }
    
    echo json_encode($response);
    exit;
}else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'process_bulk_order') {
    try {
        if (!isset($_POST['order_data'])) {
            throw new Exception('No order data received');
        }
        
        $orderData = json_decode($_POST['order_data'], true);
        if (json_last_error() !== JSON_ERROR_NONE) {
            throw new Exception('Invalid JSON data');
        }
        
        if (empty($orderData['orders'])) {
            throw new Exception('No items to order');
        }
        
        $response = ['success' => false];
        $conn->autocommit(false);
        
        foreach ($orderData['orders'] as $supplierOrder) {
            $supplierId = $supplierOrder['supplierId'];
            if (!$supplierId) {
                throw new Exception("Supplier not found: " . $supplierOrder['supplier']);
            }

            $createdOn = date('Y-m-d H:i:s');
            $poRef     = generateNextPoRef();
            $notes     = !empty($orderData['notes']) ? $conn->real_escape_string($orderData['notes']) : '';
            $deliverTo = "Thermotec Plastics Ltd, Electra Park Industrial Estate, Electric Avenue, Witton, Birmingham, B6 7EB";

            $poQuery = "INSERT INTO purchase_order 
                       (order_ref, order_status, supplier_id, deliver_to, order_notes, created_by, created_on) 
                       VALUES 
                       ('$poRef', 1, '$supplierId', '$deliverTo', '$notes', '1', '$createdOn')";
            
            if (!$conn->query($poQuery)) {
                throw new Exception("PO creation failed: " . $conn->error);
            }
            
            $poId     = $conn->insert_id;
            $ordering = 1;
            
            foreach ($supplierOrder['items'] as $item) {
                $itemId = getItemIdByPartNumber($item['partNumber']);
                if (!$itemId) {
                    throw new Exception("Part not found: " . $item['partNumber']);
                }

                // Get description + nominal code via product group in one query
                $nominal_code = '';
                $description  = '';
                $item_sql = mysqli_query($conn, "SELECT product_group, product_description FROM item_management WHERE id = '$itemId' LIMIT 1");
                $item_row = mysqli_fetch_assoc($item_sql);
                if ($item_row) {
                    $description = $conn->real_escape_string($item_row['product_description']);
                    if ($item_row['product_group'] != '') {
                        $pg_id  = intval($item_row['product_group']);
                        $pg_sql = mysqli_query($conn, "SELECT nominal_code FROM product_group WHERE id = $pg_id LIMIT 1");
                        $pg_row = mysqli_fetch_assoc($pg_sql);
                        if ($pg_row && $pg_row['nominal_code'] != '') {
                            $nominal_code = $pg_row['nominal_code'];
                        }
                    }
                }

                $expectedDate = !empty($item['weekDate']) ? 
                    date('Y-m-d', strtotime($item['weekDate'])) : 
                    date('Y-m-d', strtotime('+1 week'));
                
                $qty       = (int)$item['qty'];
                $unitPrice = (float)$item['unitPrice'];
                $total     = $qty * $unitPrice;
                
                $insertQuery = "INSERT INTO purchase_order_items_mapping 
                               (po_id, ordering, item_type, nominal_code, part_number, description, quantity, 
                                unit_price, total, expected_date, created_on)
                               VALUES 
                               ('$poId', '$ordering', '1', '$nominal_code', '$itemId', '$description', 
                                '$qty', '$unitPrice', '$total', '$expectedDate', '$createdOn')";
                
                if (!$conn->query($insertQuery)) {
                    throw new Exception("Item insertion failed: " . $conn->error);
                }
                
                $ordering++;
            }
        }
        
        $conn->commit();
        $response = ['success' => true, 'message' => 'Orders processed successfully'];
        
    } catch (Exception $e) {
        $conn->rollback();
        $response = ['success' => false, 'message' => $e->getMessage()];
    }
    
    echo json_encode($response);
    exit;
}



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

  if (empty($_POST['set_id'])) {
    echo json_encode(['success' => false, 'message' => 'Set ID is required']);
    exit;
  }

 

  $set_id = intval($_POST['set_id']);
  $current_part_id = isset($_POST['current_part_id']) ? $_POST['current_part_id'] : '';
  $quantity = isset($_POST['quantity']) ? floatval($_POST['quantity']) : 0;

  $query = "
    SELECT 
      smm.part_id,
      smm.ratio,
      im.part_number
    FROM set_mangmnt_mapping smm
    LEFT JOIN work_order wo ON wo.id = smm.part_id
    LEFT JOIN item_management im ON im.id = smm.part_id
    WHERE smm.set_id = '$set_id'
    ORDER BY smm.ratio DESC, smm.part_id
  ";

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

  if (!$result) {
    echo json_encode(['success' => false, 'message' => 'Database query failed: ' . mysqli_error($conn)]);
    exit;
  }

  $set_parts = [];
  while ($row = mysqli_fetch_assoc($result)) {
   
    $actual_quantity = $quantity * $row['ratio'];

    $set_parts[] = [
      'part_id' => $row['part_id'],
      'part_number' => $row['part_number'] ?? 'N/A',
      'ratio' => $row['ratio'],
      'base_quantity' => $quantity,
      'actual_quantity' => round($actual_quantity, 2)
    ];
  }

  echo json_encode(['success' => true, 'data' => $set_parts]);
}
else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'get_vac_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.setup_hours, 
    mmt.setting_instruction, 
    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 
    vac_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'] = '-';
        } 
         
        $boughtInMaterials = getAllBoughtInMaterials($conn, $data['part_id']);
        $data['bought_in_materials'] = !empty($boughtInMaterials) ? implode(', ', $boughtInMaterials) : 'None';
		$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;
}
elseif ($_REQUEST['action'] == 'ajax_update_raw_material') {

    $row_id     = mysqli_real_escape_string($conn, $_REQUEST['row_id']);
    $product_id = mysqli_real_escape_string($conn, $_REQUEST['product_id']);
    $quantity   = isset($_REQUEST['quantity']) ? floatval($_REQUEST['quantity']) : null;
    $basic_qty  = (isset($_REQUEST['basic_qty']) && $_REQUEST['basic_qty'] !== '')
                    ? floatval($_REQUEST['basic_qty'])
                    : null;

    if ($quantity === null || $quantity < 0) {
        echo json_encode(['success' => false, 'message' => 'Invalid quantity']);
        exit;
    }
    if ($basic_qty !== null && $basic_qty < 0) {
        echo json_encode(['success' => false, 'message' => 'Invalid basic quantity']);
        exit;
    }

    $query_current = "SELECT product_id, original_product_id 
                       FROM work_order_raw_matrial 
                       WHERE id = '$row_id'";
    $rs_current = mysqli_query($conn, $query_current);
    $current_row = mysqli_fetch_assoc($rs_current);

    if (!$current_row) {
        echo json_encode(['success' => false, 'message' => 'Row not found']);
        exit;
    }

    $product_name = $gsi->getPartNumberById($product_id);

    $material_changed = ((string)$current_row['product_id'] !== (string)$product_id);

    $old_product_name = $material_changed
        ? $gsi->getPartNumberById($current_row['product_id'])
        : null;
    $substitution_sql = '';
    if ($material_changed) {
        $current_product_id_escaped = mysqli_real_escape_string($conn, $current_row['product_id']);
        $substitution_sql = ",
            is_change = 1,
            original_product_id = COALESCE(NULLIF(original_product_id, ''), '$current_product_id_escaped')";
    }

    $basic_qty_sql = ($basic_qty !== null) ? "'$basic_qty'" : 'NULL';

    $update = "UPDATE work_order_raw_matrial 
               SET product_id = '$product_id', 
                   product_name = '$product_name', 
                   quantity_produce = '$quantity',
                   basic_qty = $basic_qty_sql
                   $substitution_sql
               WHERE id = '$row_id'";

    if (mysqli_query($conn, $update)) {
        $response = [
            'success'          => true,
            'material_changed' => $material_changed,
        ];
        if ($material_changed) {
            $response['old_product_name'] = $old_product_name;
        }
        echo json_encode($response);
    } else {
        echo json_encode(['success' => false, 'message' => mysqli_error($conn)]);
    }
    exit;
}
// action=ajax_update_raw_material
if ($_REQUEST['action'] == 'ajax_update_raw_material') {
    $row_id = mysqli_real_escape_string($conn, $_REQUEST['row_id']);
    $product_id = mysqli_real_escape_string($conn, $_REQUEST['product_id']);
    // Pull the new product_name to keep it in sync (if you store it denormalized)
    $product_name = utility::getTableFieldById("item_master", "item_name", "id", $product_id);

    $update = "UPDATE work_order_raw_matrial 
               SET product_id = '$product_id', product_name = '$product_name' 
               WHERE set_id = '$row_id'";  // change to your actual primary key column
    if (mysqli_query($conn, $update)) {
        echo json_encode(['success' => true]);
    } else {
        echo json_encode(['success' => false, 'message' => mysqli_error($conn)]);
    }
    exit;
}
else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'update_vac_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 vac_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 vac_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 vac_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_vac_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 vac_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 vac_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);
        recalculateMachineDatesforvac($conn, $machine_id);
        
        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;
}




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

function markBreakdownAffectedWorkOrders($conn, $machineId, $breakdownStart, $breakdownEnd = null) {
    // Use breakdownEnd or fallback to current timestamp for ongoing breakdown
    $endCondition = $breakdownEnd ? "'$breakdownEnd'" : "NOW()";

    // Find overlapping tasks on this machine
    $overlapQuery = "
        SELECT id, start_date, end_date
        FROM vac_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 = (int)$row['id'];

        mysqli_query($conn, "
            UPDATE vac_machine_table 
            SET is_breakdown_affected = 1
            WHERE id = $taskId
        ");

        $affectedCount++;
    }

    return $affectedCount; 
}

 function getAllBoughtInMaterials($conn, $part_id, &$visited = []) {
    // Prevent infinite loops
    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)) {
        // Only collect Bought In materials with product_group = 17
        if (
            $row['type'] === 'Bought In' &&
            $row['product_group'] == 17 &&
            !empty($row['sub_part_name'])
        ) {
            $materials[] = $row['sub_part_name'];
        }
        
        // Always recurse Manufactured parts
        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); // Remove duplicates
}

// Split Work Order Action
if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'split_vac_work_order') {
    $taskId = intval($_REQUEST['task_id']);
    $splitQuantity = floatval($_REQUEST['split_quantity']);
    
    // Validate inputs
    if ($taskId <= 0 || $splitQuantity <= 0) {
        echo json_encode(['success' => false, 'message' => 'Invalid input parameters']);
        exit;
    }
    
    // Fetch the original work order details
    $query = "SELECT * FROM vac_machine_table WHERE id = $taskId";
    $result = mysqli_query($conn, $query);
    
    if (!$result || mysqli_num_rows($result) == 0) {
        echo json_encode(['success' => false, 'message' => 'Work order not found']);
        exit;
    }
    
    $originalWO = mysqli_fetch_assoc($result);
    $originalQuantity = floatval($originalWO['quantity']);
    $machineId = intval($originalWO['machine_id']);
    $workOrderId = intval($originalWO['work_order_id']);
    $partId = intval($originalWO['part_id']);
    $setupHours = floatval($originalWO['setup_hours']);
    $totalHours = floatval($originalWO['total_hours']);
    $priority = intval($originalWO['priority']);
    
    // Validate split quantity
    if ($splitQuantity >= $originalQuantity) {
        echo json_encode(['success' => false, 'message' => 'Split quantity must be less than current quantity']);
        exit;
    }
    
    if ($splitQuantity < 1) {
        echo json_encode(['success' => false, 'message' => 'Split quantity must be at least 1']);
        exit;
    }
    
    // Calculate hours for both work orders
    $originalTotalHoursWithoutSetup = $totalHours - $setupHours;
    $hoursPerUnit = $originalTotalHoursWithoutSetup / $originalQuantity;
    
    $remainingQuantity = $originalQuantity - $splitQuantity;
    $remainingHours = ($remainingQuantity * $hoursPerUnit) + $setupHours;
    $splitHours = ($splitQuantity * $hoursPerUnit) + $setupHours;
    
    // Start transaction
    mysqli_autocommit($conn, false);
    $error = false;
    
    try {
        // Update original work order with remaining quantity
        $updateQuery = "UPDATE vac_machine_table 
                       SET quantity = $remainingQuantity,
                           total_hours = $remainingHours,
                           total_hours_update = " . ($remainingHours - $setupHours) . "
                       WHERE id = $taskId";
        
        if (!mysqli_query($conn, $updateQuery)) {
            throw new Exception('Failed to update original work order: ' . mysqli_error($conn));
        }
        
        // Increment priorities of all subsequent work orders
        $newPriority = $priority + 1;
        $incrementQuery = "UPDATE vac_machine_table 
                          SET priority = priority + 1 
                          WHERE machine_id = $machineId 
                          AND priority >= $newPriority
                          AND id != $taskId";
        
        if (!mysqli_query($conn, $incrementQuery)) {
            throw new Exception('Failed to update priorities: ' . mysqli_error($conn));
        }
        
        // Insert new work order with split quantity
        $insertQuery = "INSERT INTO vac_machine_table (
                           machine_id, 
                           work_order_id, 
                           part_id, 
                           quantity, 
                           product_name,
                           total_hours,
                           total_hours_update,
                           setup_hours,
                           setting_instruction,
                           priority,
                           set_id,
                           cycle,
                           shift_ids,
                           start_date,
                           end_date,
                           is_break,
                           finished
                       ) VALUES (
                           $machineId,
                           $workOrderId,
                           $partId,
                           $splitQuantity,
                           '" . mysqli_real_escape_string($conn, $originalWO['product_name']) . "',
                           $splitHours,
                           " . ($splitHours - $setupHours) . ",
                           $setupHours,
                           '" . mysqli_real_escape_string($conn, $originalWO['setting_instruction']) . "',
                           $newPriority,
                           " . intval($originalWO['set_id']) . ",
                           " . intval($originalWO['cycle']) . ",
                           '" . mysqli_real_escape_string($conn, $originalWO['shift_ids']) . "',
                           '" . mysqli_real_escape_string($conn, $originalWO['start_date']) . "',
                           '" . mysqli_real_escape_string($conn, $originalWO['end_date']) . "',
                           0,
                           0
                       )";
        
        if (!mysqli_query($conn, $insertQuery)) {
            throw new Exception('Failed to create new work order: ' . mysqli_error($conn));
        }
        
        // Commit transaction
        mysqli_commit($conn);
        
        // Recalculate machine schedules
        recalculateMachineDatesforvac($conn, $machineId);
        
        echo json_encode([
            'success' => true, 
            'message' => 'Work order split successfully',
            'original_quantity' => $remainingQuantity,
            'split_quantity' => $splitQuantity,
            'original_hours' => round($remainingHours, 2),
            'split_hours' => round($splitHours, 2)
        ]);
        
    } catch (Exception $e) {
        mysqli_rollback($conn);
        echo json_encode(['success' => false, 'message' => $e->getMessage()]);
    }
    
    mysqli_autocommit($conn, true);
    exit;
}
?>
