-- Migration: Convert quantity columns from INT to DECIMAL to support fractional quantities (sheet selling)
-- Run this migration on the production database to fix the bug where deleting invoice items
-- causes incorrect remaining quantity when selling by sheet.
--
-- The bug: invoice_item_quantity was INT, so 0.5 (5 sheets of 10) was stored as 0
-- When deleting, it restored 0 instead of 0.5, causing quantity mismatch
--
-- IMPORTANT: Backup your database before running this migration!

-- Step 1: Show current state for reference
SELECT 'Before migration - checking current column types' AS status;
DESCRIBE invoice_items;
DESCRIBE purchase_items;
DESCRIBE return_items;

-- Step 2: Alter invoice_items table - change quantity to DECIMAL
-- DECIMAL(10,4) allows quantities like 0.0001 (for very small sheet fractions)
ALTER TABLE invoice_items 
MODIFY COLUMN invoice_item_quantity DECIMAL(10,4) NOT NULL;

-- Step 3: Alter purchase_items table - change both quantity and remaining_quantity to DECIMAL
ALTER TABLE purchase_items 
MODIFY COLUMN purchase_item_quantity DECIMAL(10,4) NOT NULL,
MODIFY COLUMN purchase_item_remaining_quantity DECIMAL(10,4) NOT NULL;

-- Step 4: Alter return_items table (customer returns) - change quantity to DECIMAL
ALTER TABLE return_items 
MODIFY COLUMN return_item_quantity DECIMAL(10,4) NOT NULL;

-- Step 5: Alter supplier_return_items table - ensure consistent precision
-- (This table may already be DECIMAL(10,2), but we want DECIMAL(10,4) for consistency)
ALTER TABLE supplier_return_items 
MODIFY COLUMN supplier_return_item_quantity DECIMAL(10,4) NOT NULL;

-- Step 6: Verify the changes
SELECT 'After migration - verifying column types' AS status;
DESCRIBE invoice_items;
DESCRIBE purchase_items;
DESCRIBE return_items;
DESCRIBE supplier_return_items;

-- Step 7: Data integrity check - find any inconsistencies
-- This query finds purchase items where remaining qty doesn't match expected
-- (purchased - sold - returned to supplier should equal remaining)
SELECT 
    pi.purchase_item_id,
    pi.purchase_item_quantity as purchased,
    COALESCE(sold.qty_sold, 0) as sold,
    COALESCE(sr.qty_returned, 0) as returned_to_supplier,
    COALESCE(cr.qty_customer_returned, 0) as customer_returned,
    pi.purchase_item_remaining_quantity as actual_remaining,
    (pi.purchase_item_quantity - COALESCE(sold.qty_sold, 0) - COALESCE(sr.qty_returned, 0) + COALESCE(cr.qty_customer_returned, 0)) as expected_remaining,
    (pi.purchase_item_remaining_quantity - (pi.purchase_item_quantity - COALESCE(sold.qty_sold, 0) - COALESCE(sr.qty_returned, 0) + COALESCE(cr.qty_customer_returned, 0))) as difference
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 sold.purchase_item_id = pi.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
) sr ON sr.purchase_item_id = pi.purchase_item_id
LEFT JOIN (
    SELECT purchase_item_id, SUM(return_item_quantity) as qty_customer_returned 
    FROM return_items 
    GROUP BY purchase_item_id
) cr ON cr.purchase_item_id = pi.purchase_item_id
WHERE pi.purchase_item_remaining_quantity != (pi.purchase_item_quantity - COALESCE(sold.qty_sold, 0) - COALESCE(sr.qty_returned, 0) + COALESCE(cr.qty_customer_returned, 0))
ORDER BY ABS(pi.purchase_item_remaining_quantity - (pi.purchase_item_quantity - COALESCE(sold.qty_sold, 0) - COALESCE(sr.qty_returned, 0) + COALESCE(cr.qty_customer_returned, 0))) DESC;

-- Step 8: OPTIONAL - Fix remaining quantities based on actual sales/returns
-- Uncomment this section only after reviewing the inconsistencies above
-- WARNING: This will modify data - make sure you have a backup!
/*
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 sold.purchase_item_id = pi.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
) sr ON sr.purchase_item_id = pi.purchase_item_id
LEFT JOIN (
    SELECT purchase_item_id, SUM(return_item_quantity) as qty_customer_returned 
    FROM return_items 
    GROUP BY purchase_item_id
) cr ON cr.purchase_item_id = pi.purchase_item_id
SET 
    pi.purchase_item_remaining_quantity = GREATEST(0, pi.purchase_item_quantity - COALESCE(sold.qty_sold, 0) - COALESCE(sr.qty_returned, 0) + COALESCE(cr.qty_customer_returned, 0)),
    pi.purchase_item_is_active = CASE 
        WHEN (pi.purchase_item_quantity - COALESCE(sold.qty_sold, 0) - COALESCE(sr.qty_returned, 0) + COALESCE(cr.qty_customer_returned, 0)) > 0 THEN 1 
        ELSE 0 
    END;
*/

SELECT 'Migration completed successfully!' AS status;