#---------------------------------------------------------------------
# Create or recreate public schema model tables 'Gazelle Master Model'
# Note: drop and create order takes keys into account, avoids cascade
#---------------------------------------------------------------------

#-------------------------------------- drop existing tables and types
DROP TABLE IF EXISTS typeConfiguration;
DROP TABLE IF EXISTS typeActor;
DROP TABLE IF EXISTS typeIntegrationProfile;
DROP TABLE IF EXISTS typeTransaction;

DROP TYPE  IF EXISTS typeInterface;
DROP TYPE  IF EXISTS typeParticipant;
DROP TYPE  IF EXISTS typeMandatory;
DROP TYPE  IF EXISTS typeUserRole;

#---------------------------------------------------- Create new types
CREATE TYPE typeInterface   AS ENUM ('DICOM', 'HL7V2', 'HL7V3');
CREATE TYPE typeParticipant AS ENUM ('INITIATOR', 'RESPONDER');
CREATE TYPE typeMandatory   AS ENUM ('REQUIRED', 'OPTIONAL');
CREATE TYPE typeUserRole    AS ENUM ('USER', 'ADMIN', 'INSTITUTION_ADMIN');

CREATE TABLE typeActor (
   code VARCHAR(16) PRIMARY KEY,
   description VARCHAR(255) NOT NULL DEFAULT ''
);
INSERT INTO typeActor VALUES ('ADT',    'Patient Registration');
INSERT INTO typeActor VALUES ('OP',     'Order Placer');
INSERT INTO typeActor VALUES ('OF',     'Department System Scheduler/Order Filler');
INSERT INTO typeActor VALUES ('MOD',    'Aquisition Modality');
INSERT INTO typeActor VALUES ('IM',     'Image Manager/Image Archive');
INSERT INTO typeActor VALUES ('PPSM',   'Performed Procedure Step Manager');
INSERT INTO typeActor VALUES ('EC',     'Evidence Creator');
INSERT INTO typeActor VALUES ('ID',     'Image Display');

CREATE TABLE typeIntegrationProfile (
   code VARCHAR(16) PRIMARY KEY,
   description VARCHAR(255) NOT NULL DEFAULT ' '
);
INSERT INTO typeIntegrationProfile VALUES ('SWF', 'Structured Work Flow');

CREATE TABLE typeTransaction (
   code VARCHAR(16) PRIMARY KEY,
   description VARCHAR(255) NOT NULL DEFAULT '',
   interface typeInterface NOT NULL DEFAULT 'HL7V2'
);
INSERT INTO typeTransaction VALUES ('RAD_1',  'Patient Registration', 'HL7V2');
INSERT INTO typeTransaction VALUES ('RAD_2',  'Placer Order Management', 'HL7V2');
INSERT INTO typeTransaction VALUES ('RAD_3',  'Filler Order Management', 'HL7V2');
INSERT INTO typeTransaction VALUES ('RAD_4',  'Procedure Scheduled', 'HL7V2');
INSERT INTO typeTransaction VALUES ('RAD_5',  'Query Modality Worklist', 'DICOM');
INSERT INTO typeTransaction VALUES ('RAD_6',  'Modality Procedure Step In Progress', 'DICOM');
INSERT INTO typeTransaction VALUES ('RAD_7',  'Modality Procedure Step Completed/Discontinued', 'DICOM');
INSERT INTO typeTransaction VALUES ('RAD_8',  'Modality Images Stored', 'DICOM');
INSERT INTO typeTransaction VALUES ('RAD_10', 'Storage Commitment', 'DICOM');
INSERT INTO typeTransaction VALUES ('RAD_11', 'Image Availability Query', 'DICOM');
INSERT INTO typeTransaction VALUES ('RAD_12', 'Patient Update', 'HL7V2');
INSERT INTO typeTransaction VALUES ('RAD_13', 'Procedure Update', 'HL7V2');
INSERT INTO typeTransaction VALUES ('RAD_14', 'Query Images', 'DICOM');
INSERT INTO typeTransaction VALUES ('RAD_16', 'Retrieve Imagess', 'DICOM');
INSERT INTO typeTransaction VALUES ('RAD_18', 'Creator Images Stored', 'DICOM');
INSERT INTO typeTransaction VALUES ('RAD_20', 'Creator Procedure Step In Progress', 'DICOM');
INSERT INTO typeTransaction VALUES ('RAD_21', 'Creator Procedure Step Completed', 'DICOM');
INSERT INTO typeTransaction VALUES ('RAD_42', 'Performed Work Status Update', 'DICOM');
INSERT INTO typeTransaction VALUES ('RAD_48', 'Appointment Notification', 'HL7V2');
INSERT INTO typeTransaction VALUES ('RAD_49', 'Instance Availability Notification', 'HL7V2');

CREATE TABLE typeConfiguration (
   id INTEGER CONSTRAINT positive_id CHECK (id > 0) PRIMARY KEY,
   integrationProfile VARCHAR(16) NOT NULL REFERENCES typeIntegrationProfile,
   actor VARCHAR(16)    NOT NULL REFERENCES typeActor,
   partner VARCHAR(16)  NOT NULL REFERENCES typeActor,
   transact VARCHAR(16) NOT NULL REFERENCES typeTransaction,
   participant typeParticipant NOT NULL,
   mandatory typeMandatory NOT NULL,
   UNIQUE (integrationProfile, actor, partner, transact)
);
#-------------------------------------------------------------------------------
# NOTE: the id field is used as an array subscript internally. The SQL checks
# to insure that all values are positive and unique.  New insert SQL's can be
# put in any order, but need to follow the positive and unique rule, and
# hopefully not have many gaps.
#-------------------------------------------------------------------------------
#--------------------------------------          SWF - Structured work flow

#--------------------------------------          ADT - Patient Registration
INSERT INTO typeConfiguration VALUES ( 1, 'SWF', 'ADT',  'OF',   'RAD_1',  'INITIATOR', 'REQUIRED');
INSERT INTO typeConfiguration VALUES ( 2, 'SWF', 'ADT',  'OF',   'RAD_12', 'INITIATOR', 'REQUIRED');
INSERT INTO typeConfiguration VALUES ( 3, 'SWF', 'ADT',  'OP',   'RAD_1',  'INITIATOR', 'REQUIRED');
INSERT INTO typeConfiguration VALUES ( 4, 'SWF', 'ADT',  'OP',   'RAD_12', 'INITIATOR', 'REQUIRED');
#--------------------------------------          OP - Order Placer
INSERT INTO typeConfiguration VALUES ( 5, 'SWF', 'OP',   'ADT',  'RAD_1',  'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES ( 6, 'SWF', 'OP',   'ADT',  'RAD_12', 'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES ( 7, 'SWF', 'OP',   'OF',   'RAD_2',  'INITIATOR', 'REQUIRED');
INSERT INTO typeConfiguration VALUES ( 8, 'SWF', 'OP',   'OF',   'RAD_3',  'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES ( 9, 'SWF', 'OP',   'OF',   'RAD_48', 'RESPONDER', 'OPTIONAL');
#--------------------------------------          OF - Department System Scheduler / Order Filler
INSERT INTO typeConfiguration VALUES (10, 'SWF', 'OF',   'ADT',  'RAD_1',  'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (11, 'SWF', 'OF',   'ADT',  'RAD_12', 'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (12, 'SWF', 'OF',   'OP',   'RAD_2',  'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (13, 'SWF', 'OF',   'OP',   'RAD_3',  'INITIATOR', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (14, 'SWF', 'OF',   'IM',   'RAD_4',  'INITIATOR', 'OPTIONAL');
INSERT INTO typeConfiguration VALUES (15, 'SWF', 'OF',   'MOD',  'RAD_5',  'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (16, 'SWF', 'OF',   'MOD',  'RAD_6',  'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (17, 'SWF', 'OF',   'MOD',  'RAD_7',  'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (18, 'SWF', 'OF',   'IM',   'RAD_11', 'RESPONDER', 'OPTIONAL');
INSERT INTO typeConfiguration VALUES (19, 'SWF', 'OF',   'IM',   'RAD_13', 'INITIATOR', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (20, 'SWF', 'OF',   'PPSM', 'RAD_20', 'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (21, 'SWF', 'OF',   'PPSM', 'RAD_21', 'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (22, 'SWF', 'OF',   'IM',   'RAD_42', 'RESPONDER', 'OPTIONAL');
INSERT INTO typeConfiguration VALUES (23, 'SWF', 'OF',   'OP',   'RAD_48', 'INITIATOR', 'OPTIONAL');
INSERT INTO typeConfiguration VALUES (24, 'SWF', 'OF',   'IM',   'RAD_49', 'RESPONDER', 'OPTIONAL');
#--------------------------------------          MOD - Acquisition Modality
INSERT INTO typeConfiguration VALUES (25, 'SWF', 'MOD',  'OF',   'RAD_5',  'INITIATOR', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (26, 'SWF', 'MOD',  'OF',   'RAD_6',  'INITIATOR', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (27, 'SWF', 'MOD',  'OF',   'RAD_7',  'INITIATOR', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (28, 'SWF', 'MOD',  'IM',   'RAD_8',  'INITIATOR', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (29, 'SWF', 'MOD',  'IM',   'RAD_10', 'INITIATOR', 'REQUIRED');
#--------------------------------------          IM - Image Manager / Image Archive
INSERT INTO typeConfiguration VALUES (30, 'SWF', 'IM',   'OF',   'RAD_4',  'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (31, 'SWF', 'IM',   'PPSM', 'RAD_6',  'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (32, 'SWF', 'IM',   'PPSM', 'RAD_7',  'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (33, 'SWF', 'IM',   'MOD',  'RAD_8',  'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (34, 'SWF', 'IM',   'MOD',  'RAD_10', 'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (35, 'SWF', 'IM',   'EC',   'RAD_10', 'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (36, 'SWF', 'IM',   'OF',   'RAD_11', 'INITIATOR', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (37, 'SWF', 'IM',   'OF',   'RAD_13', 'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (38, 'SWF', 'IM',   'ID',   'RAD_14', 'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (39, 'SWF', 'IM',   'ID',   'RAD_16', 'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (40, 'SWF', 'IM',   'EC',   'RAD_18', 'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (41, 'SWF', 'IM',   'PPSM', 'RAD_20', 'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (42, 'SWF', 'IM',   'PPSM', 'RAD_21', 'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (43, 'SWF', 'IM',   'OF',   'RAD_42', 'RESPONDER', 'OPTIONAL');
INSERT INTO typeConfiguration VALUES (44, 'SWF', 'IM',   'OF',   'RAD_49', 'INITIATOR', 'OPTIONAL');
#--------------------------------------          PSM - Performed Procedure step manager
INSERT INTO typeConfiguration VALUES (45, 'SWF', 'PPSM', 'MOD',  'RAD_6',  'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (46, 'SWF', 'PPSM', 'OF',   'RAD_6',  'INITIATOR', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (47, 'SWF', 'PPSM', 'IM',   'RAD_6',  'INITIATOR', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (48, 'SWF', 'PPSM', 'MOD',  'RAD_7',  'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (49, 'SWF', 'PPSM', 'OF',   'RAD_7',  'INITIATOR', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (50, 'SWF', 'PPSM', 'IM',   'RAD_7',  'INITIATOR', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (51, 'SWF', 'PPSM', 'EC',   'RAD_20', 'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (52, 'SWF', 'PPSM', 'OF',   'RAD_20', 'INITIATOR', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (53, 'SWF', 'PPSM', 'IM',   'RAD_20', 'INITIATOR', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (54, 'SWF', 'PPSM', 'EC',   'RAD_21', 'RESPONDER', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (55, 'SWF', 'PPSM', 'OF',   'RAD_21', 'INITIATOR', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (56, 'SWF', 'PPSM', 'IM',   'RAD_21', 'INITIATOR', 'REQUIRED');
#--------------------------------------          ID - Image Display
INSERT INTO typeConfiguration VALUES (57, 'SWF', 'ID',   'IM',   'RAD_14', 'INITIATOR', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (58, 'SWF', 'ID',   'IM',   'RAD_16', 'INITIATOR', 'REQUIRED');
#--------------------------------------          EC - Evidence Creator
INSERT INTO typeConfiguration VALUES (59, 'SWF', 'EC',   'IM',   'RAD_18', 'INITIATOR', 'REQUIRED');
INSERT INTO typeConfiguration VALUES (60, 'SWF', 'EC',   'PPSM', 'RAD_20', 'INITIATOR', 'OPTIONAL');
INSERT INTO typeConfiguration VALUES (61, 'SWF', 'EC',   'PPSM', 'RAD_21', 'INITIATOR', 'OPTIONAL');
INSERT INTO typeConfiguration VALUES (62, 'SWF', 'EC',   'IM',   'RAD_10', 'INITIATOR', 'REQUIRED');
