<?php
session_start();
include('includes/config.php');

// Get parameters
$date_from = isset($_GET['date_from']) ? $_GET['date_from'] : date('Y-m-01');
$date_to = isset($_GET['date_to']) ? $_GET['date_to'] : date('Y-m-d');
$plan_filter = isset($_GET['plan']) ? intval($_GET['plan']) : 0;
$status_filter = isset($_GET['status']) ? $_GET['status'] : '';

// Get format
$format = isset($_GET['format']) ? $_GET['format'] : 'excel';

// Get summary data
$sql = "SELECT 
        COUNT(s.id) as total_subscriptions,
        COALESCE(SUM(p.subscription_fee), 0) as total_revenue,
        COUNT(CASE WHEN s.status = 'active' THEN 1 END) as active_subs,
        AVG(p.subscription_fee) as avg_value
        FROM user_bot_subscriptions s
        JOIN trading_bot_plans p ON s.bot_plan_id = p.id
        WHERE DATE(s.subscription_start) BETWEEN '$date_from' AND '$date_to'";
        
if ($plan_filter > 0) {
    $sql .= " AND s.bot_plan_id = $plan_filter";
}
if ($status_filter) {
    $sql .= " AND s.status = '$status_filter'";
}

$result = mysqli_query($conn, $sql);
$summary = mysqli_fetch_assoc($result);

// Get plan breakdown
$plan_sql = "SELECT p.plan_name, 
            COUNT(s.id) as total_subs,
            COALESCE(SUM(p.subscription_fee), 0) as revenue,
            COUNT(CASE WHEN s.status = 'active' THEN 1 END) as active_subs
            FROM trading_bot_plans p
            LEFT JOIN user_bot_subscriptions s ON p.id = s.bot_plan_id 
            AND DATE(s.subscription_start) BETWEEN '$date_from' AND '$date_to'";
            
if ($status_filter) {
    $plan_sql .= " AND s.status = '$status_filter'";
}

$plan_sql .= " WHERE p.is_active = 1
            GROUP BY p.id
            ORDER BY p.sort_order";

$plan_result = mysqli_query($conn, $plan_sql);

// Get monthly performance
$monthly_sql = "SELECT 
                DATE_FORMAT(s.subscription_start, '%Y-%m') as month,
                COUNT(s.id) as new_subs,
                COALESCE(SUM(p.subscription_fee), 0) as revenue
                FROM user_bot_subscriptions s
                JOIN trading_bot_plans p ON s.bot_plan_id = p.id
                WHERE DATE(s.subscription_start) BETWEEN '$date_from' AND '$date_to'";
                
if ($plan_filter > 0) {
    $monthly_sql .= " AND s.bot_plan_id = $plan_filter";
}
if ($status_filter) {
    $monthly_sql .= " AND s.status = '$status_filter'";
}

$monthly_sql .= " GROUP BY DATE_FORMAT(s.subscription_start, '%Y-%m')
                ORDER BY month DESC
                LIMIT 6";

$monthly_result = mysqli_query($conn, $monthly_sql);

if ($format == 'csv') {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="subscription_analytics_' . date('Y-m-d') . '.csv"');
    
    $output = fopen('php://output', 'w');
    
    // Summary Section
    fputcsv($output, ['SUBSCRIPTION ANALYTICS REPORT']);
    fputcsv($output, ['Period:', $date_from . ' to ' . $date_to]);
    fputcsv($output, []);
    fputcsv($output, ['SUMMARY']);
    fputcsv($output, ['Total Subscriptions', $summary['total_subscriptions']]);
    fputcsv($output, ['Total Revenue', '$' . number_format($summary['total_revenue'], 2)]);
    fputcsv($output, ['Active Subscriptions', $summary['active_subs']]);
    fputcsv($output, ['Average Subscription Value', '$' . number_format($summary['avg_value'], 2)]);
    fputcsv($output, []);
    
    // Plan Breakdown
    fputcsv($output, ['PLAN BREAKDOWN']);
    fputcsv($output, ['Plan Name', 'Subscriptions', 'Revenue', 'Active']);
    
    while ($row = mysqli_fetch_assoc($plan_result)) {
        fputcsv($output, [
            $row['plan_name'],
            $row['total_subs'],
            '$' . number_format($row['revenue'], 2),
            $row['active_subs']
        ]);
    }
    
    fputcsv($output, []);
    
    // Monthly Performance
    fputcsv($output, ['MONTHLY PERFORMANCE']);
    fputcsv($output, ['Month', 'New Subscriptions', 'Revenue']);
    
    while ($row = mysqli_fetch_assoc($monthly_result)) {
        fputcsv($output, [
            date('F Y', strtotime($row['month'] . '-01')),
            $row['new_subs'],
            '$' . number_format($row['revenue'], 2)
        ]);
    }
    
    fclose($output);
    
} else {
    // Excel/HTML format
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="subscription_analytics_' . date('Y-m-d') . '.xls"');
    
    ?>
    <html>
    <head>
        <style>
        table { border-collapse: collapse; width: 100%; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
        th { background-color: #f2f2f2; font-weight: bold; }
        .summary { background-color: #f8f9fa; }
        .section-title { background-color: #e9ecef; font-weight: bold; }
        </style>
    </head>
    <body>
        <h2>Subscription Analytics Report</h2>
        <p><strong>Period:</strong> <?php echo $date_from . ' to ' . $date_to; ?></p>
        
        <h3>Summary</h3>
        <table>
            <tr class="summary">
                <td width="30%">Total Subscriptions</td>
                <td><?php echo $summary['total_subscriptions']; ?></td>
            </tr>
            <tr class="summary">
                <td>Total Revenue</td>
                <td>$<?php echo number_format($summary['total_revenue'], 2); ?></td>
            </tr>
            <tr class="summary">
                <td>Active Subscriptions</td>
                <td><?php echo $summary['active_subs']; ?></td>
            </tr>
            <tr class="summary">
                <td>Average Subscription Value</td>
                <td>$<?php echo number_format($summary['avg_value'], 2); ?></td>
            </tr>
        </table>
        
        <h3>Plan Breakdown</h3>
        <table>
            <tr class="section-title">
                <th>Plan Name</th>
                <th>Subscriptions</th>
                <th>Revenue</th>
                <th>Active</th>
            </tr>
            <?php
            mysqli_data_seek($plan_result, 0);
            while ($row = mysqli_fetch_assoc($plan_result)) {
                echo "<tr>";
                echo "<td>{$row['plan_name']}</td>";
                echo "<td>{$row['total_subs']}</td>";
                echo "<td>$" . number_format($row['revenue'], 2) . "</td>";
                echo "<td>{$row['active_subs']}</td>";
                echo "</tr>";
            }
            ?>
        </table>
        
        <h3>Monthly Performance (Last 6 Months)</h3>
        <table>
            <tr class="section-title">
                <th>Month</th>
                <th>New Subscriptions</th>
                <th>Revenue</th>
            </tr>
            <?php
            mysqli_data_seek($monthly_result, 0);
            while ($row = mysqli_fetch_assoc($monthly_result)) {
                echo "<tr>";
                echo "<td>" . date('F Y', strtotime($row['month'] . '-01')) . "</td>";
                echo "<td>{$row['new_subs']}</td>";
                echo "<td>$" . number_format($row['revenue'], 2) . "</td>";
                echo "</tr>";
            }
            ?>
        </table>
        
        <p><em>Report generated on: <?php echo date('Y-m-d H:i:s'); ?></em></p>
    </body>
    </html>
    <?php
}
?>