CREATE TABLE faculties (
    faculty_id SERIAL PRIMARY KEY,
    faculty_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    faculty_id INTEGER NOT NULL REFERENCES faculties(faculty_id),
    department_name VARCHAR(150) NOT NULL
);

CREATE TABLE groups (
    group_id SERIAL PRIMARY KEY,
    faculty_id INTEGER NOT NULL REFERENCES faculties(faculty_id),
    group_name VARCHAR(20) NOT NULL,
    course INTEGER NOT NULL CHECK(course BETWEEN 1 AND 6)
);

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    group_id INTEGER NOT NULL REFERENCES groups(group_id),
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50),
    gender VARCHAR(10) CHECK(gender IN ('М','Ж')),
    birth_date DATE NOT NULL,
    scholarship NUMERIC(10,2) DEFAULT 0,
    has_children BOOLEAN DEFAULT FALSE
);

CREATE TABLE teachers (
    teacher_id SERIAL PRIMARY KEY,
    department_id INTEGER NOT NULL REFERENCES departments(department_id),
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50),
    category VARCHAR(50) NOT NULL,
    salary NUMERIC(10,2) NOT NULL CHECK(salary > 0),
    academic_degree VARCHAR(50)
);

CREATE TABLE disciplines (
    discipline_id SERIAL PRIMARY KEY,
    discipline_name VARCHAR(150) NOT NULL UNIQUE
);

CREATE TABLE semesters (
    semester_id SERIAL PRIMARY KEY,
    semester_number INTEGER NOT NULL CHECK(semester_number BETWEEN 1 AND 12),
    academic_year VARCHAR(20) NOT NULL
);

CREATE TABLE teaching_assignments (
    assignment_id SERIAL PRIMARY KEY,
    teacher_id INTEGER NOT NULL REFERENCES teachers(teacher_id),
    discipline_id INTEGER NOT NULL REFERENCES disciplines(discipline_id),
    group_id INTEGER NOT NULL REFERENCES groups(group_id),
    semester_id INTEGER NOT NULL REFERENCES semesters(semester_id),
    lecture_hours INTEGER DEFAULT 0,
    practice_hours INTEGER DEFAULT 0,
    lab_hours INTEGER DEFAULT 0
);

CREATE TABLE exams (
    exam_id SERIAL PRIMARY KEY,
    student_id INTEGER NOT NULL REFERENCES students(student_id),
    discipline_id INTEGER NOT NULL REFERENCES disciplines(discipline_id),
    teacher_id INTEGER NOT NULL REFERENCES teachers(teacher_id),
    semester_id INTEGER NOT NULL REFERENCES semesters(semester_id),
    exam_date DATE NOT NULL,
    grade INTEGER CHECK(grade BETWEEN 2 AND 5)
);

-- Заполнение таблицы students
INSERT INTO students
(group_id, last_name, first_name, middle_name, gender, birth_date, scholarship, has_children)
VALUES
(1, 'Иванов', 'Артём', 'Сергеевич', 'М', '2004-03-15', 15000, FALSE),
(1, 'Петров', 'Даниил', 'Олегович', 'М', '2003-07-22', 12000, FALSE),
(1, 'Смирнова', 'Анна', 'Игоревна', 'Ж', '2004-11-10', 18000, FALSE),
(2, 'Кузнецов', 'Максим', 'Андреевич', 'М', '2005-01-18', 0, FALSE),
(2, 'Васильева', 'Мария', 'Дмитриевна', 'Ж', '2004-05-30', 10000, FALSE),
(2, 'Попов', 'Егор', 'Владимирович', 'М', '2003-12-09', 0, TRUE),
(3, 'Соколова', 'Екатерина', 'Алексеевна', 'Ж', '2005-02-14', 13000, FALSE),
(3, 'Морозов', 'Никита', 'Ильич', 'М', '2005-09-01', 0, FALSE);

-- Заполнение таблицы teachers
INSERT INTO teachers
(department_id, last_name, first_name, middle_name, category, salary, academic_degree)
VALUES
(1, 'Сидоров', 'Павел', 'Викторович', 'Доцент', 120000, 'Кандидат наук'),
(1, 'Орлов', 'Иван', 'Николаевич', 'Профессор', 180000, 'Доктор наук'),
(2, 'Крылова', 'Елена', 'Сергеевна', 'Старший преподаватель', 95000, 'Кандидат наук'),
(2, 'Фёдоров', 'Алексей', 'Петрович', 'Ассистент', 70000, NULL),
(3, 'Новикова', 'Ольга', 'Ивановна', 'Доцент', 110000, 'Кандидат наук');

-- Заполнение таблицы disciplines
INSERT INTO disciplines (discipline_name)
VALUES
('Базы данных'),
('Программирование'),
('Компьютерные сети'),
('Экономика предприятия'),
('Правоведение');

-- Заполнение таблицы semesters
INSERT INTO semesters (semester_number, academic_year)
VALUES
(1, '2025-2026'),
(2, '2025-2026'),
(3, '2026-2027');

-- Заполнение таблицы teaching_assignments
INSERT INTO teaching_assignments
(teacher_id, discipline_id, group_id, semester_id,
lecture_hours, practice_hours, lab_hours)
VALUES
(1, 1, 1, 1, 32, 16, 24),
(1, 2, 2, 1, 40, 20, 30),
(2, 2, 1, 2, 50, 20, 10),
(3, 3, 2, 2, 24, 16, 32),
(5, 4, 3, 1, 36, 18, 0);

-- Заполнение таблицы exams
INSERT INTO exams
(student_id, discipline_id, teacher_id,
semester_id, exam_date, grade)
VALUES
(1, 1, 1, 1, '2026-01-15', 5),
(1, 2, 2, 2, '2026-06-10', 4),
(2, 1, 1, 1, '2026-01-15', 3),
(2, 2, 2, 2, '2026-06-10', 5),
(3, 1, 1, 1, '2026-01-15', 5),
(3, 2, 2, 2, '2026-06-10', 5),
(4, 2, 1, 1, '2026-01-20', 2),
(5, 3, 3, 2, '2026-06-18', 4),
(6, 3, 3, 2, '2026-06-18', 3),
(7, 4, 5, 1, '2026-01-25', 5),
(8, 4, 5, 1, '2026-01-25', 2);