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

    -- Declare variables
    DECLARE @v_primary_table NVARCHAR(255),
            @v_select_clause NVARCHAR(MAX) = '',
            @v_join_clause NVARCHAR(MAX) = '',
            @v_where_clause NVARCHAR(MAX) = ' WHERE 1=1 ',
            @v_group_by_clause NVARCHAR(MAX) = '',
            @v_having_clause NVARCHAR(MAX) = '',
            @v_order_by_clause NVARCHAR(MAX) = '',
            @v_query_str NVARCHAR(MAX) = '',
            @v_total_records_count INT = 0,
            @v_records NVARCHAR(MAX) = '{}',
            @v_company_id INT,
            @v_print_query BIT,
            @v_start_index INT = 0,
            @v_limit_range INT = 15;

    -- Declare missing variable
    DECLARE @v_alias_mapping NVARCHAR(MAX) = '{}';

    -- Parse JSON input
    SET @v_company_id = JSON_VALUE(@json_input, '$.company_id');
    SET @v_primary_table = JSON_VALUE(@json_input, '$.primary_table');
    SET @v_print_query = JSON_VALUE(@json_input, '$.print_query');
    SET @v_start_index = COALESCE(JSON_VALUE(@json_input, '$.start_index'), 0);
    SET @v_limit_range = COALESCE(JSON_VALUE(@json_input, '$.limit_range'), 15);

    -- Build SELECT clause
    DECLARE @col_name NVARCHAR(MAX), @col_alias NVARCHAR(255);
    DECLARE curSelect CURSOR FOR
    SELECT JSON_VALUE(value, '$[0]'), JSON_VALUE(value, '$[1]')
    FROM OPENJSON(@json_input, '$.select_columns');
    
    OPEN curSelect;
    FETCH NEXT FROM curSelect INTO @col_name, @col_alias;
    
    -- Process each column
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Ensure proper space between columns
        IF @v_select_clause <> '' 
            SET @v_select_clause = @v_select_clause + ', ';

        -- If alias is provided, use it
        IF @col_alias IS NOT NULL AND @col_alias <> ''
        BEGIN
            SET @v_select_clause = @v_select_clause + @col_name + ' AS ' + QUOTENAME(@col_alias);
        END
        ELSE
        BEGIN
            -- No alias provided, just use the column name
            SET @v_select_clause = @v_select_clause + @col_name;
        END

        -- Fetch the next column
        FETCH NEXT FROM curSelect INTO @col_name, @col_alias;
    END

    -- Close the cursor
    CLOSE curSelect;
    DEALLOCATE curSelect;

    -- Ensure @v_select_clause is not empty
    IF @v_select_clause = ''
        SET @v_select_clause = QUOTENAME(@v_primary_table) + '.*';  -- Default to selecting all columns

    -- Build JOIN clauses using build_includes
    DECLARE @v_include_result NVARCHAR(MAX), @includes_param NVARCHAR(MAX), @select_clause NVARCHAR(MAX);
    SET @includes_param = JSON_QUERY(@json_input, '$.includes');
    
    EXEC dbo.build_includes 
        @includes = @includes_param,
        @parent_table = @v_primary_table,
        @parent_alias = '',
        @select_clause = @select_clause OUTPUT,
        @join_clause = @v_join_clause OUTPUT,
        @alias_mapping = @v_alias_mapping OUTPUT;

    -- Build WHERE clause
    IF @v_company_id IS NOT NULL
        SET @v_where_clause = @v_where_clause + ' AND ' + QUOTENAME(@v_primary_table) + '.company_id = ' + CAST(@v_company_id AS NVARCHAR);

    -- Handle search_all conditions
    DECLARE @v_search_all NVARCHAR(MAX), @v_search_any NVARCHAR(MAX), @search_all_conditions NVARCHAR(MAX), @search_any_conditions NVARCHAR(MAX);
    SET @v_search_all = ISNULL(JSON_QUERY(@json_input, '$.search_all'), '[]');
    SET @v_search_any = ISNULL(JSON_QUERY(@json_input, '$.search_any'), '[]');
    PRINT @v_search_all;
    EXEC dbo.build_search_conditions @search_all=@v_search_all, @search_conditions=@search_all_conditions OUTPUT;
    EXEC dbo.build_search_any_conditions @search_any=@v_search_any, @search_conditions=@search_any_conditions OUTPUT;

    IF @search_all_conditions <> '' 
        SET @v_where_clause = @v_where_clause + ' AND ' + @search_all_conditions;

    IF @search_any_conditions <> '' 
        SET @v_where_clause = @v_where_clause + ' AND ' + @search_any_conditions;

    -- Handle GROUP BY clause
    DECLARE @groupCol NVARCHAR(MAX);
    DECLARE curGroupBy CURSOR FOR 
    SELECT value FROM OPENJSON(@json_input, '$.group_by');

    OPEN curGroupBy;
    FETCH NEXT FROM curGroupBy INTO @groupCol;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @v_group_by_clause <> '' SET @v_group_by_clause = @v_group_by_clause + ', ';
        SET @v_group_by_clause = @v_group_by_clause + @groupCol;
        FETCH NEXT FROM curGroupBy INTO @groupCol;
    END

    CLOSE curGroupBy;
    DEALLOCATE curGroupBy;

    IF @v_group_by_clause <> '' 
        SET @v_group_by_clause = ' GROUP BY ' + @v_group_by_clause;

    -- Handle HAVING clause
    DECLARE @v_having_conditions NVARCHAR(MAX), @v_having_conditions_str NVARCHAR(MAX);
    SET @v_having_conditions = ISNULL(JSON_QUERY(@json_input, '$.having_conditions'), '[]');
    EXEC dbo.build_search_conditions @search_all=@v_having_conditions, @search_conditions=@v_having_conditions_str OUTPUT;

    IF @v_having_conditions_str <> '' 
        SET @v_having_clause = ' HAVING ' + @v_having_conditions_str;

    DECLARE @v_having_any_conditions NVARCHAR(MAX), @v_having_any_conditions_str NVARCHAR(MAX);
    SET @v_having_any_conditions = ISNULL(JSON_QUERY(@json_input, '$.having_any_conditions'), '[]');
    EXEC dbo.build_search_any_conditions @search_any=@v_having_any_conditions, @search_conditions=@v_having_any_conditions_str OUTPUT;

    IF @v_having_any_conditions_str <> '' AND @v_having_clause = ''
        SET @v_having_clause = ' HAVING ' + @v_having_any_conditions_str;
    ELSE IF @v_having_any_conditions_str <> '' AND @v_having_clause <> ''
        SET @v_having_clause = @v_having_clause + ' AND ' + @v_having_any_conditions_str;

    -- Handle ORDER BY clause
    DECLARE @sortCol NVARCHAR(MAX), @sortDir NVARCHAR(10);
    DECLARE curSort CURSOR FOR 
    SELECT JSON_VALUE(value, '$[0]'), JSON_VALUE(value, '$[1]')
    FROM OPENJSON(@json_input, '$.sort_columns');
    
    OPEN curSort;
    FETCH NEXT FROM curSort INTO @sortCol, @sortDir;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @v_order_by_clause <> '' SET @v_order_by_clause = @v_order_by_clause + ', ';
        SET @v_order_by_clause = @v_order_by_clause + @sortCol + ' ' + @sortDir;
        FETCH NEXT FROM curSort INTO @sortCol, @sortDir;
    END

    CLOSE curSort;
    DEALLOCATE curSort;

    SET @v_query_str = 'SELECT ' + @v_select_clause + ' FROM ' + QUOTENAME(@v_primary_table) + ' ' 
                     + ISNULL(@v_join_clause, '') + ' '
                     + ISNULL(@v_where_clause, '') + ' '
                     + ISNULL(@v_group_by_clause, '') + ' '
                     + ISNULL(@v_having_clause, '');

    PRINT 'Generated Query Without order and limit: ' + @v_query_str;
    DECLARE @countQuery NVARCHAR(MAX);
    SET @countQuery = 'SELECT @count_OUT = COUNT(*) FROM (' + @v_query_str + ') AS subquery';

    EXEC sp_executesql @countQuery, 
        N'@count_OUT INT OUTPUT', 
        @v_total_records_count OUTPUT;

    IF @v_order_by_clause <> '' 
        SET @v_order_by_clause = ' ORDER BY ' + @v_order_by_clause;

    -- Build Final Query
    SET @v_query_str = @v_query_str + ' '
                     + ISNULL(@v_order_by_clause, '') + 
                     ' OFFSET ' + CAST(@v_start_index AS NVARCHAR) + ' ROWS FETCH NEXT ' + CAST(@v_limit_range AS NVARCHAR) + ' ROWS ONLY';
    PRINT 'Generated Query With order and limit: ' + @v_query_str;

    -- Execute query & store results
    DECLARE @resultQuery NVARCHAR(MAX);
    SET @resultQuery = 'SELECT @v_records_OUT = (SELECT (SELECT * FROM (' + @v_query_str + ') AS result FOR JSON AUTO, INCLUDE_NULL_VALUES))';
    EXEC sp_executesql @resultQuery, 
        N'@v_records_OUT NVARCHAR(MAX) OUTPUT', 
        @v_records OUTPUT;

    -- Construct JSON output
    SET @result = '{"total_records_count": ' + CAST(@v_total_records_count AS NVARCHAR) 
        + ', "records": ' + ISNULL(@v_records, '[]') 
        + ', "query_executed": "' + CASE WHEN @v_print_query = 1 THEN REPLACE(@v_query_str, '"', '""') ELSE 'null' END 
        + '", "execution_status": true }';
END;
