DROP DATABASE IF EXISTS school;
CREATE DATABASE school CHARSET 'utf8';
USE school;

CREATE TABLE Students(
	Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	Name VARCHAR(150) NOT NULL,
	Num INTEGER NOT NULL,
	ClassNum INTEGER NOT NULL,
	ClassLetter CHAR(1) NOT NULL,
	Birthday DATE,
	EGN CHAR(10),
	EntranceExamResult NUMERIC(3,2)
);

CREATE TABLE Subjects(
	Id INTEGER NOT NULL AUTO_INCREMENT,
	Name VARCHAR(100),

	PRIMARY KEY(Id)
);

CREATE TABLE StudentMarks(
	StudentId INTEGER NOT NULL,
	SubjectId INTEGER NOT NULL,
	ExamDate DATETIME NOT NULL,
	Mark NUMERIC(3,2) NOT NULL,

	PRIMARY KEY( StudentId, SubjectId, ExamDate ),
	FOREIGN KEY (StudentId) REFERENCES Students(Id),
	FOREIGN KEY (SubjectId) REFERENCES Subjects(Id)
);

CREATE TABLE MarkWords(
	RangeStart NUMERIC(3,2) NOT NULL,
	RangeEnd NUMERIC(3,2) NOT NULL,
	MarkAsWord VARCHAR(15),

	PRIMARY KEY(RangeStart, RangeEnd)
);

CREATE TABLE H_Students(
	UID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	EventDate DATETIME NOT NULL,
    EventType VARCHAR(10),
    Id INTEGER NOT NULL,
    Name VARCHAR(100) NOT NULL,
    EGN VARCHAR(10)
);

CREATE TABLE H_StudentMarks(
	UID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    EventDate DATETIME NOT NULL,
    EventType VARCHAR(10) NOT NULL,
    StId INTEGER NOT NULL,
    SuId INTEGER NOT NULL,
    Mark NUMERIC(3,2) NOT NULL
);

CREATE TABLE EventType(
	Id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(10)
);

delimiter |
CREATE TRIGGER StudentsUpdate BEFORE UPDATE ON Students
  FOR EACH ROW
  BEGIN
    INSERT INTO H_Students(EventDate,EventType,Id,Name,EGN) VALUES (NOW() + 1,'UPDATE',NEW.Id,NEW.Name,NEW.EGN);
  END
|
CREATE TRIGGER StudentsInsert AFTER INSERT ON Students
  FOR EACH ROW
  BEGIN
    INSERT INTO H_Students(EventDate,EventType,Id,Name,EGN) VALUES (NOW(),'INSERT',NEW.Id,NEW.Name,NEW.EGN);
  END
|
CREATE TRIGGER StudentsDelete BEFORE DELETE ON Students
  FOR EACH ROW
  BEGIN
    INSERT INTO H_Students(EventDate,EventType,Id,Name,EGN) VALUES (NOW() + 2,'DELETE',Id,Name,EGN);
  END
|
CREATE TRIGGER StudentMarksUpdate BEFORE UPDATE ON StudentMarks
  FOR EACH ROW
  BEGIN
    INSERT INTO H_StudentMarks(EventDate,EventType,StId,SuId,Mark) VALUES (NOW()+1,'UPDATE',NEW.StudentId,NEW.SubjectId,NEW.Mark);
  END
|
CREATE TRIGGER StudentMarksInsert AFTER INSERT ON StudentMarks
  FOR EACH ROW
  BEGIN
    INSERT INTO H_StudentMarks(EventDate,EventType,StId,SuId,Mark) VALUES (NOW(),'INSERT',NEW.StudentId,NEW.SubjectId,NEW.Mark);
  END
|

CREATE TRIGGER StudentMarksDelete BEFORE DELETE ON StudentMarks
  FOR EACH ROW
  BEGIN
    INSERT INTO H_StudentMarks(EventDate,EventType,StId,SuId,Mark) VALUES (NOW()+2,'INSERT',StudentId,SubjectId,Mark);
  END
|
delimiter ;


INSERT INTO Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 101, 'Зюмбюл Петров', 10, 11, 'а', '1999-02-28', NULL );
INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Исидор Иванов', 15, 10, 'б', '2000-02-29', '0042294120' );
INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Панчо Лалов', 20, 10, 'б', '2000-05-01', NULL );
INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );
INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Александър Момчев', 1, 8, 'а', '2002-06-11', NULL );

UPDATE Students
SET ClassLetter = 'г'
WHERE Id > 100;


INSERT INTO Subjects(Id, Name) VALUES( 11, 'Английски език' );
INSERT INTO Subjects(Name) VALUES( 'Литература' );
INSERT INTO Subjects(Name) VALUES( 'Математика' );
INSERT INTO Subjects(Name) VALUES( 'СУБД' );

INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-03', 6 );
INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-31', 5.50 );
INSERT INTO StudentMarks VALUES( 102, 11, '2017-04-28', 5 );
INSERT INTO StudentMarks VALUES( 103, 12, '2017-04-28', 4 );
INSERT INTO StudentMarks VALUES( 104, 13, '2017-03-03', 5 );
INSERT INTO StudentMarks VALUES( 104, 13, '2017-04-07', 6 );
INSERT INTO StudentMarks VALUES( 104, 11, '2017-04-07', 4.50 );

INSERT INTO MarkWords VALUES( 2, 2.50, 'Слаб' );
INSERT INTO MarkWords VALUES( 2.50, 3.50, 'Среден' );
INSERT INTO MarkWords VALUES( 3.50, 4.50, 'Добър' );
INSERT INTO MarkWords VALUES( 4.50, 5.50, 'Мн. добър' );
INSERT INTO MarkWords VALUES( 5.50, 6, 'Отличен' );

SELECT * FROM H_Students
WHERE Id = 101;

SELECT * FROM H_Students
WHERE Id = 102
ORDER BY EventDate DESC
LIMIT 1;

SELECT * FROM( SELECT * FROM H_Students ORDER BY EventDate DESC) as d
GROUP BY Id;