<?php
session_start();
include 'layouts/dbconn.php';

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

$start_date = $_GET['start_date'] ?? date('Y-m-01');
$end_date = $_GET['end_date'] ?? date('Y-m-t');
$property_id = $_GET['property_id'] ?? '';

// Revenue
$sql_revenue = "SELECT SUM(amount) as total FROM rent_payments 
                WHERE payment_status = 'COMPLETED' AND payment_date BETWEEN ? AND ?";
$params_rev = [$start_date, $end_date];
$types_rev = 'ss';

if (!empty($property_id)) {
    $sql_revenue = "SELECT SUM(rp.amount) as total FROM rent_payments rp
                    JOIN leases l ON rp.lease_id = l.lease_id
                    JOIN units u ON l.unit_id = u.unit_id
                    WHERE rp.payment_status = 'COMPLETED' 
                    AND rp.payment_date BETWEEN ? AND ?
                    AND u.property_id = ?";
    $params_rev[] = $property_id;
    $types_rev .= 'i';
}

$stmt_rev = $conn->prepare($sql_revenue);
$stmt_rev->bind_param($types_rev, ...$params_rev);
$stmt_rev->execute();
$result_rev = $stmt_rev->get_result();
$revenue = $result_rev->fetch_assoc()['total'] ?? 0;

// Expenses
$sql_expenses = "SELECT SUM(amount) as total FROM expenses 
                 WHERE status = 'paid' AND incurred_date BETWEEN ? AND ?";
$params_exp = [$start_date, $end_date];
$types_exp = 'ss';

if (!empty($property_id)) {
    $sql_expenses = "SELECT SUM(amount) as total FROM expenses 
                     WHERE status = 'paid' AND incurred_date BETWEEN ? AND ? AND property_id = ?";
    $params_exp[] = $property_id;
    $types_exp .= 'i';
}

$stmt_exp = $conn->prepare($sql_expenses);
$stmt_exp->bind_param($types_exp, ...$params_exp);
$stmt_exp->execute();
$result_exp = $stmt_exp->get_result();
$expenses = $result_exp->fetch_assoc()['total'] ?? 0;

$profit = $revenue - $expenses;
$profit_margin = $revenue > 0 ? ($profit / $revenue) * 100 : 0;

// Set headers for CSV download
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="pnl_report_' . date('Y-m-d') . '.csv"');

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

// Write headers
fputcsv($output, ['Profit & Loss Report']);
fputcsv($output, ['Period: ' . $start_date . ' to ' . $end_date]);
fputcsv($output, []);

// Write data
fputcsv($output, ['Item', 'Amount (KES)']);
fputcsv($output, ['REVENUE', number_format($revenue, 2)]);
fputcsv($output, ['Rental Income', number_format($revenue, 2)]);
fputcsv($output, ['EXPENSES', number_format($expenses, 2)]);
fputcsv($output, ['NET PROFIT/LOSS', number_format($profit, 2)]);
fputcsv($output, ['Profit Margin (%)', number_format($profit_margin, 2)]);

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