<?php
// Get filter parameters
$status_filter = isset($_GET['status']) ? $_GET['status'] : '';
$part_number_filter = isset($_GET['part_number']) ? trim($_GET['part_number']) : '';
$supplier_filter = isset($_GET['supplier']) ? $_GET['supplier'] : '';
// 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, standard_cost_ebq, 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'],
        'unit_price' => $part['standard_cost_ebq'],
        '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 from WORK ORDERS (overdue)
    $today = date('Y-m-d');

    $arrears_query = "
    SELECT 
        wod.product_id, 
        SUM(wod.quantity) AS total_arrears
    FROM work_order_raw_matrial wod
    JOIN item_management im ON wod.product_id = im.id
    WHERE wod.required_date < '$today'
    AND im.usual_source = 'Bought In'
    AND wod.product_id IN ($all_part_ids_str)
    GROUP BY wod.product_id";

    $arrears_result = mysqli_query($conn, $arrears_query);

    while ($row = mysqli_fetch_assoc($arrears_result)) {
        $part_id = $row['product_id'];
        if (isset($all_part_data[$part_id])) {
            $all_part_data[$part_id]['arrears'] = (int)$row['total_arrears'];
        }
    }
	// Pending Purchase Orders total (not yet received)
	$pending_po_totals = [];
	if (!empty($all_part_ids_str)) {
		$pending_po_query = "
			SELECT 
				part_number,
				SUM(quantity - received) AS pending_total
			FROM purchase_order_items_mapping
			WHERE status = 0 
			AND (quantity - received) > 0
			AND part_number IN ($all_part_ids_str)
			GROUP BY part_number";

		$pending_po_result = mysqli_query($conn, $pending_po_query);
		while ($row = mysqli_fetch_assoc($pending_po_result)) {
			$pending_po_totals[$row['part_number']] = (int)$row['pending_total'];
		}
	}

    // 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_raw_matrial wod
    JOIN item_management im ON wod.product_id = im.id
    WHERE wod.required_date BETWEEN '$min_date' AND '$max_date'
    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";
        $part['pending_po'] = $pending_po_totals[$part_id] ?? 0;
        // 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;
        }

        $running_stock = max(0, $part['stock'] - ($part['arrears'] ?? 0));
        $run_out_week = null;
        $run_out_date_obj = null;

        // Process weeks in chronological order
        foreach ($week_list as $week) {
            $pending_arriving = $pending_orders_all[$part_id][$week] ?? 0;
            $running_stock += $pending_arriving;
            
            $sales_demand = $part['sales_orders'][$week] ?? 0;
            $work_demand = $part['work_orders'][$week] ?? 0;
            $total_week_demand = $sales_demand + $work_demand;
            
            $running_stock -= $total_week_demand;
            
            if ($running_stock < 0 && $run_out_week === null) {
                $run_out_week = $week;
                
                $week_date = new DateTime();
                $week_date->setISODate(substr($week, 0, 4), substr($week, 4, 2));
                $run_out_date_obj = $week_date;
                break;
            }
        }

        if ($run_out_date_obj === null) {
            $part['status'] = 'OK';
            $part['run_out_date'] = 'Sufficient Stock';
            $part['order_date'] = '-';
            $part['order_date_obj'] = null;
        } else {
            $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;
            
            $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) {
        if (stripos($part['part_number'], $part_number_filter) !== false) {
            $filtered_part_data[$part_id] = $part;
        }
    }
    $all_part_data = $filtered_part_data;
}

if (!empty($supplier_filter)) {
    $filtered_part_data = [];
    foreach ($all_part_data as $part_id => $part) {
        $supplier_name = $gsi->getSuppliersById($part['type_2']);
        if ($supplier_name === $supplier_filter) {
            $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);

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);
    
    $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']
            ];
        }
    }

    $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);

$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 getPaginationUrl($page, $status_filter = '', $part_number_filter = '', $supplier_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 (!empty($supplier_filter)) {
        $params['supplier'] = $supplier_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 {
            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;
        }

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

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

        .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; }
    </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>
                <div class="d-flex align-items-center">
                    <span class="filter-label">Supplier:</span>
                    <select class="form-select filter-select" id="supplierFilter">
                        <option value="">All Suppliers</option>
                        <?php foreach ($suppliers as $supplier): ?>
                            <option value="<?= htmlspecialchars($supplier) ?>" 
                                <?= $supplier_filter === $supplier ? 'selected' : '' ?>>
                                <?= htmlspecialchars($supplier) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <button class="filter-btn" id="applyFilter">Apply Filter</button>
                <?php if (!empty($status_filter) || !empty($part_number_filter) || !empty($supplier_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/padding</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_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-unit-price="<?= $part['unit_price'] ?? 0 ?>"
                                data-part-number="<?= htmlspecialchars($part['part_number']) ?>" 
                                data-supplier="<?= htmlspecialchars($gsi->getSuppliersById($part['type_2'])) ?>"
                                data-supplier-id="<?= $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">
                                    <strong><?= htmlspecialchars($part['part_number']) ?></strong>
                                    <?php if (isset($bulk_order_data[$part_id])): 
                                        $data = $bulk_order_data[$part_id]; ?>
                                        <div style="font-size:0.78rem; color:#555; margin-top:4px; font-weight:normal;">
                                            <span><strong>Min Qty:</strong> <?= $data['minimum_quantity'] ?? 'Not set' ?></span>
                                            <?php if (!empty($data['bulk_orders'])): ?>
                                                <div style="margin-top:3px;"><strong>Price Breaks:</strong></div>
                                                <?php foreach ($data['bulk_orders'] as $order): ?>
                                                    <div>• Price: <?= $order['price_per_item'] ?> | Qty: <?= $order['order_quantity'] ?></div>
                                                <?php endforeach; ?>
                                            <?php endif; ?>
                                        </div>
                                    <?php endif; ?>
                                </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 
									$arrears_qty = $part['arrears'] ?? 0;
									$pending_po_qty = $part['pending_po'] ?? 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">
									<span title="Arrears">A: <?= $arrears_qty > 0 ? number_format($arrears_qty, 0) : '-' ?></span>
									<hr style="margin: 3px 0; border-color: currentColor; opacity: 0.3;">
									<span title="Pending PO">P: <?= $pending_po_qty > 0 ? number_format($pending_po_qty, 0) : '-' ?></span>
								</td>

                                <?php 
                                $arrears_qty        = $part['arrears'] ?? 0;
                                $so_available_stock = max(0, $part['stock'] - $arrears_qty);
                                $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>

                            <!-- WORK ORDER ROW -->
                            <tr class="work-order-row part-row" data-part-id="<?= $part_id ?>">
                                <td class="row-type-cell">WO</td>
                                <?php 
                                $arrears_qty        = $part['arrears'] ?? 0;
                                $wo_available_stock = max(0, $part['stock'] - $arrears_qty);
                                $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>

                            <!-- ORDER INPUT ROW -->
                            <tr class="order-input-row" data-part-id="<?= $part_id ?>">
                                <td class="row-type-cell">Order</td>
                                <?php foreach ($week_list as $i => $week): 
                                    echo "<td><input type='number' class='order-input' data-week='$week' data-part-id='$part_id' 
                                          min='0' value=''></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, $supplier_filter) ?>" aria-label="Previous">
                                <span aria-hidden="true">&laquo;</span>
                            </a>
                        </li>
                    <?php endif; ?>
                    
                    <?php
                    $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, $supplier_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, $supplier_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) || !empty($supplier_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; ?>
                    <?php if (!empty($supplier_filter)):  ?><?= (!empty($status_filter) || !empty($part_number_filter)) ? ', ' : '' ?>Supplier: <?= htmlspecialchars($supplier_filter) ?><?php endif; ?>
                    </small>
                <?php endif; ?>
            </div>
        </div>

    </div><!-- /.container-fluid -->

    <!-- 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">
                                    <!-- 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() {
    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'
        });
    });

    // ── Restore scroll position after reload ──
    const tableContainer = document.querySelector('.table-container');
    const savedTop  = sessionStorage.getItem('rmf_scrollTop');
    const savedLeft = sessionStorage.getItem('rmf_scrollLeft');
    if (tableContainer && savedTop !== null) {
        tableContainer.scrollTop  = parseInt(savedTop,  10);
        tableContainer.scrollLeft = parseInt(savedLeft, 10);
        sessionStorage.removeItem('rmf_scrollTop');
        sessionStorage.removeItem('rmf_scrollLeft');
    }

    // Apply filter
    $('#applyFilter').click(function() {
        const statusFilter     = $('#statusFilter').val();
        const partNumberFilter = $('#partNumberFilter').val().trim();
        const supplierFilter   = $('#supplierFilter').val();

        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 (supplierFilter)   params.push('supplier='    + encodeURIComponent(supplierFilter));

        if (params.length > 0) url += '&' + params.join('&');
        window.location.href = url;
    });

    // Reset filter
    $('#resetFilter').click(function() {
        window.location.href = 'index?page=view_purchase_list';
    });

    // Enter key on part number input
    $('#partNumberFilter').keypress(function(e) {
        if (e.which === 13) $('#applyFilter').click();
    });
});
</script>

<script>
// ── Bulk Order Button ─────────────────────────────────────────
$('#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}"]`).first();
        const partNumber = partRow.data('part-number');
        const supplier   = partRow.data('supplier');
        const supplierId = partRow.data('supplier-id');
        const unitPrice  = partRow.data('unit-price') || 0;
        const stock      = partRow.find('.stock-cell').text();

        const weekOrders = [];
        $(this).find('.order-input').each(function(index) {
            const week = $(this).data('week');
            const qty  = parseInt($(this).val()) || 0;
            if (qty > 0) {
                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,
                supplierId: supplierId,
                stock:      stock,
                unitPrice:  unitPrice,
                weekOrders: weekOrders
            });
        }
    });

    if (orderItems.length === 0) {
        alert('Please enter quantities for at least one part');
        return;
    }

    const itemsBody = $('#selectedItemsBody');
    itemsBody.empty();

    orderItems.forEach(item => {
        // Header row per part
        itemsBody.append(`
            <tr class="table-primary">
                <td colspan="5"><strong>${item.partNumber}</strong> (Current Stock: ${item.stock})</td>
            </tr>
        `);

        // One row per week
        item.weekOrders.forEach(weekOrder => {
            itemsBody.append(`
                <tr>
                    <td>${item.partNumber}</td>
                    <td data-supplier-id="${item.supplierId}">${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}]"
                               value="${item.unitPrice}" step="0.01" min="0" required></td>
                </tr>
            `);
        });
    });

    $('#orderModal').modal('show');
});
</script>

<script>
// ── Order Form Submit ─────────────────────────────────────────
$('#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) return; // skip header rows

        const partNumber = $(this).find('td:eq(0)').text().trim();
        const supplier   = $(this).find('td:eq(1)').text().trim();
        const supplierId = $(this).find('td:eq(1)').data('supplier-id');
        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[supplierId]) {
            supplierMap[supplierId] = {
                supplier:   supplier,
                supplierId: supplierId,
                items:      []
            };
        }

        supplierMap[supplierId].items.push({
            partNumber: partNumber,
            weekDate:   weekDate,
            qty:        qty,
            unitPrice:  unitPrice
        });
    });

    for (const sid in supplierMap) {
        orderData.orders.push(supplierMap[sid]);
    }

    if (orderData.orders.length === 0) {
        alert('Please enter quantities for at least one item');
        return;
    }

    console.log('Submitting:', orderData);

    $.ajax({
        url:      site_url + "/modules/work_order_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');

                // ── Save scroll position before reload ──
                const tableContainer = document.querySelector('.table-container');
                sessionStorage.setItem('rmf_scrollTop',  tableContainer ? tableContainer.scrollTop  : 0);
                sessionStorage.setItem('rmf_scrollLeft', tableContainer ? tableContainer.scrollLeft : 0);

                location.reload();
            } else {
                alert('Error: ' + response.message);
            }
        },
        error: function(xhr, status, error) {
            alert('Error submitting order: ' + error);
            console.error(xhr.responseText);
        }
    });
});
</script>

<script>
// ── SO / WO Cell Selection Sum (Excel-style) ──────────────────
(function() {
    const bar = document.createElement('div');
    bar.id = 'cellSumBar';
    bar.style.cssText = `
        position: fixed; bottom: 0; left: 0; right: 0;
        background: #fff; border-top: 2px solid #3498db;
        padding: 8px 24px; display: none;
        align-items: center; gap: 20px;
        z-index: 9999; box-shadow: 0 -2px 10px rgba(0,0,0,0.1);
    `;
    bar.innerHTML = `
        <span style="font-size:13px;color:#6c757d;">
            <i class="fas fa-calculator" style="color:#3498db;margin-right:6px;"></i>
            Selected: <strong id="cs-count" style="color:#2c3e50;">0</strong> cells
        </span>
        <span style="font-size:13px;color:#6c757d;">
            Sum: <strong id="cs-sum" style="color:#2c3e50;font-size:16px;">0</strong>
        </span>
        <span style="font-size:11px;color:#6c757d;">
            Hold <kbd>Ctrl</kbd> to select multiple cells
        </span>
    `;
    document.body.appendChild(bar);

    const selectedCells = new Set();

    function updateBar() {
        let sum = 0;
        selectedCells.forEach(function(td) {
            sum += parseInt(td.textContent.trim()) || 0;
        });
        document.getElementById('cs-count').textContent = selectedCells.size;
        document.getElementById('cs-sum').textContent   = sum.toLocaleString();
        bar.style.display = selectedCells.size > 0 ? 'flex' : 'none';
    }

    document.addEventListener('click', function(e) {
        const td = e.target.closest('td');
        if (!td) {
            if (!e.ctrlKey) {
                selectedCells.forEach(c => c.style.outline = '');
                selectedCells.clear();
                updateBar();
            }
            return;
        }

        const row     = td.parentElement;
        const rowType = row && row.querySelector('.row-type-cell');
        const type    = rowType ? rowType.textContent.trim() : '';

        const val = parseInt(td.textContent.trim());
        if ((type !== 'SO' && type !== 'WO') || isNaN(val) || td.textContent.trim() === '-') {
            if (!e.ctrlKey) {
                selectedCells.forEach(c => c.style.outline = '');
                selectedCells.clear();
                updateBar();
            }
            return;
        }

        if (!e.ctrlKey) {
            selectedCells.forEach(c => c.style.outline = '');
            selectedCells.clear();
        }

        if (selectedCells.has(td)) {
            td.style.outline = '';
            selectedCells.delete(td);
        } else {
            td.style.outline       = '2px solid #3498db';
            td.style.outlineOffset = '-2px';
            selectedCells.add(td);
        }

        updateBar();
    });
})();
</script>