Updating the status to ordered
should increase sold by 1, it should increase the purchased size or color or a product without them sold by 1...
// models/cartModel.ts
import { query } from '../config/database';

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

export interface CartWithItems extends Cart {
    items: CartItem[];
    coupon?: {
        id: number;
        code: string;
        name: string;
        description: string;
        discount_amount: number;
        discount_percentage: number;
        start_date: string;
        end_date: string;
        usage_limit: number;
        min_purchase_amount: number;
        max_discount_amount: number;
        is_active: boolean;
    }
}
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';
    }

    await query(updateQuery, params);
}

export async function getAllCartsWithItems(): Promise<CartWithItems[]> {
    try {
        const cartResult = await query<Cart>('SELECT * FROM carts ORDER BY created_at DESC');
        const carts = cartResult.rows;
        const cartsWithItems: CartWithItems[] = [];

        for (const cart of carts) {
            const itemsResult = await query<CartItem>(
                'SELECT * FROM cart_items WHERE cart_id = $1',
                [cart.id]
            );

            let couponInfo = null;
            if (cart.coupon_id) {
                const couponResult = await query(
                    `SELECT id, code, name, description, discount_amount, discount_percentage, 
                    start_date, end_date, usage_limit, min_purchase_amount, max_discount_amount, is_active 
                    FROM coupons WHERE id = $1`,
                    [cart.coupon_id]
                );
                if (couponResult.rows.length > 0) {
                    couponInfo = couponResult.rows[0];
                }
            }

            cartsWithItems.push({
                ...cart,
                items: itemsResult.rows,
                coupon: couponInfo
            });
        }

        return cartsWithItems;
    } catch (error) {
        console.error('Error fetching carts with items:', error);
        throw error;
    }
}
export async function updateCartStatus(cartId: number, newStatus: string): Promise<Cart | null> {
    try {
        await query('BEGIN');

        const oldStatusResult = await query<Cart>('SELECT status FROM carts WHERE id = $1', [cartId]);
        const oldStatus = oldStatusResult.rows[0]?.status;

        const result = await query<Cart>(
            'UPDATE carts SET status = $1, updated_at = CURRENT_TIMESTAMP WHERE id = $2 RETURNING *',
            [newStatus, cartId]
        );

        const updatedCart = result.rows[0];

        if (updatedCart) {
            const cartItemsResult = await query<CartItem>('SELECT * FROM cart_items WHERE cart_id = $1', [cartId]);
            const cartItems = cartItemsResult.rows;

            for (const item of cartItems) {
                let quantityChange = 0;

                if (oldStatus !== 'rejected' && newStatus === 'rejected') {
                    quantityChange = item.quantity;
                } else if (oldStatus === 'rejected' && newStatus !== 'rejected') {
                    quantityChange = -item.quantity;
                }

                if (quantityChange !== 0) {
                    await updateProductQuantity(item.product_id, item.size_id, item.color_id, quantityChange);
                }
            }
        }

        await query('COMMIT');

        return updatedCart || null;
    } catch (error) {
        await query('ROLLBACK');
        console.error('Error updating cart status:', error);
        throw error;
    }
}
export async function deleteCartById(cartId: number): Promise<boolean> {
    try {
        await query('BEGIN');

        // Delete cart items
        await query('DELETE FROM cart_items WHERE cart_id = $1', [cartId]);

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

        await query('COMMIT');

        return result.rows.length > 0;
    } catch (error) {
        await query('ROLLBACK');
        console.error('Error deleting cart:', error);
        throw error;
    }
}