USE [lcp_framework]
GO
/****** Object:  StoredProcedure [dbo].[build_includes]    Script Date: 3/5/2025 4:52:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[build_includes]
    @includes NVARCHAR(MAX),
    @parent_table NVARCHAR(255),
    @parent_alias NVARCHAR(255),
    @select_clause NVARCHAR(MAX) OUTPUT,
    @join_clause NVARCHAR(MAX) OUTPUT,
    @alias_mapping NVARCHAR(MAX) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- Initialize output variables
    SET @select_clause = '';
    SET @join_clause = '';
    SET @alias_mapping = '{}';

    -- Declare local variables
    DECLARE @table_name NVARCHAR(MAX), @join_type NVARCHAR(50), @join_condition NVARCHAR(MAX), @alias NVARCHAR(255);
    DECLARE @attributes NVARCHAR(MAX), @nested_include NVARCHAR(MAX);
    DECLARE @col NVARCHAR(MAX);
    
    -- Cursor for processing includes
    DECLARE cur CURSOR FOR 
    SELECT 
        JSON_VALUE(includes.value, '$.table_name'),
        JSON_VALUE(includes.value, '$.join_type'),
        JSON_VALUE(includes.value, '$.join_condition'),
        JSON_VALUE(includes.value, '$.alias'),
        JSON_QUERY(includes.value, '$.attributes'),
        JSON_QUERY(includes.value, '$.includes')
    FROM OPENJSON(@includes) AS includes;
    
    OPEN cur;
    FETCH NEXT FROM cur INTO @table_name, @join_type, @join_condition, @alias, @attributes, @nested_include;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Process attributes
        DECLARE curAttr CURSOR FOR 
        SELECT value FROM OPENJSON(@attributes);

        OPEN curAttr;
        FETCH NEXT FROM curAttr INTO @col;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF @select_clause <> '' 
                SET @select_clause = @select_clause + ', ';

            IF @col LIKE '%json_agg%' OR @col LIKE '%SUM%' OR @col LIKE '%COUNT%' 
                OR @col LIKE '%AVG%' OR @col LIKE '%MIN%' OR @col LIKE '%MAX%'
            BEGIN
                SET @select_clause = @select_clause + @col + ' AS ' + QUOTENAME(JSON_VALUE(@col, '$[1]'));
            END
            ELSE
            BEGIN
                SET @select_clause = @select_clause + @table_name + '.' + JSON_VALUE(@col, '$[0]') + ' AS ' + QUOTENAME(JSON_VALUE(@col, '$[1]'));
            END
            
            -- Build alias mapping
            SET @alias_mapping = JSON_MODIFY(@alias_mapping, '$.' + QUOTENAME(JSON_VALUE(@col, '$[1]')), @table_name + '.' + JSON_VALUE(@col, '$[0]'));

            FETCH NEXT FROM curAttr INTO @col;
        END
        CLOSE curAttr;
        DEALLOCATE curAttr;

        -- Process JOIN clause
        SET @join_clause = @join_clause + ' ' + @join_type + ' JOIN ' + @table_name + ' ON ' + @join_condition;

        -- Handle nested includes
        IF @nested_include IS NOT NULL
        BEGIN
            DECLARE @nested_select_clause NVARCHAR(MAX), @nested_join_clause NVARCHAR(MAX), @nested_alias_mapping NVARCHAR(MAX);
            
            EXEC dbo.build_includes 
                @includes = @nested_include, 
                @parent_table = @table_name, 
                @parent_alias = @alias,
                @select_clause = @nested_select_clause OUTPUT,
                @join_clause = @nested_join_clause OUTPUT,
                @alias_mapping = @nested_alias_mapping OUTPUT;

            -- Append nested results
            SET @select_clause = @select_clause + ', ' + @nested_select_clause;
            SET @join_clause = @join_clause + ' ' + @nested_join_clause;
            SET @alias_mapping = JSON_MODIFY(@alias_mapping, 'append $', @nested_alias_mapping);
        END

        FETCH NEXT FROM cur INTO @table_name, @join_type, @join_condition, @alias, @attributes, @nested_include;
    END
    CLOSE cur;
    DEALLOCATE cur;
END;
