<?php
/**
 * CUSTOMER ANALYSIS PAGE - ADMIN
 * File: admin/customer-analysis.php
 * Comprehensive customer spending and activity analysis
 */

require_once '../app/Core/init.php';

// Require admin authentication
Auth::requireAdmin();

$db = Database::getInstance();

// Pagination
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$per_page = 20;
$offset = ($page - 1) * $per_page;

// Search and filters
$search = $_GET['search'] ?? '';
$sort_by = $_GET['sort'] ?? 'total_spent';
$sort_order = $_GET['order'] ?? 'DESC';

// Build WHERE clause
$where_conditions = ["c.id IS NOT NULL"];
$params = [];

if (!empty($search)) {
    $where_conditions[] = "(CONCAT(c.first_name, ' ', c.last_name) LIKE ? OR u.email LIKE ? OR c.phone LIKE ?)";
    $search_term = "%$search%";
    $params[] = $search_term;
    $params[] = $search_term;
    $params[] = $search_term;
}

$where_clause = implode(' AND ', $where_conditions);

// Valid sort columns
$valid_sorts = ['customer_name', 'total_orders', 'total_spent', 'available_balance', 
                'total_withdrawn', 'running_orders', 'created_at'];
$sort_by = in_array($sort_by, $valid_sorts) ? $sort_by : 'total_spent';
$sort_order = strtoupper($sort_order) === 'ASC' ? 'ASC' : 'DESC';

// Get total count
$count_sql = "SELECT COUNT(*) as total 
              FROM customers c
              LEFT JOIN users u ON c.user_id = u.id
              WHERE $where_clause";
$count_result = $db->fetch($count_sql, $params);
$total_customers = $count_result['total'] ?? 0;
$total_pages = ceil($total_customers / $per_page);

// Get customers with analysis
$sql = "SELECT 
        c.id as customer_id,
        c.user_id,
        CONCAT(c.first_name, ' ', c.last_name) as customer_name,
        c.first_name,
        c.last_name,
        c.phone,
        c.city,
        c.state,
        c.created_at as joined_date,
        u.email,
        u.status as user_status,
        
        -- Wallet data
        w.balance as available_balance,
        w.security_onhold as security_deposit_held,
        w.total_spent,
        w.total_withdrawn,
        
        -- Order statistics
        COALESCE(COUNT(DISTINCT o.id), 0) as total_orders,
        COALESCE(SUM(CASE WHEN o.status IN ('confirmed', 'ongoing') THEN 1 ELSE 0 END), 0) as running_orders,
        COALESCE(SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END), 0) as completed_orders,
        COALESCE(SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END), 0) as cancelled_orders,
        
        -- Financial statistics
        COALESCE(SUM(CASE WHEN o.status = 'completed' THEN o.total_amount ELSE 0 END), 0) as completed_spending,
        COALESCE(SUM(CASE WHEN o.status IN ('confirmed', 'ongoing') THEN o.total_amount ELSE 0 END), 0) as pending_spending,
        COALESCE(SUM(CASE WHEN o.status IN ('confirmed', 'ongoing') THEN o.security_deposit ELSE 0 END), 0) as active_security_deposits,
        
        -- Average spending
        CASE 
            WHEN COUNT(DISTINCT o.id) > 0 THEN COALESCE(SUM(o.total_amount), 0) / COUNT(DISTINCT o.id)
            ELSE 0 
        END as average_order_value,
        
        -- Last activity
        MAX(o.created_at) as last_order_date
        
        FROM customers c
        LEFT JOIN users u ON c.user_id = u.id
        LEFT JOIN wallets w ON c.user_id = w.user_id
        LEFT JOIN orders o ON c.id = o.customer_id AND o.payment_status = 'paid'
        WHERE $where_clause
        GROUP BY c.id, c.user_id, c.first_name, c.last_name, c.phone, c.city, c.state, 
                 c.created_at, u.email, u.status,
                 w.balance, w.security_onhold, w.total_spent, w.total_withdrawn
        ORDER BY $sort_by $sort_order
        LIMIT ? OFFSET ?";

$params[] = $per_page;
$params[] = $offset;
$customers = $db->fetchAll($sql, $params);

// Platform-wide statistics
$platform_stats = $db->fetch("
    SELECT 
        COUNT(DISTINCT c.id) as total_customers,
        COUNT(DISTINCT CASE WHEN u.status = 'active' THEN c.id END) as active_customers,
        COALESCE(SUM(w.balance), 0) as total_customer_balance,
        COALESCE(SUM(w.total_spent), 0) as total_platform_spending,
        COALESCE(SUM(w.total_withdrawn), 0) as total_withdrawn,
        COALESCE(SUM(w.security_onhold), 0) as total_security_held,
        COUNT(DISTINCT o.id) as total_orders,
        COALESCE(AVG(o.total_amount), 0) as average_order_value
    FROM customers c
    LEFT JOIN users u ON c.user_id = u.id
    LEFT JOIN wallets w ON c.user_id = w.user_id
    LEFT JOIN orders o ON c.id = o.customer_id AND o.payment_status = 'paid'
");

$page_title = "Customer Analysis - " . APP_NAME;
$active_menu = "customer-analysis";

include '../templates/layouts/admin/head.php';
?>

<div class="main-content">
    <?php include '../templates/layouts/admin/header.php'; ?>

    <div class="page-header">
        <div class="header-left">
            <h1><i class="fas fa-users"></i> Customer Analysis</h1>
            <p class="text-muted">Comprehensive customer spending and activity metrics</p>
        </div>
    </div>

    <div class="content">
        
        <!-- Platform Statistics -->
        <div class="row mb-4">
            <div class="col-md-3">
                <div class="card bg-primary text-white">
                    <div class="card-body">
                        <h6 class="mb-2">Total Customers</h6>
                        <h2 class="mb-0"><?= number_format($platform_stats['total_customers'] ?? 0) ?></h2>
                        <small><?= number_format($platform_stats['active_customers'] ?? 0) ?> Active</small>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card bg-success text-white">
                    <div class="card-body">
                        <h6 class="mb-2">Total Spending</h6>
                        <h2 class="mb-0">₦<?= number_format($platform_stats['total_platform_spending'] ?? 0, 0) ?></h2>
                        <small>All customers combined</small>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card bg-warning text-white">
                    <div class="card-body">
                        <h6 class="mb-2">Security Deposits</h6>
                        <h2 class="mb-0">₦<?= number_format($platform_stats['total_security_held'] ?? 0, 0) ?></h2>
                        <small>Currently held</small>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card bg-info text-white">
                    <div class="card-body">
                        <h6 class="mb-2">Avg Order Value</h6>
                        <h2 class="mb-0">₦<?= number_format($platform_stats['average_order_value'] ?? 0, 0) ?></h2>
                        <small>Across <?= number_format($platform_stats['total_orders'] ?? 0) ?> orders</small>
                    </div>
                </div>
            </div>
        </div>

        <!-- Filters and Search -->
        <div class="card mb-4">
            <div class="card-body">
                <form method="GET" class="row g-3">
                    <div class="col-md-6">
                        <input type="text" name="search" class="form-control" 
                               placeholder="Search by name, email, phone..." 
                               value="<?= htmlspecialchars($search) ?>">
                    </div>
                    <div class="col-md-4">
                        <select name="sort" class="form-control">
                            <option value="total_spent" <?= $sort_by === 'total_spent' ? 'selected' : '' ?>>Sort by Total Spent</option>
                            <option value="total_orders" <?= $sort_by === 'total_orders' ? 'selected' : '' ?>>Sort by Orders</option>
                            <option value="available_balance" <?= $sort_by === 'available_balance' ? 'selected' : '' ?>>Sort by Balance</option>
                            <option value="running_orders" <?= $sort_by === 'running_orders' ? 'selected' : '' ?>>Sort by Active Orders</option>
                            <option value="created_at" <?= $sort_by === 'created_at' ? 'selected' : '' ?>>Sort by Join Date</option>
                        </select>
                    </div>
                    <div class="col-md-2">
                        <button type="submit" class="btn btn-primary w-100">
                            <i class="fas fa-filter"></i> Filter
                        </button>
                    </div>
                </form>
            </div>
        </div>

        <!-- Customers Table -->
        <div class="card">
            <div class="card-header bg-white">
                <h5 class="mb-0">Customer Spending Analysis</h5>
            </div>
            <div class="card-body p-0">
                <?php if (empty($customers)): ?>
                    <div class="empty-state py-5">
                        <i class="fas fa-users fa-3x text-muted mb-3"></i>
                        <p class="text-muted">No customers found</p>
                    </div>
                <?php else: ?>
                    <div class="table-responsive">
                        <table class="table table-hover mb-0">
                            <thead>
                                <tr>
                                    <th>Customer</th>
                                    <th class="text-center">Orders</th>
                                    <th class="text-end">Total Spent</th>
                                    <th class="text-end">Available Balance</th>
                                    <th class="text-end">Security Held</th>
                                    <th class="text-end">Withdrawn</th>
                                    <th class="text-end">Avg Order</th>
                                    <th class="text-center">Running</th>
                                    <th>Actions</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($customers as $c): ?>
                                    <tr>
                                        <td>
                                            <strong><?= htmlspecialchars($c['customer_name']) ?></strong><br>
                                            <small class="text-muted"><?= htmlspecialchars($c['email']) ?></small><br>
                                            <small class="text-muted"><?= htmlspecialchars($c['phone'] ?? 'N/A') ?></small>
                                        </td>
                                        <td class="text-center">
                                            <strong><?= number_format($c['total_orders'] ?? 0) ?></strong><br>
                                            <small class="text-muted">
                                                <?= number_format($c['completed_orders'] ?? 0) ?> done, 
                                                <?= number_format($c['cancelled_orders'] ?? 0) ?> cancelled
                                            </small>
                                        </td>
                                        <td class="text-end">
                                            <strong class="text-danger">₦<?= number_format($c['total_spent'] ?? 0, 2) ?></strong><br>
                                            <small class="text-muted">₦<?= number_format($c['completed_spending'] ?? 0, 2) ?> completed</small>
                                        </td>
                                        <td class="text-end">
                                            <strong class="text-primary">₦<?= number_format($c['available_balance'] ?? 0, 2) ?></strong>
                                        </td>
                                        <td class="text-end">
                                            <strong class="text-warning">₦<?= number_format($c['active_security_deposits'] ?? 0, 2) ?></strong>
                                        </td>
                                        <td class="text-end">
                                            <strong>₦<?= number_format($c['total_withdrawn'] ?? 0, 2) ?></strong>
                                        </td>
                                        <td class="text-end">
                                            <strong>₦<?= number_format($c['average_order_value'] ?? 0, 2) ?></strong>
                                        </td>
                                        <td class="text-center">
                                            <?php if ($c['running_orders'] > 0): ?>
                                                <span class="badge bg-info"><?= number_format($c['running_orders'] ?? 0) ?></span>
                                            <?php else: ?>
                                                <span class="text-muted">-</span>
                                            <?php endif; ?>
                                        </td>
                                        <td>
                                            <a href="customer-details.php?id=<?= $c['customer_id'] ?>" 
                                               class="btn btn-sm btn-outline-primary" title="View Details">
                                                <i class="fas fa-eye"></i>
                                            </a>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>

                    <!-- Pagination -->
                    <?php if ($total_pages > 1): ?>
                        <div class="card-footer">
                            <?php 
                            $pagination_params = array_filter([
                                'search' => !empty($search) ? $search : null,
                                'sort' => $sort_by !== 'total_spent' ? $sort_by : null,
                                'order' => $sort_order !== 'DESC' ? $sort_order : null
                            ]);
                            echo render_pagination($page, $total_pages, 'customer-analysis.php', $pagination_params); 
                            ?>
                        </div>
                    <?php endif; ?>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

<?php include '../templates/layouts/admin/footer.php'; ?>