---

-- ============================================
-- СОЗДАНИЕ БАЗЫ ДАННЫХ
-- ============================================

DROP DATABASE IF EXISTS library_db;
CREATE DATABASE library_db;
USE library_db;

-- ============================================
-- УДАЛЕНИЕ ТАБЛИЦ
-- ============================================

DROP TABLE IF EXISTS issues;
DROP TABLE IF EXISTS book_authors;
DROP TABLE IF EXISTS readers;
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS authors;

-- ============================================
-- СОЗДАНИЕ ТАБЛИЦ
-- ============================================

CREATE TABLE authors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    country VARCHAR(50),
    birth_year INT
);

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    pages INT,
    price DECIMAL(10,2),
    genre VARCHAR(50),
    pub_year INT
);

CREATE TABLE readers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    city VARCHAR(50),
    registration_date DATE
);

CREATE TABLE book_authors (
    book_id INT,
    author_id INT,

    PRIMARY KEY(book_id, author_id),

    FOREIGN KEY(book_id)
        REFERENCES books(id)
        ON DELETE CASCADE,

    FOREIGN KEY(author_id)
        REFERENCES authors(id)
        ON DELETE CASCADE
);

CREATE TABLE issues (
    id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT,
    reader_id INT,
    issue_date DATE,
    return_date DATE,
    rating INT,
    fine DECIMAL(10,2),

    FOREIGN KEY(book_id)
        REFERENCES books(id),

    FOREIGN KEY(reader_id)
        REFERENCES readers(id)
);

-- ============================================
-- ЗАПОЛНЕНИЕ ТАБЛИЦ
-- ============================================

-- AUTHORS

INSERT INTO authors VALUES
(1, 'Лев Толстой', 'RU', 1828),
(2, 'Фёдор Достоевский', 'RU', 1821),
(3, 'Джейн Остин', 'UK', 1775),
(4, 'Дж. Р. Р. Толкин', 'UK', 1892),
(5, 'Дж. К. Роулинг', 'UK', 1965),
(6, 'Антуан де Сент-Экзюпери', 'FR', 1900),
(7, 'Уильям Шекспир', 'UK', 1564),
(8, 'Марк Твен', 'US', 1835);

-- BOOKS

INSERT INTO books VALUES
(1, 'Война и мир', 1400, 25.50, 'Роман', 1869),
(2, 'Преступление и наказание', 670, 18.00, 'Роман', 1866),
(3, 'Гордость и предубеждение', 432, 15.99, 'Роман', 1813),
(4, 'Властелин колец', 1178, 35.00, 'Фэнтези', 1954),
(5, 'Гарри Поттер и философский камень', 223, 12.50, 'Фэнтези', 1997),
(6, 'Маленький принц', 96, 8.99, 'Сказка', 1943),
(7, 'Гамлет', 350, 10.50, 'Драма', 1603),
(8, 'Приключения Гекльберри Финна', 366, 14.00, 'Приключения', 1884);

-- READERS

INSERT INTO readers VALUES
(1, 'Иван Петров', 25, 'Москва', '2024-01-15'),
(2, 'Мария Сидорова', 32, 'СПб', '2023-11-20'),
(3, 'Алексей Иванов', 28, 'Казань', '2024-03-10'),
(4, 'Елена Козлова', 22, 'Москва', '2025-09-01'),
(5, 'Дмитрий Смирнов', 45, 'Екатеринбург', '2023-05-12');

-- BOOK_AUTHORS

INSERT INTO book_authors VALUES
(1,1),
(1,2),
(2,2),
(3,3),
(4,4),
(5,5),
(6,6),
(7,7),
(8,8);

-- ISSUES

INSERT INTO issues VALUES
(1,1,1,'2026-01-10','2026-02-05',10,0.00),
(2,2,2,'2026-02-01',NULL,NULL,5.00),
(3,3,1,'2026-03-15','2026-04-10',8,0.00),
(4,4,3,'2026-04-01',NULL,NULL,0.00),
(5,5,4,'2026-03-20','2026-04-15',9,2.50),
(6,1,5,'2026-01-20','2026-02-10',7,0.00),
(7,5,5,'2026-01-09',NULL,NULL,0.00),
(8,4,1,'2025-06-15',NULL,NULL,8.03),
(9,7,5,'2025-06-06','2026-03-25',1,1.15),
(10,8,4,'2026-01-14',NULL,NULL,0.77),
(11,2,4,'2025-12-10',NULL,NULL,0.00),
(12,6,5,'2026-04-12',NULL,NULL,1.00),
(13,2,2,'2025-10-01',NULL,NULL,0.58),
(14,4,1,'2025-10-14',NULL,NULL,0.00),
(15,4,4,'2025-06-17',NULL,NULL,0.00);

-- ============================================
-- ПРОВЕРКА ДАННЫХ
-- ============================================

SELECT * FROM authors;

SELECT * FROM books;

SELECT * FROM readers;

SELECT * FROM book_authors;

SELECT * FROM issues;