<?php
$page_title = 'Item List';
require_once('includes/load.php');
page_require_level(5);

// Check if a form was submitted for search or pagination
if ($_SERVER['REQUEST_METHOD'] === 'GET' && isset($_GET['export_excel'])) {
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="product_data.xls"');

    $sql = "SELECT p.id, p.name, p.part_number, p.quantity, p.UoM, p.buy_price, c.name AS categorie, p.loc_id, p.lead_time, p.barcode_id, p.date, p.coa_kode, (p.quantity * p.buy_price) as inventory_amount
            FROM products p
            LEFT JOIN categories c ON p.categorie_id = c.id";

    $search_term = isset($_GET['search']) ? $db->escape($_GET['search']) : '';
    $categorie_id = isset($_GET['categorie_id']) ? (int)$_GET['categorie_id'] : 0;

    $where_clauses = [];
    if (!empty($search_term)) {
        $where_clauses[] = " (p.name LIKE '%{$search_term}%' OR p.part_number LIKE '%{$search_term}%') ";
    }
    if ($categorie_id > 0) {
        $where_clauses[] = " p.categorie_id = {$categorie_id} ";
    }

    if (!empty($where_clauses)) {
        $sql .= " WHERE " . implode(' AND ', $where_clauses);
    }
    
    $result = $db->query($sql);

    // Generate table for export
    echo '<table border="1">';
    echo '<thead><tr><th>#</th><th>Part Number</th><th>Product</th><th>Category</th><th>COA Code</th><th>Quantity</th><th>Unit Price</th><th>Inventory Amount</th><th>Lokasi ID</th><th>Lead Time</th><th>Barcode ID</th><th>Tanggal</th></tr></thead>';
    echo '<tbody>';
    $i = 1;
    while ($row = $db->fetch_assoc($result)) {
        echo '<tr>';
        echo '<td>' . $i++ . '</td>';
        echo '<td>' . htmlspecialchars($row['part_number']) . '</td>';
        echo '<td>' . remove_junk($row['name']) . '</td>';
        echo '<td>' . remove_junk($row['categorie']) . '</td>';
        echo '<td>' . htmlspecialchars($row['coa_kode']) . '</td>'; // Ditambahkan: Kolom COA Code
        echo '<td>' . number_format($row['quantity'], 2) . '</td>';
        echo '<td>' . remove_junk($row['buy_price']) . '</td>';
        echo '<td>' . remove_junk(number_format($row['inventory_amount'], 2)) . '</td>';
        echo '<td>' . htmlspecialchars($row['loc_id']) . '</td>';
        echo '<td>' . htmlspecialchars($row['lead_time']) . '</td>';
        echo '<td>' . htmlspecialchars($row['barcode_id']) . '</td>';
        echo '<td>' . htmlspecialchars($row['date']) . '</td>';
        echo '</tr>';
    }
    echo '</tbody>';
    echo '</table>';

    exit;
}

// Dashboard Metrics
$total_products_count = find_by_sql("SELECT COUNT(*) AS total FROM products")[0]['total'];
$total_categories_count = find_by_sql("SELECT COUNT(DISTINCT categorie_id) AS total FROM products")[0]['total'];
$total_quantity = find_by_sql("SELECT SUM(quantity) AS total FROM products")[0]['total'];
$low_stock_count = find_by_sql("SELECT COUNT(*) AS total FROM products WHERE quantity < 5")[0]['total']; 
$total_inventory_amount = find_by_sql("SELECT SUM(buy_price * quantity) AS total FROM products")[0]['total'];

// Pagination, search, and filter settings
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$per_page = isset($_GET['per_page']) ? (int)$_GET['per_page'] : 10;
$categorie_id = isset($_GET['categorie_id']) ? (int)$_GET['categorie_id'] : 0;
$search_term = isset($_GET['search']) ? $db->escape($_GET['search']) : '';

// Build the SQL query for counting total rows
$sql = "SELECT COUNT(*) AS total FROM products p";
$where_clauses = [];
if (!empty($search_term)) {
    $where_clauses[] = " (p.name LIKE '%{$search_term}%' OR p.part_number LIKE '%{$search_term}%') ";
}
if ($categorie_id > 0) {
    $where_clauses[] = " p.categorie_id = {$categorie_id} ";
}
if (!empty($where_clauses)) {
    $sql .= " WHERE " . implode(' AND ', $where_clauses);
}

$result = $db->query($sql);
$total_products_filtered = $db->fetch_assoc($result)['total'];
$total_pages = ceil($total_products_filtered / $per_page);
$start = ($page - 1) * $per_page;

// Build the main SQL query for displaying data
$sql = "SELECT p.id, p.name, p.part_number, p.quantity, p.UoM, p.buy_price, p.loc_id, p.lead_time, p.barcode_id, p.date, p.coa_kode, c.name AS categorie, (p.quantity * p.buy_price) as inventory_amount
        FROM products p
        LEFT JOIN categories c ON p.categorie_id = c.id";

if (!empty($where_clauses)) {
    $sql .= " WHERE " . implode(' AND ', $where_clauses);
}

$sql .= " LIMIT {$per_page} OFFSET {$start}";
$products = find_by_sql($sql);
$categories = find_all('categories');
?>

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

<style>
    .table thead th,
    .table tbody td {
        font-size: 12px;
    }
    .panel-body h2 {
        font-size: 20px;
    }
    .panel-heading i {
        font-size: 12px;
    }
</style>

<div class="row">
    <div class="col-md-12">
        <?php echo display_msg($msg); ?>
    </div>
</div>

<div class="row" style="display: flex; justify-content: space-between; flex-wrap: wrap;">
    <div class="col-md-2" style="flex: 1 1 18%;">
        <div class="panel panel-info">
            <div class="panel-heading">
                <i class="glyphicon glyphicon-th"></i> Total Products
            </div>
            <div class="panel-body text-center" style="font-size: 12px;">
                <h2><?php echo number_format($total_products_count); ?></h2>
            </div>
        </div>
    </div>
    <div class="col-md-2" style="flex: 1 1 18%;">
        <div class="panel panel-success">
            <div class="panel-heading">
                <i class="glyphicon glyphicon-list"></i> Total Categories
            </div>
            <div class="panel-body text-center" style="font-size: 12px;">
                <h2><?php echo number_format($total_categories_count); ?></h2>
            </div>
        </div>
    </div>
    <div class="col-md-2" style="flex: 1 1 18%;">
        <div class="panel panel-primary">
            <div class="panel-heading">
                <i class="glyphicon glyphicon-sort-by-numeric"></i> Total Quantity
            </div>
            <div class="panel-body text-center" style="font-size: 12px;">
                <h2><?php echo number_format($total_quantity, 2); ?></h2>
            </div>
        </div>
    </div>
    <div class="col-md-2" style="flex: 1 1 18%;">
        <div class="panel panel-danger">
            <div class="panel-heading">
                <i class="glyphicon glyphicon-alert"></i> Low Stock
            </div>
            <div class="panel-body text-center" style="font-size: 12px;">
                <h2><?php echo number_format($low_stock_count); ?></h2>
            </div>
        </div>
    </div>
    <div class="col-md-2" style="flex: 1 1 18%;">
        <div class="panel panel-warning">
            <div class="panel-heading">
                <i class="glyphicon glyphicon-usd"></i> Total Inventory Amount
            </div>
            <div class="panel-body text-center" style="font-size: 12px;">
                <h2>Rp <?php echo number_format($total_inventory_amount, 2); ?></h2>
            </div>
        </div>
    </div>
</div>

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading clearfix">
                <strong>
                    <span class="glyphicon glyphicon-th"></span>
                    <span>Daftar Produk</span>
                </strong>
                <a href="add_product" class="btn btn-info pull-right">Tambah Produk</a>
            </div>
            <div class="panel-body">
                <form action="" method="GET" class="form-horizontal mb-4">
                    <div class="form-group clearfix">
                        <div class="col-md-4">
                            <input type="text" name="search" class="form-control" placeholder="Cari nama atau part number..." value="<?php echo htmlspecialchars($search_term); ?>">
                        </div>
                        <div class="col-md-3">
                            <select name="categorie_id" class="form-control">
                                <option value="0">Semua Kategori</option>
                                <?php foreach ($categories as $cat): ?>
                                    <option value="<?php echo (int)$cat['id']; ?>" <?php if ($categorie_id == $cat['id']) echo 'selected'; ?>>
                                        <?php echo html_entity_decode(htmlspecialchars($cat['name'])); ?>
                                    </option>
                                <?php endforeach; ?>
                            </select>
                        </div>
                        <div class="col-md-2">
                            <select name="per_page" class="form-control">
                                <option value="10" <?php if ($per_page == 10) echo 'selected'; ?>>10 per halaman</option>
                                <option value="25" <?php if ($per_page == 25) echo 'selected'; ?>>25 per halaman</option>
                                <option value="50" <?php if ($per_page == 50) echo 'selected'; ?>>50 per halaman</option>
                                <option value="100" <?php if ($per_page == 100) echo 'selected'; ?>>100 per halaman</option>
                            </select>
                        </div>
                        <div class="col-md-3">
                            <button type="submit" class="btn btn-primary">Filter</button>
                            <a href="products.php" class="btn btn-default">Reset</a>
                            <a href="?export_excel=1&search=<?php echo urlencode($search_term); ?>&categorie_id=<?php echo $categorie_id; ?>" class="btn btn-success pull-right">Export Excel</a>
                        </div>
                    </div>
                </form>

                <div class="table-responsive">
                    <table class="table table-bordered table-striped">
                        <thead>
                            <tr>
                                <th class="text-center" style="width: 50px;">#</th>
                                <th style="width: 10%;">Part Number</th>
                                <th style="width: 15%;">Produk</th>
                                <th class="text-center" style="width: 10%;">Kategori</th>
                                <th class="text-center" style="width: 10%;">Kode COA</th>
                                <th class="text-center" style="width: 10%;">Qty</th>
                                <th class="text-center" style="width: 10%;">Harga Beli</th>
                                <th class="text-center" style="width: 10%;">Nilai Inventory</th>
                                <th class="text-center" style="width: 10%;">Lokasi</th>
                                <th class="text-center" style="width: 100px;">Aksi</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!empty($products)): ?>
                                <?php $i = $start + 1; ?>
                                <?php foreach ($products as $product) : ?>
                                    <tr class="<?php echo ($product['quantity'] < 5) ? 'bg-warning' : ''; ?>">
                                        <td class="text-center"><?php echo (int)$i++; ?></td>
                                        <td>
                                            <?php echo htmlspecialchars($product['part_number']); ?>
                                            <?php if ($product['quantity'] < 5): ?>
                                                <span class="label label-danger">Stok Rendah</span>
                                            <?php endif; ?>
                                        </td>
                                        <td><?php echo remove_junk($product['name']); ?></td>
                                        <td class="text-center"><?php echo html_entity_decode(remove_junk($product['categorie'])); ?></td>
                                        <td class="text-center"><?php echo htmlspecialchars($product['coa_kode']); ?></td>
                                        <td class="text-center"><?php echo number_format($product['quantity'], 2) . " " . htmlspecialchars($product['UoM']); ?></td>
                                        <td class="text-center">Rp <?php echo number_format($product['buy_price'], 2); ?></td>
                                        <td class="text-center">Rp <?php echo number_format($product['inventory_amount'], 2); ?></td>
                                        <td class="text-center"><?php echo htmlspecialchars($product['loc_id']); ?></td>
                                        <td class="text-center">
                                            <div class="btn-group">
                                                <button type="button" class="btn btn-xs btn-info" data-toggle="modal" data-target="#productDetailModal"
                                                    data-name="<?php echo htmlspecialchars($product['name']); ?>"
                                                    data-partnumber="<?php echo htmlspecialchars($product['part_number']); ?>"
                                                    data-category="<?php echo htmlspecialchars(html_entity_decode($product['categorie'])); ?>"
                                                    data-quantity="<?php echo number_format($product['quantity'], 2); ?>"
                                                    data-uom="<?php echo htmlspecialchars($product['UoM']); ?>"
                                                    data-price="<?php echo number_format($product['buy_price'], 2); ?>"
                                                    data-loc-id="<?php echo htmlspecialchars($product['loc_id']); ?>"
                                                    data-lead-time="<?php echo htmlspecialchars($product['lead_time']); ?>"
                                                    data-barcode-id="<?php echo htmlspecialchars($product['barcode_id']); ?>"
                                                    data-date="<?php echo htmlspecialchars($product['date']); ?>"
                                                    title="Lihat Detail">
                                                    <i class="glyphicon glyphicon-eye-open"></i>
                                                </button>
                                                <a href="edit_product/<?php echo (int)$product['id'];?>" class="btn btn-xs btn-warning" title="Edit" data-toggle="tooltip">
                                                    <i class="glyphicon glyphicon-pencil"></i>
                                                </a>
                                                <a href="delete_product/<?php echo (int)$product['id'];?>" class="btn btn-xs btn-danger" title="Hapus" data-toggle="tooltip" onclick="return confirm('Apakah Anda yakin ingin menghapus produk ini?');">
                                                    <i class="glyphicon glyphicon-remove"></i>
                                                </a>
                                            </div>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php else: ?>
                                <tr>
                                    <td colspan="10" class="text-center">Tidak ada produk ditemukan.</td>
                                </tr>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>

                <?php if ($total_pages > 1): ?>
                    <nav aria-label="Page navigation">
                        <ul class="pagination pull-right">
                            <li class="page-item <?php if ($page <= 1) echo 'disabled'; ?>">
                                <a class="page-link" href="?page=<?php echo $page - 1; ?>&per_page=<?php echo $per_page; ?>&categorie_id=<?php echo $categorie_id; ?>&search=<?php echo urlencode($search_term); ?>">Prev</a>
                            </li>
                            <?php for ($i = 1; $i <= $total_pages; $i++): ?>
                                <li class="page-item <?php if ($i == $page) echo 'active'; ?>">
                                    <a class="page-link" href="?page=<?php echo $i; ?>&per_page=<?php echo $per_page; ?>&categorie_id=<?php echo $categorie_id; ?>&search=<?php echo urlencode($search_term); ?>"><?php echo $i; ?></a>
                                </li>
                            <?php endfor; ?>
                            <li class="page-item <?php if ($page >= $total_pages) echo 'disabled'; ?>">
                                <a class="page-link" href="?page=<?php echo $page + 1; ?>&per_page=<?php echo $per_page; ?>&categorie_id=<?php echo $categorie_id; ?>&search=<?php echo urlencode($search_term); ?>">Next</a>
                            </li>
                        </ul>
                    </nav>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

<div class="modal fade" id="productDetailModal" tabindex="-1" role="dialog" aria-labelledby="productDetailModalLabel" aria-hidden="true">
    <div class="modal-dialog" role="document">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                    <span aria-hidden="true">&times;</span>
                </button>
                <h4 class="modal-title" id="productDetailModalLabel">Detail Produk</h4>
            </div>
            <div class="modal-body">
                <p><strong>Nama Produk:</strong> <span id="modal-product-name"></span></p>
                <p><strong>Part Number:</strong> <span id="modal-product-partnumber"></span></p>
                <p><strong>Kategori:</strong> <span id="modal-product-category"></span></p>
                <p><strong>Kode COA:</strong> <span id="modal-product-coa"></span></p> <p><strong>Kuantitas:</strong> <span id="modal-product-quantity"></span> <span id="modal-product-uom"></span></p>
                <p><strong>Harga Beli:</strong> Rp <span id="modal-product-price"></span></p>
                <p><strong>Lokasi ID:</strong> <span id="modal-product-loc-id"></span></p>
                <p><strong>Lead Time:</strong> <span id="modal-product-lead-time"></span></p>
                <p><strong>Barcode ID:</strong> <span id="modal-product-barcode-id"></span></p>
                <p><strong>Tanggal:</strong> <span id="modal-product-date"></span></p>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-secondary" data-dismiss="modal">Tutup</button>
            </div>
        </div>
    </div>
</div>

<script>
    // JavaScript to handle the modal
    $('#productDetailModal').on('show.bs.modal', function (event) {
        var button = $(event.relatedTarget);
        var name = button.data('name');
        var partnumber = button.data('partnumber');
        var category = button.data('category');
        var quantity = button.data('quantity');
        var uom = button.data('uom');
        var price = button.data('price');
        var locId = button.data('loc-id');
        var leadTime = button.data('lead-time');
        var barcodeId = button.data('barcode-id');
        var date = button.data('date');
        var coa_code = $(button).closest('tr').find('td:eq(4)').text(); // Ambil dari kolom tabel

        var modal = $(this);
        modal.find('#modal-product-name').text(name);
        modal.find('#modal-product-partnumber').text(partnumber);
        modal.find('#modal-product-category').text(category);
        modal.find('#modal-product-coa').text(coa_code); // Tampilkan kode COA pada modal
        modal.find('#modal-product-quantity').text(quantity);
        modal.find('#modal-product-uom').text(uom);
        modal.find('#modal-product-price').text(price);
        modal.find('#modal-product-loc-id').text(locId);
        modal.find('#modal-product-lead-time').text(leadTime);
        modal.find('#modal-product-barcode-id').text(barcodeId);
        modal.find('#modal-product-date').text(date);
    });
</script>

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