
CREATE OR REPLACE PROCEDURE public.update_record(IN json_input jsonb, INOUT result jsonb)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_table_name TEXT;
    v_update_values JSONB;
    v_where_all JSONB;
    v_where_any JSONB;
    v_print_query BOOLEAN;
    v_set_clause TEXT := '';
    v_where_clause_str TEXT := ' WHERE ';
    v_query_str TEXT;
    v_executed_query TEXT := '';
    v_execution_status BOOLEAN := FALSE;
    v_company_id INT;
    v_col_name TEXT;
    v_col_value TEXT;
    v_updated_record RECORD;
    v_updated_ids JSONB := '[]';
    v_updated_by INT;
    v_updated_at TIMESTAMPTZ;
    v_deleted_by INT;
    v_deleted_at TIMESTAMPTZ;
    v_old_record RECORD;
    v_old_value TEXT;
    v_new_value TEXT;
    v_old_record_json JSONB;
    v_audit_log_id INT;

    -- For OR conditions
    v_or_clause TEXT := '';
    v_or_group TEXT := '';

    -- Security: Define valid operators
    v_valid_operators JSONB := jsonb_build_array('=', '!=', '<', '>', '<=', '>=', 'IS', 'IS NOT', 'IN', 'NOT IN', 'LIKE', 'NOT LIKE', 'ILIKE', 'NOT ILIKE', 'BETWEEN');
    -- Loop variables
    v_col_search RECORD;
    v_inner_col_search RECORD;
BEGIN
    -- Create a temporary table to store updated IDs
    CREATE TEMP TABLE temp_updated_ids (id INT PRIMARY KEY);

    -- Parse the JSON input
    v_table_name := json_input->>'table_name';
    v_update_values := json_input->'update_values';
    v_where_all := json_input->'where_all';
    v_where_any := json_input->'where_any';
    v_company_id := COALESCE((json_input->>'company_id')::INT, 0);
    v_print_query := COALESCE((json_input->>'print_query')::BOOLEAN, FALSE);

    -- Optional fields
    v_updated_by := COALESCE((json_input->>'updated_by')::INT, NULL);
    v_updated_at := COALESCE((json_input->>'updated_at')::TIMESTAMPTZ, NULL);
    v_deleted_by := COALESCE((json_input->>'deleted_by')::INT, NULL);
    v_deleted_at := COALESCE((json_input->>'deleted_at')::TIMESTAMPTZ, NULL);

    -- Validate the required fields
    IF v_table_name IS NULL OR v_update_values IS NULL OR (v_where_all IS NULL AND v_where_any IS NULL) OR v_company_id IS NULL THEN
        RAISE EXCEPTION 'Invalid input: table_name, update_values, where_all/where_any, and company_id are required';
    END IF;

    -- Insert a single record into audit_logs
    INSERT INTO public.audit_logs (action_type, entity_name, user_id, company_id, created_at)
    VALUES (1, v_table_name, v_updated_by, v_company_id, CURRENT_TIMESTAMP)
    RETURNING id INTO v_audit_log_id;

    -- Build the WHERE clause
    v_where_clause_str := v_where_clause_str || format('%I.company_id = %L', v_table_name, v_company_id);

    -- Handle the "where_all" conditions with AND
    FOR i IN 0 .. jsonb_array_length(v_where_all) - 1 LOOP
        -- Security check for column name and operator
        IF (v_where_all->i->>'column_name') !~ '^[a-zA-Z0-9_\. ()'',]+$' THEN
            RAISE EXCEPTION 'Invalid characters in column name: %', (v_where_all->i->>'column_name');
        END IF;
        IF NOT v_valid_operators ? (v_where_all->i->>'operator') THEN
            RAISE EXCEPTION 'Invalid operator: %', (v_where_all->i->>'operator');
        END IF;
        -- Construct the condition
        IF (v_where_all->i->>'operator') = 'IN' OR (v_where_all->i->>'operator') = 'NOT IN' THEN
            v_where_clause_str := v_where_clause_str || format(' AND %I %s (%s)',
                (v_where_all->i->>'column_name'),
                (v_where_all->i->>'operator'),
                array_to_string(ARRAY(SELECT quote_literal(trim(both '''' FROM x)) FROM jsonb_array_elements_text(v_where_all->i->'value') x), ', ')
            );
        ELSIF (v_where_all->i->>'operator') = 'BETWEEN' THEN
            v_where_clause_str := v_where_clause_str || format(' AND %I BETWEEN %L AND %L',
                (v_where_all->i->>'column_name'),
                (v_where_all->i->'value')->>0,
                (v_where_all->i->'value')->>1
            );
        ELSE
            IF (v_where_all->i->>'value') IS NOT NULL THEN 
                v_where_clause_str := v_where_clause_str || format(' AND %I %s %L',
                    (v_where_all->i->>'column_name'),
                    (v_where_all->i->>'operator'),
                    (v_where_all->i->>'value')
                );
            ELSIF (v_where_all->i->>'operator' = 'IS' OR (v_where_all->i->>'operator') = 'IS NOT') THEN
                v_where_clause_str := v_where_clause_str || format(' AND %I %s NULL',
                    (v_where_all->i->>'column_name'),
                    (v_where_all->i->>'operator')
                );
            END IF;
        END IF;
    END LOOP;

    -- Handle the "where_any" conditions with nested OR
    FOR v_col_search IN SELECT * FROM jsonb_array_elements(v_where_any) LOOP
        IF jsonb_typeof(v_col_search.value) = 'array' THEN
            -- Multiple sets of conditions
            v_or_group := '';
            FOR v_inner_col_search IN SELECT * FROM jsonb_array_elements(v_col_search.value) LOOP
                -- Security check for column name and operator
                IF (v_inner_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_\. ()'',]+$' THEN
                    RAISE EXCEPTION 'Invalid characters in column name: %', (v_inner_col_search.value->>'column_name');
                END IF;
                IF NOT v_valid_operators ? (v_inner_col_search.value->>'operator') THEN
                    RAISE EXCEPTION 'Invalid operator: %', (v_inner_col_search.value->>'operator');
                END IF;
                -- Construct the condition
                IF (v_inner_col_search.value->>'operator') = 'IN' OR (v_inner_col_search.value->>'operator') = 'NOT IN' THEN
                    v_or_group := v_or_group || (v_inner_col_search.value->>'column_name') || ' ' || (v_inner_col_search.value->>'operator') || ' (' || array_to_string(ARRAY(SELECT quote_literal(trim(both '''' FROM x)) FROM jsonb_array_elements_text(v_inner_col_search.value->'value') x), ', ') || ') OR ';
                ELSIF (v_inner_col_search.value->>'operator') = 'BETWEEN' THEN
                    v_or_group := v_or_group || (v_inner_col_search.value->>'column_name') || ' BETWEEN ' || quote_literal((v_inner_col_search.value->'value')->>0) || ' AND ' || quote_literal((v_inner_col_search.value->'value')->>1) || ' OR ';
                ELSE
                    IF (v_inner_col_search.value->>'value') IS NOT NULL THEN 
                        v_or_group := v_or_group || (v_inner_col_search.value->>'column_name') || ' ' || (v_inner_col_search.value->>'operator') || ' ' || quote_literal((v_inner_col_search.value->>'value')) || ' OR ';
                    ELSIF (v_inner_col_search.value->>'operator' = 'IS' OR v_inner_col_search.value->>'operator' = 'IS NOT') THEN
                        v_or_group := v_or_group || (v_inner_col_search.value->>'column_name') || ' ' || (v_inner_col_search.value->>'operator') || ' ' || 'NULL' || ' OR ';
                    END IF;
                END IF;
            END LOOP;
            -- Remove the trailing ' OR ' and add to the OR clause
            IF v_or_group <> '' THEN
                v_or_group := LEFT(v_or_group, LENGTH(v_or_group) - 4);
                v_or_clause := v_or_clause || '(' || v_or_group || ')' || ' AND ';
            END IF;
        ELSE
            -- Single set of conditions
            -- Security check for column name and operator
            IF (v_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_\. ()'',]+$' THEN
                RAISE EXCEPTION 'Invalid characters in column name: %', (v_col_search.value->>'column_name');
            END IF;
            IF NOT v_valid_operators ? (v_col_search.value->>'operator') THEN
                RAISE EXCEPTION 'Invalid operator: %', (v_col_search.value->>'operator');
            END IF;
            -- Construct the condition
            IF (v_col_search.value->>'operator') = 'IN' OR (v_col_search.value->>'operator') = 'NOT IN' THEN
                v_or_clause := v_or_clause || (v_col_search.value->>'column_name') || ' ' || (v_col_search.value->>'operator') || ' (' || array_to_string(ARRAY(SELECT quote_literal(trim(both '''' FROM x)) FROM jsonb_array_elements_text(v_col_search.value->'value') x), ', ') || ') OR ';
            ELSIF (v_col_search.value->>'operator') = 'BETWEEN' THEN
                v_or_clause := v_or_clause || (v_col_search.value->>'column_name') || ' BETWEEN ' || quote_literal((v_col_search.value->'value')->>0) || ' AND ' || quote_literal((v_col_search.value->'value')->>1) || ' OR ';
            ELSE
                IF (v_col_search.value->>'value') IS NOT NULL THEN 
                    v_or_clause := v_or_clause || (v_col_search.value->>'column_name') || ' ' || (v_col_search.value->>'operator') || ' ' || quote_literal((v_col_search.value->>'value')) || ' OR ';
                ELSIF (v_col_search.value->>'operator' = 'IS' OR v_col_search.value->>'operator' = 'IS NOT') THEN
                    v_or_clause := v_or_clause || (v_col_search.value->>'column_name') || ' ' || (v_col_search.value->>'operator') || ' ' || 'NULL' || ' OR ';
                END IF;
            END IF;
        END IF;
    END LOOP;

    -- Remove the trailing ' OR ' and ' AND ' and add to the where clause
    IF v_or_clause <> '' THEN
        v_or_clause := LEFT(v_or_clause, LENGTH(v_or_clause) - 4);
        v_where_clause_str := v_where_clause_str || ' AND (' || v_or_clause || ')';
    END IF;

    -- Fetch the old records before updating
    FOR v_old_record IN EXECUTE format('SELECT * FROM %I %s', v_table_name, v_where_clause_str) LOOP
        -- Convert the old record to JSONB for dynamic access
        v_old_record_json := to_jsonb(v_old_record);

        -- Build the SET clause for the update query
        v_set_clause := '';
        FOR v_col_name, v_col_value IN SELECT * FROM jsonb_each_text(v_update_values) LOOP
            v_old_value := COALESCE(v_old_record_json->>v_col_name, '');
            v_new_value := COALESCE(v_col_value, '');

            -- Compare old and new values
            IF v_old_value IS DISTINCT FROM v_new_value THEN
                -- Insert into audit_log_line_items
                INSERT INTO public.audit_log_line_items (
                    audit_log_id, entity_id, field_name, old_value, new_value, company_id, created_at
                ) VALUES (
                    v_audit_log_id, v_old_record.id, v_col_name, v_old_value, v_new_value, v_company_id, CURRENT_TIMESTAMP
                );

                -- Add to SET clause
                v_set_clause := v_set_clause || format('%I = %L, ', v_col_name, v_col_value);
            END IF;
        END LOOP;

        -- Add updated_by, updated_at, deleted_by, and deleted_at to the SET clause if provided
        IF v_updated_by IS NOT NULL THEN
            v_set_clause := v_set_clause || format('updated_by = %L, ', v_updated_by);
        END IF;
        IF v_updated_at IS NOT NULL THEN
            v_set_clause := v_set_clause || format('updated_at = %L, ', v_updated_at);
        END IF;
        IF v_deleted_by IS NOT NULL THEN
            v_set_clause := v_set_clause || format('deleted_by = %L, ', v_deleted_by);
        END IF;
        IF v_deleted_at IS NOT NULL THEN
            v_set_clause := v_set_clause || format('deleted_at = %L, ', v_deleted_at);
        END IF;

        -- Remove the trailing comma and space
        v_set_clause := left(v_set_clause, length(v_set_clause) - 2);

        -- Build the update query
        v_query_str := format('UPDATE %I SET %s %s RETURNING *', v_table_name, v_set_clause, v_where_clause_str);

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

        -- Execute the update query and fetch updated records
        FOR v_updated_record IN EXECUTE v_query_str LOOP
            BEGIN
                INSERT INTO temp_updated_ids (id) VALUES (v_updated_record.id);
            EXCEPTION WHEN unique_violation THEN
                -- Ignore duplicate inserts
            END;
        END LOOP;

    END LOOP;

    -- Collect distinct updated IDs
    SELECT jsonb_agg(id) INTO v_updated_ids FROM temp_updated_ids;

    -- Drop the temporary table
    DROP TABLE temp_updated_ids;

    -- 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,
        'updated_ids', v_updated_ids,
        '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,
            'updated_ids', '[]'::jsonb,
            'error_type', SQLSTATE,
            'error_message', SQLERRM,
            'execution_status', FALSE
        );
END;
$procedure$
