1)
create table player (
	playerId INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
	playername VARCHAR(15) UNIQUE NOT NULL,
	phoneId VARCHAR(40) UNIQUE NOT NULL,
	PRIMARY KEY(playerId)
)

// TRACK MIT ÜBERSETZUNG
2)
create table track (
	trackId INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
	deleted BOOLEAN DEFAULT false,
	PRIMARY KEY (trackId)
)

3)
create table language (
	langcode VARCHAR(2) PRIMARY KEY
)

4)
insert into language values ('en')
insert into language values ('de')

5)
create table track_translation (
	trackId INTEGER NOT NULL,
	langcode VARCHAR(2) NOT NULL,
	trackname VARCHAR(30) NOT NULL,
	FOREIGN KEY(trackId) REFERENCES track(trackId),
	FOREIGN KEY(langcode) REFERENCES language(langcode),
	PRIMARY KEY (trackId, langcode)
)


// IMAGES MIT ÜBERSETZUNG

6)
create table image (
	imageId INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
	deleted BOOLEAN DEFAULT false,
	PRIMARY KEY(imageId)
)

7)
create table image_orig (
	imageId INTEGER PRIMARY KEY,
	data BLOB NOT NULL,
	FOREIGN KEY(imageId) REFERENCES image(imageId)
)

8)
create table image_edge (
	imageId INTEGER PRIMARY KEY,
	data BLOB NOT NULL,
	FOREIGN KEY(imageId) REFERENCES image(imageId)
)

9)	
create table image_translation (
	imageId INTEGER NOT NULL,
	langcode VARCHAR(2) NOT NULL,
	imagename VARCHAR(25) NOT NULL,
	FOREIGN KEY(imageId) REFERENCES image(imageId),
	FOREIGN KEY(langcode) REFERENCES language(langcode),
	PRIMARY KEY (imageId, langcode)
)

	
// Verknüpfung zwischen Track und Images (damit Image mehreren Tracks zugewiesen werden kann)

10)
create table image_in_track (
	trackId INTEGER NOT NULL,
	imageId INTEGER NOT NULL,
	position INTEGER NOT NULL,
	FOREIGN KEY(trackId) REFERENCES track(trackId),
	FOREIGN KEY(imageId) REFERENCES image(imageId),
	PRIMARY KEY (trackId, imageId)
)
	

// SESSION

11)
create table session (
	sessionId INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
	trackId INTEGER,
	playerId INTEGER NOT NULL,
	score DECIMAL(5, 2) NOT NULL,
	date BIGINT NOT NULL,
	FOREIGN KEY(trackId) REFERENCES track(trackId),
	FOREIGN KEY(playerId) REFERENCES player(playerId),
	PRIMARY KEY(sessionId)
)
	
	
// DRAWING

12)
create table drawing (
	drawingId INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
	sessionId INTEGER NOT NULL,
	imageId INTEGER NOT NULL,
	data BLOB NOT NULL,
	FOREIGN KEY(sessionId) REFERENCES session(sessionId),
	FOREIGN KEY(imageId) REFERENCES image(imageId),
	PRIMARY KEY(drawingId)
)

13)
create table scoreimage (
	scoreimageId INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
	drawingId INTEGER NOT NULL,
	score DECIMAL(5, 2) NOT NULL,
	data BLOB NOT NULL,
	FOREIGN KEY(drawingId) REFERENCES drawing(drawingId),
	PRIMARY KEY(scoreimageId)
)
	
// DROPS

drop table scoreimage;
drop table image_in_track;
drop table image_translation;
drop table track_translation;
drop table language;
drop table drawing;
drop table session;
drop table image_orig;
drop table image_edge;
drop table player;
drop table image;
drop table track

