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

// Get filter parameters
$format = isset($_GET['format']) ? $_GET['format'] : 'excel';
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-d', strtotime('-30 days'));
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');
$user_id = isset($_GET['user_id']) ? intval($_GET['user_id']) : 0;
$bot_plan_id = isset($_GET['bot_plan_id']) ? intval($_GET['bot_plan_id']) : 0;
$type = isset($_GET['type']) ? $_GET['type'] : 'all';

// Build commission query
$commission_sql = "SELECT 
                    rt.*,
                    u1.username as referrer_username,
                    u1.email as referrer_email,
                    u2.username as referred_username,
                    u2.email as referred_email,
                    s.bot_plan_id,
                    p.plan_name
                  FROM referral_transactions rt
                  LEFT JOIN users u1 ON rt.referrer_id = u1.id
                  LEFT JOIN users u2 ON rt.referred_id = u2.id
                  LEFT JOIN user_bot_subscriptions s ON rt.referred_id = s.user_id AND rt.transaction_type = 'bot_bonus'
                  LEFT JOIN trading_bot_plans p ON s.bot_plan_id = p.id
                  WHERE rt.created_at BETWEEN '$start_date 00:00:00' AND '$end_date 23:59:59'";

if ($user_id > 0) {
    $commission_sql .= " AND rt.referrer_id = $user_id";
}

if ($bot_plan_id > 0) {
    $commission_sql .= " AND s.bot_plan_id = $bot_plan_id";
}

if ($type != 'all') {
    $commission_sql .= " AND rt.transaction_type = '$type'";
}

$commission_sql .= " ORDER BY rt.created_at DESC";
$commission_result = mysqli_query($conn, $commission_sql);

if ($format == 'csv') {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="commission_report_' . date('Y-m-d') . '.csv"');
    
    $output = fopen('php://output', 'w');
    
    // Add headers
    fputcsv($output, array('Date', 'Referrer', 'Referrer Email', 'Referred User', 'Referred Email', 'Type', 'Bot Plan', 'Amount', 'Description'));
    
    // Add data
    while ($row = mysqli_fetch_assoc($commission_result)) {
        fputcsv($output, array(
            $row['created_at'],
            $row['referrer_username'],
            $row['referrer_email'],
            $row['referred_username'],
            $row['referred_email'],
            $row['transaction_type'],
            $row['plan_name'],
            $row['amount'],
            $row['description']
        ));
    }
    
    fclose($output);
    
} elseif ($format == 'excel') {
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="commission_report_' . date('Y-m-d') . '.xls"');
    
    echo '<table border="1">';
    echo '<tr>';
    echo '<th>Date</th>';
    echo '<th>Referrer</th>';
    echo '<th>Referrer Email</th>';
    echo '<th>Referred User</th>';
    echo '<th>Referred Email</th>';
    echo '<th>Type</th>';
    echo '<th>Bot Plan</th>';
    echo '<th>Amount</th>';
    echo '<th>Description</th>';
    echo '</tr>';
    
    while ($row = mysqli_fetch_assoc($commission_result)) {
        echo '<tr>';
        echo '<td>' . $row['created_at'] . '</td>';
        echo '<td>' . $row['referrer_username'] . '</td>';
        echo '<td>' . $row['referrer_email'] . '</td>';
        echo '<td>' . $row['referred_username'] . '</td>';
        echo '<td>' . $row['referred_email'] . '</td>';
        echo '<td>' . $row['transaction_type'] . '</td>';
        echo '<td>' . $row['plan_name'] . '</td>';
        echo '<td>' . $row['amount'] . '</td>';
        echo '<td>' . $row['description'] . '</td>';
        echo '</tr>';
    }
    
    echo '</table>';
    
} elseif ($format == 'pdf') {
    header('Content-Type: application/pdf');
    header('Content-Disposition: attachment; filename="commission_report_' . date('Y-m-d') . '.pdf"');
    
    $html = '<h1>Commission Report</h1>';
    $html .= '<p>Generated on: ' . date('Y-m-d H:i:s') . '</p>';
    $html .= '<p>Date Range: ' . $start_date . ' to ' . $end_date . '</p>';
    $html .= '<table border="1" cellpadding="5" cellspacing="0">';
    $html .= '<tr>';
    $html .= '<th>Date</th>';
    $html .= '<th>Referrer</th>';
    $html .= '<th>Referred User</th>';
    $html .= '<th>Type</th>';
    $html .= '<th>Amount</th>';
    $html .= '<th>Description</th>';
    $html .= '</tr>';
    
    while ($row = mysqli_fetch_assoc($commission_result)) {
        $html .= '<tr>';
        $html .= '<td>' . date('M d, Y', strtotime($row['created_at'])) . '</td>';
        $html .= '<td>' . $row['referrer_username'] . '</td>';
        $html .= '<td>' . $row['referred_username'] . '</td>';
        $html .= '<td>' . $row['transaction_type'] . '</td>';
        $html .= '<td>$' . $row['amount'] . '</td>';
        $html .= '<td>' . $row['description'] . '</td>';
        $html .= '</tr>';
    }
    
    $html .= '</table>';
    
    // Get summary stats
    $stats_sql = "SELECT 
                    SUM(amount) as total,
                    COUNT(DISTINCT referrer_id) as referrers,
                    COUNT(*) as transactions
                  FROM referral_transactions
                  WHERE created_at BETWEEN '$start_date 00:00:00' AND '$end_date 23:59:59'";
    
    $stats_result = mysqli_query($conn, $stats_sql);
    $stats = mysqli_fetch_assoc($stats_result);
    
    $html .= '<br><br>';
    $html .= '<h3>Summary</h3>';
    $html .= '<p>Total Commission: $' . number_format($stats['total'], 2) . '</p>';
    $html .= '<p>Active Referrers: ' . $stats['referrers'] . '</p>';
    $html .= '<p>Total Transactions: ' . $stats['transactions'] . '</p>';
    
    echo $html;
}
?>