<?php
/**
 * Revenue Report
 * Financial analytics and income trends
 */

require_once '../../config/config.php';
require_once '../../includes/functions.php';

requireAdmin();

$pdo = getDB();
$year = $_GET['year'] ?? date('Y');

// Get monthly revenue for selected year
$stmt = $pdo->prepare("SELECT 
    MONTH(created_at) as month,
    COUNT(*) as subscription_count,
    SUM(amount) as total_amount,
    AVG(amount) as avg_amount
    FROM subscriptions
    WHERE YEAR(created_at) = ?
    GROUP BY MONTH(created_at)
    ORDER BY month");
$stmt->execute([$year]);
$monthlyRevenue = $stmt->fetchAll();

// Fill missing months
$revenueByMonth = array_fill(1, 12, ['count' => 0, 'amount' => 0]);
foreach ($monthlyRevenue as $row) {
    $revenueByMonth[$row['month']] = ['count' => $row['subscription_count'], 'amount' => $row['total_amount']];
}

// Get plan breakdown
$planRevenue = $pdo->query("SELECT plan_type, COUNT(*) as count, SUM(amount) as amount 
                           FROM subscriptions GROUP BY plan_type")->fetchAll();

// Get total revenue
$totalRevenue = $pdo->query("SELECT SUM(amount) as total FROM subscriptions")->fetch()['total'];

// Get revenue by period
$stmt = $pdo->query("SELECT 
    DATE_FORMAT(created_at, '%Y-%m') as month,
    SUM(amount) as revenue
    FROM subscriptions
    GROUP BY DATE_FORMAT(created_at, '%Y-%m')
    ORDER BY month DESC
    LIMIT 12");
$recentRevenue = $stmt->fetchAll();
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Revenue Report</title>
    <link href="../../assets/vendor/bootstrap/css/bootstrap.min.css" rel="stylesheet">
    <link href="../../assets/vendor/fontawesome/css/all.min.css" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <style>
        .sidebar { min-height: 100vh; background: #2c3e50; }
        .sidebar .nav-link { color: #ecf0f1; padding: 12px 20px; }
        .sidebar .nav-link:hover { background: #34495e; }
        .sidebar .nav-link.active { background: #3498db; }
        .main-content { background: #f8f9fa; min-height: 100vh; }
        .top-navbar { background: white; padding: 15px 20px; }
        .stats-card { background: white; border-radius: 10px; padding: 15px; text-align: center; margin-bottom: 20px; }
        .stats-number { font-size: 28px; font-weight: bold; }
    </style>
</head>
<body>
    <div class="container-fluid">
        <div class="row">
            <div class="col-md-2 p-0 sidebar">
                <div class="text-center py-4"><h4 class="text-white">🏥 Admin</h4></div>
                <nav class="nav flex-column">
                    <a class="nav-link" href="../dashboard.php"><i class="fas fa-tachometer-alt"></i> Dashboard</a>
                    <a class="nav-link" href="../doctors/index.php"><i class="fas fa-user-md"></i> Doctors</a>
                    <a class="nav-link" href="../subscriptions/index.php"><i class="fas fa-credit-card"></i> Subscriptions</a>
                    <a class="nav-link" href="../rubrics/index.php"><i class="fas fa-tree"></i> Rubrics</a>
                    <a class="nav-link" href="../remedies/index.php"><i class="fas fa-capsules"></i> Remedies</a>
                    <a class="nav-link active" href="index.php"><i class="fas fa-chart-line"></i> Reports</a>
                    <a class="nav-link" href="../profile.php"><i class="fas fa-user"></i> Profile</a>
                    <hr><a class="nav-link" href="../../logout.php"><i class="fas fa-sign-out-alt"></i> Logout</a>
                </nav>
            </div>
            
            <div class="col-md-10 main-content">
                <div class="top-navbar">
                    <div class="d-flex justify-content-between">
                        <h4 class="mb-0"><i class="fas fa-chart-line"></i> Revenue Report</h4>
                        <div>
                            <a href="?export=csv&<?php echo http_build_query($_GET); ?>" class="btn btn-success btn-sm">Export CSV</a>
                            <a href="index.php" class="btn btn-secondary btn-sm">Back</a>
                        </div>
                    </div>
                </div>
                
                <div class="p-4">
                    <!-- Year Filter -->
                    <div class="card mb-4">
                        <div class="card-body">
                            <form method="GET" class="row">
                                <div class="col-md-3">
                                    <label>Select Year</label>
                                    <select name="year" class="form-control" onchange="this.form.submit()">
                                        <?php for($y = date('Y'); $y >= 2020; $y--): ?>
                                            <option value="<?php echo $y; ?>" <?php echo $year == $y ? 'selected' : ''; ?>><?php echo $y; ?></option>
                                        <?php endfor; ?>
                                    </select>
                                </div>
                            </form>
                        </div>
                    </div>
                    
                    <!-- Stats Cards -->
                    <div class="row mb-4">
                        <div class="col-md-4"><div class="stats-card"><div class="stats-number"><?php echo formatCurrency($totalRevenue); ?></div><div>Total Lifetime Revenue</div></div></div>
                        <div class="col-md-4"><div class="stats-card"><div class="stats-number"><?php echo formatCurrency(array_sum(array_column($revenueByMonth, 'amount'))); ?></div><div><?php echo $year; ?> Revenue</div></div></div>
                        <div class="col-md-4"><div class="stats-card"><div class="stats-number"><?php echo array_sum(array_column($revenueByMonth, 'count')); ?></div><div>Subscriptions (<?php echo $year; ?>)</div></div></div>
                    </div>
                    
                    <!-- Charts -->
                    <div class="row">
                        <div class="col-md-8">
                            <div class="card"><div class="card-header"><h5>Monthly Revenue - <?php echo $year; ?></h5></div>
                            <div class="card-body"><canvas id="revenueChart" height="300"></canvas></div></div>
                        </div>
                        <div class="col-md-4">
                            <div class="card"><div class="card-header"><h5>Revenue by Plan</h5></div>
                            <div class="card-body"><canvas id="planChart" height="250"></canvas></div></div>
                            <div class="card mt-3">
                                <div class="card-header"><h5>Plan Breakdown</h5></div>
                                <div class="card-body">
                                    <table class="table table-sm">
                                        <?php foreach($planRevenue as $plan): ?>
                                        <tr><td><?php echo ucfirst($plan['plan_type']); ?></td><td><?php echo $plan['count']; ?> subs</td><td><?php echo formatCurrency($plan['amount']); ?></td></tr>
                                        <?php endforeach; ?>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Recent Revenue Table -->
                    <div class="card mt-4">
                        <div class="card-header"><h5>Recent Revenue (Last 12 Months)</h5></div>
                        <div class="card-body">
                            <table class="table table-bordered">
                                <thead><tr><th>Month</th><th>Revenue</th><th>Growth</th></tr></thead>
                                <tbody>
                                    <?php 
                                    $prevRevenue = 0;
                                    foreach($recentRevenue as $i => $rev): 
                                        $growth = $prevRevenue > 0 ? (($rev['revenue'] - $prevRevenue) / $prevRevenue) * 100 : 0;
                                        $growthClass = $growth >= 0 ? 'text-success' : 'text-danger';
                                    ?>
                                    <tr>
                                        <td><?php echo date('F Y', strtotime($rev['month'] . '-01')); ?></td>
                                        <td><?php echo formatCurrency($rev['revenue']); ?></td>
                                        <td class="<?php echo $growthClass; ?>"><?php echo $i > 0 ? ($growth >= 0 ? '+' : '') . number_format($growth, 1) . '%' : '-'; ?></td>
                                    </tr>
                                    <?php $prevRevenue = $rev['revenue']; endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    
    <script>
        // Monthly Revenue Chart
        const revenueCtx = document.getElementById('revenueChart').getContext('2d');
        new Chart(revenueCtx, {
            type: 'bar',
            data: {
                labels: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
                datasets: [{
                    label: 'Revenue (<?php echo $year; ?>)',
                    data: [<?php echo implode(',', array_map(function($m) { return $m['amount']; }, $revenueByMonth)); ?>],
                    backgroundColor: '#28a745',
                    borderRadius: 5
                }]
            },
            options: { responsive: true, maintainAspectRatio: true, plugins: { tooltip: { callbacks: { label: function(ctx) { return '$' + ctx.raw.toFixed(2); } } } } }
        });
        
        // Plan Distribution Chart
        const planCtx = document.getElementById('planChart').getContext('2d');
        new Chart(planCtx, {
            type: 'pie',
            data: {
                labels: <?php echo json_encode(array_map(function($p) { return ucfirst($p['plan_type']); }, $planRevenue)); ?>,
                datasets: [{ data: <?php echo json_encode(array_column($planRevenue, 'amount')); ?>, backgroundColor: ['#3498db', '#2ecc71', '#f39c12', '#9b59b6'] }]
            },
            options: { responsive: true, plugins: { tooltip: { callbacks: { label: function(ctx) { return ctx.label + ': $' + ctx.raw.toFixed(2); } } } } }
        });
    </script>
</body>
</html>