<?php
/**
 * Binance P2P Statistics Dashboard
 * View order statistics and payment analytics
 */

// Enable error reporting for debugging
error_reporting(E_ALL);
ini_set('display_errors', 0);
ini_set('log_errors', 1);

try {
    require_once __DIR__ . '/../includes/config.php';
    require_once __DIR__ . '/../includes/functions.php';
    require_once __DIR__ . '/../includes/binance-api.php';
    require_once __DIR__ . '/../includes/binance-settings.php';
} catch (Throwable $e) {
    error_log("Error loading includes in statistics.php: " . $e->getMessage());
    die("Error loading page configuration. Please check error logs.");
}

require_login();

// Get time period filter
$period = $_GET['period'] ?? 'today';
$validPeriods = ['last_hour', 'today', 'yesterday', 'week', 'month', 'all_time'];
if (!in_array($period, $validPeriods)) {
    $period = 'today';
}

// Calculate date ranges (using Nairobi timezone)
// Note: Database stores UTC, so we need to convert Nairobi time to UTC for queries
$nairobiTz = new DateTimeZone('Africa/Nairobi');
$utcTz = new DateTimeZone('UTC');

// Helper function to convert Nairobi time to UTC for database queries
$toUtc = function($nairobiTime) use ($nairobiTz, $utcTz) {
    $dt = new DateTime($nairobiTime, $nairobiTz);
    $dt->setTimezone($utcTz);
    return $dt->format('Y-m-d H:i:s');
};

// Get current time in Nairobi
$nowNairobi = new DateTime('now', $nairobiTz);
$nowNairobiStr = $nowNairobi->format('Y-m-d H:i:s');
$todayStartNairobi = $nowNairobi->format('Y-m-d') . ' 00:00:00';
$todayEndNairobi = $nowNairobiStr;

// Calculate date ranges (convert Nairobi time to UTC for database queries)
$dateRanges = [
    'last_hour' => [
        'start' => $toUtc((clone $nowNairobi)->modify('-1 hour')->format('Y-m-d H:i:s')),
        'end' => $toUtc($nowNairobiStr)
    ],
    'today' => [
        'start' => $toUtc($todayStartNairobi),
        'end' => $toUtc($todayEndNairobi)
    ],
    'yesterday' => [
        'start' => $toUtc((clone $nowNairobi)->modify('-1 day')->format('Y-m-d') . ' 00:00:00'),
        'end' => $toUtc((clone $nowNairobi)->modify('-1 day')->format('Y-m-d') . ' 23:59:59')
    ],
    'week' => [
        'start' => $toUtc((clone $nowNairobi)->modify('-7 days')->format('Y-m-d') . ' 00:00:00'),
        'end' => $toUtc($nowNairobiStr)
    ],
    'month' => [
        'start' => $toUtc((clone $nowNairobi)->modify('-30 days')->format('Y-m-d') . ' 00:00:00'),
        'end' => $toUtc($nowNairobiStr)
    ],
    'all_time' => [
        'start' => '1970-01-01 00:00:00',
        'end' => $toUtc($nowNairobiStr)
    ]
];

$dateRange = $dateRanges[$period];

try {
    // Get statistics for the selected period
    $statsQuery = "
        SELECT 
            COUNT(*) as total_orders,
            SUM(CASE WHEN payment_status = 'completed' THEN 1 ELSE 0 END) as completed_orders,
            SUM(CASE WHEN payment_status = 'pending' THEN 1 ELSE 0 END) as pending_orders,
            SUM(CASE WHEN payment_status = 'processing' THEN 1 ELSE 0 END) as processing_orders,
            SUM(CASE WHEN payment_status = 'failed' THEN 1 ELSE 0 END) as failed_orders,
            SUM(CASE WHEN requires_manual_review = 1 THEN 1 ELSE 0 END) as manual_review_orders,
            SUM(CASE WHEN fiat_currency = 'EUR' AND order_status = 'COMPLETED' THEN fiat_amount ELSE 0 END) as total_eur_sent,
            SUM(CASE WHEN fiat_currency = 'EUR' AND payment_status = 'completed' AND order_status = 'COMPLETED' THEN fiat_amount ELSE 0 END) as completed_eur_sent,
            SUM(CASE WHEN order_status = 'COMPLETED' THEN fiat_amount ELSE 0 END) as total_fiat_sent,
            SUM(usdc_amount_sent) as total_usdc_sent,
            AVG(fiat_amount) as avg_order_value,
            MIN(fiat_amount) as min_order_value,
            MAX(fiat_amount) as max_order_value,
            COUNT(DISTINCT buyer_user_id) as unique_buyers,
            SUM(CASE WHEN tx_hash IS NOT NULL AND tx_hash != '' THEN 1 ELSE 0 END) as usdc_transactions_sent
        FROM binance_p2p_orders
        WHERE created_at >= ? AND created_at <= ?
    ";
    
    $statsStmt = $pdo->prepare($statsQuery);
    $statsStmt->execute([$dateRange['start'], $dateRange['end']]);
    $stats = $statsStmt->fetch(PDO::FETCH_ASSOC);
    
    // Get today's statistics separately (using Nairobi timezone)
    $nairobiTz = new DateTimeZone('Africa/Nairobi');
    $utcTz = new DateTimeZone('UTC');
    $nowNairobi = new DateTime('now', $nairobiTz);
    $todayStartNairobi = $nowNairobi->format('Y-m-d') . ' 00:00:00';
    $todayEndNairobi = $nowNairobi->format('Y-m-d H:i:s');
    
    // Convert to UTC for database query
    $todayStartDt = new DateTime($todayStartNairobi, $nairobiTz);
    $todayStartDt->setTimezone($utcTz);
    $todayStart = $todayStartDt->format('Y-m-d H:i:s');
    
    $todayEndDt = new DateTime($todayEndNairobi, $nairobiTz);
    $todayEndDt->setTimezone($utcTz);
    $todayEnd = $todayEndDt->format('Y-m-d H:i:s');
    $todayStatsQuery = "
        SELECT 
            COUNT(*) as today_orders,
            SUM(CASE WHEN fiat_currency = 'EUR' AND order_status = 'COMPLETED' THEN fiat_amount ELSE 0 END) as today_eur_sent
        FROM binance_p2p_orders
        WHERE created_at >= ? AND created_at <= ?
    ";
    $todayStatsStmt = $pdo->prepare($todayStatsQuery);
    $todayStatsStmt->execute([$todayStart, $todayEnd]);
    $todayStats = $todayStatsStmt->fetch(PDO::FETCH_ASSOC);
    
    // Get order status breakdown
    $statusQuery = "
        SELECT 
            order_status,
            COUNT(*) as count
        FROM binance_p2p_orders
        WHERE created_at >= ? AND created_at <= ?
        GROUP BY order_status
        ORDER BY count DESC
    ";
    $statusStmt = $pdo->prepare($statusQuery);
    $statusStmt->execute([$dateRange['start'], $dateRange['end']]);
    $statusBreakdown = $statusStmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Get payment status breakdown
    $paymentStatusQuery = "
        SELECT 
            payment_status,
            COUNT(*) as count,
            SUM(CASE WHEN fiat_currency = 'EUR' AND order_status = 'COMPLETED' THEN fiat_amount ELSE 0 END) as eur_amount
        FROM binance_p2p_orders
        WHERE created_at >= ? AND created_at <= ?
        GROUP BY payment_status
        ORDER BY count DESC
    ";
    $paymentStatusStmt = $pdo->prepare($paymentStatusQuery);
    $paymentStatusStmt->execute([$dateRange['start'], $dateRange['end']]);
    $paymentStatusBreakdown = $paymentStatusStmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Get recent orders (last 10)
    $recentOrdersQuery = "
        SELECT 
            id,
            order_no,
            fiat_amount,
            fiat_currency,
            crypto_amount,
            crypto_currency,
            order_status,
            payment_status,
            created_at
        FROM binance_p2p_orders
        WHERE created_at >= ? AND created_at <= ?
        ORDER BY created_at DESC
        LIMIT 10
    ";
    $recentOrdersStmt = $pdo->prepare($recentOrdersQuery);
    $recentOrdersStmt->execute([$dateRange['start'], $dateRange['end']]);
    $recentOrders = $recentOrdersStmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Calculate success rate
    $successRate = $stats['total_orders'] > 0 
        ? round(($stats['completed_orders'] / $stats['total_orders']) * 100, 2) 
        : 0;
    
} catch (PDOException $e) {
    error_log("Database error in statistics.php: " . $e->getMessage());
    die("Database error: " . $e->getMessage());
}

$page_title = "Statistics Dashboard";
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title><?php echo $page_title; ?></title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.10.0/font/bootstrap-icons.css">
    <style>
        body {
            background-color: #f5f5f5;
            font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif;
        }
        .navbar {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
        }
        .stat-card {
            background: white;
            border-radius: 15px;
            padding: 25px;
            margin-bottom: 20px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            transition: transform 0.2s;
        }
        .stat-card:hover {
            transform: translateY(-5px);
            box-shadow: 0 4px 20px rgba(0,0,0,0.15);
        }
        .stat-card.primary {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
        }
        .stat-card.success {
            background: linear-gradient(135deg, #43e97b 0%, #38f9d7 100%);
            color: white;
        }
        .stat-card.warning {
            background: linear-gradient(135deg, #f093fb 0%, #f5576c 100%);
            color: white;
        }
        .stat-card.info {
            background: linear-gradient(135deg, #4facfe 0%, #00f2fe 100%);
            color: white;
        }
        .stat-value {
            font-size: 2.5rem;
            font-weight: bold;
            margin: 10px 0;
        }
        .stat-label {
            font-size: 0.9rem;
            opacity: 0.9;
            text-transform: uppercase;
            letter-spacing: 1px;
        }
        .stat-icon {
            font-size: 3rem;
            opacity: 0.3;
            position: absolute;
            right: 20px;
            top: 20px;
        }
        .card {
            border: none;
            border-radius: 15px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            margin-bottom: 20px;
        }
        .period-selector {
            background: white;
            padding: 15px;
            border-radius: 15px;
            margin-bottom: 20px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
        }
        .period-btn {
            margin: 5px;
        }
        .table {
            background: white;
        }
    </style>
</head>
<body>
    <nav class="navbar navbar-dark">
        <div class="container-fluid">
            <span class="navbar-brand mb-0 h1">
                <i class="bi bi-bar-chart-fill"></i> Statistics Dashboard
            </span>
            <div>
                <a href="orders.php" class="btn btn-light btn-sm me-2">
                    <i class="bi bi-list-ul"></i> Orders
                </a>
                <a href="trade-history.php" class="btn btn-light btn-sm me-2">
                    <i class="bi bi-clock-history"></i> Trade History
                </a>
                <a href="settings.php" class="btn btn-light btn-sm">
                    <i class="bi bi-gear"></i> Settings
                </a>
            </div>
        </div>
    </nav>

    <div class="container-fluid mt-4">
        <!-- Period Selector -->
        <div class="period-selector">
            <h5 class="mb-3">Time Period</h5>
            <div class="btn-group" role="group">
                <a href="?period=last_hour" class="btn btn-sm <?php echo $period === 'last_hour' ? 'btn-primary' : 'btn-outline-primary'; ?> period-btn">
                    Last Hour
                </a>
                <a href="?period=today" class="btn btn-sm <?php echo $period === 'today' ? 'btn-primary' : 'btn-outline-primary'; ?> period-btn">
                    Today
                </a>
                <a href="?period=yesterday" class="btn btn-sm <?php echo $period === 'yesterday' ? 'btn-primary' : 'btn-outline-primary'; ?> period-btn">
                    Yesterday
                </a>
                <a href="?period=week" class="btn btn-sm <?php echo $period === 'week' ? 'btn-primary' : 'btn-outline-primary'; ?> period-btn">
                    Last 7 Days
                </a>
                <a href="?period=month" class="btn btn-sm <?php echo $period === 'month' ? 'btn-primary' : 'btn-outline-primary'; ?> period-btn">
                    Last 30 Days
                </a>
                <a href="?period=all_time" class="btn btn-sm <?php echo $period === 'all_time' ? 'btn-primary' : 'btn-outline-primary'; ?> period-btn">
                    All Time
                </a>
            </div>
        </div>

        <!-- Key Statistics Cards -->
        <div class="row">
            <div class="col-md-3">
                <div class="stat-card primary" style="position: relative;">
                    <i class="bi bi-cart-check stat-icon"></i>
                    <div class="stat-label">Today Orders</div>
                    <div class="stat-value"><?php echo number_format($todayStats['today_orders'] ?? 0); ?></div>
                    <small>Orders created today</small>
                </div>
            </div>
            <div class="col-md-3">
                <div class="stat-card success" style="position: relative;">
                    <i class="bi bi-currency-euro stat-icon"></i>
                    <div class="stat-label">Today Payment Sent (EUR)</div>
                    <div class="stat-value"><?php echo number_format($todayStats['today_eur_sent'] ?? 0, 2); ?> €</div>
                    <small>EUR sent today</small>
                </div>
            </div>
            <div class="col-md-3">
                <div class="stat-card info" style="position: relative;">
                    <i class="bi bi-list-check stat-icon"></i>
                    <div class="stat-label">Total Orders (<?php echo ucfirst(str_replace('_', ' ', $period)); ?>)</div>
                    <div class="stat-value"><?php echo number_format($stats['total_orders'] ?? 0); ?></div>
                    <small>All orders in period</small>
                </div>
            </div>
            <div class="col-md-3">
                <div class="stat-card success" style="position: relative;">
                    <i class="bi bi-cash-coin stat-icon"></i>
                    <div class="stat-label">Total EUR Sent (<?php echo ucfirst(str_replace('_', ' ', $period)); ?>)</div>
                    <div class="stat-value"><?php echo number_format($stats['total_eur_sent'] ?? 0, 2); ?> €</div>
                    <small>EUR sent in period</small>
                </div>
            </div>
        </div>

        <!-- Additional Statistics -->
        <div class="row">
            <div class="col-md-3">
                <div class="stat-card" style="position: relative;">
                    <i class="bi bi-check-circle stat-icon text-success"></i>
                    <div class="stat-label">Completed Orders</div>
                    <div class="stat-value text-success"><?php echo number_format($stats['completed_orders'] ?? 0); ?></div>
                    <small><?php echo $successRate; ?>% success rate</small>
                </div>
            </div>
            <div class="col-md-3">
                <div class="stat-card" style="position: relative;">
                    <i class="bi bi-hourglass-split stat-icon text-warning"></i>
                    <div class="stat-label">Pending Orders</div>
                    <div class="stat-value text-warning"><?php echo number_format($stats['pending_orders'] ?? 0); ?></div>
                    <small>Awaiting payment</small>
                </div>
            </div>
            <div class="col-md-3">
                <div class="stat-card" style="position: relative;">
                    <i class="bi bi-x-circle stat-icon text-danger"></i>
                    <div class="stat-label">Failed Orders</div>
                    <div class="stat-value text-danger"><?php echo number_format($stats['failed_orders'] ?? 0); ?></div>
                    <small>Payment failed</small>
                </div>
            </div>
            <div class="col-md-3">
                <div class="stat-card" style="position: relative;">
                    <i class="bi bi-people stat-icon text-info"></i>
                    <div class="stat-label">Unique Buyers</div>
                    <div class="stat-value text-info"><?php echo number_format($stats['unique_buyers'] ?? 0); ?></div>
                    <small>Different buyers</small>
                </div>
            </div>
        </div>

        <!-- Financial Statistics -->
        <div class="row">
            <div class="col-md-4">
                <div class="stat-card" style="position: relative;">
                    <i class="bi bi-currency-exchange stat-icon text-primary"></i>
                    <div class="stat-label">Completed EUR Sent</div>
                    <div class="stat-value text-primary"><?php echo number_format($stats['completed_eur_sent'] ?? 0, 2); ?> €</div>
                    <small>Successfully sent EUR</small>
                </div>
            </div>
            <div class="col-md-4">
                <div class="stat-card" style="position: relative;">
                    <i class="bi bi-wallet2 stat-icon text-success"></i>
                    <div class="stat-label">Total USDC Sent</div>
                    <div class="stat-value text-success"><?php echo number_format($stats['total_usdc_sent'] ?? 0, 6); ?> USDC</div>
                    <small>USDC transactions sent</small>
                </div>
            </div>
            <div class="col-md-4">
                <div class="stat-card" style="position: relative;">
                    <i class="bi bi-graph-up stat-icon text-info"></i>
                    <div class="stat-label">Average Order Value</div>
                    <div class="stat-value text-info"><?php echo number_format($stats['avg_order_value'] ?? 0, 2); ?> €</div>
                    <small>Avg: <?php echo number_format($stats['min_order_value'] ?? 0, 2); ?> - <?php echo number_format($stats['max_order_value'] ?? 0, 2); ?> €</small>
                </div>
            </div>
        </div>

        <!-- Status Breakdowns -->
        <div class="row">
            <div class="col-md-6">
                <div class="card">
                    <div class="card-header">
                        <h5 class="mb-0"><i class="bi bi-pie-chart"></i> Order Status Breakdown</h5>
                    </div>
                    <div class="card-body">
                        <table class="table table-sm">
                            <thead>
                                <tr>
                                    <th>Status</th>
                                    <th>Count</th>
                                    <th>Percentage</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($statusBreakdown as $status): ?>
                                    <tr>
                                        <td>
                                            <span class="badge bg-<?php echo get_status_badge_class($status['order_status']); ?>">
                                                <?php echo get_order_status_display($status['order_status']); ?>
                                            </span>
                                        </td>
                                        <td><?php echo number_format($status['count']); ?></td>
                                        <td>
                                            <?php 
                                            $percentage = $stats['total_orders'] > 0 
                                                ? round(($status['count'] / $stats['total_orders']) * 100, 1) 
                                                : 0;
                                            echo $percentage . '%';
                                            ?>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
            <div class="col-md-6">
                <div class="card">
                    <div class="card-header">
                        <h5 class="mb-0"><i class="bi bi-credit-card"></i> Payment Status Breakdown</h5>
                    </div>
                    <div class="card-body">
                        <table class="table table-sm">
                            <thead>
                                <tr>
                                    <th>Status</th>
                                    <th>Count</th>
                                    <th>EUR Amount</th>
                                    <th>Percentage</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($paymentStatusBreakdown as $paymentStatus): ?>
                                    <tr>
                                        <td>
                                            <span class="badge bg-<?php echo get_status_badge_class($paymentStatus['payment_status']); ?>">
                                                <?php echo get_payment_status_display($paymentStatus['payment_status']); ?>
                                            </span>
                                        </td>
                                        <td><?php echo number_format($paymentStatus['count']); ?></td>
                                        <td><?php echo number_format($paymentStatus['eur_amount'] ?? 0, 2); ?> €</td>
                                        <td>
                                            <?php 
                                            $percentage = $stats['total_orders'] > 0 
                                                ? round(($paymentStatus['count'] / $stats['total_orders']) * 100, 1) 
                                                : 0;
                                            echo $percentage . '%';
                                            ?>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>

        <!-- Recent Orders -->
        <div class="row">
            <div class="col-12">
                <div class="card">
                    <div class="card-header">
                        <h5 class="mb-0"><i class="bi bi-clock-history"></i> Recent Orders (<?php echo ucfirst(str_replace('_', ' ', $period)); ?>)</h5>
                    </div>
                    <div class="card-body p-0">
                        <div class="table-responsive">
                            <table class="table table-hover mb-0">
                                <thead>
                                    <tr>
                                        <th>Order No</th>
                                        <th>Amount</th>
                                        <th>Order Status</th>
                                        <th>Payment Status</th>
                                        <th>Created</th>
                                        <th>Actions</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php if (empty($recentOrders)): ?>
                                        <tr>
                                            <td colspan="6" class="text-center py-5 text-muted">
                                                <i class="bi bi-inbox" style="font-size: 3rem;"></i>
                                                <p class="mt-3">No orders found for this period</p>
                                            </td>
                                        </tr>
                                    <?php else: ?>
                                        <?php foreach ($recentOrders as $order): ?>
                                            <tr>
                                                <td><code><?php echo sanitize($order['order_no']); ?></code></td>
                                                <td>
                                                    <strong><?php echo format_currency($order['fiat_amount'], $order['fiat_currency']); ?></strong>
                                                    <br><small class="text-muted"><?php echo $order['crypto_amount']; ?> <?php echo $order['crypto_currency']; ?></small>
                                                </td>
                                                <td>
                                                    <span class="badge bg-<?php echo get_status_badge_class($order['order_status']); ?>">
                                                        <?php echo get_order_status_display($order['order_status']); ?>
                                                    </span>
                                                </td>
                                                <td>
                                                    <span class="badge bg-<?php echo get_status_badge_class($order['payment_status']); ?>">
                                                        <?php echo get_payment_status_display($order['payment_status']); ?>
                                                    </span>
                                                </td>
                                                <td><?php echo format_date($order['created_at']); ?></td>
                                                <td>
                                                    <a href="order-details.php?id=<?php echo $order['id']; ?>" 
                                                       class="btn btn-sm btn-outline-primary">
                                                        <i class="bi bi-eye"></i> View
                                                    </a>
                                                </td>
                                            </tr>
                                        <?php endforeach; ?>
                                    <?php endif; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <!-- Additional Important Statistics -->
        <div class="row">
            <div class="col-md-4">
                <div class="stat-card" style="position: relative;">
                    <i class="bi bi-gear stat-icon text-warning"></i>
                    <div class="stat-label">Manual Review Required</div>
                    <div class="stat-value text-warning"><?php echo number_format($stats['manual_review_orders'] ?? 0); ?></div>
                    <small>Orders needing attention</small>
                </div>
            </div>
            <div class="col-md-4">
                <div class="stat-card" style="position: relative;">
                    <i class="bi bi-arrow-repeat stat-icon text-info"></i>
                    <div class="stat-label">Processing Orders</div>
                    <div class="stat-value text-info"><?php echo number_format($stats['processing_orders'] ?? 0); ?></div>
                    <small>Currently processing</small>
                </div>
            </div>
            <div class="col-md-4">
                <div class="stat-card" style="position: relative;">
                    <i class="bi bi-send-check stat-icon text-success"></i>
                    <div class="stat-label">USDC Transactions Sent</div>
                    <div class="stat-value text-success"><?php echo number_format($stats['usdc_transactions_sent'] ?? 0); ?></div>
                    <small>Successful USDC sends</small>
                </div>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>

