USE [lcp_framework]
GO
/****** Object:  StoredProcedure [dbo].[build_includes1]    Script Date: 3/5/2025 4:54:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[build_includes1]
    @entity_name NVARCHAR(255),
    @parent_table NVARCHAR(255),
    @parent_alias NVARCHAR(255),
    @var_company_id INT,
    @select_clause NVARCHAR(MAX) OUTPUT,
    @join_clause NVARCHAR(MAX) OUTPUT,
    @alias_mapping NVARCHAR(MAX) OUTPUT,
    @having_clause NVARCHAR(MAX) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

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

    -- Declare local variables
    DECLARE @query_information NVARCHAR(MAX);
    DECLARE @group_by NVARCHAR(MAX), @includes NVARCHAR(MAX), @having_conditions NVARCHAR(MAX);
    DECLARE @table_name NVARCHAR(MAX), @join_type NVARCHAR(50), @join_condition NVARCHAR(MAX), @alias NVARCHAR(255);
    DECLARE @nested_include NVARCHAR(MAX);
    DECLARE @group_col NVARCHAR(MAX), @having_condition NVARCHAR(MAX), @having_operator NVARCHAR(10);
    DECLARE @having_conditions_json NVARCHAR(MAX), @having_any_conditions_json NVARCHAR(MAX) , @having_conditions_clause NVARCHAR(MAX) = '', @having_any_conditions_clause NVARCHAR(MAX) = '';

    -- Fetch `query_information` from `master_entities` table
    SELECT @query_information = query_information
    FROM master_entities
    WHERE entity_name = @entity_name AND company_id = @var_company_id;

    -- Extract `includes`, `group_by`, and `having_conditions` from `query_information`
    SET @includes = JSON_QUERY(@query_information, '$.includes');
    SET @group_by = JSON_QUERY(@query_information, '$.group_by');
    SET @having_conditions_json = JSON_QUERY(@query_information, '$.having_conditions');
    SET @having_any_conditions_json = JSON_QUERY(@query_information, '$.having_any_conditions');

    -- Cursor for processing includes
    DECLARE curIncludes CURSOR FOR 
    SELECT 
        JSON_VALUE(value, '$.table_name'),
        JSON_VALUE(value, '$.join_type'),
        JSON_VALUE(value, '$.join_condition'),
        JSON_VALUE(value, '$.alias'),
        JSON_QUERY(value, '$.includes')
    FROM OPENJSON(@includes);

    OPEN curIncludes;
    FETCH NEXT FROM curIncludes INTO @table_name, @join_type, @join_condition, @alias, @nested_include;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 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), @nested_having_clause NVARCHAR(MAX);

            EXEC dbo.build_includes1 
                @entity_name, @table_name, @alias, @var_company_id,
                @nested_select_clause OUTPUT, @nested_join_clause OUTPUT, @nested_alias_mapping OUTPUT, @nested_having_clause 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);
            SET @having_clause = @having_clause + ' ' + @nested_having_clause;
        END

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

    -- Build GROUP BY clause
    IF @group_by IS NOT NULL
    BEGIN
        DECLARE curGroupBy CURSOR FOR 
        SELECT value FROM OPENJSON(@group_by);

        OPEN curGroupBy;
        FETCH NEXT FROM curGroupBy INTO @group_col;

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

        CLOSE curGroupBy;
        DEALLOCATE curGroupBy;

        SET @select_clause = ' GROUP BY ' + @select_clause;
    END

    -- **Build HAVING Clause Using `build_search_conditions`**
    IF @having_conditions_json IS NOT NULL AND @having_conditions_json <> '[]'
    BEGIN
        EXEC dbo.build_search_conditions 
            @search_all = @having_conditions_json, 
            @search_conditions = @having_conditions_clause OUTPUT;

        -- Append HAVING clause if conditions exist
        IF @having_conditions_clause <> ''
            SET @having_clause = ' HAVING ' + @having_conditions_clause;
    END

	IF @having_any_conditions_json IS NOT NULL AND @having_any_conditions_json <> '[]'
    BEGIN
        EXEC dbo.build_search_any_conditions 
            @search_any = @having_any_conditions_json, 
            @search_conditions = @having_any_conditions_clause OUTPUT;

        -- Append HAVING clause if conditions exist
        IF @having_any_conditions_clause <> '' AND @having_clause <> ''
            SET @having_clause = @having_clause + ' AND ' + @having_any_conditions_clause;
		ELSE IF @having_any_conditions_clause <> '' AND @having_clause = ''
			SET @having_clause = ' HAVING ' + @having_any_conditions_clause;
    END
END;
