<?php
session_start();
include 'php/db_connection.php';
require('pdf/fpdf.php'); // Use regular FPDF, NOT tFPDF
if(!isset($_SESSION['user_id'])){
    header("Location: login.php");
    exit();
}

$pdf = new FPDF();
$pdf->AddPage();
$pdf->SetFont('Arial', 'B', 14);

// Report Title
$pdf->Cell(0, 10, 'Product Report (with Quantity-based Totals)', 0, 1, 'C');
$pdf->Ln(5);

// Table Headers
$pdf->SetFont('Arial', 'B', 10);
$pdf->Cell(10, 10, 'ID', 1);
$pdf->Cell(55, 10, 'Name', 1);
$pdf->Cell(25, 10, 'Product ID', 1,0,'C');
$pdf->Cell(15, 10, 'Qty', 1,0, 'C');
$pdf->Cell(20, 10, 'Buy Price', 1,0,'C');
$pdf->Cell(20, 10, 'Sell Price', 1,0,'C');
$pdf->Cell(45, 10, 'Expiration', 1,0,'C');
$pdf->Ln();

$pdf->SetFont('Arial', '', 10);
// Query data with JOIN
$query = "
    SELECT 
        p.id,
        p.Product_id,
        p.supplier_id,
        p.name,
        p.Quantity,
        pc.purchase_price,
        pc.price,
        p.Expiration_date
    FROM product AS p
    INNER JOIN product_cat AS pc ON p.Product_id = pc.Product_id
";

$result = mysqli_query($con, $query);

$total_purchase = 0;
$total_sale = 0;

while ($row = mysqli_fetch_assoc($result)) {
    $id = $row['id'];
    $product_id = $row['Product_id'];
    $supplier_id = $row['supplier_id'];
    $name = $row['name'];
    $quantity = (int)$row['Quantity'];
    $purchase_price = (float)$row['purchase_price'];
    $price = (float)$row['price'];
    $expiration = $row['Expiration_date'];

    $line_purchase_total = $quantity * $purchase_price;
    $line_sale_total = $quantity * $price;

    $total_purchase += $line_purchase_total;
    $total_sale += $line_sale_total;

    // Here you can output to PDF or table (as in earlier code)


    $pdf->Cell(10, 10, $id, 1);
    $pdf->Cell(55, 10, $name, 1);
    $pdf->Cell(25, 10, $product_id, 1,0,'C');
    $pdf->Cell(15, 10, $quantity, 1,0, 'C');
    $pdf->Cell(20, 10, number_format($purchase_price, 2), 1,0, 'C');
    $pdf->Cell(20, 10, number_format($price, 2), 1,0, 'C');
    $pdf->Cell(45, 10, $expiration, 1,0,'C');
    $pdf->Ln();
}

// Totals
$pdf->Ln(5);
$pdf->SetFont('Arial', 'B', 12);
$pdf->Cell(100, 10, 'Total Purchase Value:', 1);
$pdf->Cell(40, 10, number_format($total_purchase, 2), 1);
$pdf->Ln();
$pdf->Cell(100, 10, 'Total Sale Value:', 1);
$pdf->Cell(40, 10, number_format($total_sale, 2), 1);
$pdf->Ln();
$pdf->Cell(100, 10, 'Total Profit Value:', 1);
$pdf->Cell(40, 10, number_format($total_sale - $total_purchase , 2), 1);

// Output
$pdf->Output();
?>
