---

-- =====================================================================
-- ЗАДАНИЕ 2. Реализация БД в СУБД MySQL (с учетом 3 НФ)
-- =====================================================================

-- Создание базы данных
CREATE DATABASE IF NOT EXISTS library_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE library_db;

-- Удаление таблиц, если они уже существовали (для чистого перезапуска)
DROP TABLE IF EXISTS book_loans;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS readers;
DROP TABLE IF EXISTS books;

-- 1. Таблица "Книги" (Данные о книгах без дублирования экземпляров)
CREATE TABLE books (
    id_book INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    publisher VARCHAR(150) NOT NULL,
    publish_year INT NOT NULL,
    pages_count INT NOT NULL,
    total_copies INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

-- 2. Таблица "Читатели" (Картотека читателей)
CREATE TABLE readers (
    ticket_number INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL
);

-- 3. Таблица "Сотрудники" (Данные о работниках библиотеки)
CREATE TABLE employees (
    id_employee INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    position VARCHAR(100) NOT NULL
);

-- 4. Таблица "Выдача книг" (Специальный вкладыш / Связующая таблица)
CREATE TABLE book_loans (
    id_loan INT AUTO_INCREMENT PRIMARY KEY,
    ticket_number INT NOT NULL,
    id_book INT NOT NULL,
    id_employee INT NOT NULL,
    loan_date DATE NOT NULL,
    return_date DATE NULL,
    is_returned BOOLEAN NOT NULL DEFAULT FALSE,
    FOREIGN KEY (ticket_number) REFERENCES readers(ticket_number) ON DELETE CASCADE,
    FOREIGN KEY (id_book) REFERENCES books(id_book) ON DELETE CASCADE,
    FOREIGN KEY (id_employee) REFERENCES employees(id_employee) ON DELETE CASCADE
);


-- =====================================================================
-- ЗАДАНИЕ 3. Заполнение таблиц данными (Оператор INSERT)
-- =====================================================================

-- Заполнение таблицы "Книги"
INSERT INTO books (title, author, publisher, publish_year, pages_count, total_copies, price) VALUES
('Чистый код', 'Роберт Мартин', 'Питер', 2019, 464, 5, 1200.00),
('Изучаем SQL', 'Алан Бьюли', 'O`Reilly', 2021, 384, 3, 950.50),
('Совершенный код', 'Стив Макконнелл', 'БХВ', 2022, 896, 2, 2500.00),
('Грокаем алгоритмы', 'Адитья Бхаргава', 'Питер', 2020, 288, 7, 850.00);

-- Заполнение таблицы "Читатели"
INSERT INTO readers (full_name, address, phone) VALUES
('Иванов Иван Иванович', 'г. Ростов-на-Дону, ул. Большая Садовая, 10', '+7-900-123-45-67'),
('Петров Петр Петрович', 'г. Ростов-на-Дону, пр. Буденновский, 45', '+7-918-765-43-21'),
('Сидорова Анна Сергеевна', 'г. Батайск, ул. Ленина, 12', '+7-905-999-88-77');

-- Заполнение таблицы "Сотрудники"
INSERT INTO employees (full_name, position) VALUES
('Смирнова Елена Викторовна', 'Старший библиотекарь'),
('Кузнецов Олег Николаевич', 'Библиотекарь');

-- Заполнение таблицы "Выдача книг" (Вкладыши)
INSERT INTO book_loans (ticket_number, id_book, id_employee, loan_date, return_date, is_returned) VALUES
(1, 1, 1, '2026-05-10', '2026-05-24', TRUE),  -- Книга возвращена
(1, 2, 1, '2026-06-01', NULL, FALSE),          -- Книга на руках у Иванова
(2, 2, 2, '2026-05-15', '2026-05-29', TRUE),  -- Книга возвращена
(3, 3, 2, '2026-06-10', NULL, FALSE);          -- Книга на руках у Сидоровой