---

CREATE OR ALTER PROCEDURE sp_issue_book
    @book_id INT,
    @reader_id INT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Проверка существования книги
    IF NOT EXISTS (SELECT 1 FROM Books WHERE book_id = @book_id)
    BEGIN
        RAISERROR('Ошибка: Книга с ID %d не найдена.', 16, 1, @book_id);
        RETURN;
    END

    -- Проверка существования читателя
    IF NOT EXISTS (SELECT 1 FROM Readers WHERE reader_id = @reader_id)
    BEGIN
        RAISERROR('Ошибка: Читатель с ID %d не найден.', 16, 1, @reader_id);
        RETURN;
    END

    -- Проверка наличия свободных экземпляров
    DECLARE @available_copies INT;
    SELECT @available_copies = copies_count FROM Books WHERE book_id = @book_id;

    IF @available_copies <= 0
    BEGIN
        RAISERROR('Ошибка: У книги (ID: %d) нет доступных экземпляров для выдачи.', 16, 1, @book_id);
        RETURN;
    END

    -- Выполнение транзакции выдачи книги
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 1. Создание записи в таблице Loans
        INSERT INTO Loans (book_id, reader_id, loan_date, return_date)
        VALUES (@book_id, @reader_id, GETDATE(), NULL);

        -- 2. Уменьшение количества экземпляров в Books
        UPDATE Books
        SET copies_count = copies_count - 1
        WHERE book_id = @book_id;

        COMMIT TRANSACTION;
        PRINT 'Успех: Книга успешно выдана читателю.';
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        RAISERROR(@ErrorMessage, 16, 1);
    END CATCH
END;
GO
CREATE OR ALTER TRIGGER trg_check_loan_dates
ON Loans
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Проверка: если дата возврата указана и она раньше даты выдачи
    IF EXISTS (
        SELECT 1 
        FROM inserted 
        WHERE return_date IS NOT NULL 
          AND return_date < loan_date
    )
    BEGIN
        RAISERROR('Ошибка валидации: Дата возврата книги не может быть раньше даты выдачи!', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;
GO
-- Индекс для быстрого поиска по ФИО читателя
CREATE NONCLUSTERED INDEX IX_Readers_FullName 
ON Readers(full_name);

-- Индекс для быстрого поиска по названию книги
CREATE NONCLUSTERED INDEX IX_Books_Title 
ON Books(title);

-- Составной индекс по датам выдачи и возврата для оптимизации аналитических запросов
CREATE NONCLUSTERED INDEX IX_Loans_Dates 
ON Loans(loan_date, return_date);
GO
-- А. Тест: Успешная выдача книги (у книги есть экземпляры)
EXEC sp_issue_book @book_id = 1, @reader_id = 2;

-- Проверяем, что создалась запись в Loans и уменьшилось количество в Books
SELECT * FROM Loans WHERE book_id = 1 AND reader_id = 2;
SELECT book_id, title, copies_count FROM Books WHERE book_id = 1;

-- Б. Тест: Попытка выдать книгу с 0 экземплярами
-- Обнуляем экземпляры книги для проверки
UPDATE Books SET copies_count = 0 WHERE book_id = 5;

-- Вызов процедуры должен завершиться ошибкой
EXEC sp_issue_book @book_id = 5, @reader_id = 1;
-- Тест: Попытка обновить дату возврата на дату, которая раньше даты выдачи
-- (Ожидается срабатывание ошибки из триггера и отмена операции)
UPDATE Loans 
SET return_date = '2020-01-01' 
WHERE loan_id = 1;