<?php
include 'layouts/session.php';
include 'layouts/main.php';
include 'layouts/dbconn.php';

// Get date filters
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-01');
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-t');
$currency_filter = isset($_GET['currency']) ? $_GET['currency'] : 'all';

// Fetch cargo bookings within date range
$sql = "SELECT 
            cb.booking_number,
            cb.cargo_type,
            cb.booking_status,
            cb.payment_status,
            cb.booking_date,
            cb.total_amount,
            cb.paid_amount,
            cb.currency,
            (cb.total_amount - cb.paid_amount) as agency_fee,
            CONCAT(sender.first_name, ' ', sender.last_name) as sender_name,
            CONCAT(receiver.first_name, ' ', receiver.last_name) as receiver_name,
            cb.receiver_name as manual_receiver_name
        FROM cargo_bookings cb
        LEFT JOIN customers sender ON cb.sender_customer_id = sender.customer_id
        LEFT JOIN customers receiver ON cb.receiver_customer_id = receiver.customer_id
        WHERE cb.booking_date BETWEEN ? AND ?";

if ($currency_filter !== 'all') {
    $sql .= " AND currency = ?";
}

$stmt = $conn->prepare($sql);

if ($currency_filter !== 'all') {
    $stmt->bind_param("sss", $start_date, $end_date, $currency_filter);
} else {
    $stmt->bind_param("ss", $start_date, $end_date);
}

$stmt->execute();
$result = $stmt->get_result();

// Calculate totals
$totals = [
    'USD' => ['total_amount' => 0, 'paid_amount' => 0, 'agency_fee' => 0, 'count' => 0],
    'KES' => ['total_amount' => 0, 'paid_amount' => 0, 'agency_fee' => 0, 'count' => 0]
];

$bookings = [];
while ($row = $result->fetch_assoc()) {
    $bookings[] = $row;
    $totals[$row['currency']]['total_amount'] += $row['total_amount'];
    $totals[$row['currency']]['paid_amount'] += $row['paid_amount'];
    $totals[$row['currency']]['agency_fee'] += $row['agency_fee'];
    $totals[$row['currency']]['count']++;
}
?>

<!DOCTYPE html>
<html>
<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Cargo Bookings Report')); ?>
    <?php include 'layouts/head-css.php'; ?>
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <div class="row">
                        <div class="col-12">
                            <div class="card">
                                <div class="card-header d-flex justify-content-between align-items-center">
                                    <h4 class="card-title mb-0">Cargo Bookings Report</h4>
                                    <div>
                                        <button type="button" class="btn btn-success" onclick="exportToExcel()">
                                            <i class="ri-file-excel-line align-middle me-1"></i> Export
                                        </button>
                                    </div>
                                </div>
                                <div class="card-body">
                                    <!-- Filter Form -->
                                    <form method="GET" class="row g-3 mb-4">
                                        <div class="col-sm-3">
                                            <label class="form-label">Start Date</label>
                                            <input type="date" class="form-control" name="start_date" 
                                                   value="<?php echo $start_date; ?>">
                                        </div>
                                        <div class="col-sm-3">
                                            <label class="form-label">End Date</label>
                                            <input type="date" class="form-control" name="end_date" 
                                                   value="<?php echo $end_date; ?>">
                                        </div>
                                        <div class="col-sm-3">
                                            <label class="form-label">Currency</label>
                                            <select class="form-select" name="currency">
                                                <option value="all" <?php if($currency_filter == 'all') echo 'selected'; ?>>All</option>
                                                <option value="USD" <?php if($currency_filter == 'USD') echo 'selected'; ?>>USD</option>
                                                <option value="KES" <?php if($currency_filter == 'KES') echo 'selected'; ?>>KES</option>
                                            </select>
                                        </div>
                                        <div class="col-sm-3">
                                            <label class="form-label">&nbsp;</label>
                                            <button type="submit" class="btn btn-primary w-100">
                                                <i class="ri-filter-2-line align-middle me-1"></i> Filter
                                            </button>
                                        </div>
                                    </form>

                                    <!-- Summary Cards -->
                                    <div class="row mb-4">
                                        <?php foreach ($totals as $currency => $total): ?>
                                            <div class="col-sm-6">
                                                <div class="card border">
                                                    <div class="card-body">
                                                        <h5 class="card-title mb-3"><?php echo $currency; ?> Summary</h5>
                                                        <div class="table-responsive">
                                                            <table class="table table-borderless mb-0">
                                                                <tr>
                                                                    <th>Total Bookings:</th>
                                                                    <td><?php echo $total['count']; ?></td>
                                                                </tr>
                                                                <tr>
                                                                    <th>Total Amount:</th>
                                                                    <td><?php echo $currency . ' ' . number_format($total['total_amount'], 2); ?></td>
                                                                </tr>
                                                                <tr>
                                                                    <th>Paid Amount:</th>
                                                                    <td><?php echo $currency . ' ' . number_format($total['paid_amount'], 2); ?></td>
                                                                </tr>
                                                                <tr>
                                                                    <th>Agency Fee:</th>
                                                                    <td><?php echo $currency . ' ' . number_format($total['agency_fee'], 2); ?></td>
                                                                </tr>
                                                            </table>
                                                        </div>
                                                    </div>
                                                </div>
                                            </div>
                                        <?php endforeach; ?>
                                    </div>

                                    <!-- Detailed Table -->
                                    <div class="table-responsive">
                                        <table class="table table-bordered table-striped">
                                            <thead>
                                                <tr>
                                                    <th>Booking No</th>
                                                    <th>Date</th>
                                                    <th>Sender</th>
                                                    <th>Receiver</th>
                                                    <th>Type</th>
                                                    <th>Amount</th>
                                                    <th>Paid</th>
                                                    <th>Agency Fee</th>
                                                    <th>Status</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php foreach ($bookings as $booking): ?>
                                                    <tr>
                                                        <td><?php echo htmlspecialchars($booking['booking_number']); ?></td>
                                                        <td><?php echo date('d M Y', strtotime($booking['booking_date'])); ?></td>
                                                        <td><?php echo htmlspecialchars($booking['sender_name']); ?></td>
                                                        <td><?php echo htmlspecialchars($booking['receiver_customer_id'] ? 
                                                            $booking['receiver_name'] : $booking['manual_receiver_name']); ?></td>
                                                        <td><?php echo htmlspecialchars($booking['cargo_type']); ?></td>
                                                        <td><?php echo $booking['currency'] . ' ' . 
                                                            number_format($booking['total_amount'], 2); ?></td>
                                                        <td><?php echo $booking['currency'] . ' ' . 
                                                            number_format($booking['paid_amount'], 2); ?></td>
                                                        <td><?php echo $booking['currency'] . ' ' . 
                                                            number_format($booking['agency_fee'], 2); ?></td>
                                                        <td>
                                                            <span class="badge bg-<?php
                                                                echo $booking['booking_status'] === 'delivered' ? 'success' : 
                                                                    ($booking['booking_status'] === 'cancelled' ? 'danger' : 'warning');
                                                            ?>">
                                                                <?php echo ucfirst($booking['booking_status']); ?>
                                                            </span>
                                                        </td>
                                                    </tr>
                                                <?php endforeach; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="assets/js/app.js"></script>
    <script>
    function exportToExcel() {
        // Get current filter parameters
        const urlParams = new URLSearchParams(window.location.search);
        const startDate = urlParams.get('start_date') || '<?php echo date('Y-m-01'); ?>';
        const endDate = urlParams.get('end_date') || '<?php echo date('Y-m-t'); ?>';
        const currency = urlParams.get('currency') || 'all';

        // Redirect to export script with parameters
        window.location.href = `export_cargo_report.php?start_date=${startDate}&end_date=${endDate}&currency=${currency}`;
    }
    </script>
</body>
</html>