<?php
/**
 * Advanced Analytics Dashboard
 * Comprehensive analytics and insights
 * Kenyan Pension & Life Insurance Management System
 */

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

requireLogin();
requirePermission('view_analytics');

// Get date range
$date_from = $_GET['date_from'] ?? date('Y-m-01', strtotime('-12 months'));
$date_to = $_GET['date_to'] ?? date('Y-m-t');
$scheme_id = $_GET['scheme_id'] ?? '';

// Contribution trends
$trendsSql = "SELECT DATE_FORMAT(contribution_period, '%Y-%m') as period,
             SUM(CASE WHEN contribution_type = 'EMPLOYEE' THEN contribution_amount ELSE 0 END) as employee,
             SUM(CASE WHEN contribution_type = 'EMPLOYER' THEN contribution_amount ELSE 0 END) as employer,
             SUM(contribution_amount) as total
             FROM contributions
             WHERE status = 'PROCESSED'
             AND DATE(contribution_period) BETWEEN ? AND ?
             GROUP BY period
             ORDER BY period";
$trendsStmt = $conn->prepare($trendsSql);
$trendsStmt->bind_param("ss", $date_from, $date_to);
$trendsStmt->execute();
$trends = $trendsStmt->get_result()->fetch_all(MYSQLI_ASSOC);

// Withdrawal analysis
$withdrawalSql = "SELECT withdrawal_type, 
                 COUNT(*) as count,
                 SUM(calculated_amount) as total_amount,
                 AVG(calculated_amount) as avg_amount
                 FROM withdrawal_requests
                 WHERE status = 'PROCESSED'
                 AND DATE(requested_date) BETWEEN ? AND ?
                 GROUP BY withdrawal_type";
$withdrawalStmt = $conn->prepare($withdrawalSql);
$withdrawalStmt->bind_param("ss", $date_from, $date_to);
$withdrawalStmt->execute();
$withdrawals = $withdrawalStmt->get_result()->fetch_all(MYSQLI_ASSOC);

// Policy performance
$policySql = "SELECT lip.product_name,
             COUNT(*) as total_policies,
             SUM(lp.sum_assured) as total_sum_assured,
             SUM(lp.premium_amount) as total_premiums,
             COUNT(CASE WHEN lp.policy_status = 'LAPSED' THEN 1 END) as lapsed_count
             FROM life_policies lp
             LEFT JOIN life_insurance_products lip ON lp.product_id = lip.product_id
             GROUP BY lip.product_id, lip.product_name";
$policies = $conn->query($policySql)->fetch_all(MYSQLI_ASSOC);

// Fund performance
$fundSql = "SELECT ps.scheme_name,
           SUM(ma.accrued_benefits) as total_accrued,
           SUM(ma.available_balance) as total_available,
           SUM(ma.allocated_investment_returns) as total_returns,
           COUNT(DISTINCT ma.member_id) as member_count
           FROM member_accounts ma
           INNER JOIN members m ON ma.member_id = m.member_id
           LEFT JOIN pension_schemes ps ON m.scheme_id = ps.scheme_id
           GROUP BY ps.scheme_id, ps.scheme_name";
$funds = $conn->query($fundSql)->fetch_all(MYSQLI_ASSOC);

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

$pageTitle = 'Advanced Analytics';
include 'layouts/topbar.php';
?>

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Advanced Analytics')); ?>
    <link rel="stylesheet" href="assets/css/pension-dashboard.css">
    <?php include 'layouts/head-css.php'; ?>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</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, #8b5cf6 0%, #7c3aed 100%); margin-bottom: 2rem;">
                        <div class="welcome-content">
                            <div class="welcome-text">
                                <h2>Advanced Analytics</h2>
                                <p>Comprehensive insights and performance metrics</p>
                            </div>
                        </div>
                    </div>

                    <div class="row mb-3">
                        <div class="col-12">
                            <form method="GET" class="d-inline-flex gap-2">
                                <select name="scheme_id" class="form-select" style="width: auto;">
                                    <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>
                                <input type="date" name="date_from" class="form-control" style="width: auto;" value="<?php echo $date_from; ?>">
                                <input type="date" name="date_to" class="form-control" style="width: auto;" value="<?php echo $date_to; ?>">
                                <button type="submit" class="btn btn-primary">Filter</button>
                            </form>
                        </div>
                    </div>

                    <!-- Contribution Trends Chart -->
                    <div class="row">
                        <div class="col-12">
                            <div class="page-card">
                                <div class="page-card-header">
                                    <h4 class="page-card-title">Contribution Trends</h4>
                                </div>
                                <div class="page-card-body">
                                    <canvas id="contributionChart" height="80"></canvas>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Withdrawal Analysis -->
                    <div class="row">
                        <div class="col-md-6">
                            <div class="page-card">
                                <div class="page-card-header">
                                    <h4 class="page-card-title">Withdrawal Analysis</h4>
                                </div>
                                <div class="page-card-body">
                                    <canvas id="withdrawalChart"></canvas>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-md-6">
                            <div class="page-card">
                                <div class="page-card-header">
                                    <h4 class="page-card-title">Policy Performance</h4>
                                </div>
                                <div class="page-card-body">
                                    <canvas id="policyChart"></canvas>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Fund Performance -->
                    <div class="row">
                        <div class="col-12">
                            <div class="page-card">
                                <div class="page-card-header">
                                    <h4 class="page-card-title">Fund Performance by Scheme</h4>
                                </div>
                                <div class="page-card-body">
                                    <div class="table-responsive">
                                        <table class="data-table">
                                            <thead>
                                                <tr>
                                                    <th>Scheme</th>
                                                    <th>Members</th>
                                                    <th>Total Accrued</th>
                                                    <th>Available Balance</th>
                                                    <th>Investment Returns</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php foreach ($funds as $fund): ?>
                                                    <tr>
                                                        <td><strong><?php echo htmlspecialchars($fund['scheme_name'] ?? 'N/A'); ?></strong></td>
                                                        <td><?php echo $fund['member_count']; ?></td>
                                                        <td>KES <?php echo number_format($fund['total_accrued'], 2); ?></td>
                                                        <td>KES <?php echo number_format($fund['total_available'], 2); ?></td>
                                                        <td>KES <?php echo number_format($fund['total_returns'], 2); ?></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>
    // Contribution Trends Chart
    const contributionCtx = document.getElementById('contributionChart').getContext('2d');
    new Chart(contributionCtx, {
        type: 'line',
        data: {
            labels: <?php echo json_encode(array_column($trends, 'period')); ?>,
            datasets: [{
                label: 'Employee Contributions',
                data: <?php echo json_encode(array_column($trends, 'employee')); ?>,
                borderColor: 'rgb(37, 99, 235)',
                backgroundColor: 'rgba(37, 99, 235, 0.1)',
                tension: 0.1
            }, {
                label: 'Employer Contributions',
                data: <?php echo json_encode(array_column($trends, 'employer')); ?>,
                borderColor: 'rgb(16, 185, 129)',
                backgroundColor: 'rgba(16, 185, 129, 0.1)',
                tension: 0.1
            }]
        },
        options: {
            responsive: true,
            scales: {
                y: {
                    beginAtZero: true
                }
            }
        }
    });

    // Withdrawal Chart
    const withdrawalCtx = document.getElementById('withdrawalChart').getContext('2d');
    new Chart(withdrawalCtx, {
        type: 'doughnut',
        data: {
            labels: <?php echo json_encode(array_column($withdrawals, 'withdrawal_type')); ?>,
            datasets: [{
                data: <?php echo json_encode(array_column($withdrawals, 'total_amount')); ?>,
                backgroundColor: [
                    'rgb(37, 99, 235)',
                    'rgb(16, 185, 129)',
                    'rgb(245, 158, 11)',
                    'rgb(236, 72, 153)',
                    'rgb(139, 92, 246)'
                ]
            }]
        }
    });

    // Policy Chart
    const policyCtx = document.getElementById('policyChart').getContext('2d');
    new Chart(policyCtx, {
        type: 'bar',
        data: {
            labels: <?php echo json_encode(array_column($policies, 'product_name')); ?>,
            datasets: [{
                label: 'Total Policies',
                data: <?php echo json_encode(array_column($policies, 'total_policies')); ?>,
                backgroundColor: 'rgba(37, 99, 235, 0.8)'
            }, {
                label: 'Lapsed',
                data: <?php echo json_encode(array_column($policies, 'lapsed_count')); ?>,
                backgroundColor: 'rgba(239, 68, 68, 0.8)'
            }]
        },
        options: {
            responsive: true,
            scales: {
                y: {
                    beginAtZero: true
                }
            }
        }
    });
    </script>
</body>
</html>
