CREATE OR ALTER PROCEDURE dbo.check_for_related_records
    @json_input NVARCHAR(MAX),
    @result NVARCHAR(MAX) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @entity_name_var NVARCHAR(255);
    DECLARE @record_id INT;
    DECLARE @associated_tables NVARCHAR(MAX);
    DECLARE @table_name NVARCHAR(255);
    DECLARE @where_clause NVARCHAR(MAX);
    DECLARE @count_result INT;
    DECLARE @sql NVARCHAR(MAX);
    DECLARE @json_table TABLE (table_entry NVARCHAR(MAX));

    -- Extract values from JSON input
    SET @entity_name_var = JSON_VALUE(@json_input, '$.entity_name');
    SET @record_id = JSON_VALUE(@json_input, '$.record_id');

    -- Initialize the result as an empty JSON object if it's not provided
    IF @result IS NULL
    BEGIN
        SET @result = '{}';
    END;

    -- Get associated tables from master_entities
    SELECT @associated_tables = m.associated_tables
    FROM master_entities m
    WHERE m.entity_name = @entity_name_var;

    -- Check if associated tables exist for the entity
    IF @associated_tables IS NULL OR @associated_tables = '[]'
    BEGIN
        PRINT 'No related records found.';
        RETURN;
    END;

    -- Insert JSON array elements into a table variable
    INSERT INTO @json_table
    SELECT [value]
    FROM OPENJSON(@associated_tables);

    -- Loop through each entry in the associated_tables array
    DECLARE table_cursor CURSOR FOR
    SELECT JSON_VALUE(table_entry, '$.table'), JSON_VALUE(table_entry, '$.where_clause')
    FROM @json_table;

    OPEN table_cursor;
    FETCH NEXT FROM table_cursor INTO @table_name, @where_clause;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Directly use the where_clause, assuming it contains the placeholder @record_id
        PRINT 'Checking table: ' + @table_name + ', Where Clause: ' + @where_clause;

        -- Dynamically execute the query with the where_clause and bind the record_id
        SET @sql = 'SELECT @count_result = COUNT(*) FROM ' + QUOTENAME(@table_name) + ' WHERE ' + @where_clause;
		SET @sql = REPLACE(@sql, '$1', CAST(@record_id AS NVARCHAR));
        EXEC sp_executesql @sql, N'@record_id INT, @count_result INT OUTPUT', @record_id, @count_result OUTPUT;

        PRINT 'Count result for table ' + @table_name + ': ' + CAST(@count_result AS NVARCHAR);

        IF @count_result > 0
        BEGIN
            -- Add the associated table and count to the result JSON object
            SET @result = JSON_MODIFY(@result, '$.' + @table_name, @count_result);
        END;

        FETCH NEXT FROM table_cursor INTO @table_name, @where_clause;
    END;

    CLOSE table_cursor;
    DEALLOCATE table_cursor;

    -- Check if the result JSON object is empty
    IF JSON_QUERY(@result) = '{}'
    BEGIN
        PRINT 'No related records found.';
    END
    ELSE
    BEGIN
        PRINT 'Related records: ' + @result;
    END;
END;



-- DECLARE @result NVARCHAR(MAX);
-- EXEC dbo.check_for_related_records 
--     @json_input = '{"entity_name": "user", "record_id": 1}',
--     @result = @result OUTPUT;
-- PRINT @result;
