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

// Get filter parameters
$format = isset($_GET['format']) ? $_GET['format'] : 'csv';
$date_from = isset($_GET['date_from']) ? $_GET['date_from'] : date('Y-m-d', strtotime('-30 days'));
$date_to = isset($_GET['date_to']) ? $_GET['date_to'] : date('Y-m-d');
$report_type = isset($_GET['report_type']) ? $_GET['report_type'] : 'daily';

// Calculate totals (same as revenue.php)
$total_revenue = 0;
$total_profit = 0;
$total_fees = 0;
$total_subscriptions = 0;
$total_deposits = 0;
$total_withdrawals = 0;

// Get subscription revenue
$sub_sql = "SELECT COALESCE(SUM(subscription_fee), 0) as total FROM user_bot_subscriptions 
            WHERE subscription_start BETWEEN '$date_from' AND '$date_to' 
            AND status = 'active'";
$sub_result = mysqli_query($conn, $sub_sql);
$sub_row = mysqli_fetch_assoc($sub_result);
$total_subscriptions = $sub_row['total'];

// Get deposit revenue
$deposit_sql = "SELECT COALESCE(SUM(amount), 0) as total FROM deposits 
                WHERE status = 'confirmed' 
                AND DATE(created_at) BETWEEN '$date_from' AND '$date_to'";
$deposit_result = mysqli_query($conn, $deposit_sql);
$deposit_row = mysqli_fetch_assoc($deposit_result);
$total_deposits = $deposit_row['total'];

// Get withdrawal amounts
$withdrawal_sql = "SELECT COALESCE(SUM(amount), 0) as total FROM withdrawals 
                   WHERE status = 'completed' 
                   AND DATE(created_at) BETWEEN '$date_from' AND '$date_to'";
$withdrawal_result = mysqli_query($conn, $withdrawal_sql);
$withdrawal_row = mysqli_fetch_assoc($withdrawal_result);
$total_withdrawals = $withdrawal_row['total'];

// Get trading profit
$profit_sql = "SELECT COALESCE(SUM(amount), 0) as total FROM transactions 
               WHERE type = 'profit' AND status = 'completed' 
               AND DATE(date) BETWEEN '$date_from' AND '$date_to'";
$profit_result = mysqli_query($conn, $profit_sql);
$profit_row = mysqli_fetch_assoc($profit_result);
$total_profit = $profit_row['total'];

// Get fees
$fees_sql = "SELECT COALESCE(SUM(amount), 0) as total FROM transactions 
             WHERE type = 'fee' AND status = 'completed' 
             AND DATE(date) BETWEEN '$date_from' AND '$date_to'";
$fees_result = mysqli_query($conn, $fees_sql);
$fees_row = mysqli_fetch_assoc($fees_result);
$total_fees = $fees_row['total'];

// Calculate total revenue
$total_revenue = $total_subscriptions + $total_deposits + $total_profit + $total_fees;

// Get daily revenue data
$daily_sql = "SELECT DATE(created_at) as date, 
              SUM(CASE WHEN type = 'profit' THEN amount ELSE 0 END) as profit,
              SUM(CASE WHEN type = 'fee' THEN amount ELSE 0 END) as fees
              FROM transactions 
              WHERE status = 'completed' 
              AND DATE(created_at) BETWEEN '$date_from' AND '$date_to'
              GROUP BY DATE(created_at)
              ORDER BY date";
$daily_result = mysqli_query($conn, $daily_sql);

// Get revenue by bot plan
$plan_revenue_sql = "SELECT p.plan_name, COUNT(s.id) as subscriptions, 
                     COALESCE(SUM(p.subscription_fee), 0) as revenue
                     FROM trading_bot_plans p 
                     LEFT JOIN user_bot_subscriptions s ON p.id = s.bot_plan_id 
                     AND s.subscription_start BETWEEN '$date_from' AND '$date_to'
                     AND s.status = 'active'
                     WHERE p.is_active = 1
                     GROUP BY p.id
                     ORDER BY revenue DESC";
$plan_revenue_result = mysqli_query($conn, $plan_revenue_sql);

if ($format == 'csv') {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="revenue_report_' . date('Y-m-d') . '.csv"');
    
    $output = fopen('php://output', 'w');
    
    // Add summary header
    fputcsv($output, array('AI Trading Platform - Revenue Report'));
    fputcsv($output, array('Period:', $date_from . ' to ' . $date_to));
    fputcsv($output, array('Generated:', date('Y-m-d H:i:s')));
    fputcsv($output, array(''));
    
    // Add summary totals
    fputcsv($output, array('SUMMARY TOTALS'));
    fputcsv($output, array('Total Revenue', '$' . number_format($total_revenue, 2)));
    fputcsv($output, array('Trading Profit', '$' . number_format($total_profit, 2)));
    fputcsv($output, array('Subscription Fees', '$' . number_format($total_subscriptions, 2)));
    fputcsv($output, array('Transaction Fees', '$' . number_format($total_fees, 2)));
    fputcsv($output, array('User Deposits', '$' . number_format($total_deposits, 2)));
    fputcsv($output, array('User Withdrawals', '$' . number_format($total_withdrawals, 2)));
    fputcsv($output, array('Net Profit', '$' . number_format($total_profit + $total_fees + $total_subscriptions - $total_withdrawals, 2)));
    fputcsv($output, array(''));
    
    // Add daily revenue
    fputcsv($output, array('DAILY REVENUE BREAKDOWN'));
    fputcsv($output, array('Date', 'Trading Profit', 'Transaction Fees', 'Total'));
    
    while ($row = mysqli_fetch_assoc($daily_result)) {
        $day_total = $row['profit'] + $row['fees'];
        fputcsv($output, array(
            $row['date'],
            '$' . number_format($row['profit'], 2),
            '$' . number_format($row['fees'], 2),
            '$' . number_format($day_total, 2)
        ));
    }
    fputcsv($output, array(''));
    
    // Add bot plan revenue
    fputcsv($output, array('REVENUE BY BOT PLAN'));
    fputcsv($output, array('Plan Name', 'Subscriptions', 'Revenue', 'Percentage'));
    
    mysqli_data_seek($plan_revenue_result, 0);
    while ($plan = mysqli_fetch_assoc($plan_revenue_result)) {
        $percentage = $total_subscriptions > 0 ? ($plan['revenue'] / $total_subscriptions) * 100 : 0;
        fputcsv($output, array(
            $plan['plan_name'],
            $plan['subscriptions'],
            '$' . number_format($plan['revenue'], 2),
            number_format($percentage, 1) . '%'
        ));
    }
    
    fclose($output);
    
} elseif ($format == 'excel') {
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="revenue_report_' . date('Y-m-d') . '.xls"');
    
    echo '<table border="1">';
    echo '<tr><th colspan="4">AI Trading Platform - Revenue Report</th></tr>';
    echo '<tr><td colspan="4"><strong>Period:</strong> ' . $date_from . ' to ' . $date_to . '</td></tr>';
    echo '<tr><td colspan="4"><strong>Generated:</strong> ' . date('Y-m-d H:i:s') . '</td></tr>';
    echo '<tr><td colspan="4">&nbsp;</td></tr>';
    
    // Summary totals
    echo '<tr><th colspan="4">SUMMARY TOTALS</th></tr>';
    echo '<tr><td>Total Revenue</td><td>$' . number_format($total_revenue, 2) . '</td><td colspan="2">&nbsp;</td></tr>';
    echo '<tr><td>Trading Profit</td><td>$' . number_format($total_profit, 2) . '</td><td colspan="2">&nbsp;</td></tr>';
    echo '<tr><td>Subscription Fees</td><td>$' . number_format($total_subscriptions, 2) . '</td><td colspan="2">&nbsp;</td></tr>';
    echo '<tr><td>Transaction Fees</td><td>$' . number_format($total_fees, 2) . '</td><td colspan="2">&nbsp;</td></tr>';
    echo '<tr><td>User Deposits</td><td>$' . number_format($total_deposits, 2) . '</td><td colspan="2">&nbsp;</td></tr>';
    echo '<tr><td>User Withdrawals</td><td>$' . number_format($total_withdrawals, 2) . '</td><td colspan="2">&nbsp;</td></tr>';
    echo '<tr><td>Net Profit</td><td>$' . number_format($total_profit + $total_fees + $total_subscriptions - $total_withdrawals, 2) . '</td><td colspan="2">&nbsp;</td></tr>';
    echo '<tr><td colspan="4">&nbsp;</td></tr>';
    
    // Daily revenue
    echo '<tr><th colspan="4">DAILY REVENUE BREAKDOWN</th></tr>';
    echo '<tr><th>Date</th><th>Trading Profit</th><th>Transaction Fees</th><th>Total</th></tr>';
    
    mysqli_data_seek($daily_result, 0);
    while ($row = mysqli_fetch_assoc($daily_result)) {
        $day_total = $row['profit'] + $row['fees'];
        echo '<tr>';
        echo '<td>' . $row['date'] . '</td>';
        echo '<td>$' . number_format($row['profit'], 2) . '</td>';
        echo '<td>$' . number_format($row['fees'], 2) . '</td>';
        echo '<td>$' . number_format($day_total, 2) . '</td>';
        echo '</tr>';
    }
    echo '<tr><td colspan="4">&nbsp;</td></tr>';
    
    // Bot plan revenue
    echo '<tr><th colspan="4">REVENUE BY BOT PLAN</th></tr>';
    echo '<tr><th>Plan Name</th><th>Subscriptions</th><th>Revenue</th><th>Percentage</th></tr>';
    
    mysqli_data_seek($plan_revenue_result, 0);
    while ($plan = mysqli_fetch_assoc($plan_revenue_result)) {
        $percentage = $total_subscriptions > 0 ? ($plan['revenue'] / $total_subscriptions) * 100 : 0;
        echo '<tr>';
        echo '<td>' . $plan['plan_name'] . '</td>';
        echo '<td>' . $plan['subscriptions'] . '</td>';
        echo '<td>$' . number_format($plan['revenue'], 2) . '</td>';
        echo '<td>' . number_format($percentage, 1) . '%</td>';
        echo '</tr>';
    }
    
    echo '</table>';
    
} elseif ($format == 'pdf') {
    header('Content-Type: application/pdf');
    header('Content-Disposition: attachment; filename="revenue_report_' . date('Y-m-d') . '.pdf"');
    
    // Simple HTML that can be converted to PDF
    $html = '<h1>AI Trading Platform - Revenue Report</h1>';
    $html .= '<p><strong>Period:</strong> ' . $date_from . ' to ' . $date_to . '</p>';
    $html .= '<p><strong>Generated:</strong> ' . date('Y-m-d H:i:s') . '</p>';
    $html .= '<hr>';
    
    // Summary totals
    $html .= '<h2>Summary Totals</h2>';
    $html .= '<table border="1" cellpadding="5" cellspacing="0" width="100%">';
    $html .= '<tr><th>Metric</th><th>Amount</th></tr>';
    $html .= '<tr><td>Total Revenue</td><td>$' . number_format($total_revenue, 2) . '</td></tr>';
    $html .= '<tr><td>Trading Profit</td><td>$' . number_format($total_profit, 2) . '</td></tr>';
    $html .= '<tr><td>Subscription Fees</td><td>$' . number_format($total_subscriptions, 2) . '</td></tr>';
    $html .= '<tr><td>Transaction Fees</td><td>$' . number_format($total_fees, 2) . '</td></tr>';
    $html .= '<tr><td>User Deposits</td><td>$' . number_format($total_deposits, 2) . '</td></tr>';
    $html .= '<tr><td>User Withdrawals</td><td>$' . number_format($total_withdrawals, 2) . '</td></tr>';
    $html .= '<tr><td>Net Profit</td><td>$' . number_format($total_profit + $total_fees + $total_subscriptions - $total_withdrawals, 2) . '</td></tr>';
    $html .= '</table>';
    
    // Daily revenue
    $html .= '<h2>Daily Revenue Breakdown</h2>';
    $html .= '<table border="1" cellpadding="5" cellspacing="0" width="100%">';
    $html .= '<tr><th>Date</th><th>Trading Profit</th><th>Transaction Fees</th><th>Total</th></tr>';
    
    mysqli_data_seek($daily_result, 0);
    while ($row = mysqli_fetch_assoc($daily_result)) {
        $day_total = $row['profit'] + $row['fees'];
        $html .= '<tr>';
        $html .= '<td>' . $row['date'] . '</td>';
        $html .= '<td>$' . number_format($row['profit'], 2) . '</td>';
        $html .= '<td>$' . number_format($row['fees'], 2) . '</td>';
        $html .= '<td>$' . number_format($day_total, 2) . '</td>';
        $html .= '</tr>';
    }
    $html .= '</table>';
    
    echo $html;
}
?>