CREATE OR REPLACE PROCEDURE public.create_record(
    IN json_input JSONB,
    INOUT result JSONB)
LANGUAGE 'plpgsql'
AS $$
DECLARE
    v_table_name TEXT;
    v_insert_values JSONB;
    v_print_query BOOLEAN;
    v_columns TEXT := '';
    v_values TEXT := '';
    v_query_str TEXT;
    v_executed_query TEXT := '';
    v_execution_status BOOLEAN := FALSE;
    v_company_id INT;
    v_created_by INT;
    v_updated_by INT;
    v_updated_at TIMESTAMPTZ;
    v_col_name TEXT;
    v_col_value TEXT;
    v_is_array BOOLEAN;
    v_inserted_count INT := 0;
    v_inserted_records JSONB := '[]';
    v_record JSONB;
BEGIN
    -- Parse the JSON input
    v_table_name := json_input->>'table_name';
    v_insert_values := json_input->'insert_values';
    v_company_id := (json_input->>'company_id')::INT;
    v_print_query := COALESCE((json_input->>'print_query')::BOOLEAN, FALSE);

    -- Optional fields
    v_created_by := COALESCE((json_input->>'created_by')::INT, NULL);
    v_updated_by := COALESCE((json_input->>'updated_by')::INT, NULL);
    v_updated_at := COALESCE((json_input->>'updated_at')::TIMESTAMPTZ, NULL);

    -- Validate the required fields
    IF v_table_name IS NULL OR v_insert_values IS NULL OR v_company_id IS NULL THEN
        RAISE EXCEPTION 'Invalid input: table_name, insert_values, and company_id are required';
    END IF;

    -- Check if insert_values is an array or not
    v_is_array := jsonb_typeof(v_insert_values) = 'array';

    -- Build the columns and values for the insert query
    IF v_is_array THEN
        -- For array of insert values
        FOR i IN 0 .. jsonb_array_length(v_insert_values) - 1 LOOP
            v_columns := '';
            v_values := '';
            v_record := v_insert_values->i;

            FOR v_col_name, v_col_value IN SELECT * FROM jsonb_each_text(v_record) LOOP
                v_columns := v_columns || format('%I, ', v_col_name);
                v_values := v_values || format('%L, ', v_col_value);
            END LOOP;

            -- Add company_id to the columns and values
            v_columns := v_columns || 'company_id';
            v_values := v_values || quote_literal(v_company_id);

            -- Update created_by, updated_by, updated_at fields if provided
            IF v_created_by IS NOT NULL THEN
                v_columns := v_columns || ', created_by';
                v_values := v_values || ', ' || quote_literal(v_created_by);
            END IF;
            IF v_updated_by IS NOT NULL THEN
                v_columns := v_columns || ', updated_by';
                v_values := v_values || ', ' || quote_literal(v_updated_by);
            END IF;
            IF v_updated_at IS NOT NULL THEN
                v_columns := v_columns || ', updated_at';
                v_values := v_values || ', ' || quote_literal(v_updated_at);
            END IF;

            -- Initialize query string
            v_query_str := format('INSERT INTO %I (%s) VALUES (%s) RETURNING *', v_table_name, v_columns, v_values);

            -- Execute the insert query
            EXECUTE v_query_str INTO v_record;
            v_inserted_records := v_inserted_records || jsonb_build_array(v_record);
            v_inserted_count := v_inserted_count + 1;
        END LOOP;
    ELSE
        -- For single insert value
        v_columns := '';
        v_values := '';
        FOR v_col_name, v_col_value IN SELECT * FROM jsonb_each_text(v_insert_values) LOOP
            v_columns := v_columns || format('%I, ', v_col_name);
            v_values := v_values || format('%L, ', v_col_value);
        END LOOP;

        -- Add company_id to the columns and values
        v_columns := v_columns || 'company_id';
        v_values := v_values || quote_literal(v_company_id);

        -- Update created_by, updated_by, updated_at fields if provided
        IF v_created_by IS NOT NULL THEN
            v_columns := v_columns || ', created_by';
            v_values := v_values || ', ' || quote_literal(v_created_by);
        END IF;
        IF v_updated_by IS NOT NULL THEN
            v_columns := v_columns || ', updated_by';
            v_values := v_values || ', ' || quote_literal(v_updated_by);
        END IF;
        IF v_updated_at IS NOT NULL THEN
            v_columns := v_columns || ', updated_at';
            v_values := v_values || ', ' || quote_literal(v_updated_at);
        END IF;

        -- Initialize query string
        v_query_str := format('INSERT INTO %I (%s) VALUES (%s) RETURNING *', v_table_name, v_columns, v_values);

        -- Execute the insert query
        EXECUTE v_query_str INTO v_record;
        v_inserted_records := jsonb_build_array(v_record);
        v_inserted_count := 1;
    END IF;

    -- Store the executed query if print_query is true
    IF v_print_query THEN
        v_executed_query := v_query_str;
    END IF;

    -- Set execution status to true if query executed successfully
    v_execution_status := TRUE;

    -- Set the output result
    result := jsonb_build_object(
        'query_executed', v_executed_query,
        'inserted_count', v_inserted_count,
        'inserted_records', v_inserted_records,
        'error_type', '',
        'error_message', '',
        'execution_status', v_execution_status
    );
EXCEPTION
    WHEN OTHERS THEN
        -- Handle exceptions and set the result with the error message
        result := jsonb_build_object(
            'query_executed', v_executed_query,
            'inserted_count', v_inserted_count,
            'inserted_records', v_inserted_records,
            'error_type', SQLSTATE,
            'error_message', SQLERRM,
            'execution_status', v_execution_status
        );
END;
$$;

/*-------------------------------------------------------------------------------------------------------
DO $$
DECLARE
    input JSONB;
    output JSONB;
BEGIN
    -- Single record insertion example
    input := '{
        "table_name": "roles",
        "insert_values": {
            "uuid": "0664b4aa-2463-4e3a-b61b-432c0dd34123",
            "name": "role1"
        },
        "company_id": 1,
        "updated_by": 1,
        "created_by": 1,
		"updated_at": "now()",
        "print_query": true
    }';

    CALL public.create_record(input, output);
    RAISE NOTICE 'Output for single record: %', output;
END $$;

DO $$
DECLARE
    input JSONB;
    output JSONB;
BEGIN
    -- Single record insertion example
    input := '{
        "table_name": "roles",
        "insert_values": [{
            "uuid": "0664b4aa-2463-4e3a-b61b-432c0dd34434",
            "name": "role1"
        },{
            "uuid": "0664b4aa-2463-4e3a-b61b-432c0dd34435",
            "name": "role1"
        }],
        "company_id": 1,
        "updated_by": 1,
        "created_by": 1,
		"updated_at": "now()",
        "print_query": true
    }';

    CALL public.create_record(input, output);
    RAISE NOTICE 'Output for single record: %', output;
END $$;

*/


