<?php
/**
 * Order Processing Functions
 * Shared functions for BOM explosion, purchase list generation, and routing calculations
 * Used by both CSV bulk import and manual order addition
 */

// Global operations array for tracking processing steps
if (!isset($operations)) {
    $operations = [];
}

/**
 * Process order with BOM explosion, purchase list generation, and routing
 * 
 * @param array $product_ids Array of product IDs to process
 * @param array $order_quantities Array of quantities for each product
 * @param array $shift_ids Array of shift IDs from working_hours table
 * @param mysqli $conn Database connection
 * @param string $required_date Required completion date (Y-m-d format)
 * @param int $work_order_id Work order ID for linking purchase list
 * @param int $simulateOnly 1 to insert into purchase_list, 0 to skip
 * @param array $set_ids Array of set configurations
 * @param int $depth Current recursion depth
 * @param int $max_depth Maximum recursion depth
 * @param array $parent_ids Array of parent product IDs
 * @return bool True if order can be fulfilled
 */
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;

    // Validate inputs
    if (empty($product_ids) || empty($order_quantities) || count($product_ids) !== count($order_quantities)) {
        error_log("Invalid input: product_ids and order_quantities must be non-empty arrays of equal length");
        return false;
    }

    $all_sub_parts = [];
    $all_can_fulfill = true;

    // Process each product
    foreach ($product_ids as $i => $product_id) {
        $quantity_needed = (int)$order_quantities[$i];
        if ($quantity_needed <= 0) {
            continue; // Skip invalid quantities
        }

        $current_parent_id = $parent_ids;
        $product_code = "Level" . str_pad(max(0, ($depth - 1) * 10), 2, "0", STR_PAD_LEFT);

        // Get product name
        $stmt = $conn->prepare("SELECT part_number FROM item_management WHERE id = ?");
        $stmt->bind_param("i", $product_id);
        $stmt->execute();
        $result = $stmt->get_result();
        $p_name = $result->num_rows > 0 ? $result->fetch_assoc()['part_number'] : "Unknown";
        $stmt->close();

        $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' => $quantity_needed,
            'process_type' => 55,
            'required_date' => $required_date
        ];

        // Calculate route time and schedule work
        $route_data = calculateRouteTime($product_id, $quantity_needed, $conn, $set_ids);
        $route_time_hours = $route_data['route_time'];
        $machine_id = $route_data['machine'];

        // Get machine name
        $machine_name = "Unknown";
        if ($machine_id) {
            $stmt = $conn->prepare("SELECT resource_code FROM station_detail_table WHERE id = ?");
            $stmt->bind_param("i", $machine_id);
            $stmt->execute();
            $result = $stmt->get_result();
            if ($result->num_rows > 0) {
                $machine_name = $result->fetch_assoc()['resource_code'];
            }
            $stmt->close();
        }

        // Schedule work across days
        $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_name.",
                'qty' => $quantity_needed,
                'process_type' => 6,
                'machine' => $machine_id,
                'Start_Date' => $start_date,
                'total_time' => $route_time_hours,
                'required_date' => $required_date
            ];
        }

        // Log daily allocations with highlights
        foreach ($days_allocation as $index => $day) {
            $day_name = date('l', strtotime("Sunday +{$day['day']} days"));
            
            // Determine highlight based on position
            $highlight = null;
            if ($index === 0) {
                $highlight = 121; // First day
            } elseif ($index === 2) {
                $highlight = 131; // Third day
            } elseif ($index === 3) {
                $highlight = 141; // Fourth day
            } elseif ($index === array_key_last($days_allocation)) {
                $highlight = 151; // Last day
            }

            $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' => $quantity_needed,
                'process_type' => 7,
                'required_date' => $required_date,
                'highlight' => $highlight,
                'allocated_dates' => $day['date']
            ];
        }

        // Get BOM (Bill of Materials) for this product
        $stmt = $conn->prepare("SELECT sub_part_id, sub_part_name, quantity_required, type FROM bom WHERE main_product_id = ?");
        $stmt->bind_param("i", $product_id);
        $stmt->execute();
        $bom_parts = $stmt->get_result();

        if ($bom_parts->num_rows > 0) {
            // Check recursion depth
            if ($depth >= $max_depth) {
                error_log("Max recursion depth reached for product $product_id, switching to stack processing");
                $all_can_fulfill = $all_can_fulfill && processOrderStack($product_id, $quantity_needed, $conn);
                $stmt->close();
                continue;
            }

            // Accumulate sub-parts
            while ($bom = $bom_parts->fetch_assoc()) {
                $sub_part_id = (int)$bom['sub_part_id'];
                $sub_part_name = $bom['sub_part_name'];
                $sub_quantity_needed = $quantity_needed * (float)$bom['quantity_required'];
                $type = $bom['type'];
                $sub_required_date = date('Y-m-d', strtotime($start_date));

                // Initialize or update sub-part tracking
                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' => []
                    ];
                }

                // Accumulate quantities and find earliest required date
                $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;
                }

                // Track parent relationships
                if (!empty($current_parent_id)) {
                    $all_sub_parts[$sub_part_id]['parent_ids'] = array_merge(
                        $all_sub_parts[$sub_part_id]['parent_ids'], 
                        is_array($current_parent_id) ? $current_parent_id : [$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 - this is a raw material that needs to be purchased
            if ($simulateOnly == 1) {
                $stmt_insert = $conn->prepare("INSERT INTO purchase_list (item_id, quantity_requested, required_date, work_order_id) VALUES (?, ?, ?, ?)");
                $stmt_insert->bind_param("idsi", $product_id, $quantity_needed, $required_date, $work_order_id);
                $stmt_insert->execute();
                $stmt_insert->close();
            }

            $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: $quantity_needed, Required Date: $required_date).",
                'qty' => $quantity_needed,
                'process_type' => 44,
                'required_date' => $required_date
            ];
            $all_can_fulfill = false;
        }
        $stmt->close();
    }

    // Process accumulated sub-parts
    foreach ($all_sub_parts as $sub_part_id => $sub_part_data) {
        $sub_product_code = "Level" . str_pad(max(0, ($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") {
            // Purchase bought-in parts
            if ($simulateOnly == 1) {
                $stmt = $conn->prepare("INSERT INTO purchase_list (item_id, quantity_requested, required_date, work_order_id) VALUES (?, ?, ?, ?)");
                $stmt->bind_param("idsi", $sub_part_id, $sub_part_data['quantity'], $sub_part_data['required_date'], $work_order_id);
                $stmt->execute();
                $stmt->close();
            }

            $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") {
            // Recursively process manufactured/assembly parts
            $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;
}

/**
 * Arrange work across days with proper start date calculation
 * Works backward from required date considering working hours and holidays
 * 
 * @param float $total_hours Total hours of work needed
 * @param string $required_date Required completion date
 * @param array $shift_ids Array of shift IDs
 * @param mysqli $conn Database connection
 * @return array Contains start_date and days_allocation
 */
function arrangeWorkAcrossDaysWithStartDate($total_hours, $required_date, $shift_ids, $conn) {
    $working_hours = calculateShiftHours($shift_ids, $conn);
    $remaining_hours = $total_hours;

    // Get holidays
    $holidays = [];
    $result = $conn->query("SELECT holiday_date FROM admin_holidays");
    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $holidays[] = $row['holiday_date'];
        }
    }

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

    // Work backwards from required date to find start date
    while ($remaining_hours > 0) {
        $current_day = (int)$current_date->format('w');
        $current_date_string = $current_date->format('Y-m-d');

        // Check if it's a working day
        if (!in_array($current_date_string, $holidays, true) && isset($working_hours[$current_day]) && $working_hours[$current_day] > 0) {
            $hours_for_day = $working_hours[$current_day];

            if ($remaining_hours <= $hours_for_day) {
                // This day can complete the remaining work
                $days_allocation[] = [
                    'date' => $current_date_string,
                    'day' => $current_day,
                    'hours_allocated' => round($remaining_hours, 2)
                ];
                $start_date = $current_date_string;
                $remaining_hours = 0;
            } else {
                // Allocate full day and continue
                $days_allocation[] = [
                    'date' => $current_date_string,
                    'day' => $current_day,
                    'hours_allocated' => round($hours_for_day, 2)
                ];
                $remaining_hours -= $hours_for_day;
                $start_date = $current_date_string;
            }
        } else {
            // Non-working day
            $start_date = $current_date_string;
        }

        $current_date->modify('-1 day');
    }

    // Add 3 extra working days as buffer
    $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) && isset($working_hours[$current_day]) && $working_hours[$current_day] > 0) {
            array_unshift($days_allocation, [
                'date' => $current_date_string,
                'day' => $current_day,
                'hours_allocated' => round($working_hours[$current_day], 2)
            ]);
            $start_date = $current_date_string;
            $extra_days_added++;
        }
    }

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

/**
 * Calculate working hours for each day of the week
 * 
 * @param array $ids Array of working_hours table IDs
 * @param mysqli $conn Database connection
 * @return array Working hours indexed by day (0=Sunday, 6=Saturday)
 */
function calculateShiftHours($ids, $conn) {
    $working_hours = array_fill(0, 7, 0); // Initialize all days to 0 hours

    if (empty($ids)) {
        return $working_hours;
    }

    $ids_str = implode(',', array_map('intval', $ids));
    $query = "SELECT day_shifts, hours FROM working_hours WHERE id IN ($ids_str)";
    $result = $conn->query($query);

    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $day_shift = (int)$row['day_shifts'];
            $hours = (float)$row['hours'];

            if ($day_shift >= 0 && $day_shift <= 6) {
                $working_hours[$day_shift] += $hours;
            }
        }
    }

    return $working_hours;
}

/**
 * Calculate route time for a product
 * Converts operation time (HH:MM:SS) to hours
 * 
 * @param int $product_id Product ID
 * @param int $quantity_needed Quantity to produce
 * @param mysqli $conn Database connection
 * @param array $set_ids Array of set configurations
 * @return array Contains route_time (hours) and machine ID
 */
function calculateRouteTime($product_id, $quantity_needed, $conn, $set_ids) {
    // Check if product is part of a set
    $setId = null;
    foreach ($set_ids as $set) {
        if (isset($set['setItemId']) && $set['setItemId'] == $product_id) {
            $setId = $set['setId'];
            break;
        }
    }

    // Query appropriate table
    if ($setId !== null) {
        $stmt = $conn->prepare("SELECT operation_time, parts_per_operation, machine_high_priority FROM set_route_details WHERE set_id = ?");
        $stmt->bind_param("i", $setId);
    } else {
        $stmt = $conn->prepare("SELECT operation_time, parts_per_operation, machine_high_priority FROM route_details WHERE item_id = ?");
        $stmt->bind_param("i", $product_id);
    }

    $stmt->execute();
    $result = $stmt->get_result();

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

        // Convert operation_time (HH:MM:SS) to seconds
        $operation_time = 1; // Default fallback
        if (!empty($row['operation_time'])) {
            $time_parts = explode(":", $row['operation_time']);
            if (count($time_parts) === 3) {
                list($h, $m, $s) = $time_parts;
                $operation_time = ($h * 3600) + ($m * 60) + $s;
            }
        }

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

        $quantity_needed = $quantity_needed > 0 ? (int)$quantity_needed : 1;

        // Calculate route time in hours
        $route_time_seconds = ($operation_time / $parts_per_operation) * $quantity_needed;
        $route_time_hours = $route_time_seconds / 3600;

        $stmt->close();

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

    $stmt->close();

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

/**
 * Fallback stack-based processing for deep BOM structures
 * Used when recursion depth exceeds maximum
 * 
 * @param int $product_id Product ID
 * @param int $quantity_needed Quantity needed
 * @param mysqli $conn Database connection
 * @return bool Always returns true
 */
function processOrderStack($product_id, $quantity_needed, $conn) {
    global $operations;

    $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 (Stack Mode). Required Date: $required_date.",
            'qty' => $current_quantity_needed,
            'process_type' => 888,
            'required_date' => $required_date
        ];

        // Get BOM
        $stmt = $conn->prepare("SELECT sub_part_id, sub_part_name, quantity_required, type FROM bom WHERE main_product_id = ?");
        $stmt->bind_param("i", $current_product_id);
        $stmt->execute();
        $bom_parts = $stmt->get_result();

        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).",
                    '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 for Sub-Part ID $sub_part_id.",
                        'qty' => $sub_quantity_needed,
                        'process_type' => 888,
                        'required_date' => $sub_required_date
                    ];
                } elseif ($type === "Manufactured" || $type === "Assembly") {
                    $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 for Product ID $current_product_id.",
                'qty' => $current_quantity_needed,
                'process_type' => 4,
                'required_date' => $required_date
            ];
        }
        $stmt->close();
    }

    return true;
}

/**
 * Get previous working date by subtracting working days
 * 
 * @param string $targetDate Starting date
 * @param array $shift_ids Array of shift IDs
 * @param mysqli $conn Database connection
 * @param int $days_to_subtract Number of working days to subtract
 * @return string Date in Y-m-d format
 */
function getPreviousWorkingDate($targetDate, $shift_ids, $conn, $days_to_subtract = 2) {
    $working_hours = calculateShiftHours($shift_ids, $conn);

    // Get holidays
    $holidays = [];
    $result = $conn->query("SELECT holiday_date FROM admin_holidays");
    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $holidays[$row['holiday_date']] = true;
        }
    }

    $date = new DateTime($targetDate);
    $working_days_subtracted = 0;

    while ($working_days_subtracted < $days_to_subtract) {
        $date->modify('-1 day');
        $day_index = (int)$date->format('w');
        $date_str = $date->format('Y-m-d');

        // Skip holidays
        if (isset($holidays[$date_str])) {
            continue;
        }

        // Skip non-working days
        if (!isset($working_hours[$day_index]) || $working_hours[$day_index] <= 0) {
            continue;
        }

        $working_days_subtracted++;
    }

    return $date->format('Y-m-d');
}

/**
 * Sanitize and validate CSV input
 * 
 * @param string $value Input value
 * @return string Sanitized value
 */
function sanitizeInput($value) {
    return trim(strip_tags($value));
}
