<?php
require_once '../includes/config.php';
require_once 'header.php';

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

// Member Report
if ($report_type == 'members') {
    $stmt = $pdo->prepare("SELECT m.*, mt.type as membership_type 
                          FROM members m 
                          LEFT JOIN membership_types mt ON m.membership_type_id = mt.id 
                          ORDER BY m.created_at DESC");
    $stmt->execute();
    $data = $stmt->fetchAll();
    $title = "Member Report";
    $headers = ['ID', 'Membership No', 'Name', 'Email', 'Phone', 'Type', 'Join Date', 'Expiry Date', 'Status'];
    $columns = ['id', 'membership_number', 'fullname', 'email', 'phone', 'membership_type', 'join_date', 'expiry_date', 'status'];
}

// Revenue Report
elseif ($report_type == 'revenue') {
    $stmt = $pdo->prepare("SELECT 
                          DATE_FORMAT(renewal_date, '%Y-%m') as month,
                          COUNT(*) as renewals,
                          SUM(amount) as total_amount
                          FROM renewals 
                          WHERE renewal_date BETWEEN ? AND ?
                          GROUP BY DATE_FORMAT(renewal_date, '%Y-%m')
                          ORDER BY month");
    $stmt->execute([$start_date, $end_date]);
    $data = $stmt->fetchAll();
    $title = "Revenue Report ($start_date to $end_date)";
    $headers = ['Month', 'Number of Renewals', 'Total Amount'];
    $columns = ['month', 'renewals', 'total_amount'];
}

// Renewal Report
elseif ($report_type == 'renewals') {
    $stmt = $pdo->prepare("SELECT r.*, m.fullname, m.membership_number, mt.type as membership_type
                          FROM renewals r
                          JOIN members m ON r.member_id = m.id
                          LEFT JOIN membership_types mt ON m.membership_type_id = mt.id
                          WHERE r.renewal_date BETWEEN ? AND ?
                          ORDER BY r.renewal_date DESC");
    $stmt->execute([$start_date, $end_date]);
    $data = $stmt->fetchAll();
    $title = "Renewal Report ($start_date to $end_date)";
    $headers = ['Date', 'Member Name', 'Membership No', 'Type', 'Amount', 'Expiry Date'];
    $columns = ['renewal_date', 'fullname', 'membership_number', 'membership_type', 'amount', 'expiry_date'];
}

// Event Report
else {
    $report_type = 'events';
    $stmt = $pdo->prepare("SELECT e.*, 
                          (SELECT COUNT(*) FROM event_registrations WHERE event_id = e.id) as registered_count
                          FROM events e
                          ORDER BY e.event_date DESC");
    $stmt->execute();
    $data = $stmt->fetchAll();
    $title = "Event Report";
    $headers = ['ID', 'Title', 'Date', 'Venue', 'Registered', 'Capacity', 'Status'];
    $columns = ['id', 'title', 'event_date', 'venue', 'registered_count', 'max_attendees', 'status'];
}

// Get totals
$totalRecords = count($data);
$totalRevenue = array_sum(array_column($data, 'total_amount')) ?? 0;
?>

<div class="d-flex justify-content-between flex-wrap flex-md-nowrap align-items-center pt-3 pb-2 mb-3 border-bottom">
    <h1 class="h2"><i class="fas fa-chart-bar me-2"></i>Reports & Analytics</h1>
    <div>
        <button onclick="window.print()" class="btn btn-secondary">
            <i class="fas fa-print me-2"></i>Print
        </button>
        <button onclick="exportToExcel()" class="btn btn-success">
            <i class="fas fa-file-excel me-2"></i>Export Excel
        </button>
    </div>
</div>

<!-- Report Type Selection -->
<div class="card mb-4">
    <div class="card-body">
        <form method="GET" class="row g-3">
            <div class="col-md-3">
                <label class="form-label">Report Type</label>
                <select name="type" class="form-control" onchange="this.form.submit()">
                    <option value="members" <?php echo $report_type == 'members' ? 'selected' : ''; ?>>Member Report</option>
                    <option value="revenue" <?php echo $report_type == 'revenue' ? 'selected' : ''; ?>>Revenue Report</option>
                    <option value="renewals" <?php echo $report_type == 'renewals' ? 'selected' : ''; ?>>Renewal Report</option>
                    <option value="events" <?php echo $report_type == 'events' ? 'selected' : ''; ?>>Event Report</option>
                </select>
            </div>
            <?php if ($report_type == 'revenue' || $report_type == 'renewals'): ?>
            <div class="col-md-3">
                <label class="form-label">Start Date</label>
                <input type="date" name="start_date" class="form-control" value="<?php echo $start_date; ?>">
            </div>
            <div class="col-md-3">
                <label class="form-label">End Date</label>
                <input type="date" name="end_date" class="form-control" value="<?php echo $end_date; ?>">
            </div>
            <div class="col-md-3">
                <label class="form-label">&nbsp;</label>
                <button type="submit" class="btn btn-primary w-100">Generate Report</button>
            </div>
            <?php else: ?>
            <div class="col-md-9">
                <label class="form-label">&nbsp;</label>
                <button type="submit" class="btn btn-primary w-100">Generate Report</button>
            </div>
            <?php endif; ?>
        </form>
    </div>
</div>

<!-- Summary Cards -->
<div class="row mb-4">
    <div class="col-md-4">
        <div class="card bg-primary text-white">
            <div class="card-body">
                <h5 class="card-title">Total Records</h5>
                <h2 class="mb-0"><?php echo number_format($totalRecords); ?></h2>
            </div>
        </div>
    </div>
    <?php if ($report_type == 'revenue'): ?>
    <div class="col-md-4">
        <div class="card bg-success text-white">
            <div class="card-body">
                <h5 class="card-title">Total Revenue</h5>
                <h2 class="mb-0"><?php echo formatCurrency($totalRevenue); ?></h2>
            </div>
        </div>
    </div>
    <?php endif; ?>
</div>

<!-- Report Table -->
<div class="card">
    <div class="card-header">
        <h5 class="mb-0"><i class="fas fa-table me-2"></i><?php echo $title; ?></h5>
    </div>
    <div class="card-body">
        <div class="table-responsive">
            <table class="table table-striped table-bordered" id="reportTable">
                <thead class="table-dark">
                    <tr>
                        <?php foreach ($headers as $header): ?>
                            <th><?php echo $header; ?></th>
                        <?php endforeach; ?>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($data as $row): ?>
                    <tr>
                        <?php foreach ($columns as $col): ?>
                            <td>
                                <?php
                                if (strpos($col, 'date') !== false || $col == 'join_date' || $col == 'expiry_date' || $col == 'renewal_date') {
                                    echo formatDate($row[$col]);
                                } elseif ($col == 'amount' || $col == 'total_amount') {
                                    echo formatCurrency($row[$col]);
                                } elseif ($col == 'status') {
                                    $badgeClass = $row[$col] == 'active' ? 'success' : ($row[$col] == 'expired' ? 'danger' : 'warning');
                                    echo '<span class="badge bg-' . $badgeClass . '">' . ucfirst($row[$col]) . '</span>';
                                } else {
                                    echo htmlspecialchars($row[$col] ?? 'N/A');
                                }
                                ?>
                            </td>
                        <?php endforeach; ?>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

<!-- Charts for Revenue Report -->
<?php if ($report_type == 'revenue' && count($data) > 0): ?>
<div class="card mt-4">
    <div class="card-header">
        <h5 class="mb-0"><i class="fas fa-chart-line me-2"></i>Revenue Trend</h5>
    </div>
    <div class="card-body">
        <canvas id="revenueChart" height="80"></canvas>
    </div>
</div>
<?php endif; ?>

<script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.11.5/js/dataTables.bootstrap5.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
$(document).ready(function() {
    $('#reportTable').DataTable({
        pageLength: 25,
        order: [[0, 'desc']],
        dom: 'Bfrtip'
    });
});

function exportToExcel() {
    var table = document.getElementById('reportTable');
    var html = table.outerHTML;
    var url = 'data:application/vnd.ms-excel,' + encodeURIComponent(html);
    var downloadLink = document.createElement('a');
    downloadLink.href = url;
    downloadLink.download = 'report_<?php echo $report_type; ?>_<?php echo date('Y-m-d'); ?>.xls';
    document.body.appendChild(downloadLink);
    downloadLink.click();
    document.body.removeChild(downloadLink);
}

<?php if ($report_type == 'revenue' && count($data) > 0): ?>
const ctx = document.getElementById('revenueChart').getContext('2d');
new Chart(ctx, {
    type: 'line',
    data: {
        labels: <?php echo json_encode(array_column($data, 'month')); ?>,
        datasets: [{
            label: 'Revenue (₹)',
            data: <?php echo json_encode(array_column($data, 'total_amount')); ?>,
            borderColor: '#3498db',
            backgroundColor: 'rgba(52, 152, 219, 0.1)',
            fill: true,
            tension: 0.3
        }]
    },
    options: {
        responsive: true,
        plugins: {
            legend: { position: 'top' }
        }
    }
});
<?php endif; ?>
</script>

<?php require_once 'footer.php'; ?>