-------------------------------------------------------------------------------
-- 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()) < 100)
BEGIN
	PRINT 'Please alter your database compatibility level'
	PRINT 'Minimal compatibility level is 100 - SQL Server 2008R2'
	SET NOEXEC ON
END
GO

EXECUTE updateLogger '$Id: //depot/main/AdobeConnect/components/dbscripts/updates/update_10_2_0_0.sql#1 $', '$Change: 1381689 $', 'Start';

--------------------------------------
-- Start of updates
--------------------------------------
------------------------------------------------------------------------------------------------------
--BEGIN Bug: CONN-4109763 cqEmailer thread seems to process the same action from multiple hosts
------------------------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT 1 FROM PPS_FIELD_NAMES WHERE FIELD_ID = 500)
	INSERT INTO PPS_FIELD_NAMES (FIELD_ID, XML_NAME) VALUES (500, 'action-retry-count');
GO
------------------------------------------------------------------------------------------------------
--END Bug: CONN-4109763
------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------
--BEGIN Bug: [CONN-4109519] [SSO]GSuite and ADFS idp not working with Connect
----------------------------------------------------------------------------------------------------------------------------------
IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'SAML_TEMPLATE_ID' AND Object_ID = Object_ID(N'PPS_SAML_IDP'))
BEGIN
	IF dbo.isBigint() = 1
		ALTER TABLE PPS_SAML_IDP ADD [SAML_TEMPLATE_ID] BIGINT
	ELSE  
		ALTER TABLE PPS_SAML_IDP ADD [SAML_TEMPLATE_ID] INT
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.key_constraints WHERE name='PK_PPS_SAML_IDP')
	ALTER TABLE PPS_SAML_IDP ADD CONSTRAINT PK_PPS_SAML_IDP PRIMARY KEY (ACCOUNT_ID);
GO
IF OBJECT_ID(N'PPS_SAML_IDP_TEMPLATES', N'U') IS  NULL
 BEGIN
    IF dbo.isBigint() = 1
        BEGIN
            CREATE TABLE PPS_SAML_IDP_TEMPLATES (
		          TEMPLATE_ID BIGINT NOT NULL IDENTITY(1,1) ,
		          ACCOUNT_ID BIGINT NOT NULL,
					 TEMPLATE_URL VARCHAR(250) NOT NULL,
                SAML_NAME VARCHAR(30) NOT NULL,
                [DISABLED] DATETIME
            );
        END
    ELSE  
        BEGIN
            CREATE TABLE PPS_SAML_IDP_TEMPLATES (
                TEMPLATE_ID INT NOT NULL IDENTITY(1,1),
                ACCOUNT_ID INT NOT NULL,
                TEMPLATE_URL VARCHAR(250) NOT NULL,
                SAML_NAME VARCHAR(30) NOT NULL,
                [DISABLED] DATETIME
            );
        END
 END
GO
IF NOT EXISTS(SELECT 1 FROM sys.key_constraints WHERE name='PK_PPS_SAML_IDP_TEMPLATES')
	ALTER TABLE PPS_SAML_IDP_TEMPLATES ADD CONSTRAINT PK_PPS_SAML_IDP_TEMPLATES PRIMARY KEY (TEMPLATE_ID);
GO
IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE name='UQ_SAMP_IDP_TEMPLATES')
	CREATE UNIQUE INDEX UQ_SAMP_IDP_TEMPLATES ON PPS_SAML_IDP_TEMPLATES (ACCOUNT_ID, SAML_NAME)
GO
IF NOT EXISTS(SELECT 1 FROM SYS.foreign_keys WHERE NAME = 'FK_PPS_SAML_IDP_TEMPLATES_ACCOUNT_ID')
   ALTER TABLE PPS_SAML_IDP_TEMPLATES ADD CONSTRAINT FK_PPS_SAML_IDP_TEMPLATES_ACCOUNT_ID FOREIGN KEY(ACCOUNT_ID) REFERENCES PPS_ACCOUNTS (ACCOUNT_ID)
GO
IF NOT EXISTS(SELECT 1 FROM SYS.foreign_keys WHERE NAME = 'FK_PPS_SAML_IDP_SAML_TEMPLATE_ID')
   ALTER TABLE PPS_SAML_IDP ADD CONSTRAINT FK_PPS_SAML_IDP_SAML_TEMPLATE_ID FOREIGN KEY([SAML_TEMPLATE_ID]) REFERENCES PPS_SAML_IDP_TEMPLATES (TEMPLATE_ID)
GO
IF NOT EXISTS (SELECT * FROM PPS_SAML_IDP_TEMPLATES WHERE ACCOUNT_ID = 1 AND SAML_NAME = 'Default')
    INSERT INTO PPS_SAML_IDP_TEMPLATES (ACCOUNT_ID, TEMPLATE_URL, SAML_NAME) VALUES (1,'{$LOGIN_URL}' , 'Default');
GO
IF NOT EXISTS (SELECT * FROM PPS_SAML_IDP_TEMPLATES WHERE ACCOUNT_ID = 1 AND SAML_NAME = 'Okta')
	INSERT INTO PPS_SAML_IDP_TEMPLATES (ACCOUNT_ID, TEMPLATE_URL, SAML_NAME) VALUES (1,'{$LOGIN_URL}' , 'Okta');
GO
IF NOT EXISTS (SELECT * FROM PPS_SAML_IDP_TEMPLATES WHERE ACCOUNT_ID = 1 AND SAML_NAME = 'GSuite')
   INSERT INTO PPS_SAML_IDP_TEMPLATES (ACCOUNT_ID, TEMPLATE_URL, SAML_NAME) VALUES (1,'{$LOGIN_URL}&RelayState={$RELAYSTATE}&SAMLRequest={$SAMLREQUEST}' , 'GSuite');
GO
IF NOT EXISTS (SELECT * FROM PPS_SAML_IDP_TEMPLATES WHERE ACCOUNT_ID = 1 AND SAML_NAME = 'ADFS')
    INSERT INTO PPS_SAML_IDP_TEMPLATES (ACCOUNT_ID, TEMPLATE_URL, SAML_NAME) VALUES (1,'{$LOGIN_URL}idpinitiatedsignon.aspx?RelayState=RPID%3D{$SAMLPROTO}%3A%2F%2F{$SAMLHOSTNAME}%2Fsaml%2Fcps%26RelayState%3D{$RELAYSTATE}' , 'ADFS');
GO
UPDATE si SET si.SAML_TEMPLATE_ID=sit.TEMPLATE_ID FROM PPS_SAML_IDP si JOIN PPS_SAML_IDP_TEMPLATES sit ON  sit.SAML_NAME='Default' WHERE si.SAML_TEMPLATE_ID IS NULL
GO
IF EXISTS(SELECT 1 FROM sys.indexes WHERE name='PPS_SAMP_IDP_SAML_TEMPLATE_ID')
	DROP INDEX PPS_SAMP_IDP_SAML_TEMPLATE_ID ON PPS_SAML_IDP
GO
IF dbo.isBigint() = 1
	ALTER TABLE PPS_SAML_IDP ALTER COLUMN SAML_TEMPLATE_ID BIGINT NOT NULL
ELSE  
	ALTER TABLE PPS_SAML_IDP ALTER COLUMN SAML_TEMPLATE_ID INT NOT NULL
GO
CREATE INDEX PPS_SAMP_IDP_SAML_TEMPLATE_ID ON PPS_SAML_IDP (SAML_TEMPLATE_ID) WITH (PAD_INDEX=ON, SORT_IN_TEMPDB=ON, FILLFACTOR=80)
GO

----------------------------------------------------------------------------------------------------------------------------------
--END Bug: [CONN-4109519] [SSO]GSuite and ADFS idp not working with Connect
----------------------------------------------------------------------------------------------------------------------------------

--------------------------------------
-- 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 = 10, 
	MINOR_RELEASE = 2, 
	[UPDATE] = 0, 
	REVISION = 0
GO

EXECUTE updateLogger '$Id: //depot/main/AdobeConnect/components/dbscripts/updates/update_10_2_0_0.sql#1 $', '$Change: 1381689 $', 'Finish';
GO

