---

-- ============================================================================
-- УРОВЕНЬ «УДОВЛЕТВОРИТЕЛЬНО» (БАЗОВЫЙ)
-- ============================================================================

-- 2. Создание таблиц (3НФ, ограничения NOT NULL, CHECK, PRIMARY/FOREIGN KEY)
IF OBJECT_ID('v_readers_masked', 'V') IS NOT NULL DROP VIEW v_readers_masked;
IF OBJECT_ID('Audit_Log', 'U') IS NOT NULL DROP TABLE Audit_Log;
IF OBJECT_ID('Loans', 'U') IS NOT NULL DROP TABLE Loans;
IF OBJECT_ID('Books', 'U') IS NOT NULL DROP TABLE Books;
IF OBJECT_ID('Readers', 'U') IS NOT NULL DROP TABLE Readers;
GO

CREATE TABLE Books (
    Код INT PRIMARY KEY,
    Название NVARCHAR(255) NOT NULL,
    Автор NVARCHAR(150) NOT NULL,
    Год_издания INT NOT NULL CHECK (Год_издания > 1900 AND Год_издания <= YEAR(GETDATE())),
    Количество_экземпляров INT NOT NULL DEFAULT 1 CHECK (Количество_экземпляров >= 0)
);

CREATE TABLE Readers (
    Код INT PRIMARY KEY,
    ФИО NVARCHAR(150) NOT NULL,
    Дата_рождения DATE NOT NULL CHECK (Дата_рождения > '1900-01-01' AND Дата_рождения < CAST(GETDATE() AS DATE)),
    Адрес NVARCHAR(255) NOT NULL,
    Телефон NVARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE Loans (
    Код_выдачи INT PRIMARY KEY,
    Книга INT NOT NULL FOREIGN KEY REFERENCES Books(Код),
    Читатель INT NOT NULL FOREIGN KEY REFERENCES Readers(Код),
    Дата_выдачи DATE NOT NULL DEFAULT CAST(GETDATE() AS DATE),
    Дата_возврата DATE NULL
);
GO

-- 3. Добавление 10 строк тестовых данных в каждую таблицу
INSERT INTO Books (Код, Название, Автор, Год_издания, Количество_экземпляров) VALUES
(1, N'Мастер и Маргарита', N'Булгаков М.А.', 1967, 5),
(2, N'Собачье сердце', N'Булгаков М.А.', 1987, 3),
(3, N'Тихий Дон', N'Шолохов М.А.', 1928, 4),
(4, N'Доктор Живаго', N'Пастернак Б.Л.', 1957, 2),
(5, N'Архипелаг ГУЛАГ', N'Солженицын А.И.', 1973, 3),
(6, N'Двенадцать стульев', N'Ильф И.А., Петров Е.П.', 1928, 6),
(7, N'Золотой телёнок', N'Ильф И.А., Петров Е.П.', 1931, 4),
(8, N'Пикник на обочине', N'Стругацкие А. и Б.', 1972, 5),
(9, N'Трудно быть богом', N'Стругацкие А. и Б.', 1964, 0),
(10, N'Сто лет одиночества', N'Маркес Г.Г.', 1967, 3);

INSERT INTO Readers (Код, ФИО, Дата_рождения, Адрес, Телефон) VALUES
(1, N'Иванов Иван Иванович', '1990-05-14', N'г. Москва, ул. Ленина, д. 10, кв. 5', '+79001112233'),
(2, N'Петрова Анна Сергеевна', '1985-11-23', N'г. Москва, пр. Мира, д. 45, кв. 12', '+79002223344'),
(3, N'Сидоров Алексей Дмитриевич', '1998-02-10', N'г. Москва, ул. Тверская, д. 8, кв. 30', '+79003334455'),
(4, N'Козлова Мария Владимировна', '2001-07-19', N'г. Москва, ул. Арбат, д. 15, кв. 4', '+79004445566'),
(5, N'Смирнов Дмитрий Олегович', '1993-09-01', N'г. Москва, ул. Садовая, д. 3, кв. 18', '+79005556677'),
(6, N'Морозова Елена Викторовна', '1988-12-12', N'г. Москва, ул. Гагарина, д. 22, кв. 7', '+79006667788'),
(7, N'Волков Сергей Николаевич', '1995-04-05', N'г. Москва, ул. Пушкина, д. 11, кв. 9', '+79007778899'),
(8, N'Лебедева Ольга Игоревна', '2003-08-27', N'г. Москва, ул. Чехова, д. 6, кв. 21', '+79008889900'),
(9, N'Соколов Павел Андреевич', '1992-01-30', N'г. Москва, ул. Кирова, д. 19, кв. 14', '+79009990011'),
(10, N'Новикова Татьяна Юрьевна', '1997-06-15', N'г. Москва, ул. Ломоносова, д. 2, кв. 3', '+79010001122');

INSERT INTO Loans (Код_выдачи, Книга, Читатель, Дата_выдачи, Дата_возврата) VALUES
(1, 1, 1, '2026-09-01', '2026-09-15'),
(2, 2, 2, '2026-09-05', '2026-09-20'),
(3, 1, 3, '2026-09-10', NULL),
(4, 6, 4, '2026-09-12', '2026-09-25'),
(5, 7, 5, '2026-09-15', NULL),
(6, 8, 6, '2026-09-18', '2026-10-02'),
(7, 8, 7, '2026-09-20', NULL),
(8, 3, 8, '2026-09-22', '2026-10-05'),
(9, 4, 9, '2026-09-25', NULL),
(10, 1, 10, '2026-10-01', NULL);
GO

-- 4. Три SELECT-запроса
-- 4.1. Список книг с количеством экземпляров
SELECT Код, Название, Автор, Год_издания, Количество_экземпляров
FROM Books;

-- 4.2. Активные выдачи (где дата возврата NULL)
SELECT 
    l.Код_выдачи,
    b.Название AS Книга,
    r.ФИО AS Читатель,
    l.Дата_выдачи,
    l.Дата_возврата
FROM Loans l
JOIN Books b ON l.Книга = b.Код
JOIN Readers r ON l.Читатель = r.Код
WHERE l.Дата_возврата IS NULL;

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

-- 5. Меры защиты на уровне БД (базовый пользователь и разграничение прав)
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'lib_user')
    CREATE USER lib_user WITHOUT LOGIN;

GRANT SELECT ON Books TO lib_user;
GRANT SELECT ON Readers TO lib_user;
GRANT SELECT, INSERT, UPDATE ON Loans TO lib_user;
GO


-- ============================================================================
-- УРОВЕНЬ «ХОРОШО» (ПРОДВИНУТЫЙ)
-- ============================================================================

-- 1. Хранимая процедура sp_issue_book(book_id, reader_id)
IF OBJECT_ID('sp_issue_book', 'P') IS NOT NULL DROP PROCEDURE sp_issue_book;
GO

CREATE PROCEDURE sp_issue_book
    @book_id INT,
    @reader_id INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        -- Проверка существования читателя
        IF NOT EXISTS (SELECT 1 FROM Readers WHERE Код = @reader_id)
        BEGIN
            ROLLBACK TRANSACTION;
            SELECT 404 AS Код_ответа, N'ОШИБКА: Читатель не найден.' AS Сообщение;
            RETURN;
        END

        -- Проверка существования книги
        DECLARE @copies INT;
        SELECT @copies = Количество_экземпляров FROM Books WHERE Код = @book_id;

        IF @copies IS NULL
        BEGIN
            ROLLBACK TRANSACTION;
            SELECT 404 AS Код_ответа, N'ОШИБКА: Книга не найдена.' AS Сообщение;
            RETURN;
        END

        -- Проверка, не выдана ли уже эта книга данному читателю
        IF EXISTS (SELECT 1 FROM Loans WHERE Книга = @book_id AND Читатель = @reader_id AND Дата_возврата IS NULL)
        BEGIN
            ROLLBACK TRANSACTION;
            SELECT 409 AS Код_ответа, N'ОШИБКА: Эта книга уже выдана данному читателю.' AS Сообщение;
            RETURN;
        END

        -- Проверка свободных экземпляров
        IF @copies <= 0
        BEGIN
            ROLLBACK TRANSACTION;
            SELECT 400 AS Код_ответа, N'ОШИБКА: Нет свободных экземпляров книги.' AS Сообщение;
            RETURN;
        END

        -- Вычисляем новый Код_выдачи и добавляем запись в Loans
        DECLARE @new_loan_id INT;
        SELECT @new_loan_id = ISNULL(MAX(Код_выдачи), 0) + 1 FROM Loans;

        INSERT INTO Loans (Код_выдачи, Книга, Читатель, Дата_выдачи, Дата_возврата)
        VALUES (@new_loan_id, @book_id, @reader_id, CAST(GETDATE() AS DATE), NULL);

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

        COMMIT TRANSACTION;
        SELECT 200 AS Код_ответа, N'УСПЕХ: Книга успешно выдана читателю.' AS Сообщение;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SELECT ERROR_NUMBER() AS Код_ответа, ERROR_MESSAGE() AS Сообщение;
    END CATCH
END;
GO

-- 2. Триггер на таблицу Loans (проверка дат при вставке и обновлении)
IF OBJECT_ID('trg_check_loan_dates', 'TR') IS NOT NULL DROP TRIGGER trg_check_loan_dates;
GO

CREATE TRIGGER trg_check_loan_dates
ON Loans
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- При вставке проверяем, что дата возврата больше даты выдачи (если указана)
    IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
    BEGIN
        IF EXISTS (SELECT 1 FROM inserted WHERE Дата_возврата IS NOT NULL AND Дата_возврата <= Дата_выдачи)
        BEGIN
            RAISERROR (N'Ошибка при вставке: Дата возврата должна быть строго больше даты выдачи!', 16, 1);
            ROLLBACK TRANSACTION;
            RETURN;
        END
    END

    -- При обновлении проверяем, что дата возврата не раньше даты выдачи
    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
        IF EXISTS (SELECT 1 FROM inserted WHERE Дата_возврата IS NOT NULL AND Дата_возврата < Дата_выдачи)
        BEGIN
            RAISERROR (N'Ошибка при обновлении: Дата возврата не может быть раньше даты выдачи!', 16, 1);
            ROLLBACK TRANSACTION;
            RETURN;
        END
    END
END;
GO

-- 3. Индексы (B-Tree — стандартный тип индекса NONCLUSTERED в MS SQL)
CREATE NONCLUSTERED INDEX idx_readers_fio ON Readers(ФИО);
CREATE NONCLUSTERED INDEX idx_books_title ON Books(Название);
CREATE NONCLUSTERED INDEX idx_loans_dates ON Loans(Дата_выдачи, Дата_возврата);
GO
/*
   Обоснование индексов (B-Tree):
   Поля ФИО, Название, Дата_выдачи и Дата_возврата чаще всего используются в условиях
   поиска WHERE и сортировки ORDER BY. Индекс B-Tree обеспечивает быстрый поиск O(log N)
   как по точному совпадению, так и по диапазонам дат и префиксам строк.
*/

-- 4. Разграничение прав: роли lib_admin и lib_librarian
IF DATABASE_PRINCIPAL_ID('lib_admin') IS NULL CREATE ROLE lib_admin;
IF DATABASE_PRINCIPAL_ID('lib_librarian') IS NULL CREATE ROLE lib_librarian;

-- Полный доступ для lib_admin
GRANT CONTROL ON SCHEMA::dbo TO lib_admin;

-- Права для lib_librarian (только выдача книг и просмотр отчетов)
GRANT SELECT ON Books TO lib_librarian;
GRANT SELECT ON Readers TO lib_librarian;
GRANT SELECT, INSERT, UPDATE ON Loans TO lib_librarian;
GRANT EXECUTE ON sp_issue_book TO lib_librarian;

-- Запрет лишних операций для lib_librarian
DENY DELETE ON Books TO lib_librarian;
DENY INSERT, UPDATE, DELETE ON Readers TO lib_librarian;
DENY DELETE ON Loans TO lib_librarian;
GO

-- 5. Тестирование процедур и триггера
-- Успешная выдача книги (Книга 2 -> Читатель 1)
EXEC sp_issue_book @book_id = 2, @reader_id = 1;

-- Попытка выдать книгу без экземпляров (Книга 9 имеет 0 экземпляров)
EXEC sp_issue_book @book_id = 9, @reader_id = 1;

-- Проверка срабатывания триггера при некорректной дате возврата
BEGIN TRY
    INSERT INTO Loans (Код_выдачи, Книга, Читатель, Дата_выдачи, Дата_возврата)
    VALUES (100, 3, 2, '2026-10-20', '2026-10-10');
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS Результат_работы_триггера;
END CATCH;
GO


-- ============================================================================
-- УРОВЕНЬ «ОТЛИЧНО» (ВЫСОКИЙ)
-- ============================================================================

-- 1. Аудит доступа: таблица Audit_Log и триггер на таблицу Books
CREATE TABLE Audit_Log (
    Код INT IDENTITY(1,1) PRIMARY KEY,
    Дата DATETIME NOT NULL DEFAULT GETDATE(),
    Пользователь NVARCHAR(100) NOT NULL DEFAULT SYSTEM_USER,
    Операция NVARCHAR(20) NOT NULL,
    Таблица NVARCHAR(50) NOT NULL,
    Первичный_ключ_записи INT NOT NULL,
    Старое_значение NVARCHAR(255) NULL,
    Новое_значение NVARCHAR(255) NULL
);
GO

CREATE TRIGGER trg_audit_books_copies
ON Books
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF UPDATE(Количество_экземпляров)
    BEGIN
        INSERT INTO Audit_Log (Дата, Пользователь, Операция, Таблица, Первичный_ключ_записи, Старое_значение, Новое_значение)
        SELECT 
            GETDATE(),
            SYSTEM_USER,
            'UPDATE',
            'Books',
            i.Код,
            CAST(d.Количество_экземпляров AS NVARCHAR(50)),
            CAST(i.Количество_экземпляров AS NVARCHAR(50))
        FROM inserted i
        JOIN deleted d ON i.Код = d.Код
        WHERE i.Количество_экземпляров <> d.Количество_экземпляров;
    END
END;
GO

-- Проверка работы аудита на примере уменьшения количества экземпляров через процедуру выдачи
EXEC sp_issue_book @book_id = 3, @reader_id = 4;
SELECT * FROM Audit_Log;
GO

-- 2. Защита чувствительных данных: VIEW с маскированием телефона
CREATE VIEW v_readers_masked AS
SELECT 
    Код,
    ФИО,
    Дата_рождения,
    Адрес,
    '+7 (XXX) XXX-' + RIGHT(Телефон, 2) + '-' + SUBSTRING(Телефон, LEN(Телефон) - 3, 2) AS Телефон
FROM Readers;
GO

-- Выдаем права библиотекарю на VIEW вместо прямой таблицы Readers
REVOKE SELECT ON Readers FROM lib_librarian;
GRANT SELECT ON v_readers_masked TO lib_librarian;
GO

SELECT * FROM v_readers_masked;
GO

-- 3. Резервное копирование и восстановление (команда для MS SQL Server)
/*
   План бэкапов:
   - Полный бэкап (Full Backup): раз в неделю (воскресенье, 02:00).
   - Дифференциальный бэкап: ежедневно в 02:00.
   - Бэкап журнала транзакций (Transaction Log): каждый час.
   - Хранение: локальный диск сервера + защищенное сетевое хранилище (NAS/Облако).

   Команда создания полного бэкапа (раскомментировать при наличии папки C:\Backups):
   BACKUP DATABASE CURRENT TO DISK = 'C:\Backups\LibraryDB_Full.bak' WITH INIT, COMPRESSION;

   Команда восстановления из бэкапа:
   RESTORE DATABASE LibraryDB FROM DISK = 'C:\Backups\LibraryDB_Full.bak' WITH REPLACE;

   Риски при восстановлении:
   1. Потеря последних транзакций, совершенных после момента создания бэкапа.
   2. Несоответствие схемы БД версии приложения после отката.
*/

-- 4. Анализ рисков и рекомендации
/*
   1. SQL-инъекции: защита через параметризованные хранимые процедуры (реализовано в sp_issue_book).
   2. Утечка персональных данных (ФИО, телефон): маскирование через VIEW и отзыв прав с таблицы (реализовано в v_readers_masked).
   3. Несанкционированное изменение данных: ролевая модель lib_admin / lib_librarian и запрет DENY DELETE (реализовано).
   4. Потеря или порча остатков книг: аудит изменений через триггер в таблицу Audit_Log и ограничение CHECK >= 0 (реализовано).
*/

-- 5. Документация и комментарии ко всем объектам БД (sp_addextendedproperty в MS SQL)
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'Таблица книг библиотеки', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'TABLE', @level1name = 'Books';
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'Таблица читателей библиотеки', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'TABLE', @level1name = 'Readers';
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'Таблица выдач книг читателям', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'TABLE', @level1name = 'Loans';
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'Журнал аудита изменений количества книг', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'TABLE', @level1name = 'Audit_Log';
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'Представление с маскированием телефонов читателей', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'VIEW', @level1name = 'v_readers_masked';
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'Хранимая процедура выдачи книги читателю', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_issue_book';
GO