/* This script has been tested on SQL Server 2016 */

use [emdash]

declare @AppName varchar(25) 
declare @Today as date
declare @SQLServerInstance varchar(30)
declare @SQLVersion varchar(20)
declare @EnvOwner varchar(30)

 /* e.g. gets todays date*/
set @Today = getdate() 

/* Name of the custom Application to be defined. For Example: PI for PriceImporter */
set @AppName='Enter YOUR Custom Application Name'  

/* For Example : "ACME-SQL2016-01" for default SQL Server instance or "ACME-SQL2016-01\ENDUR" for the ENDUR SQL Server Instance */
set @SQLServerInstance = 'Enter YOUR SQL Server Instance' 

/* For Example : SQL 2008R2 or SQL 2012 or SQL 2016 or Oracle 11g or Oracle 12c */
set @SQLVersion = 'Enter YOUR SQL Server Version' 

/* For Example: "Sajjad Ahmed" - User should be in list of users in emdash*/
set @EnvOwner = 'Enter YOUR Environment Owner' 

/********************************************************************************************************************************/

/* Creates APPLICATION TYPE record for the new TEST environment */
insert into applicationtype values (@AppName)

/* Creates APPLICATION record for the new TEST environment */
insert into Application values (@AppName,@AppName+' Description',@AppName+' Application Type',null,'NA','NA','NA')

/* Creates DATABASE TYPE record for the new TEST environment */
insert into DatabaseType values (@AppName)

/* Creates CodeVersion record for the new TEST environment */
insert into CodeVersion values (@AppName+'-Initial',@Today,null,(Select applicationId from Application where name = @AppName),@AppName)

/* Creates DATABASE record for the new TEST environment */
insert into [database] values (@AppName+'_TEST',@SqlServerInstance,@AppName,'SQL Server',@SQLVersion,null)

/* Creates Enviroment record for the new TEST environment */
insert into [environment] values (@AppName+'_TEST',@EnvOwner,'NA',@AppName+'_TEST',@AppName+'_TEST',null,'AD',0,'OTHER',null,null,null,4,1,0x)

/* Creates Env Wide Settings for the new Custom Application */
insert into [EnvWideSettingsOptions] values ('CustomSQLFileAfter'+@AppName+'Refresh','TEMPLATE_CUSTOM_SQL_AFTER_'+@AppName+'_REFRESH','NA',DEFAULT,'Custom SQL File After '+@AppName+' Refresh',null,null,'String')
insert into [EnvWideSettingsOptions] values ('Override'+@AppName+'BackupPath','TEMPLATE_OVERRIDE_'+@AppName+'_BACKUP_PATH','NA',DEFAULT,'Override '+@AppName+' Backup Path',null,null,'String')

/* Creates ENVIRONMENT TYPE "Other" - If this exist then this line will give a duplicate error which can be ignored*/
insert into environmentType values ('OTHER','OTHER',99,null)

/* Update platformid to 4 i.e. as "Other" for custom environments created */
update [environment] set platformid = 4 where [Name] like @AppName+'%'