<?php
include 'includes/auth.php';
include 'db.php';
require_once('tcpdf/tcpdf.php'); // path to TCPDF

$search = $_GET['search'] ?? '';
$category = $_GET['category'] ?? '';
$lowstock = $_GET['lowstock'] ?? '';

// ================= BUILD QUERY =================
$where = "1";

if($search){
    $search_safe = $conn->real_escape_string($search);
    $where .= " AND (LOWER(p.name) LIKE '%".strtolower($search_safe)."%' OR LOWER(p.sku) LIKE '%".strtolower($search_safe)."%')";
}

if($category){
    $category_safe = $conn->real_escape_string($category);
    $where .= " AND c.name = '$category_safe'";
}

if($lowstock){
    if($lowstock=='Low'){
        $where .= " AND COALESCE(SUM(ps.quantity),0) <= p.min_level";
    } else {
        $where .= " AND COALESCE(SUM(ps.quantity),0) > p.min_level";
    }
}

// ================= FETCH DATA =================
$sql = "
SELECT p.sku, p.name, c.name AS category_name, COALESCE(SUM(ps.quantity),0) AS total_qty,
       p.min_level, p.buying_price, p.selling_price, (p.selling_price - p.buying_price) AS profit,
       (p.selling_price * COALESCE(SUM(ps.quantity),0)) AS total_value
FROM products p
LEFT JOIN categories c ON p.category_id=c.id
LEFT JOIN product_stock ps ON p.id = ps.product_id
WHERE $where
GROUP BY p.id
ORDER BY p.name ASC
";

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

// ================= CREATE PDF =================
$pdf = new TCPDF();
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('Inventory System');
$pdf->SetTitle('Inventory Report');
$pdf->SetMargins(10,10,10);
$pdf->AddPage();

// HTML Table
$html = '<h2>📦 Inventory Report</h2>';
$html .= '<table border="1" cellpadding="4">
<tr style="background-color:#000;color:#fff;">
<th>SKU</th><th>Name</th><th>Category</th><th>Qty</th><th>Min</th>
<th>CP</th><th>SP</th><th>Profit</th><th>Total Value</th>
</tr>';

while($row = $result->fetch_assoc()){
    $html .= '<tr>
    <td>'.$row['sku'].'</td>
    <td>'.$row['name'].'</td>
    <td>'.$row['category_name'].'</td>
    <td>'.$row['total_qty'].'</td>
    <td>'.$row['min_level'].'</td>
    <td>'.number_format($row['buying_price'],2).'</td>
    <td>'.number_format($row['selling_price'],2).'</td>
    <td>'.number_format($row['profit'],2).'</td>
    <td>'.number_format($row['total_value'],2).'</td>
    </tr>';
}

$html .= '</table>';

$pdf->writeHTML($html, true, false, true, false, '');
$pdf->Output('inventory_'.date('Ymd_His').'.pdf', 'D');
exit;