<?php
session_start();

if (!isset($_SESSION['user_id'])) {
    header("Location: login.php");
    exit();
}

require_once 'db_connection.php';

try {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="monthly_sales_by_buyer_' . date('Y-m-d') . '.csv"');
    
    $output = fopen('php://output', 'w');
    fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));
    
    fputcsv($output, array('Buyer', 'Month', 'Tilapia (kg)', 'Small Fish (kg)', 'Big Fish (kg)', 'Total Weight (kg)', 'Sales Amount', 'Payment'));
    
    // Get monthly sales by buyer
    $query = "SELECT 
        b.name as buyer_name,
        DATE_FORMAT(s.date, '%M %Y') as month_display,
        SUM(s.weight_tilapia) as total_tilapia,
        SUM(s.weight_small_fish) as total_small_fish,
        SUM(s.weight_big_fish) as total_big_fish,
        SUM(s.weight_tilapia + s.weight_small_fish + s.weight_big_fish) as total_weight,
        SUM(COALESCE(NULLIF(s.final_amount_override, 0), s.final_amount)) as total_sales,
        COALESCE((SELECT SUM(p.amount_paid) 
                  FROM payments p 
                  WHERE p.buyer_id = b.id 
                  AND DATE_FORMAT(p.date, '%Y-%m') = DATE_FORMAT(s.date, '%Y-%m')), 0) as total_payment
    FROM buyers b
    LEFT JOIN sales s ON b.id = s.buyer_id
    WHERE s.date IS NOT NULL
    GROUP BY b.id, DATE_FORMAT(s.date, '%Y-%m')
    ORDER BY DATE_FORMAT(s.date, '%Y-%m') DESC, b.name";
    
    $result = $conn->query($query);
    
    // Calculate grand totals
    $grandTotalTilapia = 0;
    $grandTotalSmallFish = 0;
    $grandTotalBigFish = 0;
    $grandTotalWeight = 0;
    $grandTotalSales = 0;
    $grandTotalPayment = 0;
    
    while ($row = $result->fetch_assoc()) {
        $grandTotalTilapia += $row['total_tilapia'];
        $grandTotalSmallFish += $row['total_small_fish'];
        $grandTotalBigFish += $row['total_big_fish'];
        $grandTotalWeight += $row['total_weight'];
        $grandTotalSales += $row['total_sales'];
        $grandTotalPayment += $row['total_payment'];
        
        fputcsv($output, array(
            $row['buyer_name'],
            $row['month_display'],
            number_format($row['total_tilapia'], 2),
            number_format($row['total_small_fish'], 2),
            number_format($row['total_big_fish'], 2),
            number_format($row['total_weight'], 2),
            number_format($row['total_sales'], 2),
            number_format($row['total_payment'], 2)
        ));
    }
    
    // Add grand total row
    fputcsv($output, array(
        'GRAND TOTAL',
        '',
        number_format($grandTotalTilapia, 2),
        number_format($grandTotalSmallFish, 2),
        number_format($grandTotalBigFish, 2),
        number_format($grandTotalWeight, 2),
        number_format($grandTotalSales, 2),
        number_format($grandTotalPayment, 2)
    ));

    fclose($output);
    $conn->close();
} catch (Exception $e) {
    header('Content-Type: text/plain');
    echo "Export error";
    error_log("Monthly sales by buyer export error: " . $e->getMessage());
}

exit();
?>