GO
PRINT N'Creating Table [dbo].[UserPins]...';


GO
CREATE TABLE [dbo].[UserPins] (
    [Id]             INT           IDENTITY (1, 1) NOT NULL,
    [UserName]       VARCHAR (100) NOT NULL,
    [Hash]           VARCHAR (72)  NOT NULL,
    [ExpiryTime]     DATETIME      NOT NULL,
    [FailureCount]   INT           NOT NULL,
    [LockedOutUntil] DATETIME      NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating Primary Key [dbo].[PK_AutoScanConfigs]...';


GO
ALTER TABLE [dbo].[AutoScanConfigs]
    ADD CONSTRAINT [PK_AutoScanConfigs] PRIMARY KEY CLUSTERED ([id] ASC);


GO
PRINT N'Creating Default Constraint unnamed constraint on [dbo].[UserPins]...';


GO
ALTER TABLE [dbo].[UserPins]
    ADD DEFAULT 0 FOR [FailureCount];


GO
PRINT N'Creating View [dbo].[PinConfigurations]...';


GO
CREATE VIEW [dbo].[PinConfigurations]
	AS SELECT top 100 * FROM AutoScanConfigs
	where ConfigName like 'PIN_%'
	order by ConfigName;
GO
PRINT N'Creating Procedure [dbo].[AddDefaultDetectionTypes]...';


GO
CREATE PROCEDURE [dbo].[AddDefaultDetectionTypes]
AS
begin
  if (not exists (select 1 from [dbo].DetectionTypes where id=1) ) 
  insert into [dbo].DetectionTypes(id,DetectionTypeName,Description) 
  values 
   (1,'Barcode','Document is Identified by the barcode and assiged the Coded Label in the Image');
  
  if (not exists (select 1 from [dbo].DetectionTypes where id=2) )
    insert into [dbo].DetectionTypes (id,DetectionTypeName,Description) 
    values (2,'Size','The document is identified on size alone');
  
  if (not exists (select 1 from [dbo].DetectionTypes where id=3) )
    insert into [dbo].DetectionTypes (id,DetectionTypeName,Description) 
    values (3,'Document Map','A map of the diagram is compared to reference maps');
  
  RETURN 0;
end;
GO
PRINT N'Creating Procedure [dbo].[AddDefaultEventTypes]...';


GO
CREATE PROCEDURE [dbo].[AddDefaultEventTypes]
AS
begin

declare @NewTypes Table(
    [EventType]        SMALLINT     NULL,
    [EventCode]        VARCHAR (10) NULL,
    [EventDescription] VARCHAR (50) NULL
)

INSERT INTO @NewTypes
           ([EventType]
           ,[EventCode]
           ,[EventDescription])
     VALUES
           (1,'SCAN','Document has been scanned'),
           (2,'VIEW','Document has been viewed'),
           (3,'DELETE','Document has been deleted'),
           (4,'PRINT','Document has been Printed'),
           (5,'IMPORTED','Document has been imported'),
           (6,'FAXIN','Document has been received by fax'),
           (7,'FIXED','Document Barcode manually assigned');

MERGE INTO [dbo].[EventTypes] AS target
USING (SELECT [EventType], [EventCode], [EventDescription] FROM @NewTypes) AS source ([EventType], [EventCode], [EventDescription])
ON (target.[EventType] = source.[EventType])
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([EventType], [EventCode], [EventDescription])
    VALUES (source.[EventType], source.[EventCode], source.[EventDescription]);


end
GO
PRINT N'Creating Procedure [dbo].[AddDefaultPinConfiguration]...';


GO
CREATE PROCEDURE [dbo].[AddDefaultPinConfiguration]
AS
BEGIN
    -- exec [dbo].[AddDefaultPinConfiguration]
    -- Declare a table variable to hold the config values
    DECLARE @Configurations TABLE (ConfigName VARCHAR(255), ConfigData Varchar(8000));

    -- Insert configurations into the table variable
    INSERT INTO @Configurations (ConfigName, ConfigData) VALUES
    ('PIN_RequirePinSecurity', '1'),
    ('PIN_WorkstationAccessTimeoutSeconds', '90'),
    ('PIN_PinAccessTimeoutMinutes', '120'), 
    ('PIN_PinAccessMaxRetryCount', '5'),
    ('PIN_PinAccessLockoutAfterFailureMinutes', '10'),
    ('PIN_PinLength', '4'),
    ('PIN_Validation', N'^(?!1234)(?!.*(.)\1{#PIN_LEN_-1})[a-zA-Z0-9]{#PIN_LEN}$'),
    ('PIN_ValidationMessage', 
      ' Must be #PIN_LEN Characters long,' + 
      ' Use any Digit or Letter (case insensitive),' +
      ' All the same character (eg 1111) is not allowed,' + 
      ' Some easily guessed pins (eg 1234) are not allowed');

    insert into [dbo].[AutoScanConfigs]
    select * from @Configurations where 
        ConfigName not in (select ConfigName from [dbo].[AutoScanConfigs]);

END
GO
PRINT N'Creating Procedure [dbo].[AddDefaultTaskTypes]...';


GO
CREATE PROCEDURE [dbo].[AddDefaultTaskTypes]
AS
BEGIN
    if (not exists (select 1 from [dbo].TaskTypes where TaskType=1))
	insert into [dbo].TaskTypes (TaskType,TaskCode,TaskDescription) 
	values 
	(1,'DELETE','Delete Image record');

    if (not exists (select 1 from [dbo].TaskTypes where TaskType=2))
	insert into [dbo].TaskTypes (TaskType,TaskCode,TaskDescription) 
	values 
	(2,'ALIAS','Create Alias Record');

    if (not exists (select 1 from [dbo].TaskTypes where TaskType=3))
	insert into [dbo].TaskTypes (TaskType,TaskCode,TaskDescription) 
	values 
	(3,'REFRESHPIN','Clear Cached pins.');

    if (not exists (select 1 from [dbo].TaskTypes where TaskType=4))
	insert into [dbo].TaskTypes (TaskType,TaskCode,TaskDescription) 
	values 
	(4,'REFRESHCFG','Reload Configuration');
	    
RETURN 0;

END;
GO
PRINT N'Creating Procedure [dbo].[ResetAutoscanUsers]...';


GO
CREATE PROCEDURE [dbo].[ResetAutoscanUsers]
AS
begin
BEGIN transaction
delete from [dbo].AutoScanUsersAndDomains where intUserID < 4;
delete from [dbo].AutoscanUsersAndDomainGroups where intUserId < 4;

DBCC CHECKIDENT('AutoScanUsersAndDomains', RESEED, 0);
insert into [dbo].AutoScanUsersAndDomains([Type],DomainName,UserName,Description,PasswordHash) values (0,'AutoSCAN','DefaultScanner','Default Scanning User','');
insert into [dbo].AutoScanUsersAndDomains([Type],DomainName,UserName,Description,PasswordHash) values (0,'AutoSCAN','DefaultImageManager','Default Image Manager','');
insert into [dbo].AutoScanUsersAndDomains([Type],DomainName,UserName,Description,PasswordHash) values (0,'AutoSCAN','DefaultAdmin','Default Admin User','');
DBCC CHECKIDENT('AutoScanUsersAndDomains');

insert into [dbo].AutoscanUsersAndDomainGroups(intUserId,GroupCode) values (0,0);
insert into [dbo].AutoscanUsersAndDomainGroups(intUserId,GroupCode) values (0,1);
insert into [dbo].AutoscanUsersAndDomainGroups(intUserId,GroupCode) values (0,2);
insert into [dbo].AutoscanUsersAndDomainGroups(intUserId,GroupCode) values (0,3);
insert into [dbo].AutoscanUsersAndDomainGroups(intUserId,GroupCode) values (1,0);
insert into [dbo].AutoscanUsersAndDomainGroups(intUserId,GroupCode) values (1,1);
insert into [dbo].AutoscanUsersAndDomainGroups(intUserId,GroupCode) values (1,2);
insert into [dbo].AutoscanUsersAndDomainGroups(intUserId,GroupCode) values (1,3);
insert into [dbo].AutoscanUsersAndDomainGroups(intUserId,GroupCode) values (1,6);
insert into [dbo].AutoscanUsersAndDomainGroups(intUserId,GroupCode) values (2,0);
insert into [dbo].AutoscanUsersAndDomainGroups(intUserId,GroupCode) values (2,1);
insert into [dbo].AutoscanUsersAndDomainGroups(intUserId,GroupCode) values (2,2);
insert into [dbo].AutoscanUsersAndDomainGroups(intUserId,GroupCode) values (2,3);
insert into [dbo].AutoscanUsersAndDomainGroups(intUserId,GroupCode) values (2,4);
insert into [dbo].AutoscanUsersAndDomainGroups(intUserId,GroupCode) values (2,5);
insert into [dbo].AutoscanUsersAndDomainGroups(intUserId,GroupCode) values (2,6);
insert into [dbo].AutoscanUsersAndDomainGroups(intUserId,GroupCode) values (2,7);
Commit;

	
RETURN 0;
end;
GO
PRINT N'Creating Procedure [dbo].[ResetDocumentDetection]...';


GO
CREATE PROCEDURE [dbo].[ResetDocumentDetection]
AS
begin
delete from DocumentDetection where CodeLabel like 'D-%';
DBCC CHECKIDENT('DocumentDetection', RESEED, 0);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-0000C','0C','Type C',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-0003B','3B','Nursing Home Type Patient (Acute Care)',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000PS','PS','Psychiatric',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000RE','RE','Rehabilitation',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000NE','NE','Neonate',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000CC','CC','Critical care',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000AC','AC','Accident',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000HC','HC','Home Care',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000IC','IC','Intensive Care',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000WC','WC','Wound Care',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000SU','SU','Special Unit',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000EL','EL','Election Form',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000PE','PE','PEA Certificate',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000OS','OS','Overseas Certificate',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000NS','NS','Non schedule 5 prosthesis',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000SP','SP','Spinal surgery/neurocomplex',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000LY','LY','Lymphedema',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000DE','DE','Dental',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000MU','MU','Multiple Admission (Chemotherapy and Dialysis)',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000DO','DO','Day only for Dialysis',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000RD','RD','Rehabilitation Discharge/Separation',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000AD','AD','Ambulatory Discharge/Separation',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000ED','ED','Early Discharge/Separation',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000EX','EX','Expected Date of confinement',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000EC','EC','Emergency Caesar',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000FL','FL','Failed Labour',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000MB','MB','Mother and Baby',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000OO','OO','Obstetric Outlier',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000AE','AE','Antenatal Education',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000OU','OU','Outlier',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000DR','DR','Exceptional Drug (non HPPA medication)',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-000OT','OT','Other',1,1,99,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-MP0OT','MC','Miscellaneous ',1,1,99,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-ML0OT','MC','Miscellaneous ',1,2,99,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-ML1OT','CF','Claim Form',1,2,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-MP0EL','CE','Continuous Election',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-MP2OT','PR','Prosthetic Supply',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-ML2OT','PR','Prosthetic Supply',1,2,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-0000B','0B','Type B',1,1,1,NULL);
insert into DocumentDetection (CodeLabel,FileCode,Description,DetectionMethod,ViewOrientation,PageLimit,ReferenceMap) values ('D-MP3OT','CP','Transition Care Program Agreement',1,1,3,NULL);
RETURN 0

end;
GO

PRINT N'Updating Types';
exec [dbo].[AddDefaultTaskTypes];
exec [dbo].[AddDefaultEventTypes];
GO

PRINT N'Adding Default Pin Configuration';
exec [dbo].[AddDefaultPinConfiguration]


PRINT N'Update complete.';
GO
