<?php
// Get the number of weeks to display (default 12)
$weeks_to_show = isset($_GET['weeks']) ? intval($_GET['weeks']) : 12;

// Calculate week dates
$week_dates = [];
$current_date = new DateTime();
$current_date->modify('this week');

for ($i = 0; $i < $weeks_to_show; $i++) {
    $week_start = clone $current_date;
    $week_start->modify("+$i week");
    $week_end = clone $week_start;
    $week_end->modify('+6 days');
    
    $week_dates[$i] = [
        'start' => $week_start->format('Y-m-d'),
        'end' => $week_end->format('Y-m-d'),
        'label' => 'WEEK+' . $i
    ];
}

// Function to get inventory stock for a part
function getInventoryStock($conn, $part_id) {
    $query = "SELECT COALESCE(SUM(quantity_in_stock), 0) as stock 
              FROM inventory 
              WHERE item_id = $part_id";
    $result = mysqli_query($conn, $query);
    $row = mysqli_fetch_array($result);
    return $row['stock'];
}

// Function to check if PO exists for a part
function checkPOExists($conn, $part_id, $week_start, $week_end) {
    $query = "SELECT COUNT(*) as po_count 
              FROM purchase_order po
              JOIN purchase_order_items_mapping poim ON po.id = poim.po_id
              WHERE poim.part_number = $part_id
              AND poim.expected_date BETWEEN '$week_start' AND '$week_end'
              AND po.order_status NOT IN (4, 5)";
    
    $result = mysqli_query($conn, $query);
    $row = mysqli_fetch_array($result);
    return $row['po_count'] > 0;
}

// Function to get all bought-in parts recursively from BOM - MOVED BEFORE getWorkOrderStatus
function getAllBoughtInParts($conn, $main_product_id, $multiplier = 1) {
    $bought_in_parts = [];
    
    $query = "SELECT sub_part_id, quantity_required, type 
              FROM bom 
              WHERE main_product_id = $main_product_id";
    
    $result = mysqli_query($conn, $query);
    
    while ($row = mysqli_fetch_array($result)) {
        $sub_part_id = $row['sub_part_id'];
        $qty = $row['quantity_required'] * $multiplier;
        $type = $row['type'];
        
        if ($type == 'Bought In') {
            // This is a bought-in part, add it to our list
            if (isset($bought_in_parts[$sub_part_id])) {
                $bought_in_parts[$sub_part_id] += $qty;
            } else {
                $bought_in_parts[$sub_part_id] = $qty;
            }
        } else if ($type == 'Manufactured') {
            // This is manufactured, recursively get its bought-in parts
            $child_parts = getAllBoughtInParts($conn, $sub_part_id, $qty);
            foreach ($child_parts as $child_id => $child_qty) {
                if (isset($bought_in_parts[$child_id])) {
                    $bought_in_parts[$child_id] += $child_qty;
                } else {
                    $bought_in_parts[$child_id] = $child_qty;
                }
            }
        }
    }
    
    return $bought_in_parts;
}

// Function to get work order status for manufactured parts
function getWorkOrderStatus($conn, $part_id, $week_start, $week_end, $req_date = null)
{
    $query = "
        SELECT pp.work_order_id
        FROM production_planning pp
        WHERE pp.part_id = '$part_id'
        AND pp.req_date BETWEEN '$week_start' AND '$week_end'
    ";

    $result = mysqli_query($conn, $query);

    if (mysqli_num_rows($result) == 0)
    {
        return 'no_wo';
    }

    $wo_statuses = [];

    while ($row = mysqli_fetch_assoc($result))
    {
        $work_order_id = $row['work_order_id'];

        if (empty($work_order_id))
        {
            $wo_statuses[] = 'no_wo_id';
            continue;
        }

        /* ==========================
           VAC STATUS
           ========================== */

        $vac_query = "
            SELECT
                COUNT(*) AS total,
                SUM(CASE WHEN finished = 1 THEN 1 ELSE 0 END) AS finished_count,
                MAX(end_date) AS latest_end_date
            FROM vac_machine_table
            WHERE work_order_id = '$work_order_id'
        ";

        $vac_result = mysqli_query($conn, $vac_query);
        $vac_row = mysqli_fetch_assoc($vac_result);

        $vac_total = (int)$vac_row['total'];
        $vac_finished = (int)$vac_row['finished_count'];

        $vac_complete =
            ($vac_total > 0 && $vac_total == $vac_finished);

        $vac_in_progress =
            ($vac_total > 0 && $vac_finished < $vac_total);

        /* ==========================
           CNC STATUS
           ========================== */

        $cnc_query = "
            SELECT
                COUNT(*) AS total,
                SUM(CASE WHEN finished = 1 THEN 1 ELSE 0 END) AS finished_count,
                MAX(end_date) AS latest_end_date
            FROM cnc_machine_table
            WHERE work_order_id = '$work_order_id'
        ";

        $cnc_result = mysqli_query($conn, $cnc_query);
        $cnc_row = mysqli_fetch_assoc($cnc_result);

        $cnc_total = (int)$cnc_row['total'];
        $cnc_finished = (int)$cnc_row['finished_count'];

        $cnc_complete =
            ($cnc_total > 0 && $cnc_total == $cnc_finished);

        $cnc_in_progress =
            ($cnc_total > 0 && $cnc_finished < $cnc_total);

        /* ==========================
           COMPLETION DATE
           ========================== */

        $vac_end =
            !empty($vac_row['latest_end_date'])
                ? strtotime($vac_row['latest_end_date'])
                : 0;

        $cnc_end =
            !empty($cnc_row['latest_end_date'])
                ? strtotime($cnc_row['latest_end_date'])
                : 0;

        $completion_date = max($vac_end, $cnc_end);

        /* ==========================
           STATUS LOGIC
           ========================== */

        if ($vac_in_progress)
        {
            $wo_statuses[] = 'in_vac';
        }
        elseif ($cnc_in_progress)
        {
            $wo_statuses[] = 'in_cnc';
        }
        elseif (
            ($vac_total > 0 || $cnc_total > 0) &&
            ($vac_complete || $vac_total == 0) &&
            ($cnc_complete || $cnc_total == 0)
        )
        {
            if (
                $req_date &&
                $completion_date > 0 &&
                $completion_date <= strtotime($req_date)
            )
            {
                $wo_statuses[] = 'completed_on_time';
            }
            else
            {
                $wo_statuses[] = 'completed_late';
            }
        }
        else
        {
            $wo_statuses[] = 'wo_created';
        }
    }

    if (empty($wo_statuses))
    {
        return 'no_wo';
    }

    $priority = [
        'wo_not_found'      => 1,
        'no_wo_id'          => 2,
        'wo_created'        => 3,
        'in_cnc'            => 4,
        'in_vac'            => 5,
        'completed_late'    => 6,
        'completed_on_time' => 7
    ];

    $current_priority = 0;
    $overall_status = 'no_wo';

    foreach ($wo_statuses as $status)
    {
        if (isset($priority[$status]) &&
            $priority[$status] > $current_priority)
        {
            $current_priority = $priority[$status];
            $overall_status = $status;
        }
    }

    return $overall_status;
}

// Function to get child parts status (updated to pass requirement date)
function getChildPartsStatus($conn, $part_id, $week_start, $week_end, $req_date = null) {
    // Get all child parts from BOM
    $query = "SELECT sub_part_id, type, quantity_required 
              FROM bom 
              WHERE main_product_id = $part_id";
    
    $result = mysqli_query($conn, $query);
    $all_ready = true;
    
    while ($row = mysqli_fetch_array($result)) {
        $child_id = $row['sub_part_id'];
        $child_type = $row['type'];
        $qty_required = $row['quantity_required'];
        
        if ($child_type == 'Bought In') {
            $stock = getInventoryStock($conn, $child_id);
            if ($stock < $qty_required) {
                $all_ready = false;
                break;
            }
        } else if ($child_type == 'Manufactured') {
            // Pass the requirement date to child status check
            $status = getWorkOrderStatus($conn, $child_id, $week_start, $week_end, $req_date);
            
            // Check if child part will be ready on time
            if (!in_array($status, ['completed_on_time', 'vac_scheduled', 'cnc_scheduled', 'wo_created'])) {
                $all_ready = false;
                break;
            }
        }
    }
    
    return $all_ready;
}

// Main query to get production planning data (updated to include req_date)
$query = "SELECT DISTINCT
            pp.id,
            pp.part_id,
            pp.work_order_id,
            im.part_number as main_part_number,
            im.product_description as main_product_desc,
            im.usual_source,
            pp.req_date,
            pp.req_qty,
            pp.customer_reff,
            pp.status
          FROM production_planning pp
          JOIN item_management im ON pp.part_id = im.id
          WHERE pp.req_date >= '{$week_dates[0]['start']}'
          AND pp.req_date <= '{$week_dates[$weeks_to_show - 1]['end']}'
          ORDER BY im.part_number, pp.req_date";

$result = mysqli_query($conn, $query);

// Collect all main parts with their weekly requirements
$parts_data = [];

while ($row = mysqli_fetch_array($result)) {
    $main_part_num = $row['main_part_number'];
    
    if (!isset($parts_data[$main_part_num])) {
        $parts_data[$main_part_num] = [
            'description' => $row['main_product_desc'],
            'part_id' => $row['part_id'],
            'usual_source' => $row['usual_source'],
            'customer' => $row['customer_reff'],
            'weeks' => array_fill(0, $weeks_to_show, ['qty' => 0, 'status' => '', 'req_dates' => [], 'bought_in_parts' => []])
        ];
    }
    
    // Find which week this requirement falls into
    $req_date = $row['req_date'];
    $req_qty = $row['req_qty'];
    $work_order_id = $row['work_order_id'];
    
    for ($i = 0; $i < $weeks_to_show; $i++) {
        if ($req_date >= $week_dates[$i]['start'] && $req_date <= $week_dates[$i]['end']) {
            $parts_data[$main_part_num]['weeks'][$i]['qty'] += $req_qty;
            $parts_data[$main_part_num]['weeks'][$i]['req_dates'][] = $req_date;
            
            // Get all bought-in parts for this main product
            $bought_in_parts = getAllBoughtInParts($conn, $row['part_id'], $req_qty);
            
            // Merge bought-in parts
            if (!empty($parts_data[$main_part_num]['weeks'][$i]['bought_in_parts'])) {
                foreach ($bought_in_parts as $bought_part_id => $bought_qty) {
                    if (isset($parts_data[$main_part_num]['weeks'][$i]['bought_in_parts'][$bought_part_id])) {
                        $parts_data[$main_part_num]['weeks'][$i]['bought_in_parts'][$bought_part_id] += $bought_qty;
                    } else {
                        $parts_data[$main_part_num]['weeks'][$i]['bought_in_parts'][$bought_part_id] = $bought_qty;
                    }
                }
            } else {
                $parts_data[$main_part_num]['weeks'][$i]['bought_in_parts'] = $bought_in_parts;
            }
            break;
        }
    }
}

// ... (keep all your existing functions unchanged until the display section)

// Add this new function after getStatusText()
function getInventoryAvailability($conn, $part_id, $required_qty) {
    $stock = getInventoryStock($conn, $part_id);
    return [
        'available' => min($stock, $required_qty),
        'remaining' => max(0, $required_qty - $stock),
        'in_stock' => $stock >= $required_qty
    ];
}

// ... (keep all your existing functions unchanged until the main loop)

// Calculate status for each part and week with progressive inventory consumption
foreach ($parts_data as $part_num => &$part_info) {
    $part_id = $part_info['part_id'];
    $usual_source = $part_info['usual_source'];
    
    // Get initial inventory stock for this part
    $remaining_inventory = getInventoryStock($conn, $part_id);
    
    for ($i = 0; $i < $weeks_to_show; $i++) {
        if ($part_info['weeks'][$i]['qty'] > 0) {
            $req_qty = $part_info['weeks'][$i]['qty'];
            $week_start = $week_dates[$i]['start'];
            $week_end = $week_dates[$i]['end'];
            $bought_in_parts = $part_info['weeks'][$i]['bought_in_parts'];
            $req_dates = $part_info['weeks'][$i]['req_dates'];
            
            // Calculate how much can be fulfilled from remaining inventory
            $from_inventory = min($remaining_inventory, $req_qty);
            $production_qty = $req_qty - $from_inventory;
            
            // Deduct used inventory for next week's calculation
            $remaining_inventory -= $from_inventory;
            
            // Store inventory information
            $part_info['weeks'][$i]['inventory_info'] = [
                'available' => $from_inventory,
                'remaining' => $production_qty,
                'in_stock' => ($production_qty == 0)
            ];
            $part_info['weeks'][$i]['production_qty'] = $production_qty;
            
            // Use the earliest requirement date for checking
            $earliest_req_date = !empty($req_dates) ? min($req_dates) : null;
            
            // If entire requirement can be fulfilled from inventory
            if ($production_qty == 0) {
                $part_info['weeks'][$i]['status'] = 'from_inventory';
                $part_info['weeks'][$i]['process_status'] = 'complete';
                continue;
            }
            
            // Calculate bought-in parts needed only for production quantity
            $production_bought_in_parts = [];
            foreach ($bought_in_parts as $bought_part_id => $total_bought_qty) {
                // Calculate proportional quantity needed for production
                $production_bought_in_parts[$bought_part_id] = ($total_bought_qty / $req_qty) * $production_qty;
            }
            
            // Check bought-in parts status for production quantity
            $all_parts_in_stock = true;
            $any_po_required = false;
            $any_po_created = false;
            
            foreach ($production_bought_in_parts as $bought_part_id => $bought_qty) {
                $stock = getInventoryStock($conn, $bought_part_id);
                if ($stock < $bought_qty) {
                    $all_parts_in_stock = false;
                    $po_exists = checkPOExists($conn, $bought_part_id, $week_start, $week_end);
                    if ($po_exists) {
                        $any_po_created = true;
                    } else {
                        $any_po_required = true;
                    }
                }
            }
            
            // Set status based on bought-in parts availability
            if ($any_po_required) {
                $part_info['weeks'][$i]['status'] = 'po_required';
                $part_info['weeks'][$i]['process_status'] = 'po_required';
            } else if ($any_po_created) {
                $part_info['weeks'][$i]['status'] = 'po_created';
                $part_info['weeks'][$i]['process_status'] = 'po_created';
            } else if ($all_parts_in_stock) {
                // All bought-in parts are in stock, check work order status
                $wo_status = getWorkOrderStatus($conn, $part_id, $week_start, $week_end, $earliest_req_date);
                
                // Map detailed status to simplified status for display
                switch ($wo_status) {
                    case 'in_vac':
						$part_info['weeks'][$i]['status'] = 'in_vac';
						break;

					case 'in_cnc':
						$part_info['weeks'][$i]['status'] = 'in_cnc';
						break;

					case 'wo_created':
						$part_info['weeks'][$i]['status'] = 'wo_created';
						break;
                    case 'vac_scheduled':
                    case 'cnc_scheduled':
               
                    case 'completed_on_time':
						$part_info['weeks'][$i]['status'] = 'in_stock';
						break;

					case 'completed_late':
						$part_info['weeks'][$i]['status'] = 'late';
						break;
                    default:
                        $part_info['weeks'][$i]['status'] = 'wo_required';
                        $part_info['weeks'][$i]['process_status'] = 'wo_required';
                }
            }
        } else {
            // No requirement this week, but inventory carries forward
            $part_info['weeks'][$i]['inventory_info'] = [
                'available' => 0,
                'remaining' => 0,
                'in_stock' => false
            ];
            $part_info['weeks'][$i]['production_qty'] = 0;
        }
    }
}

// Function to get cell color based on status (updated)
function getCellColor($status) {
    switch ($status) {
        case 'in_stock':
        case 'wo_created':
        case 'ready_for_wo':
        case 'from_inventory':
            return 'background-color: white; color: black;';
        case 'po_required':
        case 'materials_pending':
        case 'late':
            return 'background-color: #ff0000; color: white;';
        case 'po_created':
            return 'background-color: #ffdab9; color: black;';
        case 'in_vac':
            return 'background-color: #ffff00; color: black;';
        case 'in_cnc':
            return 'background-color: #800080; color: white;';
        case 'wo_required':
            return 'background-color: #ffa500; color: black;';
        case 'partial_inventory':
            return 'background-color: #90EE90; color: black;';
        default:
            return 'background-color: #f0f0f0; color: black;';
    }
}

// Function to get status text (updated)
function getStatusText($status) {
    switch ($status) {
        case 'in_stock':
            return 'Stock';
        case 'from_inventory':
            return 'Inventory';
        case 'po_required':
            return 'PO Req';
        case 'po_created':
            return 'PO Done';
        case 'wo_created':
            return 'WO';
        case 'ready_for_wo':
            return 'Ready';
        case 'materials_pending':
            return 'Materials';
        case 'in_vac':
            return 'VAC';
        case 'in_cnc':
            return 'CNC';
        case 'wo_required':
            return 'WO Req';
        case 'late':
            return 'Late';
        case 'partial_inventory':
            return 'Partial';
        default:
            return '';
    }
}
?>

    <style>
        
        table {
            border-collapse: collapse;
            width: 100%;
            margin-top: 20px;
        }
        th, td {
            border: 1px solid #ddd;
            padding: 8px;
            text-align: center;
        }
        th {
            background-color: #4CAF50;
            color: white;
            font-weight: bold;
        }
        .legend {
            margin: 20px 0;
            padding: 10px;
            background-color: #f9f9f9;
            border: 1px solid #ddd;
        }
        .legend-item {
            display: inline-block;
            margin-right: 20px;
            padding: 5px 10px;
            border: 1px solid #ccc;
        }
        .sub-row {
            background-color: #f5f5f5;
            font-size: 0.9em;
        }
        .sub-row td {
            padding: 5px 8px;
        }
        .qty-label {
            font-weight: bold;
            text-align: left;
            padding-left: 20px !important;
        }
        .process-label {
            font-weight: bold;
            text-align: left;
            padding-left: 20px !important;
        }
        .mixed-status {
            background: linear-gradient(to right, #90EE90 50%, #ffa500 50%);
        }
    </style>

    <h1>Production Planner Hybrid Report</h1>
    
    <div class="legend">
        <strong>Legend:</strong>
        <span class="legend-item" style="background-color: white; color: black;">Stock Available</span>
        <span class="legend-item" style="background-color: #ff0000; color: white;">PO Required</span>
        <span class="legend-item" style="background-color: #ffdab9; color: black;">PO Created</span>
        <span class="legend-item" style="background-color: white; color: black;">WO Created</span>
        <span class="legend-item" style="background-color: #ffa500; color: black;">WO Required</span>
        <span class="legend-item" style="background-color: #ffff00; color: black;">Moulded</span>
        <span class="legend-item" style="background-color: #800080; color: white;">CNC Done</span>
        <span class="legend-item" style="background-color: #ff0000; color: white;">Late</span>
        <span class="legend-item" style="background-color: #90EE90; color: black;">Partial Inventory / No Requirement</span>
    </div>
    
    <table>
        <thead>
            <tr>
                <th>Part Number</th>
                <th>Description</th>
                <th>Customer</th>
                <?php for ($i = 0; $i < $weeks_to_show; $i++): ?>
                    <th><?php echo $week_dates[$i]['label']; ?><br>
                        <small><?php echo date('M d', strtotime($week_dates[$i]['start'])); ?> - 
                        <?php echo date('M d', strtotime($week_dates[$i]['end'])); ?></small>
                    </th>
                <?php endfor; ?>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($parts_data as $part_num => $part_info): ?>
                <!-- Main Row -->
                <tr>
                    <td rowspan="3"><?php echo htmlspecialchars($part_num); ?></td>
                    <td rowspan="3"><?php echo htmlspecialchars($part_info['description']); ?></td>
                    <td rowspan="3"><?php echo $gsi->getCustomercodeById($part_info['customer']); ?></td>
                    <?php for ($i = 0; $i < $weeks_to_show; $i++): ?>
                        <?php if ($part_info['weeks'][$i]['qty'] > 0): ?>
                            <?php 
                            $inv = $part_info['weeks'][$i]['inventory_info'];
                            $display_status = $part_info['weeks'][$i]['status'];
                            
                            // If partially from inventory, show mixed status
                            if ($inv['available'] > 0 && $inv['remaining'] > 0) {
                                $cell_style = 'background-color: #90EE90; color: black;';
                            } else {
                                $cell_style = getCellColor($display_status);
                            }
                            ?>
                            <td style="<?php echo $cell_style; ?>">
                                <?php 
                                if ($inv['available'] > 0 && $inv['remaining'] > 0) {
                                    echo 'Mixed';
                                } else {
                                    echo getStatusText($display_status);
                                }
                                ?>
                            </td>
                        <?php else: ?>
                            <td style="background-color: #90EE90; color: black;">0</td>
                        <?php endif; ?>
                    <?php endfor; ?>
                </tr>
                
                <!-- Sub Row 1: Required Quantity -->
                <tr class="sub-row">
                    <?php for ($i = 0; $i < $weeks_to_show; $i++): ?>
                        <?php if ($part_info['weeks'][$i]['qty'] > 0): ?>
                            <td>
                                <strong>Req:</strong> <?php echo $part_info['weeks'][$i]['qty']; ?>
                                <?php $inv = $part_info['weeks'][$i]['inventory_info']; ?>
                                <?php if ($inv['available'] > 0): ?>
                                    <br><span style="color: green;">✓ Inv: <?php echo $inv['available']; ?></span>
                                <?php endif; ?>
                                <?php if ($inv['remaining'] > 0): ?>
                                    <br><span style="color: #0066cc;">⚙ Prod: <?php echo $inv['remaining']; ?></span>
                                <?php endif; ?>
                            </td>
                        <?php else: ?>
                            <td style="background-color: #90EE90; color: black;">0</td>
                        <?php endif; ?>
                    <?php endfor; ?>
                </tr>
                
                <!-- Sub Row 2: Process Status -->
                <tr class="sub-row">
                    <?php for ($i = 0; $i < $weeks_to_show; $i++): ?>
                        <?php if ($part_info['weeks'][$i]['qty'] > 0): ?>
                            <td class="hide">
                                <?php 
                                $inv = $part_info['weeks'][$i]['inventory_info'];
                                if ($inv['in_stock']) {
                                    echo '<span style="color: green; font-weight: bold;">✓ Fully from Inventory</span>';
                                } else if ($inv['available'] > 0 && $inv['remaining'] > 0) {
                                    echo '<span style="color: green;">' . $inv['available'] . ' from Inv</span><br>';
                                    echo '<span style="color: #0066cc;">' . $inv['remaining'] . ' → ' . getStatusText($part_info['weeks'][$i]['status']) . '</span>';
                                } else {
                                    echo '<span style="color: #0066cc;">' . $inv['remaining'] . ' → ' . getStatusText($part_info['weeks'][$i]['status']) . '</span>';
                                }
                                ?>
                            </td>
                        <?php else: ?>
                            <td style="background-color: #90EE90; color: black;">-</td>
                        <?php endif; ?>
                    <?php endfor; ?>
                </tr>
            <?php endforeach; ?>
        </tbody>
    </table>