CREATE OR REPLACE PROCEDURE public.update_and_insert_stock_take(
    IN json_input jsonb,
    INOUT result jsonb)
LANGUAGE plpgsql
AS $$
DECLARE
    v_stock_take_id INTEGER;  
    v_company_id INTEGER;      
    v_created_by INTEGER;      
    v_highest_version INTEGER;  
BEGIN
    -- Extract the input values from the JSON
    v_stock_take_id := (json_input ->> 'stock_take_id')::INTEGER;
    v_company_id := (json_input ->> 'company_id')::INTEGER;
    v_created_by := (json_input ->> 'created_by')::INTEGER;

    RAISE NOTICE 'Received stock_take_id: %, company_id: %, created_by: %', v_stock_take_id, v_company_id, v_created_by;

    -- Initialize the result
    result := jsonb_build_object('status', 'success', 'details', '[]');

    -- Fetch the highest version number for the given stock_take_id
    SELECT MAX(version_number) INTO v_highest_version
    FROM stock_audit_entry_header
    WHERE stock_take_id = v_stock_take_id;

    RAISE NOTICE 'Highest version number found: %', v_highest_version;

    IF v_highest_version IS NOT NULL THEN
        -- Update the record with the highest version number
        UPDATE stock_audit_entry_header
        SET status_id = 2,
            updated_at = CURRENT_TIMESTAMP,
            updated_by = v_created_by
        WHERE stock_take_id = v_stock_take_id
        AND version_number = v_highest_version;

        RAISE NOTICE 'Record updated for stock_take_id: %', v_stock_take_id;

        -- Insert new record with incremented version number
        INSERT INTO stock_audit_entry_header (stock_take_id, version_number, status_id, company_id, created_by)
        VALUES (
            v_stock_take_id,
            v_highest_version + 1,
            1, 
            v_company_id,
            v_created_by
        );

        RAISE NOTICE 'New entry inserted for stock_take_id: %', v_stock_take_id;

        -- Add result details for the operation
        result := jsonb_set(
            result,
            '{details}',
            (result->'details') || jsonb_build_object('stock_take_id', v_stock_take_id, 'status', 'processed')
        );

    ELSE
        result := jsonb_build_object('status', 'failure', 'error_message', 'stock_take_id not found');
    END IF;

    RAISE NOTICE 'Result: %', result;
    RETURN;
END;
$$;