---

-- Очистка таблиц, если они были созданы частично (при необходимости)
-- DROP TABLE IF EXISTS Loans;
-- DROP TABLE IF EXISTS Readers;
-- DROP TABLE IF EXISTS Books;

-- 1. Создание таблицы книг
CREATE TABLE Books (
    book_id INT IDENTITY(1,1) PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    year_published INT CHECK (year_published > 1900),
    copies_count INT CHECK (copies_count >= 0) NOT NULL
);
GO

-- 2. Создание таблицы читателей
CREATE TABLE Readers (
    reader_id INT IDENTITY(1,1) PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    birth_date DATE NOT NULL,
    address VARCHAR(MAX) NOT NULL,
    phone VARCHAR(30) NOT NULL
);
GO

-- 3. Создание таблицы выдач
CREATE TABLE Loans (
    loan_id INT IDENTITY(1,1) PRIMARY KEY,
    book_id INT NOT NULL FOREIGN KEY REFERENCES Books(book_id),
    reader_id INT NOT NULL FOREIGN KEY REFERENCES Readers(reader_id),
    loan_date DATE NOT NULL,
    return_date DATE,
    CHECK (return_date IS NULL OR return_date >= loan_date)
);
GO

-- 4. Вставка данных в таблицу Books (12 строк)
INSERT INTO Books (title, author, year_published, copies_count) VALUES
('1984', 'Джордж Оруэлл', 1949, 4),
('Три товарища', 'Эрих Мария Ремарк', 1936, 5),
('Мастер и Маргарита', 'Михаил Булгаков', 1967, 3),
('Властелин колец', 'Джон Р. Р. Толкин', 1954, 6),
('Сто лет одиночества', 'Габриэль Гарсия Маркес', 1967, 2),
('Над пропастью во ржи', 'Джером Сэлинджер', 1951, 3),
('Великий Гэтсби', 'Фрэнсис Скотт Фицджеральд', 1925, 4),
('Цветы для Элджернона', 'Дэниел Киз', 1966, 5),
('Вино из одуванчиков', 'Рэй Брэдбери', 1957, 4),
('Алхимик', 'Пауло Коэльо', 1988, 3),
('Гарри Поттер и философский камень', 'Джоан Роулинг', 1997, 7),
('Метро 2033', 'Дмитрий Глуховский', 2005, 5);
GO

-- 5. Вставка данных в таблицу Readers (10 строк)
INSERT INTO Readers (full_name, birth_date, address, phone) VALUES
('Иванов Иван Иванович', '1995-05-12', 'г. Москва, ул. Ленина, д. 5', '+7-999-111-22-33'),
('Петрова Анна Сергеевна', '1998-09-23', 'г. Москва, ул. Мира, д. 10', '+7-999-222-33-44'),
('Смирнов Алексей Петрович', '1985-02-14', 'г. Санкт-Петербург, Невский пр., д. 20', '+7-911-333-44-55'),
('Кузнецова Елена Дмитриевна', '2000-11-05', 'г. Казань, ул. Баумана, д. 15', '+7-987-444-55-66'),
('Васильев Дмитрий Олегович', '1992-07-30', 'г. Екатеринбург, ул. Вайнера, д. 8', '+7-912-555-66-77'),
('Морозова Ольга Николаевна', '1988-12-19', 'г. Новосибирск, Красный пр., д. 25', '+7-913-666-77-88'),
('Соколова Екатерина Павловна', '1999-04-03', 'г. Нижний Новгород, ул. Большая Покровская, д. 12', '+7-910-777-88-99'),
('Попов Сергей Александрович', '1991-08-16', 'г. Самара, ул. Ленинградская, д. 18', '+7-927-888-99-00'),
('Лебедева Мария Андреевна', '2001-01-27', 'г. Казань, ул. Чистопольская, д. 30', '+7-987-999-00-11'),
('Козлов Артем Витальевич', '1987-06-22', 'г. Москва, ул. Тверская, д. 4', '+7-999-000-11-22');
GO

-- 6. Вставка данных в таблицу Loans (12 строк, включая активные выдачи с return_date IS NULL)
INSERT INTO Loans (book_id, reader_id, loan_date, return_date) VALUES
(1, 1, '2026-10-01', '2026-10-15'),
(2, 2, '2026-10-05', NULL), 
(3, 3, '2026-10-10', '2026-10-20'),
(4, 4, '2026-10-12', NULL), 
(1, 5, '2026-10-15', NULL), 
(5, 6, '2026-09-01', '2026-09-15'),
(6, 7, '2026-10-18', NULL), 
(2, 8, '2026-09-20', '2026-10-02'),
(7, 9, '2026-10-22', NULL), 
(8, 10, '2026-09-10', '2026-09-25'),
(4, 1, '2026-10-23', NULL), 
(9, 3, '2026-10-01', '2026-10-10');
GO

-- 7. Необходимые SELECT-запросы по заданию:

-- Список книг с количеством экземпляров
SELECT title, author, copies_count FROM Books;

-- Активные выдачи (где дата возврата NULL)
SELECT 
    l.loan_id, 
    b.title AS book_title, 
    r.full_name AS reader_name, 
    l.loan_date 
FROM Loans l
JOIN Books b ON l.book_id = b.book_id
JOIN Readers r ON l.reader_id = r.reader_id
WHERE l.return_date IS NULL;

-- Топ-5 самых популярных авторов по количеству выданных книг
SELECT TOP 5
    b.author, 
    COUNT(l.loan_id) AS total_loans
FROM Loans l
JOIN Books b ON l.book_id = b.book_id
GROUP BY b.author
ORDER BY total_loans DESC;