<?php
$page_title = 'Production Inventory';
require_once('includes/load.php');
page_require_level(3);

if (isset($_POST['submit'])) {
    $product_id = (int)$_POST['product_id'];
    $part_number = $_POST['part_number'];
    $new_code = $_POST['new_code'];
    $name = $_POST['name'];
    $quantity = (float)$_POST['quantity']; // Float for decimal handling
    $uom = $_POST['uom'];
    $categorie_id = (int)$_POST['categorie_id'];

    $sql = "INSERT INTO prod_loc (product_id, part_number, new_code, name, quantity, uom, categorie_id)
            VALUES (?, ?, ?, ?, ?, ?, ?)";
    if ($stmt = $db->prepare($sql)) {
        $stmt->bind_param('isssdsi', $product_id, $part_number, $new_code, $name, $quantity, $uom, $categorie_id);
        if ($stmt->execute()) {
            $session->msg('s', 'Product location added successfully!');
            redirect('prod_loc.php', false);
        } else {
            $session->msg('d', 'Failed to add product location.');
            redirect('prod_loc.php', false);
        }
    }
}

if (isset($_GET['delete'])) {
    $id = (int)$_GET['delete'];
    $sql = "DELETE FROM prod_loc WHERE id = ?";
    if ($stmt = $db->prepare($sql)) {
        $stmt->bind_param('i', $id);
        if ($stmt->execute()) {
            $session->msg('s', 'Product location deleted!');
            redirect('prod_loc.php', false);
        } else {
            $session->msg('d', 'Failed to delete product location.');
            redirect('prod_loc.php', false);
        }
    }
}

// Ensure distinct records by adding DISTINCT keyword
$sql = "SELECT DISTINCT pl.*, c.name AS category_name, p.quantity AS product_quantity, 
        COALESCE(w.quantity, 0) AS WIP_quantity
        FROM prod_loc pl
        LEFT JOIN categories c ON pl.categorie_id = c.id
        LEFT JOIN products p ON pl.product_id = p.id
        LEFT JOIN WIP w ON pl.part_number = w.part_number";

// Apply category filter if set
if (isset($_POST['categorie_id']) && !empty($_POST['categorie_id'])) {
    $categorie_id = (int)$_POST['categorie_id'];
    $sql .= " WHERE pl.categorie_id = $categorie_id";
}

$product_locations = $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 clearfix">
                <div class="pull-right">
                    <a href="data_consume.php" class="btn btn-success">
                        <i class="glyphicon glyphicon-list-alt"></i> List TF WIP
                    </a>
                    <a href="list_consume_production.php" class="btn btn-success">
                        <i class="glyphicon glyphicon-plus"></i> Transfer WIP
                    </a>
                    <a href="list_transfer_prod_loc.php" class="btn btn-success">
                        <i class="glyphicon glyphicon-list-alt"></i> List Transfer
                    </a>
                    <a href="list_reject_prod_loc.php" class="btn btn-success">
                        <i class="glyphicon glyphicon-list-alt"></i> List Reject
                    </a>
                    <a href="reject_prod_loc.php" class="btn btn-success">
                        <i class="glyphicon glyphicon-plus"></i> Add Reject
                    </a>
                    <a href="add_prod_loc.php" class="btn btn-success">
                        <i class="glyphicon glyphicon-plus"></i> Add Transfer
                    </a>
                    <button type="button" class="btn btn-success" id="exportExcelBtn">Export Excel</button>
                    <script>
                        document.getElementById('exportExcelBtn').addEventListener('click', function() {
                            var wb = XLSX.utils.table_to_book(document.querySelector('.table'));
                            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'
                            }), 'product_locations.xlsx');
                        });
                    </script>
                </div>
                <div class="panel-title">
                    <h4>Production Inventory</h4>
                </div>
            </div>
            <div class="panel-body">
                <form method="post" action="prod_loc.php" class="form-inline mb-2">
                    <div class="form-group">
                        <label for="category_filter">Filter by Category:</label>
                        <select class="form-control" id="category_filter" name="categorie_id">
                            <option value="">All Categories</option>
                            <?php
                            $categories = find_all('categories');
                            foreach ($categories as $category) :
                            ?>
                                <option value="<?php echo (int)$category['id']; ?>"><?php echo htmlspecialchars($category['name']); ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <button type="submit" class="btn btn-primary ml-2">Filter</button>
                </form>
                <table class="table table-bordered table-striped">
                    <thead>
                        <tr>
                            <th class="text-center">#</th>
                            <th>Part Number</th>
                            <th>New Code</th>
                            <th>Name</th>
                            <th>WIP Quantity</th>
                            <th>Prod. WH</th>
                            <th>Warehouse</th>
                            <th>UOM</th>
                            <th>Category</th>
                            <th class="text-center">Actions</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($product_locations as $product_loc) : ?>
                            <tr>
                                <td class="text-center"><?php echo $product_loc['id']; ?></td>
                                <td><?php echo htmlspecialchars($product_loc['part_number']); ?></td>
                                <td><?php echo htmlspecialchars($product_loc['new_code']); ?></td>
                                <td><?php echo htmlspecialchars($product_loc['name']); ?></td>
                                <td><?php echo $product_loc['WIP_quantity']; ?></td>
                                <td><?php echo $product_loc['quantity']; ?></td>
                                <td><?php echo $product_loc['product_quantity']; ?></td>
                                <td><?php echo htmlspecialchars($product_loc['uom']); ?></td>
                                <td><?php echo htmlspecialchars($product_loc['category_name']); ?></td>
                                <td class="text-center">
                                    <div class="btn-group">
                                        <a href="edit_prod_loc.php?id=<?php echo (int)$product_loc['id']; ?>" class="btn btn-xs btn-warning"
                                            data-toggle="tooltip" title="Edit">
                                            <i class="glyphicon glyphicon-edit"></i>
                                        </a>
                                        <a href="prod_loc.php?delete=<?php echo (int)$product_loc['id']; ?>" class="btn btn-xs btn-danger"
                                            data-toggle="tooltip" title="Delete">
                                            <i class="glyphicon glyphicon-trash"></i>
                                        </a>
                                    </div>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

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