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

    -- Declare variables
    DECLARE @v_primary_table NVARCHAR(255),
            @v_entity_name 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_headers NVARCHAR(MAX) = '[]',
            @v_company_id INT,
            @v_print_query BIT,
            @v_start_index INT = 0,
            @v_limit_range INT = 10,
            @v_search_all NVARCHAR(MAX) = '',
            @v_search_any NVARCHAR(MAX) = '',
            @v_having_all NVARCHAR(MAX) = '',
            @v_having_any NVARCHAR(MAX) = '',
            @search_all_conditions NVARCHAR(MAX) = '',
            @search_any_conditions NVARCHAR(MAX) = '';

    -- Alias mapping for dynamic SQL
    DECLARE @v_alias_mapping NVARCHAR(MAX) = '{}';

    -- Parse JSON input
    SET @v_company_id = JSON_VALUE(@json_input, '$.company_id');
    SET @v_entity_name = JSON_VALUE(@json_input, '$.entity_name');
    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'), 10);
    SET @v_search_all = JSON_QUERY(@json_input, '$.search_all');
    SET @v_search_any = JSON_QUERY(@json_input, '$.search_any');
    SET @v_having_all = JSON_QUERY(@json_input, '$.having_conditions');
    SET @v_having_any = JSON_QUERY(@json_input, '$.having_any_conditions');

    -- Fetch `select_columns` dynamically using `get_grid_columns`
    DECLARE @v_select_columns NVARCHAR(MAX);
    EXEC dbo.get_grid_columns 
        @var_entity_name = @v_entity_name,
        @var_company_id = @v_company_id,
        @result = @v_select_columns OUTPUT;

	PRINT @v_select_columns;

    -- Build the SELECT clause dynamically & Generate @v_headers JSON
    DECLARE @col_name NVARCHAR(MAX), 
            @col_alias NVARCHAR(255),
            @col_field_type_id NVARCHAR(10), 
            @col_order NVARCHAR(10), 
            @col_searchable NVARCHAR(10),
            @col_grid_column NVARCHAR(10),
            @col_sortable NVARCHAR(10),
            @col_field_value NVARCHAR(MAX),
            @col_clause_type NVARCHAR(255),
			@col_json NVARCHAR(MAX);  -- Variable to hold formatted JSON string

    DECLARE curSelect CURSOR FOR
    SELECT 
        JSON_VALUE(value, '$[0]'),  
        JSON_VALUE(value, '$[1]'),
        JSON_VALUE(value, '$[2]'), 
        JSON_VALUE(value, '$[3]'),
        JSON_VALUE(value, '$[4]'),
        JSON_VALUE(value, '$[5]'),
        JSON_VALUE(value, '$[6]'),
        JSON_VALUE(value, '$[7]'),
        JSON_VALUE(value, '$[8]')
    FROM OPENJSON(@v_select_columns);

    OPEN curSelect;
    FETCH NEXT FROM curSelect INTO @col_name, @col_alias, @col_order, @col_searchable, @col_grid_column, @col_sortable, @col_field_value, @col_field_type_id, @col_clause_type;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Append to `SELECT` clause
        IF @v_select_clause <> '' 
            SET @v_select_clause = @v_select_clause + ', ';

        IF @col_alias IS NOT NULL AND @col_alias <> ''
            SET @v_select_clause = @v_select_clause + @col_name + ' AS ' + QUOTENAME(@col_alias);
        ELSE
            SET @v_select_clause = @v_select_clause + @col_name;

        -- Construct `HEADERS` JSON manually
		--DECLARE @is_searchable_str NVARCHAR(5) = CASE WHEN @col_searchable = '1' THEN 'true' ELSE 'false' END;
        --DECLARE @is_grid_column_str NVARCHAR(5) = CASE WHEN @col_grid_column = '1' THEN 'true' ELSE 'false' END;
        --DECLARE @is_sortable_str NVARCHAR(5) = CASE WHEN @col_sortable = '1' THEN 'true' ELSE 'false' END;
		
        SET @col_json = '{"header": "' + ISNULL(@col_alias, @col_name) + '", 
                        "column_width": "' + COALESCE(@col_field_type_id, '0') + '", 
                        "column_order": "' + @col_order + '", 
                        "is_searchable": "' + @col_searchable + '", 
                        "is_grid_column": "' + @col_grid_column + '", 
                        "is_sortable": "' + @col_sortable + '", 
                        "field_value": "' + ISNULL(@col_field_value, '') + '", 
						"field_type_id": ' + COALESCE(@col_field_type_id, '0') + ',
                        "clause_type": "' + ISNULL(@col_clause_type, '') + '"}';
		PRINT @col_json;
        -- Append to `HEADERS` JSON array safely
        SET @v_headers = JSON_MODIFY(@v_headers, 'append $', JSON_QUERY(@col_json));

        FETCH NEXT FROM curSelect INTO @col_name, @col_alias, @col_order, @col_searchable, @col_grid_column, @col_sortable, @col_field_value, @col_field_type_id, @col_clause_type;
    END

    CLOSE curSelect;
    DEALLOCATE curSelect;

    -- Ensure @v_select_clause is not empty
    IF @v_select_clause = ''
        SET @v_select_clause = QUOTENAME(@v_primary_table) + '.*';

    -- Fetch the primary_table dynamically based on the entity_name
    SELECT @v_primary_table = primary_table 
    FROM master_entities 
    WHERE entity_name = @v_entity_name AND company_id = @v_company_id;

    -- Fetch `search_all` & `search_any` from `master_entities`
    DECLARE @v_query_info_search NVARCHAR(MAX),
            @v_query_info_search_any NVARCHAR(MAX),
            @v_combined_search_all NVARCHAR(MAX),
            @v_combined_search_any NVARCHAR(MAX),
            @having_all_conditions NVARCHAR(MAX),
            @having_any_conditions NVARCHAR(MAX);

    SELECT 
        @v_query_info_search = JSON_QUERY(query_information, '$.search_all'),
        @v_query_info_search_any = JSON_QUERY(query_information, '$.search_any')
    FROM master_entities
    WHERE entity_name = @v_entity_name AND company_id = @v_company_id;

    -- **Merge search conditions correctly**
    -- Ensure they are not NULL, defaulting to empty JSON arrays
	SET @v_query_info_search = ISNULL(@v_query_info_search, '[]');
	SET @v_query_info_search_any = ISNULL(@v_query_info_search_any, '[]');
	SET @v_search_all = ISNULL(@v_search_all, '[]');
	SET @v_search_any = ISNULL(@v_search_any, '[]');
	SET @v_having_all = ISNULL(@v_having_all, '[]');
	SET @v_having_any = ISNULL(@v_having_any, '[]');

	SET @v_combined_search_all = (
		SELECT STRING_AGG(value, ',') 
		FROM (
			SELECT value FROM OPENJSON(@v_search_all)
			UNION ALL
			SELECT value FROM OPENJSON(@v_query_info_search)
		) AS merged_data
	);

	-- Wrap the merged JSON in brackets to ensure it's valid JSON array format
	SET @v_combined_search_all = '[' + ISNULL(@v_combined_search_all, '') + ']';

	SET @v_combined_search_any = (
		SELECT STRING_AGG(value, ',') 
		FROM (
			SELECT value FROM OPENJSON(@v_search_any)
			UNION ALL
			SELECT value FROM OPENJSON(@v_query_info_search_any)
		) AS merged_data
	);

	-- Wrap the merged JSON in brackets to ensure it's valid JSON array format
	SET @v_combined_search_any = '[' + ISNULL(@v_combined_search_any, '') + ']';

	-- **Process Search Conditions using Separate Procedures**
	EXEC dbo.build_search_conditions @search_all=@v_combined_search_all, @search_conditions=@search_all_conditions OUTPUT;
	EXEC dbo.build_search_any_conditions @search_any=@v_combined_search_any, @search_conditions=@search_any_conditions OUTPUT;
	EXEC dbo.build_search_conditions @search_all=@v_having_all, @search_conditions=@having_all_conditions OUTPUT;
	EXEC dbo.build_search_any_conditions @search_any=@v_having_any, @search_conditions=@having_any_conditions OUTPUT;

    -- **Build JOIN clauses using `build_includes1`**
    DECLARE @v_include_result NVARCHAR(MAX);
    DECLARE @includes_param NVARCHAR(MAX);
    SET @includes_param = JSON_QUERY(@json_input, '$.includes');

    EXEC dbo.build_includes1 
        @entity_name = @v_entity_name,
        @parent_table = '',
        @parent_alias = '',
        @var_company_id = @v_company_id,
        @select_clause = @v_group_by_clause OUTPUT,
        @join_clause = @v_join_clause OUTPUT,
        @alias_mapping = @v_alias_mapping OUTPUT,
        @having_clause = @v_having_clause OUTPUT;

    -- **Apply search filters to WHERE clause**
    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;

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

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

	-- Handle ORDER BY clause
	PRINT @json_input;
    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;
	
    -- Ensure ORDER BY exists for pagination
	IF @v_order_by_clause <> '' 
        SET @v_order_by_clause = ' ORDER BY ' + @v_order_by_clause;
    ELSE IF @v_order_by_clause = '' 
        SET @v_order_by_clause = ' ORDER BY ' + QUOTENAME(@v_primary_table) + '.id ASC';

    -- **Build Query WITHOUT Pagination (For Counting Total Records)**
    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, '');

    -- **Execute count query**
    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;

    -- **Add ORDER BY and Pagination After Getting Total Count**
    SET @v_query_str = @v_query_str + @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 Pagination: ' + @v_query_str;

    -- **Execute final query & store results**
    DECLARE @resultQuery NVARCHAR(MAX);
    SET @resultQuery = 'SELECT @v_records_OUT = (SELECT (SELECT * FROM (' + @v_query_str + ') AS result FOR JSON AUTO))';

    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, '[]') 
        + ', "headers": ' + ISNULL(@v_headers, '[]') 
        + ', "query_executed": "' + CASE WHEN @v_print_query = 1 THEN REPLACE(@v_query_str, '"', '""') ELSE 'null' END 
        + '", "execution_status": true }';
END;
