---

CREATE DATABASE IF NOT EXISTS practice3;
USE practice3;

-- Таблица пользователей
CREATE TABLE users (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50)  NOT NULL,
    last_name  VARCHAR(50)  NOT NULL,
    email      VARCHAR(100) UNIQUE NOT NULL,
    role       ENUM('student', 'teacher') DEFAULT 'student'
);

-- Таблица курсов (добавлен teacher_id для связи с преподавателем)
CREATE TABLE courses (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    title       VARCHAR(100) NOT NULL,
    description TEXT,
    duration    INT          NOT NULL,
    category    VARCHAR(100) NOT NULL,
    teacher_id  INT,
    FOREIGN KEY (teacher_id) REFERENCES users(id)
);

-- Таблица уроков
CREATE TABLE lessons (
    id               INT PRIMARY KEY AUTO_INCREMENT,
    topic            VARCHAR(100) NOT NULL,
    course_id        INT          NOT NULL,
    duration_minutes INT          NOT NULL,
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- Таблица записей на курс
CREATE TABLE enrollments (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    user_id     INT  NOT NULL,
    course_id   INT  NOT NULL,
    start_date  DATE NOT NULL,
    status      ENUM('active', 'inactive') DEFAULT 'active',
    FOREIGN KEY (user_id)   REFERENCES users(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- Таблица отзывов
CREATE TABLE reviews (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    user_id   INT     NOT NULL,
    course_id INT     NOT NULL,
    rating    TINYINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment   TEXT,
    FOREIGN KEY (user_id)   REFERENCES users(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);


-- Пользователи (преподаватели и студенты)
INSERT INTO users (first_name, last_name, email, role) VALUES
('Сергей',    'Иванов',   'ivanov@mail.ru',    'teacher'),
('Анна',      'Смирнова', 'smirnova@mail.ru',  'teacher'),
('Алексей',   'Петров',   'petrov@mail.ru',    'student'),
('Мария',     'Кузнецова','kuznecova@mail.ru', 'student'),
('Дмитрий',   'Новиков',  'novikov@mail.ru',   'student'),
('Ольга',     'Морозова', 'morozova@mail.ru',  'student'),
('Иван',      'Козлов',   'kozlov@mail.ru',    'student'),
('Светлана',  'Лебедева', 'lebedeva@mail.ru',  'student'),
('Андрей',    'Попов',    'popov@mail.ru',     'student'),
('Екатерина', 'Васильева','vasileva@mail.ru',  'student');

-- Курсы (teacher_id=1 — Иванов Сергей, teacher_id=2 — Смирнова Анна)
INSERT INTO courses (title, description, duration, category, teacher_id) VALUES
('Программирование на Python', 'Основы Python',          40, 'информатика', 1),
('Базы данных',                'SQL и проектирование БД', 30, 'информатика', 1),
('Математика для начинающих',  'Базовая математика',      20, 'математика',  2),
('Алгебра и анализ',           'Высшая математика',       35, 'математика',  2),
('Веб-разработка',             'HTML, CSS, JavaScript',  50, 'информатика', 1),
('Статистика',                 'Основы статистики',       25, 'математика',  2);

-- Записи на курсы (студенты id: 3–10)
INSERT INTO enrollments (user_id, course_id, start_date, status) VALUES
(3,  1, '2024-09-01', 'active'),
(4,  1, '2024-09-01', 'active'),
(5,  1, '2024-09-01', 'inactive'),
(6,  1, '2024-09-01', 'active'),
(7,  2, '2024-09-05', 'active'),
(8,  2, '2024-09-05', 'active'),
(9,  3, '2024-09-10', 'active'),
(10, 3, '2024-09-10', 'active'),
(3,  3, '2024-09-10', 'inactive'),
(4,  5, '2024-09-15', 'active'),
(5,  5, '2024-09-15', 'active'),
(6,  4, '2024-09-20', 'active');

-- Отзывы
INSERT INTO reviews (user_id, course_id, rating, comment) VALUES
(3,  1, 5, 'Отличный курс!'),
(4,  1, 4, 'Очень полезно'),
(9,  3, 5, 'Всё понятно объясняется'),
(10, 3, 3, 'Неплохо, но хотелось бы больше практики'),
(3,  3, 4, 'Хороший курс');

SELECT first_name, last_name, email
FROM users
WHERE role = 'teacher'
ORDER BY first_name ASC, last_name ASC;

SELECT title, description, duration
FROM courses
WHERE category = 'информатика'
ORDER BY title ASC;

SELECT u.first_name, u.last_name, u.email, e.start_date, e.status
FROM users u
JOIN enrollments e ON u.id = e.user_id
JOIN courses c     ON e.course_id = c.id
WHERE c.title = 'Программирование на Python'
  AND u.role  = 'student';

SELECT c.title,
       COUNT(e.user_id) AS active_students
FROM courses c
JOIN enrollments e ON c.id = e.course_id
WHERE e.status = 'active'
GROUP BY c.id, c.title
ORDER BY active_students DESC;


SELECT c.title, c.category, c.duration
FROM courses c
JOIN users u ON c.teacher_id = u.id
WHERE u.last_name  = 'Иванов'
  AND u.first_name = 'Сергей'
  AND u.role       = 'teacher';


SELECT u.first_name, u.last_name, r.rating, r.comment
FROM users u
JOIN reviews r ON u.id = r.user_id
JOIN courses c ON r.course_id = c.id
WHERE c.title = 'Математика для начинающих';