-- ==========================================
-- Инициализация расширений
-- ==========================================
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ===============================
-- Справочники высшего уровня
-- ===============================
CREATE TABLE IF NOT EXISTS departments (
    id BIGSERIAL UNIQUE PRIMARY KEY NOT NULL,
    name VARCHAR(255) NOT NULL,
    code BIGINT UNIQUE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    active_from DATE,
    active_to DATE,
    archived_at TIMESTAMP,
    archived_by BIGINT,
    archive_reason TEXT
);

INSERT INTO departments (name, code) VALUES
    ('Кафедра ИБ', 1),
    ('Кафедра ВТ', 2),
    ('Кафедра КТ', 3);

CREATE TABLE IF NOT EXISTS specialties (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    specialty_code VARCHAR(255) UNIQUE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    active_from DATE,
    active_to DATE,
    archived_at TIMESTAMP,
    archived_by BIGINT,
    archive_reason TEXT
);

INSERT INTO specialties (name, specialty_code) VALUES
    ('Информационная безопасность', '10.03.01'),
    ('Информатика и вычислительная техника', '09.03.01'),
    ('Программная инженерия', '09.03.04')
ON CONFLICT (specialty_code) DO NOTHING;

CREATE TABLE IF NOT EXISTS specialty_profiles (
    id BIGSERIAL PRIMARY KEY,
    specialty_id BIGINT NOT NULL REFERENCES specialties(id) ON DELETE CASCADE,
    name VARCHAR(500) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    active_from DATE,
    active_to DATE,
    archived_at TIMESTAMP,
    archived_by BIGINT,
    archive_reason TEXT,
    CONSTRAINT uq_specialty_profiles_name UNIQUE (specialty_id, name)
);

INSERT INTO specialty_profiles (specialty_id, name)
SELECT id, 'Без профиля'
FROM specialties
ON CONFLICT (specialty_id, name) DO NOTHING;

-- ==========================================
-- Пользователи и роли
-- ==========================================
CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(30) NOT NULL DEFAULT 'STUDENT',
    full_name VARCHAR(255) NOT NULL,
    job_title VARCHAR(255) NOT NULL,
    department_id BIGINT NOT NULL REFERENCES departments(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    active_from DATE,
    active_to DATE,
    archived_at TIMESTAMP,
    archived_by BIGINT,
    archive_reason TEXT
);

-- Админ по умолчанию: admin / admin (bcrypt через pgcrypto)
INSERT INTO users (username, password, role, full_name, job_title, department_id)
VALUES ('admin', crypt('admin', gen_salt('bf', 10)), 'ADMIN', 'Иванов Админ Иванович', 'Доцент', 1),
       ('Тестовый преподаватель', crypt('1234567890', gen_salt('bf', 10)), 'TEACHER', 'Петров Препод Петрович', 'Профессор', 2),
       ('учебный_отдел', crypt('1234567890', gen_salt('bf', 10)), 'EDUCATION_OFFICE', 'Оператор учебного отдела', 'Учебный отдел', 1),
       ('кафедра_иб', crypt('1234567890', gen_salt('bf', 10)), 'DEPARTMENT', 'Оператор кафедры ИБ', 'Кафедра', 1),
       ('просмотр_расписаний', crypt('1234567890', gen_salt('bf', 10)), 'SCHEDULE_VIEWER', 'Оператор просмотра расписаний', 'Просмотр расписаний', 1)
ON CONFLICT (username) DO NOTHING;

CREATE TABLE IF NOT EXISTS auth_refresh_tokens (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    tenant VARCHAR(100) NOT NULL,
    token_hash VARCHAR(64) UNIQUE NOT NULL,
    issued_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    revoked_at TIMESTAMP,
    rotated_to_token_hash VARCHAR(64),
    user_agent VARCHAR(512),
    ip_address VARCHAR(64)
);

CREATE INDEX IF NOT EXISTS idx_auth_refresh_tokens_user
    ON auth_refresh_tokens(user_id);

CREATE INDEX IF NOT EXISTS idx_auth_refresh_tokens_tenant_expires
    ON auth_refresh_tokens(tenant, expires_at);

CREATE TABLE IF NOT EXISTS teacher_department_assignments (
    id BIGSERIAL PRIMARY KEY,
    teacher_id BIGINT NOT NULL REFERENCES users(id),
    department_id BIGINT NOT NULL REFERENCES departments(id),
    valid_from DATE NOT NULL,
    valid_to DATE,
    is_primary BOOLEAN NOT NULL DEFAULT TRUE,
    comment TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by BIGINT REFERENCES users(id),
    CONSTRAINT chk_teacher_department_dates CHECK (valid_to IS NULL OR valid_to >= valid_from)
);

CREATE INDEX IF NOT EXISTS idx_teacher_department_teacher_dates
    ON teacher_department_assignments(teacher_id, valid_from, valid_to);

CREATE INDEX IF NOT EXISTS idx_teacher_department_department_dates
    ON teacher_department_assignments(department_id, valid_from, valid_to);

CREATE UNIQUE INDEX IF NOT EXISTS uq_teacher_department_open_primary
    ON teacher_department_assignments(teacher_id)
    WHERE is_primary = TRUE AND valid_to IS NULL;

CREATE UNIQUE INDEX IF NOT EXISTS uq_teacher_department_open_pair
    ON teacher_department_assignments(teacher_id, department_id)
    WHERE valid_to IS NULL;

INSERT INTO teacher_department_assignments (teacher_id, department_id, valid_from, is_primary, comment)
SELECT u.id, u.department_id, COALESCE(u.created_at::DATE, CURRENT_DATE), TRUE, 'Начальная кафедра из users.department_id'
FROM users u
WHERE u.role = 'TEACHER'
  AND NOT EXISTS (
      SELECT 1
      FROM teacher_department_assignments existing
      WHERE existing.teacher_id = u.id
  );

CREATE TABLE IF NOT EXISTS teacher_creation_requests (
    id BIGSERIAL PRIMARY KEY,
    department_id BIGINT NOT NULL REFERENCES departments(id),
    username VARCHAR(50) NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    job_title VARCHAR(255) NOT NULL,
    comment TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    requested_by BIGINT REFERENCES users(id),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reviewed_by BIGINT REFERENCES users(id),
    reviewed_at TIMESTAMP,
    review_comment TEXT,
    created_teacher_id BIGINT REFERENCES users(id),
    CONSTRAINT chk_teacher_creation_request_status CHECK (status IN ('PENDING', 'APPROVED', 'REJECTED'))
);

CREATE INDEX IF NOT EXISTS idx_teacher_creation_requests_department
    ON teacher_creation_requests(department_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_teacher_creation_requests_status
    ON teacher_creation_requests(status, created_at DESC);

CREATE UNIQUE INDEX IF NOT EXISTS uq_teacher_creation_requests_pending_username
    ON teacher_creation_requests(username)
    WHERE status = 'PENDING';

-- ==========================================
-- Образовательные формы
-- ==========================================
CREATE TABLE IF NOT EXISTS education_forms (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    active_from DATE,
    active_to DATE,
    archived_at TIMESTAMP,
    archived_by BIGINT,
    archive_reason TEXT
);

INSERT INTO education_forms (name) VALUES 
    ('Бакалавриат'), 
    ('Магистратура'), 
    ('Специалитет')
ON CONFLICT (name) DO NOTHING;

-- ==========================================
-- Учебные группы
-- ==========================================
CREATE TABLE IF NOT EXISTS student_groups (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    group_size BIGINT NOT NULL,
    education_form_id BIGINT NOT NULL REFERENCES education_forms(id),
    department_id BIGINT NOT NULL REFERENCES departments(id),
    specialty_id BIGINT NOT NULL REFERENCES specialties(id),
    specialty_profile_id BIGINT NOT NULL REFERENCES specialty_profiles(id),
    year_start_study BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    active_from DATE,
    active_to DATE,
    archived_at TIMESTAMP,
    archived_by BIGINT,
    archive_reason TEXT
);

-- Тестовая базовая группа для работы
INSERT INTO student_groups (name, group_size, education_form_id, department_id, specialty_id, specialty_profile_id, year_start_study)
SELECT data.name, data.group_size, ef.id, dept.id, specialty.id, profile.id, data.year_start_study
FROM (VALUES
    ('ИВТ-21-1', 25::BIGINT, 'Бакалавриат', 'Кафедра ИБ', '09.03.01', 2025::BIGINT),
    ('ИБ-41м', 15::BIGINT, 'Магистратура', 'Кафедра ИБ', '10.03.01', 2024::BIGINT)
) AS data(name, group_size, education_form_name, department_name, specialty_code, year_start_study)
JOIN education_forms ef ON ef.name = data.education_form_name
JOIN departments dept ON dept.name = data.department_name
JOIN specialties specialty ON specialty.specialty_code = data.specialty_code
JOIN specialty_profiles profile ON profile.specialty_id = specialty.id AND profile.name = 'Без профиля'
WHERE NOT EXISTS (SELECT 1 FROM student_groups existing WHERE existing.name = data.name);

-- ==========================================
-- Подгруппы (например: "ИВТ-21-1 Подгруппа 1")
-- ==========================================
CREATE TABLE IF NOT EXISTS subgroups (
    id BIGSERIAL PRIMARY KEY,
    group_id BIGINT NOT NULL REFERENCES student_groups(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    student_capacity INT,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    active_from DATE,
    active_to DATE,
    archived_at TIMESTAMP,
    archived_by BIGINT,
    archive_reason TEXT,
    UNIQUE(group_id, name)
);

INSERT INTO subgroups (group_id, name, student_capacity)
SELECT
    sg.id,
    data.name,
    CASE
        WHEN data.part_number = 1 THEN CEIL(sg.group_size::NUMERIC / 2)::INT
        ELSE FLOOR(sg.group_size::NUMERIC / 2)::INT
    END
FROM student_groups sg
CROSS JOIN (VALUES
    ('Подгруппа 1', 1),
    ('Подгруппа 2', 2)
) AS data(name, part_number)
WHERE sg.name IN ('ИВТ-21-1', 'ИБ-41м')
ON CONFLICT (group_id, name) DO NOTHING;

-- ==========================================
-- Справочники
-- ==========================================

-- Дисциплины
CREATE TABLE IF NOT EXISTS subjects (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) UNIQUE NOT NULL,
    code VARCHAR(20),
    department_id BIGINT NOT NULL REFERENCES departments(id),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    active_from DATE,
    active_to DATE,
    archived_at TIMESTAMP,
    archived_by BIGINT,
    archive_reason TEXT
);

INSERT INTO subjects (name, department_id) VALUES 
    ('Высшая математика', 1), 
    ('Философия', 1), 
    ('Информатика', 1), 
    ('Базы данных', 1),
    ('Английский язык', 1)
ON CONFLICT (name) DO NOTHING;

CREATE TABLE IF NOT EXISTS subject_comments (
    id BIGSERIAL PRIMARY KEY,
    subject_id BIGINT NOT NULL REFERENCES subjects(id),
    author_id BIGINT REFERENCES users(id),
    comment TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_subject_comments_subject
    ON subject_comments(subject_id, created_at DESC);

-- Типы занятий
CREATE TABLE IF NOT EXISTS lesson_types (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    color_code VARCHAR(7) DEFAULT '#3788d8',
    duration_minutes INT DEFAULT 90
);

INSERT INTO lesson_types (name, color_code) VALUES 
    ('Лекция', '#FF6B6B'),
    ('Практика', '#4ECDC4'),
    ('Лабораторная работа', '#45B7D1')
ON CONFLICT (name) DO NOTHING;

-- Оборудование
CREATE TABLE IF NOT EXISTS equipments (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    inventory_number VARCHAR(50),
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    active_from DATE,
    active_to DATE,
    archived_at TIMESTAMP,
    archived_by BIGINT,
    archive_reason TEXT
);

INSERT INTO equipments (name) VALUES 
    ('Проектор'), 
    ('ПК'), 
    ('Лаборатория'),
    ('Интерактивная доска'),
    ('Документ-камера'),
    ('Аудиосистема')
ON CONFLICT (name) DO NOTHING;

-- Аудитории
CREATE TABLE IF NOT EXISTS classrooms (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    capacity INT NOT NULL CHECK (capacity > 0),
    building VARCHAR(50),
    floor INT,
    is_available BOOLEAN DEFAULT TRUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    active_from DATE,
    active_to DATE,
    archived_at TIMESTAMP,
    archived_by BIGINT,
    archive_reason TEXT
);

INSERT INTO classrooms (name, capacity, building, floor) VALUES 
    ('101 Ленинская', 120, 'Главный корпус', 1),
    ('202 IT Lab', 20, 'Корпус IT', 2),
    ('303 Обычная', 30, 'Главный корпус', 3)
ON CONFLICT (name) DO NOTHING;

-- Привязка оборудования к аудиториям (Many-to-Many)
CREATE TABLE IF NOT EXISTS classroom_equipments (
    classroom_id BIGINT NOT NULL REFERENCES classrooms(id) ON DELETE CASCADE,
    equipment_id BIGINT NOT NULL REFERENCES equipments(id) ON DELETE CASCADE,
    quantity INT DEFAULT 1 CHECK (quantity > 0),
    notes TEXT,
    PRIMARY KEY (classroom_id, equipment_id)
);

INSERT INTO classroom_equipments (classroom_id, equipment_id, quantity)
SELECT c.id, e.id, 
    CASE 
        WHEN e.name = 'ПК' AND c.name = '202 IT Lab' THEN 15
        WHEN e.name = 'ПК' THEN 1
        ELSE 1
    END
FROM classrooms c, equipments e 
WHERE 
    (c.name = '101 Ленинская' AND e.name IN ('Проектор', 'Интерактивная доска', 'Аудиосистема'))
    OR (c.name = '202 IT Lab' AND e.name IN ('ПК', 'Проектор', 'Лаборатория', 'Интерактивная доска'))
    OR (c.name = '303 Обычная' AND e.name IN ('Проектор'))
ON CONFLICT (classroom_id, equipment_id) DO NOTHING;

CREATE INDEX IF NOT EXISTS idx_departments_status ON departments(status);
CREATE INDEX IF NOT EXISTS idx_specialties_status ON specialties(status);
CREATE INDEX IF NOT EXISTS idx_specialty_profiles_status ON specialty_profiles(status);
CREATE INDEX IF NOT EXISTS idx_users_status ON users(status);
CREATE INDEX IF NOT EXISTS idx_education_forms_status ON education_forms(status);
CREATE INDEX IF NOT EXISTS idx_student_groups_status ON student_groups(status);
CREATE INDEX IF NOT EXISTS idx_subgroups_status ON subgroups(status);
CREATE INDEX IF NOT EXISTS idx_subjects_status ON subjects(status);
CREATE INDEX IF NOT EXISTS idx_equipments_status ON equipments(status);
CREATE INDEX IF NOT EXISTS idx_classrooms_status ON classrooms(status);

-- ==========================================
-- Связи для преподавателей
-- ==========================================

CREATE TABLE IF NOT EXISTS teacher_subjects (
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    subject_id BIGINT NOT NULL REFERENCES subjects(id) ON DELETE CASCADE,
    qualification_level VARCHAR(50),
    experience_years INT,
    PRIMARY KEY(user_id, subject_id)
);

CREATE TABLE IF NOT EXISTS teacher_lesson_types (
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    subject_id BIGINT NOT NULL REFERENCES subjects(id) ON DELETE CASCADE,
    lesson_type_id BIGINT NOT NULL REFERENCES lesson_types(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, subject_id, lesson_type_id)
);

-- ==========================================
-- Динамическое расписание: календарь, слоты и правила
-- ==========================================

CREATE TABLE IF NOT EXISTS time_slot_scopes (
    id BIGSERIAL PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(120) NOT NULL,
    apply_mode VARCHAR(20) NOT NULL,
    day_of_week INT NULL,
    system_scope BOOLEAN NOT NULL DEFAULT FALSE,
    display_order INT NOT NULL DEFAULT 100,
    CONSTRAINT chk_time_slot_scopes_mode CHECK (apply_mode IN ('DEFAULT', 'WEEKDAY', 'MANUAL')),
    CONSTRAINT chk_time_slot_scopes_day CHECK (day_of_week IS NULL OR day_of_week BETWEEN 1 AND 7),
    CONSTRAINT chk_time_slot_scopes_weekday CHECK (
        (apply_mode = 'WEEKDAY' AND day_of_week IS NOT NULL)
        OR (apply_mode <> 'WEEKDAY' AND day_of_week IS NULL)
    )
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_time_slot_scopes_default
    ON time_slot_scopes(apply_mode)
    WHERE apply_mode = 'DEFAULT';

CREATE UNIQUE INDEX IF NOT EXISTS uq_time_slot_scopes_weekday
    ON time_slot_scopes(day_of_week)
    WHERE apply_mode = 'WEEKDAY';

INSERT INTO time_slot_scopes (code, name, apply_mode, day_of_week, system_scope, display_order) VALUES
    ('default', 'Базовая сетка', 'DEFAULT', NULL, TRUE, 10),
    ('saturday', 'Субботняя сетка', 'WEEKDAY', 6, TRUE, 20)
ON CONFLICT (code) DO NOTHING;

CREATE TABLE IF NOT EXISTS time_slots (
    id BIGSERIAL PRIMARY KEY,
    time_slot_scope_id BIGINT NOT NULL REFERENCES time_slot_scopes(id) ON DELETE CASCADE,
    order_number INT NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    duration_minutes INT NOT NULL,
    CONSTRAINT chk_time_slots_order_positive CHECK (order_number > 0),
    CONSTRAINT chk_time_slots_time_range CHECK (start_time < end_time),
    CONSTRAINT chk_time_slots_duration_positive CHECK (duration_minutes > 0)
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_time_slots_scope_order
    ON time_slots(time_slot_scope_id, order_number);

CREATE INDEX IF NOT EXISTS idx_time_slots_scope ON time_slots(time_slot_scope_id, order_number);

INSERT INTO time_slots (time_slot_scope_id, order_number, start_time, end_time, duration_minutes)
SELECT scope.id, data.order_number, data.start_time::TIME, data.end_time::TIME, data.duration_minutes
FROM (VALUES
    (1, '08:00', '09:30', 90),
    (2, '09:40', '11:10', 90),
    (3, '11:40', '13:10', 90),
    (4, '13:20', '14:50', 90),
    (5, '15:00', '16:30', 90),
    (6, '16:50', '18:20', 90),
    (7, '18:30', '20:00', 90)
) AS data(order_number, start_time, end_time, duration_minutes)
CROSS JOIN time_slot_scopes scope
WHERE scope.code IN ('default', 'saturday')
ON CONFLICT DO NOTHING;

CREATE TABLE IF NOT EXISTS time_slot_date_assignments (
    id BIGSERIAL PRIMARY KEY,
    assignment_date DATE NOT NULL UNIQUE,
    time_slot_scope_id BIGINT NOT NULL REFERENCES time_slot_scopes(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_time_slot_date_assignments_scope
    ON time_slot_date_assignments(time_slot_scope_id);

CREATE TABLE IF NOT EXISTS academic_years (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(20) NOT NULL UNIQUE,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    CONSTRAINT chk_academic_years_dates CHECK (start_date <= end_date)
);

INSERT INTO academic_years (title, start_date, end_date) VALUES
    ('2024-2025', '2024-09-01', '2025-06-30'),
    ('2025-2026', '2025-09-01', '2026-06-30')
ON CONFLICT (title) DO NOTHING;

CREATE TABLE IF NOT EXISTS semesters (
    id BIGSERIAL PRIMARY KEY,
    academic_year_id BIGINT NOT NULL REFERENCES academic_years(id) ON DELETE CASCADE,
    semester_type VARCHAR(20) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    CONSTRAINT chk_semesters_type CHECK (semester_type IN ('autumn', 'spring')),
    CONSTRAINT chk_semesters_dates CHECK (start_date <= end_date),
    CONSTRAINT uq_semesters_year_type UNIQUE (academic_year_id, semester_type)
);

INSERT INTO semesters (academic_year_id, semester_type, start_date, end_date)
SELECT id, 'autumn', start_date, MAKE_DATE(SPLIT_PART(title, '-', 2)::INT, 1, 31)
FROM academic_years
ON CONFLICT (academic_year_id, semester_type) DO NOTHING;

INSERT INTO semesters (academic_year_id, semester_type, start_date, end_date)
SELECT id, 'spring', MAKE_DATE(SPLIT_PART(title, '-', 2)::INT, 2, 1), end_date
FROM academic_years
ON CONFLICT (academic_year_id, semester_type) DO NOTHING;

CREATE INDEX IF NOT EXISTS idx_semesters_dates ON semesters(start_date, end_date);

CREATE TABLE IF NOT EXISTS academic_calendar_activity_types (
    id BIGSERIAL PRIMARY KEY,
    code VARCHAR(10) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    allow_schedule BOOLEAN NOT NULL DEFAULT FALSE,
    color_code VARCHAR(7) NOT NULL DEFAULT '#64748b',
    display_order INT NOT NULL DEFAULT 100,
    description TEXT
);

INSERT INTO academic_calendar_activity_types (code, name, allow_schedule, color_code, display_order, description) VALUES
    ('Т', 'Теоретическое обучение', TRUE, '#10b981', 10, 'Обычные занятия динамического расписания разрешены'),
    ('Э', 'Экзаменационная сессия', FALSE, '#f59e0b', 20, 'Обычные занятия не генерируются'),
    ('К', 'Каникулы', FALSE, '#38bdf8', 30, 'Обычные занятия не генерируются'),
    ('У', 'Учебная практика', FALSE, '#a78bfa', 40, 'Обычные занятия не генерируются'),
    ('П', 'Производственная практика', FALSE, '#fb7185', 50, 'Обычные занятия не генерируются'),
    ('Пд', 'Преддипломная практика', FALSE, '#f97316', 60, 'Обычные занятия не генерируются'),
    ('Н', 'Научно-исследовательская работа', FALSE, '#22c55e', 70, 'Обычные занятия не генерируются'),
    ('Г', 'Подготовка к сдаче и сдача государственного экзамена', FALSE, '#ef4444', 80, 'Обычные занятия не генерируются'),
    ('Д', 'Подготовка к защите и защита выпускной квалификационной работы', FALSE, '#dc2626', 90, 'Обычные занятия не генерируются'),
    ('ПА', 'Повторная промежуточная аттестация', FALSE, '#eab308', 100, 'Обычные занятия не генерируются'),
    ('С', 'Симуляционный курс', FALSE, '#06b6d4', 110, 'Обычные занятия не генерируются'),
    ('*', 'Нерабочий праздничный день', FALSE, '#64748b', 120, 'День исключается из генерации расписания'),
    ('=', 'День вне учебного года', FALSE, '#475569', 130, 'День исключается из генерации расписания')
ON CONFLICT (code) DO NOTHING;

CREATE TABLE IF NOT EXISTS academic_calendars (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    academic_year_id BIGINT NOT NULL REFERENCES academic_years(id) ON DELETE CASCADE,
    specialty_id BIGINT NOT NULL REFERENCES specialties(id),
    specialty_profile_id BIGINT NOT NULL REFERENCES specialty_profiles(id),
    study_form_id BIGINT NOT NULL REFERENCES education_forms(id),
    course_count INT NOT NULL DEFAULT 4,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_academic_calendars_course_count CHECK (course_count BETWEEN 1 AND 8),
    CONSTRAINT uq_academic_calendar_title UNIQUE (academic_year_id, specialty_profile_id, study_form_id, title)
);

CREATE INDEX IF NOT EXISTS idx_academic_calendars_context
    ON academic_calendars(academic_year_id, specialty_id, specialty_profile_id, study_form_id);

INSERT INTO academic_calendars (title, academic_year_id, specialty_id, specialty_profile_id, study_form_id, course_count)
SELECT
    specialty.specialty_code || ' ' || profile.name || ' ' || study_form.name || ' ' || ay.title,
    ay.id,
    specialty.id,
    profile.id,
    study_form.id,
    4
FROM academic_years ay
CROSS JOIN education_forms study_form
JOIN specialties specialty ON specialty.specialty_code IN ('09.03.01', '10.03.01')
JOIN specialty_profiles profile ON profile.specialty_id = specialty.id AND profile.name = 'Без профиля'
WHERE ay.title = '2025-2026'
ON CONFLICT (academic_year_id, specialty_profile_id, study_form_id, title) DO NOTHING;

CREATE TABLE IF NOT EXISTS academic_calendar_days (
    id BIGSERIAL PRIMARY KEY,
    calendar_id BIGINT NOT NULL REFERENCES academic_calendars(id) ON DELETE CASCADE,
    course_number INT NOT NULL,
    date DATE NOT NULL,
    week_number INT NOT NULL,
    day_of_week INT NOT NULL,
    activity_type_id BIGINT NOT NULL REFERENCES academic_calendar_activity_types(id),
    CONSTRAINT chk_calendar_days_course_positive CHECK (course_number > 0),
    CONSTRAINT chk_calendar_days_week_positive CHECK (week_number > 0),
    CONSTRAINT chk_calendar_days_day CHECK (day_of_week BETWEEN 1 AND 7),
    CONSTRAINT uq_calendar_days UNIQUE (calendar_id, course_number, date)
);

CREATE INDEX IF NOT EXISTS idx_calendar_days_lookup
    ON academic_calendar_days(calendar_id, course_number, date);

INSERT INTO academic_calendar_days (calendar_id, course_number, date, week_number, day_of_week, activity_type_id)
SELECT
    calendar.id,
    course.course_number,
    days.date::DATE,
    ((days.date::DATE - ay.start_date) / 7) + 1,
    EXTRACT(ISODOW FROM days.date::DATE)::INT,
    activity.id
FROM academic_calendars calendar
JOIN academic_years ay ON ay.id = calendar.academic_year_id
JOIN academic_calendar_activity_types activity ON activity.code = 'Т'
CROSS JOIN GENERATE_SERIES(1, calendar.course_count) AS course(course_number)
CROSS JOIN GENERATE_SERIES(ay.start_date, ay.end_date, INTERVAL '1 day') AS days(date)
ON CONFLICT (calendar_id, course_number, date) DO NOTHING;

CREATE TABLE IF NOT EXISTS academic_calendar_subjects (
    id BIGSERIAL PRIMARY KEY,
    calendar_id BIGINT NOT NULL REFERENCES academic_calendars(id) ON DELETE CASCADE,
    semester_number INT NOT NULL,
    subject_id BIGINT NOT NULL REFERENCES subjects(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_academic_calendar_subjects_semester_positive CHECK (semester_number > 0),
    CONSTRAINT uq_academic_calendar_subject UNIQUE (calendar_id, semester_number, subject_id)
);

CREATE INDEX IF NOT EXISTS idx_academic_calendar_subjects_calendar_semester
    ON academic_calendar_subjects(calendar_id, semester_number);

CREATE INDEX IF NOT EXISTS idx_academic_calendar_subjects_subject
    ON academic_calendar_subjects(subject_id);

CREATE TABLE IF NOT EXISTS student_group_calendar_assignments (
    id BIGSERIAL PRIMARY KEY,
    group_id BIGINT NOT NULL REFERENCES student_groups(id) ON DELETE CASCADE,
    academic_year_id BIGINT NOT NULL REFERENCES academic_years(id) ON DELETE CASCADE,
    calendar_id BIGINT NOT NULL REFERENCES academic_calendars(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_group_calendar_year UNIQUE (group_id, academic_year_id)
);

CREATE INDEX IF NOT EXISTS idx_group_calendar_assignments_lookup
    ON student_group_calendar_assignments(group_id, academic_year_id);

INSERT INTO student_group_calendar_assignments (group_id, academic_year_id, calendar_id)
SELECT
    sg.id,
    ay.id,
    calendar.id
FROM student_groups sg
JOIN academic_years ay ON ay.title = '2025-2026'
JOIN academic_calendars calendar
    ON calendar.academic_year_id = ay.id
   AND calendar.specialty_profile_id = sg.specialty_profile_id
WHERE sg.name IN ('ИВТ-21-1', 'ИБ-41м')
ON CONFLICT (group_id, academic_year_id) DO NOTHING;

CREATE TABLE IF NOT EXISTS schedule_rules (
    id BIGSERIAL PRIMARY KEY,
    subject_id BIGINT NOT NULL REFERENCES subjects(id),
    semester_id BIGINT NOT NULL REFERENCES semesters(id) ON DELETE CASCADE,
    lecture_academic_hours INT NOT NULL,
    laboratory_academic_hours INT NOT NULL,
    practice_academic_hours INT NOT NULL,
    lecture_start_week INT NOT NULL,
    laboratory_start_week INT NOT NULL,
    practice_start_week INT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    valid_from DATE,
    valid_to DATE,
    version_group_id BIGINT,
    change_reason TEXT,
    created_by BIGINT REFERENCES users(id),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_schedule_rules_type_hours_non_negative CHECK (
        lecture_academic_hours >= 0
        AND laboratory_academic_hours >= 0
        AND practice_academic_hours >= 0
    ),
    CONSTRAINT chk_schedule_rules_has_type_hours CHECK (
        lecture_academic_hours + laboratory_academic_hours + practice_academic_hours > 0
    ),
    CONSTRAINT chk_schedule_rules_start_weeks_positive CHECK (
        lecture_start_week > 0
        AND laboratory_start_week > 0
        AND practice_start_week > 0
    ),
    CONSTRAINT chk_schedule_rules_dates CHECK (valid_to IS NULL OR valid_from IS NULL OR valid_to >= valid_from)
);

CREATE INDEX IF NOT EXISTS idx_schedule_rules_semester ON schedule_rules(semester_id);
CREATE INDEX IF NOT EXISTS idx_schedule_rules_subject ON schedule_rules(subject_id);
CREATE INDEX IF NOT EXISTS idx_schedule_rules_status_dates
    ON schedule_rules(status, valid_from, valid_to);

CREATE TABLE IF NOT EXISTS schedule_rule_groups (
    schedule_rule_id BIGINT NOT NULL REFERENCES schedule_rules(id) ON DELETE CASCADE,
    group_id BIGINT NOT NULL REFERENCES student_groups(id) ON DELETE CASCADE,
    PRIMARY KEY (schedule_rule_id, group_id)
);

CREATE INDEX IF NOT EXISTS idx_schedule_rule_groups_group ON schedule_rule_groups(group_id);

CREATE TABLE IF NOT EXISTS schedule_rule_slots (
    id BIGSERIAL PRIMARY KEY,
    schedule_rule_id BIGINT NOT NULL REFERENCES schedule_rules(id) ON DELETE CASCADE,
    day_of_week INT NOT NULL,
    parity VARCHAR(10) NOT NULL,
    time_slot_id BIGINT NOT NULL REFERENCES time_slots(id),
    teacher_id BIGINT NOT NULL REFERENCES users(id),
    classroom_id BIGINT NOT NULL REFERENCES classrooms(id),
    lesson_type_id BIGINT NOT NULL REFERENCES lesson_types(id),
    lesson_format VARCHAR(30) NOT NULL,
    time_locked BOOLEAN NOT NULL DEFAULT FALSE,
    classroom_locked BOOLEAN NOT NULL DEFAULT FALSE,
    teacher_locked BOOLEAN NOT NULL DEFAULT FALSE,
    locked_by BIGINT REFERENCES users(id),
    locked_at TIMESTAMP,
    lock_comment TEXT,
    CONSTRAINT chk_schedule_rule_slots_day CHECK (day_of_week BETWEEN 1 AND 7),
    CONSTRAINT chk_schedule_rule_slots_parity CHECK (parity IN ('BOTH', 'EVEN', 'ODD')),
    CONSTRAINT chk_schedule_rule_slots_format CHECK (lesson_format IN ('Очно', 'Онлайн'))
);

CREATE INDEX IF NOT EXISTS idx_schedule_rule_slots_rule ON schedule_rule_slots(schedule_rule_id);
CREATE INDEX IF NOT EXISTS idx_schedule_rule_slots_teacher ON schedule_rule_slots(teacher_id);
CREATE INDEX IF NOT EXISTS idx_schedule_rule_slots_classroom ON schedule_rule_slots(classroom_id);
CREATE INDEX IF NOT EXISTS idx_schedule_rule_slots_time ON schedule_rule_slots(day_of_week, parity, time_slot_id);

CREATE TABLE IF NOT EXISTS schedule_rule_slot_subgroups (
    schedule_rule_slot_id BIGINT NOT NULL REFERENCES schedule_rule_slots(id) ON DELETE CASCADE,
    subgroup_id BIGINT NOT NULL REFERENCES subgroups(id) ON DELETE CASCADE,
    PRIMARY KEY (schedule_rule_slot_id, subgroup_id)
);

CREATE INDEX IF NOT EXISTS idx_schedule_rule_slot_subgroups_subgroup ON schedule_rule_slot_subgroups(subgroup_id);

CREATE TABLE IF NOT EXISTS schedule_overrides (
    id BIGSERIAL PRIMARY KEY,
    base_rule_slot_id BIGINT NOT NULL REFERENCES schedule_rule_slots(id),
    lesson_date DATE NOT NULL,
    action VARCHAR(20) NOT NULL,
    new_time_slot_id BIGINT REFERENCES time_slots(id),
    new_classroom_id BIGINT REFERENCES classrooms(id),
    new_teacher_id BIGINT REFERENCES users(id),
    new_lesson_format VARCHAR(30),
    comment TEXT,
    created_by BIGINT REFERENCES users(id),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_schedule_overrides_action CHECK (action IN ('MOVE', 'CANCEL', 'REPLACE')),
    CONSTRAINT uq_schedule_overrides_slot_date UNIQUE (base_rule_slot_id, lesson_date)
);

CREATE INDEX IF NOT EXISTS idx_schedule_overrides_date
    ON schedule_overrides(lesson_date);

CREATE INDEX IF NOT EXISTS idx_schedule_overrides_new_teacher
    ON schedule_overrides(new_teacher_id);

CREATE OR REPLACE FUNCTION validate_schedule_rule_slot_subgroups()
RETURNS TRIGGER AS $$
DECLARE
    lesson_type_name VARCHAR(50);
    rule_id BIGINT;
    subgroup_group_id BIGINT;
    has_group_duplicate BOOLEAN;
BEGIN
    SELECT slot.schedule_rule_id, type.name INTO rule_id, lesson_type_name
    FROM schedule_rule_slots slot
    JOIN lesson_types type ON type.id = slot.lesson_type_id
    WHERE slot.id = NEW.schedule_rule_slot_id;

    IF lesson_type_name IS NULL OR LOWER(lesson_type_name) NOT LIKE '%лаб%' THEN
        RAISE EXCEPTION 'Подгруппы можно выбирать только для лабораторных занятий';
    END IF;

    SELECT group_id INTO subgroup_group_id
    FROM subgroups
    WHERE id = NEW.subgroup_id;

    IF subgroup_group_id IS NULL THEN
        RAISE EXCEPTION 'Подгруппа не найдена';
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM schedule_rule_groups rule_group
        WHERE rule_group.schedule_rule_id = rule_id
          AND rule_group.group_id = subgroup_group_id
    ) THEN
        RAISE EXCEPTION 'Подгруппа должна относиться к одной из групп правила';
    END IF;

    IF TG_OP = 'UPDATE' THEN
        SELECT EXISTS (
            SELECT 1
            FROM schedule_rule_slot_subgroups slot_subgroup
            JOIN subgroups subgroup ON subgroup.id = slot_subgroup.subgroup_id
            WHERE slot_subgroup.schedule_rule_slot_id = NEW.schedule_rule_slot_id
              AND subgroup.group_id = subgroup_group_id
              AND NOT (
                  slot_subgroup.schedule_rule_slot_id = OLD.schedule_rule_slot_id
                  AND slot_subgroup.subgroup_id = OLD.subgroup_id
              )
        ) INTO has_group_duplicate;
    ELSE
        SELECT EXISTS (
            SELECT 1
            FROM schedule_rule_slot_subgroups slot_subgroup
            JOIN subgroups subgroup ON subgroup.id = slot_subgroup.subgroup_id
            WHERE slot_subgroup.schedule_rule_slot_id = NEW.schedule_rule_slot_id
              AND subgroup.group_id = subgroup_group_id
        ) INTO has_group_duplicate;
    END IF;

    IF has_group_duplicate THEN
        RAISE EXCEPTION 'В одном слоте можно выбрать не больше одной подгруппы каждой группы';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_schedule_rule_slot_subgroups
    BEFORE INSERT OR UPDATE ON schedule_rule_slot_subgroups
    FOR EACH ROW
    EXECUTE FUNCTION validate_schedule_rule_slot_subgroups();

CREATE TEMP TABLE tmp_default_rules AS
SELECT
    NEXTVAL(PG_GET_SERIAL_SEQUENCE('schedule_rules', 'id')) AS schedule_rule_id,
    rule_key,
    subject_id,
    semester_id,
    lecture_academic_hours,
    laboratory_academic_hours,
    practice_academic_hours,
    lecture_start_week,
    laboratory_start_week,
    practice_start_week
FROM (
    SELECT
        data.rule_key,
        subj.id AS subject_id,
        sem.id AS semester_id,
        data.lecture_academic_hours,
        data.laboratory_academic_hours,
        data.practice_academic_hours,
        data.lecture_start_week,
        data.laboratory_start_week,
        data.practice_start_week
    FROM (VALUES
        ('stream_math', 'Высшая математика', '2025-2026', 'spring', 72, 0, 0, 1, 1, 1),
        ('ivt_informatics_lab', 'Информатика', '2025-2026', 'spring', 0, 72, 0, 1, 1, 1),
        ('ivt_databases_practice', 'Базы данных', '2025-2026', 'spring', 0, 0, 72, 1, 1, 1),
        ('ib_philosophy', 'Философия', '2025-2026', 'spring', 54, 0, 0, 1, 1, 1)
    ) AS data(
        rule_key,
        subject_name,
        year_title,
        semester_type,
        lecture_academic_hours,
        laboratory_academic_hours,
        practice_academic_hours,
        lecture_start_week,
        laboratory_start_week,
        practice_start_week
    )
    JOIN subjects subj ON subj.name = data.subject_name
    JOIN academic_years ay ON ay.title = data.year_title
    JOIN semesters sem ON sem.academic_year_id = ay.id AND sem.semester_type = data.semester_type
) prepared_rules;

INSERT INTO schedule_rules (
    id,
    subject_id,
    semester_id,
    lecture_academic_hours,
    laboratory_academic_hours,
    practice_academic_hours,
    lecture_start_week,
    laboratory_start_week,
    practice_start_week
)
SELECT
    schedule_rule_id,
    subject_id,
    semester_id,
    lecture_academic_hours,
    laboratory_academic_hours,
    practice_academic_hours,
    lecture_start_week,
    laboratory_start_week,
    practice_start_week
FROM tmp_default_rules;

INSERT INTO schedule_rule_groups (schedule_rule_id, group_id)
SELECT
    rules.schedule_rule_id,
    sg.id
FROM (VALUES
    ('stream_math', 'ИВТ-21-1'),
    ('stream_math', 'ИБ-41м'),
    ('ivt_informatics_lab', 'ИВТ-21-1'),
    ('ivt_databases_practice', 'ИВТ-21-1'),
    ('ib_philosophy', 'ИБ-41м')
) AS data(rule_key, group_name)
JOIN tmp_default_rules rules ON rules.rule_key = data.rule_key
JOIN student_groups sg ON sg.name = data.group_name
ON CONFLICT DO NOTHING;

INSERT INTO schedule_rule_slots (
    schedule_rule_id,
    day_of_week,
    parity,
    time_slot_id,
    teacher_id,
    classroom_id,
    lesson_type_id,
    lesson_format
)
SELECT
    rules.schedule_rule_id,
    data.day_of_week,
    data.parity,
    ts.id,
    teacher.id,
    classroom.id,
    lt.id,
    data.lesson_format
FROM (VALUES
    ('stream_math', 1, 'BOTH', 3, 'Тестовый преподаватель', '101 Ленинская', 'Лекция', 'Очно'),
    ('ivt_informatics_lab', 3, 'BOTH', 1, 'Тестовый преподаватель', '202 IT Lab', 'Лабораторная работа', 'Очно'),
    ('ivt_databases_practice', 5, 'EVEN', 5, 'Тестовый преподаватель', '303 Обычная', 'Практика', 'Онлайн'),
    ('ib_philosophy', 2, 'ODD', 2, 'Тестовый преподаватель', '101 Ленинская', 'Лекция', 'Онлайн')
) AS data(rule_key, day_of_week, parity, time_slot_order, teacher_username, classroom_name, lesson_type_name, lesson_format)
JOIN tmp_default_rules rules ON rules.rule_key = data.rule_key
JOIN time_slot_scopes scope ON scope.code = 'default'
JOIN time_slots ts ON ts.time_slot_scope_id = scope.id AND ts.order_number = data.time_slot_order
JOIN users teacher ON teacher.username = data.teacher_username
JOIN classrooms classroom ON classroom.name = data.classroom_name
JOIN lesson_types lt ON lt.name = data.lesson_type_name;

-- ==========================================
-- Функция обновления timestamp
-- ==========================================
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_academic_calendars_updated_at
    BEFORE UPDATE ON academic_calendars
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- ==========================================
-- Комментарии к таблицам и полям (для документации)
-- ==========================================
COMMENT ON TABLE users IS 'Пользователи системы (студенты, преподаватели, администраторы)';
COMMENT ON TABLE departments IS 'Кафедры';
COMMENT ON TABLE specialties IS 'Специальности';
COMMENT ON TABLE specialty_profiles IS 'Профили обучения внутри специальности';
COMMENT ON TABLE time_slot_scopes IS 'Именованные сетки времени: базовая, автоматическая субботняя и ручные сетки';
COMMENT ON TABLE time_slots IS 'Настраиваемая сетка временных слотов занятий';
COMMENT ON TABLE time_slot_date_assignments IS 'Ручное назначение сетки времени на конкретную дату';
COMMENT ON TABLE academic_years IS 'Учебные годы';
COMMENT ON TABLE semesters IS 'Семестры учебного года';
COMMENT ON TABLE academic_calendar_activity_types IS 'Коды активностей календарного учебного графика';
COMMENT ON TABLE academic_calendars IS 'Календарные учебные графики по профилю, форме обучения и учебному году';
COMMENT ON TABLE academic_calendar_days IS 'Дневные ячейки календарного учебного графика';
COMMENT ON TABLE academic_calendar_subjects IS 'Привязка дисциплин к календарному учебному графику по номерам учебных семестров';
COMMENT ON TABLE student_group_calendar_assignments IS 'Назначения календарных графиков учебным группам по учебному году';
COMMENT ON TABLE schedule_rules IS 'Правила динамической генерации расписания';
COMMENT ON TABLE schedule_rule_groups IS 'Привязка правил расписания к учебным группам';
COMMENT ON TABLE schedule_rule_slots IS 'Слоты проведения занятий внутри правила расписания';
COMMENT ON TABLE schedule_rule_slot_subgroups IS 'Подгруппы лабораторных слотов правила расписания';
COMMENT ON TABLE teacher_department_assignments IS 'История принадлежности преподавателей к кафедрам';
COMMENT ON TABLE teacher_creation_requests IS 'Заявки кафедр на создание преподавателей';
COMMENT ON TABLE subject_comments IS 'Комментарии кафедр и администраторов к дисциплинам';
COMMENT ON TABLE schedule_overrides IS 'Точечные изменения конкретных сгенерированных пар';
COMMENT ON COLUMN schedule_rules.lecture_academic_hours IS 'Лимит академических часов лекций';
COMMENT ON COLUMN schedule_rules.laboratory_academic_hours IS 'Лимит академических часов лабораторных работ';
COMMENT ON COLUMN schedule_rules.practice_academic_hours IS 'Лимит академических часов практик';
COMMENT ON COLUMN schedule_rules.lecture_start_week IS 'Неделя начала лекций в семестре';
COMMENT ON COLUMN schedule_rules.laboratory_start_week IS 'Неделя начала лабораторных работ в семестре';
COMMENT ON COLUMN schedule_rules.practice_start_week IS 'Неделя начала практик в семестре';
COMMENT ON COLUMN schedule_rules.valid_from IS 'Дата начала действия версии правила расписания';
COMMENT ON COLUMN schedule_rules.valid_to IS 'Дата окончания действия версии правила расписания';

COMMENT ON TABLE education_forms IS 'Формы обучения';
COMMENT ON TABLE subgroups IS 'Подгруппы';
COMMENT ON TABLE lesson_types IS 'Типы занятий';
COMMENT ON TABLE equipments IS 'Оборудование';
COMMENT ON TABLE classrooms IS 'Аудитории';
COMMENT ON TABLE classroom_equipments IS 'Привязка оборудования к аудиториям';
COMMENT ON TABLE teacher_subjects IS 'Привязка преподавателей к дисциплинам';
COMMENT ON TABLE teacher_lesson_types IS 'Типы занятий преподавателя';

COMMENT ON COLUMN users.id IS 'ID пользователя';
COMMENT ON COLUMN users.username IS 'Логин пользователя';
COMMENT ON COLUMN users.password IS 'Хэш пароля пользователя';
COMMENT ON COLUMN users.role IS 'Роль пользователя';
COMMENT ON COLUMN users.status IS 'Жизненный цикл пользователя: ACTIVE или ARCHIVED';
COMMENT ON COLUMN users.created_at IS 'Дата и время создания';
COMMENT ON COLUMN users.updated_at IS 'Дата и время последнего обновления';
COMMENT ON COLUMN users.full_name IS 'ФИО пользователя';
COMMENT ON COLUMN users.job_title IS 'Должность пользователя';
COMMENT ON COLUMN users.department_id IS 'ID кафедры';

COMMENT ON TABLE auth_refresh_tokens IS 'Отзывные refresh-сессии JWT. Сырые refresh-токены не хранятся, только SHA-256 хэш';
COMMENT ON COLUMN auth_refresh_tokens.user_id IS 'ID пользователя, которому выдан refresh-токен';
COMMENT ON COLUMN auth_refresh_tokens.tenant IS 'Тенант, в рамках которого выдан refresh-токен';
COMMENT ON COLUMN auth_refresh_tokens.token_hash IS 'SHA-256 хэш refresh-токена';
COMMENT ON COLUMN auth_refresh_tokens.issued_at IS 'Дата и время выдачи refresh-токена';
COMMENT ON COLUMN auth_refresh_tokens.expires_at IS 'Дата и время истечения refresh-токена';
COMMENT ON COLUMN auth_refresh_tokens.revoked_at IS 'Дата и время отзыва refresh-токена';
COMMENT ON COLUMN auth_refresh_tokens.rotated_to_token_hash IS 'Хэш следующего refresh-токена после ротации';
COMMENT ON COLUMN auth_refresh_tokens.user_agent IS 'User-Agent клиента при выдаче токена';
COMMENT ON COLUMN auth_refresh_tokens.ip_address IS 'IP-адрес клиента при выдаче токена';

COMMENT ON COLUMN education_forms.id IS 'ID формы обучения';
COMMENT ON COLUMN education_forms.name IS 'Название формы обучения';
COMMENT ON COLUMN education_forms.description IS 'Описание';
COMMENT ON COLUMN education_forms.created_at IS 'Дата и время создания';

COMMENT ON COLUMN student_groups.id IS 'ID учебной группы';
COMMENT ON COLUMN student_groups.name IS 'Название группы';
COMMENT ON COLUMN student_groups.group_size IS 'Количество студентов';
COMMENT ON COLUMN student_groups.education_form_id IS 'ID формы обучения, к которой относится группа';
COMMENT ON COLUMN student_groups.department_id IS 'ID кафедры';
COMMENT ON COLUMN student_groups.specialty_id IS 'ID специальности';
COMMENT ON COLUMN student_groups.specialty_profile_id IS 'ID профиля обучения';
COMMENT ON COLUMN student_groups.year_start_study IS 'Год начала обучения';
COMMENT ON COLUMN student_groups.created_at IS 'Дата и время создания';

COMMENT ON COLUMN subgroups.id IS 'ID подгруппы';
COMMENT ON COLUMN subgroups.group_id IS 'ID учебной группы, к которой относится подгруппа';
COMMENT ON COLUMN subgroups.name IS 'Название подгруппы';
COMMENT ON COLUMN subgroups.student_capacity IS 'Количество студентов в подгруппе';

COMMENT ON COLUMN subjects.id IS 'ID предмета';
COMMENT ON COLUMN subjects.name IS 'Название предмета';
COMMENT ON COLUMN subjects.code IS 'Код предмета';
COMMENT ON COLUMN subjects.department_id IS 'ID кафедры';
COMMENT ON COLUMN subjects.description IS 'Описание предмета';
COMMENT ON COLUMN subjects.created_at IS 'Дата и время создания';

COMMENT ON COLUMN lesson_types.id IS 'ID урока';
COMMENT ON COLUMN lesson_types.name IS 'Название типа урока';
COMMENT ON COLUMN lesson_types.color_code IS 'Цветовой код для типа урока';
COMMENT ON COLUMN lesson_types.duration_minutes IS 'Длительность урока в минутах';

COMMENT ON COLUMN equipments.id IS 'ID оборудования';
COMMENT ON COLUMN equipments.name IS 'Название оборудования';
COMMENT ON COLUMN equipments.description IS 'Описание оборудования';
COMMENT ON COLUMN equipments.inventory_number IS 'Инвентарный номер оборудования';

COMMENT ON COLUMN classrooms.id IS 'ID аудитории';
COMMENT ON COLUMN classrooms.name IS 'Название аудитории';
COMMENT ON COLUMN classrooms.capacity IS 'Вместимость аудитории';
COMMENT ON COLUMN classrooms.building IS 'Корпус';
COMMENT ON COLUMN classrooms.floor IS 'Этаж';
COMMENT ON COLUMN classrooms.is_available IS 'Доступность аудитории';
COMMENT ON COLUMN classrooms.description IS 'Описание аудитории';
COMMENT ON COLUMN classrooms.created_at IS 'Дата и время создания';
COMMENT ON COLUMN classrooms.status IS 'Жизненный цикл аудитории: ACTIVE или ARCHIVED';

COMMENT ON COLUMN classroom_equipments.classroom_id IS 'ID аудитории';
COMMENT ON COLUMN classroom_equipments.equipment_id IS 'ID оборудования';
COMMENT ON COLUMN classroom_equipments.quantity IS 'Количество единиц оборудования';
COMMENT ON COLUMN classroom_equipments.notes IS 'Примечания к записи';

COMMENT ON COLUMN teacher_subjects.user_id IS 'ID преподавателя';
COMMENT ON COLUMN teacher_subjects.subject_id IS 'ID предмета';
COMMENT ON COLUMN teacher_subjects.qualification_level IS 'Уровень квалификации преподавателя';
COMMENT ON COLUMN teacher_subjects.experience_years IS 'Опыт преподавания';

COMMENT ON COLUMN departments.id IS 'ID кафедры';
COMMENT ON COLUMN departments.name IS 'Название кафедры';
COMMENT ON COLUMN departments.code IS 'Код кафедры';

COMMENT ON COLUMN specialties.id IS 'ID специальности';
COMMENT ON COLUMN specialties.name IS 'Название специальности';
COMMENT ON COLUMN specialties.specialty_code IS 'Код специальности';

COMMENT ON COLUMN specialty_profiles.id IS 'ID профиля обучения';
COMMENT ON COLUMN specialty_profiles.specialty_id IS 'ID специальности';
COMMENT ON COLUMN specialty_profiles.name IS 'Название профиля обучения';
COMMENT ON COLUMN specialty_profiles.description IS 'Описание профиля обучения';

COMMENT ON COLUMN academic_calendar_activity_types.code IS 'Код активности из календарного графика';
COMMENT ON COLUMN academic_calendar_activity_types.name IS 'Название активности';
COMMENT ON COLUMN academic_calendar_activity_types.allow_schedule IS 'Разрешает ли код генерацию обычного расписания';

COMMENT ON COLUMN academic_calendars.title IS 'Название календарного учебного графика';
COMMENT ON COLUMN academic_calendars.academic_year_id IS 'ID учебного года';
COMMENT ON COLUMN academic_calendars.specialty_id IS 'ID специальности';
COMMENT ON COLUMN academic_calendars.specialty_profile_id IS 'ID профиля обучения';
COMMENT ON COLUMN academic_calendars.study_form_id IS 'ID формы обучения из education_forms';
COMMENT ON COLUMN academic_calendars.course_count IS 'Количество курсов в графике';

COMMENT ON COLUMN academic_calendar_days.calendar_id IS 'ID календарного учебного графика';
COMMENT ON COLUMN academic_calendar_days.course_number IS 'Номер курса';
COMMENT ON COLUMN academic_calendar_days.date IS 'Дата';
COMMENT ON COLUMN academic_calendar_days.week_number IS 'Номер недели в учебном году';
COMMENT ON COLUMN academic_calendar_days.day_of_week IS 'День недели ISO';
COMMENT ON COLUMN academic_calendar_days.activity_type_id IS 'ID кода активности';

COMMENT ON COLUMN academic_calendar_subjects.calendar_id IS 'ID календарного учебного графика';
COMMENT ON COLUMN academic_calendar_subjects.semester_number IS 'Номер учебного семестра внутри графика: 1, 2, 3 ...';
COMMENT ON COLUMN academic_calendar_subjects.subject_id IS 'ID дисциплины из справочника subjects';
COMMENT ON COLUMN academic_calendar_subjects.created_at IS 'Дата и время создания привязки';

COMMENT ON COLUMN student_group_calendar_assignments.group_id IS 'ID учебной группы';
COMMENT ON COLUMN student_group_calendar_assignments.academic_year_id IS 'ID учебного года';
COMMENT ON COLUMN student_group_calendar_assignments.calendar_id IS 'ID назначенного календарного графика';

COMMENT ON COLUMN teacher_lesson_types.user_id IS 'ID преподавателя';
COMMENT ON COLUMN teacher_lesson_types.subject_id IS 'ID предмета';
COMMENT ON COLUMN teacher_lesson_types.lesson_type_id IS 'ID типа занятия';
