-- Fix missing product data in order items
-- Updates order items to use actual product names, images, and specifications from the product tables.

DO $$
BEGIN
    -- 1. Update image_url from SKU if available
    UPDATE public.ml_order_items oi
    SET image_url = sku.image_url
    FROM public.ml_product_skus sku
    WHERE oi.sku_id = sku.id 
    AND (oi.image_url IS NULL OR oi.image_url = '');

    -- 2. Update image_url from Product Main Image if still NULL
    UPDATE public.ml_order_items oi
    SET image_url = p.main_image_url
    FROM public.ml_products p
    WHERE oi.product_id = p.id 
    AND (oi.image_url IS NULL OR oi.image_url = '');

    -- 3. Update product_name from Product if it looks like a placeholder
    UPDATE public.ml_order_items oi
    SET product_name = p.name
    FROM public.ml_products p
    WHERE oi.product_id = p.id 
    AND (
        oi.product_name LIKE '%Product%' 
        OR oi.product_name LIKE 'Pending Order%'
        OR oi.product_name = 'Test Product'
    );

    -- 4. Update specifications from SKU if missing
    -- This ensures the frontend shows the correct spec (e.g. "Color: Red")
    UPDATE public.ml_order_items oi
    SET specifications = sku.specifications
    FROM public.ml_product_skus sku
    WHERE oi.sku_id = sku.id 
    AND (oi.specifications IS NULL OR oi.specifications = '{}'::jsonb);

    RAISE NOTICE 'Updated order items with real product data (images, names, specs).';
END $$;