<?php
/**
 * Export Members
 * Export member data to CSV/Excel
 * Kenyan Pension & Life Insurance Management System
 */

require_once 'layouts/main.php';
require_once 'includes/auth.php';

requireLogin();
requirePermission('export_members');

$format = $_GET['format'] ?? 'csv';
$scheme_id = $_GET['scheme_id'] ?? '';

$where = ["m.is_active = 1"];
$params = [];
$types = '';

if ($scheme_id) {
    $where[] = "m.scheme_id = ?";
    $params[] = $scheme_id;
    $types .= 'i';
}

$whereClause = implode(' AND ', $where);

$sql = "SELECT m.member_number, m.first_name, m.middle_name, m.last_name, m.date_of_birth, 
               m.gender, m.email, m.phone_number, m.national_id, m.employment_status,
               ps.scheme_name, e.employer_name, ma.accrued_benefits, ma.available_balance
        FROM members m
        LEFT JOIN pension_schemes ps ON m.scheme_id = ps.scheme_id
        LEFT JOIN employers e ON m.employer_id = e.employer_id
        LEFT JOIN member_accounts ma ON m.member_id = ma.member_id
        WHERE $whereClause
        ORDER BY m.member_number";

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

if ($_GET['action'] === 'export') {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="members_export_' . date('Y-m-d') . '.csv"');
    
    $output = fopen('php://output', 'w');
    
    // Headers
    fputcsv($output, [
        'Member Number', 'First Name', 'Middle Name', 'Last Name', 'Date of Birth',
        'Gender', 'Email', 'Phone', 'National ID', 'Employment Status',
        'Scheme', 'Employer', 'Accrued Benefits', 'Available Balance'
    ]);
    
    // Data
    foreach ($members as $member) {
        fputcsv($output, [
            $member['member_number'],
            $member['first_name'],
            $member['middle_name'] ?? '',
            $member['last_name'],
            $member['date_of_birth'],
            $member['gender'] ?? '',
            $member['email'] ?? '',
            $member['phone_number'] ?? '',
            $member['national_id'] ?? '',
            $member['employment_status'],
            $member['scheme_name'] ?? '',
            $member['employer_name'] ?? '',
            number_format($member['accrued_benefits'] ?? 0, 2),
            number_format($member['available_balance'] ?? 0, 2)
        ]);
    }
    
    fclose($output);
    exit();
}

$schemes = $conn->query("SELECT scheme_id, scheme_name FROM pension_schemes WHERE is_active = 1")->fetch_all(MYSQLI_ASSOC);

$pageTitle = 'Export Members';
include 'layouts/topbar.php';
?>

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Export Members')); ?>
    <link rel="stylesheet" href="assets/css/pension-dashboard.css">
    <?php include 'layouts/head-css.php'; ?>
</head>

<body data-sidebar="dark">
    <div id="layout-wrapper">
        <?php include 'layouts/sidebar.php'; ?>
        
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <div class="welcome-header" style="background: linear-gradient(135deg, #2563eb 0%, #1e40af 100%); margin-bottom: 2rem;">
                        <div class="welcome-content">
                            <div class="welcome-text">
                                <h2>Export Members</h2>
                                <p>Export member data to CSV/Excel format</p>
                            </div>
                        </div>
                    </div>

                    <div class="row">
                        <div class="col-md-6">
                            <div class="page-card">
                                <div class="page-card-header">
                                    <h4 class="page-card-title">Export Options</h4>
                                </div>
                                <div class="page-card-body">
                                    <form method="GET">
                                        <input type="hidden" name="action" value="export">
                                        <div class="mb-3">
                                            <label class="form-label">Scheme</label>
                                            <select name="scheme_id" class="form-select">
                                                <option value="">All Schemes</option>
                                                <?php foreach ($schemes as $scheme): ?>
                                                    <option value="<?php echo $scheme['scheme_id']; ?>" <?php echo $scheme_id == $scheme['scheme_id'] ? 'selected' : ''; ?>>
                                                        <?php echo htmlspecialchars($scheme['scheme_name']); ?>
                                                    </option>
                                                <?php endforeach; ?>
                                            </select>
                                        </div>
                                        
                                        <div class="mb-3">
                                            <label class="form-label">Export Format</label>
                                            <select name="format" class="form-select">
                                                <option value="csv" <?php echo $format === 'csv' ? 'selected' : ''; ?>>CSV</option>
                                                <option value="excel" <?php echo $format === 'excel' ? 'selected' : ''; ?>>Excel</option>
                                            </select>
                                        </div>
                                        
                                        <div class="alert alert-info">
                                            <i class="ri-information-line me-2"></i>
                                            The export will include all active members with their account balances and scheme information.
                                        </div>
                                        
                                        <button type="submit" class="btn btn-primary w-100">
                                            <i class="ri-download-line me-2"></i>Export Members
                                        </button>
                                    </form>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>
    <?php include 'layouts/vendor-scripts.php'; ?>
</body>
</html>
