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

// Get current month and year
$current_month = date('m');
$current_year = date('Y');

// Get selected month/year from URL or use current
$selected_month = isset($_GET['month']) ? intval($_GET['month']) : $current_month;
$selected_year = isset($_GET['year']) ? intval($_GET['year']) : $current_year;

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

// Generate 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'
);
$selected_month_name = $month_names[$selected_month];

// Calculate date ranges
$first_day = date('Y-m-01', strtotime("$selected_year-$selected_month-01"));
$last_day = date('Y-m-t', strtotime("$selected_year-$selected_month-01"));
$total_days = date('t', strtotime($first_day));

// Previous and next month
$prev_month = $selected_month - 1;
$prev_year = $selected_year;
if ($prev_month < 1) {
    $prev_month = 12;
    $prev_year = $selected_year - 1;
}

$next_month = $selected_month + 1;
$next_year = $selected_year;
if ($next_month > 12) {
    $next_month = 1;
    $next_year = $selected_year + 1;
}

// Get monthly statistics
function getMonthlyStats($conn, $year, $month) {
    $stats = array();
    
    // Total Revenue (subscription fees + deposits)
    $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 (from transactions)
    $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 (Revenue - Withdrawals)
    $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;
}

// Get stats for selected month
$monthly_stats = getMonthlyStats($conn, $selected_year, $selected_month);

// Get stats for previous month for comparison
$prev_stats = getMonthlyStats($conn, $prev_year, $prev_month);

// Calculate percentage changes
function calculateChange($current, $previous) {
    if ($previous == 0) return $current > 0 ? 100 : 0;
    return round((($current - $previous) / $previous) * 100, 2);
}

$changes = array();
foreach ($monthly_stats as $key => $value) {
    $changes[$key] = calculateChange($value, $prev_stats[$key] ?: 0);
}

// Get daily data for charts
$daily_data = array();
for ($day = 1; $day <= $total_days; $day++) {
    $date = sprintf("%04d-%02d-%02d", $selected_year, $selected_month, $day);
    
    // Daily revenue
    $sql = "SELECT COALESCE(SUM(amount), 0) as revenue FROM (
        SELECT subscription_fee as amount FROM user_bot_subscriptions WHERE DATE(subscription_start) = '$date'
        UNION ALL
        SELECT amount FROM deposits WHERE DATE(created_at) = '$date' AND status = 'confirmed'
    ) as combined";
    $result = mysqli_query($conn, $sql);
    $row = mysqli_fetch_assoc($result);
    
    // Daily profit
    $sql = "SELECT COALESCE(SUM(amount), 0) as profit FROM transactions WHERE type = 'profit' AND status = 'completed' AND DATE(date) = '$date'";
    $result2 = mysqli_query($conn, $sql);
    $row2 = mysqli_fetch_assoc($result2);
    
    // Daily new users
    $sql = "SELECT COUNT(*) as new_users FROM users WHERE DATE(registration_date) = '$date'";
    $result3 = mysqli_query($conn, $sql);
    $row3 = mysqli_fetch_assoc($result3);
    
    $daily_data[] = array(
        'date' => date('M d', strtotime($date)),
        'revenue' => floatval($row['revenue']),
        'profit' => floatval($row2['profit']),
        'new_users' => intval($row3['new_users'])
    );
}

// Get top performing bot plans
$top_plans_sql = "SELECT p.plan_name, COUNT(s.id) as subscriptions, COALESCE(SUM(s.total_profit), 0) as total_profit 
                  FROM trading_bot_plans p 
                  LEFT JOIN user_bot_subscriptions s ON p.id = s.bot_plan_id AND YEAR(s.subscription_start) = $selected_year AND MONTH(s.subscription_start) = $selected_year
                  WHERE p.is_active = 1
                  GROUP BY p.id 
                  ORDER BY total_profit DESC 
                  LIMIT 5";
$top_plans_result = mysqli_query($conn, $top_plans_sql);

// Get top referrers for the month
$top_referrers_sql = "SELECT u.username, COUNT(r.id) as referrals, COALESCE(SUM(rt.amount), 0) as bonus_paid 
                      FROM users u 
                      LEFT JOIN referrals r ON u.id = r.user_id AND YEAR(r.created_at) = $selected_year AND MONTH(r.created_at) = $selected_year
                      LEFT JOIN referral_transactions rt ON r.id = rt.referred_id AND YEAR(rt.created_at) = $selected_year AND MONTH(rt.created_at) = $selected_year
                      WHERE u.total_referrals > 0
                      GROUP BY u.id 
                      ORDER BY referrals DESC 
                      LIMIT 10";
$top_referrers_result = mysqli_query($conn, $top_referrers_sql);

// Get user growth trend (last 6 months)
$growth_data = array();
for ($i = 5; $i >= 0; $i--) {
    $date = date('Y-m', strtotime("-$i months", strtotime("$selected_year-$selected_month-01")));
    list($year, $month) = explode('-', $date);
    
    $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);
    
    $growth_data[] = array(
        'month' => date('M Y', strtotime("$year-$month-01")),
        'users' => intval($row['count'])
    );
}
?>

<!DOCTYPE html>
<html lang="en">
  	
	
	<body class="hold-transition dark-skin sidebar-mini theme-primary fixed">
		
		<div class="wrapper">
		  	<div id="loader"></div>
		  	
		  <?php include('includes/header.php') ?>

		  <?php include('includes/sidebar.php') ?>

		  	<!-- Content Wrapper. Contains page content -->
			<div class="content-wrapper">
				<div class="container-full">
					<!-- Main content -->
					<section class="content">
						<div class="row">
							<div class="col-12">
								<div class="box">
									<div class="box-header">
										<div class="d-flex justify-content-between align-items-center">
											<div>
												<h4 class="box-title">
													<i class="fas fa-chart-bar me-2"></i>
													Monthly Report: <?php echo $selected_month_name . ' ' . $selected_year; ?>
												</h4>
												<p class="mb-0 text-mute">Comprehensive financial and user analytics report</p>
											</div>
											<div class="d-flex align-items-center">
												<!-- Month/Year Selector -->
												<div class="btn-group me-3">
													<a href="monthly_report.php?month=<?php echo $prev_month; ?>&year=<?php echo $prev_year; ?>" class="btn btn-light">
														<i class="fas fa-chevron-left"></i>
													</a>
													<button type="button" class="btn btn-primary dropdown-toggle" data-bs-toggle="dropdown">
														<?php echo $selected_month_name . ' ' . $selected_year; ?>
													</button>
													<a href="monthly_report.php?month=<?php echo $next_month; ?>&year=<?php echo $next_year; ?>" class="btn btn-light">
														<i class="fas fa-chevron-right"></i>
													</a>
													<div class="dropdown-menu dropdown-menu-end" style="width: 300px; padding: 15px;">
														<form method="GET">
															<div class="row">
																<div class="col-md-6">
																	<div class="mb-3">
																		<label class="form-label">Month</label>
																		<select class="form-control" name="month">
																			<?php foreach ($month_names as $num => $name): ?>
																				<option value="<?php echo $num; ?>" <?php echo $selected_month == $num ? 'selected' : ''; ?>><?php echo $name; ?></option>
																			<?php endforeach; ?>
																		</select>
																	</div>
																</div>
																<div class="col-md-6">
																	<div class="mb-3">
																		<label class="form-label">Year</label>
																		<select class="form-control" name="year">
																			<?php for ($y = 2020; $y <= date('Y') + 1; $y++): ?>
																				<option value="<?php echo $y; ?>" <?php echo $selected_year == $y ? 'selected' : ''; ?>><?php echo $y; ?></option>
																			<?php endfor; ?>
																		</select>
																	</div>
																</div>
															</div>
															<button type="submit" class="btn btn-primary w-100">Generate Report</button>
														</form>
													</div>
												</div>
												
												<!-- Export Button -->
												<div class="dropdown">
													<button class="btn btn-success dropdown-toggle" type="button" data-bs-toggle="dropdown">
														<i class="fas fa-download me-1"></i> Export
													</button>
													<div class="dropdown-menu">
														<a class="dropdown-item" href="export_monthly_report.php?month=<?php echo $selected_month; ?>&year=<?php echo $selected_year; ?>&format=csv"><i class="fas fa-file-csv"></i> CSV</a>
														<a class="dropdown-item" href="export_monthly_report.php?month=<?php echo $selected_month; ?>&year=<?php echo $selected_year; ?>&format=excel"><i class="fas fa-file-excel"></i> Excel</a>
														<a class="dropdown-item" href="export_monthly_report.php?month=<?php echo $selected_month; ?>&year=<?php echo $selected_year; ?>&format=pdf"><i class="fas fa-file-pdf"></i> PDF</a>
														<a class="dropdown-item" href="#" onclick="printReport()"><i class="fas fa-print"></i> Print</a>
													</div>
												</div>
											</div>
										</div>
									</div>
									<div class="box-body">
										<!-- Summary Stats -->
										<div class="row mb-20">
											<div class="col-xl-3 col-md-6 col-12">
												<div class="box bg-gradient-primary pull-up">
													<div class="box-body">
														<div class="d-flex justify-content-between align-items-center">
															<div>
																<h5 class="text-white mb-0">Total Revenue</h5>
																<h2 class="text-white fw-500 mb-0">$<?php echo number_format($monthly_stats['total_revenue'], 2); ?></h2>
																<p class="text-white-50 mb-0">
																	<?php 
																	$change_class = $changes['total_revenue'] >= 0 ? 'text-success' : 'text-danger';
																	$change_icon = $changes['total_revenue'] >= 0 ? 'fa-arrow-up' : 'fa-arrow-down';
																	?>
																	<i class="fas <?php echo $change_icon; ?> me-1"></i>
																	<span class="<?php echo $change_class; ?>"><?php echo abs($changes['total_revenue']); ?>%</span> from last month
																</p>
															</div>
															<div class="bg-white-20 rounded-circle p-15">
																<i class="fas fa-money-bill-wave fa-2x text-white"></i>
															</div>
														</div>
													</div>
												</div>
											</div>
											
											<div class="col-xl-3 col-md-6 col-12">
												<div class="box bg-gradient-success pull-up">
													<div class="box-body">
														<div class="d-flex justify-content-between align-items-center">
															<div>
																<h5 class="text-white mb-0">Net Profit</h5>
																<h2 class="text-white fw-500 mb-0">$<?php echo number_format($monthly_stats['net_profit'], 2); ?></h2>
																<p class="text-white-50 mb-0">
																	<?php 
																	$change_class = $changes['net_profit'] >= 0 ? 'text-success' : 'text-danger';
																	$change_icon = $changes['net_profit'] >= 0 ? 'fa-arrow-up' : 'fa-arrow-down';
																	?>
																	<i class="fas <?php echo $change_icon; ?> me-1"></i>
																	<span class="<?php echo $change_class; ?>"><?php echo abs($changes['net_profit']); ?>%</span> from last month
																</p>
															</div>
															<div class="bg-white-20 rounded-circle p-15">
																<i class="fas fa-chart-line fa-2x text-white"></i>
															</div>
														</div>
													</div>
												</div>
											</div>
											
											<div class="col-xl-3 col-md-6 col-12">
												<div class="box bg-gradient-warning pull-up">
													<div class="box-body">
														<div class="d-flex justify-content-between align-items-center">
															<div>
																<h5 class="text-white mb-0">New Users</h5>
																<h2 class="text-white fw-500 mb-0"><?php echo number_format($monthly_stats['new_users']); ?></h2>
																<p class="text-white-50 mb-0">
																	<?php 
																	$change_class = $changes['new_users'] >= 0 ? 'text-success' : 'text-danger';
																	$change_icon = $changes['new_users'] >= 0 ? 'fa-arrow-up' : 'fa-arrow-down';
																	?>
																	<i class="fas <?php echo $change_icon; ?> me-1"></i>
																	<span class="<?php echo $change_class; ?>"><?php echo abs($changes['new_users']); ?>%</span> from last month
																</p>
															</div>
															<div class="bg-white-20 rounded-circle p-15">
																<i class="fas fa-user-plus fa-2x text-white"></i>
															</div>
														</div>
													</div>
												</div>
											</div>
											
											<div class="col-xl-3 col-md-6 col-12">
												<div class="box bg-gradient-info pull-up">
													<div class="box-body">
														<div class="d-flex justify-content-between align-items-center">
															<div>
																<h5 class="text-white mb-0">Active Subscriptions</h5>
																<h2 class="text-white fw-500 mb-0"><?php echo number_format($monthly_stats['active_subscriptions']); ?></h2>
																<p class="text-white-50 mb-0">
																	<?php 
																	$change_class = $changes['active_subscriptions'] >= 0 ? 'text-success' : 'text-danger';
																	$change_icon = $changes['active_subscriptions'] >= 0 ? 'fa-arrow-up' : 'fa-arrow-down';
																	?>
																	<i class="fas <?php echo $change_icon; ?> me-1"></i>
																	<span class="<?php echo $change_class; ?>"><?php echo abs($changes['active_subscriptions']); ?>%</span> from last month
																</p>
															</div>
															<div class="bg-white-20 rounded-circle p-15">
																<i class="fas fa-robot fa-2x text-white"></i>
															</div>
														</div>
													</div>
												</div>
											</div>
										</div>
										
										<!-- Charts Row -->
										<div class="row">
											<!-- Daily Revenue & Profit Chart -->
											<div class="col-xl-8 col-lg-6">
												<div class="box b-1">
													<div class="box-header">
														<h5 class="box-title"><i class="fas fa-chart-line me-2"></i>Daily Revenue & Profit Trend</h5>
													</div>
													<div class="box-body">
														<canvas id="dailyChart" height="250"></canvas>
													</div>
												</div>
											</div>
											
											<!-- Profit Breakdown -->
											<div class="col-xl-4 col-lg-6">
												<div class="box b-1">
													<div class="box-header">
														<h5 class="box-title"><i class="fas fa-chart-pie me-2"></i>Profit Breakdown</h5>
													</div>
													<div class="box-body">
														<div class="row">
															<div class="col-6">
																<div class="text-center p-3 bg-light rounded mb-3">
																	<h3 class="fw-500 text-primary">$<?php echo number_format($monthly_stats['total_profit'], 2); ?></h3>
																	<p class="text-mute mb-0">Total Profit</p>
																</div>
															</div>
															<div class="col-6">
																<div class="text-center p-3 bg-light rounded mb-3">
																	<h3 class="fw-500 text-success">$<?php echo number_format($monthly_stats['avg_daily_profit'], 2); ?></h3>
																	<p class="text-mute mb-0">Avg Daily Profit</p>
																</div>
															</div>
														</div>
														<div class="mt-3">
															<p><strong>Profit Margin:</strong> <span class="badge bg-<?php echo $monthly_stats['profit_margin'] >= 20 ? 'success' : ($monthly_stats['profit_margin'] >= 10 ? 'warning' : 'danger'); ?>">
																<?php echo number_format($monthly_stats['profit_margin'], 2); ?>%
															</span></p>
															<div class="progress mb-3" style="height: 10px;">
																<div class="progress-bar bg-success" style="width: <?php echo min($monthly_stats['profit_margin'], 100); ?>%"></div>
															</div>
														</div>
													</div>
												</div>
											</div>
										</div>
										
										<!-- Detailed Statistics -->
										<div class="row">
											<div class="col-lg-6">
												<div class="box">
													<div class="box-header">
														<h5 class="box-title"><i class="fas fa-exchange-alt me-2"></i>Transaction Statistics</h5>
													</div>
													<div class="box-body">
														<div class="table-responsive">
															<table class="table table-bordered">
																<thead class="bg-light">
																	<tr>
																		<th>Metric</th>
																		<th class="text-end">Count</th>
																		<th class="text-end">Amount</th>
																		<th class="text-end">Change</th>
																	</tr>
																</thead>
																<tbody>
																	<tr>
																		<td>Total Deposits</td>
																		<td class="text-end"><?php echo number_format($monthly_stats['total_deposits']); ?></td>
																		<td class="text-end">$<?php echo number_format($monthly_stats['deposit_amount'], 2); ?></td>
																		<td class="text-end">
																			<span class="badge bg-<?php echo $changes['deposit_amount'] >= 0 ? 'success' : 'danger'; ?>">
																				<i class="fas fa-arrow-<?php echo $changes['deposit_amount'] >= 0 ? 'up' : 'down'; ?> me-1"></i>
																				<?php echo abs($changes['deposit_amount']); ?>%
																			</span>
																		</td>
																	</tr>
																	<tr>
																		<td>Total Withdrawals</td>
																		<td class="text-end"><?php echo number_format($monthly_stats['total_withdrawals']); ?></td>
																		<td class="text-end">$<?php echo number_format($monthly_stats['withdrawal_amount'], 2); ?></td>
																		<td class="text-end">
																			<span class="badge bg-<?php echo $changes['withdrawal_amount'] >= 0 ? 'success' : 'danger'; ?>">
																				<i class="fas fa-arrow-<?php echo $changes['withdrawal_amount'] >= 0 ? 'up' : 'down'; ?> me-1"></i>
																				<?php echo abs($changes['withdrawal_amount']); ?>%
																			</span>
																		</td>
																	</tr>
																	<tr>
																		<td>Total Profit</td>
																		<td class="text-end">-</td>
																		<td class="text-end">$<?php echo number_format($monthly_stats['total_profit'], 2); ?></td>
																		<td class="text-end">
																			<span class="badge bg-<?php echo $changes['total_profit'] >= 0 ? 'success' : 'danger'; ?>">
																				<i class="fas fa-arrow-<?php echo $changes['total_profit'] >= 0 ? 'up' : 'down'; ?> me-1"></i>
																				<?php echo abs($changes['total_profit']); ?>%
																			</span>
																		</td>
																	</tr>
																	<tr>
																		<td>Net Profit</td>
																		<td class="text-end">-</td>
																		<td class="text-end">$<?php echo number_format($monthly_stats['net_profit'], 2); ?></td>
																		<td class="text-end">
																			<span class="badge bg-<?php echo $changes['net_profit'] >= 0 ? 'success' : 'danger'; ?>">
																				<i class="fas fa-arrow-<?php echo $changes['net_profit'] >= 0 ? 'up' : 'down'; ?> me-1"></i>
																				<?php echo abs($changes['net_profit']); ?>%
																			</span>
																		</td>
																	</tr>
																</tbody>
															</table>
														</div>
													</div>
												</div>
											</div>
											
											<div class="col-lg-6">
												<div class="box">
													<div class="box-header">
														<h5 class="box-title"><i class="fas fa-users me-2"></i>User & Support Statistics</h5>
													</div>
													<div class="box-body">
														<div class="table-responsive">
															<table class="table table-bordered">
																<thead class="bg-light">
																	<tr>
																		<th>Metric</th>
																		<th class="text-end">Count</th>
																		<th class="text-end">Change</th>
																	</tr>
																</thead>
																<tbody>
																	<tr>
																		<td>New Users</td>
																		<td class="text-end"><?php echo number_format($monthly_stats['new_users']); ?></td>
																		<td class="text-end">
																			<span class="badge bg-<?php echo $changes['new_users'] >= 0 ? 'success' : 'danger'; ?>">
																				<i class="fas fa-arrow-<?php echo $changes['new_users'] >= 0 ? 'up' : 'down'; ?> me-1"></i>
																				<?php echo abs($changes['new_users']); ?>%
																			</span>
																		</td>
																	</tr>
																	<tr>
																		<td>Active Subscriptions</td>
																		<td class="text-end"><?php echo number_format($monthly_stats['active_subscriptions']); ?></td>
																		<td class="text-end">
																			<span class="badge bg-<?php echo $changes['active_subscriptions'] >= 0 ? 'success' : 'danger'; ?>">
																				<i class="fas fa-arrow-<?php echo $changes['active_subscriptions'] >= 0 ? 'up' : 'down'; ?> me-1"></i>
																				<?php echo abs($changes['active_subscriptions']); ?>%
																			</span>
																		</td>
																	</tr>
																	<tr>
																		<td>Support Tickets</td>
																		<td class="text-end"><?php echo number_format($monthly_stats['total_tickets']); ?></td>
																		<td class="text-end">
																			<span class="badge bg-<?php echo $changes['total_tickets'] >= 0 ? 'success' : 'danger'; ?>">
																				<i class="fas fa-arrow-<?php echo $changes['total_tickets'] >= 0 ? 'up' : 'down'; ?> me-1"></i>
																				<?php echo abs($changes['total_tickets']); ?>%
																			</span>
																		</td>
																	</tr>
																	<tr>
																		<td>Resolved Tickets</td>
																		<td class="text-end"><?php echo number_format($monthly_stats['resolved_tickets']); ?></td>
																		<td class="text-end">
																			<span class="badge bg-<?php echo $changes['resolved_tickets'] >= 0 ? 'success' : 'danger'; ?>">
																				<i class="fas fa-arrow-<?php echo $changes['resolved_tickets'] >= 0 ? 'up' : 'down'; ?> me-1"></i>
																				<?php echo abs($changes['resolved_tickets']); ?>%
																			</span>
																		</td>
																	</tr>
																	<tr>
																		<td>Resolution Rate</td>
																		<td class="text-end">
																			<?php 
																			$resolution_rate = $monthly_stats['total_tickets'] > 0 ? ($monthly_stats['resolved_tickets'] / $monthly_stats['total_tickets']) * 100 : 0;
																			echo number_format($resolution_rate, 1); ?>%
																		</td>
																		<td class="text-end">
																			<span class="badge bg-<?php echo $resolution_rate >= 80 ? 'success' : ($resolution_rate >= 60 ? 'warning' : 'danger'); ?>">
																				<?php echo number_format($resolution_rate, 1); ?>%
																			</span>
																		</td>
																	</tr>
																</tbody>
															</table>
														</div>
													</div>
												</div>
											</div>
										</div>
										
										<!-- Top Performers -->
										<div class="row">
											<!-- Top Bot Plans -->
											<div class="col-lg-6">
												<div class="box">
													<div class="box-header">
														<h5 class="box-title"><i class="fas fa-trophy me-2"></i>Top Performing Bot Plans</h5>
													</div>
													<div class="box-body">
														<div class="table-responsive">
															<table class="table table-hover">
																<thead>
																	<tr>
																		<th>Plan</th>
																		<th class="text-end">Subscriptions</th>
																		<th class="text-end">Total Profit</th>
																	</tr>
																</thead>
																<tbody>
																	<?php if (mysqli_num_rows($top_plans_result) > 0): ?>
																		<?php while ($plan = mysqli_fetch_assoc($top_plans_result)): ?>
																			<tr>
																				<td>
																					<div class="d-flex align-items-center">
																						<div class="me-3">
																							<div class="bg-primary rounded-circle w-40 h-40 d-flex align-items-center justify-content-center">
																								<i class="fas fa-robot text-white"></i>
																							</div>
																						</div>
																						<div>
																							<p class="mb-0 fw-500"><?php echo htmlspecialchars($plan['plan_name']); ?></p>
																						</div>
																					</div>
																				</td>
																				<td class="text-end fw-500"><?php echo number_format($plan['subscriptions']); ?></td>
																				<td class="text-end fw-500 text-success">$<?php echo number_format($plan['total_profit'], 2); ?></td>
																			</tr>
																		<?php endwhile; ?>
																	<?php else: ?>
																		<tr>
																			<td colspan="3" class="text-center py-20">No subscription data available</td>
																		</tr>
																	<?php endif; ?>
																</tbody>
															</table>
														</div>
													</div>
												</div>
											</div>
											
											<!-- Top Referrers -->
											<div class="col-lg-6">
												<div class="box">
													<div class="box-header">
														<h5 class="box-title"><i class="fas fa-user-friends me-2"></i>Top Referrers</h5>
													</div>
													<div class="box-body">
														<div class="table-responsive">
															<table class="table table-hover">
																<thead>
																	<tr>
																		<th>User</th>
																		<th class="text-end">Referrals</th>
																		<th class="text-end">Bonus Paid</th>
																	</tr>
																</thead>
																<tbody>
																	<?php if (mysqli_num_rows($top_referrers_result) > 0): ?>
																		<?php while ($referrer = mysqli_fetch_assoc($top_referrers_result)): ?>
																			<tr>
																				<td>
																					<div class="d-flex align-items-center">
																						<div class="me-3">
																							<div class="bg-success rounded-circle w-40 h-40 d-flex align-items-center justify-content-center">
																								<i class="fas fa-user text-white"></i>
																							</div>
																						</div>
																						<div>
																							<p class="mb-0 fw-500"><?php echo htmlspecialchars($referrer['username']); ?></p>
																						</div>
																					</div>
																				</td>
																				<td class="text-end fw-500"><?php echo number_format($referrer['referrals']); ?></td>
																				<td class="text-end fw-500 text-success">$<?php echo number_format($referrer['bonus_paid'], 2); ?></td>
																			</tr>
																		<?php endwhile; ?>
																	<?php else: ?>
																		<tr>
																			<td colspan="3" class="text-center py-20">No referral data available</td>
																		</tr>
																	<?php endif; ?>
																</tbody>
															</table>
														</div>
													</div>
												</div>
											</div>
										</div>
										
										<!-- User Growth Chart -->
										<div class="row">
											<div class="col-12">
												<div class="box">
													<div class="box-header">
														<h5 class="box-title"><i class="fas fa-chart-area me-2"></i>User Growth Trend (Last 6 Months)</h5>
													</div>
													<div class="box-body">
														<canvas id="growthChart" height="100"></canvas>
													</div>
												</div>
											</div>
										</div>
										
										<!-- Summary Notes -->
										<div class="row">
											<div class="col-12">
												<div class="box">
													<div class="box-header">
														<h5 class="box-title"><i class="fas fa-sticky-note me-2"></i>Monthly Summary & Notes</h5>
													</div>
													<div class="box-body">
														<form method="POST" action="save_monthly_notes.php">
															<input type="hidden" name="month" value="<?php echo $selected_month; ?>">
															<input type="hidden" name="year" value="<?php echo $selected_year; ?>">
															
															<div class="row">
																<div class="col-md-6">
																	<div class="mb-3">
																		<label class="form-label">Overall Performance</label>
																		<select class="form-control" name="performance">
																			<option value="excellent">Excellent</option>
																			<option value="good" selected>Good</option>
																			<option value="average">Average</option>
																			<option value="poor">Poor</option>
																			<option value="critical">Critical</option>
																		</select>
																	</div>
																</div>
																<div class="col-md-6">
																	<div class="mb-3">
																		<label class="form-label">Key Achievement</label>
																		<input type="text" class="form-control" name="achievement" placeholder="e.g., Reached 1000 active users">
																	</div>
																</div>
															</div>
															
															<div class="mb-3">
																<label class="form-label">Monthly Notes</label>
																<textarea class="form-control" name="notes" rows="4" placeholder="Enter monthly summary, observations, and action items..."></textarea>
															</div>
															
															<div class="mb-3">
																<label class="form-label">Admin PIN <span class="text-danger">*</span></label>
																<input type="password" class="form-control" name="admin_pin" placeholder="Enter your 4-digit PIN" required>
															</div>
															
															<div class="text-end">
																<button type="submit" class="btn btn-primary">
																	<i class="fas fa-save me-1"></i> Save Summary
																</button>
															</div>
														</form>
													</div>
												</div>
											</div>
										</div>
									</div>
								</div>
							</div>
						</div>
					</section>
					<!-- /.content -->
				</div>
			</div>
		  	
		  <?php include('includes/footer.php') ?>

		  <?php include('includes/control_sidebar.php') ?>
			
		</div>
		<!-- ./wrapper -->
		
		  
    
		
		
		  <?php include('includes/scripts.php') ?>
		
		<!-- Chart.js -->
		<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
		
		<script>
		$(document).ready(function() {
			// Daily Revenue & Profit Chart
			var dailyCtx = document.getElementById('dailyChart').getContext('2d');
			var dailyChart = new Chart(dailyCtx, {
				type: 'line',
				data: {
					labels: [
						<?php 
						$labels = array();
						foreach ($daily_data as $day) {
							$labels[] = "'" . $day['date'] . "'";
						}
						echo implode(', ', $labels);
						?>
					],
					datasets: [{
						label: 'Revenue ($)',
						data: [
							<?php 
							$revenue_data = array();
							foreach ($daily_data as $day) {
								$revenue_data[] = $day['revenue'];
							}
							echo implode(', ', $revenue_data);
							?>
						],
						borderColor: '#667eea',
						backgroundColor: 'rgba(102, 126, 234, 0.1)',
						borderWidth: 2,
						fill: true,
						tension: 0.4
					}, {
						label: 'Profit ($)',
						data: [
							<?php 
							$profit_data = array();
							foreach ($daily_data as $day) {
								$profit_data[] = $day['profit'];
							}
							echo implode(', ', $profit_data);
							?>
						],
						borderColor: '#f093fb',
						backgroundColor: 'rgba(240, 147, 251, 0.1)',
						borderWidth: 2,
						fill: true,
						tension: 0.4
					}]
				},
				options: {
					responsive: true,
					maintainAspectRatio: false,
					plugins: {
						legend: {
							position: 'top',
						},
						tooltip: {
							mode: 'index',
							intersect: false,
							callbacks: {
								label: function(context) {
									return context.dataset.label + ': $' + context.parsed.y.toFixed(2);
								}
							}
						}
					},
					scales: {
						y: {
							beginAtZero: true,
							ticks: {
								callback: function(value) {
									return '$' + value;
								}
							}
						}
					}
				}
			});
			
			// User Growth Chart
			var growthCtx = document.getElementById('growthChart').getContext('2d');
			var growthChart = new Chart(growthCtx, {
				type: 'bar',
				data: {
					labels: [
						<?php 
						$growth_labels = array();
						foreach ($growth_data as $data) {
							$growth_labels[] = "'" . $data['month'] . "'";
						}
						echo implode(', ', $growth_labels);
						?>
					],
					datasets: [{
						label: 'New Users',
						data: [
							<?php 
							$growth_values = array();
							foreach ($growth_data as $data) {
								$growth_values[] = $data['users'];
							}
							echo implode(', ', $growth_values);
							?>
						],
						backgroundColor: '#4facfe',
						borderColor: '#00f2fe',
						borderWidth: 1
					}]
				},
				options: {
					responsive: true,
					maintainAspectRatio: false,
					plugins: {
						legend: {
							display: false
						}
					},
					scales: {
						y: {
							beginAtZero: true,
							ticks: {
								stepSize: 1
							}
						}
					}
				}
			});
			
			// Auto-refresh report every 5 minutes
			setInterval(function() {
				location.reload();
			}, 300000); // 5 minutes
		});
		
		// Print Report
		function printReport() {
			var printContent = document.querySelector('.box').outerHTML;
			var originalContent = document.body.innerHTML;
			
			document.body.innerHTML = `
				<!DOCTYPE html>
				<html>
				<head>
					<title>Monthly Report - <?php echo $selected_month_name . ' ' . $selected_year; ?></title>
					<style>
					body { font-family: Arial, sans-serif; margin: 20px; }
					.table { width: 100%; border-collapse: collapse; margin-bottom: 20px; }
					.table th, .table td { border: 1px solid #ddd; padding: 8px; text-align: left; }
					.table th { background-color: #f5f5f5; }
					.badge { padding: 3px 8px; border-radius: 4px; font-size: 12px; }
					.text-end { text-align: right; }
					.fw-500 { font-weight: 500; }
					.mb-20 { margin-bottom: 20px; }
					.mt-20 { margin-top: 20px; }
					</style>
				</head>
				<body>
					<h1>Monthly Report: <?php echo $selected_month_name . ' ' . $selected_year; ?></h1>
					<p>Generated on: <?php echo date('F d, Y H:i:s'); ?></p>
					${printContent}
				</body>
				</html>
			`;
			
			window.print();
			document.body.innerHTML = originalContent;
			location.reload();
		}
		
		// Export to PDF (using jsPDF)
		function exportToPDF() {
			// You can implement jsPDF here if needed
			alert('PDF export feature requires jsPDF library installation.');
		}
		</script>
		
		<style>
		.pull-up {
			transition: transform 0.3s ease;
		}
		.pull-up:hover {
			transform: translateY(-5px);
		}
		.bg-white-20 {
			background-color: rgba(255, 255, 255, 0.2);
		}
		.box {
			border-radius: 10px;
			overflow: hidden;
		}
		.table-hover tbody tr:hover {
			background-color: rgba(0,0,0,0.02);
		}
		</style>
		
	</body>
</html>