USE [lcp_framework]
GO
/****** Object:  StoredProcedure [dbo].[build_search_conditions]    Script Date: 3/5/2025 4:53:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[build_search_conditions]
    @search_all NVARCHAR(MAX),
    @search_conditions NVARCHAR(MAX) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- Declare variables
    DECLARE @col_search_name NVARCHAR(MAX), 
            @col_search_value NVARCHAR(MAX), 
            @col_search_value_arr NVARCHAR(MAX), 
            @col_operator NVARCHAR(10),
            @formatted_values NVARCHAR(MAX),
            @where_clause NVARCHAR(MAX) = '';

    -- Cursor to loop through search_all conditions
    DECLARE curSearchAll CURSOR FOR
    SELECT JSON_VALUE(value, '$.column_name'), 
           JSON_VALUE(value, '$.value'),  
           JSON_QUERY(value, '$.value'),  -- Extract full JSON array if present
           JSON_VALUE(value, '$.operator')
    FROM OPENJSON(@search_all);

    OPEN curSearchAll;
    FETCH NEXT FROM curSearchAll INTO @col_search_name, @col_search_value, @col_search_value_arr, @col_operator;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Handle NULL checks separately
        IF @col_operator = 'IS' OR @col_operator = 'IS NOT'
        BEGIN
            SET @where_clause = @where_clause + ' AND ' + @col_search_name + ' ' + @col_operator + ' NULL ';
        END
        -- Handle `IN` and `NOT IN` by converting JSON array to comma-separated values
        ELSE IF @col_operator = 'IN' OR @col_operator = 'NOT IN'
        BEGIN
            SET @formatted_values = '';

            -- Convert JSON array to comma-separated list (handles both numbers & strings)
            IF @col_search_value_arr IS NOT NULL AND @col_search_value_arr <> '[]'
            BEGIN
                SELECT @formatted_values = STRING_AGG(
                    CASE 
                        WHEN ISNUMERIC(value) = 1 THEN value  -- Keep numbers as is
                        ELSE '''' + value + ''''  -- Wrap string values in single quotes
                    END, ', ')
                FROM OPENJSON(@col_search_value_arr);
            END
            ELSE
                SET @formatted_values = @col_search_value;

            -- Append the IN clause with properly formatted values
            IF @formatted_values IS NOT NULL AND @formatted_values <> ''
                SET @where_clause = @where_clause + ' AND ' + @col_search_name + ' ' + @col_operator + ' (' + @formatted_values + ')';
        END
        -- Handle `BETWEEN` operator
        ELSE IF @col_operator = 'BETWEEN'
        BEGIN
            -- Ensure that `@col_search_value_arr` is used, as it contains the full JSON array
            IF @col_search_value_arr IS NOT NULL AND @col_search_value_arr <> '[]'
            BEGIN
                SET @where_clause = @where_clause + ' AND ' + @col_search_name + ' BETWEEN ''' 
                                    + JSON_VALUE(@col_search_value_arr, '$[0]') + '''' +
                                    ' AND ''' + JSON_VALUE(@col_search_value_arr, '$[1]') + '''';
            END
        END
        ELSE
        BEGIN
            -- Handle string and numeric values correctly
            IF @col_search_value IS NULL
                SET @where_clause = @where_clause + ' AND ' + @col_search_name + ' ' + @col_operator + ' NULL ';
            ELSE
                SET @where_clause = @where_clause + ' AND ' + @col_search_name + ' ' + @col_operator + ' ' + 
                                    CASE 
                                        WHEN ISNUMERIC(@col_search_value) = 1 THEN @col_search_value  -- Keep numbers as is
                                        ELSE '''' + @col_search_value + ''''  -- Wrap string values in single quotes
                                    END;
        END

        -- Fetch next search condition
        FETCH NEXT FROM curSearchAll INTO @col_search_name, @col_search_value, @col_search_value_arr, @col_operator;
    END

    CLOSE curSearchAll;
    DEALLOCATE curSearchAll;

    -- Return the generated WHERE clause (remove leading " AND ")
    IF LEN(@where_clause) > 5
        SET @search_conditions = STUFF(@where_clause, 1, 5, '');  
    ELSE
        SET @search_conditions = '';  -- Ensure empty conditions return blank
END;
