<?php
// Function to get a collection by slug
function get_collection_by_slug($pdo, $slug) {
    $stmt = $pdo->prepare("SELECT * FROM collections WHERE slug = ? LIMIT 1");
    $stmt->execute([$slug]);
    return $stmt->fetch();
}

// Function to get product by slug
function get_product_by_slug($pdo, $slug) {
    $stmt = $pdo->prepare("SELECT * FROM products WHERE slug = ? LIMIT 1");
    $stmt->execute([$slug]);
    return $stmt->fetch();
}

// Function to get products in a collection
function get_products_by_collection($pdo, $collection_id, $limit = 12) {
    // This is a simplified query. In a real scenario, you'd likely use a pivot table 'product_collections'
    // Assuming a simple schema for now or joining
    $sql = "
        SELECT p.* 
        FROM products p
        JOIN product_collections pc ON p.id = pc.product_id
        WHERE pc.collection_id = ?
        LIMIT $limit
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$collection_id]);
    return $stmt->fetchAll();
}

// Function to get distinct collections
function get_collections($pdo, $limit = 5) {
    $stmt = $pdo->prepare("SELECT * FROM collections ORDER BY id DESC LIMIT :limit");
    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll();
}

// Function to get products by collection slug
function get_products_by_collection_slug($pdo, $slug, $limit = 10) {
    $stmt = $pdo->prepare("
        SELECT p.*,
        (SELECT image_path FROM product_images pi WHERE pi.product_id = p.id ORDER BY is_featured DESC, sort_order ASC LIMIT 1) as image
        FROM products p
        JOIN product_collections pc ON p.id = pc.product_id
        JOIN collections c ON pc.collection_id = c.id
        WHERE c.slug = ?
        LIMIT $limit
    ");
    $stmt->bindValue(1, $slug, PDO::PARAM_STR);
    // Note: LIMIT in prepared statements can be tricky with string concatenation vs bindValue. 
    // Ideally bind :limit as integer, but mixing ? and named placeholders matches can be an issue.
    // For simplicity with this driver setup, let's strictly bind.
    
    // Re-writing to use named parameters for clarity and safety with LIMIT
    $query = "
        SELECT p.*,
        (SELECT image_path FROM product_images pi WHERE pi.product_id = p.id ORDER BY is_featured DESC, sort_order ASC LIMIT 1) as image
        FROM products p
        JOIN product_collections pc ON p.id = pc.product_id
        JOIN collections c ON pc.collection_id = c.id
        WHERE c.slug = :slug
        LIMIT :limit
    ";
    $stmt = $pdo->prepare($query);
    $stmt->bindValue(':slug', $slug, PDO::PARAM_STR);
    $stmt->bindValue(':limit', (int)$limit, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll();
}

// Function to get featured collections
function get_featured_collections($pdo, $limit = 5) {
    $stmt = $pdo->prepare("SELECT * FROM collections WHERE is_featured = 1 AND is_active = 1 ORDER BY sort_order ASC LIMIT :limit");
    $stmt->bindValue(':limit', (int)$limit, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll();
}

// Function to get latest blogs
function get_latest_blogs($pdo, $limit = 3) {
    $stmt = $pdo->prepare("SELECT * FROM blogs WHERE visibility = 'visible' ORDER BY published_at DESC LIMIT :limit");
    $stmt->bindValue(':limit', (int)$limit, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll();
}
// Function to get full collection tree
function get_collection_tree($pdo) {
    try {
        $stmt = $pdo->query("SELECT id, name, slug, parent_id, image, url_path, description FROM collections WHERE is_active = 1 ORDER BY sort_order ASC, name ASC");
        $collections = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (PDOException $e) {
        return [];
    }

    $tree = [];
    $refs = [];

    // Initialize references
    foreach ($collections as $index => $c) {
        $collections[$index]['children'] = [];
        $refs[$c['id']] = &$collections[$index];
    }

    // Build Tree
    foreach ($collections as $index => $c) {
        $parentId = $c['parent_id'];
        if ($parentId && isset($refs[$parentId])) {
            $refs[$parentId]['children'][] = &$collections[$index];
        } else {
            $tree[] = &$collections[$index];
        }
    }

    return $tree;
}

// Function to find a specific node in the tree by slug
function find_collection_by_slug($tree, $slug) {
    foreach ($tree as $node) {
        if ($node['slug'] === $slug) {
            return $node;
        }
        if (!empty($node['children'])) {
            $found = find_collection_by_slug($node['children'], $slug);
            if ($found) return $found;
        }
    }
    return null;
}
?>
