---

use Библиотека; -- Убедитесь, что используете вашу базу
go

-- ==========================================
-- 1. УДАЛЕНИЕ СТАРЫХ ОБЪЕКТОВ (если были)
-- ==========================================
drop trigger if exists trg_проверка_дат;
drop proc if exists sp_выдать_книгу;
drop table if exists выдачи;
drop table if exists книги;
drop table if exists читатели;
drop table if exists авторы;
go

-- ==========================================
-- 2. СОЗДАНИЕ ТАБЛИЦ В 3НФ
-- ==========================================

-- Справочник авторов
create table авторы (
    код_автора int primary key,
    фио_автора varchar(255) not null
);

-- Таблица книг (ссылается на авторов)
create table книги (
    код_книги int primary key,
    название varchar(255) not null,
    код_автора int not null references авторы(код_автора),
    год_издания int check (год_издания > 1900),
    количество_экземпляров int check (количество_экземпляров >= 0)
);

-- Таблица читателей
create table читатели (
    код_читателя int primary key,
    фио varchar(255) not null,
    дата_рождения date,
    адрес varchar(500),
    телефон varchar(20)
);

-- Таблица выдач (связующая)
create table выдачи (
    код_выдачи int primary key,
    код_книги int not null references книги(код_книги),
    код_читателя int not null references читатели(код_читателя),
    дата_выдачи date not null default getdate(),
    дата_возврата date
);
go

-- ==========================================
-- 3. ЗАПОЛНЕНИЕ ДАННЫМИ (10-15 СТРОК)
-- ==========================================

-- Заполняем авторов (12 строк)
insert into авторы (код_автора, фио_автора) values
(1, 'Лев Толстой'), (2, 'Федор Достоевский'), (3, 'Михаил Булгаков'),
(4, 'Джордж Оруэлл'), (5, 'Дж. К. Роулинг'), (6, 'Александр Пушкин'),
(7, 'Михаил Лермонтов'), (8, 'Николай Гоголь'), (9, 'Иван Тургенев'),
(10, 'Антон Чехов'), (11, 'Рэй Брэдбери'), (12, 'Айзек Азимов');

-- Заполняем книги (15 строк)
insert into книги (код_книги, название, код_автора, год_издания, количество_экземпляров) values
(1, 'Война и мир', 1, 1869, 5),
(2, 'Анна Каренина', 1, 1877, 3),
(3, 'Преступление и наказание', 2, 1866, 4),
(4, 'Идиот', 2, 1869, 2),
(5, 'Мастер и Маргарита', 3, 1967, 6),
(6, 'Собачье сердце', 3, 1925, 3),
(7, '1984', 4, 1949, 8),
(8, 'Скотный двор', 4, 1945, 4),
(9, 'Гарри Поттер и философский камень', 5, 1997, 10),
(10, 'Евгений Онегин', 6, 1833, 2),
(11, 'Герой нашего времени', 7, 1840, 3),
(12, 'Мертвые души', 8, 1842, 1),
(13, 'Отцы и дети', 9, 1862, 2),
(14, 'Вишневый сад', 10, 1904, 3),
(15, '451 градус по Фаренгейту', 11, 1953, 0); -- 0 экземпляров для теста ошибки

-- Заполняем читателей (12 строк)
insert into читатели (код_читателя, фио, дата_рождения, адрес, телефон) values
(1, 'Иванов Иван Иванович', '1990-05-12', 'ул. Ленина, 10', '+79001112233'),
(2, 'Петрова Анна Сергеевна', '1985-08-23', 'пр. Мира, 45', '+79004445566'),
(3, 'Сидоров Алексей Петрович', '1992-11-30', 'ул. Гагарина, 7', '+79007778899'),
(4, 'Кузнецова Мария Дмитриевна', '1998-02-14', 'ул. Пушкина, 1', '+79001234567'),
(5, 'Смирнов Дмитрий Олегович', '1980-07-19', 'ул. Лермонтова, 22', '+79009876543'),
(6, 'Васильева Елена Игоревна', '1995-09-05', 'ул. Чехова, 8', '+79005554433'),
(7, 'Михайлов Роман Викторович', '1988-12-01', 'ул. Толстого, 15', '+79002223344'),
(8, 'Новикова Ольга Андреевна', '2000-04-17', 'ул. Некрасова, 3', '+79006667788'),
(9, 'Федоров Сергей Николаевич', '1975-06-28', 'ул. Горького, 9', '+79008889900'),
(10, 'Морозова Татьяна Владимировна', '1993-10-10', 'ул. Есенина, 12', '+79003332211'),
(11, 'Волков Артем Игоревич', '1991-01-20', 'ул. Садовая, 5', '+79001119988'),
(12, 'Соколова Дарья Алексеевна', '1997-06-15', 'ул. Центральная, 2', '+79004443322');

-- Заполняем выдачи (12 строк)
insert into выдачи (код_выдачи, код_книги, код_читателя, дата_выдачи, дата_возврата) values
(1, 1, 1, '2023-10-01', '2023-10-15'),
(2, 2, 1, '2023-10-05', null),
(3, 3, 2, '2023-10-10', '2023-10-25'),
(4, 4, 3, '2023-10-12', null),
(5, 5, 4, '2023-10-15', '2023-10-30'),
(6, 6, 5, '2023-10-20', null),
(7, 7, 6, '2023-10-22', '2023-11-05'),
(8, 8, 7, '2023-10-25', null),
(9, 9, 8, '2023-10-28', '2023-11-10'),
(10, 10, 9, '2023-11-01', null),
(11, 11, 10, '2023-11-05', '2023-11-20'),
(12, 12, 11, '2023-11-10', null);
go

-- ==========================================
-- 4. ИНДЕКСЫ (Ускорение поиска)
-- ==========================================
create index idx_читатели_фио on читатели(фио);
create index idx_книги_название on книги(название);
create index idx_выдачи_дата_выдачи on выдачи(дата_выдачи);
create index idx_выдачи_дата_возврата on выдачи(дата_возврата);
go

-- ==========================================
-- 5. ТРИГГЕР (Проверка логики дат)
-- ==========================================
create trigger trg_проверка_дат on выдачи
after insert, update
as
begin
    -- Если дата возврата указана и она раньше даты выдачи
    if exists (
        select 1 from inserted 
        where дата_возврата is not null and дата_возврата < дата_выдачи
    )
    begin
        raiserror('Ошибка: Дата возврата не может быть раньше даты выдачи!', 16, 1);
        rollback transaction; -- Отменяем ошибочное действие
    end
end
go

-- ==========================================
-- 6. ХРАНИМАЯ ПРОЦЕДУРА (Выдача книги)
-- ==========================================
create proc sp_выдать_книгу
    @код_книги int,
    @код_читателя int,
    @сообщение nvarchar(100) output
as
begin
    -- Проверяем наличие книги
    if (select количество_экземпляров from книги where код_книги = @код_книги) > 0
    begin
        -- Находим новый код выдачи
        declare @new_id int = (select isnull(max(код_выдачи), 0) + 1 from выдачи);
        
        -- Вставляем запись в выдачи
        insert into выдачи (код_выдачи, код_книги, код_читателя, дата_выдачи) 
        values (@new_id, @код_книги, @код_читателя, getdate());
        
        -- Уменьшаем количество экземпляров на складе
        update книги set количество_экземпляров = количество_экземпляров - 1 
        where код_книги = @код_книги;
        
        set @сообщение = 'Успешно выдана';
    end
    else
    begin
        set @сообщение = 'Ошибка: нет свободных экземпляров';
    end
end
go