---

CREATE TABLE Faculties (
    FacultyID INT PRIMARY KEY,
    FacultyName NVARCHAR(100) NOT NULL
);

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName NVARCHAR(100) NOT NULL,
    FacultyID INT NOT NULL,
    FOREIGN KEY (FacultyID) REFERENCES Faculties(FacultyID)
);

CREATE TABLE Groups (
    GroupID INT PRIMARY KEY,
    GroupName NVARCHAR(20) NOT NULL,
    FacultyID INT NOT NULL,
    FOREIGN KEY (FacultyID) REFERENCES Faculties(FacultyID)
);

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    LastName NVARCHAR(50) NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    BirthDate DATE NOT NULL,
    GroupID INT NOT NULL,
    FOREIGN KEY (GroupID) REFERENCES Groups(GroupID)
);

CREATE TABLE Teachers (
    TeacherID INT PRIMARY KEY,
    LastName NVARCHAR(50) NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    DepartmentID INT NOT NULL,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

CREATE TABLE Subjects (
    SubjectID INT PRIMARY KEY,
    SubjectName NVARCHAR(100) NOT NULL,
    HoursPerWeek INT NOT NULL,
    DepartmentID INT NOT NULL,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

CREATE TABLE Classrooms (
    ClassroomID INT PRIMARY KEY,
    ClassroomName NVARCHAR(20) NOT NULL,
    Capacity INT NOT NULL
);

CREATE TABLE Schedules (
    ScheduleID INT PRIMARY KEY,
    SubjectID INT NOT NULL,
    TeacherID INT NOT NULL,
    GroupID INT NOT NULL,
    ClassroomID INT NOT NULL,
    LessonDate DATE NOT NULL,
    FOREIGN KEY (SubjectID) REFERENCES Subjects(SubjectID),
    FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID),
    FOREIGN KEY (GroupID) REFERENCES Groups(GroupID),
    FOREIGN KEY (ClassroomID) REFERENCES Classrooms(ClassroomID)
);

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT NOT NULL,
    SubjectID INT NOT NULL,
    EnrollmentDate DATE NOT NULL,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (SubjectID) REFERENCES Subjects(SubjectID)
);

CREATE TABLE Grades (
    GradeID INT PRIMARY KEY,
    StudentID INT NOT NULL,
    SubjectID INT NOT NULL,
    Grade INT NOT NULL,
    GradeDate DATE NOT NULL,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (SubjectID) REFERENCES Subjects(SubjectID)
);
-- 1. Факультеты (Faculties)
INSERT INTO Faculties (FacultyID, FacultyName) VALUES
(1, 'Информационные технологии'),
(2, 'Экономика и финансы'),
(3, 'Гуманитарные науки'),
(4, 'Инженерный факультет'),
(5, 'Юридический факультет'),
(6, 'Естественные науки'),
(7, 'Факультет дизайна');

-- 2. Кафедры (Departments)
INSERT INTO Departments (DepartmentID, DepartmentName, FacultyID) VALUES
(1, 'ПОИТ', 1), (2, 'Информационная безопасность', 1),
(3, 'Бухгалтерский учет', 2), (4, 'Лингвистика', 3),
(5, 'Робототехника', 4), (6, 'Гражданское право', 5),
(7, 'Высшая математика', 6);

-- 3. Группы (Groups)
INSERT INTO Groups (GroupID, GroupName, FacultyID) VALUES
(1, 'ИТ-21', 1), (2, 'ИТ-22', 1),
(3, 'ЭК-10', 2), (4, 'ЛИН-1', 3),
(5, 'РОБ-5', 4), (6, 'ЮР-12', 5),
(7, 'ФИЗ-4', 6);

-- 4. Студенты (Students)
INSERT INTO Students (StudentID, LastName, FirstName, BirthDate, GroupID) VALUES
(1, 'Иванов', 'Иван', '2005-04-12', 1),
(2, 'Петрова', 'Анна', '2004-11-23', 1),
(3, 'Сидоров', 'Олег', '2005-01-30', 2),
(4, 'Кузнецова', 'Мария', '2006-03-15', 3),
(5, 'Васильев', 'Артем', '2004-09-10', 4),
(6, 'Смирнова', 'Елена', '2005-12-05', 5),
(7, 'Попов', 'Дмитрий', '2005-06-20', 6);

-- 5. Преподаватели (Teachers)
INSERT INTO Teachers (TeacherID, LastName, FirstName, DepartmentID) VALUES
(1, 'Белов', 'Сергей', 1), (2, 'Чернов', 'Игорь', 2),
(3, 'Морозов', 'Виктор', 3), (4, 'Павлова', 'Ольга', 4),
(5, 'Соколов', 'Андрей', 5), (6, 'Никитин', 'Лев', 6),
(7, 'Орлова', 'Инна', 7);

-- 6. Предметы (Subjects)
INSERT INTO Subjects (SubjectID, SubjectName, HoursPerWeek, DepartmentID) VALUES
(1, 'Алгоритмы', 6, 1), (2, 'Криптография', 4, 2),
(3, 'Аудит', 2, 3), (4, 'Английский язык', 4, 4),
(5, 'Механика', 4, 5), (6, 'Теория права', 3, 6),
(7, 'Матанализ', 8, 7);

-- 7. Аудитории (Classrooms)
INSERT INTO Classrooms (ClassroomID, ClassroomName, Capacity) VALUES
(1, '101-А', 30), (2, '102-А', 25),
(3, '205-Б', 40), (4, 'Лаб-3', 15),
(5, 'Актовый зал', 200), (6, '301', 50),
(7, 'Чит.зал', 80);

-- 8. Расписание (Schedules)
INSERT INTO Schedules (ScheduleID, SubjectID, TeacherID, GroupID, ClassroomID, LessonDate) VALUES
(1, 1, 1, 1, 1, '2026-09-01'), (2, 2, 2, 2, 4, '2026-09-01'),
(3, 3, 3, 3, 2, '2026-09-02'), (4, 4, 4, 4, 3, '2026-09-02'),
(5, 5, 5, 5, 5, '2026-09-03'), (6, 6, 6, 6, 6, '2026-09-03'),
(7, 7, 7, 7, 7, '2026-09-04');

-- 9. Зачисления (Enrollments)
INSERT INTO Enrollments (EnrollmentID, StudentID, SubjectID, EnrollmentDate) VALUES
(1, 1, 1, '2026-08-25'), (2, 2, 1, '2026-08-25'),
(3, 3, 2, '2026-08-26'), (4, 4, 3, '2026-08-26'),
(5, 5, 4, '2026-08-27'), (6, 6, 5, '2026-08-27'),
(7, 7, 6, '2026-08-28');

-- 10. Оценки (Grades)
INSERT INTO Grades (GradeID, StudentID, SubjectID, Grade, GradeDate) VALUES
(1, 1, 1, 5, '2026-12-15'), (2, 2, 1, 4, '2026-12-15'),
(3, 3, 2, 5, '2026-12-16'), (4, 4, 3, 3, '2026-12-17'),
(5, 5, 4, 5, '2026-12-18'), (6, 6, 5, 4, '2026-12-19'),
(7, 7, 6, 5, '2026-12-20');