<?php
include 'indexapoteker.php';

// Hitung total demand otomatis dari mutasi stok tahun berjalan
$tahun_sekarang = date('Y');
$query_demand = "SELECT 
                    m.id_obat,
                    o.nama_obat,
                    o.kode_obat,
                    o.satuan,
                    o.lead_time_hari,
                    o.harga_beli,
                    SUM(m.jumlah) as total_demand
                 FROM mutasi_stok m
                 LEFT JOIN obat o ON m.id_obat = o.id_obat
                 WHERE m.tipe = 'KELUAR' AND YEAR(m.tanggal) = '$tahun_sekarang'
                 GROUP BY m.id_obat
                 HAVING total_demand > 0
                 ORDER BY o.nama_obat";
$result_demand = mysqli_query($conn, $query_demand);
?>
<section class="pc-container">
    <div class="pc-content">
        <div class="row">
            <div class="mb-4 d-flex justify-content-between align-items-center">
                <h2 class="mb-0">Economic Order Quantity (EOQ)</h2>
            </div>
        </div>

        <!-- Info Card -->
        <div class="row mb-3">
            <div class="col-md-12">
                <div class="card bg-light">
                    <div class="card-body">
                        <h6 class="mb-2"><i class="ti ti-info-circle"></i> Tentang EOQ</h6>
                        <p class="mb-2 small">
                            <strong>Economic Order Quantity (EOQ)</strong> adalah metode untuk menentukan jumlah pemesanan optimal yang meminimalkan total biaya persediaan.
                            Data perhitungan EOQ berikut merupakan hasil analisis dari admin berdasarkan data transaksi dan parameter biaya.
                        </p>
                        <p class="mb-0 small">
                            <strong>Rumus:</strong> EOQ = √((2 × D × S) / H) | 
                            <strong>ROP:</strong> (Demand Harian × Lead Time) + Safety Stock
                        </p>
                    </div>
                </div>
            </div>
        </div>

        <!-- Statistik Card -->
        <div class="row mb-3">
            <div class="col-md-6 col-xl-3">
                <div class="card social-widget-card bg-dark">
                    <div class="card-body">
                        <h3 class="text-white m-0">
                            <?php
                                $count_eoq = mysqli_query($conn, "SELECT COUNT(*) as total FROM eoq");
                                $total_eoq = mysqli_fetch_assoc($count_eoq)['total'];
                                echo $total_eoq;
                            ?>
                        </h3>
                        <span class="m-t-10">Total Perhitungan EOQ</span><br>
                        <small>Obat dengan EOQ aktif</small>
                        <i class="ti ti-rotate-clockwise"></i>
                    </div>
                </div>
            </div>
            <div class="col-md-6 col-xl-3">
                <div class="card social-widget-card bg-dark">
                    <div class="card-body">
                        <h3 class="text-white m-0">
                           <?= date('Y') ?>
                        </h3>
                        <span class="m-t-10">Periode Aktif</span><br>
                        <small>Tahun perhitungan</small>
                        <i class="ti ti-calendar"></i>
                    </div>
                </div>
            </div>
            <div class="col-md-6 col-xl-3">
                <div class="card social-widget-card bg-dark">
                    <div class="card-body">
                        <h3 class="text-white m-0">
                           <?= mysqli_num_rows($result_demand) ?>
                        </h3>
                        <span class="m-t-10">Obat dengan Demand</span><br>
                        <small>Obat aktif tahun ini</small>
                        <i class="ti ti-pill"></i>
                    </div>
                </div>
            </div>
            <div class="col-md-6 col-xl-3">
                <div class="card social-widget-card bg-dark">
                    <div class="card-body">
                        <h3 class="text-white m-0">
                           95%
                        </h3>
                        <span class="m-t-10">Service Level</span><br>
                        <small>Tingkat keandalan stok</small>
                        <i class="ti ti-percentage"></i>
                    </div>
                </div>
            </div>
        </div>

        <div class="row">
            <div class="col-sm-12">
                <div class="card">
                    <div class="card-header">
                        <h5>Daftar Perhitungan EOQ</h5>
                        <small>Hasil perhitungan Economic Order Quantity berdasarkan data transaksi (View Only)</small>
                    </div>
                    <div class="card-body">
                        <div class="dt-responsive">
                            <table id="dom-jqry" class="table table-striped table-bordered nowrap">
                                <thead>
                                    <tr>
                                        <th>No</th>
                                        <th>Periode</th>
                                        <th>Nama Obat</th>
                                        <th>Demand Tahunan</th>
                                        <th>EOQ</th>
                                        <th>Safety Stock</th>
                                        <th>Reorder Point</th>
                                        <th class="text-center">Aksi</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    $query = "SELECT e.*, o.nama_obat, o.kode_obat, o.satuan 
                                              FROM eoq e 
                                              LEFT JOIN obat o ON e.id_obat = o.id_obat 
                                              ORDER BY e.id_eoq DESC";
                                    $result = mysqli_query($conn, $query);
                                    $no = 1;
                                    while ($row = mysqli_fetch_assoc($result)) {
                                    ?>
                                    <tr>
                                        <td><?= $no++ ?></td>
                                        <td><span class="badge bg-secondary"><?= $row['periode'] ?></span></td>
                                        <td>
                                            <strong><?= $row['nama_obat'] ?></strong><br>
                                            <small class="text-muted"><?= $row['kode_obat'] ?></small>
                                        </td>
                                        <td><?= number_format($row['demand_tahunan'], 0, ',', '.') ?> <?= $row['satuan'] ?></td>
                                        <td><span class="badge bg-primary fs-6"><?= number_format($row['eoq'], 0, ',', '.') ?> <?= $row['satuan'] ?></span></td>
                                        <td><span class="badge bg-warning"><?= number_format($row['safety_stock'], 0, ',', '.') ?> <?= $row['satuan'] ?></span></td>
                                        <td><span class="badge bg-danger"><?= number_format($row['reorder_point'], 0, ',', '.') ?> <?= $row['satuan'] ?></span></td>
                                        <td class="text-center">
                                            <button class="btn btn-info btn-sm" data-bs-toggle="modal" data-bs-target="#detailEOQModal<?php echo $row['id_eoq']; ?>" title="Detail">
                                                <i class="ti ti-eye"></i>
                                            </button>
                                        </td>
                                    </tr>
                                    <?php } ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <!-- Tabel Obat dengan Demand (untuk referensi) -->
        <div class="row mt-3">
            <div class="col-sm-12">
                <div class="card">
                    <div class="card-header">
                        <h5>Data Demand Obat Tahun <?= date('Y') ?></h5>
                        <small>Data demand otomatis dari transaksi keluar (pemakaian obat)</small>
                    </div>
                    <div class="card-body">
                        <div class="table-responsive">
                            <table class="table table-sm table-bordered">
                                <thead class="table-light">
                                    <tr>
                                        <th>No</th>
                                        <th>Kode Obat</th>
                                        <th>Nama Obat</th>
                                        <th>Total Demand (Tahun Ini)</th>
                                        <th>Lead Time</th>
                                        <th>Status EOQ</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    mysqli_data_seek($result_demand, 0); // Reset pointer
                                    $no = 1;
                                    while ($demand = mysqli_fetch_assoc($result_demand)) {
                                        // Cek apakah sudah ada EOQ untuk obat ini
                                        $cek_eoq = mysqli_query($conn, "SELECT id_eoq FROM eoq WHERE id_obat = '{$demand['id_obat']}' AND periode = '" . date('Y') . "'");
                                        $has_eoq = mysqli_num_rows($cek_eoq) > 0;
                                    ?>
                                    <tr>
                                        <td><?= $no++ ?></td>
                                        <td><?= $demand['kode_obat'] ?></td>
                                        <td><?= $demand['nama_obat'] ?></td>
                                        <td><?= number_format($demand['total_demand'], 0, ',', '.') ?> <?= $demand['satuan'] ?></td>
                                        <td><?= $demand['lead_time_hari'] ?> hari</td>
                                        <td>
                                            <?php if ($has_eoq) { ?>
                                                <span class="badge bg-success">Sudah Dihitung</span>
                                            <?php } else { ?>
                                                <span class="badge bg-warning">Belum Dihitung</span>
                                            <?php } ?>
                                        </td>
                                    </tr>
                                    <?php } ?>
                                    
                                    <?php if (mysqli_num_rows($result_demand) == 0) { ?>
                                    <tr>
                                        <td colspan="6" class="text-center">Belum ada data demand untuk tahun ini</td>
                                    </tr>
                                    <?php } ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</section>

<?php
    $query = "SELECT e.*, o.nama_obat, o.kode_obat, o.satuan, o.lead_time_hari, o.harga_beli 
              FROM eoq e 
              LEFT JOIN obat o ON e.id_obat = o.id_obat 
              ORDER BY e.id_eoq DESC";
    $result = mysqli_query($conn, $query);
    while ($row = mysqli_fetch_assoc($result)) {
?>
<!-- Modal Detail EOQ -->
<div class="modal fade" id="detailEOQModal<?php echo $row['id_eoq']; ?>" tabindex="-1" aria-labelledby="detailEOQModalLabel" aria-hidden="true">
    <div class="modal-dialog modal-xl">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title" id="detailEOQModalLabel">📊 Detail Perhitungan EOQ - <?= $row['nama_obat'] ?></h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
            </div>
            <div class="modal-body">
                <div class="row mb-3">
                    <div class="col-md-6">
                        <h6 class="fw-bold">📦 Informasi Obat</h6>
                        <table class="table table-sm table-bordered">
                            <tr>
                                <td width="150"><strong>Kode Obat</strong></td>
                                <td><?= $row['kode_obat'] ?></td>
                            </tr>
                            <tr>
                                <td><strong>Nama Obat</strong></td>
                                <td><strong class="text-primary"><?= $row['nama_obat'] ?></strong></td>
                            </tr>
                            <tr>
                                <td><strong>Satuan</strong></td>
                                <td><?= $row['satuan'] ?></td>
                            </tr>
                            <tr>
                                <td><strong>Harga Beli</strong></td>
                                <td>Rp <?= number_format($row['harga_beli'], 0, ',', '.') ?></td>
                            </tr>
                        </table>
                    </div>
                    <div class="col-md-6">
                        <h6 class="fw-bold">📅 Informasi Periode</h6>
                        <table class="table table-sm table-bordered">
                            <tr>
                                <td width="150"><strong>Periode</strong></td>
                                <td><span class="badge bg-secondary"><?= $row['periode'] ?></span></td>
                            </tr>
                            <tr>
                                <td><strong>Demand Tahunan</strong></td>
                                <td><strong class="text-success"><?= number_format($row['demand_tahunan'], 0, ',', '.') ?> <?= $row['satuan'] ?></strong></td>
                            </tr>
                            <tr>
                                <td><strong>Lead Time</strong></td>
                                <td><?= $row['lead_time_hari'] ?> Hari</td>
                            </tr>
                            <tr>
                                <td><strong>Demand Harian</strong></td>
                                <td><?= number_format($row['demand_tahunan'] / 365, 2) ?> <?= $row['satuan'] ?>/hari</td>
                            </tr>
                        </table>
                    </div>
                </div>

                <div class="row mb-3">
                    <div class="col-md-12">
                        <h6 class="fw-bold">💰 Parameter Biaya</h6>
                        <div class="row">
                            <div class="col-md-6">
                                <div class="card bg-light">
                                    <div class="card-body">
                                        <h6 class="card-title">Biaya Pemesanan (S)</h6>
                                        <h3 class="text-primary mb-0">Rp <?= number_format($row['biaya_pesan'], 0, ',', '.') ?></h3>
                                        <small class="text-muted">Per order</small>
                                    </div>
                                </div>
                            </div>
                            <div class="col-md-6">
                                <div class="card bg-light">
                                    <div class="card-body">
                                        <h6 class="card-title">Biaya Penyimpanan (H)</h6>
                                        <h3 class="text-warning mb-0">Rp <?= number_format($row['biaya_simpan'], 0, ',', '.') ?></h3>
                                        <small class="text-muted">Per unit per tahun</small>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>

                <div class="row mb-3">
                    <div class="col-md-12">
                        <h6 class="fw-bold">📈 Hasil Perhitungan EOQ</h6>
                        <div class="card bg-success bg-gradient text-white">
                            <div class="card-body">
                                <div class="row text-center">
                                    <div class="col-md-4">
                                        <h6>Economic Order Quantity</h6>
                                        <h2 class="mb-0"><strong><?= number_format($row['eoq'], 0, ',', '.') ?></strong></h2>
                                        <small><?= $row['satuan'] ?> per order</small>
                                    </div>
                                    <div class="col-md-4">
                                        <h6>Safety Stock</h6>
                                        <h2 class="mb-0"><strong><?= number_format($row['safety_stock'], 0, ',', '.') ?></strong></h2>
                                        <small><?= $row['satuan'] ?> (stok pengaman)</small>
                                    </div>
                                    <div class="col-md-4">
                                        <h6>Reorder Point (ROP)</h6>
                                        <h2 class="mb-0"><strong><?= number_format($row['reorder_point'], 0, ',', '.') ?></strong></h2>
                                        <small><?= $row['satuan'] ?> (titik pesan ulang)</small>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>

                <div class="row">
                    <div class="col-md-12">
                        <h6 class="fw-bold">📊 Analisis & Rekomendasi</h6>
                        <div class="card">
                            <div class="card-body">
                                <?php
                                $demand_harian = $row['demand_tahunan'] / 365;
                                $frekuensi_pesan = ceil($row['demand_tahunan'] / $row['eoq']);
                                $interval_pesan = 365 / $frekuensi_pesan;
                                $total_biaya_pesan = $frekuensi_pesan * $row['biaya_pesan'];
                                $avg_inventory = $row['eoq'] / 2;
                                $total_biaya_simpan = $avg_inventory * $row['biaya_simpan'];
                                $total_biaya = $total_biaya_pesan + $total_biaya_simpan;
                                ?>
                                <div class="row">
                                    <div class="col-md-6">
                                        <ul class="list-unstyled mb-0">
                                            <li class="mb-2">📊 <strong>Demand Harian:</strong> <?= number_format($demand_harian, 2) ?> <?= $row['satuan'] ?>/hari</li>
                                            <li class="mb-2">🔄 <strong>Frekuensi Pemesanan:</strong> <?= $frekuensi_pesan ?> kali per tahun</li>
                                            <li class="mb-2">📅 <strong>Interval Pemesanan:</strong> Setiap <?= round($interval_pesan) ?> hari</li>
                                            <li class="mb-2">🏪 <strong>Rata-rata Inventory:</strong> <?= number_format($avg_inventory, 2) ?> <?= $row['satuan'] ?></li>
                                        </ul>
                                    </div>
                                    <div class="col-md-6">
                                        <ul class="list-unstyled mb-0">
                                            <li class="mb-2">💰 <strong>Total Biaya Pemesanan:</strong> Rp <?= number_format($total_biaya_pesan, 0, ',', '.') ?>/tahun</li>
                                            <li class="mb-2">💵 <strong>Total Biaya Penyimpanan:</strong> Rp <?= number_format($total_biaya_simpan, 0, ',', '.') ?>/tahun</li>
                                            <li class="mb-2"><strong>📈 TOTAL BIAYA PERSEDIAAN:</strong> <span class="text-danger fs-5"><strong>Rp <?= number_format($total_biaya, 0, ',', '.') ?>/tahun</strong></span></li>
                                        </ul>
                                    </div>
                                </div>
                                <hr>
                                <div class="alert alert-info mb-0">
                                    <h6 class="mb-2"><strong>💡 Rekomendasi Pemesanan:</strong></h6>
                                    <ul class="mb-0">
                                        <li>Lakukan pemesanan sebanyak <strong><?= number_format($row['eoq'], 0, ',', '.') ?> <?= $row['satuan'] ?></strong> per order</li>
                                        <li>Pesan ulang ketika stok mencapai <strong><?= number_format($row['reorder_point'], 0, ',', '.') ?> <?= $row['satuan'] ?></strong> atau kurang</li>
                                        <li>Pertahankan safety stock minimal <strong><?= number_format($row['safety_stock'], 0, ',', '.') ?> <?= $row['satuan'] ?></strong></li>
                                        <li>Estimasi pemesanan dilakukan setiap <strong><?= round($interval_pesan) ?> hari</strong> sekali</li>
                                    </ul>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-outline-dark" data-bs-dismiss="modal">Tutup</button>
                <button type="button" class="btn btn-primary" onclick="window.print()"><i class="ti ti-printer"></i> Cetak</button>
            </div>
        </div>
    </div>
</div>

<?php } ?>

<?php include 'footer.php'; ?>