I have created a color_images table...
CREATE TABLE IF NOT EXISTS color_images (
name VARCHAR(255) PRIMARY KEY,
url TEXT NOT NULL
);
it has two columns name and url... each name should be assigned to a url...
using nodejs and express create routes and controllers to get all color_images, get a url by name, edit and delete.
Also store all the operations in the cache as well! so less querying
server.ts:
import express from 'express';
import morgan from 'morgan';
import dotenv from 'dotenv';
import cors from 'cors';
import compression from 'compression';
import { errorHandler } from './middleware/errorHandler';
import categoryRoutes from './routes/categoryRoutes';
import subcategoryRoutes from './routes/subcategoryRoutes';
import productRoutes from "./routes/productRoutes";
import userRoutes from "./routes/userRoutes";
import addressRoutes from "./routes/addressRoutes";
import couponRoutes from "./routes/couponRoutes";
import cartRoutes from "./routes/cartRoutes";
import wishlistRoutes from "./routes/wishlistRoutes";
import nocache from 'nocache';
import counterRoutes from "./routes/counterRoutes";
import imageOptimizationRoutes from "./routes/imageOptimizationRoutes";

dotenv.config();


const app = express();

app.use(cors());
app.use(compression());
app.use(nocache());
app.use(express.json({ limit: '500kb' }));
app.use(morgan('dev'));

app.set('trust proxy', 1);
app.use('/user', userRoutes);
app.use('/categories', categoryRoutes);
app.use('/subcategories', subcategoryRoutes);
app.use('/products', productRoutes);
app.use('/addresses', addressRoutes);
app.use('/coupons', couponRoutes);
app.use('/counter', counterRoutes);
app.use('/cart', cartRoutes);
app.use('/wishlist', wishlistRoutes);
app.use('/images', imageOptimizationRoutes);
app.use(errorHandler);

const PORT = 4000;
app.listen(PORT, () => {
    console.log(`Server running on port ${PORT}`);
});

export default app;
config/database.ts:
import { Pool, QueryResult, QueryResultRow, PoolClient } from 'pg';
import dotenv from 'dotenv';

dotenv.config();

const MAX_RETRIES = 5;
const RETRY_DELAY = 5000; // 5 seconds

export const pool = new Pool({
    host: 'ep-young-math-a2o7mpxf-pooler.eu-central-1.aws.neon.tech',
    database: 'ecommerce',
    user: 'ecommerce_owner',
    password: 'passwordhere',
    ssl: {
        rejectUnauthorized: false
    },
    max: 10,
    idleTimeoutMillis: 30000,
    keepAlive: true,
    connectionTimeoutMillis: 50000,
});

pool.on('error', (err) => {
    console.error('Unexpected error on idle client', err);
    // Instead of exiting, we'll log the error and let the app continue
    console.error('Application will continue running, but database operations may fail');
});

async function retryOperation<T>(operation: () => Promise<T>, retries = MAX_RETRIES): Promise<T> {
    try {
        return await operation();
    } catch (error) {
        if (retries > 0) {
            console.warn(`Operation failed, retrying... (${retries} attempts left)`);
            await new Promise(resolve => setTimeout(resolve, RETRY_DELAY));
            return retryOperation(operation, retries - 1);
        } else {
            throw error;
        }
    }
}

export async function query<T extends QueryResultRow = any>(text: string, params?: any[]): Promise<QueryResult<T>> {
    return retryOperation(async () => {
        const client = await pool.connect();
        try {
            return await client.query(text, params);
        } finally {
            client.release();
        }
    });
}

export async function getClient(): Promise<PoolClient> {
    return retryOperation(() => pool.connect());
}
cache.ts:
// src/cache.ts
import NodeCache from 'node-cache';

export const productCache = new NodeCache({ stdTTL: 0, checkperiod: 600 });
export const brandsCache = new NodeCache({ stdTTL: 0, checkperiod: 600 });
export const cartCache = new NodeCache({ stdTTL: 0, checkperiod: 600 }); // Cache carts for 5 minutes

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

export const flushCartCache = (userId: number) => {
    cartCache.del(`cart_user_${userId}`);
};

export const flushAllCarts = () => {
    cartCache.flushAll();
};
cacheHelper.ts:
// src/cacheHelper.ts

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

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

            // Extract all prices from the new product
            const productPrices: number[] = [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) {
                cacheData.minPrice = Math.min(cacheData.minPrice, ...productPrices);
                cacheData.maxPrice = Math.max(cacheData.maxPrice, ...productPrices);
            }

            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): 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;
        }

        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 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 increase
 */
export const increaseProductQtyInCache = (productId: number, sizeId: number | null, colorId: number | null, 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;
        }

        if (sizeId) {
            // Increase quantity in the specific size
            const size = product.sizes.find(s => s.id === sizeId);
            if (size) {
                size.qty += quantity;
            }

            // 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 += quantity;
                    }
                }
            }
        } else if (colorId) {
            // Increase quantity in the specific color
            const color = product.colors.find(c => c.id === colorId);
            if (color) {
                color.qty += quantity;
            }
        } else {
            // Increase quantity in the product itself
            product.qty += quantity;
        }

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

/**
 * Decreases the 'sold' count for a size in the cache.
 */
export const decreaseSizeSoldInCache = (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;
            if (size.sold < 0) size.sold = 0; // Prevent negative sold counts
            // Ensure cache is updated
            productCache.set('allProducts', cacheData);
            console.log(`Decreased sold count for size ID ${sizeId} in cache.`);
        }
    } catch (error) {
        console.error('Error decreasing size sold in cache:', error);
        // Optionally, decide to flush the cache or handle the error differently
        productCache.del('allProducts');
    }
};

/**
 * Decreases the 'sold' count for a color in the cache.
 */
export const decreaseColorSoldInCache = (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;
            if (color.sold < 0) color.sold = 0; // Prevent negative sold counts
            // Ensure cache is updated
            productCache.set('allProducts', cacheData);
            console.log(`Decreased sold count for color ID ${colorId} in cache.`);
        }
    } catch (error) {
        console.error('Error decreasing color sold in cache:', error);
        // Optionally, decide to flush the cache or handle the error differently
        productCache.del('allProducts');
    }
};

/**
 * Decreases the 'sold' count for a product in the cache.
 */
export const decreaseProductSoldInCache = (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;
            if (product.sold < 0) product.sold = 0; // Prevent negative sold counts
            // Ensure cache is updated
            productCache.set('allProducts', cacheData);
            console.log(`Decreased sold count for product ID ${productId} in cache.`);
        }
    } catch (error) {
        console.error('Error decreasing product sold in cache:', error);
        // Optionally, decide 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');
    }
};