Database_Script.sql - master - NikitaOnianov/Education_System

Database_Script.sql
1 297 строк57 KB
NikitaOnianov
add docker
20 июл 2026, 17:28
20 июл 2026, 17:282f66494
-- DROP SCHEMA "College";

CREATE SCHEMA "College" AUTHORIZATION userdb;

-- DROP SEQUENCE "College".admins_admin_id_seq;

CREATE SEQUENCE "College".admins_admin_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".admins_admin_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".admins_admin_id_seq TO userdb;

-- DROP SEQUENCE "College".attendance_attendance_id_seq;

CREATE SEQUENCE "College".attendance_attendance_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".attendance_attendance_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".attendance_attendance_id_seq TO userdb;

-- DROP SEQUENCE "College".attendance_type_attendance_type_id_seq;

CREATE SEQUENCE "College".attendance_type_attendance_type_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".attendance_type_attendance_type_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".attendance_type_attendance_type_id_seq TO userdb;

-- DROP SEQUENCE "College".buildings_building_id_seq;

CREATE SEQUENCE "College".buildings_building_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".buildings_building_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".buildings_building_id_seq TO userdb;

-- DROP SEQUENCE "College".classrooms_classroom_id_seq;

CREATE SEQUENCE "College".classrooms_classroom_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 32767
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".classrooms_classroom_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".classrooms_classroom_id_seq TO userdb;

-- DROP SEQUENCE "College".disciplines_discipline_id_seq;

CREATE SEQUENCE "College".disciplines_discipline_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".disciplines_discipline_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".disciplines_discipline_id_seq TO userdb;

-- DROP SEQUENCE "College".grade_type_grade_type_id_seq;

CREATE SEQUENCE "College".grade_type_grade_type_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".grade_type_grade_type_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".grade_type_grade_type_id_seq TO userdb;

-- DROP SEQUENCE "College".grades_grade_id_seq;

CREATE SEQUENCE "College".grades_grade_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".grades_grade_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".grades_grade_id_seq TO userdb;

-- DROP SEQUENCE "College".group_disciplines_group_discipline_id_seq;

CREATE SEQUENCE "College".group_disciplines_group_discipline_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".group_disciplines_group_discipline_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".group_disciplines_group_discipline_id_seq TO userdb;

-- DROP SEQUENCE "College".groups_group_id_seq;

CREATE SEQUENCE "College".groups_group_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".groups_group_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".groups_group_id_seq TO userdb;

-- DROP SEQUENCE "College".holidays_holiday_id_seq;

CREATE SEQUENCE "College".holidays_holiday_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".holidays_holiday_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".holidays_holiday_id_seq TO userdb;

-- DROP SEQUENCE "College".lessons_deleted_log_log_id_seq;

CREATE SEQUENCE "College".lessons_deleted_log_log_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".lessons_deleted_log_log_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".lessons_deleted_log_log_id_seq TO userdb;

-- DROP SEQUENCE "College".lessons_lesson_id_seq;

CREATE SEQUENCE "College".lessons_lesson_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".lessons_lesson_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".lessons_lesson_id_seq TO userdb;

-- DROP SEQUENCE "College".refresh_tokens_id_seq;

CREATE SEQUENCE "College".refresh_tokens_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".refresh_tokens_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".refresh_tokens_id_seq TO userdb;

-- DROP SEQUENCE "College".roles_role_id_seq;

CREATE SEQUENCE "College".roles_role_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 32767
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".roles_role_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".roles_role_id_seq TO userdb;

-- DROP SEQUENCE "College".schedule_errors_error_id_seq;

CREATE SEQUENCE "College".schedule_errors_error_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".schedule_errors_error_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".schedule_errors_error_id_seq TO userdb;

-- DROP SEQUENCE "College".schedule_preferences_preference_id_seq;

CREATE SEQUENCE "College".schedule_preferences_preference_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".schedule_preferences_preference_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".schedule_preferences_preference_id_seq TO userdb;

-- DROP SEQUENCE "College".semestres_semestre_id_seq;

CREATE SEQUENCE "College".semestres_semestre_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".semestres_semestre_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".semestres_semestre_id_seq TO userdb;

-- DROP SEQUENCE "College".specialties_specialization_id_seq;

CREATE SEQUENCE "College".specialties_specialization_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".specialties_specialization_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".specialties_specialization_id_seq TO userdb;

-- DROP SEQUENCE "College".students_student_id_seq;

CREATE SEQUENCE "College".students_student_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".students_student_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".students_student_id_seq TO userdb;

-- DROP SEQUENCE "College".teacher_disciplines_teacher_disciplines_id_seq;

CREATE SEQUENCE "College".teacher_disciplines_teacher_disciplines_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".teacher_disciplines_teacher_disciplines_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".teacher_disciplines_teacher_disciplines_id_seq TO userdb;

-- DROP SEQUENCE "College".teachers_teacher_id_seq;

CREATE SEQUENCE "College".teachers_teacher_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".teachers_teacher_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".teachers_teacher_id_seq TO userdb;

-- DROP SEQUENCE "College".time_slots_time_slots_id_seq;

CREATE SEQUENCE "College".time_slots_time_slots_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 32767
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".time_slots_time_slots_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".time_slots_time_slots_id_seq TO userdb;

-- DROP SEQUENCE "College".users_user_id_seq;

CREATE SEQUENCE "College".users_user_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE "College".users_user_id_seq OWNER TO userdb;
GRANT ALL ON SEQUENCE "College".users_user_id_seq TO userdb;
-- "College".attendance_type определение

-- Drop table

-- DROP TABLE "College".attendance_type;

CREATE TABLE "College".attendance_type ( attendance_type_id int8 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL, attendance_type_name varchar NOT NULL, description text NULL, CONSTRAINT attendance_type_pk PRIMARY KEY (attendance_type_id));

INSERT INTO "College".attendance_type (attendance_type_name,description) VALUES
	 ('Присутствовал','Студент был на уроке'),
	 ('Отсутствовал','По неуважительной причине'),
	 ('По семейным обстоятельствам','Студент отсутствовал на уроке по семейным обстоятельсвам'),
	 ('У врача','Студент отсутствовал на уроке по причине посещения врача');


-- Column comments

COMMENT ON COLUMN "College".attendance_type.attendance_type_id IS 'ID типа присутствия/отсутствия';
COMMENT ON COLUMN "College".attendance_type.attendance_type_name IS 'Название типа';
COMMENT ON COLUMN "College".attendance_type.description IS 'Описание';

-- Permissions

ALTER TABLE "College".attendance_type OWNER TO userdb;
GRANT ALL ON TABLE "College".attendance_type TO userdb;


-- "College".buildings определение

-- Drop table

-- DROP TABLE "College".buildings;

CREATE TABLE "College".buildings ( building_id serial4 NOT NULL, building_name varchar(100) NOT NULL, building_address varchar(255) NULL, description text NULL, CONSTRAINT buildings_pkey PRIMARY KEY (building_id));
COMMENT ON TABLE "College".buildings IS 'Учебные корпуса';

-- Column comments

COMMENT ON COLUMN "College".buildings.building_id IS 'Первичный ключ, идентификатор здания';
COMMENT ON COLUMN "College".buildings.building_name IS 'Название корпуса (например, Главный корпус)';
COMMENT ON COLUMN "College".buildings.building_address IS 'Физический адрес здания';
COMMENT ON COLUMN "College".buildings.description IS 'Дополнительная информация о здании';

-- Permissions

ALTER TABLE "College".buildings OWNER TO userdb;


-- "College".grade_type определение

-- Drop table

-- DROP TABLE "College".grade_type;

CREATE TABLE "College".grade_type ( grade_type_id int8 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL, grade_type_name varchar NOT NULL, CONSTRAINT grade_type_pk PRIMARY KEY (grade_type_id));

INSERT INTO "College".grade_type (grade_type_name) VALUES
	 ('Работа на уроке'),
	 ('Домашнее задание'),
	 ('Лабораторная работа'),
	 ('Контрольная работа'),
	 ('Итоговая');


-- Column comments

COMMENT ON COLUMN "College".grade_type.grade_type_id IS 'ID типа';
COMMENT ON COLUMN "College".grade_type.grade_type_name IS 'Название типа (за работу на уроке или ДЗ)';

-- Permissions

ALTER TABLE "College".grade_type OWNER TO userdb;
GRANT ALL ON TABLE "College".grade_type TO userdb;


-- "College".holidays определение

-- Drop table

-- DROP TABLE "College".holidays;

CREATE TABLE "College".holidays ( holiday_id serial4 NOT NULL, start_date date NOT NULL, end_date date NOT NULL, holiday_name varchar(100) NOT NULL, description text NULL, CONSTRAINT holidays_dates_check CHECK ((start_date <= end_date)), CONSTRAINT holidays_pkey PRIMARY KEY (holiday_id));
COMMENT ON TABLE "College".holidays IS 'Периоды праздничных нерабочих дней (включая многодневные)';

-- Column comments

COMMENT ON COLUMN "College".holidays.holiday_id IS 'Первичный ключ, идентификатор записи о празднике/каникулах';
COMMENT ON COLUMN "College".holidays.start_date IS 'Начало периода (включительно)';
COMMENT ON COLUMN "College".holidays.end_date IS 'Конец периода (включительно)';
COMMENT ON COLUMN "College".holidays.holiday_name IS 'Название праздника или каникулярного периода';
COMMENT ON COLUMN "College".holidays.description IS 'Подробное описание, примечания';

-- Permissions

ALTER TABLE "College".holidays OWNER TO userdb;


-- "College".lessons_deleted_log определение

-- Drop table

-- DROP TABLE "College".lessons_deleted_log;

CREATE TABLE "College".lessons_deleted_log ( log_id bigserial NOT NULL, deleted_at timestamp DEFAULT now() NULL, lesson_id int8 NULL, lesson_date date NULL, lesson_group int8 NULL, lesson_teacher int8 NULL, lesson_semester int4 NULL, lesson_slot int2 NULL, lesson_discipline int8 NULL, lesson_classroom_id int2 NULL, is_manual bool NULL, deleted_by varchar(50) DEFAULT 'auto_schedule'::character varying NULL, CONSTRAINT lessons_deleted_log_pkey PRIMARY KEY (log_id));
COMMENT ON TABLE "College".lessons_deleted_log IS 'Лог удалённых уроков (автоматическая генерация)';

-- Permissions

ALTER TABLE "College".lessons_deleted_log OWNER TO userdb;
GRANT ALL ON TABLE "College".lessons_deleted_log TO userdb;


-- "College".roles определение

-- Drop table

-- DROP TABLE "College".roles;

CREATE TABLE "College".roles ( role_id int2 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 32767 START 1 CACHE 1 NO CYCLE) NOT NULL, role_name varchar(20) NOT NULL, CONSTRAINT roles_pk PRIMARY KEY (role_id), CONSTRAINT roles_role_name_key UNIQUE (role_name));
COMMENT ON TABLE "College".roles IS 'Роли пользователей системы';

INSERT INTO "College".roles (role_name) VALUES
	 ('admin'),
	 ('teacher'),
	 ('student');


-- Column comments

COMMENT ON COLUMN "College".roles.role_id IS 'Первичный ключ, идентификатор роли пользователя';
COMMENT ON COLUMN "College".roles.role_name IS 'Название роли';

-- Permissions

ALTER TABLE "College".roles OWNER TO userdb;
GRANT ALL ON TABLE "College".roles TO userdb;


-- "College".semesters определение

-- Drop table

-- DROP TABLE "College".semesters;

CREATE TABLE "College".semesters ( semester_id int4 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL, semester_start date NOT NULL, semester_final date NOT NULL, CONSTRAINT semester_dates_check CHECK ((semester_start < semester_final)), CONSTRAINT semestres_pk PRIMARY KEY (semester_id));

-- Column comments

COMMENT ON COLUMN "College".semesters.semester_id IS 'ID семестра';
COMMENT ON COLUMN "College".semesters.semester_start IS 'Начало семестра';
COMMENT ON COLUMN "College".semesters.semester_final IS 'Конец семестра';

-- Permissions

ALTER TABLE "College".semesters OWNER TO userdb;
GRANT ALL ON TABLE "College".semesters TO userdb;


-- "College".specialties определение

-- Drop table

-- DROP TABLE "College".specialties;

CREATE TABLE "College".specialties ( specialization_id bigserial NOT NULL, specialization_name varchar NOT NULL, specialization_description text NULL, CONSTRAINT specialties_pk PRIMARY KEY (specialization_id));
COMMENT ON TABLE "College".specialties IS 'Справочник специальностей (направлений подготовки)';

-- Column comments

COMMENT ON COLUMN "College".specialties.specialization_id IS 'Первичный ключ, идентификатор специальности';
COMMENT ON COLUMN "College".specialties.specialization_name IS 'Название специальности (например, "Машиностроение")';
COMMENT ON COLUMN "College".specialties.specialization_description IS 'Описание специальности';

-- Permissions

ALTER TABLE "College".specialties OWNER TO userdb;
GRANT ALL ON TABLE "College".specialties TO userdb;


-- "College".time_slots определение

-- Drop table

-- DROP TABLE "College".time_slots;

CREATE TABLE "College".time_slots ( time_slots_id int2 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 32767 START 1 CACHE 1 NO CYCLE) NOT NULL, time_slots_start time NOT NULL, time_slots_final time NOT NULL, CONSTRAINT slot_times_check CHECK ((time_slots_start < time_slots_final)), CONSTRAINT time_slots_pk PRIMARY KEY (time_slots_id));

INSERT INTO "College".time_slots (time_slots_start,time_slots_final) VALUES
	 ('09:00:00','09:45:00'),
	 ('09:55:00','10:40:00'),
	 ('11:00:00','11:45:00'),
	 ('12:05:00','12:50:00'),
	 ('13:00:00','13:45:00'),
	 ('13:55:00','14:40:00'),
	 ('15:45:00','16:30:00'),
	 ('14:50:00','15:35:00'),
	 ('16:35:00','17:25:00');


COMMENT ON TABLE "College".time_slots IS 'Временные слоты для занятий (пары)';

-- Column comments

COMMENT ON COLUMN "College".time_slots.time_slots_id IS 'Первичный ключ, идентификатор временного слота';
COMMENT ON COLUMN "College".time_slots.time_slots_start IS 'Время начала занятия';
COMMENT ON COLUMN "College".time_slots.time_slots_final IS 'Время окончания занятия';

-- Permissions

ALTER TABLE "College".time_slots OWNER TO userdb;
GRANT ALL ON TABLE "College".time_slots TO userdb;


-- "College".classrooms определение

-- Drop table

-- DROP TABLE "College".classrooms;

CREATE TABLE "College".classrooms ( classroom_id int2 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 32767 START 1 CACHE 1 NO CYCLE) NOT NULL, classroom_number varchar(10) NOT NULL, classroom_floor int2 NULL, classroom_capacity int2 NULL, classroom_type varchar(50) NULL, building_id int4 NULL, CONSTRAINT classrooms_classroom_capacity_check CHECK ((classroom_capacity > 0)), CONSTRAINT classrooms_pk PRIMARY KEY (classroom_id), CONSTRAINT classrooms_buildings_fk FOREIGN KEY (building_id) REFERENCES "College".buildings(building_id));
COMMENT ON TABLE "College".classrooms IS 'Аудитории';

-- Column comments

COMMENT ON COLUMN "College".classrooms.classroom_id IS 'ID аудитории';
COMMENT ON COLUMN "College".classrooms.classroom_number IS 'Номер аудитории';
COMMENT ON COLUMN "College".classrooms.classroom_floor IS 'Этаж';
COMMENT ON COLUMN "College".classrooms.classroom_capacity IS 'Вместимость (кол-во мест)';
COMMENT ON COLUMN "College".classrooms.classroom_type IS 'Тип аудитории';

-- Permissions

ALTER TABLE "College".classrooms OWNER TO userdb;
GRANT ALL ON TABLE "College".classrooms TO userdb;


-- "College".disciplines определение

-- Drop table

-- DROP TABLE "College".disciplines;

CREATE TABLE "College".disciplines ( discipline_id int8 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL, discipline_name varchar NOT NULL, discipline_number_of_hours int4 NOT NULL, discipline_classroom_id int2 NULL, discipline_specialization int8 NULL, CONSTRAINT check_hours CHECK ((discipline_number_of_hours > 0)), CONSTRAINT disciplines_pk PRIMARY KEY (discipline_id), CONSTRAINT disciplines_classrooms_fk FOREIGN KEY (discipline_classroom_id) REFERENCES "College".classrooms(classroom_id), CONSTRAINT disciplines_specialties_fk FOREIGN KEY (discipline_specialization) REFERENCES "College".specialties(specialization_id));

-- Column comments

COMMENT ON COLUMN "College".disciplines.discipline_id IS 'ID дисциплины';
COMMENT ON COLUMN "College".disciplines.discipline_name IS 'Название дисциплины';
COMMENT ON COLUMN "College".disciplines.discipline_number_of_hours IS 'Количество часов для изучения';
COMMENT ON COLUMN "College".disciplines.discipline_classroom_id IS 'Номер кабинета';
COMMENT ON COLUMN "College".disciplines.discipline_specialization IS 'Ссылка на специальность, для которой дисциплина является профильной (NULL – общая дисциплина)';

-- Permissions

ALTER TABLE "College".disciplines OWNER TO userdb;
GRANT ALL ON TABLE "College".disciplines TO userdb;


-- "College".users определение

-- Drop table

-- DROP TABLE "College".users;

CREATE TABLE "College".users ( user_id bigserial NOT NULL, user_login varchar(50) NOT NULL, user_password_hash varchar(60) NOT NULL, role_id int2 NOT NULL, user_created_at date DEFAULT now() NULL, user_last_login timestamptz NULL, user_is_active bool DEFAULT true NULL, CONSTRAINT users_pk PRIMARY KEY (user_id), CONSTRAINT users_user_login_key UNIQUE (user_login), CONSTRAINT users_role_id_fkey FOREIGN KEY (role_id) REFERENCES "College".roles(role_id));
COMMENT ON TABLE "College".users IS 'Пользователи системы (учётные записи)';

-- Column comments

COMMENT ON COLUMN "College".users.user_id IS 'ID пользователя';
COMMENT ON COLUMN "College".users.user_login IS 'Логин (уникальный)';
COMMENT ON COLUMN "College".users.user_password_hash IS 'Хеш пароля';
COMMENT ON COLUMN "College".users.role_id IS 'Роль пользователя';
COMMENT ON COLUMN "College".users.user_created_at IS 'Дата регистрации';
COMMENT ON COLUMN "College".users.user_last_login IS 'Последний вход';
COMMENT ON COLUMN "College".users.user_is_active IS 'Активен ли пользователь';

-- Permissions

ALTER TABLE "College".users OWNER TO userdb;
GRANT ALL ON TABLE "College".users TO userdb;


-- "College".admins определение

-- Drop table

-- DROP TABLE "College".admins;

CREATE TABLE "College".admins ( admin_id bigserial NOT NULL, user_id int8 NOT NULL, admin_name varchar NOT NULL, admin_surname varchar NOT NULL, admin_patronymic varchar NULL, admin_email varchar NOT NULL, admin_phone varchar NULL, admin_position varchar(100) NULL, CONSTRAINT admins_admin_email_key UNIQUE (admin_email), CONSTRAINT admins_pk PRIMARY KEY (admin_id), CONSTRAINT admins_user_id_key UNIQUE (user_id), CONSTRAINT admins_user_id_fkey FOREIGN KEY (user_id) REFERENCES "College".users(user_id) ON DELETE CASCADE);
COMMENT ON TABLE "College".admins IS 'Администраторы';

-- Column comments

COMMENT ON COLUMN "College".admins.admin_id IS 'ID администратора';
COMMENT ON COLUMN "College".admins.user_id IS 'Ссылка на учётную запись';
COMMENT ON COLUMN "College".admins.admin_name IS 'Имя';
COMMENT ON COLUMN "College".admins.admin_surname IS 'Фамилия';
COMMENT ON COLUMN "College".admins.admin_patronymic IS 'Отчество';
COMMENT ON COLUMN "College".admins.admin_email IS 'Почта';
COMMENT ON COLUMN "College".admins.admin_phone IS 'Телефон';
COMMENT ON COLUMN "College".admins.admin_position IS 'Должность';

-- Permissions

ALTER TABLE "College".admins OWNER TO userdb;
GRANT ALL ON TABLE "College".admins TO userdb;


-- "College".refresh_tokens определение

-- Drop table

-- DROP TABLE "College".refresh_tokens;

CREATE TABLE "College".refresh_tokens ( id bigserial NOT NULL, "token" varchar(255) NOT NULL, user_id int8 NOT NULL, expiry_date timestamptz NOT NULL, is_revoked bool DEFAULT false NOT NULL, created_at timestamptz DEFAULT now() NULL, CONSTRAINT refresh_tokens_pkey PRIMARY KEY (id), CONSTRAINT refresh_tokens_token_key UNIQUE (token), CONSTRAINT fk_refresh_token_user FOREIGN KEY (user_id) REFERENCES "College".users(user_id) ON DELETE CASCADE);
CREATE INDEX idx_refresh_token_token ON "College".refresh_tokens USING btree (token);
CREATE INDEX idx_refresh_token_user_id ON "College".refresh_tokens USING btree (user_id);
COMMENT ON TABLE "College".refresh_tokens IS 'Хранилище Refresh токенов для JWT-аутентификации';

-- Column comments

COMMENT ON COLUMN "College".refresh_tokens.id IS 'Первичный ключ';
COMMENT ON COLUMN "College".refresh_tokens."token" IS 'Уникальный Refresh токен (строка)';
COMMENT ON COLUMN "College".refresh_tokens.user_id IS 'Ссылка на пользователя';
COMMENT ON COLUMN "College".refresh_tokens.expiry_date IS 'Дата истечения срока действия токена (UTC)';
COMMENT ON COLUMN "College".refresh_tokens.is_revoked IS 'Признак отзыва токена (если True – токен больше не действителен)';
COMMENT ON COLUMN "College".refresh_tokens.created_at IS 'Дата создания записи';

-- Permissions

ALTER TABLE "College".refresh_tokens OWNER TO userdb;
GRANT ALL ON TABLE "College".refresh_tokens TO userdb;


-- "College".teachers определение

-- Drop table

-- DROP TABLE "College".teachers;

CREATE TABLE "College".teachers ( teacher_id bigserial NOT NULL, teacher_name varchar NOT NULL, teacher_surname varchar NOT NULL, teacher_patronymic varchar NULL, teacher_address text NOT NULL, teacher_passport varchar NOT NULL, teacher_email varchar NOT NULL, teacher_phone varchar NOT NULL, teacher_birth_date date NOT NULL, teacher_user_id int8 NOT NULL, building_id int4 NULL, CONSTRAINT teacher_passport_unique UNIQUE (teacher_passport), CONSTRAINT teachers_pk PRIMARY KEY (teacher_id), CONSTRAINT teachers_unique UNIQUE (teacher_user_id), CONSTRAINT teachers_buildings_fk FOREIGN KEY (building_id) REFERENCES "College".buildings(building_id), CONSTRAINT teachers_users_fk FOREIGN KEY (teacher_user_id) REFERENCES "College".users(user_id));
CREATE INDEX idx_teachers_surname ON "College".teachers USING btree (teacher_surname);

-- Column comments

COMMENT ON COLUMN "College".teachers.teacher_id IS 'ID учителя';
COMMENT ON COLUMN "College".teachers.teacher_name IS 'Имя';
COMMENT ON COLUMN "College".teachers.teacher_surname IS 'Фамилия';
COMMENT ON COLUMN "College".teachers.teacher_patronymic IS 'Отчество';
COMMENT ON COLUMN "College".teachers.teacher_address IS 'Адрес';
COMMENT ON COLUMN "College".teachers.teacher_passport IS 'Паспорт';
COMMENT ON COLUMN "College".teachers.teacher_email IS 'Почта';
COMMENT ON COLUMN "College".teachers.teacher_phone IS 'Телефон';
COMMENT ON COLUMN "College".teachers.teacher_birth_date IS 'День рождения';
COMMENT ON COLUMN "College".teachers.teacher_user_id IS 'Ссылка на учётную запись пользователя (users)';
COMMENT ON COLUMN "College".teachers.building_id IS 'Здание, к которому прикреплён учитель (NULL – может работать в любом)';

-- Permissions

ALTER TABLE "College".teachers OWNER TO userdb;
GRANT ALL ON TABLE "College".teachers TO userdb;


-- "College"."groups" определение

-- Drop table

-- DROP TABLE "College"."groups";

CREATE TABLE "College"."groups" ( group_id bigserial NOT NULL, group_name varchar NOT NULL, group_curator int8 NULL, group_specialization int8 NULL, building_id int4 NULL, CONSTRAINT groups_pk PRIMARY KEY (group_id), CONSTRAINT groups_unique UNIQUE (group_name), CONSTRAINT groups_buildings_fk FOREIGN KEY (building_id) REFERENCES "College".buildings(building_id), CONSTRAINT groups_specialties_fk FOREIGN KEY (group_specialization) REFERENCES "College".specialties(specialization_id), CONSTRAINT groups_teachers_fk FOREIGN KEY (group_curator) REFERENCES "College".teachers(teacher_id) ON DELETE SET NULL);

-- Column comments

COMMENT ON COLUMN "College"."groups".group_id IS 'ID группы';
COMMENT ON COLUMN "College"."groups".group_name IS 'Название группы';
COMMENT ON COLUMN "College"."groups".group_curator IS 'Куратор группы (учитель)';
COMMENT ON COLUMN "College"."groups".group_specialization IS 'Специальность группы (ссылка на specialties)';
COMMENT ON COLUMN "College"."groups".building_id IS 'Основное здание, где проходят занятия группы';

-- Permissions

ALTER TABLE "College"."groups" OWNER TO userdb;
GRANT ALL ON TABLE "College"."groups" TO userdb;


-- "College".lessons определение

-- Drop table

-- DROP TABLE "College".lessons;

CREATE TABLE "College".lessons ( lesson_id bigserial NOT NULL, lesson_date date NOT NULL, lesson_group int8 NOT NULL, lesson_teacher int8 NOT NULL, lesson_semester int4 NOT NULL, lesson_slot int2 NOT NULL, lesson_discipline int8 NOT NULL, lesson_classroom_id int2 NOT NULL, is_manual bool DEFAULT false NULL, CONSTRAINT lessons_pk PRIMARY KEY (lesson_id), CONSTRAINT lessons_classrooms_fk FOREIGN KEY (lesson_classroom_id) REFERENCES "College".classrooms(classroom_id), CONSTRAINT lessons_disciplines_fk FOREIGN KEY (lesson_discipline) REFERENCES "College".disciplines(discipline_id), CONSTRAINT lessons_groups_fk FOREIGN KEY (lesson_group) REFERENCES "College"."groups"(group_id), CONSTRAINT lessons_semesters_fk FOREIGN KEY (lesson_semester) REFERENCES "College".semesters(semester_id), CONSTRAINT lessons_teachers_fk FOREIGN KEY (lesson_teacher) REFERENCES "College".teachers(teacher_id), CONSTRAINT lessons_time_slots_fk FOREIGN KEY (lesson_slot) REFERENCES "College".time_slots(time_slots_id));
CREATE INDEX idx_lessons_date ON "College".lessons USING btree (lesson_date);
CREATE INDEX idx_lessons_group ON "College".lessons USING btree (lesson_group);
CREATE INDEX idx_lessons_room ON "College".lessons USING btree (lesson_classroom_id);
CREATE INDEX idx_lessons_slot ON "College".lessons USING btree (lesson_slot);
CREATE INDEX idx_lessons_teacher ON "College".lessons USING btree (lesson_teacher);

-- Column comments

COMMENT ON COLUMN "College".lessons.lesson_id IS 'ID занятия';
COMMENT ON COLUMN "College".lessons.lesson_date IS 'День проведения занятия';
COMMENT ON COLUMN "College".lessons.lesson_group IS 'Номер группы';
COMMENT ON COLUMN "College".lessons.lesson_teacher IS 'Учитель';
COMMENT ON COLUMN "College".lessons.lesson_semester IS 'В каком семестре';
COMMENT ON COLUMN "College".lessons.lesson_slot IS 'Номер занятия';
COMMENT ON COLUMN "College".lessons.lesson_discipline IS 'Предмет занятия';
COMMENT ON COLUMN "College".lessons.lesson_classroom_id IS 'Аудитория';
COMMENT ON COLUMN "College".lessons.is_manual IS 'Флаг ручного редактирования (не удалять при перегенерации)';

-- Permissions

ALTER TABLE "College".lessons OWNER TO userdb;
GRANT ALL ON TABLE "College".lessons TO userdb;


-- "College".schedule_errors определение

-- Drop table

-- DROP TABLE "College".schedule_errors;

CREATE TABLE "College".schedule_errors ( error_id serial4 NOT NULL, semester_id int4 NOT NULL, error_date date NOT NULL, group_id int8 NOT NULL, message text NOT NULL, created_at timestamp DEFAULT now() NULL, CONSTRAINT schedule_errors_pkey PRIMARY KEY (error_id), CONSTRAINT schedule_errors_group_id_fkey FOREIGN KEY (group_id) REFERENCES "College"."groups"(group_id), CONSTRAINT schedule_errors_semester_id_fkey FOREIGN KEY (semester_id) REFERENCES "College".semesters(semester_id));
COMMENT ON TABLE "College".schedule_errors IS 'Лог ошибок, возникших при автоматической генерации расписания (требуют ручного вмешательства)';

-- Column comments

COMMENT ON COLUMN "College".schedule_errors.error_id IS 'Первичный ключ';
COMMENT ON COLUMN "College".schedule_errors.semester_id IS 'Семестр, для которого генерировалось расписание';
COMMENT ON COLUMN "College".schedule_errors.error_date IS 'Дата, на которую не удалось создать урок';
COMMENT ON COLUMN "College".schedule_errors.group_id IS 'Группа, для которой возникла ошибка';
COMMENT ON COLUMN "College".schedule_errors.message IS 'Текст ошибки с подробностями';
COMMENT ON COLUMN "College".schedule_errors.created_at IS 'Время возникновения ошибки';

-- Permissions

ALTER TABLE "College".schedule_errors OWNER TO userdb;
GRANT ALL ON TABLE "College".schedule_errors TO userdb;


-- "College".schedule_preferences определение

-- Drop table

-- DROP TABLE "College".schedule_preferences;

CREATE TABLE "College".schedule_preferences ( preference_id serial4 NOT NULL, group_id int8 NOT NULL, discipline_id int8 NOT NULL, preferred_dow int4 NOT NULL, priority int4 DEFAULT 1 NULL, CONSTRAINT schedule_preferences_group_id_discipline_id_preferred_dow_key UNIQUE (group_id, discipline_id, preferred_dow), CONSTRAINT schedule_preferences_pkey PRIMARY KEY (preference_id), CONSTRAINT schedule_preferences_preferred_dow_check CHECK (((preferred_dow >= 1) AND (preferred_dow <= 5))), CONSTRAINT schedule_preferences_discipline_id_fkey FOREIGN KEY (discipline_id) REFERENCES "College".disciplines(discipline_id) ON DELETE CASCADE, CONSTRAINT schedule_preferences_group_id_fkey FOREIGN KEY (group_id) REFERENCES "College"."groups"(group_id) ON DELETE CASCADE);
COMMENT ON TABLE "College".schedule_preferences IS 'Предпочтения по дням недели для проведения занятий определённых дисциплин у групп';

-- Column comments

COMMENT ON COLUMN "College".schedule_preferences.preference_id IS 'Первичный ключ';
COMMENT ON COLUMN "College".schedule_preferences.group_id IS 'Группа, для которой задано предпочтение';
COMMENT ON COLUMN "College".schedule_preferences.discipline_id IS 'Дисциплина, которую желательно ставить в указанный день';
COMMENT ON COLUMN "College".schedule_preferences.preferred_dow IS 'День недели (1 – понедельник, 5 – пятница)';
COMMENT ON COLUMN "College".schedule_preferences.priority IS 'Приоритет предпочтения (чем больше, тем важнее)';

-- Permissions

ALTER TABLE "College".schedule_preferences OWNER TO userdb;
GRANT ALL ON TABLE "College".schedule_preferences TO userdb;


-- "College".students определение

-- Drop table

-- DROP TABLE "College".students;

CREATE TABLE "College".students ( student_id bigserial NOT NULL, student_name varchar NOT NULL, student_surname varchar NOT NULL, student_patronymic varchar NULL, student_phone varchar NOT NULL, student_email varchar NOT NULL, student_passport varchar NOT NULL, student_address text NOT NULL, student_group int8 NULL, student_birth_date date NOT NULL, student_user_id int8 NOT NULL, CONSTRAINT students_pk PRIMARY KEY (student_id), CONSTRAINT students_unique UNIQUE (student_passport), CONSTRAINT students_unique_1 UNIQUE (student_user_id), CONSTRAINT students_groups_fk FOREIGN KEY (student_group) REFERENCES "College"."groups"(group_id), CONSTRAINT students_users_fk FOREIGN KEY (student_user_id) REFERENCES "College".users(user_id));
CREATE INDEX idx_students_surname ON "College".students USING btree (student_surname);

-- Column comments

COMMENT ON COLUMN "College".students.student_id IS 'ID студента';
COMMENT ON COLUMN "College".students.student_name IS 'Имя';
COMMENT ON COLUMN "College".students.student_surname IS 'Фамилия';
COMMENT ON COLUMN "College".students.student_patronymic IS 'Отчество';
COMMENT ON COLUMN "College".students.student_phone IS 'Телефон';
COMMENT ON COLUMN "College".students.student_email IS 'Почта';
COMMENT ON COLUMN "College".students.student_passport IS 'Паспорт';
COMMENT ON COLUMN "College".students.student_address IS 'Адрес';
COMMENT ON COLUMN "College".students.student_group IS 'Группа студента';
COMMENT ON COLUMN "College".students.student_birth_date IS 'День рождения';

-- Permissions

ALTER TABLE "College".students OWNER TO userdb;
GRANT ALL ON TABLE "College".students TO userdb;


-- "College".teacher_disciplines определение

-- Drop table

-- DROP TABLE "College".teacher_disciplines;

CREATE TABLE "College".teacher_disciplines ( teacher_disciplines_id bigserial NOT NULL, teacher_disciplines_teacher int8 NOT NULL, teacher_disciplines_discipline int8 NOT NULL, CONSTRAINT teacher_disciplines_pk PRIMARY KEY (teacher_disciplines_id), CONSTRAINT teacher_disciplines_unique UNIQUE (teacher_disciplines_teacher, teacher_disciplines_discipline), CONSTRAINT teacher_disciplines_disciplines_fk FOREIGN KEY (teacher_disciplines_discipline) REFERENCES "College".disciplines(discipline_id), CONSTRAINT teacher_disciplines_teachers_fk FOREIGN KEY (teacher_disciplines_teacher) REFERENCES "College".teachers(teacher_id));

-- Column comments

COMMENT ON COLUMN "College".teacher_disciplines.teacher_disciplines_id IS 'ID записи';
COMMENT ON COLUMN "College".teacher_disciplines.teacher_disciplines_teacher IS 'ID учителя';
COMMENT ON COLUMN "College".teacher_disciplines.teacher_disciplines_discipline IS 'ID предмета';

-- Permissions

ALTER TABLE "College".teacher_disciplines OWNER TO userdb;
GRANT ALL ON TABLE "College".teacher_disciplines TO userdb;


-- "College".attendance определение

-- Drop table

-- DROP TABLE "College".attendance;

CREATE TABLE "College".attendance ( attendance_id bigserial NOT NULL, attendance_lesson int8 NOT NULL, attendance_student int8 NOT NULL, attendance_type int8 NOT NULL, CONSTRAINT attendance_pk PRIMARY KEY (attendance_id), CONSTRAINT attendance_attendance_type_fk FOREIGN KEY (attendance_type) REFERENCES "College".attendance_type(attendance_type_id), CONSTRAINT attendance_lessons_fk FOREIGN KEY (attendance_lesson) REFERENCES "College".lessons(lesson_id), CONSTRAINT attendance_students_fk FOREIGN KEY (attendance_student) REFERENCES "College".students(student_id));
CREATE INDEX idx_attendance_lesson ON "College".attendance USING btree (attendance_lesson);
CREATE INDEX idx_attendance_student ON "College".attendance USING btree (attendance_student);

-- Column comments

COMMENT ON COLUMN "College".attendance.attendance_id IS 'ID посещения';
COMMENT ON COLUMN "College".attendance.attendance_lesson IS 'Урок';
COMMENT ON COLUMN "College".attendance.attendance_student IS 'Студент';
COMMENT ON COLUMN "College".attendance.attendance_type IS 'Причина присутствия/отсутствия';

-- Permissions

ALTER TABLE "College".attendance OWNER TO userdb;
GRANT ALL ON TABLE "College".attendance TO userdb;


-- "College".grades определение

-- Drop table

-- DROP TABLE "College".grades;

CREATE TABLE "College".grades ( grade_id bigserial NOT NULL, grade_value int4 NOT NULL, grade_student int8 NOT NULL, grade_type int8 NOT NULL, grade_lesson int8 NOT NULL, CONSTRAINT grades_pk PRIMARY KEY (grade_id), CONSTRAINT grades_grade_type_fk FOREIGN KEY (grade_type) REFERENCES "College".grade_type(grade_type_id), CONSTRAINT grades_lessons_fk FOREIGN KEY (grade_lesson) REFERENCES "College".lessons(lesson_id), CONSTRAINT grades_students_fk FOREIGN KEY (grade_student) REFERENCES "College".students(student_id));
CREATE INDEX idx_grades_student ON "College".grades USING btree (grade_student);

-- Column comments

COMMENT ON COLUMN "College".grades.grade_id IS 'ID оценки';
COMMENT ON COLUMN "College".grades.grade_value IS 'Значение оценки (число)';
COMMENT ON COLUMN "College".grades.grade_student IS 'Какой студент';
COMMENT ON COLUMN "College".grades.grade_type IS 'За что оценка';
COMMENT ON COLUMN "College".grades.grade_lesson IS 'За какой урок';

-- Permissions

ALTER TABLE "College".grades OWNER TO userdb;
GRANT ALL ON TABLE "College".grades TO userdb;


-- "College".group_disciplines определение

-- Drop table

-- DROP TABLE "College".group_disciplines;

CREATE TABLE "College".group_disciplines ( group_discipline_id bigserial NOT NULL, group_id int8 NOT NULL, discipline_id int8 NOT NULL, planned_hours int4 NOT NULL, is_active bool DEFAULT false NOT NULL, CONSTRAINT group_disciplines_group_id_discipline_id_key UNIQUE (group_id, discipline_id), CONSTRAINT group_disciplines_pkey PRIMARY KEY (group_discipline_id), CONSTRAINT group_disciplines_planned_hours_check CHECK ((planned_hours > 0)), CONSTRAINT group_disciplines_discipline_id_fkey FOREIGN KEY (discipline_id) REFERENCES "College".disciplines(discipline_id) ON DELETE CASCADE, CONSTRAINT group_disciplines_group_id_fkey FOREIGN KEY (group_id) REFERENCES "College"."groups"(group_id) ON DELETE CASCADE);
COMMENT ON TABLE "College".group_disciplines IS 'Плановое количество часов дисциплины для конкретной группы (если отличается от discipline_number_of_hours)';

-- Permissions

ALTER TABLE "College".group_disciplines OWNER TO userdb;
GRANT ALL ON TABLE "College".group_disciplines TO userdb;



-- DROP PROCEDURE "College".generate_schedule(int4, date, bool);

CREATE OR REPLACE PROCEDURE "College".generate_schedule(IN p_semester_id integer DEFAULT NULL::integer, IN p_start_date date DEFAULT NULL::date, IN p_force boolean DEFAULT false)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_semester_start DATE;
    v_semester_end DATE;
    effective_start_date DATE;
    cur_date DATE;
    cur_dow INTEGER;
    group_rec RECORD;
    group_building_id INTEGER;
    pairs_today INTEGER;
    slot1 INTEGER;
    slot2 INTEGER;
    v_discipline_id INTEGER;
    v_teacher_id INTEGER;
    v_classroom_id INTEGER;
    attempt INTEGER;
    max_attempts INTEGER := 20;
    chosen_pair_block INTEGER;
    used_blocks INTEGER[];
    slot_ids INTEGER[];
    blocks INTEGER[][];
    num_blocks INTEGER;
    i INTEGER;
    preference_exists BOOLEAN;
    error_message TEXT;
    total_possible_days INTEGER;
    daily_pair_limit INTEGER := 4;
BEGIN
    -- 1. Определяем даты семестра
    IF p_semester_id IS NULL THEN
        SELECT semester_id, semester_start, semester_final 
        INTO p_semester_id, v_semester_start, v_semester_end
        FROM "College".semesters ORDER BY semester_id LIMIT 1;
    ELSE
        SELECT semester_start, semester_final 
        INTO v_semester_start, v_semester_end
        FROM "College".semesters WHERE semester_id = p_semester_id;
    END IF;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Semester with ID % not found', p_semester_id;
    END IF;

    effective_start_date := COALESCE(p_start_date, CURRENT_DATE + 1);
    IF effective_start_date < v_semester_start THEN
        effective_start_date := v_semester_start;
    END IF;
    IF effective_start_date > v_semester_end THEN
        RAISE EXCEPTION 'Start date % is after semester end %', effective_start_date, v_semester_end;
    END IF;

    -- 2. Защита от удаления будущих оценок/посещаемости
    IF NOT p_force THEN
        IF EXISTS (
            SELECT 1 FROM "College".grades g
            JOIN "College".lessons l ON l.lesson_id = g.grade_lesson
            WHERE l.lesson_semester = p_semester_id AND l.lesson_date >= effective_start_date
        ) OR EXISTS (
            SELECT 1 FROM "College".attendance a
            JOIN "College".lessons l ON l.lesson_id = a.attendance_lesson
            WHERE l.lesson_semester = p_semester_id AND l.lesson_date >= effective_start_date
        ) THEN
            RAISE EXCEPTION 'Cannot regenerate schedule because there are grades or attendance records for future dates. Use p_force=true to override.';
        END IF;
    END IF;

    -- 3. Архивирование удаляемых уроков (только автоматические)
    INSERT INTO "College".lessons_deleted_log (
        lesson_id, lesson_date, lesson_group, lesson_teacher, lesson_semester,
        lesson_slot, lesson_discipline, lesson_classroom_id, is_manual, deleted_by
    )
    SELECT lesson_id, lesson_date, lesson_group, lesson_teacher, lesson_semester,
           lesson_slot, lesson_discipline, lesson_classroom_id, is_manual, 'generate_schedule'
    FROM "College".lessons
    WHERE lesson_semester = p_semester_id 
      AND lesson_date >= effective_start_date 
      AND is_manual = FALSE;

    -- 4. Удаление автоматических уроков и зависимостей
    DELETE FROM "College".attendance
    WHERE attendance_lesson IN (
        SELECT lesson_id FROM "College".lessons
        WHERE lesson_semester = p_semester_id AND lesson_date >= effective_start_date AND is_manual = FALSE
    );

    DELETE FROM "College".grades
    WHERE grade_lesson IN (
        SELECT lesson_id FROM "College".lessons
        WHERE lesson_semester = p_semester_id AND lesson_date >= effective_start_date AND is_manual = FALSE
    );

    DELETE FROM "College".lessons
    WHERE lesson_semester = p_semester_id AND lesson_date >= effective_start_date AND is_manual = FALSE;

    DELETE FROM "College".schedule_errors
    WHERE semester_id = p_semester_id AND error_date >= effective_start_date;

    -- 5. Подготовка временных слотов
    SELECT array_agg(time_slots_id ORDER BY time_slots_start) INTO slot_ids
    FROM "College".time_slots;

    IF array_length(slot_ids, 1) % 2 != 0 THEN
        RAISE WARNING 'Number of time slots is odd: %. Last slot ignored.', array_length(slot_ids, 1);
        slot_ids := slot_ids[1:array_length(slot_ids,1)-1];
    END IF;

    num_blocks := array_length(slot_ids, 1) / 2;
    blocks := array_fill(NULL::INTEGER, ARRAY[num_blocks, 2]);
    FOR i IN 1..num_blocks LOOP
        blocks[i][1] := slot_ids[2*i - 1];
        blocks[i][2] := slot_ids[2*i];
    END LOOP;

    -- ===================== НОВЫЙ БЛОК: только активные дисциплины =====================
    -- 6. Плановая нагрузка (часы) для активных дисциплин
    CREATE TEMP TABLE hours_plan AS
    SELECT 
        gd.group_id,
        gd.discipline_id,
        gd.planned_hours AS total_hours,
        COALESCE(SUM(CASE WHEN l.lesson_date < effective_start_date THEN 2 ELSE 0 END), 0) AS hours_done
    FROM "College".group_disciplines gd
    JOIN "College".groups g ON g.group_id = gd.group_id
    LEFT JOIN "College".lessons l ON l.lesson_group = g.group_id AND l.lesson_discipline = gd.discipline_id
    WHERE gd.is_active = true
    GROUP BY gd.group_id, gd.discipline_id, gd.planned_hours;

    -- Если нет ни одной активной дисциплины – выходим
    IF NOT EXISTS (SELECT 1 FROM hours_plan) THEN
        RAISE WARNING 'No active disciplines found for any group. Schedule generation aborted.';
        DROP TABLE hours_plan;
        RETURN;
    END IF;

    -- 7. Назначение учителей с балансировкой нагрузки (только для активных дисциплин)
    CREATE TEMP TABLE assigned_teacher AS
    WITH teacher_load AS (
        SELECT 
            t.teacher_id,
            COUNT(l.lesson_id) AS current_load
        FROM "College".teachers t
        LEFT JOIN "College".lessons l ON l.lesson_teacher = t.teacher_id 
            AND l.lesson_semester = p_semester_id
            AND l.lesson_date >= effective_start_date
        GROUP BY t.teacher_id
    )
    SELECT DISTINCT ON (hp.group_id, hp.discipline_id)
        hp.group_id,
        hp.discipline_id,
        td.teacher_disciplines_teacher AS teacher_id
    FROM hours_plan hp
    JOIN "College".groups g ON g.group_id = hp.group_id
    JOIN "College".teacher_disciplines td ON td.teacher_disciplines_discipline = hp.discipline_id
    JOIN "College".teachers t ON t.teacher_id = td.teacher_disciplines_teacher
    JOIN "College".users u ON u.user_id = t.teacher_user_id
    LEFT JOIN teacher_load tl ON tl.teacher_id = t.teacher_id
    WHERE (t.building_id IS NULL OR t.building_id = g.building_id)
      AND u.user_is_active = true
    ORDER BY hp.group_id, hp.discipline_id, tl.current_load NULLS FIRST, random();

    CREATE INDEX ON assigned_teacher (group_id, discipline_id);
    -- =============================================================================

    -- 8. Основной цикл по дням
    FOR cur_date IN SELECT generate_series(effective_start_date, v_semester_end, '1 day') LOOP
        IF EXTRACT(dow FROM cur_date) IN (0, 6) OR EXISTS (
            SELECT 1 FROM "College".holidays h WHERE cur_date BETWEEN h.start_date AND h.end_date
        ) THEN
            CONTINUE;
        END IF;

        cur_dow := EXTRACT(dow FROM cur_date);

        FOR group_rec IN SELECT group_id, building_id FROM "College"."groups" ORDER BY group_id LOOP
            group_building_id := group_rec.building_id;

            IF group_building_id IS NULL THEN
                INSERT INTO "College".schedule_errors (semester_id, error_date, group_id, message)
                VALUES (p_semester_id, cur_date, group_rec.group_id, 'Group has no building assigned');
                CONTINUE;
            END IF;

            -- Проверяем, есть ли у группы хотя бы одна активная дисциплина с остатком часов
            PERFORM 1 FROM hours_plan hp
            WHERE hp.group_id = group_rec.group_id AND hp.hours_done < hp.total_hours
            LIMIT 1;
            IF NOT FOUND THEN
                CONTINUE;
            END IF;

            PERFORM 1 FROM "College".schedule_preferences
            WHERE group_id = group_rec.group_id AND preferred_dow = cur_dow
            LIMIT 1;
            preference_exists := FOUND;

            total_possible_days := (SELECT COUNT(*) FROM generate_series(cur_date, v_semester_end, '1 day') d
                                    WHERE EXTRACT(dow FROM d) NOT IN (0,6) 
                                      AND NOT EXISTS (SELECT 1 FROM "College".holidays h WHERE d BETWEEN h.start_date AND h.end_date));
            IF total_possible_days <= 0 THEN
                CONTINUE;
            END IF;

            IF random() < 0.7 THEN
                pairs_today := 1 + floor(random() * daily_pair_limit)::int;
            ELSE
                CONTINUE;
            END IF;

            used_blocks := '{}';
            FOR pair_num IN 1..pairs_today LOOP
                LOOP
                    chosen_pair_block := 1 + floor(random() * num_blocks)::int;
                    EXIT WHEN NOT (chosen_pair_block = ANY(used_blocks));
                END LOOP;
                used_blocks := array_append(used_blocks, chosen_pair_block);
                slot1 := blocks[chosen_pair_block][1];
                slot2 := blocks[chosen_pair_block][2];

                attempt := 0;
                v_teacher_id := NULL;
                v_classroom_id := NULL;
                v_discipline_id := NULL;

                LOOP
                    attempt := attempt + 1;
                    EXIT WHEN attempt > max_attempts;

                    -- Выбор дисциплины с учётом предпочтений и остатка часов (только активные)
                    IF preference_exists THEN
                        SELECT hp.discipline_id INTO v_discipline_id
                        FROM hours_plan hp
                        JOIN "College".schedule_preferences sp ON sp.discipline_id = hp.discipline_id
                        WHERE hp.group_id = group_rec.group_id
                          AND hp.hours_done < hp.total_hours
                          AND sp.preferred_dow = cur_dow
                        ORDER BY random()
                        LIMIT 1;
                    ELSE
                        SELECT discipline_id INTO v_discipline_id
                        FROM hours_plan
                        WHERE group_id = group_rec.group_id AND hours_done < total_hours
                        ORDER BY random()
                        LIMIT 1;
                    END IF;

                    IF NOT FOUND THEN
                        EXIT;
                    END IF;

                    -- Назначенный учитель
                    SELECT teacher_id INTO v_teacher_id
                    FROM assigned_teacher
                    WHERE group_id = group_rec.group_id AND discipline_id = v_discipline_id;

                    IF NOT FOUND THEN
                        v_teacher_id := NULL;
                        EXIT;
                    END IF;

                    -- Проверка занятости учителя
                    PERFORM 1 FROM "College".lessons l
                    WHERE l.lesson_date = cur_date
                      AND l.lesson_teacher = v_teacher_id
                      AND l.lesson_slot IN (slot1, slot2)
                    LIMIT 1;
                    IF FOUND THEN
                        CONTINUE;
                    END IF;

                    -- Подбор аудитории
                    SELECT d.discipline_classroom_id INTO v_classroom_id
                    FROM "College".disciplines d
                    WHERE d.discipline_id = v_discipline_id;

                    IF v_classroom_id IS NOT NULL AND EXISTS (
                        SELECT 1 FROM "College".classrooms c
                        WHERE c.classroom_id = v_classroom_id
                          AND c.building_id = group_building_id
                          AND NOT EXISTS (
                              SELECT 1 FROM "College".lessons l
                              WHERE l.lesson_date = cur_date
                                AND l.lesson_classroom_id = c.classroom_id
                                AND l.lesson_slot IN (slot1, slot2)
                          )
                    ) THEN
                        EXIT;
                    ELSE
                        SELECT c.classroom_id INTO v_classroom_id
                        FROM "College".classrooms c
                        WHERE c.building_id = group_building_id
                          AND NOT EXISTS (
                              SELECT 1 FROM "College".lessons l
                              WHERE l.lesson_date = cur_date
                                AND l.lesson_classroom_id = c.classroom_id
                                AND l.lesson_slot IN (slot1, slot2)
                          )
                        ORDER BY random()
                        LIMIT 1;

                        IF v_classroom_id IS NOT NULL THEN
                            EXIT;
                        END IF;
                    END IF;
                END LOOP;

                IF attempt <= max_attempts 
                   AND v_teacher_id IS NOT NULL 
                   AND v_classroom_id IS NOT NULL 
                   AND v_discipline_id IS NOT NULL 
                THEN
                    INSERT INTO "College".lessons
                        (lesson_date, lesson_group, lesson_teacher, lesson_semester,
                         lesson_slot, lesson_discipline, lesson_classroom_id, is_manual)
                    VALUES
                        (cur_date, group_rec.group_id, v_teacher_id, p_semester_id, slot1, v_discipline_id, v_classroom_id, FALSE),
                        (cur_date, group_rec.group_id, v_teacher_id, p_semester_id, slot2, v_discipline_id, v_classroom_id, FALSE);
                ELSE
                    error_message := 'Ошибка: для группы ' || group_rec.group_id || ' на дату ' || cur_date || ' не удалось найти подходящего учителя или аудиторию. Проверьте активные дисциплины и связи учителей.';
                    INSERT INTO "College".schedule_errors (semester_id, error_date, group_id, message)
                    VALUES (p_semester_id, cur_date, group_rec.group_id, error_message);
                END IF;
            END LOOP;
        END LOOP;
    END LOOP;

    DROP TABLE assigned_teacher;
    DROP TABLE hours_plan;
END;
$procedure$
;

-- Permissions

ALTER PROCEDURE "College".generate_schedule(int4, date, bool) OWNER TO userdb;
GRANT ALL ON PROCEDURE "College".generate_schedule(int4, date, bool) TO userdb;


-- Permissions

GRANT ALL ON SCHEMA "College" TO userdb;

-- =====================================================
-- Добавление администратора для входа в систему
-- =====================================================

-- Вставка учётной записи (пароль в открытом виде – будет перехеширован при первом входе)
INSERT INTO "College".users (user_login, user_password_hash, role_id, user_is_active)
SELECT 'admin', 'admin', 1, true
WHERE NOT EXISTS (SELECT 1 FROM "College".users WHERE user_login = 'admin');

-- Вставка профиля администратора (привязываем к созданному пользователю)
INSERT INTO "College".admins (user_id, admin_name, admin_surname, admin_patronymic, admin_email, admin_phone, admin_position)
SELECT u.user_id, 'Admin', 'Adminov', 'Adminovich', 'admin@example.com', '+1234567890', 'System Administrator'
FROM "College".users u
WHERE u.user_login = 'admin'
AND NOT EXISTS (SELECT 1 FROM "College".admins WHERE user_id = u.user_id);