---

PRAGMA foreign_keys = ON;

BEGIN;

CREATE TABLE readers (
    reader_id INTEGER PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    phone VARCHAR(30),
    email VARCHAR(150) UNIQUE
);

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL
);

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    publication_year INTEGER
        CHECK (publication_year BETWEEN 1 AND 2100),
    genre VARCHAR(80) NOT NULL
);

CREATE TABLE book_authors (
    book_id INTEGER NOT NULL REFERENCES books(book_id),
    author_id INTEGER NOT NULL REFERENCES authors(author_id),
    PRIMARY KEY (book_id, author_id)
);

CREATE TABLE copies (
    copy_id INTEGER PRIMARY KEY,
    book_id INTEGER NOT NULL REFERENCES books(book_id),
    status VARCHAR(20) NOT NULL DEFAULT 'available',
    CHECK (status IN ('available', 'on_loan', 'repair', 'lost'))
);

CREATE TABLE loans (
    loan_id INTEGER PRIMARY KEY,
    reader_id INTEGER NOT NULL REFERENCES readers(reader_id),
    copy_id INTEGER NOT NULL REFERENCES copies(copy_id),
    loan_date DATE NOT NULL,
    return_date DATE,
    CHECK (return_date IS NULL OR return_date >= loan_date)
);

CREATE UNIQUE INDEX one_active_loan_per_copy
    ON loans(copy_id)
    WHERE return_date IS NULL;

CREATE INDEX idx_copies_book
    ON copies(book_id);

CREATE INDEX idx_loans_reader
    ON loans(reader_id);

CREATE INDEX idx_book_authors_author
    ON book_authors(author_id);

INSERT INTO readers (reader_id, full_name, phone, email) VALUES
(1, 'Иван Петров', '+7-000-000-00-01', 'ivan@example.com'),
(2, 'Анна Смирнова', '+7-000-000-00-02', 'anna@example.com'),
(3, 'Мария Иванова', '+7-000-000-00-03', 'maria@example.com'),
(4, 'Алексей Соколов', '+7-000-000-00-04', 'alex@example.com'),
(5, 'Елена Кузнецова', '+7-000-000-00-05', 'elena@example.com'),
(6, 'Дмитрий Волков', '+7-000-000-00-06', 'dmitry@example.com'),
(7, 'Ольга Морозова', '+7-000-000-00-07', 'olga@example.com'),
(8, 'Сергей Орлов', '+7-000-000-00-08', 'sergey@example.com');

INSERT INTO authors (author_id, full_name) VALUES
(1, 'Александр Пушкин'),
(2, 'Лев Толстой'),
(3, 'Фёдор Достоевский'),
(4, 'Антон Чехов'),
(5, 'Михаил Булгаков'),
(6, 'Илья Ильф'),
(7, 'Евгений Петров'),
(8, 'Иван Тургенев');

INSERT INTO books (book_id, title, publication_year, genre) VALUES
(1, 'Евгений Онегин', 1833, 'Роман в стихах'),
(2, 'Капитанская дочка', 1836, 'Исторический роман'),
(3, 'Война и мир', 1869, 'Роман'),
(4, 'Анна Каренина', 1878, 'Роман'),
(5, 'Преступление и наказание', 1866, 'Роман'),
(6, 'Идиот', 1868, 'Роман'),
(7, 'Вишнёвый сад', 1904, 'Пьеса'),
(8, 'Мастер и Маргарита', 1967, 'Роман'),
(9, 'Двенадцать стульев', 1928, 'Сатирический роман'),
(10, 'Отцы и дети', 1862, 'Роман');

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

INSERT INTO copies (copy_id, book_id, status) VALUES
(1, 1, 'on_loan'),
(2, 1, 'available'),
(3, 2, 'available'),
(4, 3, 'on_loan'),
(5, 3, 'available'),
(6, 4, 'available'),
(7, 5, 'on_loan'),
(8, 5, 'available'),
(9, 6, 'available'),
(10, 7, 'repair'),
(11, 8, 'on_loan'),
(12, 8, 'available'),
(13, 9, 'on_loan'),
(14, 9, 'available'),
(15, 10, 'available');

INSERT INTO loans (
    loan_id, reader_id, copy_id, loan_date, return_date
) VALUES
(1, 1, 2, '2026-09-01', '2026-09-12'),
(2, 2, 3, '2026-09-02', '2026-09-15'),
(3, 3, 5, '2026-09-03', '2026-09-20'),
(4, 4, 6, '2026-09-05', '2026-09-18'),
(5, 5, 8, '2026-09-07', '2026-09-25'),
(6, 6, 12, '2026-09-10', '2026-09-28'),
(7, 7, 14, '2026-09-12', '2026-09-29'),
(8, 1, 1, '2026-10-01', NULL),
(9, 2, 4, '2026-10-02', NULL),
(10, 3, 7, '2026-10-03', NULL),
(11, 4, 11, '2026-10-04', NULL),
(12, 5, 13, '2026-10-05', NULL);

COMMIT;