<?php
require('config.php');

// Set headers to force CSV download
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=stock_data.csv');

// Open output stream for writing CSV
$output = fopen('php://output', 'w');

// Write CSV headers
fputcsv($output, [
    'Stock ID', 'Stock Code', 'Product ID', 'Product Name', 'Quantity In Stock', 
    'Shop Quantity', 'Product Price', 'Store Price', 'Product Size', 
    'Added By Name', 'Added By Email', 'Added By Phone'
]);

// Query to fetch stock data
$query = "
    SELECT 
        store_stock.id, 
        store_stock.productID, 
        products.productName, 
        products.productCode, 
        store_stock.quantity AS QuantityInStock, 
        COALESCE(stock.quantity, 0) AS ShopQuantity, 
        store_stock.productPrice, 
        store_stock.store_price, 
        store_stock.size AS productSize, 
        users.name AS addedByName,
        users.email AS addedByEmail,
        users.phone AS addedByPhone
    FROM 
        store_stock
    JOIN 
        products ON store_stock.productID = products.id
    JOIN 
        users ON store_stock.addedBy = users.id
    LEFT JOIN 
        stock ON store_stock.productID = stock.productID;
";

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

// Check if there are results
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        // Format the stock code
        $stockCode = $row["id"] . " - " . $row["productCode"];

        // Write data row to CSV
        fputcsv($output, [
            $row["id"],
            $stockCode,
            $row["productID"],
            $row["productName"],
            $row["QuantityInStock"],
            $row["ShopQuantity"],
            $row["productPrice"],
            $row["store_price"],
            $row["productSize"],
            $row["addedByName"],
            $row["addedByEmail"],
            $row["addedByPhone"]
        ]);
    }
} else {
    // No data found message
    fputcsv($output, ["No stock records found."]);
}

// Close output stream
fclose($output);
exit();
?>
