-- ============================================================================
-- MIGRATION: Enhanced Transfer Trigger with Bulk Breaking Support
-- ============================================================================
-- Created: 2025-10-23
-- Description: 
--   Enhances the inventory transfer trigger to automatically handle bulk
--   breaking. When break_bulk flag is true in an item, the system:
--   1. Decreases source inventory in the "send as" unit
--   2. Increases destination inventory in the "receive as" unit
--   3. Uses convert_product_quantity() for accurate conversion
--   4. Creates proper audit trail with unit information
--
-- Dependencies:
--   - multi_unit_product_support.sql (must be applied first)
--   - inventory_transfer_triggers.sql (enhances existing trigger)
--
-- Example Item with Bulk Breaking:
--   {
--     "product_id": "uuid-here",
--     "quantity": 2,
--     "unit_type": "CARTON",        // Send as CARTON
--     "break_bulk": true,           // Enable breaking
--     "break_to_unit": "PIECE",     // Receive as PIECE
--     "batch_number": "B001",
--     "notes": "Breaking bulk for retail"
--   }
-- ============================================================================

-- ============================================================================
-- Enhanced Transfer Processing Function
-- ============================================================================

CREATE OR REPLACE FUNCTION process_inventory_transfer()
RETURNS TRIGGER AS $$
DECLARE
    v_item jsonb;
    v_product_uuid uuid;
    v_from_branch_name text;
    v_to_branch_name text;
    v_txn_notes text;
    v_debit_txn_id uuid;
    v_credit_txn_id uuid;
    v_item_count integer := 0;
    
    -- Bulk breaking variables
    v_break_bulk boolean;
    v_send_unit varchar(50);
    v_receive_unit varchar(50);
    v_send_quantity numeric;
    v_receive_quantity numeric;
    v_conversion_valid boolean;
    v_conversion_error text;
BEGIN
    -- Only process when status changes to 'completed'
    IF NEW.status = 'completed' AND (TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND COALESCE(OLD.status, '') != 'completed')) THEN
        
        -- Get branch names for better audit trail
        SELECT name INTO v_from_branch_name FROM branches WHERE branch_id = NEW.from_branch_id;
        SELECT name INTO v_to_branch_name FROM branches WHERE branch_id = NEW.to_branch_id;
        
        -- Build base transaction notes
        v_txn_notes := format('Transfer %s: %s → %s', 
            COALESCE(NEW.reference_number, NEW.inventory_transfer_id::text),
            COALESCE(v_from_branch_name, 'Unknown'),
            COALESCE(v_to_branch_name, 'Unknown')
        );
        
        -- Validate items array is not empty
        IF jsonb_array_length(NEW.items) = 0 THEN
            RAISE EXCEPTION 'Cannot complete transfer % - no items specified', NEW.inventory_transfer_id;
        END IF;
        
        -- Loop through each item in the items JSONB array
        FOR v_item IN SELECT * FROM jsonb_array_elements(NEW.items)
        LOOP
            -- Extract and validate product_id
            BEGIN
                v_product_uuid := (v_item->>'product_id')::uuid;
            EXCEPTION WHEN OTHERS THEN
                RAISE EXCEPTION 'Invalid product_id in transfer item: %', v_item->>'product_id';
            END;
            
            -- Extract bulk breaking configuration
            v_break_bulk := COALESCE((v_item->>'break_bulk')::boolean, false);
            v_send_unit := COALESCE(v_item->>'unit_type', 'PIECE');
            v_send_quantity := (v_item->>'quantity')::numeric;
            
            -- Validate quantity
            IF v_send_quantity <= 0 THEN
                RAISE EXCEPTION 'Invalid quantity % for product % in transfer %', 
                    v_send_quantity, v_product_uuid, NEW.inventory_transfer_id;
            END IF;
            
            -- ================================================================
            -- HANDLE BULK BREAKING (if enabled)
            -- ================================================================
            IF v_break_bulk THEN
                -- Get the receive unit from item configuration
                v_receive_unit := v_item->>'break_to_unit';
                
                -- Validate break_to_unit is specified
                IF v_receive_unit IS NULL OR v_receive_unit = '' THEN
                    RAISE EXCEPTION 'Bulk breaking enabled but break_to_unit not specified for product % in transfer %',
                        v_product_uuid, NEW.inventory_transfer_id;
                END IF;
                
                -- Validate units are different (no point breaking to same unit)
                IF v_send_unit = v_receive_unit THEN
                    RAISE WARNING 'Bulk breaking enabled but send and receive units are identical (%) for product % - treating as regular transfer',
                        v_send_unit, v_product_uuid;
                    v_break_bulk := false;
                    v_receive_unit := v_send_unit;
                    v_receive_quantity := v_send_quantity;
                ELSE
                    -- Validate the receive unit is valid for this product
                    SELECT is_valid_unit_for_product(v_product_uuid, v_receive_unit) INTO v_conversion_valid;
                    
                    IF NOT v_conversion_valid THEN
                        RAISE EXCEPTION 'Invalid break_to_unit "%" for product % in transfer %. Unit not configured for this product.',
                            v_receive_unit, v_product_uuid, NEW.inventory_transfer_id;
                    END IF;
                    
                    -- Validate the send unit is valid for this product
                    SELECT is_valid_unit_for_product(v_product_uuid, v_send_unit) INTO v_conversion_valid;
                    
                    IF NOT v_conversion_valid THEN
                        RAISE EXCEPTION 'Invalid unit_type "%" for product % in transfer %. Unit not configured for this product.',
                            v_send_unit, v_product_uuid, NEW.inventory_transfer_id;
                    END IF;
                    
                    -- Convert quantity from send unit to receive unit
                    BEGIN
                        SELECT convert_product_quantity(
                            v_product_uuid,
                            v_send_quantity,
                            v_send_unit,
                            v_receive_unit
                        ) INTO v_receive_quantity;
                        
                        -- Log the conversion
                        RAISE NOTICE 'Bulk breaking: % % → % % (product: %)',
                            v_send_quantity, v_send_unit,
                            v_receive_quantity, v_receive_unit,
                            v_product_uuid;
                            
                    EXCEPTION WHEN OTHERS THEN
                        GET STACKED DIAGNOSTICS v_conversion_error = MESSAGE_TEXT;
                        RAISE EXCEPTION 'Failed to convert % % to % for product % in transfer %: %',
                            v_send_quantity, v_send_unit, v_receive_unit,
                            v_product_uuid, NEW.inventory_transfer_id, v_conversion_error;
                    END;
                    
                    -- Update transaction notes to include bulk breaking info
                    v_txn_notes := v_txn_notes || format(
                        E'\nBulk Breaking: %s %s → %s %s',
                        v_send_quantity, v_send_unit,
                        v_receive_quantity, v_receive_unit
                    );
                END IF;
            ELSE
                -- Regular transfer (no bulk breaking)
                v_receive_unit := v_send_unit;
                v_receive_quantity := v_send_quantity;
            END IF;
            
            -- ================================================================
            -- Create DECREASE transaction at source branch (stock out)
            -- ================================================================
            INSERT INTO inventory_txn (
                inventory_txn_id,
                product_id,
                branch_id,
                change,
                reason,
                unit_type,
                reference_id,
                reference_table,
                batch_number,
                notes,
                org_id,
                user_id,
                processed_at,
                isdeleted,
                updatedat
            ) VALUES (
                uuid_generate_v7(),
                v_product_uuid,
                NEW.from_branch_id,
                -ABS(v_send_quantity),  -- Always negative for decrease, using SEND unit quantity
                'transfer_out',
                v_send_unit,  -- Record transaction in the SEND unit
                NEW.inventory_transfer_id,
                'inventory_transfers',
                v_item->>'batch_number',
                COALESCE(v_item->>'notes' || E'\n' || v_txn_notes, v_txn_notes),
                NEW.org_id,
                NEW.user_id,
                NEW.transfer_date,
                false,
                now()
            ) RETURNING inventory_txn_id INTO v_debit_txn_id;
            
            -- ================================================================
            -- Create INCREASE transaction at destination branch (stock in)
            -- ================================================================
            INSERT INTO inventory_txn (
                inventory_txn_id,
                product_id,
                branch_id,
                change,
                reason,
                unit_type,
                reference_id,
                reference_table,
                batch_number,
                notes,
                org_id,
                user_id,
                processed_at,
                isdeleted,
                updatedat
            ) VALUES (
                uuid_generate_v7(),
                v_product_uuid,
                NEW.to_branch_id,
                ABS(v_receive_quantity),  -- Always positive for increase, using RECEIVE unit quantity
                'transfer_in',
                v_receive_unit,  -- Record transaction in the RECEIVE unit (may differ if bulk breaking)
                NEW.inventory_transfer_id,
                'inventory_transfers',
                v_item->>'batch_number',
                COALESCE(v_item->>'notes' || E'\n' || v_txn_notes, v_txn_notes),
                NEW.org_id,
                NEW.user_id,
                NEW.transfer_date,
                false,
                now()
            ) RETURNING inventory_txn_id INTO v_credit_txn_id;
            
            v_item_count := v_item_count + 1;
            
            -- Log successful transaction pair creation
            IF v_break_bulk THEN
                RAISE NOTICE 'Transfer % (BULK BREAK): Created txn pair [OUT: % (%), IN: % (%)] for product %',
                    NEW.inventory_transfer_id, 
                    v_debit_txn_id, format('%s %s', v_send_quantity, v_send_unit),
                    v_credit_txn_id, format('%s %s', v_receive_quantity, v_receive_unit),
                    v_product_uuid;
            ELSE
                RAISE NOTICE 'Transfer %: Created txn pair [OUT: %, IN: %] for product % (qty: % %)',
                    NEW.inventory_transfer_id, v_debit_txn_id, v_credit_txn_id, 
                    v_product_uuid, v_send_quantity, v_send_unit;
            END IF;
            
        END LOOP;
        
        -- Log completion
        RAISE NOTICE 'Transfer % completed: % items processed, % transactions created',
            NEW.inventory_transfer_id, v_item_count, v_item_count * 2;
        
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Update function comment
COMMENT ON FUNCTION process_inventory_transfer() IS 'Auto-creates paired inventory_txn records (decrease at source, increase at destination) when transfer status becomes completed. Supports bulk breaking: sends in one unit, receives in another (e.g., send 2 CARTON, receive 80 PIECE). Uses convert_product_quantity() for accurate conversions.';

-- ============================================================================
-- STEP 3: Attach trigger to inventory_transfers table
-- ============================================================================

-- Drop existing trigger if it exists
DROP TRIGGER IF EXISTS trg_process_inventory_transfer ON inventory_transfers;

-- Create trigger that fires AFTER INSERT OR UPDATE
CREATE TRIGGER trg_process_inventory_transfer
    AFTER INSERT OR UPDATE OF status, items
    ON inventory_transfers
    FOR EACH ROW
    EXECUTE FUNCTION process_inventory_transfer();

-- Add trigger comment
COMMENT ON TRIGGER trg_process_inventory_transfer ON inventory_transfers IS 'Processes completed inventory transfers by creating matching debit/credit inventory_txn records with automatic bulk breaking support';

-- ============================================================================
-- VERIFICATION & TESTING
-- ============================================================================

/*
-- Test 1: Verify function exists and is updated
SELECT 
    p.proname as function_name,
    pg_get_functiondef(p.oid) as definition,
    obj_description(p.oid) as description
FROM pg_proc p
WHERE p.proname = 'process_inventory_transfer';

-- Test 2: Create a test transfer with bulk breaking
DO $$
DECLARE
    v_transfer_id uuid;
    v_product_id uuid;
    v_from_branch uuid;
    v_to_branch uuid;
    v_org_id uuid;
    v_user_id uuid;
BEGIN
    -- Get test data (adjust as needed for your database)
    SELECT product_id INTO v_product_id 
    FROM products 
    WHERE master_unit IS NOT NULL 
    LIMIT 1;
    
    SELECT branch_id INTO v_from_branch 
    FROM branches 
    ORDER BY name 
    LIMIT 1;
    
    SELECT branch_id INTO v_to_branch 
    FROM branches 
    ORDER BY name 
    OFFSET 1 LIMIT 1;
    
    SELECT org_id INTO v_org_id FROM orgs LIMIT 1;
    SELECT user_id INTO v_user_id FROM users LIMIT 1;
    
    -- Create transfer with bulk breaking
    INSERT INTO inventory_transfers (
        inventory_transfer_id,
        from_branch_id,
        to_branch_id,
        transfer_date,
        reference_number,
        items,
        status,
        notes,
        org_id,
        user_id
    ) VALUES (
        uuid_generate_v7(),
        v_from_branch,
        v_to_branch,
        CURRENT_DATE,
        'TEST-BULK-BREAK-001',
        format('[{
            "product_id": "%s",
            "quantity": 2,
            "unit_type": "CARTON",
            "break_bulk": true,
            "break_to_unit": "PIECE",
            "batch_number": "TEST-001",
            "notes": "Test bulk breaking"
        }]', v_product_id)::jsonb,
        'completed',  -- Automatically triggers transaction creation
        'Test transfer with bulk breaking',
        v_org_id,
        v_user_id
    ) RETURNING inventory_transfer_id INTO v_transfer_id;
    
    RAISE NOTICE 'Test transfer created: %', v_transfer_id;
    
    -- Verify transactions were created with correct units
    RAISE NOTICE 'Checking transactions...';
    
    PERFORM * FROM inventory_txn
    WHERE reference_id = v_transfer_id
      AND reference_table = 'inventory_transfers';
    
    IF FOUND THEN
        RAISE NOTICE 'Transactions created successfully! Check inventory_txn table.';
    ELSE
        RAISE WARNING 'No transactions found for transfer %', v_transfer_id;
    END IF;
END $$;

-- Test 3: Verify transaction details
SELECT 
    t.reference_number,
    t.status,
    fb.name as from_branch,
    tb.name as to_branch,
    p.name as product_name,
    txn.reason,
    txn.change,
    txn.unit_type,
    b.name as transaction_branch,
    txn.batch_number,
    txn.notes
FROM inventory_transfers t
JOIN branches fb ON t.from_branch_id = fb.branch_id
JOIN branches tb ON t.to_branch_id = tb.branch_id
JOIN inventory_txn txn ON txn.reference_id = t.inventory_transfer_id
JOIN products p ON txn.product_id = p.product_id
JOIN branches b ON txn.branch_id = b.branch_id
WHERE t.reference_number = 'TEST-BULK-BREAK-001'
ORDER BY txn.reason;

-- Expected Output:
-- Row 1: transfer_out, change: -2, unit_type: CARTON (source branch)
-- Row 2: transfer_in, change: 80 (or appropriate converted amount), unit_type: PIECE (destination branch)
*/

-- ============================================================================
-- EXAMPLE USAGE
-- ============================================================================

/*
-- Example 1: Regular transfer (no bulk breaking)
INSERT INTO inventory_transfers (
    from_branch_id, 
    to_branch_id, 
    reference_number, 
    items, 
    status,
    org_id, 
    user_id
) VALUES (
    (SELECT branch_id FROM branches WHERE name = 'Main Warehouse' LIMIT 1),
    (SELECT branch_id FROM branches WHERE name = 'Retail Store' LIMIT 1),
    'TRF-2025-100',
    '[{
        "product_id": "product-uuid-here",
        "quantity": 50,
        "unit_type": "PIECE",
        "batch_number": "B001"
    }]'::jsonb,
    'completed',
    (SELECT org_id FROM orgs LIMIT 1),
    (SELECT user_id FROM users LIMIT 1)
);
-- Result: -50 PIECE at source, +50 PIECE at destination

-- Example 2: Bulk breaking transfer (send cartons, receive pieces)
INSERT INTO inventory_transfers (
    from_branch_id, 
    to_branch_id, 
    reference_number, 
    items, 
    status,
    org_id, 
    user_id
) VALUES (
    (SELECT branch_id FROM branches WHERE name = 'Main Warehouse' LIMIT 1),
    (SELECT branch_id FROM branches WHERE name = 'Retail Store' LIMIT 1),
    'TRF-2025-101',
    '[{
        "product_id": "product-uuid-here",
        "quantity": 3,
        "unit_type": "CARTON",
        "break_bulk": true,
        "break_to_unit": "PIECE",
        "batch_number": "B002",
        "notes": "Breaking cartons for retail sales"
    }]'::jsonb,
    'completed',
    (SELECT org_id FROM orgs LIMIT 1),
    (SELECT user_id FROM users LIMIT 1)
);
-- Result: -3 CARTON at source, +120 PIECE at destination (if 1 CARTON = 40 PIECE)

-- Example 3: Mixed transfer (some items with bulk breaking, some without)
INSERT INTO inventory_transfers (
    from_branch_id, 
    to_branch_id, 
    reference_number, 
    items, 
    status,
    org_id, 
    user_id
) VALUES (
    (SELECT branch_id FROM branches WHERE name = 'Main Warehouse' LIMIT 1),
    (SELECT branch_id FROM branches WHERE name = 'Retail Store' LIMIT 1),
    'TRF-2025-102',
    '[
        {
            "product_id": "product-1-uuid",
            "quantity": 2,
            "unit_type": "CARTON",
            "break_bulk": true,
            "break_to_unit": "PIECE",
            "batch_number": "B003"
        },
        {
            "product_id": "product-2-uuid",
            "quantity": 10,
            "unit_type": "PIECE",
            "batch_number": "B004"
        }
    ]'::jsonb,
    'completed',
    (SELECT org_id FROM orgs LIMIT 1),
    (SELECT user_id FROM users LIMIT 1)
);
-- Result: 
--   Product 1: -2 CARTON at source, +80 PIECE at destination (bulk broken)
--   Product 2: -10 PIECE at source, +10 PIECE at destination (regular)

-- Example 4: Multi-level bulk breaking (pallet to cartons)
INSERT INTO inventory_transfers (
    from_branch_id, 
    to_branch_id, 
    reference_number, 
    items, 
    status,
    org_id, 
    user_id
) VALUES (
    (SELECT branch_id FROM branches WHERE name = 'Distribution Center' LIMIT 1),
    (SELECT branch_id FROM branches WHERE name = 'Regional Warehouse' LIMIT 1),
    'TRF-2025-103',
    '[{
        "product_id": "product-uuid-here",
        "quantity": 1,
        "unit_type": "PALLET",
        "break_bulk": true,
        "break_to_unit": "CARTON",
        "batch_number": "B005",
        "notes": "Breaking pallet for regional distribution"
    }]'::jsonb,
    'completed',
    (SELECT org_id FROM orgs LIMIT 1),
    (SELECT user_id FROM users LIMIT 1)
);
-- Result: -1 PALLET at source, +80 CARTON at destination (if 1 PALLET = 80 CARTON)
*/

-- ============================================================================
-- DEPLOYMENT SUCCESS MESSAGE
-- ============================================================================

DO $$
BEGIN
    RAISE NOTICE '';
    RAISE NOTICE '=========================================================';
    RAISE NOTICE 'MIGRATION SUCCESSFUL: Enhanced Transfer Trigger';
    RAISE NOTICE '=========================================================';
    RAISE NOTICE 'The inventory transfer trigger now supports bulk breaking!';
    RAISE NOTICE '';
    RAISE NOTICE 'Features:';
    RAISE NOTICE '  ✓ Automatic unit conversion during transfers';
    RAISE NOTICE '  ✓ Send in one unit, receive in another';
    RAISE NOTICE '  ✓ Full validation and error handling';
    RAISE NOTICE '  ✓ Complete audit trail with unit information';
    RAISE NOTICE '';
    RAISE NOTICE 'Usage: Add "break_bulk": true and "break_to_unit": "UNIT"';
    RAISE NOTICE '       to items in inventory_transfers.items JSONB';
    RAISE NOTICE '';
    RAISE NOTICE 'Example: Send 2 CARTON, receive as 80 PIECE automatically';
    RAISE NOTICE '=========================================================';
    RAISE NOTICE '';
END $$;
