---

-- =========================================
-- БАЗА ДАННЫХ: Библиотечный фонд
-- =========================================

CREATE DATABASE LibraryDB;
USE LibraryDB;

-- =========================================
-- Таблица: Издательство
-- =========================================

CREATE TABLE Publisher (
    publisher_id INT PRIMARY KEY AUTO_INCREMENT,
    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 VARCHAR(50),
    section_name VARCHAR(50)
);

-- =========================================
-- Таблица: Читатель
-- =========================================

CREATE TABLE Reader (
    reader_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    address VARCHAR(150),
    phone VARCHAR(20),
    ticket_number VARCHAR(20) UNIQUE
);

-- =========================================
-- Таблица: Библиотекарь
-- =========================================

CREATE TABLE Librarian (
    librarian_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    position_name VARCHAR(50),
    phone VARCHAR(20)
);

-- =========================================
-- Таблица: Книга
-- =========================================

CREATE TABLE Book (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(150) NOT NULL,
    author VARCHAR(100) NOT NULL,
    genre VARCHAR(50),
    publish_year INT,
    isbn VARCHAR(20) 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 Issue (
    issue_id INT PRIMARY KEY AUTO_INCREMENT,

    issue_date DATE NOT NULL,
    return_date DATE,

    status VARCHAR(30),

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

-- =========================================
-- Тестовые данные
-- =========================================

INSERT INTO Publisher (name, address, phone)
VALUES
('Эксмо', 'Москва', '+7-900-111-22-33'),
('АСТ', 'Санкт-Петербург', '+7-900-444-55-66');

INSERT INTO Shelf (shelf_number, hall, section_name)
VALUES
('A-01', 'Зал 1', 'Программирование'),
('B-15', 'Зал 2', 'Художественная литература');

INSERT INTO Reader (full_name, address, phone, ticket_number)
VALUES
('Иванов Иван Иванович', 'Москва', '+7-999-123-45-67', 'RB1001'),
('Петров Петр Петрович', 'Казань', '+7-999-222-33-44', 'RB1002');

INSERT INTO Librarian (full_name, position_name, phone)
VALUES
('Смирнова Анна', 'Главный библиотекарь', '+7-999-555-66-77');

INSERT INTO Book (
    title,
    author,
    genre,
    publish_year,
    isbn,
    publisher_id,
    shelf_id
)
VALUES
(
    'Изучаем SQL',
    'Джон Смит',
    'Образование',
    2020,
    '978-5-17-123456-7',
    1,
    1
),
(
    'Война и мир',
    'Лев Толстой',
    'Роман',
    1869,
    '978-5-17-765432-1',
    2,
    2
);

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

-- =========================================
-- Примеры запросов
-- =========================================

-- Все книги

SELECT * FROM Book;

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

SELECT
    Book.title,
    Book.author,
    Publisher.name AS publisher
FROM Book
JOIN Publisher
ON Book.publisher_id = Publisher.publisher_id;

-- Какие книги взял читатель

SELECT
    Reader.full_name,
    Book.title,
    Issue.issue_date,
    Issue.return_date
FROM Issue
JOIN Reader
ON Issue.reader_id = Reader.reader_id
JOIN Book
ON Issue.book_id = Book.book_id;

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

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