<?php
global $db, $conn;
set_time_limit(0);
ini_set('memory_limit', '1024M');
ini_set('post_max_size', '32M');
ini_set('upload_max_filesize', '32M');

$operations = [];

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));
}
// ===========================
// CSV BULK IMPORT PROCESSING
// ===========================
// Step 1: Preview conflicts before import
if (isset($_POST['preview_import']) && $_POST['preview_import']) {
    
    // Validate file upload
    if (!isset($_FILES['import_gsi']) || $_FILES['import_gsi']['error'] !== UPLOAD_ERR_OK) {
        $_SESSION['error_message'] = "File upload failed. Please try again.";
        header("Location: index?page=sales_order_bulk_import");
        exit;
    }

    // Check if customer is selected
    if (!isset($_POST['selected_customer']) || empty($_POST['selected_customer'])) {
        $_SESSION['error_message'] = "Please select a customer before uploading.";
        header("Location: index?page=sales_order_bulk_import");
        exit;
    }

    $selected_customer_id = (int)$_POST['selected_customer'];
    
    // Get customer code for the selected customer
    $selected_customer_code = '';
    if ($selected_customer_id > 0) {
        $cust_code_query = "SELECT customer_code FROM client_management WHERE id = $selected_customer_id";
        $cust_code_result = $conn->query($cust_code_query);
        if ($cust_code_result && $cust_code_result->num_rows > 0) {
            $cust_code_row = $cust_code_result->fetch_assoc();
            $selected_customer_code = $cust_code_row['customer_code'];
        }
    }

    // Store in session for use in confirmation step
    $_SESSION['selected_customer_id'] = $selected_customer_id;
    $_SESSION['selected_customer_code'] = $selected_customer_code;

    $path = $_FILES['import_gsi']['name'];
    $ext = strtolower(pathinfo($path, PATHINFO_EXTENSION));

    if ($ext !== 'csv') {
        echo "<div style='border:2px solid red;color:black;font-size:14px;width:250px;height:50px;margin:0 auto;text-align:center;padding:10px;'>Please upload CSV file only</div>";
        exit;
    }

    // Setup file paths
    $destdir = getcwd() . "/sales_order_bulk_import_csv/";
    if (!is_dir($destdir)) {
        mkdir($destdir, 0755, true);
    }

    $filename = pathinfo($_FILES['import_gsi']['name'], PATHINFO_FILENAME);
    $dateString = date('Y_m_d');
    $timeString = date('H_i_s');
    $name = $filename . '_' . $dateString . '_' . $timeString . '.' . $ext;
    $destfile = $destdir . $name;

    if (!move_uploaded_file($_FILES['import_gsi']['tmp_name'], $destfile)) {
        $_SESSION['error_message'] = "Failed to save uploaded file.";
        header("Location: index?page=sales_order_bulk_import");
        exit;
    }

    // Store filename in session for later processing
    $_SESSION['pending_import_file'] = $destfile;

    $fp = fopen($destfile, 'r');
    if (!$fp) {
        $_SESSION['error_message'] = "Failed to open CSV file.";
        header("Location: index?page=sales_order_bulk_import");
        exit;
    }

    $flag = true;
    $line_number = 0;
    $csvRecords = [];

    while (!feof($fp)) {
        $buffer = fgetcsv($fp, 1000000, ',');
        $line_number++;

        if ($flag) {
            // Validate header
            $fixed_header = "part_number,CustomersRefCode,ReqDate,ReqQty,RefSalesCode";
            if ($buffer && implode(",", $buffer) !== $fixed_header) {
                echo "<div style='border:2px solid red;color:black;font-size:14px;width:450px;margin:0 auto;text-align:center;padding:10px;'>CSV header is not correct. Please follow import_sample.csv format.</div>";
                fclose($fp);
                exit;
            }
            $flag = false;
            continue;
        }

        if (!$buffer || empty(array_filter($buffer))) {
            continue; // Skip empty lines
        }

        // Sanitize and extract data
        list($part_number, $CustomersRefCode, $ReqDate, $ReqQty, $RefSalesCode) = array_map('sanitizeInput', $buffer);

        if ($part_number === "part_number") {
            continue; // Skip duplicate header
        }

        // Filter by selected customer
        if (!empty($selected_customer_code) && $selected_customer_code !== $CustomersRefCode) {
            // Skip records that don't match the selected customer
            continue;
        }
try {
    $ReqDateObj = DateTime::createFromFormat('d-m-Y', trim($ReqDate));

    if (!$ReqDateObj) {
        // Try alternative formats
        $ReqDateObj = DateTime::createFromFormat('d/m/Y', trim($ReqDate));
    }

    if (!$ReqDateObj) {
        throw new Exception("Invalid date format: $ReqDate");
    }

    $ReqDate_Date = $ReqDateObj->format('Y-m-d');

} catch (Exception $e) {
    $ReqDate_Date = null; // or skip line
}


        // Validate required fields
        if (empty($part_number) || empty($ReqDate) || empty($ReqQty)) {
            continue;
        }

        // Get customer ID
        $cust_check_q = "SELECT id FROM client_management WHERE customer_code = '" . addslashes($CustomersRefCode) . "' LIMIT 1";
        $cust_check_res = $conn->query($cust_check_q);

        if ($cust_check_res && $cust_check_res->num_rows > 0) {
            $cust_row = $cust_check_res->fetch_assoc();
            $customer_id = $cust_row['id'];
        } else {
            $customer_id = 0;
        }

        // Get part ID
        $part_id = utility::getTableFieldById("item_management", "id", "part_number", $part_number);

        if (!empty($part_id) && is_numeric($part_id)) {
            $csvRecords[] = [
                'customer_reff' => $customer_id,
                'part_id' => $part_id,
                'part_number' => $part_number,
                'req_date' => $ReqDate_Date,
                'req_qty' => (int)$ReqQty,
                'RefSalesCode' => $RefSalesCode,
                'customer_code' => $CustomersRefCode
            ];
        }
    }

    fclose($fp);

    // Get existing orders from database for the selected customer
    $existingOrders = [];
    if ($selected_customer_id > 0) {
        $existingQuery = "SELECT pp.id, pp.customer_reff, pp.part_id, pp.shipped_qty, pp.is_shipped, pp.req_date, pp.req_qty, pp.RefSalesCode,
                                 im.part_number, cm.customer_code
                          FROM production_planning pp
                          LEFT JOIN item_management im ON pp.part_id = im.id
                          LEFT JOIN client_management cm ON pp.customer_reff = cm.id
                          WHERE pp.customer_reff = '$selected_customer_id'";
    } else {
        $existingQuery = "SELECT pp.id, pp.customer_reff, pp.part_id, pp.shipped_qty, pp.is_shipped, pp.req_date, pp.req_qty, pp.RefSalesCode,
                                 im.part_number, cm.customer_code
                          FROM production_planning pp
                          LEFT JOIN item_management im ON pp.part_id = im.id
                          LEFT JOIN client_management cm ON pp.customer_reff = cm.id";
    }
    
    $existingResult = mysqli_query($conn, $existingQuery);
    while ($row = mysqli_fetch_assoc($existingResult)) {
        $existingOrders[] = $row;
    }

    // Find records that exist in DB but NOT in CSV
    $recordsOnlyInDB = [];
    $recordsToImport = []; // Records in CSV that are not exact matches in DB
    
    foreach ($existingOrders as $existRecord) {
        $foundInCSV = false;
        
        foreach ($csvRecords as $csvRecord) {
            // Check if it's an exact match (all fields match)
            if ($existRecord['customer_reff'] == $csvRecord['customer_reff'] &&
                $existRecord['part_id'] == $csvRecord['part_id'] &&
                $existRecord['req_date'] == $csvRecord['req_date'] &&
                $existRecord['req_qty'] == $csvRecord['req_qty']) {
                $foundInCSV = true;
                // This is an exact match, don't import it
                break;
            }
        }
        
        if (!$foundInCSV) {
            $recordsOnlyInDB[] = [
                'type' => 'missing_in_csv',
                'existing_record' => $existRecord,
                'note' => 'This record exists in database but not in CSV'
            ];
        }
    }
    
    // Find records to import (records in CSV that are not exact matches in DB)
    foreach ($csvRecords as $csvRecord) {
        $isExactMatch = false;
        
        foreach ($existingOrders as $existRecord) {
            // Check if it's an exact match
            if ($existRecord['customer_reff'] == $csvRecord['customer_reff'] &&
                $existRecord['part_id'] == $csvRecord['part_id'] &&
                $existRecord['req_date'] == $csvRecord['req_date'] &&
                $existRecord['req_qty'] == $csvRecord['req_qty']) {
                $isExactMatch = true;
                break;
            }
        }
        
        if (!$isExactMatch) {
            $recordsToImport[] = $csvRecord;
        }
    }

    // Store data in session for confirmation step
    $_SESSION['records_only_in_db'] = $recordsOnlyInDB;
    $_SESSION['records_to_import'] = $recordsToImport;
    $_SESSION['csv_records'] = $csvRecords;

    // Display preview page
    displayPreviewPage($recordsOnlyInDB, $recordsToImport, $selected_customer_id, $selected_customer_code);
    exit;
}

// Step 2: Process confirmed import
if (isset($_POST['confirm_import']) && $_POST['confirm_import']) {
    
    $import_start_time = date("Y-m-d H:i:s");
    
    // Get the stored file path
    if (!isset($_SESSION['pending_import_file']) || !file_exists($_SESSION['pending_import_file'])) {
        $_SESSION['error_message'] = "Import session expired. Please upload the file again.";
        header("Location: index?page=sales_order_bulk_import");
        exit;
    }

    // Get selected customer from session
    $selected_customer_id = isset($_SESSION['selected_customer_id']) ? (int)$_SESSION['selected_customer_id'] : 0;
    $selected_customer_code = isset($_SESSION['selected_customer_code']) ? $_SESSION['selected_customer_code'] : '';

    $destfile = $_SESSION['pending_import_file'];
    
    // Get which existing records to delete (user selected)
    $recordsToDelete = isset($_POST['delete_records']) ? $_POST['delete_records'] : [];

    // Delete selected existing records
    foreach ($recordsToDelete as $recordId) {
        $recordId = (int)$recordId;
        if ($recordId > 0) {
            mysqli_query($conn, "DELETE FROM production_planning WHERE id = $recordId");
            mysqli_query($conn, "DELETE FROM purchase_list WHERE work_order_id = $recordId");
        }
    }

    // Get shift IDs
    $shift_ids = [];
    $result = $conn->query("SELECT id FROM working_hours");
    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $shift_ids[] = (int)$row['id'];
        }
    }

    // Process CSV file - only import new records (skip exact matches)
    $fp = fopen($destfile, 'r');
    $flag = true;
    $line_number = 0;
    $records_inserted = 0;
    $records_skipped = 0;
    $errors = [];

    while (!feof($fp)) {
        $buffer = fgetcsv($fp, 1000000, ',');
        $line_number++;

        if ($flag) {
            $flag = false;
            continue;
        }

        if (!$buffer || empty(array_filter($buffer))) {
            continue;
        }

        list($part_number, $CustomersRefCode, $ReqDate, $ReqQty, $RefSalesCode) = array_map('sanitizeInput', $buffer);

        if ($part_number === "part_number") {
            continue;
        }

        // Filter by selected customer
        if (!empty($selected_customer_code) && $selected_customer_code !== $CustomersRefCode) {
            $records_skipped++;
            continue; // Skip records that don't match the selected customer
        }

       try {
    $ReqDateObj = DateTime::createFromFormat('d-m-Y', trim($ReqDate));

    if (!$ReqDateObj) {
        // Try alternative formats
        $ReqDateObj = DateTime::createFromFormat('d/m/Y', trim($ReqDate));
    }

    if (!$ReqDateObj) {
        throw new Exception("Invalid date format: $ReqDate");
    }

    $ReqDate_Date = $ReqDateObj->format('Y-m-d');

} catch (Exception $e) {
    $ReqDate_Date = null; // or skip line
}


        if (empty($part_number) || empty($ReqDate) || empty($ReqQty)) {
            $errors[] = "Line $line_number: Missing required fields";
            $records_skipped++;
            continue;
        }

        $ReqQty = (int)$ReqQty;
        if ($ReqQty <= 0) {
            $errors[] = "Line $line_number: Invalid quantity '$ReqQty'";
            $records_skipped++;
            continue;
        }

        $created_by = isset($_SESSION['user_id']) ? (int)$_SESSION['user_id'] : 0;
        $created_on = date("Y-m-d H:i:s");
        $updated_by = $created_by;

        $cust_check_q = "SELECT id FROM client_management WHERE customer_code = '" . addslashes($CustomersRefCode) . "' LIMIT 1";
        $cust_check_res = $conn->query($cust_check_q);

        if ($cust_check_res && $cust_check_res->num_rows > 0) {
            $cust_row = $cust_check_res->fetch_assoc();
            $customer_id = $cust_row['id'];
        } else {
            $customer_id = 0;
        }

        $part_id = utility::getTableFieldById("item_management", "id", "part_number", $part_number);
        
        if (!empty($part_id) && is_numeric($part_id)) {
            
            // Check if this is an exact match in database (skip it)
            $isExactMatch = false;
            $checkQuery = "SELECT COUNT(*) as count FROM production_planning 
                          WHERE customer_reff = '$customer_id' 
                          AND part_id = '$part_id' 
                          AND req_date = '$ReqDate_Date' 
                          AND req_qty = '$ReqQty'";
            $checkResult = $conn->query($checkQuery);
            if ($checkResult) {
                $checkRow = $checkResult->fetch_assoc();
                if ($checkRow['count'] > 0) {
                    $isExactMatch = true;
                    $records_skipped++;
                }
            }
            
            if (!$isExactMatch) {
                $shift_ids = array_column(mysqli_fetch_all($conn->query("SELECT id FROM working_hours"), MYSQLI_ASSOC), 'id');
                $required_date = getPreviousWorkingDate($ReqDate_Date, $shift_ids, $conn, 2);

                $sql = "INSERT INTO production_planning 
                        SET part_id='$part_id',
                            customer_reff='$customer_id',
                            req_date='$ReqDate_Date',
                            req_qty='$ReqQty',
                            RefSalesCode='$RefSalesCode',
                            created_on='$created_on',
                            created_by='$created_by',
                            updated_by='$updated_by',
                            is_changed='2'";

                if (mysqli_query($conn, $sql)) {
                    $records_inserted++;
                    $last_id = mysqli_insert_id($conn);
                    $work_order_id = $last_id;
                    
                    processOrderHybrid(
                        [$part_id],
                        [$ReqQty],
                        $shift_ids,
                        $conn,
                        $required_date,
                        $work_order_id,
                        1,
                        $set_ids
                    );
                }
            }
        } else {
            $sql_not_exist = "INSERT INTO production_planning 
                             SET part_id='0',
                                 item_name_if_not_exist='$part_number',
                                 customer_reff='$customer_id',
                                 req_date='$ReqDate_Date',
                                 req_qty='$ReqQty',
                                 item_not_exist=1,
                                 RefSalesCode='$RefSalesCode',
                                 created_on='$created_on',
                                 created_by='$created_by',
                                 updated_by='$updated_by'";
            if (mysqli_query($conn, $sql_not_exist)) $records_skipped++;
        }
    }

    fclose($fp);
    $import_end_time = date("Y-m-d H:i:s");

    // Mark unchanged old records
    mysqli_query($conn, "UPDATE production_planning SET is_changed = 1 WHERE created_on < '$import_start_time' AND (is_changed IS NULL OR is_changed = 0)");

    // Clean up session
    unset($_SESSION['pending_import_file']);
    unset($_SESSION['records_only_in_db']);
    unset($_SESSION['records_to_import']);
    unset($_SESSION['csv_records']);
    unset($_SESSION['selected_customer_id']);
    unset($_SESSION['selected_customer_code']);

    $_SESSION['info_message_sales_order'] = "$records_inserted sales order(s) inserted successfully.";
    $_SESSION['info_message_skipped_sales_order'] = "$records_skipped sales order(s) skipped (exact matches or item not found).";
    
    if (!empty($errors)) {
        $_SESSION['error_details'] = implode("<br>", array_slice($errors, 0, 10));
    }

    header("Location: index?page=sales_order_bulk_import");
    exit();
}

// Display preview page function
function displayPreviewPage($recordsOnlyInDB, $recordsToImport, $selected_customer_id, $selected_customer_code) {
    // Get customer name for display
    $customer_name = $selected_customer_code;
    ?>
        <style>
          
            .container { max-width: 1400px; margin: 0 auto; background: white; padding: 20px; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
            h1 { color: #333; border-bottom: 3px solid #007bff; padding-bottom: 10px; }
            h2 { color: #555; margin-top: 30px; }
            .customer-info { background: #e3f2fd; padding: 15px; border-radius: 5px; margin-bottom: 20px; font-weight: bold; }
            .summary { background: #f8f9fa; padding: 15px; border-radius: 5px; margin-bottom: 20px; }
            .summary-item { display: inline-block; margin-right: 30px; font-weight: bold; }
            .section { margin-bottom: 40px; border: 1px solid #ddd; border-radius: 5px; padding: 15px; }
            table { width: 100%; border-collapse: collapse; margin-top: 15px; }
            th { background: #007bff; color: white; padding: 12px; text-align: left; font-weight: 600; }
            td { padding: 10px; border-bottom: 1px solid #ddd; }
            tr:hover { background: #f8f9fa; }
            .existing-only { background: #fff3cd; }
            .to-import { background: #d4edda; }
            .action-cell { text-align: center; width: 10px; }
            .checkbox { width: 20px; height: 20px; cursor: pointer; }
            .btn-container { text-align: center; margin-top: 30px; padding: 20px; background: #f8f9fa; border-radius: 5px; }
            .btn { padding: 12px 30px; font-size: 16px; border: none; border-radius: 5px; cursor: pointer; margin: 0 10px; }
            .btn-primary { background: #007bff; color: white; }
            .btn-primary:hover { background: #0056b3; }
            .btn-secondary { background: #6c757d; color: white; }
            .btn-secondary:hover { background: #545b62; }
            .label { display: inline-block; padding: 4px 8px; border-radius: 3px; font-size: 12px; font-weight: bold; }
            .label-existing { background: #ffc107; color: #000; }
            .label-new { background: #28a745; color: white; }
            .info-box { background: #f8f9fa; border-left: 4px solid #007bff; padding: 15px; margin-bottom: 20px; }
            .info-box h3 { margin-top: 0; color: #007bff; }
            .note { color: #666; font-style: italic; font-size: 13px; margin-top: 5px; }
        </style>
    </head>
    <body>
        <div class="container">
            <h4>📋 Import Preview - Review Changes</h4>
            <div class="customer-info">
                Customer: <?php echo htmlspecialchars($customer_name); ?>
            </div>
            <div class="summary">
                <div class="summary-item">Existing Records Not in CSV: <span style="color: #ff9800;"><?php echo count($recordsOnlyInDB); ?></span></div>
                <div class="summary-item">New Records to Import: <span style="color: #28a745;"><?php echo count($recordsToImport); ?></span></div>
            </div>

            <form method="post" id="confirmForm">
                <input type="hidden" name="confirm_import" value="1">

                <?php if (!empty($recordsOnlyInDB)): ?>
                <div class="section">
                    <h4>Existing Records Not in CSV (May Delete)</h4>
                    <table>
                        <thead>
                            <tr>
                                <th>Status</th>
                                <th>Customer Code</th>
                                <th>Part Number</th>
                                <th>Required Date</th>
                                <th>Quantity</th>
                                <th>Shipped</th>
                                <th>Ref Sales Code</th>
                                <th class="action-cell">Delete?</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($recordsOnlyInDB as $record): ?>
                            <tr class="existing-only">
                                <td><span class="label label-existing">EXISTING</span></td>
                                <td><?php echo htmlspecialchars($record['existing_record']['customer_code']); ?></td>
                                <td><?php echo htmlspecialchars($record['existing_record']['part_number']); ?></td>
                                <td><?php echo date('d-m-Y', strtotime($record['existing_record']['req_date'])); ?></td>
                                <td><?php echo $record['existing_record']['req_qty']; ?></td>
                                <td><?php echo $record['existing_record']['is_shipped'] ? 'Yes' : 'No'; ?></td>
                                <td><?php echo htmlspecialchars($record['existing_record']['RefSalesCode']); ?></td>
                                <td class="action-cell">
                                    <input type="checkbox" name="delete_records[]" value="<?php echo $record['existing_record']['id']; ?>" class="checkbox">
                                </td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
                <?php endif; ?>

                <?php if (!empty($recordsToImport)): ?>
                <div class="section">
                    <h4>New Records to be Imported</h4>
                    <table>
                        <thead>
                            <tr>
                                <th>Status</th>
                                <th>Customer Code</th>
                                <th>Part Number</th>
                                <th>Required Date</th>
                                <th>Quantity</th>
                                <th>Ref Sales Code</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($recordsToImport as $record): ?>
                            <tr class="to-import">
                                <td><span class="label label-new">NEW</span></td>
                                <td><?php echo htmlspecialchars($record['customer_code']); ?></td>
                                <td><?php echo htmlspecialchars($record['part_number']); ?></td>
                                <td><?php echo date('d-m-Y', strtotime($record['req_date'])); ?></td>
                                <td><?php echo $record['req_qty']; ?></td>
                                <td><?php echo htmlspecialchars($record['RefSalesCode']); ?></td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
                <?php endif; ?>

                <div class="btn-container">
                    <button type="button" onclick="window.history.back();" class="btn btn-secondary">← Cancel</button>
                    <button type="submit" class="btn btn-primary" onclick="return confirm('Are you sure you want to proceed with this import for customer <?php echo addslashes($customer_name); ?>?');">
                        ✓ Confirm & Import
                    </button>
                </div>
            </form>
        </div>

        <script>
            document.addEventListener('DOMContentLoaded', function() {
                // Optional: Add "Select All" functionality
                const selectAllBtn = document.createElement('button');
                selectAllBtn.type = 'button';
                selectAllBtn.className = 'btn btn-secondary';
                selectAllBtn.textContent = 'Select All';
                selectAllBtn.style.margin = '2px 0';
                
                const table = document.querySelector('.existing-only')?.closest('table');
                if (table) {
                    const thead = table.querySelector('thead tr');
                    const actionTh = thead.querySelector('.action-cell');
                    if (actionTh) {
                        actionTh.appendChild(selectAllBtn);
                        
                        selectAllBtn.addEventListener('click', function() {
                            const checkboxes = table.querySelectorAll('input[type="checkbox"]');
                            const allChecked = Array.from(checkboxes).every(cb => cb.checked);
                            
                            checkboxes.forEach(cb => {
                                cb.checked = !allChecked;
                            });
                            
                            selectAllBtn.textContent = allChecked ? 
                                'Select All' : 
                                'Deselect All';
                        });
                    }
                }
            });
        </script>
    <?php
}
?>

<section class="content">
    <div class="row">
        <div class="col-lg-12">
            <?php if (isset($_SESSION['info_message_sales_order'])): ?>
                <div class="alert alert-info alert-dismissable">
                    <button type="button" class="close" data-dismiss="alert">&times;</button>
                    <?php echo $_SESSION['info_message_sales_order']; unset($_SESSION['info_message_sales_order']); ?>
                </div>
            <?php endif; ?>
            
            <?php if (isset($_SESSION['info_message_skipped_sales_order'])): ?>
                <div class="alert alert-info alert-dismissable">
                    <button type="button" class.close="alert">&times;</button>
                    <?php echo $_SESSION['info_message_skipped_sales_order']; unset($_SESSION['info_message_skipped_sales_order']); ?>
                </div>
            <?php endif; ?>

            <?php if (isset($_SESSION['error_message'])): ?>
                <div class="alert alert-danger alert-dismissable">
                    <button type="button" class="close" data-dismiss="alert">&times;</button>
                    <?php echo $_SESSION['error_message']; unset($_SESSION['error_message']); ?>
                </div>
            <?php endif; ?>

            <div class="box box-default">
                <section class="bulkuploadheader">
                    <h1 style="color:black">Bulk Upload</h1>
                </section>
                <form id="bulkuploadform" method="post" enctype="multipart/form-data">
                    <div style="background-color: #f9f9f9;margin:0 auto;text-align:center;">
                        <br /><br />
                        <span style="color:#F44336;font-weight:bold;font-style:italic;padding-left:20px;">
                            To bulk upload, please follow the below mentioned rules
                            <br>1. The upload file should be in <span style='color:black;font-size:22px;text-transform:uppercase;'>"CSV"</span> file format.
                            <br>2. The csv file should be like <a target="_blank" href="<?php echo $config->siteURL; ?>/images/sales_order_sample.csv">import_sample.csv</a>
                            <br>3. <strong>Select a customer first</strong> - only orders for that customer will be processed
                        </span>
                        
                        <div style="margin: 20px auto; width: 300px;">
                            <label for="selected_customer" style="display: block; margin-bottom: 8px; font-weight: bold; color: #333;">
                                Select Customer: <span style="color: red;">*</span>
                            </label>
                            <select name="selected_customer" id="selected_customer" class="form-control" required style="width: 100%; padding: 8px; border: 1px solid #ccc; border-radius: 4px;">
                                <option value="">-- Select Customer --</option>
                                <?php
                                // Fetch customers from database
                                $customer_query = "SELECT id, customer_code FROM client_management ORDER BY id";
                                $customer_result = $conn->query($customer_query);
                                
                                while ($customer = $customer_result->fetch_assoc()) {
                                    $selected = (isset($_POST['selected_customer']) && $_POST['selected_customer'] == $customer['id']) ? 'selected' : '';
                                    echo "<option value='{$customer['id']}' {$selected}>{$customer['customer_code']}</option>";
                                }
                                ?>
                            </select>
                            <div id="customer-error" style="color: red; font-size: 12px; margin-top: 5px; display: none;">
                                Please select a customer before uploading
                            </div>
                        </div>
                        
                        <div class="upload-btn-wrapper">
                            <button class="btn btn-primary">Choose File</button>
                            <input type="file" name="import_gsi" id="import_gsi" accept=".csv">
                        </div>
                        
                        <br /><br /><br />
                        <div style="display:none;">
                            <input type="submit" name="preview_import" id="preview_import" value="preview import">
                        </div>
                    </div>
                </form>
            </div>
        </div>
    </div>
</section>

<style>
.content-wrapper { min-height: 593px; }
.bulkuploadheader { padding:16px; background:aliceblue; text-align:center; }
.bulkuploadheader h1 { color:#808080; font-weight:bold; font-size:25px; margin:0; }
.upload-btn-wrapper { position:relative; overflow:hidden; display:inline-block; }
.upload-btn-wrapper input[type=file] { font-size:100px; position:absolute; left:0; top:0; opacity:0; }
</style>

<script>
$(document).ready(function(){
    $("#bulkuploadform").on("change","#import_gsi",function(){
        // Check if customer is selected
        var selectedCustomer = $("#selected_customer").val();
        
        if (!selectedCustomer) {
            $("#customer-error").show();
            $("#selected_customer").focus();
            
            // Clear the file input
            $(this).val('');
            
            // Show a more prominent alert
            alert("Please select a customer first!");
            return false;
        }
        
        $("#customer-error").hide();
        
        if(confirm("Do you want to preview the import and check for conflicts?")) {
            $("#preview_import").click();
        }
    });
    
    // Validate form on submit
    $("#bulkuploadform").on("submit", function(e){
        if ($("#preview_import").is(":focus") || $("#preview_import").is(":active")) {
            var selectedCustomer = $("#selected_customer").val();
            
            if (!selectedCustomer) {
                $("#customer-error").show();
                $("#selected_customer").focus();
                e.preventDefault();
                return false;
            }
        }
    });
});
</script>