BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "Building" (
	"Id"	INTEGER NOT NULL UNIQUE,
	"Name"	TEXT NOT NULL,
	"Faculty"	TEXT NOT NULL,
	"Address"	TEXT,
	PRIMARY KEY("Id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "SemesterType" (
	"Id"	INTEGER NOT NULL UNIQUE,
	"Name"	TEXT NOT NULL,
	PRIMARY KEY("Id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "LessonType" (
	"Id"	INTEGER NOT NULL UNIQUE,
	"Abbreviation"	TEXT NOT NULL,
	"Description"	TEXT,
	"ColorHexcode"	TEXT NOT NULL DEFAULT '#ffffff',
	PRIMARY KEY("Id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "Room" (
	"Id"	INTEGER NOT NULL UNIQUE,
	"Name"	TEXT NOT NULL,
	"BuildingId"	INTEGER NOT NULL,
	"RoomTypeId"	INTEGER,
	"Description"	TEXT,
	"Capacity"	INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY("Id" AUTOINCREMENT),
	FOREIGN KEY("BuildingId") REFERENCES "Building"("Id") ON DELETE RESTRICT ON UPDATE CASCADE,
	FOREIGN KEY("RoomTypeId") REFERENCES "RoomType"("Id") ON DELETE SET NULL ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS "LessonRoom" (
	"LessonId"	INTEGER NOT NULL,
	"RoomId"	INTEGER NOT NULL,
	PRIMARY KEY("LessonId","RoomId"),
	FOREIGN KEY("RoomId") REFERENCES "Room"("Id") ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY("LessonId") REFERENCES "Lesson"("Id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS "LessonStudentGroup" (
	"LessonId"	INTEGER NOT NULL,
	"StudentGroupId"	INTEGER NOT NULL,
	PRIMARY KEY("LessonId","StudentGroupId"),
	FOREIGN KEY("LessonId") REFERENCES "Lesson"("Id") ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY("StudentGroupId") REFERENCES "StudentGroup"("Id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS "LessonTeacher" (
	"LessonId"	INTEGER NOT NULL,
	"TeacherId"	INTEGER NOT NULL,
	PRIMARY KEY("LessonId","TeacherId"),
	FOREIGN KEY("TeacherId") REFERENCES "Teacher"("Id") ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY("LessonId") REFERENCES "Lesson"("Id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS "CourseHours" (
	"Id"	INTEGER NOT NULL UNIQUE,
	"LessonTypeId"	INTEGER NOT NULL,
	"CourseId"	INTEGER NOT NULL,
	"Hours"	INTEGER NOT NULL DEFAULT 2,
	PRIMARY KEY("Id" AUTOINCREMENT),
	FOREIGN KEY("CourseId") REFERENCES "Course"("Id") ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY("LessonTypeId") REFERENCES "LessonType"("Id") ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS "Semester" (
	"Id"	INTEGER NOT NULL UNIQUE,
	"SemesterTypeId"	INTEGER NOT NULL,
	"DateStartDT"	TEXT NOT NULL,
	"DateEndDT"	TEXT NOT NULL,
	"AcademicStartYear"	INTEGER NOT NULL,
	PRIMARY KEY("Id" AUTOINCREMENT),
	FOREIGN KEY("SemesterTypeId") REFERENCES "SemesterType"("Id") ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS "StudentGroup" (
	"Id"	INTEGER NOT NULL UNIQUE,
	"CurriculumId"	INTEGER NOT NULL,
	"SemesterId"	INTEGER NOT NULL,
	"Name"	TEXT NOT NULL,
	"Capacity"	INTEGER NOT NULL DEFAULT 20,
	"YearOfStudy"	INTEGER NOT NULL DEFAULT 1,
	PRIMARY KEY("Id" AUTOINCREMENT),
	FOREIGN KEY("CurriculumId") REFERENCES "Curriculum"("Id") ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY("SemesterId") REFERENCES "Semester"("Id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS "CurriculumCourse" (
	"CurriculumId"	INTEGER NOT NULL,
	"CourseId"	INTEGER NOT NULL,
	"YearOfStudy"	INTEGER NOT NULL DEFAULT 1,
	"SemesterTypeId"	INTEGER NOT NULL DEFAULT 1,
	PRIMARY KEY("CurriculumId","CourseId"),
	FOREIGN KEY("CurriculumId") REFERENCES "Curriculum"("Id") ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY("SemesterTypeId") REFERENCES "SemesterType"("Id") ON DELETE RESTRICT ON UPDATE CASCADE,
	FOREIGN KEY("CourseId") REFERENCES "Course"("Id") ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "Course" (
	"Id"	INTEGER NOT NULL UNIQUE,
	"Abbreviation"	TEXT NOT NULL,
	"Name"	TEXT,
	PRIMARY KEY("Id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "Lesson" (
	"Id"	INTEGER NOT NULL UNIQUE,
	"CourseId"	INTEGER NOT NULL,
	"LessonTypeId"	INTEGER NOT NULL,
	"Periodicity"	INTEGER NOT NULL DEFAULT 1,
	"PeriodStart"	INTEGER NOT NULL DEFAULT 0,
	"Hours"	INTEGER NOT NULL DEFAULT 2,
	"StartDateDT"	TEXT NOT NULL,
	"Note"	TEXT,
	"SemesterId"	INTEGER NOT NULL DEFAULT 1,
	FOREIGN KEY("LessonTypeId") REFERENCES "LessonType"("Id") ON UPDATE CASCADE ON DELETE RESTRICT,
	FOREIGN KEY("SemesterId") REFERENCES "Semester"("Id") ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY("CourseId") REFERENCES "Course"("Id") ON UPDATE CASCADE ON DELETE CASCADE,
	PRIMARY KEY("Id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "RoomType" (
	"Id"	INTEGER NOT NULL UNIQUE,
	"Name"	TEXT NOT NULL,
	"Description"	TEXT,
	PRIMARY KEY("Id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "Curriculum" (
	"Id"	INTEGER NOT NULL UNIQUE,
	"Abbreviation"	TEXT NOT NULL,
	"Name"	TEXT NOT NULL,
	"Faculty"	TEXT NOT NULL,
	"Length"	INTEGER NOT NULL DEFAULT 3,
	"AccreditStartDT"	TEXT NOT NULL,
	"AccreditEndDT"	TEXT NOT NULL,
	PRIMARY KEY("Id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "Teacher" (
	"Id"	INTEGER NOT NULL UNIQUE,
	"FirstName"	TEXT NOT NULL,
	"LastName"	TEXT NOT NULL,
	"Ranks"	TEXT,
	PRIMARY KEY("Id" AUTOINCREMENT)
);
INSERT INTO "RoomType" VALUES (1,'Běžná',NULL);
INSERT INTO "RoomType" VALUES (2,'Přednášková místnost',NULL);
INSERT INTO "RoomType" VALUES (3,'Laboratoř',NULL);
INSERT INTO "RoomType" VALUES (4,'Ateliér',NULL);
INSERT INTO "RoomType" VALUES (5,'PC učebna',NULL);
INSERT INTO "SemesterType" VALUES (1,'Zimní');
INSERT INTO "SemesterType" VALUES (2,'Letní');
INSERT INTO "LessonType" VALUES (0,'X','Nedostupný','#222222');
INSERT INTO "LessonType" VALUES (1,'P','Přednáška','#9cff9c');
INSERT INTO "LessonType" VALUES (2,'C1','Cvičení','#ccffff');
INSERT INTO "LessonType" VALUES (3,'C2a','Cvičení na PC','#ffffcc');
INSERT INTO "LessonType" VALUES (4,'C2b','Laboratoře a ateliéry','#ffe5b7');
INSERT INTO "LessonType" VALUES (5,'CPP','Cvičení s počítačovou podporou','#ffffff');
INSERT INTO "LessonType" VALUES (6,'L','Laboratorní cvičení','#ffe5b7');
INSERT INTO "LessonType" VALUES (7,'KK','Konzultace v kombinovaném studiu','#ffffff');
INSERT INTO "LessonType" VALUES (8,'K','Konzultace','#ffffff');
INSERT INTO "LessonType" VALUES (9,'Cj','Cvičení jazykové','#ffffcc');
INSERT INTO "LessonType" VALUES (10,'VB','Vedení bakalářské práce','#ffffcc');
INSERT INTO "LessonType" VALUES (11,'VD','Vedení diplomové práce','#ffffcc');
INSERT INTO "LessonType" VALUES (12,'A','Ateliér','#ffe5b7');
INSERT INTO "LessonType" VALUES (13,'Z','Zkouška','#ffffff');
INSERT INTO "LessonType" VALUES (14,'Cp','Cvičení na počítači','#ffffcc');
INSERT INTO "LessonType" VALUES (15,'PX','Odborná praxe','#ffffff');
CREATE VIEW LessonMN AS SELECT Id as LessonId,TeacherId,RoomId,StudentGroupId FROM Lesson 
LEFT OUTER JOIN LessonTeacher ON LessonTeacher.LessonId = Lesson.Id
LEFT OUTER JOIN LessonRoom ON LessonRoom.LessonId = Lesson.Id
LEFT OUTER JOIN LessonStudentGroup ON LessonStudentGroup.LessonId = Lesson.Id;
CREATE VIEW CourseCheck AS
SELECT c.Id as "CourseId", c.Abbreviation as "CourseAbbreviation", lt.Id as "LessonTypeId", lt.Abbreviation as "LessonType",ch.Hours as "MinHours" FROM CourseHours AS ch 
JOIN LessonType AS lt ON lt.Id = ch.LessonTypeId 
JOIN Course AS c ON c.Id = ch.CourseId;
COMMIT;
