<?php
include 'includes/auth.php';
include 'db.php';
include 'includes/header.php';
require 'dompdf/vendor/autoload.php';

use Dompdf\Dompdf;

// --- Filters ---
$start_date = $_POST['start_date'] ?? '';
$end_date = $_POST['end_date'] ?? '';
$product_id = $_POST['product_id'] ?? '';
$category_id = $_POST['category_id'] ?? '';
$export_type = $_POST['export_type'] ?? ''; // pdf or excel

$where_sales = "WHERE 1=1";
if($start_date && $end_date){
    $where_sales .= " AND DATE(s.sale_date) BETWEEN '$start_date' AND '$end_date'";
}
if($product_id){
    $where_sales .= " AND si.product_id = ".intval($product_id);
}
if($category_id){
    $where_sales .= " AND p.category_id = ".intval($category_id);
}

// --- Fetch products ---
$products_sql = "
SELECT p.id, p.sku, p.name, c.name AS category_name, p.quantity, p.buying_price, p.selling_price,
       IFNULL(SUM(si.qty),0) AS qty_sold,
       IFNULL(SUM(si.qty*si.price),0) AS value_sold
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN sale_items si ON p.id = si.product_id
LEFT JOIN sales s ON si.sale_id = s.id $where_sales
GROUP BY p.id
ORDER BY p.name ASC
";

$result = $conn->query($products_sql);

// --- Export ---
if($export_type){
    $html = '<h3 style="text-align:center;">Inventory & Stock Report</h3>';
    $html .= '<table border="1" cellpadding="5" cellspacing="0" width="100%">';
    $html .= '<tr style="background:#000;color:#fff;">
                <th>SKU</th>
                <th>Product</th>
                <th>Category</th>
                <th>Current Qty</th>
                <th>Stock Value (MK)</th>
                <th>Qty Sold</th>
                <th>Sold Value (MK)</th>
              </tr>';
    $grand_stock_value = 0;
    $grand_sold_value = 0;
    while($row = $result->fetch_assoc()){
        $stock_value = $row['quantity'] * $row['buying_price'];
        $grand_stock_value += $stock_value;
        $grand_sold_value += $row['value_sold'];
        $html .= "<tr>
                    <td>{$row['sku']}</td>
                    <td>{$row['name']}</td>
                    <td>{$row['category_name']}</td>
                    <td>{$row['quantity']}</td>
                    <td>".number_format($stock_value,2)."</td>
                    <td>{$row['qty_sold']}</td>
                    <td>".number_format($row['value_sold'],2)."</td>
                  </tr>";
    }
    $html .= "<tr>
                <th colspan='4' style='text-align:right'>Grand Totals:</th>
                <th>".number_format($grand_stock_value,2)."</th>
                <th></th>
                <th>".number_format($grand_sold_value,2)."</th>
              </tr>";
    $html .= '</table>';

    if($export_type=='pdf'){
        $dompdf = new Dompdf();
        $dompdf->loadHtml($html);
        $dompdf->setPaper('A4','landscape');
        $dompdf->render();
        $dompdf->stream("inventory_report.pdf", ["Attachment"=>true]);
    } elseif($export_type=='excel'){
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="inventory_report.csv"');
        $output = fopen('php://output', 'w');
        fputcsv($output, ['SKU','Product','Category','Current Qty','Stock Value','Qty Sold','Sold Value']);
        $result->data_seek(0);
        while($row = $result->fetch_assoc()){
            $stock_value = $row['quantity'] * $row['buying_price'];
            fputcsv($output, [
                $row['sku'],
                $row['name'],
                $row['category_name'],
                $row['quantity'],
                $stock_value,
                $row['qty_sold'],
                $row['value_sold']
            ]);
        }
        fclose($output);
    }
    exit;
}

// --- Fetch products and categories for filters ---
$products_list = $conn->query("SELECT id,name FROM products ORDER BY name ASC");
$categories_list = $conn->query("SELECT id,name FROM categories ORDER BY name ASC");
?>

<div class="container mt-4">
    <h3 class="text-center">Inventory & Stock Report</h3>
    <hr>

    <!-- Filter Form -->
    <form method="POST" class="row g-3 mb-4">
        <div class="col-md-2">
            <input type="date" name="start_date" value="<?= htmlspecialchars($start_date) ?>" class="form-control">
        </div>
        <div class="col-md-2">
            <input type="date" name="end_date" value="<?= htmlspecialchars($end_date) ?>" class="form-control">
        </div>
        <div class="col-md-3">
            <select name="product_id" class="form-control">
                <option value="">-- All Products --</option>
                <?php while($p = $products_list->fetch_assoc()): ?>
                    <option value="<?= $p['id'] ?>" <?= ($product_id==$p['id']?'selected':'') ?>><?= $p['name'] ?></option>
                <?php endwhile; ?>
            </select>
        </div>
        <div class="col-md-3">
            <select name="category_id" class="form-control">
                <option value="">-- All Categories --</option>
                <?php while($c = $categories_list->fetch_assoc()): ?>
                    <option value="<?= $c['id'] ?>" <?= ($category_id==$c['id']?'selected':'') ?>><?= $c['name'] ?></option>
                <?php endwhile; ?>
            </select>
        </div>
        <div class="col-md-2">
            <button type="submit" class="btn btn-primary w-100">Filter</button>
        </div>
    </form>

    <!-- Export Buttons -->
    <form method="POST" class="mb-4">
        <input type="hidden" name="start_date" value="<?= htmlspecialchars($start_date) ?>">
        <input type="hidden" name="end_date" value="<?= htmlspecialchars($end_date) ?>">
        <input type="hidden" name="product_id" value="<?= htmlspecialchars($product_id) ?>">
        <input type="hidden" name="category_id" value="<?= htmlspecialchars($category_id) ?>">
        <button name="export_type" value="pdf" class="btn btn-danger">Export PDF</button>
        <button name="export_type" value="excel" class="btn btn-success">Export Excel</button>
    </form>

    <!-- Inventory Table -->
    <table class="table table-bordered table-striped">
        <thead class="bg-dark text-white">
            <tr>
                <th>SKU</th>
                <th>Product</th>
                <th>Category</th>
                <th>Current Qty</th>
                <th>Stock Value (MK)</th>
                <th>Qty Sold</th>
                <th>Sold Value (MK)</th>
            </tr>
        </thead>
        <tbody>
            <?php
            $grand_stock_value = 0;
            $grand_sold_value = 0;
            $result->data_seek(0);
            while($row = $result->fetch_assoc()){
                $stock_value = $row['quantity'] * $row['buying_price'];
                $grand_stock_value += $stock_value;
                $grand_sold_value += $row['value_sold'];

                // Conditional coloring for stock level
                if($row['quantity']<10){
                    $row_color = 'bg-danger text-white';
                } elseif($row['quantity']<20){
                    $row_color = 'bg-warning';
                } else {
                    $row_color = '';
                }

                echo "<tr class='$row_color'>
                        <td>{$row['sku']}</td>
                        <td>{$row['name']}</td>
                        <td>{$row['category_name']}</td>
                        <td>{$row['quantity']}</td>
                        <td>".number_format($stock_value,2)."</td>
                        <td>{$row['qty_sold']}</td>
                        <td>".number_format($row['value_sold'],2)."</td>
                      </tr>";
            }
            ?>
        </tbody>
        <tfoot>
            <tr>
                <th colspan="4" class="text-end">Grand Totals:</th>
                <th><?= number_format($grand_stock_value,2) ?></th>
                <th></th>
                <th><?= number_format($grand_sold_value,2) ?></th>
            </tr>
        </tfoot>
    </table>
</div>

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