<?php
require_once __DIR__ . '/db/config.php';

try {
    $pdo = db();
    $stmt = $pdo->query("
        SELECT 
            e.expense_date,
            e.title,
            c.name as category_name,
            a.name as account_name,
            u.name as user_name,
            e.expense_type,
            e.amount,
            e.currency
        FROM 
            expenses e
        LEFT JOIN 
            categories c ON e.category_id = c.id
        LEFT JOIN 
            users u ON e.user_id = u.id
        LEFT JOIN 
            accounts a ON e.account_id = a.id
        ORDER BY 
            e.expense_date DESC
    ");

    $filename = "expenses_" . date('Y-m-d') . ".csv";

    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=' . $filename);

    $output = fopen('php://output', 'w');

    // Add UTF-8 BOM to prevent issues with special characters in Excel
    fputs($output, "\xEF\xBB\xBF");

    // Header row
    fputcsv($output, ['Date', 'Title', 'Category', 'Account', 'User', 'Type', 'Amount', 'Currency']);

    // Data rows
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        fputcsv($output, $row);
    }

    fclose($output);
    exit;

} catch (PDOException $e) {
    error_log("Export Error: " . $e->getMessage());
    die("An error occurred during export. Please check the logs.");
}