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

// Set default start and end dates to the beginning of the current month and today's date
$default_start_date = date('Y-m-01');
$default_end_date = date('Y-m-d');

// Initialize filter values with default dates
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : $default_start_date;
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : $default_end_date;
$part_number = isset($_GET['part_number']) ? $_GET['part_number'] : '';
$categorie_id = isset($_GET['categorie_id']) ? (int)$_GET['categorie_id'] : '';
$remark_keyword = isset($_GET['remark_keyword']) ? $_GET['remark_keyword'] : '';

// Pagination settings
$per_page = isset($_GET['per_page']) ? (int)$_GET['per_page'] : 200;
$current_page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$offset = ($current_page - 1) * $per_page;

// Construct SQL query with filters
$sql = "SELECT COUNT(*) as total FROM sales_makassar s
        LEFT JOIN products p ON p.id = s.product_id
        LEFT JOIN categories c ON c.id = p.categorie_id
        WHERE s.date BETWEEN '$start_date' AND '$end_date'";
if (!empty($part_number)) {
    $sql .= " AND s.part_number LIKE '%$part_number%'";
}
if (!empty($categorie_id)) {
    $sql .= " AND p.categorie_id = '$categorie_id'";
}
if (!empty($remark_keyword)) {
    $sql .= " AND s.remark LIKE '%$remark_keyword%'";
}
$total_result = find_by_sql($sql);
$total_records = $total_result[0]['total'];
$total_pages = ceil($total_records / $per_page);

$sql = "SELECT s.part_number, p.name AS product_name, c.name AS categorie_name, s.qty, s.date, s.remark, s.loc
        FROM sales_makassar s
        LEFT JOIN products p ON p.part_number = s.part_number
        LEFT JOIN categories c ON c.id = p.categorie_id
        WHERE s.date BETWEEN '$start_date' AND '$end_date'";
if (!empty($part_number)) {
    $sql .= " AND s.part_number LIKE '%$part_number%'";
}
if (!empty($categorie_id)) {
    $sql .= " AND p.categorie_id = '$categorie_id'";
}
if (!empty($remark_keyword)) {
    $sql .= " AND s.remark LIKE '%$remark_keyword%'";
}
$sql .= " LIMIT $per_page OFFSET $offset";
$sales = find_by_sql($sql);

include_once('layouts/header.php');

// Get all categories for the dropdown filter
$all_categories = find_all('categories');

// Calculate total quantity
$total_quantity = 0;
foreach ($sales as $item) {
    $total_quantity += $item['qty'];
}
?>

<form method="get" action="sales_makassar.php">
    <label for="start_date">Start Date:</label>
    <input type="date" name="start_date" value="<?php echo $start_date; ?>">
    <label for="end_date">End Date:</label>
    <input type="date" name="end_date" value="<?php echo $end_date; ?>">
    <label for="part_number">Part Number:</label>
    <input type="text" name="part_number" value="<?php echo $part_number; ?>">
    <label for="remark_keyword">Remark Keyword:</label>
    <input type="text" name="remark_keyword" value="<?php echo $remark_keyword; ?>">
    <label for="categorie_id">Category:</label>
    <select name="categorie_id">
        <option value="">All Categories</option>
        <?php foreach ($all_categories as $category): ?>
            <option value="<?php echo (int)$category['id']; ?>" <?php if ($category['id'] == $categorie_id) echo 'selected'; ?>>
                <?php echo $category['name']; ?>
            </option>
        <?php endforeach; ?>
    </select>
    <label for="per_page">Records Per Page:</label>
    <input type="number" name="per_page" value="<?php echo $per_page; ?>" min="1">
    <button type="submit">Filter</button>
    <button type="button" class="btn btn-success" id="exportExcelBtn">Export to Excel</button>
</form>

<!-- Display Total Quantity -->
<div class="total-quantity">
    <p><strong>Total Quantity:</strong> <?php echo $total_quantity; ?></p>
</div>
<script>
    document.getElementById('exportExcelBtn').addEventListener('click', function() {
        var wb = XLSX.utils.book_new();
        var ws_data = [
            ['Part Number', 'Product Name', 'Category Name', 'Quantity', 'Date', 'Remark', 'Location']
        ];

        <?php foreach ($sales as $item): ?>
            ws_data.push([
                '<?php echo $item["part_number"]; ?>',
                '<?php echo $item["product_name"]; ?>',
                '<?php echo $item["categorie_name"]; ?>',
                '<?php echo $item["qty"]; ?>',
                '<?php echo $item["date"]; ?>',
                '<?php echo $item["remark"]; ?>',
                '<?php echo $item["loc"]; ?>'
            ]);
        <?php endforeach; ?>

        var ws = XLSX.utils.aoa_to_sheet(ws_data);
        XLSX.utils.book_append_sheet(wb, ws, "Sales Report");

        var wbout = XLSX.write(wb, {
            bookType: 'xlsx',
            type: 'binary'
        });

        function s2ab(s) {
            var buf = new ArrayBuffer(s.length);
            var view = new Uint8Array(buf);
            for (var i = 0; i < s.length; i++) view[i] = s.charCodeAt(i) & 0xFF;
            return buf;
        }

        saveAs(new Blob([s2ab(wbout)], {
            type: "application/octet-stream"
        }), 'sales_report_makassar.xlsx');
    });
</script>

<!-- Display Sales Table -->
<table class="table table-bordered table-striped">
    <thead>
        <tr>
            <th>Part Number</th>
            <th>Product Name</th>
            <th>Category Name</th>
            <th>Quantity</th>
            <th>Date</th>
            <th>Remark</th>
            <th>Location</th>
        </tr>
    </thead>
    <tbody>
        <?php foreach ($sales as $item): ?>
            <tr>
                <td><?php echo $item['part_number']; ?></td>
                <td><?php echo $item['product_name']; ?></td>
                <td><?php echo $item['categorie_name']; ?></td>
                <td><?php echo $item['qty']; ?></td>
                <td><?php echo $item['date']; ?></td>
                <td><?php echo $item['remark']; ?></td>
                <td><?php echo $item['loc']; ?></td>
            </tr>
        <?php endforeach; ?>
    </tbody>
</table>

<!-- Pagination Links -->
<div class="pagination">
    <ul class="pagination">
        <?php if ($current_page > 1): ?>
            <li><a href="sales_makassar.php?page=<?php echo $current_page - 1; ?>">Previous</a></li>
        <?php endif; ?>
        <?php for ($i = 1; $i <= $total_pages; $i++): ?>
            <li <?php if ($i == $current_page) echo 'class="active"'; ?>>
                <a href="sales_makassar.php?page=<?php echo $i; ?>"><?php echo $i; ?></a>
            </li>
        <?php endfor; ?>
        <?php if ($current_page < $total_pages): ?>
            <li><a href="sales_makassar.php?page=<?php echo $current_page + 1; ?>">Next</a></li>
        <?php endif; ?>
    </ul>
</div>

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