Handle cart conflict on both updateProduct and removeProduct.
import { Request, Response } from 'express';
import {query} from "../config/database";


// Get all products with caching
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]);

        // Delete cart items related to this product's 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));

        // Delete colors and sizes that are not in the updated product data
        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));

        // 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 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' });
    }
};
import express from 'express';
import { body } from 'express-validator';
import { validateRequest } from '../middleware/validateRequest';
import { isAuthenticated } from '../middleware/isAuthenticated';
import * as cartController from '../controllers/cartController';

const router = express.Router();

// Create a new cart with items
router.post(
    '/',
    [
            // Validate cart fields
            body('notes').optional().isString().withMessage('Notes must be a string'),
            body('full_name').optional().isString().withMessage('Full name must be a string'),
            body('governorate').optional().isString().withMessage('Governorate must be a string'),
            body('city').optional().isString().withMessage('City must be a string'),
            body('address').optional().isString().withMessage('Address must be a string'),
            body('closest_point').optional().isString().withMessage('Closest point must be a string'),
            body('phone_number').optional().isString().withMessage('Phone number must be a string'),
            body('type')
                .optional()
                .isString()
                .custom((value) => {
                        const allowedTypes = ['home', 'work', 'other'];
                        if (typeof value !== 'string') return false;
                        return allowedTypes.includes(value.toLowerCase());
                })
                .withMessage('Type must be home, work, or other'),
            body('coupon_id')
                .optional({ nullable: true })
                .custom((value) => {
                        if (value === null) return true;
                        return Number.isInteger(Number(value));
                })
                .withMessage('Coupon ID must be an integer or null'),

            body('items').isArray({ min: 1 }).withMessage('Items array is required'),
            body('items.*.product_id').isInt().withMessage('Product ID must be an integer'),
            body('items.*.size_id').optional({values: "falsy"}).isInt().withMessage('Size ID must be an integer'),
            body('items.*.color_id').optional({values: "falsy"}).isInt().withMessage('Color ID must be an integer'),
            body('items.*.quantity').isInt({ min: 1 }).withMessage('Quantity must be greater than zero'),
    ],
    validateRequest,
    cartController.createCart
);

router.get('/', isAuthenticated, cartController.getCart);

export default router;
// controllers/cartController.ts
import { Request, Response, NextFunction } from 'express';
import * as cartModel from '../models/cartModel';

export async function createCart(req: Request, res: Response, next: NextFunction) {
    try {
        const userId: any = req.user?.id || null;

        const {
            status,
            notes,
            full_name,
            governorate,
            city,
            address,
            closest_point,
            phone_number,
            type,
            coupon_id,
            items,
        } = req.body;

        if (!items || !Array.isArray(items) || items.length === 0) {
            return res.status(400).json({ message: 'Items are required to create a cart' });
        }

        // Prepare cart data
        const cartData = {
            user_id: userId,
            status: status || 'Pending',
            notes,
            full_name,
            governorate,
            city,
            address,
            closest_point,
            phone_number,
            type,
            coupon_id,
        };

        // Create cart with items
        const { cart, items: cartItems } = await cartModel.createCartWithItems(cartData, items);

        res.status(201).json({ cart, items: cartItems });
    } catch (error) {
        next(error);
    }
}

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);
    }
}
import { query } from '../config/database';

// Interfaces for TypeScript typing
export interface Cart {
    id: number;
    user_id?: number | null;
    status: string;
    notes?: string;
    full_name?: string;
    governorate?: string;
    city?: string;
    address?: string;
    closest_point?: string;
    phone_number?: string;
    type?: string;
    created_at: Date;
    coupon_id?: number | null;
}

export interface CartItem {
    id: number;
    cart_id: number;
    product_id: number;
    size_id?: number | null;
    color_id?: number | null;
    quantity: number;
}

// Function to create a cart with items in a single transaction
export async function createCartWithItems(
    cartData: Partial<Cart>,
    items: Partial<CartItem>[]
): Promise<{ cart: Cart; items: CartItem[] }> {
    try {
        await query('BEGIN');

        // Insert into carts table
        const cartResult = await query<Cart>(
            `INSERT INTO carts (
        user_id, status, notes, full_name, governorate, city, address, closest_point, phone_number, type, coupon_id
      ) VALUES (
        $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11
      ) RETURNING *`,
            [
                cartData.user_id,
                cartData.status || 'Pending',
                cartData.notes,
                cartData.full_name,
                cartData.governorate,
                cartData.city,
                cartData.address,
                cartData.closest_point,
                cartData.phone_number,
                cartData.type,
                cartData.coupon_id,
            ]
        );

        const cart = cartResult.rows[0];
        if (!cart) {
            throw new Error('Failed to create cart');
        }

        // Insert cart items
        const cartItems: CartItem[] = [];
        const insertItemText = `INSERT INTO cart_items (
      cart_id, product_id, size_id, color_id, quantity
    ) VALUES ($1, $2, $3, $4, $5) RETURNING *`;

        for (const item of items) {
            const itemResult = await query<CartItem>(insertItemText, [
                cart.id,
                item.product_id,
                item.size_id,
                item.color_id,
                item.quantity,
            ]);
            cartItems.push(itemResult.rows[0]);
        }

        await query('COMMIT');
        return { cart, items: cartItems };
    } catch (error) {
        await query('ROLLBACK');
        console.error('Error creating cart with items:', error);
        throw error;
    }
}

export async function getCartWithItemsByUserId(
    userId: number
): Promise<{ cart: Cart; items: CartItem[] } | null> {
    // Get the most recent cart for the user
    const cartResult = await query<Cart>(
        `SELECT * FROM carts WHERE user_id = $1 ORDER BY created_at DESC LIMIT 1`,
        [userId]
    );

    const cart = cartResult.rows[0];
    if (!cart) {
        return null;
    }

    // Get the cart items
    const itemsResult = await query<CartItem>(
        `SELECT * FROM cart_items WHERE cart_id = $1`,
        [cart.id]
    );

    return { cart, items: itemsResult.rows };
}