---

-- ============================================
-- Создание базы данных "Библиотека"
-- ============================================

DROP DATABASE IF EXISTS Library;
CREATE DATABASE Library
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE Library;

-- ============================================
-- Таблица: authors (Авторы)
-- ============================================
CREATE TABLE authors (
    author_id   INT AUTO_INCREMENT PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL,
    birth_year  INT,
    country     VARCHAR(50)
) ENGINE=InnoDB;

-- ============================================
-- Таблица: books (Книги)
-- ============================================
CREATE TABLE books (
    book_id         INT AUTO_INCREMENT PRIMARY KEY,
    title           VARCHAR(200) NOT NULL,
    author_id       INT NOT NULL,
    genre           VARCHAR(50),
    year_published  INT,
    quantity        INT DEFAULT 1,
    CONSTRAINT fk_books_author
        FOREIGN KEY (author_id) REFERENCES authors(author_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ============================================
-- Таблица: readers (Читатели)
-- ============================================
CREATE TABLE readers (
    reader_id   INT AUTO_INCREMENT PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL,
    phone       VARCHAR(20),
    email       VARCHAR(100) UNIQUE,
    reg_date    DATE DEFAULT (CURRENT_DATE)
) ENGINE=InnoDB;

-- ============================================
-- Таблица: loans (Выдачи книг)
-- ============================================
CREATE TABLE loans (
    loan_id        INT AUTO_INCREMENT PRIMARY KEY,
    book_id        INT NOT NULL,
    reader_id      INT NOT NULL,
    loan_date      DATE NOT NULL,
    return_date    DATE NOT NULL,
    actual_return  DATE,
    CONSTRAINT fk_loans_book
        FOREIGN KEY (book_id) REFERENCES books(book_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_loans_reader
        FOREIGN KEY (reader_id) REFERENCES readers(reader_id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ============================================
-- Индексы для ускорения поиска
-- ============================================
CREATE INDEX idx_books_title     ON books(title);
CREATE INDEX idx_books_author    ON books(author_id);
CREATE INDEX idx_loans_reader    ON loans(reader_id);
CREATE INDEX idx_loans_book      ON loans(book_id);
CREATE INDEX idx_loans_return    ON loans(return_date);