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

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

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

// Write CSV header
fputcsv($output, ['Product', 'Total Quantity', 'Total Credits', 'Total Cash']);

// Query all transaction details from the transactions table
$query = "SELECT trans_details FROM transactions";
$result = $connect->query($query);

$productData = [];

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $transDetails = json_decode($row['trans_details'], true);
        
        if (is_array($transDetails)) {
            foreach ($transDetails as $detail) {
                $product = isset($detail['productCode']) ? $detail['productCode'] : (isset($detail['product']) ? $detail['product'] : 'Unknown');
                $quantity = isset($detail['quantity']) ? intval($detail['quantity']) : 0;
                $cashAmount = isset($detail['amount']) ? intval($detail['amount']) : 0;
                $creditAmount = isset($detail['c_amount']) ? intval($detail['c_amount']) : 0;
                
                if (!isset($productData[$product])) {
                    $productData[$product] = [
                        'product' => $product,
                        'totalQty' => 0,
                        'totalCredits' => 0,
                        'totalCash' => 0
                    ];
                }
                
                $productData[$product]['totalQty'] += $quantity;
                $productData[$product]['totalCredits'] += $creditAmount;
                $productData[$product]['totalCash'] += $cashAmount;
            }
        }
    }
}

// Convert associative array to indexed array and sort by totalQty in descending order
$response = array_values($productData);
usort($response, function($a, $b) {
    return $b['totalQty'] - $a['totalQty'];
});

// Write each row to CSV
foreach ($response as $row) {
    fputcsv($output, $row);
}

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