<?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 passport extensions within date range
// Updated SQL query without care_of
$sql = "SELECT 
            pe.*,
            u_created.full_name as created_by_name,
            u_created.full_name as processed_by
        FROM passport_extensions pe
        LEFT JOIN users u_created ON pe.created_by = u_created.user_id
        WHERE pe.created_at BETWEEN ? AND ?";

if ($currency_filter !== 'all') {
    $sql .= " AND pe.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, 
        'total_payment' => 0, 
        'total_commission' => 0, 
        'count' => 0,
        'service_types' => [
            'extension' => 0,
            'renewal' => 0
        ],
        'status_counts' => [
            'Pending' => 0,
            'Processing' => 0,
            'Completed' => 0,
            'Cancelled' => 0
        ]
    ],
    'KES' => [
        'total_amount' => 0, 
        'total_payment' => 0, 
        'total_commission' => 0, 
        'count' => 0,
        'service_types' => [
            'extension' => 0,
            'renewal' => 0
        ],
        'status_counts' => [
            'Pending' => 0,
            'Processing' => 0,
            'Completed' => 0,
            'Cancelled' => 0
        ]
    ]
];

$extensions = [];
while ($row = $result->fetch_assoc()) {
    $extensions[] = $row;
    $totals[$row['currency']]['total_amount'] += $row['amount'];
    $totals[$row['currency']]['total_payment'] += $row['payment'];
    $totals[$row['currency']]['total_commission'] += $row['agency_fee'];
    $totals[$row['currency']]['count']++;
    $totals[$row['currency']]['service_types'][$row['service_type']]++;
    $totals[$row['currency']]['status_counts'][$row['status']]++;
}
?>

<!DOCTYPE html>
<html>
<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Passport Extensions 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">Passport Extensions 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 Extensions:</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>Total Payments:</th>
                                                                    <td><?php echo $currency . ' ' . number_format($total['total_payment'], 2); ?></td>
                                                                </tr>
                                                                <tr>
                                                                    <th>Total Commission:</th>
                                                                    <td><?php echo $currency . ' ' . number_format($total['total_commission'], 2); ?></td>
                                                                </tr>
                                                                <tr>
                                                                    <th colspan="2" class="pt-3">Service Types:</th>
                                                                </tr>
                                                                <?php foreach ($total['service_types'] as $type => $count): ?>
                                                                    <tr>
                                                                        <td><?php echo ucfirst($type); ?>:</td>
                                                                        <td><?php echo $count; ?></td>
                                                                    </tr>
                                                                <?php endforeach; ?>
                                                                <tr>
                                                                    <th colspan="2" class="pt-3">Status Breakdown:</th>
                                                                </tr>
                                                                <?php foreach ($total['status_counts'] as $status => $count): ?>
                                                                    <tr>
                                                                        <td><?php echo $status; ?>:</td>
                                                                        <td><?php echo $count; ?></td>
                                                                    </tr>
                                                                <?php endforeach; ?>
                                                            </table>
                                                        </div>
                                                    </div>
                                                </div>
                                            </div>
                                        <?php endforeach; ?>
                                    </div>

                                    <!-- Detailed Table -->
                                    <div class="table-responsive">
                                        <table class="table table-bordered table-striped">
                                            <thead>
                                                <tr>
                                                    <th>Extension ID</th>
                                                    <th>Date</th>
                                                    <th>Client Name</th>
                                                    <th>Reference No</th>
                                                    <th>Service Type</th>
                                                    <th>Duration</th>
                                                    <th>Expiry</th>
                                                    <th>Amount</th>
                                                    <th>Payment</th>
                                                    <th>Commission</th>
                                                    <th>Status</th>
                                                    <th>Processed By</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php foreach ($extensions as $ext): ?>
                                                    <tr>
                                                        <td><?php echo htmlspecialchars($ext['extension_id']); ?></td>
                                                        <td><?php echo date('d M Y', strtotime($ext['created_at'])); ?></td>
                                                        <td><?php echo htmlspecialchars($ext['client_name']); ?></td>
                                                        <td><?php echo htmlspecialchars($ext['reference_number']); ?></td>
                                                        <td><?php echo ucfirst($ext['service_type']); ?></td>
                                                        <td><?php echo $ext['validity_period'] . ' months'; ?></td>
                                                        <td><?php echo date('d M Y', strtotime($ext['expiry_date'])); ?></td>
                                                        <td><?php echo $ext['currency'] . ' ' . 
                                                            number_format($ext['amount'], 2); ?></td>
                                                        <td><?php echo $ext['currency'] . ' ' . 
                                                            number_format($ext['payment'], 2); ?></td>
                                                        <td><?php echo $ext['currency'] . ' ' . 
                                                            number_format($ext['agency_fee'], 2); ?></td>
                                                        <td>
                                                            <span class="badge bg-<?php
                                                                echo $ext['status'] === 'Completed' ? 'success' : 
                                                                    ($ext['status'] === 'Cancelled' ? 'danger' : 
                                                                    ($ext['status'] === 'Processing' ? 'warning' : 'info'));
                                                                ?>">
                                                                <?php echo ucfirst($ext['status']); ?>
                                                            </span>
                                                        </td>
                                                        <td><?php echo htmlspecialchars($ext['processed_by']); ?></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() {
        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';

        window.location.href = `export_extensions_report.php?start_date=${startDate}&end_date=${endDate}&currency=${currency}`;
    }
    </script>
</body>
</html>