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

// Date ranges
$today = date('Y-m-d');
$yesterday = date('Y-m-d', strtotime('-1 day'));
$last_week_start = date('Y-m-d', strtotime('-7 days'));
$last_month_start = date('Y-m-d', strtotime('-30 days'));
$month_start = date('Y-m-01');
$month_end = date('Y-m-t');

// Get filter parameters
$date_from = isset($_GET['date_from']) ? $_GET['date_from'] : $month_start;
$date_to = isset($_GET['date_to']) ? $_GET['date_to'] : $today;
$plan_filter = isset($_GET['plan']) ? intval($_GET['plan']) : 0;
$status_filter = isset($_GET['status']) ? $_GET['status'] : '';
?>

<!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">
										<h4 class="box-title">
											<i class="fas fa-chart-line me-2"></i>
											Subscription Analytics
										</h4>
										<div class="box-controls pull-right">
											<button class="btn btn-primary" onclick="exportAnalytics()">
												<i class="fas fa-download me-1"></i> Export Report
											</button>
										</div>
									</div>
									<div class="box-body">
										<!-- Filters -->
										<div class="row mb-20">
											<div class="col-md-10">
												<form method="GET" class="row">
													<div class="col-md-3">
														<div class="form-group">
															<label class="form-label">Date From</label>
															<input type="date" class="form-control" name="date_from" value="<?php echo $date_from; ?>">
														</div>
													</div>
													<div class="col-md-3">
														<div class="form-group">
															<label class="form-label">Date To</label>
															<input type="date" class="form-control" name="date_to" value="<?php echo $date_to; ?>">
														</div>
													</div>
													<div class="col-md-3">
														<div class="form-group">
															<label class="form-label">Bot Plan</label>
															<select class="form-control" name="plan">
																<option value="0">All Plans</option>
																<?php
																$plan_sql = "SELECT * FROM trading_bot_plans WHERE is_active = 1 ORDER BY sort_order";
																$plan_result = mysqli_query($conn, $plan_sql);
																while ($plan = mysqli_fetch_assoc($plan_result)) {
																	$selected = $plan_filter == $plan['id'] ? 'selected' : '';
																	echo "<option value='{$plan['id']}' $selected>{$plan['plan_name']}</option>";
																}
																?>
															</select>
														</div>
													</div>
													<div class="col-md-3">
														<div class="form-group">
															<label class="form-label">Status</label>
															<select class="form-control" name="status">
																<option value="">All Status</option>
																<option value="active" <?php echo $status_filter == 'active' ? 'selected' : ''; ?>>Active</option>
																<option value="expired" <?php echo $status_filter == 'expired' ? 'selected' : ''; ?>>Expired</option>
																<option value="cancelled" <?php echo $status_filter == 'cancelled' ? 'selected' : ''; ?>>Cancelled</option>
																<option value="pending" <?php echo $status_filter == 'pending' ? 'selected' : ''; ?>>Pending</option>
															</select>
														</div>
													</div>
													<div class="col-md-12">
														<button type="submit" class="btn btn-primary">
															<i class="fas fa-filter me-1"></i> Apply Filters
														</button>
														<a href="subscription_analytics.php" class="btn btn-secondary">Reset</a>
														<div class="btn-group ms-2">
															<button type="button" class="btn btn-light dropdown-toggle" data-bs-toggle="dropdown">
																Quick Ranges
															</button>
															<div class="dropdown-menu">
																<a class="dropdown-item" href="subscription_analytics.php?date_from=<?php echo $today; ?>&date_to=<?php echo $today; ?>">Today</a>
																<a class="dropdown-item" href="subscription_analytics.php?date_from=<?php echo $yesterday; ?>&date_to=<?php echo $yesterday; ?>">Yesterday</a>
																<a class="dropdown-item" href="subscription_analytics.php?date_from=<?php echo $last_week_start; ?>&date_to=<?php echo $today; ?>">Last 7 Days</a>
																<a class="dropdown-item" href="subscription_analytics.php?date_from=<?php echo $last_month_start; ?>&date_to=<?php echo $today; ?>">Last 30 Days</a>
																<a class="dropdown-item" href="subscription_analytics.php?date_from=<?php echo $month_start; ?>&date_to=<?php echo $month_end; ?>">This Month</a>
															</div>
														</div>
													</div>
												</form>
											</div>
										</div>

										<!-- Key Metrics -->
										<div class="row mb-30">
											<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 Subscriptions</h5>
																<?php
																$sql = "SELECT COUNT(*) as total FROM user_bot_subscriptions WHERE DATE(subscription_start) BETWEEN '$date_from' AND '$date_to'";
																if ($plan_filter > 0) $sql .= " AND bot_plan_id = $plan_filter";
																if ($status_filter) $sql .= " AND status = '$status_filter'";
																$result = mysqli_query($conn, $sql);
																$row = mysqli_fetch_assoc($result);
																$total_subs = $row['total'];
																?>
																<h2 class="text-white fw-500 mb-0"><?php echo number_format($total_subs); ?></h2>
															</div>
															<div class="bg-white-20 rounded-circle p-15">
																<i class="fas fa-chart-bar 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">Total Revenue</h5>
																<?php
																$sql = "SELECT COALESCE(SUM(subscription_fee), 0) as total 
																		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);
																$row = mysqli_fetch_assoc($result);
																$total_revenue = $row['total'];
																?>
																<h2 class="text-white fw-500 mb-0">$<?php echo number_format($total_revenue, 2); ?></h2>
															</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-warning 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>
																<?php
																$sql = "SELECT COUNT(*) as total FROM user_bot_subscriptions WHERE status = 'active'";
																if ($plan_filter > 0) $sql .= " AND bot_plan_id = $plan_filter";
																if ($date_from != $month_start || $date_to != $today) {
																	$sql .= " AND DATE(subscription_start) BETWEEN '$date_from' AND '$date_to'";
																}
																$result = mysqli_query($conn, $sql);
																$row = mysqli_fetch_assoc($result);
																$active_subs = $row['total'];
																?>
																<h2 class="text-white fw-500 mb-0"><?php echo number_format($active_subs); ?></h2>
															</div>
															<div class="bg-white-20 rounded-circle p-15">
																<i class="fas fa-play-circle 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">Avg. Subscription Value</h5>
																<?php
																$avg_value = $total_subs > 0 ? $total_revenue / $total_subs : 0;
																?>
																<h2 class="text-white fw-500 mb-0">$<?php echo number_format($avg_value, 2); ?></h2>
															</div>
															<div class="bg-white-20 rounded-circle p-15">
																<i class="fas fa-calculator fa-2x text-white"></i>
															</div>
														</div>
													</div>
												</div>
											</div>
										</div>

										<!-- Charts Row -->
										<div class="row mb-30">
											<!-- Revenue Trend Chart -->
											<div class="col-xl-8 col-lg-6">
												<div class="box">
													<div class="box-header">
														<h5 class="box-title"><i class="fas fa-chart-line me-2"></i>Revenue Trend</h5>
													</div>
													<div class="box-body">
														<div id="revenue-trend-chart" style="height: 300px;"></div>
													</div>
												</div>
											</div>

											<!-- Plan Distribution Chart -->
											<div class="col-xl-4 col-lg-6">
												<div class="box">
													<div class="box-header">
														<h5 class="box-title"><i class="fas fa-chart-pie me-2"></i>Plan Distribution</h5>
													</div>
													<div class="box-body">
														<div id="plan-distribution-chart" style="height: 300px;"></div>
													</div>
												</div>
											</div>
										</div>

										<!-- Detailed Statistics -->
										<div class="row mb-30">
											<div class="col-xl-6 col-lg-6">
												<div class="box">
													<div class="box-header">
														<h5 class="box-title"><i class="fas fa-table me-2"></i>Subscription Statistics by Plan</h5>
													</div>
													<div class="box-body">
														<div class="table-responsive">
															<table class="table table-hover">
																<thead>
																	<tr>
																		<th>Plan</th>
																		<th>Subscriptions</th>
																		<th>Revenue</th>
																		<th>Avg. Value</th>
																		<th>Active</th>
																	</tr>
																</thead>
																<tbody>
																	<?php
																	$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'
																			WHERE p.is_active = 1
																			GROUP BY p.id
																			ORDER BY p.sort_order";
																	$result = mysqli_query($conn, $sql);
																	$total_plans = 0;
																	$total_plan_revenue = 0;
																	
																	while ($row = mysqli_fetch_assoc($result)) {
																		$avg_value = $row['total_subs'] > 0 ? $row['revenue'] / $row['total_subs'] : 0;
																		$total_plans += $row['total_subs'];
																		$total_plan_revenue += $row['revenue'];
																		
																		echo "<tr>";
																		echo "<td><strong>{$row['plan_name']}</strong></td>";
																		echo "<td>" . number_format($row['total_subs']) . "</td>";
																		echo "<td>$" . number_format($row['revenue'], 2) . "</td>";
																		echo "<td>$" . number_format($avg_value, 2) . "</td>";
																		echo "<td>" . number_format($row['active_subs']) . "</td>";
																		echo "</tr>";
																	}
																	
																	// Total row
																	$total_avg = $total_plans > 0 ? $total_plan_revenue / $total_plans : 0;
																	echo "<tr class='fw-500' style='background-color: #f8f9fa;'>";
																	echo "<td><strong>TOTAL</strong></td>";
																	echo "<td>" . number_format($total_plans) . "</td>";
																	echo "<td>$" . number_format($total_plan_revenue, 2) . "</td>";
																	echo "<td>$" . number_format($total_avg, 2) . "</td>";
																	echo "<td>-</td>";
																	echo "</tr>";
																	?>
																</tbody>
															</table>
														</div>
													</div>
												</div>
											</div>

											<div class="col-xl-6 col-lg-6">
												<div class="box">
													<div class="box-header">
														<h5 class="box-title"><i class="fas fa-chart-bar me-2"></i>Monthly Performance</h5>
													</div>
													<div class="box-body">
														<div class="table-responsive">
															<table class="table table-hover">
																<thead>
																	<tr>
																		<th>Month</th>
																		<th>New Subscriptions</th>
																		<th>Revenue</th>
																		<th>Growth %</th>
																		<th>Churn Rate</th>
																	</tr>
																</thead>
																<tbody>
																	<?php
																	// Get last 6 months data
																	for ($i = 5; $i >= 0; $i--) {
																		$month = date('Y-m', strtotime("-$i months"));
																		$month_start = date('Y-m-01', strtotime($month));
																		$month_end = date('Y-m-t', strtotime($month));
																		$next_month_start = date('Y-m-01', strtotime("+1 month", strtotime($month)));
																		
																		// New subscriptions this month
																		$sql = "SELECT COUNT(*) 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 '$month_start' AND '$month_end'";
																		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);
																		$current = mysqli_fetch_assoc($result);
																		
																		// Previous month for growth calculation
																		$prev_month = date('Y-m', strtotime("-1 month", strtotime($month)));
																		$prev_month_start = date('Y-m-01', strtotime($prev_month));
																		$prev_month_end = date('Y-m-t', strtotime($prev_month));
																		
																		$sql = "SELECT COALESCE(SUM(p.subscription_fee), 0) as prev_revenue
																				FROM user_bot_subscriptions s
																				JOIN trading_bot_plans p ON s.bot_plan_id = p.id
																				WHERE DATE(s.subscription_start) BETWEEN '$prev_month_start' AND '$prev_month_end'";
																		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);
																		$previous = mysqli_fetch_assoc($result);
																		
																		// Calculate growth
																		$growth = 0;
																		if ($previous['prev_revenue'] > 0) {
																			$growth = (($current['revenue'] - $previous['prev_revenue']) / $previous['prev_revenue']) * 100;
																		}
																		
																		// Churn rate (simplified)
																		$sql = "SELECT COUNT(*) as total 
																				FROM user_bot_subscriptions 
																				WHERE status IN ('expired', 'cancelled') 
																				AND DATE(subscription_end) BETWEEN '$month_start' AND '$month_end'";
																		if ($plan_filter > 0) $sql .= " AND bot_plan_id = $plan_filter";
																		$result = mysqli_query($conn, $sql);
																		$churn = mysqli_fetch_assoc($result);
																		
																		$churn_rate = $current['new_subs'] > 0 ? ($churn['total'] / $current['new_subs']) * 100 : 0;
																		
																		$growth_class = $growth >= 0 ? 'text-success' : 'text-danger';
																		$churn_class = $churn_rate <= 5 ? 'text-success' : ($churn_rate <= 10 ? 'text-warning' : 'text-danger');
																		
																		echo "<tr>";
																		echo "<td><strong>" . date('M Y', strtotime($month)) . "</strong></td>";
																		echo "<td>" . number_format($current['new_subs']) . "</td>";
																		echo "<td>$" . number_format($current['revenue'], 2) . "</td>";
																		echo "<td class='$growth_class'><strong>" . number_format($growth, 1) . "%</strong></td>";
																		echo "<td class='$churn_class'><strong>" . number_format($churn_rate, 1) . "%</strong></td>";
																		echo "</tr>";
																	}
																	?>
																</tbody>
															</table>
														</div>
													</div>
												</div>
											</div>
										</div>

										<!-- Recent Subscriptions -->
										<div class="row">
											<div class="col-12">
												<div class="box">
													<div class="box-header">
														<h5 class="box-title"><i class="fas fa-list me-2"></i>Recent Subscriptions</h5>
													</div>
													<div class="box-body">
														<div class="table-responsive">
															<table class="table table-hover" id="recentSubscriptionsTable">
																<thead>
																	<tr>
																		<th>ID</th>
																		<th>User</th>
																		<th>Plan</th>
																		<th>Amount</th>
																		<th>Status</th>
																		<th>Start Date</th>
																		<th>End Date</th>
																		<th>Profit Generated</th>
																	</tr>
																</thead>
																<tbody>
																	<?php
																	$sql = "SELECT s.*, u.username, u.email, p.plan_name, p.subscription_fee
																			FROM user_bot_subscriptions s
																			JOIN users u ON s.user_id = u.id
																			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'";
																	$sql .= " ORDER BY s.created_at DESC LIMIT 20";
																	
																	$result = mysqli_query($conn, $sql);
																	
																	if (mysqli_num_rows($result) > 0) {
																		while ($row = mysqli_fetch_assoc($result)) {
																			$status_color = $row['status'] == 'active' ? 'success' : 
																						   ($row['status'] == 'expired' ? 'danger' : 
																						   ($row['status'] == 'cancelled' ? 'warning' : 'secondary'));
																			$profit = $row['total_profit'] ?: 0;
																			
																			echo "<tr>";
																			echo "<td>{$row['id']}</td>";
																			echo "<td>
																					<div class='d-flex align-items-center'>
																						<div class='me-2'>
																							<div class='bg-primary rounded-circle w-30 h-30 d-flex align-items-center justify-content-center'>
																								<i class='fas fa-user text-white fs-12'></i>
																							</div>
																						</div>
																						<div>
																							<p class='mb-0 fw-500'>{$row['username']}</p>
																							<small class='text-mute'>{$row['email']}</small>
																						</div>
																					</div>
																				</td>";
																			echo "<td><span class='badge bg-light text-dark'>{$row['plan_name']}</span></td>";
																			echo "<td class='fw-500'>$" . number_format($row['subscription_fee'], 2) . "</td>";
																			echo "<td><span class='badge bg-$status_color'>" . ucfirst($row['status']) . "</span></td>";
																			echo "<td>" . date('M d, Y', strtotime($row['subscription_start'])) . "</td>";
																			echo "<td>" . date('M d, Y', strtotime($row['subscription_end'])) . "</td>";
																			echo "<td class='fw-500 text-success'>$" . number_format($profit, 2) . "</td>";
																			echo "</tr>";
																		}
																	} else {
																		echo "<tr><td colspan='8' class='text-center py-20'>No subscriptions found for the selected period</td></tr>";
																	}
																	?>
																</tbody>
															</table>
														</div>
													</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') ?>
		
		<!-- ApexCharts -->
		<script src="https://cdn.jsdelivr.net/npm/apexcharts"></script>
		
		<script>
		$(document).ready(function() {
			// Initialize DataTable for recent subscriptions
			$('#recentSubscriptionsTable').DataTable({
				"pageLength": 10,
				"order": [[0, 'desc']],
				"language": {
					"search": "Search:",
					"lengthMenu": "Show _MENU_ entries",
					"info": "Showing _START_ to _END_ of _TOTAL_ entries",
					"paginate": {
						"first": "First",
						"last": "Last",
						"next": "Next",
						"previous": "Previous"
					}
				}
			});
			
			// Load charts
			loadRevenueTrendChart();
			loadPlanDistributionChart();
			
			// Auto-refresh charts every 60 seconds
			setInterval(function() {
				loadRevenueTrendChart();
				loadPlanDistributionChart();
			}, 60000);
		});
		
		// Revenue Trend Chart
		function loadRevenueTrendChart() {
			$.ajax({
				url: 'ajax/get_revenue_trend.php',
				type: 'GET',
				data: {
					date_from: '<?php echo $date_from; ?>',
					date_to: '<?php echo $date_to; ?>',
					plan: '<?php echo $plan_filter; ?>',
					status: '<?php echo $status_filter; ?>'
				},
				dataType: 'json',
				success: function(data) {
					if (data.success) {
						var options = {
							series: [{
								name: 'Revenue',
								data: data.revenue
							}],
							chart: {
								type: 'area',
								height: 300,
								toolbar: {
									show: false
								}
							},
							colors: ['#667eea'],
							dataLabels: {
								enabled: false
							},
							stroke: {
								curve: 'smooth',
								width: 2
							},
							fill: {
								type: 'gradient',
								gradient: {
									shadeIntensity: 1,
									opacityFrom: 0.7,
									opacityTo: 0.3,
									stops: [0, 90, 100]
								}
							},
							xaxis: {
								categories: data.labels,
								labels: {
									style: {
										colors: '#8c8c8c'
									}
								}
							},
							yaxis: {
								labels: {
									formatter: function(value) {
										return '$' + value;
									},
									style: {
										colors: '#8c8c8c'
									}
								}
							},
							tooltip: {
								y: {
									formatter: function(value) {
										return '$' + value;
									}
								}
							},
							grid: {
								borderColor: '#f1f1f1'
							}
						};
						
						var chart = new ApexCharts(document.querySelector("#revenue-trend-chart"), options);
						chart.render();
					}
				}
			});
		}
		
		// Plan Distribution Chart
		function loadPlanDistributionChart() {
			$.ajax({
				url: 'ajax/get_plan_distribution.php',
				type: 'GET',
				data: {
					date_from: '<?php echo $date_from; ?>',
					date_to: '<?php echo $date_to; ?>',
					status: '<?php echo $status_filter; ?>'
				},
				dataType: 'json',
				success: function(data) {
					if (data.success) {
						var options = {
							series: data.series,
							chart: {
								type: 'donut',
								height: 300
							},
							labels: data.labels,
							colors: ['#667eea', '#f093fb', '#4facfe', '#43e97b', '#fa709a'],
							legend: {
								position: 'bottom'
							},
							responsive: [{
								breakpoint: 480,
								options: {
									chart: {
										width: 200
									},
									legend: {
										position: 'bottom'
									}
								}
							}]
						};
						
						var chart = new ApexCharts(document.querySelector("#plan-distribution-chart"), options);
						chart.render();
					}
				}
			});
		}
		
		// Export Analytics
		function exportAnalytics() {
			var date_from = '<?php echo $date_from; ?>';
			var date_to = '<?php echo $date_to; ?>';
			var plan = '<?php echo $plan_filter; ?>';
			var status = '<?php echo $status_filter; ?>';
			
			var url = 'export_subscription_analytics.php?date_from=' + date_from + 
					 '&date_to=' + date_to + 
					 '&plan=' + plan + 
					 '&status=' + status;
			
			window.open(url, '_blank');
		}
		</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);
		}
		.table-hover tbody tr:hover {
			background-color: rgba(0,0,0,0.02);
		}
		</style>
		
	</body>
</html>