USE [master]

IF NOT EXISTS (SELECT name FROM sys.DATABASES WHERE name = N'Quiz')
    CREATE DATABASE [Quiz]
ELSE
    DROP DATABASE [Quiz]
    CREATE DATABASE [Quiz]
GO
USE [Quiz]

CREATE TABLE Lecturer
(
lecturer_id_ INT IDENTITY(1,1)  NOT NULL,
lecturer_nickname_  VARCHAR(50) NOT NULL,
lecturer_password_ VARCHAR(50) NOT NULL,
lecturer_email_ VARCHAR(50) NOT NULL,
PRIMARY KEY(lecturer_id_)
)

CREATE TABLE Student
(
student_id_ INT IDENTITY(1,1)  NOT NULL,
student_nickname_  VARCHAR(50) NOT NULL,
student_password_ VARCHAR(50) NOT NULL,
student_email_ VARCHAR(50) NOT NULL,
PRIMARY KEY(student_id_)
)


CREATE TABLE Course(
course_id INT IDENTITY(1,1)  NOT NULL,
course_description_  VARCHAR(50) NOT NULL,
course_lecturer_id int NOT NULL,
course_creation_time VARCHAR(50) NOT NULL,


PRIMARY KEY(course_id)
)

ALTER TABLE Course
WITH CHECK ADD CONSTRAINT FK_course_lecturer_id FOREIGN KEY(course_lecturer_id)
REFERENCES Lecturer(lecturer_id_);


create table  CourseSubscriptions
(
 course_subscription_id INT IDENTITY(1,1) not null,
 course_id int,
 student_id int,
 PRIMARY KEY(course_subscription_id)
);

ALTER TABLE CourseSubscriptions
WITH CHECK ADD CONSTRAINT FK_coursesubs_id FOREIGN KEY( course_id)
REFERENCES  Course( course_id);

ALTER TABLE CourseSubscriptions
WITH CHECK ADD CONSTRAINT FK_studentss_id FOREIGN KEY(student_id)
REFERENCES Student(student_id_ );


Create Table CourseLesson(
lesson_id INT IDENTITY(1,1)  NOT NULL,
lesson_description_  VARCHAR(50) NOT NULL,
lesson_creator VARCHAR(50) NOT NULL,
lesson_creation_time DATETIME NOT NULL,
course_id int
PRIMARY KEY(lesson_id)
)

ALTER TABLE CourseLesson
WITH CHECK ADD CONSTRAINT FK_courselesson_id FOREIGN KEY(course_id)
REFERENCES Course(course_id);

Create table LessonFiles
(
lesson_file_id INT IDENTITY(1,1)   NOT NULL,
file_content  Varbinary not null,
lesson_id INT,
file_creation_time DATETIME NOT NULL,
PRIMARY KEY(lesson_file_id)
)

ALTER TABLE LessonFiles
WITH CHECK ADD CONSTRAINT FK_lessonfile_id FOREIGN KEY(lesson_id)
REFERENCES CourseLesson(lesson_id);

Create table CourseChat
(
Chat_id INT IDENTITY(1,1)   NOT NULL,
course_id int,
creation_time DATETIME NOT NULL,
PRIMARY KEY(Chat_id)
)

ALTER TABLE CourseChat
WITH CHECK ADD CONSTRAINT FK_coursechat_id FOREIGN KEY(course_id)
REFERENCES Course(course_id);

Create table ChatMessages
(
message_id INT IDENTITY(1,1)   NOT NULL,
message_text Varchar(100) Not null,
chat_id int,
author_lecturer_id int ,
author_student_id int,
creation_time DATETIME NOT NULL,
PRIMARY KEY(message_id)
)

ALTER TABLE ChatMessages
WITH CHECK ADD CONSTRAINT FK_author_lecturer_id FOREIGN KEY(author_lecturer_id)
REFERENCES Lecturer(lecturer_id_);


ALTER TABLE ChatMessages
WITH CHECK ADD CONSTRAINT FK_author_student_id FOREIGN KEY(author_student_id)
REFERENCES Student(student_id_);

ALTER TABLE ChatMessages
WITH CHECK ADD CONSTRAINT FK_ChatMessageschat__id FOREIGN KEY(chat_id)
REFERENCES CourseChat(Chat_id);

Create table SearchTags
(
search_tag_id int IDENTITY(1,1)   NOT NULL,
search_tag_text Varchar(100) Not null,
PRIMARY KEY(search_tag_id)
)

Create table CourseSearchTags
(
course_search_tag_id int IDENTITY(1,1)   NOT NULL,
course_id int,
search_tag_id int,
PRIMARY KEY(course_search_tag_id)
)

ALTER TABLE CourseSearchTags
WITH CHECK ADD CONSTRAINT FK_coursesearch_id FOREIGN KEY(course_id)
REFERENCES Course(course_id);

ALTER TABLE CourseSearchTags
WITH CHECK ADD CONSTRAINT FK_coursesearchtags_id FOREIGN KEY(search_tag_id)
REFERENCES SearchTags(search_tag_id);

Create table LessonTestQuiz(
quiz_id int IDENTITY(1,1)   NOT NULL,
quiz_title Varchar(100) Not null,
lesson_id int
PRIMARY KEY(quiz_id)
);

ALTER TABLE LessonTestQuiz
WITH CHECK ADD CONSTRAINT FK_testlesson_id FOREIGN KEY(lesson_id)
REFERENCES  CourseLesson(lesson_id);

Create table TestQuizQuestions(
question_id int IDENTITY(1,1)   NOT NULL,
question_title Varchar(100) Not null,
quiz_id int
PRIMARY KEY(question_id)
);

ALTER TABLE TestQuizQuestions
WITH CHECK ADD CONSTRAINT FK_TestQuizQuestions_id FOREIGN KEY(quiz_id )
REFERENCES  LessonTestQuiz(quiz_id);

Create table QuestionsAnswerResult(
answer_result_id int IDENTITY(1,1)   NOT NULL,
answer Varchar(100) Not null,
result int Not null,
question_id int
PRIMARY KEY(answer_result_id)
);

ALTER TABLE QuestionsAnswerResult
WITH CHECK ADD CONSTRAINT FK_QuestionsAnswerResult_id FOREIGN KEY(question_id)
REFERENCES  TestQuizQuestions(question_id );


Create table TestQuizStudent(
pair_id int IDENTITY(1,1)   NOT NULL,
quiz_id int,
student_id int,

PRIMARY KEY(pair_id)
);

ALTER TABLE TestQuizStudent
WITH CHECK ADD CONSTRAINT FK_TestQuizStudent_id FOREIGN KEY(quiz_id)
REFERENCES  LessonTestQuiz(quiz_id);

ALTER TABLE TestQuizStudent
WITH CHECK ADD CONSTRAINT FK_TestQuizStudent555_id FOREIGN KEY(student_id)
REFERENCES  Student(student_id_);

Create table TestQuizStudentAnswers
(
    id int IDENTITY(1,1)   NOT NULL,
    quiz_question Varchar(100) Not null,
    student_answer  Varchar(100) Not null,
    TestQuizStudent_id int not null,
    PRIMARY KEY(id)
);

ALTER TABLE TestQuizStudentAnswers
WITH CHECK ADD CONSTRAINT FK_TestQuizStudentAnswers_id FOREIGN KEY(TestQuizStudent_id)
REFERENCES  TestQuizStudent(pair_id);