/*
 * One Stop Script for CFMS Database Setup
 * Copyright © 2018-2025 SingularLogic S.A. All rights reserved.
 *
 * Usage: Run after creating a new CFMS database or after restoring a backup of a CFMS database.
 * Select the CFMS database and run the script.
 * Switch the SSMS output fromm "Results" to "Messages".
 * If the script seems blocked you need to close the other connections to the database.
 * It is safe to interrupt and rerun many times this script even on your production database.
 * You can change the user name from clroot to something else. (Using DOMAIN\user is not supported).
 */

declare @user as nvarchar(max) = 'clroot';

/*********/

declare @msg as nvarchar(max);
declare @sql as nvarchar(max);

if user_name() <> 'dbo'
begin
	print 'You are not a sysadmin. Please login as database administrator.';
	return;
end;

declare @ver int = cast(substring(CONVERT(nvarchar(128), SERVERPROPERTY('ProductVersion')), 1, 2) AS int);
if @ver < 13
begin
	print 'Please upgrade your SQL Server. CFMS requires SQL Server 2016 with Service Pack 1. SQL Server 2019 is recommended.';
	return;
end;

if cast(SERVERPROPERTY('IsIntegratedSecurityOnly') as int) = 1
begin
	print 'Database server must have mixed security mode enabled.';
	return;
end;

/* expires 2020-07-05, validates earlier code. */
if not exists(select 1 from master.sys.certificates where name = 'SingularLogicCertificate')
	exec('use master; create certificate SingularLogicCertificate from binary = 0x3082058B30820473A00302010202105316756BCE9318A1B2A45024EC09806E300D06092A864886F70D01010B0500307D310B3009060355040613024742311B30190603550408131247726561746572204D616E636865737465723110300E0603550407130753616C666F7264311A3018060355040A1311434F4D4F444F204341204C696D69746564312330210603550403131A434F4D4F444F2052534120436F6465205369676E696E67204341301E170D3137303730353030303030305A170D3230303730343233353935395A3081CB310B3009060355040613024752310E300C06035504110C0531343536343113301106035504080C0A4E2E204B696669736961310F300D06035504070C06417468656E73311A301806035504090C11CE91CEA6CE9C3A203939373938353136393123302106035504090C1A3320416368616961732026205472697A696E696173205374722E311B3019060355040A0C1253494E47554C41524C4F47494320532E412E310B3009060355040B0C024954311B301906035504030C1253494E47554C41524C4F47494320532E412E30820122300D06092A864886F70D01010105000382010F003082010A0282010100C40CA8D2782EAF6267EB2844CD478DC46C4DF4599200924EC7148F9D42B835F73CCE3A7BDD35DF07AC6EE5EC0C2DA351392C21FC4FCA8DDCB2C47C1A0D3173A563F40F4C854A9A937EAD190DBE495ED94D6A5F69ABF79CCDF2890A9D5EF2A021DA1A58BDCF6143395EBD2F189C5B8E1F7DA13025F3EA589085DA3977C1F93BCA1E0B741DE5311F1EB06E0681BE58FE1903072884B39DAB5AF06CF41B7963E47E2BBC83AA2D57233DE9A745B740B674437BA682CAC90264D816795318DCE8AECEF6997C3EC084B161EF23FB8378465274B203F4E83B21F0C012A5532090CACA292C9006DEA989A63A55B95D3AADE1EC6845627B5B35FDFAAA34FB038E0BAD93170203010001A38201B6308201B2301F0603551D23041830168014299160FF8A4DFAEBF9A66AB8CFF9E64BBD49CE12301D0603551D0E04160414AAB55AC0E4B7902B1AC7D76DB4CF0BE17B0B2B13300E0603551D0F0101FF040403020780300C0603551D130101FF0402300030130603551D25040C300A06082B06010505070303301106096086480186F842010104040302041030460603551D20043F303D303B060C2B06010401B2310102010302302B302906082B06010505070201161D68747470733A2F2F7365637572652E636F6D6F646F2E6E65742F43505330430603551D1F043C303A3038A036A0348632687474703A2F2F63726C2E636F6D6F646F63612E636F6D2F434F4D4F444F525341436F64655369676E696E6743412E63726C307406082B0601050507010104683066303E06082B060105050730028632687474703A2F2F6372742E636F6D6F646F63612E636F6D2F434F4D4F444F525341436F64655369676E696E6743412E637274302406082B060105050730018618687474703A2F2F6F6373702E636F6D6F646F63612E636F6D30270603551D110420301E811C646E74696E6F64696D6F734073696E67756C61726C6F6769632E6575300D06092A864886F70D01010B0500038201010042DEB2DFC904833A95397D3DCFD3E385A52C932516E0A1C016EBABC4BEBC3259E8F905055CCD4BD4A2BA94ECBC34809D0988FAB3C0F3DE3CE20539861D063DBAC7CD28B8AFC785C4EE21F1B4B56C5742C7D3EE8FC1700AB3A97D73E3D2E8B4AAF5D5C23A3EA9AB153829328A1CE7F5FBD8CF6029B5DC0A7DFFF05DDDF18DADF108CCFC36B70CEE1C0E95AF3C8FA1BFA6493E4C385E17C86125E02576DFDAF459652934933C5DAD188DC05F8B6EC20ECF2270C8805080C6270EDE300677EDCA43C144851C1FD65BC31057A0DDB2CD32892823728D85AB77E26BF9DCECA63C9CACF0B83EB4AF8867DB70B4760FB4805C5505A1797D0665E4872E9B303C02BF1EF3;');
if not exists(select 1 from sys.server_principals where name = 'SingularLogic')
	create login SingularLogic from certificate SingularLogicCertificate;
exec('use master; grant unsafe assembly to SingularLogic;');

/* expires 2023-07-29, validates earlier code. */
if not exists(select 1 from master.sys.certificates where name = 'SingularLogic2020Certificate')
	exec('use master; create certificate SingularLogic2020Certificate from binary = 0x3082059130820479A003020102021076174B02FDC71CE1356E7803427C9BE1300D06092A864886F70D01010B0500307C310B3009060355040613024742311B30190603550408131247726561746572204D616E636865737465723110300E0603550407130753616C666F726431183016060355040A130F5365637469676F204C696D69746564312430220603550403131B5365637469676F2052534120436F6465205369676E696E67204341301E170D3230303732383030303030305A170D3233303732383233353935395A3081CF310B3009060355040613024752310E300C06035504110C053134353634310F300D06035504080C06417474696B693113301106035504070C0A4E2E204B6966697369613123302106035504090C1A3320416368616961732026205472697A696E696173205374722E311B3019060355040A0C1253494E47554C41524C4F47494320532E412E312B3029060355040B0C2246696E616E6369616C204170706C69636174696F6E7320446576656C6F706D656E74311B301906035504030C1253494E47554C41524C4F47494320532E412E30820122300D06092A864886F70D01010105000382010F003082010A02820101009B79C552E4EB634C65F6219F718F9A14B37840765C509FDECAAA639A2FEA4315A3D1CD142270C41FC09D655939541B525A75D0095BE0F9E76405179750900019B78E0AE9D347C7CFB83F44AA6751F955AF423B68183C3BC19DA11D7C207C46CCEDF53CDADF0B417D99B9B7978B9E6FDEC4C19148CF875E5D7B632E252758F4D5E57F4337DA2FB96D4043BB815EF2E84FB6DFA483A83297B2FC95908DEE187FB9A8690B111E8ACCE74107842F810BDA65D506B844C0F4BBAE7673BEA87C2918E9BCD4588ECB8E495FC93DF3072AB87CFE8F9F9C62F91EABD294FD8A5B4D4CA73B693B25859259BA59A3C053F3E01654B62CCC917D9CAF55A08D5254C28E00B0E10203010001A38201B9308201B5301F0603551D230418301680140EE13AA8533A31D58ABEC1BBAD671A0385AD340E301D0603551D0E0416041469096516F8B36CD1C08F858E1B205282469442F9300E0603551D0F0101FF040403020780300C0603551D130101FF0402300030130603551D25040C300A06082B06010505070303301106096086480186F8420101040403020410304A0603551D20044330413035060C2B06010401B23101020103023025302306082B06010505070201161768747470733A2F2F7365637469676F2E636F6D2F4350533008060667810C01040130430603551D1F043C303A3038A036A0348632687474703A2F2F63726C2E7365637469676F2E636F6D2F5365637469676F525341436F64655369676E696E6743412E63726C307306082B0601050507010104673065303E06082B060105050730028632687474703A2F2F6372742E7365637469676F2E636F6D2F5365637469676F525341436F64655369676E696E6743412E637274302306082B060105050730018617687474703A2F2F6F6373702E7365637469676F2E636F6D30270603551D110420301E811C63666D73737570706F72744073696E67756C61726C6F6769632E6575300D06092A864886F70D01010B050003820101003DD3EB349EF5F6F2360C36E23D8C2CDE5DA0E576576765206B1D256CD22B1E81C22E37F968DD93CC9AA3763F55B8B3F16DE635BC22E61235B3AA163401D83D5DE8188E28B4D4C638C50ACAE9B3D48D70099681A8ABB5F27ECEE43461C2E7B92BF98DEAEB04AC155D1267551439523F156B78ADBBA15641C735FD9E7A1580DFA04BC5DB7CF529E33713A34F5E6DF82299E1F272685DBB8C60D61264B50D47F7C0144B059CA9E3B68B69A0562E4530695742DD8B33632320900FDCF040FF4CD24C02E11FC6EEB18681E79E1EE5452C3D053C9261EDF17C4F2A68AAF4AF62D312E707017C28B9CF569DCAD51B569482DD8A3BE71D0FEFAE14D726CE34EADB0A6DAA;');
if not exists(select 1 from sys.server_principals where name = 'SingularLogic2020')
	create login SingularLogic2020 from certificate SingularLogic2020Certificate;
exec('use master; grant unsafe assembly to SingularLogic2020;');

if exists(select 1 from sys.configurations where name = 'show advanced options' and cast(value_in_use as int) = 0)
begin
	exec sp_configure 'show advanced options', '1';
	reconfigure;
end;

if exists (select 1 from sys.configurations where name = 'clr enabled' and cast(value_in_use as int) = 0)
	exec sp_configure 'clr enabled', '1';

if exists (select 1 from sys.configurations where name = 'backup compression default' and cast(value_in_use as int) = 0)
	exec sp_configure 'backup compression default', '1';

if exists (select 1 from sys.configurations where name = 'cost threshold for parallelism' and cast(value_in_use as int) = 5)
	exec sp_configure 'cost threshold for parallelism', '50';

reconfigure;

set @msg = 'Server ' + quotename(@@SERVERNAME) + ' initialized for CFMS.';
raiserror(@msg,0,1) with nowait;

/*********/

if not exists(select 1 from sys.server_principals where name = @user)
begin
	if charindex('\',@user) > 0
		set @sql = 'create login ' + quotename(@user) + ' FROM WINDOWS;';
	else
		set @sql = 'create login ' + quotename(@user) + ' with password = ' + quotename(@user,'''') + ', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;';
	exec(@sql);
end;

if exists (
	select 1
	from sys.server_principals as P
	inner join sys.server_role_members as M on M.member_principal_id = P.principal_id
	inner join sys.server_principals as R on M.role_principal_id = R.principal_id
	where P.name = @user
	and R.name = 'sysadmin'
)
begin
	print 'user '+quotename(@user)+' is a sysadmin and cannot be used for CFMS database access.'
	return
end;

set @sql = 'use master; grant connect sql to '+quotename(@user)+';';
exec(@sql);
set @sql = 'use master; grant view any database to '+quotename(@user)+';';
exec(@sql);
set @sql = 'use master; grant view any definition to '+quotename(@user)+';';
exec(@sql);
set @sql = 'use master; grant view server state to '+quotename(@user)+';';
exec(@sql);
set @sql = 'use master; grant alter trace to '+quotename(@user)+';';
exec(@sql);
set @sql = 'use master; grant external access assembly to '+quotename(@user)+';';
exec(@sql);

set @msg = 'User ' + quotename(@user) + ' initialized for CFMS.';
raiserror(@msg,0,1) with nowait;

/*********/

if db_name() in ('master','msdb','tempdb')
begin
	print 'Please select the CFMS Database and rerun the script.';
	return;
end;

if exists(select 1 from sys.objects where name = 'MDSVer')
begin
	print 'Skipping Database initialization because this is a Omega Database.';
	return;
end;

if cast(DATABASEPROPERTYEX(DB_NAME(),'ComparisonStyle') as int) & 1 = 0
begin
	print 'Database must have a case insensitive (_CI) collation (e.g. Greek_CI_AS)';
	return;
end;

if not exists(select 1 from sys.sysusers where name = @user)
	set @sql = 'create user ' + quotename(@user) + ' for login ' + quotename(@user) + ' with default_schema = dbo;';
else
	set @sql = 'alter user ' + quotename(@user) + ' with login = ' + quotename(@user) + ', default_schema = dbo;';
exec(@sql);

set @sql = 'grant view definition to ' + quotename(@user) + ';';
exec(@sql);
set @sql = 'grant view database state to ' + quotename(@user) + ';';
exec(@sql);
set @sql = 'grant execute to ' + quotename(@user) + ';';
exec(@sql);
set @sql = 'alter role db_datareader add member ' + quotename(@user) + ';';
exec(@sql);
set @sql = 'alter role db_datawriter add member ' + quotename(@user) + ';';
exec(@sql);
set @sql = 'alter role db_ddladmin add member ' + quotename(@user) + ';';
exec(@sql);

if exists (select 1 from sys.databases where database_id = DB_ID() and is_auto_close_on <> 0)
	ALTER DATABASE CURRENT SET AUTO_CLOSE OFF;

if exists (select 1 from sys.databases where database_id = DB_ID() and is_auto_shrink_on <> 0)
	ALTER DATABASE CURRENT SET AUTO_SHRINK OFF;

if exists (select 1 from sys.databases where database_id = DB_ID() and is_auto_create_stats_on <> 1)
	ALTER DATABASE CURRENT SET AUTO_CREATE_STATISTICS ON;

if exists (select 1 from sys.databases where database_id = DB_ID() and is_auto_update_stats_on <> 1)
	ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS ON;

if exists (select 1 from sys.databases where database_id = DB_ID() and page_verify_option <> 2)
	ALTER DATABASE CURRENT SET PAGE_VERIFY CHECKSUM;

if exists (select 1 from sys.databases where database_id = DB_ID() and snapshot_isolation_state <> 1)
begin
	if exists (select 1 from sys.dm_tran_active_snapshot_database_transactions)
		raiserror('There are long running transactions, consider killing them. Waiting while setting snapshot isolation.',0,1) with nowait;
	if exists (select 1 from sys.sysprocesses where dbid = DB_ID() and spid <> @@SPID)
		raiserror('Please close any connections to the database. Waiting while setting snapshot isolation.',0,1) with nowait;
	ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON;
end;

if exists (select 1 from sys.databases where database_id = DB_ID() and is_read_committed_snapshot_on <> 1)
	ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON;

declare @max_level int = CAST(SERVERPROPERTY('ProductMajorVersion') as int) * 10;
if @max_level > 150
	set @max_level = 150;

if (select compatibility_level from sys.databases where database_id = DB_ID()) <> @max_level
begin
	set @sql = 'ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = ' + CAST(@max_level as nvarchar(max));
	exec(@sql);
end;

if exists (select 1 from sys.databases where database_id = DB_ID() and is_broker_enabled <> 1)
	ALTER DATABASE CURRENT SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

if not exists (select 1 from sys.service_message_types where Name = '//ScriptCall/Message')
	CREATE MESSAGE TYPE [//ScriptCall/Message] VALIDATION = WELL_FORMED_XML;

if not exists (select 1 from sys.service_contracts where Name = '//ScriptCall/Contract')
	CREATE CONTRACT [//ScriptCall/Contract] ([//ScriptCall/Message] SENT BY INITIATOR);

if not exists (select 1 from sys.service_queues where Name = 'ScriptCall')
	CREATE QUEUE ScriptCall;

if not exists (select 1 from sys.services where Name = '//ScriptCall/Consumer')
	CREATE SERVICE [//ScriptCall/Consumer] ON QUEUE ScriptCall ([//ScriptCall/Contract]);

if not exists (select 1 from sys.services where Name = '//ScriptCall/Producer')
	CREATE SERVICE [//ScriptCall/Producer] ON QUEUE ScriptCall;

set @sql = 'grant receive on ScriptCall to ' + quotename(@user) + ';';
exec(@sql);

set @msg = 'Database [' + DB_NAME() + '] initialized for CFMS.';
raiserror(@msg,0,1) with nowait;

/*********/

if exists(select 1 from sys.schemas where name = 'clroot')
begin
	print '[===> DROP SCHEMA clroot! Please call cfmssupport@singularlogic.eu <===]';
	return;
end;

/*********/

set @msg = 'Optimizing database...';
raiserror(@msg,0,1) with nowait;

declare @stats as table(cmd nvarchar(max));

SET NOCOUNT ON;
insert into @stats(cmd)
select 'UPDATE STATISTICS ' + quotename(sch.name) + '.' + quotename(obj.name) + ' (' + quotename(stat.name) + ') WITH FULLSCAN;'
from sys.objects as obj
inner join sys.schemas as sch on sch.schema_id = obj.schema_id
inner join sys.stats as stat on stat.object_id = obj.object_id
cross apply sys.dm_db_stats_properties(stat.object_id, stat.stats_id) as p
where obj.type in ('U','S')
and (p.modification_counter * p.modification_counter > 9 * p.rows
	 or p.rows_sampled < 0.9 * p.rows)
order by p.rows desc;

if exists (select 1 from @stats)
begin
	set @msg = (select 'Updating ' + cast(count(*) as nvarchar(max)) + ' statistics...' from @stats);
	raiserror(@msg,0,1) with nowait;

	set @sql = '';
	select @sql += cmd from @stats;
	exec(@sql);
end;

/*********/

if not exists(select 1 from sys.schemas where name = 'clroot')
	print 'DONE. Everything is OK.';
