---

-- =====================================================
--   ИНФОРМАЦИОННАЯ СИСТЕМА
--   "УПРАВЛЕНИЕ БИБЛИОТЕЧНЫМ ФОНДОМ"
-- =====================================================

CREATE DATABASE LibraryManagement;
USE LibraryManagement;

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

CREATE TABLE Publisher (
    publisher_id INT PRIMARY KEY AUTO_INCREMENT,
    publisher_name VARCHAR(150) NOT NULL,
    director_name VARCHAR(100),
    address VARCHAR(200),
    city VARCHAR(100),
    country VARCHAR(100),
    phone VARCHAR(20),
    email VARCHAR(100),
    website VARCHAR(150),
    foundation_year INT
);

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

CREATE TABLE Shelf (
    shelf_id INT PRIMARY KEY AUTO_INCREMENT,
    shelf_number VARCHAR(20) NOT NULL,
    hall_name VARCHAR(100),
    section_name VARCHAR(100),
    floor_number INT,
    max_capacity INT,
    current_books_count INT DEFAULT 0
);

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

CREATE TABLE Reader (
    reader_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(150) NOT NULL,
    birth_date DATE,
    gender VARCHAR(10),
    address VARCHAR(200),
    city VARCHAR(100),
    phone VARCHAR(20),
    email VARCHAR(100),
    passport_number VARCHAR(30),
    ticket_number VARCHAR(30) UNIQUE,
    registration_date DATE,
    status VARCHAR(30)
);

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

CREATE TABLE Librarian (
    librarian_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(150) NOT NULL,
    birth_date DATE,
    position_name VARCHAR(100),
    education VARCHAR(100),
    work_experience INT,
    phone VARCHAR(20),
    email VARCHAR(100),
    salary DECIMAL(10,2),
    hire_date DATE
);

-- =====================================================
-- ТАБЛИЦА: АВТОР
-- =====================================================

CREATE TABLE Author (
    author_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(150) NOT NULL,
    birth_year INT,
    death_year INT,
    country VARCHAR(100),
    biography TEXT
);

-- =====================================================
-- ТАБЛИЦА: ЖАНР
-- =====================================================

CREATE TABLE Genre (
    genre_id INT PRIMARY KEY AUTO_INCREMENT,
    genre_name VARCHAR(100) NOT NULL,
    description TEXT
);

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

CREATE TABLE Book (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    subtitle VARCHAR(200),
    isbn VARCHAR(30) UNIQUE,
    publish_year INT,
    page_count INT,
    language_name VARCHAR(50),
    book_description TEXT,
    edition_number INT,
    price DECIMAL(10,2),
    quantity_total INT,
    quantity_available INT,

    publisher_id INT,
    shelf_id INT,
    genre_id INT,

    FOREIGN KEY (publisher_id)
        REFERENCES Publisher(publisher_id),

    FOREIGN KEY (shelf_id)
        REFERENCES Shelf(shelf_id),

    FOREIGN KEY (genre_id)
        REFERENCES Genre(genre_id)
);

-- =====================================================
-- СВЯЗЬ КНИГА-АВТОР
-- (многие ко многим)
-- =====================================================

CREATE TABLE BookAuthor (
    book_author_id INT PRIMARY KEY AUTO_INCREMENT,

    book_id INT,
    author_id INT,

    FOREIGN KEY (book_id)
        REFERENCES Book(book_id),

    FOREIGN KEY (author_id)
        REFERENCES Author(author_id)
);

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

CREATE TABLE Issue (
    issue_id INT PRIMARY KEY AUTO_INCREMENT,

    issue_date DATE NOT NULL,
    expected_return_date DATE,
    actual_return_date DATE,

    issue_status VARCHAR(50),
    fine_amount DECIMAL(10,2) DEFAULT 0,

    notes TEXT,

    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 TABLE Reservation (
    reservation_id INT PRIMARY KEY AUTO_INCREMENT,

    reservation_date DATE,
    expiration_date DATE,
    reservation_status VARCHAR(50),

    reader_id INT,
    book_id INT,

    FOREIGN KEY (reader_id)
        REFERENCES Reader(reader_id),

    FOREIGN KEY (book_id)
        REFERENCES Book(book_id)
);

-- =====================================================
-- ТАБЛИЦА: ШТРАФЫ
-- =====================================================

CREATE TABLE Fine (
    fine_id INT PRIMARY KEY AUTO_INCREMENT,

    fine_date DATE,
    amount DECIMAL(10,2),
    payment_status VARCHAR(50),
    reason TEXT,

    reader_id INT,
    issue_id INT,

    FOREIGN KEY (reader_id)
        REFERENCES Reader(reader_id),

    FOREIGN KEY (issue_id)
        REFERENCES Issue(issue_id)
);

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

INSERT INTO Genre (genre_name, description)
VALUES
('Программирование', 'Книги по IT и программированию'),
('Роман', 'Художественная литература'),
('Наука', 'Научная литература');

INSERT INTO Publisher (
    publisher_name,
    director_name,
    address,
    city,
    country,
    phone,
    email,
    website,
    foundation_year
)
VALUES
(
    'Эксмо',
    'Иванов А.А.',
    'ул. Ленина 10',
    'Москва',
    'Россия',
    '+7-999-111-22-33',
    'info@eksmo.ru',
    'www.eksmo.ru',
    1991
),
(
    'АСТ',
    'Петров В.В.',
    'пр. Мира 25',
    'Санкт-Петербург',
    'Россия',
    '+7-999-555-66-77',
    'contact@ast.ru',
    'www.ast.ru',
    1989
);

INSERT INTO Shelf (
    shelf_number,
    hall_name,
    section_name,
    floor_number,
    max_capacity
)
VALUES
('A-12', 'Главный зал', 'Программирование', 1, 100),
('B-07', 'Художественный зал', 'Романы', 2, 150);

INSERT INTO Author (
    full_name,
    birth_year,
    country
)
VALUES
('Лев Толстой', 1828, 'Россия'),
('Герберт Шилдт', 1951, 'США');

INSERT INTO Reader (
    full_name,
    birth_date,
    gender,
    address,
    city,
    phone,
    email,
    passport_number,
    ticket_number,
    registration_date,
    status
)
VALUES
(
    'Иванов Иван Иванович',
    '2000-04-12',
    'Мужской',
    'ул. Гагарина 15',
    'Москва',
    '+7-999-123-45-67',
    'ivanov@mail.ru',
    '4510 123456',
    'RB-1001',
    CURDATE(),
    'Активен'
);

INSERT INTO Librarian (
    full_name,
    birth_date,
    position_name,
    education,
    work_experience,
    phone,
    email,
    salary,
    hire_date
)
VALUES
(
    'Смирнова Анна Сергеевна',
    '1985-06-11',
    'Главный библиотекарь',
    'Высшее',
    12,
    '+7-999-888-77-66',
    'smirnova@library.ru',
    65000,
    '2015-03-01'
);

INSERT INTO Book (
    title,
    subtitle,
    isbn,
    publish_year,
    page_count,
    language_name,
    book_description,
    edition_number,
    price,
    quantity_total,
    quantity_available,
    publisher_id,
    shelf_id,
    genre_id
)
VALUES
(
    'Java: Полное руководство',
    'Подробный справочник',
    '978-5-17-123456-7',
    2021,
    1200,
    'Русский',
    'Книга по языку Java',
    12,
    2500,
    10,
    7,
    1,
    1,
    1
),
(
    'Война и мир',
    'Роман-эпопея',
    '978-5-17-765432-1',
    2018,
    1300,
    'Русский',
    'Классический роман',
    3,
    1800,
    5,
    4,
    2,
    2,
    2
);

INSERT INTO BookAuthor (book_id, author_id)
VALUES
(1, 2),
(2, 1);

INSERT INTO Issue (
    issue_date,
    expected_return_date,
    actual_return_date,
    issue_status,
    fine_amount,
    notes,
    reader_id,
    book_id,
    librarian_id
)
VALUES
(
    '2025-05-01',
    '2025-05-15',
    NULL,
    'Выдана',
    0,
    'Книга выдана в хорошем состоянии',
    1,
    1,
    1
);

INSERT INTO Reservation (
    reservation_date,
    expiration_date,
    reservation_status,
    reader_id,
    book_id
)
VALUES
(
    CURDATE(),
    DATE_ADD(CURDATE(), INTERVAL 7 DAY),
    'Активно',
    1,
    2
);

-- =====================================================
-- ПРИМЕРЫ SQL-ЗАПРОСОВ
-- =====================================================

-- Все книги библиотеки

SELECT * FROM Book;

-- Книги и их авторы

SELECT
    Book.title,
    Author.full_name AS author
FROM Book
JOIN BookAuthor
ON Book.book_id = BookAuthor.book_id
JOIN Author
ON BookAuthor.author_id = Author.author_id;

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

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

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

SELECT
    Reader.full_name,
    Book.title,
    Issue.expected_return_date
FROM Issue
JOIN Reader
ON Issue.reader_id = Reader.reader_id
JOIN Book
ON Issue.book_id = Book.book_id
WHERE Issue.expected_return_date < CURDATE()
AND Issue.actual_return_date IS NULL;

-- Количество книг по жанрам

SELECT
    Genre.genre_name,
    COUNT(Book.book_id) AS total_books
FROM Genre
LEFT JOIN Book
ON Genre.genre_id = Book.genre_id
GROUP BY Genre.genre_name;

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

SELECT
    Book.title,
    Publisher.publisher_name
FROM Book
JOIN Publisher
ON Book.publisher_id = Publisher.publisher_id
WHERE Publisher.publisher_name = 'Эксмо';

-- Читатели со штрафами

SELECT
    Reader.full_name,
    Fine.amount,
    Fine.payment_status
FROM Fine
JOIN Reader
ON Fine.reader_id = Reader.reader_id;