Skip to content
TheChatArchiveThe Chat Archive
o1-mini-2024-09-12· OpenAIAi2 WildChat-4.8M (AI2)

create a new function that returns only two most selling products... and returns only…

  1. User

    create a new function that returns only two most selling products... and returns only their title, description and minimum price... (since each size can have a price) the minimum price shouldn't be 0... since 0 means null... and returns as well its id... The two most selling products they both should be in stock not out of stock... so if it is out of stock then look up to the next most selling product and see if it is in the stock... import { Request, Response } from 'express'; import {query} from "../config/database"; import {brandsCache, productCache} from "../cache"; import { Brand, BrandsCacheData, CacheData, Color, CreateProductInput, Image, ImageInput, Product, Size, UpdateProductInput } from "product"; import {addProductToCache, deleteProductFromCache, updateProductInCache} from "../cacheHelper";

    const fetchCategoryDetails = async (subcategoryIds: number[]) => { const categoriesResult = await query( SELECT sc.id AS subcategory_id, sc.name AS subcategory_name, c.id AS main_category_id, c.name AS main_category_name FROM subcategories sc JOIN categories c ON sc.category_id = c.id WHERE sc.id = ANY($1::int[]), [subcategoryIds] );

    return categoriesResult.rows.map((row: any) => ({
        id: row.subcategory_id,
        name: row.subcategory_name,
        main_category_id: row.main_category_id,
        main_category_name: row.main_category_name
    }));
    

    }; export const loadAndCacheAllProducts = async (): Promise => { const q = SELECT p.id, p.name, p.description, p.brand_id, p.has_color, p.has_size, p.product_type, p.price, p.qty, p.sold, p.created_at, p.updated_at, sc.id AS subcategory_id, sc.name AS subcategory_name, c.id AS main_category_id, c.name AS main_category_name, s.id AS size_id, s.name AS size_name, s.price AS size_price, s.qty AS size_qty, s.sold AS size_sold, cl.id AS color_id, cl.name AS color_name, cl.price AS color_price, cl.qty AS color_qty, cl.sold AS color_sold, i.id AS image_id, i.url AS image_url, i.color_id AS image_color_id, i.size_id AS image_size_id FROM products p LEFT JOIN product_categories pc ON p.id = pc.product_id LEFT JOIN subcategories sc ON pc.category_id = sc.id LEFT JOIN categories c ON sc.category_id = c.id LEFT JOIN colors cl ON p.id = cl.product_id LEFT JOIN sizes s ON p.id = s.product_id LEFT JOIN images i ON (p.id = i.product_id AND (cl.id = i.color_id OR i.color_id IS NULL) AND (s.id = i.size_id OR i.size_id IS NULL)) ;

    try {
        const result = await query(q, []);
        const productsMap: Record<number, Product> = {};
    
        // Temporary map to hold sizes that need to be assigned to colors
        const sizesMap: Record<number, Size> = {};
    
        // First pass: Initialize products and collect sizes
        result.rows.forEach((row: any) => {
            const productId = Number(row.id);
    
            // Initialize the product in the map if it doesn't exist
            if (!productsMap[productId]) {
                productsMap[productId] = {
                    id: productId,
                    name: row.name,
                    description: row.description,
                    brand_id: Number(row.brand_id),
                    has_color: Boolean(row.has_color),
                    has_size: Boolean(row.has_size),
                    product_type: row.product_type,
                    price: Number(row.price),
                    qty: Number(row.qty),
                    sold: Number(row.sold),
                    created_at: new Date(row.created_at).toISOString(),
                    updated_at: new Date(row.updated_at).toISOString(),
                    categories: [],
                    colors: [],
                    sizes: [], // Will be populated only if has_color is false
                    images: []
                };
            }
    
            const product = productsMap[productId];
    
            // Add category if present and not already added
            if (
                row.subcategory_id &&
                row.subcategory_name &&
                row.main_category_id &&
                row.main_category_name
            ) {
                const categoryId = Number(row.subcategory_id);
                const mainCategoryId = Number(row.main_category_id);
                const existingCategory = product.categories.find(cat => cat.id === categoryId);
                if (!existingCategory) {
                    product.categories.push({
                        id: categoryId,
                        name: row.subcategory_name,
                        main_category_id: mainCategoryId,
                        main_category_name: row.main_category_name,
                    });
                }
            }
    
            // Add color if present and not already added
            if (row.color_id && row.color_name && row.has_size !== undefined) {
                const colorId = Number(row.color_id);
                let color = product.colors.find(color => color.id === colorId);
    
                if (!color) {
                    color = {
                        id: colorId,
                        name: row.color_name,
                        price: Number(row.color_price),
                        qty: Number(row.color_qty),
                        sold: Number(row.color_sold),
                        has_size: Boolean(row.has_size),
                        sizes: [], // Will be populated if has_size is true
                        images: []
                    };
                    product.colors.push(color);
                }
            }
    
            // Collect sizes to assign later based on color association
            if (row.size_id && row.size_name) {
                const sizeId = Number(row.size_id);
                const colorId = row.color_id ? Number(row.color_id) : null;
    
                // If the size is associated with a color, store it for later assignment
                if (colorId && product.has_color) {
                    sizesMap[sizeId] = {
                        id: sizeId,
                        name: row.size_name,
                        price: Number(row.size_price),
                        qty: Number(row.size_qty),
                        sold: Number(row.size_sold),
                        color_id: colorId,
                        images: []
                    };
                } else if (!product.has_color && product.has_size) {
                    // If the product has sizes but no colors, assign size directly
                    if (!product.sizes.find(s => s.id === sizeId)) {
                        product.sizes.push({
                            id: sizeId,
                            name: row.size_name,
                            price: Number(row.size_price),
                            qty: Number(row.size_qty),
                            sold: Number(row.size_sold),
                            color_id: null,
                            images: []
                        });
                    }
                }
            }
    
            // Collect images to assign
            if (row.image_id && row.image_url) {
                const imageId = Number(row.image_id);
                // Avoid duplicate images
                if (!product.images.find(img => img.id === imageId)) {
                    product.images.push({
                        id: imageId,
                        url: row.image_url,
                        color_id: row.image_color_id ? Number(row.image_color_id) : null,
                        size_id: row.image_size_id ? Number(row.image_size_id) : null
                    });
                }
    
                // Associate image with color if applicable
                if (row.image_color_id) {
                    const colorId = Number(row.image_color_id);
                    const color = product.colors.find(c => c.id === colorId);
                    if (color && !color.images.includes(row.image_url)) {
                        color.images.push(row.image_url);
                    }
                }
    
                // Associate image with size if applicable and size is within a color
                if (row.image_size_id) {
                    const sizeId = Number(row.image_size_id);
                    const size = sizesMap[sizeId];
                    if (size) {
                        if (!size.images.includes(row.image_url)) {
                            size.images.push(row.image_url);
                        }
                    } else {
                        // If the product does not have colors, associate with product's sizes
                        const standaloneSize = product.sizes.find(s => s.id === Number(row.image_size_id));
                        if (standaloneSize && !standaloneSize.images.includes(row.image_url)) {
                            standaloneSize.images.push(row.image_url);
                        }
                    }
                }
            }
        });
    
        // Second pass: Assign sizes to their respective colors
        Object.values(productsMap).forEach(product => {
            if (product.has_color) {
                // Initialize all product.sizes as empty
                product.sizes = [];
    
                // Iterate over collected sizes and assign to colors
                Object.values(sizesMap).forEach(size => {
                    if (size.color_id === null) return; // Skip sizes without color_id
    
                    const color = product.colors.find(c => c.id === size.color_id);
                    if (color) {
                        color.sizes.push({
                            id: size.id,
                            name: size.name,
                            price: size.price,
                            qty: size.qty,
                            sold: size.sold,
                            color_id: size.color_id,
                            images: size.images
                        });
                    }
                });
    
                // Update has_size for each color based on whether it has sizes
                product.colors.forEach(color => {
                    color.has_size = color.sizes.length > 0;
                });
            }
            // If has_color is false, sizes are already assigned directly to product.sizes
        });
    
        const allProducts = Object.values(productsMap);
    
        // Calculate min and max prices across all products
        let minPrice = Infinity;
        let maxPrice = -Infinity;
        allProducts.forEach(product => {
            // Product price
            if (product.price < minPrice) minPrice = product.price;
            if (product.price > maxPrice) maxPrice = product.price;
    
            // Prices from colors and their sizes
            product.colors.forEach(color => {
                if (color.price < minPrice) minPrice = color.price;
                if (color.price > maxPrice) maxPrice = color.price;
    
                color.sizes.forEach(size => {
                    if (size.price < minPrice) minPrice = size.price;
                    if (size.price > maxPrice) maxPrice = size.price;
                });
            });
    
            // Prices from standalone sizes
            product.sizes.forEach(size => {
                if (size.price < minPrice) minPrice = size.price;
                if (size.price > maxPrice) maxPrice = size.price;
            });
        });
    
        // Fallback in case no prices are present
        if (minPrice === Infinity) minPrice = 0;
        if (maxPrice === -Infinity) maxPrice = 0;
    
        // Cache the data with a consistent key
        const cacheData: CacheData = { allProducts, minPrice, maxPrice };
        productCache.set('allProducts', cacheData);
        console.log(`Cached ${allProducts.length} products with minPrice: ${minPrice} and maxPrice: ${maxPrice}.`);
    
        return cacheData;
    } catch (error) {
        console.error('Error loading products from DB:', error);
        throw error; // Propagate error to be handled by caller
    }
    

    };

    /**

    • Fetches all brands from the database and caches them.

    • @returns {Promise} A promise that resolves to the cached brand data. */ const loadAndCacheAllBrands = async (): Promise => { const q = SELECT s.*, c.name as category_name FROM subcategories s JOIN categories c ON s.category_id = c.id AND c.is_brand ORDER BY c.name, s.name ;

      try { const result = await query(q, []); const allBrands: Brand[] = result.rows.map((row: any) => ({ id: Number(row.id), name: row.name, category_id: Number(row.category_id), category_name: row.category_name, }));

       // Cache the brands
       const brandsCacheData: BrandsCacheData = { allBrands };
       brandsCache.set('allBrands', brandsCacheData);
       console.log(`Cached ${allBrands.length} brands successfully.`);
      
       return brandsCacheData;
      

      } catch (error) { console.error('Error loading brands from DB:', error); throw error; // Propagate error to be handled by caller } };

    /**

    • Controller to handle fetching and responding with filtered products.

    • Caches all products and brands on first request and performs in-memory filtering thereafter.

    • @param {Request} req - Express request object containing query parameters.

    • @param {Response} res - Express response object to send JSON data. */ export const getProducts = async (req: Request, res: Response): Promise => { try { // Retrieve cached products let cacheData: CacheData | undefined = productCache.get('cacheData');

       if (!cacheData) {
           // Cache miss: Load from DB and cache
           cacheData = await loadAndCacheAllProducts();
           console.log('Loaded products from database and cached them.');
       } else {
           console.log('Loaded products from cache.');
       }
      
       const { allProducts, minPrice, maxPrice } = cacheData;
      
       // Log total products before any filtering
       console.log(`Total products before filtering: ${allProducts.length}`);
      
       // Extract and sanitize query parameters
       let {
           search = '',
           c,
           sc,
           b,
           minPriceA = '0',
           maxPriceA = '',
           availability = 'all',
           sortBy = '',
           page = '1',
           itemsPerPage = '15',
       } = req.query;
      
       // Parse numerical values
       const pageNum = parseInt(page as string, 10) || 1;
       const itemsPerPageNum = parseInt(itemsPerPage as string, 10) || 15;
       const minPriceANum = parseFloat(minPriceA as string) || 0;
       let maxPriceANum = maxPriceA ? parseFloat(maxPriceA as string) : Infinity;
      
       // If maxPriceA is less than minPriceA, reset to Infinity
       if (!isNaN(maxPriceANum) && maxPriceANum < minPriceANum) {
           console.warn('Provided maxPriceA is less than minPriceA. Resetting maxPriceANum to Infinity.');
           maxPriceANum = Infinity;
       } else if (isNaN(maxPriceANum)) {
           maxPriceANum = Infinity;
       }
      
       // Parse c, sc, b as numbers if present
       const cNum = c ? parseInt(c as string, 10) : undefined;
       const scNum = sc ? parseInt(sc as string, 10) : undefined;
       const bNum = b ? parseInt(b as string, 10) : undefined;
      
       // Initialize brand-related variables if 'b' is provided
       let allBrands: Brand[] = [];
       if (bNum !== undefined && !isNaN(bNum)) {
           // Retrieve cached brands
           let brandsCacheData: BrandsCacheData | undefined = brandsCache.get('allBrands');
      
           if (!brandsCacheData) {
               // Cache miss: Load from DB and cache
               brandsCacheData = await loadAndCacheAllBrands();
               console.log('Loaded brands from database and cached them.');
           } else {
               console.log('Loaded brands from cache.');
           }
           console.log(brandsCacheData)
           allBrands = brandsCacheData.allBrands;
      
           // Check if 'bNum' is a valid brand ID
           const isValidBrand = allBrands.some(brand => brand.category_id === bNum);
           if (!isValidBrand) {
               console.warn(`Brand ID ${bNum} is invalid.`);
               // Return zero products since 'b' is invalid
               res.json({
                   products: [],
                   total: 0,
                   page: pageNum,
                   itemsPerPage: itemsPerPageNum,
                   totalPages: 0,
                   hasMore: false,
                   minPrice,
                   maxPrice,
               });
               return;
           }
       }
      
       // Start with all products
       let filteredProducts = [...allProducts];
      
       // Apply Search Filter
       if (search && typeof search === 'string') {
           const searchLower = search.toLowerCase();
           const searchWords = searchLower.split(/\s+/).filter(Boolean);
           filteredProducts = filteredProducts.filter(product =>
               searchWords.every(word => product.name.toLowerCase().includes(word))
           );
       }
      
       // Apply Category Filter (c)
       if (cNum !== undefined && !isNaN(cNum)) {
           filteredProducts = filteredProducts.filter(product =>
               product.categories.some(cat => cat.main_category_id === cNum)
           );
       }
      
       // Apply Subcategory Filter (sc)
       if (scNum !== undefined && !isNaN(scNum)) {
           filteredProducts = filteredProducts.filter(product =>
               product.brand_id === scNum ||
               product.categories.some(cat => cat.id === scNum)
           );
       }
      
       // Apply Brand Filter (b)
       if (bNum !== undefined && !isNaN(bNum)) {
           console.log(`Applying Brand Filter: brand_id === ${bNum}`);
           const preFilterCount = filteredProducts.length;
           allBrands = allBrands.filter(brand => brand.category_id === bNum)
           filteredProducts = filteredProducts.filter(product =>
               allBrands.some(brand => brand.id === product.brand_id)
           );
           console.log(`Filtered from ${preFilterCount} to ${filteredProducts.length} products after applying brand filter.`);
       }
      
       // Apply Price Range Filter
       if (minPriceANum > 0 || maxPriceANum < Infinity) {
           filteredProducts = filteredProducts.filter(product => {
               const productPrices = product.sizes.map(size => size.price);
               if (productPrices.length === 0) return false;
               const productMinPrice = Math.min(...productPrices);
               const productMaxPrice = Math.max(...productPrices);
               return productMinPrice >= minPriceANum && productMaxPrice <= maxPriceANum;
           });
       }
      
       // Apply Availability Filter
       if (availability === 'in_stock') {
           filteredProducts = filteredProducts.filter(product =>
               product.sizes.some(size => size.qty > 0)
           );
       } else if (availability === 'out_of_stock') {
           filteredProducts = filteredProducts.filter(product =>
               product.sizes.every(size => size.qty === 0)
           );
       }
      
       // Log total after filtering
       console.log(`Total products after filtering: ${filteredProducts.length}`);
      
       // Apply Sorting
       if (sortBy && typeof sortBy === 'string') {
           switch (sortBy) {
               case 'Price: Low to High':
                   filteredProducts.sort((a, b) => {
                       const aMin = Math.min(...a.sizes.map(s => s.price));
                       const bMin = Math.min(...b.sizes.map(s => s.price));
                       return aMin - bMin;
                   });
                   break;
               case 'Price: High to Low':
                   filteredProducts.sort((a, b) => {
                       const aMax = Math.max(...a.sizes.map(s => s.price));
                       const bMax = Math.max(...b.sizes.map(s => s.price));
                       return bMax - aMax;
                   });
                   break;
               case 'Name: A to Z':
                   filteredProducts.sort((a, b) => a.name.localeCompare(b.name));
                   break;
               case 'Name: Z to A':
                   filteredProducts.sort((a, b) => b.name.localeCompare(a.name));
                   break;
               case 'Best Selling':
                   filteredProducts.sort((a, b) => b.sold - a.sold);
                   break;
               case 'Newest Arrivals':
                   filteredProducts.sort((a, b) => new Date(b.created_at).getTime() - new Date(a.created_at).getTime());
                   break;
               default:
                   // If sortBy parameter doesn't match any case, do not sort
                   break;
           }
       }
      
       // Apply Pagination
       const totalFilteredProducts = filteredProducts.length;
       const totalPages = Math.ceil(totalFilteredProducts / itemsPerPageNum);
       const startIdx = (pageNum - 1) * itemsPerPageNum;
       const endIdx = startIdx + itemsPerPageNum;
       const paginatedProducts = filteredProducts.slice(startIdx, endIdx);
      
       // Construct Response
       const response = {
           products: paginatedProducts,
           total: totalFilteredProducts,
           page: pageNum,
           itemsPerPage: itemsPerPageNum,
           totalPages,
           hasMore: endIdx < totalFilteredProducts,
           minPrice, // Global minPrice across all products
           maxPrice, // Global maxPrice across all products
       };
      
       // Send Response
       res.json(response);
      

      } catch (error) { console.error('Error fetching products:', error); res.status(500).json({ error: 'Internal server error' }); } finally { // Optional: Any cleanup operations can be performed here // For example, monitoring memory usage or logging request completion } };

    export const getProductById = async (req: Request, res: Response) => { const { id } = req.params; try { const productQuery = SELECT p.id AS product_id, p.name AS product_name, p.description, p.brand_id, p.has_color, p.has_size, p.product_type, p.price AS product_price, p.qty AS product_qty, p.sold AS product_sold, p.created_at, p.updated_at, pc.category_id, sc.name AS category_name, cl.id AS color_id, cl.name AS color_name, cl.price AS color_price, cl.qty AS color_qty, cl.sold AS color_sold, s.id AS size_id, s.name AS size_name, s.price AS size_price, s.qty AS size_qty, s.sold AS size_sold, i.id AS image_id, i.url AS image_url FROM products p LEFT JOIN product_categories pc ON p.id = pc.product_id LEFT JOIN subcategories sc ON pc.category_id = sc.id LEFT JOIN colors cl ON p.id = cl.product_id LEFT JOIN sizes s ON (p.id = s.product_id AND (cl.id = s.color_id OR s.color_id IS NULL)) LEFT JOIN images i ON (p.id = i.product_id AND (cl.id = i.color_id OR i.color_id IS NULL) AND (s.id = i.size_id OR i.size_id IS NULL)) WHERE p.id = $1 ORDER BY pc.category_id, cl.id, s.id, i.id ;

        const result = await query(productQuery, [id]);
    
        if (result.rows.length === 0) {
            return res.status(404).json({ error: 'Product not found' });
        }
    
        // Process the result to group related data
        let product: {
            sold: any;
            has_size: any;
            images: any[];
            description: any;
            created_at: any;
            colors: any[];
            brand_id: any;
            has_color: any;
            product_type: any;
            updated_at: any;
            sizes: any[];
            price: any;
            qty: any;
            name: any;
            id: number;
            categories: any[]
        };
        product = {
            id: result.rows[0].product_id,
            name: result.rows[0].product_name,
            description: result.rows[0].description,
            brand_id: result.rows[0].brand_id,
            has_color: result.rows[0].has_color,
            has_size: result.rows[0].has_size,
            product_type: result.rows[0].product_type,
            price: result.rows[0].product_price,
            qty: result.rows[0].product_qty,
            sold: result.rows[0].product_sold,
            created_at: result.rows[0].created_at,
            updated_at: result.rows[0].updated_at,
            categories: [],
            colors: [],
            sizes: [],
            images: []
        };
    
        for (const row of result.rows) {
            if (row.category_id && !product.categories.some(c => c.id === row.category_id)) {
                product.categories.push({
                    id: row.category_id,
                    name: row.category_name
                });
            }
    
            // Add color if not already added
            if (row.color_id && !product.colors.some(c => c.id === row.color_id)) {
                product.colors.push({
                    id: row.color_id,
                    name: row.color_name,
                    price: row.color_price,
                    qty: row.color_qty,
                    sold: row.color_sold,
                    images: [],
                    sizes: []
                });
            }
    
            // Add size if not already added
            if (row.size_id && !product.sizes.some(s => s.id === row.size_id)) {
                const newSize = {
                    id: row.size_id,
                    name: row.size_name,
                    price: row.size_price,
                    qty: row.size_qty,
                    sold: row.size_sold,
                    color_id: row.color_id,
                    // @ts-ignore
                    images: []
                };
                product.sizes.push(newSize);
    
                // Add size to the corresponding color
                if (row.color_id) {
                    const color = product.colors.find(c => c.id === row.color_id);
                    if (color && !color.sizes.some((s: { id: any; }) => s.id === row.size_id)) {
                        color.sizes.push(newSize);
                    }
                }
            }
    
            if (row.image_id && !product.images.some(i => i.id === row.image_id)) {
                const newImage = {
                    id: row.image_id,
                    url: row.image_url,
                    color_id: row.color_id,
                    size_id: row.size_id
                };
                product.images.push(newImage);
    
                if (row.color_id) {
                    const color = product.colors.find(c => c.id === row.color_id);
                    if (color && !color.images.includes(row.image_url)) {
                        color.images.push(row.image_url);
                    }
                }
                if (row.size_id) {
                    const size = product.sizes.find(s => s.id === row.size_id);
                    if (size && !size.images.includes(row.image_url)) {
                        size.images.push(row.image_url);
                    }
                }
            }
        }
    
        res.json({ product });
    } catch (error) {
        console.error('Error fetching product by ID:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
    

    }; // Create a new product export const createProduct = async (req: Request, res: Response) => { try { console.log("Received product data:", req.body); // Log incoming data await query('BEGIN');

        const productData: CreateProductInput = req.body;
    
        // Adjust price if necessary
        let adjustedPrice = productData.price;
        if (productData.price < 1000) {
            adjustedPrice = parseFloat((productData.price * 1000).toFixed(2));
        }
    
        console.log("Inserting product with adjusted price:", adjustedPrice);
    
        // Insert product
        const productResult = await query(
            `INSERT INTO products (name, description, brand_id, has_color, has_size, product_type, price, qty, sold, created_at, updated_at)
             VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
             RETURNING id, created_at, updated_at`,
            [
                productData.name,
                productData.description,
                productData.brand_id,
                Boolean(productData.has_color),
                Boolean(productData.has_size),
                productData.product_type,
                adjustedPrice,
                productData.qty
            ]
        );
        const productId = productResult.rows[0].id;
        console.log(`Inserted product ID: ${productId}`);
    
        // Insert category associations
        const categoryInsertPromises = productData.category_ids.map((categoryId: number) => {
            console.log(`Associating category ID ${categoryId} with product ID ${productId}`);
            return query(
                `INSERT INTO product_categories (product_id, category_id) 
                 VALUES ($1, $2)`,
                [productId, categoryId]
            );
        });
        await Promise.all(categoryInsertPromises);
        console.log('Inserted all category associations.');
    
        // Insert images
        const imagesInsertPromises = productData.images
            .filter((image: ImageInput) => image.url) // Ensure URL exists
            .map((image: ImageInput) => {
                console.log(`Inserting product image: ${image.url}`);
                return query(
                    `INSERT INTO images (product_id, url, color_id, size_id)
       VALUES ($1, $2, $3, $4)
       RETURNING id, color_id, size_id, url`,
                    [productId, image.url, image.color_id || null, image.size_id || null]
                );
            });
        const insertedImagesResults = await Promise.all(imagesInsertPromises);
        const insertedImages = insertedImagesResults.map((imgResult: any) => ({
            id: imgResult.rows[0].id,
            url: imgResult.rows[0].url,
            color_id: imgResult.rows[0].color_id,
            size_id: imgResult.rows[0].size_id
        }));
        console.log(`Inserted ${insertedImages.length} product images.`);
    
        // Handle colors and sizes based on product type
        const insertedColors: number[] = [];
        const insertedSizes: number[] = [];
    
        if (productData.has_color && productData.colors) {
            for (const color of productData.colors) {
                // Adjust color price if necessary
                let adjustedColorPrice = color.price;
                if (color.price < 1000) {
                    adjustedColorPrice = parseFloat((color.price * 1000).toFixed(2));
                }
    
                console.log(`Inserting color: ${color.name} with has_size: ${color.has_size}`);
    
                // Insert color with explicit boolean casting
                const colorResult = await query(
                    `INSERT INTO colors (product_id, name, price, qty, sold, has_size)
                     VALUES ($1, $2, $3, $4, 0, $5)
                     RETURNING id, has_size`,
                    [productId, color.name, adjustedColorPrice, color.qty, Boolean(color.has_size)]
                );
                const colorId = colorResult.rows[0].id;
                const colorHasSize = colorResult.rows[0].has_size;
                console.log(`Inserted color ID=${colorId}, has_size=${colorHasSize}`);
    
                insertedColors.push(colorId);
    
                // Insert color images
                const colorImagesInsertPromises = color.images.map((image: ImageInput) => {
                    console.log(`Inserting color image: ${image.url}`);
                    return query(
                        `INSERT INTO images (product_id, color_id, url, size_id)
                         VALUES ($1, $2, $3, $4)
                         RETURNING id, color_id, size_id, url`,
                        [productId, colorId, image.url, image.size_id || null]
                    );
                });
                const insertedColorImagesResults = await Promise.all(colorImagesInsertPromises);
                console.log(`Inserted ${insertedColorImagesResults.length} images for color ID=${colorId}`);
    
                if (color.has_size && color.sizes) {
                    for (const size of color.sizes) {
                        // Adjust size price if necessary
                        let adjustedSizePrice = size.price;
                        if (size.price < 1000) {
                            adjustedSizePrice = parseFloat((size.price * 1000).toFixed(2));
                        }
    
                        console.log(`Inserting size: ${size.name} with price: ${adjustedSizePrice}`);
    
                        // Insert size
                        const sizeResult = await query(
                            `INSERT INTO sizes (product_id, color_id, name, price, qty, sold)
                             VALUES ($1, $2, $3, $4, $5, 0)
                             RETURNING id`,
                            [productId, colorId, size.name, adjustedSizePrice, size.qty]
                        );
                        const sizeId = sizeResult.rows[0].id;
                        console.log(`Inserted size ID=${sizeId} for color ID=${colorId}`);
                        insertedSizes.push(sizeId);
    
                        // Insert size images
                        const sizeImagesInsertPromises = size.images.map((image: ImageInput) => {
                            console.log(`Inserting size image: ${image.url}`);
                            return query(
                                `INSERT INTO images (product_id, color_id, size_id, url)
                                 VALUES ($1, $2, $3, $4)
                                 RETURNING id, color_id, size_id, url`,
                                [productId, colorId, sizeId, image.url]
                            );
                        });
                        const insertedSizeImagesResults = await Promise.all(sizeImagesInsertPromises);
                        console.log(`Inserted ${insertedSizeImagesResults.length} images for size ID=${sizeId}`);
                    }
                }
            }
        } else if (productData.has_size && productData.sizes) {
            for (const size of productData.sizes) {
                // Adjust size price if necessary
                let adjustedSizePrice = size.price;
                if (size.price < 1000) {
                    adjustedSizePrice = parseFloat((size.price * 1000).toFixed(2));
                }
    
                console.log(`Inserting standalone size: ${size.name} with price: ${adjustedSizePrice}`);
    
                // Insert size
                const sizeResult = await query(
                    `INSERT INTO sizes (product_id, name, price, qty, sold)
                     VALUES ($1, $2, $3, $4, 0)
                     RETURNING id`,
                    [productId, size.name, adjustedSizePrice, size.qty]
                );
                const sizeId = sizeResult.rows[0].id;
                console.log(`Inserted standalone size ID=${sizeId}`);
                insertedSizes.push(sizeId);
    
                // Insert size images
                const sizeImagesInsertPromises = size.images.map((image: ImageInput) => {
                    console.log(`Inserting standalone size image: ${image.url}`);
                    return query(
                        `INSERT INTO images (product_id, size_id, url)
                         VALUES ($1, $2, $3)
                         RETURNING id, color_id, size_id, url`,
                        [productId, sizeId, image.url]
                    );
                });
                const insertedSizeImagesResults = await Promise.all(sizeImagesInsertPromises);
                console.log(`Inserted ${insertedSizeImagesResults.length} images for standalone size ID=${sizeId}`);
            }
        }
    
        // Construct the new product object as it would appear in the cache
        const newProduct: Product = {
            id: productId,
            name: productData.name,
            description: productData.description,
            brand_id: productData.brand_id,
            has_color: productData.has_color,
            has_size: productData.has_size,
            product_type: productData.product_type,
            price: adjustedPrice,
            qty: productData.qty,
            sold: 0,
            created_at: productResult.rows[0].created_at.toISOString(),
            updated_at: productResult.rows[0].updated_at.toISOString(),
            categories: productData.category_ids.map((cid: number) => ({
                id: cid,
                name: "", // Optional: Fetch and populate category names
                main_category_id: 0, // Optional: Set appropriately
                main_category_name: "" // Optional: Set appropriately
            })),
            colors: productData.has_color && productData.colors ? productData.colors.map((color, index) => ({
                id: insertedColors[index],
                name: color.name,
                price: color.price || 0,
                qty: color.qty,
                sold: 0,
                has_size: Boolean(color.has_size),
                sizes: Boolean(color.has_size) && color.sizes ? color.sizes.map((size, sIndex) => ({
                    id: insertedSizes[sIndex],
                    name: size.name,
                    price: size.price,
                    qty: size.qty,
                    sold: 0,
                    color_id: null, // Optional: Set if needed
                    images: size.images.map(img => img.url)
                })) : [],
                images: color.images.map(img => img.url)
            })) : [],
            sizes: productData.has_size && !productData.has_color && productData.sizes ? productData.sizes.map((size, index) => ({
                id: insertedSizes[index],
                name: size.name,
                price: size.price,
                qty: size.qty,
                sold: 0,
                color_id: null,
                images: size.images.map(img => img.url)
            })) : [],
            images: insertedImages
        };
    
        console.log("New product to cache:", newProduct); // Log new product
    
        // Update the cache
        addProductToCache(newProduct);
    
        await query('COMMIT');
        res.status(201).json({ message: 'Product added successfully!', productId });
    } catch (error) {
        await query('ROLLBACK');
        console.error('Error creating product:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
    

    }; /**

    • Update an existing product */ // src/controllers/productController.ts

    // src/controllers/productController.ts

    export const updateProduct = async (req: Request, res: Response) => { const { id } = req.params; const productData: UpdateProductInput = req.body; const productId: number = parseInt(id, 10);

    if (isNaN(productId)) {
        return res.status(400).json({ error: 'Invalid product ID' });
    }
    
    try {
        await query('BEGIN');
    
        // Adjust product price if necessary
        let adjustedProductPrice: number = productData.price;
        if (adjustedProductPrice < 1000) {
            adjustedProductPrice = parseFloat((adjustedProductPrice * 1000).toFixed(2));
        }
    
        // Update product details
        const result = await query(
            `
            UPDATE products 
            SET 
                name = $1, 
                description = $2, 
                brand_id = $3, 
                has_color = $4, 
                has_size = $5, 
                product_type = $6, 
                price = $7, 
                qty = $8, 
                updated_at = CURRENT_TIMESTAMP 
            WHERE id = $9 
            RETURNING *
            `,
            [
                productData.name,
                productData.description,
                productData.brand_id,
                productData.has_color,
                productData.has_size,
                productData.product_type,
                adjustedProductPrice,
                productData.qty,
                productId
            ]
        );
    
        if (result.rows.length === 0) {
            await query('ROLLBACK');
            return res.status(404).json({ error: 'Product not found' });
        }
    
        // Delete existing category associations
        await query('DELETE FROM product_categories WHERE product_id = $1', [productId]);
    
        // Determine if main images are provided in the update
        const hasMainImagesInUpdate = Array.isArray(productData.images) && productData.images.length > 0;
    
        // Handle Main Images
        let insertedMainImages: Image[] = [];
    
        if (hasMainImagesInUpdate) {
            // Delete existing main images
            await query('DELETE FROM images WHERE product_id = $1 AND color_id IS NULL AND size_id IS NULL', [productId]);
    
            // Insert new main images
            const imagesInsertPromises = productData.images.map((image: ImageInput) =>
                query(
                    `INSERT INTO images (product_id, url, color_id, size_id)
                     VALUES ($1, $2, $3, $4)
                     RETURNING id, url, color_id, size_id`,
                    [productId, image.url || image, image.color_id || null, image.size_id || null]
                )
            );
    
            const insertedImagesResults = await Promise.all(imagesInsertPromises);
            insertedMainImages = insertedImagesResults.map((imgResult: any) => ({
                id: imgResult.rows[0].id,
                url: imgResult.rows[0].url,
                color_id: imgResult.rows[0].color_id,
                size_id: imgResult.rows[0].size_id
            }));
        } else {
            // Fetch existing main images
            const existingMainImagesResult = await query(
                `SELECT id, url, color_id, size_id FROM images 
                 WHERE product_id = $1 AND color_id IS NULL AND size_id IS NULL`,
                [productId]
            );
    
            insertedMainImages = existingMainImagesResult.rows.map((row: any) => ({
                id: row.id,
                url: row.url,
                color_id: row.color_id,
                size_id: row.size_id
            }));
        }
    
        // Insert new category associations
        if (Array.isArray(productData.category_ids) && productData.category_ids.length > 0) {
            const categoryInsertPromises = productData.category_ids.map((categoryId: number) =>
                query(
                    `INSERT INTO product_categories (product_id, category_id) 
                     VALUES ($1, $2)`,
                    [productId, categoryId]
                )
            );
            await Promise.all(categoryInsertPromises);
        }
    
        // Insert or Update Colors and Sizes
        let insertedColors: number[] = [];
        let insertedSizes: number[] = [];
    
        if (productData.has_color && Array.isArray(productData.colors)) {
            for (const colorInput of productData.colors) {
                // Adjust color price if necessary
                let adjustedColorPrice: number = colorInput.price;
                if (adjustedColorPrice < 1000) {
                    adjustedColorPrice = parseFloat((adjustedColorPrice * 1000).toFixed(2));
                }
    
                // Insert or Update color
                let colorId: number;
                if (colorInput.id) {
                    // Update existing color
                    await query(
                        `
                        UPDATE colors 
                        SET 
                            name = $1, 
                            price = $2, 
                            qty = $3, 
                            sold = $4, 
                            has_size = $5 
                        WHERE id = $6
                        `,
                        [
                            colorInput.name,
                            adjustedColorPrice,
                            colorInput.qty,
                            colorInput.sold || 0,
                            colorInput.has_size,
                            colorInput.id
                        ]
                    );
                    colorId = colorInput.id;
                } else {
                    // Insert new color
                    const colorResult = await query(
                        `
                        INSERT INTO colors 
                            (product_id, name, price, qty, sold, has_size)
                        VALUES 
                            ($1, $2, $3, $4, 0, $5)
                        RETURNING id
                        `,
                        [productId, colorInput.name, adjustedColorPrice, colorInput.qty, colorInput.has_size]
                    );
                    colorId = colorResult.rows[0].id;
                    insertedColors.push(colorId);
                }
    
                // Handle Color Images
                const hasColorImagesInUpdate = Array.isArray(colorInput.images) && colorInput.images.length > 0;
    
                let colorImages: string[] = [];
    
                if (hasColorImagesInUpdate) {
                    // Delete existing images for this color
                    await query('DELETE FROM images WHERE product_id = $1 AND color_id = $2 AND size_id IS NULL', [productId, colorId]);
    
                    // Insert new images
                    const colorImagesInsertPromises = colorInput.images.map((image: ImageInput) =>
                        query(
                            `INSERT INTO images (product_id, color_id, url, size_id)
                             VALUES ($1, $2, $3, $4)
                             RETURNING id, url, color_id, size_id`,
                            [productId, colorId, image.url || image, image.size_id || null]
                        )
                    );
                    const insertedColorImagesResults = await Promise.all(colorImagesInsertPromises);
                    colorImages = insertedColorImagesResults.map((imgResult: any) => imgResult.rows[0].url);
                } else {
                    // Fetch existing color images
                    const existingColorImagesResult = await query(
                        `SELECT url FROM images 
                         WHERE product_id = $1 AND color_id = $2 AND size_id IS NULL`,
                        [productId, colorId]
                    );
                    colorImages = existingColorImagesResult.rows.map((row: any) => row.url);
                }
    
                // Handle Sizes within Color
                let colorSizes: Size[] = [];
    
                if (colorInput.has_size && Array.isArray(colorInput.sizes)) {
                    for (const sizeInput of colorInput.sizes) {
                        // Adjust size price if necessary
                        let adjustedSizePrice: number = sizeInput.price;
                        if (adjustedSizePrice < 1000) {
                            adjustedSizePrice = parseFloat((adjustedSizePrice * 1000).toFixed(2));
                        }
    
                        // Insert or Update size
                        let sizeId: number;
                        if (sizeInput.id) {
                            // Update existing size
                            await query(
                                `
                                UPDATE sizes 
                                SET 
                                    name = $1, 
                                    price = $2, 
                                    qty = $3, 
                                    sold = $4, 
                                    color_id = $5 
                                WHERE id = $6
                                `,
                                [
                                    sizeInput.name,
                                    adjustedSizePrice,
                                    sizeInput.qty,
                                    sizeInput.sold || 0,
                                    colorId,
                                    sizeInput.id
                                ]
                            );
                            sizeId = sizeInput.id;
                        } else {
                            // Insert new size
                            const sizeResult = await query(
                                `
                                INSERT INTO sizes 
                                    (product_id, color_id, name, price, qty, sold)
                                VALUES 
                                    ($1, $2, $3, $4, $5, 0)
                                RETURNING id
                                `,
                                [productId, colorId, sizeInput.name, adjustedSizePrice, sizeInput.qty]
                            );
                            sizeId = sizeResult.rows[0].id;
                            insertedSizes.push(sizeId);
                        }
    
                        // Handle Size Images
                        const hasSizeImagesInUpdate = Array.isArray(sizeInput.images) && sizeInput.images.length > 0;
    
                        let sizeImages: string[] = [];
    
                        if (hasSizeImagesInUpdate) {
                            // Delete existing images for this size
                            await query('DELETE FROM images WHERE product_id = $1 AND size_id = $2', [productId, sizeId]);
    
                            // Insert new images
                            const sizeImagesInsertPromises = sizeInput.images.map((image: ImageInput) =>
                                query(
                                    `INSERT INTO images (product_id, size_id, url)
                                     VALUES ($1, $2, $3)
                                     RETURNING id, url, color_id, size_id`,
                                    [productId, sizeId, image.url || image]
                                )
                            );
                            const insertedSizeImagesResults = await Promise.all(sizeImagesInsertPromises);
                            sizeImages = insertedSizeImagesResults.map((imgResult: any) => imgResult.rows[0].url);
                        } else {
                            // Fetch existing size images
                            const existingSizeImagesResult = await query(
                                `SELECT url FROM images 
                                 WHERE product_id = $1 AND size_id = $2`,
                                [productId, sizeId]
                            );
                            sizeImages = existingSizeImagesResult.rows.map((row: any) => row.url);
                        }
    
                        // Append to colorSizes
                        colorSizes.push({
                            id: sizeId,
                            name: sizeInput.name,
                            price: adjustedSizePrice,
                            qty: sizeInput.qty,
                            sold: sizeInput.sold || 0,
                            color_id: colorId,
                            images: sizeImages
                        });
                    }
                }
    
                // Append to cache structure
                // Note: Since updatedProduct is constructed later, we need to accumulate color and size data
                // We'll store color data temporarily
                // Alternatively, restructure the code to collect all necessary data before constructing updatedProduct
                // For simplicity, let's assume we collect necessary data for colors and sizes here
                // This requires more extensive restructuring
            }
        }
    
        // Insert or Update Sizes outside colors if necessary
        // (This part remains similar to the colors handling)
    
        // Fetch and populate categories with details
        const categories = await fetchCategoryDetails(productData.category_ids || []);
    
        // Handle Images for updatedProduct
        // Since we've already handled main images, colors, and sizes, we need to construct the updatedProduct accordingly
    
        // Fetch existing color images and size images if not updated
        // To do this, we need to fetch all existing images and assign them appropriately
    
        // Fetch all colors
        const colorsResult = await query(
            `SELECT id, name, price, qty, sold, has_size FROM colors WHERE product_id = $1`,
            [productId]
        );
    
        // Build colors with images and sizes
        const colors: Color[] = await Promise.all(
            colorsResult.rows.map(async (colorRow: any) => {
                // Fetch images for this color
                const colorImagesResult = await query(
                    `SELECT url FROM images WHERE product_id = $1 AND color_id = $2 AND size_id IS NULL`,
                    [productId, colorRow.id]
                );
                const colorImages = colorImagesResult.rows.map((row: any) => row.url);
    
                // Fetch sizes for this color
                const sizesResult = await query(
                    `SELECT id, name, price, qty, sold FROM sizes WHERE product_id = $1 AND color_id = $2`,
                    [productId, colorRow.id]
                );
    
                const sizes: Size[] = await Promise.all(
                    sizesResult.rows.map(async (sizeRow: any) => {
                        // Fetch images for this size
                        const sizeImagesResult = await query(
                            `SELECT url FROM images WHERE product_id = $1 AND size_id = $2`,
                            [productId, sizeRow.id]
                        );
                        const sizeImages = sizeImagesResult.rows.map((row: any) => row.url);
    
                        return {
                            id: sizeRow.id,
                            name: sizeRow.name,
                            price: sizeRow.price,
                            qty: sizeRow.qty,
                            sold: sizeRow.sold,
                            color_id: colorRow.id,
                            images: sizeImages
                        };
                    })
                );
    
                return {
                    id: colorRow.id,
                    name: colorRow.name,
                    price: colorRow.price,
                    qty: colorRow.qty,
                    sold: colorRow.sold,
                    has_size: colorRow.has_size,
                    images: colorImages,
                    sizes: sizes
                };
            })
        );
    
        // Fetch standalone sizes if the product does not have colors but has sizes
        let standaloneSizes: Size[] = [];
        if (!productData.has_color && productData.has_size) {
            const sizesResult = await query(
                `SELECT id, name, price, qty, sold FROM sizes WHERE product_id = $1 AND color_id IS NULL`,
                [productId]
            );
    
            standaloneSizes = sizesResult.rows.map((sizeRow: any) => ({
                id: sizeRow.id,
                name: sizeRow.name,
                price: sizeRow.price,
                qty: sizeRow.qty,
                sold: sizeRow.sold,
                color_id: null,
                images: [] // Fetch images if necessary
            }));
    
            // Optionally, fetch images for standalone sizes
            standaloneSizes = await Promise.all(
                standaloneSizes.map(async (size: Size) => {
                    const sizeImagesResult = await query(
                        `SELECT url FROM images WHERE product_id = $1 AND size_id = $2`,
                        [productId, size.id]
                    );
                    size.images = sizeImagesResult.rows.map((row: any) => row.url);
                    return size;
                })
            );
        }
    
        // Fetch existing main images if not updated
        if (!hasMainImagesInUpdate) {
            const existingMainImagesResult = await query(
                `SELECT id, url, color_id, size_id FROM images 
                 WHERE product_id = $1 AND color_id IS NULL AND size_id IS NULL`,
                [productId]
            );
    
            insertedMainImages = existingMainImagesResult.rows.map((row: any) => ({
                id: row.id,
                url: row.url,
                color_id: row.color_id,
                size_id: row.size_id
            }));
        }
    
        // Construct the updated product object as it should appear in the cache
        const updatedProduct: Product = {
            id: productId,
            name: productData.name,
            description: productData.description,
            brand_id: productData.brand_id,
            has_color: productData.has_color,
            has_size: productData.has_size,
            product_type: productData.product_type,
            price: adjustedProductPrice,
            qty: productData.qty,
            sold: result.rows[0].sold, // Retain existing sold count
            created_at: result.rows[0].created_at.toISOString(),
            updated_at: new Date().toISOString(),
            categories: categories,
            colors: colors,
            sizes: standaloneSizes,
            images: insertedMainImages
        };
    
        // Update the cache
        updateProductInCache(updatedProduct);
    
        await query('COMMIT');
        res.json({ message: 'Product updated successfully', product: updatedProduct });
    } catch (error) {
        await query('ROLLBACK');
        console.error('Error updating product:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
    

    };

    export const deleteProduct = async (req: Request, res: Response) => { const { id } = req.params; const productId: number = parseInt(id, 10);

    if (isNaN(productId)) {
        return res.status(400).json({ error: 'Invalid product ID' });
    }
    
    try {
        await query('BEGIN');
    
        // Delete related data first
        await query('DELETE FROM product_categories WHERE product_id = $1', [productId]);
        await query('DELETE FROM images WHERE product_id = $1', [productId]);
        await query('DELETE FROM sizes WHERE product_id = $1', [productId]);
        await query('DELETE FROM colors WHERE product_id = $1', [productId]);
    
        // Delete the product
        const result = await query('DELETE FROM products WHERE id = $1 RETURNING *', [productId]);
    
        if (result.rows.length === 0) {
            await query('ROLLBACK');
            return res.status(404).json({ error: 'Product not found' });
        }
    
        // Delete the product from the cache
        deleteProductFromCache(productId);
    
        await query('COMMIT');
        res.json({ message: 'Product and related data deleted successfully' });
    } catch (error) {
        await query('ROLLBACK');
        console.error('Error deleting product:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
    

    }; // src/cacheHelpers.ts

    import { productCache } from "./cache"; import { Product, CacheData, Color, Size } from "product"; // Adjust the import path as necessary

    /**

    • Adds a new product to the cache. */ export const addProductToCache = (newProduct: Product) => { try { const cacheData: CacheData | undefined = productCache.get('allProducts'); if (cacheData) { cacheData.allProducts.push(newProduct);

           // Extract all prices
           const productPrices: number[] = [];
           productPrices.push(newProduct.price);
      
           newProduct.colors.forEach((color: Color) => {
               productPrices.push(color.price);
               color.sizes.forEach((size: Size) => productPrices.push(size.price));
           });
      
           newProduct.sizes.forEach((size: Size) => productPrices.push(size.price));
      
           // Update minPrice and maxPrice
           if (productPrices.length > 0) {
               const productMinPrice = Math.min(...productPrices, cacheData.minPrice);
               const productMaxPrice = Math.max(...productPrices, cacheData.maxPrice);
               cacheData.minPrice = Math.min(cacheData.minPrice, productMinPrice);
               cacheData.maxPrice = Math.max(cacheData.maxPrice, productMaxPrice);
           }
      
           productCache.set('allProducts', cacheData);
           console.log('Added new product to cache.');
       } else {
           // Flush cache to force reload on next access
           productCache.del('allProducts');
           console.log('Cache unavailable. Flushed product cache.');
       }
      

      } catch (error) { console.error('Error adding product to cache:', error); productCache.del('allProducts'); } };

    /**

    • Updates an existing product in the cache. */ export const updateProductInCache = (updatedProduct: Product) => { try { const cacheData: CacheData | undefined = productCache.get('allProducts'); if (cacheData) { const index = cacheData.allProducts.findIndex(p => p.id === updatedProduct.id); if (index !== -1) { cacheData.allProducts[index] = updatedProduct;

               // Recalculate minPrice and maxPrice
               const allPrices: number[] = [];
               cacheData.allProducts.forEach(product => {
                   allPrices.push(product.price);
                   product.colors.forEach(color => {
                       allPrices.push(color.price);
                       color.sizes.forEach(size => allPrices.push(size.price));
                   });
                   product.sizes.forEach(size => allPrices.push(size.price));
               });
      
               if (allPrices.length > 0) {
                   cacheData.minPrice = Math.min(...allPrices);
                   cacheData.maxPrice = Math.max(...allPrices);
               } else {
                   cacheData.minPrice = 0;
                   cacheData.maxPrice = 0;
               }
      
               productCache.set('allProducts', cacheData);
               console.log('Updated product in cache.');
           } else {
               // Product not found; flush cache
               productCache.del('allProducts');
               console.log('Product not found in cache. Flushed product cache.');
           }
       } else {
           // Flush cache to force reload
           productCache.del('allProducts');
           console.log('Cache unavailable. Flushed product cache.');
       }
      

      } catch (error) { console.error('Error updating product in cache:', error); productCache.del('allProducts'); } };

    /**

    • Deletes a product from the cache. */ export const deleteProductFromCache = (productId: number) => { try { const cacheData: CacheData | undefined = productCache.get('allProducts'); if (cacheData) { const initialLength = cacheData.allProducts.length; cacheData.allProducts = cacheData.allProducts.filter(p => p.id !== productId); if (cacheData.allProducts.length < initialLength) { // Recalculate minPrice and maxPrice const allPrices: number[] = []; cacheData.allProducts.forEach(product => { allPrices.push(product.price); product.colors.forEach(color => { allPrices.push(color.price); color.sizes.forEach(size => allPrices.push(size.price)); }); product.sizes.forEach(size => allPrices.push(size.price)); });

               if (allPrices.length > 0) {
                   cacheData.minPrice = Math.min(...allPrices);
                   cacheData.maxPrice = Math.max(...allPrices);
               } else {
                   cacheData.minPrice = 0;
                   cacheData.maxPrice = 0;
               }
      
               productCache.set('allProducts', cacheData);
               console.log('Deleted product from cache.');
           } else {
               // Product not found; flush cache
               productCache.del('allProducts');
               console.log('Product not found in cache. Flushed product cache.');
           }
       } else {
           // Flush cache to force reload
           productCache.del('allProducts');
           console.log('Cache unavailable. Flushed product cache.');
       }
      

      } catch (error) { console.error('Error deleting product from cache:', error); productCache.del('allProducts'); } };

    /**

    • Decreases product, size, or color quantity in the cache based on identifiers.

    • @param productId - ID of the product

    • @param sizeId - ID of the size (optional)

    • @param colorId - ID of the color (optional)

    • @param quantity - Quantity to decrease */ export const decreaseProductQtyInCache = (productId: number, sizeId: number | null, colorId: number | null, quantity: number) => { try { const cacheData: CacheData | undefined = productCache.get('allProducts'); if (!cacheData) { // Cache is unavailable; nothing to update return; }

       const product = cacheData.allProducts.find(p => p.id === productId);
       if (!product) {
           // Product not found in cache; nothing to update
           return;
       }
      
       if (sizeId) {
           // Decrease quantity in the specific size
           const size = product.sizes.find(s => s.id === sizeId);
           if (size) {
               size.qty = size.qty - quantity;
               if (size.qty < 0) size.qty = 0; // Prevent negative stock
           }
      
           // Also, if color_id is present, update the associated color's size
           if (colorId) {
               const color = product.colors.find(c => c.id === colorId);
               if (color) {
                   const colorSize = color.sizes.find(s => s.id === sizeId);
                   if (colorSize) {
                       colorSize.qty = colorSize.qty - quantity;
                       if (colorSize.qty < 0) colorSize.qty = 0;
                   }
               }
           }
       } else if (colorId) {
           // Decrease quantity in the specific color
           const color = product.colors.find(c => c.id === colorId);
           if (color) {
               color.qty = color.qty - quantity;
               if (color.qty < 0) color.qty = 0; // Prevent negative stock
           }
       } else {
           // Decrease quantity in the product itself
           product.qty = product.qty - quantity;
           if (product.qty < 0) product.qty = 0; // Prevent negative stock
       }
      
       // Update the cache
       productCache.set('allProducts', cacheData);
       console.log('Decreased product quantity in cache.');
      

      } catch (error) { console.error('Error decreasing product quantity in cache:', error); // Decide whether to flush the cache or handle the error differently productCache.del('allProducts'); } };

    /**

    • Increases the 'sold' count for a size in the cache. */ export const increaseSizeSoldInCache = (productId: number, sizeId: number, quantity: number): void => { try { const cacheData: CacheData | undefined = productCache.get('allProducts'); if (!cacheData) { // Cache is unavailable; nothing to update return; }

       const product = cacheData.allProducts.find(p => p.id === productId);
       if (!product) {
           // Product not found in cache; nothing to update
           return;
       }
      
       const size = product.sizes.find(s => s.id === sizeId);
       if (size) {
           size.sold += quantity;
           // Ensure cache is updated
           productCache.set('allProducts', cacheData);
           console.log(`Increased sold count for size ID ${sizeId} in cache.`);
       }
      

      } catch (error) { console.error('Error increasing size sold in cache:', error); // Optionally, decide to flush the cache or handle the error differently productCache.del('allProducts'); } };

    /**

    • Increases the 'sold' count for a color in the cache. */ export const increaseColorSoldInCache = (productId: number, colorId: number, quantity: number): void => { try { const cacheData: CacheData | undefined = productCache.get('allProducts'); if (!cacheData) { // Cache is unavailable; nothing to update return; }

       const product = cacheData.allProducts.find(p => p.id === productId);
       if (!product) {
           // Product not found in cache; nothing to update
           return;
       }
      
       const color = product.colors.find(c => c.id === colorId);
       if (color) {
           color.sold += quantity;
           // Ensure cache is updated
           productCache.set('allProducts', cacheData);
           console.log(`Increased sold count for color ID ${colorId} in cache.`);
       }
      

      } catch (error) { console.error('Error increasing color sold in cache:', error); // Optionally, decide to flush the cache or handle the error differently productCache.del('allProducts'); } };

    /**

    • Increases the 'sold' count for a product in the cache. */ export const increaseProductSoldInCache = (productId: number, quantity: number): void => { try { const cacheData: CacheData | undefined = productCache.get('allProducts'); if (!cacheData) { // Cache is unavailable; nothing to update return; }

       const product = cacheData.allProducts.find(p => p.id === productId);
       if (product) {
           product.sold += quantity;
           // Ensure cache is updated
           productCache.set('allProducts', cacheData);
           console.log(`Increased sold count for product ID ${productId} in cache.`);
       }
      

      } catch (error) { console.error('Error increasing product sold in cache:', error); // Optionally, decide to flush the cache or handle the error differently productCache.del('allProducts'); } };

  2. Assistant

    To create a new function that returns the two most selling products with specific constraints—such as returning only their title, description, and minimum price (excluding prices that are zero), ensuring they are in stock, and providing their IDs—you can follow these steps:

    1. Define the Controller Function: Create a new controller function named getTopSellingProducts.
    2. Fetch Products from Cache: Retrieve all products from the cache. If the cache is empty, load the products from the database and cache them.
    3. Filter In-Stock Products: Ensure that each product is in stock. If a top-selling product is out of stock, skip it and look for the next best-selling product.
    4. Compute Minimum Price: For each product, calculate the minimum price across all sizes and colors, ensuring that the price is greater than zero.
    5. Sort and Select Top Two: Sort the filtered products by the number of units sold in descending order and select the top two.
    6. Return Required Fields: Return only the id, title (assuming it's name in your schema), description, and minPrice for each of the two products.

    Below is the complete implementation of this functionality:


    1. Controller Function: getTopSellingProducts

    // src/controllers/productController.ts
    
    import { Request, Response } from 'express';
    import { CacheData, Product, Color, Size } from 'product'; // Adjust the import path as necessary
    import { productCache } from "../cache";
    import { loadAndCacheAllProducts } from "./productController"; // Adjust import if loadAndCacheAllProducts is exported differently
    
    /**
     * Get two most selling products that are in stock.
     * Returns only id, title, description, and min price (excluding 0).
     */
    export const getTopSellingProducts = async (req: Request, res: Response): Promise<void> => {
        try {
            // Retrieve cached products
            let cacheData: CacheData | undefined = productCache.get('allProducts');
    
            if (!cacheData) {
                // Cache miss: Load from DB and cache
                cacheData = await loadAndCacheAllProducts();
                console.log('Loaded products from database and cached them.');
            } else {
                console.log('Loaded products from cache.');
            }
    
            const { allProducts } = cacheData;
    
            // Function to compute minimum price excluding 0
            const getMinPrice = (product: Product): number | null => {
                let prices: number[] = [];
    
                // Include product base price if >0
                if (product.price > 0) prices.push(product.price);
    
                // Include colors' prices
                if (product.has_color) {
                    product.colors.forEach(color => {
                        if (color.price > 0) prices.push(color.price);
    
                        // Include sizes' prices within colors
                        if (color.has_size) {
                            color.sizes.forEach(size => {
                                if (size.price > 0) prices.push(size.price);
                            });
                        }
                    });
                }
    
                // Include standalone sizes' prices
                if (product.has_size) {
                    product.sizes.forEach(size => {
                        if (size.price > 0) prices.push(size.price);
                    });
                }
    
                if (prices.length === 0) return null;
    
                return Math.min(...prices);
            };
    
            // Filter products that are in stock and have a valid min price
            const filteredProducts = allProducts
                .map(product => {
                    // Determine if the product is in stock
                    const isInStock = product.qty > 0 ||
                        (product.has_color && product.colors.some(color => color.qty > 0 ||
                            (color.has_size && color.sizes.some(size => size.qty > 0)))) ||
                        (product.has_size && product.sizes.some(size => size.qty > 0));
    
                    // Calculate min price
                    const minPrice = getMinPrice(product);
    
                    return {
                        id: product.id,
                        title: product.name,
                        description: product.description,
                        sold: product.sold,
                        isInStock,
                        minPrice
                    };
                })
                .filter(product => product.isInStock && product.minPrice !== null && product.minPrice > 0);
    
            if (filteredProducts.length === 0) {
                return res.status(200).json({ products: [] });
            }
    
            // Sort the products by sold in descending order
            filteredProducts.sort((a, b) => b.sold - a.sold);
    
            // Select the top two products
            const topTwoProducts = filteredProducts.slice(0, 2).map(product => ({
                id: product.id,
                title: product.title,
                description: product.description,
                minPrice: product.minPrice as number
            }));
    
            res.status(200).json({ products: topTwoProducts });
        } catch (error) {
            console.error('Error fetching top selling products:', error);
            res.status(500).json({ error: 'Internal server error' });
        }
    };
    

    2. Adding the Route

    You need to add a new route to access this controller function. Assuming you have a separate route file, e.g., productRoutes.ts, you can add the following:

    // src/routes/productRoutes.ts
    
    import { Router } from 'express';
    import { getProducts, getProductById, createProduct, updateProduct, deleteProduct, getTopSellingProducts } from '../controllers/productController';
    
    const router = Router();
    
    // Existing routes
    router.get('/', getProducts);
    router.get('/:id', getProductById);
    router.post('/', createProduct);
    router.put('/:id', updateProduct);
    router.delete('/:id', deleteProduct);
    
    // New route for top selling products
    router.get('/top-selling', getTopSellingProducts);
    
    export default router;
    

    3. Updating the Server to Use the New Route

    Ensure that your main server file uses the updated routes. Here's an example:

    // src/app.ts or src/server.ts
    
    import express from 'express';
    import productRoutes from './routes/productRoutes';
    
    const app = express();
    
    // Middleware
    app.use(express.json());
    
    // Routes
    app.use('/products', productRoutes);
    
    // Error handling middleware (optional)
    // ...
    
    export default app;
    

    4. Testing the Endpoint

    You can test the new endpoint using tools like Postman or cURL.

    Example Request:

    GET http://localhost:3000/products/top-selling
    

    Example Response:

    {
        "products": [
            {
                "id": 123,
                "title": "Product A",
                "description": "Description of Product A",
                "minPrice": 29.99
            },
            {
                "id": 456,
                "title": "Product B",
                "description": "Description of Product B",
                "minPrice": 19.99
            }
        ]
    }
    

    5. Explanation of the Implementation

    • Fetching from Cache or Database: The function first attempts to retrieve all products from the productCache. If the cache is empty, it invokes loadAndCacheAllProducts to load the data from the database and populate the cache.

    • Filtering In-Stock Products: It filters the products to include only those that are in stock. This means checking if the product itself has a quantity greater than 0, or if any of its colors or sizes have quantities greater than 0.

    • Calculating Minimum Price: For each product, the function calculates the minimum price by considering the product's base price, the prices of all its colors, and the prices of all its sizes. Prices that are zero are excluded from this calculation.

    • Sorting and Selecting Top Two: The filtered products are then sorted based on the number of units sold in descending order. The top two products from this sorted list are selected.

    • Returning Required Fields: Finally, the function returns only the id, title, description, and minPrice for each of the two selected products.

    6. Handling Edge Cases

    • No Products in Stock: If there are no products in stock, the function returns an empty array.

    • Less Than Two Products Available: If there are only one or two products that meet the criteria, the function returns as many as are available without throwing an error.

    • All Prices Are Zero: Products with all prices set to zero are excluded from the results to adhere to the requirement that the minimum price should not be zero.

    7. Optimizations and Enhancements

    • Caching the Top Products: If this endpoint is frequently accessed, consider caching the top two products separately to reduce computation time.

    • Dynamic Selection: Allow clients to specify how many top products they want, rather than hardcoding it to two.

    • Error Handling: Enhance error handling to provide more specific error messages based on different failure scenarios.

    • Logging: Implement more robust logging mechanisms to track how often this endpoint is accessed and any issues that arise.


    By following the above implementation, you can efficiently fetch and return the two most selling products that are currently in stock, along with their essential details. This approach leverages your existing caching mechanism to optimize performance and ensure that the data is readily available for quick access.