<?php
// Orders Functions for My Orders Page
// This file contains all the functions needed for the my-orders.php page

require_once './layouts/dbconn.php';

/**
 * Get user orders with filtering and pagination
 */
function getUserOrders($user_id, $status_filter = '', $date_filter = '', $page = 1, $per_page = 10) {
    global $conn;
    
    $offset = ($page - 1) * $per_page;
    $where_conditions = ["o.user_id = ?"];
    $params = [$user_id];
    $param_types = "i";
    
    // Add status filter
    if (!empty($status_filter)) {
        $where_conditions[] = "o.order_status = ?";
        $params[] = strtoupper($status_filter);
        $param_types .= "s";
    }
    
    // Add date filter
    if (!empty($date_filter)) {
        switch ($date_filter) {
            case 'today':
                $where_conditions[] = "DATE(o.created_at) = CURDATE()";
                break;
            case 'week':
                $where_conditions[] = "o.created_at >= DATE_SUB(NOW(), INTERVAL 1 WEEK)";
                break;
            case 'month':
                $where_conditions[] = "o.created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)";
                break;
            case 'quarter':
                $where_conditions[] = "o.created_at >= DATE_SUB(NOW(), INTERVAL 3 MONTH)";
                break;
            case 'year':
                $where_conditions[] = "o.created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)";
                break;
        }
    }
    
    $where_clause = "WHERE " . implode(" AND ", $where_conditions);
    
    $query = "
        SELECT 
            o.order_id,
            o.order_number,
            o.total_amount,
            o.total_amount as subtotal_amount,
            0 as discount_amount,
            0 as tax_amount,
            o.order_status,
            o.payment_status,
            '' as discount_code,
            o.created_at,
            o.updated_at,
            o.completed_at
        FROM orders o 
        {$where_clause}
        ORDER BY o.created_at DESC 
        LIMIT ? OFFSET ?
    ";
    
    $params[] = $per_page;
    $params[] = $offset;
    $param_types .= "ii";
    
    try {
        $stmt = $conn->prepare($query);
        $stmt->bind_param($param_types, ...$params);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $orders = [];
        while ($row = $result->fetch_assoc()) {
            // Calculate discount and tax from order items if needed
            $order_details = calculateOrderTotals($row['order_id']);
            $row['subtotal'] = $order_details['subtotal'];
            $row['discount_amount'] = $order_details['discount_amount'];
            $row['tax_amount'] = $order_details['tax_amount'];
            
            // Get order items
            $row['items'] = getOrderItems($row['order_id']);
            
            // Get licenses if order is completed
            if ($row['order_status'] === 'COMPLETED') {
                $row['licenses'] = getOrderLicenses($row['order_id']);
                $row['download_links'] = getOrderDownloadLinks($row['order_id']);
            } else {
                $row['licenses'] = [];
                $row['download_links'] = [];
            }
            
            // Check if user can request support
            $row['can_request_support'] = canRequestSupport($row['order_id'], $user_id);
            
            $orders[] = $row;
        }
        
        $stmt->close();
        return $orders;
        
    } catch (Exception $e) {
        error_log("Error fetching user orders: " . $e->getMessage());
        return [];
    }
}

/**
 * Get total count of user orders with filters
 */
function getUserOrdersCount($user_id, $status_filter = '', $date_filter = '') {
    global $conn;
    
    $where_conditions = ["user_id = ?"];
    $params = [$user_id];
    $param_types = "i";
    
    // Add status filter
    if (!empty($status_filter)) {
        $where_conditions[] = "order_status = ?";
        $params[] = strtoupper($status_filter);
        $param_types .= "s";
    }
    
    // Add date filter
    if (!empty($date_filter)) {
        switch ($date_filter) {
            case 'today':
                $where_conditions[] = "DATE(created_at) = CURDATE()";
                break;
            case 'week':
                $where_conditions[] = "created_at >= DATE_SUB(NOW(), INTERVAL 1 WEEK)";
                break;
            case 'month':
                $where_conditions[] = "created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)";
                break;
            case 'quarter':
                $where_conditions[] = "created_at >= DATE_SUB(NOW(), INTERVAL 3 MONTH)";
                break;
            case 'year':
                $where_conditions[] = "created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)";
                break;
        }
    }
    
    $where_clause = "WHERE " . implode(" AND ", $where_conditions);
    
    $query = "SELECT COUNT(*) as count FROM orders {$where_clause}";
    
    try {
        $stmt = $conn->prepare($query);
        $stmt->bind_param($param_types, ...$params);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        
        return $result['count'] ?? 0;
        
    } catch (Exception $e) {
        error_log("Error counting user orders: " . $e->getMessage());
        return 0;
    }
}

/**
 * Get user order statistics
 */
function getUserOrderStats($user_id) {
    global $conn;
    
    $stats = [
        'total_orders' => 0,
        'total_spent' => 0,
        'active_licenses' => 0,
        'pending_orders' => 0,
        'orders_trend' => 0,
        'spending_trend' => 0,
        'expiring_soon' => 0,
        'lifetime_orders' => 0,
        'lifetime_value' => 0,
        'favorite_product' => 'None',
        'first_order_date' => date('Y-m-d')
    ];
    
    try {
        // Get basic order stats
        $query = "
            SELECT 
                COUNT(*) as total_orders,
                COALESCE(SUM(total_amount), 0) as total_spent,
                COUNT(CASE WHEN order_status = 'PENDING' THEN 1 END) as pending_orders,
                MIN(created_at) as first_order_date
            FROM orders 
            WHERE user_id = ?
        ";
        
        $stmt = $conn->prepare($query);
        $stmt->bind_param("i", $user_id);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        
        $stats['total_orders'] = $result['total_orders'] ?? 0;
        $stats['total_spent'] = $result['total_spent'] ?? 0;
        $stats['pending_orders'] = $result['pending_orders'] ?? 0;
        $stats['lifetime_orders'] = $result['total_orders'] ?? 0;
        $stats['lifetime_value'] = $result['total_spent'] ?? 0;
        $stats['first_order_date'] = $result['first_order_date'] ?? date('Y-m-d');
        
        $stmt->close();
        
        // Get active licenses count
        $query = "SELECT COUNT(*) as count FROM licenses WHERE user_id = ? AND status = 'ACTIVE'";
        $stmt = $conn->prepare($query);
        $stmt->bind_param("i", $user_id);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        $stats['active_licenses'] = $result['count'] ?? 0;
        $stmt->close();
        
        // Get expiring licenses count (expiring within 30 days)
        $query = "
            SELECT COUNT(*) as count 
            FROM licenses 
            WHERE user_id = ? 
            AND status = 'ACTIVE' 
            AND expires_at IS NOT NULL 
            AND expires_at <= DATE_ADD(NOW(), INTERVAL 30 DAY)
        ";
        $stmt = $conn->prepare($query);
        $stmt->bind_param("i", $user_id);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        $stats['expiring_soon'] = $result['count'] ?? 0;
        $stmt->close();
        
        // Get most purchased product
        $query = "
            SELECT p.name 
            FROM order_items oi
            JOIN orders o ON oi.order_id = o.order_id
            JOIN products p ON oi.product_id = p.product_id
            WHERE o.user_id = ? AND o.order_status = 'COMPLETED'
            GROUP BY oi.product_id, p.name
            ORDER BY COUNT(*) DESC
            LIMIT 1
        ";
        $stmt = $conn->prepare($query);
        $stmt->bind_param("i", $user_id);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        $stats['favorite_product'] = $result['name'] ?? 'None';
        $stmt->close();
        
        // Calculate trends (compare this month to last month)
        $query = "
            SELECT 
                COUNT(CASE WHEN created_at >= DATE_SUB(DATE_SUB(NOW(), INTERVAL DAY(NOW())-1 DAY), INTERVAL 0 MONTH) THEN 1 END) as this_month_orders,
                COUNT(CASE WHEN created_at >= DATE_SUB(DATE_SUB(NOW(), INTERVAL DAY(NOW())-1 DAY), INTERVAL 1 MONTH) 
                           AND created_at < DATE_SUB(DATE_SUB(NOW(), INTERVAL DAY(NOW())-1 DAY), INTERVAL 0 MONTH) THEN 1 END) as last_month_orders,
                COALESCE(SUM(CASE WHEN created_at >= DATE_SUB(DATE_SUB(NOW(), INTERVAL DAY(NOW())-1 DAY), INTERVAL 0 MONTH) THEN total_amount END), 0) as this_month_spent,
                COALESCE(SUM(CASE WHEN created_at >= DATE_SUB(DATE_SUB(NOW(), INTERVAL DAY(NOW())-1 DAY), INTERVAL 1 MONTH) 
                                  AND created_at < DATE_SUB(DATE_SUB(NOW(), INTERVAL DAY(NOW())-1 DAY), INTERVAL 0 MONTH) THEN total_amount END), 0) as last_month_spent
            FROM orders 
            WHERE user_id = ? AND order_status = 'COMPLETED'
        ";
        
        $stmt = $conn->prepare($query);
        $stmt->bind_param("i", $user_id);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        
        $this_month_orders = $result['this_month_orders'] ?? 0;
        $last_month_orders = $result['last_month_orders'] ?? 0;
        $this_month_spent = $result['this_month_spent'] ?? 0;
        $last_month_spent = $result['last_month_spent'] ?? 0;
        
        // Calculate percentage trends
        if ($last_month_orders > 0) {
            $stats['orders_trend'] = round((($this_month_orders - $last_month_orders) / $last_month_orders) * 100, 1);
        }
        
        if ($last_month_spent > 0) {
            $stats['spending_trend'] = round((($this_month_spent - $last_month_spent) / $last_month_spent) * 100, 1);
        }
        
        $stmt->close();
        
    } catch (Exception $e) {
        error_log("Error fetching user order stats: " . $e->getMessage());
    }
    
    return $stats;
}

/**
 * Get order items for a specific order
 */
function getOrderItems($order_id) {
    global $conn;
    
    $query = "
        SELECT 
            oi.item_id,
            oi.quantity,
            oi.unit_price,
            oi.total_price,
            p.name,
            p.description,
            p.logo_url as image,
            pc.name as type,
            pp.tier_name as license_type
        FROM order_items oi
        JOIN products p ON oi.product_id = p.product_id
        JOIN product_categories pc ON p.category_id = pc.category_id
        JOIN product_pricing pp ON oi.pricing_tier_id = pp.pricing_id
        WHERE oi.order_id = ?
    ";
    
    try {
        $stmt = $conn->prepare($query);
        $stmt->bind_param("i", $order_id);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $items = [];
        while ($row = $result->fetch_assoc()) {
            $row['price'] = $row['total_price']; // For compatibility
            $items[] = $row;
        }
        
        $stmt->close();
        return $items;
        
    } catch (Exception $e) {
        error_log("Error fetching order items: " . $e->getMessage());
        return [];
    }
}

/**
 * Get licenses for a completed order
 */
function getOrderLicenses($order_id) {
    global $conn;
    
    $query = "
        SELECT 
            l.license_key,
            l.sites_allowed,
            l.sites_used,
            l.expires_at,
            p.name as product_name
        FROM licenses l
        JOIN order_items oi ON l.license_id = oi.license_id
        JOIN products p ON oi.product_id = p.product_id
        WHERE oi.order_id = ?
    ";
    
    try {
        $stmt = $conn->prepare($query);
        $stmt->bind_param("i", $order_id);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $licenses = [];
        while ($row = $result->fetch_assoc()) {
            $licenses[] = $row;
        }
        
        $stmt->close();
        return $licenses;
        
    } catch (Exception $e) {
        error_log("Error fetching order licenses: " . $e->getMessage());
        return [];
    }
}

/**
 * Get download links for a completed order
 */
function getOrderDownloadLinks($order_id) {
    global $conn;
    
    $query = "
        SELECT 
            dl.download_url as url,
            CONCAT(p.name, ' v', pf.version) as name,
            pf.file_type
        FROM download_links dl
        JOIN order_items oi ON dl.order_id = oi.order_id
        JOIN products p ON oi.product_id = p.product_id
        JOIN product_files pf ON dl.file_id = pf.file_id
        WHERE dl.order_id = ? AND dl.expires_at > NOW()
    ";
    
    try {
        $stmt = $conn->prepare($query);
        $stmt->bind_param("i", $order_id);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $downloads = [];
        while ($row = $result->fetch_assoc()) {
            $downloads[] = $row;
        }
        
        $stmt->close();
        return $downloads;
        
    } catch (Exception $e) {
        error_log("Error fetching download links: " . $e->getMessage());
        return [];
    }
}

/**
 * Check if user can request support for an order
 */
function canRequestSupport($order_id, $user_id) {
    global $conn;
    
    // Check if order exists and belongs to user
    $query = "
        SELECT order_status, created_at 
        FROM orders 
        WHERE order_id = ? AND user_id = ?
    ";
    
    try {
        $stmt = $conn->prepare($query);
        $stmt->bind_param("ii", $order_id, $user_id);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        
        if (!$result) {
            return false;
        }
        
        // Can request support if:
        // 1. Order is completed or has issues
        // 2. Order is not older than 1 year
        $order_age_days = (time() - strtotime($result['created_at'])) / (24 * 60 * 60);
        
        return in_array($result['order_status'], ['COMPLETED', 'PROCESSING', 'FAILED']) && $order_age_days <= 365;
        
    } catch (Exception $e) {
        error_log("Error checking support eligibility: " . $e->getMessage());
        return false;
    }
}

/**
 * Helper function to format time ago
 */
function timeAgo($datetime) {
    $time = time() - strtotime($datetime);
    
    if ($time < 60) return 'just now';
    if ($time < 3600) return floor($time/60) . ' min ago';
    if ($time < 86400) return floor($time/3600) . ' hr ago';
    if ($time < 2592000) return floor($time/86400) . ' days ago';
    if ($time < 31536000) return floor($time/2592000) . ' months ago';
    
    return floor($time/31536000) . ' years ago';
}

/**
 * Get plugin categories for filtering
 */
function getPluginCategories() {
    global $conn;
    
    $query = "
        SELECT category_id, name, slug, description
        FROM product_categories 
        WHERE is_active = 1 
        ORDER BY sort_order, name
    ";
    
    try {
        $stmt = $conn->prepare($query);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $categories = [];
        while ($row = $result->fetch_assoc()) {
            $categories[] = $row;
        }
        
        $stmt->close();
        return $categories;
        
    } catch (Exception $e) {
        error_log("Error fetching categories: " . $e->getMessage());
        return [];
    }
}

/**
 * Get featured plugins
 */
function getFeaturedPlugins() {
    global $conn;
    
    $query = "
        SELECT 
            p.product_id,
            p.name,
            p.description,
            p.logo_url,
            p.developer,
            p.rating_average,
            p.rating_count,
            p.download_count,
            pp.price,
            p.created_at,
            p.updated_at
        FROM products p
        JOIN product_pricing pp ON p.product_id = pp.product_id
        WHERE p.is_featured = 1 AND p.is_active = 1
        AND pp.tier_name = 'Basic'
        ORDER BY p.download_count DESC
        LIMIT 6
    ";
    
    try {
        $stmt = $conn->prepare($query);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $plugins = [];
        while ($row = $result->fetch_assoc()) {
            $plugins[] = $row;
        }
        
        $stmt->close();
        return $plugins;
        
    } catch (Exception $e) {
        error_log("Error fetching featured plugins: " . $e->getMessage());
        return [];
    }
}

/**
 * Get recent plugins
 */
function getRecentPlugins($limit = 6) {
    global $conn;
    
    $query = "
        SELECT 
            p.product_id,
            p.name,
            p.description,
            p.logo_url,
            p.developer,
            p.rating_average,
            p.rating_count,
            p.download_count,
            pp.price,
            p.created_at,
            p.updated_at
        FROM products p
        JOIN product_pricing pp ON p.product_id = pp.product_id
        WHERE p.is_active = 1
        AND pp.tier_name = 'Basic'
        ORDER BY p.created_at DESC
        LIMIT ?
    ";
    
    try {
        $stmt = $conn->prepare($query);
        $stmt->bind_param("i", $limit);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $plugins = [];
        while ($row = $result->fetch_assoc()) {
            $plugins[] = $row;
        }
        
        $stmt->close();
        return $plugins;
        
    } catch (Exception $e) {
        error_log("Error fetching recent plugins: " . $e->getMessage());
        return [];
    }
}

/**
 * Calculate order totals from order items
 */
function calculateOrderTotals($order_id) {
    global $conn;
    
    $query = "
        SELECT 
            SUM(oi.total_price) as subtotal,
            0 as discount_amount,
            0 as tax_amount
        FROM order_items oi 
        WHERE oi.order_id = ?
    ";
    
    try {
        $stmt = $conn->prepare($query);
        $stmt->bind_param("i", $order_id);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        
        return [
            'subtotal' => $result['subtotal'] ?? 0,
            'discount_amount' => $result['discount_amount'] ?? 0,
            'tax_amount' => $result['tax_amount'] ?? 0
        ];
        
    } catch (Exception $e) {
        error_log("Error calculating order totals: " . $e->getMessage());
        return [
            'subtotal' => 0,
            'discount_amount' => 0,
            'tax_amount' => 0
        ];
    }
}

/**
 * Get plugins with search and filtering - Updated with sample data fallback
 */
function getPlugins($search = '', $category_filter = '', $sort_by = 'popularity') {
    global $conn;
    
    $where_conditions = ["p.is_active = 1"];
    $params = [];
    $param_types = "";
    
    // Add search filter
    if (!empty($search)) {
        $where_conditions[] = "(p.name LIKE ? OR p.description LIKE ? OR p.developer LIKE ?)";
        $search_term = "%{$search}%";
        $params = array_merge($params, [$search_term, $search_term, $search_term]);
        $param_types .= "sss";
    }
    
    // Add category filter
    if (!empty($category_filter)) {
        $where_conditions[] = "pc.slug = ?";
        $params[] = $category_filter;
        $param_types .= "s";
    }
    
    $where_clause = "WHERE " . implode(" AND ", $where_conditions);
    
    // Determine sort order
    $order_clause = "ORDER BY ";
    switch ($sort_by) {
        case 'newest':
            $order_clause .= "p.created_at DESC";
            break;
        case 'rating':
            $order_clause .= "p.rating_average DESC, p.rating_count DESC";
            break;
        case 'price_low':
            $order_clause .= "pp.price ASC";
            break;
        case 'price_high':
            $order_clause .= "pp.price DESC";
            break;
        default: // popularity
            $order_clause .= "p.download_count DESC, p.rating_average DESC";
            break;
    }
    
    $query = "
        SELECT 
            p.product_id,
            p.name,
            p.description,
            p.logo_url,
            p.developer,
            p.rating_average,
            p.rating_count,
            p.download_count,
            p.is_featured,
            pp.price,
            p.created_at,
            p.updated_at
        FROM products p
        LEFT JOIN product_pricing pp ON p.product_id = pp.product_id AND pp.tier_name = 'Basic'
        LEFT JOIN product_categories pc ON p.category_id = pc.category_id
        {$where_clause}
        {$order_clause}
        LIMIT 50
    ";
    
    try {
        $stmt = $conn->prepare($query);
        if (!empty($params)) {
            $stmt->bind_param($param_types, ...$params);
        }
        $stmt->execute();
        $result = $stmt->get_result();
        
        $plugins = [];
        while ($row = $result->fetch_assoc()) {
            // Set default price if null
            $row['price'] = $row['price'] ?? 99;
            $plugins[] = $row;
        }
        
        $stmt->close();
        
        // If no plugins found, return sample data
        if (empty($plugins) && empty($search) && empty($category_filter)) {
            return getSamplePlugins();
        }
        
        return $plugins;
        
    } catch (Exception $e) {
        error_log("Error fetching plugins: " . $e->getMessage());
        return getSamplePlugins();
    }
}

/**
 * Get sample plugins data when database is empty
 */
function getSamplePlugins() {
    return [
        [
            'product_id' => 1,
            'name' => 'South Sudan MoMo Gateway',
            'description' => 'Complete mobile money payment gateway supporting MTN Mobile Money, m-GURUSH, and Nilepay for seamless WordPress e-commerce integration.',
            'logo_url' => 'assets/images/plugins/momo-gateway.png',
            'developer' => 'ConnectPay Solutions',
            'rating_average' => 4.8,
            'rating_count' => 156,
            'download_count' => 2340,
            'is_featured' => 1,
            'price' => 149,
            'created_at' => date('Y-m-d H:i:s', strtotime('-30 days')),
            'updated_at' => date('Y-m-d H:i:s', strtotime('-5 days'))
        ],
        [
            'product_id' => 2,
            'name' => 'MTN Mobile Money Pro',
            'description' => 'Advanced MTN Mobile Money integration with real-time transaction status, automated reconciliation, and comprehensive reporting features.',
            'logo_url' => 'assets/images/plugins/mtn-pro.png',
            'developer' => 'ConnectPay Solutions',
            'rating_average' => 4.9,
            'rating_count' => 89,
            'download_count' => 1580,
            'is_featured' => 1,
            'price' => 199,
            'created_at' => date('Y-m-d H:i:s', strtotime('-45 days')),
            'updated_at' => date('Y-m-d H:i:s', strtotime('-10 days'))
        ],
        [
            'product_id' => 3,
            'name' => 'Nilepay WooCommerce',
            'description' => 'Professional Nilepay integration for WooCommerce with multi-currency support, subscription billing, and advanced fraud protection.',
            'logo_url' => 'assets/images/plugins/nilepay.png',
            'developer' => 'ConnectPay Solutions',
            'rating_average' => 4.7,
            'rating_count' => 124,
            'download_count' => 980,
            'is_featured' => 0,
            'price' => 179,
            'created_at' => date('Y-m-d H:i:s', strtotime('-20 days')),
            'updated_at' => date('Y-m-d H:i:s', strtotime('-3 days'))
        ],
        [
            'product_id' => 4,
            'name' => 'm-GURUSH Gateway',
            'description' => 'Complete m-GURUSH payment solution with instant notifications, automatic order completion, and detailed transaction logging.',
            'logo_url' => 'assets/images/plugins/mgurush.png',
            'developer' => 'ConnectPay Solutions',
            'rating_average' => 4.6,
            'rating_count' => 67,
            'download_count' => 756,
            'is_featured' => 0,
            'price' => 129,
            'created_at' => date('Y-m-d H:i:s', strtotime('-60 days')),
            'updated_at' => date('Y-m-d H:i:s', strtotime('-7 days'))
        ],
        [
            'product_id' => 5,
            'name' => 'Universal MoMo Adapter',
            'description' => 'Universal mobile money adapter supporting multiple providers with intelligent failover, load balancing, and unified reporting.',
            'logo_url' => 'assets/images/plugins/universal-momo.png',
            'developer' => 'ConnectPay Solutions',
            'rating_average' => 4.5,
            'rating_count' => 203,
            'download_count' => 3420,
            'is_featured' => 1,
            'price' => 299,
            'created_at' => date('Y-m-d H:i:s', strtotime('-90 days')),
            'updated_at' => date('Y-m-d H:i:s', strtotime('-1 day'))
        ],
        [
            'product_id' => 6,
            'name' => 'Donation Manager Pro',
            'description' => 'Comprehensive donation management system with recurring donations, donor management, and automated receipt generation.',
            'logo_url' => 'assets/images/plugins/donation-pro.png',
            'developer' => 'ConnectPay Solutions',
            'rating_average' => 4.4,
            'rating_count' => 45,
            'download_count' => 534,
            'is_featured' => 0,
            'price' => 249,
            'created_at' => date('Y-m-d H:i:s', strtotime('-15 days')),
            'updated_at' => date('Y-m-d H:i:s', strtotime('-2 days'))
        ]
    ];
}


/**
 * Check if plugin is new (created within last 30 days)
 */
function isNewPlugin($created_at) {
    $created_timestamp = strtotime($created_at);
    $thirty_days_ago = time() - (30 * 24 * 60 * 60);
    return $created_timestamp > $thirty_days_ago;
}

/**
 * Get category name by slug
 */
function getCategoryName($slug) {
    global $conn;
    
    $query = "SELECT name FROM product_categories WHERE slug = ?";
    
    try {
        $stmt = $conn->prepare($query);
        $stmt->bind_param("s", $slug);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        
        return $result['name'] ?? 'Unknown Category';
        
    } catch (Exception $e) {
        error_log("Error fetching category name: " . $e->getMessage());
        return 'Unknown Category';
    }
}

/**
 * Get user by ID
 */
function getUserById($user_id) {
    global $conn;
    
    $query = "
        SELECT user_id, full_name, email, company_name, phone, avatar
        FROM users 
        WHERE user_id = ?
    ";
    
    try {
        $stmt = $conn->prepare($query);
        $stmt->bind_param("i", $user_id);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        
        return $result;
        
    } catch (Exception $e) {
        error_log("Error fetching user: " . $e->getMessage());
        return null;
    }
}

// Additional functions for payment gateways and custom solutions pages

/**
 * Get payment gateway plugins
 */
function getPaymentGatewayPlugins() {
    global $conn;
    
    $query = "
        SELECT 
            p.product_id,
            p.name,
            p.description,
            p.logo_url as provider_logo,
            p.developer as provider_name,
            p.rating_average,
            p.rating_count,
            p.download_count,
            p.features,
            p.supported_currencies,
            pp.price,
            p.created_at,
            p.updated_at,
            CASE WHEN p.created_at > DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 ELSE 0 END as is_new,
            p.is_featured as is_recommended,
            CASE WHEN pp.annual_support_fee > 0 THEN 1 ELSE 0 END as has_annual_support,
            pp.annual_support_fee as annual_support_price
        FROM products p
        JOIN product_pricing pp ON p.product_id = pp.product_id
        JOIN product_categories pc ON p.category_id = pc.category_id
        WHERE pc.slug = 'payment-gateways' AND p.is_active = 1
        AND pp.tier_name = 'Basic'
        ORDER BY p.download_count DESC, p.rating_average DESC
    ";
    
    try {
        $stmt = $conn->prepare($query);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $gateways = [];
        while ($row = $result->fetch_assoc()) {
            $gateways[] = $row;
        }
        
        $stmt->close();
        return $gateways;
        
    } catch (Exception $e) {
        error_log("Error fetching payment gateways: " . $e->getMessage());
        return [];
    }
}

/**
 * Get gateway categories with counts
 */
function getGatewayCategories() {
    global $conn;
    
    // Mock data for gateway categories
    return [
        ['name' => 'Mobile Money', 'slug' => 'mobile-money', 'count' => 8],
        ['name' => 'Credit Cards', 'slug' => 'credit-cards', 'count' => 5],
        ['name' => 'Bank Transfer', 'slug' => 'bank-transfer', 'count' => 3],
        ['name' => 'Digital Wallets', 'slug' => 'digital-wallets', 'count' => 4],
    ];
}

/**
 * Get popular payment gateways
 */
function getPopularGateways() {
    global $conn;
    
    $query = "
        SELECT 
            p.product_id,
            p.name,
            p.description,
            p.logo_url as provider_logo,
            p.developer as provider_name,
            p.rating_average,
            p.rating_count,
            p.download_count,
            p.features,
            p.supported_currencies,
            pp.price
        FROM products p
        JOIN product_pricing pp ON p.product_id = pp.product_id
        JOIN product_categories pc ON p.category_id = pc.category_id
        WHERE pc.slug = 'payment-gateways' 
        AND p.is_active = 1 
        AND p.download_count > 100
        AND pp.tier_name = 'Basic'
        ORDER BY p.download_count DESC
        LIMIT 3
    ";
    
    try {
        $stmt = $conn->prepare($query);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $gateways = [];
        while ($row = $result->fetch_assoc()) {
            $gateways[] = $row;
        }
        
        $stmt->close();
        return $gateways;
        
    } catch (Exception $e) {
        error_log("Error fetching popular gateways: " . $e->getMessage());
        return [];
    }
}

/**
 * Get custom solution packages
 */
function getCustomSolutionPackages() {
    // Mock data for custom solution packages
    return [
        [
            'id' => 'starter',
            'name' => 'Starter Package',
            'subtitle' => 'Perfect for small businesses',
            'description' => 'Basic custom plugin development with essential payment gateway integration. Ideal for WordPress sites with standard e-commerce needs.',
            'icon' => '🚀',
            'badge_type' => 'popular',
            'badge_text' => 'Popular',
            'starting_price' => 2500,
            'price_note' => 'Complete solution',
            'timeline' => '2-4 weeks',
            'features' => [
                'Single payment gateway integration',
                'Basic WordPress plugin development',
                'Mobile-responsive design',
                'Email notifications',
                'Basic admin panel',
                '30 days support included'
            ]
        ],
        [
            'id' => 'professional',
            'name' => 'Professional Package',
            'subtitle' => 'Advanced business solutions',
            'description' => 'Comprehensive custom development with multiple payment providers, advanced features, and extended support for growing businesses.',
            'icon' => '💼',
            'badge_type' => 'recommended',
            'badge_text' => 'Recommended',
            'starting_price' => 7500,
            'price_note' => 'Full-featured solution',
            'timeline' => '4-8 weeks',
            'features' => [
                'Multiple payment gateway integration',
                'Advanced plugin customization',
                'User management system',
                'Detailed reporting & analytics',
                'API integration capabilities',
                'Custom admin dashboard',
                '90 days support included'
            ]
        ],
        [
            'id' => 'enterprise',
            'name' => 'Enterprise Package',
            'subtitle' => 'Large-scale implementations',
            'description' => 'Full-scale enterprise solution with custom architecture, scalability features, and comprehensive ongoing support for large organizations.',
            'icon' => '🏢',
            'badge_type' => 'enterprise',
            'badge_text' => 'Enterprise',
            'starting_price' => 15000,
            'price_note' => 'Scalable enterprise solution',
            'timeline' => '8-16 weeks',
            'features' => [
                'Multi-provider payment orchestration',
                'Custom system architecture',
                'Advanced security features',
                'Scalability optimization',
                'Third-party integrations',
                'Custom reporting suite',
                'White-label solutions',
                '6 months support included'
            ]
        ]
    ];
}

/**
 * Get past projects for showcase
 */
function getPastProjects() {
    // Mock data for past projects
    return [
        [
            'title' => 'NGO Donation Platform',
            'description' => 'Custom WordPress plugin for a major NGO to accept donations via multiple mobile money providers across East Africa. Features real-time donation tracking and automated receipt generation.',
            'technologies' => ['WordPress', 'PHP', 'MTN MoMo', 'Nilepay', 'MySQL', 'REST API']
        ],
        [
            'title' => 'E-commerce Marketplace',
            'description' => 'Multi-vendor marketplace with integrated payment splitting, escrow services, and dispute resolution. Supports multiple currencies and payment methods.',
            'technologies' => ['WooCommerce', 'Multi-vendor', 'Payment Splitting', 'Mobile Money', 'Stripe']
        ],
        [
            'title' => 'University Fee Management',
            'description' => 'Comprehensive student fee management system with payment plans, partial payments, and integration with student information systems.',
            'technologies' => ['Custom PHP', 'Payment Plans', 'Student Portal', 'SMS Notifications', 'Reporting']
        ],
        [
            'title' => 'Government Tax Portal',
            'description' => 'Secure tax payment portal for local government with citizen authentication, payment verification, and compliance reporting.',
            'technologies' => ['Laravel', 'Government Integration', 'Security', 'Compliance', 'Multi-language']
        ]
    ];
}

/**
 * Get client testimonials
 */
function getClientTestimonials() {
    // Mock data for client testimonials
    return [
        [
            'quote' => 'The team delivered exactly what we needed. Our donation processing increased by 300% after implementing their custom mobile money solution.',
            'author_name' => 'Sarah Makuei',
            'author_title' => 'Director of Operations',
            'company' => 'Hope Foundation'
        ],
        [
            'quote' => 'Professional service from start to finish. They understood our complex requirements and delivered a robust payment system that handles thousands of transactions daily.',
            'author_name' => 'James Wani',
            'author_title' => 'IT Manager',
            'company' => 'University of Juba'
        ],
        [
            'quote' => 'Excellent technical expertise and ongoing support. Our marketplace now supports multiple payment methods seamlessly across different countries.',
            'author_name' => 'Mary Akol',
            'author_title' => 'CEO',
            'company' => 'AfriTrade Marketplace'
        ],
        [
            'quote' => 'They delivered our government payment portal on time and within budget. The security features and compliance reporting exceeded our expectations.',
            'author_name' => 'David Ayuel',
            'author_title' => 'Systems Administrator',
            'company' => 'Juba City Council'
        ]
    ];
}

/**
 * Submit custom solution request
 */
function submitCustomSolutionRequest($data) {
    global $conn;
    
    try {
        $query = "
            INSERT INTO custom_solution_requests (
                user_id, package_id, full_name, email, company_name, phone,
                project_type, budget_range, project_description, requirements,
                timeline, referral_source, status, created_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'PENDING', NOW())
        ";
        
        $requirements = isset($data['requirements']) ? json_encode($data['requirements']) : null;
        
        $stmt = $conn->prepare($query);
        $stmt->bind_param(
            "isssssssssss",
            $data['user_id'],
            $data['package_id'],
            $data['full_name'],
            $data['email'],
            $data['company_name'],
            $data['phone'],
            $data['project_type'],
            $data['budget_range'],
            $data['project_description'],
            $requirements,
            $data['timeline'],
            $data['referral_source']
        );
        
        if ($stmt->execute()) {
            $request_id = $conn->insert_id;
            $stmt->close();
            
            // Send notification email (mock)
            sendCustomSolutionNotification($data);
            
            return [
                'success' => true,
                'message' => 'Your custom solution request has been submitted successfully. We will contact you within 24 hours with a detailed quote.',
                'request_id' => $request_id
            ];
        } else {
            $stmt->close();
            return [
                'success' => false,
                'message' => 'Failed to submit your request. Please try again or contact support.'
            ];
        }
        
    } catch (Exception $e) {
        error_log("Error submitting custom solution request: " . $e->getMessage());
        return [
            'success' => false,
            'message' => 'An error occurred while submitting your request. Please try again later.'
        ];
    }
}

/**
 * Send custom solution notification (mock function)
 */
function sendCustomSolutionNotification($data) {
    // In a real implementation, this would send an email
    error_log("Custom solution request submitted by: " . $data['email']);
    error_log("Project type: " . $data['project_type']);
    error_log("Budget range: " . $data['budget_range']);
    return true;
}

// Helper function to ensure the custom_solution_requests table exists
function createCustomSolutionRequestsTable() {
    global $conn;
    
    $query = "
        CREATE TABLE IF NOT EXISTS custom_solution_requests (
            request_id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            package_id VARCHAR(50),
            full_name VARCHAR(100) NOT NULL,
            email VARCHAR(100) NOT NULL,
            company_name VARCHAR(255),
            phone VARCHAR(20) NOT NULL,
            project_type VARCHAR(50) NOT NULL,
            budget_range VARCHAR(50),
            project_description TEXT NOT NULL,
            requirements JSON,
            timeline VARCHAR(50),
            referral_source VARCHAR(50),
            status ENUM('PENDING', 'CONTACTED', 'QUOTED', 'ACCEPTED', 'REJECTED') DEFAULT 'PENDING',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
    ";
    
    try {
        $conn->query($query);
        return true;
    } catch (Exception $e) {
        error_log("Error creating custom_solution_requests table: " . $e->getMessage());
        return false;
    }
}

// Initialize the custom solution requests table
createCustomSolutionRequestsTable();

?>