---

-- Создание базы
CREATE DATABASE RaspisanieDB;
GO

USE RaspisanieDB;
GO

-- 1. Teachers
CREATE TABLE Teachers (
    teacher_id INT IDENTITY PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL
);

-- 2. Days
CREATE TABLE Days (
    day_id INT IDENTITY PRIMARY KEY,
    day_name VARCHAR(20) NOT NULL UNIQUE
);

-- 3. Subjects
CREATE TABLE Subjects (
    subject_id INT IDENTITY PRIMARY KEY,
    subject_name VARCHAR(100) NOT NULL
);

-- 4. LessonTypes
CREATE TABLE LessonTypes (
    type_id INT IDENTITY PRIMARY KEY,
    type_name VARCHAR(50) NOT NULL
);

-- 5. Groups
CREATE TABLE GroupsTable (
    group_id INT IDENTITY PRIMARY KEY,
    group_number VARCHAR(20) NOT NULL UNIQUE
);

-- 6. Schedule
CREATE TABLE Schedule (
    schedule_id INT IDENTITY PRIMARY KEY,
    teacher_id INT NOT NULL,
    day_id INT NOT NULL,
    lesson_number INT NOT NULL,
    subject_id INT NOT NULL,
    type_id INT NOT NULL,
    group_id INT NOT NULL,

    FOREIGN KEY (teacher_id) REFERENCES Teachers(teacher_id),
    FOREIGN KEY (day_id) REFERENCES Days(day_id),
    FOREIGN KEY (subject_id) REFERENCES Subjects(subject_id),
    FOREIGN KEY (type_id) REFERENCES LessonTypes(type_id),
    FOREIGN KEY (group_id) REFERENCES GroupsTable(group_id)
);

-- Наполнение справочников

INSERT INTO Teachers (full_name) VALUES
('Petrov V.I.'),
('Kirov V.A.'),
('Serov A.A.');

INSERT INTO Days (day_name) VALUES
('Ponedelnik'),
('Vtornik'),
('Sreda'),
('Chetverg');

INSERT INTO Subjects (subject_name) VALUES
('Teoriya vychislitelnyh processov'),
('Kompyuternaya grafika'),
('Teoriya informatiki'),
('Programmirovanie na C++'),
('Zashchita informacii'),
('Bazy dannyh');

INSERT INTO LessonTypes (type_name) VALUES
('Lekciya'),
('Laboratornaya');

INSERT INTO GroupsTable (group_number) VALUES
('4906'),
('4907'),
('4944'),
('4942'),
('4922');

-- БАЗОВЫЕ ДАННЫЕ (с картинки)

INSERT INTO Schedule (teacher_id, day_id, lesson_number, subject_id, type_id, group_id) VALUES
(1,1,1,1,1,1),
(1,2,1,2,2,2),
(1,2,2,2,2,1),

(2,1,2,3,1,1),
(2,2,3,4,2,2),
(2,2,4,4,2,1),

(3,1,3,5,1,3),
(3,3,3,6,2,4),
(3,4,4,6,2,5);

-- ДОБИВАЕМ ДО 200 СТРОК

DECLARE @i INT = 1;

WHILE @i <= 191
BEGIN
    INSERT INTO Schedule (teacher_id, day_id, lesson_number, subject_id, type_id, group_id)
    SELECT TOP 1 teacher_id, day_id, lesson_number, subject_id, type_id, group_id
    FROM Schedule
    ORDER BY NEWID();

    SET @i = @i + 1;
END;