--Для Специальностей
DROP TABLE IF EXISTS Specializations CASCADE;
CREATE TABLE Specializations (
    ID INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Code VARCHAR(16),
    Name VARCHAR(16),
    Form VARCHAR(16),
    Level VARCHAR(16),
    Duration INTEGER,
    Describtion VARCHAR(64)
);

    INSERT INTO Specializations (Code, Name, Form, Level, Duration, Describtion) VALUES ('09.03.01', 'IVT', 'Full','Bachelor', 8, '-');
    INSERT INTO Specializations (Code, Name, Form, Level, Duration, Describtion) VALUES ('09.04.01', 'IVT', 'Full','Master', 4, '-');
    INSERT INTO Specializations (Code, Name, Form, Level, Duration, Describtion) VALUES ('09.03.02', 'ICIT', 'Full','Bachelor', 8, '-');
    INSERT INTO Specializations (Code, Name, Form, Level, Duration, Describtion) VALUES ('09.03.03', 'PI', 'Full','Bachelor', 8, '-');
    INSERT INTO Specializations (Code, Name, Form, Level, Duration, Describtion) VALUES ('-', '-', '-','-', 1, '-');
    INSERT INTO Specializations (Code, Name, Form, Level, Duration, Describtion) VALUES ('-', '-', '-','-', 1, '-');



--Для групп
DROP TABLE IF EXISTS Groupa CASCADE;
CREATE TABLE Groupa (
    ID INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Name VARCHAR(16),
    Specialization INTEGER references  Specializations (ID),
    Elder integer,
    Tutor integer
);

INSERT INTO Groupa (Name, Specialization, Elder, Tutor) VALUES ('23-01B', 1, 1, 5);
INSERT INTO Groupa (Name, Specialization, Elder, Tutor) VALUES ('23-01M', 2, 7, 9);
INSERT INTO Groupa (Name, Specialization, Elder, Tutor) VALUES ('23-03B', 3, 16, 14);
INSERT INTO Groupa (Name, Specialization, Elder, Tutor) VALUES ('23-04B', 3, 24, 25);





--Для студентов
DROP TABLE IF EXISTS Student CASCADE;
CREATE TABLE Student (
    ID INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Surname VARCHAR(16),
    Name VARCHAR(16),
    Midname VARCHAR(16),
    Year VARCHAR(4),
    ID_Group integer references Groupa (ID)
);

INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('A', 'A', 'A', '2023', 1);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('B', 'R', 'E', '2023', 1);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('P', 'C', 'B', '2023', 1);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('U', 'B', 'C', '2023', 1);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('E', 'A', 'A', '2023', 1);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('W', 'N', 'A', '2023', 1);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('P', 'N', 'A', '2023', 1);

INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('A', 'A', 'A', '2023', 2);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('B', 'G', 'X', '2023', 2);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('C', 'R', 'N', '2023', 2);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('R', 'M', 'N', '2023', 2);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('D', 'V', 'F', '2023', 2);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('S', 'T', 'M', '2023', 2);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('Q', 'I', 'A', '2023', 2);

INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('SACX', 'N', 'DFC', '2023', 3);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('ASCZ', 'R', 'SC', '2023', 3);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('WERF', 'S', 'A', '2023', 3);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('RFVC', 'N', 'A', '2023', 3);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('RTG', 'C', 'A', '2023', 3);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('DFV', 'AD', 'A', '2023', 3);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('ASOP', 'ASD', 'A', '2023', 3);

INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('VET', 'SEM', 'B', '2023', 4);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('SCU', 'MIC', 'D', '2023', 4);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('HAM', 'LEW', 'R', '2023', 4);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('CVS', 'PKC', 'M', '2023', 4);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('VSSD', 'A', 'P', '2023', 4);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('MAZ', 'A', 'M', '2023', 4);
INSERT INTO Student (Surname, Name, Midname, Year, ID_Group) VALUES ('VER', 'MAX', 'K', '2023', 4);


--Для Дисциплин
DROP TABLE IF EXISTS Subject CASCADE;
CREATE TABLE Subject (
    ID SERIAL PRIMARY KEY,
    Name VARCHAR(16),
    Specialization Integer references  Specializations (ID),
    Semester INTEGER,
    Duration INTEGER,
    Control VARCHAR(16)
);

INSERT INTO Subject (ID, Name, Specialization, Semester, Duration, Control) VALUES (0, 'Algem', 1, 1, 1, 'Exam');
INSERT INTO Subject (ID, Name, Specialization, Semester, Duration, Control) VALUES (1, 'Matan', 1, 1, 1, 'Test');
INSERT INTO Subject (ID, Name, Specialization, Semester, Duration, Control) VALUES (2, 'OP', 1, 1, 1, 'Test');
INSERT INTO Subject (ID, Name, Specialization, Semester, Duration, Control) VALUES (3, 'Inf', 1, 1, 1, 'Exam');
INSERT INTO Subject (ID, Name, Specialization, Semester, Duration, Control) VALUES (4, 'Hist', 1, 1, 1, 'Test');

INSERT INTO Subject (ID, Name, Specialization, Semester, Duration, Control) VALUES (5, 'Algem', 1, 2, 1, 'Exam');
INSERT INTO Subject (ID, Name, Specialization, Semester, Duration, Control) VALUES (6, 'Matan', 1, 2, 1, 'Exam');
INSERT INTO Subject (ID, Name, Specialization, Semester, Duration, Control) VALUES (7, 'OP', 1, 2, 1, 'Test');
INSERT INTO Subject (ID, Name, Specialization, Semester, Duration, Control) VALUES (8, 'Inf', 1, 2, 1, 'Exam');
INSERT INTO Subject (ID, Name, Specialization, Semester, Duration, Control) VALUES (9, 'Hist', 1, 2, 1, 'Test');

INSERT INTO Subject (ID, Name, Specialization, Semester, Duration, Control) VALUES (10, 'A', 2, 1, 1, 'Exam');
INSERT INTO Subject (ID, Name, Specialization, Semester, Duration, Control) VALUES (11, 'B', 2, 1, 1, 'Exam');
INSERT INTO Subject (ID, Name, Specialization, Semester, Duration, Control) VALUES (12, 'C', 2, 1, 1, 'Test');
INSERT INTO Subject (ID, Name, Specialization, Semester, Duration, Control) VALUES (13, 'D', 2, 1, 1, 'Test');
INSERT INTO Subject (ID, Name, Specialization, Semester, Duration, Control) VALUES (14, 'E', 2, 1, 1, 'Test');

INSERT INTO Subject (ID, Name, Specialization, Semester, Duration, Control) VALUES (15, 'C', 2, 2, 1, 'Exam');
INSERT INTO Subject (ID, Name, Specialization, Semester, Duration, Control) VALUES (16, 'Aa', 2, 2, 1, 'Exam');
INSERT INTO Subject (ID, Name, Specialization, Semester, Duration, Control) VALUES (17, 'Bb', 2, 2, 1, 'Test');
INSERT INTO Subject (ID, Name, Specialization, Semester, Duration, Control) VALUES (18, 'Cc', 2, 2, 1, 'Test');
INSERT INTO Subject (ID, Name, Specialization, Semester, Duration, Control) VALUES (19, 'Dd', 2, 2, 1, 'Test');

--Teacher
DROP TABLE IF EXISTS  Teacher CASCADE;
CREATE TABLE Teacher (
    ID SERIAL PRIMARY KEY,
    Surname VARCHAR(16),
    Name VARCHAR(16),
    Midname VARCHAR(16),
    Degree VARCHAR(16),
    Title VARCHAR(16),
    Chair VARCHAR(64)
);


INSERT INTO Teacher (Surname, Name, Midname, Degree, Title, Chair) VALUES ('Vettel', 'Seb', 'B', 'Ph. D', 'Docent', 'VT');
INSERT INTO Teacher (Surname, Name, Midname, Degree, Title, Chair) VALUES ('Scumaher', 'Michael', 'D', 'Ph. D', 'Docent', 'ICIT');
INSERT INTO Teacher (Surname, Name, Midname, Degree, Title, Chair) VALUES ('Hamiltom', 'Lewis', 'R', 'Ph. D', 'Docent', 'PI');
INSERT INTO Teacher (Surname, Name, Midname, Degree, Title, Chair) VALUES ('CVSFC', 'PKCCX', 'MFF', 'Master', 'Lecturer', 'VT');
INSERT INTO Teacher (Surname, Name, Midname, Degree, Title, Chair) VALUES ('OJS', 'ZXV', 'N', 'Master', 'Lecturer', 'VT');
INSERT INTO Teacher (Surname, Name, Midname, Degree, Title, Chair) VALUES ('MAZCS', 'ACSZ', 'MSCX', 'Master', 'Lecturer', 'PI');
INSERT INTO Teacher (Surname, Name, Midname, Degree, Title, Chair) VALUES ('VERSC', 'MAXCS', 'KCS', 'Ph.D', 'Lecturer', 'ICIT');

--Teaching
DROP TABLE IF EXISTS Teaching CASCADE;
CREATE TABLE Teaching (
    ID_Subject INTEGER references Subject(ID),
    Number Integer references teacher(ID)
);

--Learning
DROP TABLE IF EXISTS Learning CASCADE;
CREATE TABLE Learning (
    ID_Student INTEGER references Student(ID),
    ID_Subject INTEGER references Subject(ID),
    Date DATE,
    Mark VARCHAR(16)
);


Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(1, 1, '2023-12-28', 'Pass');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(2, 1, '2023-12-28', 'Pass');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(3, 1, '2023-12-28', 'Pass');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(4, 1, '2023-12-28', 'Pass');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(5, 1, '2023-12-28', 'Pass');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(6, 1, '2023-12-28', 'Pass');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(7, 1, '2023-12-28', 'Not pass');

Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(1, 2, '2024-01-10', 'Pass');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(2, 2, '2024-01-10', 'Pass');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(3, 2, '2024-01-10', 'Pass');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(4, 2, '2024-01-10', 'Pass');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(5, 2, '2024-01-10', 'Pass');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(6, 2, '2024-01-10', 'Pass');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(7, 2, '2024-01-10', 'Not pass');

Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(1, 4, '2024-01-11', 'Pass');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(2, 4, '2024-01-11', 'Pass');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(3, 4, '2024-01-11', 'Pass');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(4, 4, '2024-01-11', 'Pass');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(5, 4, '2024-01-11', 'Pass');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(6, 4, '2024-01-11', 'Pass');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(7, 4, '2024-01-11', 'Not pass');

Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(1, 0, '2024-01-15', 'Exc');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(2, 0, '2024-01-15', 'Exc');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(3, 0, '2024-01-15', 'Good');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(4, 0, '2024-01-15', 'Sat');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(5, 0, '2024-01-15', 'Sat');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(6, 0, '2024-01-15', 'Fail');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(7, 0, '2024-01-15', 'Fail');

Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(1, 3, '2024-01-19', 'Exc');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(2, 3, '2024-01-19', 'Exc');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(3, 3, '2024-01-19', 'Exc');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(4, 3, '2024-01-19', 'Exc');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(5, 3, '2024-01-19', 'Good');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(6, 3, '2024-01-19', 'Good');
Insert into Learning(ID_Student, ID_Subject, Date, Mark) values(7, 3, '2024-01-19', 'Fail');