<?php
/**
 * Scheme Reports
 * Generate comprehensive scheme reports
 * Kenyan Pension & Life Insurance Management System
 */

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

requireLogin();
requirePermission('view_scheme_reports');

$scheme_id = $_GET['scheme_id'] ?? null;
$report_type = $_GET['report_type'] ?? 'summary';
$period_start = $_GET['period_start'] ?? date('Y-m-01');
$period_end = $_GET['period_end'] ?? date('Y-m-t');

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

$report_data = null;
if ($scheme_id && $_GET['action'] === 'generate') {
    $scheme = $conn->query("SELECT * FROM pension_schemes WHERE scheme_id = $scheme_id")->fetch_assoc();
    
    // Get scheme statistics
    $total_members = $conn->query("SELECT COUNT(*) as count FROM members WHERE scheme_id = $scheme_id AND is_active = 1")->fetch_assoc()['count'];
    $total_employers = $conn->query("SELECT COUNT(DISTINCT employer_id) as count FROM members WHERE scheme_id = $scheme_id AND is_active = 1")->fetch_assoc()['count'];
    
    // Get total contributions in period
    $total_contributions = $conn->query("SELECT COALESCE(SUM(contribution_amount), 0) as total 
                                        FROM contributions c
                                        INNER JOIN members m ON c.member_id = m.member_id
                                        WHERE m.scheme_id = $scheme_id AND DATE(c.contribution_period) BETWEEN '$period_start' AND '$period_end'")->fetch_assoc()['total'];
    
    // Get total withdrawals in period
    $total_withdrawals = $conn->query("SELECT COALESCE(SUM(calculated_amount), 0) as total 
                                      FROM withdrawal_requests 
                                      WHERE scheme_id = $scheme_id AND DATE(requested_date) BETWEEN '$period_start' AND '$period_end' AND status = 'PROCESSED'")->fetch_assoc()['total'];
    
    // Get total fund value
    $total_fund_value = $conn->query("SELECT COALESCE(SUM(accrued_benefits), 0) as total 
                                     FROM member_accounts ma
                                     INNER JOIN members m ON ma.member_id = m.member_id
                                     WHERE m.scheme_id = $scheme_id AND ma.account_status = 'ACTIVE'")->fetch_assoc()['total'];
    
    // Get fund returns in period
    $fund_returns = $conn->query("SELECT * FROM fund_returns WHERE scheme_id = $scheme_id AND allocation_date BETWEEN '$period_start' AND '$period_end' ORDER BY allocation_date DESC")->fetch_all(MYSQLI_ASSOC);
    
    // Get top contributors
    $top_contributors = $conn->query("SELECT m.member_number, m.first_name, m.last_name, 
                                      COALESCE(SUM(c.contribution_amount), 0) as total_contributions
                                      FROM members m
                                      LEFT JOIN contributions c ON m.member_id = c.member_id AND DATE(c.contribution_period) BETWEEN '$period_start' AND '$period_end'
                                      WHERE m.scheme_id = $scheme_id AND m.is_active = 1
                                      GROUP BY m.member_id
                                      ORDER BY total_contributions DESC
                                      LIMIT 10")->fetch_all(MYSQLI_ASSOC);
    
    $report_data = [
        'scheme' => $scheme,
        'total_members' => $total_members,
        'total_employers' => $total_employers,
        'total_contributions' => $total_contributions,
        'total_withdrawals' => $total_withdrawals,
        'total_fund_value' => $total_fund_value,
        'fund_returns' => $fund_returns,
        'top_contributors' => $top_contributors,
        'period_start' => $period_start,
        'period_end' => $period_end
    ];
}

$pageTitle = 'Scheme Reports';
include 'layouts/topbar.php';
?>

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Scheme Reports')); ?>
    <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">
                    <!-- Page Header -->
                    <div class="welcome-header" style="background: linear-gradient(135deg, #10b981 0%, #059669 100%); margin-bottom: 2rem;">
                        <div class="welcome-content">
                            <div class="welcome-text">
                                <h2>Scheme Reports</h2>
                                <p>Generate comprehensive pension scheme reports</p>
                            </div>
                        </div>
                    </div>

                    <?php if (!$report_data): ?>
                        <!-- Report Generator Form -->
                        <div class="row">
                            <div class="col-12">
                                <div class="page-card">
                                    <div class="page-card-header">
                                        <h4 class="page-card-title">Generate Scheme Report</h4>
                                    </div>
                                    <div class="page-card-body">
                                        <form method="GET">
                                            <input type="hidden" name="action" value="generate">
                                            <div class="row">
                                                <div class="col-md-6 mb-3">
                                                    <label class="form-label">Scheme <span class="text-danger">*</span></label>
                                                    <select name="scheme_id" class="form-select" required>
                                                        <option value="">Select Scheme</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="col-md-3 mb-3">
                                                    <label class="form-label">Report Type</label>
                                                    <select name="report_type" class="form-select">
                                                        <option value="summary" <?php echo $report_type === 'summary' ? 'selected' : ''; ?>>Summary</option>
                                                        <option value="detailed" <?php echo $report_type === 'detailed' ? 'selected' : ''; ?>>Detailed</option>
                                                        <option value="financial" <?php echo $report_type === 'financial' ? 'selected' : ''; ?>>Financial</option>
                                                    </select>
                                                </div>
                                                <div class="col-md-3 mb-3">
                                                    <label class="form-label">Period Start</label>
                                                    <input type="date" name="period_start" class="form-control" value="<?php echo $period_start; ?>" required>
                                                </div>
                                                <div class="col-md-3 mb-3">
                                                    <label class="form-label">Period End</label>
                                                    <input type="date" name="period_end" class="form-control" value="<?php echo $period_end; ?>" required>
                                                </div>
                                            </div>
                                            <button type="submit" class="btn btn-primary">
                                                <i class="ri-file-chart-line me-2"></i>Generate Report
                                            </button>
                                            <a href="scheme_reports.php" class="btn btn-secondary">Reset</a>
                                        </form>
                                    </div>
                                </div>
                            </div>
                        </div>
                    <?php else: ?>
                        <!-- Report Display -->
                        <div class="row mb-3">
                            <div class="col-12">
                                <a href="scheme_reports.php" class="btn btn-secondary">
                                    <i class="ri-arrow-left-line me-2"></i>Back to Generator
                                </a>
                                <button onclick="window.print()" class="btn btn-primary">
                                    <i class="ri-printer-line me-2"></i>Print Report
                                </button>
                                <button onclick="exportPDF()" class="btn btn-success">
                                    <i class="ri-file-pdf-line me-2"></i>Export PDF
                                </button>
                            </div>
                        </div>
                        
                        <div class="row">
                            <div class="col-12">
                                <div class="page-card" id="report-content">
                                    <div class="page-card-body">
                                        <!-- Report Header -->
                                        <div class="text-center mb-4" style="border-bottom: 2px solid #e2e8f0; padding-bottom: 1rem;">
                                            <h3 style="color: #10b981; font-weight: 700;">SCHEME REPORT</h3>
                                            <h4><?php echo htmlspecialchars($report_data['scheme']['scheme_name']); ?></h4>
                                            <p class="mb-0" style="color: #64748b;">Period: <?php echo date('d M Y', strtotime($period_start)); ?> to <?php echo date('d M Y', strtotime($period_end)); ?></p>
                                        </div>
                                        
                                        <!-- Key Metrics -->
                                        <div class="metrics-row mb-4">
                                            <div class="metric-card members">
                                                <div class="metric-header">
                                                    <h6 class="metric-title">Total Members</h6>
                                                    <span class="metric-icon"><i class="ri-user-line"></i></span>
                                                </div>
                                                <div class="metric-value"><?php echo number_format($report_data['total_members']); ?></div>
                                            </div>
                                            
                                            <div class="metric-card schemes">
                                                <div class="metric-header">
                                                    <h6 class="metric-title">Total Employers</h6>
                                                    <span class="metric-icon"><i class="ri-building-2-line"></i></span>
                                                </div>
                                                <div class="metric-value"><?php echo number_format($report_data['total_employers']); ?></div>
                                            </div>
                                            
                                            <div class="metric-card contributions">
                                                <div class="metric-header">
                                                    <h6 class="metric-title">Period Contributions</h6>
                                                    <span class="metric-icon"><i class="ri-file-add-line"></i></span>
                                                </div>
                                                <div class="metric-value">KES <?php echo number_format($report_data['total_contributions'], 0); ?></div>
                                            </div>
                                            
                                            <div class="metric-card withdrawals">
                                                <div class="metric-header">
                                                    <h6 class="metric-title">Period Withdrawals</h6>
                                                    <span class="metric-icon"><i class="ri-money-dollar-circle-line"></i></span>
                                                </div>
                                                <div class="metric-value">KES <?php echo number_format($report_data['total_withdrawals'], 0); ?></div>
                                            </div>
                                            
                                            <div class="metric-card balances">
                                                <div class="metric-header">
                                                    <h6 class="metric-title">Total Fund Value</h6>
                                                    <span class="metric-icon"><i class="ri-wallet-line"></i></span>
                                                </div>
                                                <div class="metric-value">KES <?php echo number_format($report_data['total_fund_value'], 0); ?></div>
                                            </div>
                                        </div>
                                        
                                        <!-- Fund Returns -->
                                        <?php if (!empty($report_data['fund_returns'])): ?>
                                        <div class="mb-4">
                                            <h5 style="color: #1e293b; margin-bottom: 1rem;">Fund Returns in Period</h5>
                                            <div class="table-responsive">
                                                <table class="data-table">
                                                    <thead>
                                                        <tr>
                                                            <th>Period</th>
                                                            <th>Return %</th>
                                                            <th>Return Amount (KES)</th>
                                                            <th>Allocation Date</th>
                                                            <th>Status</th>
                                                        </tr>
                                                    </thead>
                                                    <tbody>
                                                        <?php foreach ($report_data['fund_returns'] as $return): ?>
                                                            <tr>
                                                                <td><?php echo date('M Y', strtotime($return['return_period_start'])); ?> - <?php echo date('M Y', strtotime($return['return_period_end'])); ?></td>
                                                                <td><?php echo number_format($return['return_percentage'], 2); ?>%</td>
                                                                <td>KES <?php echo number_format($return['return_amount'], 2); ?></td>
                                                                <td><?php echo date('d M Y', strtotime($return['allocation_date'])); ?></td>
                                                                <td><span class="status-badge status-<?php echo strtolower($return['status']); ?>"><?php echo htmlspecialchars($return['status']); ?></span></td>
                                                            </tr>
                                                        <?php endforeach; ?>
                                                    </tbody>
                                                </table>
                                            </div>
                                        </div>
                                        <?php endif; ?>
                                        
                                        <!-- Top Contributors -->
                                        <?php if (!empty($report_data['top_contributors'])): ?>
                                        <div class="mb-4">
                                            <h5 style="color: #1e293b; margin-bottom: 1rem;">Top Contributors (Period)</h5>
                                            <div class="table-responsive">
                                                <table class="data-table">
                                                    <thead>
                                                        <tr>
                                                            <th>Rank</th>
                                                            <th>Member Number</th>
                                                            <th>Name</th>
                                                            <th>Total Contributions (KES)</th>
                                                        </tr>
                                                    </thead>
                                                    <tbody>
                                                        <?php foreach ($report_data['top_contributors'] as $index => $contributor): ?>
                                                            <tr>
                                                                <td><?php echo $index + 1; ?></td>
                                                                <td><?php echo htmlspecialchars($contributor['member_number']); ?></td>
                                                                <td><?php echo htmlspecialchars($contributor['first_name'] . ' ' . $contributor['last_name']); ?></td>
                                                                <td>KES <?php echo number_format($contributor['total_contributions'], 2); ?></td>
                                                            </tr>
                                                        <?php endforeach; ?>
                                                    </tbody>
                                                </table>
                                            </div>
                                        </div>
                                        <?php endif; ?>
                                        
                                        <!-- Footer -->
                                        <div class="text-center mt-4" style="border-top: 2px solid #e2e8f0; padding-top: 1rem; color: #64748b; font-size: 0.875rem;">
                                            <p>Report generated on <?php echo date('d M Y, h:i A'); ?></p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    <?php endif; ?>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>
    <?php include 'layouts/vendor-scripts.php'; ?>
    <script>
    function exportPDF() {
        alert('PDF export functionality will be implemented');
    }
    </script>
</body>
</html>
