CREATE OR REPLACE PROCEDURE update_and_insert_stock_take(
    IN stock_take_data JSON
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_stock_take_id INTEGER;
    v_company_id INTEGER;
    v_created_by INTEGER;    
    highest_version INTEGER;
    result JSONB;  -- Local variable to hold the result
BEGIN
    -- Extract stock_take_id, company_id, and created_by from the JSON input
    v_stock_take_id := (stock_take_data ->> 'stock_take_id')::INTEGER;
    v_company_id := (stock_take_data ->> 'company_id')::INTEGER;
    v_created_by := (stock_take_data ->> 'created_by')::INTEGER;

    -- Initialize the result as an empty JSON object
    result := '{}'::JSONB;

    -- Step 1: Find the highest version_number for the given stock_take_id
    SELECT MAX(version_number) INTO highest_version
    FROM stock_audit_entry_header
    WHERE stock_take_id = v_stock_take_id;

    -- Step 2: Check if stock_take_id exists and if the highest version is found
    IF highest_version IS NOT NULL THEN
        -- Step 3: Update the status of the highest version to 2
        UPDATE stock_audit_entry_header
        SET status_id = 2
        WHERE stock_take_id = v_stock_take_id
        AND version_number = highest_version;

        -- Step 4: Insert a new entry with version_number = highest_version + 1, status = 1
        INSERT INTO stock_audit_entry_header (
            stock_take_id, 
            version_number, 
            status_id, 
            company_id, 
            created_by 
        )
        VALUES (
            v_stock_take_id,
            highest_version + 1,
            1,                -- New status
            v_company_id,
            v_created_by 
        );

        -- Prepare the output JSON with the new entry details
        result := jsonb_build_object(
            'stock_take_id', v_stock_take_id,
            'new_version', highest_version + 1,
            'status', 1
        );
    ELSE
        RAISE NOTICE 'stock_take_id % not found in stock_audit_entry_header', v_stock_take_id;
        result := jsonb_build_object('error', 'stock_take_id not found');
    END IF;

    -- Output result as a notice
    RAISE NOTICE 'Result: %', result::TEXT;
END;
$$;