<?php
/**
 * Subscription Reports
 * View detailed subscription analytics and reports
 */

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

requireAdmin();

$pdo = getDB();
$period = $_GET['period'] ?? 'month';
$export = isset($_GET['export']);

// Get date range
$dateRange = getDateRange($period);

// Get revenue by plan
$stmt = $pdo->prepare("SELECT 
                      plan_type,
                      COUNT(*) as count,
                      SUM(amount) as total_amount,
                      AVG(amount) as avg_amount
                      FROM subscriptions
                      WHERE created_at BETWEEN ? AND ?
                      GROUP BY plan_type");
$stmt->execute([$dateRange['start'], $dateRange['end']]);
$revenueByPlan = $stmt->fetchAll();

// Get monthly revenue
$stmt = $pdo->prepare("SELECT 
                      DATE_FORMAT(created_at, '%Y-%m') as month,
                      COUNT(*) as count,
                      SUM(amount) as revenue
                      FROM subscriptions
                      WHERE created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
                      GROUP BY DATE_FORMAT(created_at, '%Y-%m')
                      ORDER BY month ASC");
$stmt->execute();
$monthlyRevenue = $stmt->fetchAll();

// Get subscription trends
$stmt = $pdo->prepare("SELECT 
                      DATE(created_at) as date,
                      COUNT(*) as new_subscriptions
                      FROM subscriptions
                      WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
                      GROUP BY DATE(created_at)
                      ORDER BY date ASC");
$stmt->execute();
$dailyTrends = $stmt->fetchAll();

// Get total statistics
$stmt = $pdo->query("SELECT 
                    COUNT(*) as total_subscriptions,
                    SUM(CASE WHEN is_active = 1 AND plan_type != 'unlimited' AND end_date >= CURDATE() THEN 1 ELSE 0 END) as active,
                    SUM(CASE WHEN is_active = 1 AND plan_type = 'unlimited' THEN 1 ELSE 0 END) as lifetime,
                    SUM(amount) as total_revenue,
                    AVG(amount) as avg_revenue
                    FROM subscriptions");
$totalStats = $stmt->fetch();

// Export to CSV
if ($export) {
    $csvData = [];
    $csvData[] = ['Date', 'Plan Type', 'Amount', 'Doctor', 'Status'];
    
    $stmt = $pdo->prepare("SELECT s.created_at, s.plan_type, s.amount, u.full_name, s.is_active
                          FROM subscriptions s
                          JOIN users u ON s.user_id = u.id
                          WHERE s.created_at BETWEEN ? AND ?
                          ORDER BY s.created_at DESC");
    $stmt->execute([$dateRange['start'], $dateRange['end']]);
    $exportData = $stmt->fetchAll();
    
    foreach ($exportData as $row) {
        $csvData[] = [
            date(DISPLAY_DATE_FORMAT, strtotime($row['created_at'])),
            $row['plan_type'],
            $row['amount'],
            $row['full_name'],
            $row['is_active'] ? 'Active' : 'Cancelled'
        ];
    }
    
    exportToCSV($csvData, "subscription_report_{$period}_{$dateRange['start']}_to_{$dateRange['end']}.csv");
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Subscription Reports - Admin Panel</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;
            transition: all 0.3s;
        }
        .sidebar .nav-link:hover {
            background: #34495e;
            padding-left: 25px;
        }
        .sidebar .nav-link.active {
            background: #3498db;
        }
        .sidebar .nav-link i {
            margin-right: 10px;
            width: 20px;
        }
        .main-content {
            background: #f8f9fa;
            min-height: 100vh;
        }
        .top-navbar {
            background: white;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            padding: 15px 20px;
        }
        .stats-card {
            background: white;
            border-radius: 10px;
            padding: 20px;
            text-align: center;
            box-shadow: 0 1px 3px rgba(0,0,0,0.1);
        }
        .stats-number {
            font-size: 32px;
            font-weight: bold;
        }
    </style>
</head>
<body>
    <div class="container-fluid">
        <div class="row">
            <!-- Sidebar -->
            <div class="col-md-2 p-0 sidebar">
                <div class="text-center py-4">
                    <h4 class="text-white">🏥 Admin Panel</h4>
                    <small class="text-muted"><?php echo APP_VERSION; ?></small>
                </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="index.php">
                        <i class="fas fa-credit-card"></i> Subscriptions
                    </a>
                    <a class="nav-link" href="plans.php">
                        <i class="fas fa-tags"></i> Plans
                    </a>
                    <a class="nav-link active" href="reports.php">
                        <i class="fas fa-chart-line"></i> Reports
                    </a>
                    <a class="nav-link" href="../profile.php">
                        <i class="fas fa-user"></i> My Profile
                    </a>
                    <hr class="bg-secondary">
                    <a class="nav-link" href="../../logout.php">
                        <i class="fas fa-sign-out-alt"></i> Logout
                    </a>
                </nav>
            </div>
            
            <!-- Main Content -->
            <div class="col-md-10 main-content">
                <div class="top-navbar">
                    <div class="d-flex justify-content-between align-items-center">
                        <h4 class="mb-0"><i class="fas fa-chart-line"></i> Subscription Reports</h4>
                        <div>
                            <a href="?<?php echo http_build_query(array_merge($_GET, ['export' => 1])); ?>" class="btn btn-success btn-sm">
                                <i class="fas fa-download"></i> Export CSV
                            </a>
                        </div>
                    </div>
                </div>
                
                <div class="p-4">
                    <!-- Period Filter -->
                    <div class="card mb-4">
                        <div class="card-body">
                            <form method="GET" class="row g-3">
                                <div class="col-md-3">
                                    <label>Report Period</label>
                                    <select name="period" class="form-control" onchange="this.form.submit()">
                                        <option value="week" <?php echo $period == 'week' ? 'selected' : ''; ?>>Last 7 Days</option>
                                        <option value="month" <?php echo $period == 'month' ? 'selected' : ''; ?>>Last 30 Days</option>
                                        <option value="year" <?php echo $period == 'year' ? 'selected' : ''; ?>>Last 12 Months</option>
                                        <option value="all" <?php echo $period == 'all' ? 'selected' : ''; ?>>All Time</option>
                                    </select>
                                </div>
                            </form>
                        </div>
                    </div>
                    
                    <!-- Statistics Cards -->
                    <div class="row mb-4">
                        <div class="col-md-3">
                            <div class="stats-card">
                                <div class="stats-number"><?php echo $totalStats['total_subscriptions']; ?></div>
                                <div class="text-muted">Total Subscriptions</div>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="stats-card">
                                <div class="stats-number text-success"><?php echo $totalStats['active']; ?></div>
                                <div class="text-muted">Active</div>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="stats-card">
                                <div class="stats-number text-info"><?php echo $totalStats['lifetime']; ?></div>
                                <div class="text-muted">Lifetime</div>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="stats-card">
                                <div class="stats-number"><?php echo formatCurrency($totalStats['total_revenue']); ?></div>
                                <div class="text-muted">Total Revenue</div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Charts Row -->
                    <div class="row mb-4">
                        <div class="col-md-6">
                            <div class="card">
                                <div class="card-header">
                                    <h5 class="mb-0">Monthly Revenue</h5>
                                </div>
                                <div class="card-body">
                                    <canvas id="revenueChart" height="250"></canvas>
                                </div>
                            </div>
                        </div>
                        <div class="col-md-6">
                            <div class="card">
                                <div class="card-header">
                                    <h5 class="mb-0">Revenue by Plan</h5>
                                </div>
                                <div class="card-body">
                                    <canvas id="planChart" height="250"></canvas>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Daily Trends -->
                    <div class="row">
                        <div class="col-md-12">
                            <div class="card">
                                <div class="card-header">
                                    <h5 class="mb-0">Daily New Subscriptions (Last 30 Days)</h5>
                                </div>
                                <div class="card-body">
                                    <canvas id="trendsChart" height="200"></canvas>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    
    <script src="../../assets/vendor/bootstrap/js/bootstrap.bundle.min.js"></script>
    <script>
        // Monthly Revenue Chart
        const revenueCtx = document.getElementById('revenueChart').getContext('2d');
        new Chart(revenueCtx, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode(array_column($monthlyRevenue, 'month')); ?>,
                datasets: [{
                    label: 'Revenue (USD)',
                    data: <?php echo json_encode(array_column($monthlyRevenue, 'revenue')); ?>,
                    backgroundColor: '#28a745',
                    borderRadius: 5
                }, {
                    label: 'Subscriptions',
                    data: <?php echo json_encode(array_column($monthlyRevenue, 'count')); ?>,
                    type: 'line',
                    borderColor: '#3498db',
                    backgroundColor: 'transparent',
                    tension: 0.3
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: true
            }
        });
        
        // Revenue by Plan 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']); }, $revenueByPlan)); ?>,
                datasets: [{
                    data: <?php echo json_encode(array_column($revenueByPlan, 'total_amount')); ?>,
                    backgroundColor: ['#3498db', '#2ecc71', '#f39c12', '#9b59b6']
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: { position: 'bottom' },
                    tooltip: {
                        callbacks: {
                            label: function(context) {
                                const label = context.label || '';
                                const value = context.raw || 0;
                                return `${label}: $${value.toFixed(2)}`;
                            }
                        }
                    }
                }
            }
        });
        
        // Daily Trends Chart
        const trendsCtx = document.getElementById('trendsChart').getContext('2d');
        new Chart(trendsCtx, {
            type: 'line',
            data: {
                labels: <?php echo json_encode(array_column($dailyTrends, 'date')); ?>,
                datasets: [{
                    label: 'New Subscriptions',
                    data: <?php echo json_encode(array_column($dailyTrends, 'new_subscriptions')); ?>,
                    borderColor: '#f39c12',
                    backgroundColor: 'rgba(243, 156, 18, 0.1)',
                    tension: 0.3,
                    fill: true
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: true,
                plugins: {
                    tooltip: {
                        callbacks: {
                            label: function(context) {
                                return `${context.raw} new subscription(s)`;
                            }
                        }
                    }
                }
            }
        });
    </script>
</body>
</html>