<?php
ob_start();
include("../../../include/startup.php");
ob_clean();
header('Content-Type: application/json');
error_reporting(0);

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

/**
 * Resolve the material actually consumed for a given BOM/set sub-part,
 * matching the same substitution logic used in ajax_actions_vac.php.
 *
 * If this work order's raw material was swapped on the "Raw Materials"
 * edit screen (work_order_raw_matrial.is_change = 1, with
 * original_product_id = the BOM sub-part id), the NEW product_id is
 * returned instead of the original one. Otherwise the original id is
 * returned unchanged.
 *
 * $setId must match how work_order_raw_matrial rows are scoped
 * (work_order_id + original_product_id + set_id), so callers must pass
 * the set_id that applies to this task (0 for non-set tasks).
 */
if (!function_exists('getEffectiveMaterialId')) {
    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']);
            }
        }

        // No substitution on record — use the original BOM part as-is
        return $originalPartId;
    }
}

/**
 * Walk the BOM tree collecting "Bought In" (product_group = 17) material
 * names for display. Substitution-aware: if a sub-part's material was
 * swapped for this specific work order (see getEffectiveMaterialId()),
 * the NEW part's part_number is shown instead of the original bom name,
 * and each entry is flagged so the UI can mark it visually.
 */
function getAllBoughtInMaterials($conn, $part_id, $work_order_id, $set_id = 0, &$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)) {

        $original_sub_part_id = intval($row['sub_part_id']);

        // If this raw material was substituted for this work order,
        // resolve to the NEW part id. The BOM "role" (type / product_group)
        // still comes from the original position — only the identity
        // actually consumed/displayed changes.
        $effective_sub_part_id = $original_sub_part_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;
            }
        }

        // Only collect Bought In materials with product_group = 17
        if (
            $row['type'] === 'Bought In' &&
            $row['product_group'] == 17 &&
            !empty($display_name)
        ) {
            $materials[$display_name] = [
                'name'        => $display_name,
                'substituted' => $is_substituted,
            ];
        }

        // Always recurse Manufactured parts (recurse on the effective id,
        // in case an intermediate manufactured part was itself substituted)
        if (
            $row['type'] === 'Manufactured' &&
            !empty($original_sub_part_id)
        ) {
            $sub_materials = getAllBoughtInMaterials(
                $conn,
                $effective_sub_part_id,
                $work_order_id,
                $set_id,
                $visited
            );
            foreach ($sub_materials as $sm) {
                $materials[$sm['name']] = $sm;
            }
        }
    }

    // Re-index as a plain array (was keyed by name to dedupe)
    return array_values($materials);
}

// ✅ Handle POST action FIRST, before anything 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']) ? intval($_REQUEST['left_neighbor_priority']) : null;
    $leftNeighborTaskId   = isset($_REQUEST['left_neighbor_task_id'])  ? intval($_REQUEST['left_neighbor_task_id'])  : null;

    $draggedTask = $conn->query("SELECT * FROM vac_machine_table WHERE id = $taskId")->fetch_assoc();
    $todays = date('Y-m-d');

    // Get tasks on the DESTINATION machine (excluding the dragged card itself)
    $newResult = mysqli_query($conn,
        "SELECT id, priority 
         FROM vac_machine_table 
         WHERE machine_id = $newMachineId
           AND id != $taskId
           AND end_date >= '$todays'
         ORDER BY priority"
    );
    $newTasks = [];
    while ($row = mysqli_fetch_assoc($newResult)) {
        $newTasks[] = $row;
    }

    // Determine insert priority
    $insertPriority = 1;
    if ($leftNeighborPriority !== null) {
        $insertPriority = $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']}"
                );
            }
        }
    } else {
        // Dropping at top — shift everything down
        foreach ($newTasks as $task) {
            mysqli_query($conn,
                "UPDATE vac_machine_table SET priority = priority + 1 WHERE id = {$task['id']}"
            );
        }
    }

    // Setup hours logic
    $draggedTaskDetails = $conn->query("SELECT setting_instruction, total_hours, is_break FROM vac_machine_table WHERE id = $taskId")->fetch_assoc();
    $setting_instruction = $draggedTaskDetails['setting_instruction'];
    $total_hours = (float)$draggedTaskDetails['total_hours'];
    $is_break    = (int)$draggedTaskDetails['is_break'];

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

        if ($previous_setting_instruction !== null) {
            $total_hours += (strcasecmp($previous_setting_instruction, $setting_instruction) === 0) ? 1.00 : 2.00;
        } else {
            $total_hours += 2.00;
        }
    }

    // Apply the move
    $originalMachineId = $draggedTask['machine_id'];
    mysqli_query($conn,
        "UPDATE vac_machine_table 
         SET machine_id = $newMachineId, priority = $insertPriority
         WHERE id = $taskId"
    );

    // Recalculate dates for both machines
    recalculateMachineDatesforvac($conn, $originalMachineId);
    recalculateMachineDatesforvac($conn, $newMachineId);

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

// ============================================================
// LIVE UPH / QTY MADE CONFIG
// ------------------------------------------------------------
// NOTE: vac_part_count_log.vac_machine_table_id stores the
// manuf_id of vac_machine_table (NOT vac_machine_table.id).
// This matches the convention already documented inside
// recalculateMachineDatesforvac() in ajax_actions_vac.php.
// Both the Qty Made subquery and the Live UPH subquery below
// correlate on mmt.manuf_id for this reason.
// ============================================================
define('VAC_LIST_UPH_WINDOW_MINUTES', 60);

try {
    // Get filter parameters (use 'current_page' to avoid conflict with routing 'page' parameter)
    $current_page = isset($_GET['current_page']) ? max(1, (int)$_GET['current_page']) : 1;
    $per_page = isset($_GET['per_page']) ? max(1, (int)$_GET['per_page']) : 50;
    $offset = max(0, ($current_page - 1) * $per_page);

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

    // Build WHERE conditions
    $where_conditions = ["mmt.end_date >= '$start_date'", "(mmt.is_break = 0 OR mmt.is_break IS NULL)"];

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

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

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

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

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

    // Count total records
    $count_query = "SELECT COUNT(DISTINCT mmt.id) as total
    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 station_detail_table sd ON mmt.machine_id = sd.id
    WHERE $where_clause";

    $count_result = $conn->query($count_query);
    if (!$count_result) {
        throw new Exception($conn->error);
    }
    
    $total_records = $count_result->fetch_assoc()['total'];
    $total_pages = ceil($total_records / $per_page);

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

    $query = "SELECT 
        mmt.machine_id AS task, 
        mmt.id as task_id, 
        mmt.manuf_id,
        wo.work_order_no, 
        mmt.part_id,
        mmt.set_id,
        wo.require_date,
        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.finished,
        won.work_order_name,  
        mmt.shift_ids AS shift_name, 
        DATE(mmt.start_date) AS start_date, 
        DATE(mmt.end_date) AS end_date,
        TIME(mmt.start_date) AS start_time,
        mmt.is_break,
        mmt.break_reason,
        mmt.is_breakdown_affected,
        mmt.breakdown_hours,
        mb.id as breakdown_id,
        mb.breakdown_reason as machine_breakdown_reason,
        m.end_date as manufacture_end_date,
        m.description as manufacture_description,
        m.parent_ids,
        im.part_number as parent_part_number,
        sd.resource_code as machine_name,
        GROUP_CONCAT(DISTINCT wocr.work_order_no ORDER BY wocr.work_order_no SEPARATOR ', ') as customer_order_nos,

        -- Total OK units ever logged for this specific scheduled task
        -- (manuf_id is the correct join key — see note above define())
        COALESCE((
            SELECT SUM(vpcl.quantity)
            FROM vac_part_count_log vpcl
            WHERE vpcl.work_order_id = wo.id
              AND vpcl.vac_machine_table_id = mmt.manuf_id
              AND vpcl.part_id = mmt.part_id
              AND vpcl.type = 'ok'
        ), 0) as quantity_made,

        -- ⭐ CORRECTED: OK units logged in the last N minutes using PHP $windowStart
        COALESCE((
            SELECT SUM(vpcl.quantity)
            FROM vac_part_count_log vpcl
            WHERE vpcl.work_order_id = wo.id
              AND vpcl.vac_machine_table_id = mmt.manuf_id
              AND vpcl.part_id = mmt.part_id
              AND vpcl.type = 'ok'
              AND vpcl.created_at >= '$windowStart'
        ), 0) as ok_units_last_window

    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 
        station_detail_table sd ON mmt.machine_id = sd.id
    LEFT JOIN (
        SELECT 
            m1.part_id, 
            m1.work_order_id, 
            m1.end_date, 
            m1.description, 
            m1.parent_ids
        FROM manufacture_machine_table m1
        INNER JOIN (
            SELECT 
                part_id, 
                work_order_id, 
                MAX(end_date) as max_end_date
            FROM manufacture_machine_table
            GROUP BY part_id, work_order_id
        ) m2 ON m1.part_id = m2.part_id 
            AND m1.work_order_id = m2.work_order_id 
            AND m1.end_date = m2.max_end_date
    ) m ON mmt.part_id = m.part_id 
        AND mmt.work_order_id = m.work_order_id
    LEFT JOIN 
        item_management im ON m.parent_ids = im.id
    LEFT JOIN 
        work_order_customer_ref_mapping wocr ON mmt.work_order_id = wocr.work_order_id
    WHERE 
        $where_clause
    GROUP BY 
        mmt.id, mmt.machine_id, mmt.manuf_id, wo.work_order_no, mmt.part_id, mmt.set_id, mmt.setup_hours,
        mmt.setting_instruction, mmt.priority, mmt.total_hours, mmt.work_order_id,
        mmt.product_name, mmt.quantity, mmt.finished,
        won.work_order_name, mmt.shift_ids, mmt.start_date,
        mmt.end_date, mmt.is_break, mmt.break_reason, mmt.is_breakdown_affected,
        mmt.breakdown_hours, mb.id, mb.breakdown_reason, m.end_date,
        m.description, im.part_number, sd.resource_code
    ORDER BY 
        mmt.priority ASC, mmt.start_date ASC
    LIMIT $per_page OFFSET $offset";

    $result = $conn->query($query);

    if (!$result) {
        throw new Exception($conn->error);
    }

    // Build response
    $work_orders = [];
    while ($row = $result->fetch_assoc()) {
        $part_id       = intval($row['part_id']);
        $work_order_id = intval($row['work_order_id']);
        $set_id        = intval($row['set_id'] ?? 0);

        // Bought-in materials (product_group = 17), substitution-aware
        $row['bought_in_materials'] = getAllBoughtInMaterials($conn, $part_id, $work_order_id, $set_id);

        // Inventory dots: each BOM sub-part → stock status, substitution-aware
        $invDots = [];
        if ($part_id > 0) {
            $subPartRes = $conn->query("
                SELECT sub_part_id
                FROM bom
                WHERE main_product_id = $part_id
            ");
            if ($subPartRes) {
                while ($sp = $subPartRes->fetch_assoc()) {
                    $original_sub_id = intval($sp['sub_part_id']);
                    if ($original_sub_id <= 0) continue;

                    // Resolve to the substituted part for THIS work order, if any
                    $effective_sub_id = getEffectiveMaterialId($conn, $work_order_id, $original_sub_id, $set_id);
                    $is_substituted   = ($effective_sub_id !== $original_sub_id);

                    $detailRow = $conn->query("
                        SELECT im.part_number,
                               COALESCE(i.quantity_in_stock, 0) AS quantity_in_stock
                        FROM item_management im
                        LEFT JOIN inventory i ON i.item_id = im.id
                        WHERE im.id = $effective_sub_id
                    ")->fetch_assoc();

                    $invDots[] = [
                        'part_number' => $detailRow['part_number'] ?? '',
                        'in_stock'    => $detailRow ? ((float)$detailRow['quantity_in_stock'] > 0) : false,
                        'substituted' => $is_substituted,
                    ];
                }
            }
        }
        $row['inventory_dots'] = $invDots;

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

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

        // ⭐ FIX: Determine if Live UPH should be grey (no active production)
        $isFinished = (int)$row['finished'] == 1;
        $hasProductionStarted = (int)$row['quantity_made'] > 0;
        
        // Grey if: 
        // 1. Task is finished, OR
        // 2. No production has started yet (pending)
        $isGrey = $isFinished || !$hasProductionStarted;

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

        $work_orders[] = $row;
    }

    // Clean output buffer and send JSON
    ob_clean();
    echo json_encode([
        'success' => true,
        'data' => $work_orders,
        'pagination' => [
            'current_page' => $current_page,
            'per_page' => $per_page,
            'total_records' => $total_records,
            'total_pages' => $total_pages,
            'offset' => $offset,
            'showing_from' => $offset + 1,
            'showing_to' => min($offset + $per_page, $total_records)
        ]
    ]);
    
} catch (Exception $e) {
    // Clean output buffer and send error JSON
    ob_clean();
    echo json_encode([
        'success' => false,
        'error' => $e->getMessage()
    ]);
}

// End output buffering
ob_end_flush();
?>