<?php
// CSV export — expenses and income for any date range (Section 8, C1,
// Phase 11). No page of its own; invoked from Settings with ?date_from=
// &date_to=. Missing/invalid dates mean "no restriction on that side" —
// this is the full data escape hatch, so it defaults to everything rather
// than silently narrowing to the current month.

require_once __DIR__ . '/includes/auth.php';
require_login();
require_admin(); // Export is Motaher-only (Section 3 role split).

$dateFrom = trim((string) ($_GET['date_from'] ?? ''));
$dateTo = trim((string) ($_GET['date_to'] ?? ''));

if ($dateFrom !== '' && DateTime::createFromFormat('Y-m-d', $dateFrom) === false) {
    $dateFrom = '';
}
if ($dateTo !== '' && DateTime::createFromFormat('Y-m-d', $dateTo) === false) {
    $dateTo = '';
}

$db = get_db();

// Distinct parameter names per branch of the UNION — PDO's native prepared
// statements (PDO::ATTR_EMULATE_PREPARES = false) don't allow binding the
// same named parameter twice (see the set_setting() fix, includes/helpers.php).
$whereExpense = [];
$whereIncome = [];
$params = [];

if ($dateFrom !== '') {
    $whereExpense[] = 'e.expense_date >= :exp_start';
    $whereIncome[] = 'i.income_date >= :inc_start';
    $params['exp_start'] = $dateFrom;
    $params['inc_start'] = $dateFrom;
}
if ($dateTo !== '') {
    $whereExpense[] = 'e.expense_date <= :exp_end';
    $whereIncome[] = 'i.income_date <= :inc_end';
    $params['exp_end'] = $dateTo;
    $params['inc_end'] = $dateTo;
}

$expenseWhereSql = $whereExpense ? ('WHERE ' . implode(' AND ', $whereExpense)) : '';
$incomeWhereSql = $whereIncome ? ('WHERE ' . implode(' AND ', $whereIncome)) : '';

$stmt = $db->prepare(
    "SELECT 'Expense' AS type, e.expense_date AS date, e.amount, c.name AS category,
            pm.name AS payment_method, e.description, u.display_name AS entered_by
     FROM expenses e
     JOIN categories c ON c.id = e.category_id
     LEFT JOIN payment_methods pm ON pm.id = e.payment_method_id
     JOIN users u ON u.id = e.user_id
     $expenseWhereSql

     UNION ALL

     SELECT 'Income' AS type, i.income_date AS date, i.amount, c.name AS category,
            NULL AS payment_method, i.description, u.display_name AS entered_by
     FROM incomes i
     JOIN categories c ON c.id = i.category_id
     JOIN users u ON u.id = i.user_id
     $incomeWhereSql

     ORDER BY date ASC, type ASC"
);
$stmt->execute($params);

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="monthly_ledger_export_' . date('Y-m-d') . '.csv"');

$out = fopen('php://output', 'w');
// UTF-8 BOM so Excel correctly detects the encoding (Chinese category/app names, ¥).
fwrite($out, "\xEF\xBB\xBF");
fputcsv($out, ['Type', 'Date', 'Amount', 'Category', 'Payment Method', 'Description', 'Entered By']);

// Raw ISO dates and plain decimal amounts (not format_money()'d) so Excel
// parses them as real dates/numbers, not text — that's the whole point of
// a CSV escape hatch (Section 5: "always leave the app with your data").
foreach ($stmt as $row) {
    fputcsv($out, [
        $row['type'],
        $row['date'],
        $row['amount'],
        $row['category'],
        $row['payment_method'] ?? '',
        $row['description'] ?? '',
        $row['entered_by'],
    ]);
}
fclose($out);
