<?php
$operations = [];

function getLowestSetRatioForBom($product_id, $set_ids, $conn)
{
    // Find the set this product belongs to
    $setId = null;
    foreach ($set_ids as $set) {
        if ($set['setItemId'] == $product_id) {
            $setId = $set['setId'];
            break;
        }
    }

    if ($setId === null) {
        return 1; // Not in any set → neutral multiplier
    }

    // Get all parts of this set with their ratios
    $setPartsRes = $conn->query("
        SELECT part_id, ratio
        FROM set_mangmnt_mapping
        WHERE set_id = $setId
    ");

    if (!$setPartsRes || $setPartsRes->num_rows === 0) {
        return 1;
    }

    $setParts = [];
    while ($r = $setPartsRes->fetch_assoc()) {
        $setParts[$r['part_id']] = (float)$r['ratio'];
    }

    // Get all sub_part_ids from BOM of the main_product_id
    // (we look at any level BOM that contains these set parts)
    $partIdList = implode(',', array_map('intval', array_keys($setParts)));
    if (empty($partIdList)) {
        return 1;
    }

    // Check which set parts appear in BOM (as sub_part_id of ANY parent)
    $bomCheckRes = $conn->query("
        SELECT DISTINCT sub_part_id
        FROM bom
        WHERE sub_part_id IN ($partIdList)
    ");

    if (!$bomCheckRes || $bomCheckRes->num_rows === 0) {
        return 1;
    }

    $lowestRatio = PHP_INT_MAX;
    while ($bomRow = $bomCheckRes->fetch_assoc()) {
        $bom_part_id = $bomRow['sub_part_id'];
        if (isset($setParts[$bom_part_id])) {
            $ratio = $setParts[$bom_part_id];
            if ($ratio < $lowestRatio) {
                $lowestRatio = $ratio;
            }
        }
    }

    return ($lowestRatio === PHP_INT_MAX) ? 1 : $lowestRatio;
}

/**
 * NEW HELPER: Get the ratio of a specific part within its set.
 * Returns 1 if the part is not in any set.
 */
function getPartSetRatio($product_id, $set_ids, $conn)
{
    $setId = null;
    foreach ($set_ids as $set) {
        if ($set['setItemId'] == $product_id) {
            $setId = $set['setId'];
            break;
        }
    }

    if ($setId === null) {
        return 1;
    }

    $res = $conn->query("
        SELECT ratio
        FROM set_mangmnt_mapping
        WHERE set_id = $setId
          AND part_id = $product_id
        LIMIT 1
    ");

    if ($res && $res->num_rows > 0) {
        $r = $res->fetch_assoc();
        return (float)$r['ratio'];
    }

    return 1;
}


function processOrderHybrid($product_ids, $order_quantities, $shift_ids, $conn, $required_date, $work_order_id, $simulateOnly, $set_ids, $depth = 0, $max_depth = 10, $parent_ids = []) {
    global $operations;
    global $work_order_id;

    $all_sub_parts = [];
    $all_can_fulfill = true;

    for ($i = 0; $i < count($product_ids); $i++) {
        $product_id      = $product_ids[$i];
        $quantity_needed = $order_quantities[$i];
        $current_parent_id = $parent_ids;

        $lowestRatio = getLowestSetRatioForBom($product_id, $set_ids, $conn);
        $partRatio   = getPartSetRatio($product_id, $set_ids, $conn);

        // route / machine quantity  (÷ lowest ratio so machine sees reduced qty)
        $route_qty = ($lowestRatio > 0) ? ceil($quantity_needed / $lowestRatio) : $quantity_needed;

        // raw material quantity  (route_qty × this part's own ratio)
        $raw_material_qty = ceil($route_qty * $partRatio);

        // ------------------------------------------------------------------

        $product_code = "Level" . str_pad(($depth - 1) * 10, 2, "0", STR_PAD_LEFT);
        $operations[] = [
            'product_code'  => $product_code,
            'product_id'    => $product_id,
            'parent_ids'    => is_array($current_parent_id) ? array_unique($current_parent_id) : [],
            'operation'     => "Processing $product_code for Product ID $product_id. Required Date: $required_date.",
            'qty'           => $route_qty,   // ← use route qty
            'process_type'  => 55,
            'required_date' => $required_date
        ];

        $query_p  = "SELECT part_number FROM item_management WHERE id = $product_id";
        $result_p = $conn->query($query_p);
        $p_name   = '';
        if ($result_p && $result_p->num_rows > 0) {
            $row_p  = $result_p->fetch_assoc();
            $p_name = $row_p['part_number'];
        }

        // Route time is calculated on route_qty (reduced machine quantity)
        $route_data       = calculateRouteTime($product_id, $route_qty, $conn, $set_ids);
        $route_time_hours = $route_data['route_time'];
        $machine_id       = $route_data['machine'];

        $machine = '';
        $query_machine  = "SELECT id, resource_code FROM station_detail_table WHERE id = '$machine_id'";
        $result_machine = $conn->query($query_machine);
        if ($result_machine && $result_machine->num_rows > 0) {
            while ($row = $result_machine->fetch_assoc()) {
                $machine = $row['resource_code'];
            }
        }

        $work_schedule  = arrangeWorkAcrossDaysWithStartDate($route_time_hours, $required_date, $shift_ids, $conn);
        $start_date     = date('Y-m-d', strtotime($work_schedule['start_date']));
        $days_allocation = $work_schedule['days_allocation'];

        if ($route_time_hours > 0) {
            $operations[] = [
                'product_code'  => $product_code,
                'product_name'  => $p_name,
                'product_id'    => $product_id,
                'parent_ids'    => array_unique($current_parent_id),
                'operation'     => "Route time: " . round($route_time_hours, 2) . " hours (100% efficiency), on machine $machine.",
                'qty'           => $route_qty,        // ← machine sees route qty
                'process_type'  => 6,
                'machine'       => $machine_id,
                'Start_Date'    => $start_date,
                'total_time'    => $route_time_hours,
                'required_date' => $required_date
            ];
        }

        foreach ($days_allocation as $index => $day) {
            $day_name  = date('l', strtotime("Sunday +{$day['day']} days"));
            $highlight = null;
            if ($index === 0) {
                $highlight = 121;
            } elseif ($index === 2) {
                $highlight = 131;
            } elseif ($index === 3) {
                $highlight = 141;
            } elseif ($index === array_key_last($days_allocation)) {
                $highlight = 151;
            }

            $operations[] = [
                'product_code'   => $product_code,
                'product_id'     => $product_id,
                'parent_ids'     => is_array($current_parent_id) ? array_unique($current_parent_id) : [],
                'operation'      => "Allocated {$day['hours_allocated']} hours of work on {$day['date']} ({$day_name}).",
                'qty'            => $route_qty,
                'process_type'   => 7,
                'required_date'  => $required_date,
                'highlight'      => $highlight,
                'allocated_dates'=> $day['date']
            ];
        }

        // BOM explosion
        $query    = "SELECT sub_part_id, sub_part_name, quantity_required, type FROM bom WHERE main_product_id = $product_id";
        $bom_parts = $conn->query($query);

        if ($bom_parts->num_rows > 0) {
            if ($depth >= $max_depth) {
                $all_can_fulfill = $all_can_fulfill && processOrderStack($product_id, $route_qty, $conn);
                continue;
            }

            while ($bom = $bom_parts->fetch_assoc()) {
                $sub_part_id   = $bom['sub_part_id'];
                $sub_part_name = $bom['sub_part_name'];
                $type          = $bom['type'];

                $sub_quantity_needed = $raw_material_qty * $bom['quantity_required'];

                $sub_required_date = date('Y-m-d', strtotime($start_date));

                if (!isset($all_sub_parts[$sub_part_id])) {
                    $all_sub_parts[$sub_part_id] = [
                        'name'          => $sub_part_name,
                        'quantity'      => 0,
                        'type'          => $type,
                        'required_date' => $sub_required_date,
                        'parent_ids'    => [],
                        'product_ids'   => []
                    ];
                }

                $all_sub_parts[$sub_part_id]['quantity'] += $sub_quantity_needed;

                if (strtotime($sub_required_date) < strtotime($all_sub_parts[$sub_part_id]['required_date'])) {
                    $all_sub_parts[$sub_part_id]['required_date'] = $sub_required_date;
                }

                if ($current_parent_id) {
                    $all_sub_parts[$sub_part_id]['parent_ids'][] = $current_parent_id;
                }
                $all_sub_parts[$sub_part_id]['parent_ids'][]  = $product_id;
                $all_sub_parts[$sub_part_id]['product_ids'][] = $product_id;
            }

        } else {
            // No BOM → raw material leaf node
            $operations[] = [
                'product_code'  => $product_code,
                'product_id'    => $product_id,
                'parent_ids'    => is_array($current_parent_id) ? array_unique($current_parent_id) : [],
                'operation'     => "Raw Material $product_code (Quantity: $raw_material_qty, Required Date: $required_date).",
                'qty'           => $raw_material_qty,   // ← raw material qty
                'process_type'  => 44,
                'required_date' => $required_date
            ];
            $all_can_fulfill = false;
        }
    }

    // Process accumulated sub-parts
    foreach ($all_sub_parts as $sub_part_id => $sub_part_data) {
        $sub_product_code  = "Level" . str_pad(($depth - 2) * 10, 2, "0", STR_PAD_LEFT);
        $unique_parent_ids = array_unique($sub_part_data['parent_ids']);

        $operations[] = [
            'product_code'  => $sub_product_code,
            'product_id'    => $sub_part_id,
            'parent_ids'    => $unique_parent_ids,
            'operation'     => "Processing $sub_product_code for Sub-Part ID $sub_part_id (Name: {$sub_part_data['name']}, Type: {$sub_part_data['type']}, Qty: {$sub_part_data['quantity']}, Required Date: {$sub_part_data['required_date']}).",
            'qty'           => $sub_part_data['quantity'],
            'process_type'  => 5,
            'required_date' => $sub_part_data['required_date']
        ];

        if ($sub_part_data['type'] === "Bought In") {
            $operations[] = [
                'product_code'  => $sub_product_code,
                'product_name'  => $sub_part_data['name'],
                'product_id'    => $sub_part_id,
                'parent_ids'    => $unique_parent_ids,
                'operation'     => "Raw Material {$sub_part_data['quantity']} units of Sub-Part ID $sub_part_id.",
                'qty'           => $sub_part_data['quantity'],
                'process_type'  => 4,
                'required_date' => $sub_part_data['required_date']
            ];
            $all_can_fulfill = false;

        } elseif ($sub_part_data['type'] === "Manufactured" || $sub_part_data['type'] === "Assembly") {
            $result = processOrderHybrid(
                [$sub_part_id],
                [$sub_part_data['quantity']],
                $shift_ids,
                $conn,
                $sub_part_data['required_date'],
                $work_order_id,
                $simulateOnly,
                $set_ids,
                $depth - 1,
                $max_depth,
                $unique_parent_ids
            );
            $all_can_fulfill = $all_can_fulfill && $result;
        }
    }

    return $all_can_fulfill;
}


function arrangeWorkAcrossDaysWithStartDate($total_hours, $required_date, $shift_ids, $conn) {
    $working_hours   = calculateShiftHours($shift_ids, $conn);
    $remaining_hours = $total_hours;

    $query_h  = "SELECT holiday_date FROM admin_holidays";
    $result_h = $conn->query($query_h);
    $holidays = [];
    if ($result_h && $result_h->num_rows > 0) {
        while ($row = $result_h->fetch_assoc()) {
            $holidays[] = $row['holiday_date'];
        }
    }

    $days_allocation  = [];
    $current_date     = new DateTime($required_date);
    $start_date_found = false;
    $start_date       = null;

    while (!$start_date_found && $remaining_hours > 0) {
        $current_day         = (int) $current_date->format('w');
        $current_date_string = $current_date->format('Y-m-d');

        if (!in_array($current_date_string, $holidays, true) && $working_hours[$current_day] > 0) {
            $hours_for_day = $working_hours[$current_day];

            if ($remaining_hours <= $hours_for_day) {
                $days_allocation[] = [
                    'date'            => $current_date_string,
                    'day'             => $current_day,
                    'hours_allocated' => $remaining_hours
                ];
                $start_date       = $current_date_string;
                $remaining_hours  = 0;
                $start_date_found = true;
            } else {
                $days_allocation[] = [
                    'date'            => $current_date_string,
                    'day'             => $current_day,
                    'hours_allocated' => $hours_for_day
                ];
                $remaining_hours -= $hours_for_day;
                $start_date       = $current_date_string;
            }
        } else {
            $start_date = $current_date_string;
        }

        if (!$start_date_found) {
            $current_date->modify('-1 day');
        }
    }

    while ($remaining_hours > 0) {
        $current_date->modify('-1 day');
        $current_day         = (int) $current_date->format('w');
        $current_date_string = $current_date->format('Y-m-d');

        if (!in_array($current_date_string, $holidays, true) && $working_hours[$current_day] > 0) {
            $hours_for_day = $working_hours[$current_day];

            if ($remaining_hours <= $hours_for_day) {
                $days_allocation[] = [
                    'date'            => $current_date_string,
                    'day'             => $current_day,
                    'hours_allocated' => $remaining_hours
                ];
                $start_date      = $current_date_string;
                $remaining_hours = 0;
            } else {
                $days_allocation[] = [
                    'date'            => $current_date_string,
                    'day'             => $current_day,
                    'hours_allocated' => $hours_for_day
                ];
                $remaining_hours -= $hours_for_day;
                $start_date       = $current_date_string;
            }
        }
    }

    // Add 3 buffer working days before start
    $extra_days_added = 0;
    $buffer_date      = new DateTime($start_date);

    while ($extra_days_added < 3) {
        $buffer_date->modify('-1 day');
        $current_day         = (int) $buffer_date->format('w');
        $current_date_string = $buffer_date->format('Y-m-d');

        if (!in_array($current_date_string, $holidays, true) && $working_hours[$current_day] > 0) {
            array_unshift($days_allocation, [
                'date'            => $current_date_string,
                'day'             => $current_day,
                'hours_allocated' => $working_hours[$current_day]
            ]);
            $start_date = $current_date_string;
            $extra_days_added++;
        }
    }

    return [
        'start_date'     => $start_date,
        'days_allocation'=> array_reverse($days_allocation)
    ];
}


function calculateShiftHours($ids, $conn) {
    $working_hours = [
        0 => 0, 1 => 0, 2 => 0,
        3 => 0, 4 => 0, 5 => 0, 6 => 0
    ];

    foreach ($ids as $id) {
        $query  = "SELECT day_shifts, hours FROM working_hours WHERE id = $id";
        $result = $conn->query($query);

        if ($result && $result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $day_shift = $row['day_shifts'];
                $hours     = $row['hours'];
                if (isset($working_hours[$day_shift])) {
                    $working_hours[$day_shift] += $hours;
                }
            }
        }
    }

    return $working_hours;
}


function calculateRouteTime($product_id, $quantity_needed, $conn, $set_ids) {
    $setId = null;
    foreach ($set_ids as $set) {
        if ($set['setItemId'] == $product_id) {
            $setId = $set['setId'];
            break;
        }
    }

    if ($setId !== null) {
        $query = "SELECT operation_time, parts_per_operation, machine_high_priority FROM set_route_details WHERE set_id = $setId";
    } else {
        $query = "SELECT operation_time, parts_per_operation, machine_high_priority FROM route_details WHERE item_id = $product_id";
    }

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

    if ($result && $result->num_rows > 0) {
        $row     = $result->fetch_assoc();
        $machine = $row['machine_high_priority'];

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

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

        $quantity_needed    = !empty($quantity_needed) ? (float)$quantity_needed : 1;
        $route_time_seconds = ($operation_time / $parts_per_operation) * $quantity_needed;
        $route_time_hours   = $route_time_seconds / 3600;

        return [
            'route_time' => $route_time_hours,
            'machine'    => $machine
        ];
    }

    return [
        'route_time' => 0,
        'machine'    => null
    ];
}

function processOrderStack($product_id, $quantity_needed, $conn) {
    global $operations;

    $stack = [];
    array_push($stack, [
        'product_id'      => $product_id,
        'quantity_needed' => $quantity_needed,
        'depth'           => 0,
        'required_date'   => date('Y-m-d'),
    ]);

    while (!empty($stack)) {
        $current              = array_pop($stack);
        $current_product_id   = $current['product_id'];
        $current_quantity_needed = $current['quantity_needed'];
        $depth                = $current['depth'];
        $required_date        = $current['required_date'];

        $current_product_code = "Level" . str_pad(($depth + 1) * 10, 2, "0", STR_PAD_LEFT);

        $operations[] = [
            'product_code'  => $current_product_code,
            'product_id'    => $current_product_id,
            'operation'     => "Processing $current_product_code for Product ID $current_product_id. Required Date: $required_date.",
            'qty'           => $current_quantity_needed,
            'process_type'  => 888,
            'required_date' => $required_date
        ];

        $query     = "SELECT sub_part_id, sub_part_name, quantity_required, type FROM bom WHERE main_product_id = $current_product_id";
        $bom_parts = $conn->query($query);

        if ($bom_parts->num_rows > 0) {
            while ($bom = $bom_parts->fetch_assoc()) {
                $sub_part_id         = $bom['sub_part_id'];
                $sub_part_name       = $bom['sub_part_name'];
                $sub_quantity_needed = $current_quantity_needed * $bom['quantity_required'];
                $type                = $bom['type'];

                $sub_product_code  = "Level" . str_pad(($depth + 2) * 10, 2, "0", STR_PAD_LEFT);
                $sub_required_date = date('Y-m-d', strtotime($required_date . " -2 days"));

                $operations[] = [
                    'product_code'  => $sub_product_code,
                    'operation'     => "Processing $sub_product_code for Sub-Part ID $sub_part_id (Name: $sub_part_name, Type: $type, Qty: $sub_quantity_needed, Required Date: $sub_required_date).",
                    'qty'           => $sub_quantity_needed,
                    'process_type'  => 888,
                    'required_date' => $sub_required_date
                ];

                if ($type === "Bought In") {
                    $operations[] = [
                        'product_code'  => $sub_product_code,
                        'operation'     => "Raw Material $sub_part_id.",
                        'qty'           => $sub_quantity_needed,
                        'process_type'  => 888,
                        'required_date' => $sub_required_date
                    ];
                } elseif ($type === "Manufactured" || $type === "Assembly") {
                    array_push($stack, [
                        'product_id'      => $sub_part_id,
                        'quantity_needed' => $sub_quantity_needed,
                        'depth'           => $depth + 1,
                        'required_date'   => $sub_required_date
                    ]);
                }
            }
        } else {
            $operations[] = [
                'product_code'  => $current_product_code,
                'operation'     => "Raw Material $current_product_id.",
                'qty'           => $current_quantity_needed,
                'process_type'  => 4,
                'required_date' => $required_date
            ];
        }
    }

    return true;
}
?>