---

-- =========================================================
-- БАЗА ДАННЫХ:
-- УПРАВЛЕНИЕ БИБЛИОТЕЧНЫМ ФОНДОМ
-- (SQL-код по вашей ER/UML схеме)
-- =========================================================

CREATE DATABASE LibraryDB;
USE LibraryDB;

-- =========================================================
-- ТАБЛИЦА: ИЗДАТЕЛЬСТВО
-- =========================================================

CREATE TABLE Publisher (
    publisher_id INT PRIMARY KEY AUTO_INCREMENT,
    publisher_name VARCHAR(100) NOT NULL,
    address VARCHAR(150),
    phone VARCHAR(20)
);

-- =========================================================
-- ТАБЛИЦА: ПОЛКА
-- =========================================================

CREATE TABLE Shelf (
    shelf_id INT PRIMARY KEY AUTO_INCREMENT,
    shelf_number VARCHAR(20) NOT NULL,
    hall_name VARCHAR(50),
    section_name VARCHAR(50)
);

-- =========================================================
-- ТАБЛИЦА: КНИГА
-- =========================================================

CREATE TABLE Book (
    book_id INT PRIMARY KEY AUTO_INCREMENT,

    title VARCHAR(200) NOT NULL,
    author VARCHAR(150) NOT NULL,
    genre VARCHAR(100),
    publish_year INT,
    isbn VARCHAR(30) UNIQUE,

    publisher_id INT,
    shelf_id INT,

    FOREIGN KEY (publisher_id)
        REFERENCES Publisher(publisher_id),

    FOREIGN KEY (shelf_id)
        REFERENCES Shelf(shelf_id)
);

-- =========================================================
-- ТАБЛИЦА: ЧИТАТЕЛЬ
-- =========================================================

CREATE TABLE Reader (
    reader_id INT PRIMARY KEY AUTO_INCREMENT,

    full_name VARCHAR(150) NOT NULL,
    address VARCHAR(200),
    phone VARCHAR(20),
    reader_ticket_number VARCHAR(30) UNIQUE
);

-- =========================================================
-- ТАБЛИЦА: БИБЛИОТЕКАРЬ
-- =========================================================

CREATE TABLE Librarian (
    librarian_id INT PRIMARY KEY AUTO_INCREMENT,

    full_name VARCHAR(150) NOT NULL,
    position_name VARCHAR(100),
    phone VARCHAR(20)
);

-- =========================================================
-- ТАБЛИЦА: ВЫДАЧА
-- =========================================================

CREATE TABLE Issue (
    issue_id INT PRIMARY KEY AUTO_INCREMENT,

    issue_date DATE NOT NULL,
    return_date DATE,

    status VARCHAR(50),

    reader_id INT,
    book_id INT,
    librarian_id INT,

    FOREIGN KEY (reader_id)
        REFERENCES Reader(reader_id),

    FOREIGN KEY (book_id)
        REFERENCES Book(book_id),

    FOREIGN KEY (librarian_id)
        REFERENCES Librarian(librarian_id)
);

-- =========================================================
-- ДОПОЛНИТЕЛЬНЫЕ ИНДЕКСЫ
-- =========================================================

CREATE INDEX idx_book_title
ON Book(title);

CREATE INDEX idx_book_author
ON Book(author);

CREATE INDEX idx_reader_name
ON Reader(full_name);

-- =========================================================
-- ТЕСТОВЫЕ ДАННЫЕ
-- =========================================================

-- Издательства

INSERT INTO Publisher (
    publisher_name,
    address,
    phone
)
VALUES
(
    'Эксмо',
    'Москва, ул. Ленина 10',
    '+7-900-111-22-33'
),
(
    'АСТ',
    'Санкт-Петербург, Невский проспект 5',
    '+7-900-555-66-77'
);

-- Полки

INSERT INTO Shelf (
    shelf_number,
    hall_name,
    section_name
)
VALUES
(
    'A-01',
    'Главный зал',
    'Программирование'
),
(
    'B-15',
    'Художественный зал',
    'Романы'
);

-- Книги

INSERT INTO Book (
    title,
    author,
    genre,
    publish_year,
    isbn,
    publisher_id,
    shelf_id
)
VALUES
(
    'Java. Полное руководство',
    'Герберт Шилдт',
    'Программирование',
    2021,
    '978-5-17-123456-7',
    1,
    1
),
(
    'Война и мир',
    'Лев Толстой',
    'Роман',
    2018,
    '978-5-17-765432-1',
    2,
    2
),
(
    'Преступление и наказание',
    'Фёдор Достоевский',
    'Классика',
    2019,
    '978-5-17-888888-8',
    2,
    2
);

-- Читатели

INSERT INTO Reader (
    full_name,
    address,
    phone,
    reader_ticket_number
)
VALUES
(
    'Иванов Иван Иванович',
    'Москва, ул. Гагарина 15',
    '+7-999-123-45-67',
    'RB-1001'
),
(
    'Петров Петр Петрович',
    'Казань, ул. Баумана 8',
    '+7-999-777-88-99',
    'RB-1002'
);

-- Библиотекари

INSERT INTO Librarian (
    full_name,
    position_name,
    phone
)
VALUES
(
    'Смирнова Анна Сергеевна',
    'Главный библиотекарь',
    '+7-999-444-55-66'
),
(
    'Кузнецов Сергей Иванович',
    'Библиотекарь',
    '+7-999-111-33-22'
);

-- Выдача книг

INSERT INTO Issue (
    issue_date,
    return_date,
    status,
    reader_id,
    book_id,
    librarian_id
)
VALUES
(
    '2025-05-01',
    '2025-05-15',
    'Возвращена',
    1,
    1,
    1
),
(
    '2025-05-10',
    NULL,
    'Выдана',
    2,
    2,
    2
);

-- =========================================================
-- SQL-ЗАПРОСЫ
-- =========================================================

-- Все книги

SELECT * FROM Book;

-- Все читатели

SELECT * FROM Reader;

-- Все выданные книги

SELECT
    Reader.full_name AS Читатель,
    Book.title AS Книга,
    Issue.issue_date AS Дата_выдачи,
    Issue.return_date AS Дата_возврата,
    Issue.status AS Статус
FROM Issue
JOIN Reader
ON Issue.reader_id = Reader.reader_id
JOIN Book
ON Issue.book_id = Book.book_id;

-- Книги и издательства

SELECT
    Book.title,
    Publisher.publisher_name
FROM Book
JOIN Publisher
ON Book.publisher_id = Publisher.publisher_id;

-- Книги на полках

SELECT
    Book.title,
    Shelf.shelf_number,
    Shelf.hall_name
FROM Book
JOIN Shelf
ON Book.shelf_id = Shelf.shelf_id;

-- Поиск книги по автору

SELECT *
FROM Book
WHERE author = 'Лев Толстой';

-- Просроченные книги

SELECT
    Reader.full_name,
    Book.title,
    Issue.return_date
FROM Issue
JOIN Reader
ON Issue.reader_id = Reader.reader_id
JOIN Book
ON Issue.book_id = Book.book_id
WHERE Issue.return_date < CURDATE()
AND Issue.status <> 'Возвращена';

-- Количество книг в библиотеке

SELECT COUNT(*) AS total_books
FROM Book;

-- Количество читателей

SELECT COUNT(*) AS total_readers
FROM Reader;