#!/bin/sh

AddSampleRate()
{
  # Helper function that adds an entry exists in the SHOWS DB
  # Arg #1 = TITLE, #2 = SAMPLE_RATE, #3 = UUID
  $DBConnectionString /opt/firebird/New.fdb << EOF
  SET TERM !! ;
  EXECUTE BLOCK AS
  BEGIN
  insert into SHOWS
    values (null,0,'$1','Blank Desk',$2,0, CURRENT_TIMESTAMP(0),'$3',1,-1,-1,null,-1,CURRENT_TIMESTAMP(0));
  END!!
  SET TERM ; !!
EOF
}

# *********************************************
# IMPORTANT, PLEASE READ: in response to bug CAL-10872 
#  the following version number field has been added  
#  and MUST be updated when adding new fields to the 
#  database. This version number will be written to  
#  the LastUpgradeVersion file at the end of the script.
#  It is the responsibility of the developer to keep 
#  this number up to date 
# *********************************************

EquivalentDatabaseVersionNumber=8.0.4

# *********************************************

if [ "$1" == "largo" ] || [ "$2" == "largo" ]; then
  Largo=true
fi

if [ "$1" == "blank" ] || [ "$2" == "blank" ]; then
  BlankShow=true
  echo Creating Blank DeskShows database
  DBNAME="/opt/firebird/BlankShows.fdb"
  rm -f $DBNAME
else
  DBNAME="/opt/firebird/DeskShows.fdb"

  mkdir -p /home/MasterControl/Shows/Non_Default_Shows
  mkdir -p /home/MasterControl/Shows/Default_Shows/Calrec/Defaults

  cd /home/MasterControl/Shows/Default_Shows/Calrec/Defaults

  if [ "$Largo" == "true" ]; then
    mkdir -p Default_44_1Khz_1 Default_88_2Khz_1
  else
    mkdir -p Default_48Khz_1 Default_96Khz_1
  fi

  mkdir -p /home/MasterControl/Options
  cd /home/MasterControl/Options

  mkdir -p PathConfigs
  mkdir -p ExtIFace
  mkdir -p Plugins
  mkdir -p MidiSetup
  mkdir -p UserSectionSetups
  mkdir -p InsertSetup
  mkdir -p DefaultSettings
  mkdir -p MonSelSetups
  mkdir -p TFTSetups
  mkdir -p MonMtrExtIP
  mkdir -p WildSetups
  mkdir -p HydraNetwork
  mkdir -p PortAlias
  mkdir -p SurfaceLayout
  mkdir -p Presets

  rm -f /opt/firebird/DeskShows.*
  rm -f /dev/shm/DeskShows.fdb
fi

DBConnectionString='/opt/firebird/bin/isql -q -u sysdba -p phoenix'

$DBConnectionString << EOF
create database '/opt/firebird/New.fdb';

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

-- *********************************************
-- CLIENTS
-- *********************************************
CREATE TABLE clients (client_id BIGINT PRIMARY KEY NOT NULL,
		      client_name VARCHAR(50) NOT NULL UNIQUE);

CREATE GENERATOR gen_client_id;
SET GENERATOR gen_client_id TO -1;
SET TERM !! ;
CREATE TRIGGER set_client_id FOR clients
ACTIVE BEFORE INSERT AS
BEGIN
 IF (new.client_id IS NULL) THEN
  new.client_id = GEN_ID(gen_client_id, 1);
END!!
SET TERM ; !!

-- *********************************************
-- PROJECTS
-- *********************************************
CREATE TABLE projects ( project_id BIGINT PRIMARY KEY NOT NULL,
			project_name VARCHAR(50) NOT NULL,
			client_id BIGINT);

CREATE GENERATOR gen_project_id;
SET GENERATOR gen_project_id TO -1;
SET TERM !! ;
CREATE TRIGGER set_project_id FOR projects
ACTIVE BEFORE INSERT AS
BEGIN
 IF (new.project_id IS NULL) THEN
  new.project_id = GEN_ID(gen_project_id, 1);
END!!
SET TERM ; !!

-- *********************************************
-- SHOWS
-- lastsaved is set to allow nulls as trigger will automatically set datetime on insert and update
-- *********************************************
CREATE TABLE shows (show_id BIGINT PRIMARY KEY NOT NULL,
		    project_id BIGINT,
		    title VARCHAR(50) NOT NULL,
		    description VARCHAR(200),
		    sample_rate SMALLINT NOT NULL,
		    user_id BIGINT,
		    last_saved TIMESTAMP,
		    uuid VARCHAR(36) UNIQUE,
		    default_show CHAR(1),
		    major_version INTEGER NOT NULL,
		    minor_version INTEGER NOT NULL,
		    last_loaded_memory VARCHAR(36) UNIQUE,
		    num_of_fader_sections INTEGER DEFAULT -1 NOT NULL,
		    created_on TIMESTAMP);

CREATE GENERATOR gen_show_id;
SET GENERATOR gen_show_id TO -1;
SET TERM !! ;
CREATE TRIGGER set_show_id FOR shows
ACTIVE BEFORE INSERT AS
BEGIN
 IF (new.show_id IS NULL) THEN
  new.show_id = GEN_ID(gen_show_id, 1);
  new.user_id = 0;
END!!
SET TERM ; !!

-- *********************************************
-- LAST LOADED SHOW
-- *********************************************

CREATE TABLE last_loaded_show(id BIGINT PRIMARY KEY NOT NULL,
			      show_uuid VARCHAR(36));

SET TERM !! ;
CREATE OR ALTER TRIGGER tr_last_loaded_show_deleted FOR last_loaded_show
ACTIVE AFTER DELETE AS
BEGIN
	IF (old.id = 0) THEN
		INSERT INTO last_loaded_show SELECT 0, uuid from shows WHERE last_saved = (SELECT MAX(last_saved) FROM shows WHERE default_show = 0);
END!!
SET TERM ; !!

-- *********************************************
-- MEMORIES
-- lastsaved is set to allow nulls as trigger will automatically set datetime on insert and update
-- *********************************************
CREATE TABLE memories (memory_id BIGINT PRIMARY KEY NOT NULL,
		       label VARCHAR(50) NOT NULL,
		       description VARCHAR(200),
		       last_saved TIMESTAMP,
		       uuid VARCHAR(36) UNIQUE,
		       created TIMESTAMP,
		       major_version INTEGER NOT NULL,
		       minor_version INTEGER NOT NULL);

CREATE GENERATOR gen_memory_id;
SET GENERATOR gen_memory_id TO -1;
SET TERM !! ;
CREATE TRIGGER set_memory_id FOR memories
ACTIVE BEFORE INSERT AS
BEGIN
 IF (new.memory_id IS NULL) THEN
  new.memory_id = GEN_ID(gen_memory_id, 1);
END!!
CREATE OR ALTER TRIGGER clear_show_last_loaded_memory FOR memories
ACTIVE AFTER DELETE AS
BEGIN
  UPDATE shows set last_loaded_memory = null WHERE last_loaded_memory = old.uuid;
END!!
SET TERM ; !!


-- *********************************************
-- MEMORY->SHOW LINKS
-- *********************************************
CREATE TABLE show_memories (show_memory_id BIGINT PRIMARY KEY NOT NULL,
			    show_uuid VARCHAR(36) NOT NULL,
			    memory_uuid VARCHAR(36) NOT NULL,
			    memory_link1_uuid VARCHAR(36),
			    memory_link2_uuid VARCHAR(36));

CREATE GENERATOR gen_show_memory_id;
SET GENERATOR gen_show_memory_id TO -1;
SET TERM !! ;
CREATE TRIGGER set_show_memory_id FOR show_memories
ACTIVE BEFORE INSERT AS
BEGIN
 IF (new.show_memory_id IS NULL) THEN
  new.show_memory_id = GEN_ID(gen_show_memory_id, 1);
END!!
CREATE OR ALTER TRIGGER tr_del_show_memories FOR shows
ACTIVE BEFORE DELETE AS
BEGIN
  DELETE FROM memories WHERE uuid IN (SELECT memory_uuid FROM show_memories WHERE show_uuid = old.uuid);
END!!
SET TERM ; !!


-- *********************************************
-- MEMORY FEATURES
-- *********************************************
CREATE TABLE memory_features (memory_feature_id BIGINT PRIMARY KEY NOT NULL,
			      memory_uuid VARCHAR(36) NOT NULL,
			      feature_id INTEGER NOT NULL,
			      fader_section_id INTEGER,
			      path_uid VARCHAR(36));

CREATE GENERATOR gen_memory_feature_id;
SET GENERATOR gen_memory_feature_id TO -1;
SET TERM !! ;
CREATE TRIGGER set_memory_feature_id FOR memory_features
ACTIVE BEFORE INSERT AS
BEGIN
 IF (new.memory_feature_id IS NULL) THEN
  new.memory_feature_id = GEN_ID(gen_memory_feature_id, 1);
END!!
SET TERM ; !!


-- *********************************************
-- DESK FEATURES - primary key will be montana preset feature id
-- *********************************************
CREATE TABLE desk_features (feature_id INTEGER PRIMARY KEY NOT NULL,
			    description VARCHAR(50) NOT NULL,
			    feature_type INTEGER NOT NULL);

CREATE GENERATOR gen_desk_feature_id;
SET GENERATOR gen_desk_feature_id TO -1;
SET TERM !! ;
CREATE TRIGGER set_desk_feature_id FOR desk_features
ACTIVE BEFORE INSERT AS
BEGIN
 IF (new.feature_id IS NULL) THEN
  new.feature_id = GEN_ID(gen_desk_feature_id, 1);
END!!
SET TERM ; !!

-- *********************************************
-- SEQUENCES
-- *********************************************
CREATE TABLE sequences (sequence_id BIGINT PRIMARY KEY NOT NULL, 
			uuid VARCHAR(36) NOT NULL UNIQUE, 
			show_uuid VARCHAR(36) NOT NULL, 
			external_control SMALLINT, 
			sequence_cue_view SMALLINT);

CREATE GENERATOR gen_sequence_id;
SET GENERATOR gen_sequence_id TO -1;
SET TERM !! ;
CREATE TRIGGER set_sequence_id FOR sequences
ACTIVE BEFORE INSERT AS
BEGIN
 IF (new.sequence_id IS NULL) THEN
  new.sequence_id = GEN_ID(gen_sequence_id, 1);
END!!
SET TERM ; !!

-- *********************************************
-- CUES
-- *********************************************
CREATE TABLE cues (cue_id BIGINT PRIMARY KEY NOT NULL, 
		   uuid VARCHAR(36) NOT NULL UNIQUE, 
           sequence_uuid VARCHAR(36) NOT NULL, 
		   cue_number DECIMAL(5,4), 
		   cue_desc VARCHAR(50), 
		   memory_uuid VARCHAR(36));

CREATE GENERATOR gen_cue_id;
SET GENERATOR gen_cue_id TO -1;
SET TERM !! ;
CREATE TRIGGER set_cue_id FOR cues
ACTIVE BEFORE INSERT AS
BEGIN
 IF (new.cue_id IS NULL) THEN
  new.cue_id = GEN_ID(gen_cue_id, 1);
END!!
SET TERM ; !!

-- *********************************************
-- USERS
-- *********************************************
CREATE TABLE users (user_id BIGINT PRIMARY KEY NOT NULL,
		    full_name VARCHAR(50) NOT NULL UNIQUE);

CREATE GENERATOR gen_user_id;
SET GENERATOR gen_user_id TO -1;
SET TERM !! ;
CREATE TRIGGER set_user_id FOR users
ACTIVE BEFORE INSERT AS
BEGIN
 IF (new.user_id IS NULL) THEN
  new.user_id = GEN_ID(gen_user_id, 1);
END!!
SET TERM ; !!


-- *********************************************
-- PORT ALIAS FILES
-- *********************************************

CREATE TABLE port_alias_output_files (
				    file_id  		    	BIGINT PRIMARY KEY NOT NULL,
				    file_name			VARCHAR(200) NOT NULL UNIQUE,
				    status			SMALLINT);
CREATE GENERATOR gen_alias_output_files_id;
SET GENERATOR gen_alias_output_files_id TO -1;
SET TERM !! ;
CREATE TRIGGER set_alias_output_files_id FOR port_alias_output_files
ACTIVE BEFORE INSERT OR UPDATE AS
BEGIN
 IF (new.file_id IS NULL) THEN
   new.file_id = GEN_ID(gen_alias_output_files_id, 1);
END!!
SET TERM ; !!

CREATE TABLE port_alias_input_files (
				    file_id  		    	BIGINT PRIMARY KEY NOT NULL,
				    file_name			VARCHAR(200) NOT NULL UNIQUE,
				    status			SMALLINT);
CREATE GENERATOR gen_alias_input_files_id;
SET GENERATOR gen_alias_input_files_id TO -1;
SET TERM !! ;
CREATE TRIGGER set_alias_input_files_id FOR port_alias_input_files
ACTIVE BEFORE INSERT OR UPDATE AS
BEGIN
 IF (new.file_id IS NULL) THEN
   new.file_id = GEN_ID(gen_alias_input_files_id, 1);
END!!
SET TERM ; !!

-- *********************************************
-- PRESETS AND FOLDERS
-- *********************************************

CREATE TABLE preset_folders (id INTEGER PRIMARY KEY NOT NULL,
                             type SMALLINT NOT NULL,
                             name VARCHAR(128) NOT NULL,
                             date_modified TIMESTAMP NOT NULL);
CREATE GENERATOR gen_preset_folder_id;
SET GENERATOR gen_preset_folder_id TO -1;
SET TERM !! ;
CREATE TRIGGER set_preset_folder_id FOR preset_folders
ACTIVE BEFORE INSERT AS
BEGIN
  IF (new.id IS NULL) THEN
    new.id = GEN_ID(gen_preset_folder_id, 1);
END!!
SET TERM ; !!

CREATE TABLE presets (id INTEGER PRIMARY KEY NOT NULL,
                      folder_id INTEGER NOT NULL,
                      name VARCHAR(128) NOT NULL,
                      description VARCHAR(256),
                      date_modified TIMESTAMP NOT NULL,
                      properties BIGINT NOT NULL,
                      num_bytes INTEGER NOT NULL);
CREATE GENERATOR gen_presets_id;
SET GENERATOR gen_presets_id TO -1;
SET TERM !! ;
CREATE TRIGGER set_preset_id FOR presets
ACTIVE BEFORE INSERT AS
BEGIN
  IF (new.id IS NULL) THEN
    new.id = GEN_ID(gen_presets_id, 1);
END!!
SET TERM ; !!

-- *********************************************
-- #CONSTRAINTS
-- *********************************************
ALTER TABLE projects ADD CONSTRAINT unique_client_project UNIQUE (client_id, project_id);
ALTER TABLE projects ADD CONSTRAINT fk_client_project FOREIGN KEY (client_id) REFERENCES clients(client_id) ON DELETE CASCADE;

ALTER TABLE shows ADD CONSTRAINT fk_project_show FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE;

ALTER TABLE show_memories ADD CONSTRAINT unique_show_mem UNIQUE (show_uuid, memory_uuid);
ALTER TABLE show_memories ADD CONSTRAINT fk_showmemory_show FOREIGN KEY (show_uuid) REFERENCES shows(uuid) ON UPDATE CASCADE;
ALTER TABLE show_memories ADD CONSTRAINT fk_showmemory_memory FOREIGN KEY (memory_uuid) REFERENCES memories(uuid) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE memory_features ADD CONSTRAINT fk_memoryfeature_memory FOREIGN KEY (memory_uuid) REFERENCES memories(uuid) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE last_loaded_show ADD CONSTRAINT fk_lastloadedshow_show FOREIGN KEY (show_uuid) REFERENCES shows(uuid) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE sequences ADD CONSTRAINT fk_show_sequence FOREIGN KEY (show_uuid) REFERENCES shows(uuid) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE cues ADD CONSTRAINT fk_sequence_cue FOREIGN KEY (sequence_uuid) REFERENCES sequences(uuid) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE cues ADD CONSTRAINT fk_memory_cue FOREIGN KEY (memory_uuid) REFERENCES memories(uuid) ON UPDATE CASCADE ON DELETE CASCADE;

--*********************************************
-- #INSERT DESK FEATURE DATA (set values)

-- Feature types: UNIDENTIFIED_FEATURE  = 0;	///< Feature type unidentified
--		  PATH_FEATURE		= 1;	///< Path related desk feature
--		  FADER_SECTION_FEATURE = 2;	///< Fader section related desk feature
--		  DESK_FEATURE		= 3;	///< Desk wide feature
--		  REMOTE_PORT_FEATURE	= 4;	///< Remote port type feature
--		  PANEL_FEATURE         = 5;	///< Panel Feature
-- *********************************************

insert into desk_features values (-1, 'Invalid', 0);
insert into desk_features values (0, 'Eq', 1);
insert into desk_features values (1, 'Assignment', 3);
insert into desk_features values (2, 'Fader Block', 2);
insert into desk_features values (3, 'Dynamics', 1);
insert into desk_features values (4, 'Track Send', 1);
insert into desk_features values (5, 'Input', 1);
insert into desk_features values (6, 'Direct Input', 1);
insert into desk_features values (7, 'Fader', 2);
insert into desk_features values (8, 'Routing', 1);
insert into desk_features values (9, 'Width', 1);
insert into desk_features values (10, 'Pan', 1);
insert into desk_features values (11, 'Pre-Post', 1);
insert into desk_features values (12, 'Direct Output', 1);
insert into desk_features values (13, 'Path Control', 1);
insert into desk_features values (14, 'Buss Config', 3);
insert into desk_features values (15, 'Aux Send', 1);
insert into desk_features values (16, 'Oscillator', 3);
insert into desk_features values (17, 'Insert', 1);
insert into desk_features values (18, 'Monitor', 3);
insert into desk_features values (19, 'Monitor Selector', 3);
insert into desk_features values (20, 'Talkback', 3);
insert into desk_features values (21, 'Fader Block Monitor', 2);
insert into desk_features values (22, 'IO', 3);
insert into desk_features values (23, 'Fader Strip', 2);
insert into desk_features values (24, 'Mode Selector', 2);
insert into desk_features values (25, 'IO List', 3);
insert into desk_features values (26, 'Pool', 3);
insert into desk_features values (27, 'Template Management', 5);
insert into desk_features values (28, 'Tone', 1);
insert into desk_features values (29, 'GPIO', 3);
insert into desk_features values (30, 'Mic Open Buss', 3);
insert into desk_features values (31, 'Path Config', 1);
insert into desk_features values (32, 'Tx Reh', 3);
insert into desk_features values (33, 'Layer Selector', 2);
insert into desk_features values (34, 'Channel Config', 3);
insert into desk_features values (35, 'Mix Minus', 1);
insert into desk_features values (36, 'Studio Setup', 3);
insert into desk_features values (37, 'Buss Outputs', 3);
insert into desk_features values (38, 'Metering', 3);

-- *********************************************
-- DEFAULT SHOW DATA
-- *********************************************

insert into clients values (null, 'Calrec');
insert into projects values (null, 'Defaults', 0);

-- *********************************************
-- GENERAL PRESET FOLDERS
-- *********************************************

INSERT INTO preset_folders (type, name, date_modified) VALUES (0, 'General Presets', CURRENT_TIMESTAMP(0));
INSERT INTO preset_folders (type, name, date_modified) VALUES (1, 'General Presets', CURRENT_TIMESTAMP(0));
INSERT INTO preset_folders (type, name, date_modified) VALUES (2, 'General Presets', CURRENT_TIMESTAMP(0));
INSERT INTO preset_folders (type, name, date_modified) VALUES (3, 'General Presets', CURRENT_TIMESTAMP(0));
INSERT INTO preset_folders (type, name, date_modified) VALUES (4, 'General Presets', CURRENT_TIMESTAMP(0));
INSERT INTO preset_folders (type, name, date_modified) VALUES (5, 'General Presets', CURRENT_TIMESTAMP(0));

commit;

EOF

if [ "$Largo" == "true" ]; then
  echo Base rate 44_1kHz
  AddSampleRate Default_44_1Khz_1 44 '225088d0-b7c0-11dd-ad8b-0800200c9a66'
  AddSampleRate Default_88_2Khz_1 88 '37eaa458-bbb5-4c06-8255-0e9ad568c16e'
else
  echo Base rate 48kHz
  AddSampleRate Default_48Khz_1 48 '225088d0-b7c0-11dd-ad8b-0800200c9a66'
  AddSampleRate Default_96Khz_1 96 '4ea75bf0-0d71-11de-8c30-0800200c9a66'
fi

$DBConnectionString /opt/firebird/New.fdb << EOF
commit;
EOF


mv  /opt/firebird/New.fdb $DBNAME

if [ "$BlankShow" == "true" ]
then
  echo Finished Creating Blank DeskShows database
else
  chmod 775 /opt/firebird
  chown firebird:firebird $DBNAME

  # write the new version number to the LastUpgradeVersion file (the file is created if it doesn't already exist)
  echo $EquivalentDatabaseVersionNumber > /home/MasterControl/UpgradeScripts/LastUpgradeVersion
fi

