#!/bin/sh

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

echo Creating Hydra DataBase

/opt/firebird/bin/isql -q -u sysdba -p phoenix << EOF
create database '/opt/firebird/Hydra2DB.fdb';
CREATE GENERATOR gen_timestamp;
SET GENERATOR gen_timestamp TO -1;
CREATE GENERATOR gen_port_id;
SET GENERATOR gen_port_id TO 0;
CREATE GENERATOR gen_node_id;
SET GENERATOR gen_node_id TO 0;
CREATE GENERATOR gen_salvo_patchList_id;
SET GENERATOR gen_salvo_patchList_id TO -1;
CREATE GENERATOR remote_network_id;
SET GENERATOR remote_network_id TO 0;

COMMIT;
EOF

/opt/firebird/bin/isql -q -u sysdba -p phoenix << EOF
connect /opt/firebird/Hydra2DB.fdb;

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$GENERATOR_NAME FROM RDB\$GENERATORS WHERE RDB\$GENERATOR_NAME = 'GEN_VPBTREENODE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE GENERATOR gen_vpbtreenode;';
    EXECUTE STATEMENT '
      SET GENERATOR gen_vpbtreenode TO 1;';
  END
 
END!!

SET TERM ; !!

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN
  
  IF( NOT EXISTS ( SELECT RDB\$GENERATOR_NAME FROM RDB\$GENERATORS WHERE RDB\$GENERATOR_NAME = 'GEN_TREENODE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE GENERATOR gen_treenode;';
    EXECUTE STATEMENT '
      SET GENERATOR gen_treenode TO 1;';
  END
    
END!!
SET TERM ; !!

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN
  
  IF( NOT EXISTS ( SELECT RDB\$GENERATOR_NAME FROM RDB\$GENERATORS WHERE RDB\$GENERATOR_NAME = 'GEN_FOLDER_NODE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE GENERATOR gen_folder_node;';
    EXECUTE STATEMENT '
      SET GENERATOR gen_folder_node TO 1;';
  END

END!!
SET TERM ; !!

-- Now connect to the database....

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'AUTHORITIES')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE  authorities;' ;
  END

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'USERS')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE users;';
  END

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'ROUTER_SYNC_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE router_sync_table ;' ;
  END

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'CLIENT_MASK_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE client_mask_table;' ;
  END

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'TREE_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE tree_table ;' ;
  END

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'VPB_TREE_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE vpb_tree_table ;' ;
  END

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'PORT_ACCESS_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE port_access_table;' ;
  END

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'HYDRA_PORT_PERMISSIONS_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE hydra_port_permissions_table;' ;
  END

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'HYDRA_RESOURCE_PORTS_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE hydra_resource_ports_table;' ;
  END

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'HYDRA_RESOURCE_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE hydra_resource_table;' ;
  END

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'REMOTE_NETWORKS_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE remote_networks_table;' ;
  END

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'SOURCE_MAP_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE source_map_table;' ;
  END

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'DEST_MAP_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE dest_map_table ;' ;
  END

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'THIRD_PARTY_PATCH_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE third_party_patch_table ;' ;
  END

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'PORT_ALLOCATION_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE port_allocation_table ;' ;
  END

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'PORT_TO_PORT_PATCH_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE port_to_port_patch_table ;' ;
  END

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'META_DATA_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE meta_data_table ;' ;
  END

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'ACCESS_RIGHTS_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE access_rights_table ;' ;
  END

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'DELETED_ITEMS')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE deleted_items ;' ;
  END

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'HYDRA_PORTS_AUDIO_OUTPUT')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE HYDRA_PORTS_AUDIO_OUTPUT ;' ;
  END

  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'HYDRA_PORTS_AUDIO_INPUT')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE HYDRA_PORTS_AUDIO_INPUT ;' ;
  END
  
  IF( EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'AOIP_EXTERNAL_ADDRESSES')) THEN
  BEGIN
    EXECUTE STATEMENT '
      DROP TABLE aoip_external_addresses;' ;
  END

END!!
SET TERM ; !!

-- Owner id name lookup table
CREATE TABLE Owner_Ids (
	id INTEGER NOT NULL,
	console_type INTEGER NOT NULL,
	owner_id BIGINT NOT NULL UNIQUE,
	description VARCHAR(50) NOT NULL,
	PRIMARY KEY(id)
	);
COMMIT;

--Users Table

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'USERS')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE TABLE users (
        uname        	VARCHAR(50) PRIMARY KEY NOT NULL,
        pwd          	VARCHAR(50) NOT NULL,
        enabled			CHAR(1) NOT NULL,
        owner_id		BIGINT NOT NULL,
        timestamp_id 	INT,
        FOREIGN KEY (owner_id) REFERENCES owner_ids(owner_id) ON DELETE CASCADE) ;' ;
    EXECUTE STATEMENT '
      CREATE TRIGGER set_users FOR users
      ACTIVE BEFORE INSERT OR UPDATE AS
      BEGIN
        new.timestamp_id = GEN_ID(gen_timestamp, 1);
      END';
  END
  
END!!
SET TERM ; !!

-- authorities

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'AUTHORITIES')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE TABLE authorities (
        uname        VARCHAR(50) NOT NULL,
        authority    VARCHAR(50) NOT NULL,
        timestamp_id INT,
        FOREIGN KEY (uname) REFERENCES users(uname) ON DELETE CASCADE) ;' ;
    EXECUTE STATEMENT '
      CREATE TRIGGER set_authorities FOR authorities
      ACTIVE BEFORE INSERT OR UPDATE AS
      BEGIN
        new.timestamp_id = GEN_ID(gen_timestamp, 1);
      END';
  END

END!!
SET TERM ; !!

-- client_mask_table

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'CLIENT_MASK_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE TABLE client_mask_table ( mask_id SMALLINT PRIMARY KEY NOT NULL,
        description  VARCHAR(50) UNIQUE NOT NULL,
        console_type INT NOT NULL,
        mask         BIGINT UNIQUE,
        address      BIGINT UNIQUE,
        timestamp_id INT);' ;
    EXECUTE STATEMENT '
      CREATE TRIGGER mask_id FOR client_mask_table
      ACTIVE BEFORE INSERT OR UPDATE AS
      BEGIN
        new.timestamp_id = GEN_ID(gen_timestamp, 1);
      END';
  END
  
END!!
SET TERM ; !!

-- port_access_table

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'PORT_ACCESS_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE TABLE port_access_table( access_rights_id SMALLINT,
        hid          INT,
        port         SMALLINT,
        style_id     SMALLINT,
        timestamp_id INT);' ;
    EXECUTE STATEMENT '
      CREATE TRIGGER src_access_rights_id FOR port_access_table
      ACTIVE BEFORE INSERT OR UPDATE AS
      BEGIN
        new.timestamp_id = GEN_ID(gen_timestamp, 1);
      END';
  END

END!!
SET TERM ; !!

-- remote_networks_table

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'REMOTE_NETWORKS_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE TABLE remote_networks_table(
        id           INT PRIMARY KEY,
        name         VARCHAR(9),
        type         INT,
        primary_ip   VARCHAR(15),
        secondary_ip VARCHAR(15),   
        nChannels     INT,
        nAuxes        INT,
        timestamp_id INT);' ;
    EXECUTE STATEMENT '
      CREATE TRIGGER remote_networks_table_id FOR remote_networks_table
      ACTIVE BEFORE INSERT OR UPDATE AS
      BEGIN
        new.timestamp_id = GEN_ID(gen_timestamp, 1);
        if(new.id is null) then
          new.id = GEN_ID(remote_network_id,1) ;
      END';
  END

END!!
SET TERM ; !!

-- hydra_resource_table

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'HYDRA_RESOURCE_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE TABLE hydra_resource_table( 
        hid          INT PRIMARY KEY,
        default_name VARCHAR(50) NOT NULL,
        user_name    VARCHAR(50),
        org_name     VARCHAR(50),
        org_num_1    INT,
        org_num_2    INT,
        sample_rate  INT,
        follows_console_id INT,
        capabilities INT,
        timestamp_id INT,
        FOREIGN KEY (follows_console_id) REFERENCES owner_ids(id) ON DELETE SET NULL);' ;
    EXECUTE STATEMENT '
      CREATE TRIGGER hydra_resource_table_id FOR hydra_resource_table
      ACTIVE BEFORE INSERT OR UPDATE AS
      BEGIN
        new.timestamp_id = GEN_ID(gen_timestamp, 1);
      END';
  END

END!!
SET TERM ; !!

-- hydra_resource_ports_table

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN
  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'HYDRA_RESOURCE_PORTS_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE TABLE hydra_resource_ports_table(
        id                  INT,
        hid                 INT NOT NULL,
        port_id             SMALLINT NOT NULL,
        default_name        VARCHAR(20) NOT NULL,
        user_name           VARCHAR(20),
        default_description VARCHAR(50) NOT NULL,
        user_description    VARCHAR(50),
        type_id             SMALLINT,
        style_id            SMALLINT,
        slot_id             SMALLINT,
        assoc               SMALLINT,
        assoc_hid           INT,
        assoc_style         SMALLINT,
        assoc_port          SMALLINT,
        timestamp_id        INT,
        owner_id            BIGINT DEFAULT 0 NOT NULL,
        PRIMARY KEY(id),
        FOREIGN KEY(hid) REFERENCES hydra_resource_table(hid) ON DELETE CASCADE);' ;
    EXECUTE STATEMENT '
      CREATE INDEX hpt_hid_idx ON hydra_resource_ports_table(hid);';
    EXECUTE STATEMENT '
      CREATE TRIGGER hydra_resource_ports_table_id FOR hydra_resource_ports_table
      ACTIVE BEFORE INSERT OR UPDATE AS
      BEGIN
        new.timestamp_id = GEN_ID(gen_timestamp, 1);
        if(new.id is null) then
          new.id = gen_id(gen_port_id,1) ;
      END';
  END

END!!
SET TERM ; !!

-- hydra_port_permissions_table

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'HYDRA_PORT_PERMISSIONS_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE TABLE hydra_port_permissions_table(
        id           INT NOT NULL,
        denials      BIGINT NOT NULL,
        timestamp_id INT,
        PRIMARY KEY(id),
        FOREIGN KEY(id) REFERENCES hydra_resource_ports_table(id) ON DELETE CASCADE) ;' ;
    EXECUTE STATEMENT '
      CREATE TRIGGER hydra_port_permissions_table_id FOR hydra_port_permissions_table
      ACTIVE BEFORE INSERT OR UPDATE AS
      BEGIN
        new.timestamp_id = GEN_ID(gen_timestamp, 1);
      END';
    EXECUTE STATEMENT '
      CREATE TRIGGER hydra_resource_ports_table_perm FOR hydra_resource_ports_table
      ACTIVE AFTER INSERT AS
      BEGIN
        INSERT INTO hydra_port_permissions_table (id,denials) values (new.id,0) ;
      END';
  END

END!!
SET TERM ; !!

-- tree_table

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'TREE_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE TABLE tree_table (
        nodeID           INT NOT NULL,
        parentID         INT NOT NULL,
        access_rights    BIGINT NOT NULL,
        show_alerts      VARCHAR(1) NOT NULL,
        name             VARCHAR(50),
        port_id          INT,
        groupID          INT,
        group_sort_order INT,
        timestamp_id     INT ,
        PRIMARY KEY (nodeID),
        FOREIGN KEY (port_id) REFERENCES hydra_resource_ports_table(id) ON DELETE CASCADE) ;' ;

    EXECUTE STATEMENT '
      CREATE TRIGGER set_node_id FOR tree_table
      ACTIVE BEFORE INSERT AS
      BEGIN
        IF (new.nodeID IS NULL) THEN
          new.nodeID = GEN_ID(gen_node_id, 1);
      END';

    EXECUTE STATEMENT '
      CREATE TRIGGER set_tree FOR tree_table
      ACTIVE BEFORE INSERT OR UPDATE AS
      BEGIN
        new.timestamp_id = GEN_ID(gen_timestamp, 1);
      END';
    EXECUTE STATEMENT '
      CREATE INDEX treetable_parentid_idx ON tree_table(parentid);';

  END
  
END!!
SET TERM ; !!

-- Vitual Patchbay tree_table

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'VPB_TREE_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE TABLE vpb_tree_table (
        nodeID           INT NOT NULL,
        parentID         INT NOT NULL,
        access_rights    BIGINT NOT NULL,
        show_alerts      VARCHAR(1) NOT NULL,
        name             VARCHAR(50),
        port_id          INT,
        groupID          INT,
        group_sort_order INT,
        clientBitMask    INT,
        timestamp_id     INT ,
        PRIMARY KEY (nodeID) ,
        FOREIGN KEY (port_id) REFERENCES hydra_resource_ports_table(id) ON DELETE CASCADE,
        FOREIGN KEY (parentID) REFERENCES VPB_TREE_TABLE(nodeID) ON DELETE CASCADE);' ;

    EXECUTE STATEMENT '
      CREATE TRIGGER set_vbp_node_id FOR vpb_tree_table
      ACTIVE BEFORE INSERT AS
      BEGIN
        IF (new.nodeID IS NULL) THEN
          new.nodeID = GEN_ID(gen_vpbtreenode, 1);
        END';

    EXECUTE STATEMENT '
      CREATE TRIGGER set_vpb_tree FOR vpb_tree_table
      ACTIVE BEFORE INSERT OR UPDATE AS
      BEGIN
        new.timestamp_id = GEN_ID(gen_timestamp, 1);
      END';

    EXECUTE STATEMENT '
      CREATE INDEX vpb_treetable_parentid_idx ON vpb_tree_table(parentid);';
  END
  
END!!
SET TERM ; !!

-- source_map_table

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'SOURCE_MAP_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE TABLE source_map_table (
        matrix_id    SMALLINT,
        level_id     SMALLINT,
        source_id    INTEGER NOT NULL,
        hid_id       INTEGER,
        hid_port_id  SMALLINT,
        timestamp_id INT );' ;
    EXECUTE STATEMENT '
      CREATE TRIGGER set_source_entry_id FOR source_map_table
      ACTIVE BEFORE INSERT OR UPDATE AS
      BEGIN
        new.timestamp_id = GEN_ID(gen_timestamp, 1);
      END';

    EXECUTE STATEMENT '
      CREATE INDEX INDEX_SOURCE_MAP_SOURCEID ON SOURCE_MAP_TABLE(SOURCE_ID)';
    EXECUTE STATEMENT '
      CREATE INDEX INDEX_SOURCE_MAP_PORT_ID ON SOURCE_MAP_TABLE(HID_PORT_ID)';
    EXECUTE STATEMENT '
      CREATE INDEX INDEX_SOURCE_MAP_HID ON SOURCE_MAP_TABLE(HID_ID)';
  END
  
END!!
SET TERM ; !!

-- dest_map_table

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'DEST_MAP_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE TABLE dest_map_table (
        matrix_id    SMALLINT,
        level_id     SMALLINT,
        dest_id      INTEGER NOT NULL,
        hid_id       INTEGER,
        hid_port_id  SMALLINT,
        timestamp_id INT );' ;
    EXECUTE STATEMENT '
      CREATE TRIGGER set_dest_entry_id FOR dest_map_table
      ACTIVE BEFORE INSERT OR UPDATE AS
      BEGIN
        new.timestamp_id = GEN_ID(gen_timestamp, 1);
      END';

    EXECUTE STATEMENT '
      CREATE INDEX INDEX_DEST_MAP_DESTID ON DEST_MAP_TABLE(DEST_ID)';
    EXECUTE STATEMENT '
      CREATE INDEX INDEX_DEST_MAP_PORT_ID ON DEST_MAP_TABLE(HID_PORT_ID)';
    EXECUTE STATEMENT '
      CREATE INDEX INDEX_DEST_MAP_HID ON DEST_MAP_TABLE(HID_ID)';
  END
  
END!!
SET TERM ; !!

-- third_party_patch_table

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'THIRD_PARTY_PATCH_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE TABLE third_party_patch_table (
        third_party_id SMALLINT,
        matrix_id      SMALLINT,
        level_id       SMALLINT,
        src_id         SMALLINT,
        dest_id        SMALLINT,
        timestamp_id   INT );' ;
    EXECUTE STATEMENT '
      CREATE TRIGGER set_third_party_patch_table FOR third_party_patch_table
      ACTIVE BEFORE INSERT OR UPDATE AS
      BEGIN
        new.timestamp_id = GEN_ID(gen_timestamp, 1);
      END';

    EXECUTE STATEMENT '
      CREATE INDEX INDEX_TPPTABLE_DST_HID ON THIRD_PARTY_PATCH_TABLE(DEST_ID);';
    EXECUTE STATEMENT '
      CREATE INDEX INDEX_TPPTABLE_SRC_HID ON THIRD_PARTY_PATCH_TABLE(SRC_ID)';
  END
  
END!!
SET TERM ; !!

-- third_party_undo_patch_table
SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'third_party_undo_patch_table' ) )  
  THEN
  BEGIN

    -- *********************************************
    -- If the THIRD_PARTY_UNDO_PATCH_TABLE table does not already
    -- exist, create it, and the accompanying triggers on the
    -- source_map_table and dest_map_table.
    -- *********************************************

    EXECUTE STATEMENT 'CREATE TABLE THIRD_PARTY_UNDO_PATCH_TABLE (\
      SRC_HID Integer,\
      SRC_PORT Integer,\
      DST_HID Integer,\
      DST_PORT Integer \
      );' ;

    EXECUTE STATEMENT 'CREATE TRIGGER SOURCE_MAP_TABLE_BEFORE_UPDATE FOR SOURCE_MAP_TABLE ACTIVE \
      BEFORE UPDATE POSITION 0 \
      AS \
      BEGIN \
        INSERT INTO THIRD_PARTY_UNDO_PATCH_TABLE (SRC_HID, SRC_PORT, DST_HID, DST_PORT) \
          SELECT DISTINCT \
          SRC.HID_ID AS SOURCE_HID, \
          SRC.HID_PORT_ID AS SOURCE_PORT, \
          DST.HID_ID AS DESTIN_HID, \
          DST.HID_PORT_ID AS DESTIN_PORT \
        FROM \
          SOURCE_MAP_TABLE SRC, \
          DEST_MAP_TABLE DST, \
          THIRD_PARTY_PATCH_TABLE TPPT \
        WHERE \
          SRC.SOURCE_ID = TPPT.SRC_ID+1 and \
          DST.DEST_ID = TPPT.DEST_ID+1 and \
          TPPT.MATRIX_ID = old.MATRIX_ID-1 and \
          TPPT.LEVEL_ID = old.LEVEL_ID-1 and \
          TPPT.SRC_ID = old.SOURCE_ID-1; \
      END';

    EXECUTE STATEMENT 'CREATE TRIGGER DEST_MAP_TABLE_BEFORE_UPDATE FOR DEST_MAP_TABLE ACTIVE \
    BEFORE UPDATE POSITION 0 \
    AS \
    BEGIN \
      INSERT INTO THIRD_PARTY_UNDO_PATCH_TABLE (SRC_HID, SRC_PORT, DST_HID, DST_PORT) \
      SELECT DISTINCT \
        SRC.HID_ID AS SOURCE_HID, \
        SRC.HID_PORT_ID AS SOURCE_PORT, \
        DST.HID_ID AS DESTIN_HID, \
        DST.HID_PORT_ID AS DESTIN_PORT \
      FROM \
        SOURCE_MAP_TABLE SRC, \
        DEST_MAP_TABLE DST, \
        THIRD_PARTY_PATCH_TABLE TPPT \
      WHERE \
        SRC.SOURCE_ID = TPPT.SRC_ID+1 and \
        DST.DEST_ID = TPPT.DEST_ID+1 and \
        TPPT.MATRIX_ID = old.MATRIX_ID-1 and \
        TPPT.LEVEL_ID = old.LEVEL_ID-1 and \
        TPPT.DEST_ID = old.DEST_ID-1; \
      END';

  END

END!!
SET TERM ; !!

-- port_allocation_table

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'PORT_ALLOCATION_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE TABLE port_allocation_table (
        port_id      SMALLINT UNIQUE,
        port_width   SMALLINT,
        trunk_id     SMALLINT,
        timestamp_id INT );' ;
    EXECUTE STATEMENT '
      CREATE TRIGGER set_port_allocation_table FOR port_allocation_table
      ACTIVE BEFORE INSERT OR UPDATE AS
      BEGIN
        new.timestamp_id = GEN_ID(gen_timestamp, 1);
      END';
  END
  
END!!
SET TERM ; !!

-- port_to_port_patch_table

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'PORT_TO_PORT_PATCH_TABLE')) THEN
  BEGIN
  EXECUTE STATEMENT '
    CREATE TABLE port_to_port_patch_table (
      SrcHid       INT,
      SrcPort      SMALLINT,
      DestHid      INT,
      DestPort     SMALLINT,
      SalvoName    VARCHAR(50),
      isActive     SMALLINT,
      timestamp_id INT );' ;
    EXECUTE STATEMENT '
      CREATE TRIGGER set_port_to_port_patch_table FOR port_to_port_patch_table
      ACTIVE BEFORE INSERT OR UPDATE AS
      BEGIN
        new.timestamp_id = GEN_ID(gen_timestamp, 1);
      END';
  END
  
END!!
SET TERM ; !!

-- meta_data_table

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'META_DATA_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE TABLE meta_data_table (
        meta_id      INT,
        description  VARCHAR(50),
        meta_data    BLOB SUB_TYPE TEXT,
        meta_data_2  BLOB SUB_TYPE TEXT,
        timestamp_id INT );' ;
    EXECUTE STATEMENT '
      CREATE TRIGGER set_meta_data_table FOR meta_data_table
      ACTIVE BEFORE INSERT OR UPDATE AS
      BEGIN
        new.timestamp_id = GEN_ID(gen_timestamp, 1);
      END';
  END
  
END!!
SET TERM ; !!

-- router sync table

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'ROUTER_SYNC_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE TABLE router_sync_table (
        client_mask_id SMALLINT,
        pri_one        SMALLINT DEFAULT 0 NOT NULL,
        pri_two        SMALLINT DEFAULT 0 NOT NULL,
        pri_three      SMALLINT DEFAULT 0 NOT NULL,
        pri_four       SMALLINT DEFAULT 0 NOT NULL,
        pri_five       SMALLINT DEFAULT 0 NOT NULL,
        current_index  SMALLINT,
        SECONDARY_CURRENT_INDEX SMALLINT,
        timestamp_id   INT,
        FOREIGN KEY(client_mask_id) REFERENCES client_mask_table(mask_id) ON DELETE CASCADE);';
    EXECUTE STATEMENT '
      CREATE TRIGGER set_router_sync_table FOR router_sync_table
      ACTIVE BEFORE INSERT OR UPDATE AS
      BEGIN
        new.timestamp_id = GEN_ID(gen_timestamp, 1);
      END';
  END
  
END!!
SET TERM ; !!

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT a.RDB\$PROCEDURE_NAME FROM RDB\$PROCEDURES a WHERE a.RDB\$PROCEDURE_NAME = 'AMEND_PK')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE PROCEDURE AMEND_PK ( THETABLENAME Varchar(100) )
      AS
      DECLARE VARIABLE theConstraintName varchar(100); 
      DECLARE VARIABLE theStatement varchar(255);
      BEGIN
        SELECT rdb\$constraint_name
        FROM rdb\$relation_constraints
        WHERE rdb\$relation_name = :THETABLENAME
        AND rdb\$constraint_type = ''PRIMARY KEY''
        INTO :theConstraintName;
    
        theStatement = ''alter table '' || :theTablename || '' drop constraint '' || :theConstraintName; 
    
        EXECUTE STATEMENT :theStatement;
      END' ;
  END

END!!
SET TERM ; !!

-- Console Settings Table

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'CONSOLE_SETTINGS_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE TABLE console_settings_table (
        client_mask_id SMALLINT,
        setting_id     SMALLINT NOT NULL,
        setting_value  VARCHAR(50) NOT NULL,
        timestamp_id   INT,
        FOREIGN KEY(client_mask_id) REFERENCES client_mask_table(mask_id) ON DELETE CASCADE);';
    EXECUTE STATEMENT '
      CREATE TRIGGER set_console_settings_table FOR console_settings_table
      ACTIVE BEFORE INSERT OR UPDATE AS
      BEGIN
        new.timestamp_id = GEN_ID(gen_timestamp, 1);
      END';
  END
  
END!!
SET TERM ; !!

-- Port Data Table

SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'PORT_DATA_TABLE')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE TABLE port_data_table (
        port_id       SMALLINT,
        setting_id    SMALLINT NOT NULL,
        setting_value VARCHAR(50) NOT NULL,
        timestamp_id  INT,
        FOREIGN KEY(port_id) REFERENCES hydra_resource_ports_table(id) ON DELETE CASCADE);';
    EXECUTE STATEMENT '
      CREATE TRIGGER set_port_data_table FOR port_data_table
      ACTIVE BEFORE INSERT OR UPDATE AS
      BEGIN
        new.timestamp_id = GEN_ID(gen_timestamp, 1);
      END';


  END




END!!
SET TERM ; !!

-- *********************************************
-- H20 Salvo Patching List Memory

-- *********************************************
SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'SALVO_PATCH_LIST')) THEN
  BEGIN
    EXECUTE STATEMENT '	
                   CREATE TABLE Salvo_Patch_List (salvo_id INT PRIMARY KEY NOT NULL,
		       name VARCHAR(50) UNIQUE NOT NULL  ,
		       description VARCHAR(200),
			   modifiedBy VARCHAR(50),
		       last_saved TIMESTAMP,		      
		       created TIMESTAMP,
                    FOREIGN KEY (modifiedBy) REFERENCES Users(uname)                  

	   );';
 EXECUTE STATEMENT '	
	CREATE TRIGGER gen_salvo_patchList_id FOR Salvo_Patch_List
	ACTIVE BEFORE INSERT AS
	BEGIN
		IF (new.salvo_id IS NULL) THEN
		new.salvo_id = GEN_ID(gen_salvo_patchList_id, 1);
	END';



END
END!!
SET TERM ; !!

-- *********************************************
-- H20 Salvo patches 
-- Foreign key refr3nce will be indexed
-- *********************************************
SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'SALVO_PATCHES')) THEN
  BEGIN
    EXECUTE STATEMENT '	
CREATE TABLE Salvo_Patches (salvo_PatchList_id INT,
		       sourceID INT NOT NULL,
		       destID INT NOT NULL,			  
			   FOREIGN KEY(salvo_PatchList_id) REFERENCES Salvo_Patch_List(salvo_id) ON DELETE CASCADE,
               FOREIGN KEY (sourceID) REFERENCES hydra_resource_ports_table(id) ON DELETE CASCADE ,
               FOREIGN KEY (destID) REFERENCES hydra_resource_ports_table(id) ON DELETE CASCADE 
			  
		  );';

END
END!!
SET TERM ; !!

-- aoip_external_addresses
SET TERM !! ;
EXECUTE BLOCK AS
BEGIN

  IF( NOT EXISTS ( SELECT RDB\$RELATION_NAME FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME = 'AOIP_EXTERNAL_ADDRESSES')) THEN
  BEGIN
    EXECUTE STATEMENT '
      CREATE TABLE aoip_external_addresses(
        hid          INT PRIMARY KEY,
        timestamp_id INT,
        host VARCHAR(16) NOT NULL,
        aoip1_management VARCHAR(16),
        aoip1_media1 VARCHAR(16),
        aoip1_media2 VARCHAR(16),
        aoip2_management VARCHAR(16),
        aoip2_media1 VARCHAR(16),
        aoip2_media2 VARCHAR(16),       
        FOREIGN KEY(hid) REFERENCES hydra_resource_table(hid) ON DELETE CASCADE        
      );' ;
    EXECUTE STATEMENT '
      CREATE TRIGGER aoip_external_addresses_id FOR aoip_external_addresses
      ACTIVE BEFORE INSERT OR UPDATE AS
      BEGIN
        new.timestamp_id = GEN_ID(gen_timestamp, 1);
      END';
  END

END!!
SET TERM ; !!

COMMIT;

UPDATE OR INSERT INTO tree_table (nodeID,parentID,access_rights,show_alerts,name,port_id,groupID) values ( 0,0,0,'N','Ports',NULL,NULL ) MATCHING (nodeID);
UPDATE OR INSERT INTO VPB_TREE_TABLE (nodeID,parentID,access_rights,show_alerts,name,port_id,groupID,clientBitMask) values ( 0,0,0,'N','Hydra Patchbays',NULL,NULL,null ) MATCHING (nodeID);

INSERT INTO client_mask_table (Mask_Id, Description, Console_Type, Mask, Address) VALUES( 1 ,'Shared Patchbays',5, 1 ,0 ) ;
INSERT INTO vpb_tree_table (ParentID, ACCESS_Rights, Show_Alerts, Name, clientBitMask, Group_Sort_Order) VALUES(0, 0, 'N', 'Shared Patchbays', 1, -1) ;

COMMIT;

SET TERM ^ ;

-- Hydra audio input ports table
	CREATE TABLE HYDRA_PORTS_AUDIO_OUTPUT (
         id INTEGER NOT NULL,
         FOREIGN KEY(id) REFERENCES hydra_resource_ports_table(id) ON DELETE CASCADE
         )^

-- Hydra audio output ports table
	CREATE TABLE HYDRA_PORTS_AUDIO_INPUT (
		id INTEGER NOT NULL,
		gain SMALLINT DEFAULT 0 NOT NULL,
		phantom_power SMALLINT DEFAULT 0 NOT NULL,
		src SMALLINT DEFAULT 0 NOT NULL,
        assume_shared SMALLINT DEFAULT 0 NOT NULL,
		FOREIGN KEY(id) REFERENCES hydra_resource_ports_table(id) ON DELETE CASCADE
	       )^

-- Console primary key id generator
	CREATE GENERATOR gen_owner_ids_id^
	SET GENERATOR gen_owner_ids_id TO 0^ -- first record will be 1


-- Console primary key id
	CREATE TRIGGER tr_console_id FOR Owner_Ids
       	ACTIVE BEFORE INSERT AS
		BEGIN
			IF (new.id IS NULL) THEN
				new.id = GEN_ID(gen_owner_ids_id, 1);
		END^

-- Insert an audio input record into it's tables
	CREATE OR ALTER PROCEDURE InsertOrUpdateInputPort(
            hid INTEGER,
            styleId SMALLINT,
            portId SMALLINT,
            defaultName VARCHAR(12),
            userName VARCHAR(12),
            defaultDescription VARCHAR(50),
            userDescription VARCHAR(50),
            typeId SMALLINT,
            slotId SMALLINT,
            ownerId BIGINT,
            gain SMALLINT DEFAULT 0,
            phantomPower SMALLINT DEFAULT 0,
            src SMALLINT DEFAULT 0,
            assumeShared SMALLINT DEFAULT 0
            )
  	RETURNS (newId INTEGER)
	AS
 	BEGIN

 		--- Insert/update a record returning the id field
	  	UPDATE OR INSERT INTO hydra_resource_ports_table
		  		(hid, style_id, port_id, default_name, user_name,
            	default_description, user_description, type_id, slot_id, owner_id)
			VALUES ( :hid, :styleId, :portId, :defaultName, :userName, 
				:defaultDescription, :userDescription, :typeId, :slotId, :ownerId)
			MATCHING ( hid, port_id, style_id ) 
			RETURNING id INTO :newId;

		-- Insert/update a record using the id field from the previous table
	  	UPDATE OR INSERT INTO hydra_ports_audio_input
	  			(id, gain, phantom_power, src, assume_shared)
	  		VALUES ( :newId, :gain, :phantomPower, :src, :assumeShared)
			MATCHING ( id );
	 END^

-- When a record is added to client_mask_table, add it to Owner_Ids
	CREATE OR ALTER TRIGGER tr_add_client FOR CLIENT_MASK_TABLE
		ACTIVE AFTER INSERT
		AS
		BEGIN
			INSERT INTO Owner_Ids VALUES(null,new.console_type,new.address,new.description);
		END^

	-- When a record is updated in client_mask_table, update it in Owner_Ids
	CREATE OR ALTER TRIGGER tr_update_client FOR CLIENT_MASK_TABLE
		ACTIVE AFTER UPDATE
		AS
		BEGIN
			UPDATE Owner_Ids
            	SET console_type=new.console_type,
            		owner_id=new.address,
                	description=new.description
				WHERE owner_id = old.address;
		END^

-- When a record is removed from client_mask_table, remove it from Owner_Ids
	CREATE OR ALTER TRIGGER tr_del_client FOR CLIENT_MASK_TABLE
		ACTIVE BEFORE DELETE
		AS
		BEGIN
			DELETE FROM Owner_Ids WHERE owner_id = old.address ;
		END^

-- When a record is removed from ports table, remove from input and output ports table
	CREATE OR ALTER TRIGGER tr_del_port FOR HYDRA_RESOURCE_PORTS_TABLE
		ACTIVE BEFORE DELETE
		AS
		BEGIN
			DELETE FROM HYDRA_PORTS_AUDIO_INPUT WHERE id = old.id;
			DELETE FROM HYDRA_PORTS_AUDIO_OUTPUT WHERE id = old.id;
		END^

--  Add the default clients to the Owner_Ids if they don't already exist

-- n.b. ip addresses are stored in network format
INSERT INTO Owner_Ids (console_type, description, owner_id) VALUES (6, 'None', 4294967295 )^	-- 255.255.255.255;
INSERT INTO Owner_Ids (console_type, description, owner_id) VALUES (6, 'External', (1*65536) )^	-- 0.0.1.0;
INSERT INTO Owner_Ids (console_type, description, owner_id) VALUES (7, 'H2O Admin', (3*65536) )^	-- 0.0.3.0;
INSERT INTO Owner_Ids (console_type, description, owner_id) VALUES (8, 'H2O Guest', (1*16777216) + (3*65536) )^	-- 0.0.3.1;
INSERT INTO Owner_Ids (console_type, description, owner_id) VALUES (8, 'H2O User', (2*16777216) + (3*65536) )^	-- 0.0.3.2;

-- Duplicate the current consoles from client_mask_table into the new owner ids table
INSERT INTO Owner_Ids SELECT null,console_type,address,description FROM client_mask_table^

COMMIT^

SET TERM ; ^

UPDATE OR INSERT INTO users (uname, pwd, enabled, owner_id ) values ('admin','admin1','Y',(3*65536)) MATCHING (uname);
UPDATE OR INSERT INTO users (uname, pwd, enabled, owner_id ) values ('user','user1','Y',(2*16777216) + (3*65536)) MATCHING (uname);
UPDATE OR INSERT INTO authorities ( uname, authority) values ('admin','ROLE_ADMIN') MATCHING (uname);

EOF

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