Whenever a new product is created pushed it into the cache if possible... if not possible just clear the cache...
Whenever a product is deleted delete that product from the cache if possible... if not possible just clear the cache...
Whenever a product is updated, update the product in the cache if possible... if not possible just clear the cache...
productController.ts:
import { Request, Response } from 'express';
import {query} from "../config/database";
import {brandsCache, productCache} from "../cache";
import {Brand, BrandsCacheData, CacheData, Product} from "product";


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
        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 sizes s ON p.id = s.product_id
    `;

    try {
        const result = await query(q, []);
        const productsMap: Record<number, Product> = {};

        result.rows.forEach((row: any) => {
            const productId = Number(row.id);
            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: row.created_at,
                    updated_at: row.updated_at,
                    categories: [],
                    sizes: [],
                };
            }

            // Add category if not already present
            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 = productsMap[productId].categories.find(cat => cat.id === categoryId);
                if (!existingCategory) {
                    productsMap[productId].categories.push({
                        id: categoryId,
                        name: row.subcategory_name,
                        main_category_id: mainCategoryId,
                        main_category_name: row.main_category_name,
                    });
                }
            }

            // Add size if not already present
            if (row.size_id && row.size_name) {
                const sizeId = Number(row.size_id);
                const existingSize = productsMap[productId].sizes.find(size => size.id === sizeId);
                if (!existingSize) {
                    productsMap[productId].sizes.push({
                        id: sizeId,
                        name: row.size_name,
                        price: Number(row.size_price),
                        qty: Number(row.size_qty),
                        sold: Number(row.size_sold),
                    });
                }
            }
        });

        const allProducts = Object.values(productsMap);

        // Calculate min and max prices across all products
        let minPrice = Infinity;
        let maxPrice = -Infinity;
        allProducts.forEach(product => {
            product.sizes.forEach(size => {
                if (size.price < minPrice) minPrice = size.price;
                if (size.price > maxPrice) maxPrice = size.price;
            });
        });

        // Fallback in case no sizes are present
        if (minPrice === Infinity) minPrice = 0;
        if (maxPrice === -Infinity) maxPrice = 0;

        // Cache the data
        const cacheData: CacheData = { allProducts, minPrice, maxPrice };
        productCache.set('cacheData', 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 product has no sizes, exclude it
                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 {
        await query('BEGIN');

        const productData = req.body;

        // Adjust price if necessary
        if (productData.price < 1000) {
            productData.price *= 1000;
        }

        // Insert product
        const productResult = await query(
            `INSERT INTO products (name, description, brand_id, has_color, has_size, product_type, price, qty, sold)
             VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 0) RETURNING id`,
            [
                productData.name,
                productData.description,
                productData.brand_id,
                productData.hasColor,
                productData.hasSize,
                productData.product_type,
                productData.price,
                productData.qty
            ]
        );
        const productId = productResult.rows[0].id;

        // Insert category associations
        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 images
        const imagesInsertPromises = productData.images.map((imageUrl: string) =>
            query(
                `INSERT INTO images (product_id, url) 
                 VALUES ($1, $2)`,
                [productId, imageUrl]
            )
        );
        await Promise.all(imagesInsertPromises);

        // Handle colors and sizes based on product type
        if (productData.hasColor) {
            for (const color of productData.colors) {
                // Adjust color price if necessary
                if (color.price < 1000) {
                    color.price *= 1000;
                }

                // Insert color
                const colorResult = await query(
                    `INSERT INTO colors (product_id, name, has_size, price, qty, sold)
                     VALUES ($1, $2, $3, $4, $5, 0) RETURNING id`,
                    [productId, color.name, color.hasSize, color.price, color.qty]
                );
                const colorId = colorResult.rows[0].id;

                // Insert color images
                const colorImagesInsertPromises = color.images.map((imageUrl: string) =>
                    query(
                        `INSERT INTO images (product_id, color_id, url)
                         VALUES ($1, $2, $3)`,
                        [productId, colorId, imageUrl]
                    )
                );
                await Promise.all(colorImagesInsertPromises);

                if (color.hasSize) {
                    for (const size of color.sizes) {
                        // Adjust size price if necessary
                        if (size.price < 1000) {
                            size.price *= 1000;
                        }

                        // 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, size.price, size.qty]
                        );
                        const sizeId = sizeResult.rows[0].id;

                        // Insert size images
                        const sizeImagesInsertPromises = size.images.map((imageUrl: string) =>
                            query(
                                `INSERT INTO images (product_id, color_id, size_id, url)
                                 VALUES ($1, $2, $3, $4)`,
                                [productId, colorId, sizeId, imageUrl]
                            )
                        );
                        await Promise.all(sizeImagesInsertPromises);
                    }
                }
            }
        } else if (productData.hasSize) {
            for (const size of productData.sizes) {
                // Adjust size price if necessary
                if (size.price < 1000) {
                    size.price *= 1000;
                }

                // 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, size.price, size.qty]
                );
                const sizeId = sizeResult.rows[0].id;

                // Insert size images
                const sizeImagesInsertPromises = size.images.map((imageUrl: string) =>
                    query(
                        `INSERT INTO images (product_id, size_id, url)
                         VALUES ($1, $2, $3)`,
                        [productId, sizeId, imageUrl]
                    )
                );
                await Promise.all(sizeImagesInsertPromises);
            }
        }

        // Invalidate the all_products cache

        await query('COMMIT');
        res.status(201).json({ message: 'Product created successfully', productId });
    } catch (error) {
        await query('ROLLBACK');
        console.error('Error creating product:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
};

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

    try {
        await query('BEGIN');

        // Adjust price if necessary
        if (productData.price < 1000) {
            productData.price *= 1000;
        }

        // 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.hasColor,
                productData.hasSize,
                productData.product_type,
                productData.price,
                productData.qty,
                id
            ]
        );

        if (result.rows.length === 0) {
            await query('ROLLBACK');
            return res.status(404).json({ error: 'Product not found' });
        }

        // Delete existing associations and data
        await query('DELETE FROM product_categories WHERE product_id = $1', [id]);
        await query('DELETE FROM images WHERE product_id = $1', [id]);

        // Get existing colors and sizes
        const existingColors = await query('SELECT id FROM colors WHERE product_id = $1', [id]);
        const existingColorIds = new Set(existingColors.rows.map(row => row.id));

        const existingSizes = await query('SELECT id FROM sizes WHERE product_id = $1', [id]);
        const existingSizeIds = new Set(existingSizes.rows.map(row => row.id));

        // Determine colors and sizes to delete
        const updatedColorIds = new Set(
            productData.colors
                .map((color: { id: any }) => color.id)
                .filter((id: any) => id !== undefined)
        );
        const colorsToDelete = [...existingColorIds].filter(id => !updatedColorIds.has(id));

        const updatedSizeIds = new Set([
            ...productData.sizes
                .map((size: { id: any }) => size.id)
                .filter((id: any) => id !== undefined),
            ...productData.colors.flatMap((color: { sizes: any[] }) =>
                color.sizes.map(size => size.id).filter(id => id !== undefined)
            )
        ]);
        const sizesToDelete = [...existingSizeIds].filter(id => !updatedSizeIds.has(id));

        // Delete colors and associated sizes and images
        for (const colorId of colorsToDelete) {
            await query('DELETE FROM sizes WHERE color_id = $1', [colorId]);
            await query('DELETE FROM images WHERE color_id = $1', [colorId]);
            await query('DELETE FROM colors WHERE id = $1', [colorId]);
        }

        // Delete sizes and associated images
        for (const sizeId of sizesToDelete) {
            await query('DELETE FROM images WHERE size_id = $1', [sizeId]);
            await query('DELETE FROM sizes WHERE id = $1', [sizeId]);
        }

        // Insert category associations
        const categoryInsertPromises = productData.category_ids.map((categoryId: number) =>
            query(
                `INSERT INTO product_categories (product_id, category_id) 
                 VALUES ($1, $2)`,
                [id, categoryId]
            )
        );
        await Promise.all(categoryInsertPromises);

        // Insert images
        const imagesInsertPromises = productData.images.map((imageUrl: string) =>
            query(
                `INSERT INTO images (product_id, url) 
                 VALUES ($1, $2)`,
                [id, imageUrl]
            )
        );
        await Promise.all(imagesInsertPromises);

        // Handle colors and sizes based on product type
        if (productData.hasColor) {
            for (const color of productData.colors) {
                // Adjust color price if necessary
                if (color.price < 1000) {
                    color.price *= 1000;
                }

                let colorId: number;
                if (color.id) {
                    // Update existing color
                    await query(
                        `UPDATE colors 
                         SET name = $1, 
                             has_size = $2, 
                             price = $3, 
                             qty = $4, 
                             sold = $5 
                         WHERE id = $6`,
                        [color.name, color.hasSize, color.price, color.qty, color.sold || 0, color.id]
                    );
                    colorId = color.id;
                } else {
                    // Insert new color
                    const colorResult = await query(
                        `INSERT INTO colors (product_id, name, has_size, price, qty, sold)
                         VALUES ($1, $2, $3, $4, $5, 0) RETURNING id`,
                        [id, color.name, color.hasSize, color.price, color.qty]
                    );
                    colorId = colorResult.rows[0].id;
                }

                // Insert color images
                const colorImagesInsertPromises = color.images.map((imageUrl: string) =>
                    query(
                        `INSERT INTO images (product_id, color_id, url)
                         VALUES ($1, $2, $3)`,
                        [id, colorId, imageUrl]
                    )
                );
                await Promise.all(colorImagesInsertPromises);

                if (color.hasSize) {
                    for (const size of color.sizes) {
                        // Adjust size price if necessary
                        if (size.price < 1000) {
                            size.price *= 1000;
                        }

                        let sizeId: number;
                        if (size.id) {
                            // Update existing size
                            await query(
                                `UPDATE sizes 
                                 SET name = $1, 
                                     price = $2, 
                                     qty = $3, 
                                     sold = $4 
                                 WHERE id = $5`,
                                [size.name, size.price, size.qty, size.sold || 0, size.id]
                            );
                            sizeId = size.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`,
                                [id, colorId, size.name, size.price, size.qty]
                            );
                            sizeId = sizeResult.rows[0].id;
                        }

                        // Insert size images
                        const sizeImagesInsertPromises = size.images.map((imageUrl: string) =>
                            query(
                                `INSERT INTO images (product_id, color_id, size_id, url)
                                 VALUES ($1, $2, $3, $4)`,
                                [id, colorId, sizeId, imageUrl]
                            )
                        );
                        await Promise.all(sizeImagesInsertPromises);
                    }
                }
            }
        } else if (productData.hasSize) {
            for (const size of productData.sizes) {
                // Adjust size price if necessary
                if (size.price < 1000) {
                    size.price *= 1000;
                }

                let sizeId: number;
                if (size.id) {
                    // Update existing size
                    await query(
                        `UPDATE sizes 
                         SET name = $1, 
                             price = $2, 
                             qty = $3, 
                             sold = $4 
                         WHERE id = $5`,
                        [size.name, size.price, size.qty, size.sold || 0, size.id]
                    );
                    sizeId = size.id;
                } else {
                    // Insert new size
                    const sizeResult = await query(
                        `INSERT INTO sizes (product_id, name, price, qty, sold)
                         VALUES ($1, $2, $3, $4, 0) RETURNING id`,
                        [id, size.name, size.price, size.qty]
                    );
                    sizeId = sizeResult.rows[0].id;
                }

                // Insert size images
                const sizeImagesInsertPromises = size.images.map((imageUrl: string) =>
                    query(
                        `INSERT INTO images (product_id, size_id, url)
                         VALUES ($1, $2, $3)`,
                        [id, sizeId, imageUrl]
                    )
                );
                await Promise.all(sizeImagesInsertPromises);
            }
        }

        // Invalidate the all_products cache

        await query('COMMIT');
        res.json({ message: 'Product updated successfully', product: result.rows[0] });
    } 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;
    try {
        await query('BEGIN');

        // Delete related data first
        await query('DELETE FROM product_categories WHERE product_id = $1', [id]);
        await query('DELETE FROM images WHERE product_id = $1', [id]);
        await query('DELETE FROM sizes WHERE product_id = $1', [id]);
        await query('DELETE FROM colors WHERE product_id = $1', [id]);

        // Delete the product
        const result = await query('DELETE FROM products WHERE id = $1 RETURNING *', [id]);

        if (result.rows.length === 0) {
            await query('ROLLBACK');
            return res.status(404).json({ error: 'Product not found' });
        }

        // Invalidate the all_products cache

        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' });
    }
};
types/product.ts:
export interface Category {
    id: number;
    name: string;
    main_category_id: number;
    main_category_name: string;
}

interface Size {
    id: number;
    name: string;
    price: number;
    qty: number;
    sold: number;
}

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;
    qty: number;
    sold: number;
    created_at: string;
    updated_at: string;
    categories: Category[];
    sizes: Size[];
    // Add other relevant fields like colors and images if necessary
}

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[];
}

cache.ts:
// src/cache.ts
import NodeCache from 'node-cache';

// Initialize cache with a TTL (Time To Live) of 1 hour
export const productCache = new NodeCache({ stdTTL: 0, checkperiod: 600 });
export const brandsCache = new NodeCache({ stdTTL: 0, checkperiod: 600 });

export const flushProductCache = () => {
    productCache.del('allProducts');
};