on createProduct, it should always add the images in products.images like this:
{"product":{"id":1289,"name":"Cantu Flaxseed Smoothing Shampoo","description":"شامبو للشعر بخلاصة بذور الكتان من كانتو\r\n-يعمل على تقليل التكسر وإصلاح التجاعيد التالفة\r\nو تقليل النفشه لإظهار جمال الشعر ولمعانه الطبيعي\r\n-يحتوي على تركيبة منتقاة بعناية\r\n-مصمم للشعر المجعد والمتعرج والمموج\r\n-يعزز نمو الشعر مع كل استخدام","brand_id":103,"has_color":false,"has_size":true,"product_type":"size","price":"0.00","qty":null,"sold":3,"created_at":"2024-09-30T19:32:15.254Z","updated_at":"2024-10-02T14:13:15.060Z","categories":[{"id":1,"name":"Shampoos","main_category_id":3,"main_category_name":"Hair Care"}],"colors":[],"sizes":[{"id":6010,"name":"400 ml","price":"15000.00","qty":1,"sold":0,"color_id":null,"images":[]}],"images":[{"id":6010,"url":"3122.jpeg","color_id":null,"size_id":null}]}}
id and url!
on updateProduct it should never change the id of the size or color!
import { Request, Response } from 'express';
import {query} from "../config/database";
import {brandsCache, productCache} from "../cache";
import {
    Brand,
    BrandsCacheData,
    CacheData, Category, Color, CreateProductInput,
    Image,
    ImageInput,
    Product, Size,
    UpdateProductInput
} from "product";
import {addProductToCache, deleteProductFromCache, updateProductInCache} from "../cacheHelper";

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

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

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

        // Temporary map to hold sizes that need to be assigned to colors
        const sizesMap: Record<number, Size> = {};

        // First pass: Initialize products and collect sizes
        result.rows.forEach((row: any) => {
            const productId = Number(row.id);

            // Initialize the product in the map if it doesn't exist
            if (!productsMap[productId]) {
                productsMap[productId] = {
                    id: productId,
                    name: row.name,
                    description: row.description,
                    brand_id: Number(row.brand_id),
                    has_color: Boolean(row.has_color),
                    has_size: Boolean(row.has_size),
                    product_type: row.product_type,
                    price: Number(row.price),
                    qty: Number(row.qty),
                    sold: Number(row.sold),
                    created_at: new Date(row.created_at).toISOString(),
                    updated_at: new Date(row.updated_at).toISOString(),
                    categories: [],
                    colors: [],
                    sizes: [], // Will be populated only if has_color is false
                    images: []
                };
            }

            const product = productsMap[productId];

            // Add category if present and not already added
            if (
                row.subcategory_id &&
                row.subcategory_name &&
                row.main_category_id &&
                row.main_category_name
            ) {
                const categoryId = Number(row.subcategory_id);
                const mainCategoryId = Number(row.main_category_id);
                const existingCategory = product.categories.find(cat => cat.id === categoryId);
                if (!existingCategory) {
                    product.categories.push({
                        id: categoryId,
                        name: row.subcategory_name,
                        main_category_id: mainCategoryId,
                        main_category_name: row.main_category_name,
                    });
                }
            }

            // Add color if present and not already added
            if (row.color_id && row.color_name && row.has_size !== undefined) {
                const colorId = Number(row.color_id);
                let color = product.colors.find(color => color.id === colorId);

                if (!color) {
                    color = {
                        id: colorId,
                        name: row.color_name,
                        price: Number(row.color_price),
                        qty: Number(row.color_qty),
                        sold: Number(row.color_sold),
                        has_size: Boolean(row.has_size),
                        sizes: [], // Will be populated if has_size is true
                        images: []
                    };
                    product.colors.push(color);
                }
            }

            // Collect sizes to assign later based on color association
            if (row.size_id && row.size_name) {
                const sizeId = Number(row.size_id);
                const colorId = row.color_id ? Number(row.color_id) : null;

                // If the size is associated with a color, store it for later assignment
                if (colorId && product.has_color) {
                    sizesMap[sizeId] = {
                        id: sizeId,
                        name: row.size_name,
                        price: Number(row.size_price),
                        qty: Number(row.size_qty),
                        sold: Number(row.size_sold),
                        color_id: colorId,
                        images: []
                    };
                } else if (!product.has_color && product.has_size) {
                    // If the product has sizes but no colors, assign size directly
                    if (!product.sizes.find(s => s.id === sizeId)) {
                        product.sizes.push({
                            id: sizeId,
                            name: row.size_name,
                            price: Number(row.size_price),
                            qty: Number(row.size_qty),
                            sold: Number(row.size_sold),
                            color_id: null,
                            images: []
                        });
                    }
                }
            }

            // Collect images to assign
            if (row.image_id && row.image_url) {
                const imageId = Number(row.image_id);
                // Avoid duplicate images
                if (!product.images.find(img => img.id === imageId)) {
                    product.images.push({
                        id: imageId,
                        url: row.image_url,
                        color_id: row.image_color_id ? Number(row.image_color_id) : null,
                        size_id: row.image_size_id ? Number(row.image_size_id) : null
                    });
                }

                // Associate image with color if applicable
                if (row.image_color_id) {
                    const colorId = Number(row.image_color_id);
                    const color = product.colors.find(c => c.id === colorId);
                    if (color && !color.images.includes(row.image_url)) {
                        color.images.push(row.image_url);
                    }
                }

                // Associate image with size if applicable and size is within a color
                if (row.image_size_id) {
                    const sizeId = Number(row.image_size_id);
                    const size = sizesMap[sizeId];
                    if (size) {
                        if (!size.images.includes(row.image_url)) {
                            size.images.push(row.image_url);
                        }
                    } else {
                        // If the product does not have colors, associate with product's sizes
                        const standaloneSize = product.sizes.find(s => s.id === Number(row.image_size_id));
                        if (standaloneSize && !standaloneSize.images.includes(row.image_url)) {
                            standaloneSize.images.push(row.image_url);
                        }
                    }
                }
            }
        });

        // Second pass: Assign sizes to their respective colors
        Object.values(productsMap).forEach(product => {
            if (product.has_color) {
                // Initialize all product.sizes as empty
                product.sizes = [];

                // Iterate over collected sizes and assign to colors
                Object.values(sizesMap).forEach(size => {
                    if (size.color_id === null) return; // Skip sizes without color_id

                    const color = product.colors.find(c => c.id === size.color_id);
                    if (color) {
                        color.sizes.push({
                            id: size.id,
                            name: size.name,
                            price: size.price,
                            qty: size.qty,
                            sold: size.sold,
                            color_id: size.color_id,
                            images: size.images
                        });
                    }
                });

                // Update has_size for each color based on whether it has sizes
                product.colors.forEach(color => {
                    color.has_size = color.sizes.length > 0;
                });
            }
            // If has_color is false, sizes are already assigned directly to product.sizes
        });

        const allProducts = Object.values(productsMap);

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

            // Prices from colors and their sizes
            product.colors.forEach(color => {
                if (color.price < minPrice) minPrice = color.price;
                if (color.price > maxPrice) maxPrice = color.price;

                color.sizes.forEach(size => {
                    if (size.price < minPrice) minPrice = size.price;
                    if (size.price > maxPrice) maxPrice = size.price;
                });
            });

            // Prices from standalone sizes
            product.sizes.forEach(size => {
                if (size.price < minPrice) minPrice = size.price;
                if (size.price > maxPrice) maxPrice = size.price;
            });
        });

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

        // Cache the data with a consistent key
        const cacheData: CacheData = { allProducts, minPrice, maxPrice };
        productCache.set('allProducts', cacheData);

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

        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('allProducts');

        if (!cacheData) {
            // Cache miss: Load from DB and cache
            cacheData = await loadAndCacheAllProducts();
        } else {
        }

        const { allProducts, minPrice, maxPrice } = cacheData;

        // Log total products before any filtering

        // 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();
            } else {
            }
            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;
            }
        }

        // Determine if any filters are applied
        const hasFilters = !!search || !!cNum || !!scNum || !!bNum ||
            minPriceANum > 0 || maxPriceANum < Infinity ||
            (availability !== 'all');

        // 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)) {
            const preFilterCount = filteredProducts.length;
            allBrands = allBrands.filter(brand => brand.category_id === bNum)
            filteredProducts = filteredProducts.filter(product =>
                allBrands.some(brand => brand.id === product.brand_id)
            );
        }

        // Apply Price Range Filter
        if (minPriceANum > 0 || maxPriceANum < Infinity) {
            filteredProducts = filteredProducts.filter(product => {
                const productPrices = product.sizes.map(size => size.price);
                if (productPrices.length === 0) return false;
                const productMinPrice = Math.min(...productPrices);
                const productMaxPrice = Math.max(...productPrices);
                return productMinPrice >= minPriceANum && productMaxPrice <= maxPriceANum;
            });
        }

        // Apply Availability Filter
        if (availability === 'in_stock') {
            filteredProducts = filteredProducts.filter(product =>
                product.sizes.some(size => size.qty > 0)
            );
        } else if (availability === 'out_of_stock') {
            filteredProducts = filteredProducts.filter(product =>
                product.sizes.every(size => size.qty === 0)
            );
        }

        // Log total after filtering

        // 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;
            }
        } else if (!hasFilters) {
            // If no filters are applied, sort by created_at descending to show latest products
            filteredProducts.sort((a, b) => new Date(b.created_at).getTime() - new Date(a.created_at).getTime());
        }

        // 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;
    const productId = parseInt(id, 10);

    if (isNaN(productId)) {
        return res.status(400).json({ error: 'Invalid product ID' });
    }

    try {
        // Attempt to retrieve the cached products
        let cacheData = productCache.get('allProducts') as CacheData | undefined;

        if (cacheData) {
        } else {
            // Cache miss: Load all products and cache them
            cacheData = await loadAndCacheAllProducts();
        }

        // Find the product in the cached data
        const cachedProduct = cacheData.allProducts.find(product => product.id === productId);

        if (cachedProduct) {
            return res.json({ product: cachedProduct });
        } else {
            // Fallback: Fetch the product from the database
            const productFromDb = await fetchProductByIdFromDb(productId);

            if (productFromDb) {
                // Add the fetched product to the cache
                addProductToCache(productFromDb);
                return res.json({ product: productFromDb });
            } else {
                // Product not found in the database
                return res.status(404).json({ error: 'Product not found' });
            }
        }
    } catch (error) {
        console.error('Error fetching product by ID:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
};

/**
 * Fetches a single product by ID directly from the database.
 * @param productId - ID of the product to fetch.
 * @returns {Promise<Product | null>} The product if found, else null.
 */
const fetchProductByIdFromDb = async (productId: number): Promise<Product | null> => {
    const productQuery = `
        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,
            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 categories c ON sc.category_id = c.id
        LEFT JOIN colors cl ON p.id = cl.product_id
        LEFT JOIN sizes s ON (p.id = s.product_id 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
    `;

    try {
        const result = await query(productQuery, [productId]);

        if (result.rows.length === 0) {
            return null;
        }

        // Process the result to construct the Product object
        let product: Product = {
            id: result.rows[0].id,
            name: result.rows[0].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].price,
            qty: result.rows[0].qty,
            sold: result.rows[0].sold,
            created_at: new Date(result.rows[0].created_at).toISOString(),
            updated_at: new Date(result.rows[0].updated_at).toISOString(),
            categories: [],
            colors: [],
            sizes: [],
            images: []
        };

        // Iterate through the rows to populate categories, colors, sizes, and images
        for (const row of result.rows) {
            // Categories
            if (row.subcategory_id && row.subcategory_name && row.main_category_id && row.main_category_name) {
                const existingCategory = product.categories.find(cat => cat.id === row.subcategory_id);
                if (!existingCategory) {
                    product.categories.push({
                        id: row.subcategory_id,
                        name: row.subcategory_name,
                        main_category_id: row.main_category_id,
                        main_category_name: row.main_category_name,
                    });
                }
            }

            // Colors
            if (row.color_id && row.color_name) {
                let color = product.colors.find(c => c.id === row.color_id);
                if (!color) {
                    color = {
                        id: row.color_id,
                        name: row.color_name,
                        price: row.color_price,
                        qty: row.color_qty,
                        sold: row.color_sold,
                        has_size: row.has_size,
                        sizes: [],
                        images: []
                    };
                    product.colors.push(color);
                }
            }

            // Sizes
            if (row.size_id && row.size_name) {
                const size: Size = {
                    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 || null,
                    images: []
                };

                // Associate size with color if applicable
                if (row.color_id) {
                    const color = product.colors.find(c => c.id === row.color_id);
                    if (color) {
                        const existingSize = color.sizes.find(s => s.id === size.id);
                        if (!existingSize) {
                            color.sizes.push(size);
                        }
                    }
                } else if (product.has_size) {
                    const existingSize = product.sizes.find(s => s.id === size.id);
                    if (!existingSize) {
                        product.sizes.push(size);
                    }
                }
            }

            // Images
            if (row.image_id && row.image_url) {
                const image = {
                    id: row.image_id,
                    url: row.image_url,
                    color_id: row.color_id || null,
                    size_id: row.size_id || null
                };

                // Avoid duplicate images
                if (!product.images.some(img => img.id === image.id)) {
                    product.images.push(image);
                }

                // Associate image with color if applicable
                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);
                    }
                }

                // Associate image with size if applicable
                if (row.size_id) {
                    if (row.color_id) {
                        const color = product.colors.find(c => c.id === row.color_id);
                        const size = color?.sizes.find(s => s.id === row.size_id);
                        if (size && !size.images.includes(row.image_url)) {
                            size.images.push(row.image_url);
                        }
                    } else {
                        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);
                        }
                    }
                }
            }
        }

        return product;
    } catch (error) {
        console.error('Error fetching product from DB:', error);
        throw error; // Let the caller handle the error
    }
};

export const createProduct = async (req: Request, res: Response) => {
    try {

        const productData: CreateProductInput = req.body;

        // Adjust price if necessary
        let adjustedPrice = productData.price;
        if (productData.price < 1000) {
            adjustedPrice = parseFloat((productData.price * 1000).toFixed(2));
        }


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

        // Fetch category details
        const categoriesResult = await query(
            `SELECT sc.id AS subcategory_id, sc.name AS subcategory_name,
                    c.id AS main_category_id, c.name AS main_category_name
             FROM product_categories pc
             JOIN subcategories sc ON pc.category_id = sc.id
             JOIN categories c ON sc.category_id = c.id
             WHERE pc.product_id = $1`,
            [productId]
        );

        const categories: Category[] = categoriesResult.rows.map((row: any) => ({
            id: row.subcategory_id,
            name: row.subcategory_name,
            main_category_id: row.main_category_id,
            main_category_name: row.main_category_name
        }));

        // Insert category associations
        if (productData.category_ids && productData.category_ids.length > 0) {
            const categoryInsertPromises = productData.category_ids.map((categoryId: number) => {
                return query(
                    `INSERT INTO product_categories (product_id, category_id) 
                     VALUES ($1, $2)`,
                    [productId, categoryId]
                );
            });
            await Promise.all(categoryInsertPromises);
        }

        // Insert images
        const insertedImages: Image[] = [];
        if (productData.images && productData.images.length > 0) {
            const imagesInsertPromises = productData.images
                .filter((image: ImageInput) => image.url) // Ensure URL exists
                .map(async (image: ImageInput) => {
                    const imgResult = await query(
                        `INSERT INTO images (product_id, url, color_id, size_id)
                         VALUES ($1, $2, $3, $4)
                         RETURNING id, color_id, size_id, url`,
                        [productId, image.url, image.color_id || null, image.size_id || null]
                    );
                    return {
                        id: imgResult.rows[0].id,
                        url: imgResult.rows[0].url,
                        color_id: imgResult.rows[0].color_id,
                        size_id: imgResult.rows[0].size_id
                    } as Image;
                });
            const insertedImagesResults = await Promise.all(imagesInsertPromises);
            insertedImages.push(...insertedImagesResults);
        }

        // Handle colors and sizes based on product type
        const insertedColors: { [key: number]: Color } = {}; // Map color index to Color
        const insertedSizes: { [key: number]: Size } = {}; // Map size index to Size

        if (productData.has_color && productData.colors && productData.colors.length > 0) {
            for (const [colorIndex, color] of productData.colors.entries()) {
                // Adjust color price if necessary
                let adjustedColorPrice = color.price;
                if (color.price < 1000) {
                    adjustedColorPrice = parseFloat((color.price * 1000).toFixed(2));
                }


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


                insertedColors[colorIndex] = {
                    id: colorId,
                    name: color.name,
                    price: adjustedColorPrice,
                    qty: color.qty,
                    sold: 0,
                    has_size: colorHasSize,
                    sizes: [],
                    images: []
                };

                // Insert color images
                if (color.images && color.images.length > 0) {
                    const colorImagesInsertPromises = color.images.map(async (image: ImageInput) => {
                        const colorImgResult = await query(
                            `INSERT INTO images (product_id, color_id, url, size_id)
                             VALUES ($1, $2, $3, $4)
                             RETURNING id, color_id, size_id, url`,
                            [productId, colorId, image.url, image.size_id || null]
                        );
                        return colorImgResult.rows[0].url;
                    });
                    const insertedColorImages = await Promise.all(colorImagesInsertPromises);
                    insertedColors[colorIndex].images.push(...insertedColorImages);
                }

                // Insert sizes for the color
                if (color.has_size && color.sizes && color.sizes.length > 0) {
                    for (const [sizeIndex, size] of color.sizes.entries()) {
                        // Adjust size price if necessary
                        let adjustedSizePrice = size.price;
                        if (size.price < 1000) {
                            adjustedSizePrice = parseFloat((size.price * 1000).toFixed(2));
                        }


                        // Insert size
                        const sizeResult = await query(
                            `INSERT INTO sizes (product_id, color_id, name, price, qty, sold)
                             VALUES ($1, $2, $3, $4, $5, 0)
                             RETURNING id`,
                            [productId, colorId, size.name, adjustedSizePrice, size.qty]
                        );
                        const sizeId = sizeResult.rows[0].id;

                        insertedSizes[size.id] = {
                            id: sizeId,
                            name: size.name,
                            price: adjustedSizePrice,
                            qty: size.qty,
                            sold: 0,
                            color_id: colorId,
                            images: []
                        };

                        // Insert size images
                        if (size.images && size.images.length > 0) {
                            const sizeImagesInsertPromises = size.images.map(async (image: ImageInput) => {
                                const sizeImgResult = await query(
                                    `INSERT INTO images (product_id, color_id, size_id, url)
                                     VALUES ($1, $2, $3, $4)
                                     RETURNING id, color_id, size_id, url`,
                                    [productId, colorId, sizeId, image.url]
                                );
                                return sizeImgResult.rows[0].url;
                            });
                            const insertedSizeImages = await Promise.all(sizeImagesInsertPromises);
                            insertedSizes[size.id].images.push(...insertedSizeImages);
                        }

                        // Assign size to color
                        insertedColors[colorIndex].sizes.push(insertedSizes[size.id]);
                    }
                }
            }
        }

        // Handle standalone sizes (if has_size and no colors)
        const standaloneSizes: Size[] = [];
        if (productData.has_size && (!productData.has_color || (productData.colors && productData.colors.length === 0)) && productData.sizes && productData.sizes.length > 0) {
            for (const [sizeIndex, size] of productData.sizes.entries()) {
                // Adjust size price if necessary
                let adjustedSizePrice = size.price;
                if (size.price < 1000) {
                    adjustedSizePrice = parseFloat((size.price * 1000).toFixed(2));
                }


                // Insert size
                const sizeResult = await query(
                    `INSERT INTO sizes (product_id, name, price, qty, sold)
                     VALUES ($1, $2, $3, $4, 0)
                     RETURNING id`,
                    [productId, size.name, adjustedSizePrice, size.qty]
                );
                const sizeId = sizeResult.rows[0].id;

                const newSize: Size = {
                    id: sizeId,
                    name: size.name,
                    price: adjustedSizePrice,
                    qty: size.qty,
                    sold: 0,
                    color_id: null,
                    images: []
                };

                // Insert size images
                if (size.images && size.images.length > 0) {
                    const sizeImagesInsertPromises = size.images.map(async (image: ImageInput) => {
                        const sizeImgResult = await query(
                            `INSERT INTO images (product_id, size_id, url)
                             VALUES ($1, $2, $3)
                             RETURNING id, color_id, size_id, url`,
                            [productId, sizeId, image.url]
                        );
                        return sizeImgResult.rows[0].url;
                    });
                    const insertedSizeImages = await Promise.all(sizeImagesInsertPromises);
                    newSize.images.push(...insertedSizeImages);
                }

                standaloneSizes.push(newSize);
            }
        }

        // Construct the new product object as it would appear in the cache
        const newProduct: Product = {
            id: productId,
            name: productData.name,
            description: productData.description,
            brand_id: productData.brand_id,
            has_color: productData.has_color,
            has_size: productData.has_size,
            product_type: productData.product_type,
            price: adjustedPrice,
            qty: productData.qty,
            sold: 0,
            created_at: productResult.rows[0].created_at.toISOString(),
            updated_at: productResult.rows[0].updated_at.toISOString(),
            categories: categories,
            colors: Object.values(insertedColors),
            sizes: standaloneSizes,
            images: insertedImages
        };


        // Update the cache
        addProductToCache(newProduct);

        await query('COMMIT');
        res.status(201).json({ message: 'Product added successfully!', productId });
    } catch (error) {
        await query('ROLLBACK');
        console.error('Error creating product:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
};
export const updateProduct = async (req: Request, res: Response) => {
    const { id } = req.params;
    const productId = parseInt(id, 10);
    const productData: UpdateProductInput = req.body;

    if (isNaN(productId)) {
        return res.status(400).json({ error: 'Invalid product ID' });
    }

    try {
        await query('BEGIN');

        let adjustedPrice = productData.price;
        if (productData.price < 1000) {
            adjustedPrice = parseFloat((productData.price * 1000).toFixed(2));
        }

        const existingProductResult = await query('SELECT sold FROM products WHERE id = $1', [productId]);
        const existingSold = existingProductResult.rows[0]?.sold || 0;

        const result = await query(
            `UPDATE products 
             SET name = $1, 
                 description = $2, 
                 brand_id = $3, 
                 has_color = $4, 
                 has_size = $5, 
                 product_type = $6, 
                 price = $7, 
                 qty = $8, 
                 updated_at = CURRENT_TIMESTAMP 
             WHERE id = $9 
             RETURNING *`,
            [
                productData.name,
                productData.description,
                productData.brand_id,
                productData.has_color,
                productData.has_size,
                productData.product_type,
                adjustedPrice,
                productData.qty,
                productId
            ]
        );

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

        const updatedProduct = result.rows[0];

        await query('DELETE FROM product_categories WHERE product_id = $1', [productId]);

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




        if (productData.images && productData.images.length > 0) {  // Only delete and re-insert images if provided
            await query('DELETE FROM images WHERE product_id = $1', [productId]);
            const imagesInsertPromises = productData.images.map((image: ImageInput) =>
                query(
                    `INSERT INTO images (product_id, url, color_id, size_id)
                     VALUES ($1, $2, $3, $4)`,
                    [productId, image.url, image.color_id || null, image.size_id || null]
                )
            );
            await Promise.all(imagesInsertPromises);
        }



        // Colors and Sizes
        await query('DELETE FROM colors WHERE product_id = $1', [productId]); // Delete all existing colors
        await query('DELETE FROM sizes WHERE product_id = $1', [productId]); // Delete all existing sizes


        if (productData.has_color) {
            for (const color of productData.colors) {
                let adjustedColorPrice = color.price;
                if (color.price < 1000) {
                    adjustedColorPrice = parseFloat((color.price * 1000).toFixed(2));
                }

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

                const colorId = colorResult.rows[0].id;

                if (color.has_size) {
                    for (const size of color.sizes) {

                        let adjustedSizePrice = size.price;
                        if (size.price < 1000) {
                            adjustedSizePrice = parseFloat((size.price * 1000).toFixed(2));
                        }

                        await query(
                            `INSERT INTO sizes (product_id, color_id, name, price, qty, sold) 
                             VALUES ($1, $2, $3, $4, $5, $6)`,
                            [productId, colorId, size.name, adjustedSizePrice, size.qty, size.sold || 0]
                        );
                    }
                }
            }
        } else if (productData.has_size) {

            for (const size of productData.sizes) {
                let adjustedSizePrice = size.price;

                if (size.price < 1000) {
                    adjustedSizePrice = parseFloat((size.price * 1000).toFixed(2));
                }

                await query(
                    `INSERT INTO sizes (product_id, name, price, qty, sold) 
                     VALUES ($1, $2, $3, $4, $5)`,
                    [productId, size.name, adjustedSizePrice, size.qty, size.sold || 0]
                );

            }
        }



        // Cache Update
        const productQuery = `
            SELECT *  FROM products WHERE id = $1
        `;
        const productResult = await query(productQuery, [productId]);
        const productRow = productResult.rows[0];


        const categoryDetails = await fetchCategoryDetails(productData.category_ids)

        const cachedProduct: Product = {
            id: productRow.id,
            name: productRow.name,
            description: productRow.description,
            brand_id: productRow.brand_id,
            has_color: productRow.has_color,
            has_size: productRow.has_size,
            product_type: productRow.product_type,
            price: productRow.price,
            qty: productRow.qty,
            sold: existingSold,
            created_at: productRow.created_at,
            updated_at: productRow.updated_at,
            categories: categoryDetails,
            colors: [],
            sizes: [],
            images: [] // Initialize as empty, will be populated below
        };

        const imagesResult = await query('SELECT * FROM images WHERE product_id = $1', [productId]);
        cachedProduct.images = imagesResult.rows.map((row: any): Image => ({ // Explicitly type as Image[]
            id: row.id,
            url: row.url,
            color_id: row.color_id,
            size_id: row.size_id,
        }));

        if (cachedProduct.has_color) {
            const colorsResult = await query('SELECT * FROM colors WHERE product_id = $1', [productId]);

            cachedProduct.colors = await Promise.all(colorsResult.rows.map(async (colorRow: any) => {
                const color: Color = {
                    id: colorRow.id,
                    name: colorRow.name,
                    price: colorRow.price,
                    qty: colorRow.qty,
                    sold: colorRow.sold,
                    has_size: colorRow.has_size,
                    images: cachedProduct.images.filter((img) => img.color_id === colorRow.id).map(img => img.url),
                    sizes: []
                };

                if (color.has_size) { // Using color.has_size
                    const sizesResult = await query('SELECT * FROM sizes WHERE color_id = $1', [colorRow.id]);
                    color.sizes = sizesResult.rows.map((sizeRow: any): Size => ({
                        id: sizeRow.id,
                        name: sizeRow.name,
                        price: sizeRow.price,
                        qty: sizeRow.qty,
                        sold: sizeRow.sold,
                        color_id: sizeRow.color_id,
                        images: cachedProduct.images.filter((img) => img.size_id === sizeRow.id).map(img => img.url),
                    }));
                }

                return color;
            }));
        }

        if (cachedProduct.has_size && !cachedProduct.has_color) {
            const sizesResult = await query('SELECT * FROM sizes WHERE product_id = $1 AND color_id IS NULL', [productId]);
            cachedProduct.sizes = sizesResult.rows.map((sizeRow: any): Size => ({
                id: sizeRow.id,
                name: sizeRow.name,
                price: sizeRow.price,
                qty: sizeRow.qty,
                sold: sizeRow.sold,
                color_id: null,
                images: cachedProduct.images.filter((img) => img.size_id === sizeRow.id).map(img => img.url),
            }));
        } else {
            cachedProduct.sizes = []; // Important: Initialize to empty array if no standalone sizes
        }



        updateProductInCache(cachedProduct);


        await query('COMMIT');
        res.json({ message: 'Product updated successfully', product: updatedProduct });
    } catch (error) {
        await query('ROLLBACK');
        console.error('Error updating product:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
};
export const getTopSellingProducts = async (req: Request, res: Response): Promise<void> => {
    try {
        // Retrieve cached products
        let cacheData: CacheData | undefined = productCache.get('allProducts');

        if (!cacheData) {
            // Cache miss: Load from DB and cache
            cacheData = await loadAndCacheAllProducts();
        } else {
        }

        const { allProducts } = cacheData;

        // Function to compute minimum price excluding 0
        const getMinPrice = (product: Product): number | null => {
            let prices: number[] = [];

            // Include product base price if >0
            if (product.price > 0) prices.push(product.price);

            // Include colors' prices
            if (product.has_color) {
                product.colors.forEach(color => {
                    if (color.price > 0) prices.push(color.price);

                    // Include sizes' prices within colors
                    if (color.has_size) {
                        color.sizes.forEach(size => {
                            if (size.price > 0) prices.push(size.price);
                        });
                    }
                });
            }

            // Include standalone sizes' prices
            if (product.has_size) {
                product.sizes.forEach(size => {
                    if (size.price > 0) prices.push(size.price);
                });
            }

            if (prices.length === 0) return null;

            return Math.min(...prices);
        };

        // Function to get one image URL (first available)
        const getOneImage = (product: Product): string | null => {
            if (product.images && product.images.length > 0) {
                return product.images[0].url; // Assuming each image has a 'url' property
            }
            return null; // Or return a placeholder image URL if desired
        };

        // Filter products that are in stock and have a valid min price
        const filteredProducts = allProducts
            .map(product => {
                // Determine if the product is in stock
                const isInStock = product.qty > 0 ||
                    (product.has_color && product.colors.some(color => color.qty > 0 ||
                        (color.has_size && color.sizes.some(size => size.qty > 0)))) ||
                    (product.has_size && product.sizes.some(size => size.qty > 0));

                // Calculate min price
                const minPrice = getMinPrice(product);

                // Get one image
                const image = getOneImage(product);

                return {
                    id: product.id,
                    title: product.name,
                    description: product.description,
                    sold: Math.abs(product.sold), // Ensure sold is absolute
                    isInStock,
                    minPrice,
                    image
                };
            })
            .filter(product => product.isInStock && product.minPrice !== null && product.minPrice > 0);

        if (filteredProducts.length === 0) {
             res.status(200).json({ products: [] });
        }

        // Sort the products by sold in descending order using absolute sold values
        filteredProducts.sort((a, b) => b.sold - a.sold);

        // Select the top two products
        const topTwoProducts = filteredProducts.slice(0, 2).map(product => ({
            id: product.id,
            title: product.title,
            description: product.description,
            minPrice: product.minPrice as number,
            image: product.image
        }));

        res.status(200).json({ products: topTwoProducts });
    } catch (error) {
        console.error('Error fetching top selling products:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
};


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

    if (isNaN(productId)) {
        return res.status(400).json({ error: 'Invalid product ID' });
    }

    try {
        await query('BEGIN');

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

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

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

        // Delete the product from the cache
        deleteProductFromCache(productId);

        await query('COMMIT');
        res.json({ message: 'Product and related data deleted successfully' });
    } catch (error) {
        await query('ROLLBACK');
        console.error('Error deleting product:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
};
createDatabase.ts:
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/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');
    }
};