CREATE OR REPLACE PROCEDURE consolidate_stock_take(
    IN json_input JSONB,
    INOUT result JSONB
)
LANGUAGE plpgsql
AS $$
DECLARE
    stock_record JSONB;
    rec_company_id INTEGER;
    rec_item_id INTEGER;
    rec_uom_id INTEGER;
    rec_item_status_id INTEGER;
    rec_onhand_quantity INTEGER;
    rec_identified_quantity INTEGER;
    rec_is_audit_completed BOOLEAN;
    rec_version_number INTEGER;
    input_type TEXT;
    input_stock_take_id INTEGER;
    input_user_id INTEGER;
    input_stock_take_status_id INTEGER;
    input_company_id INTEGER;
BEGIN
    -- Initialize result if it is null
    IF result IS NULL THEN
        result := jsonb_build_object('status', 'success', 'details', '[]');
    END IF;

    -- Extract the "type" and other input values from the input JSON
    input_type := json_input ->> 'type';
    input_stock_take_id := (json_input ->> 'stock_take_id')::INTEGER;
    input_user_id := (json_input ->> 'user_id')::INTEGER;
    input_company_id := (json_input ->> 'company_id')::INTEGER;
    input_stock_take_status_id := (json_input ->> 'stock_take_status_id')::INTEGER;
    
    -- Update stock_take status if the type is "stock_audit_close"
    IF input_type = 'stock_audit_close' THEN
        UPDATE stock_take
        SET stock_take_status_id = input_stock_take_status_id,
            updated_by = input_user_id,
            updated_at = CURRENT_TIMESTAMP
        WHERE id = input_stock_take_id AND company_id = input_company_id;
    END IF;

    -- Process each record in the "records" array
    FOR stock_record IN SELECT * FROM jsonb_array_elements(json_input -> 'records')
    LOOP
        -- Extract values from each JSON object
        rec_company_id := (stock_record ->> 'company_id')::INTEGER;
        rec_item_id := (stock_record ->> 'item_id')::INTEGER;
        rec_uom_id := (stock_record ->> 'uom_id')::INTEGER;
        rec_item_status_id := (stock_record ->> 'item_status_id')::INTEGER;
        rec_onhand_quantity := (stock_record ->> 'onhand_quantity')::INTEGER;
        rec_identified_quantity := (stock_record ->> 'identified_quantity')::INTEGER;
        rec_is_audit_completed := (stock_record ->> 'is_audit_completed')::BOOLEAN;
        rec_version_number := (stock_record ->> 'version_number')::INTEGER;

        -- Insert or update based on conflict
        INSERT INTO onhand_stocks (
            company_id, stock_take_id, item_id, uom_id, item_status_id, 
            onhand_quantity, identified_quantity, is_audit_completed, version_number, created_by
        )
        VALUES (
            rec_company_id, input_stock_take_id, rec_item_id, rec_uom_id, rec_item_status_id,
            rec_onhand_quantity, rec_identified_quantity, rec_is_audit_completed, rec_version_number, input_user_id
        )
        ON CONFLICT (company_id, stock_take_id, item_id, uom_id, item_status_id)
        DO UPDATE SET
            identified_quantity = EXCLUDED.identified_quantity,
            version_number = EXCLUDED.version_number,
            is_audit_completed = EXCLUDED.is_audit_completed
        WHERE onhand_stocks.is_audit_completed = false;

        -- Append details to the result
        result := jsonb_set(
            result,
            '{details}',
            (result->'details') || jsonb_build_object('company_id', rec_company_id, 'item_id', rec_item_id, 'status', 'processed')
        );
    END LOOP;

    -- Completion message
    RAISE NOTICE 'Procedure executed successfully.';

EXCEPTION
    -- Handle errors and update the result with error information
    WHEN OTHERS THEN
        result := jsonb_build_object('status', 'failure', 'error_message', SQLERRM);
        RAISE NOTICE 'An error occurred: %', SQLERRM;
END;
$$;