I want to alter products, sizes and colors tables to add a new column "sold" which indicates how many sold!, give me the postgresql query to alter, also merge it with the backend...
import { Request, Response } from 'express';
import {query} from "../config/database";



export const getProducts = async (req: Request, res: Response) => {
    try {
        const page = parseInt(req.query.page as string) || 1;
        const limit = 10;
        const offset = (page - 1) * limit;
        const search = (req.query.search as string || '').toLowerCase();
        const categoryId = req.query.c as string;
        const subcategoryId = req.query.sc as string;
        const brandId = req.query.b as string;


        const conditions = [];
        const queryParams: any[] = [];

        if (search) {
            const searchWords = search.split(/\s+/).filter(Boolean);
            const searchConditions = searchWords.map((word) => {
                queryParams.push(`${word}%`, `%${word}%`);
                return `(LOWER(p.name) LIKE $${queryParams.length - 1} OR LOWER(p.name) LIKE $${queryParams.length})`;
            });
            conditions.push(`(${searchConditions.join(' OR ')})`);
        }

        if (categoryId) {
            queryParams.push(categoryId);
            conditions.push(`(c.id = $${queryParams.length} OR EXISTS (
                SELECT 1 FROM subcategories sub 
                WHERE sub.category_id = $${queryParams.length} 
                AND (sub.id = sc.id OR sub.id = p.brand_id)
            ))`);
        }

        if (subcategoryId) {
            queryParams.push(subcategoryId);
            conditions.push(`(sc.id = $${queryParams.length} OR p.brand_id = $${queryParams.length})`);
        }

        if (brandId) {
            queryParams.push(brandId);
            conditions.push(`p.brand_id = $${queryParams.length}`);
        }

        const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

        // Add search, limit, and offset to queryParams
        queryParams.push(search, limit, offset);

        const productQuery = `
            WITH filtered_products AS (
                SELECT p.*, 
                       sc.id AS subcategory_id, sc.name AS subcategory_name,
                       c.id AS main_category_id, c.name AS main_category_name,
                       COUNT(*) OVER() AS total_count
                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
                ${whereClause}
                ORDER BY 
                    CASE 
                        WHEN $${queryParams.length - 2} != '' THEN 
                            CASE 
                                WHEN LOWER(p.name) LIKE LOWER($${queryParams.length - 2} || '%') THEN 1
                                WHEN LOWER(p.name) LIKE '%' || LOWER($${queryParams.length - 2}) || '%' THEN 2
                                ELSE 3
                            END
                        ELSE 0
                    END,
                    p.created_at DESC
            )
            SELECT 
                fp.*,
                (SELECT jsonb_agg(jsonb_build_object(
                    'id', c.id, 'name', c.name, 'price', c.price, 'qty', c.qty,
                    'images', (SELECT jsonb_agg(i.url) FROM images i WHERE i.color_id = c.id),
                    'sizes', (SELECT jsonb_agg(jsonb_build_object(
                        'id', s.id, 'name', s.name, 'price', s.price, 'qty', s.qty,
                        'images', (SELECT jsonb_agg(i.url) FROM images i WHERE i.size_id = s.id)
                    )) FROM sizes s WHERE s.color_id = c.id)
                )) FROM colors c WHERE c.product_id = fp.id) AS colors,
                (SELECT jsonb_agg(jsonb_build_object(
                    'id', s.id, 'name', s.name, 'price', s.price, 'qty', s.qty, 'color_id', s.color_id,
                    'images', (SELECT jsonb_agg(i.url) FROM images i WHERE i.size_id = s.id)
                )) FROM sizes s WHERE s.product_id = fp.id AND s.color_id IS NULL) AS sizes,
                (SELECT jsonb_agg(i.url) FROM images i WHERE i.product_id = fp.id AND i.color_id IS NULL AND i.size_id IS NULL) AS images
            FROM filtered_products fp
            LIMIT $${queryParams.length - 1}::integer OFFSET $${queryParams.length}::integer
        `;

        const productResult = await query(productQuery, queryParams);

        const totalProducts = parseInt(productResult.rows[0]?.total_count || '0');
        const totalPages = Math.ceil(totalProducts / limit);

        const products = productResult.rows.map(product => ({
            id: product.id,
            name: product.name,
            description: product.description,
            brand_id: product.brand_id,
            has_color: product.has_color,
            has_size: product.has_size,
            product_type: product.product_type,
            price: product.price,
            qty: product.qty,
            created_at: product.created_at,
            updated_at: product.updated_at,
            categories: [
                {
                    id: product.subcategory_id,
                    name: product.subcategory_name,
                    main_category_id: product.main_category_id,
                    main_category_name: product.main_category_name
                }
            ],
            colors: product.colors || [],
            sizes: product.sizes || [],
            images: [
                ...(product.images || []),
                ...(product.colors || []).flatMap((c: { images: any; }) => c.images || []),
                ...(product.sizes || []).flatMap((s: { images: any; }) => s.images || [])
            ]
        }));

        const result = {
            products,
            pagination: {
                currentPage: page,
                totalPages,
                totalProducts,
                limit
            }
        };

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



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.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,
                s.id AS size_id,
                s.name AS size_name,
                s.price AS size_price,
                s.qty AS size_qty,
                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
        // @ts-ignore
        const 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,
            created_at: result.rows[0].created_at,
            updated_at: result.rows[0].updated_at,
            // @ts-ignore
            categories: [],
            // @ts-ignore
            colors: [],
            // @ts-ignore
            sizes: [],
            // @ts-ignore
            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,
                    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,
                    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);
                    }
                }
            }

            // Add image to the product, and to the appropriate color or size
            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 {
        await query('BEGIN');

        const productData = req.body;

        if (productData.price < 1000) {
            productData.price *= 1000;
        }

        const productResult = await query(
            'INSERT INTO products (name, description, brand_id, has_color, has_size, product_type, price, qty) VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING id',
            [productData.name, productData.description, productData.brand_id, productData.hasColor, productData.hasSize, productData.product_type, productData.price, productData.qty]
        );
        const productId = productResult.rows[0].id;

        // Insert category associations
        for (const categoryId of productData.category_ids) {
            await query(
                'INSERT INTO product_categories (product_id, category_id) VALUES ($1, $2)',
                [productId, categoryId]
            );
        }

        // Insert images
        for (const imageUrl of productData.images) {
            await query(
                'INSERT INTO images (product_id, url) VALUES ($1, $2)',
                [productId, imageUrl]
            );
        }

        // Handle colors and sizes based on product type
        if (productData.hasColor) {
            for (const color of productData.colors) {
                // Adjust color price if less than 1000
                if (color.price < 1000) {
                    color.price *= 1000;
                }

                const colorResult = await query(
                    'INSERT INTO colors (product_id, name, has_size, price, qty) VALUES ($1, $2, $3, $4, $5) RETURNING id',
                    [productId, color.name, color.hasSize, color.price, color.qty]
                );
                const colorId = colorResult.rows[0].id;

                // Insert color images
                for (const imageUrl of color.images) {
                    await query(
                        'INSERT INTO images (product_id, color_id, url) VALUES ($1, $2, $3)',
                        [productId, colorId, imageUrl]
                    );
                }

                if (color.hasSize) {
                    for (const size of color.sizes) {
                        // Adjust size price if less than 1000
                        if (size.price < 1000) {
                            size.price *= 1000;
                        }

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

                        // Insert size images
                        for (const imageUrl of size.images) {
                            await query(
                                'INSERT INTO images (product_id, color_id, size_id, url) VALUES ($1, $2, $3, $4)',
                                [productId, colorId, sizeId, imageUrl]
                            );
                        }
                    }
                }
            }
        } else if (productData.hasSize) {
            for (const size of productData.sizes) {
                // Adjust size price if less than 1000
                if (size.price < 1000) {
                    size.price *= 1000;
                }

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

                // Insert size images
                for (const imageUrl of size.images) {
                    await query(
                        'INSERT INTO images (product_id, size_id, url) VALUES ($1, $2, $3)',
                        [productId, sizeId, imageUrl]
                    );
                }
            }
        }
        await query('COMMIT');
        res.status(201).json({ message: 'Product created successfully', productId });
    } catch (error) {
        await query('ROLLBACK');
        console.error('Error creating product:', error);
        res.status(500).json({ error: 'Internal server error' });
    } finally {
    }
};

// Update a product
export const updateProduct = async (req: Request, res: Response) => {
    const { id } = req.params;
    const productData = req.body;

    try {
        await query('BEGIN');

        // Adjust main product price if less than 1000
        if (productData.price < 1000) {
            productData.price *= 1000;
        }

        // Update main 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.hasColor, productData.hasSize, productData.product_type, productData.price, productData.qty, id]
        );

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

        // Delete existing associations and data
        await query('DELETE FROM product_categories WHERE product_id = $1', [id]);
        await query('DELETE FROM images WHERE product_id = $1', [id]);

        // Get existing colors and sizes
        const existingColors = await query('SELECT id FROM colors WHERE product_id = $1', [id]);
        const existingColorIds = new Set(existingColors.rows.map(row => row.id));

        const existingSizes = await query('SELECT id FROM sizes WHERE product_id = $1', [id]);
        const existingSizeIds = new Set(existingSizes.rows.map(row => row.id));

        // Determine colors and sizes to delete
        const updatedColorIds = new Set(productData.colors.map((color: { id: any; }) => color.id).filter((id: any) => id !== undefined));
        const colorsToDelete = [...existingColorIds].filter(id => !updatedColorIds.has(id));

        const updatedSizeIds = new Set([
            ...productData.sizes.map((size: { id: any; }) => size.id).filter((id: any) => id !== undefined),
            ...productData.colors.flatMap((color: { sizes: any[]; }) => color.sizes.map(size => size.id).filter(id => id !== undefined))
        ]);
        const sizesToDelete = [...existingSizeIds].filter(id => !updatedSizeIds.has(id));

        // Handle cart conflicts by deleting cart items associated with sizes and colors to be deleted
        if (sizesToDelete.length > 0) {
            await query('DELETE FROM cart_items WHERE size_id = ANY ($1)', [sizesToDelete]);
        }

        if (colorsToDelete.length > 0) {
            await query('DELETE FROM cart_items WHERE color_id = ANY ($1)', [colorsToDelete]);
        }

        // Delete colors and associated sizes
        for (const colorId of colorsToDelete) {
            await query('DELETE FROM sizes WHERE color_id = $1', [colorId]);
            await query('DELETE FROM images WHERE color_id = $1', [colorId]);
            await query('DELETE FROM colors WHERE id = $1', [colorId]);
        }

        // Delete sizes
        for (const sizeId of sizesToDelete) {
            await query('DELETE FROM images WHERE size_id = $1', [sizeId]);
            await query('DELETE FROM sizes WHERE id = $1', [sizeId]);
        }

        // Insert category associations
        for (const categoryId of productData.category_ids) {
            await query(
                'INSERT INTO product_categories (product_id, category_id) VALUES ($1, $2)',
                [id, categoryId]
            );
        }

        // Insert images
        for (const imageUrl of productData.images) {
            await query(
                'INSERT INTO images (product_id, url) VALUES ($1, $2)',
                [id, imageUrl]
            );
        }

        // Handle colors and sizes based on product type
        if (productData.hasColor) {
            for (const color of productData.colors) {
                // Adjust color price if less than 1000
                if (color.price < 1000) {
                    color.price *= 1000;
                }

                let colorId;
                if (color.id) {
                    // Update existing color
                    await query(
                        'UPDATE colors SET name = $1, has_size = $2, price = $3, qty = $4 WHERE id = $5',
                        [color.name, color.hasSize, color.price, color.qty, color.id]
                    );
                    colorId = color.id;
                } else {
                    // Insert new color
                    const colorResult = await query(
                        'INSERT INTO colors (product_id, name, has_size, price, qty) VALUES ($1, $2, $3, $4, $5) RETURNING id',
                        [id, color.name, color.hasSize, color.price, color.qty]
                    );
                    colorId = colorResult.rows[0].id;
                }

                if (color.images && color.images.length > 0) {
                    for (const imageUrl of color.images) {
                        await query(
                            'INSERT INTO images (product_id, color_id, url) VALUES ($1, $2, $3)',
                            [id, colorId, imageUrl]
                        );
                    }
                }

                if (color.hasSize) {
                    for (const size of color.sizes) {
                        // Adjust size price if less than 1000
                        if (size.price < 1000) {
                            size.price *= 1000;
                        }

                        let sizeId;
                        if (size.id) {
                            // Update existing size
                            await query(
                                'UPDATE sizes SET name = $1, price = $2, qty = $3 WHERE id = $4',
                                [size.name, size.price, size.qty, size.id]
                            );
                            sizeId = size.id;
                        } else {
                            // Insert new size
                            const sizeResult = await query(
                                'INSERT INTO sizes (product_id, color_id, name, price, qty) VALUES ($1, $2, $3, $4, $5) RETURNING id',
                                [id, colorId, size.name, size.price, size.qty]
                            );
                            sizeId = sizeResult.rows[0].id;
                        }

                        // Insert size images
                        if (size.images && size.images.length > 0) {
                            for (const imageUrl of size.images) {
                                await query(
                                    'INSERT INTO images (product_id, color_id, size_id, url) VALUES ($1, $2, $3, $4)',
                                    [id, colorId, sizeId, imageUrl]
                                );
                            }
                        }
                    }
                }
            }
        } else if (productData.hasSize) {
            for (const size of productData.sizes) {
                // Adjust size price if less than 1000
                if (size.price < 1000) {
                    size.price *= 1000;
                }

                let sizeId;
                if (size.id) {
                    // Update existing size
                    await query(
                        'UPDATE sizes SET name = $1, price = $2, qty = $3 WHERE id = $4',
                        [size.name, size.price, size.qty, size.id]
                    );
                    sizeId = size.id;
                } else {
                    // Insert new size
                    const sizeResult = await query(
                        'INSERT INTO sizes (product_id, name, price, qty) VALUES ($1, $2, $3, $4) RETURNING id',
                        [id, size.name, size.price, size.qty]
                    );
                    sizeId = sizeResult.rows[0].id;
                }

                // Insert size images
                if (size.images && size.images.length > 0) {
                    for (const imageUrl of size.images) {
                        await query(
                            'INSERT INTO images (product_id, size_id, url) VALUES ($1, $2, $3)',
                            [id, sizeId, imageUrl]
                        );
                    }
                }
            }
        }
        await query('COMMIT');
        res.json({ message: 'Product updated successfully', product: result.rows[0] });
    } 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;
    try {
        await query('BEGIN');

        // Delete cart items associated with this product
        await query('DELETE FROM cart_items WHERE product_id = $1', [id]);

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

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

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

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