<cfif form.Type EQ "Patch">
    <cfinclude template="patch_15.5.02.cfm">
</cfif>

<!--- Case AZ-1402 - Conditionally allow embedding the intranet --->
<cftransaction>
<cfquery datasource="#application.config.DSN#" name="addAllowEmbed">
IF NOT EXISTS(SELECT * FROM AppSetting WHERE Name = 'ALLOWINTRANETEMBED')
BEGIN
      INSERT INTO [dbo].[AppSetting]
          ([Name]
          ,[Value])
    VALUES
          ('ALLOWINTRANETEMBED'
          ,'0')
END

</cfquery>
</cftransaction>

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

</cfquery>
</cftransaction>


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

</cfquery>
</cftransaction>

<!--- App Setting TIMEZONE --->
<cftransaction>
<cfquery datasource="#application.config.DSN#" name="TIMEZONE">
IF NOT EXISTS(SELECT * FROM AppSetting WHERE Name = 'TIMEZONE')
BEGIN
      INSERT INTO [dbo].[AppSetting]
          ([Name]
          ,[Value])
    VALUES
          ('TIMEZONE'
          ,'Pacific Standard Time')
END

</cfquery>
</cftransaction>

<!--- Add dropdown options for SSO intranet field mapping --->
<cfquery datasource="#application.config.DSN#" name="ssonamedropdown">
IF NOT EXISTS(SELECT * FROM AppSetting WHERE Name = 'SSOINTRANETNAMEMAPPING')
BEGIN 
      INSERT INTO [dbo].[AppSetting]
           ([Name]
           ,[Value])
     VALUES
           ('SSOINTRANETNAMEMAPPING', 'ssonameid')
 END
</cfquery>

<!--- Case AZ-2838 - Skip search indexing for employees during AD Sync --->
<cfquery datasource="#application.config.DSN#" name="employeesearchindex">
IF NOT EXISTS(SELECT * FROM AppSetting WHERE Name = 'ADSKIPEMPLOYEESEARCHINDEX')
BEGIN 
      INSERT INTO [dbo].[AppSetting]
           ([Name]
           ,[Value])
     VALUES
           ('ADSKIPEMPLOYEESEARCHINDEX', '0')
 END
</cfquery>

<!--- Case AZ-752 - Graph API AD Sync --->

<cftransaction>
<!--- Add ConnectionType Field to ADConnection --->
<cfquery name="graphAPITableUpdates" datasource="#application.config.dsn#">
	IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ADConnection' AND COLUMN_NAME = 'ConnectionType')
	BEGIN
	    ALTER TABLE ADConnection
	    ADD ConnectionType varchar(2);
	END;
 </cfquery>
<!--- Add SSONameIDMapping Field to ADConnection --->
<cfquery name="graphAPITableUpdates" datasource="#application.config.dsn#">
	IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ADConnection' AND COLUMN_NAME = 'SSONameIDMapping')
	BEGIN
	    ALTER TABLE ADConnection
	    ADD SSONameIDMapping varchar(25);
	END;
 </cfquery>
 <!--- Set default ConnectionType value to 'L' for existing connections not tagged with a ConnectionType yet. --->
<cfquery name="graphAPITableUpdates" datasource="#application.config.dsn#">
	  UPDATE ADConnection SET ConnectionType = 'L' WHERE ConnectionType IS NULL OR ConnectionType = '';
</cfquery>

 <!--- Set Default value for SSONameIDMapping Field to ADConnection
 
      What we are doing in the next query, is checking for the presense of the "ConnectionType" value. 
      checking if the any values in "ConnectionType" are 'G'. This would indicate a Graph API connection. 
      Since the default Graph API Hotfix SSONameID mapping was e-mail. We auto set this value to keep 
      consistant and be backwards compatible to those users who are upgrading to v16 from the Graph API Hotfix. 
  --->
<cfquery name="graphAPITableUpdates" datasource="#application.config.dsn#">
	-- Check if any ADConnection table has ConnectionType field
      IF EXISTS (
            SELECT 1
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = 'ADConnection'
            AND COLUMN_NAME = 'ConnectionType'
      )
      BEGIN
            -- Check if any records have ConnectionType = 'G'
            IF EXISTS (
                  SELECT 1
                  FROM dbo.ADConnection
                  WHERE ConnectionType = 'G'
            )
            BEGIN
                  -- Update SSONameIDMapping to 'email' for records with ConnectionType = 'G'
                  -- and SSONameIDMapping is Null or empty. 
                  UPDATE dbo.ADConnection
                  SET SSONameIDMapping = 'mail'
                  WHERE ConnectionType = 'G'
                  AND SSONameIDMapping is NULL;
            END;
      END;
 </cfquery>
<!--- Increase size of Password Field in ADConnection --->
<cfquery name="graphAPITableUpdates" datasource="#application.config.dsn#">
	ALTER TABLE ADConnection
	ALTER COLUMN Password varchar(500);
</cfquery>

<!--- Add GraphFieldName Field to ADField --->
<cfquery name="graphAPITableUpdates" datasource="#application.config.dsn#">
	IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ADField' AND COLUMN_NAME = 'GraphFieldName')
	BEGIN
	    ALTER TABLE ADField
	    ADD GraphFieldName varchar(100);
	END;
</cfquery>

<!--- Add support for Graph fields to target screen via ADField table --->
<cfquery name="graphAPITableUpdates" datasource="#application.config.dsn#">

UPDATE ADField 
 
SET GraphFieldName =  
 
  CASE 
 
    WHEN FieldName = 'GivenName' THEN 'GivenName' 
 
    WHEN FieldName = 'sn' THEN 'sn' 
 
    WHEN FieldName = 'sAMAccountName' THEN 'sAMAccountName' 
 
    WHEN FieldName = 'mobile' THEN 'mobilePhone' 
 
    WHEN FieldName = 'telephonenumber' THEN 'businessPhones' 
 
    WHEN FieldName = 'st' THEN 'state' 
 
    WHEN FieldName = 'postalcode' THEN 'postalCode' 
 
    WHEN FieldName = 'manager' THEN 'manager' 
 
    WHEN FieldName = 'streetAddress' THEN 'streetAddress' 
 
    WHEN FieldName = 'l' THEN 'city' 
 
    WHEN FieldName = 'homephone' THEN NULL 
 
    WHEN FieldName = 'pager' THEN NULL 
 
    WHEN FieldName = 'description' THEN NULL 
 
    WHEN FieldName = 'ipPhone' THEN NULL 
 
    WHEN FieldName = 'postOfficeBox' THEN NULL 
 
    WHEN FieldName = 'department' THEN 'department' 
 
    WHEN FieldName = 'company' THEN 'companyName' 
 
    WHEN FieldName = 'title' THEN 'jobTitle' 
 
    WHEN FieldName = 'facsimileTelePhonenumber' THEN 'faxNumber' 
 
    WHEN FieldName = 'USNChanged' THEN NULL 
 
    WHEN FieldName = 'objectSID' THEN 'securityIdentifier' 
 
    WHEN FieldName = 'physicalDeliveryOfficeName' THEN 'officeLocation' 
 
    WHEN FieldName = 'userAccountControl' THEN NULL 
 
    WHEN FieldName = 'employeeNumber' THEN NULL 
 
    WHEN FieldName = 'employeeID' THEN 'employeeId' 
 
    WHEN FieldName = 'info' THEN NULL 
 
    WHEN FieldName = 'Extensionattribute1' THEN NULL 
 
    WHEN FieldName = 'thumbnailPhoto' THEN 'thumbnailphoto' 
 
    WHEN FieldName = 'Mail' THEN 'mail' 
 
    ELSE NULL 
 
  END 
  
  </cfquery>

</cftransaction>

<!--- Add  AzureUserCache table --->
<cfquery name="AddAzureUserCacheTable" datasource="#application.config.dsn#">
  
  IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AzureUserCache')
  BEGIN
      CREATE TABLE AzureUserCache (
        AZUserID INT PRIMARY KEY IDENTITY(1,1),
        AZUserName VARCHAR(200),
        AzUserObjectID VARCHAR(36),
        AZAppID INT
    );
  END

</cfquery>

<!--- Add  CalendarSyncTargets  table --->
<cfquery name="AddCalendarSyncTargetsTable" datasource="#application.config.dsn#">
  
  IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CalendarSyncTargets')
  BEGIN
      CREATE TABLE CalendarSyncTargets (
        SyncTargetID INT PRIMARY KEY IDENTITY(1,1),
        AppID INT,
        ItemName VARCHAR(500),
        ObjectID VARCHAR(36),
        ItemType VARCHAR(2),
        ImportExport VARCHAR(2),
        CategoryID INT,
        SubCategoryID INT,
        AZAppID INT
    );
  END

</cfquery>

<!--- Add  EventSyncTracker   table --->
<cfquery name="AddEventSyncTrackerTable" datasource="#application.config.dsn#">
  
  IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventSyncTracker')
  BEGIN
      CREATE TABLE EventSyncTracker (
          EventSyncTrackerID INT PRIMARY KEY IDENTITY(1,1),
          MenuID INT,
          ICEventID INT,
          AZEventID VARCHAR(255)
      );

  END

</cfquery>
