#!/bin/bash

# database paths
HTCDB="$1"
AVLDB="$2"
TMPSQL="/tmp/htc.sql"

echo "Checking AVL database: $AVLDB"
rm $AVLDB

# Check if the database exists; if not then create and initialise it.
if [ ! -f $AVLDB ]; then
	echo "$AVLDB: creating mandatory tables."
	sqlite3 $AVLDB """
	BEGIN TRANSACTION;
			CREATE TABLE IF NOT EXISTS [attributes] ( [ID] text, [SYMBOL] text, [VALUE] text,CONSTRAINT [PK_attributes]              PRIMARY KEY ([ID], [SYMBOL]) );
              CREATE TABLE IF NOT EXISTS [link_nodes] ( [LINKNODE_ID] text PRIMARY KEY, [POINT_ID] text, [CUES] text );
              CREATE TABLE IF NOT EXISTS [journeys] (                      [JOURNEY_ID] text PRIMARY KEY NOT NULL, [JOURNEY_TRANSITION_CUE] text, [LINK_TRANSITION_CUE] text, [CUES] text);
              CREATE TABLE IF NOT EXISTS [JOURNEY_LINKS] (                      [JOURNEY_ID] text NOT NULL, [LINK_ID] text NOT NULL, CONSTRAINT [PK_JOURNEY_LINKS]                      PRIMARY KEY ([JOURNEY_ID], [LINK_ID]), CONSTRAINT [FK_JOURNEY_LINKS_0_0]                       FOREIGN KEY ([JOURNEY_ID]) REFERENCES [journeys] ([JOURNEY_ID]) MATCH NONE CONSTRAINT [FK_JOURNEY_LINKS_links]                      FOREIGN KEY ([LINK_ID]) REFERENCES [links] ([LINK_ID]));
              CREATE TABLE IF NOT EXISTS [links] ( [LINK_ID] text NOT NULL,[LINK_END_NAME1] text, [LINK_END_NAME2] text, [LINKNODE_ID1]  text,                      [LINKNODE_ID2]  text,  [DISTANCE_TRANSIT] REAL DEFAULT 0,PRIMARY KEY(LINK_ID) );
              REPLACE INTO attributes VALUES('R0','Line','');
              REPLACE INTO attributes VALUES('R0','Name','');
              REPLACE INTO journeys VALUES('R0','enter_journey','enter_link','enter_journey,exit_journey,Journey_Selected');
	      REPLACE INTO attributes VALUES ('CUE_INTERPRETER','FEEDBACK_MODE','0');
	      REPLACE INTO attributes VALUES ('LED_PRESENTATION','DISABLE_CLEAR_SIGN_BEHAVIOUR','1');
    COMMIT;
	"""
fi

# uodate cues and VolumeScaling tables based on HTC.db
echo "Updating tables 'cues' and 'VolumeScaling'..."
sqlite3 $AVLDB "DROP TABLE IF EXISTS cues"
sqlite3 $AVLDB "DROP TABLE IF EXISTS VolumeScaling"
# copy table cues from HTC.db
echo "Dumping cues from $HTCDB to $TMPSQL..."
sqlite3 $HTCDB ".dump cues" > $TMPSQL
sqlite3 -init $TMPSQL $AVLDB .exit
# copy table VolumeScaling from HTC.db
echo "Dumping VolumeSacling from $HTCDB to $TMPSQL..."
sqlite3 $HTCDB ".dump VolumeScaling" > $TMPSQL
sqlite3 -init $TMPSQL $AVLDB .exit
# clean up
rm $TMPSQL
echo "done. $AVLDB is ready to use"
