<?php
/**
 * VENDOR ANALYSIS PAGE - ADMIN
 * File: admin/vendor-analysis.php
 * Comprehensive vendor performance and financial 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'] ?? '';
$status_filter = $_GET['status'] ?? 'all';
$sort_by = $_GET['sort'] ?? 'total_earning';
$sort_order = $_GET['order'] ?? 'DESC';

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

if (!empty($search)) {
    $where_conditions[] = "(v.business_name LIKE ? OR u.email LIKE ? OR v.phone LIKE ?)";
    $search_term = "%$search%";
    $params[] = $search_term;
    $params[] = $search_term;
    $params[] = $search_term;
}

if ($status_filter !== 'all') {
    $where_conditions[] = "v.approval_status = ?";
    $params[] = $status_filter;
}

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

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

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

// Get vendors with analysis - FIXED with subqueries for accurate counts
$sql = "SELECT 
        v.id as vendor_id,
        v.user_id,
        v.business_name,
        v.phone,
        v.city,
        v.state,
        v.approval_status,
        v.created_at as joined_date,
        u.email,
        u.status as user_status,
        
        -- Wallet data
        w.balance as available_balance,
        w.total_earned as total_earning,
        w.total_withdrawn,
        
        -- Item statistics (using subqueries to avoid multiplication bug)
        (SELECT COUNT(*) FROM items WHERE vendor_id = v.id) as total_items,
        (SELECT COUNT(*) FROM items WHERE vendor_id = v.id AND status = 'approved') as active_items,
        
        -- Order statistics (using subqueries)
        (SELECT COUNT(*) FROM orders WHERE vendor_id = v.id AND payment_status = 'paid') as total_orders,
        (SELECT COUNT(*) FROM orders WHERE vendor_id = v.id AND status IN ('confirmed', 'ongoing') AND payment_status = 'paid') as running_orders,
        (SELECT COUNT(*) FROM orders WHERE vendor_id = v.id AND status = 'completed' AND payment_status = 'paid') as completed_orders,
        (SELECT COUNT(*) FROM orders WHERE vendor_id = v.id AND status = 'cancelled') as cancelled_orders,
        
        -- Financial statistics (using subqueries)
        (SELECT COALESCE(SUM(o.subtotal - o.platform_fee), 0) 
         FROM orders o 
         WHERE o.vendor_id = v.id AND o.status = 'completed' AND o.payment_status = 'paid') as completed_earning,
         
        (SELECT COALESCE(SUM(o.subtotal - o.platform_fee), 0) 
         FROM orders o 
         WHERE o.vendor_id = v.id AND o.status IN ('confirmed', 'ongoing') AND o.payment_status = 'paid') as pending_earning,
         
        (SELECT COALESCE(SUM(o.platform_fee), 0) 
         FROM orders o 
         WHERE o.vendor_id = v.id AND o.status NOT IN ('cancelled', 'rejected') AND o.payment_status = 'paid') as platform_commission,
        
        -- Last activity
        (SELECT MAX(created_at) FROM orders WHERE vendor_id = v.id) as last_order_date
        
        FROM vendors v
        LEFT JOIN users u ON v.user_id = u.id
        LEFT JOIN wallets w ON v.user_id = w.user_id
        WHERE $where_clause
        ORDER BY $sort_by $sort_order
        LIMIT ? OFFSET ?";

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

// Platform-wide statistics
$platform_stats = $db->fetch("
    SELECT 
        COUNT(DISTINCT v.id) as total_vendors,
        COUNT(DISTINCT CASE WHEN v.approval_status = 'approved' THEN v.id END) as active_vendors,
        COALESCE(SUM(w.balance), 0) as total_vendor_balance,
        COALESCE(SUM(w.total_earned), 0) as total_platform_earning,
        COALESCE(SUM(w.total_withdrawn), 0) as total_withdrawn,
        COUNT(DISTINCT i.id) as total_items,
        COUNT(DISTINCT o.id) as total_orders,
        COALESCE(SUM(o.platform_fee), 0) as total_commission
    FROM vendors v
    LEFT JOIN users u ON v.user_id = u.id
    LEFT JOIN wallets w ON v.user_id = w.user_id
    LEFT JOIN items i ON v.id = i.vendor_id
    LEFT JOIN orders o ON v.id = o.vendor_id AND o.payment_status = 'paid'
");

$page_title = "Vendor Analysis - " . APP_NAME;
$active_menu = "vendor-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-store"></i> Vendor Analysis</h1>
            <p class="text-muted">Comprehensive vendor performance 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 Vendors</h6>
                        <h2 class="mb-0"><?= number_format($platform_stats['total_vendors'] ?? 0) ?></h2>
                        <small><?= number_format($platform_stats['active_vendors'] ?? 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 Earnings</h6>
                        <h2 class="mb-0">₦<?= number_format($platform_stats['total_platform_earning'] ?? 0, 0) ?></h2>
                        <small>All vendors combined</small>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card bg-info text-white">
                    <div class="card-body">
                        <h6 class="mb-2">Platform Commission</h6>
                        <h2 class="mb-0">₦<?= number_format($platform_stats['total_commission'] ?? 0, 0) ?></h2>
                        <small>From all orders</small>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card bg-warning text-white">
                    <div class="card-body">
                        <h6 class="mb-2">Available Balance</h6>
                        <h2 class="mb-0">₦<?= number_format($platform_stats['total_vendor_balance'] ?? 0, 0) ?></h2>
                        <small>All vendor wallets</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-4">
                        <input type="text" name="search" class="form-control" 
                               placeholder="Search by business name, email, phone..." 
                               value="<?= htmlspecialchars($search) ?>">
                    </div>
                    <div class="col-md-3">
                        <select name="status" class="form-control">
                            <option value="all">All Status</option>
                            <option value="approved" <?= $status_filter === 'approved' ? 'selected' : '' ?>>Approved</option>
                            <option value="pending" <?= $status_filter === 'pending' ? 'selected' : '' ?>>Pending</option>
                            <option value="rejected" <?= $status_filter === 'rejected' ? 'selected' : '' ?>>Rejected</option>
                        </select>
                    </div>
                    <div class="col-md-3">
                        <select name="sort" class="form-control">
                            <option value="total_earning" <?= $sort_by === 'total_earning' ? 'selected' : '' ?>>Sort by Earnings</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="total_items" <?= $sort_by === 'total_items' ? 'selected' : '' ?>>Sort by Items</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>

        <!-- Vendors Table -->
        <div class="card">
            <div class="card-header bg-white">
                <h5 class="mb-0">Vendor Performance Analysis</h5>
            </div>
            <div class="card-body p-0">
                <?php if (empty($vendors)): ?>
                    <div class="empty-state py-5">
                        <i class="fas fa-store fa-3x text-muted mb-3"></i>
                        <p class="text-muted">No vendors found</p>
                    </div>
                <?php else: ?>
                    <div class="table-responsive">
                        <table class="table table-hover mb-0">
                            <thead>
                                <tr>
                                    <th>Vendor</th>
                                    <th class="text-center">Items</th>
                                    <th class="text-center">Orders</th>
                                    <th class="text-end">Total Earning</th>
                                    <th class="text-end">Pending Earning</th>
                                    <th class="text-end">Available Balance</th>
                                    <th class="text-end">Withdrawn</th>
                                    <th class="text-center">Running</th>
                                    <th>Status</th>
                                    <th>Actions</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($vendors as $v): ?>
                                    <tr>
                                        <td>
                                            <strong><?= htmlspecialchars($v['business_name']) ?></strong><br>
                                            <small class="text-muted"><?= htmlspecialchars($v['email']) ?></small><br>
                                            <small class="text-muted"><?= htmlspecialchars($v['city'] ?? '') ?>, <?= htmlspecialchars($v['state'] ?? '') ?></small>
                                        </td>
                                        <td class="text-center">
                                            <strong><?= number_format($v['total_items'] ?? 0) ?></strong><br>
                                            <small class="text-success"><?= number_format($v['active_items'] ?? 0) ?> active</small>
                                        </td>
                                        <td class="text-center">
                                            <strong><?= number_format($v['total_orders'] ?? 0) ?></strong><br>
                                            <small class="text-muted">
                                                <?= number_format($v['completed_orders'] ?? 0) ?> done, 
                                                <?= number_format($v['cancelled_orders'] ?? 0) ?> cancelled
                                            </small>
                                        </td>
                                        </td>
                                        <td class="text-end">
                                            <strong class="text-success">₦<?= number_format($v['total_earning'] ?? 0, 2) ?></strong><br>
                                            <small class="text-muted">₦<?= number_format($v['completed_earning'] ?? 0, 2) ?> completed</small>
                                        </td>
                                        <td class="text-end">
                                            <strong class="text-warning">₦<?= number_format($v['pending_earning'] ?? 0, 2) ?></strong><br>
                                            <small class="text-muted"><?= number_format($v['running_orders'] ?? 0) ?> ongoing</small>
                                        </td>
                                        <td class="text-end">
                                            <strong class="text-primary">₦<?= number_format($v['available_balance'] ?? 0, 2) ?></strong>
                                        </td>
                                        <td class="text-end">
                                            <strong>₦<?= number_format($v['total_withdrawn'] ?? 0, 2) ?></strong>
                                        <td class="text-center">
                                            <?php if ($v['running_orders'] > 0): ?>
                                                <span class="badge bg-info"><?= number_format($v['running_orders'] ?? 0) ?></span>
                                            <?php else: ?>
                                                <span class="text-muted">-</span>
                                            <?php endif; ?>
                                        </td>
                                        <td>
                                            <?php
                                            $badge_class = 'secondary';
                                            switch($v['approval_status']) {
                                                case 'approved': $badge_class = 'success'; break;
                                                case 'pending': $badge_class = 'warning'; break;
                                                case 'rejected': $badge_class = 'danger'; break;
                                            }
                                            ?>
                                            <span class="badge bg-<?= $badge_class ?>"><?= ucfirst($v['approval_status']) ?></span>
                                        </td>
                                        <td>
                                            <a href="vendor-details.php?id=<?= $v['vendor_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,
                                'status' => $status_filter !== 'all' ? $status_filter : null,
                                'sort' => $sort_by !== 'total_earning' ? $sort_by : null,
                                'order' => $sort_order !== 'DESC' ? $sort_order : null
                            ]);
                            echo render_pagination($page, $total_pages, 'vendor-analysis.php', $pagination_params); 
                            ?>
                        </div>
                    <?php endif; ?>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

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