<?php
require_once '../config/db.php';
require_once '../includes/auth.php';
require_once '../includes/functions.php';

requireLogin();
requireAdmin();

$year = intval($_GET['year'] ?? date('Y'));
$month = intval($_GET['month'] ?? date('m'));

// Get data
$loans = fetchAll($conn, 
    "SELECT l.*, b.first_name, b.last_name FROM loans l 
     JOIN borrowers b ON l.borrower_id = b.id 
     ORDER BY l.created_at DESC"
);

$payments = fetchAll($conn, 
    "SELECT p.*, l.loan_amount, b.first_name, b.last_name FROM payments p 
     JOIN loans l ON p.loan_id = l.id 
     JOIN borrowers b ON l.borrower_id = b.id 
     WHERE YEAR(p.payment_date) = ? AND MONTH(p.payment_date) = ? 
     ORDER BY p.payment_date DESC",
    [$year, $month],
    "ii"
);

// Create CSV data
$filename = 'loan-report-' . $year . '-' . str_pad($month, 2, '0', STR_PAD_LEFT) . '.csv';

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

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

// Write BOM for UTF-8
fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));

// Write header
fputcsv($output, [
    'Report Date',
    date('F Y', mktime(0, 0, 0, $month, 1, $year))
]);
fputcsv($output, []);

// Payments Section
fputcsv($output, ['PAYMENT TRANSACTIONS']);
fputcsv($output, ['Receipt #', 'Borrower', 'Payment Date', 'Amount', 'Penalty Paid', 'Method']);

foreach ($payments as $payment) {
    fputcsv($output, [
        $payment['receipt_number'],
        $payment['first_name'] . ' ' . $payment['last_name'],
        $payment['payment_date'],
        $payment['amount'],
        $payment['penalty_paid'],
        $payment['payment_method']
    ]);
}

fputcsv($output, []);
fputcsv($output, ['TOTAL PAYMENTS', count($payments), '', array_sum(array_column($payments, 'amount'))]);
fputcsv($output, []);
fputcsv($output, []);

// Loans Section
fputcsv($output, ['ACTIVE LOANS']);
fputcsv($output, ['Borrower', 'Loan Amount', 'Balance', 'Interest Rate', 'Status', 'Due Date']);

$active_loans = array_filter($loans, function($loan) {
    return in_array($loan['status'], ['pending', 'approved']);
});

foreach ($active_loans as $loan) {
    fputcsv($output, [
        $loan['first_name'] . ' ' . $loan['last_name'],
        $loan['loan_amount'],
        $loan['balance'],
        $loan['interest_rate'] . '%',
        ucfirst($loan['status']),
        $loan['due_date']
    ]);
}

fputcsv($output, []);
fputcsv($output, ['TOTAL ACTIVE LOANS', count($active_loans), '', array_sum(array_column($active_loans, 'loan_amount'))]);

fclose($output);
exit();
?>
