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

// 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'] : '';

// 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 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%'";
}
$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 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%'";
}
$sql .= " LIMIT $per_page OFFSET $offset";
$sales = find_by_sql($sql);

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

<form method="get" action="sales_forecast_trend.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="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>
    <button type="button" class="btn btn-success" id="exportCSVBtn">Export to CSV</button>
</form>
<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']
        ]; // Header row

        <?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 Forecast Trend");

        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_forecast_trend.xlsx');
    });

    document.getElementById('exportCSVBtn').addEventListener('click', function() {
        var csv = 'Part Number,Product Name,Category Name,Quantity,Date,Remark,Location\n';

        <?php foreach ($sales as $item): ?>
            csv += '<?php echo $item["part_number"]; ?>,';
            csv += '<?php echo $item["product_name"]; ?>,';
            csv += '<?php echo $item["categorie_name"]; ?>,';
            csv += '<?php echo $item["qty"]; ?>,';
            csv += '<?php echo $item["date"]; ?>,';
            csv += '<?php echo $item["remark"]; ?>,';
            csv += '<?php echo $item["loc"]; ?>\n';
        <?php endforeach; ?>

        var blob = new Blob([csv], {
            type: 'text/csv;charset=utf-8;'
        });
        var link = document.createElement("a");
        if (link.download !== undefined) {
            var url = URL.createObjectURL(blob);
            link.setAttribute("href", url);
            link.setAttribute("download", 'sales_forecast_trend.csv');
            link.style.visibility = 'hidden';
            document.body.appendChild(link);
            link.click();
            document.body.removeChild(link);
        }
    });
</script>

<!-- Display the results -->
<?php if (!empty($sales)) : ?>
    <table class="table table-bordered">
        <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 $sale): ?>
                <tr>
                    <td><?php echo remove_junk($sale['part_number']); ?></td>
                    <td><?php echo remove_junk($sale['product_name']); ?></td>
                    <td><?php echo remove_junk($sale['categorie_name']); ?></td>
                    <td><?php echo remove_junk($sale['qty']); ?></td>
                    <td><?php echo remove_junk($sale['date']); ?></td>
                    <td><?php echo remove_junk($sale['remark']); ?></td>
                    <td><?php echo remove_junk($sale['loc']); ?></td>
                </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
<?php else: ?>
    <p>No sales found for the given criteria.</p>
<?php endif; ?>

<!-- Pagination Controls -->
<nav aria-label="Page navigation">
    <ul class="pagination">
        <?php if ($current_page > 1): ?>
            <li class="page-item"><a class="page-link"
                    href="?start_date=<?php echo $start_date; ?>&end_date=<?php echo $end_date; ?>&part_number=<?php echo $part_number; ?>&per_page=<?php echo $per_page; ?>&page=<?php echo $current_page - 1; ?>">Previous</a>
            </li>
        <?php endif; ?>
        <?php for ($i = 1; $i <= $total_pages; $i++): ?>
            <li class="page-item <?php if ($current_page == $i) echo 'active'; ?>"><a class="page-link"
                    href="?start_date=<?php echo $start_date; ?>&end_date=<?php echo $end_date; ?>&part_number=<?php echo $part_number; ?>&per_page=<?php echo $per_page; ?>&page=<?php echo $i; ?>"><?php echo $i; ?></a>
            </li>
        <?php endfor; ?>
        <?php if ($current_page < $total_pages): ?>
            <li class="page-item"><a class="page-link"
                    href="?start_date=<?php echo $start_date; ?>&end_date=<?php echo $end_date; ?>&part_number=<?php echo $part_number; ?>&per_page=<?php echo $per_page; ?>&page=<?php echo $current_page + 1; ?>">Next</a>
            </li>
        <?php endif; ?>
    </ul>
</nav>

<!-- Include Chart.js for visualizing the sales trends -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<canvas id="salesTrendChart" width="400" height="200"></canvas>
<script>
    var ctx = document.getElementById('salesTrendChart').getContext('2d');
    var salesData = {
        labels: [<?php foreach ($sales as $item) {
                        echo "'" . $item['date'] . "',";
                    } ?>],
        datasets: [{
            label: 'Sales Quantity',
            data: [<?php foreach ($sales as $item) {
                        echo $item['qty'] . ",";
                    } ?>],
            backgroundColor: 'rgba(75, 192, 192, 0.2)',
            borderColor: 'rgba(75, 192, 192, 1)',
            borderWidth: 1
        }]
    };

    var salesTrendChart = new Chart(ctx, {
        type: 'line',
        data: salesData,
        options: {
            scales: {
                y: {
                    beginAtZero: true
                }
            }
        }
    });
</script>

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