<?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 ticket details within date range
$sql = "SELECT 
            td.*,
            u_created.full_name as created_by_name,
            b.booking_code,
            b.booking_date,
            b.status as booking_status
        FROM ticket_details td
        LEFT JOIN users u_created ON td.created_by = u_created.user_id
        LEFT JOIN bookings b ON td.booking_id = b.booking_id
        WHERE DATE(td.created_date) BETWEEN ? AND ?";

if ($currency_filter !== 'all') {
    $sql .= " AND td.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_ticket_cost' => 0,
        'total_agency_fee' => 0,
        'total_amount' => 0,
        'count' => 0,
        'booking_class' => [
            'Y' => 0, // Economy
            'L' => 0, // Business
            'A' => 0  // First
        ],
        'status_counts' => [
            'Active' => 0,
            'Cancelled' => 0,
            'Used' => 0
        ]
    ],
    'KES' => [
        'total_ticket_cost' => 0,
        'total_agency_fee' => 0,
        'total_amount' => 0,
        'count' => 0,
        'booking_class' => [
            'Y' => 0,
            'L' => 0,
            'A' => 0
        ],
        'status_counts' => [
            'Active' => 0,
            'Cancelled' => 0,
            'Used' => 0
        ]
    ]
];

$tickets = [];
while ($row = $result->fetch_assoc()) {
    $tickets[] = $row;
    $totals[$row['currency']]['total_ticket_cost'] += $row['ticket_cost'];
    $totals[$row['currency']]['total_agency_fee'] += $row['agency_fee'];
    $totals[$row['currency']]['total_amount'] += ($row['ticket_cost'] + $row['agency_fee']);
    $totals[$row['currency']]['count']++;
    $totals[$row['currency']]['booking_class'][$row['booking_class']]++;
    $totals[$row['currency']]['status_counts'][$row['status']]++;
}
?>

<!DOCTYPE html>
<html>
<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Ticket Sales 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">Ticket Sales 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 Tickets:</th>
                                                                    <td><?php echo $total['count']; ?></td>
                                                                </tr>
                                                                <tr>
                                                                    <th>Total Ticket Cost:</th>
                                                                    <td><?php echo $currency . ' ' . number_format($total['total_ticket_cost'], 2); ?></td>
                                                                </tr>
                                                                <tr>
                                                                    <th>Total Agency Fees:</th>
                                                                    <td><?php echo $currency . ' ' . number_format($total['total_agency_fee'], 2); ?></td>
                                                                </tr>
                                                                <tr>
                                                                    <th>Total Amount:</th>
                                                                    <td><?php echo $currency . ' ' . number_format($total['total_amount'], 2); ?></td>
                                                                </tr>
                                                                <tr>
                                                                    <th colspan="2" class="pt-3">Booking Classes:</th>
                                                                </tr>
                                                                <tr>
                                                                    <td>Economy (Y):</td>
                                                                    <td><?php echo $total['booking_class']['Y']; ?></td>
                                                                </tr>
                                                                <tr>
                                                                    <td>Business (L):</td>
                                                                    <td><?php echo $total['booking_class']['L']; ?></td>
                                                                </tr>
                                                                <tr>
                                                                    <td>First (A):</td>
                                                                    <td><?php echo $total['booking_class']['A']; ?></td>
                                                                </tr>
                                                                <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>Ticket Ref</th>
                                                    <th>Date</th>
                                                    <th>Customer Name</th>
                                                    <th>Airline</th>
                                                    <th>Flight Route</th>
                                                    <th>Class</th>
                                                    <th>Ticket Cost</th>
                                                    <th>Agency Fee</th>
                                                    <th>Total Amount</th>
                                                    <th>Status</th>
                                                    <th>Processed By</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php foreach ($tickets as $ticket): ?>
                                                    <tr>
                                                        <td><?php echo htmlspecialchars($ticket['ticket_ref']); ?></td>
                                                        <td><?php echo date('d M Y', strtotime($ticket['created_date'])); ?></td>
                                                        <td><?php echo htmlspecialchars($ticket['customer_name']); ?></td>
                                                        <td><?php echo htmlspecialchars($ticket['airline']); ?></td>
                                                        <td><?php echo htmlspecialchars($ticket['flight_route']); ?></td>
                                                        <td>
                                                            <?php 
                                                                echo $ticket['booking_class'] === 'Y' ? 'Economy' : 
                                                                    ($ticket['booking_class'] === 'L' ? 'Business' : 'First');
                                                            ?>
                                                        </td>
                                                        <td><?php echo $ticket['currency'] . ' ' . 
                                                            number_format($ticket['ticket_cost'], 2); ?></td>
                                                        <td><?php echo $ticket['currency'] . ' ' . 
                                                            number_format($ticket['agency_fee'], 2); ?></td>
                                                        <td><?php echo $ticket['currency'] . ' ' . 
                                                            number_format($ticket['ticket_cost'] + $ticket['agency_fee'], 2); ?></td>
                                                        <td>
                                                            <span class="badge bg-<?php
                                                                echo $ticket['status'] === 'Active' ? 'success' : 
                                                                    ($ticket['status'] === 'Cancelled' ? 'danger' : 'info');
                                                                ?>">
                                                                <?php echo $ticket['status']; ?>
                                                            </span>
                                                        </td>
                                                        <td><?php echo htmlspecialchars($ticket['created_by_name']); ?></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_tickets_report.php?start_date=${startDate}&end_date=${endDate}&currency=${currency}`;
    }
    </script>
</body>
</html>