why is this sooo slow?? actually it takes forever to finish??
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');

        // Lock the product row to prevent concurrent updates
        const productLockResult = await query(
            'SELECT * FROM products WHERE id = $1 FOR UPDATE',
            [productId]
        );
        if (productLockResult.rows.length === 0) {
            await query('ROLLBACK');
            return res.status(404).json({error: 'Product not found'});
        }

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

        // Update product details without altering 'sold'
        const updatedProductResult = 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 (updatedProductResult.rows.length === 0) {
            await query('ROLLBACK');
            return res.status(404).json({error: 'Product not found'});
        }

        // Handle category associations
        // Delete existing category associations
        const deleteCategoriesResult = await query(
            'DELETE FROM product_categories WHERE product_id = $1',
            [productId]
        );
        console.log(`Deleted ${deleteCategoriesResult.rowCount} existing category associations for product_id ${productId}.`);

        // Ensure category_ids are unique to prevent duplicates
        const uniqueCategoryIds = Array.from(new Set(productData.category_ids));

        // Insert unique category associations
        if (Array.isArray(uniqueCategoryIds) && uniqueCategoryIds.length > 0) {
            const categoryInsertPromises = uniqueCategoryIds.map((categoryId: number) =>
                query(
                    `INSERT INTO product_categories (product_id, category_id) 
                     VALUES ($1, $2)
                     ON CONFLICT DO NOTHING`,
                    [productId, categoryId]
                )
            );
            await Promise.all(categoryInsertPromises);
            console.log(`Inserted ${uniqueCategoryIds.length} new category associations for product_id ${productId}.`);
        }

        // Handle Images
        // Determine if main images are provided in the update
        const hasMainImagesInUpdate = Array.isArray(productData.images) && productData.images.length > 0;

        // Handle Main Images
        let insertedMainImages: Image[];

        if (hasMainImagesInUpdate) {
            // Delete existing main images
            const deleteMainImagesResult = await query(
                'DELETE FROM images WHERE product_id = $1 AND color_id IS NULL AND size_id IS NULL',
                [productId]
            );
            console.log(`Deleted ${deleteMainImagesResult.rowCount} existing main images for product_id ${productId}.`);

            // Insert new main images
            const imagesInsertPromises = productData.images.map((image: ImageInput) =>
                query(
                    `INSERT INTO images (product_id, url, color_id, size_id)
                     VALUES ($1, $2, NULL, NULL)
                     RETURNING id, url`,
                    [productId, image.url]
                )
            );

            const insertedImagesResults = await Promise.all(imagesInsertPromises);
            insertedMainImages = insertedImagesResults.map((imgResult: any) => ({
                id: imgResult.rows[0].id,
                url: imgResult.rows[0].url,
                color_id: null,
                size_id: null
            }));
            console.log(`Inserted ${insertedMainImages.length} new main images for product_id ${productId}.`);
        } else {
            // Retain existing main images
            const existingMainImagesResult = await query(
                `SELECT id, url FROM images 
                 WHERE product_id = $1 AND color_id IS NULL AND size_id IS NULL`,
                [productId]
            );

            insertedMainImages = existingMainImagesResult.rows.map((row: any) => ({
                id: row.id,
                url: row.url,
                color_id: null,
                size_id: null
            }));
            console.log(`Retained existing ${insertedMainImages.length} main images for product_id ${productId}.`);
        }

        // Handle Colors and Sizes
        let insertedColors: number[] = [];
        let insertedSizes: number[] = [];

        if (productData.has_color && Array.isArray(productData.colors)) {
            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 without resetting 'sold' unless provided
                    const updateFields = ['name', 'price', 'qty', 'has_size'];
                    const updateValues = [
                        colorInput.name,
                        adjustedColorPrice,
                        colorInput.qty,
                        colorInput.has_size
                    ];
                    let queryText = `
                        UPDATE colors 
                        SET 
                            name = $1, 
                            price = $2, 
                            qty = $3, 
                            has_size = $4,
                            updated_at = CURRENT_TIMESTAMP
                    `;

                    if (colorInput.sold !== undefined) {
                        updateFields.push('sold');
                        updateValues.push(colorInput.sold);
                        queryText += `, sold = $${updateFields.length}`;
                    }

                    queryText += ` WHERE id = $${updateFields.length + 1} RETURNING id`;

                    const colorUpdateResult = await query(
                        queryText,
                        colorInput.sold !== undefined
                            ? [...updateValues, colorInput.id]
                            : [...updateValues, colorInput.id]
                    );

                    if (colorUpdateResult.rows.length === 0) {
                        throw new Error(`Color with ID ${colorInput.id} not found.`);
                    }

                    colorId = colorUpdateResult.rows[0].id;
                } else {
                    // Insert new color with 'sold' initialized to 0
                    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 = Array.isArray(colorInput.images) && colorInput.images.length > 0;

                let colorImages: string[] = [];

                if (hasColorImagesInUpdate) {
                    // Delete existing images for this color
                    const deleteColorImagesResult = await query(
                        'DELETE FROM images WHERE product_id = $1 AND color_id = $2 AND size_id IS NULL',
                        [productId, colorId]
                    );
                    console.log(`Deleted ${deleteColorImagesResult.rowCount} existing images for color_id ${colorId}.`);

                    // Insert new images
                    const colorImagesInsertPromises = colorInput.images.map((image: ImageInput) =>
                        query(
                            `INSERT INTO images (product_id, color_id, url, size_id)
                             VALUES ($1, $2, $3, NULL)
                             RETURNING id, url`,
                            [productId, colorId, image.url]
                        )
                    );
                    const insertedColorImagesResults = await Promise.all(colorImagesInsertPromises);
                    colorImages = insertedColorImagesResults.map((imgResult: any) => imgResult.rows[0].url);
                    console.log(`Inserted ${colorImages.length} new images for color_id ${colorId}.`);
                } else {
                    // Fetch existing color images
                    const existingColorImagesResult = await query(
                        `SELECT url FROM images 
                         WHERE product_id = $1 AND color_id = $2 AND size_id IS NULL`,
                        [productId, colorId]
                    );
                    colorImages = existingColorImagesResult.rows.map((row: any) => row.url);
                    console.log(`Retained existing ${colorImages.length} images for color_id ${colorId}.`);
                }

                // Handle Sizes within Color
                let colorSizes: Size[] = [];

                if (colorInput.has_size && Array.isArray(colorInput.sizes)) {
                    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 without resetting 'sold' unless provided
                            const updateFields = ['name', 'price', 'qty'];
                            const updateValues = [
                                sizeInput.name,
                                adjustedSizePrice,
                                sizeInput.qty
                            ];
                            let queryText = `
                                UPDATE sizes 
                                SET 
                                    name = $1, 
                                    price = $2, 
                                    qty = $3,
                                    updated_at = CURRENT_TIMESTAMP
                            `;

                            if (sizeInput.sold !== undefined) {
                                updateFields.push('sold');
                                updateValues.push(sizeInput.sold);
                                queryText += `, sold = $${updateFields.length}`;
                            }

                            queryText += ` WHERE id = $${updateFields.length + 1} RETURNING id`;

                            const sizeUpdateResult = await query(
                                queryText,
                                sizeInput.sold !== undefined
                                    ? [...updateValues, sizeInput.id]
                                    : [...updateValues, sizeInput.id]
                            );

                            if (sizeUpdateResult.rows.length === 0) {
                                throw new Error(`Size with ID ${sizeInput.id} not found.`);
                            }

                            sizeId = sizeUpdateResult.rows[0].id;
                        } else {
                            // Insert new size with 'sold' initialized to 0
                            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 = Array.isArray(sizeInput.images) && sizeInput.images.length > 0;

                        let sizeImages: string[] = [];

                        if (hasSizeImagesInUpdate) {
                            // Delete existing images for this size
                            const deleteSizeImagesResult = await query(
                                'DELETE FROM images WHERE product_id = $1 AND size_id = $2',
                                [productId, sizeId]
                            );
                            console.log(`Deleted ${deleteSizeImagesResult.rowCount} existing images for size_id ${sizeId}.`);

                            // Insert new images
                            const sizeImagesInsertPromises = sizeInput.images.map((image: ImageInput) =>
                                query(
                                    `INSERT INTO images (product_id, size_id, url)
                                     VALUES ($1, $2, $3)
                                     RETURNING id, url`,
                                    [productId, sizeId, image.url]
                                )
                            );
                            const insertedSizeImagesResults = await Promise.all(sizeImagesInsertPromises);
                            sizeImages = insertedSizeImagesResults.map((imgResult: any) => imgResult.rows[0].url);
                            console.log(`Inserted ${sizeImages.length} new images for size_id ${sizeId}.`);
                        } else {
                            // Fetch existing size images
                            const existingSizeImagesResult = await query(
                                `SELECT url FROM images 
                                 WHERE product_id = $1 AND size_id = $2`,
                                [productId, sizeId]
                            );
                            sizeImages = existingSizeImagesResult.rows.map((row: any) => row.url);
                            console.log(`Retained existing ${sizeImages.length} images for size_id ${sizeId}.`);
                        }

                        // Append to colorSizes
                        colorSizes.push({
                            id: sizeId,
                            name: sizeInput.name,
                            price: adjustedSizePrice,
                            qty: sizeInput.qty,
                            sold: sizeInput.sold !== undefined ? sizeInput.sold : 0, // Ensure 'sold' is preserved if provided
                            color_id: colorId,
                            images: sizeImages
                        });
                    }
                }

                // At this point, we have updated color images and sizes.
                // We'll reconstruct the colors array later from the database.
            }

            // Handle standalone sizes if applicable
            if (productData.has_size && !productData.has_color && Array.isArray(productData.sizes)) {
                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 without resetting 'sold' unless provided
                        const updateFields = ['name', 'price', 'qty'];
                        const updateValues = [
                            sizeInput.name,
                            adjustedSizePrice,
                            sizeInput.qty
                        ];
                        let queryText = `
                            UPDATE sizes 
                            SET 
                                name = $1, 
                                price = $2, 
                                qty = $3,
                                updated_at = CURRENT_TIMESTAMP
                        `;

                        if (sizeInput.sold !== undefined) {
                            updateFields.push('sold');
                            updateValues.push(sizeInput.sold);
                            queryText += `, sold = $${updateFields.length}`;
                        }

                        queryText += ` WHERE id = $${updateFields.length + 1} RETURNING id`;

                        const sizeUpdateResult = await query(
                            queryText,
                            sizeInput.sold !== undefined
                                ? [...updateValues, sizeInput.id]
                                : [...updateValues, sizeInput.id]
                        );

                        if (sizeUpdateResult.rows.length === 0) {
                            throw new Error(`Size with ID ${sizeInput.id} not found.`);
                        }

                        sizeId = sizeUpdateResult.rows[0].id;
                    } else {
                        // Insert new size with 'sold' initialized to 0
                        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 = Array.isArray(sizeInput.images) && sizeInput.images.length > 0;

                    let sizeImages: string[] = [];

                    if (hasSizeImagesInUpdate) {
                        // Delete existing images for this size
                        const deleteSizeImagesResult = await query(
                            'DELETE FROM images WHERE product_id = $1 AND size_id = $2',
                            [productId, sizeId]
                        );
                        console.log(`Deleted ${deleteSizeImagesResult.rowCount} existing images for size_id ${sizeId}.`);

                        // Insert new images
                        const sizeImagesInsertPromises = sizeInput.images.map((image: ImageInput) =>
                            query(
                                `INSERT INTO images (product_id, size_id, url)
                                 VALUES ($1, $2, $3)
                                 RETURNING id, url`,
                                [productId, sizeId, image.url]
                            )
                        );
                        const insertedSizeImagesResults = await Promise.all(sizeImagesInsertPromises);
                        sizeImages = insertedSizeImagesResults.map((imgResult: any) => imgResult.rows[0].url);
                        console.log(`Inserted ${sizeImages.length} new images for size_id ${sizeId}.`);
                    } else {
                        // Retain existing size images
                        const existingSizeImagesResult = await query(
                            `SELECT url FROM images 
                             WHERE product_id = $1 AND size_id = $2`,
                            [productId, sizeId]
                        );
                        sizeImages = existingSizeImagesResult.rows.map((row: any) => row.url);
                        console.log(`Retained existing ${sizeImages.length} images for size_id ${sizeId}.`);
                    }

                    // Append to standaloneSizes
                    // We'll reconstruct the sizes array later from the database.
                }
            }

            // At this point, all updates to categories, images, colors, and sizes are done.

            // Fetch and populate categories with details
            const categories = await fetchCategoryDetails(uniqueCategoryIds || []);

            // Fetch all colors for the updated product to reconstruct the colors array
            const colorsResultAfterUpdate = await query(
                `SELECT id, name, price, qty, sold, has_size FROM colors WHERE product_id = $1`,
                [productId]
            );

            // Build colors with images and sizes
            const colors: Color[] = await Promise.all(
                colorsResultAfterUpdate.rows.map(async (colorRow: any) => {
                    // Fetch images for this color
                    const colorImagesResult = await query(
                        `SELECT url FROM images WHERE product_id = $1 AND color_id = $2 AND size_id IS NULL`,
                        [productId, colorRow.id]
                    );
                    const colorImages = colorImagesResult.rows.map((row: any) => row.url);

                    // Fetch sizes for this color
                    const sizesResult = await query(
                        `SELECT id, name, price, qty, sold FROM sizes WHERE product_id = $1 AND color_id = $2`,
                        [productId, colorRow.id]
                    );

                    const sizes: Size[] = await Promise.all(
                        sizesResult.rows.map(async (sizeRow: any) => {
                            // Fetch images for this size
                            const sizeImagesResult = await query(
                                `SELECT url FROM images WHERE product_id = $1 AND size_id = $2`,
                                [productId, sizeRow.id]
                            );
                            const sizeImages = sizeImagesResult.rows.map((row: any) => row.url);

                            return {
                                id: sizeRow.id,
                                name: sizeRow.name,
                                price: sizeRow.price,
                                qty: sizeRow.qty,
                                sold: sizeRow.sold,
                                color_id: colorRow.id,
                                images: sizeImages
                            };
                        })
                    );

                    return {
                        id: colorRow.id,
                        name: colorRow.name,
                        price: colorRow.price,
                        qty: colorRow.qty,
                        sold: colorRow.sold,
                        has_size: colorRow.has_size,
                        images: colorImages,
                        sizes: sizes
                    };
                })
            );

            // Fetch standalone sizes if the product does not have colors but has sizes
            let standaloneSizes: Size[] = [];
            if (!productData.has_color && productData.has_size) {
                const sizesResultAfterUpdate = await query(
                    `SELECT id, name, price, qty, sold FROM sizes WHERE product_id = $1 AND color_id IS NULL`,
                    [productId]
                );

                standaloneSizes = sizesResultAfterUpdate.rows.map((sizeRow: any) => ({
                    id: sizeRow.id,
                    name: sizeRow.name,
                    price: sizeRow.price,
                    qty: sizeRow.qty,
                    sold: sizeRow.sold,
                    color_id: null,
                    images: [] // Will be populated next
                }));

                // Fetch images for standalone sizes
                standaloneSizes = await Promise.all(
                    standaloneSizes.map(async (size: Size) => {
                        const sizeImagesResult = await query(
                            `SELECT url FROM images WHERE product_id = $1 AND size_id = $2`,
                            [productId, size.id]
                        );
                        size.images = sizeImagesResult.rows.map((row: any) => row.url);
                        return size;
                    })
                );
                console.log(`Fetched ${standaloneSizes.length} standalone sizes with images.`);
            }

            // Fetch existing main images if not updated (already handled earlier)
            // insertedMainImages already contains the correct data.

            // Construct the updated product object as it should 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: updatedProductResult.rows[0].sold, // Retain existing sold count
                created_at: updatedProductResult.rows[0].created_at.toISOString(),
                updated_at: new Date().toISOString(),
                categories: categories,
                colors: colors,
                sizes: standaloneSizes,
                images: insertedMainImages
            };

            updateProductInCache(updatedProduct);
            console.log(`Updated product in cache: Product ID ${productId}.`);

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