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

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

// Initialize filters
$date_from = $_GET['date_from'] ?? date('Y-m-d', strtotime('-1 month'));
$date_to = $_GET['date_to'] ?? date('Y-m-d');
$report_type = $_GET['report_type'] ?? 'active_members';
$corporate = $_GET['corporate'] ?? 'all';
$benefit = $_GET['benefit'] ?? 'all';

// Fetch corporates for filter
$sql_corporates = "SELECT corp_id, corporate FROM corporate WHERE cancelled = 0 ORDER BY corporate";
$result_corporates = $conn->query($sql_corporates);
$corporates = $result_corporates->fetch_all(MYSQLI_ASSOC);

// Fetch benefits for filter
$sql_benefits = "SELECT code, benefit FROM benefit ORDER BY benefit";
$result_benefits = $conn->query($sql_benefits);
$benefits = $result_benefits->fetch_all(MYSQLI_ASSOC);

// Function to get report data
function getReportData($conn, $report_type, $filters) {
    $sql = "";
    $params = [];
    $types = "";
    
    switch($report_type) {
        case 'active_members':
            $sql = "SELECT 
                    m.member_no,
                    m.surname,
                    m.first_name,
                    m.other_names,
                    m.family_no,
                    CASE m.relation_to_principal 
                        WHEN 1 THEN 'Principal'
                        WHEN 2 THEN 'Spouse'
                        WHEN 3 THEN 'Child'
                        WHEN 4 THEN 'Parent'
                        ELSE 'Other'
                    END as relation,
                    m.dob,
                    TIMESTAMPDIFF(YEAR, m.dob, CURDATE()) as age,
                    c.corporate,
                    c.scheme,
                    ma.start_date,
                    ma.end_date
                FROM member_info m
                JOIN corporate c ON m.corp_id = c.corp_id
                LEFT JOIN member_anniversary ma ON m.member_no = ma.member_no
                WHERE m.status = 1";

            if ($filters['corporate'] !== 'all') {
                $sql .= " AND m.corp_id = ?";
                $params[] = $filters['corporate'];
                $types .= "s";
            }
            
            $sql .= " ORDER BY c.corporate, m.surname, m.first_name";
            break;

        case 'member_benefits':
            $sql = "SELECT 
                    m.member_no,
                    m.surname,
                    m.first_name,
                    CASE m.relation_to_principal 
                        WHEN 1 THEN 'Principal'
                        WHEN 2 THEN 'Spouse'
                        WHEN 3 THEN 'Child'
                        WHEN 4 THEN 'Parent'
                        ELSE 'Other'
                    END as relation,
                    c.corporate,
                    c.scheme,
                    b.benefit as benefit_name,
                    mb.limit,
                    mb.sharing,
                    mb.category,
                    mb.status as benefit_status
                FROM member_info m
                JOIN corporate c ON m.corp_id = c.corp_id
                JOIN member_benefits mb ON m.member_no = mb.member_no
                JOIN benefit b ON mb.benefit = b.code
                WHERE m.status = 1";

            if ($filters['corporate'] !== 'all') {
                $sql .= " AND m.corp_id = ?";
                $params[] = $filters['corporate'];
                $types .= "s";
            }

            if ($filters['benefit'] !== 'all') {
                $sql .= " AND mb.benefit = ?";
                $params[] = $filters['benefit'];
                $types .= "i";
            }
            
            $sql .= " ORDER BY c.corporate, m.surname, m.first_name, b.benefit";
            break;

        case 'member_utilization':
            $sql = "SELECT 
                    m.member_no,
                    m.surname,
                    m.first_name,
                    CASE m.relation_to_principal 
                        WHEN 1 THEN 'Principal'
                        WHEN 2 THEN 'Spouse'
                        WHEN 3 THEN 'Child'
                        WHEN 4 THEN 'Parent'
                        ELSE 'Other'
                    END as relation,
                    c.corporate,
                    b.benefit as benefit_name,
                    mb.limit,
                    COUNT(DISTINCT bl.claim_no) as claims_count,
                    SUM(bl.invoiced_amount) as total_claims,
                    SUM(CASE WHEN bl.vet_status = 1 THEN bl.amount_payable ELSE 0 END) as approved_amount,
                    (mb.limit - SUM(CASE WHEN bl.vet_status = 1 THEN bl.amount_payable ELSE 0 END)) as remaining_limit
                FROM member_info m
                JOIN corporate c ON m.corp_id = c.corp_id
                JOIN member_benefits mb ON m.member_no = mb.member_no
                JOIN benefit b ON mb.benefit = b.code
                LEFT JOIN bills bl ON m.member_no = bl.member_no 
                    AND bl.invoice_date BETWEEN ? AND ?
                WHERE m.status = 1";

            $params = [$filters['date_from'], $filters['date_to']];
            $types = "ss";

            if ($filters['corporate'] !== 'all') {
                $sql .= " AND m.corp_id = ?";
                $params[] = $filters['corporate'];
                $types .= "s";
            }

            if ($filters['benefit'] !== 'all') {
                $sql .= " AND mb.benefit = ?";
                $params[] = $filters['benefit'];
                $types .= "i";
            }
            
            $sql .= " GROUP BY m.member_no, mb.benefit 
                      ORDER BY c.corporate, m.surname, m.first_name, b.benefit";
            break;
    }

    if (!empty($sql)) {
        $stmt = $conn->prepare($sql);
        if (!empty($params)) {
            $stmt->bind_param($types, ...$params);
        }
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }
    
    return [];
}

// Get report data if form submitted
$report_data = [];
if (isset($_GET['action']) && $_GET['action'] === 'view') {
    $report_data = getReportData($conn, $report_type, [
        'date_from' => $date_from,
        'date_to' => $date_to,
        'corporate' => $corporate,
        'benefit' => $benefit
    ]);
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Member Reports')); ?>
    <?php include 'layouts/head-css.php'; ?>
    
    <!-- DataTables CSS -->
    <link href="https://cdn.datatables.net/1.13.7/css/dataTables.bootstrap5.min.css" rel="stylesheet" />
    <link href="https://cdn.datatables.net/responsive/2.5.0/css/responsive.bootstrap5.min.css" rel="stylesheet" />
    <link href="https://cdn.datatables.net/buttons/2.4.2/css/buttons.bootstrap5.min.css" rel="stylesheet" />
    
    <style>
        .dataTables_wrapper .dataTables_length select {
            width: 80px;
        }
        .date-required {
            display: none;
        }
    </style>
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <?php includeFileWithVariables('layouts/page-title.php', array('pagetitle' => 'Reports', 'title' => 'Member Reports')); ?>

                    <!-- Filters Section -->
                    <div class="row">
                        <div class="col-12">
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0">Report Filters</h4>
                                </div>
                                <div class="card-body">
                                    <form method="GET" class="row gy-2 gx-3 align-items-center">
                                        <div class="col-md-3">
                                            <label class="form-label">Report Type</label>
                                            <select class="form-select" name="report_type" id="report_type" required>
                                                <option value="active_members" <?php echo $report_type === 'active_members' ? 'selected' : ''; ?>>
                                                    Active Members List
                                                </option>
                                                <option value="member_benefits" <?php echo $report_type === 'member_benefits' ? 'selected' : ''; ?>>
                                                    Member Benefits
                                                </option>
                                                <option value="member_utilization" <?php echo $report_type === 'member_utilization' ? 'selected' : ''; ?>>
                                                    Member Utilization
                                                </option>
                                            </select>
                                        </div>

                                        <div class="col-md-3">
                                            <label class="form-label">Corporate</label>
                                            <select class="form-select" name="corporate">
                                                <option value="all">All Corporates</option>
                                                <?php foreach($corporates as $corp): ?>
                                                    <option value="<?php echo $corp['corp_id']; ?>" 
                                                            <?php echo $corporate === $corp['corp_id'] ? 'selected' : ''; ?>>
                                                        <?php echo htmlspecialchars($corp['corporate']); ?>
                                                    </option>
                                                <?php endforeach; ?>
                                            </select>
                                        </div>

                                        <div class="col-md-3 benefit-filter">
                                            <label class="form-label">Benefit</label>
                                            <select class="form-select" name="benefit">
                                                <option value="all">All Benefits</option>
                                                <?php foreach($benefits as $ben): ?>
                                                    <option value="<?php echo $ben['code']; ?>" 
                                                            <?php echo $benefit === $ben['code'] ? 'selected' : ''; ?>>
                                                        <?php echo htmlspecialchars($ben['benefit']); ?>
                                                    </option>
                                                <?php endforeach; ?>
                                            </select>
                                        </div>

                                        <div class="col-md-3 date-required">
                                            <label class="form-label">Date From</label>
                                            <input type="date" class="form-control" name="date_from" 
                                                   value="<?php echo $date_from; ?>">
                                        </div>

                                        <div class="col-md-3 date-required">
                                            <label class="form-label">Date To</label>
                                            <input type="date" class="form-control" name="date_to" 
                                                   value="<?php echo $date_to; ?>">
                                        </div>

                                        <div class="col-md-3">
                                            <label class="form-label">&nbsp;</label>
                                            <div class="d-flex gap-2">
                                                <button type="submit" class="btn btn-primary" name="action" value="view">
                                                    <i class="ri-search-line align-middle me-1"></i> Generate
                                                </button>
                                                <?php if (!empty($report_data)): ?>
                                                <button type="submit" class="btn btn-success" name="action" value="excel" 
                                                        formaction="export-member-report.php">
                                                    <i class="ri-file-excel-line align-middle me-1"></i> Excel
                                                </button>
                                                <button type="submit" class="btn btn-danger" name="action" value="pdf" 
                                                        formaction="print-member-report.php">
                                                    <i class="ri-file-pdf-line align-middle me-1"></i> PDF
                                                </button>
                                                <?php endif; ?>
                                            </div>
                                        </div>
                                    </form>
                                </div>
                            </div>
                        </div>
                    </div>

                    <?php if (!empty($report_data)): ?>
                    <!-- Report Content -->
                    <div class="row">
                        <div class="col-12">
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0"><?php echo ucwords(str_replace('_', ' ', $report_type)); ?></h4>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table id="reportTable" class="table table-bordered table-striped">
                                            <?php if ($report_type === 'active_members'): ?>
                                                <thead>
                                                    <tr>
                                                        <th>Member No</th>
                                                        <th>Name</th>
                                                        <th>Corporate</th>
                                                        <th>Family No</th>
                                                        <th>Relation</th>
                                                        <th>Age</th>
                                                        <th>Status</th>
                                                    </tr>
                                                </thead>
                                                <tbody>
                                                    <?php foreach($report_data as $row): ?>
                                                    <tr>
                                                        <td><?php echo htmlspecialchars($row['member_no']); ?></td>
                                                        <td>
                                                            <?php 
                                                            echo htmlspecialchars($row['surname'] . ', ' . 
                                                                $row['first_name']); 
                                                            ?>
                                                        </td>
                                                        <td><?php echo htmlspecialchars($row['corporate']); ?></td>
                                                        <td><?php echo htmlspecialchars($row['family_no']); ?></td>
                                                        <td><?php echo htmlspecialchars($row['relation']); ?></td>
                                                        <td><?php echo $row['age']; ?></td>
                                                        <td>
                                                            <span class="badge bg-<?php echo $row['benefit_status'] === 'Active' ? 'success' : 'danger'; ?>">
                                                                <?php echo htmlspecialchars($row['benefit_status']); ?>
                                                            </span>
                                                        </td>
                                                    </tr>
                                                    <?php endforeach; ?>
                                                </tbody>

                                            <?php elseif ($report_type === 'member_benefits'): ?>
                                                <thead>
                                                    <tr>
                                                        <th>Member No</th>
                                                        <th>Name</th>
                                                        <th>Corporate</th>
                                                        <th>Relation</th>
                                                        <th>Benefit</th>
                                                        <th class="text-end">Limit</th>
                                                        <th>Category</th>
                                                        <th>Sharing</th>
                                                        <th>Status</th>
                                                    </tr>
                                                </thead>
                                                <tbody>
                                                    <?php foreach($report_data as $row): ?>
                                                    <tr>
                                                        <td><?php echo htmlspecialchars($row['member_no']); ?></td>
                                                        <td>
                                                            <?php 
                                                            echo htmlspecialchars($row['surname'] . ', ' . 
                                                                $row['first_name']); 
                                                            ?>
                                                        </td>
                                                        <td><?php echo htmlspecialchars($row['corporate']); ?></td>
                                                        <td><?php echo htmlspecialchars($row['relation']); ?></td>
                                                        <td><?php echo htmlspecialchars($row['benefit_name']); ?></td>
                                                        <td class="text-end"><?php echo number_format($row['limit'], 2); ?></td>
                                                        <td><?php echo htmlspecialchars($row['category']); ?></td>
                                                        <td>
                                                            <span class="badge bg-<?php echo $row['sharing'] ? 'success' : 'warning'; ?>">
                                                                <?php echo $row['sharing'] ? 'Yes' : 'No'; ?>
                                                            </span>
                                                        </td>
                                                        <td>
                                                            <span class="badge bg-<?php echo $row['benefit_status'] === 'Active' ? 'success' : 'danger'; ?>">
                                                                <?php echo htmlspecialchars($row['benefit_status']); ?>
                                                            </span>
                                                        </td>
                                                    </tr>
                                                    <?php endforeach; ?>
                                                </tbody>

                                            <?php elseif ($report_type === 'member_utilization'): ?>
                                                <thead>
                                                    <tr>
                                                        <th>Member No</th>
                                                        <th>Name</th>
                                                        <th>Corporate</th>
                                                        <th>Benefit</th>
                                                        <th>Claims</th>
                                                        <th class="text-end">Limit</th>
                                                        <th class="text-end">Total Claims</th>
                                                        <th class="text-end">Approved</th>
                                                        <th class="text-end">Remaining</th>
                                                        <th>Utilization %</th>
                                                    </tr>
                                                </thead>
                                                <tbody>
                                                    <?php foreach($report_data as $row): 
                                                        $utilization = $row['limit'] > 0 ? 
                                                            ($row['approved_amount'] / $row['limit']) * 100 : 0;
                                                    ?>
                                                    <tr>
                                                        <td><?php echo htmlspecialchars($row['member_no']); ?></td>
                                                        <td>
                                                            <?php 
                                                            echo htmlspecialchars($row['surname'] . ', ' . 
                                                                $row['first_name']); 
                                                            ?>
                                                        </td>
                                                        <td><?php echo htmlspecialchars($row['corporate']); ?></td>
                                                        <td><?php echo htmlspecialchars($row['benefit_name']); ?></td>
                                                        <td><?php echo $row['claims_count']; ?></td>
                                                        <td class="text-end"><?php echo number_format($row['limit'], 2); ?></td>
                                                        <td class="text-end"><?php echo number_format($row['total_claims'], 2); ?></td>
                                                        <td class="text-end"><?php echo number_format($row['approved_amount'], 2); ?></td>
                                                        <td class="text-end">
                                                            <span class="badge bg-<?php echo $row['remaining_limit'] < 0 ? 'danger' : ($row['remaining_limit'] < ($row['limit'] * 0.2) ? 'warning' : 'success'); ?>">
                                                                <?php echo number_format($row['remaining_limit'], 2); ?>
                                                            </span>
                                                        </td>
                                                        <td>
                                                            <div class="progress" style="height: 20px;">
                                                                <div class="progress-bar bg-<?php echo $utilization >= 80 ? 'danger' : ($utilization >= 60 ? 'warning' : 'success'); ?>"
                                                                     role="progressbar" 
                                                                     style="width: <?php echo min(100, $utilization); ?>%;"
                                                                     aria-valuenow="<?php echo $utilization; ?>" 
                                                                     aria-valuemin="0" 
                                                                     aria-valuemax="100">
                                                                    <?php echo number_format($utilization, 1); ?>%
                                                                </div>
                                                            </div>
                                                        </td>
                                                    </tr>
                                                    <?php endforeach; ?>
                                                </tbody>
                                                <tfoot>
                                                    <tr class="table-dark">
                                                        <th colspan="4">Totals</th>
                                                        <th><?php echo array_sum(array_column($report_data, 'claims_count')); ?></th>
                                                        <th class="text-end"><?php echo number_format(array_sum(array_column($report_data, 'limit')), 2); ?></th>
                                                        <th class="text-end"><?php echo number_format(array_sum(array_column($report_data, 'total_claims')), 2); ?></th>
                                                        <th class="text-end"><?php echo number_format(array_sum(array_column($report_data, 'approved_amount')), 2); ?></th>
                                                        <th class="text-end"><?php echo number_format(array_sum(array_column($report_data, 'remaining_limit')), 2); ?></th>
                                                        <th>
                                                            <?php
                                                            $total_limit = array_sum(array_column($report_data, 'limit'));
                                                            $total_approved = array_sum(array_column($report_data, 'approved_amount'));
                                                            $overall_utilization = $total_limit > 0 ? ($total_approved / $total_limit) * 100 : 0;
                                                            echo number_format($overall_utilization, 1) . '%';
                                                            ?>
                                                        </th>
                                                    </tr>
                                                </tfoot>
                                            <?php endif; ?>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    <?php endif; ?>

                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>

    <script src="assets/js/app.js"></script>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<link href="assets/libs/sweetalert2/sweetalert2.min.css" rel="stylesheet">
<script src="assets/libs/sweetalert2/sweetalert2.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.7/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.7/js/dataTables.bootstrap5.min.js"></script>
    <script src="https://cdn.datatables.net/responsive/2.5.0/js/dataTables.responsive.min.js"></script>
    <script src="https://cdn.datatables.net/responsive/2.5.0/js/responsive.bootstrap5.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.4.2/js/dataTables.buttons.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.bootstrap5.min.js"></script>

    <script>
        $(document).ready(function() {
            // Initialize DataTable
            if ($('#reportTable').length) {
                $('#reportTable').DataTable({
                    responsive: true,
                    pageLength: 25,
                    order: [[0, 'asc']],
                    language: {
                        search: "Search reports:",
                        lengthMenu: "Show _MENU_ entries per page",
                        info: "Showing _START_ to _END_ of _TOTAL_ entries",
                        infoEmpty: "No entries available",
                        infoFiltered: "(filtered from _MAX_ total entries)"
                    }
                });
            }

            // Handle report type change
            $('#report_type').on('change', function() {
                var showDates = $(this).val() === 'member_utilization';
                $('.date-required').toggle(showDates);
                $('.date-required input').prop('required', showDates);

                var showBenefits = ['member_benefits', 'member_utilization'].includes($(this).val());
                $('.benefit-filter').toggle(showBenefits);
            }).trigger('change');
        });
    </script>
</body>
</html