<?php
require_once('includes/load.php');
page_require_level(3);

// Initialize error message
$error = '';

try {
    // Get month and year from GET parameters, default to current month/year
    $month = isset($_GET['month']) ? intval($_GET['month']) : date('m');
    $year = isset($_GET['year']) ? intval($_GET['year']) : date('Y');
    $search_part = isset($_GET['search_part']) ? $db->escape($_GET['search_part']) : '';

    function generate_pick_list($month, $year, $search_part = '') {
        global $db, $error;

        $pick_list = [];
        $summary = [];

        // Validate month and year
        if ($month < 1 || $month > 12) {
            $error = 'Invalid month selected';
            return ['pick_list' => [], 'summary' => []];
        }

        if ($year < 2000 || $year > (date('Y') + 5)) {
            $error = 'Invalid year selected';
            return ['pick_list' => [], 'summary' => []];
        }

        // Base SQL query for production schedule
        $sql_schedule = "SELECT part_number, quantity_order FROM production_schedule
                        WHERE MONTH(delivery_date) = '{$month}' 
                        AND YEAR(delivery_date) = '{$year}'";
        
        // Add search filter if provided
        if (!empty($search_part)) {
            $sql_schedule .= " AND part_number LIKE '%{$search_part}%'";
        }

        $result_schedule = $db->query($sql_schedule);

        if (!$result_schedule) {
            $error = 'Failed to load production schedule: ' . $db->get_last_error();
            return ['pick_list' => [], 'summary' => []];
        }

        while ($row_schedule = $db->fetch_assoc($result_schedule)) {
            $part_number = $db->escape($row_schedule['part_number']);
            $quantity_ordered = intval($row_schedule['quantity_order']);

            // Get BOM components
            $sql_bom = "SELECT child_bom, qty FROM bom_level WHERE parent_bom = '{$part_number}'";
            $result_bom = $db->query($sql_bom);

            if (!$result_bom) {
                $error = 'Failed to load BOM for part ' . $part_number . ': ' . $db->get_last_error();
                continue;
            }

            while ($row_bom = $db->fetch_assoc($result_bom)) {
                $component = $row_bom['child_bom'];
                $quantity_needed = $row_bom['qty'] * $quantity_ordered;

                // Get component details - REMOVED 'location' from query
                $sql_product = "SELECT new_code, name, uom FROM products WHERE part_number = '{$component}'";
                $result_product = $db->query($sql_product);
                
                if (!$result_product) {
                    $error = 'Failed to load product details for ' . $component . ': ' . $db->get_last_error();
                    continue;
                }

                $product_info = $db->fetch_assoc($result_product);
                if (!$product_info) {
                    $error = 'No product information found for component: ' . $component;
                    continue;
                }

                $pick_list[] = [
                    'parent_part' => $part_number,
                    'quantity_ordered' => $quantity_ordered,
                    'component' => $component,
                    'new_code' => $product_info['new_code'],
                    'name' => $product_info['name'],
                    'uom' => $product_info['uom'],
                    'quantity' => number_format($quantity_needed, 2)
                ];

                // Build summary
                if (!isset($summary[$component])) {
                    $summary[$component] = [
                        'new_code' => $product_info['new_code'],
                        'name' => $product_info['name'],
                        'uom' => $product_info['uom'],
                        'quantity' => 0
                    ];
                }
                $summary[$component]['quantity'] += $quantity_needed;
            }
        }

        return ['pick_list' => $pick_list, 'summary' => $summary];
    }

    // Generate pick list data
    $data = generate_pick_list($month, $year, $search_part);
    $pick_list = $data['pick_list'];
    $summary = $data['summary'];

} catch (Exception $e) {
    $error = 'System error: ' . $e->getMessage();
    $pick_list = [];
    $summary = [];
}

include_once('layouts/header.php');
?>

<div class="row">
    <div class="col-md-12">
        <?php if (!empty($error)): ?>
            <div class="alert alert-danger">
                <button type="button" class="close" data-dismiss="alert" aria-hidden="true">&times;</button>
                <strong>Error:</strong> <?php echo $error; ?>
            </div>
        <?php endif; ?>

        <!-- Filter Form -->
        <div class="panel panel-default">
            <div class="panel-heading">
                <strong>
                    <span class="glyphicon glyphicon-filter"></span>
                    <span>Filter Pick List</span>
                </strong>
            </div>
            <div class="panel-body">
                <form method="get" class="form-horizontal">
                    <div class="form-group">
                        <label class="control-label col-md-3">Month:</label>
                        <div class="col-md-3">
                            <select name="month" class="form-control">
                                <?php for ($m = 1; $m <= 12; $m++) : ?>
                                    <option value="<?= $m; ?>" <?= ($month == $m) ? 'selected' : ''; ?>>
                                        <?= date('F', mktime(0, 0, 0, $m, 1)); ?>
                                    </option>
                                <?php endfor; ?>
                            </select>
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="control-label col-md-3">Year:</label>
                        <div class="col-md-3">
                            <select name="year" class="form-control">
                                <?php for ($y = date('Y'); $y <= date('Y') + 2; $y++) : ?>
                                    <option value="<?= $y; ?>" <?= ($year == $y) ? 'selected' : ''; ?>>
                                        <?= $y; ?>
                                    </option>
                                <?php endfor; ?>
                            </select>
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="control-label col-md-3">Search Part:</label>
                        <div class="col-md-3">
                            <input type="text" name="search_part" class="form-control" 
                                   value="<?= remove_junk($search_part); ?>" placeholder="Part number...">
                        </div>
                    </div>
                    <div class="form-group">
                        <div class="col-md-offset-3 col-md-9">
                            <button type="submit" class="btn btn-primary">Filter</button>
                            <a href="set_list.php" class="btn btn-default">Reset</a>
                        </div>
                    </div>
                </form>
            </div>
        </div>

        <!-- Printable Pick List -->
        <div class="panel panel-info printable-area">
            <div class="panel-heading clearfix">
                <div class="pull-left">
                    <span class="glyphicon glyphicon-list-alt"></span>
                    <span>Warehouse Pick List</span>
                </div>
                <div class="pull-right">
                    <button id="print-btn" class="btn btn-primary btn-sm">
                        <span class="glyphicon glyphicon-print"></span> Print
                    </button>
                </div>
            </div>
            <div class="panel-body">
                <?php if (empty($summary) && empty($error)): ?>
                    <div class="alert alert-warning">
                        No materials found for the selected criteria. Please try different filters.
                    </div>
                <?php else: ?>
                    <div class="text-center header-info">
                        <h3>MATERIAL PICK LIST</h3>
                        <p>For Production: <?= date('F Y', mktime(0, 0, 0, $month, 1, $year)); ?></p>
                        <p>Generated on: <?= date('d/m/Y H:i:s'); ?></p>
                    </div>

                    <!-- Consolidated Pick List -->
                    <table class="table table-bordered table-condensed">
                        <thead>
                            <tr class="active">
                                <th>#</th>
                                <th>Component</th>
                                <th>New Code</th>
                                <th>Description</th>
                                <th>UOM</th>
                                <th>Total Qty</th>
                                <th>Picked Qty</th>
                                <th>Checker</th>
                                <th>Notes</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!empty($summary)) : ?>
                                <?php $counter = 1; ?>
                                <?php foreach ($summary as $component => $item) : ?>
                                    <tr>
                                        <td><?= $counter++; ?></td>
                                        <td><?= remove_junk($component); ?></td>
                                        <td><?= remove_junk($item['new_code']); ?></td>
                                        <td><?= remove_junk($item['name']); ?></td>
                                        <td><?= remove_junk($item['uom']); ?></td>
                                        <td class="text-right"><?= number_format($item['quantity'], 2); ?></td>
                                        <td class="picked-cell"></td>
                                        <td class="checker-cell"></td>
                                        <td class="notes-cell"></td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php endif; ?>
                        </tbody>
                    </table>

                    <!-- Signature Section -->
                    <div class="row signature-section" style="margin-top: 50px;">
                        <div class="col-xs-4 text-center">
                            <p>Prepared by:</p>
                            <p style="margin-top: 50px;">_________________________</p>
                        </div>
                        <div class="col-xs-4 text-center">
                            <p>Picked by:</p>
                            <p style="margin-top: 50px;">_________________________</p>
                        </div>
                        <div class="col-xs-4 text-center">
                            <p>Verified by:</p>
                            <p style="margin-top: 50px;">_________________________</p>
                        </div>
                    </div>
                <?php endif; ?>
            </div>
        </div>

        <!-- Detailed Requirements (for reference) -->
        <div class="panel panel-default">
            <div class="panel-heading">
                <span class="glyphicon glyphicon-list"></span>
                <span>Detailed Material Requirements</span>
            </div>
            <div class="panel-body">
                <?php if (empty($pick_list) && empty($error)): ?>
                    <div class="alert alert-warning">
                        No detailed material requirements found for the selected criteria.
                    </div>
                <?php else: ?>
                    <table class="table table-bordered table-striped">
                        <thead>
                            <tr>
                                <th>Parent Part</th>
                                <th>Order Qty</th>
                                <th>Component</th>
                                <th>New Code</th>
                                <th>Description</th>
                                <th>UOM</th>
                                <th>Req Qty</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!empty($pick_list)) : ?>
                                <?php foreach ($pick_list as $item) : ?>
                                    <tr>
                                        <td><?= remove_junk($item['parent_part']); ?></td>
                                        <td class="text-right"><?= $item['quantity_ordered']; ?></td>
                                        <td><?= remove_junk($item['component']); ?></td>
                                        <td><?= remove_junk($item['new_code']); ?></td>
                                        <td><?= remove_junk($item['name']); ?></td>
                                        <td><?= remove_junk($item['uom']); ?></td>
                                        <td class="text-right"><?= $item['quantity']; ?></td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php endif; ?>
                        </tbody>
                    </table>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

<script>
$(document).ready(function() {
    // Print functionality
    $('#print-btn').click(function() {
        var printContents = $('.printable-area').html();
        var originalContents = document.body.innerHTML;
        
        document.body.innerHTML = printContents;
        window.print();
        document.body.innerHTML = originalContents;
        window.location.reload();
    });

    // Allow manual entry in picked quantity cells
    $(document).on('click', '.picked-cell, .checker-cell, .notes-cell', function() {
        var currentText = $(this).text().trim();
        var input = $('<input>', {
            type: 'text',
            val: currentText,
            class: 'form-control input-sm'
        });
        
        $(this).html(input);
        input.focus();
        
        input.blur(function() {
            var newText = $(this).val();
            $(this).parent().text(newText);
        });
    });
});
</script>

<style>
@media print {
    body * {
        visibility: hidden;
    }
    .printable-area, .printable-area * {
        visibility: visible;
    }
    .printable-area {
        position: absolute;
        left: 0;
        top: 0;
        width: 100%;
    }
    .header-info {
        margin-bottom: 20px;
    }
    .signature-section {
        page-break-inside: avoid;
    }
    .table {
        font-size: 12px;
    }
    .panel-heading {
        display: none;
    }
}
</style>

<?php include_once('layouts/footer.php'); ?>