-- Stock Reconciliation Migration
-- Consolidates fragmented stock records and fixes discrepancies across all products
-- Applied: 2026-04-12
-- Purpose: Fix stock table fragmentation where single products have multiple entries

-- Step 1: Create a temporary table to hold the correct stock balances
CREATE TEMPORARY TABLE tmp_stock_consolidated AS
SELECT 
    s.tenant_id,
    s.product_id,
    MIN(s.location_id) as location_id,
    COALESCE(
        (SELECT SUM(CASE 
            WHEN sm.type IN ('purchase', 'transfer_in', 'adjustment_in', 'return', 'adjustment') 
            THEN ABS(sm.quantity)
            ELSE 0 
        END) - 
        SUM(CASE 
            WHEN sm.type IN ('sale', 'transfer_out') 
            THEN ABS(sm.quantity)
            ELSE 0 
        END)
        FROM stock_movements sm 
        WHERE sm.product_id = s.product_id),
        0
    ) as correct_quantity
FROM stock s
GROUP BY s.tenant_id, s.product_id;

-- Step 2: Delete the fragmented stock records
DELETE FROM stock
WHERE product_id IN (
    SELECT product_id 
    FROM stock 
    GROUP BY product_id 
    HAVING COUNT(*) > 1
);

-- Step 3: Insert consolidated records with correct balances
INSERT INTO stock (tenant_id, product_id, location_id, batch_id, quantity)
SELECT tenant_id, product_id, location_id, NULL, correct_quantity
FROM tmp_stock_consolidated
WHERE correct_quantity IS NOT NULL
ON DUPLICATE KEY UPDATE 
    quantity = VALUES(quantity);

-- Step 4: Clean up temporary table
DROP TEMPORARY TABLE tmp_stock_consolidated;
