drop table administrator cascade; 
drop table attempt cascade; 
drop table badge cascade;
drop table badge_is_awarded_to_quiztaker cascade;
drop table category cascade;
drop table choice cascade; 
drop table hint cascade; 
drop table medal cascade; 
drop table medal_is_awarded_to_quiztaker cascade; 
drop table moderator cascade; 
drop table question cascade; 
drop table question_is_part_of_quiz cascade; 
drop table quiz cascade; 
drop table quiz_is_part_of_tournament cascade; 
drop table quiz_is_part_of_tournamentphase cascade;
drop table quiztaker cascade; 
drop table quiztaker_participates_in_tournament cascade; 
drop table quiztaker_participates_in_tournamentphase cascade; 
drop table result_table cascade;
drop table selectedchoice cascade; 
drop table selectionquestion cascade; 
drop table subject cascade; 
drop table textquestion cascade; 
drop table tournament cascade; 
drop table tournamentorganizer cascade; 
drop table tournamentorganizer_hosts_tournament cascade; 
drop table tournamentphase cascade; 
drop table user_table cascade; 


create table user_table(
	userID serial primary key,
	fullName varchar(100),
	username varchar(50) not null unique,
	password_attr varchar(50) not null
);

create table administrator(
	userID integer primary key,
	
	constraint userID_fk foreign key(userID)
		references user_table(userID)
);

create table moderator(
	userID integer primary key,
	
	constraint userID_fk foreign key(userID)
		references user_table(userID)
);

create table tournament(
	tournamentID serial primary key,
	tournamentName varchar(50) not null
);

create table tournamentPhase(
	tournamentPhaseID serial primary key,
	tournamentID integer not null,
	tournamentPhaseName varchar(50) not null,
	
	constraint tournamentID_fk foreign key (tournamentID)
		references tournament(tournamentID)
);


create table quizTaker(
	userID integer primary key,

	constraint userID_fk foreign key(userID)
		references user_table(userID)
	
);

create table tournamentOrganizer(
	userID integer not null,
	tournamentOrganizerID serial not null unique,
	
	constraint userID_fk foreign key(userID)
		references moderator(userID),
		
	constraint tournamentOrganizer_pk primary key(tournamentOrganizerID, userID)
		
	
	
);

create table subject(
	subjectID serial primary key,
	subjectName varchar(100) not null

);

create table category(
	categoryID serial primary key,
	subjectID integer not null,
	categoryName varchar(100) not null,
	
	constraint subjectID_fk foreign key(subjectID)
		references subject(subjectID)

);

create table quiz(
	quizID serial primary key,
	categoryID integer not null,
	quizName varchar(100) not null,
	
	constraint categoryID_fk foreign key(categoryID)
		references category(categoryID)
		
);

create table medal(
	medalID serial primary key,
	tournamentID integer not null,
	medalName varchar(50) not null,
	description varchar(500) not null,
	
	constraint tournamentID_fk foreign key(tournamentID)
		references tournament(tournamentID)

);


create table question(

	questionID serial primary key,
	questionText varchar(500) not null
	
);

create table hint(
	hintID serial primary key,
	hintText varchar(500) not null,
	questionID integer not null,
	
	constraint questionID_fk foreign key(questionID)
		references question(questionID)
);

create table textQuestion(
	questionID integer primary key,
	
	constraint questionID_fk foreign key(questionID)
		references question(questionID)
);

create table selectionQuestion(
	questionID integer primary key,
	
	constraint questionID_fk foreign key(questionID)
		references question(questionID)
);

create table choice(
	choiceID serial primary key,
	questionID integer not null,
	isCorrect boolean not null,
	choiceText varchar(500) not null,
	
	constraint questionID_fk foreign key(questionID)
		references selectionQuestion(questionID)
);

create table attempt(
	attemptID serial primary key,
	userID integer not null,
	attemptDate Date not null,
	quizID integer not null,
	
	constraint userID_fk foreign key(userID)
		references quizTaker(userID),
		
	constraint quizID_fk foreign key(quizID)
		references quiz(quizID)
);

create table selectedChoice (

	selectedChoiceID serial not null,

	choiceID integer not null,
	
	attemptID integer not null,
	
	constraint choiceID_fk foreign key(choiceID)
		references choice(choiceID),
		
	constraint attemptID_fk foreign key(attemptID)
		references attempt(attemptID),
		
	constraint selectedChoice_pk primary key(selectedChoiceID, choiceID)

);


create table result_table(
	resultID serial primary key,
	attemptID integer not null,
	points integer not null,
	
	constraint attemptID_fk foreign key(attemptID)
		references attempt(attemptID)
);

create table badge(
	badgeID serial primary key,
	badgeName varchar(100) not null,
	description varchar(500) not null,
	resultID integer not null,
	
	constraint resultID_fk foreign key(resultID)
		references result_table(resultID)
);

create table quiz_is_part_of_tournament(
quizID integer not null,
tournamentID integer not null,

constraint quizID_fk foreign key(quizID)
	references quiz(quizID),
	
constraint tournamentID_fk foreign key(tournamentID)
	references tournament(tournamentID),
	
	constraint pom1_pk primary key(quizID, tournamentID)
);

create table quiz_is_part_of_tournamentPhase(
quizID integer not null,
tournamentPhaseID integer not null,

constraint quizID_fk foreign key(quizID)
	references quiz(quizID),
	
constraint tournamentPhaseID_fk foreign key(tournamentPhaseID)
	references tournamentPhase(tournamentPhaseID),
	
	constraint pom8_pk primary key(quizID, tournamentPhaseID)
);

create table tournamentOrganizer_hosts_tournament(
userID integer not null,
tournamentID integer not null,

constraint tournamentOrganizerID_fk foreign key(userID)
	references tournamentOrganizer(tournamentorganizerid),

constraint tournamentID_fk foreign key(tournamentID)
	references tournament(tournamentID),
	
	constraint pom2_pk primary key(userID, tournamentID)
);

create table question_is_part_of_quiz(
	questionID integer not null,
	quizID integer not null,
	
	constraint questionID_fk foreign key(questionID)
		references question(questionID),
		
	constraint quizID_fk foreign key(quizID)
		references quiz(quizID),
	
	constraint pom3_pk primary key(questionID, quizID)
);

create table quizTaker_participates_in_tournament(
	userID integer not null,
	tournamentID integer not null,
	
	constraint userID_fk foreign key(userID)
		references quizTaker(userID),
		
	constraint tournamentID_fk foreign key(tournamentID)
		references tournament(tournamentID),
		
	constraint pom4_pk primary key(userID, tournamentID)
	
);

create table quizTaker_participates_in_tournamentPhase(
	userID integer not null,
	tournamentPhaseID integer not null,
	
	constraint userID_fk foreign key(userID)
		references quizTaker(userID),
		
	constraint tournamentPhaseID_fk foreign key(tournamentPhaseID)
		references tournamentPhase(tournamentPhaseID),
		
	constraint pom5_pk primary key(userID, tournamentPhaseID)
	
);

create table medal_is_awarded_to_quizTaker(
	medalID integer not null,
	userID integer not null,
	
	constraint medalID_fk foreign key(medalID)
		references medal(medalID),
		
	constraint userID_fk foreign key(userID)
		references quizTaker(userID),
		
	constraint pom6_pk primary key(medalID, userID)
);

create table badge_is_awarded_to_quizTaker(
	badgeID integer not null,
	userID integer not null,
	
	constraint badgeID_fk foreign key(badgeID)
		references badge(badgeID),
		
	constraint userID_fk foreign key(userID)
		references quizTaker(userID),
		
	constraint pom7_pk primary key(badgeID, userID)
);
