/* Adjust DNN Portal Settings - Version 0.9.9 (2017-11-13)
   ================================================================================
   (c) Sebastian Leupold, dnnWerk/gamma concept mbH 2014-2017

   Run this Script to fix malformed website settings in DNN Platform V.7.0.0 - 9.1.1
      
   == Please make sure to use latest version from http://dnnscript.codeplex.com ===
   
   Instructions:
   =============
   - Install by running as script from SQL item in Host menu inside DNN or run in 
     SQL Server Management Studio, after replacing placeholders {databaseOwner} and
     {objectQualifier} by its proper values from web.config file.

   - Make sure that the currently used account is member of dbOwner database role.
   
   - You may adjust token in line 238 and 239 of this script according to your needs.
   
   License and Disclaimer:
   =======================
   Published under Microsoft Open Source Reciprocal License (Ms-RL). For details, 
   please read http://dnnscript.codeplex.com/license.
   Feel free to use this script as you need, but there is no warranty or liability 
   for any damage or effort, eventually been caused.

   Please report issues at https://dnnscript.codeplex.com/WorkItem/Create
   ================================================================================
*/

-- --------- ensure, tooling does not exist already: --------- 
IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]') AND Type = N'P')
    DROP PROCEDURE {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]
GO

IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}sys_AdjustPortalSettingInt]') AND Type = N'P')
    DROP PROCEDURE {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingInt]
GO

IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}sys_AdjustPortalSettingString]' ) AND Type = N'P')
    DROP PROCEDURE {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingString]
GO

IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}sys_IntValue]' ) AND Type = N'FN')
    DROP FUNCTION {databaseOwner}[{objectQualifier}sys_IntValue]
GO

IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}sys_currentDNNVersion]') AND Type = N'FN')
    DROP FUNCTION {databaseOwner}[{objectQualifier}sys_currentDNNVersion]
GO

-- --------- create tooling: --------- 

CREATE FUNCTION {databaseOwner}[{objectQualifier}sys_currentDNNVersion]()
    RETURNS Int
AS  
BEGIN
    DECLARE @Vers Int;
    SELECT Top(1) @Vers = Major * 10000 + Minor * 100 + Build FROM {databaseOwner}[{objectQualifier}Version] ORDER BY CreatedDate DESC;
    RETURN @Vers;
END
GO

CREATE FUNCTION {databaseOwner}[{objectQualifier}sys_IntValue](@value nVarchar)
    RETURNS INT
AS
BEGIN
    DECLARE @ReturnInt Int = Null;
    IF (RTrim(LTrim(IsNull(@value, N''))) != N'') AND (IsNumeric(@value) = 1) AND (CharIndex(',', @value) = 0) AND (CharIndex('.', @value) = 0)
       SET  @ReturnInt = CAST(@value AS INT);
     RETURN @ReturnInt;
END
GO

IF {databaseOwner}[{objectQualifier}sys_currentDNNVersion]() < 70400 Exec('
CREATE PROCEDURE {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingString]
    @SettingName    nVarChar( 50), -- not Null or Empty
    @DefaultValue   nVarChar(256), -- Null: ignore
    @ForceDefault   Bit =  0,      -- replace existing non-empty value?
    @UserID         Int = -1       -- ID of executing user
AS
BEGIN
    MERGE INTO {databaseOwner}[{objectQualifier}PortalSettings] S USING {databaseOwner}[{objectQualifier}Portals] P ON (S.PortalID = P.PortalID AND S.SettingName = @SettingName)
     WHEN NOT MATCHED AND @DefaultValue IS Not Null THEN 
          INSERT (  PortalID,  SettingName,  SettingValue, CreatedByUserID, CreatedOnDate, LastModifiedByUserID, LastModifiedOnDate,   CultureCode    )
          VALUES (P.PortalID, @SettingName, @DefaultValue,         @UserID,     GetDate(),              @UserID,          GetDate(), P.DefaultLanguage)
     WHEN MATCHED AND @DefaultValue Is Not Null AND ((SettingValue = N'''' AND @DefaultValue != N'''') OR SettingValue Is Null OR @ForceDefault = 1) THEN 
          UPDATE  SET SettingValue = @DefaultValue, LastModifiedByUserID = @UserID, LastModifiedOnDate = GetDate();
END
') ELSE Exec('
CREATE PROCEDURE {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingString]
    @SettingName    nVarChar( 50), -- not Null or Empty
    @DefaultValue   nVarChar(256), -- Null or Empty: delete
    @ForceDefault   Bit =  0,      -- replace existing non-empty value?
    @UserID         Int = -1       -- ID of executing user
AS
BEGIN
    IF @SettingName IN (''DefaultAdminContainer'', ''DefaultAdminSkin'', ''DefaultPortalContainer'', ''DefaultPortalSkin'', 
                             ''DefaultIconLocation'', ''Redirect_AfterLogin'', ''Redirect_AfterLogout'' ,''Redirect_AfterRegistration'') 
        MERGE INTO {databaseOwner}[{objectQualifier}PortalSettings] S USING {databaseOwner}[{objectQualifier}Portals] P ON (S.PortalID = P.PortalID AND S.SettingName = @SettingName)
         WHEN NOT MATCHED AND @DefaultValue IS Not Null THEN 
              INSERT (  PortalID,  SettingName,  SettingValue, CreatedByUserID, CreatedOnDate, LastModifiedByUserID, LastModifiedOnDate,   CultureCode    )
              VALUES (P.PortalID, @SettingName, @DefaultValue,         @UserID,     GetDate(),              @UserID,          GetDate(), P.DefaultLanguage)
         WHEN MATCHED AND @DefaultValue Is Not Null AND ((SettingValue = N'''' AND @DefaultValue != N'''') OR SettingValue Is Null OR @ForceDefault = 1) THEN
              UPDATE  SET SettingValue = @DefaultValue, LastModifiedByUserID = @UserID, LastModifiedOnDate = GetDate();
    ELSE
        MERGE INTO {databaseOwner}[{objectQualifier}PortalSettings] S USING {databaseOwner}[{objectQualifier}PortalLocalization] P ON (S.PortalID = P.PortalID AND S.SettingName = @SettingName)
         WHEN NOT MATCHED AND @DefaultValue IS Not Null THEN 
              INSERT (  PortalID,  SettingName,  SettingValue, CreatedByUserID, CreatedOnDate, LastModifiedByUserID, LastModifiedOnDate,   CultureCode)
              VALUES (P.PortalID, @SettingName, @DefaultValue,         @UserID,     GetDate(),              @UserID,          GetDate(), P.CultureCode)
         WHEN MATCHED AND @DefaultValue Is Not Null AND ((SettingValue = N'''' AND @DefaultValue != N'''') OR SettingValue Is Null OR @ForceDefault = 1) THEN 
              UPDATE  SET SettingValue = @DefaultValue, LastModifiedByUserID = @UserID, LastModifiedOnDate = GetDate();
END
')
GO

IF {databaseOwner}[{objectQualifier}sys_currentDNNVersion]() < 70400 Exec('
CREATE PROCEDURE {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]
    @SettingName    nVarChar( 50), -- not Null or Empty
    @DefaultValue   nVarChar(256), -- Null: remove invalid settings only
    @ForceDefault   Bit,           -- replace existing non-empty value?
    @ValueList      nVarChar(511), -- List of allowed values, separated by comma
    @UserID         Int = -1       -- ID of executing user
AS
BEGIN
    WITH ValueCTE AS (SELECT RowValue FROM {databaseOwner}[{objectQualifier}ConvertListToTable]('','', @ValueList))
        MERGE INTO {databaseOwner}[{objectQualifier}PortalSettings] S USING {databaseOwner}[{objectQualifier}Portals] P ON (S.PortalID = P.PortalID AND S.SettingName = @SettingName)
         WHEN NOT MATCHED THEN 
              INSERT (  PortalID,  SettingName,  SettingValue, CreatedByUserID, CreatedOnDate, LastModifiedByUserID, LastModifiedOnDate,   CultureCode    )
              VALUES (P.PortalID, @SettingName, @DefaultValue,         @UserID,     GetDate(),              @UserID,          GetDate(), P.DefaultLanguage)
         WHEN MATCHED AND @DefaultValue Is Null AND (NOT SettingValue IN (SELECT RowValue FROM valueCTE) OR @ForceDefault = 1) THEN
              DELETE
         WHEN MATCHED AND (IsNull(SettingValue, N'''') = N'''' OR NOT SettingValue IN (SELECT RowValue FROM valueCTE) OR @ForceDefault = 1) THEN
              UPDATE  SET SettingValue = @DefaultValue, LastModifiedByUserID = @UserID, LastModifiedOnDate = GetDate();
END
') ELSE Exec('
CREATE PROCEDURE {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]
    @SettingName    nVarChar( 50), -- not Null or Empty
    @DefaultValue   nVarChar(256), -- Null or Empty: delete
    @ForceDefault   Bit,           -- replace existing non-empty value?
    @ValueList      nVarChar(511), -- List of allowed values, separated by comma
    @UserID         Int = -1       -- ID of executing user
AS
BEGIN
    IF @SettingName IN (''DefaultAdminContainer'', ''DefaultAdminSkin'', ''DefaultPortalContainer'', ''DefaultPortalSkin'', 
                             ''DefaultIconLocation'', ''Redirect_AfterLogin'', ''Redirect_AfterLogout'' ,''Redirect_AfterRegistration'')
        WITH ValueCTE AS (SELECT RowValue FROM {databaseOwner}[{objectQualifier}ConvertListToTable]('','', @ValueList))
            MERGE INTO {databaseOwner}[{objectQualifier}PortalSettings] S USING {databaseOwner}[{objectQualifier}PortalLocalization] P ON (S.PortalID = P.PortalID AND S.SettingName = @SettingName)
             WHEN NOT MATCHED THEN 
                  INSERT (  PortalID,  SettingName,  SettingValue, CreatedByUserID, CreatedOnDate, LastModifiedByUserID, LastModifiedOnDate,   CultureCode)
                  VALUES (P.PortalID, @SettingName, @DefaultValue,         @UserID,     GetDate(),              @UserID,          GetDate(), P.CultureCode)
             WHEN MATCHED AND @DefaultValue Is Null AND (NOT SettingValue IN (SELECT RowValue FROM valueCTE) OR @ForceDefault = 1) THEN
                  DELETE
             WHEN MATCHED AND (IsNull(SettingValue, N'''') = N'''' OR NOT SettingValue IN (SELECT RowValue FROM valueCTE) OR @ForceDefault = 1) THEN
                  UPDATE SET SettingValue = @DefaultValue, LastModifiedByUserID = @UserID, LastModifiedOnDate = GetDate();
    ELSE
        WITH ValueCTE AS (SELECT RowValue FROM {databaseOwner}[{objectQualifier}ConvertListToTable]('','', @ValueList))
            MERGE INTO {databaseOwner}[{objectQualifier}PortalSettings] S USING {databaseOwner}[{objectQualifier}Portals] P ON (S.PortalID = P.PortalID AND S.SettingName = @SettingName)
             WHEN NOT MATCHED THEN 
                  INSERT (  PortalID,  SettingName,  SettingValue, CreatedByUserID, CreatedOnDate, LastModifiedByUserID, LastModifiedOnDate,   CultureCode    )
                  VALUES (P.PortalID, @SettingName, @DefaultValue,         @UserID,     GetDate(),              @UserID,          GetDate(), P.DefaultLanguage)
             WHEN MATCHED AND @DefaultValue Is Null AND (NOT SettingValue IN (SELECT RowValue FROM valueCTE) OR @ForceDefault = 1) THEN
                  DELETE
             WHEN MATCHED AND (IsNull(SettingValue, N'''') = N'''' OR NOT SettingValue IN (SELECT RowValue FROM valueCTE) OR @ForceDefault = 1) THEN 
                  UPDATE SET SettingValue = @DefaultValue, LastModifiedByUserID = @UserID, LastModifiedOnDate = GetDate();
END
')
GO


IF {databaseOwner}[{objectQualifier}sys_currentDNNVersion]() < 70400 Exec('
CREATE PROCEDURE {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingInt]
    @SettingName    nVarChar( 50), -- not Null or Empty
    @DefaultValue   Int,           -- Null: remove invalid settings only
    @ForceDefault   Bit,           -- replace existing non-empty value?
    @MinValue       Int = Null,    -- optional: specify min value, smaller values will be adjusted to default
    @MaxValue       Int = Null,    -- optional: specify max value, smaller values will be adjusted to default
    @UserID         Int = -1       -- ID of executing user
AS
BEGIN
    DECLARE @DefaultString AS nVarChar( 11) = CAST(@DefaultValue AS nVarChar(11));
    MERGE INTO {databaseOwner}[{objectQualifier}PortalSettings] S USING {databaseOwner}[{objectQualifier}Portals] P ON (S.PortalID = P.PortalID AND S.SettingName = @SettingName)
     WHEN NOT MATCHED AND @DefaultValue IS Not Null THEN 
          INSERT (  PortalID,  SettingName,  SettingValue, CreatedByUserID, CreatedOnDate, LastModifiedByUserID, LastModifiedOnDate,   CultureCode    )
          VALUES (P.PortalID, @SettingName, @DefaultValue,         @UserID,     GetDate(),              @UserID,          GetDate(), P.DefaultLanguage)
     WHEN MATCHED AND @DefaultValue Is Null 
                  AND ({databaseOwner}[{objectQualifier}sys_IntValue](SettingValue) IS Null
                   OR  {databaseOwner}[{objectQualifier}sys_IntValue](SettingValue)  < @MinValue 
                   OR  {databaseOwner}[{objectQualifier}sys_IntValue](SettingValue)  > @MaxValue) THEN 
          DELETE
     WHEN MATCHED AND ({databaseOwner}[{objectQualifier}sys_IntValue](SettingValue) IS Null
                   OR  {databaseOwner}[{objectQualifier}sys_IntValue](SettingValue)  < @MinValue 
                   OR  {databaseOwner}[{objectQualifier}sys_IntValue](SettingValue)  > @MaxValue 
                   OR  @ForceDefault = 1) THEN 
          UPDATE  SET SettingValue = @DefaultString, LastModifiedByUserID = @UserID, LastModifiedOnDate = GetDate();
END
') ELSE Exec('
CREATE PROCEDURE {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingInt]
    @SettingName    nVarChar( 50), -- not Null or Empty
    @DefaultValue   Int,           -- Null: delete
    @ForceDefault   Bit,           -- replace existing non-empty value?
    @MinValue       Int = Null,    -- optional: specify min value, smaller values will be adjusted to default
    @MaxValue       Int = Null,    -- optional: specify max value, smaller values will be adjusted to default
    @UserID         Int = -1       -- ID of executing user
AS
BEGIN
    DECLARE @DefaultString AS nVarChar( 11) = CAST(@DefaultValue AS nVarChar(11));
    IF @SettingName IN (''DefaultAdminContainer'', ''DefaultAdminSkin'', ''DefaultPortalContainer'', ''DefaultPortalSkin'', 
                        ''DefaultIconLocation'', ''Redirect_AfterLogin'', ''Redirect_AfterLogout'' ,''Redirect_AfterRegistration'') 
        MERGE INTO {databaseOwner}[{objectQualifier}PortalSettings] S USING {databaseOwner}[{objectQualifier}PortalLocalization] P ON (S.PortalID = P.PortalID AND S.SettingName = @SettingName)
         WHEN NOT MATCHED AND @DefaultValue IS Not Null THEN 
              INSERT (  PortalID,  SettingName,  SettingValue, CreatedByUserID, CreatedOnDate, LastModifiedByUserID, LastModifiedOnDate,   CultureCode)
              VALUES (P.PortalID, @SettingName, @DefaultValue,         @UserID,     GetDate(),              @UserID,          GetDate(), P.CultureCode)
         WHEN MATCHED AND @DefaultValue Is Null 
                      AND ({databaseOwner}[{objectQualifier}sys_IntValue](SettingValue) IS Null
                       OR  {databaseOwner}[{objectQualifier}sys_IntValue](SettingValue)  < @MinValue 
                       OR  {databaseOwner}[{objectQualifier}sys_IntValue](SettingValue)  > @MaxValue) THEN 
              DELETE
         WHEN MATCHED AND ({databaseOwner}[{objectQualifier}sys_IntValue](SettingValue) IS Null
                       OR  {databaseOwner}[{objectQualifier}sys_IntValue](SettingValue)  < @MinValue 
                       OR  {databaseOwner}[{objectQualifier}sys_IntValue](SettingValue)  > @MaxValue 
                       OR  @ForceDefault = 1) THEN 
              UPDATE  SET SettingValue = @DefaultString, LastModifiedByUserID = @UserID, LastModifiedOnDate = GetDate();
    ELSE 
        MERGE INTO {databaseOwner}[{objectQualifier}PortalSettings] S USING {databaseOwner}[{objectQualifier}Portals] P ON (S.PortalID = P.PortalID AND S.SettingName = @SettingName)
         WHEN NOT MATCHED AND @DefaultValue IS Not Null THEN 
              INSERT (  PortalID,  SettingName,  SettingValue, CreatedByUserID, CreatedOnDate, LastModifiedByUserID, LastModifiedOnDate,   CultureCode    )
              VALUES (P.PortalID, @SettingName, @DefaultValue,         @UserID,     GetDate(),              @UserID,          GetDate(), P.DefaultLanguage)
         WHEN MATCHED AND @DefaultValue Is Null 
                      AND ({databaseOwner}[{objectQualifier}sys_IntValue](SettingValue) IS Null
                       OR  {databaseOwner}[{objectQualifier}sys_IntValue](SettingValue)  < @MinValue 
                       OR  {databaseOwner}[{objectQualifier}sys_IntValue](SettingValue)  > @MaxValue) THEN 
              DELETE
         WHEN MATCHED AND ({databaseOwner}[{objectQualifier}sys_IntValue](SettingValue) IS Null
                       OR  {databaseOwner}[{objectQualifier}sys_IntValue](SettingValue)  < @MinValue 
                       OR  {databaseOwner}[{objectQualifier}sys_IntValue](SettingValue)  > @MaxValue 
                       OR  @ForceDefault = 1) THEN
              UPDATE  SET SettingValue = @DefaultString, LastModifiedByUserID = @UserID, LastModifiedOnDate = GetDate();
END')
GO

/* ================================================================================
   You may modify the two following settings, according to your needs:
   -------------------------------------------------------------------                            */
DECLARE @forceDefault AS Bit =  1; -- set to 1, if you want to use defaults for all settings
DECLARE @UserID       AS Int = -1; -- you may replace by your userID to document changes properly
/* ================================================================================               */


-- make sure, lastModifiedDate is populated
UPDATE {databaseOwner}[{objectQualifier}PortalSettings]
 SET   LastModifiedOnDate = N'2000-01-01'
 WHERE LastModifiedOnDate is Null;

     -- Adjust CultureCode:
IF {databaseOwner}[{objectQualifier}sys_currentDNNVersion]() < 70400 BEGIN
    -- culture code is ignored, we only need a single record, so make sure to keep only latest version:
    DELETE  S
     FROM {databaseOwner}[{objectQualifier}PortalSettings] S
     LEFT JOIN (SELECT PortalID PID, Settingname SN, Max(LastModifiedOnDate) LMOD 
                 FROM  {databaseOwner}[{objectQualifier}PortalSettings]
                 GROUP BY PortalID, SettingName) Q 
       ON S.PortalID = Q.PID AND S.SettingName = Q.SN AND S.LastModifiedOnDate = Q.LMOD
    WHERE Q.SN IS Null;
    -- assuming portal settings are most likely updated using default culture, set all settings to use it. 
    UPDATE S
     SET   S.CultureCode = P.DefaultLanguage
     FROM  {databaseOwner}[{objectQualifier}PortalSettings] S
     JOIN  {databaseOwner}[{objectQualifier}Portals]        P ON S.PortalID = P.PortalID;
 END ELSE BEGIN
    -- language specific settings:
    DELETE S
     FROM  {databaseOwner}[{objectQualifier}PortalSettings] S
     WHERE SettingName IN ('DefaultAdminContainer', 'DefaultAdminSkin', 'DefaultPortalContainer', 'DefaultPortalSkin', 
                           'DefaultIconLocation', 'Redirect_AfterLogin', 'Redirect_AfterLogout' ,'Redirect_AfterRegistration')
       AND ISNull(CultureCode, '') = ''
       AND Exists (SELECT * 
                    FROM {databaseOwner}[{objectQualifier}PortalSettings] Q 
                    JOIN {databaseOwner}[{objectQualifier}Portals] P ON Q.PortalID = P.PortalID AND Q.CultureCode = P.DefaultLanguage
                    WHERE Q.PortalID = S.PortalID AND Q.SettingName = S.SettingName);
    UPDATE S
     SET CultureCode = P.DefaultLanguage
     FROM {databaseOwner}[{objectQualifier}PortalSettings] S
     JOIN  {databaseOwner}[{objectQualifier}Portals]       P ON S.PortalID = P.PortalID
     WHERE SettingName IN ('DefaultAdminContainer', 'DefaultAdminSkin', 'DefaultPortalContainer', 'DefaultPortalSkin', 
                           'DefaultIconLocation', 'Redirect_AfterLogin', 'Redirect_AfterLogout' ,'Redirect_AfterRegistration')
       AND IsNull(CultureCode, N'') = N'';
    DELETE S
     FROM  {databaseOwner}[{objectQualifier}PortalSettings] S
     WHERE SettingName IN ('DefaultAdminContainer', 'DefaultAdminSkin', 'DefaultPortalContainer', 'DefaultPortalSkin', 
                           'DefaultIconLocation', 'Redirect_AfterLogin', 'Redirect_AfterLogout' ,'Redirect_AfterRegistration')
       AND CultureCode NOT IN (SELECT CultureCode
                                FROM  {databaseOwner}[{objectQualifier}PortalLanguages] P 
                                JOIN  {databaseOwner}[{objectQualifier}Languages] L ON P.LanguageID = L.LanguageID
                                WHERE P.PortalID = S.PortalID); 
    -- sync non-default language, if missing:
    MERGE INTO  {databaseOwner}[{objectQualifier}PortalSettings] S
    USING (SELECT Q.PortalID, L.CultureCode, Q.SettingName, Q.SettingValue
            FROM  {databaseOwner}[{objectQualifier}PortalSettings]  Q
            JOIN  {databaseOwner}[{objectQualifier}Portals]         P ON Q.PortalID = P.PortalID AND Q.CultureCode = P.DefaultLanguage
            JOIN  {databaseOwner}[{objectQualifier}PortalLanguages] X ON P.PortalID = X.PortalID
            JOIN  {databaseOwner}[{objectQualifier}Languages]       L ON X.LanguageID = L.LanguageID
            WHERE SettingName IN ('DefaultAdminContainer', 'DefaultAdminSkin', 'DefaultPortalContainer', 'DefaultPortalSkin', 
                                  'DefaultIconLocation', 'Redirect_AfterLogin', 'Redirect_AfterLogout' ,'Redirect_AfterRegistration')
              AND L.CultureCode != P.DefaultLanguage) U
       ON (S.PortalID = U.PortalID AND S.SettingName = U.SettingName AND S.CultureCode != U.CultureCode)
       WHEN MATCHED AND ISNull(S.SettingValue, '') = N'' AND U.SettingValue != S.SettingValue THEN 
        UPDATE SET S.SettingValue = U.SettingValue, S.LastModifiedOnDate = GetDate(), LastModifiedByUserID = @UserID
       WHEN NOT MATCHED THEN 
        INSERT (  PortalID,   SettingName,   SettingValue,  CreatedByUserID, CreatedOnDate, LastModifiedByUserID, LastModifiedOnDate,   CultureCode)
        VALUES (U.PortalID, U.SettingName, U.SettingValue,          @UserID,     GetDate(),              @UserID,          GetDate(), U.CultureCode);
    -- neutral settings:
    UPDATE S
     SET CultureCode = Null
     FROM {databaseOwner}[{objectQualifier}PortalSettings] S
     WHERE SettingName IN ('AUM_RedirectOldProfileUrl', 'AUM_VanityUrlPrefix', 'ControlBar_BookmarkCategory', 'ControlPanelMode', 'ControlPanelSecurity', 'ControlPanelVisibility',
                           'CrmEnableCompositeFiles', 'CrmMinifyCss', 'CrmMinifyJs', 'CrmUseApplicationSettings', 'EnableModuleEffect', 'EnablePopups', 'EnableRegisterNotification', 
                           'EnableSkinWidgets', 'FavIconPath', 'HideFoldersEnabled', 'InlineEditorEnabled', 'MaximumVersionHistory', 'MessagingAllowAttachments',  
                           'MessagingProfanityFilters', 'MessagingRecipientLimit', 'MessagingSendEmail', 'MessagingThrottlingInterval', 'paypalsandbox', 'PortalAliasMapping', 
                           'Profile_DefaultVisibility', 'Profile_DisplayVisibility', 'Registration_RandomPassword', 'Registration_RegistrationFormType',  
                           'Registration_RequireConfirmPassword', 'Registration_RequireUniqueDisplayName', 'Registration_UseAuthProviders', 'Registration_UseEmailAsUserName', 
                           'Registration_UseProfanityFilter', 'SearchAdminInitialization', 'Security_CaptchaChangePassword', 'Security_CaptchaLogin', 'Security_CaptchaRegister', 
                           'Security_CaptchaRetrivePassword', 'Security_EmailValidation', 'Security_RequireValidProfile', 'Security_RequireValidProfileAtLogin', 'SSLEnabled', 
                           'SSLEnforced', 'SSLURL', 'STDURL', 'TimeZone')
       AND CultureCode = N''
       AND NOT Exists (SELECT * 
                        FROM  {databaseOwner}[{objectQualifier}PortalSettings] Q 
                        JOIN  {databaseOwner}[{objectQualifier}Portals] P ON Q.PortalID = P.PortalID AND Q.CultureCode = P.DefaultLanguage
                        WHERE Q.PortalID = S.PortalID AND Q.SettingName = S.SettingName AND Q.CultureCode Is Null)
    UPDATE S
     SET CultureCode = Null
     FROM {databaseOwner}[{objectQualifier}PortalSettings] S
     JOIN  {databaseOwner}[{objectQualifier}Portals]       P ON S.PortalID = P.PortalID
     WHERE SettingName IN ('AUM_RedirectOldProfileUrl', 'AUM_VanityUrlPrefix', 'ControlBar_BookmarkCategory', 'ControlPanelMode', 'ControlPanelSecurity', 'ControlPanelVisibility',
                           'CrmEnableCompositeFiles', 'CrmMinifyCss', 'CrmMinifyJs', 'CrmUseApplicationSettings', 'EnableModuleEffect', 'EnablePopups', 'EnableRegisterNotification', 
                           'EnableSkinWidgets', 'FavIconPath', 'HideFoldersEnabled', 'InlineEditorEnabled', 'MaximumVersionHistory', 'MessagingAllowAttachments',  
                           'MessagingProfanityFilters', 'MessagingRecipientLimit', 'MessagingSendEmail', 'MessagingThrottlingInterval', 'paypalsandbox', 'PortalAliasMapping', 
                           'Profile_DefaultVisibility', 'Profile_DisplayVisibility', 'Registration_RandomPassword', 'Registration_RegistrationFormType',  
                           'Registration_RequireConfirmPassword', 'Registration_RequireUniqueDisplayName', 'Registration_UseAuthProviders', 'Registration_UseEmailAsUserName', 
                           'Registration_UseProfanityFilter', 'SearchAdminInitialization', 'Security_CaptchaChangePassword', 'Security_CaptchaLogin', 'Security_CaptchaRegister', 
                           'Security_CaptchaRetrivePassword', 'Security_EmailValidation', 'Security_RequireValidProfile', 'Security_RequireValidProfileAtLogin', 'SSLEnabled', 
                           'SSLEnforced', 'SSLURL', 'STDURL', 'TimeZone')
       AND CultureCode = P.DefaultLanguage
       AND NOT Exists (SELECT * 
                        FROM  {databaseOwner}[{objectQualifier}PortalSettings] Q 
                        JOIN  {databaseOwner}[{objectQualifier}Portals] P ON Q.PortalID = P.PortalID AND Q.CultureCode = P.DefaultLanguage
                        WHERE Q.PortalID = S.PortalID AND Q.SettingName = S.SettingName AND Q.CultureCode Is Null)
    DELETE S
     FROM  {databaseOwner}[{objectQualifier}PortalSettings] S
     WHERE SettingName IN ('AUM_RedirectOldProfileUrl', 'AUM_VanityUrlPrefix', 'ControlBar_BookmarkCategory', 'ControlPanelMode', 'ControlPanelSecurity', 'ControlPanelVisibility',
                           'CrmEnableCompositeFiles', 'CrmMinifyCss', 'CrmMinifyJs', 'CrmUseApplicationSettings', 'EnableModuleEffect', 'EnablePopups', 'EnableRegisterNotification', 
                           'EnableSkinWidgets', 'FavIconPath', 'HideFoldersEnabled', 'InlineEditorEnabled', 'MaximumVersionHistory', 'MessagingAllowAttachments',  
                           'MessagingProfanityFilters', 'MessagingRecipientLimit', 'MessagingSendEmail', 'MessagingThrottlingInterval', 'paypalsandbox', 'PortalAliasMapping', 
                           'Profile_DefaultVisibility', 'Profile_DisplayVisibility', 'Registration_RandomPassword', 'Registration_RegistrationFormType',  
                           'Registration_RequireConfirmPassword', 'Registration_RequireUniqueDisplayName', 'Registration_UseAuthProviders', 'Registration_UseEmailAsUserName', 
                           'Registration_UseProfanityFilter', 'SearchAdminInitialization', 'Security_CaptchaChangePassword', 'Security_CaptchaLogin', 'Security_CaptchaRegister', 
                           'Security_CaptchaRetrivePassword', 'Security_EmailValidation', 'Security_RequireValidProfile', 'Security_RequireValidProfileAtLogin', 'SSLEnabled', 
                           'SSLEnforced', 'SSLURL', 'STDURL', 'TimeZone')
       AND CultureCode Is NOT NULL;
END;

-- ----------- fix settings: --------------
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Column_Address'                  , N'False' , @forceDefault, N'False,True'   , @UserID 
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Column_Authorized'               , N'True'  , @forceDefault, N'False,True'   , @UserID 
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Column_CreatedDate'              , N'True'  , @forceDefault, N'False,True'   , @UserID 
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Column_DisplayName'              , N'True'  , @forceDefault, N'False,True'   , @UserID 
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Column_Email'                    , N'False' , @forceDefault, N'False,True'   , @UserID 
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Column_FirstName'                , N'False' , @forceDefault, N'False,True'   , @UserID 
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Column_LastLogin'                , N'False' , @forceDefault, N'False,True'   , @UserID 
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Column_LastName'                 , N'False' , @forceDefault, N'False,True'   , @UserID 
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Column_Telephone'                , N'False' , @forceDefault, N'False,True'   , @UserID 
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'AddCachebusterToResourceUrls'    , N'True'  , @forceDefault, N'False,True'   , @UserID 
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingString] N'AddCompatibleHttpHeader'         , N'IE=edge', @forceDefault,                   @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'AUM_RedirectOldProfileUrl'       , N'Y'     , @forceDefault, N'Y,N'          , @UserID 
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingString] N'AUM_VanityUrlPrefix'             , N'users' , @forceDefault,                   @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'ControlBar_BookmarkCategory'     , N'Common', @forceDefault, N'Common'       , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'ControlPanelMode'                , N'VIEW'  , @forceDefault, N'VIEW,EDIT'    , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'ControlPanelSecurity'            , N'MODULE', @forceDefault, N'TAB,MODULE'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'ControlPanelVisibility'          , N'MAX'   , @forceDefault, N'MAX,MIN'      , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'CrmEnableCompositeFiles'         , N'False' , @forceDefault, N'False,True'   , @UserID 
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'CrmMinifyCss'                    , N'False' , @forceDefault, N'False,True'   , @UserID 
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'CrmMinifyJs'                     , N'False' , @forceDefault, N'False,True'   , @UserID 
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'CrmUseApplicationSettings'       , N'False' , @forceDefault, N'False,True'   , @UserID 
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingString] N'DefaultAdminContainer'           , Null     , @forceDefault,                   @UserID -- Inherit from Host
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingString] N'DefaultAdminSkin'                , Null     , @forceDefault,                   @UserID -- Inherit from Host
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingString] N'DefaultIconLocation'             , N'icons/Sigma',                             @forceDefault, @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingString] N'DefaultPortalContainer'          , Null     , @forceDefault,                   @UserID -- Inherit from Host
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingString] N'DefaultPortalContainer'          , Null     , @forceDefault,                   @UserID -- Inherit from Host
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingString] N'DefPreviewProfiles_Created'      , N'DNNCORP.CE', @forceDefault,               @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'DisablePrivateMessage'           , N'N' ,     @forceDefault, N'N,Y'          , @UserID 
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingInt]    N'Display_Mode'                    ,        2 , @forceDefault,       0,       2, @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Display_SuppressPager'           , N'FAlse' , @forceDefault, N'False,True'   , @UserID  
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'EnableModuleEffect'              , N'True'  , @forceDefault, N'False,True'   , @UserID  
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'EnablePopups'                    , N'True'  , @forceDefault, N'False,True'   , @UserID  
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'EnableRegisterNotification'      , N'True'  , @forceDefault, N'False,True'   , @UserID  
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'EnableSkinWidgets'               , N'True'  , @forceDefault, N'False,True'   , @UserID  
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingString] N'FavIconPath'                     , N'FileID=0',                                @forceDefault, @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'HideFoldersEnabled'              , N'True'  , @forceDefault, N'False,True'   , @UserID  
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'HideLoginControl'                , N'False' , @forceDefault, N'False,True'   , @UserID  
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'InjectModuleHyperlink'           , N'True'  , @forceDefault, N'False,True'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'InlineEditorEnabled'             , N'True'  , @forceDefault, N'False,True'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingInt]    N'MaximumVersionHistory'           ,        5 , @forceDefault,       1,      99, @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'MessagingAllowAttachments'       , N'NO'    , @forceDefault, N'YES,NO'       , @UserID 
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'MessagingIncludeAttachments'     , N'NO'    , @forceDefault, N'YES,NO'       , @UserID 
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'MessagingProfanityFilters'       , N'NO'    , @forceDefault, N'YES,NO'       , @UserID 
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingInt]    N'MessagingRecipientLimit'         ,        5 , @forceDefault,       3,  999999, @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'MessagingSendEmail'              , N'YES'   , @forceDefault, N'YES,NO'       , @UserID 
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingInt]    N'MessagingThrottlingInterval'     ,        0 , @forceDefault,       0,     240, @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingString] N'PageHeadText'                     , N'<meta content="text/html; charset=UTF-8" http-equiv="Content-Type" />',                                @forceDefault, @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'paypalsandbox'                   , N'False' , @forceDefault, N'False,True'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'PortalAliasMapping'              , N'CANONICALURL', @forceDefault, N'CANONICALURL,REDIRECT,NONE', @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingInt]    N'Profile_DefaultVisibility'       ,        0 , @forceDefault,       0,       5, @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Profile_DisplayVisibility'       , N'True'  , @forceDefault, N'False,True'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Profile_ManageServices'          , N'True'  , @forceDefault, N'False,True'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingInt]    N'Records_PerPage'                 ,       20 , @forceDefault,       1,     100, @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingInt]    N'Redirect_AfterLogin'             ,       -1 , @forceDefault,      -1, 9999999, @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingInt]    N'Redirect_AfterLogout'            ,       -1 , @forceDefault,      -1, 9999999, @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingInt]    N'Redirect_AfterRegistration'      ,       -1 , @forceDefault,      -1, 9999999, @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Registration_RandomPassword'     , N'False' , @forceDefault, N'False,True'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingInt]    N'Registration_RegistrationFormType',       0 , @forceDefault,       0,       1, @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Registration_RequireConfirmPassword', N'True',@forceDefault, N'False,True'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Registration_RequireUniqueDisplayName', N'True',@forceDefault, N'False,True' , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Registration_UseAuthProviders'   , N'False' , @forceDefault, N'False,True'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Registration_UseEmailAsUserName' , N'false' , @forceDefault, N'false,true'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Registration_UseProfanityFilter' , N'False' , @forceDefault, N'False,True'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'SearchAdminInitialization'       , N'false' , @forceDefault, N'False,True'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Security_CaptchaChangePassword'  , N'False' , @forceDefault, N'False,True'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Security_CaptchaLogin'           , N'False' , @forceDefault, N'False,True'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Security_CaptchaRegister'        , N'False' , @forceDefault, N'False,True'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Security_CaptchaRetrivePassword' , N'False' , @forceDefault, N'False,True'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingString] N'Security_EmailValidation'        , Null     , @forceDefault,                   @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Security_RequireValidProfile'    , N'False' , @forceDefault, N'False,True'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'Security_RequireValidProfileAtLogin',N'True', @forceDefault, N'False,True'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingInt]    N'Security_UsersControl'           ,        0 , @forceDefault,       0,       1, @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingInt]    N'SMTPAuthentication'              ,        0 ,             0,       0,       2, @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingInt]    N'SMTPConnectionLimit'             ,        1 ,             0,       1,     100, @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'SMTPEnableSSL'                   , N'N'     ,             0, N'Y,N'          , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingInt]    N'SMTPMaxIdleTime'                 ,        0 ,             0,       0,    3600, @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'SMTPmode'                        , 'h'      , @forceDefault, N'p,h'          , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'SSLEnabled'                      , N'False' ,             0, N'False,True'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'SSLEnforced'                     , N'False' ,             0, N'False,True'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingString] N'SSLURL'                          , Null     ,             0,                   @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingString] N'STDURL'                          , Null     ,             0,                   @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingString] N'TimeZone'                        , N'Pacific Standard Time', @forceDefault,    @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'LiveID_Enabled'                  , N'False' ,             0, N'False,True'   , @UserID
Exec  {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]   N'LiveID_IncludeHelp'              , N'True'  , @forceDefault, N'False,True'   , @UserID
GO

-- ------------ remove tooling: ------------
DROP PROCEDURE {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingString]
DROP PROCEDURE {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingList]
DROP PROCEDURE {databaseOwner}[{objectQualifier}sys_AdjustPortalSettingInt]
DROP FUNCTION  {databaseOwner}[{objectQualifier}sys_currentDNNVersion]
DROP FUNCTION  {databaseOwner}[{objectQualifier}sys_IntValue]
GO