<cfif form.Type EQ "Patch">
	<cfinclude template="patch_16.0.01.cfm">
</cfif>

<!--- AZ-3511 Add ReadConfirmRecurAfterDueDate Field to AppBuilderSettings --->
<cftransaction>
    <cfquery name="UpdateDatabaseSchema" datasource="#application.config.dsn#">
        IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppBuilderSettings' 
        AND COLUMN_NAME = 'ReadConfirmRecurAfterDueDate')
        BEGIN
            ALTER TABLE AppBuilderSettings
            ADD ReadConfirmRecurAfterDueDate INT NULL DEFAULT (1) WITH VALUES;
        END;
    </cfquery>
</cftransaction> 
    
<!--- AZ-3511 Add ReadConfirmSendNotificationsAfterDueDate Field to AppBuilderSettings --->
<cftransaction>
    <cfquery name="UpdateDatabaseSchema" datasource="#application.config.dsn#">
        IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppBuilderSettings' 
        AND COLUMN_NAME = 'ReadConfirmSendNotificationsAfterDueDate')
        BEGIN
            ALTER TABLE AppBuilderSettings
            ADD ReadConfirmSendNotificationsAfterDueDate TINYINT NULL DEFAULT (0) WITH VALUES;
        END;
    </cfquery>
</cftransaction>


<!--- AZ-3511 Add ReadConfirmDurationAfterDueDate Field to AppBuilderSettings --->
<cftransaction>
    <cfquery name="UpdateDatabaseSchema" datasource="#application.config.dsn#">
        IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppBuilderSettings' 
        AND COLUMN_NAME = 'ReadConfirmDurationAfterDueDate')
        BEGIN
            ALTER TABLE AppBuilderSettings
            ADD ReadConfirmDurationAfterDueDate INT NULL DEFAULT (7) WITH VALUES;
        END;
    </cfquery>
</cftransaction>
<!--- AZ-3511 Add ReadConfirmSendImmediateNotification Field to AppBuilderSettings --->
<cftransaction>
    <cfquery name="UpdateDatabaseSchema" datasource="#application.config.dsn#">
        IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppBuilderSettings' 
        AND COLUMN_NAME = 'ReadConfirmSendImmediateNotification')
        BEGIN
            ALTER TABLE AppBuilderSettings
            ADD ReadConfirmSendImmediateNotification TINYINT NULL DEFAULT (0) WITH VALUES;
        END;
    </cfquery>
</cftransaction>

<!--- AZ-3511 Add ReadConfirmSendNotificationOnDueDate Field to AppBuilderSettings --->
<cftransaction>
    <cfquery name="UpdateDatabaseSchema" datasource="#application.config.dsn#">
        IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppBuilderSettings' 
        AND COLUMN_NAME = 'ReadConfirmSendNotificationOnDueDate')
        BEGIN
            ALTER TABLE AppBuilderSettings
            ADD ReadConfirmSendNotificationOnDueDate TINYINT NULL DEFAULT (1) WITH VALUES;
        END;
    </cfquery>
</cftransaction>

<!--- Feature AZ-3269 - Customize Read & Confirm Message --->
<!--- Add ConfirmMessage Field to ReadConfirm --->
<cftransaction>
    <cfquery name="readConfirmTableUpdate" datasource="#application.config.dsn#">
        IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ReadConfirm' AND COLUMN_NAME = 'ConfirmMessage')
        BEGIN
            ALTER TABLE ReadConfirm
            ADD ConfirmMessage varchar(500);
        END; 
    </cfquery>
</cftransaction>
<!--- AZ-3511 Add Notified Field to ReadReceipts table. This empowers us to know who has been added as a new person to a read and confirm doc.  --->
<cftransaction> 
    <cfquery name="UpdateDatabaseSchema" datasource="#application.config.dsn#">
        IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ReadReceipt' 
        AND COLUMN_NAME = 'Notified')
        BEGIN
            ALTER TABLE ReadReceipt
            ADD Notified TINYINT NULL DEFAULT (1) WITH VALUES;
        END;
    </cfquery>
 </cftransaction>

<!--- Feature AZ-3583 - App Ownership change. --->
<cfquery datasource="#application.config.DSN#">
IF OBJECT_ID(N'sp_AppItemOwnershipByUser', N'P') IS NOT NULL
DROP PROC [sp_AppItemOwnershipByUser];
</cfquery>

<cftransaction>
    <cfquery name="addUserOwnershipSP" datasource="#application.config.dsn#">
        CREATE PROCEDURE sp_AppItemOwnershipByUser
            @UserID INT
        AS
        BEGIN
            SET NOCOUNT ON;

            -- Create a temp table to store the item pages ID's and title. 
            CREATE TABLE ##TEMP_ITEM_PAGES(
                ID INT PRIMARY KEY IDENTITY(1,1), 
                AppID Integer, 
                Title Varchar(500)
            ); 
            -- Get all the app's which have possible items in them owned by our user. 
            INSERT INTO ##TEMP_ITEM_PAGES (AppID, Title)
            SELECT 
                AppID, 
                Title 
            FROM Application 
            INNER JOIN Asset ON AppID = AssetID 
            WHERE Template = 0 and AppTypeID = 31 and Disabled = 0; 

            -- Create temp table to store all the users items; 
            CREATE TABLE ##TEMP_USER_OWNED_ITEMS(
                ID INT PRIMARY KEY IDENTITY(1,1), 
                AppID INT, 
                AppTitle VARCHAR(max), 
                ItemID INT,
                Title VARCHAR(max), 
                CategoryID INT, 
                CategoryName VARCHAR(max), 
                DateModified DATETIME
            ); 

            DECLARE @Counter INT = 1; 
            DECLARE @TotalPages INT = (SELECT COUNT(AppID) FROM ##TEMP_ITEM_PAGES); 
            PRINT @UserID; 
            WHILE @Counter <= @TotalPages -- Loop through each app table and add all the items of that user to TEMP_USER_OWNED_ITEMS
            BEGIN 
                DECLARE @CurrentAppID INT = (
                    SELECT 
                        AppID 
                    FROM ##TEMP_ITEM_PAGES 
                    WHERE ID = @Counter);
                DECLARE @CurrentAppTitle VARCHAR(max) = (
                    SELECT 
                        Title 
                    FROM ##TEMP_ITEM_PAGES 
                    WHERE ID = @Counter);

                DECLARE @AppbuilderDataTable VARCHAR(25) = 'AppBuilderData' + CAST(@CurrentAppID AS varchar); 
                DECLARE @AppbuilderCategoriesTable VARCHAR(35) = 'AppBuilderCategories' + CAST(@CurrentAppID AS varchar); 

                DECLARE @SQL AS NVARCHAR(max); 
                SET @SQL = N'INSERT INTO ##TEMP_USER_OWNED_ITEMS (AppID, AppTitle, ItemID, Title, CategoryID, CategoryName, DateModified) 
                            SELECT ' + CAST(@CurrentAppID AS varchar) +',' + CHAR(39) + @CurrentAppTitle + CHAR(39) +' ,d.ID, d.Title, d.CategoryID, c.Category ,d.DateModified
                            FROM ' + @AppbuilderDataTable + ' as d 
                            INNER JOIN ' + @AppbuilderCategoriesTable + ' c ON d.CategoryID = c.CategoryID
                            WHERE d.UserID = ' + CAST(@UserID AS varchar); 
                PRINT @SQL; 
                EXEC sp_executesql @SQL; 

                SET @Counter = @Counter + 1
            END

            SELECT * FROM ##TEMP_USER_OWNED_ITEMS;

            DROP TABLE ##TEMP_ITEM_PAGES; 
			DROP TABLE ##TEMP_USER_OWNED_ITEMS; 
        END;
    </cfquery> 
</cftransaction> 

<!---  AZ-1576 App Setting SENDNOTIFICATIONSFROMADMIN  --->
<cftransaction>
<cfquery datasource="#application.config.DSN#" name="SendNotificationsFromAdmin">
    IF NOT EXISTS(SELECT * FROM AppSetting WHERE Name = 'SENDNOTIFICATIONSFROMADMIN')
    BEGIN
        INSERT INTO [dbo].[AppSetting]
            ([Name]
            ,[Value])
        VALUES
            ('SENDNOTIFICATIONSFROMADMIN'
            ,'0')
    END
</cfquery>
</cftransaction>