#!/bin/sh

rm -f /opt/firebird/Awacs.*
rm -f /dev/shm/Awacs.fdb

/opt/firebird/bin/isql -q -u sysdba -p phoenix << EOF

create database '/opt/firebird/Awacs.fdb';

-- *********************************************
-- #CREATE TABLES
-- *********************************************

-- *********************************************
-- AWACS
-- lastsaved is set to allow nulls as trigger will automatically set datetime on insert and update
-- *********************************************
CREATE TABLE awacs (entry_id INTEGER PRIMARY KEY NOT NULL,
		    message_type 	 SMALLINT,
		    user_id 		 SMALLINT,
		    error_id		 SMALLINT,
		    watch_id		 INTEGER,
		    date_raised		 TIMESTAMP,
		    date_closed		 TIMESTAMP,
		    source			 VARCHAR(256),
		    summary			 VARCHAR(256),
	   	    details	 		 VARCHAR(1024),
		    awacs_open		 SMALLINT,
		    readby_pc		 SMALLINT
			);

CREATE GENERATOR gen_entry_id;
SET GENERATOR gen_entry_id TO -1;
SET TERM !! ;
CREATE TRIGGER set_entry_id FOR awacs
ACTIVE BEFORE INSERT AS
BEGIN
 IF (new.entry_id IS NULL) THEN
  new.entry_id 		= GEN_ID(gen_entry_id, 1);
END!!
SET TERM ; !!


-- *********************************************
-- #CONSTRAINTS
-- *********************************************

insert into awacs values (null, 0, 3, 0, -1, CURRENT_TIMESTAMP(0), CURRENT_TIMESTAMP(0), 'Awacs Script', 'Awacs Table Created', 'Awacs Table Created', 1 , 1);

commit;

EOF

chmod 775 /opt/firebird
chown firebird:firebird /opt/firebird/Awacs.fdb
