-------------------------------------------------------------------------------
-- This is the update template file
--
-- For every hotfix/service pack there is a unique update script that is 
-- executed by the installer. 
--
-- The first statement is a logging statement to indicate what db version
-- the database currently has.
--
-- The script ends with an update statement to increment the database version
-- and another log statement to indicate completion of the update script.
-- Wrap all blocks with begin/end keywords, even if there is only one statement.
--
-- If some sql statements only need to be executed against a hosted or licensed -- database, use the dbo.isHosted() function call. This function returns 1 if 
-- the database is a hosted database and 0 if licensed, i.e. 
--	if dbo.isHosted() = 1
--		begin
--			print 'hosted';
--		end
--
--
-- Please use isBigint() function to determine if database uses bigint for IDs:
--	if dbo.isBigint() = 1
--		begin
--			print 'IDs are of BIGINT type';
--		end
-- else 
--		begin
--			print 'IDs are of INTERGER type';
--		end
-------------------------------------------------------------------------------

IF((SELECT COMPATIBILITY_LEVEL FROM SYS.DATABASES WHERE NAME = DB_NAME()) < 110)
BEGIN
	PRINT 'Please alter your database compatibility level'
	PRINT 'Minimal compatibility level is 110 - SQL Server 2012'
	SET NOEXEC ON
END
GO
SET NOCOUNT ON
GO
EXECUTE updateLogger 'file: components/dbscripts/updates/update_11_4_5_0.sql', 'blob $Id: b9589af847321728994f09f14bfe1213ce3976f0 $', 'Start';
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--------------------------------------
-- Start of updates
--------------------------------------

----------------------------------------------------------------------------------------
--BEGIN Jira: CONN-4132903 Opt-out option for Account Admins to not switch all existing rooms to WebRTC with AC12 release
----------------------------------------------------------------------------------------

IF NOT EXISTS (SELECT * FROM PPS_FIELD_NAMES WHERE FIELD_ID = 999)
BEGIN
	INSERT INTO PPS_FIELD_NAMES (FIELD_ID, XML_NAME, VERSION) VALUES (999, 'opt-out-webrtc', 1145);
END
GO  

----------------------------------------------------------------------------------------
--END Jira: CONN-4132903
----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------
--BEGIN Jira: CONN-4131807 - CPS changes to handle recording videos from CloudFront
----------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM PPS_FIELD_NAMES WHERE FIELD_ID = 1001 AND XML_NAME = 'cas-delivered-recording-endpoint')
BEGIN
    UPDATE PPS_ACL_FIELDS SET VALUE = 'true' WHERE FIELD_ID = 1001 AND VALUE IS NOT NULL
    UPDATE PPS_ACL_FIELDS SET VALUE = 'false' WHERE FIELD_ID = 1001 AND VALUE IS NULL
    UPDATE PPS_FIELD_NAMES SET XML_NAME = 'cas-delivered-recording' WHERE FIELD_ID = 1001;
END
GO

IF NOT EXISTS (SELECT * FROM PPS_FIELD_NAMES WHERE FIELD_ID = 1001)
BEGIN
    INSERT INTO PPS_FIELD_NAMES (FIELD_ID, XML_NAME, VERSION) VALUES (1001, 'cas-delivered-recording', 1145);
END
GO

IF NOT EXISTS (SELECT * FROM PPS_FIELD_NAMES WHERE FIELD_ID = 1002)
BEGIN
    INSERT INTO PPS_FIELD_NAMES (FIELD_ID, XML_NAME, VERSION) VALUES (1002, 'create-cas-delivered-recordings', 1145);
END
GO

IF NOT EXISTS (SELECT * FROM PPS_CONFIG WHERE SECTION = 'webrtc' AND NAME = 'config-recordings-provider-session-generation-salt')
BEGIN
    INSERT INTO PPS_CONFIG (SECTION, NAME, VALUE, INIT_VECTOR) VALUES ('webrtc', 'config-recordings-provider-session-generation-salt', '#V1#gMzsTd0HBhtINuJc/gsVSA==', 'lCTPNPqbCmm/72kEuKyCPw==');
END
GO
----------------------------------------------------------------------------------------
--END Jira: CONN-4131807
----------------------------------------------------------------------------------------

--------------------------------------
-- End of updates
--------------------------------------

--------------------------------------------------------------------
-- BEGIN REFRESH block
--
-- Recompile SPs, views and other objects that need to be recompiled
-- Please DO NOT change following lines
--------------------------------------------------------------------
GO
IF CURSOR_STATUS('global', 'ObjectCursor') >= -1
BEGIN
	DEALLOCATE ObjectCursor;
END
GO

DECLARE @Name nvarchar(1000);
DECLARE @Sql nvarchar(1000);
DECLARE @Result int;

DECLARE ObjectCursor CURSOR FAST_FORWARD FOR
	SELECT SCHEMA_NAME(o.schema_id) + '.[' + OBJECT_NAME(o.object_id) + ']'
	FROM sys.objects o 
	WHERE type_desc IN ('SQL_STORED_PROCEDURE', 'SQL_TRIGGER', 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION',
								'SQL_INLINE_TABLE_VALUED_FUNCTION',	'VIEW')
	AND ISNULL(OBJECTPROPERTY(o.object_id,'IsSchemaBound'), 0) = 0	 -- don't check schemabinding they don't like it

OPEN ObjectCursor;
FETCH NEXT FROM ObjectCursor INTO @Name;

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @Sql = N'EXEC sp_refreshsqlmodule ''' + @Name + '''';
   PRINT @Sql;

   BEGIN TRY
		EXEC @Result = sp_executesql @Sql;
		IF @Result <> 0 RAISERROR('Failed', 16, 1);
   END TRY
   BEGIN CATCH
		-- PRINT @Sql
      PRINT 'The module ''' + @Name + ''' does not compile.';
   IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    END CATCH

    FETCH NEXT FROM ObjectCursor INTO @Name;
END

CLOSE ObjectCursor;
DEALLOCATE ObjectCursor;
GO

----------------------------------------------------------------------------------------------------------
-- Change the version number to match the file build number below
-- MINOR_RELEASE is tantamount to Service Pack
-- The REVISION field should never really be set to anything other than 0 in the DB update scripts context
----------------------------------------------------------------------------------------------------------
UPDATE PPS_DATABASE_VERSION 
	SET MAJOR_RELEASE = 11, 
	MINOR_RELEASE = 4, 
	[UPDATE] = 5, 
	REVISION = 0
GO

EXECUTE updateLogger 'file: components/dbscripts/updates/update_11_4_5_0.sql', 'blob $Id: b9589af847321728994f09f14bfe1213ce3976f0 $', 'Finish';
GO
SET NOEXEC OFF
SET NOCOUNT OFF
GO


