Optimize the getProducts performance as much as possible with making sure it returns the same result as it is now...
also improve the stability of it and of filtering and searching... Also remove console.log... Revise your solution and make sure it is the best...
I'm hosting my express app in 1gb ram vps...
productController.ts:
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<CacheData> => {
    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<BrandsCacheData>} A promise that resolves to the cached brand data.
 */
const loadAndCacheAllBrands = async (): Promise<BrandsCacheData> => {
    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<void> => {
    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
    }
};

// Rest of productController code...
cache.ts:
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<CacheData> => {
    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<BrandsCacheData>} A promise that resolves to the cached brand data.
 */
const loadAndCacheAllBrands = async (): Promise<BrandsCacheData> => {
    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<void> => {
    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
    }
};
cacheHelper.ts:
// src/cacheHelpers.ts

import { productCache } from "./cache";
import { Product, CacheData, Color, Size } from "./types/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');
    }
};
types/product.ts:
// src/types/productInput.ts

export interface ImageInput {
    url: string;
    color_id?: number | null;
    size_id?: number | null;
}

export interface SizeInput {
    id?: number; // Optional for creation
    name: string;
    price: number; // Changed from string to number
    qty: number;
    sold?: number;
    color_id?: number | null;
    images: ImageInput[];
}

export interface ColorInput {
    id?: number; // Optional for creation
    name: string;
    price: number; // Changed from string to number
    qty: number;
    sold?: number;
    has_size: boolean;
    sizes: SizeInput[];
    images: ImageInput[];
}

export interface CreateProductInput {
    name: string;
    description: string;
    brand_id: number;
    has_color: boolean;
    has_size: boolean;
    product_type: string;
    price: number; // Changed from string to number
    qty: number;
    category_ids: number[];
    images: ImageInput[];
    colors?: ColorInput[]; // Optional based on has_color
    sizes?: SizeInput[]; // Optional based on has_size and has_color
}

export interface UpdateProductInput extends CreateProductInput {}

export interface Category {
    id: number;
    name: string;
    main_category_id: number;
    main_category_name: string;
}

export interface Image {
    id: number;
    url: string;
    color_id: number | null;
    size_id: number | null;
}

export interface Size {
    id: number;
    name: string;
    price: number; // Changed from string to number for arithmetic operations
    qty: number;
    sold: number;
    color_id: number | null;
    images: string[];
}

export interface Color {
    id: number;
    name: string;
    price: number; // Changed from string to number
    qty: number;
    sold: number;
    has_size: boolean; // Mandatory property
    sizes: Size[];
    images: string[];
}

export interface Product {
    id: number;
    name: string;
    description: string;
    brand_id: number; // Also a subcategory_id for brands
    has_color: boolean;
    has_size: boolean;
    product_type: string;
    price: number; // Changed from string to number
    qty: number;
    sold: number;
    created_at: string;
    updated_at: string;
    categories: Category[];
    colors: Color[];
    sizes: Size[];
    images: Image[];
}

export interface Brand {
    id: number;
    name: string;
    category_id: number;
    category_name: string;
}

export interface CacheData {
    allProducts: Product[];
    minPrice: number;
    maxPrice: number;
}

export interface BrandsCacheData {
    allBrands: Brand[];
}