-- =====================================================
-- FIX REMAINING QUANTITIES
-- This script recalculates purchase_item_remaining_quantity
-- based on actual sales and supplier returns
-- =====================================================

-- STEP 1: First, let's see what's wrong (run this to preview)
-- =====================================================
SELECT 
    pi.purchase_item_id,
    i.item_scientific_name,
    pi.purchase_item_quantity AS purchased,
    pi.purchase_item_remaining_quantity AS current_remaining,
    COALESCE(sold.qty_sold, 0) AS actually_sold,
    COALESCE(supplier_returned.qty_returned, 0) AS returned_to_supplier,
    (pi.purchase_item_quantity - COALESCE(sold.qty_sold, 0) - COALESCE(supplier_returned.qty_returned, 0)) AS correct_remaining,
    (pi.purchase_item_remaining_quantity - (pi.purchase_item_quantity - COALESCE(sold.qty_sold, 0) - COALESCE(supplier_returned.qty_returned, 0))) AS difference
FROM purchase_items pi
LEFT JOIN items i ON pi.item_id = i.item_id
LEFT JOIN (
    SELECT purchase_item_id, SUM(invoice_item_quantity) AS qty_sold
    FROM invoice_items
    GROUP BY purchase_item_id
) sold ON pi.purchase_item_id = sold.purchase_item_id
LEFT JOIN (
    SELECT purchase_item_id, SUM(supplier_return_item_quantity) AS qty_returned
    FROM supplier_return_items
    GROUP BY purchase_item_id
) supplier_returned ON pi.purchase_item_id = supplier_returned.purchase_item_id
WHERE pi.purchase_item_remaining_quantity != (pi.purchase_item_quantity - COALESCE(sold.qty_sold, 0) - COALESCE(supplier_returned.qty_returned, 0))
ORDER BY ABS(pi.purchase_item_remaining_quantity - (pi.purchase_item_quantity - COALESCE(sold.qty_sold, 0) - COALESCE(supplier_returned.qty_returned, 0))) DESC;


-- =====================================================
-- STEP 2: UPDATE - Run this to fix all wrong values
-- =====================================================
UPDATE purchase_items pi
LEFT JOIN (
    SELECT purchase_item_id, SUM(invoice_item_quantity) AS qty_sold
    FROM invoice_items
    GROUP BY purchase_item_id
) sold ON pi.purchase_item_id = sold.purchase_item_id
LEFT JOIN (
    SELECT purchase_item_id, SUM(supplier_return_item_quantity) AS qty_returned
    FROM supplier_return_items
    GROUP BY purchase_item_id
) supplier_returned ON pi.purchase_item_id = supplier_returned.purchase_item_id
SET 
    pi.purchase_item_remaining_quantity = GREATEST(0, pi.purchase_item_quantity - COALESCE(sold.qty_sold, 0) - COALESCE(supplier_returned.qty_returned, 0)),
    pi.purchase_item_is_active = CASE 
        WHEN (pi.purchase_item_quantity - COALESCE(sold.qty_sold, 0) - COALESCE(supplier_returned.qty_returned, 0)) > 0 THEN 1 
        ELSE 0 
    END;


-- =====================================================
-- STEP 3: Verify the fix worked (run after update)
-- =====================================================
SELECT 
    COUNT(*) AS total_items,
    SUM(CASE WHEN pi.purchase_item_remaining_quantity = (pi.purchase_item_quantity - COALESCE(sold.qty_sold, 0) - COALESCE(supplier_returned.qty_returned, 0)) THEN 1 ELSE 0 END) AS correct_items,
    SUM(CASE WHEN pi.purchase_item_remaining_quantity != (pi.purchase_item_quantity - COALESCE(sold.qty_sold, 0) - COALESCE(supplier_returned.qty_returned, 0)) THEN 1 ELSE 0 END) AS still_wrong
FROM purchase_items pi
LEFT JOIN (
    SELECT purchase_item_id, SUM(invoice_item_quantity) AS qty_sold
    FROM invoice_items
    GROUP BY purchase_item_id
) sold ON pi.purchase_item_id = sold.purchase_item_id
LEFT JOIN (
    SELECT purchase_item_id, SUM(supplier_return_item_quantity) AS qty_returned
    FROM supplier_return_items
    GROUP BY purchase_item_id
) supplier_returned ON pi.purchase_item_id = supplier_returned.purchase_item_id;