For each new purchased product...
if the product have size and have color or not then increase that size sold by 1... and ignore the color...
if the product doesn't have size and have color increase the color sold by 1
if the product doesn't have either increase product sold by 1.
cart:
// src/controllers/cartController.ts

import { Request, Response, NextFunction } from 'express';
import * as cartModel from '../models/cartModel';
import { query } from "../config/database";
import { deleteCartById, getAllCartsWithItems, updateCartStatus } from "../models/cartModel";
import {decreaseProductQtyInCache} from "../cacheHelper";

/**
 * Updates the quantity of a product, size, or color in the database and cache.
 * @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
 */
async function updateProductQuantity(productId: number, sizeId: number | null, colorId: number | null, quantity: number): Promise<void> {
    let updateQuery = '';
    const params = [quantity, productId];

    if (sizeId) {
        updateQuery = 'UPDATE sizes SET qty = qty - $1 WHERE id = $3 AND product_id = $2';
        params.push(sizeId);
    } else if (colorId) {
        updateQuery = 'UPDATE colors SET qty = qty - $1 WHERE id = $3 AND product_id = $2';
        params.push(colorId);
    } else {
        updateQuery = 'UPDATE products SET qty = qty - $1 WHERE id = $2';
    }

    // Update the database
    await query(updateQuery, params);

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

/**
 * Creates a new cart and updates product quantities 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]
            );

            await updateProductQuantity(item.product_id, item.size_id, item.color_id, item.quantity);
        }

        await query('COMMIT');

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

/**
 * Other cart controller functions remain unchanged.
 */
export async function getCart(req: Request, res: Response, next: NextFunction) {
    try {
        const userId = req.user?.id;
        if (!userId) {
            return res.status(401).json({ message: 'Unauthorized' });
        }

        const cartWithItems = await cartModel.getCartWithItemsByUserId(userId);
        if (!cartWithItems) {
            return res.status(404).json({ message: 'No cart found for the user' });
        }

        res.json(cartWithItems);
    } catch (error) {
        next(error);
    }
}

export async function getAllCarts(req: Request, res: Response, next: NextFunction) {
    try {
        const carts = await getAllCartsWithItems();
        res.json(carts);
    } catch (error) {
        next(error);
    }
}

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

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

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

        res.json({ message: 'Cart deleted successfully' });
    } catch (error) {
        next(error);
    }
}
productController.ts:
import { Request, Response } from 'express';
import {query} from "../config/database";
import {brandsCache, productCache} from "../cache";
import {
    Brand,
    BrandsCacheData,
    CacheData, CreateProductInput,
    Image,
    ImageInput,
    Product, Size,
    UpdateProductInput
} from "product";
import {addProductToCache, deleteProductFromCache, updateProductInCache} from "../cacheHelper";

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

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

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

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

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

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

            const product = productsMap[productId];

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

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

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

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

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

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

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

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

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

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

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

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

        const allProducts = Object.values(productsMap);

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

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

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

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

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

        // Cache the data with a consistent key
        const cacheData: CacheData = { allProducts, minPrice, maxPrice };
        productCache.set('allProducts', cacheData);
        console.log(`Cached ${allProducts.length} products with minPrice: ${minPrice} and maxPrice: ${maxPrice}.`);

        return cacheData;
    } catch (error) {
        console.error('Error loading products from DB:', error);
        throw error; // Propagate error to be handled by caller
    }
};


/**
 * Fetches all brands from the database and caches them.
 * @returns {Promise<BrandsCacheData>} A promise that resolves to the cached brand data.
 */
const loadAndCacheAllBrands = async (): Promise<BrandsCacheData> => {
    const q = `
        SELECT s.*, c.name as category_name 
        FROM subcategories s 
        JOIN categories c ON s.category_id = c.id AND c.is_brand
        ORDER BY c.name, s.name
    `;

    try {
        const result = await query(q, []);
        const allBrands: Brand[] = result.rows.map((row: any) => ({
            id: Number(row.id),
            name: row.name,
            category_id: Number(row.category_id),
            category_name: row.category_name,
        }));

        // Cache the brands
        const brandsCacheData: BrandsCacheData = { allBrands };
        brandsCache.set('allBrands', brandsCacheData);
        console.log(`Cached ${allBrands.length} brands successfully.`);

        return brandsCacheData;
    } catch (error) {
        console.error('Error loading brands from DB:', error);
        throw error; // Propagate error to be handled by caller
    }
};

/**
 * Controller to handle fetching and responding with filtered products.
 * Caches all products and brands on first request and performs in-memory filtering thereafter.
 * @param {Request} req - Express request object containing query parameters.
 * @param {Response} res - Express response object to send JSON data.
 */
export const getProducts = async (req: Request, res: Response): Promise<void> => {
    try {
        // Retrieve cached products
        let cacheData: CacheData | undefined = productCache.get('cacheData');

        if (!cacheData) {
            // Cache miss: Load from DB and cache
            cacheData = await loadAndCacheAllProducts();
            console.log('Loaded products from database and cached them.');
        } else {
            console.log('Loaded products from cache.');
        }

        const { allProducts, minPrice, maxPrice } = cacheData;

        // Log total products before any filtering
        console.log(`Total products before filtering: ${allProducts.length}`);

        // Extract and sanitize query parameters
        let {
            search = '',
            c,
            sc,
            b,
            minPriceA = '0',
            maxPriceA = '',
            availability = 'all',
            sortBy = '',
            page = '1',
            itemsPerPage = '15',
        } = req.query;

        // Parse numerical values
        const pageNum = parseInt(page as string, 10) || 1;
        const itemsPerPageNum = parseInt(itemsPerPage as string, 10) || 15;
        const minPriceANum = parseFloat(minPriceA as string) || 0;
        let maxPriceANum = maxPriceA ? parseFloat(maxPriceA as string) : Infinity;

        // If maxPriceA is less than minPriceA, reset to Infinity
        if (!isNaN(maxPriceANum) && maxPriceANum < minPriceANum) {
            console.warn('Provided maxPriceA is less than minPriceA. Resetting maxPriceANum to Infinity.');
            maxPriceANum = Infinity;
        } else if (isNaN(maxPriceANum)) {
            maxPriceANum = Infinity;
        }

        // Parse c, sc, b as numbers if present
        const cNum = c ? parseInt(c as string, 10) : undefined;
        const scNum = sc ? parseInt(sc as string, 10) : undefined;
        const bNum = b ? parseInt(b as string, 10) : undefined;

        // Initialize brand-related variables if 'b' is provided
        let allBrands: Brand[] = [];
        if (bNum !== undefined && !isNaN(bNum)) {
            // Retrieve cached brands
            let brandsCacheData: BrandsCacheData | undefined = brandsCache.get('allBrands');

            if (!brandsCacheData) {
                // Cache miss: Load from DB and cache
                brandsCacheData = await loadAndCacheAllBrands();
                console.log('Loaded brands from database and cached them.');
            } else {
                console.log('Loaded brands from cache.');
            }
            console.log(brandsCacheData)
            allBrands = brandsCacheData.allBrands;

            // Check if 'bNum' is a valid brand ID
            const isValidBrand = allBrands.some(brand => brand.category_id === bNum);
            if (!isValidBrand) {
                console.warn(`Brand ID ${bNum} is invalid.`);
                // Return zero products since 'b' is invalid
                res.json({
                    products: [],
                    total: 0,
                    page: pageNum,
                    itemsPerPage: itemsPerPageNum,
                    totalPages: 0,
                    hasMore: false,
                    minPrice,
                    maxPrice,
                });
                return;
            }
        }

        // Start with all products
        let filteredProducts = [...allProducts];

        // Apply Search Filter
        if (search && typeof search === 'string') {
            const searchLower = search.toLowerCase();
            const searchWords = searchLower.split(/\s+/).filter(Boolean);
            filteredProducts = filteredProducts.filter(product =>
                searchWords.every(word => product.name.toLowerCase().includes(word))
            );
        }

        // Apply Category Filter (c)
        if (cNum !== undefined && !isNaN(cNum)) {
            filteredProducts = filteredProducts.filter(product =>
                product.categories.some(cat => cat.main_category_id === cNum)
            );
        }

        // Apply Subcategory Filter (sc)
        if (scNum !== undefined && !isNaN(scNum)) {
            filteredProducts = filteredProducts.filter(product =>
                product.brand_id === scNum ||
                product.categories.some(cat => cat.id === scNum)
            );
        }

        // Apply Brand Filter (b)
        if (bNum !== undefined && !isNaN(bNum)) {
            console.log(`Applying Brand Filter: brand_id === ${bNum}`);
            const preFilterCount = filteredProducts.length;
            allBrands = allBrands.filter(brand => brand.category_id === bNum)
            filteredProducts = filteredProducts.filter(product =>
                allBrands.some(brand => brand.id === product.brand_id)
            );
            console.log(`Filtered from ${preFilterCount} to ${filteredProducts.length} products after applying brand filter.`);
        }

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

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

        // Log total after filtering
        console.log(`Total products after filtering: ${filteredProducts.length}`);

        // Apply Sorting
        if (sortBy && typeof sortBy === 'string') {
            switch (sortBy) {
                case 'Price: Low to High':
                    filteredProducts.sort((a, b) => {
                        const aMin = Math.min(...a.sizes.map(s => s.price));
                        const bMin = Math.min(...b.sizes.map(s => s.price));
                        return aMin - bMin;
                    });
                    break;
                case 'Price: High to Low':
                    filteredProducts.sort((a, b) => {
                        const aMax = Math.max(...a.sizes.map(s => s.price));
                        const bMax = Math.max(...b.sizes.map(s => s.price));
                        return bMax - aMax;
                    });
                    break;
                case 'Name: A to Z':
                    filteredProducts.sort((a, b) => a.name.localeCompare(b.name));
                    break;
                case 'Name: Z to A':
                    filteredProducts.sort((a, b) => b.name.localeCompare(a.name));
                    break;
                case 'Best Selling':
                    filteredProducts.sort((a, b) => b.sold - a.sold);
                    break;
                case 'Newest Arrivals':
                    filteredProducts.sort((a, b) => new Date(b.created_at).getTime() - new Date(a.created_at).getTime());
                    break;
                default:
                    // If sortBy parameter doesn't match any case, do not sort
                    break;
            }
        }

        // Apply Pagination
        const totalFilteredProducts = filteredProducts.length;
        const totalPages = Math.ceil(totalFilteredProducts / itemsPerPageNum);
        const startIdx = (pageNum - 1) * itemsPerPageNum;
        const endIdx = startIdx + itemsPerPageNum;
        const paginatedProducts = filteredProducts.slice(startIdx, endIdx);

        // Construct Response
        const response = {
            products: paginatedProducts,
            total: totalFilteredProducts,
            page: pageNum,
            itemsPerPage: itemsPerPageNum,
            totalPages,
            hasMore: endIdx < totalFilteredProducts,
            minPrice, // Global minPrice across all products
            maxPrice, // Global maxPrice across all products
        };

        // Send Response
        res.json(response);
    } catch (error) {
        console.error('Error fetching products:', error);
        res.status(500).json({ error: 'Internal server error' });
    } finally {
        // Optional: Any cleanup operations can be performed here
        // For example, monitoring memory usage or logging request completion
    }
};


export const getProductById = async (req: Request, res: Response) => {
    const { id } = req.params;
    try {
        const productQuery = `
            SELECT 
                p.id AS product_id,
                p.name AS product_name,
                p.description,
                p.brand_id,
                p.has_color,
                p.has_size,
                p.product_type,
                p.price AS product_price,
                p.qty AS product_qty,
                p.sold AS product_sold,
                p.created_at,
                p.updated_at,
                pc.category_id,
                sc.name AS category_name,
                cl.id AS color_id,
                cl.name AS color_name,
                cl.price AS color_price,
                cl.qty AS color_qty,
                cl.sold AS color_sold,
                s.id AS size_id,
                s.name AS size_name,
                s.price AS size_price,
                s.qty AS size_qty,
                s.sold AS size_sold,
                i.id AS image_id,
                i.url AS image_url
            FROM products p
            LEFT JOIN product_categories pc ON p.id = pc.product_id
            LEFT JOIN subcategories sc ON pc.category_id = sc.id
            LEFT JOIN colors cl ON p.id = cl.product_id
            LEFT JOIN sizes s ON (p.id = s.product_id AND (cl.id = s.color_id OR s.color_id IS NULL))
            LEFT JOIN images i ON (p.id = i.product_id AND (cl.id = i.color_id OR i.color_id IS NULL) AND (s.id = i.size_id OR i.size_id IS NULL))
            WHERE p.id = $1
            ORDER BY pc.category_id, cl.id, s.id, i.id
        `;

        const result = await query(productQuery, [id]);

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

        // Process the result to group related data
        let product: {
            sold: any;
            has_size: any;
            images: any[];
            description: any;
            created_at: any;
            colors: any[];
            brand_id: any;
            has_color: any;
            product_type: any;
            updated_at: any;
            sizes: any[];
            price: any;
            qty: any;
            name: any;
            id: number;
            categories: any[]
        };
        product = {
            id: result.rows[0].product_id,
            name: result.rows[0].product_name,
            description: result.rows[0].description,
            brand_id: result.rows[0].brand_id,
            has_color: result.rows[0].has_color,
            has_size: result.rows[0].has_size,
            product_type: result.rows[0].product_type,
            price: result.rows[0].product_price,
            qty: result.rows[0].product_qty,
            sold: result.rows[0].product_sold,
            created_at: result.rows[0].created_at,
            updated_at: result.rows[0].updated_at,
            categories: [],
            colors: [],
            sizes: [],
            images: []
        };

        for (const row of result.rows) {
            if (row.category_id && !product.categories.some(c => c.id === row.category_id)) {
                product.categories.push({
                    id: row.category_id,
                    name: row.category_name
                });
            }

            // Add color if not already added
            if (row.color_id && !product.colors.some(c => c.id === row.color_id)) {
                product.colors.push({
                    id: row.color_id,
                    name: row.color_name,
                    price: row.color_price,
                    qty: row.color_qty,
                    sold: row.color_sold,
                    images: [],
                    sizes: []
                });
            }

            // Add size if not already added
            if (row.size_id && !product.sizes.some(s => s.id === row.size_id)) {
                const newSize = {
                    id: row.size_id,
                    name: row.size_name,
                    price: row.size_price,
                    qty: row.size_qty,
                    sold: row.size_sold,
                    color_id: row.color_id,
                    // @ts-ignore
                    images: []
                };
                product.sizes.push(newSize);

                // Add size to the corresponding color
                if (row.color_id) {
                    const color = product.colors.find(c => c.id === row.color_id);
                    if (color && !color.sizes.some((s: { id: any; }) => s.id === row.size_id)) {
                        color.sizes.push(newSize);
                    }
                }
            }

            if (row.image_id && !product.images.some(i => i.id === row.image_id)) {
                const newImage = {
                    id: row.image_id,
                    url: row.image_url,
                    color_id: row.color_id,
                    size_id: row.size_id
                };
                product.images.push(newImage);

                if (row.color_id) {
                    const color = product.colors.find(c => c.id === row.color_id);
                    if (color && !color.images.includes(row.image_url)) {
                        color.images.push(row.image_url);
                    }
                }
                if (row.size_id) {
                    const size = product.sizes.find(s => s.id === row.size_id);
                    if (size && !size.images.includes(row.image_url)) {
                        size.images.push(row.image_url);
                    }
                }
            }
        }

        res.json({ product });
    } catch (error) {
        console.error('Error fetching product by ID:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
};
// Create a new product
export const createProduct = async (req: Request, res: Response) => {
    try {
        console.log("Received product data:", req.body); // Log incoming data
        await query('BEGIN');

        const productData: CreateProductInput = req.body;

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

        console.log("Inserting product with adjusted price:", adjustedPrice);

        // 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;
        console.log(`Inserted product ID: ${productId}`);

        // Insert category associations
        const categoryInsertPromises = productData.category_ids.map((categoryId: number) => {
            console.log(`Associating category ID ${categoryId} with product ID ${productId}`);
            return query(
                `INSERT INTO product_categories (product_id, category_id) 
                 VALUES ($1, $2)`,
                [productId, categoryId]
            );
        });
        await Promise.all(categoryInsertPromises);
        console.log('Inserted all category associations.');

        // Insert images
        const imagesInsertPromises = productData.images
            .filter((image: ImageInput) => image.url) // Ensure URL exists
            .map((image: ImageInput) => {
                console.log(`Inserting product image: ${image.url}`);
                return 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]
                );
            });
        const insertedImagesResults = await Promise.all(imagesInsertPromises);
        const insertedImages = insertedImagesResults.map((imgResult: any) => ({
            id: imgResult.rows[0].id,
            url: imgResult.rows[0].url,
            color_id: imgResult.rows[0].color_id,
            size_id: imgResult.rows[0].size_id
        }));
        console.log(`Inserted ${insertedImages.length} product images.`);

        // Handle colors and sizes based on product type
        const insertedColors: number[] = [];
        const insertedSizes: number[] = [];

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

                console.log(`Inserting color: ${color.name} with has_size: ${color.has_size}`);

                // 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;
                console.log(`Inserted color ID=${colorId}, has_size=${colorHasSize}`);

                insertedColors.push(colorId);

                // Insert color images
                const colorImagesInsertPromises = color.images.map((image: ImageInput) => {
                    console.log(`Inserting color image: ${image.url}`);
                    return 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]
                    );
                });
                const insertedColorImagesResults = await Promise.all(colorImagesInsertPromises);
                console.log(`Inserted ${insertedColorImagesResults.length} images for color ID=${colorId}`);

                if (color.has_size && color.sizes) {
                    for (const size of color.sizes) {
                        // Adjust size price if necessary
                        let adjustedSizePrice = size.price;
                        if (size.price < 1000) {
                            adjustedSizePrice = parseFloat((size.price * 1000).toFixed(2));
                        }

                        console.log(`Inserting size: ${size.name} with price: ${adjustedSizePrice}`);

                        // 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;
                        console.log(`Inserted size ID=${sizeId} for color ID=${colorId}`);
                        insertedSizes.push(sizeId);

                        // Insert size images
                        const sizeImagesInsertPromises = size.images.map((image: ImageInput) => {
                            console.log(`Inserting size image: ${image.url}`);
                            return 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]
                            );
                        });
                        const insertedSizeImagesResults = await Promise.all(sizeImagesInsertPromises);
                        console.log(`Inserted ${insertedSizeImagesResults.length} images for size ID=${sizeId}`);
                    }
                }
            }
        } else if (productData.has_size && productData.sizes) {
            for (const size of productData.sizes) {
                // Adjust size price if necessary
                let adjustedSizePrice = size.price;
                if (size.price < 1000) {
                    adjustedSizePrice = parseFloat((size.price * 1000).toFixed(2));
                }

                console.log(`Inserting standalone size: ${size.name} with price: ${adjustedSizePrice}`);

                // 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;
                console.log(`Inserted standalone size ID=${sizeId}`);
                insertedSizes.push(sizeId);

                // Insert size images
                const sizeImagesInsertPromises = size.images.map((image: ImageInput) => {
                    console.log(`Inserting standalone size image: ${image.url}`);
                    return query(
                        `INSERT INTO images (product_id, size_id, url)
                         VALUES ($1, $2, $3)
                         RETURNING id, color_id, size_id, url`,
                        [productId, sizeId, image.url]
                    );
                });
                const insertedSizeImagesResults = await Promise.all(sizeImagesInsertPromises);
                console.log(`Inserted ${insertedSizeImagesResults.length} images for standalone size ID=${sizeId}`);
            }
        }

        // 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: productData.category_ids.map((cid: number) => ({
                id: cid,
                name: "", // Optional: Fetch and populate category names
                main_category_id: 0, // Optional: Set appropriately
                main_category_name: "" // Optional: Set appropriately
            })),
            colors: productData.has_color && productData.colors ? productData.colors.map((color, index) => ({
                id: insertedColors[index],
                name: color.name,
                price: color.price || 0,
                qty: color.qty,
                sold: 0,
                has_size: Boolean(color.has_size),
                sizes: Boolean(color.has_size) && color.sizes ? color.sizes.map((size, sIndex) => ({
                    id: insertedSizes[sIndex],
                    name: size.name,
                    price: size.price,
                    qty: size.qty,
                    sold: 0,
                    color_id: null, // Optional: Set if needed
                    images: size.images.map(img => img.url)
                })) : [],
                images: color.images.map(img => img.url)
            })) : [],
            sizes: productData.has_size && !productData.has_color && productData.sizes ? productData.sizes.map((size, index) => ({
                id: insertedSizes[index],
                name: size.name,
                price: size.price,
                qty: size.qty,
                sold: 0,
                color_id: null,
                images: size.images.map(img => img.url)
            })) : [],
            images: insertedImages
        };

        console.log("New product to cache:", newProduct); // Log new product

        // 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' });
    }
};
/**
 * Update an existing product
 */
// src/controllers/productController.ts

// src/controllers/productController.ts

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

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

    try {
        await query('BEGIN');

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

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

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

        // Delete existing category associations
        await query('DELETE FROM product_categories WHERE product_id = $1', [productId]);

        // Determine if images are provided in the update
        const hasMainImagesInUpdate = productData.images !== undefined && productData.images !== null;

        // Handle Images
        let insertedImages: Image[] = [];

        if (hasMainImagesInUpdate) {
            // Delete existing main images
            await query('DELETE FROM images WHERE product_id = $1 AND color_id IS NULL AND size_id IS NULL', [productId]);

            // Insert new main images if provided
            if (productData.images.length > 0) {
                const imagesInsertPromises = productData.images.map((image: ImageInput) =>
                    query(
                        `INSERT INTO images (product_id, url, color_id, size_id)
                         VALUES ($1, $2, $3, $4)
                         RETURNING id, url, color_id, size_id`,
                        [productId, image.url, image.color_id || null, image.size_id || null]
                    )
                );

                const insertedImagesResults = await Promise.all(imagesInsertPromises);
                insertedImages = insertedImagesResults.map((imgResult: any) => ({
                    id: imgResult.rows[0].id,
                    url: imgResult.rows[0].url,
                    color_id: imgResult.rows[0].color_id,
                    size_id: imgResult.rows[0].size_id
                }));
            }
        } else {
            // If images are not provided, fetch existing main images
            const existingImagesResult = await query(
                `SELECT id, url, color_id, size_id FROM images 
                 WHERE product_id = $1 AND color_id IS NULL AND size_id IS NULL`,
                [productId]
            );

            insertedImages = existingImagesResult.rows.map((row: any) => ({
                id: row.id,
                url: row.url,
                color_id: row.color_id,
                size_id: row.size_id
            }));
        }

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

        // Insert or Update Colors and Sizes
        let insertedColors: number[] = [];
        let insertedSizes: number[] = [];

        if (productData.has_color && productData.colors && productData.colors.length > 0) {
            for (const colorInput of productData.colors) {
                // Adjust color price if necessary
                let adjustedColorPrice: number = colorInput.price;
                if (adjustedColorPrice < 1000) {
                    adjustedColorPrice = parseFloat((adjustedColorPrice * 1000).toFixed(2));
                }

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

                // Handle Color Images
                const hasColorImagesInUpdate = colorInput.images !== undefined && colorInput.images !== null;

                if (hasColorImagesInUpdate) {
                    // Delete existing images for this color
                    await query('DELETE FROM images WHERE product_id = $1 AND color_id = $2 AND size_id IS NULL', [productId, colorId]);

                    // Insert new images if provided
                    if (colorInput.images.length > 0) {
                        const colorImagesInsertPromises = colorInput.images.map((image: ImageInput) =>
                            query(
                                `INSERT INTO images (product_id, color_id, url, size_id)
                                 VALUES ($1, $2, $3, $4)
                                 RETURNING id, url, color_id, size_id`,
                                [productId, colorId, image.url, image.size_id || null]
                            )
                        );
                        await Promise.all(colorImagesInsertPromises);
// Optionally, store or handle color images if needed
                    }
                } else {
                    // Fetch existing color images
                    await query(
                        `SELECT id, url, color_id, size_id FROM images 
                         WHERE product_id = $1 AND color_id = $2 AND size_id IS NULL`,
                        [productId, colorId]
                    );
                }

                // Handle Sizes within Color
                if (colorInput.has_size && colorInput.sizes && colorInput.sizes.length > 0) {
                    for (const sizeInput of colorInput.sizes) {
                        // Adjust size price if necessary
                        let adjustedSizePrice: number = sizeInput.price;
                        if (adjustedSizePrice < 1000) {
                            adjustedSizePrice = parseFloat((adjustedSizePrice * 1000).toFixed(2));
                        }

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

                        // Handle Size Images
                        const hasSizeImagesInUpdate = sizeInput.images !== undefined && sizeInput.images !== null;

                        if (hasSizeImagesInUpdate) {
                            // Delete existing images for this size
                            await query('DELETE FROM images WHERE product_id = $1 AND size_id = $2', [productId, sizeId]);

                            // Insert new images if provided
                            if (sizeInput.images.length > 0) {
                                const sizeImagesInsertPromises = sizeInput.images.map((image: ImageInput) =>
                                    query(
                                        `INSERT INTO images (product_id, size_id, url)
                                         VALUES ($1, $2, $3)
                                         RETURNING id, url, color_id, size_id`,
                                        [productId, sizeId, image.url]
                                    )
                                );
                                await Promise.all(sizeImagesInsertPromises);
// Optionally, store or handle size images if needed
                            }
                        } else {
                            // Fetch existing size images
                            await query(
                                `SELECT id, url, color_id, size_id FROM images 
                                 WHERE product_id = $1 AND size_id = $2`,
                                [productId, sizeId]
                            );
                        }
                    }
                }
            }
        } else if (productData.has_size && productData.sizes && productData.sizes.length > 0) {
            for (const sizeInput of productData.sizes) {
                // Adjust size price if necessary
                let adjustedSizePrice: number = sizeInput.price;
                if (adjustedSizePrice < 1000) {
                    adjustedSizePrice = parseFloat((adjustedSizePrice * 1000).toFixed(2));
                }

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

                // Handle Size Images
                const hasSizeImagesInUpdate = sizeInput.images !== undefined && sizeInput.images !== null;

                if (hasSizeImagesInUpdate) {
                    // Delete existing images for this size
                    await query('DELETE FROM images WHERE product_id = $1 AND size_id = $2', [productId, sizeId]);

                    // Insert new images if provided
                    if (sizeInput.images.length > 0) {
                        const sizeImagesInsertPromises = sizeInput.images.map((image: ImageInput) =>
                            query(
                                `INSERT INTO images (product_id, size_id, url)
                                 VALUES ($1, $2, $3)
                                 RETURNING id, url, color_id, size_id`,
                                [productId, sizeId, image.url]
                            )
                        );
                        await Promise.all(sizeImagesInsertPromises);
// Optionally, store or handle size images if needed
                    }
                } else {
                    // Fetch existing size images

                }
            }
        }

        // Fetch and populate categories with details
        const categories = await fetchCategoryDetails(productData.category_ids);

        // Fetch existing images if not updated
        if (!hasMainImagesInUpdate) {
            const existingImagesResult = await query(
                `SELECT id, url, color_id, size_id FROM images 
                 WHERE product_id = $1 AND color_id IS NULL AND size_id IS NULL`,
                [productId]
            );

            insertedImages = existingImagesResult.rows.map((row: any) => ({
                id: row.id,
                url: row.url,
                color_id: row.color_id,
                size_id: row.size_id
            }));
        }

        // Construct the updated product object as it would appear in the cache
        const updatedProduct: 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: adjustedProductPrice,
            qty: productData.qty,
            sold: 0, // Assuming sold remains unchanged; adjust if necessary
            created_at: result.rows[0].created_at.toISOString(),
            updated_at: new Date().toISOString(),
            categories: categories,
            colors: productData.has_color && productData.colors
                ? productData.colors.map((colorInput, index) => ({
                    id: colorInput.id || insertedColors[index],
                    name: colorInput.name,
                    price: colorInput.price || 0,
                    qty: colorInput.qty,
                    sold: colorInput.sold || 0,
                    has_size: colorInput.has_size,
                    sizes: colorInput.has_size && colorInput.sizes
                        ? colorInput.sizes.map((sizeInput, sIndex) => ({
                            id: sizeInput.id || insertedSizes[sIndex],
                            name: sizeInput.name,
                            price: sizeInput.price,
                            qty: sizeInput.qty,
                            sold: sizeInput.sold || 0,
                            color_id: colorInput.id || insertedColors[index],
                            images: sizeInput.images ? sizeInput.images.map(img => img.url) : []
                        }))
                        : [],
                    images: colorInput.images ? colorInput.images.map(img => img.url) : []
                }))
                : [],
            sizes: productData.has_size && !productData.has_color && productData.sizes
                ? productData.sizes.map((sizeInput, index) => ({
                    id: sizeInput.id || insertedSizes[index],
                    name: sizeInput.name,
                    price: sizeInput.price,
                    qty: sizeInput.qty,
                    sold: sizeInput.sold || 0,
                    color_id: null,
                    images: sizeInput.images ? sizeInput.images.map(img => img.url) : []
                }))
                : [],
            images: insertedImages
        };

        // Update the cache
        updateProductInCache(updatedProduct);

        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 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' });
    }
};
product.ts:
// src/types/productInput.ts

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

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

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

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

export interface UpdateProductInput extends CreateProductInput {}

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

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

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

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

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

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

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

export interface BrandsCacheData {
    allBrands: Brand[];
}
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
            );
        `);



        // 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 CHECK (sold >= 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 CHECK (sold >= 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 CHECK (sold >= 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) NOT NULL,
                FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE,
                FOREIGN KEY (color_id) REFERENCES colors (id) ON DELETE SET NULL,
                FOREIGN KEY (size_id) REFERENCES sizes (id) ON DELETE SET NULL
            );
        `);

        // 9. Create coupons table
        await client.query(`
            CREATE TABLE IF NOT EXISTS coupons (
                id SERIAL PRIMARY KEY,
                code VARCHAR(20) 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(20) 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 categories (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,
                FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE,
                FOREIGN KEY (size_id) REFERENCES sizes (id) ON DELETE SET NULL,
                FOREIGN KEY (color_id) REFERENCES colors (id) ON DELETE SET NULL,
                UNIQUE(cart_id, product_id, size_id, color_id)
            );
        `);
        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)`);

        // 14. Enhance Tables with Indexes

        // 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_images_product_id ON images (product_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_images_color_id ON images (color_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_images_size_id ON images (size_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_cart_items_product_id ON cart_items (product_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_cart_items_size_id ON cart_items (size_id);`);
        await client.query(`CREATE INDEX IF NOT EXISTS idx_cart_items_color_id ON cart_items (color_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);