<?php
$page_title = 'MRP Baru (Tersumif)';
require_once('includes/load.php');
page_require_level(3);

// Step 1: Delete existing data from the mrp_baru table
$deleteMrpBaruQuery = "DELETE FROM mrp_baru";
$deleteMrpBaruResult = $db->query($deleteMrpBaruQuery);

if ($deleteMrpBaruResult) {
    echo "Data existing MRP Baru has been deleted successfully.<br>";
} else {
    echo "Error deleting existing MRP Baru data: " . $db->error . "<br>";
}

// Step 2: Insert new data into the mrp_baru table
$insertMrpBaruQuery = "
    INSERT INTO mrp_baru (parent_bom, part_number, phantom_bom, level, total_requirement, quantity_order, calculate, no_order, calculation_date)
    SELECT
        ps.part_number AS parent_bom,
        bl.child_bom AS part_number,
        bl.phantom_bom,
        bl.level,
        (bl.qty * ps.quantity_order) AS total_requirement,
        ps.quantity_order,
        (bl.qty * ps.quantity_order) AS calculate,
        ps.no_order,
        CURDATE()
    FROM production_schedule ps
    JOIN bom_level bl ON ps.part_number = bl.parent_bom
    WHERE NOT EXISTS (
        SELECT 1
        FROM mrp_baru mb
        WHERE mb.parent_bom = ps.part_number
    );
";

$insertMrpBaruResult = $db->query($insertMrpBaruQuery);

if ($insertMrpBaruResult) {
    echo "New MRP Baru data has been inserted successfully.<br>";

    // Query to update no_order column in mrp_baru table to match production_schedule
    $updateNoOrderQuery = "
        UPDATE mrp_baru mb
        JOIN production_schedule ps ON mb.parent_bom = ps.part_number
        SET mb.no_order = ps.no_order
    ";

    $updateNoOrderResult = $db->query($updateNoOrderQuery);

    if ($updateNoOrderResult) {
        echo "Data MRP Baru has been updated successfully with the correct no_order.<br>";
    } else {
        echo "Error updating no_order column in MRP Baru table: " . $db->error . "<br>";
    }
} else {
    echo "Error inserting new MRP Baru data: " . $db->error . "<br>";
}

// SQL query to retrieve data from mrp_baru and join with products and prod_loc tables
$mrpBaruQuery = "
    SELECT
        mb.part_number,
        p.name AS product_name,
        SUM(mb.total_requirement) AS total_requirement,
        (COALESCE(pl.quantity, 0) + COALESCE(p.quantity, 0)) AS stock,
        p.uom
    FROM mrp_baru mb
    LEFT JOIN products p ON mb.part_number = p.part_number
    LEFT JOIN prod_loc pl ON mb.part_number = pl.part_number
    WHERE calculation_date = CURDATE()
    GROUP BY mb.part_number, p.name, p.uom, pl.quantity, p.quantity
    ORDER BY mb.part_number
";
$mrpBaruResult = $db->query($mrpBaruQuery);

// SQL query to get items that need to be purchased
$itemsToPurchaseQuery = "
    SELECT
        mb.part_number,
        p.name AS product_name,
        (COALESCE(pl.quantity, 0) + COALESCE(p.quantity, 0)) AS stock,
        SUM(mb.calculate) AS calculate,
        (SUM(mb.calculate) - (COALESCE(pl.quantity, 0) + COALESCE(p.quantity, 0))) AS shortage
    FROM mrp_baru mb
    LEFT JOIN products p ON mb.part_number = p.part_number
    LEFT JOIN prod_loc pl ON mb.part_number = pl.part_number
    WHERE calculation_date = CURDATE()
    GROUP BY mb.part_number, p.name, pl.quantity, p.quantity
    HAVING shortage > 0
    ORDER BY mb.part_number
";
$itemsToPurchaseResult = $db->query($itemsToPurchaseQuery);

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

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading">
                <h3 class="panel-title">MRP Baru (Tersumif)</h3>
            </div>
            <div class="panel-body">
                <p>Calculation Date: <?php echo date('Y-m-d'); ?></p>
            </div>
        </div>
    </div>
</div>

<?php if ($db->num_rows($mrpBaruResult) > 0) : ?>
    <div class="row">
        <div class="col-md-12">
            <div class="panel panel-default">
                <div class="panel-heading">
                    <h3 class="panel-title">MRP Baru Data (Tersumif)</h3>
                </div>
                <div class="panel-body">
                    <table class="table table-bordered table-hover">
                        <thead>
                            <tr>
                                <th>Part Number</th>
                                <th>Product Name</th>
                                <th>Total Requirement</th>
                                <th>Stock WH & Prod</th>
                                <th>UoM</th>
                                <th>Action</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php while ($row = $db->fetch_assoc($mrpBaruResult)) : ?>
                                <tr>
                                    <td><?php echo htmlspecialchars($row['part_number']); ?></td>
                                    <td><?php echo htmlspecialchars($row['product_name']); ?></td>
                                    <td><?php echo number_format($row['total_requirement'], 0, '.', ','); ?></td>
                                    <td><?php echo number_format($row['stock'], 0, '.', ','); ?></td>
                                    <td><?php echo htmlspecialchars($row['uom']); ?></td>
                                    <td><button class="btn btn-info btn-sm view-detail" data-part-number="<?php echo htmlspecialchars($row['part_number']); ?>">View Detail</button></td>
                                </tr>
                            <?php endwhile; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <div class="row">
        <div class="col-md-12">
            <div class="panel panel-default">
                <div class="panel-heading">
                    <h3 class="panel-title">Items to Purchase</h3>
                </div>
                <div class="panel-body">
                    <table class="table table-bordered table-hover">
                        <thead>
                            <tr>
                                <th>Part Number</th>
                                <th>Product Name</th>
                                <th>Current Stock</th>
                                <th>Quantity to Purchase</th>
                                <th>Action</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php while ($item = $db->fetch_assoc($itemsToPurchaseResult)) : ?>
                                <tr>
                                    <td><?php echo htmlspecialchars($item['part_number']); ?></td>
                                    <td><?php echo htmlspecialchars($item['product_name']); ?></td>
                                    <td><?php echo number_format($item['stock'], 0, '.', ','); ?></td>
                                    <td><?php echo number_format($item['shortage'], 0, '.', ','); ?></td>
                                    <td><button class="btn btn-success btn-sm create-po" data-part-number="<?php echo htmlspecialchars($item['part_number']); ?>">Create PO</button></td>
                                </tr>
                            <?php endwhile; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
<?php else : ?>
    <div class="alert alert-warning">
        Tidak ada data MRP Baru tersedia.
    </div>
<?php endif; ?>

<div id="detailModal" class="modal fade" role="dialog">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal">&times;</button>
                <h4 class="modal-title">Detail MRP Baru</h4>
            </div>
            <div class="modal-body">
                <div id="mrp-detail"></div>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
            </div>
        </div>
    </div>
</div>

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script> <!-- Include jQuery -->
<script type="text/javascript">
    $(document).ready(function() {
        $('.view-detail').click(function() {
            var part_number = $(this).data('part-number');
            $.ajax({
                url: 'fetch_mrp_detail.php',
                type: 'GET',
                data: { part_number: part_number },
                dataType: 'json',
                success: function(response) {
                    if (response.error) {
                        alert(response.error);
                    } else {
                        $('#mrp-detail').html('<p><strong>Parent BOM:</strong> ' + response.parent_bom + '</p><p><strong>Schedule Date:</strong> ' + response.schedule_date + '</p>');
                        $('#detailModal').modal('show');
                    }
                },
                error: function(xhr, status, error) {
                    alert('Error fetching MRP detail: ' + error);
                }
            });
        });

        $('.create-po').click(function() {
            var part_number = $(this).data('part-number');
            // You can add your logic to create a PO here, e.g., redirect to a create PO page or show a form
            alert('Create PO functionality for part number: ' + part_number);
        });
    });
</script>

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