create procedure dbo.init_security
(
	@Error int = null output,
	@Message nvarchar(2048) = null output
) as

/*
Initialises security-related lookup tables.



20200616, RFW - Reworked access level types, only OWNER is left
	It is unclear how we can get them, and from where. GP doesn't seem to distinguish between them.
20200605, RFW - dbo.AccessFlags renamed to dbo.AccessTypes
	and is now referenced by dbo.AccessLevels. Bit masks are gove, replaced with FK
20200603, RFW - initial release
*/

set nocount, quoted_identifier, ansi_nulls, ansi_warnings, ansi_padding, concat_null_yields_null, arithabort, xact_abort on;
set implicit_transactions, numeric_roundabort off;


declare @XTran bit = cast(sign(@@trancount) as bit);

begin try

if @XTran = 0
	begin tran;


-- Securable types
declare @SecTypes table (
  Code varchar(50) primary key,
  Title varchar(50) not null
);

insert into @SecTypes (Code, Title)
values
  ('EXECUTIVE', 'Executive Group'),
  ('PROGRAM', 'Program'),
  ('BU', 'Business Unit'),
  ('CC', 'Cost Centre'),
  ('CC_SPF', 'Cost Centre (SPF)'),
  ('ACCOUNT', 'Account');


-- IsActive is default on insert, and unchanged on update
merge dbo.SecurableTypes t
using @SecTypes s
on s.Code = t.Code
when not matched by target then
	insert (Code, [Title])
	values (s.Code, s.[Title])
when matched then
	update set Title = s.Title;


-- Access types
declare @af table (
	Code varchar(50) primary key,
	Title varchar(100) not null
);

insert into @af (Code, Title)
values
	('OWNER', 'Manager');
	--('APPROVER_SECONDARY', 'Secondary approver'),
	--('REVIEWER', 'Reviewer');


merge into dbo.AccessTypes t
using @af s on s.Code = t.Code
when not matched by target then
	insert ([code], [Title])
	values ([s].[code], [s].[Title])
when matched then
	update set Title = s.Title;



if @XTran = 0
	commit;

end try
begin catch

if nullif(@Error, 0) is null
	select @Error = error_number(), @Message = error_message();

if @@trancount > 0 and @XTran = 0
	rollback;

end catch;
return;
go