/*
create database admin
exec msdb.dbo.sp_add_category 'Job', 'Local', 'Change Control'
*/

-- must have dbo authority
set nocount on
use Admin
go

-- clean up any old tables
if (objectproperty(object_id('Admin.dbo.ActivityLog'),'IsUserTable') = 1)
        drop table dbo.ActivityLog
if (objectproperty(object_id('Admin.dbo.ProjectNode'),'IsUserTable') = 1)
        drop table Admin.dbo.ProjectNode
if (objectproperty(object_id('Admin.dbo.SqlDatabase'),'IsUserTable') = 1)
        drop table dbo.SqlDatabase
if (objectproperty(object_id('Admin.dbo.SqlServer'),'IsUserTable') = 1)
        drop table Admin.dbo.SqlServer
if (objectproperty(object_id('Admin.dbo.Environment'),'IsUserTable') = 1)
        drop table Admin.dbo.Environment
if (objectproperty(object_id('Admin.dbo.Project'),'IsUserTable') = 1)
        drop table Admin.dbo.Project
go

-- create tables
create table Admin.dbo.Project
	(id int identity(1,1) not null,
      projectName varchar(30) not null,
      vssShare varchar(255) null,
      vssHierarchyRoot varchar(255) null,
      createDate datetime not null
      	constraint dft_Project__createDate default current_timestamp,
      createUser varchar(50) not null
            constraint dft_Project__createUser default system_user,
      lastUpdateDate datetime null,
      lastUpdateUser varchar(50) null,
      constraint pkn_Project__id primary key nonclustered (id),
	constraint ukn_Project unique nonclustered (projectName),
	constraint chk_Project__vssShare 
		check (substring(vssShare,1,2) = '\\'
			and substring(vssShare,datalength(vssShare),1) = '\' ),
	constraint chk_Project__vssHierarchyRoot 
		check (substring(vssHierarchyRoot,1,2) = '$/'
			and substring(vssHierarchyRoot,datalength(vssHierarchyRoot),1) = '/'),
      constraint chk_Project__createUser check (charindex('\',createUser) > 0),
      constraint chk_Project__lastUpdateUser check (charindex('\',lastUpdateUser) > 0))

create table Admin.dbo.Environment
      (id int identity(1,1) not null,
      parentEnvironment_id int,
      environmentName varchar(30) not null,
      environmentDescription varchar(200) not null
      	constraint dft_Environment__environmentDescription default ('not provided'),
      createDate datetime not null
            constraint dft_Environment__createDate default current_timestamp,
      createUser varchar(50)not null
            constraint dft_Environment__createUser default system_user,
      lastUpdateDate datetime null,
      lastUpdateUser varchar(50) null,
      constraint pkn_Environment__id primary key nonclustered (id),
	constraint ukn_Environment__environmentName unique nonclustered (environmentName),
      constraint fk_Environment__parentEnvironment_id__to__Environment__id
      	foreign key (parentEnvironment_id) references Environment (id),
      constraint chk_Environment__environmentName check (charindex(' ',environmentName) = 0),
      constraint chk_Environment__createUser check (charindex('\',createUser) > 0),
      constraint chk_Environment__lastUpdateUser check (charindex('\',lastUpdateUser) > 0))

-- instance server name for SQL 2000 named instance compatability
create table Admin.dbo.SqlServer
      (id int identity(1,1) not null,
      serverName varchar(30) not null,
      instanceName varchar(30) not null,
      workingFolderRoot varchar(100) not null,        
      createDate datetime not null
      	constraint dft_SqlServer__createDate default current_timestamp,
      createUser varchar(50) not null
            constraint dft_SqlServer__createUser default system_user,
      lastUpdateDate datetime null,
      lastUpdateUser varchar(50) null,
      constraint pkn_SqlServer__id primary key nonclustered (id),
	constraint ukn_SqlServer unique nonclustered (serverName, instanceName),
	constraint chk_SqlServer__workingFolderRoot 
		check (substring(workingFolderRoot,datalength(workingFolderRoot),1) = '\'),
      constraint chk_SqlServer__createUser check (charindex('\',createUser) > 0),
      constraint chk_SqlServer__lastUpdateUser check (charindex('\',lastUpdateUser) > 0))

create table Admin.dbo.SqlDatabase
      (id int identity(1,1) not null,
      databaseName varchar(30) not null,
      createDate datetime not null
      	constraint dft_SqlDatabase__createDate default current_timestamp,
      createUser varchar(50) not null
            constraint dft_SqlDatabase__createUser default system_user,
      lastUpdateDate datetime null,
      lastUpdateUser varchar(50) null,
      constraint pkn_SqlDatabase__id primary key nonclustered (id),
	constraint ukn_SqlDatabase__databaseName unique nonclustered (databaseName),
      constraint chk_sqlDatabase__createUser check (charindex('\',createUser) > 0),
      constraint chk_sqlDatabase__lastUpdateUser check (charindex('\',lastUpdateUser) > 0))

create table Admin.dbo.ProjectNode
      (id int identity(1,1) not null,
      project_id int not null,
      environment_id int not null,
      sqlServer_id int not null,
      sqlDatabase_id int not null,
      vssUserName varchar(30) not null
      	constraint dft_ProjectNode__vssUserName 
			default substring(system_user,charindex('\', system_user) + 1, datalength(system_user)),
      isActive bit not null
            constraint dft_ProjectNode__isActive default (1),
      activeDate datetime not null
            constraint dft_ProjectNode__activeDate default current_timestamp,
      endDate datetime null,
      createDate datetime not null
            constraint dft_ProjectNode__createDate default current_timestamp,
      createUser varchar(50) not null
            constraint dft_ProjectNode__createUser default system_user,
      lastUpdateDate datetime null,
      lastUpdateUser varchar(50) null,
      constraint pkn_ProjectNode__id primary key nonclustered (id),
      constraint fk_ProjectNode__project_id__to__Project__id 
      	foreign key (project_id) references Project (id),
      constraint fk_ProjectNode__environment_id__to__Environment__id 
            foreign key (environment_id) references Environment (id),
      constraint fk_ProjectNode__sqlServer_id__to__SqlServer__id 
            foreign key (sqlServer_id) references sqlServer (id),
      constraint fk_ProjectNode__sqlDatabase_id__to__SqlDatabase__id 
            foreign key (sqlDatabase_id) references SqlDatabase (id),
      constraint ukn_ProjectNode__sqlServer_id__sqlDatabase_id 
	    unique nonclustered (sqlServer_id, sqlDatabase_id),
      constraint chk_ProjectNode__isActive check (isActive in(0,1)),
      constraint chk_ProjectNode__createUser check (charindex('\',createUser) > 0),
      constraint chk_ProjectNode__lastUpdateUser check (charindex('\',lastUpdateUser) > 0))

create table Admin.dbo.ActivityLog
      (id int identity(1,1) not null,
      activity varchar(2000) not null,
      activityDate datetime not null
      	constraint dft_ActivityLog__activityDate default current_timestamp,
      activityUser varchar(50) not null
            constraint dft_ActivityLog__activityUser default system_user,
      constraint pkn_ActivityLog__id primary key nonclustered (id),
      constraint chk_ActivityLog__activityUser check (charindex('\',activityUser) > 0))
go

-- create an index on ProjectNode database_id
-- don't really need it, just want an index for scripting to verify the dmo procedures
create nonclustered index ixc_ProjectNode__sqlDatabase_id 
on ProjectNode(sqlDatabase_id) 

go

--create triggers
create trigger dbo.itr_Project on Admin.dbo.Project for insert
as
if @@rowcount > 0
        begin
		-- audit the insert
		insert Admin.dbo.ActivityLog 
			(activity)
		select 'Project ' + i.projectName + ' inserted'
		from inserted i
		inner join Admin.dbo.Project p
		on i.id = p.id				
        end

go 

create trigger dbo.utr_Project on Admin.dbo.Project for update
as
if @@rowcount > 0
        begin
                update Admin.dbo.Project
			set	lastUpdateDate = current_timestamp,
                    		lastUpdateUser = system_user
                from inserted i
                inner join Admin.dbo.Project p
                on i.id = p.id    

		    -- audit the update
		    insert Admin.dbo.ActivityLog 
			    (activity)
		    select 'Project updated:' + 
				case when d.projectName <> i.projectName 
					then ' projectName: ' + d.projectName + ' --> ' + i.projectName 
					else ' '
					end +
				case when d.vssHierarchyRoot <> i.vssHierarchyRoot
					then ' vssHierarchyRoot: ' + d.vssHierarchyRoot + ' --> ' + i.vssHierarchyRoot 
					else ' '
					end +
				case when d.vssShare <> i.vssShare
					then ' vssShare: ' + d.vssShare + ' --> ' + i.vssShare
					else ' '
					end +
				' '
		    from deleted d
		    inner join inserted i
		    on d.id = i.id	
		    inner join Admin.dbo.Project p
		    on d.id = p.id				
        end

go

create trigger dbo.dtr_Project on Admin.dbo.Project for delete
as
if @@rowcount > 0
        begin
		-- audit the delete
		insert Admin.dbo.ActivityLog 
			(activity)
		select 'Project ' + d.projectName + ' deleted'
		from deleted d
		inner join Admin.dbo.Project p
		on d.id = p.id				
        end

go 

create trigger dbo.itr_Environment on Admin.dbo.Environment for insert
as
if @@rowcount > 0
        begin
		-- audit
		insert Admin.dbo.ActivityLog 
			(activity)
		select 'Environment ' + i.environmentName + ' inserted'
		from inserted i
		inner join Admin.dbo.Environment e
		on i.id = e.id				
        end

go 

create trigger dbo.utr_Environment on Admin.dbo.Environment for update
as
if @@rowcount > 0
        begin
                update Admin.dbo.Environment
			set 	lastUpdateDate = current_timestamp,
                    		lastUpdateUser = system_user
                from inserted i
                inner join Admin.dbo.Environment e
                on i.id = e.id    
		-- audit

		insert Admin.dbo.ActivityLog 
			(activity)
		select 'Environment ' + i.environmentName + ' updated'
		from inserted i
		inner join Admin.dbo.Environment e
		on i.id = e.id				
        end
go

create trigger dbo.dtr_Environment on Admin.dbo.Environment for delete
as
if @@rowcount > 0
        begin
		-- audit
		insert Admin.dbo.ActivityLog 
			(activity)
		select 'Environment ' + d.environmentName + ' deleted'
		from deleted d
		inner join Admin.dbo.Environment e
		on d.id = e.id				
        end

go 

create trigger dbo.itr_SqlServer on Admin.dbo.SqlServer for insert
as
if @@rowcount > 0
        begin
		-- audit
		insert Admin.dbo.ActivityLog 
			(activity)
		select 'SqlServer ' + i.serverName + '\' + i.instanceName + ' inserted'
		from inserted i
		inner join Admin.dbo.SqlServer s
		on i.id = s.id				
        end

go 

create trigger dbo.utr_SqlServer on Admin.dbo.SqlServer for update
as
if @@rowcount > 0
        begin
                update Admin.dbo.SqlServer
			set 	lastUpdateDate = current_timestamp,
                    		lastUpdateUser = system_user
                from inserted i
                inner join Admin.dbo.SqlServer ss
                on i.id = ss.id    

		-- audit
		insert Admin.dbo.ActivityLog 
			(activity)
		select 'SqlServer ' + i.serverName + '\' + i.instanceName +  ' updated'
		from inserted i
		inner join Admin.dbo.SqlServer s
		on i.id = s.id				
        end
go

create trigger dbo.dr_SqlServer on Admin.dbo.SqlServer for delete
as
if @@rowcount > 0
        begin
		-- audit
		insert Admin.dbo.ActivityLog 
			(activity)
		select 'SqlServer ' + d.serverName + '\' + d.instanceName + ' deleted'
		from deleted d
		inner join Admin.dbo.SqlServer s
		on d.id = s.id				
        end

go 

create trigger dbo.itr_SqlDatabase on Admin.dbo.SqlDatabase for insert
as
if @@rowcount > 0
        begin
		-- audit
		insert Admin.dbo.ActivityLog 
			(activity)
		select 'SqlDatabase ' + i.databaseName + ' inserted'
		from inserted i
		inner join Admin.dbo.SqlDatabase d
		on i.id = d.id				
        end

go 

create trigger dbo.utr_SqlDatabase on Admin.dbo.SqlDatabase for update
as
if @@rowcount > 0
        begin
                update Admin.dbo.SqlDatabase
			set 	lastUpdateDate = current_timestamp,
                    		lastUpdateUser = system_user
                from inserted i
                inner join Admin.dbo.SqlDatabase db
                on i.id = db.id    

		-- audit
		insert Admin.dbo.ActivityLog 
			(activity)
		select 'SqlDatabase ' + d.databaseName + ' updated'
		from deleted d
		inner join Admin.dbo.SqlDatabase db
		on d.id = db.id				
        end

go

create trigger dbo.dtr_SqlDatabase on Admin.dbo.SqlDatabase for delete
as
if @@rowcount > 0
        begin
		-- audit the insert
		insert Admin.dbo.ActivityLog 
			(activity)
		select 'SqlDatabase ' + d.databaseName + ' deleted'
		from deleted d
		inner join Admin.dbo.SqlDatabase db
		on d.id = db.id				
        end

go 

create trigger dbo.itr_ProjectNode on Admin.dbo.ProjectNode for insert
as
if @@rowcount > 0
        begin
		-- audit the insert
		insert Admin.dbo.ActivityLog 
			(activity)
		select  'ProjectNode ' + cast(n.id as varchar(10))+ ' inserted'
		from inserted i
		inner join Admin.dbo.ProjectNode n
		on i.id = n.id				
        end

go

create trigger dbo.utr_ProjectNode on Admin.dbo.ProjectNode for update
as
if @@rowcount > 0
        begin
                update Admin.dbo.ProjectNode
                set lastUpdateDate = current_timestamp,
                    lastUpdateUser = system_user,
                    activeDate = case when (i.isActive = 1 and d.isActive = 0)
                                        then current_timestamp  
                                        else d.activeDate
                                        end,
                    endDate = case when (i.isActive = 0 and d.isActive = 1)
                                then current_timestamp
                                else d.endDate
                                end
                from inserted i
                inner join deleted d
                on i.id = d.id    
                inner join Admin.dbo.ProjectNode db
                on i.id = db.id    

		-- audit the update 
		insert Admin.dbo.ActivityLog 
			(activity)
		select 'ProjectNode updated:
			id = ' + cast(n.id as varchar(10)) + '
					old value --> new value
			environment_id: ' + cast(d.environment_id as varchar(10)) +
				' --> ' + cast(i.environment_id as varchar(10)) + '
			sqlServer_id: ' + cast(d.sqlServer_id as varchar(10)) +
				' --> ' + cast(i.sqlServer_id as varchar(10)) + '
			sqlDatabase_id: ' + cast(d.sqlDatabase_id as varchar(10)) +
				' --> ' + cast(i.sqlDatabase_id as varchar(10)) + '
        		vssUserName: ' + d.vssUserName + ' --> ' + i.vssUserName +'
			isActive: ' + cast(d.isActive as char(1)) + 
				' --> ' + cast(i.isActive as char(1)) + '
			activeDate: ' + cast(d.activeDate as varchar(21)) +
				' --> ' + cast(i.activeDate as varchar(21)) + '
			endDate: ' + isnull(cast(d.endDate as varchar(21)),'null') +
				' --> ' + isnull(cast(i.endDate as varchar(21)),'null') + '
			createDate: ' + cast(d.createDate as varchar(21)) +
				' --> ' + cast(i.createDate as varchar(21)) + '
			createUser: ' + d.createUser + ' --> ' + i.createUser + '
			lastUpdateDate: ' + isnull(cast(d.lastUpdateDate as varchar(21)),'null') +
				' --> ' + isnull(cast(i.lastUpdateDate as varchar(21)),'null') + '
        		lastUpdateUser: ' + isnull(d.lastUpdateUser,'null') + ' --> ' + isnull(i.lastUpdateUser,'null')
		from deleted d
		inner join inserted i
		on d.id = i.id
		inner join Admin.dbo.ProjectNode n
		on d.id = n.id				
        end

go

create trigger dbo.dtr_ProjectNode on Admin.dbo.ProjectNode for delete
as
if @@rowcount > 0
        begin
		-- audit the insert
		insert Admin.dbo.ActivityLog 
			(activity)
		select  'ProjectNode ' + cast(n.id as varchar(10)) + ' deleted'
		from deleted d
		inner join Admin.dbo.ProjectNode n
		on d.id = n.id				
        end

go

/* no instead of triggers in 7

-- log updates not permitted
create trigger dbo.utr_ActivityLog on Admin.dbo.ActivityLog instead of update
as
if @@rowcount > 0
        begin
                insert Admin.dbo.ActivityLog 
				(activity)
		select 'Attempt to update activityLog activity_id = ' 
				+ cast(i.id as varchar(10)) + ' to 
			activity = ' + i.activity + '
			activityDate = ' + cast(i.ActivityDate as varchar(21)) + '
			activityUser = ' + i.ActivityUser			  
                from deleted d
		inner join inserted i
		on d.id = i.id
                inner join Admin.dbo.ActivityLog a
                on d.id = a.id
        end

-- log deletes not permitted
create trigger dbo.dtr_ActivityLog on Admin.dbo.ActivityLog instead of delete
as
if @@rowcount > 0
        begin
                insert Admin.dbo.ActivityLog 
				(activity)
		select 'Attempt to delete activityLog activity_id = ' 
				+ cast(d.id as varchar(10)) 
                from deleted d
                inner join Admin.dbo.ActivityLog a
                on d.id = a.id
        end

*/

-- log updates not permitted 
create trigger dbo.utr_ActivityLog on Admin.dbo.ActivityLog for update
as
if @@rowcount > 0
        begin				
                insert Admin.dbo.ActivityLog 
				(activity)
		select 'Attempt to update activityLog activity_id = ' 
				+ cast(i.id as varchar(10)) + ' to 
			activity = ' + i.activity + '
			activityDate = ' + cast(i.ActivityDate as varchar(21)) + '
			activityUser = ' + i.ActivityUser			  
                from deleted d
		inner join inserted i
		on d.id = i.id
                inner join Admin.dbo.ActivityLog a
                on d.id = a.id

		-- this is not needed in the instead of triggger
		update Admin.dbo.ActivityLog
		set 	activity = d.activity,
			activityDate = d.activityDate,
			activityUSer = d.activityUser		
                from deleted d
		inner join inserted i
		on d.id = i.id
                inner join Admin.dbo.ActivityLog a
                on d.id = a.id
        end

go

-- log deletes not permitted
create trigger dbo.dtr_ActivityLog on Admin.dbo.ActivityLog for delete
as
if @@rowcount > 0
        begin
                insert Admin.dbo.ActivityLog 
				(activity)
		select 'Attempt to delete activityLog activity_id = ' 
				+ cast(d.id as varchar(10)) 
                from deleted d
                inner join Admin.dbo.ActivityLog a
                on d.id = a.id

		-- this is not needed in the instead of triggger
		update Admin.dbo.ActivityLog
		set 	activity = d.activity,
			activityDate = d.activityDate,
			activityUSer = d.activityUser		
                from deleted d
		inner join inserted i
		on d.id = i.id
                inner join Admin.dbo.ActivityLog a
                on d.id = a.id
        end

go

-- functions 
/*no functions in 7


if objectproperty(object_id('dbo.thisSqlServer_id'),'IsInlineFunction') is not null
	drop function dbo.thisSqlServer_id

create function thisSqlServer_id ()
returns int
as
begin
	-- get current system's id from table SqlServer
	declare @thisSqlServer_id int 
	select @thisSqlServer_id = (select id from Admin.dbo.SqlServer
        	                    	where serverName = reverse(substring(reverse(@@servername), 
                	                                            charindex('\',reverse(@@servername)) + 1, 
                        	                                    datalength(reverse(@@servername))))
					and instanceName = substring(@@servername, 
        	                               	                    charindex('\',@@servername) + 1, 
                                                	            datalength(@@servername)))
	return (@thisSqlServer_id)
end

if objectproperty(object_id('dbo.binToHex'),'IsInlineFunction') is not null
	drop function dbo.binToHex

create function dbo.binToHex (@binaryValue varbinary(255))
returns varchar(255)
as
begin

declare @binToHex varchar(255),
	@curPosition int,
	@hexString char(16),
	@tempInt int,
	@firstInt int,
	@secondInt int

set @binToHex = '0x'
set @curPosition = 1
set @hexString = '0123456789abcdef'
while (@curPosition <= datalength(@binaryValue))
	begin
		select @tempInt = cast(substring(@binaryValue, @curPosition, 1) as int)
		select @firstint = floor(@tempInt/16)
		select @secondint = @tempInt - (@firstInt * 16)
		select @binToHex = @binToHex +
			substring(@hexString, @firstInt + 1, 1) +
			substring(@hexString, @secondInt + 1, 1)
		select @curPosition = @curPosition + 1
	end

return (@binToHex)

end

*/
go
