<?php

ini_set('display_errors', 1);
error_reporting(E_ALL);
require_once('includes/load.php');
page_require_level(3);

// Ambil parameter bulan dan tahun (default: bulan/tahun saat ini)
$month = isset($_GET['month']) ? intval($_GET['month']) : date('m');
$year = isset($_GET['year']) ? intval($_GET['year']) : date('Y');

// Handle simpan data ke database
if(isset($_POST['save_forecast'])) {
    $data = calculate_material_forecasting($month, $year);
    $forecastings = $data['forecastings'];
    
    // Hapus data lama untuk bulan/tahun ini
    $db->query("DELETE FROM material_forecasts WHERE forecast_month = '{$month}' AND forecast_year = '{$year}'");
    
    // Simpan data baru
    foreach($forecastings as $forecast) {
        $part_number = $db->escape($forecast['part_number']);
        $quantity_ordered = $db->escape($forecast['quantity_ordered']);
        $component = $db->escape($forecast['component']);
        $name = $db->escape($forecast['name']);
        $uom = $db->escape($forecast['uom']);
        $quantity_needed = $db->escape($forecast['quantity']);
        $picking_no = $db->escape($forecast['picking_no']);
        $no_order = $db->escape($forecast['no_order']);
        $schedule_date = $db->escape($forecast['schedule_date']);
        $client = $db->escape($forecast['client']);
        
        $sql = "INSERT INTO material_forecasts (
            forecast_month, forecast_year, 
            part_number, quantity_ordered, 
            component, name, uom, 
            quantity_needed, picking_no, no_order, schedule_date, client
        ) VALUES (
            '{$month}', '{$year}',
            '{$part_number}', '{$quantity_ordered}',
            '{$component}', '{$name}', '{$uom}', 
            '{$quantity_needed}', '{$picking_no}', '{$no_order}', '{$schedule_date}', '{$client}'
        )";
        
        $db->query($sql);
    }
    
    $session->msg('s', "Forecast data saved for {$month}/{$year}");
    redirect('mrp_forecasting.php?month='.$month.'&year='.$year, false);
}

// Fungsi hitung kebutuhan material
function calculate_material_forecasting($month, $year) {
    global $db;

    $forecastings = [];
    $summary = [];

    // Ambil data production_schedule (termasuk delivery_date dan client)
    $sql_schedule = "SELECT part_number, quantity_order, no_order, delivery_date, client FROM production_schedule
                     WHERE MONTH(delivery_date) = '{$month}' AND YEAR(delivery_date) = '{$year}'";
    $result_schedule = $db->query($sql_schedule);

    if (!$result_schedule) return ['forecastings' => [], 'summary' => []];

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

        // Generate picking_no: PICK + no_order
        $picking_no = 'PICK-' . $no_order;

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

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

                // Ambil info produk termasuk buy_price
                $sql_product = "SELECT name, uom, buy_price FROM products WHERE part_number = '{$component}'";
                $result_product = $db->query($sql_product);
                $product_info = $db->fetch_assoc($result_product);

                // Simpan data forecast
                $forecastings[] = [
                    'part_number' => $part_number,
                    'quantity_ordered' => $quantity_ordered,
                    'component' => $component,
                    'name' => $product_info['name'],
                    'uom' => $product_info['uom'],
                    'quantity' => $quantity_needed,
                    'picking_no' => $picking_no,
                    'no_order' => $no_order,
                    'schedule_date' => $schedule_date,
                    'client' => $client
                ];

                // Buat summary
                if (!isset($summary[$component])) {
                    $summary[$component] = [
                        'name' => $product_info['name'],
                        'uom' => $product_info['uom'],
                        'buy_price' => $product_info['buy_price'] ? floatval($product_info['buy_price']) : 0,
                        'quantity' => 0,
                        'amount' => 0
                    ];
                }
                $summary[$component]['quantity'] += $quantity_needed;
                $summary[$component]['amount'] += $quantity_needed * $summary[$component]['buy_price'];
            }
        }
    }

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

// Hitung forecast
$data = calculate_material_forecasting($month, $year);
$forecastings = $data['forecastings'];
$summary = $data['summary'];

// Hitung total amount keseluruhan
$total_amount = 0;
foreach ($summary as $item) {
    $total_amount += $item['amount'];
}

// Cek apakah data sudah ada di database
$existing_data = $db->query("SELECT COUNT(*) as total FROM material_forecasts WHERE forecast_month = '{$month}' AND forecast_year = '{$year}'");
$existing_count = $db->fetch_assoc($existing_data)['total'];

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

<div class="row">
    <div class="col-md-12">
        <form action="mrp_forecasting.php" method="get" class="form-inline">
            <div class="form-group">
                <label for="month">Month:</label>
                <select name="month" id="month" class="form-control">
                    <?php for ($m = 1; $m <= 12; $m++) : ?>
                        <option value="<?php echo $m; ?>" <?php echo ($month == $m) ? 'selected' : ''; ?>>
                            <?php echo date('F', mktime(0, 0, 0, $m, 1)); ?>
                        </option>
                    <?php endfor; ?>
                </select>
            </div>
            <div class="form-group">
                <label for="year">Year:</label>
                <select name="year" id="year" class="form-control">
                    <?php for ($y = date('Y') + 2; $y >= 2000; $y--) : ?>
                        <option value="<?php echo $y; ?>" <?php echo ($year == $y) ? 'selected' : ''; ?>>
                            <?php echo $y; ?>
                        </option>
                    <?php endfor; ?>
                </select>
            </div>
            <button type="submit" class="btn btn-primary">Filter</button>
        </form>
    </div>
</div>

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-info">
            <div class="panel-heading">Material Forecasting Summary</div>
            <div class="panel-body">
                <div class="btn-group mb-3">
                    <button id="export-summary" class="btn btn-success">Export Summary to Excel</button>
                    <form method="post" style="display: inline;">
                        <button type="submit" name="save_forecast" class="btn btn-primary" <?php echo empty($forecastings) ? 'disabled' : ''; ?>>
                            <?php echo ($existing_count > 0) ? 'Update Forecast Data' : 'Save Forecast Data'; ?>
                        </button>
                        <input type="hidden" name="month" value="<?php echo $month; ?>">
                        <input type="hidden" name="year" value="<?php echo $year; ?>">
                    </form>
                </div>
                
                <div class="alert alert-info">
                    <strong>Total Forecast Amount:</strong> <?php echo number_format($total_amount, 2); ?>
                </div>
                
                <table id="summary-table" class="table table-bordered table-striped">
                    <thead>
                        <tr>
                            <th>Component</th>
                            <th>Name</th>
                            <th>UOM</th>
                            <th>Buy Price</th>
                            <th>Total Quantity Needed</th>
                            <th>Total Amount</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (!empty($summary)) : ?>
                            <?php foreach ($summary as $component => $details) : ?>
                                <tr>
                                    <td><?php echo remove_junk($component); ?></td>
                                    <td><?php echo remove_junk($details['name']); ?></td>
                                    <td><?php echo remove_junk($details['uom']); ?></td>
                                    <td><?php echo number_format($details['buy_price'], 2); ?></td>
                                    <td><?php echo number_format($details['quantity'], 2); ?></td>
                                    <td><?php echo number_format($details['amount'], 2); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php else : ?>
                            <tr>
                                <td colspan="6" class="text-center">No data available.</td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>

        <div class="panel panel-info mt-4">
            <div class="panel-heading">Detailed Material Forecasting</div>
            <div class="panel-body">
                <button id="export-detail" class="btn btn-success mb-3" <?php echo empty($forecastings) ? 'disabled' : ''; ?>>Export Detailed Data to Excel</button>
                <table id="forecasting-table" class="table table-bordered table-striped">
                    <thead>
                        <tr>
                            <th>No Order</th>
                            <th>Schedule Date</th>
                            <th>Client</th>
                            <th>Part Number</th>
                            <th>Quantity Ordered</th>
                            <th>Component</th>
                            <th>Name</th>
                            <th>UOM</th>
                            <th>Picking No</th>
                            <th>Quantity Needed</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (!empty($forecastings)) : ?>
                            <?php foreach ($forecastings as $row) : ?>
                                <tr>
                                    <td><?php echo remove_junk($row['no_order']); ?></td>
                                    <td><?php echo remove_junk($row['schedule_date']); ?></td>
                                    <td><?php echo remove_junk($row['client']); ?></td>
                                    <td><?php echo remove_junk($row['part_number']); ?></td>
                                    <td><?php echo number_format($row['quantity_ordered']); ?></td>
                                    <td><?php echo remove_junk($row['component']); ?></td>
                                    <td><?php echo remove_junk($row['name']); ?></td>
                                    <td><?php echo remove_junk($row['uom']); ?></td>
                                    <td><?php echo remove_junk($row['picking_no']); ?></td>
                                    <td><?php echo number_format($row['quantity'], 2); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php else : ?>
                            <tr>
                                <td colspan="10" class="text-center">No data available.</td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
<script>
document.getElementById('export-summary').addEventListener('click', function () {
    let wb = XLSX.utils.table_to_book(document.getElementById('summary-table'), { sheet: "Summary" });
    XLSX.writeFile(wb, 'Material_Forecast_Summary_<?php echo $month.'_'.$year; ?>.xlsx');
});

document.getElementById('export-detail').addEventListener('click', function () {
    let wb = XLSX.utils.table_to_book(document.getElementById('forecasting-table'), { sheet: "Detail" });
    XLSX.writeFile(wb, 'Material_Forecast_Detail_<?php echo $month.'_'.$year; ?>.xlsx');
});
</script>

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