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

// Get parameters
$month = isset($_GET['month']) ? intval($_GET['month']) : date('m');
$year = isset($_GET['year']) ? intval($_GET['year']) : date('Y');
$format = isset($_GET['format']) ? $_GET['format'] : 'csv';

// Validate
if ($month < 1 || $month > 12) $month = date('m');
if ($year < 2020 || $year > date('Y') + 1) $year = date('Y');

// Get month name
$month_names = array(
    1 => 'January', 2 => 'February', 3 => 'March', 4 => 'April',
    5 => 'May', 6 => 'June', 7 => 'July', 8 => 'August',
    9 => 'September', 10 => 'October', 11 => 'November', 12 => 'December'
);
$month_name = $month_names[$month];

// Get monthly stats (reuse the function from monthly_report.php)
function getMonthlyStats($conn, $year, $month) {
    $stats = array();
    
    // Total Revenue
    $sql = "SELECT COALESCE(SUM(amount), 0) as total FROM (
        SELECT subscription_fee as amount FROM user_bot_subscriptions WHERE YEAR(subscription_start) = $year AND MONTH(subscription_start) = $month
        UNION ALL
        SELECT amount FROM deposits WHERE YEAR(created_at) = $year AND MONTH(created_at) = $month AND status = 'confirmed'
    ) as combined";
    $result = mysqli_query($conn, $sql);
    $row = mysqli_fetch_assoc($result);
    $stats['total_revenue'] = floatval($row['total']);
    
    // Total Profit
    $sql = "SELECT COALESCE(SUM(amount), 0) as total FROM transactions WHERE type = 'profit' AND status = 'completed' AND YEAR(date) = $year AND MONTH(date) = $month";
    $result = mysqli_query($conn, $sql);
    $row = mysqli_fetch_assoc($result);
    $stats['total_profit'] = floatval($row['total']);
    
    // Total Deposits
    $sql = "SELECT COUNT(*) as count, COALESCE(SUM(amount), 0) as amount FROM deposits WHERE YEAR(created_at) = $year AND MONTH(created_at) = $month AND status = 'confirmed'";
    $result = mysqli_query($conn, $sql);
    $row = mysqli_fetch_assoc($result);
    $stats['total_deposits'] = $row['count'];
    $stats['deposit_amount'] = floatval($row['amount']);
    
    // Total Withdrawals
    $sql = "SELECT COUNT(*) as count, COALESCE(SUM(amount), 0) as amount FROM withdrawals WHERE YEAR(created_at) = $year AND MONTH(created_at) = $month AND status = 'completed'";
    $result = mysqli_query($conn, $sql);
    $row = mysqli_fetch_assoc($result);
    $stats['total_withdrawals'] = $row['count'];
    $stats['withdrawal_amount'] = floatval($row['amount']);
    
    // New Users
    $sql = "SELECT COUNT(*) as count FROM users WHERE YEAR(registration_date) = $year AND MONTH(registration_date) = $month";
    $result = mysqli_query($conn, $sql);
    $row = mysqli_fetch_assoc($result);
    $stats['new_users'] = $row['count'];
    
    // Active Subscriptions
    $sql = "SELECT COUNT(*) as count FROM user_bot_subscriptions WHERE status = 'active' AND YEAR(subscription_start) <= $year AND MONTH(subscription_start) <= $month AND (YEAR(subscription_end) >= $year AND MONTH(subscription_end) >= $month)";
    $result = mysqli_query($conn, $sql);
    $row = mysqli_fetch_assoc($result);
    $stats['active_subscriptions'] = $row['count'];
    
    // Total Tickets
    $sql = "SELECT COUNT(*) as count FROM support_tickets WHERE YEAR(created_at) = $year AND MONTH(created_at) = $month";
    $result = mysqli_query($conn, $sql);
    $row = mysqli_fetch_assoc($result);
    $stats['total_tickets'] = $row['count'];
    
    // Resolved Tickets
    $sql = "SELECT COUNT(*) as count FROM support_tickets WHERE status = 'resolved' AND YEAR(created_at) = $year AND MONTH(created_at) = $month";
    $result = mysqli_query($conn, $sql);
    $row = mysqli_fetch_assoc($result);
    $stats['resolved_tickets'] = $row['count'];
    
    // Average Daily Profit
    $sql = "SELECT COALESCE(AVG(daily_total), 0) as avg_profit FROM (
        SELECT DATE(date) as day, SUM(amount) as daily_total FROM transactions WHERE type = 'profit' AND status = 'completed' AND YEAR(date) = $year AND MONTH(date) = $month GROUP BY DATE(date)
    ) as daily_profits";
    $result = mysqli_query($conn, $sql);
    $row = mysqli_fetch_assoc($result);
    $stats['avg_daily_profit'] = floatval($row['avg_profit']);
    
    // Net Profit
    $stats['net_profit'] = $stats['total_revenue'] - $stats['withdrawal_amount'];
    
    // Profit Margin
    $stats['profit_margin'] = $stats['total_revenue'] > 0 ? ($stats['net_profit'] / $stats['total_revenue']) * 100 : 0;
    
    return $stats;
}

$monthly_stats = getMonthlyStats($conn, $year, $month);

if ($format == 'csv') {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="monthly_report_' . $month_name . '_' . $year . '.csv"');
    
    $output = fopen('php://output', 'w');
    
    // Header
    fputcsv($output, array('Monthly Report: ' . $month_name . ' ' . $year));
    fputcsv($output, array('Generated on: ' . date('Y-m-d H:i:s')));
    fputcsv($output, array('')); // Empty line
    
    // Summary Stats
    fputcsv($output, array('SUMMARY STATISTICS'));
    fputcsv($output, array('Metric', 'Value'));
    fputcsv($output, array('Total Revenue', '$' . number_format($monthly_stats['total_revenue'], 2)));
    fputcsv($output, array('Total Profit', '$' . number_format($monthly_stats['total_profit'], 2)));
    fputcsv($output, array('Net Profit', '$' . number_format($monthly_stats['net_profit'], 2)));
    fputcsv($output, array('Profit Margin', number_format($monthly_stats['profit_margin'], 2) . '%'));
    fputcsv($output, array('New Users', $monthly_stats['new_users']));
    fputcsv($output, array('Active Subscriptions', $monthly_stats['active_subscriptions']));
    fputcsv($output, array('')); // Empty line
    
    // Transaction Stats
    fputcsv($output, array('TRANSACTION STATISTICS'));
    fputcsv($output, array('Metric', 'Count', 'Amount'));
    fputcsv($output, array('Total Deposits', $monthly_stats['total_deposits'], '$' . number_format($monthly_stats['deposit_amount'], 2)));
    fputcsv($output, array('Total Withdrawals', $monthly_stats['total_withdrawals'], '$' . number_format($monthly_stats['withdrawal_amount'], 2)));
    fputcsv($output, array('Average Daily Profit', '-', '$' . number_format($monthly_stats['avg_daily_profit'], 2)));
    fputcsv($output, array('')); // Empty line
    
    // Support Stats
    fputcsv($output, array('SUPPORT STATISTICS'));
    fputcsv($output, array('Metric', 'Count'));
    fputcsv($output, array('Total Tickets', $monthly_stats['total_tickets']));
    fputcsv($output, array('Resolved Tickets', $monthly_stats['resolved_tickets']));
    $resolution_rate = $monthly_stats['total_tickets'] > 0 ? ($monthly_stats['resolved_tickets'] / $monthly_stats['total_tickets']) * 100 : 0;
    fputcsv($output, array('Resolution Rate', number_format($resolution_rate, 1) . '%'));
    
    fclose($output);
    
} elseif ($format == 'excel') {
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="monthly_report_' . $month_name . '_' . $year . '.xls"');
    
    echo '<table border="1">';
    echo '<tr><th colspan="2" style="background-color: #f5f5f5; font-size: 16px;">Monthly Report: ' . $month_name . ' ' . $year . '</th></tr>';
    echo '<tr><td colspan="2">Generated on: ' . date('Y-m-d H:i:s') . '</td></tr>';
    echo '<tr><td colspan="2">&nbsp;</td></tr>';
    
    echo '<tr><th colspan="2" style="background-color: #f0f0f0;">SUMMARY STATISTICS</th></tr>';
    echo '<tr><th>Metric</th><th>Value</th></tr>';
    echo '<tr><td>Total Revenue</td><td>$' . number_format($monthly_stats['total_revenue'], 2) . '</td></tr>';
    echo '<tr><td>Total Profit</td><td>$' . number_format($monthly_stats['total_profit'], 2) . '</td></tr>';
    echo '<tr><td>Net Profit</td><td>$' . number_format($monthly_stats['net_profit'], 2) . '</td></tr>';
    echo '<tr><td>Profit Margin</td><td>' . number_format($monthly_stats['profit_margin'], 2) . '%</td></tr>';
    echo '<tr><td>New Users</td><td>' . $monthly_stats['new_users'] . '</td></tr>';
    echo '<tr><td>Active Subscriptions</td><td>' . $monthly_stats['active_subscriptions'] . '</td></tr>';
    echo '<tr><td colspan="2">&nbsp;</td></tr>';
    
    echo '<tr><th colspan="2" style="background-color: #f0f0f0;">TRANSACTION STATISTICS</th></tr>';
    echo '<tr><th>Metric</th><th>Count</th><th>Amount</th></tr>';
    echo '<tr><td>Total Deposits</td><td>' . $monthly_stats['total_deposits'] . '</td><td>$' . number_format($monthly_stats['deposit_amount'], 2) . '</td></tr>';
    echo '<tr><td>Total Withdrawals</td><td>' . $monthly_stats['total_withdrawals'] . '</td><td>$' . number_format($monthly_stats['withdrawal_amount'], 2) . '</td></tr>';
    echo '<tr><td>Average Daily Profit</td><td>-</td><td>$' . number_format($monthly_stats['avg_daily_profit'], 2) . '</td></tr>';
    echo '<tr><td colspan="3">&nbsp;</td></tr>';
    
    echo '<tr><th colspan="2" style="background-color: #f0f0f0;">SUPPORT STATISTICS</th></tr>';
    echo '<tr><th>Metric</th><th>Count</th></tr>';
    echo '<tr><td>Total Tickets</td><td>' . $monthly_stats['total_tickets'] . '</td></tr>';
    echo '<tr><td>Resolved Tickets</td><td>' . $monthly_stats['resolved_tickets'] . '</td></tr>';
    echo '<tr><td>Resolution Rate</td><td>' . number_format($resolution_rate, 1) . '%</td></tr>';
    
    echo '</table>';
    
} elseif ($format == 'pdf') {
    header('Content-Type: application/pdf');
    header('Content-Disposition: attachment; filename="monthly_report_' . $month_name . '_' . $year . '.pdf"');
    
    // Simple HTML that can be converted to PDF
    $html = '<h1>Monthly Report: ' . $month_name . ' ' . $year . '</h1>';
    $html .= '<p>Generated on: ' . date('Y-m-d H:i:s') . '</p>';
    $html .= '<hr>';
    
    $html .= '<h2>Summary Statistics</h2>';
    $html .= '<table border="1" cellpadding="5" cellspacing="0" style="width: 100%;">';
    $html .= '<tr><th>Metric</th><th>Value</th></tr>';
    $html .= '<tr><td>Total Revenue</td><td>$' . number_format($monthly_stats['total_revenue'], 2) . '</td></tr>';
    $html .= '<tr><td>Total Profit</td><td>$' . number_format($monthly_stats['total_profit'], 2) . '</td></tr>';
    $html .= '<tr><td>Net Profit</td><td>$' . number_format($monthly_stats['net_profit'], 2) . '</td></tr>';
    $html .= '<tr><td>Profit Margin</td><td>' . number_format($monthly_stats['profit_margin'], 2) . '%</td></tr>';
    $html .= '<tr><td>New Users</td><td>' . $monthly_stats['new_users'] . '</td></tr>';
    $html .= '<tr><td>Active Subscriptions</td><td>' . $monthly_stats['active_subscriptions'] . '</td></tr>';
    $html .= '</table>';
    
    $html .= '<h2>Transaction Statistics</h2>';
    $html .= '<table border="1" cellpadding="5" cellspacing="0" style="width: 100%;">';
    $html .= '<tr><th>Metric</th><th>Count</th><th>Amount</th></tr>';
    $html .= '<tr><td>Total Deposits</td><td>' . $monthly_stats['total_deposits'] . '</td><td>$' . number_format($monthly_stats['deposit_amount'], 2) . '</td></tr>';
    $html .= '<tr><td>Total Withdrawals</td><td>' . $monthly_stats['total_withdrawals'] . '</td><td>$' . number_format($monthly_stats['withdrawal_amount'], 2) . '</td></tr>';
    $html .= '<tr><td>Average Daily Profit</td><td>-</td><td>$' . number_format($monthly_stats['avg_daily_profit'], 2) . '</td></tr>';
    $html .= '</table>';
    
    $html .= '<h2>Support Statistics</h2>';
    $html .= '<table border="1" cellpadding="5" cellspacing="0" style="width: 100%;">';
    $html .= '<tr><th>Metric</th><th>Count</th></tr>';
    $html .= '<tr><td>Total Tickets</td><td>' . $monthly_stats['total_tickets'] . '</td></tr>';
    $html .= '<tr><td>Resolved Tickets</td><td>' . $monthly_stats['resolved_tickets'] . '</td></tr>';
    $html .= '<tr><td>Resolution Rate</td><td>' . number_format($resolution_rate, 1) . '%</td></tr>';
    $html .= '</table>';
    
    echo $html;
}
?>