jweb/ac1/src/api/agent-functions.php

<?php
// api/agent-functions.php

// Database connection
function getDBConnection() {
    static $conn;
    if (!$conn) {
        $host = '127.0.0.1';
        $dbname = 'jmdb';
        $username = 'root';
        $password = 'YourStrongPassword123';
        
        try {
            $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $e) {
            die("Connection failed: " . $e->getMessage());
        }
    }
    return $conn;
}

// Get pending agents for a sponsor
function getPendingAgents($sponsorId) {
    $conn = getDBConnection();
    $stmt = $conn->prepare("
        SELECT aa.*, u.username, u.email 
        FROM agent_applications aa 
        JOIN users u ON aa.user_id = u.id 
        WHERE aa.sponsor_id = ? AND aa.status = 'pending'
        ORDER BY aa.applied_at DESC
    ");
    $stmt->execute([$sponsorId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Get agent statistics for a sponsor
function getAgentStats($sponsorId) {
    $conn = getDBConnection();
    
    // Initialize default stats
    $stats = [
        'pending' => 0,
        'approved' => 0,
        'rejected' => 0,
        'documents_needed' => 0,
        'total_applications' => 0,
        'commission_this_month' => 0.00,
        'total_commission' => 0.00
    ];
    
    try {
        // Count applications by status
        $stmt = $conn->prepare("
            SELECT 
                status,
                COUNT(*) as count
            FROM agent_applications 
            WHERE sponsor_id = ?
            GROUP BY status
        ");
        $stmt->execute([$sponsorId]);
        $statusCounts = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        foreach ($statusCounts as $row) {
            $stats[$row['status']] = $row['count'];
            $stats['total_applications'] += $row['count'];
        }
        
        // Get commission data
        $stmt = $conn->prepare("
            SELECT 
                COALESCE(SUM(CASE WHEN MONTH(created_at) = MONTH(CURRENT_DATE()) THEN commission_amount ELSE 0 END), 0) as commission_this_month,
                COALESCE(SUM(commission_amount), 0) as total_commission
            FROM commissions 
            WHERE sponsor_id = ? AND status = 'paid'
        ");
        $stmt->execute([$sponsorId]);
        $commissionData = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($commissionData) {
            $stats['commission_this_month'] = $commissionData['commission_this_month'];
            $stats['total_commission'] = $commissionData['total_commission'];
        }
    } catch (Exception $e) {
        error_log("Error getting agent stats: " . $e->getMessage());
    }
    
    return $stats;
}

// Review agent application (admin function)
function reviewAgentApplication($agentId, $adminId, $status, $notes = '') {
    $conn = getDBConnection();
    
    try {
        $conn->beginTransaction();
        
        // Update agent application
        $stmt = $conn->prepare("
            UPDATE agent_applications 
            SET status = ?, reviewed_by = ?, reviewed_at = NOW(), review_notes = ?
            WHERE id = ?
        ");
        $stmt->execute([$status, $adminId, $notes, $agentId]);
        
        // If approved, update user type to agent
        if ($status === 'approved') {
            $stmt = $conn->prepare("
                UPDATE users u
                JOIN agent_applications aa ON u.id = aa.user_id
                SET u.user_type = 'agent'
                WHERE aa.id = ?
            ");
            $stmt->execute([$agentId]);
        }
        
        $conn->commit();
        return true;
    } catch (Exception $e) {
        $conn->rollBack();
        error_log("Error reviewing agent: " . $e->getMessage());
        return false;
    }
}

// Search agents
function searchAgents($sponsorId, $searchTerm) {
    $conn = getDBConnection();
    $searchTerm = "%$searchTerm%";
    
    $stmt = $conn->prepare("
        SELECT aa.*, u.username, u.email 
        FROM agent_applications aa 
        JOIN users u ON aa.user_id = u.id 
        WHERE aa.sponsor_id = ? 
        AND (aa.full_name LIKE ? OR aa.phone LIKE ? OR u.username LIKE ? OR aa.id LIKE ?)
        ORDER BY aa.applied_at DESC
    ");
    $stmt->execute([$sponsorId, $searchTerm, $searchTerm, $searchTerm, $searchTerm]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Get agent details for review
function getAgentDetails($agentId) {
    $conn = getDBConnection();
    $stmt = $conn->prepare("
        SELECT aa.*, u.username, u.email, u.created_at as user_joined
        FROM agent_applications aa 
        JOIN users u ON aa.user_id = u.id 
        WHERE aa.id = ?
    ");
    $stmt->execute([$agentId]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}
?>