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

-- ==========================================
-- Пользователи и роли
-- ==========================================
CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL DEFAULT 'STUDENT',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Админ по умолчанию: admin / admin (bcrypt через pgcrypto)
INSERT INTO users (username, password, role)
VALUES ('admin', crypt('admin', gen_salt('bf', 10)), 'ADMIN')
ON CONFLICT (username) DO NOTHING;

-- ==========================================
-- Образовательные формы
-- ==========================================
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
);

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) UNIQUE NOT NULL,
    education_form_id BIGINT NOT NULL REFERENCES education_forms(id),
    course INT CHECK (course BETWEEN 1 AND 6),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Тестовая базовая группа для работы
INSERT INTO student_groups (name, education_form_id, course) 
VALUES ('ИВТ-21-1', 1, 3) 
ON CONFLICT (name) DO NOTHING;

-- ==========================================
-- Подгруппы (например: "ИВТ-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,
    UNIQUE(group_id, name)
);

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

-- Дисциплины
CREATE TABLE IF NOT EXISTS subjects (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) UNIQUE NOT NULL,
    code VARCHAR(20),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

-- Типы занятий
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)
);

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
);

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 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)
);

-- ==========================================
-- Основная таблица Расписания (Lessons)
-- ==========================================
CREATE TABLE IF NOT EXISTS lessons (
    id BIGSERIAL PRIMARY KEY,
    teacher_id BIGINT NOT NULL REFERENCES users(id),
    group_id BIGINT NOT NULL REFERENCES student_groups(id),
    lesson_type_id BIGINT NOT NULL REFERENCES lesson_types(id),
    day VARCHAR(255) NOT NULL,
    week VARCHAR(255) NOT NULL,
    time VARCHAR(255) NOT NULL
);

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

-- Триггеры для обновления updated_at
CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- ==========================================
-- Комментарии к таблицам и полям (для документации)
-- ==========================================
COMMENT ON TABLE users IS 'Пользователи системы (студенты, преподаватели, администраторы)';
COMMENT ON TABLE lessons IS 'Основное расписание занятий';