<?php
$page_title = "Reports & Analytics";
require_once 'header.php';

$db = Database::getInstance()->connect();

// Set default date range
$start_date = $_GET['start_date'] ?? date('Y-m-d', strtotime('-30 days'));
$end_date = $_GET['end_date'] ?? date('Y-m-d');
$report_type = $_GET['report_type'] ?? 'financial';

// Fetch reports based on type
switch ($report_type) {
    case 'financial':
        $title = "Financial Reports";
        break;
    case 'users':
        $title = "User Reports";
        break;
    case 'investments':
        $title = "Investment Reports";
        break;
    case 'referrals':
        $title = "Referral Reports";
        break;
    default:
        $title = "Reports";
}

// Main dashboard stats
$main_stats_sql = "
    SELECT 
        (SELECT COUNT(*) FROM users WHERE DATE(created_at) BETWEEN ? AND ?) as new_users,
        (SELECT SUM(amount) FROM transactions WHERE type = 'deposit' AND status = 'completed' AND DATE(created_at) BETWEEN ? AND ?) as total_deposits,
        (SELECT SUM(amount) FROM transactions WHERE type = 'withdrawal' AND status = 'completed' AND DATE(created_at) BETWEEN ? AND ?) as total_withdrawals,
        (SELECT SUM(amount) FROM investments WHERE DATE(created_at) BETWEEN ? AND ?) as total_investments,
        (SELECT SUM(total_profit) FROM investments WHERE status = 'completed' AND DATE(created_at) BETWEEN ? AND ?) as total_profits,
        (SELECT SUM(amount) FROM transactions WHERE type = 'referral_bonus' AND status = 'completed' AND DATE(created_at) BETWEEN ? AND ?) as total_referral_bonus
";

$main_stats_stmt = $db->prepare($main_stats_sql);
$main_stats_stmt->execute([
    $start_date, $end_date,
    $start_date, $end_date,
    $start_date, $end_date,
    $start_date, $end_date,
    $start_date, $end_date,
    $start_date, $end_date
]);
$main_stats = $main_stats_stmt->fetch();

// Daily stats for chart
$daily_stats_sql = "
    SELECT 
        DATE(created_at) as date,
        COUNT(CASE WHEN type = 'deposit' AND status = 'completed' THEN 1 END) as deposits_count,
        SUM(CASE WHEN type = 'deposit' AND status = 'completed' THEN amount ELSE 0 END) as deposits_amount,
        COUNT(CASE WHEN type = 'withdrawal' AND status = 'completed' THEN 1 END) as withdrawals_count,
        SUM(CASE WHEN type = 'withdrawal' AND status = 'completed' THEN amount ELSE 0 END) as withdrawals_amount,
        COUNT(CASE WHEN type = 'investment' THEN 1 END) as investments_count,
        SUM(CASE WHEN type = 'investment' THEN amount ELSE 0 END) as investments_amount
    FROM transactions 
    WHERE created_at BETWEEN ? AND ?
    GROUP BY DATE(created_at)
    ORDER BY date
";

$daily_stats_stmt = $db->prepare($daily_stats_sql);
$daily_stats_stmt->execute([$start_date . ' 00:00:00', $end_date . ' 23:59:59']);
$daily_stats = $daily_stats_stmt->fetchAll();

// Top users
$top_users_sql = "
    SELECT 
        u.id, 
        u.username, 
        u.email,
        SUM(CASE WHEN t.type = 'deposit' AND t.status = 'completed' THEN t.amount ELSE 0 END) as total_deposits,
        SUM(CASE WHEN t.type = 'withdrawal' AND t.status = 'completed' THEN t.amount ELSE 0 END) as total_withdrawals,
        COUNT(DISTINCT i.id) as total_investments,
        u.balance
    FROM users u
    LEFT JOIN transactions t ON u.id = t.user_id
    LEFT JOIN investments i ON u.id = i.user_id
    WHERE t.created_at BETWEEN ? AND ? OR t.created_at IS NULL
    GROUP BY u.id
    ORDER BY total_deposits DESC
    LIMIT 10
";

$top_users_stmt = $db->prepare($top_users_sql);
$top_users_stmt->execute([$start_date . ' 00:00:00', $end_date . ' 23:59:59']);
$top_users = $top_users_stmt->fetchAll();

// Investment plans performance
$plans_performance_sql = "
    SELECT 
        p.name,
        p.daily_return,
        COUNT(i.id) as total_investments,
        SUM(i.amount) as total_invested,
        AVG(i.amount) as avg_investment,
        SUM(i.total_profit) as total_profits
    FROM investment_plans p
    LEFT JOIN investments i ON p.id = i.plan_id
    WHERE i.created_at BETWEEN ? AND ? OR i.created_at IS NULL
    GROUP BY p.id
    ORDER BY total_invested DESC
";

$plans_stmt = $db->prepare($plans_performance_sql);
$plans_stmt->execute([$start_date . ' 00:00:00', $end_date . ' 23:59:59']);
$plans_performance = $plans_stmt->fetchAll();

// Country stats
$country_stats_sql = "
    SELECT 
        country,
        COUNT(*) as user_count,
        SUM(balance) as total_balance,
        SUM(total_deposit) as total_deposits
    FROM users 
    WHERE country IS NOT NULL AND country != ''
    GROUP BY country
    ORDER BY user_count DESC
    LIMIT 10
";

$country_stmt = $db->query($country_stats_sql);
$country_stats = $country_stmt->fetchAll();
?>

<div class="container-fluid">
    <!-- Page Header -->
    <div class="mb-6">
        <h1 class="text-2xl font-bold">Reports & Analytics</h1>
        <p class="text-gray-600">Comprehensive reports and analytics for your investment platform</p>
    </div>

    <!-- Report Type Selector -->
    <div class="bg-white rounded-xl shadow p-6 mb-6">
        <div class="flex flex-wrap gap-2 mb-6">
            <a href="?report_type=financial&start_date=<?php echo $start_date; ?>&end_date=<?php echo $end_date; ?>" 
               class="px-4 py-2 rounded-lg <?php echo $report_type == 'financial' ? 'bg-purple-600 text-white' : 'bg-gray-100 hover:bg-gray-200'; ?>">
                Financial Reports
            </a>
            <a href="?report_type=users&start_date=<?php echo $start_date; ?>&end_date=<?php echo $end_date; ?>" 
               class="px-4 py-2 rounded-lg <?php echo $report_type == 'users' ? 'bg-purple-600 text-white' : 'bg-gray-100 hover:bg-gray-200'; ?>">
                User Reports
            </a>
            <a href="?report_type=investments&start_date=<?php echo $start_date; ?>&end_date=<?php echo $end_date; ?>" 
               class="px-4 py-2 rounded-lg <?php echo $report_type == 'investments' ? 'bg-purple-600 text-white' : 'bg-gray-100 hover:bg-gray-200'; ?>">
                Investment Reports
            </a>
            <a href="?report_type=referrals&start_date=<?php echo $start_date; ?>&end_date=<?php echo $end_date; ?>" 
               class="px-4 py-2 rounded-lg <?php echo $report_type == 'referrals' ? 'bg-purple-600 text-white' : 'bg-gray-100 hover:bg-gray-200'; ?>">
                Referral Reports
            </a>
        </div>

        <!-- Date Range Selector -->
        <form method="GET" class="grid grid-cols-1 md:grid-cols-4 gap-4">
            <input type="hidden" name="report_type" value="<?php echo $report_type; ?>">
            
            <div>
                <label class="block text-sm mb-1">Start Date</label>
                <input type="date" name="start_date" value="<?php echo $start_date; ?>"
                       class="w-full px-3 py-2 border border-gray-300 rounded-lg">
            </div>
            
            <div>
                <label class="block text-sm mb-1">End Date</label>
                <input type="date" name="end_date" value="<?php echo $end_date; ?>"
                       class="w-full px-3 py-2 border border-gray-300 rounded-lg">
            </div>
            
            <div>
                <label class="block text-sm mb-1">&nbsp;</label>
                <button type="submit" class="w-full bg-purple-600 text-white px-4 py-2 rounded-lg hover:bg-purple-700">
                    <i class="fas fa-filter mr-2"></i>Filter
                </button>
            </div>
            
            <div>
                <label class="block text-sm mb-1">&nbsp;</label>
                <button type="button" onclick="exportReport()" class="w-full bg-blue-600 text-white px-4 py-2 rounded-lg hover:bg-blue-700">
                    <i class="fas fa-download mr-2"></i>Export
                </button>
            </div>
        </form>
    </div>

    <!-- Main Stats Cards -->
    <div class="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-4 gap-4 mb-6">
        <div class="bg-white rounded-xl shadow p-4">
            <div class="flex items-center justify-between">
                <div>
                    <p class="text-gray-600 text-sm">New Users</p>
                    <h3 class="text-2xl font-bold"><?php echo number_format($main_stats['new_users']); ?></h3>
                    <p class="text-xs text-gray-500"><?php echo date('M d', strtotime($start_date)); ?> - <?php echo date('M d, Y', strtotime($end_date)); ?></p>
                </div>
                <i class="fas fa-users text-purple-500 text-2xl"></i>
            </div>
        </div>
        
        <div class="bg-white rounded-xl shadow p-4">
            <div class="flex items-center justify-between">
                <div>
                    <p class="text-gray-600 text-sm">Total Deposits</p>
                    <h3 class="text-2xl font-bold text-green-600">$<?php echo number_format($main_stats['total_deposits'] ?? 0, 2); ?></h3>
                </div>
                <i class="fas fa-money-bill-wave text-green-500 text-2xl"></i>
            </div>
        </div>
        
        <div class="bg-white rounded-xl shadow p-4">
            <div class="flex items-center justify-between">
                <div>
                    <p class="text-gray-600 text-sm">Total Withdrawals</p>
                    <h3 class="text-2xl font-bold text-red-600">$<?php echo number_format($main_stats['total_withdrawals'] ?? 0, 2); ?></h3>
                </div>
                <i class="fas fa-hand-holding-usd text-red-500 text-2xl"></i>
            </div>
        </div>
        
        <div class="bg-white rounded-xl shadow p-4">
            <div class="flex items-center justify-between">
                <div>
                    <p class="text-gray-600 text-sm">Total Investments</p>
                    <h3 class="text-2xl font-bold text-blue-600">$<?php echo number_format($main_stats['total_investments'] ?? 0, 2); ?></h3>
                </div>
                <i class="fas fa-chart-pie text-blue-500 text-2xl"></i>
            </div>
        </div>
    </div>

    <!-- Charts Section -->
    <div class="grid grid-cols-1 lg:grid-cols-2 gap-6 mb-6">
        <!-- Daily Transaction Chart -->
        <div class="bg-white rounded-xl shadow p-6">
            <h3 class="text-lg font-bold mb-4">Daily Transactions</h3>
            <div class="h-80">
                <canvas id="dailyChart"></canvas>
            </div>
        </div>
        
        <!-- Investment Plans Performance -->
        <div class="bg-white rounded-xl shadow p-6">
            <h3 class="text-lg font-bold mb-4">Investment Plans Performance</h3>
            <div class="h-80">
                <canvas id="plansChart"></canvas>
            </div>
        </div>
    </div>

    <!-- Top Users Table -->
    <div class="bg-white rounded-xl shadow mb-6">
        <div class="p-6 border-b">
            <h3 class="text-lg font-bold">Top Users by Deposits</h3>
        </div>
        <div class="overflow-x-auto">
            <table class="w-full">
                <thead class="bg-gray-50">
                    <tr>
                        <th class="p-4 text-left">User</th>
                        <th class="p-4 text-left">Total Deposits</th>
                        <th class="p-4 text-left">Total Withdrawals</th>
                        <th class="p-4 text-left">Investments</th>
                        <th class="p-4 text-left">Current Balance</th>
                        <th class="p-4 text-left">Actions</th>
                    </tr>
                </thead>
                <tbody class="divide-y divide-gray-100">
                    <?php foreach ($top_users as $top_user): ?>
                    <tr class="hover:bg-gray-50">
                        <td class="p-4">
                            <p class="font-medium"><?php echo htmlspecialchars($top_user['username']); ?></p>
                            <p class="text-sm text-gray-500"><?php echo htmlspecialchars($top_user['email']); ?></p>
                        </td>
                        <td class="p-4">
                            <p class="font-bold text-green-600">$<?php echo number_format($top_user['total_deposits'], 2); ?></p>
                        </td>
                        <td class="p-4">
                            <p class="font-bold text-red-600">$<?php echo number_format($top_user['total_withdrawals'], 2); ?></p>
                        </td>
                        <td class="p-4">
                            <p class="font-medium"><?php echo number_format($top_user['total_investments']); ?></p>
                        </td>
                        <td class="p-4">
                            <p class="font-bold">$<?php echo number_format($top_user['balance'], 2); ?></p>
                        </td>
                        <td class="p-4">
                            <a href="user-edit.php?id=<?php echo $top_user['id']; ?>" 
                               class="text-blue-600 hover:text-blue-800 text-sm font-medium">
                                <i class="fas fa-eye mr-1"></i> View
                            </a>
                        </td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>

    <!-- Detailed Reports -->
    <div class="grid grid-cols-1 lg:grid-cols-2 gap-6">
        <!-- Country Statistics -->
        <div class="bg-white rounded-xl shadow">
            <div class="p-6 border-b">
                <h3 class="text-lg font-bold">User Distribution by Country</h3>
            </div>
            <div class="p-6">
                <div class="overflow-x-auto">
                    <table class="w-full">
                        <thead class="bg-gray-50">
                            <tr>
                                <th class="p-3 text-left">Country</th>
                                <th class="p-3 text-left">Users</th>
                                <th class="p-3 text-left">Total Deposits</th>
                                <th class="p-3 text-left">Avg. Balance</th>
                            </tr>
                        </thead>
                        <tbody class="divide-y divide-gray-100">
                            <?php foreach ($country_stats as $country): ?>
                            <tr>
                                <td class="p-3 font-medium"><?php echo htmlspecialchars($country['country']); ?></td>
                                <td class="p-3"><?php echo number_format($country['user_count']); ?></td>
                                <td class="p-3">$<?php echo number_format($country['total_deposits'], 2); ?></td>
                                <td class="p-3">$<?php echo number_format($country['total_balance'] / max(1, $country['user_count']), 2); ?></td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

        <!-- Investment Plans Performance Table -->
        <div class="bg-white rounded-xl shadow">
            <div class="p-6 border-b">
                <h3 class="text-lg font-bold">Investment Plans Performance</h3>
            </div>
            <div class="p-6">
                <div class="overflow-x-auto">
                    <table class="w-full">
                        <thead class="bg-gray-50">
                            <tr>
                                <th class="p-3 text-left">Plan</th>
                                <th class="p-3 text-left">Investments</th>
                                <th class="p-3 text-left">Total Invested</th>
                                <th class="p-3 text-left">Avg. Investment</th>
                                <th class="p-3 text-left">Total Profits</th>
                            </tr>
                        </thead>
                        <tbody class="divide-y divide-gray-100">
                            <?php foreach ($plans_performance as $plan): ?>
                            <tr>
                                <td class="p-3">
                                    <p class="font-medium"><?php echo htmlspecialchars($plan['name']); ?></p>
                                    <p class="text-xs text-gray-500"><?php echo $plan['daily_return']; ?>% daily</p>
                                </td>
                                <td class="p-3"><?php echo number_format($plan['total_investments']); ?></td>
                                <td class="p-3 font-bold">$<?php echo number_format($plan['total_invested'], 2); ?></td>
                                <td class="p-3">$<?php echo number_format($plan['avg_investment'], 2); ?></td>
                                <td class="p-3 text-green-600 font-bold">$<?php echo number_format($plan['total_profits'], 2); ?></td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <!-- Quick Reports -->
    <div class="mt-6 bg-white rounded-xl shadow p-6">
        <h3 class="text-lg font-bold mb-4">Quick Reports</h3>
        <div class="grid grid-cols-1 md:grid-cols-3 gap-4">
            <button onclick="generateReport('weekly')" class="bg-gray-100 hover:bg-gray-200 p-4 rounded-lg text-left">
                <i class="fas fa-calendar-week text-purple-600 text-xl mb-2"></i>
                <p class="font-semibold">Weekly Report</p>
                <p class="text-sm text-gray-600">Generate last week's report</p>
            </button>
            
            <button onclick="generateReport('monthly')" class="bg-gray-100 hover:bg-gray-200 p-4 rounded-lg text-left">
                <i class="fas fa-calendar-alt text-blue-600 text-xl mb-2"></i>
                <p class="font-semibold">Monthly Report</p>
                <p class="text-sm text-gray-600">Generate last month's report</p>
            </button>
            
            <button onclick="generateReport('custom')" class="bg-gray-100 hover:bg-gray-200 p-4 rounded-lg text-left">
                <i class="fas fa-file-alt text-green-600 text-xl mb-2"></i>
                <p class="font-semibold">Custom Report</p>
                <p class="text-sm text-gray-600">Generate custom period report</p>
            </button>
        </div>
    </div>
</div>

<!-- Chart.js -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
// Prepare data for charts
const dailyLabels = <?php echo json_encode(array_column($daily_stats, 'date')); ?>;
const depositAmounts = <?php echo json_encode(array_column($daily_stats, 'deposits_amount')); ?>;
const withdrawalAmounts = <?php echo json_encode(array_column($daily_stats, 'withdrawals_amount')); ?>;

const planLabels = <?php echo json_encode(array_column($plans_performance, 'name')); ?>;
const planInvestments = <?php echo json_encode(array_column($plans_performance, 'total_invested')); ?>;
const planProfits = <?php echo json_encode(array_column($plans_performance, 'total_profits')); ?>;

// Daily Transaction Chart
const dailyCtx = document.getElementById('dailyChart').getContext('2d');
const dailyChart = new Chart(dailyCtx, {
    type: 'line',
    data: {
        labels: dailyLabels,
        datasets: [
            {
                label: 'Deposits',
                data: depositAmounts,
                borderColor: 'rgb(34, 197, 94)',
                backgroundColor: 'rgba(34, 197, 94, 0.1)',
                tension: 0.3,
                fill: true
            },
            {
                label: 'Withdrawals',
                data: withdrawalAmounts,
                borderColor: 'rgb(239, 68, 68)',
                backgroundColor: 'rgba(239, 68, 68, 0.1)',
                tension: 0.3,
                fill: true
            }
        ]
    },
    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.toFixed(2);
                    }
                }
            }
        }
    }
});

// Investment Plans Chart
const plansCtx = document.getElementById('plansChart').getContext('2d');
const plansChart = new Chart(plansCtx, {
    type: 'bar',
    data: {
        labels: planLabels,
        datasets: [
            {
                label: 'Total Invested',
                data: planInvestments,
                backgroundColor: 'rgba(59, 130, 246, 0.5)',
                borderColor: 'rgb(59, 130, 246)',
                borderWidth: 1
            },
            {
                label: 'Total Profits',
                data: planProfits,
                backgroundColor: 'rgba(34, 197, 94, 0.5)',
                borderColor: 'rgb(34, 197, 94)',
                borderWidth: 1
            }
        ]
    },
    options: {
        responsive: true,
        maintainAspectRatio: false,
        plugins: {
            legend: {
                position: 'top',
            },
            tooltip: {
                callbacks: {
                    label: function(context) {
                        return context.dataset.label + ': $' + context.parsed.y.toFixed(2);
                    }
                }
            }
        },
        scales: {
            y: {
                beginAtZero: true,
                ticks: {
                    callback: function(value) {
                        return '$' + value.toFixed(2);
                    }
                }
            }
        }
    }
});

// Export report function
function exportReport() {
    const params = new URLSearchParams(window.location.search);
    window.open(export-report.php?${params.toString()}, '_blank');
}

// Generate quick reports
function generateReport(type) {
    let startDate, endDate;
    const today = new Date();
    
    switch(type) {
        case 'weekly':
            startDate = new Date(today);
            startDate.setDate(today.getDate() - 7);
            break;
        case 'monthly':
            startDate = new Date(today);
            startDate.setMonth(today.getMonth() - 1);
            break;
        case 'custom':
            // Show custom date modal
            showCustomDateModal();
            return;
    }
    
    // Format dates
    const formatDate = (date) => date.toISOString().split('T')[0];
    
    // Redirect with new dates
    window.location.href = ?report_type=<?php echo $report_type; ?>&start_date=${formatDate(startDate)}&end_date=${formatDate(today)};
}

function showCustomDateModal() {
    const modal = `
        <div class="fixed inset-0 bg-black bg-opacity-50 flex items-center justify-center z-50">
            <div class="bg-white rounded-xl p-6 max-w-md w-full">
                <h3 class="text-lg font-bold mb-4">Custom Report Period</h3>
                <div class="space-y-4">
                    <div>
                        <label class="block text-sm mb-1">Start Date</label>
                        <input type="date" id="customStartDate" 
                               class="w-full px-3 py-2 border border-gray-300 rounded-lg">
                    </div>
                    <div>
                        <label class="block text-sm mb-1">End Date</label>
                        <input type="date" id="customEndDate" 
                               class="w-full px-3 py-2 border border-gray-300 rounded-lg">
                    </div>
                </div>
                <div class="mt-6 flex justify-end space-x-2">
                    <button onclick="closeModal()" class="px-4 py-2 text-gray-600 hover:text-gray-800">Cancel</button>
                    <button onclick="submitCustomReport()" class="px-4 py-2 bg-purple-600 text-white rounded-lg hover:bg-purple-700">Generate</button>
                </div>
            </div>
        </div>
    `;
    
    document.body.insertAdjacentHTML('beforeend', modal);
}

function closeModal() {
    const modal = document.querySelector('.fixed.inset-0');
    if (modal) modal.remove();
}

function submitCustomReport() {
    const startDate = document.getElementById('customStartDate').value;
    const endDate = document.getElementById('customEndDate').value;
    
    if (!startDate || !endDate) {
        alert('Please select both start and end dates');
        return;
    }
    
    window.location.href = ?report_type=<?php echo $report_type; ?>&start_date=${startDate}&end_date=${endDate};
}

// Auto-refresh data every 5 minutes
setTimeout(() => {
    window.location.reload();
}, 300000); // 5 minutes
</script>

<?php require_once 'footer.php'; ?>