createCart calls updateProductQuantity that does update the qty of product or size or color in cache... but doesn't in the database!
// src/controllers/cartController.ts

import { Request, Response, NextFunction } from 'express';
import { query } from "../config/database";
import { deleteCartById, updateCartStatus } from "../models/cartModel";
import {
    decreaseProductQtyInCache,
    increaseColorSoldInCache,
    increaseProductSoldInCache,
    increaseSizeSoldInCache
} from "../cacheHelper";
import {cartCache, flushAllCarts, flushCartCache} from "../cache";

/**
 * Creates a new cart and updates product quantities accordingly.
 */
async function updateProductQuantity(productId: number, sizeId: number | null, colorId: number | null, quantity: number): Promise<void> {
    let updateQuery = '';
    const params: any[] = [quantity, productId, null]; // Initialize with null

    if (sizeId) {
        updateQuery = 'UPDATE sizes SET qty = qty - $1 WHERE id = $2 AND product_id = $3';
        params[2] = sizeId;
    } else if (colorId) {
        updateQuery = 'UPDATE colors SET qty = qty - $1 WHERE id = $2 AND product_id = $3';
        params[2] = colorId;
    } else {
        updateQuery = 'UPDATE products SET qty = qty - $1 WHERE id = $2';
        params.splice(2, 1); // Remove the third parameter as it's not needed
    }

    if (updateQuery) {
        await query(updateQuery, params);

        // Decrease the quantity in the cache
        decreaseProductQtyInCache(productId, sizeId, colorId, quantity);
    }
}

/**
 * Increases the 'sold' count based on product attributes.
 * @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
 */
async function increaseSold(productId: number, sizeId: number | null, colorId: number | null, quantity: number): Promise<void> {
    let updateQuery = '';
    let params: any[] = [quantity, productId];

    if (sizeId) {
        updateQuery = 'UPDATE sizes SET sold = sold + $1 WHERE id = $2 AND product_id = $3';
        params.push(sizeId);
    } else if (colorId) {
        updateQuery = 'UPDATE colors SET sold = sold + $1 WHERE id = $2 AND product_id = $3';
        params.push(colorId);
    }
    let productQuery = 'UPDATE products SET sold = sold + $1 WHERE id = $2';
    let productParams = [quantity, productId];

    if (updateQuery) {
        await query(updateQuery, params);
        await query(productQuery, productParams);

        // Update the sold count in the cache
        if (sizeId) {
            increaseSizeSoldInCache(productId, sizeId, quantity);
        } else if (colorId) {
            increaseColorSoldInCache(productId, colorId, quantity);
        }
        increaseProductSoldInCache(productId, quantity);
    }
}
/**
 * Creates a new cart and updates product quantities and sold counts accordingly.
 */
export const createCart = async (req: Request, res: Response) => {
    try {
        const { coupon_id, ...cartData } = req.body;

        await query('BEGIN');

        const cartResult = await query(
            'INSERT INTO carts (user_id, status, notes, full_name, governorate, city, address, closest_point, phone_number, type) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) RETURNING *',
            [cartData.user_id, 'Pending', cartData.notes, cartData.full_name, cartData.governorate, cartData.city, cartData.address, cartData.closest_point, cartData.phone_number, cartData.type]
        );
        const cart = cartResult.rows[0];

        if (coupon_id) {
            const couponResult = await query('SELECT * FROM coupons WHERE id = $1', [coupon_id]);
            const coupon = couponResult.rows[0];

            if (coupon && coupon.is_active) {
                if (coupon.usage_limit !== 0) {
                    await query('UPDATE coupons SET usage_limit = usage_limit - 1 WHERE id = $1', [coupon_id]);
                    const updatedCouponResult = await query('SELECT usage_limit FROM coupons WHERE id = $1', [coupon_id]);
                    if (updatedCouponResult.rows[0].usage_limit === 0) {
                        await query('UPDATE coupons SET is_active = false WHERE id = $1', [coupon_id]);
                    }
                }
                await query('UPDATE carts SET coupon_id = $1 WHERE id = $2', [coupon_id, cart.id]);
            } else {
                await query('ROLLBACK');
                return res.status(400).json({ message: 'Invalid or inactive coupon' });
            }
        }

        for (const item of cartData.items) {
            await query(
                'INSERT INTO cart_items (cart_id, product_id, size_id, color_id, quantity) VALUES ($1, $2, $3, $4, $5)',
                [cart.id, item.product_id, item.size_id, item.color_id, item.quantity]
            );

            // Decrease the product quantity
            await updateProductQuantity(item.product_id, item.size_id, item.color_id, item.quantity);

            // Increase the sold count based on product attributes
            await increaseSold(item.product_id, item.size_id, item.color_id, item.quantity);
        }

        await query('COMMIT');

        // Invalidate cart caches as data has changed
        flushAllCarts();

        res.status(201).json({ cart });
    } catch (error) {
        await query('ROLLBACK');
        console.error('Error creating cart:', error);
        res.status(500).json({ message: 'Error creating cart' });
    }
};

/**
 * Updates cart status.
 */
export async function updateCart(req: Request, res: Response, next: NextFunction) {
    try {
        const cartId = parseInt(req.params.id, 10);
        const { status } = req.body;

        const updatedCart = await updateCartStatus(cartId, status);

        if (!updatedCart) {
            return res.status(404).json({ message: 'Cart not found' });
        }

        // Invalidate specific cart cache if necessary
        if (updatedCart.user_id) {
            flushCartCache(updatedCart.user_id);
        }

        res.json({ message: 'Cart status updated successfully', cart: updatedCart });
    } catch (error) {
        next(error);
    }
}

/**
 * Deletes a cart by ID.
 */
export async function deleteCart(req: Request, res: Response, next: NextFunction) {
    try {
        const cartId = parseInt(req.params.id, 10);

        const deleted = await deleteCartById(cartId);

        if (!deleted) {
            return res.status(404).json({ message: 'Cart not found' });
        }

        // Invalidate all carts cache as data has changed
        flushAllCarts();

        res.json({ message: 'Cart deleted successfully' });
    } catch (error) {
        next(error);
    }
}

export async function getAllCarts(req: Request, res: Response, next: NextFunction) {
    try {
        const { page = 1, limit = 10 } = req.query;

        const pageNumber = parseInt(page as string, 10);
        const limitNumber = parseInt(limit as string, 10);

        const cacheKey = `allCarts_page_${pageNumber}_limit_${limitNumber}`;

        // Check if data is in cache
        const cachedCarts = cartCache.get(cacheKey);
        if (cachedCarts) {
            return res.json(cachedCarts);
        }

        const { carts, total } = await getAllCartsWithItemsPaginated(pageNumber, limitNumber);

        const response = { carts, total, page: pageNumber, limit: limitNumber };

        // Store in cache
        cartCache.set(cacheKey, response);

        res.json(response);
    } catch (error) {
        next(error);
    }
}
export async function getAllCartsWithItemsPaginated(page: number, limit: number) {
    const offset = (page - 1) * limit;

    const cartsResult = await query(
        `SELECT * FROM carts ORDER BY created_at DESC LIMIT $1 OFFSET $2`,
        [limit, offset]
    );
    const carts = cartsResult.rows;

    // Fetch total number of carts for pagination
    const totalResult = await query(`SELECT COUNT(*) FROM carts`);
    const total = parseInt(totalResult.rows[0].count, 10);

    // Fetch items for each cart
    for (const cart of carts) {
        const itemsResult = await query(
            `SELECT * FROM cart_items WHERE cart_id = $1`,
            [cart.id]
        );
        cart.items = itemsResult.rows;
    }

    return { carts, total };
}
Database:
import { Pool } from 'pg';
import bcrypt from 'bcrypt';
import dotenv from 'dotenv';

dotenv.config();

const pool = new Pool({
    host: 'ep-young-math-a2o7mpxf-pooler.eu-central-1.aws.neon.tech',
    database: 'ecommerce',
    user: 'ecommerce_owner',
    password: 'np8CZAUYKP3f',
    ssl: {
        rejectUnauthorized: false
    }
});

async function createTables(client: any): Promise<void> {
    try {
        await client.query('BEGIN');

        // 1. Create users table
        await client.query(`
            CREATE TABLE IF NOT EXISTS users (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMPTZ,
    is_active BOOLEAN DEFAULT true,
    role VARCHAR(50) NOT NULL DEFAULT 'user',
    dob DATE,
    phone_number VARCHAR(20),
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP);
        `);



        // 3. Create categories table
        await client.query(`
            CREATE TABLE IF NOT EXISTS categories (
                id SERIAL PRIMARY KEY,
                name VARCHAR(255) UNIQUE NOT NULL,
                is_brand BOOLEAN NOT NULL
            );
        `);

        // 4. Create subcategories table
        await client.query(`
            CREATE TABLE IF NOT EXISTS subcategories (
                id SERIAL PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                category_id INTEGER NOT NULL,
                FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE CASCADE,
                UNIQUE(name, category_id)
            );
        `);

        // 5. Create products table with foreign key to brands
        await client.query(`
            CREATE TABLE IF NOT EXISTS products (
                id SERIAL PRIMARY KEY,
                name TEXT NOT NULL,
                description TEXT NOT NULL,
                brand_id INTEGER NOT NULL,
                has_color BOOLEAN NOT NULL DEFAULT false,
                has_size BOOLEAN NOT NULL DEFAULT false,
                product_type VARCHAR(10) NOT NULL,
                price NUMERIC(10, 2) CHECK (price >= 0),
                qty INTEGER CHECK (qty >= 0),
                created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
                sold INTEGER NOT NULL DEFAULT 0,
                FOREIGN KEY (brand_id) REFERENCES subcategories (id) ON DELETE RESTRICT
                -- Changed from ON DELETE SET NULL to ON DELETE RESTRICT to maintain NOT NULL
            );
        `);

        // 6. Create colors table with foreign key to products
        await client.query(`
            CREATE TABLE IF NOT EXISTS colors (
                id SERIAL PRIMARY KEY,
                product_id INTEGER NOT NULL,
                name VARCHAR(255) NOT NULL,
                has_size BOOLEAN NOT NULL DEFAULT false,
                price NUMERIC(10,2) CHECK (price >= 0),
                qty INTEGER NOT NULL CHECK (qty >= 0),
                sold INTEGER NOT NULL DEFAULT 0,
                FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE,
                UNIQUE(name, product_id)
            );
        `);

        // 7. Create sizes table with foreign keys to products and colors
        await client.query(`
            CREATE TABLE IF NOT EXISTS sizes (
                id SERIAL PRIMARY KEY,
                product_id INTEGER NOT NULL,
                color_id INTEGER,
                name VARCHAR(255) NOT NULL,
                price NUMERIC(10,2) CHECK (price >= 0),
                qty INTEGER CHECK (qty >= 0),
                sold INTEGER NOT NULL DEFAULT 0,
                FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE,
                FOREIGN KEY (color_id) REFERENCES colors (id) ON DELETE SET NULL,
                UNIQUE(name, color_id, product_id)
            );
        `);

        // 8. Create images table with foreign keys to products, colors, and sizes
        await client.query(`
            CREATE TABLE IF NOT EXISTS images (
                id SERIAL PRIMARY KEY,
                product_id INTEGER NOT NULL,
                color_id INTEGER,
                size_id INTEGER,
                url VARCHAR(255));
        `);

        // 9. Create coupons table
        await client.query(`
            CREATE TABLE IF NOT EXISTS coupons (
                id SERIAL PRIMARY KEY,
                code VARCHAR(255) UNIQUE NOT NULL,
                name VARCHAR(255) NOT NULL,
                description VARCHAR(255),
                discount_amount NUMERIC(10,2),
                discount_percentage NUMERIC(5,2) CHECK (discount_percentage >= 0 AND discount_percentage <= 100),
                start_date DATE NOT NULL,
                end_date DATE NOT NULL,
                usage_limit INTEGER NOT NULL CHECK (usage_limit >= 0),
                min_purchase_amount NUMERIC(10,2) NOT NULL CHECK (min_purchase_amount >= 0),
                max_discount_amount NUMERIC(10,2) CHECK (max_discount_amount >= 0),
                is_active BOOLEAN DEFAULT true,
                coupon_image VARCHAR(255),
                created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
                updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL
            );
        `);

        // 10. Create address table
        await client.query(`
            CREATE TABLE IF NOT EXISTS address (
                id SERIAL PRIMARY KEY,
                user_id INTEGER NOT NULL,
                full_name VARCHAR(255) NOT NULL,
                governorate VARCHAR(255) NOT NULL,
                city VARCHAR(255) NOT NULL,
                address TEXT NOT NULL,
                closest_point TEXT,
                phone_number VARCHAR(80) NOT NULL,
                type VARCHAR(20),
                FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE
            );
        `);

        // 11. Create product_categories table with composite primary key
        await client.query(`
            CREATE TABLE IF NOT EXISTS product_categories (
                product_id INTEGER NOT NULL,
                category_id INTEGER NOT NULL,
                PRIMARY KEY (product_id, category_id),
                FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE,
                FOREIGN KEY (category_id) REFERENCES subcategories (id) ON DELETE CASCADE
            );
        `);

        // 12. Create carts table with foreign keys to users and coupons
        await client.query(`
            CREATE TABLE IF NOT EXISTS carts (
                id SERIAL PRIMARY KEY,
                user_id INTEGER,
                status VARCHAR(20) NOT NULL DEFAULT 'active',
                notes VARCHAR(255),
                full_name VARCHAR(255) NOT NULL,
                governorate VARCHAR(50) NOT NULL,
                city VARCHAR(100) NOT NULL,
                address TEXT NOT NULL,
                closest_point TEXT,
                phone_number VARCHAR(20) NOT NULL,
                type VARCHAR(20),
                created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
                coupon_id INTEGER,
                updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE SET NULL,
                FOREIGN KEY (coupon_id) REFERENCES coupons (id) ON DELETE SET NULL
            );
        `);

        // 13. Create cart_items table with foreign keys to carts, products, sizes, and colors
        await client.query(`
            CREATE TABLE IF NOT EXISTS cart_items (
                id SERIAL PRIMARY KEY,
                cart_id INTEGER NOT NULL,
                product_id INTEGER NOT NULL,
                size_id INTEGER,
                color_id INTEGER,
                quantity INTEGER NOT NULL CHECK (quantity > 0),
                FOREIGN KEY (cart_id) REFERENCES carts (id) ON DELETE CASCADE
            );
        `);
        await client.query(`
        CREATE TABLE "counter" (
    id SERIAL PRIMARY KEY
);
       `);
        await client.query(`ALTER SEQUENCE "counter_id_seq" RESTART WITH 3500`);
        await client.query(`INSERT INTO counter(id) VALUES(3500)`);

        const tables = ['users', 'categories', 'subcategories', 'products', 'colors', 'sizes', 'images', 'coupons', 'address', 'carts', 'cart_items'];
        for (const table of tables) {
            await client.query(`
                DO $$
                BEGIN
                    IF EXISTS (SELECT 1 FROM information_schema.sequences WHERE sequence_name = '${table}_id_seq') THEN
                        ALTER SEQUENCE ${table}_id_seq RESTART WITH 5000;
                    END IF;
                END $$;
            `);
        }

        // Update counter table
        await client.query(`DROP TABLE IF EXISTS Counter`);
        await client.query(`
            CREATE TABLE Counter (
                id SERIAL PRIMARY KEY
            );
        `);
        await client.query(`ALTER SEQUENCE Counter_id_seq RESTART WITH 5000`);
        await client.query(`INSERT INTO Counter(id) VALUES(5000)`);

        // Create color_images table
        await client.query(`
            CREATE TABLE IF NOT EXISTS color_images (
                name VARCHAR(255) PRIMARY KEY,
                url TEXT NOT NULL
            );
        `);

        // Foreign Key Indexes
        await client.query(`CREATE INDEX IF NOT EXISTS idx_products_brand_id ON products (brand_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_colors_product_id ON colors (product_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_sizes_product_id ON sizes (product_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_sizes_color_id ON sizes (color_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_product_categories_category_id ON product_categories (category_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_carts_user_id ON carts (user_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_carts_coupon_id ON carts (coupon_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_cart_items_cart_id ON cart_items (cart_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_address_user_id ON address (user_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_coupons_code ON coupons (code);`);

        // Commonly Searched Columns Indexes
        await client.query(`CREATE INDEX IF NOT EXISTS idx_products_name ON products (name);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_categories_name ON categories (name);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_subcategories_name ON subcategories (name);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_colors_name ON colors (name);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_sizes_name ON sizes (name);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_carts_status ON carts (status);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_coupons_name ON coupons (name);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_address_city ON address (city);`);

        // 15. Create Triggers for updated_at Columns
        // Function to update updated_at column
        await client.query(`
            CREATE OR REPLACE FUNCTION update_updated_at_column()
            RETURNS TRIGGER AS $$
               BEGIN
                   NEW.updated_at = NOW();
                   RETURN NEW;
               END;
               $$ LANGUAGE plpgsql;
        `);

        // Trigger for products table
        await client.query(`
            CREATE TRIGGER trg_update_products_updated_at
            BEFORE UPDATE ON products
            FOR EACH ROW
            EXECUTE PROCEDURE update_updated_at_column();
        `);

        // Trigger for carts table
        await client.query(`
            CREATE TRIGGER trg_update_carts_updated_at
            BEFORE UPDATE ON carts
            FOR EACH ROW
            EXECUTE PROCEDURE update_updated_at_column();
        `);

        // Trigger for coupons table
        await client.query(`
            CREATE TRIGGER trg_update_coupons_updated_at
            BEFORE UPDATE ON coupons
            FOR EACH ROW
            EXECUTE PROCEDURE update_updated_at_column();
        `);

        // Commit Transaction
        await client.query('COMMIT');
        console.log('All tables created successfully with enhanced constraints and indexes.');
    } catch (error) {
        await client.query('ROLLBACK');
        console.error('Error creating tables:', error);
        throw error;
    }
}
async function createDatabase(): Promise<void> {
    let client;
    try {
        client = await pool.connect();
        console.log('Connected to the database successfully');

        await createTables(client);

        // Check if superadmin already exists
        const superadminCheck = await client.query(
            "SELECT * FROM staff WHERE email = 'admin@example.com'"
        );

        if (superadminCheck.rows.length === 0) {
            // Insert sample superadmin
            const hashedPassword = await bcrypt.hash('admin123', 10);
            await client.query(`
                INSERT INTO staff (name, email, password, rank)
                VALUES ('Super Admin', 'admin@example.com', $1, 'superadmin');
            `, [hashedPassword]);

            console.log('Sample superadmin inserted successfully');
        } else {
            console.log('Superadmin already exists');
        }
        const subcategories = [
            'Shampoos', 'Conditioners', 'Dry Shampoos',
            'Hair Oils', 'Masks', 'Hair Mist',
            'Hair Brush', 'Hair Styling', 'Hair Treatments', 'Message Tools'
        ];

        for (const subcategory of subcategories) {
            await client.query(
                `INSERT INTO subcategories (name, category_id) VALUES ($1, $2) 
                ON CONFLICT DO NOTHING`,
                [subcategory, 3]
            );
        }
        await client.query(`
        CREATE TABLE IF NOT EXISTS products (
            id SERIAL PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            original_price DECIMAL(10, 2) NOT NULL,
            sale_price DECIMAL(10, 2),
            description TEXT,
            images TEXT[],
            color VARCHAR(50),
            color_image TEXT,
            size VARCHAR(50),
            qty INTEGER NOT NULL,
            category_id INTEGER REFERENCES categories(id),
            brand_id INTEGER REFERENCES categories(id)
        );
    `);
        console.log('Hair Care subcategories inserted successfully');

    } catch (err) {
        console.error('Error creating database:', err);
    } finally {
        if (client) {
            client.release();
        }
        await pool.end();
    }
}



createTables(pool).catch(console.error);
cacheHelper.ts:
// src/cacheHelpers.ts

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

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

            // Extract all prices 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) => {
    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');
    }
};