<?php
include 'index.php';

// Ambil data EOQ dan stok saat ini
$tahun_sekarang = date('Y');

// Build query dengan filter
$where_clauses = ["e.periode = '$tahun_sekarang'"];

if (isset($_GET['status']) && !empty($_GET['status'])) {
    // Filter status akan diterapkan setelah query
}

$where_sql = implode(" AND ", $where_clauses);

$query_rop = "SELECT 
                e.id_eoq,
                e.id_obat,
                e.periode,
                e.demand_tahunan,
                e.biaya_pesan,
                e.biaya_simpan,
                e.eoq,
                e.safety_stock,
                e.reorder_point,
                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
              WHERE $where_sql
              ORDER BY o.nama_obat ASC";

$result_rop = mysqli_query($conn, $query_rop);

if (!$result_rop) {
    die("Query Error: " . mysqli_error($conn));
}

// Hitung statistik dan proses data
$total_obat = 0;
$perlu_pesan = 0;
$kritis = 0;
$aman = 0;

$temp_data = [];
while ($row = mysqli_fetch_assoc($result_rop)) {
    // Ambil stok tersedia untuk obat ini
    $query_stok = "SELECT COALESCE(SUM(jumlah), 0) as stok_tersedia 
                   FROM stok 
                   WHERE id_obat = '{$row['id_obat']}'";
    $result_stok = mysqli_query($conn, $query_stok);
    $data_stok = mysqli_fetch_assoc($result_stok);
    $row['stok_tersedia'] = $data_stok['stok_tersedia'];
    
    $selisih = $row['stok_tersedia'] - $row['reorder_point'];
    $demand_harian = $row['demand_tahunan'] / 365;
    
    // Tentukan status
    if ($row['stok_tersedia'] <= $row['safety_stock']) {
        $row['status_filter'] = 'kritis';
        $row['status_text'] = 'KRITIS';
        $row['status_badge'] = 'bg-danger';
        $jumlah_pesan = $row['eoq'] + ($row['safety_stock'] - $row['stok_tersedia']);
        $row['jumlah_pesan'] = $jumlah_pesan;
        $row['rekomendasi'] = "SEGERA PESAN " . number_format($jumlah_pesan, 0, ',', '.') . " " . $row['satuan'];
        $kritis++;
        $perlu_pesan++;
    } elseif ($row['stok_tersedia'] <= $row['reorder_point']) {
        $row['status_filter'] = 'perlu_pesan';
        $row['status_text'] = 'PERLU PESAN';
        $row['status_badge'] = 'bg-warning';
        $row['jumlah_pesan'] = $row['eoq'];
        $row['rekomendasi'] = "Pesan " . number_format($row['eoq'], 0, ',', '.') . " " . $row['satuan'];
        $perlu_pesan++;
    } else {
        $row['status_filter'] = 'aman';
        $row['status_text'] = 'AMAN';
        $row['status_badge'] = 'bg-success';
        $hari_aman = $demand_harian > 0 ? floor($selisih / $demand_harian) : 0;
        $row['jumlah_pesan'] = 0;
        $row['rekomendasi'] = "Stok aman untuk ~" . $hari_aman . " hari";
        $aman++;
    }
    
    $row['selisih'] = $selisih;
    $row['demand_harian'] = $demand_harian;
    
    // Apply status filter
    if (isset($_GET['status']) && !empty($_GET['status'])) {
        if ($_GET['status'] != $row['status_filter']) {
            continue;
        }
    }
    
    $temp_data[] = $row;
    $total_obat++;
}
?>
<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">Reorder Point (ROP) Monitoring</h2>
                <a href="eoq.php" class="btn btn-primary rounded">
                    <i class="ti ti-calculator"></i> Kelola EOQ
                </a>
            </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 Reorder Point (ROP)</h6>
                        <p class="mb-2 small">
                            <strong>Reorder Point (ROP)</strong> adalah titik pemesanan kembali yang menunjukkan kapan harus melakukan pemesanan ulang agar stok tidak habis.
                            ROP dihitung berdasarkan demand harian, lead time, dan safety stock.
                        </p>
                        <p class="mb-0 small">
                            <strong>Rumus:</strong> ROP = (Demand Harian × Lead Time) + Safety Stock | 
                            <strong>Status:</strong> 
                            <span class="badge bg-danger ms-1">Kritis</span> Stok ≤ Safety Stock |
                            <span class="badge bg-warning ms-1">Perlu Pesan</span> Stok ≤ ROP |
                            <span class="badge bg-success ms-1">Aman</span> Stok > ROP
                        </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-primary">
                    <div class="card-body">
                        <h3 class="text-white m-0"><?= $total_obat ?></h3>
                        <span class="m-t-10">Total Obat Monitoring</span><br>
                        <small>Obat dengan perhitungan ROP</small>
                        <i class="ti ti-package"></i>
                    </div>
                </div>
            </div>
            <div class="col-md-6 col-xl-3">
                <div class="card social-widget-card bg-danger">
                    <div class="card-body">
                        <h3 class="text-white m-0"><?= $kritis ?></h3>
                        <span class="m-t-10">Status Kritis</span><br>
                        <small>Stok ≤ Safety Stock</small>
                        <i class="ti ti-alert-triangle"></i>
                    </div>
                </div>
            </div>
            <div class="col-md-6 col-xl-3">
                <div class="card social-widget-card bg-warning">
                    <div class="card-body">
                        <h3 class="text-white m-0"><?= $perlu_pesan ?></h3>
                        <span class="m-t-10">Perlu Pemesanan</span><br>
                        <small>Stok ≤ Reorder Point</small>
                        <i class="ti ti-shopping-cart"></i>
                    </div>
                </div>
            </div>
            <div class="col-md-6 col-xl-3">
                <div class="card social-widget-card bg-success">
                    <div class="card-body">
                        <h3 class="text-white m-0"><?= $aman ?></h3>
                        <span class="m-t-10">Status Aman</span><br>
                        <small>Stok > Reorder Point</small>
                        <i class="ti ti-check"></i>
                    </div>
                </div>
            </div>
        </div>

        <!-- Filter -->
        <div class="row mb-3">
            <div class="col-sm-12">
                <div class="card">
                    <div class="card-header">
                        <h5>Filter Status ROP</h5>
                    </div>
                    <div class="card-body">
                        <form method="GET" action="" id="filterForm">
                            <div class="row align-items-end">
                                <div class="col-md-4">
                                    <label for="status" class="form-label">Status Stok</label>
                                    <select class="form-select" id="status" name="status">
                                        <option value="">Semua Status</option>
                                        <option value="kritis" <?= (isset($_GET['status']) && $_GET['status'] == 'kritis') ? 'selected' : '' ?>>Kritis</option>
                                        <option value="perlu_pesan" <?= (isset($_GET['status']) && $_GET['status'] == 'perlu_pesan') ? 'selected' : '' ?>>Perlu Pesan</option>
                                        <option value="aman" <?= (isset($_GET['status']) && $_GET['status'] == 'aman') ? 'selected' : '' ?>>Aman</option>
                                    </select>
                                </div>
                                <div class="col-md-4">
                                    <button type="submit" class="btn btn-primary">
                                        <i class="ti ti-filter"></i> Filter
                                    </button>
                                    <?php if (isset($_GET['status'])) { ?>
                                    <a href="rop.php" class="btn btn-secondary">
                                        <i class="ti ti-refresh"></i> Reset
                                    </a>
                                    <?php } ?>
                                </div>
                            </div>
                        </form>
                    </div>
                </div>
            </div>
        </div>

        <!-- Tabel ROP -->
        <div class="row">
            <div class="col-sm-12">
                <div class="card">
                    <div class="card-header">
                        <h5>Monitoring Reorder Point Tahun <?= $tahun_sekarang ?></h5>
                        <small>Status stok berdasarkan perhitungan EOQ dan ROP</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>Kode Obat</th>
                                        <th>Nama Obat</th>
                                        <th>Stok Tersedia</th>
                                        <th>Safety Stock</th>
                                        <th>Reorder Point</th>
                                        <th>EOQ</th>
                                        <th>Status</th>
                                        <th>Rekomendasi</th>
                                        <th class="text-center">Aksi</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    $no = 1;
                                    if (count($temp_data) > 0) {
                                        foreach ($temp_data as $row) {
                                    ?>
                                    <tr>
                                        <td><?= $no++ ?></td>
                                        <td><?= $row['kode_obat'] ?></td>
                                        <td>
                                            <strong><?= $row['nama_obat'] ?></strong><br>
                                            <small class="text-muted">Lead Time: <?= $row['lead_time_hari'] ?> hari</small>
                                        </td>
                                        <td>
                                            <strong class="fs-6"><?= number_format($row['stok_tersedia'], 0, ',', '.') ?></strong> <?= $row['satuan'] ?><br>
                                            <small class="text-muted">Selisih: <?= $row['selisih'] >= 0 ? '+' : '' ?><?= number_format($row['selisih'], 0, ',', '.') ?></small>
                                        </td>
                                        <td>
                                            <span class="badge bg-info"><?= number_format($row['safety_stock'], 0, ',', '.') ?></span> <?= $row['satuan'] ?>
                                        </td>
                                        <td>
                                            <span class="badge bg-primary"><?= number_format($row['reorder_point'], 0, ',', '.') ?></span> <?= $row['satuan'] ?>
                                        </td>
                                        <td>
                                            <span class="badge bg-dark"><?= number_format($row['eoq'], 0, ',', '.') ?></span> <?= $row['satuan'] ?>
                                        </td>
                                        <td>
                                            <span class="badge <?= $row['status_badge'] ?> fs-6"><?= $row['status_text'] ?></span>
                                        </td>
                                        <td>
                                            <?php if ($row['status_filter'] == 'kritis') { ?>
                                                <span class="text-danger"><strong><?= $row['rekomendasi'] ?></strong></span>
                                            <?php } elseif ($row['status_filter'] == 'perlu_pesan') { ?>
                                                <span class="text-warning"><strong><?= $row['rekomendasi'] ?></strong></span>
                                            <?php } else { ?>
                                                <span class="text-success"><?= $row['rekomendasi'] ?></span>
                                            <?php } ?>
                                        </td>
                                        <td class="text-center">
                                            <button class="btn btn-info btn-sm" data-bs-toggle="modal" data-bs-target="#detailROPModal<?= $row['id_eoq'] ?>" title="Detail">
                                                <i class="ti ti-eye"></i>
                                            </button>
                                        </td>
                                    </tr>
                                    <?php 
                                        }
                                    } else {
                                    ?>
                                    <tr>
                                        <td colspan="10" class="text-center">
                                            <?php if (isset($_GET['status'])) { ?>
                                                Tidak ada data dengan status "<?= strtoupper(str_replace('_', ' ', $_GET['status'])) ?>"
                                            <?php } else { ?>
                                                Belum ada data EOQ untuk tahun <?= $tahun_sekarang ?>. Silakan hitung EOQ terlebih dahulu di menu <a href="eoq.php">EOQ</a>.
                                            <?php } ?>
                                        </td>
                                    </tr>
                                    <?php } ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</section>

<?php
// Modal Detail ROP
foreach ($temp_data as $row) {
?>
<!-- Modal Detail ROP -->
<div class="modal fade" id="detailROPModal<?= $row['id_eoq'] ?>" tabindex="-1" aria-labelledby="detailROPModalLabel" aria-hidden="true">
    <div class="modal-dialog modal-xl">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title" id="detailROPModalLabel">📊 Detail Monitoring ROP - <?= $row['nama_obat'] ?></h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
            </div>
            <div class="modal-body">
                <!-- Status Alert -->
                <div class="alert alert-<?= $row['status_filter'] == 'kritis' ? 'danger' : ($row['status_filter'] == 'perlu_pesan' ? 'warning' : 'success') ?> alert-dismissible fade show">
                    <h5 class="alert-heading">
                        <i class="ti ti-<?= $row['status_filter'] == 'kritis' ? 'alert-triangle' : ($row['status_filter'] == 'perlu_pesan' ? 'alert-circle' : 'check') ?>"></i> 
                        Status: <strong><?= $row['status_text'] ?></strong>
                    </h5>
                    <p class="mb-0">
                        <?php if ($row['status_filter'] == 'kritis') { ?>
                            SEGERA lakukan pemesanan minimal <?= number_format($row['jumlah_pesan'], 0, ',', '.') ?> <?= $row['satuan'] ?> untuk menghindari stockout!
                        <?php } elseif ($row['status_filter'] == 'perlu_pesan') { ?>
                            Saatnya melakukan pemesanan sejumlah <?= number_format($row['eoq'], 0, ',', '.') ?> <?= $row['satuan'] ?> (sesuai EOQ).
                        <?php } else { ?>
                            Stok masih aman. Estimasi akan mencapai ROP dalam <?= $row['demand_harian'] > 0 ? floor($row['selisih'] / $row['demand_harian']) : 0 ?> hari kedepan.
                        <?php } ?>
                    </p>
                </div>

                <div class="row mb-3">
                    <div class="col-md-4">
                        <div class="card bg-light">
                            <div class="card-body text-center">
                                <h6 class="text-muted mb-2">Stok Tersedia</h6>
                                <h2 class="mb-0"><strong><?= number_format($row['stok_tersedia'], 0, ',', '.') ?></strong></h2>
                                <p class="mb-0"><?= $row['satuan'] ?></p>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-4">
                        <div class="card bg-primary bg-gradient text-white">
                            <div class="card-body text-center">
                                <h6 class="text-white mb-2">Reorder Point (ROP)</h6>
                                <h2 class="mb-0 text-white"><strong><?= number_format($row['reorder_point'], 0, ',', '.') ?></strong></h2>
                                <p class="mb-0"><?= $row['satuan'] ?></p>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-4">
                        <div class="card bg-info bg-gradient text-white">
                            <div class="card-body text-center">
                                <h6 class="text-white mb-2">Safety Stock</h6>
                                <h2 class="mb-0 text-white"><strong><?= number_format($row['safety_stock'], 0, ',', '.') ?></strong></h2>
                                <p class="mb-0"><?= $row['satuan'] ?></p>
                            </div>
                        </div>
                    </div>
                </div>

                <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">📈 Data Perhitungan</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><?= number_format($row['demand_tahunan'], 0, ',', '.') ?> <?= $row['satuan'] ?></td>
                            </tr>
                            <tr>
                                <td><strong>Demand Harian</strong></td>
                                <td><?= number_format($row['demand_harian'], 2) ?> <?= $row['satuan'] ?>/hari</td>
                            </tr>
                            <tr>
                                <td><strong>Lead Time</strong></td>
                                <td><?= $row['lead_time_hari'] ?> hari</td>
                            </tr>
                        </table>
                    </div>
                </div>

                <div class="row mb-3">
                    <div class="col-md-12">
                        <h6 class="fw-bold">📊 Analisis Stok</h6>
                        <table class="table table-bordered">
                            <tr class="table-light">
                                <td width="250"><strong>Economic Order Quantity (EOQ)</strong></td>
                                <td><strong><?= number_format($row['eoq'], 0, ',', '.') ?> <?= $row['satuan'] ?></strong></td>
                            </tr>
                            <tr>
                                <td><strong>Stok Saat Ini</strong></td>
                                <td><?= number_format($row['stok_tersedia'], 0, ',', '.') ?> <?= $row['satuan'] ?></td>
                            </tr>
                            <tr>
                                <td><strong>Reorder Point (ROP)</strong></td>
                                <td><span class="badge bg-primary fs-6"><?= number_format($row['reorder_point'], 0, ',', '.') ?> <?= $row['satuan'] ?></span></td>
                            </tr>
                            <tr>
                                <td><strong>Safety Stock</strong></td>
                                <td><span class="badge bg-info fs-6"><?= number_format($row['safety_stock'], 0, ',', '.') ?> <?= $row['satuan'] ?></span></td>
                            </tr>
                            <tr class="table-light">
                                <td><strong>Selisih (Stok - ROP)</strong></td>
                                <td>
                                    <strong class="<?= $row['selisih'] < 0 ? 'text-danger' : 'text-success' ?>">
                                        <?= $row['selisih'] >= 0 ? '+' : '' ?><?= number_format($row['selisih'], 0, ',', '.') ?> <?= $row['satuan'] ?>
                                    </strong>
                                </td>
                            </tr>
                            <tr class="table-light">
                                <td><strong>Status</strong></td>
                                <td><span class="badge <?= $row['status_badge'] ?> fs-6"><?= $row['status_text'] ?></span></td>
                            </tr>
                        </table>
                    </div>
                </div>

                <div class="row">
                    <div class="col-md-12">
                        <h6 class="fw-bold">💡 Rekomendasi Tindakan</h6>
                        <div class="card">
                            <div class="card-body">
                                <?php if ($row['status_filter'] == 'kritis') { ?>
                                    <div class="alert alert-danger mb-3">
                                        <h6><strong>⚠️ TINDAKAN SEGERA DIPERLUKAN!</strong></h6>
                                        <ul class="mb-0">
                                            <li>Stok sudah berada di bawah safety stock</li>
                                            <li>Risiko tinggi terjadi stockout</li>
                                            <li>Lakukan pemesanan darurat minimal <?= number_format($row['jumlah_pesan'], 0, ',', '.') ?> <?= $row['satuan'] ?></li>
                                            <li>Pertimbangkan pemesanan expedited delivery</li>
                                            <li>Estimasi nilai pemesanan: Rp <?= number_format($row['jumlah_pesan'] * $row['harga_beli'], 0, ',', '.') ?></li>
                                        </ul>
                                    </div>
                                <?php } elseif ($row['status_filter'] == 'perlu_pesan') { ?>
                                    <div class="alert alert-warning mb-3">
                                        <h6><strong>📋 WAKTU PEMESANAN</strong></h6>
                                        <ul class="mb-0">
                                            <li>Stok sudah mencapai reorder point</li>
                                            <li>Lakukan pemesanan sejumlah <?= number_format($row['eoq'], 0, ',', '.') ?> <?= $row['satuan'] ?> (sesuai EOQ)</li>
                                            <li>Lead time: <?= $row['lead_time_hari'] ?> hari</li>
                                            <li>Estimasi nilai pemesanan: Rp <?= number_format($row['eoq'] * $row['harga_beli'], 0, ',', '.') ?></li>
                                        </ul>
                                    </div>
                                <?php } else { ?>
                                    <div class="alert alert-success mb-3">
                                        <h6><strong>✅ STOK AMAN</strong></h6>
                                        <ul class="mb-0">
                                            <li>Stok masih di atas reorder point</li>
                                            <li>Belum perlu melakukan pemesanan</li>
                                            <li>Estimasi mencapai ROP dalam <?= $row['demand_harian'] > 0 ? floor($row['selisih'] / $row['demand_harian']) : 0 ?> hari</li>
                                            <li>Monitor stok secara berkala</li>
                                        </ul>
                                    </div>
                                <?php } ?>

                                <div class="table-responsive">
                                    <table class="table table-sm table-bordered mb-0">
                                        <thead class="table-dark">
                                            <tr>
                                                <th>Parameter</th>
                                                <th>Nilai</th>
                                                <th>Keterangan</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <tr>
                                                <td><strong>Demand Harian</strong></td>
                                                <td><?= number_format($row['demand_harian'], 2) ?> <?= $row['satuan'] ?></td>
                                                <td>Kebutuhan per hari</td>
                                            </tr>
                                            <tr>
                                                <td><strong>Konsumsi selama Lead Time</strong></td>
                                                <td><?= number_format($row['demand_harian'] * $row['lead_time_hari'], 2) ?> <?= $row['satuan'] ?></td>
                                                <td>Kebutuhan selama <?= $row['lead_time_hari'] ?> hari</td>
                                            </tr>
                                            <tr>
                                                <td><strong>Estimasi Habis (jika tidak pesan)</strong></td>
                                                <td><?= $row['demand_harian'] > 0 ? floor($row['stok_tersedia'] / $row['demand_harian']) : 0 ?> hari</td>
                                                <td>Berdasarkan stok saat ini</td>
                                            </tr>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-outline-dark" data-bs-dismiss="modal">Tutup</button>
                <a href="eoq.php" class="btn btn-primary"><i class="ti ti-calculator"></i> Lihat EOQ</a>
            </div>
        </div>
    </div>
</div>
<?php } ?>

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