---------------------------------------------------------
-- iWear RouteTracker Database Definition [09-04-2005] --
---------------------------------------------------------
-- Authors: Tobias Warden, Roman Pesina und Xin Xing   --
---------------------------------------------------------

-- create routetracker database currently owned by user postgres
CREATE DATABASE routetracker;

-- USE routetracker;
\c routetracker;

-- Delete relation tables (n:m)
DROP TABLE t_locations;
DROP TABLE r_tour_keyword CASCADE;
DROP TABLE r_tour_person CASCADE;
DROP TABLE r_waypoint CASCADE;


-- Tours

DROP TABLE t_tour CASCADE;

CREATE TABLE t_tour
	(tour_id SERIAL,
	 description TEXT DEFAULT NULL,
	 constraint pk__t_tour PRIMARY KEY (tour_id)
	);


-- Keywords
	
DROP TABLE t_keyword CASCADE;

CREATE TABLE t_keyword
	( keyword_id SERIAL,
	  label VARCHAR(150) not null,
	  constraint pk__t_keyword PRIMARY KEY (keyword_id),
	  constraint un__t_keyword__label UNIQUE (label)
	);

	
-- Persons

DROP TABLE t_person CASCADE;
	
CREATE TABLE t_person
	(person_id SERIAL,
	 name VARCHAR(100) not null,
	 first_name VARCHAR(100) not null,
	 login VARCHAR(30) DEFAULT NULL,
	 constraint pk__t_person PRIMARY KEY (person_id),
	 constraint un__t_person__login UNIQUE (login)
	);

	
-- Waypoint-Relation

CREATE TABLE r_waypoint
	(waypoint_id SERIAL,
	 tour_id INT not null,
	 time TIMESTAMP not null,
	 constraint pk__t_waypoint PRIMARY KEY (waypoint_id),
	 constraint un__r_waypoint__time UNIQUE (time)
	);

	
-- Sensor Types

DROP TABLE t_data_sensors CASCADE;
	
CREATE TABLE t_data_sensors	
	(sensor_id SERIAL,
	 label varchar(50) not null,
	 description TEXT default NULL,
	 constraint pk__t_data_sensors PRIMARY KEY(sensor_id),
	 constraint un__t_data_sensors__label UNIQUE (label)
	);

-- load data for the static table t_data_sensors

\i db_sensors.sql


-- Sensor Values 

DROP TABLE t_data_sensor_values CASCADE;

CREATE TABLE t_data_sensor_values
	(value_id SERIAL,
	 value FLOAT not null,
	 sensor_id INT not null,
	 waypoint_id int not null,
	 constraint pk__t_data_sensor_values PRIMARY KEY (value_id),
	 constraint fk__t_data_sensor_values__sensor_id
	 	FOREIGN KEY (sensor_id)
	 		references t_data_sensors (sensor_id)
	 		on delete CASCADE, 
	 constraint fk__t_data_sensor_values__waypoint_id
		FOREIGN KEY (waypoint_id)
			references r_waypoint (waypoint_id)
			on delete CASCADE
	);

	
-- Recreate locations-tables

CREATE TABLE t_locations
	(location_id SERIAL,
	 city 		VARCHAR(30) DEFAULT 'Bremen',
	 area 		VARCHAR(30) DEFAULT 'University',
	 building 	VARCHAR(60) DEFAULT 'MZH',
	 level 		VARCHAR(30) DEFAULT 'Ebene 5',
	 room 		VARCHAR(30) DEFAULT 'MZH 5380',
	 comment 	TEXT DEFAULT NULL,
	 lon	NUMERIC(11,8) DEFAULT '0',
	 lat	NUMERIC(11,8) DEFAULT '0',
	 elev	NUMERIC(9,4) DEFAULT'0',
	 waypoint_id int not null,
	 constraint pk__t_location PRIMARY KEY (location_id),
	 constraint fk__t_locations__waypoint_id
		FOREIGN KEY (waypoint_id)
			references r_waypoint (waypoint_id)
			on delete CASCADE
	);


-- Tour-Keyword-Relation

CREATE TABLE r_tour_keyword
	(tour_id INT not null,
	 keyword_id INT not null,
	 constraint fk__r_tour_keyword__tour_id 
	 	FOREIGN KEY (tour_id) 
	 		references t_tour (tour_id) 
	 		on delete CASCADE, 
	 constraint fk__r_tour_keyword__keyword_id 
	 	FOREIGN KEY (keyword_id)
	 		references t_keyword (keyword_id) 
	 		on delete CASCADE,
	constraint pk__r_tour_keyword 
		PRIMARY KEY (tour_id, keyword_id)
	);


-- Tour-Person-Relation

CREATE TABLE r_tour_person
	(tour_id INT not null,
	 person_id INT not null,
	 constraint fk__r_tour_person__tour_id 
	 	FOREIGN KEY (tour_id)
	 		references t_tour (tour_id)
	 		on delete CASCADE,
	 constraint fk__r_tour_person__person_id 
	 	FOREIGN KEY (person_id)
	 		references t_person (person_id)
	 		on delete CASCADE,
	 constraint pk__r_tour_person 
	 	PRIMARY KEY (tour_id, person_id)	
	);


-- Images

DROP TABLE t_images CASCADE;

CREATE TABLE t_images
	(image_id SERIAL,
	 waypoint_id INT not null, 
	 image BYTEA not null,
	 comment TEXT DEFAULT NULL, 	 
	 constraint pk__t_images PRIMARY KEY (image_id),
	 constraint fk__t_images__waypoint_id
	 	FOREIGN KEY (waypoint_id)
	 	references r_waypoint (waypoint_id)
	 	on delete SET NULL,
	 constraint un__t_images__image UNIQUE (image)
	 -- resolution (x,y)
	 -- image size
	 -- image type (may be obsolete)
	 -- file name
	 -- ...
	);


-- Sounds

DROP TABLE t_sounds CASCADE;

CREATE TABLE t_sounds
	(sound_id SERIAL,
	 waypoint_id INT not null,
	 sound BYTEA not null,
	 comment TEXT DEFAULT NULL,
	 constraint pk__t_sounds PRIMARY KEY (sound_id),
 	 constraint fk__t_sounds__waypoint_id
	 	FOREIGN KEY (waypoint_id)
	 	references r_waypoint (waypoint_id)
	 	on delete SET NULL,
	 constraint un__t_sounds__sound UNIQUE (sound)
	);


-- Annotations

DROP TABLE r_annotations;

CREATE TABLE r_annotations	
	(annotation_id SERIAL,
	 keyword_id INT not null,
	 waypoint_id INT not null,
	 image_id INT DEFAULT NULL, 
	 sound_id INT DEFAULT NULL,
	 comment TEXT DEFAULT NULL,
	 constraint fk__r_annotations__keyword_id
	 	FOREIGN KEY (keyword_id)
	 		references t_keyword (keyword_id)
	 		on delete CASCADE,
	 constraint fk__r_annotations__waypoint_id
	 	FOREIGN KEY (waypoint_id)
	 		references r_waypoint (waypoint_id)
	 		on delete CASCADE,
	 constraint fk__r_annotations__image_id
	 	FOREIGN KEY (image_id)
	 		references t_images (image_id)
	 		on delete SET NULL,
	 constraint fk__r_annotations__sound_id
	 	FOREIGN KEY (sound_id)
	 		references t_sounds (sound_id)
	 		on delete SET NULL,
	 constraint pk__r_annotation 
	 	PRIMARY KEY (annotation_id),
	 constraint u__keyword_id__waypoint_id 
	 	UNIQUE (keyword_id, waypoint_id)
	);