#---------------------------------------------------------------------
# Create or recreate public schema devie tables in shakespeare public
# schema. Note: drop and create order takes keys into account, avoids
# cascade. However, these tables depend on the model tables, so if it
# is desired to rebuild the model tables, these tables must be dropped
# first, then rebuilt after the model tables have been rebuilt.
#---------------------------------------------------------------------

#-------------------------------------- drop existing tables and types

#----------------------------------------- create new types and tables
CREATE TABLE institution (
   id SERIAL PRIMARY KEY,
   code VARCHAR(16) NOT NULL UNIQUE,
   description VARCHAR(255) NOT NULL DEFAULT ' ',
   active BOOLEAN NOT NULL DEFAULT 'TRUE'
);
INSERT INTO institution VALUES (DEFAULT, 'IHE', 'RSNA IHE', true);
INSERT INTO institution VALUES (DEFAULT, 'GE', 'General Electric', true);
INSERT INTO institution VALUES (DEFAULT, 'FUJI', 'Fuji', true);

CREATE TABLE actorInstance (
   id SERIAL PRIMARY KEY,
   code VARCHAR(16) UNIQUE NOT NULL DEFAULT ' ',
   actorName VARCHAR(64) NOT NULL DEFAULT ' ',
   controllerUrl VARCHAR(256) NOT NULL DEFAULT ' ',
   aips VARCHAR(255) NOT NULL DEFAULT ' ',
   institutionId INTEGER NOT NULL REFERENCES institution,
   usersId INTEGER NOT NULL REFERENCES users
);

CREATE TABLE configuration (
   id SERIAL PRIMARY KEY,
   supported BOOLEAN NOT NULL,
   ipAddress VARCHAR(128) NOT NULL DEFAULT ' ',
   port INTEGER,
   receivingFacility VARCHAR(128) NOT NULL DEFAULT ' ',
   receivingApplication VARCHAR(128) NOT NULL DEFAULT ' ',
   aeTitle VARCHAR(16) NOT NULL DEFAULT ' ',
   actorInstanceId INTEGER NOT NULL REFERENCES actorInstance,
   typeConfigurationId INTEGER NOT NULL REFERENCES typeConfiguration
);

CREATE TABLE sessions (
   id SERIAL PRIMARY KEY,
   code VARCHAR(16) NOT NULL UNIQUE,
   description VARCHAR(255) NOT NULL DEFAULT ' ',
   institutionSession BOOLEAN NOT NULL,
   active BOOLEAN NOT NULL,
   openflag BOOLEAN NOT NULL,
   institutionId INTEGER NOT NULL REFERENCES institution,
   usersId INTEGER NOT NULL REFERENCES users
);


