<?php
/**
 * Helper Functions - COMPLETE PRODUCTION VERSION
 * Digital Marketplace
 * 
 * Complete function library for the marketplace system
 */

// Prevent direct access
if (!defined('MARKETPLACE_VERSION')) {
    define('MARKETPLACE_VERSION', '2.0.0');
}

// Include mailer functions
$mailer_file = __DIR__ . '/mailer.php';
if (file_exists($mailer_file)) {
    require_once $mailer_file;
}

// ============================================
// SANITIZATION & VALIDATION
// ============================================

if (!function_exists('sanitize')) {
    function sanitize($data) {
        if ($data === null) return '';
        return htmlspecialchars(strip_tags(trim((string)$data)), ENT_QUOTES, 'UTF-8');
    }
}

if (!function_exists('sanitize_input')) {
    function sanitize_input($data) {
        return sanitize($data);
    }
}

if (!function_exists('sanitize_email')) {
    function sanitize_email($email) {
        if ($email === null) return '';
        return filter_var(trim((string)$email), FILTER_SANITIZE_EMAIL);
    }
}

if (!function_exists('validate_email')) {
    function validate_email($email) {
        if (empty($email)) return false;
        return filter_var($email, FILTER_VALIDATE_EMAIL) !== false;
    }
}

if (!function_exists('is_valid_email')) {
    function is_valid_email($email) {
        return validate_email($email);
    }
}

if (!function_exists('validate_url')) {
    function validate_url($url) {
        if (empty($url)) return false;
        return filter_var($url, FILTER_VALIDATE_URL) !== false;
    }
}

if (!function_exists('validate_phone')) {
    function validate_phone($phone) {
        if (empty($phone)) return false;
        $phone = preg_replace('/[^0-9+]/', '', $phone);
        return strlen($phone) >= 10;
    }
}

// ============================================
// DATE/TIME FUNCTIONS
// ============================================

if (!function_exists('format_date')) {
    function format_date($date, $format = 'M d, Y') {
        if (empty($date) || $date === '0000-00-00' || $date === '0000-00-00 00:00:00') return '-';
        $timestamp = strtotime($date);
        return $timestamp ? date($format, $timestamp) : '-';
    }
}

if (!function_exists('format_datetime')) {
    function format_datetime($datetime, $format = 'M d, Y h:i A') {
        return format_date($datetime, $format);
    }
}

if (!function_exists('time_ago')) {
    function time_ago($datetime) {
        if (empty($datetime)) return 'Unknown';
        $time = strtotime($datetime);
        if (!$time) return 'Unknown';
        
        $diff = time() - $time;
        
        if ($diff < 60) return 'Just now';
        if ($diff < 3600) {
            $mins = floor($diff / 60);
            return $mins . ' min' . ($mins > 1 ? 's' : '') . ' ago';
        }
        if ($diff < 86400) {
            $hours = floor($diff / 3600);
            return $hours . ' hour' . ($hours > 1 ? 's' : '') . ' ago';
        }
        if ($diff < 604800) {
            $days = floor($diff / 86400);
            return $days . ' day' . ($days > 1 ? 's' : '') . ' ago';
        }
        if ($diff < 2592000) {
            $weeks = floor($diff / 604800);
            return $weeks . ' week' . ($weeks > 1 ? 's' : '') . ' ago';
        }
        return date('M d, Y', $time);
    }
}

if (!function_exists('format_duration')) {
    function format_duration($seconds) {
        if ($seconds < 60) return $seconds . 's';
        if ($seconds < 3600) return floor($seconds / 60) . 'm';
        if ($seconds < 86400) return floor($seconds / 3600) . 'h';
        return floor($seconds / 86400) . 'd';
    }
}

// ============================================
// URL & SLUG FUNCTIONS
// ============================================

if (!function_exists('slugify')) {
    function slugify($text) {
        if (empty($text)) return '';
        
        $text = preg_replace('~[^\pL\d]+~u', '-', $text);
        $text = iconv('utf-8', 'us-ascii//TRANSLIT', $text);
        $text = preg_replace('~[^-\w]+~', '', $text);
        $text = trim($text, '-');
        $text = preg_replace('~-+~', '-', $text);
        $text = strtolower($text);
        
        return empty($text) ? 'n-a' : $text;
    }
}

if (!function_exists('generate_unique_slug')) {
    function generate_unique_slug($pdo, $text, $table, $column = 'slug', $exclude_id = null) {
        $slug = slugify($text);
        $original_slug = $slug;
        $counter = 1;
        
        while (true) {
            $sql = "SELECT COUNT(*) FROM {$table} WHERE {$column} = ?";
            $params = [$slug];
            
            if ($exclude_id) {
                $sql .= " AND id != ?";
                $params[] = $exclude_id;
            }
            
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            
            if ($stmt->fetchColumn() == 0) {
                return $slug;
            }
            
            $slug = $original_slug . '-' . $counter;
            $counter++;
        }
    }
}

if (!function_exists('get_base_url')) {
    function get_base_url() {
        return BASE_URL . '/';
    }
}

if (!function_exists('get_current_url')) {
    function get_current_url() {
        $protocol = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off') ? 'https' : 'http';
        return $protocol . '://' . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'];
    }
}

// ============================================
// SETTINGS FUNCTIONS
// ============================================

if (!function_exists('get_all_settings')) {
    function get_all_settings() {
        global $pdo;
        if (!isset($pdo)) return [];
        try {
            $stmt = $pdo->query("SELECT setting_key, setting_value FROM settings");
            $settings = [];
            while ($row = $stmt->fetch()) {
                $settings[$row['setting_key']] = $row['setting_value'];
            }
            return $settings;
        } catch (PDOException $e) {
            return [];
        }
    }
}

if (!function_exists('get_setting')) {
    function get_setting($key, $default = '') {
        global $pdo, $settings;
        
        if (isset($settings[$key])) {
            return $settings[$key];
        }
        
        if (isset($pdo)) {
            try {
                $stmt = $pdo->prepare("SELECT setting_value FROM settings WHERE setting_key = ?");
                $stmt->execute([$key]);
                $result = $stmt->fetch();
                return $result ? $result['setting_value'] : $default;
            } catch (PDOException $e) {
                return $default;
            }
        }
        
        return $default;
    }
}

if (!function_exists('update_setting')) {
    function update_setting($key, $value) {
        global $pdo;
        if (!isset($pdo)) return false;
        try {
            $stmt = $pdo->prepare("INSERT INTO settings (setting_key, setting_value, created_at, updated_at) 
                                   VALUES (?, ?, NOW(), NOW()) 
                                   ON DUPLICATE KEY UPDATE setting_value = ?, updated_at = NOW()");
            return $stmt->execute([$key, $value, $value]);
        } catch (PDOException $e) {
            error_log("Failed to update setting {$key}: " . $e->getMessage());
            return false;
        }
    }
}

if (!function_exists('get_multiple_settings')) {
    function get_multiple_settings($keys) {
        $result = [];
        foreach ($keys as $key) {
            $result[$key] = get_setting($key);
        }
        return $result;
    }
}

// ============================================
// THEME SETTINGS
// ============================================

if (!function_exists('get_theme_colors')) {
    function get_theme_colors($pdo = null) {
        global $pdo;
        
        $defaults = [
            'primary_color' => '#8b5cf6',
            'secondary_color' => '#6366f1',
            'accent_color' => '#f59e0b',
            'success_color' => '#10b981',
            'info_color' => '#3b82f6',
            'warning_color' => '#f59e0b',
            'danger_color' => '#ef4444',
            'dark_color' => '#1f2937',
            'light_color' => '#f3f4f6',
            'logo' => '',
            'favicon' => '',
            'site_name' => 'Digital Marketplace',
            'site_description' => 'Premium Digital Products',
            'site_email' => 'admin@marketplace.com',
            'site_phone' => '',
            'facebook_url' => '',
            'twitter_url' => '',
            'instagram_url' => '',
            'youtube_url' => '',
            'whatsapp_number' => ''
        ];
        
        if (!isset($pdo)) return $defaults;
        
        try {
            $stmt = $pdo->query("SELECT setting_key, setting_value FROM theme_settings");
            while ($row = $stmt->fetch()) {
                if (isset($defaults[$row['setting_key']])) {
                    $defaults[$row['setting_key']] = $row['setting_value'];
                }
            }
        } catch (PDOException $e) {
            // Return defaults
        }
        
        return $defaults;
    }
}

if (!function_exists('update_theme_setting')) {
    function update_theme_setting($key, $value) {
        global $pdo;
        if (!isset($pdo)) return false;
        try {
            $stmt = $pdo->prepare("INSERT INTO theme_settings (setting_key, setting_value, created_at, updated_at) 
                                   VALUES (?, ?, NOW(), NOW()) 
                                   ON DUPLICATE KEY UPDATE setting_value = ?, updated_at = NOW()");
            return $stmt->execute([$key, $value, $value]);
        } catch (PDOException $e) {
            return false;
        }
    }
}

// ============================================
// AUTHENTICATION
// ============================================

if (!function_exists('is_admin_logged_in')) {
    function is_admin_logged_in() {
        return isset($_SESSION['admin_logged_in']) && $_SESSION['admin_logged_in'] === true;
    }
}

if (!function_exists('is_user_logged_in')) {
    function is_user_logged_in() {
        return isset($_SESSION['user_logged_in']) && $_SESSION['user_logged_in'] === true;
    }
}

if (!function_exists('get_current_user_id')) {
    function get_current_user_id() {
        return $_SESSION['user_id'] ?? null;
    }
}

if (!function_exists('get_current_admin_id')) {
    function get_current_admin_id() {
        return $_SESSION['admin_id'] ?? null;
    }
}

if (!function_exists('get_current_user_data')) {
    function get_current_user_data($pdo = null) {
        if (!is_user_logged_in()) return null;
        
        global $pdo;
        if (!isset($pdo)) return null;
        
        try {
            $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
            $stmt->execute([$_SESSION['user_id']]);
            return $stmt->fetch();
        } catch (PDOException $e) {
            return null;
        }
    }
}

if (!function_exists('get_current_admin_data')) {
    function get_current_admin_data($pdo = null) {
        if (!is_admin_logged_in()) return null;
        
        global $pdo;
        if (!isset($pdo)) return null;
        
        try {
            $stmt = $pdo->prepare("SELECT * FROM admins WHERE id = ?");
            $stmt->execute([$_SESSION['admin_id']]);
            return $stmt->fetch();
        } catch (PDOException $e) {
            return null;
        }
    }
}

if (!function_exists('require_login')) {
    function require_login() {
        if (!is_user_logged_in()) {
            $_SESSION['redirect_after_login'] = $_SERVER['REQUEST_URI'];
            header('Location: ' . BASE_URL . '/login.php');
            exit;
        }
    }
}

if (!function_exists('require_admin')) {
    function require_admin() {
        if (!is_admin_logged_in()) {
            $_SESSION['redirect_after_login'] = $_SERVER['REQUEST_URI'];
            header('Location: ' . ADMIN_URL . '/login.php');
            exit;
        }
    }
}

if (!function_exists('check_login_attempts')) {
    function check_login_attempts($identifier) {
        global $pdo;
        if (!isset($pdo)) return true;
        
        try {
            // Clean old attempts
            $stmt = $pdo->prepare("DELETE FROM login_attempts WHERE attempted_at < DATE_SUB(NOW(), INTERVAL ? SECOND)");
            $stmt->execute([LOGIN_LOCKOUT_TIME]);
            
            // Count recent attempts
            $stmt = $pdo->prepare("SELECT COUNT(*) FROM login_attempts WHERE identifier = ? AND attempted_at > DATE_SUB(NOW(), INTERVAL ? SECOND)");
            $stmt->execute([$identifier, LOGIN_LOCKOUT_TIME]);
            $attempts = $stmt->fetchColumn();
            
            return $attempts < MAX_LOGIN_ATTEMPTS;
        } catch (PDOException $e) {
            return true;
        }
    }
}

if (!function_exists('log_login_attempt')) {
    function log_login_attempt($identifier, $success = false) {
        global $pdo;
        if (!isset($pdo)) return;
        
        try {
            $stmt = $pdo->prepare("INSERT INTO login_attempts (identifier, ip_address, success, attempted_at) VALUES (?, ?, ?, NOW())");
            $stmt->execute([$identifier, $_SERVER['REMOTE_ADDR'] ?? '', $success ? 1 : 0]);
        } catch (PDOException $e) {
            // Silently fail
        }
    }
}

if (!function_exists('verify_password')) {
    function verify_password($password, $hash) {
        return password_verify($password, $hash);
    }
}

if (!function_exists('hash_password')) {
    function hash_password($password) {
        return password_hash($password, PASSWORD_BCRYPT, ['cost' => 12]);
    }
}

if (!function_exists('create_user')) {
    function create_user($user_data) {
        global $pdo;
        if (!isset($pdo)) return false;
        
        try {
            $stmt = $pdo->prepare("INSERT INTO users (name, email, phone, password, role, is_active, created_at, updated_at) 
                                   VALUES (?, ?, ?, ?, 'user', 1, NOW(), NOW())");
            $stmt->execute([
                $user_data['name'],
                $user_data['email'],
                $user_data['phone'] ?? null,
                hash_password($user_data['password'])
            ]);
            return $pdo->lastInsertId();
        } catch (PDOException $e) {
            error_log("Error creating user: " . $e->getMessage());
            return false;
        }
    }
}

if (!function_exists('generate_random_string')) {
    function generate_random_string($length = 32) {
        return bin2hex(random_bytes($length / 2));
    }
}

// ============================================
// CSRF PROTECTION
// ============================================

if (!function_exists('generate_csrf_token')) {
    function generate_csrf_token() {
        if (!isset($_SESSION['csrf_token'])) {
            $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
        }
        return $_SESSION['csrf_token'];
    }
}

if (!function_exists('verify_csrf_token')) {
    function verify_csrf_token($token) {
        return isset($_SESSION['csrf_token']) && hash_equals($_SESSION['csrf_token'], $token);
    }
}

if (!function_exists('csrf_field')) {
    function csrf_field() {
        return '<input type="hidden" name="' . CSRF_TOKEN_NAME . '" value="' . generate_csrf_token() . '">';
    }
}

if (!function_exists('csrf_token')) {
    function csrf_token() {
        return generate_csrf_token();
    }
}

// ============================================
// FLASH MESSAGES
// ============================================

if (!function_exists('set_flash_message')) {
    function set_flash_message($message, $type = 'success') {
        $_SESSION['flash_message'] = ['type' => $type, 'message' => $message];
    }
}

if (!function_exists('get_flash_message')) {
    function get_flash_message() {
        if (isset($_SESSION['flash_message'])) {
            $flash = $_SESSION['flash_message'];
            unset($_SESSION['flash_message']);
            
            $alert_class = match($flash['type']) {
                'success' => 'alert-success',
                'error', 'danger' => 'alert-danger',
                'warning' => 'alert-warning',
                'info' => 'alert-info',
                default => 'alert-info'
            };
            
            $icon = match($flash['type']) {
                'success' => 'check-circle',
                'error', 'danger' => 'exclamation-circle',
                'warning' => 'exclamation-triangle',
                'info' => 'info-circle',
                default => 'info-circle'
            };
            
            return '<div class="alert ' . $alert_class . ' alert-dismissible fade show" role="alert">
                <i class="fas fa-' . $icon . ' me-2"></i>' . sanitize($flash['message']) . '
                <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
            </div>';
        }
        return '';
    }
}

if (!function_exists('has_flash_message')) {
    function has_flash_message() {
        return isset($_SESSION['flash_message']);
    }
}

// ============================================
// CATEGORY FUNCTIONS
// ============================================

if (!function_exists('get_categories')) {
    function get_categories($parent_id = null, $active_only = true) {
        global $pdo;
        if (!isset($pdo)) return [];
        try {
            $sql = "SELECT c.*, (SELECT COUNT(*) FROM products p WHERE p.category_id = c.id AND p.status = 'active') as product_count 
                    FROM categories c";
            $params = [];
            
            $conditions = [];
            if ($active_only) {
                $conditions[] = "c.is_active = 1";
            }
            if ($parent_id !== null) {
                $conditions[] = "c.parent_id = ?";
                $params[] = $parent_id;
            } else {
                $conditions[] = "c.parent_id IS NULL";
            }
            
            if (!empty($conditions)) {
                $sql .= " WHERE " . implode(" AND ", $conditions);
            }
            
            $sql .= " ORDER BY c.sort_order, c.name";
            
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            return [];
        }
    }
}

if (!function_exists('get_all_categories')) {
    function get_all_categories($active_only = true) {
        global $pdo;
        if (!isset($pdo)) return [];
        try {
            $sql = "SELECT c.*, (SELECT COUNT(*) FROM products p WHERE p.category_id = c.id AND p.status = 'active') as product_count 
                    FROM categories c";
            if ($active_only) {
                $sql .= " WHERE c.is_active = 1";
            }
            $sql .= " ORDER BY c.sort_order, c.name";
            
            $stmt = $pdo->query($sql);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            return [];
        }
    }
}

if (!function_exists('get_category_by_slug')) {
    function get_category_by_slug($slug) {
        global $pdo;
        if (!isset($pdo)) return null;
        try {
            $stmt = $pdo->prepare("SELECT c.*, (SELECT COUNT(*) FROM products p WHERE p.category_id = c.id AND p.status = 'active') as product_count 
                                  FROM categories c WHERE c.slug = ? AND c.is_active = 1");
            $stmt->execute([$slug]);
            return $stmt->fetch();
        } catch (PDOException $e) {
            return null;
        }
    }
}

if (!function_exists('get_category_by_id')) {
    function get_category_by_id($id) {
        global $pdo;
        if (!isset($pdo)) return null;
        try {
            $stmt = $pdo->prepare("SELECT * FROM categories WHERE id = ?");
            $stmt->execute([$id]);
            return $stmt->fetch();
        } catch (PDOException $e) {
            return null;
        }
    }
}

// ============================================
// PRODUCT FUNCTIONS
// ============================================

if (!function_exists('get_products')) {
    function get_products($options = []) {
        global $pdo;
        if (!isset($pdo)) return [];
        
        $defaults = [
            'limit' => null,
            'offset' => 0,
            'category_id' => null,
            'category_slug' => null,
            'featured' => null,
            'bestseller' => null,
            'search' => null,
            'sort' => 'newest',
            'status' => 'active',
            'min_price' => null,
            'max_price' => null
        ];
        $options = array_merge($defaults, $options);
        
        try {
            $sql = "SELECT p.*, c.name as category_name, c.slug as category_slug,
                    (SELECT AVG(rating) FROM reviews WHERE product_id = p.id AND status = 'approved') as avg_rating,
                    (SELECT COUNT(*) FROM reviews WHERE product_id = p.id AND status = 'approved') as review_count
                    FROM products p 
                    LEFT JOIN categories c ON p.category_id = c.id 
                    WHERE 1=1";
            $params = [];
            
            if ($options['status']) {
                $sql .= " AND p.status = ?";
                $params[] = $options['status'];
            }
            
            if ($options['category_id']) {
                $sql .= " AND p.category_id = ?";
                $params[] = $options['category_id'];
            }
            
            if ($options['category_slug']) {
                $sql .= " AND c.slug = ?";
                $params[] = $options['category_slug'];
            }
            
            if ($options['featured'] !== null) {
                $sql .= " AND p.featured = ?";
                $params[] = $options['featured'] ? 1 : 0;
            }
            
            if ($options['bestseller'] !== null) {
                $sql .= " AND (p.bestseller = 1 OR p.times_sold > 50)";
            }
            
            if ($options['search']) {
                $sql .= " AND (p.name LIKE ? OR p.description LIKE ? OR p.short_description LIKE ?)";
                $search_term = '%' . $options['search'] . '%';
                $params[] = $search_term;
                $params[] = $search_term;
                $params[] = $search_term;
            }
            
            if ($options['min_price'] !== null) {
                $sql .= " AND (p.sale_price > 0 AND p.sale_price >= ? OR p.sale_price = 0 AND p.price >= ?)";
                $params[] = $options['min_price'];
                $params[] = $options['min_price'];
            }
            
            if ($options['max_price'] !== null) {
                $sql .= " AND (p.sale_price > 0 AND p.sale_price <= ? OR p.sale_price = 0 AND p.price <= ?)";
                $params[] = $options['max_price'];
                $params[] = $options['max_price'];
            }
            
            // Sorting
            switch ($options['sort']) {
                case 'price_low':
                    $sql .= " ORDER BY (CASE WHEN p.sale_price > 0 THEN p.sale_price ELSE p.price END) ASC";
                    break;
                case 'price_high':
                    $sql .= " ORDER BY (CASE WHEN p.sale_price > 0 THEN p.sale_price ELSE p.price END) DESC";
                    break;
                case 'bestseller':
                    $sql .= " ORDER BY p.times_sold DESC";
                    break;
                case 'rating':
                    $sql .= " ORDER BY avg_rating DESC";
                    break;
                case 'popular':
                    $sql .= " ORDER BY p.views DESC";
                    break;
                case 'newest':
                default:
                    $sql .= " ORDER BY p.created_at DESC";
            }
            
            if ($options['limit']) {
                $sql .= " LIMIT ? OFFSET ?";
                $params[] = (int)$options['limit'];
                $params[] = (int)$options['offset'];
            }
            
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            error_log("Error getting products: " . $e->getMessage());
            return [];
        }
    }
}

if (!function_exists('get_product_by_id')) {
    function get_product_by_id($id) {
        global $pdo;
        if (!isset($pdo)) return null;
        try {
            $stmt = $pdo->prepare("SELECT p.*, c.name as category_name, c.slug as category_slug,
                                  (SELECT AVG(rating) FROM reviews WHERE product_id = p.id AND status = 'approved') as avg_rating,
                                  (SELECT COUNT(*) FROM reviews WHERE product_id = p.id AND status = 'approved') as review_count
                                  FROM products p 
                                  LEFT JOIN categories c ON p.category_id = c.id 
                                  WHERE p.id = ?");
            $stmt->execute([$id]);
            return $stmt->fetch();
        } catch (PDOException $e) {
            return null;
        }
    }
}

if (!function_exists('get_product_by_slug')) {
    function get_product_by_slug($slug) {
        global $pdo;
        if (!isset($pdo)) return null;
        try {
            $stmt = $pdo->prepare("SELECT p.*, c.name as category_name, c.slug as category_slug,
                                  (SELECT AVG(rating) FROM reviews WHERE product_id = p.id AND status = 'approved') as avg_rating,
                                  (SELECT COUNT(*) FROM reviews WHERE product_id = p.id AND status = 'approved') as review_count
                                  FROM products p 
                                  LEFT JOIN categories c ON p.category_id = c.id 
                                  WHERE p.slug = ? AND p.status = 'active'");
            $stmt->execute([$slug]);
            return $stmt->fetch();
        } catch (PDOException $e) {
            return null;
        }
    }
}

if (!function_exists('get_featured_products')) {
    function get_featured_products($limit = 8) {
        return get_products(['featured' => true, 'limit' => $limit]);
    }
}

if (!function_exists('get_bestseller_products')) {
    function get_bestseller_products($limit = 4) {
        return get_products(['bestseller' => true, 'limit' => $limit]);
    }
}

if (!function_exists('get_related_products')) {
    function get_related_products($product_id, $category_id, $limit = 4) {
        global $pdo;
        if (!isset($pdo)) return [];
        try {
            $stmt = $pdo->prepare("SELECT p.*, c.name as category_name, c.slug as category_slug 
                                  FROM products p 
                                  LEFT JOIN categories c ON p.category_id = c.id 
                                  WHERE p.category_id = ? AND p.id != ? AND p.status = 'active' 
                                  ORDER BY RAND() LIMIT ?");
            $stmt->execute([$category_id, $product_id, $limit]);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            return [];
        }
    }
}

if (!function_exists('get_product_images')) {
    function get_product_images($product_id) {
        global $pdo;
        if (!isset($pdo)) return [];
        try {
            $stmt = $pdo->prepare("SELECT * FROM product_images WHERE product_id = ? ORDER BY sort_order ASC");
            $stmt->execute([$product_id]);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            return [];
        }
    }
}

if (!function_exists('get_product_licenses')) {
    function get_product_licenses($product_id) {
        global $pdo;
        if (!isset($pdo)) return [];
        try {
            $stmt = $pdo->prepare("SELECT * FROM product_licenses WHERE product_id = ? AND is_active = 1 ORDER BY price ASC");
            $stmt->execute([$product_id]);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            return [];
        }
    }
}

if (!function_exists('get_product_image_url')) {
    function get_product_image_url($product, $size = 'full') {
        $image = '';
        
        if (is_array($product)) {
            $image = $product['image'] ?? $product['image_path'] ?? '';
        } else {
            $image = $product;
        }
        
        if (empty($image)) {
            return ASSETS_URL . '/images/no-image.jpg';
        }
        
        if (strpos($image, 'http') === 0) {
            return $image;
        }
        
        if (strpos($image, 'uploads/') !== 0 && strpos($image, '/') !== 0) {
            $image = 'uploads/products/' . $image;
        }
        
        $image = ltrim($image, '/');
        
        return BASE_URL . '/' . $image;
    }
}

if (!function_exists('get_youtube_embed_url')) {
    function get_youtube_embed_url($url) {
        if (empty($url)) return null;

        $url = trim($url);

        if (preg_match('/youtu\.be\/([^\?&]+)/', $url, $matches)) {
            return 'https://www.youtube.com/embed/' . $matches[1];
        }

        if (preg_match('/v=([^&]+)/', $url, $matches)) {
            return 'https://www.youtube.com/embed/' . $matches[1];
        }

        if (strpos($url, 'embed/') !== false) {
            return $url;
        }

        return null;
    }
}

if (!function_exists('increment_product_views')) {
    function increment_product_views($product_id) {
        global $pdo;
        if (!isset($pdo)) return;
        
        try {
            $stmt = $pdo->prepare("UPDATE products SET views = views + 1 WHERE id = ?");
            $stmt->execute([$product_id]);
        } catch (PDOException $e) {
            // Silently fail
        }
    }
}

if (!function_exists('update_product_rating')) {
    function update_product_rating($product_id) {
        global $pdo;
        if (!isset($pdo)) return;
        
        try {
            $stmt = $pdo->prepare("UPDATE products SET rating = (SELECT AVG(rating) FROM reviews WHERE product_id = ? AND status = 'approved'), review_count = (SELECT COUNT(*) FROM reviews WHERE product_id = ? AND status = 'approved') WHERE id = ?");
            $stmt->execute([$product_id, $product_id, $product_id]);
        } catch (PDOException $e) {
            // Silently fail
        }
    }
}

if (!function_exists('get_product_reviews')) {
    function get_product_reviews($product_id, $status = 'approved') {
        global $pdo;
        if (!isset($pdo)) return [];
        
        try {
            $sql = "SELECT r.*, u.name as user_name 
                   FROM reviews r 
                   JOIN users u ON r.user_id = u.id 
                   WHERE r.product_id = ?";
            if ($status) {
                $sql .= " AND r.status = ?";
            }
            $sql .= " ORDER BY r.created_at DESC";
            
            $stmt = $pdo->prepare($sql);
            $params = [$product_id];
            if ($status) {
                $params[] = $status;
            }
            $stmt->execute($params);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            return [];
        }
    }
}

if (!function_exists('add_review')) {
    function add_review($product_id, $user_id, $rating, $review) {
        global $pdo;
        if (!isset($pdo)) return false;
        
        try {
            $stmt = $pdo->prepare("INSERT INTO reviews (product_id, user_id, rating, review, status, created_at, updated_at) VALUES (?, ?, ?, ?, 'pending', NOW(), NOW())");
            return $stmt->execute([$product_id, $user_id, $rating, $review]);
        } catch (PDOException $e) {
            return false;
        }
    }
}

// ============================================
// PRICING FUNCTIONS
// ============================================

if (!function_exists('format_price')) {
    function format_price($price, $decimals = 2) {
        if ($price === null || $price === '') return CURRENCY_SYMBOL . '0.00';
        
        $formatted = number_format((float)$price, $decimals);
        
        if (CURRENCY_POSITION === 'after') {
            return $formatted . ' ' . CURRENCY_SYMBOL;
        }
        return CURRENCY_SYMBOL . $formatted;
    }
}

if (!function_exists('calculate_discount')) {
    function calculate_discount($original_price, $sale_price) {
        if (!$sale_price || $sale_price >= $original_price) return 0;
        return round((($original_price - $sale_price) / $original_price) * 100);
    }
}

if (!function_exists('get_product_price')) {
    function get_product_price($product, $license_type = 'regular') {
        if (!is_array($product)) return 0;
        
        if ($license_type === 'extended' && !empty($product['extended_license_price'])) {
            return $product['extended_license_price'];
        }
        if ($license_type === 'regular' && !empty($product['regular_license_price'])) {
            return $product['regular_license_price'];
        }
        
        if (!empty($product['sale_price']) && $product['sale_price'] > 0) {
            return $product['sale_price'];
        }
        
        return $product['price'] ?? 0;
    }
}

if (!function_exists('truncate')) {
    function truncate($text, $length = 100, $suffix = '...') {
        if ($text === null) return '';
        $text = (string)$text;
        if (strlen($text) <= $length) return $text;
        return substr($text, 0, $length) . $suffix;
    }
}

// ============================================
// CART FUNCTIONS
// ============================================

if (!function_exists('get_cart_count')) {
    function get_cart_count() {
        global $pdo;
        if (!isset($pdo)) return 0;
        
        $session_id = session_id();
        $user_id = get_current_user_id();
        
        try {
            if ($user_id) {
                $stmt = $pdo->prepare("SELECT SUM(quantity) FROM cart WHERE user_id = ?");
                $stmt->execute([$user_id]);
            } else {
                $stmt = $pdo->prepare("SELECT SUM(quantity) FROM cart WHERE session_id = ?");
                $stmt->execute([$session_id]);
            }
            return (int)$stmt->fetchColumn();
        } catch (PDOException $e) {
            return 0;
        }
    }
}

if (!function_exists('get_cart_items')) {
    function get_cart_items() {
        global $pdo;
        if (!isset($pdo)) return [];
        
        $session_id = session_id();
        $user_id = get_current_user_id();
        
        try {
            if ($user_id) {
                $stmt = $pdo->prepare("SELECT c.*, p.name, p.slug, p.image, p.stock, p.file_path, p.download_limit, p.download_url, p.regular_license_price, p.extended_license_price
                                      FROM cart c 
                                      JOIN products p ON c.product_id = p.id 
                                      WHERE c.user_id = ?");
                $stmt->execute([$user_id]);
            } else {
                $stmt = $pdo->prepare("SELECT c.*, p.name, p.slug, p.image, p.stock, p.file_path, p.download_limit, p.download_url, p.regular_license_price, p.extended_license_price
                                      FROM cart c 
                                      JOIN products p ON c.product_id = p.id 
                                      WHERE c.session_id = ?");
                $stmt->execute([$session_id]);
            }
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            return [];
        }
    }
}

if (!function_exists('get_cart_total')) {
    function get_cart_total() {
        $items = get_cart_items();
        $total = 0;
        foreach ($items as $item) {
            $total += $item['price'] * $item['quantity'];
        }
        return $total;
    }
}

if (!function_exists('add_to_cart')) {
    function add_to_cart($product_id, $quantity = 1, $license_type = 'regular') {
        global $pdo;
        if (!isset($pdo)) return false;
        
        $session_id = session_id();
        $user_id = get_current_user_id();
        
        try {
            // Get product price
            $product = get_product_by_id($product_id);
            if (!$product) return false;
            
            $price = get_product_price($product, $license_type);
            
            // Check if item already in cart
            if ($user_id) {
                $stmt = $pdo->prepare("SELECT id, quantity FROM cart WHERE user_id = ? AND product_id = ?");
                $stmt->execute([$user_id, $product_id]);
            } else {
                $stmt = $pdo->prepare("SELECT id, quantity FROM cart WHERE session_id = ? AND product_id = ?");
                $stmt->execute([$session_id, $product_id]);
            }
            $existing = $stmt->fetch();
            
            if ($existing) {
                // Update quantity
                $new_quantity = $existing['quantity'] + $quantity;
                $stmt = $pdo->prepare("UPDATE cart SET quantity = ?, price = ? WHERE id = ?");
                return $stmt->execute([$new_quantity, $price, $existing['id']]);
            } else {
                // Add new item
                if ($user_id) {
                    $stmt = $pdo->prepare("INSERT INTO cart (user_id, product_id, quantity, price, created_at, updated_at) VALUES (?, ?, ?, ?, NOW(), NOW())");
                    return $stmt->execute([$user_id, $product_id, $quantity, $price]);
                } else {
                    $stmt = $pdo->prepare("INSERT INTO cart (session_id, product_id, quantity, price, created_at, updated_at) VALUES (?, ?, ?, ?, NOW(), NOW())");
                    return $stmt->execute([$session_id, $product_id, $quantity, $price]);
                }
            }
        } catch (PDOException $e) {
            error_log("Error adding to cart: " . $e->getMessage());
            return false;
        }
    }
}

if (!function_exists('update_cart_quantity')) {
    function update_cart_quantity($cart_id, $quantity) {
        global $pdo;
        if (!isset($pdo)) return false;
        
        try {
            if ($quantity <= 0) {
                $stmt = $pdo->prepare("DELETE FROM cart WHERE id = ?");
                return $stmt->execute([$cart_id]);
            } else {
                $stmt = $pdo->prepare("UPDATE cart SET quantity = ? WHERE id = ?");
                return $stmt->execute([$quantity, $cart_id]);
            }
        } catch (PDOException $e) {
            return false;
        }
    }
}

if (!function_exists('remove_from_cart')) {
    function remove_from_cart($cart_id) {
        global $pdo;
        if (!isset($pdo)) return false;
        
        try {
            $stmt = $pdo->prepare("DELETE FROM cart WHERE id = ?");
            return $stmt->execute([$cart_id]);
        } catch (PDOException $e) {
            return false;
        }
    }
}

if (!function_exists('clear_cart')) {
    function clear_cart() {
        global $pdo;
        if (!isset($pdo)) return false;
        
        $session_id = session_id();
        $user_id = get_current_user_id();
        
        try {
            if ($user_id) {
                $stmt = $pdo->prepare("DELETE FROM cart WHERE user_id = ?");
                return $stmt->execute([$user_id]);
            } else {
                $stmt = $pdo->prepare("DELETE FROM cart WHERE session_id = ?");
                return $stmt->execute([$session_id]);
            }
        } catch (PDOException $e) {
            return false;
        }
    }
}

if (!function_exists('merge_cart_on_login')) {
    function merge_cart_on_login($user_id) {
        global $pdo;
        if (!isset($pdo)) return;
        
        $session_id = session_id();
        
        try {
            // Get session cart items
            $stmt = $pdo->prepare("SELECT * FROM cart WHERE session_id = ? AND user_id IS NULL");
            $stmt->execute([$session_id]);
            $session_items = $stmt->fetchAll();
            
            foreach ($session_items as $item) {
                // Check if user already has this product
                $stmt = $pdo->prepare("SELECT id, quantity FROM cart WHERE user_id = ? AND product_id = ?");
                $stmt->execute([$user_id, $item['product_id']]);
                $existing = $stmt->fetch();
                
                if ($existing) {
                    // Update quantity
                    $new_quantity = $existing['quantity'] + $item['quantity'];
                    $stmt = $pdo->prepare("UPDATE cart SET quantity = ? WHERE id = ?");
                    $stmt->execute([$new_quantity, $existing['id']]);
                    
                    // Delete session item
                    $stmt = $pdo->prepare("DELETE FROM cart WHERE id = ?");
                    $stmt->execute([$item['id']]);
                } else {
                    // Update session item to user
                    $stmt = $pdo->prepare("UPDATE cart SET user_id = ?, session_id = NULL WHERE id = ?");
                    $stmt->execute([$user_id, $item['id']]);
                }
            }
        } catch (PDOException $e) {
            error_log("Error merging cart: " . $e->getMessage());
        }
    }
}

// ============================================
// ORDER FUNCTIONS
// ============================================

if (!function_exists('create_order')) {
    function create_order($order_data) {
        global $pdo;
        if (!isset($pdo)) return false;
        
        try {
            $order_number = 'ORD-' . date('Ymd') . '-' . strtoupper(substr(uniqid(), -6));
            
            $stmt = $pdo->prepare("INSERT INTO orders 
                (order_number, user_id, customer_name, customer_email, customer_phone, 
                 subtotal, tax, shipping, discount, total, coupon_code, payment_method, 
                 payment_status, order_status, affiliate_id, affiliate_commission, created_at, updated_at) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', 'pending', ?, ?, NOW(), NOW())");
            
            $stmt->execute([
                $order_number,
                $order_data['user_id'] ?? null,
                $order_data['customer_name'],
                $order_data['customer_email'],
                $order_data['customer_phone'] ?? null,
                $order_data['subtotal'],
                $order_data['tax'] ?? 0,
                $order_data['shipping'] ?? 0,
                $order_data['discount'] ?? 0,
                $order_data['total'],
                $order_data['coupon_code'] ?? null,
                $order_data['payment_method'],
                $order_data['affiliate_id'] ?? null,
                $order_data['affiliate_commission'] ?? 0
            ]);
            
            return $pdo->lastInsertId();
        } catch (PDOException $e) {
            error_log("Error creating order: " . $e->getMessage());
            return false;
        }
    }
}

if (!function_exists('add_order_item')) {
    function add_order_item($order_id, $product_id, $product_name, $price, $quantity, $license_type = 'regular', $download_limit = 5) {
        global $pdo;
        if (!isset($pdo)) return false;
        
        try {
            $stmt = $pdo->prepare("INSERT INTO order_items 
                (order_id, product_id, product_name, product_price, quantity, subtotal, download_limit, created_at) 
                VALUES (?, ?, ?, ?, ?, ?, ?, NOW())");
            
            $subtotal = $price * $quantity;
            
            return $stmt->execute([
                $order_id,
                $product_id,
                $product_name,
                $price,
                $quantity,
                $subtotal,
                $download_limit
            ]);
        } catch (PDOException $e) {
            error_log("Error adding order item: " . $e->getMessage());
            return false;
        }
    }
}

if (!function_exists('get_order_by_id')) {
    function get_order_by_id($order_id) {
        global $pdo;
        if (!isset($pdo)) return null;
        
        try {
            $stmt = $pdo->prepare("SELECT o.*, u.name as user_name, u.email as user_email 
                                  FROM orders o 
                                  LEFT JOIN users u ON o.user_id = u.id 
                                  WHERE o.id = ?");
            $stmt->execute([$order_id]);
            return $stmt->fetch();
        } catch (PDOException $e) {
            return null;
        }
    }
}

if (!function_exists('get_order_by_number')) {
    function get_order_by_number($order_number) {
        global $pdo;
        if (!isset($pdo)) return null;
        
        try {
            $stmt = $pdo->prepare("SELECT o.*, u.name as user_name, u.email as user_email 
                                  FROM orders o 
                                  LEFT JOIN users u ON o.user_id = u.id 
                                  WHERE o.order_number = ?");
            $stmt->execute([$order_number]);
            return $stmt->fetch();
        } catch (PDOException $e) {
            return null;
        }
    }
}

if (!function_exists('get_order_items')) {
    function get_order_items($order_id) {
        global $pdo;
        if (!isset($pdo)) return [];
        
        try {
            $stmt = $pdo->prepare("SELECT * FROM order_items WHERE order_id = ?");
            $stmt->execute([$order_id]);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            return [];
        }
    }
}

if (!function_exists('update_order_payment_status')) {
    function update_order_payment_status($order_id, $status, $payment_id = null) {
        global $pdo;
        if (!isset($pdo)) return false;
        
        try {
            $sql = "UPDATE orders SET payment_status = ?, updated_at = NOW()";
            $params = [$status];
            
            if ($payment_id) {
                $sql .= ", payment_id = ?";
                $params[] = $payment_id;
            }
            
            if ($status === 'completed' || $status === 'paid') {
                $sql .= ", order_status = 'completed'";
            }
            
            $sql .= " WHERE id = ?";
            $params[] = $order_id;
            
            $stmt = $pdo->prepare($sql);
            return $stmt->execute($params);
        } catch (PDOException $e) {
            return false;
        }
    }
}

if (!function_exists('get_user_orders')) {
    function get_user_orders($user_id, $limit = null) {
        global $pdo;
        if (!isset($pdo)) return [];
        
        try {
            $sql = "SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC";
            if ($limit) {
                $sql .= " LIMIT " . (int)$limit;
            }
            $stmt = $pdo->prepare($sql);
            $stmt->execute([$user_id]);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            return [];
        }
    }
}

// ============================================
// DOWNLOAD FUNCTIONS
// ============================================

if (!function_exists('get_user_downloads')) {
    function get_user_downloads($user_id) {
        global $pdo;
        if (!isset($pdo)) return [];
        
        try {
            $stmt = $pdo->prepare("SELECT oi.*, o.order_number, o.payment_status, p.file_path, p.download_url 
                                  FROM order_items oi 
                                  JOIN orders o ON oi.order_id = o.id 
                                  JOIN products p ON oi.product_id = p.id 
                                  WHERE o.user_id = ? AND o.payment_status = 'completed'");
            $stmt->execute([$user_id]);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            return [];
        }
    }
}

if (!function_exists('can_download')) {
    function can_download($download) {
        if ($download['payment_status'] !== 'completed') return false;
        if ($download['download_limit'] > 0 && $download['download_count'] >= $download['download_limit']) return false;
        return true;
    }
}

if (!function_exists('increment_download_count')) {
    function increment_download_count($order_item_id) {
        global $pdo;
        if (!isset($pdo)) return false;
        
        try {
            $stmt = $pdo->prepare("UPDATE order_items SET download_count = download_count + 1 WHERE id = ?");
            return $stmt->execute([$order_item_id]);
        } catch (PDOException $e) {
            return false;
        }
    }
}

// ============================================
// COUPON FUNCTIONS
// ============================================

if (!function_exists('get_coupon_by_code')) {
    function get_coupon_by_code($code) {
        global $pdo;
        if (!isset($pdo)) return null;
        
        try {
            $stmt = $pdo->prepare("SELECT * FROM coupons WHERE code = ? AND is_active = 1 
                                  AND (start_date IS NULL OR start_date <= CURDATE()) 
                                  AND (end_date IS NULL OR end_date >= CURDATE())");
            $stmt->execute([$code]);
            return $stmt->fetch();
        } catch (PDOException $e) {
            return null;
        }
    }
}

if (!function_exists('calculate_coupon_discount')) {
    function calculate_coupon_discount($coupon, $subtotal) {
        if ($coupon['discount_type'] === 'percentage') {
            $discount = $subtotal * ($coupon['discount_value'] / 100);
        } else {
            $discount = $coupon['discount_value'];
        }
        
        if ($coupon['max_discount'] > 0) {
            $discount = min($discount, $coupon['max_discount']);
        }
        
        return min($discount, $subtotal);
    }
}

if (!function_exists('increment_coupon_usage')) {
    function increment_coupon_usage($coupon_id) {
        global $pdo;
        if (!isset($pdo)) return false;
        
        try {
            $stmt = $pdo->prepare("UPDATE coupons SET usage_count = usage_count + 1 WHERE id = ?");
            return $stmt->execute([$coupon_id]);
        } catch (PDOException $e) {
            return false;
        }
    }
}

// ============================================
// PAYMENT GATEWAY FUNCTIONS
// ============================================

if (!function_exists('get_active_payment_gateways')) {
    function get_active_payment_gateways() {
        global $pdo;
        if (!isset($pdo)) return [];
        
        try {
            $stmt = $pdo->query("SELECT * FROM payment_settings WHERE is_active = 1 ORDER BY gateway ASC");
            $gateways = $stmt->fetchAll();
            
            foreach ($gateways as &$gateway) {
                $gateway['config'] = json_decode($gateway['config'] ?? '{}', true);
            }
            
            return $gateways;
        } catch (PDOException $e) {
            return [];
        }
    }
}

if (!function_exists('get_payment_gateway')) {
    function get_payment_gateway($gateway) {
        global $pdo;
        if (!isset($pdo)) return null;
        
        try {
            $stmt = $pdo->prepare("SELECT * FROM payment_settings WHERE gateway = ?");
            $stmt->execute([$gateway]);
            $result = $stmt->fetch();
            
            if ($result) {
                $result['config'] = json_decode($result['config'] ?? '{}', true);
            }
            
            return $result;
        } catch (PDOException $e) {
            return null;
        }
    }
}

// ============================================
// NEGOTIATION FUNCTIONS
// ============================================

if (!function_exists('create_negotiation')) {
    function create_negotiation($product_id, $user_id, $offered_price, $message = '') {
        global $pdo;
        if (!isset($pdo)) return false;
        
        try {
            // Get product original price
            $product = get_product_by_id($product_id);
            if (!$product) return false;
            
            $stmt = $pdo->prepare("INSERT INTO negotiations 
                (product_id, user_id, original_price, offered_price, message, status, created_at, updated_at) 
                VALUES (?, ?, ?, ?, ?, 'pending', NOW(), NOW())");
            
            return $stmt->execute([
                $product_id,
                $user_id,
                $product['price'],
                $offered_price,
                $message
            ]);
        } catch (PDOException $e) {
            error_log("Error creating negotiation: " . $e->getMessage());
            return false;
        }
    }
}

if (!function_exists('get_user_negotiations')) {
    function get_user_negotiations($user_id) {
        global $pdo;
        if (!isset($pdo)) return [];
        
        try {
            $stmt = $pdo->prepare("SELECT n.*, p.name as product_name, p.slug as product_slug, p.image as product_image 
                                  FROM negotiations n 
                                  JOIN products p ON n.product_id = p.id 
                                  WHERE n.user_id = ? 
                                  ORDER BY n.created_at DESC");
            $stmt->execute([$user_id]);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            return [];
        }
    }
}

if (!function_exists('get_pending_negotiations')) {
    function get_pending_negotiations() {
        global $pdo;
        if (!isset($pdo)) return [];
        
        try {
            $stmt = $pdo->query("SELECT n.*, p.name as product_name, u.name as user_name, u.email as user_email 
                                FROM negotiations n 
                                JOIN products p ON n.product_id = p.id 
                                JOIN users u ON n.user_id = u.id 
                                WHERE n.status = 'pending' 
                                ORDER BY n.created_at DESC");
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            return [];
        }
    }
}

if (!function_exists('respond_to_negotiation')) {
    function respond_to_negotiation($negotiation_id, $status, $admin_message = '', $counter_price = null) {
        global $pdo;
        if (!isset($pdo)) return false;
        
        try {
            $sql = "UPDATE negotiations SET status = ?, admin_message = ?, responded_at = NOW(), updated_at = NOW()";
            $params = [$status, $admin_message];
            
            if ($counter_price !== null) {
                $sql .= ", counter_price = ?";
                $params[] = $counter_price;
            }
            
            $sql .= " WHERE id = ?";
            $params[] = $negotiation_id;
            
            $stmt = $pdo->prepare($sql);
            $result = $stmt->execute($params);
            
            if ($result) {
                // Send email notification
                send_negotiation_response_email($negotiation_id);
            }
            
            return $result;
        } catch (PDOException $e) {
            return false;
        }
    }
}

// ============================================
// AFFILIATE FUNCTIONS
// ============================================

if (!function_exists('get_affiliate_by_user_id')) {
    function get_affiliate_by_user_id($user_id) {
        global $pdo;
        if (!isset($pdo)) return null;
        
        try {
            $stmt = $pdo->prepare("SELECT * FROM affiliates WHERE user_id = ?");
            $stmt->execute([$user_id]);
            return $stmt->fetch();
        } catch (PDOException $e) {
            return null;
        }
    }
}

if (!function_exists('get_affiliate_by_code')) {
    function get_affiliate_by_code($code) {
        global $pdo;
        if (!isset($pdo)) return null;
        
        try {
            $stmt = $pdo->prepare("SELECT * FROM affiliates WHERE affiliate_code = ? AND status = 'active'");
            $stmt->execute([$code]);
            return $stmt->fetch();
        } catch (PDOException $e) {
            return null;
        }
    }
}

if (!function_exists('create_affiliate')) {
    function create_affiliate($user_id, $commission_rate = 10) {
        global $pdo;
        if (!isset($pdo)) return false;
        
        try {
            // Generate unique affiliate code
            $code = strtoupper(substr(md5(uniqid()), 0, 8));
            
            $stmt = $pdo->prepare("INSERT INTO affiliates 
                (user_id, affiliate_code, commission_rate, status, created_at, updated_at) 
                VALUES (?, ?, ?, 'pending', NOW(), NOW())");
            
            return $stmt->execute([$user_id, $code, $commission_rate]);
        } catch (PDOException $e) {
            error_log("Error creating affiliate: " . $e->getMessage());
            return false;
        }
    }
}

if (!function_exists('track_affiliate_referral')) {
    function track_affiliate_referral($affiliate_id, $order_id, $order_total) {
        global $pdo;
        if (!isset($pdo)) return false;
        
        try {
            // Get affiliate commission rate
            $affiliate = get_affiliate_by_user_id($affiliate_id);
            if (!$affiliate) return false;
            
            $commission = $order_total * ($affiliate['commission_rate'] / 100);
            
            $stmt = $pdo->prepare("INSERT INTO affiliate_referrals 
                (affiliate_id, order_id, commission_amount, status, created_at) 
                VALUES (?, ?, ?, 'pending', NOW())");
            
            return $stmt->execute([$affiliate_id, $order_id, $commission]);
        } catch (PDOException $e) {
            error_log("Error tracking affiliate referral: " . $e->getMessage());
            return false;
        }
    }
}

if (!function_exists('get_affiliate_stats')) {
    function get_affiliate_stats($affiliate_id) {
        global $pdo;
        if (!isset($pdo)) return [];
        
        try {
            $stmt = $pdo->prepare("SELECT 
                COUNT(*) as total_referrals,
                SUM(CASE WHEN status = 'approved' THEN commission_amount ELSE 0 END) as total_earnings,
                SUM(CASE WHEN status = 'pending' THEN commission_amount ELSE 0 END) as pending_earnings,
                SUM(CASE WHEN status = 'paid' THEN commission_amount ELSE 0 END) as paid_earnings
                FROM affiliate_referrals 
                WHERE affiliate_id = ?");
            $stmt->execute([$affiliate_id]);
            return $stmt->fetch();
        } catch (PDOException $e) {
            return [];
        }
    }
}

// ============================================
// WISHLIST FUNCTIONS
// ============================================

if (!function_exists('is_in_wishlist')) {
    function is_in_wishlist($product_id) {
        global $pdo;
        if (!isset($pdo) || !is_user_logged_in()) return false;
        
        try {
            $stmt = $pdo->prepare("SELECT id FROM wishlist WHERE user_id = ? AND product_id = ?");
            $stmt->execute([get_current_user_id(), $product_id]);
            return $stmt->fetch() !== false;
        } catch (PDOException $e) {
            return false;
        }
    }
}

if (!function_exists('add_to_wishlist')) {
    function add_to_wishlist($product_id) {
        global $pdo;
        if (!isset($pdo) || !is_user_logged_in()) return false;
        
        try {
            $stmt = $pdo->prepare("INSERT IGNORE INTO wishlist (user_id, product_id, created_at) VALUES (?, ?, NOW())");
            return $stmt->execute([get_current_user_id(), $product_id]);
        } catch (PDOException $e) {
            return false;
        }
    }
}

if (!function_exists('remove_from_wishlist')) {
    function remove_from_wishlist($product_id) {
        global $pdo;
        if (!isset($pdo) || !is_user_logged_in()) return false;
        
        try {
            $stmt = $pdo->prepare("DELETE FROM wishlist WHERE user_id = ? AND product_id = ?");
            return $stmt->execute([get_current_user_id(), $product_id]);
        } catch (PDOException $e) {
            return false;
        }
    }
}

if (!function_exists('get_user_wishlist')) {
    function get_user_wishlist($user_id) {
        global $pdo;
        if (!isset($pdo)) return [];
        
        try {
            $stmt = $pdo->prepare("SELECT p.* FROM wishlist w JOIN products p ON w.product_id = p.id WHERE w.user_id = ?");
            $stmt->execute([$user_id]);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            return [];
        }
    }
}

// ============================================
// POPUP FUNCTIONS
// ============================================

if (!function_exists('get_active_popup')) {
    function get_active_popup() {
        global $pdo;
        if (!isset($pdo)) return null;
        
        try {
            $stmt = $pdo->query("SELECT * FROM popups WHERE is_active = 1 
                                AND (start_date IS NULL OR start_date <= CURDATE()) 
                                AND (end_date IS NULL OR end_date >= CURDATE()) 
                                ORDER BY created_at DESC LIMIT 1");
            return $stmt->fetch();
        } catch (PDOException $e) {
            return null;
        }
    }
}

// ============================================
// WHATSAPP FUNCTIONS
// ============================================

if (!function_exists('get_whatsapp_settings')) {
    function get_whatsapp_settings() {
        global $pdo;
        if (!isset($pdo)) return [];
        
        try {
            $stmt = $pdo->query("SELECT setting_key, setting_value FROM whatsapp_settings");
            $settings = [];
            while ($row = $stmt->fetch()) {
                $settings[$row['setting_key']] = $row['setting_value'];
            }
            return $settings;
        } catch (PDOException $e) {
            return [
                'enabled' => '0',
                'phone_number' => '',
                'default_message' => 'Hi, I have a question about your products.',
                'position' => 'right',
                'show_on_mobile' => '1',
                'greeting_text' => 'Need help? Chat with us on WhatsApp!'
            ];
        }
    }
}

// ============================================
// ACTIVITY LOG FUNCTIONS
// ============================================

if (!function_exists('log_activity')) {
    function log_activity($action, $description = '', $user_id = null) {
        global $pdo;
        if (!isset($pdo)) return false;
        
        try {
            $stmt = $pdo->prepare("INSERT INTO activity_log 
                (user_id, user_type, action, description, ip_address, user_agent, created_at) 
                VALUES (?, ?, ?, ?, ?, ?, NOW())");
            
            return $stmt->execute([
                $user_id ?? get_current_user_id(),
                is_user_logged_in() ? 'customer' : 'guest',
                $action,
                $description,
                $_SERVER['REMOTE_ADDR'] ?? null,
                $_SERVER['HTTP_USER_AGENT'] ?? null
            ]);
        } catch (PDOException $e) {
            return false;
        }
    }
}

if (!function_exists('log_admin_activity')) {
    function log_admin_activity($pdo, $admin_id, $action, $description = '') {
        if (!isset($pdo)) return false;
        
        try {
            $stmt = $pdo->prepare("INSERT INTO activity_log 
                (user_id, user_type, action, description, ip_address, user_agent, created_at) 
                VALUES (?, 'admin', ?, ?, ?, ?, NOW())");
            
            return $stmt->execute([
                $admin_id,
                $action,
                $description,
                $_SERVER['REMOTE_ADDR'] ?? null,
                $_SERVER['HTTP_USER_AGENT'] ?? null
            ]);
        } catch (PDOException $e) {
            return false;
        }
    }
}

// ============================================
// DASHBOARD STATS FUNCTIONS
// ============================================

if (!function_exists('get_dashboard_stats')) {
    function get_dashboard_stats() {
        global $pdo;
        if (!isset($pdo)) return [];
        
        try {
            $stats = [];
            
            // Total products
            $stmt = $pdo->query("SELECT COUNT(*) FROM products");
            $stats['total_products'] = $stmt->fetchColumn();
            
            // Total orders
            $stmt = $pdo->query("SELECT COUNT(*) FROM orders");
            $stats['total_orders'] = $stmt->fetchColumn();
            
            // Total customers
            $stmt = $pdo->query("SELECT COUNT(*) FROM users WHERE role = 'user'");
            $stats['total_customers'] = $stmt->fetchColumn();
            
            // Total revenue
            $stmt = $pdo->query("SELECT COALESCE(SUM(total), 0) FROM orders WHERE payment_status = 'completed'");
            $stats['total_revenue'] = $stmt->fetchColumn();
            
            // Today's revenue
            $stmt = $pdo->query("SELECT COALESCE(SUM(total), 0) FROM orders WHERE payment_status = 'completed' AND DATE(created_at) = CURDATE()");
            $stats['today_revenue'] = $stmt->fetchColumn();
            
            // This month's revenue
            $stmt = $pdo->query("SELECT COALESCE(SUM(total), 0) FROM orders WHERE payment_status = 'completed' AND MONTH(created_at) = MONTH(CURDATE()) AND YEAR(created_at) = YEAR(CURDATE())");
            $stats['month_revenue'] = $stmt->fetchColumn();
            
            // Pending orders
            $stmt = $pdo->query("SELECT COUNT(*) FROM orders WHERE payment_status = 'pending'");
            $stats['pending_orders'] = $stmt->fetchColumn();
            
            // Pending negotiations
            $stmt = $pdo->query("SELECT COUNT(*) FROM negotiations WHERE status = 'pending'");
            $stats['pending_negotiations'] = $stmt->fetchColumn();
            
            return $stats;
        } catch (PDOException $e) {
            return [];
        }
    }
}

if (!function_exists('get_sales_chart_data')) {
    function get_sales_chart_data($days = 30) {
        global $pdo;
        if (!isset($pdo)) return [];
        
        try {
            $stmt = $pdo->prepare("SELECT 
                DATE(created_at) as date,
                COUNT(*) as orders,
                COALESCE(SUM(total), 0) as revenue
                FROM orders 
                WHERE payment_status = 'completed' 
                AND created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
                GROUP BY DATE(created_at)
                ORDER BY date ASC");
            $stmt->execute([$days]);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            return [];
        }
    }
}

// ============================================
// PAGE/CMS FUNCTIONS
// ============================================

if (!function_exists('get_page_by_slug')) {
    function get_page_by_slug($slug) {
        global $pdo;
        if (!isset($pdo)) return null;
        
        try {
            $stmt = $pdo->prepare("SELECT * FROM pages WHERE slug = ? AND status = 'published' AND is_active = 1");
            $stmt->execute([$slug]);
            return $stmt->fetch();
        } catch (PDOException $e) {
            return null;
        }
    }
}

if (!function_exists('get_all_pages')) {
    function get_all_pages($status = null) {
        global $pdo;
        if (!isset($pdo)) return [];
        
        try {
            $sql = "SELECT * FROM pages WHERE is_active = 1";
            if ($status) {
                $sql .= " AND status = ?";
            }
            $sql .= " ORDER BY created_at DESC";
            
            $stmt = $pdo->prepare($sql);
            $stmt->execute($status ? [$status] : []);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            return [];
        }
    }
}

// ============================================
// FAQ FUNCTIONS
// ============================================

if (!function_exists('get_faqs')) {
    function get_faqs($category = null, $active_only = true) {
        global $pdo;
        if (!isset($pdo)) return [];
        
        try {
            $sql = "SELECT * FROM faqs WHERE 1=1";
            $params = [];
            
            if ($active_only) {
                $sql .= " AND is_active = 1";
            }
            
            if ($category) {
                $sql .= " AND category = ?";
                $params[] = $category;
            }
            
            $sql .= " ORDER BY sort_order ASC, created_at DESC";
            
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            return [];
        }
    }
}

// ============================================
// BLOG FUNCTIONS
// ============================================

if (!function_exists('get_blog_posts')) {
    function get_blog_posts($limit = null, $status = 'published') {
        global $pdo;
        if (!isset($pdo)) return [];
        
        try {
            $sql = "SELECT bp.*, u.name as author_name 
                   FROM blog_posts bp 
                   LEFT JOIN users u ON bp.author_id = u.id 
                   WHERE bp.status = ? 
                   ORDER BY bp.published_at DESC";
            
            if ($limit) {
                $sql .= " LIMIT " . (int)$limit;
            }
            
            $stmt = $pdo->prepare($sql);
            $stmt->execute([$status]);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            return [];
        }
    }
}

if (!function_exists('get_blog_post_by_slug')) {
    function get_blog_post_by_slug($slug) {
        global $pdo;
        if (!isset($pdo)) return null;
        
        try {
            $stmt = $pdo->prepare("SELECT bp.*, u.name as author_name 
                                  FROM blog_posts bp 
                                  LEFT JOIN users u ON bp.author_id = u.id 
                                  WHERE bp.slug = ? AND bp.status = 'published'");
            $stmt->execute([$slug]);
            return $stmt->fetch();
        } catch (PDOException $e) {
            return null;
        }
    }
}

// ============================================
// NEWSLETTER FUNCTIONS
// ============================================

if (!function_exists('subscribe_newsletter')) {
    function subscribe_newsletter($email, $name = null, $source = 'website') {
        global $pdo;
        if (!isset($pdo)) return false;
        
        try {
            $stmt = $pdo->prepare("INSERT INTO subscribers (email, name, source, created_at) VALUES (?, ?, ?, NOW()) ON DUPLICATE KEY UPDATE is_active = 1");
            return $stmt->execute([$email, $name, $source]);
        } catch (PDOException $e) {
            return false;
        }
    }
}

if (!function_exists('unsubscribe_newsletter')) {
    function unsubscribe_newsletter($email) {
        global $pdo;
        if (!isset($pdo)) return false;
        
        try {
            $stmt = $pdo->prepare("UPDATE subscribers SET is_active = 0 WHERE email = ?");
            return $stmt->execute([$email]);
        } catch (PDOException $e) {
            return false;
        }
    }
}

// ============================================
// FILE UPLOAD FUNCTIONS
// ============================================

if (!function_exists('upload_file')) {
    function upload_file($file, $directory = 'uploads/', $allowed_types = ['jpg', 'jpeg', 'png', 'gif', 'pdf', 'zip']) {
        if (!isset($file) || $file['error'] !== UPLOAD_ERR_OK) {
            return ['success' => false, 'error' => 'Upload failed'];
        }
        
        $file_name = $file['name'];
        $file_tmp = $file['tmp_name'];
        $file_size = $file['size'];
        $file_ext = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));
        
        // Validate extension
        if (!in_array($file_ext, $allowed_types)) {
            return ['success' => false, 'error' => 'Invalid file type'];
        }
        
        // Generate unique filename
        $new_name = uniqid() . '_' . time() . '.' . $file_ext;
        $upload_path = $directory . $new_name;
        
        // Create directory if not exists
        if (!is_dir($directory)) {
            mkdir($directory, 0755, true);
        }
        
        if (move_uploaded_file($file_tmp, $upload_path)) {
            return ['success' => true, 'path' => $upload_path, 'name' => $new_name];
        } else {
            return ['success' => false, 'error' => 'Failed to move file'];
        }
    }
}

if (!function_exists('upload_image')) {
    function upload_image($file, $directory = 'uploads/images/', $max_width = 1920, $max_height = 1080) {
        return upload_file($file, $directory, ['jpg', 'jpeg', 'png', 'gif', 'webp']);
    }
}
