<?php
$page_title = 'Update Unit Prices';
require_once('includes/load.php');
page_require_level(3);

// Handle form submission
if (isset($_POST['update_price'])) {
    $product_id = (int)$_POST['product_id'];
    $new_price = (float)$_POST['new_price'];

    // Validate input
    if (empty($new_price)) {
        $session->msg("d", "Missing price.");
        redirect('unit_price_update.php', false);
    }

    // Set updated_at to today's date
    $updated_at = date('Y-m-d');

    // Update product price
    $sql = "UPDATE products SET buy_price = '{$new_price}' WHERE id = '{$product_id}'";
    $result = $db->query($sql);

    // Insert into unit_price_update table
    if ($result) {
        $sql = "INSERT INTO unit_price_update (product_id, new_price, updated_at) VALUES ('{$product_id}', '{$new_price}', '{$updated_at}')";
        $db->query($sql);
        $session->msg("s", "Price updated successfully.");
    } else {
        $session->msg("d", "Update failed.");
    }
}

// Fetch products with category_id 1 and 4
$sql = "SELECT p.id, p.part_number, p.name, p.buy_price, p.uom, p.new_code,
        (SELECT u.updated_at FROM unit_price_update u WHERE u.product_id = p.id ORDER BY u.updated_at DESC LIMIT 1) AS last_update,
        (SELECT u.new_price FROM unit_price_update u WHERE u.product_id = p.id ORDER BY u.updated_at DESC LIMIT 1) AS last_new_price
        FROM products p
        WHERE p.categorie_id IN (1, 4)";
$products = $db->query($sql);
?>

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

<div class="row">
    <div class="col-md-12">
        <?php echo display_msg($msg); ?>
    </div>
</div>

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading">
                <h3>Update Unit Prices</h3>
                <!-- Export Excel Button -->
                <button type="button" class="btn btn-success" id="exportExcelBtn">Export to Excel</button>
            </div>
            <div class="panel-body">
                <div class="table-responsive">
                    <table class="table table-bordered" id="unitPriceTable">
                        <thead>
                            <tr>
                                <th class="text-center">#</th>
                                <th>Part Number</th>
                                <th>Product</th>
                                <th>UOM</th>
                                <th>New Code</th>
                                <th>Current Price</th>
                                <th>New Price</th>
                                <th>Last Update</th>
                                <th>Action</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if ($products->num_rows > 0): ?>
                                <?php foreach ($products as $product) : ?>
                                    <tr>
                                        <td class="text-center"><?php echo (int)$product['id']; ?></td>
                                        <td><?php echo htmlspecialchars($product['part_number']); ?></td>
                                        <td><?php echo htmlspecialchars($product['name']); ?></td>
                                        <td><?php echo htmlspecialchars($product['uom']); ?></td>
                                        <td><?php echo htmlspecialchars($product['new_code']); ?></td>
                                        <td><?php echo number_format($product['buy_price'], 2); ?></td>
                                        <td>
                                            <form method="post" action="unit_price_update.php">
                                                <input type="hidden" name="product_id" value="<?php echo (int)$product['id']; ?>">
                                                <input type="number" step="0.01" class="form-control" name="new_price" placeholder="New Price"
                                                    required>
                                        </td>
                                        <td>
                                            <?php
                                            if (!empty($product['last_update'])) {
                                                echo htmlspecialchars($product['last_new_price']) . " (Updated on: " . htmlspecialchars($product['last_update']) . ")";
                                            } else {
                                                echo "Belum diperbarui";
                                            }
                                            ?>
                                        </td>
                                        <td>
                                            <input type="hidden" name="updated_at" value="<?php echo date('Y-m-d'); ?>">
                                            <button type="submit" name="update_price" class="btn btn-primary">Update</button>
                                            </form>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php else: ?>
                                <tr>
                                    <td colspan="9" class="text-center">No products available for update.</td>
                                </tr>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<?php include_once('layouts/footer.php'); ?>
<script>
    document.getElementById('exportExcelBtn').addEventListener('click', function() {
        var wb = XLSX.utils.table_to_book(document.getElementById('unitPriceTable'));
        var wbout = XLSX.write(wb, {
            bookType: 'xlsx',
            bookSST: true,
            type: 'binary'
        });

        function s2ab(s) {
            var buf = new ArrayBuffer(s.length);
            var view = new Uint8Array(buf);
            for (var i = 0; i < s.length; i++) view[i] = s.charCodeAt(i) & 0xFF;
            return buf;
        }

        saveAs(new Blob([s2ab(wbout)], {
            type: 'application/octet-stream'
        }), 'unit_price_update.xlsx');
    });
</script>