<?php
require_once('tcpdf-main/tcpdf.php'); // path to TCPDF library
include 'db.php';

// Create new PDF document
$pdf = new TCPDF('L', PDF_UNIT, 'A4', true, 'UTF-8', false);
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('Inventory System');
$pdf->SetTitle('Perishable Products Report');
$pdf->SetHeaderData('', 0, 'Perishable Products Report', '');
$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
$pdf->SetMargins(10, 20, 10);
$pdf->SetHeaderMargin(10);
$pdf->SetFooterMargin(10);
$pdf->SetAutoPageBreak(TRUE, 15);
$pdf->SetFont('dejavusans', '', 10);
$pdf->AddPage();

// Fetch perishable data
$query = "
SELECT pp.*, d.name AS department
FROM perishable_products pp
LEFT JOIN departments d ON pp.department_id=d.id
ORDER BY pp.expiry_date ASC
";

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

// Build HTML table
$html = '<table border="1" cellpadding="4">
    <thead>
        <tr bgcolor="#f2f2f2">
            <th><b>Product</b></th>
            <th><b>Department</b></th>
            <th><b>Quantity</b></th>
            <th><b>Batch</b></th>
            <th><b>Expiry</b></th>
            <th><b>Recorded By</b></th>
            <th><b>Created At</b></th>
        </tr>
    </thead>
    <tbody>';

while($row = $result->fetch_assoc()){
    $html .= '<tr>
        <td>'.htmlspecialchars($row['product_name']).'</td>
        <td>'.htmlspecialchars($row['department']).'</td>
        <td>'.$row['quantity'].'</td>
        <td>'.$row['batch_number'].'</td>
        <td>'.$row['expiry_date'].'</td>
        <td>'.$row['recorded_by'].'</td>
        <td>'.$row['created_at'].'</td>
    </tr>';
}

$html .= '</tbody></table>';

// Output the HTML content
$pdf->writeHTML($html, true, false, true, false, '');

// Close and output PDF
$pdf->Output('perishable_products_report.pdf', 'I');
?>