-- ============================================================================
-- Phase 7: Multi-Unit Product Support
-- Migration: multi_unit_product_support.sql
-- Purpose: Add helper functions for multi-unit products and bulk breaking
-- ============================================================================

-- ============================================================================
-- HELPER FUNCTIONS
-- ============================================================================

-- Function 1: Get product unit hierarchy
-- Returns the complete unit configuration for a product
CREATE OR REPLACE FUNCTION get_product_unit_hierarchy(p_product_id UUID)
RETURNS TABLE (
    base_unit VARCHAR,
    master_unit VARCHAR,
    units_per_master INTEGER,
    master_unit_2 VARCHAR,
    units_per_master_2 INTEGER
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        p.base_unit,
        p.master_unit,
        p.units_per_master,
        p.master_unit_2,
        p.units_per_master_2
    FROM products p
    WHERE p.product_id = p_product_id
      AND p.isDeleted = false;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION get_product_unit_hierarchy IS 'Returns the complete unit hierarchy for a product (base, master, master_2)';

-- Function 2: Convert quantity between units
-- Converts a quantity from one unit to another based on product configuration
CREATE OR REPLACE FUNCTION convert_product_quantity(
    p_product_id UUID,
    p_quantity NUMERIC,
    p_from_unit VARCHAR,
    p_to_unit VARCHAR
)
RETURNS NUMERIC AS $$
DECLARE
    v_base_unit VARCHAR;
    v_master_unit VARCHAR;
    v_units_per_master INTEGER;
    v_master_unit_2 VARCHAR;
    v_units_per_master_2 INTEGER;
    v_quantity_in_base NUMERIC;
    v_result NUMERIC;
BEGIN
    -- Get product unit hierarchy
    SELECT * INTO v_base_unit, v_master_unit, v_units_per_master, v_master_unit_2, v_units_per_master_2
    FROM get_product_unit_hierarchy(p_product_id);
    
    -- If same unit, no conversion needed
    IF p_from_unit = p_to_unit THEN
        RETURN p_quantity;
    END IF;
    
    -- Validate units exist
    IF v_base_unit IS NULL THEN
        RAISE EXCEPTION 'Product % not found or has no base unit defined', p_product_id;
    END IF;
    
    -- Convert from_unit to base units first
    CASE p_from_unit
        WHEN v_base_unit THEN
            v_quantity_in_base := p_quantity;
        WHEN v_master_unit THEN
            IF v_units_per_master IS NULL THEN
                RAISE EXCEPTION 'Product % has no master unit conversion factor', p_product_id;
            END IF;
            v_quantity_in_base := p_quantity * v_units_per_master;
        WHEN v_master_unit_2 THEN
            IF v_units_per_master_2 IS NULL OR v_units_per_master IS NULL THEN
                RAISE EXCEPTION 'Product % has no master_unit_2 conversion factors', p_product_id;
            END IF;
            v_quantity_in_base := p_quantity * v_units_per_master_2 * v_units_per_master;
        ELSE
            RAISE EXCEPTION 'Invalid from_unit: % for product %. Valid units are: %, %, %', 
                p_from_unit, p_product_id, v_base_unit, v_master_unit, v_master_unit_2;
    END CASE;
    
    -- Convert from base units to to_unit
    CASE p_to_unit
        WHEN v_base_unit THEN
            v_result := v_quantity_in_base;
        WHEN v_master_unit THEN
            IF v_units_per_master IS NULL THEN
                RAISE EXCEPTION 'Product % has no master unit conversion factor', p_product_id;
            END IF;
            v_result := v_quantity_in_base / v_units_per_master;
        WHEN v_master_unit_2 THEN
            IF v_units_per_master_2 IS NULL OR v_units_per_master IS NULL THEN
                RAISE EXCEPTION 'Product % has no master_unit_2 conversion factors', p_product_id;
            END IF;
            v_result := v_quantity_in_base / (v_units_per_master_2 * v_units_per_master);
        ELSE
            RAISE EXCEPTION 'Invalid to_unit: % for product %. Valid units are: %, %, %', 
                p_to_unit, p_product_id, v_base_unit, v_master_unit, v_master_unit_2;
    END CASE;
    
    RETURN v_result;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION convert_product_quantity IS 'Convert quantity between different units for multi-unit products. Example: convert_product_quantity(product_id, 2, ''CARTON'', ''PIECE'') returns 48 if 1 carton = 24 pieces';

-- Function 3: Validate unit for product
-- Checks if a unit is valid for a given product
CREATE OR REPLACE FUNCTION is_valid_unit_for_product(
    p_product_id UUID,
    p_unit VARCHAR
)
RETURNS BOOLEAN AS $$
DECLARE
    v_base_unit VARCHAR;
    v_master_unit VARCHAR;
    v_master_unit_2 VARCHAR;
BEGIN
    SELECT base_unit, master_unit, master_unit_2 
    INTO v_base_unit, v_master_unit, v_master_unit_2
    FROM products
    WHERE product_id = p_product_id AND isDeleted = false;
    
    -- If product not found, return false
    IF v_base_unit IS NULL THEN
        RETURN false;
    END IF;
    
    -- Check if unit matches any of the product's defined units
    RETURN p_unit IN (v_base_unit, v_master_unit, v_master_unit_2);
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION is_valid_unit_for_product IS 'Check if a unit type is valid for a given product (matches base_unit, master_unit, or master_unit_2)';

-- Function 4: Get quantity in base units
-- Converts any quantity to base units for standardized storage/reporting
CREATE OR REPLACE FUNCTION get_quantity_in_base_units(
    p_product_id UUID,
    p_quantity NUMERIC,
    p_unit VARCHAR
)
RETURNS INTEGER AS $$
BEGIN
    RETURN CAST(convert_product_quantity(p_product_id, p_quantity, p_unit, 
        (SELECT base_unit FROM products WHERE product_id = p_product_id AND isDeleted = false)
    ) AS INTEGER);
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION get_quantity_in_base_units IS 'Convert any unit quantity to base units for the product. Used for quantity_in_base_units column in inventory_txn';

-- Function 5: Get available units for product (returns JSON array)
-- Returns all valid units for a product as JSON for UI dropdowns
CREATE OR REPLACE FUNCTION get_product_available_units(p_product_id UUID)
RETURNS JSONB AS $$
DECLARE
    v_base_unit VARCHAR;
    v_master_unit VARCHAR;
    v_master_unit_2 VARCHAR;
    v_units JSONB := '[]'::jsonb;
BEGIN
    SELECT base_unit, master_unit, master_unit_2 
    INTO v_base_unit, v_master_unit, v_master_unit_2
    FROM products
    WHERE product_id = p_product_id AND isDeleted = false;
    
    -- Build JSON array of available units
    IF v_base_unit IS NOT NULL THEN
        v_units := v_units || jsonb_build_object('label', v_base_unit, 'value', v_base_unit);
    END IF;
    
    IF v_master_unit IS NOT NULL THEN
        v_units := v_units || jsonb_build_object('label', v_master_unit, 'value', v_master_unit);
    END IF;
    
    IF v_master_unit_2 IS NOT NULL THEN
        v_units := v_units || jsonb_build_object('label', v_master_unit_2, 'value', v_master_unit_2);
    END IF;
    
    RETURN v_units;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION get_product_available_units IS 'Returns JSON array of available units for a product, suitable for UI dropdowns';

-- ============================================================================
-- EXAMPLES AND VERIFICATION
-- ============================================================================

-- Example 1: Set up a product with 3-level hierarchy
-- PIECE (base) → CARTON (24 pieces) → PALLET (48 cartons = 1152 pieces)
/*
UPDATE products 
SET 
    base_unit = 'PIECE',
    master_unit = 'CARTON',
    units_per_master = 24,
    master_unit_2 = 'PALLET',
    units_per_master_2 = 48
WHERE product_id = 'your-product-uuid';
*/

-- Example 2: Convert 2 cartons to pieces
-- SELECT convert_product_quantity('product-uuid', 2, 'CARTON', 'PIECE');
-- Expected result: 48

-- Example 3: Convert 1 pallet to pieces
-- SELECT convert_product_quantity('product-uuid', 1, 'PALLET', 'PIECE');
-- Expected result: 1152

-- Example 4: Convert 1152 pieces to pallets
-- SELECT convert_product_quantity('product-uuid', 1152, 'PIECE', 'PALLET');
-- Expected result: 1

-- Example 5: Get available units for a product
-- SELECT get_product_available_units('product-uuid');
-- Expected result: [{"label": "PIECE", "value": "PIECE"}, {"label": "CARTON", "value": "CARTON"}, {"label": "PALLET", "value": "PALLET"}]

-- Example 6: Validate unit for product
-- SELECT is_valid_unit_for_product('product-uuid', 'CARTON');
-- Expected result: true
-- SELECT is_valid_unit_for_product('product-uuid', 'BOX');
-- Expected result: false (if BOX is not configured)

-- ============================================================================
-- VERIFICATION QUERIES
-- ============================================================================

-- Check all products with multi-unit configuration
SELECT 
    product_id,
    name,
    base_unit,
    master_unit,
    units_per_master,
    master_unit_2,
    units_per_master_2,
    CASE 
        WHEN master_unit_2 IS NOT NULL THEN '3-level'
        WHEN master_unit IS NOT NULL THEN '2-level'
        ELSE '1-level (base only)'
    END as hierarchy_depth
FROM products
WHERE isDeleted = false
ORDER BY name;

-- Test conversions for a specific product (replace 'product-uuid' with actual UUID)
/*
SELECT 
    'Test Product Conversions' as test_name,
    convert_product_quantity('product-uuid', 1, 'CARTON', 'PIECE') as carton_to_pieces,
    convert_product_quantity('product-uuid', 48, 'PIECE', 'CARTON') as pieces_to_cartons,
    convert_product_quantity('product-uuid', 1, 'PALLET', 'PIECE') as pallet_to_pieces,
    convert_product_quantity('product-uuid', 1, 'PALLET', 'CARTON') as pallet_to_cartons;
*/

SELECT 'Multi-unit product support functions created successfully!' as status;
