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

// Fungsi untuk menghitung Safety Stock
function calculate_safety_stock($total_sales) {
    return $total_sales; // Safety Stock diambil dari total penjualan selama satu bulan penuh
}

// Mendapatkan bulan dan tahun yang dipilih dari form atau default ke bulan ini
$selected_month = isset($_POST['month']) ? (int)$_POST['month'] : date('m');
$selected_year = isset($_POST['year']) ? (int)$_POST['year'] : date('Y');

// Mengatur bulan sebelumnya untuk menghitung safety stock
$previous_month = $selected_month - 1;
$previous_year = $selected_year;
if ($previous_month == 0) {
    $previous_month = 12;
    $previous_year--;
}

// Mengatur tanggal awal dan akhir bulan sebelumnya
$start_date = date("$previous_year-$previous_month-01");
$end_date = date("Y-m-t", strtotime($start_date)); // Mendapatkan tanggal akhir bulan sebelumnya

// Mendapatkan data untuk bulan sebelumnya
$filter_month = str_pad($previous_month, 2, '0', STR_PAD_LEFT);
$filter_year = $previous_year;

// Mengambil data part_number dengan categorie_id = 7
$sql_parts = "SELECT part_number, name, quantity 
              FROM products 
              WHERE categorie_id = 7";
$products = find_by_sql($sql_parts);

// Array untuk menyimpan Safety Stock
$safety_stock_data = [];
$replenishment_data = [];

// Mengambil dan menghitung Safety Stock
foreach ($products as $product) {
    $part_number = $product['part_number'];
    $product_name = $product['name'];
    $current_stock = $product['quantity'];
    
    $sql_sales = "SELECT SUM(s.qty) AS total_qty
                  FROM sales s
                  WHERE s.part_number = '$part_number'
                  AND s.date BETWEEN '$start_date' AND '$end_date'";
    $sales_result = find_by_sql($sql_sales);
    $total_sales = !empty($sales_result) ? $sales_result[0]['total_qty'] : 0;
    
    $safety_stock = calculate_safety_stock($total_sales);
    $stock_needed = $safety_stock - $current_stock;

    $safety_stock_data[] = [
        'part_number' => $part_number,
        'product_name' => $product_name,
        'current_stock' => $current_stock,
        'safety_stock' => $safety_stock,
        'stock_needed' => $stock_needed
    ];

    if ($stock_needed > 0) {
        $replenishment_data[] = [
            'part_number' => $part_number,
            'product_name' => $product_name,
            'stock_needed' => $stock_needed
        ];
    }
}

// Hitung total Safety Stock dan total stok yang dibutuhkan
$total_safety_stock = array_sum(array_column($safety_stock_data, 'safety_stock'));
$total_stock_needed = array_sum(array_column($replenishment_data, 'stock_needed'));

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

<div class="container mt-4">
    <h2 class="mb-4">Kebutuhan Stok Berdasarkan Bulan</h2>
    
    <!-- Form untuk memilih bulan dan tahun -->
    <form method="POST" action="">
        <div class="form-row mb-3">
            <div class="col">
                <select name="month" class="form-control">
                    <?php
                    for ($i = 1; $i <= 12; $i++) {
                        $month = str_pad($i, 2, '0', STR_PAD_LEFT);
                        $selected = ($month == $filter_month) ? 'selected' : '';
                        echo "<option value='$i' $selected>" . date("F", strtotime("2000-$month-01")) . "</option>";
                    }
                    ?>
                </select>
            </div>
            <div class="col">
                <input type="number" name="year" class="form-control" value="<?php echo htmlspecialchars($filter_year); ?>" min="2000" max="2100">
            </div>
            <div class="col">
                <button type="submit" class="btn btn-primary">Filter</button>
            </div>
        </div>
    </form>

    <table class="table table-striped table-bordered">
        <thead class="thead-dark">
            <tr>
                <th>Part Number</th>
                <th>Product Name</th>
                <th>Current Stock</th>
                <th>Safety Stock</th>
                <th>Stock Needed</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($safety_stock_data as $item): ?>
                <tr>
                    <td><?php echo htmlspecialchars($item['part_number']); ?></td>
                    <td><?php echo htmlspecialchars($item['product_name']); ?></td>
                    <td class="text-right"><?php echo number_format($item['current_stock'], 2); ?></td>
                    <td class="text-right"><?php echo number_format($item['safety_stock'], 2); ?></td>
                    <td class="text-right"><?php echo number_format($item['stock_needed'], 2); ?></td>
                </tr>
            <?php endforeach; ?>
        </tbody>
        <tfoot class="thead-dark">
            <tr>
                <th colspan="4">Total Stock Needed</th>
                <th class="text-right"><?php echo number_format($total_stock_needed, 2); ?></th>
            </tr>
        </tfoot>
    </table>

    <?php if (!empty($replenishment_data)): ?>
        <div class="mt-4">
            <h3>Rekomendasi Pembelian untuk Bulan Terpilih</h3>
            <table class="table table-striped table-bordered">
                <thead class="thead-dark">
                    <tr>
                        <th>Part Number</th>
                        <th>Product Name</th>
                        <th>Stock Needed</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($replenishment_data as $item): ?>
                        <tr>
                            <td><?php echo htmlspecialchars($item['part_number']); ?></td>
                            <td><?php echo htmlspecialchars($item['product_name']); ?></td>
                            <td class="text-right"><?php echo number_format($item['stock_needed'], 2); ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    <?php endif; ?>
</div>

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