                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
<?php
// VELORA - Core Functions
require_once __DIR__ . '/db.php';

// Get Featured Products
function get_featured_products($limit = 8) {
    global $db;
    $stmt = $db->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.featured = 1 AND p.status = 1 
                          ORDER BY p.created_at DESC LIMIT ?");
    $stmt->execute([$limit]);
    return $stmt->fetchAll();
}

// Get Best Selling Products
function get_best_selling_products($limit = 8) {
    global $db;
    $stmt = $db->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.best_seller = 1 AND p.status = 1 
                          ORDER BY p.created_at DESC LIMIT ?");
    $stmt->execute([$limit]);
    return $stmt->fetchAll();
}

// Get New Arrivals
function get_new_arrivals($limit = 8) {
    global $db;
    $stmt = $db->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.new_arrival = 1 AND p.status = 1 
                          ORDER BY p.created_at DESC LIMIT ?");
    $stmt->execute([$limit]);
    return $stmt->fetchAll();
}

// Get Products by Category
function get_products_by_category($category_id, $limit = 12, $offset = 0) {
    global $db;
    $stmt = $db->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.status = 1 
                          ORDER BY p.created_at DESC LIMIT ? OFFSET ?");
    $stmt->execute([$category_id, $limit, $offset]);
    return $stmt->fetchAll();
}

// Get Single Product
function get_product($slug) {
    global $db;
    $stmt = $db->prepare("SELECT p.*, c.name as category_name, c.slug as category_slug,
                          sc.name as subcategory_name, b.name as brand_name, b.slug as brand_slug
                          FROM products p 
                          LEFT JOIN categories c ON p.category_id = c.id 
                          LEFT JOIN subcategories sc ON p.subcategory_id = sc.id
                          LEFT JOIN brands b ON p.brand_id = b.id
                          WHERE p.slug = ? AND p.status = 1");
    $stmt->execute([$slug]);
    return $stmt->fetch();
}

// Get Product Images
function get_product_images($product_id) {
    global $db;
    $stmt = $db->prepare("SELECT * FROM product_images WHERE product_id = ? ORDER BY sort_order ASC");
    $stmt->execute([$product_id]);
    return $stmt->fetchAll();
}

// Get Product Variations
function get_product_variations($product_id) {
    global $db;
    $stmt = $db->prepare("SELECT * FROM product_variations WHERE product_id = ?");
    $stmt->execute([$product_id]);
    return $stmt->fetchAll();
}

// Get Product Reviews
function get_product_reviews($product_id, $limit = 10) {
    global $db;
    $stmt = $db->prepare("SELECT r.*, CONCAT(u.first_name, ' ', u.last_name) as user_name 
                          FROM reviews r 
                          LEFT JOIN users u ON r.user_id = u.id 
                          WHERE r.product_id = ? AND r.status = 1 
                          ORDER BY r.created_at DESC LIMIT ?");
    $stmt->execute([$product_id, $limit]);
    return $stmt->fetchAll();
}

// Get Average Rating
function get_average_rating($product_id) {
    global $db;
    $stmt = $db->prepare("SELECT AVG(rating) as avg_rating, COUNT(*) as total_reviews 
                          FROM reviews WHERE product_id = ? AND status = 1");
    $stmt->execute([$product_id]);
    return $stmt->fetch();
}

// Get Related Products
function get_related_products($product_id, $category_id, $limit = 4) {
    global $db;
    $stmt = $db->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 = 1 
                          ORDER BY RAND() LIMIT ?");
    $stmt->execute([$category_id, $product_id, $limit]);
    return $stmt->fetchAll();
}

// Get Active Banners
function get_active_banners() {
    global $db;
    $stmt = $db->query("SELECT * FROM banners WHERE status = 1 ORDER BY sort_order ASC");
    return $stmt->fetchAll();
}

// Get Featured Brands
function get_featured_brands($limit = 6) {
    global $db;
    $stmt = $db->prepare("SELECT * FROM brands WHERE featured = 1 AND status = 1 ORDER BY RAND() LIMIT ?");
    $stmt->execute([$limit]);
    return $stmt->fetchAll();
}

// Get Categories (cached per request to avoid duplicate queries in navbar/shop/footer)
function get_categories() {
    global $db;
    static $cached = null;
    if ($cached !== null) return $cached;
    $stmt = $db->query("SELECT * FROM categories WHERE status = 1 ORDER BY name ASC");
    $cached = $stmt->fetchAll();
    return $cached;
}

// Get Subcategories
function get_subcategories($category_id = null) {
    global $db;
    if ($category_id) {
        $stmt = $db->prepare("SELECT * FROM subcategories WHERE category_id = ? AND status = 1");
        $stmt->execute([$category_id]);
    } else {
        $stmt = $db->query("SELECT * FROM subcategories WHERE status = 1");
    }
    return $stmt->fetchAll();
}

// Get Cart Items
function get_cart_items() {
    global $db;
    $items = [];
    if (isset($_SESSION['cart']) && !empty($_SESSION['cart'])) {
        $ids = array_keys($_SESSION['cart']);
        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        $stmt = $db->prepare("SELECT * FROM products WHERE id IN ($placeholders) AND status = 1");
        $stmt->execute($ids);
        $products = $stmt->fetchAll();
        foreach ($products as $product) {
            $cart_item = $_SESSION['cart'][$product['id']];
            $product['quantity'] = $cart_item['quantity'];
            $product['variation_details'] = $cart_item['variation_details'] ?? '';
            $items[] = $product;
        }
    }
    return $items;
}

// Add to Cart
function add_to_cart($product_id, $quantity = 1, $variation_details = '') {
    if (!isset($_SESSION['cart'])) {
        $_SESSION['cart'] = [];
    }
    if (isset($_SESSION['cart'][$product_id])) {
        $_SESSION['cart'][$product_id]['quantity'] += $quantity;
    } else {
        $_SESSION['cart'][$product_id] = [
            'quantity' => $quantity,
            'variation_details' => $variation_details
        ];
    }
    return true;
}

// Update Cart
function update_cart($product_id, $quantity) {
    if (isset($_SESSION['cart'][$product_id])) {
        if ($quantity <= 0) {
            unset($_SESSION['cart'][$product_id]);
        } else {
            $_SESSION['cart'][$product_id]['quantity'] = $quantity;
        }
        return true;
    }
    return false;
}

// Remove from Cart
function remove_from_cart($product_id) {
    if (isset($_SESSION['cart'][$product_id])) {
        unset($_SESSION['cart'][$product_id]);
        return true;
    }
    return false;
}

// Add to Wishlist
function add_to_wishlist($user_id, $product_id) {
    global $db;
    try {
        $stmt = $db->prepare("INSERT IGNORE INTO wishlists (user_id, product_id) VALUES (?, ?)");
        return $stmt->execute([$user_id, $product_id]);
    } catch (PDOException $e) {
        return false;
    }
}

// Remove from Wishlist
function remove_from_wishlist($user_id, $product_id) {
    global $db;
    $stmt = $db->prepare("DELETE FROM wishlists WHERE user_id = ? AND product_id = ?");
    return $stmt->execute([$user_id, $product_id]);
}

// Get User Wishlist
function get_user_wishlist($user_id) {
    global $db;
    $stmt = $db->prepare("SELECT p.*, w.created_at as added_at 
                          FROM wishlists w 
                          JOIN products p ON w.product_id = p.id 
                          WHERE w.user_id = ? AND p.status = 1 
                          ORDER BY w.created_at DESC");
    $stmt->execute([$user_id]);
    return $stmt->fetchAll();
}

// Check if in Wishlist
function in_wishlist($user_id, $product_id) {
    global $db;
    $stmt = $db->prepare("SELECT id FROM wishlists WHERE user_id = ? AND product_id = ?");
    $stmt->execute([$user_id, $product_id]);
    return $stmt->fetch() ? true : false;
}

// Apply Coupon
function validate_coupon($code, $cart_total) {
    global $db;
    $stmt = $db->prepare("SELECT * FROM coupons WHERE code = ? AND status = 1 
                          AND (start_date IS NULL OR start_date <= CURDATE()) 
                          AND (end_date IS NULL OR end_date >= CURDATE())");
    $stmt->execute([$code]);
    $coupon = $stmt->fetch();
    
    if (!$coupon) return false;
    if ($coupon['usage_limit'] > 0 && $coupon['used_count'] >= $coupon['usage_limit']) return false;
    if ($cart_total < $coupon['min_cart_amount']) return false;
    
    return $coupon;
}

// Calculate Discount
function calculate_discount($coupon, $cart_total) {
    if ($coupon['type'] == 'percentage') {
        return min($cart_total * $coupon['value'] / 100, $cart_total);
    } else {
        return min($coupon['value'], $cart_total);
    }
}

// Get User Orders
function get_user_orders($user_id, $limit = 10) {
    global $db;
    $stmt = $db->prepare("SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT ?");
    $stmt->execute([$user_id, $limit]);
    return $stmt->fetchAll();
}

// Get Order Details
function get_order_details($order_number) {
    global $db;
    $stmt = $db->prepare("SELECT * FROM orders WHERE order_number = ?");
    $stmt->execute([$order_number]);
    return $stmt->fetch();
}

// Get Order Items
function get_order_items($order_id) {
    global $db;
    $stmt = $db->prepare("SELECT * FROM order_items WHERE order_id = ?");
    $stmt->execute([$order_id]);
    return $stmt->fetchAll();
}

// Get Dashboard Stats
function get_dashboard_stats() {
    global $db;
    $stats = [];
    $stats['total_products'] = $db->query("SELECT COUNT(*) FROM products WHERE status = 1")->fetchColumn();
    $stats['total_orders'] = $db->query("SELECT COUNT(*) FROM orders")->fetchColumn();
    $stats['total_users'] = $db->query("SELECT COUNT(*) FROM users WHERE status = 1")->fetchColumn();
    $stats['total_revenue'] = $db->query("SELECT COALESCE(SUM(total), 0) FROM orders WHERE payment_status = 'completed'")->fetchColumn();
    $stats['pending_orders'] = $db->query("SELECT COUNT(*) FROM orders WHERE order_status = 'pending'")->fetchColumn();
    $stats['total_categories'] = $db->query("SELECT COUNT(*) FROM categories WHERE status = 1")->fetchColumn();
    $stats['total_reviews'] = $db->query("SELECT COUNT(*) FROM reviews WHERE status = 1")->fetchColumn();
    $stats['total_brands'] = $db->query("SELECT COUNT(*) FROM brands WHERE status = 1")->fetchColumn();
    return $stats;
}

// Get Recent Orders
function get_recent_orders($limit = 5) {
    global $db;
    $stmt = $db->query("SELECT * FROM orders ORDER BY created_at DESC LIMIT $limit");
    return $stmt->fetchAll();
}

// Get Monthly Sales
function get_monthly_sales($year = null) {
    global $db;
    $year = $year ?: date('Y');
    $stmt = $db->query("SELECT MONTH(created_at) as month, COUNT(*) as order_count, COALESCE(SUM(total), 0) as revenue 
                        FROM orders WHERE YEAR(created_at) = $year AND payment_status = 'completed' 
                        GROUP BY MONTH(created_at) ORDER BY month");
    return $stmt->fetchAll();
}