<?php
/**
 * ajax_actions_for_vac.php  (office / VAC-admin AJAX endpoints)
 * =====================================================================
 * CHANGES FROM ORIGINAL:
 *
 *   1. recalculateMachineDatesforvac() removed — this file's copy was
 *      one of the two that checked "has production started" against the
 *      task's real row id instead of manuf_id (see vac_scheduler_lib.php
 *      header for the full writeup). Now uses vac_fullReschedule().
 *
 *   2. select_order_for_vac's inline shift-walking loop (a 4th copy of
 *      the same scheduling primitive) replaced with
 *      vac_scheduleNewTaskInline(). Verbose "VAC DEBUG:" error_log calls
 *      removed — they were clearly temporary debugging instrumentation,
 *      not intended to ship (harmless if you want them back, just add
 *      error_log() calls at the same points).
 *
 *   3. update_vac_task_priority: FIX — the priority-renumbering query
 *      filtered `end_date >= today`, the same stale-row bug found in
 *      CNC's and PU's equivalent endpoints: a task whose end_date has
 *      already slipped into the past keeps its OLD priority number while
 *      everything else gets renumbered around it, producing collisions.
 *      Now filters `finished = 0`, matching what vac_recalculateMachineDates()
 *      itself uses. Also removed a dead code block that computed a
 *      `$total_hours` value for the dragged task and then never wrote it
 *      anywhere — the very next vac_fullReschedule() call overwrites it
 *      unconditionally, so the computation was pure noise.
 *
 *   4. add_vac_break: FIX — `$priority` (meant to place the break ahead
 *      of the selected "highest priority" task) was computed and then
 *      never used; the insert always used `$new_priority` (MAX+1),
 *      silently appending to the back of the queue. The follow-up
 *      priority-shift had NO condition beyond machine_id at all,
 *      bumping every row on the machine by one regardless of position.
 *      This endpoint also never called any reschedule function at all.
 *      All three fixed, same pattern as add_pu_break / add_cnc_break.
 *
 *   5. start_machine_breakdown / stop_machine_breakdown now mirror into
 *      vac_machine_breakdown_log, the table the shop-floor dashboard
 *      actually checks (vac_isMachineInBreakdown()) — previously a
 *      breakdown started here was invisible to the tablet. stop now also
 *      calls vac_fullReschedule() (previously it never did).
 *
 *   6. get_gantt_rows' active-breakdown lookup now reads
 *      vac_machine_breakdown_log (mirrored by fix #5) instead of the
 *      separate machine_breakdown table, for the same reason.
 *
 *   7. markBreakdownAffectedWorkOrders() removed from this file — now
 *      the shared vac_markBreakdownAffectedWorkOrders().
 *
 *   8. split_vac_work_order — reviewed specifically for the "wrong half
 *      marked finished" bug found and fixed in CNC's equivalent. VAC's
 *      manual split already gets this right (neither half is marked
 *      finished; that's the breakdown-split's job, not this one's) —
 *      left functionally unchanged, just routed through
 *      vac_fullReschedule().
 * =====================================================================
 */
include("../../../include/startup.php");
require_once __DIR__ . '/vac_scheduler_lib.php';

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

/**
 * Resolve the material actually consumed for a given BOM/set sub-part,
 * matching the substitution logic used across the VAC files.
 */
function getEffectiveMaterialId($conn, $workOrderId, $originalPartId, $setId = 0) {
    $workOrderId    = intval($workOrderId);
    $originalPartId = intval($originalPartId);
    $setId          = intval($setId);

    if ($originalPartId <= 0) return $originalPartId;

    $sql = "SELECT product_id
            FROM work_order_raw_matrial
            WHERE work_order_id = $workOrderId
              AND original_product_id = $originalPartId
              AND set_id = $setId
              AND is_change = 1
            LIMIT 1";

    $result = mysqli_query($conn, $sql);
    if ($result && mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_assoc($result);
        if (!empty($row['product_id'])) return intval($row['product_id']);
    }
    return $originalPartId;
}

/**
 * Walk the BOM tree collecting "Bought In" (product_group = 17) material
 * names for display, substitution-aware.
 */
function getAllBoughtInMaterials($conn, $part_id, $work_order_id = 0, $set_id = 0, &$visited = []) {
    if (in_array($part_id, $visited)) return [];
    $visited[] = $part_id;
    $materials = [];

    $query = "
        SELECT b.sub_part_id, b.sub_part_name, b.type, im.product_group
        FROM bom b
        LEFT JOIN item_management im ON im.id = b.sub_part_id
        WHERE b.main_product_id = '$part_id'
    ";
    $result = mysqli_query($conn, $query);
    if (!$result) return [];

    while ($row = mysqli_fetch_assoc($result)) {
        $original_sub_part_id = intval($row['sub_part_id']);
        $effective_sub_part_id = ($original_sub_part_id > 0 && $work_order_id > 0)
            ? getEffectiveMaterialId($conn, $work_order_id, $original_sub_part_id, $set_id)
            : $original_sub_part_id;
        $is_substituted = ($effective_sub_part_id !== $original_sub_part_id);

        $display_name = $row['sub_part_name'];
        if ($is_substituted) {
            $newName = utility::getTableFieldById("item_management", "part_number", "id", $effective_sub_part_id);
            if (!empty($newName)) $display_name = $newName;
        }

        if ($row['type'] === 'Bought In' && $row['product_group'] == 17 && !empty($display_name)) {
            $materials[] = $display_name . ($is_substituted ? ' (substituted)' : '');
        }

        if ($row['type'] === 'Manufactured' && !empty($original_sub_part_id)) {
            $sub_materials = getAllBoughtInMaterials($conn, $effective_sub_part_id, $work_order_id, $set_id, $visited);
            $materials = array_merge($materials, $sub_materials);
        }
    }
    return array_unique($materials);
}

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

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

    $inserted           = false;
    $processed_machines = [];
    $shiftsByDay = vac_getShiftsByDay($conn);

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

        $processed_machines[$machine_id] = true;

        $result = $conn->query("
            SELECT part_id, id, end_date, quantity, product_name, total_hours, set_id
            FROM manufacture_machine_table WHERE id = $id
        ");
        if (!$result || $result->num_rows == 0) continue;

        $row = $result->fetch_assoc();
        $manuf_id     = (int) $row['id'];
        $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'];

        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";
        }
        $result_route = $conn->query($query_route);

        if ($result_route && $result_route->num_rows > 0) {
            $row_route = $result_route->fetch_assoc();
            $setting_instruction = $conn->real_escape_string($row_route['setting_instruction']);

            if (!empty($row_route['operation_time'])) {
                [$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 {
            $setting_instruction = '';
            $cycle = 1;
        }

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

        $total_hourss = $total_hours;
        $setup_hours = ($previous_setting_instruction !== null && strcasecmp($setting_instruction, $previous_setting_instruction) === 0)
            ? 1.00 : 2.00;
        $total_hourss += $setup_hours;

        // FIX: was a 4th inline copy of the shift-walking loop; now the
        // shared primitive every other scheduling path uses.
        $scheduleData = vac_scheduleNewTaskInline($conn, $total_hourss, $shiftsByDay);
        $startStr = $scheduleData['start_date'];
        $endStr   = $scheduleData['end_date'];
        $firstShiftUsed = mysqli_real_escape_string($conn, (string) $scheduleData['shift_ids']);
        $remainingHoursStr = mysqli_real_escape_string($conn, $scheduleData['remaining_hours']);

        $insertQuery = "
            INSERT INTO vac_machine_table
            (machine_id, manuf_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, $manuf_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)) {
            $inserted = true;
            $conn->query("UPDATE manufacture_machine_table SET in_scheduler_check = 1 WHERE id = $id");
        }
    }

    foreach (array_keys($processed_machines) as $machine_id) {
        vac_fullReschedule($conn, $machine_id);
    }

    echo json_encode($inserted
        ? ['status' => 'success']
        : ['status' => 'failed', 'message' => 'No rows inserted']);
    exit;
}

/* =====================================================================
 * update_vac_task_priority — drag and drop
 * ===================================================================== */
else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'update_vac_task_priority') {
    $taskId       = intval($_REQUEST['task_id']);
    $newMachineId = intval($_REQUEST['new_machine_id']);
    $leftNeighborPriority = isset($_REQUEST['left_neighbor_priority']) ? $_REQUEST['left_neighbor_priority'] : null;

    $draggedTask = $conn->query("SELECT * FROM vac_machine_table WHERE id = $taskId")->fetch_assoc();
    if (!$draggedTask) {
        echo json_encode(['status' => 'error', 'message' => 'Task not found']);
        exit;
    }
    $oldMachineId = intval($draggedTask['machine_id']);

    // FIX: renumber every unfinished row on the old machine (finished = 0),
    // not just rows with end_date >= today -- a stale end_date previously
    // left a row at its old priority number while everything else got
    // renumbered around it.
    $originalTasks = [];
    $originalResult = mysqli_query($conn, "
        SELECT id, priority FROM vac_machine_table
        WHERE machine_id = $oldMachineId AND finished = 0
        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 finished = 0
        ORDER BY priority
    ");
    while ($row = mysqli_fetch_assoc($newResult)) $newTasks[] = $row;

    $insertPriority = 1;
    if ($leftNeighborPriority !== null) {
        $insertPriority = (int) $leftNeighborPriority + 1;
        foreach ($newTasks as $task) {
            if ($task['priority'] >= $insertPriority) {
                mysqli_query($conn, "UPDATE vac_machine_table SET priority = priority + 1 WHERE id = {$task['id']}");
            }
        }
    }

    // NOTE: the original endpoint computed a $total_hours value here
    // (factoring in a setup-hours transition based on the left
    // neighbour's setting_instruction) and never wrote it anywhere --
    // vac_fullReschedule(), called a few lines below, recomputes and
    // overwrites total_hours/setup_hours unconditionally using its own
    // (correct) logic. That dead computation has been removed.

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

    vac_fullReschedule($conn, $oldMachineId);
    if ($newMachineId != $oldMachineId) {
        vac_fullReschedule($conn, $newMachineId);
    }

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

/* =====================================================================
 * add_vac_break — admin Gantt "Add Break" button
 * ===================================================================== */
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']);
    $highestPriority = isset($_REQUEST['highest_priority']) ? intval($_REQUEST['highest_priority']) : 0;

    if ($machineId <= 0 || $duration < 0.5 || $duration > 10) {
        echo json_encode(['status' => 'error', 'message' => 'Invalid input values']);
        exit;
    }

    // FIX: this is the priority the break should actually land at --
    // just ahead of whichever task the caller flagged as currently
    // highest priority. Previously computed and then discarded; the
    // insert always used MAX(priority)+1 (appending to the back).
    $priority = $highestPriority ? max(1, $highestPriority) : 1;

    mysqli_autocommit($conn, false);
    $error = false;

    $result = mysqli_query($conn, "SELECT MAX(end_date) as last_end_date FROM vac_machine_table WHERE machine_id = $machineId");
    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));

        $startDay = date('Y-m-d', strtotime($startDate));
        $endDay   = date('Y-m-d', strtotime($endDate));
        $remainingHours = $duration;
        if ($startDay != $endDay) {
            $endOfFirstDay = date('Y-m-d 23:59:59', strtotime($startDate));
            $firstDayHours = (strtotime($endOfFirstDay) - strtotime($startDate)) / 3600;
            $remainingHours = $duration - $firstDayHours;
        }

        // FIX: scoped to the rows that actually need to move, instead of
        // every row on the machine regardless of position.
        if (!mysqli_query($conn, "
            UPDATE vac_machine_table SET priority = priority + 1
            WHERE machine_id = $machineId AND priority >= $priority
        ")) {
            $error = true;
        }

        if (!$error) {
            $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', $priority)";
            if (!mysqli_query($conn, $insertQuery)) {
                $error = true;
            }
        }
    }

    if ($error) {
        mysqli_rollback($conn);
        echo json_encode(['status' => 'error', 'message' => mysqli_error($conn)]);
        mysqli_autocommit($conn, true);
        exit;
    }

    mysqli_commit($conn);
    mysqli_autocommit($conn, true);

    // FIX: previously never called at all for this endpoint.
    vac_fullReschedule($conn, $machineId);

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

/* =====================================================================
 * start_machine_breakdown / stop_machine_breakdown
 * Mirrors into vac_machine_breakdown_log so the tablet's
 * vac_isMachineInBreakdown() check sees it immediately.
 * ===================================================================== */
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_result = mysqli_query($conn, "SELECT code FROM keycode_table WHERE id = '$breakdown_reason_id'");
    $reason_text = ($reason_result && mysqli_num_rows($reason_result) > 0)
        ? mysqli_fetch_assoc($reason_result)['code'] : 'Unknown Reason';

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

    mysqli_query($conn, "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')");

    vac_markBreakdownAffectedWorkOrders($conn, $machine_id, $now);

    // Mirror into the tablet's source-of-truth table.
    mysqli_query($conn, "
        INSERT INTO vac_machine_breakdown_log
        (work_order_id, part_id, machine_id, engineer_id, vac_machine_table_id, breakdown_reason,
         breakdown_duration, ok_count_at_breakdown, remaining_quantity, status, created_at)
        VALUES (0, 0, $machine_id, 0, 0, '" . mysqli_real_escape_string($conn, $reason_text) . "', 0, 0, 0, 'active', '$now')
    ");

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

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

    mysqli_query($conn, "UPDATE vac_machine_table SET is_breakdown_affected = 0 WHERE machine_id = $machine_id AND is_breakdown_affected = 1");

    $lastLog = mysqli_fetch_assoc(mysqli_query($conn, "
        SELECT * FROM vac_machine_breakdown_log WHERE machine_id = $machine_id AND status = 'active' ORDER BY id DESC LIMIT 1
    "));
    if ($lastLog) {
        mysqli_query($conn, "
            INSERT INTO vac_machine_breakdown_log
            (work_order_id, part_id, machine_id, engineer_id, vac_machine_table_id, breakdown_reason,
             breakdown_duration, ok_count_at_breakdown, remaining_quantity, status, created_at)
            VALUES ({$lastLog['work_order_id']}, {$lastLog['part_id']}, $machine_id, {$lastLog['engineer_id']}, {$lastLog['vac_machine_table_id']},
                    {$lastLog['breakdown_reason']}, {$lastLog['breakdown_duration']}, {$lastLog['ok_count_at_breakdown']},
                    {$lastLog['remaining_quantity']}, 'completed', '$now')
        ");
    }

    // FIX: this endpoint never rescheduled at all before.
    vac_fullReschedule($conn, $machine_id);

    echo json_encode(['status' => 'success']);
    exit;

} else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'get_machine_breakdown_status') {
    // FIX: reads vac_machine_breakdown_log now, consistent with the
    // shared source of truth (both tables are mirrored together by the
    // start/stop endpoints above, but this keeps every reader pointed at
    // the same one going forward).
    $sql = "SELECT vb.*, sd.resource_code AS machine_name
            FROM vac_machine_breakdown_log vb
            LEFT JOIN station_detail_table sd ON vb.machine_id = sd.id
            ORDER BY vb.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;
}

/* =====================================================================
 * get_work_orders_vac — unchanged from original, no scheduling bugs.
 * ===================================================================== */
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  = $row['set_id'] ?? 0;

        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) {
            $setting_instruction = mysqli_fetch_assoc($routeResult)['setting_instruction'];
        }

        $boughtInMaterials = getAllBoughtInMaterials($conn, $part_id, $row['work_order_id'], $set_id);
        $materialsText = !empty($boughtInMaterials) ? implode(', ', $boughtInMaterials) : '';

        $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' => !empty($row['end_date']) ? date('d/m/Y', strtotime($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,
            'machine_dropdown' => $machine_dropdown
        ];
    }

    echo empty($workOrders)
        ? json_encode(['success' => false, 'message' => 'No work orders found'])
        : json_encode(['success' => true, 'data' => $workOrders, 'count' => count($workOrders)]);
    exit;
}

/* =====================================================================
 * process_bulk_order_old / process_bulk_order — unrelated to scheduling,
 * unchanged from original.
 * ===================================================================== */
else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'process_bulk_order_old') {
    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');

        $conn->autocommit(false);
        foreach ($orderData['orders'] as $supplierOrder) {
            $supplierId = 15;
            if (!$supplierId) throw new Exception("Supplier not found: " . $supplierOrder['supplier']);
            $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']);
                $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();
        echo json_encode(['success' => true, 'message' => 'Orders processed successfully']);
    } catch (Exception $e) {
        $conn->rollback();
        echo json_encode(['success' => false, 'message' => $e->getMessage()]);
    }
    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');

        $conn->autocommit(false);
        foreach ($orderData['orders'] as $supplierOrder) {
            $supplierId = 15;
            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']);
                $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();
        echo json_encode(['success' => true, 'message' => 'Orders processed successfully']);
    } catch (Exception $e) {
        $conn->rollback();
        echo json_encode(['success' => false, 'message' => $e->getMessage()]);
    }
    exit;
}

/* =====================================================================
 * ajax_get_set_parts — unchanged.
 * ===================================================================== */
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']);
    $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]);
}

/* =====================================================================
 * get_vac_task_details — unchanged, no scheduling bugs.
 * ===================================================================== */
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']);

    $query = "SELECT
        mmt.machine_id AS task, mmt.id as task_id, wo.work_order_no, mmt.manuf_id, 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);
        $data['route_desc'] = ($route_result && mysqli_num_rows($route_result) > 0) ? mysqli_fetch_assoc($route_result)['route_desc'] : '-';

        $boughtInMaterials = getAllBoughtInMaterials($conn, $data['part_id'], $data['work_order_id'], $data['set_id']);
        $data['bought_in_materials'] = !empty($boughtInMaterials) ? implode(', ', $boughtInMaterials) : 'None';
        $data['is_late'] = (!empty($data['manufacture_end_date']) && $data['end_date'] > $data['manufacture_end_date']);

        $ppRes = mysqli_query($conn, "
            SELECT customer_reff, GROUP_CONCAT(DISTINCT RefSalesCode ORDER BY RefSalesCode SEPARATOR ', ') AS RefSalesCode
            FROM production_planning WHERE work_order_id = " . intval($work_order_id)
        );
        $ppRow = mysqli_fetch_assoc($ppRes);
        $customer_reff = $ppRow['customer_reff'] ?? '';
        $data['customer_code'] = utility::getTableFieldById("client_management", "customer_code", "id", $customer_reff);
        $data['RefSalesCode']  = $ppRow['RefSalesCode'] ?? '';

        $note_result = mysqli_query($conn, "SELECT note FROM vac_task_notes WHERE task_id = '$task_id' LIMIT 1");
        $note_row = mysqli_fetch_assoc($note_result);
        $data['task_note'] = $note_row ? $note_row['note'] : '';
$data['task_note'] = $note_row ? $note_row['note'] : '';

// Produced (OK) qty + Live UPH
$manufIdForCounts     = intval($data['manuf_id']);
$workOrderIdForCounts = intval($data['work_order_id']);
$partIdForCounts      = intval($data['part_id']);

$producedQty = 0;
$liveUph     = 0;

if ($manufIdForCounts > 0) {
    $okRes = mysqli_query($conn, "
        SELECT COALESCE(SUM(quantity),0) AS ok_qty
        FROM vac_part_count_log
        WHERE work_order_id = $workOrderIdForCounts
          AND vac_machine_table_id = $manufIdForCounts
          AND part_id = $partIdForCounts
          AND type = 'ok'
    ");
    if ($okRes) $producedQty = intval(mysqli_fetch_assoc($okRes)['ok_qty']);

    $windowStart = date('Y-m-d H:i:s', strtotime('-60 minutes'));
    $winRes = mysqli_query($conn, "
        SELECT COALESCE(SUM(quantity),0) AS ok_qty
        FROM vac_part_count_log
        WHERE work_order_id = $workOrderIdForCounts
          AND vac_machine_table_id = $manufIdForCounts
          AND part_id = $partIdForCounts
          AND type = 'ok'
          AND created_at >= '$windowStart'
    ");
    if ($winRes) $liveUph = intval(mysqli_fetch_assoc($winRes)['ok_qty']);
}

$plannedUph = ((float)$data['quantity'] > 0 && (float)$data['total_hours'] > 0)
    ? round((float)$data['quantity'] / (float)$data['total_hours'], 2)
    : 0;

$data['produced_qty'] = $producedQty;
$data['live_uph']     = $liveUph;
$data['planned_uph']  = $plannedUph;
$data['is_uph_green'] = ($plannedUph > 0 && $liveUph >= $plannedUph);

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

/* =====================================================================
 * update_vac_task
 * ===================================================================== */
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 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)) {
        $result = mysqli_query($conn, "SELECT id, priority FROM vac_machine_table WHERE machine_id = '$machine_id' AND finished = 0 ORDER BY priority");
        $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++;
        }
        vac_fullReschedule($conn, $machine_id);
        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;
}

/* =====================================================================
 * delete_vac_task
 * ===================================================================== */
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']);

    $machine_result = mysqli_query($conn, "SELECT machine_id FROM vac_machine_table WHERE id = '$task_id' LIMIT 1");
    $machine_data = mysqli_fetch_assoc($machine_result);
    if (!$machine_data) { echo json_encode(['success' => false, 'message' => 'Task not found']); exit; }
    $machine_id = $machine_data['machine_id'];

    if (mysqli_query($conn, "DELETE FROM vac_machine_table WHERE id = '$task_id'")) {
        mysqli_query($conn, "DELETE FROM vac_task_notes WHERE task_id = '$task_id'");
        $conn->query("UPDATE manufacture_machine_table SET in_scheduler_check = 0 WHERE work_order_id = '$work_order_id' AND part_id = '$part_id'");
        vac_fullReschedule($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;
}

/* =====================================================================
 * split_vac_work_order — reviewed for the "wrong half marked finished"
 * bug (found and fixed in CNC's equivalent); already correct here since
 * neither half is finished by a manual split. Routed through
 * vac_fullReschedule(), otherwise unchanged.
 * ===================================================================== */
if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'split_vac_work_order') {
    $taskId = intval($_REQUEST['task_id']);
    $splitQuantity = floatval($_REQUEST['split_quantity']);

    if ($taskId <= 0 || $splitQuantity <= 0) {
        echo json_encode(['success' => false, 'message' => 'Invalid input parameters']); exit;
    }

    $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']);
    $manufId = intval($originalWO['manuf_id']);

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

    $originalTotalHoursWithoutSetup = $totalHours - $setupHours;
    $hoursPerUnit = $originalQuantity > 0 ? ($originalTotalHoursWithoutSetup / $originalQuantity) : 0;

    $remainingQuantity = $originalQuantity - $splitQuantity;
    $remainingHours = ($remainingQuantity * $hoursPerUnit) + $setupHours;
    $splitHours = ($splitQuantity * $hoursPerUnit) + $setupHours;

    mysqli_autocommit($conn, false);

    try {
        $newManufId = null;

        if ($manufId > 0) {
            $manufResult = mysqli_query($conn, "SELECT * FROM manufacture_machine_table WHERE id = $manufId");
            if (!$manufResult || mysqli_num_rows($manufResult) == 0) {
                throw new Exception('Linked manufacture record not found for manuf_id ' . $manufId);
            }
            $originalManuf = mysqli_fetch_assoc($manufResult);
            $manufOriginalQuantity = floatval($originalManuf['quantity']);
            $manufTotalHours = floatval($originalManuf['total_hours']);
            $manufHoursPerUnit = $manufOriginalQuantity > 0 ? ($manufTotalHours / $manufOriginalQuantity) : 0;

            $manufRemainingQuantity = $manufOriginalQuantity - $splitQuantity;
            $manufRemainingHours = $manufRemainingQuantity * $manufHoursPerUnit;
            $manufSplitHours = $splitQuantity * $manufHoursPerUnit;

            if (!mysqli_query($conn, "UPDATE manufacture_machine_table SET quantity = $manufRemainingQuantity, total_hours = $manufRemainingHours WHERE id = $manufId")) {
                throw new Exception('Failed to update original manufacture record: ' . mysqli_error($conn));
            }

            $manufColumns = []; $manufValues = [];
            foreach ($originalManuf as $col => $val) {
                if ($col === 'id') continue;
                $manufColumns[] = "`$col`";
                if ($col === 'quantity')      $manufValues[] = $splitQuantity;
                elseif ($col === 'total_hours') $manufValues[] = $manufSplitHours;
                elseif (is_null($val))        $manufValues[] = 'NULL';
                else $manufValues[] = "'" . mysqli_real_escape_string($conn, $val) . "'";
            }
            $insertManufQuery = "INSERT INTO manufacture_machine_table (" . implode(', ', $manufColumns) . ") VALUES (" . implode(', ', $manufValues) . ")";
            if (!mysqli_query($conn, $insertManufQuery)) {
                throw new Exception('Failed to create new manufacture record: ' . mysqli_error($conn));
            }
            $newManufId = $conn->insert_id;
        }

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

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

        $manufIdForInsert = $newManufId !== null ? $newManufId : ($manufId > 0 ? $manufId : null);

        $insertQuery = "INSERT INTO vac_machine_table (
                           machine_id, manuf_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,
                           " . ($manufIdForInsert !== null ? intval($manufIdForInsert) : 'NULL') . ",
                           $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));
        }

        mysqli_commit($conn);
        vac_fullReschedule($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),
            'new_manuf_id' => $newManufId
        ]);
    } catch (Exception $e) {
        mysqli_rollback($conn);
        echo json_encode(['success' => false, 'message' => $e->getMessage()]);
    }
    mysqli_autocommit($conn, true);
    exit;
}

/* =====================================================================
 * save_task_note
 * ===================================================================== */
if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'save_task_note') {
    $task_id = intval($_POST['task_id']);
    $work_order_id = intval($_POST['work_order_id']);
    $part_id = intval($_POST['part_id']);
    $note = mysqli_real_escape_string($conn, $_POST['note']);

    $check = mysqli_query($conn, "SELECT id FROM vac_task_notes WHERE task_id = $task_id");
    if (mysqli_num_rows($check) > 0) {
        mysqli_query($conn, "UPDATE vac_task_notes SET note = '$note' WHERE task_id = $task_id");
    } else {
        mysqli_query($conn, "INSERT INTO vac_task_notes (task_id, work_order_id, part_id, note) VALUES ($task_id, $work_order_id, $part_id, '$note')");
    }
    echo json_encode(['status' => 'success']);
    exit;
}

/* =====================================================================
 * get_gantt_rows — rendering only. FIX: active-breakdown lookup now
 * reads vac_machine_breakdown_log (mirrored by start/stop endpoints
 * above), consistent with the single source of truth used elsewhere.
 * ===================================================================== */
else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'get_gantt_rows') {

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

    $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, m.parent_ids,
        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.end_date >= '$currentDateStr'
    GROUP BY mmt.id, mmt.machine_id, wo.work_order_no, mmt.part_id, mmt.setup_hours,
        mmt.setting_instruction, mmt.priority, mmt.total_hours, mmt.work_order_id,
        mmt.product_name, mmt.quantity, mmt.set_id, won.work_order_name, mmt.shift_ids, mmt.start_date,
        mmt.end_date, mmt.is_break, mmt.break_reason, mmt.is_breakdown_affected,
        mmt.breakdown_hours, mb.id, mb.breakdown_reason, m.end_date, m.description, im.part_number
    ORDER BY mmt.priority, mmt.machine_id";

    $tasks = [];
    $result = mysqli_query($conn, $query);
    while ($row = mysqli_fetch_assoc($result)) {
        $row['is_late'] = (!empty($row['manufacture_end_date']) && $row['end_date'] > $row['manufacture_end_date']);
        $tasks[] = $row;
    }

    $activeBreakdowns = [];
    $bdResult = mysqli_query($conn, "
        SELECT machine_id, breakdown_reason FROM vac_machine_breakdown_log b1
        WHERE status = 'active' AND created_at = (
            SELECT MAX(created_at) FROM vac_machine_breakdown_log b2 WHERE b2.machine_id = b1.machine_id
        )
    ");
    while ($bd = mysqli_fetch_assoc($bdResult)) {
        $activeBreakdowns[$bd['machine_id']] = $bd['breakdown_reason'];
    }

    $taskNames = [];
    $machineQuery = mysqli_query($conn, "SELECT id, resource_code FROM station_detail_table WHERE machine_type = 1 AND hide = 0");
    while ($row = mysqli_fetch_assoc($machineQuery)) $taskNames[$row['id']] = $row['resource_code'];

    $colorPalette = ['#e6f5f5','#f5e6f5','#e6ffe6','#fff2e6','#f0e6ff','#e6ffff','#ffe6f2','#f5f5e6','#e6f5f5','#f5e6ff','#e6ffec','#fff0e6','#e6f0ff','#ffe6ff','#e6ffe6','#f5e6e6','#e6e6f5','#f5f5e6','#e6f5f5','#f5e6f5'];
    $machineColors = []; $colorIndex = 0;
    foreach ($taskNames as $machineId => $taskName) { $machineColors[$machineId] = $colorPalette[$colorIndex % count($colorPalette)]; $colorIndex++; }

    $machineAllocations = [];
    foreach ($tasks as $task) {
        $machineId = $task['task'];
        $hasBreakdown = isset($activeBreakdowns[$machineId]);
        if (!isset($machineAllocations[$machineId])) $machineAllocations[$machineId] = ['current_time' => 0, 'tasks' => []];
        $scheduledTask = $task;
        $scheduledTask['has_breakdown'] = $hasBreakdown;
        $scheduledTask['breakdown_reason'] = $hasBreakdown ? $activeBreakdowns[$machineId] : '';
        $scheduledTask['position'] = $machineAllocations[$machineId]['current_time'];
        $machineAllocations[$machineId]['tasks'][] = $scheduledTask;
        $machineAllocations[$machineId]['current_time'] += $task['hours_to_schedule'];
    }
    $groupedTasks = [];
    foreach ($machineAllocations as $mid => $allocations) {
        foreach ($allocations['tasks'] as $t) { $groupedTasks[$mid][] = $t; }
    }

    $rowsHtml = '';
    foreach ($taskNames as $machineId => $taskName):
        $rowColor = $machineColors[$machineId];
        $rowsHtml .= '<div class="gantt-row" data-machine-id="' . $machineId . '" style="background-color: ' . $rowColor . ';">';

        if (!empty($groupedTasks[$machineId])):
            $cumulativePosition = 0;
            foreach ($groupedTasks[$machineId] as $task):
                $taskWidth = $task['hours_to_schedule'] * 80;
                $leftPosition = $cumulativePosition;
                $cumulativePosition += $taskWidth;

                $part_id = (int) $task['part_id'];
                $work_order_id = $task['work_order_id'];
                $parent_ids = $task['parent_ids'];
                $parent_part_number = $task['parent_part_number'];
                $setup_hours = $task['setup_hours'];
                $part_name = $task['product_name'];
                $quantity = $task['quantity'];
                $manufacture_end_date = $task['manufacture_end_date'];
                $route_detail = $task['manufacture_description'];
                $comma_customer_order_nos = $task['customer_order_nos'];

                $dotClass = 'red-dot-indicator'; $allInStock = true; $inventoryStatus = [];
                $invQuery = "SELECT im.part_number, COALESCE(i.quantity_in_stock, 0) AS quantity_in_stock
                             FROM bom b LEFT JOIN inventory i ON b.sub_part_id = i.item_id
                             LEFT JOIN item_management im ON b.sub_part_id = im.id WHERE b.main_product_id = $part_id";
                $invResult = $conn->query($invQuery);
                if (!$invResult) { $tooltipContent = 'Error fetching inventory'; }
                elseif ($invResult->num_rows > 0) {
                    while ($invRow = $invResult->fetch_assoc()) {
                        $pn = $invRow['part_number'] ?: 'Unknown Item';
                        $qty = (int) $invRow['quantity_in_stock'];
                        $inventoryStatus[] = "$pn: " . ($qty > 0 ? 'In Stock' : 'Out of Stock') . " (Qty: $qty)";
                        if ($qty <= 0) $allInStock = false;
                    }
                    $dotClass = $allInStock ? 'green-dot-indicator' : 'red-dot-indicator';
                    $tooltipContent = "Inventory Status:\n" . implode("\n", $inventoryStatus);
                } else { $tooltipContent = 'No BOM child parts linked.'; }

                $taskClasses = 'gantt-task';
                if ($task['is_break']) $taskClasses .= ' break-task';
                elseif ($task['is_late']) $taskClasses .= ' late-task';
                else $taskClasses .= ' on-time-task';

                $rowsHtml .= '<div class="' . $taskClasses . '" draggable="true"'
                    . ' data-task-id="' . htmlspecialchars($task['task_id']) . '"'
                    . ' data-priority="' . htmlspecialchars($task['priority']) . '"'
                    . ' data-part="' . htmlspecialchars($task['product_name'] ?? '') . '"'
                    . ' data-wo="' . htmlspecialchars($task['work_order_name'] ?? '') . '"'
                    . ' data-setting="' . htmlspecialchars($task['setting_instruction'] ?? '') . '"'
                    . ' data-work-order="' . htmlspecialchars($task['work_order_no'] ?? '') . '"'
                    . ' data-part-id="' . $part_id . '"'
                    . ' data-work-order-id="' . $work_order_id . '"'
                    . ' data-hours="' . $task['hours_to_schedule'] . '"'
                    . ' data-current-machine="' . $machineId . '"'
                    . ' style="left:' . $leftPosition . 'px; width:' . $taskWidth . 'px;">';

                if ($task['is_break']):
                    $rowsHtml .= '<div class="task-content"><strong>BREAK</strong><br/>'
                        . htmlspecialchars($task['break_reason'] ?? '') . '<br/>Duration: ' . $task['hours_to_schedule'] . 'h</div>';
                else:
                    $rowsHtml .= '<div class="dot-indicator ' . $dotClass . '" title="' . htmlspecialchars($tooltipContent) . '"></div>';
                    $rowsHtml .= '<div class="task-content" style="font-size:12px; line-height:1.4;">';
                    $rowsHtml .= '<div>' . ($parent_ids == 0 ? htmlspecialchars($comma_customer_order_nos ?? '') : htmlspecialchars($parent_part_number ?? '')) . '</div>';
                    $rowsHtml .= '<div>' . htmlspecialchars($task['work_order_name'] ?? '') . '</div>';
                    $rowsHtml .= '<div><strong>Item:</strong> ' . htmlspecialchars($part_name) . ' &nbsp;&nbsp;|&nbsp;&nbsp; <strong>Qty:</strong> ' . htmlspecialchars($quantity) . '</div>';
                    $rowsHtml .= '<div><strong>Set Hours:</strong> ' . htmlspecialchars($setup_hours ?? '') . ' &nbsp;&nbsp;|&nbsp;&nbsp; <strong>Setting Inst:</strong> ' . htmlspecialchars($task['setting_instruction'] ?? '') . '</div>';
                    if ($task['is_breakdown_affected']):
                        $rowsHtml .= '<div style="color:#ff0000;"><strong>Breakdown Delay: +' . $task['breakdown_hours'] . 'h</strong></div>';
                    endif;
                    $rowsHtml .= '<div><strong>Desc:</strong> ' . htmlspecialchars($route_detail ?? '') . '</div>';
                    $rowsHtml .= '<div><strong>Due Date:</strong> ' . (!empty($manufacture_end_date) ? date('d-M-Y', strtotime($manufacture_end_date)) : 'N/A') . '</div>';
                    $rowsHtml .= '</div>';
                endif;

                $rowsHtml .= '<div class="hour-indicator">' . $task['hours_to_schedule'] . 'h';
                if ($task['is_breakdown_affected']):
                    $rowsHtml .= '<br><small>+' . $task['breakdown_hours'] . 'h delay</small>';
                endif;
                $rowsHtml .= '</div></div>';
            endforeach;
        else:
            $rowsHtml .= '<div class="no-tasks">No tasks scheduled</div>';
        endif;
        $rowsHtml .= '</div>';
    endforeach;

    echo json_encode(['success' => true, 'html' => $rowsHtml]);
    exit;
}

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

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

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