<?php
// Get filter parameters
$status_filter = isset($_GET['status']) ? $_GET['status'] : '';
$part_number_filter = isset($_GET['part_number']) ? trim($_GET['part_number']) : '';

// Pagination parameters
$current_page = isset($_GET['p']) ? max(1, (int)$_GET['p']) : 1;
$per_page = 50;
$offset = max(0, ($current_page - 1) * $per_page);

$min_date = date('Y-m-d');
$min_date = date('Y-m-d', strtotime('monday this week', strtotime($min_date)));
$max_date = date('Y-m-d', strtotime($min_date . ' +14 weeks'));

// Build week list for future weeks only (no past week)
$week_list = [];
for ($i = 0; $i <= 14; $i++) {
    $week_list[] = date('oW', strtotime("$min_date +$i week"));
}

$all_parts_query = "SELECT DISTINCT id, part_number, type_2, uom, usual_source
                FROM item_management
                WHERE usual_source = 'Bought In'";
$all_parts_result = mysqli_query($conn, $all_parts_query);

$all_part_data = [];
$all_part_ids = [];
foreach ($all_parts_result as $part) {
    $part_id = $part['id'];
    $all_part_ids[] = $part_id;
    $all_part_data[$part_id] = [
        'part_number' => $part['part_number'],
        'type_2' => $part['type_2'],
        'uom' => $part['uom'],
        'usual_source' => $part['usual_source'],
        'sales_orders' => [],
        'work_orders' => [],
        'stock' => 0,
        'arrears' => 0,
        'run_out_date' => null,
        'order_date' => null,
        'status' => 'OK'
    ];
    foreach ($week_list as $week) {
        $all_part_data[$part_id]['sales_orders'][$week] = 0;
        $all_part_data[$part_id]['work_orders'][$week] = 0;
    }
}

$pending_orders_all = [];
if (!empty($all_part_ids)) {
    $all_part_ids_str = implode(',', $all_part_ids);
    
    $pending_orders_query = "SELECT 
        part_number,
        quantity,
        received,
        DATE_FORMAT(expected_date, '%x%v') AS week_number,
        (quantity - received) AS pending_qty
    FROM purchase_order_items_mapping
    WHERE status = 0 
    AND expected_date BETWEEN '$min_date' AND '$max_date'
    AND (quantity - received) > 0
    AND part_number IN ($all_part_ids_str)";
    
    $pending_orders_result = mysqli_query($conn, $pending_orders_query);

    while ($row = mysqli_fetch_assoc($pending_orders_result)) {
        $part_id = $row['part_number'];
        $week = $row['week_number'];
        $qty = (int)$row['pending_qty'];

        if (!isset($pending_orders_all[$part_id])) {
            $pending_orders_all[$part_id] = [];
        }

        $pending_orders_all[$part_id][$week] = ($pending_orders_all[$part_id][$week] ?? 0) + $qty;
    }
}

if (!empty($all_part_ids)) {
    $all_part_ids_str = implode(',', $all_part_ids);
    
    // Inventory stock for ALL parts
    $inventory_query = "SELECT item_id, quantity_in_stock FROM inventory 
                        WHERE item_id IN ($all_part_ids_str)";
    $inventory_result = mysqli_query($conn, $inventory_query);
    while ($row = mysqli_fetch_assoc($inventory_result)) {
        $part_id = $row['item_id'];
        if (isset($all_part_data[$part_id])) {
            $all_part_data[$part_id]['stock'] = $row['quantity_in_stock'];
        }
    }

    // Sales Orders for ALL parts
    $sales_query = "SELECT 
                        pl.item_id, 
                        DATE_FORMAT(pl.required_date, '%x%v') AS week_number,
                        SUM(pl.quantity_requested) AS total_qty,
                        MIN(pl.required_date) as first_date
                    FROM purchase_list pl
                    JOIN item_management im ON pl.item_id = im.id
                    WHERE pl.required_date BETWEEN '$min_date' AND '$max_date'
                    AND im.usual_source = 'Bought In'
                    AND pl.item_id IN ($all_part_ids_str)
                    GROUP BY pl.item_id, DATE_FORMAT(pl.required_date, '%x%v')";
    $sales_result = mysqli_query($conn, $sales_query);
    while ($row = mysqli_fetch_assoc($sales_result)) {
        $part_id = $row['item_id'];
        $week = $row['week_number'];
        if (isset($all_part_data[$part_id]) && in_array($week, $week_list)) {
            $all_part_data[$part_id]['sales_orders'][$week] = (int)$row['total_qty'];
        }
    }

    // Calculate ARREARS (overdue orders before today)
    $today = date('Y-m-d');
    $arrears_query = "SELECT 
                        pl.item_id, 
                        SUM(pl.quantity_requested) AS total_arrears
                    FROM purchase_list pl
                    JOIN item_management im ON pl.item_id = im.id
                    WHERE pl.required_date < '$today'
                    AND im.usual_source = 'Bought In'
                    AND pl.item_id IN ($all_part_ids_str)
                    GROUP BY pl.item_id";
    $arrears_result = mysqli_query($conn, $arrears_query);
    while ($row = mysqli_fetch_assoc($arrears_result)) {
        $part_id = $row['item_id'];
        if (isset($all_part_data[$part_id])) {
            $all_part_data[$part_id]['arrears'] = (int)$row['total_arrears'];
        }
    }

    // Work Orders for ALL parts
    $work_query = "SELECT 
                        wod.product_id, 
                        DATE_FORMAT(wod.required_date, '%x%v') AS week_number,
                        SUM(wod.quantity) AS total_qty
                    FROM work_order_detail wod
                    JOIN item_management im ON wod.product_id = im.id
                    WHERE wod.required_date BETWEEN '$min_date' AND '$max_date'
                    AND wod.process_type = 4
                    AND im.usual_source = 'Bought In'
                    AND wod.product_id IN ($all_part_ids_str)
                    GROUP BY wod.product_id, DATE_FORMAT(wod.required_date, '%x%v')";
    $work_result = mysqli_query($conn, $work_query);
    while ($row = mysqli_fetch_assoc($work_result)) {
        $part_id = $row['product_id'];
        $week = $row['week_number'];
        if (isset($all_part_data[$part_id]) && in_array($week, $week_list)) {
            $all_part_data[$part_id]['work_orders'][$week] = (int)$row['total_qty'];
        }
    }

    // CORRECTED CALCULATION LOGIC
    foreach ($all_part_data as $part_id => &$part) {
        $lead_time_days = is_numeric($part['uom']) ? (int)$part['uom'] : 7;
        $part['lead_time'] = $lead_time_days . " days";

        // Check if there's any demand at all
        $has_demand = false;
        foreach ($week_list as $week) {
            $sales_qty = $part['sales_orders'][$week] ?? 0;
            $work_qty = $part['work_orders'][$week] ?? 0;
            if ($sales_qty > 0 || $work_qty > 0) {
                $has_demand = true;
                break;
            }
        }

        if (!$has_demand) {
            $part['status'] = 'OK';
            $part['run_out_date'] = 'No demand forecast';
            $part['order_date'] = '-';
            $part['order_date_obj'] = null;
            continue;
        }

        // Week-by-week sequential calculation
        $running_stock = $part['stock'];
        $run_out_week = null;
        $run_out_date_obj = null;

        // Process weeks in chronological order
        foreach ($week_list as $week) {
            // Add pending purchase orders arriving this week
            $pending_arriving = $pending_orders_all[$part_id][$week] ?? 0;
            $running_stock += $pending_arriving;
            
            // Calculate total demand for this week
            $sales_demand = $part['sales_orders'][$week] ?? 0;
            $work_demand = $part['work_orders'][$week] ?? 0;
            $total_week_demand = $sales_demand + $work_demand;
            
            // Subtract demand
            $running_stock -= $total_week_demand;
            
            // Check if we run out this week
            if ($running_stock < 0 && $run_out_week === null) {
                $run_out_week = $week;
                
                // Convert week number to date (Monday of that week)
                $week_date = new DateTime();
                $week_date->setISODate(substr($week, 0, 4), substr($week, 4, 2));
                $run_out_date_obj = $week_date;
                break;
            }
        }

        // Determine status based on run out date
        if ($run_out_date_obj === null) {
            // No run out in forecast period
            $part['status'] = 'OK';
            $part['run_out_date'] = 'Sufficient Stock';
            $part['order_date'] = '-';
            $part['order_date_obj'] = null;
        } else {
            // Calculate order date based on lead time
            $order_date_obj = clone $run_out_date_obj;
            $order_date_obj->modify("-$lead_time_days days");
            
            $part['run_out_date'] = $run_out_date_obj->format('M d, Y');
            $part['order_date'] = $order_date_obj->format('M d, Y');
            $part['order_date_obj'] = $order_date_obj;
            
            // Determine urgency
            $today = new DateTime();
            if ($order_date_obj < $today) {
                $part['status'] = 'Urgent';
            } else {
                $two_weeks = clone $today;
                $two_weeks->modify('+2 weeks');
                if ($order_date_obj <= $two_weeks) {
                    $part['status'] = 'Plan';
                } else {
                    $part['status'] = 'OK';
                }
            }
        }
    }
    unset($part);
}

// Apply status filter if needed
if (!empty($status_filter)) {
    $filtered_part_data = [];
    foreach ($all_part_data as $part_id => $part) {
        if ($part['status'] === $status_filter) {
            $filtered_part_data[$part_id] = $part;
        }
    }
    $all_part_data = $filtered_part_data;
}

// Apply part number filter if needed
if (!empty($part_number_filter)) {
    $filtered_part_data = [];
    foreach ($all_part_data as $part_id => $part) {
        // Case-insensitive partial match
        if (stripos($part['part_number'], $part_number_filter) !== false) {
            $filtered_part_data[$part_id] = $part;
        }
    }
    $all_part_data = $filtered_part_data;
}

// Paginate the filtered results
$total_rows = count($all_part_data);
$total_pages = ceil($total_rows / $per_page);

// Ensure page doesn't exceed total pages
if ($current_page > $total_pages && $total_pages > 0) {
    $current_page = $total_pages;
    $offset = max(0, ($current_page - 1) * $per_page);
}

$part_data = array_slice($all_part_data, $offset, $per_page, true);
$part_ids = array_keys($part_data);

$bulk_order_data = [];
$pending_orders = []; 

if (!empty($part_ids)) {
    $part_ids_str = implode(',', $part_ids);
    
    // Get bulk order data for current page parts only
    $bulk_order_query = "SELECT 
                            im.id as product_id, 
                            im.minimum_quantity,
                            bom.price_per_item,
                            bom.order_quantity,
                            bom.created_on
                        FROM item_management im
                        LEFT JOIN bulk_ordering_product_mapping bom ON im.id = bom.product_id
                        WHERE im.usual_source = 'Bought In'
                        AND im.id IN ($part_ids_str)
                        ORDER BY im.id, bom.created_on DESC";
    $bulk_order_result = mysqli_query($conn, $bulk_order_query);

    while ($row = mysqli_fetch_assoc($bulk_order_result)) {
        $product_id = $row['product_id'];
        if (!isset($bulk_order_data[$product_id])) {
            $bulk_order_data[$product_id] = [
                'minimum_quantity' => $row['minimum_quantity'],
                'bulk_orders' => []
            ];
        }
        if ($row['price_per_item'] !== null) {
            $bulk_order_data[$product_id]['bulk_orders'][] = [
                'price_per_item' => $row['price_per_item'],
                'order_quantity' => $row['order_quantity'],
                'created_on' => $row['created_on']
            ];
        }
    }

    // Get pending purchase orders for current page parts only (for display)
    $pending_orders_query = "SELECT 
        part_number,
        quantity,
        received,
        DATE_FORMAT(expected_date, '%x%v') AS week_number,
        (quantity - received) AS pending_qty
    FROM purchase_order_items_mapping
    WHERE status = 0 
    AND expected_date BETWEEN '$min_date' AND '$max_date'
    AND (quantity - received) > 0
    AND part_number IN ($part_ids_str)";
    
    $pending_orders_result = mysqli_query($conn, $pending_orders_query);

    $pending_orders = [];
    while ($row = mysqli_fetch_assoc($pending_orders_result)) {
        $part_id = $row['part_number'];
        $week = $row['week_number'];
        $qty = (int)$row['pending_qty'];

        if (!isset($pending_orders[$part_id])) {
            $pending_orders[$part_id] = [];
        }

        $pending_orders[$part_id][$week] = ($pending_orders[$part_id][$week] ?? 0) + $qty;
    }
}

// Get unique suppliers for filter
$suppliers = [];
foreach ($all_part_data as $part) {
    $supplier = $gsi->getSuppliersById($part['type_2']);
    if (!in_array($supplier, $suppliers)) {
        $suppliers[] = $supplier;
    }
}
sort($suppliers);

// Get unique statuses for filter
$statuses = ['OK', 'Plan', 'Urgent'];

function getTooltipContent($part_id, $bulk_order_data) {
    if (!isset($bulk_order_data[$part_id])) {
        return "No additional information available";
    }

    $data = $bulk_order_data[$part_id];
    $content = "<div style='text-align:left;'>";
    $content .= "<strong>Minimum Qty:</strong> " . ($data['minimum_quantity'] ?? 'Not set') . "<br>";
    
    if (!empty($data['bulk_orders'])) {
        $content .= "<hr style='margin:5px 0;'>";
        $content .= "<strong>Price breaks:</strong><br>";
        
        foreach ($data['bulk_orders'] as $order) {
            $content .= "• Price: {$order['price_per_item']} | ";
            $content .= "Qty: {$order['order_quantity']} | " . "<br>";
        }
    }
    
    $content .= "</div>";
    return $content;
}

// Function to generate pagination URLs with filters
function getPaginationUrl($page, $status_filter = '', $part_number_filter = '') {
    $base_url = "index?page=view_purchase_list";
    $params = [];
    
    if (!empty($status_filter)) {
        $params['status'] = $status_filter;
    }
    
    if (!empty($part_number_filter)) {
        $params['part_number'] = $part_number_filter;
    }
    
    if ($page > 1) {
        $params['p'] = $page;
    }
    
    if (!empty($params)) {
        return $base_url . '&' . http_build_query($params);
    }
    
    return $base_url;
}
?>

    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <link href="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/css/select2.min.css" rel="stylesheet" />
    <style>
        :root {
            --primary-color: #3498db;
            --secondary-color: #f8f9fa;
            --highlight-color: #fff59d;
            --sales-row: #e3f2fd;
            --work-row: #fff8e1;
            --order-row: #f0f8ff;
            --border-color: #dee2e6;
            --urgent-color: #ffcccc;
        }
        
        body {
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            background-color: #f5f7fa;
            color: #333;
            margin: 0;
            padding: 0;
        }
        
        .dashboard-header {
            background-color: white;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            padding: 1rem 0;
            margin-bottom: 2rem;
            width: 100%;
        }
        
        .dashboard-title {
            color: #2c3e50;
            font-weight: 600;
            display: flex;
            align-items: center;
            gap: 10px;
        }
        
        .dashboard-title i {
            color: var(--primary-color);
        }
        
        .table-container {
            background-color: white;
            border-radius: 8px;
            box-shadow: 0 2px 15px rgba(0,0,0,0.05);
            padding: 20px;
            margin-bottom: 30px;
            width: 100%;
			
            overflow-x: auto;
        }
        
        .table {
            border-collapse: separate;
            border-spacing: 0;
            width: 100%;
            margin: 0;
        }
        
        .table th, .table td {
            border: 1px solid var(--border-color);
            vertical-align: middle;
            padding: 12px 8px;
        }
        
        .table thead th {
            background-color: #2c3e50;
            color: white;
            font-weight: 500;
            position: sticky;
            top: 0;
            z-index: 30;
            border-bottom: none;
        }
        
        .table thead th:first-child {
            border-top-left-radius: 8px;
        }
        
        .table thead th:last-child {
            border-top-right-radius: 8px;
        }
        
        .week-header {
            height: 120px;
            white-space: nowrap;
            position: relative;
            min-width: 50px;
        }
        
        .week-header span {
            position: absolute;
            bottom: 10px;
            left: 65%;
            transform: translateX(-50%) rotate(-45deg);
            transform-origin: 0 0;
            width: 100px;
            font-weight: 500;
        }
        
        .part-number {
            font-weight: 600;
            color: #2c3e50;
            background-color: #f8f9fa;
            position: sticky;
            left: 0;
            z-index: 5;
            width: 150px;
            min-width: 150px;
        }
        
        .type-cell {
            font-weight: 500;
            white-space: nowrap;
            background-color: #f8f9fa;
            position: sticky;
            left: 150px;
            z-index: 5;
            width: 100px;
            min-width: 100px;
        }
        
        .stock-cell {
            font-weight: 500;
            white-space: nowrap;
            background-color: #f8f9fa;
            position: sticky;
            left: 250px;
            z-index: 5;
            width: 80px;
            min-width: 80px;
            text-align: center;
        }
        
        .lead-time-cell {
            font-weight: 500;
            white-space: nowrap;
            background-color: #f8f9fa;
            position: sticky;
            left: 330px;
            z-index: 5;
            width: 80px;
            min-width: 80px;
            text-align: center;
        }
        
        .run-out-cell {
            font-weight: 500;
            white-space: nowrap;
            background-color: #f8f9fa;
            position: sticky;
            left: 410px;
            z-index: 5;
            width: 120px;
            min-width: 120px;
        }
        
        .order-by-cell {
            font-weight: 500;
            white-space: nowrap;
            background-color: #f8f9fa;
            position: sticky;
            left: 530px;
            z-index: 5;
            width: 120px;
            min-width: 120px;
        }
        
        .status-cell {
            font-weight: 500;
            white-space: nowrap;
            background-color: #f8f9fa;
            position: sticky;
            left: 650px;
            z-index: 5;
            width: 80px;
            min-width: 80px;
        }
        
        .row-type-cell {
            font-weight: 500;
            white-space: nowrap;
            background-color: #f8f9fa;
            position: sticky;
            left: 730px;
            z-index: 5;
            width: 50px;
            min-width: 50px;
            text-align: center;
        }
        
        .arrears-cell {
            font-weight: 500;
            white-space: nowrap;
            background-color: #f8f9fa;
            position: sticky;
            left: 780px;
            z-index: 5;
            width: 80px;
            min-width: 80px;
            text-align: center;
        }
        
        .arrears-urgent {
            background-color: #fcf2f2 !important;
            color: white;
            font-weight: bold;
        }
        
        .arrears-warning {
            background-color: #f7efe1 !important;
            color: white;
            font-weight: bold;
        }
        
        .sales-order-row {
            background-color: var(--sales-row);
        }
        
        .work-order-row {
            background-color: var(--work-row);
        }
        
        .order-input-row {
            background-color: var(--order-row);
        }
        
        .highlight {
            background-color: #5bbd71 !important;
            font-weight: bold;
            position: relative;
        }
        
        .highlight:after {
            content: '';
            position: absolute;
            top: 2px;
            right: 2px;
            width: 0;
            height: 0;
            border-style: solid;
            border-width: 0 12px 12px 0;
            border-color: transparent #28a745 transparent transparent;
        }
        
        .urgent {
            background-color: var(--urgent-color) !important;
            font-weight: bold;
        }
        
        .table-hover tbody tr:hover {
            background-color: rgba(52, 152, 219, 0.1);
        }
        
        .status-badge {
            font-size: 0.75rem;
            padding: 3px 8px;
            border-radius: 12px;
            font-weight: 600;
        }
        
        .badge-success {
            background-color: #d4edda;
            color: #155724;
        }
        
        .badge-warning {
            background-color: #fff3cd;
            color: #856404;
        }
        
        .badge-danger {
            background-color: #f8d7da;
            color: #721c24;
        }
        
        .bulk-order-btn {
            background-color: #17a2b8;
            color: white;
            border: none;
            padding: 8px 15px;
            border-radius: 4px;
            cursor: pointer;
            margin-left: 10px;
        }
        
        .bulk-order-btn:hover {
            background-color: #138496;
        }
        
        .bulk-order-btn:disabled {
            background-color: #6c757d;
            cursor: not-allowed;
        }
        
        .modal-lg {
            max-width: 900px;
        }
        
        #selectedItemsBody tr td {
            vertical-align: middle;
        }
        
        .order-input {
            width: 100%;
            min-width: 60px;
            text-align: center;
            padding: 5px;
            border: 1px solid #ced4da;
            border-radius: 4px;
            transition: all 0.3s ease;
        }
        
        .order-input:focus {
            border-color: #86b7fe;
            outline: 0;
            box-shadow: 0 0 0 0.25rem rgba(13, 110, 253, 0.25);
        }
        
        .order-input:hover {
            border-color: #86b7fe;
        }
        
        .order-qty-input {
            width: 80px;
            text-align: center;
        }
        
        .unit-price-input {
            width: 80px;
        }
        
        .delivery-date-input {
            width: 120px;
        }
        
        .fixed-columns {
            position: sticky;
            left: 0;
            z-index: 2;
        }
        
        .scrollable-columns {
            overflow-x: auto;
        }
        
        /* Filter controls styling */
        .filter-controls {
            background-color: white;
            border-radius: 8px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            padding: 15px;
            margin-bottom: 20px;
        }
        
        .filter-label {
            font-weight: 600;
            margin-right: 10px;
            color: #2c3e50;
        }
        
        .filter-select {
            min-width: 150px;
            margin-right: 20px;
        }
        
        .filter-btn {
            background-color: #3498db;
            color: white;
            border: none;
            padding: 8px 15px;
            border-radius: 4px;
            cursor: pointer;
        }
        
        .filter-btn:hover {
            background-color: #2980b9;
        }
        
        .filter-btn-reset {
            background-color: #95a5a6;
        }
        
        .filter-btn-reset:hover {
            background-color: #7f8c8d;
        }
    
        .table-responsive {
            position: relative;
            overflow-x: auto;
            -webkit-overflow-scrolling: touch;
        }

        /* Fix the header z-index */
        .table thead th.part-number,
        .table thead th.type-cell,
        .table thead th.stock-cell,
        .table thead th.lead-time-cell,
        .table thead th.run-out-cell,
        .table thead th.order-by-cell,
        .table thead th.status-cell,
        .table thead th.row-type-cell,
        .table thead th.arrears-cell {
            z-index: 40 !important;
        }

        .work-order-highlight {
            background-color: #fff8b3 !important;
            font-weight: bold;
        }
		/* ONLY ONE SCROLL CONTAINER */
/* Page scrolls normally */
.table-container {
    overflow: visible;
}

/* Table scrolls internally */
.table-responsive {
    max-height: calc(100vh - 220px);
    overflow-y: auto;
    overflow-x: auto;
    position: relative;
}

/* Sticky table header */
#forecastTable thead th {
    position: sticky;
    top: 0;
    background: #2c3e50;
    z-index: 100;
}



        /* Tooltip styles */
        .tooltip-inner {
            max-width: 400px;
            text-align: left;
            padding: 10px;
        }

        .part-number-tooltip {
            font-weight: bold;
            margin-bottom: 5px;
        }

        .bulk-order-history {
            margin-top: 10px;
            border-top: 1px solid #ddd;
            padding-top: 5px;
        }

        .bulk-order-item {
            margin-bottom: 5px;
        }
		.table-container {
    max-height: calc(100vh - 220px);
    overflow-y: auto;
}

.table-responsive {
    overflow-x: auto;
    overflow-y: visible !important;
}

#forecastTable thead th {
    position: sticky;
    top: 0;
    z-index: 100;
    background: #2c3e50;
}

    </style>
    <style>
        .pagination-container {
            background-color: white;
            border-radius: 8px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            padding: 15px;
        }

        .page-item.active .page-link {
            background-color: #3498db;
            border-color: #3498db;
        }

        .page-link {
            color: #3498db;
        }

        .page-link:hover {
            color: #2980b9;
        }
    </style>
    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/jquery.dataTables.min.css">
    <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
    
    <div class="dashboard-header">
        <div class="container-fluid">
            <div class="d-flex justify-content-between align-items-center">
                <h1 class="dashboard-title">
                    <i class="fas fa-boxes"></i> Raw Material Forecasting Dashboard
                </h1>
                <div>
                    <button class="bulk-order-btn" id="bulkOrderBtn">
                        <i class="fas fa-shopping-cart"></i> Bulk Purchase
                    </button>
                </div>
            </div>
        </div>
    </div>

    <div class="container-fluid">

<div class="filter-controls mb-3">
    <div class="d-flex align-items-center flex-wrap gap-2">
        <div class="d-flex align-items-center">
            <span class="filter-label">Filter by Status:</span>
            <select class="form-select filter-select" id="statusFilter">
                <option value="">All Statuses</option>
                <option value="OK" <?= $status_filter === 'OK' ? 'selected' : '' ?>>OK</option>
                <option value="Plan" <?= $status_filter === 'Plan' ? 'selected' : '' ?>>Plan</option>
                <option value="Urgent" <?= $status_filter === 'Urgent' ? 'selected' : '' ?>>Urgent</option>
            </select>
        </div>
        <div class="d-flex align-items-center">
            <span class="filter-label">Part Number:</span>
            <input type="text" class="form-control filter-select" id="partNumberFilter" 
                   placeholder="Search part number..." 
                   value="<?= htmlspecialchars($part_number_filter) ?>" 
                   style="min-width: 200px;">
        </div>
        <button class="filter-btn" id="applyFilter">Apply Filter</button>
        <?php if (!empty($status_filter) || !empty($part_number_filter)): ?>
            <button class="filter-btn filter-btn-reset ms-2" id="resetFilter">Reset</button>
        <?php endif; ?>
    </div>
</div> 
        <div class="table-container">
            <div class="table-responsive">
                <table class="table table-bordered table-hover" id="forecastTable">
                    <thead>
                        <tr>
                            <th class="part-number">Part Number</th>
                            <th class="type-cell">Preferred Supplier</th>
                            <th class="stock-cell">Stock</th>
                            <th class="lead-time-cell">Lead Time</th>
                            <th class="run-out-cell">Run Out Date</th>
                            <th class="order-by-cell">Order By Date</th>
                            <th class="status-cell">Status</th>
                            <th class="row-type-cell">Type</th>
                            <th class="arrears-cell">Arrears</th>
                            <?php foreach ($week_list as $i => $week): 
                                $week_start = date('d M', strtotime("$min_date +$i week"));
                                $week_end = date('d M', strtotime("$min_date +$i week +6 days"));
                                
                                echo "<th class='week-header'><span>$week_start<br>$week_end</span></th>";
                            endforeach; ?>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($part_data as $part_id => $part): ?>
                            <?php 
                            $available_stock = $part['stock'];
                            $status_class = '';
                            $status_text = $part['status'];
                            
                            if ($status_text === 'Urgent') {
                                $status_class = 'badge-danger';
                            } elseif ($status_text === 'OK') {
                                $status_class = 'badge-success';
                            } else {
                                $status_class = 'badge-warning';
                            }
                            ?>
                            <!-- SALES ORDER ROW -->
<tr class="sales-order-row part-row" 
    data-part-id="<?= $part_id ?>" 
    data-part-number="<?= htmlspecialchars($part['part_number']) ?>" 
    data-supplier="<?= htmlspecialchars($gsi->getSuppliersById($part['type_2'])) ?>"
    data-status="<?= $status_text ?>"
    data-order-date="<?= isset($part['order_date_obj']) ? $part['order_date_obj']->format('Y-m-d') : '' ?>">
<td class="part-number" rowspan="3" 
    data-bs-toggle="tooltip" 
    data-bs-html="true" 
    title="<?= htmlspecialchars(getTooltipContent($part_id, $bulk_order_data)) ?>">
    <?= htmlspecialchars($part['part_number']) ?>
</td>
   <td class="type-cell" rowspan="3">
    <?= $gsi->getSuppliersById($part['type_2']) . ' ' . $part['type_2'] ?>
</td>
    <td class="stock-cell" rowspan="3"><?= $part['stock'] ?></td>
    <td class="lead-time-cell" rowspan="3"><?= $part['lead_time'] ?></td>
    <td class="run-out-cell" rowspan="3"><?= $part['run_out_date'] ?></td>
    <td class="order-by-cell" rowspan="3"><?= $part['order_date'] ?></td>
    <td class="status-cell" rowspan="3"><span class="badge <?= $status_class ?>"><?= $status_text ?></span></td>
    <td class="row-type-cell">SO</td>
    <?php 
        // Calculate arrears display
        $arrears_qty = $part['arrears'] ?? 0;
        $arrears_class = '';
        if ($arrears_qty > $part['stock']) {
            $arrears_class = 'arrears-urgent';
        } elseif ($arrears_qty > 0) {
            $arrears_class = 'arrears-warning';
        }
    ?>
    <td class="arrears-cell <?= $arrears_class ?>" rowspan="3">
        <?= $arrears_qty > 0 ? number_format($arrears_qty, 0) : '-' ?>
    </td>
    <?php 
    $so_available_stock = $part['stock'];
    $total_pending_so = 0;

    foreach ($week_list as $i => $week): 
        $qty = $part['sales_orders'][$week];
        $class = '';

        $total_pending_so += $pending_orders[$part_id][$week] ?? 0;
        $available_now = $so_available_stock + $total_pending_so;

        if ($qty > 0) {
            if ($so_available_stock >= $qty) {
                $class = 'highlight';
                $so_available_stock -= $qty;
            } elseif ($available_now >= $qty) {
                $class = 'work-order-highlight';
                $consumed_from_pending = $qty - $so_available_stock;
                $total_pending_so -= $consumed_from_pending;
                $so_available_stock = 0;
            } else {
                $class = 'urgent';
                $so_available_stock = 0;
                $total_pending_so = 0;
            }
        }

        echo "<td class='$class'>" . ($qty > 0 ? $qty : '-') . "</td>";
    endforeach;
    ?>
</tr>
                         
<tr class="work-order-row part-row" data-part-id="<?= $part_id ?>">
    <td class="row-type-cell">WO</td>
    <?php 
    $wo_available_stock = $part['stock'];
    $total_pending_wo = 0;

    foreach ($week_list as $i => $week): 
        $qty = $part['work_orders'][$week];
        $class = '';

        $total_pending_wo += $pending_orders[$part_id][$week] ?? 0;
        $available_now = $wo_available_stock + $total_pending_wo;

        if ($qty > 0) {
            if ($wo_available_stock >= $qty) {
                $class = 'highlight';
                $wo_available_stock -= $qty;
            } elseif ($available_now >= $qty) {
                $class = 'work-order-highlight';
                $consumed_from_pending = $qty - $wo_available_stock;
                $total_pending_wo -= $consumed_from_pending;
                $wo_available_stock = 0;
            } else {
                $class = 'urgent';
                $wo_available_stock = 0;
                $total_pending_wo = 0;
            }
        }

        echo "<td class='$class'>" . ($qty > 0 ? $qty : '-') . "</td>";
    endforeach;
    ?>
</tr>
                           
                            <tr class="order-input-row" data-part-id="<?= $part_id ?>">
                                <td class="row-type-cell">Order</td>
                                <?php foreach ($week_list as $i => $week): 
                                    $total_qty = 0;
                                    echo "<td><input type='number' class='order-input' data-week='$week' data-part-id='$part_id' 
                                          min='0' value='" . ($total_qty > 0 ? $total_qty : '') . "'></td>";
                                endforeach; ?>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
        <!-- Pagination Section -->
<div class="pagination-container mt-4">
    <nav aria-label="Page navigation">
        <ul class="pagination justify-content-center">
            <?php if ($current_page > 1): ?>
                <li class="page-item">
                    <a class="page-link" href="<?= getPaginationUrl($current_page - 1, $status_filter, $part_number_filter) ?>" aria-label="Previous">
                        <span aria-hidden="true">&laquo;</span>
                    </a>
                </li>
            <?php endif; ?>
            
            <?php
            // Show page numbers
            $start_page = max(1, $current_page - 2);
            $end_page = min($total_pages, $current_page + 2);
            
            for ($i = $start_page; $i <= $end_page; $i++): 
            ?>
                <li class="page-item <?= $i == $current_page ? 'active' : '' ?>">
                    <a class="page-link" href="<?= getPaginationUrl($i, $status_filter, $part_number_filter) ?>"><?= $i ?></a>
                </li>
            <?php endfor; ?>
            
            <?php if ($current_page < $total_pages): ?>
                <li class="page-item">
                    <a class="page-link" href="<?= getPaginationUrl($current_page + 1, $status_filter, $part_number_filter) ?>" aria-label="Next">
                        <span aria-hidden="true">&raquo;</span>
                    </a>
                </li>
            <?php endif; ?>
        </ul>
    </nav>
    <div class="text-center text-muted">
        Showing <?= ($offset + 1) ?> to <?= min($offset + $per_page, $total_rows) ?> of <?= $total_rows ?> entries
        <?php if (!empty($status_filter) || !empty($part_number_filter)): ?>
            <br><small>Filtered by:
            <?php if (!empty($status_filter)): ?>
                Status: <?= htmlspecialchars($status_filter) ?>
            <?php endif; ?>
            <?php if (!empty($part_number_filter)): ?>
                <?= !empty($status_filter) ? ', ' : '' ?>Part Number: <?= htmlspecialchars($part_number_filter) ?>
            <?php endif; ?>
            </small>
        <?php endif; ?>
    </div>
</div>
    </div>

    <!-- Bulk Order Modal -->
<div class="modal fade" id="orderModal" tabindex="-1" aria-hidden="true">
    <div class="modal-dialog modal-lg">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Place Purchase Order</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
            </div>
            <div class="modal-body">
                <form id="orderForm">
                    <div class="table-responsive">
                        <table class="table table-bordered">
                            <thead>
                                <tr>
                                    <th>Part Number</th>
                                    <th>Supplier</th>
                                    <th>Week/Dates</th>
                                    <th>Order Qty</th>
                                    <th>Unit Price</th>
                                </tr>
                            </thead>
                            <tbody id="selectedItemsBody">
                                <!-- Will be populated by JavaScript -->
                            </tbody>
                        </table>
                    </div>
                    
                    <div class="form-group mt-3">
                        <label class="control-label">Notes</label>
                        <textarea class="form-control" name="notes" rows="2"></textarea>
                    </div>
                    
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                        <button type="submit" class="btn btn-primary">Submit Order</button>
                    </div>
                </form>
            </div>
        </div>
    </div>
</div>


    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>


    <script>
document.addEventListener('DOMContentLoaded', function() {
    // Initialize tooltips
    var tooltipTriggerList = [].slice.call(document.querySelectorAll('[data-bs-toggle="tooltip"]'));
    tooltipTriggerList.map(function (tooltipTriggerEl) {
        return new bootstrap.Tooltip(tooltipTriggerEl, {
            html: true,
            placement: 'right',
            boundary: 'window'
        });
    });

    // Apply filter button
    $('#applyFilter').click(function() {
        const statusFilter = $('#statusFilter').val();
        const partNumberFilter = $('#partNumberFilter').val().trim();
        
        let url = 'index?page=view_purchase_list';
        const params = [];
        
        if (statusFilter) {
            params.push('status=' + encodeURIComponent(statusFilter));
        }
        if (partNumberFilter) {
            params.push('part_number=' + encodeURIComponent(partNumberFilter));
        }
        
        if (params.length > 0) {
            url += '&' + params.join('&');
        }
        
        window.location.href = url;
    });

    // Reset filter button
    $('#resetFilter').click(function() {
        window.location.href = 'index?page=view_purchase_list';
    });
    
    // Allow Enter key to apply filter in part number input
    $('#partNumberFilter').keypress(function(e) {
        if (e.which === 13) {
            $('#applyFilter').click();
        }
    });

    // Apply client-side filtering if needed (for visual feedback)
    const urlParams = new URLSearchParams(window.location.search);
    const currentStatus = urlParams.get('status');
    
    if (currentStatus) {
        applyClientSideStatusFilter(currentStatus);
    }

  });
</script>
<script>
$('#bulkOrderBtn').click(function() {
    const orderItems = [];
    
    $('.order-input-row').each(function() {
        const partId = $(this).data('part-id');
        const partRow = $(this).closest('tbody').find(`.part-row[data-part-id="${partId}"]`);
        const partNumber = partRow.data('part-number');
        const supplier = partRow.data('supplier');
        const stock = partRow.find('.stock-cell').text();
        
        // Get all week headers to map week numbers to start dates
        const weekDates = {};
        $('.week-header').each(function(index) {
            const weekText = $(this).find('span').html().split('<br>')[0];
            weekDates[$('#forecastTable thead th').eq(8 + index).index()] = weekText;
        });

        // Collect all weeks with quantities
        const weekOrders = [];
        $(this).find('.order-input').each(function(index) {
            const week = $(this).data('week');
            const qty = parseInt($(this).val()) || 0;
            if (qty > 0) {
                // Get the corresponding week start date
                const weekIndex = 9 + index;
                const weekHeader = $('#forecastTable thead th').eq(weekIndex);
                const weekStartDate = weekHeader.find('span').html().split('<br>')[0] || `Week ${week}`;
                
                weekOrders.push({
                    week: week,
                    startDate: weekStartDate,
                    qty: qty
                });
            }
        });
        
        if (weekOrders.length > 0) {
            orderItems.push({
                partId: partId,
                partNumber: partNumber,
                supplier: supplier,
                stock: stock,
                weekOrders: weekOrders
            });
        }
    });
    
    if (orderItems.length === 0) {
        alert('Please enter quantities for at least one part');
        return;
    }
                
    // Populate the modal with individual week quantities
    const itemsBody = $('#selectedItemsBody');
    itemsBody.empty();
    
    orderItems.forEach(item => {
        // Add a header row for the part
        const headerRow = `
            <tr class="table-primary">
                <td colspan="5"><strong>${item.partNumber}</strong> (Current Stock: ${item.stock})</td>
            </tr>
        `;
        itemsBody.append(headerRow);
        
        // Add a row for each week with quantity
        item.weekOrders.forEach(weekOrder => {
            const row = `
                <tr>
                    <td>${item.partNumber}</td>
                    <td>${item.supplier}</td>
                    <td>${weekOrder.startDate}</td>
                    <td><input type="number" class="form-control order-qty-input" 
                               name="order_qty[${item.partId}][${weekOrder.week}]" 
                               value="${weekOrder.qty}" min="1" required></td>
                    <td><input type="number" class="form-control unit-price-input" 
                               name="unit_price[${item.partId}][${weekOrder.week}]" 
                               step="0.01" min="0" required></td>
                </tr>
            `;
            itemsBody.append(row);
        });
    });
    
    // Show the modal
    $('#orderModal').modal('show');
});
    </script>  
<script>
$('#orderForm').submit(function(e) {
    e.preventDefault();
    const orderData = {
        notes: $('textarea[name="notes"]').val(),
        orders: []
    };
    
   
    const supplierMap = {};
    
    $('#selectedItemsBody tr').each(function() {
        if ($(this).find('.order-qty-input').length) {
            const partNumber = $(this).find('td:eq(0)').text().trim();
            const supplier = $(this).find('td:eq(1)').text().trim();
            const weekDate = $(this).find('td:eq(2)').text().trim();
            const qty = $(this).find('.order-qty-input').val();
            const unitPrice = $(this).find('.unit-price-input').val();
            
            if (!qty || qty <= 0) return;
            
            if (!supplierMap[supplier]) {
                supplierMap[supplier] = {
                    supplier: supplier,
                    items: []
                };
            }
            
            supplierMap[supplier].items.push({
                partNumber: partNumber,
                weekDate: weekDate,
                qty: qty,
                unitPrice: unitPrice
            });
        }
    });
    
   
    for (const supplier in supplierMap) {
        orderData.orders.push(supplierMap[supplier]);
    }
    
    if (orderData.orders.length === 0) {
        alert('Please enter quantities for at least one item');
        return;
    }
    
    console.log('Submitting:', orderData);
    
    $.ajax({
        url: site_url+"/modules/machines_management/index.php?page=ajax_actions&action=process_bulk_order",
        type: 'POST',
        data: { order_data: JSON.stringify(orderData) },
        dataType: 'json',
        success: function(response) {
            if (response.success) {
                alert('Order submitted successfully!');
                $('#orderModal').modal('hide');
                location.reload();
            } else {
                alert('Error: ' + response.message);
            }
        },
        error: function(xhr, status, error) {
            alert('Error submitting order: ' + error);
            console.error(xhr.responseText);
        }
    });
});
    </script>