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 StudentsHistory (
	StudentId INTEGER,
	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),
    Id INTEGER NOT NULL PRIMARY KEY,
    TypeOfChange CHAR NOT NULL,
	TimeOfChange DATETIME NOT NULL
);


delimiter |

CREATE TRIGGER HistoryInsert AFTER INSERT ON Students
  FOR EACH ROW
  BEGIN
    INSERT INTO StudentsHistory(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN, TypeOfChange, TimeOfChange) VALUES(NEW.Id, NEW.Name, NEW.Num, NEW.ClassNum, NEW.ClassLetter, NEW.Birthday, NEW.EGN, 'I', NOW());
  END;
|

delimiter ;

delimiter |

CREATE TRIGGER HistoryUpdate AFTER UPDATE ON Students
  FOR EACH ROW
  BEGIN
    INSERT INTO StudentsHistory(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN, TypeOfChange, TimeOfChange) VALUES(NEW.Id, NEW.Name, NEW.Num, NEW.ClassNum, NEW.ClassLetter, NEW.Birthday, NEW.EGN, 'U', NOW());
  END;
|

delimiter ;

delimiter |

CREATE TRIGGER HistoryDelete AFTER DELETE ON Students
  FOR EACH ROW
  BEGIN
    INSERT INTO StudentsHistory(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN, TypeOfChange, TimeOfChange) VALUES(OLD.Id, OLD.Name, OLD.Num, OLD.ClassNum, OLD.ClassLetter, OLD.Birthday, OLD.EGN, 'U', NOW());
  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 );

INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES('Зюмбюл Петров', 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 );


SELECT * FROM StudentsHistory
WHERE StudentsHistory.Name = 'Зюмбюл Петров'
ORDER BY TimeOfChange ASC;

SELECT * FROM StudentsHistory
WHERE StudentsHistory.Id = 101
ORDER BY TimeOfChange DESC
LIMIT 1;

SELECT hs.Num, hs.Name
FROM StudentsHistory hs
LEFT JOIN StudentsHistory later
ON hs.id = later.id AND
	hs.TimeOfChange < later.TimeOfChange
WHERE later.id IS NULL AND
	hs.TypeOfChange <> 'D';