-- ============================================================================
-- MIGRATION: Add batch_number and notes columns to inventory_txn
-- ============================================================================
-- Created: 2025-10-23
-- Description: 
--   Adds batch_number and notes columns to inventory_txn table to support:
--   - Batch/lot number tracking for inventory transactions
--   - Detailed transaction notes and audit trail
--   - Enhanced transfer documentation with bulk breaking notes
--
-- Dependencies: None
-- ============================================================================

-- Add batch_number column
ALTER TABLE inventory_txn 
ADD COLUMN IF NOT EXISTS batch_number VARCHAR(100) DEFAULT NULL;

-- Add notes column
ALTER TABLE inventory_txn 
ADD COLUMN IF NOT EXISTS notes TEXT DEFAULT NULL;

-- Add comments
COMMENT ON COLUMN inventory_txn.batch_number IS 'Batch/lot number for tracking inventory by production batch';
COMMENT ON COLUMN inventory_txn.notes IS 'Transaction notes and audit trail, including bulk breaking information';

-- Create index for batch_number lookups
CREATE INDEX IF NOT EXISTS idx_inventory_txn_batch 
ON inventory_txn(batch_number) 
WHERE batch_number IS NOT NULL AND isDeleted = false;

-- Create index for searching notes
CREATE INDEX IF NOT EXISTS idx_inventory_txn_notes_search 
ON inventory_txn USING gin(to_tsvector('english', notes)) 
WHERE notes IS NOT NULL AND isDeleted = false;

-- ============================================================================
-- VERIFICATION
-- ============================================================================

DO $$
DECLARE
    v_batch_exists boolean;
    v_notes_exists boolean;
BEGIN
    -- Check if batch_number column exists
    SELECT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'inventory_txn' 
        AND column_name = 'batch_number'
    ) INTO v_batch_exists;
    
    -- Check if notes column exists
    SELECT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'inventory_txn' 
        AND column_name = 'notes'
    ) INTO v_notes_exists;
    
    IF v_batch_exists AND v_notes_exists THEN
        RAISE NOTICE '';
        RAISE NOTICE '=========================================================';
        RAISE NOTICE 'MIGRATION SUCCESSFUL: inventory_txn Enhanced';
        RAISE NOTICE '=========================================================';
        RAISE NOTICE 'Added columns:';
        RAISE NOTICE '  ✓ batch_number VARCHAR(100) - Batch/lot tracking';
        RAISE NOTICE '  ✓ notes TEXT - Transaction audit trail';
        RAISE NOTICE '';
        RAISE NOTICE 'Created indexes:';
        RAISE NOTICE '  ✓ idx_inventory_txn_batch - Batch number lookups';
        RAISE NOTICE '  ✓ idx_inventory_txn_notes_search - Full-text search';
        RAISE NOTICE '=========================================================';
        RAISE NOTICE '';
    ELSE
        RAISE WARNING 'Column creation may have failed. Check table structure.';
    END IF;
END $$;

-- Show updated table structure
\d inventory_txn
