-- Drop existing procedure if it exists
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDynamicContent];
GO

CREATE PROCEDURE [dbo].[sp_GetDynamicContent]
    @ModuleName NVARCHAR(100),
    @TypeName NVARCHAR(100)
AS
BEGIN
    /*
    ================================================================================
    GENERIC SITEFINITY DYNAMIC CONTENT QUERY
    ================================================================================
This stored procedure dynamically queries any Sitefinity dynamic module type
by constructing the table name from module and type names.

PARAMETERS:
-----------
@ModuleName: The module name (e.g., 'ExampleModule', 'CustomModule', 'MyModule')
@TypeName: The content type name (e.g., 'ExampleType', 'CustomItem', 'MyContent')

TABLE NAMING CONVENTION:
------------------------
Sitefinity creates tables as: {module}_{type} (all lowercase)
Example: 'ExampleModule' + 'ExampleType' = 'examplemodule_exampletype'

APPROACH:
---------
1. Dynamically constructs the module table name
2. Joins sf_dynamic_content with the module table
3. Returns ALL columns from both tables merged into single rows
4. Includes publication state calculation
5. Automatically detects and includes categories/tags as JSON if tables exist

USAGE:
------
EXEC sp_GetDynamicContent 'ExampleModule', 'ExampleType';
EXEC sp_GetDynamicContent 'CustomModule', 'CustomItem';

================================================================================
*/

SET NOCOUNT ON;

DECLARE @TableName NVARCHAR(255);
DECLARE @CategoryTableName NVARCHAR(255);
DECLARE @TagsTableName NVARCHAR(255);
DECLARE @HasCategories BIT = 0;
DECLARE @HasTags BIT = 0;
DECLARE @SQL NVARCHAR(MAX);

-- Construct the table names (lowercase module_type pattern)
SET @TableName = LOWER(@ModuleName) + '_' + LOWER(@TypeName);
SET @CategoryTableName = @TableName + '_category';
SET @TagsTableName = @TableName + '_tags';

-- Verify the main table exists
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES 
               WHERE TABLE_NAME = @TableName AND TABLE_SCHEMA = 'dbo')
BEGIN
    RAISERROR('Table %s does not exist in the database.', 16, 1, @TableName);
    RETURN;
END

-- Check if category table exists
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_NAME = @CategoryTableName AND TABLE_SCHEMA = 'dbo')
BEGIN
    SET @HasCategories = 1;
END

-- Check if tags table exists
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_NAME = @TagsTableName AND TABLE_SCHEMA = 'dbo')
BEGIN
    SET @HasTags = 1;
END

-- Build dynamic SQL to query all columns from both tables
SET @SQL = N'
WITH 
-- Pre-calculate publication states
PublishedItems AS (
    SELECT DISTINCT original_content_id AS master_id
    FROM sf_dynamic_content 
    WHERE status = 2  -- Live
    AND visible = 1   -- Must be visible to be truly published
),
ScheduledItems AS (
    SELECT DISTINCT 
        TRY_CAST(SUBSTRING(task_data, 
            PATINDEX(''%ContentItemMasterId="[0-9a-fA-F]%'', task_data) + 21, 
            36) AS UNIQUEIDENTIFIER) AS master_id,
        execute_time AS scheduled_date
    FROM sf_scheduled_tasks 
    WHERE enabled = 1 
    AND execute_time > GETDATE()
    AND task_data LIKE ''%ContentItemMasterId=%''
    AND PATINDEX(''%ContentItemMasterId="[0-9a-fA-F]%'', task_data) > 0
)
SELECT 
    -- All columns from sf_dynamic_content
    dc.base_id,
    dc.publication_date,
    dc.expiration_date,
    dc.last_modified,
    dc.date_created,
    dc.last_modified_by,
    dc.system_parent_id,
    dc.original_content_id,
    
    -- Calculated publication state columns
    CASE 
        WHEN p.master_id IS NOT NULL THEN ''Published''
        WHEN s.master_id IS NOT NULL THEN ''Scheduled''
        WHEN dc.status = 4 THEN ''Deleted''
        ELSE ''Unpublished''
    END AS sf_publication_state,
    
    CASE 
        WHEN p.master_id IS NOT NULL THEN ''Published''
        WHEN s.master_id IS NOT NULL THEN ''Scheduled''
        WHEN dc.status = 4 THEN ''Deleted''
        ELSE ''Unpublished''
    END AS calculated_state,
    
    s.scheduled_date AS scheduled_publish_date,
    
    -- All columns from the module-specific table
    mt.*' +
    
    -- Add categories column if table exists
    CASE WHEN @HasCategories = 1 THEN N',
    
    -- Categories as JSON
    (
        SELECT 
            t.id,
            t.title_ as title,
            t.url_name_ as url_name
        FROM ' + QUOTENAME(@CategoryTableName) + N' c
        INNER JOIN sf_taxa t ON c.val = t.id
        WHERE c.base_id = dc.base_id
        ORDER BY c.seq
        FOR JSON PATH
    ) AS categories'
    ELSE N''
    END +
    
    -- Add tags column if table exists
    CASE WHEN @HasTags = 1 THEN N',
    
    -- Tags as JSON
    (
        SELECT 
            t.id,
            t.title_ as title,
            t.url_name_ as url_name
        FROM ' + QUOTENAME(@TagsTableName) + N' tg
        INNER JOIN sf_taxa t ON tg.val = t.id
        WHERE tg.base_id = dc.base_id
        ORDER BY tg.seq
        FOR JSON PATH
    ) AS tags'
    ELSE N''
    END + N'
    
FROM sf_dynamic_content dc
INNER JOIN ' + QUOTENAME(@TableName) + N' mt ON dc.base_id = mt.base_id
LEFT JOIN PublishedItems p ON dc.base_id = p.master_id
LEFT JOIN ScheduledItems s ON dc.base_id = s.master_id AND s.master_id IS NOT NULL
WHERE dc.status = 0  -- Master records only
ORDER BY dc.last_modified DESC;';

-- Execute the dynamic SQL
EXEC sp_executesql @SQL;

END
GO
/*
USAGE EXAMPLES
-- Query all records from ExampleModule with ExampleType:
EXEC sp_GetDynamicContent 'ExampleModule', 'ExampleType';
-- Query all records from a CustomModule:
EXEC sp_GetDynamicContent 'CustomModule', 'CustomItem';
-- Query records from MyModule:
EXEC sp_GetDynamicContent 'MyModule', 'MyContent';
-- Query parent and child types separately:
EXEC sp_GetDynamicContent 'ParentModule', 'ParentType';
EXEC sp_GetDynamicContent 'ParentModule', 'ChildType';
NOTES:

The procedure automatically merges all columns from both tables
Module-specific columns are prefixed with 'mt.*' in the output
Publication state is calculated based on Live records and scheduled tasks
Only Master records (status=0) are returned to avoid duplicates
Results are ordered by last_modified descending (most recent first)
Categories and tags are automatically included as JSON columns if the tables exist

Table pattern: {module}_{type}category and {module}{type}_tags
JSON includes: id, title, url_name from sf_taxa



================================================================================
*/