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

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

$allowed_roles = ['SUPER_ADMIN', 'HOSPITAL_ADMIN', 'ACCOUNTANT'];
if (!in_array($_SESSION['role_code'], $allowed_roles)) {
    header('Location: ../index.php');
    exit();
}

// Fetch bulk payments
$query = "SELECT bp.payment_number, bp.payment_date, bp.payment_period_start, bp.payment_period_end,
          bp.total_amount, bp.allocated_amount, bp.unallocated_amount,
          bp.payment_method, bp.transaction_reference, bp.allocation_status, bp.reconciliation_status,
          bp.created_at,
          esp.provider_name, esp.provider_code,
          receiver.full_name as received_by_name
          FROM bulk_payments bp
          JOIN external_service_providers esp ON bp.provider_id = esp.provider_id
          LEFT JOIN users receiver ON bp.received_by = receiver.user_id
          ORDER BY bp.payment_date DESC";

$result = mysqli_query($conn, $query);

// Set headers for CSV download
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=bulk_payments_export_' . date('Y-m-d_His') . '.csv');

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

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

// Add CSV headers
fputcsv($output, [
    'Payment Number',
    'Provider Name',
    'Provider Code',
    'Payment Date',
    'Period Start',
    'Period End',
    'Total Amount (KES)',
    'Allocated Amount (KES)',
    'Unallocated Amount (KES)',
    'Allocation %',
    'Payment Method',
    'Transaction Reference',
    'Allocation Status',
    'Reconciliation Status',
    'Received By',
    'Created At'
]);

// Add data rows
if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        $allocation_percentage = ($row['total_amount'] > 0) 
            ? round(($row['allocated_amount'] / $row['total_amount']) * 100, 2) 
            : 0;
        
        fputcsv($output, [
            $row['payment_number'],
            $row['provider_name'],
            $row['provider_code'],
            date('Y-m-d', strtotime($row['payment_date'])),
            date('Y-m-d', strtotime($row['payment_period_start'])),
            date('Y-m-d', strtotime($row['payment_period_end'])),
            number_format($row['total_amount'], 2),
            number_format($row['allocated_amount'], 2),
            number_format($row['unallocated_amount'], 2),
            $allocation_percentage . '%',
            str_replace('_', ' ', $row['payment_method']),
            $row['transaction_reference'],
            $row['allocation_status'],
            $row['reconciliation_status'],
            $row['received_by_name'],
            date('Y-m-d H:i:s', strtotime($row['created_at']))
        ]);
    }
} else {
    // Add empty row if no data
    fputcsv($output, ['No bulk payments found']);
}

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