---

-- Удаляем старые таблицы, если они существуют

DROP TABLE IF EXISTS Loans;
DROP TABLE IF EXISTS BookCategories;
DROP TABLE IF EXISTS Copies;
DROP TABLE IF EXISTS LibraryBooks;
DROP TABLE IF EXISTS Categories;
DROP TABLE IF EXISTS PublishingHouses;
DROP TABLE IF EXISTS LibraryReaders;


-- ==========================================
-- 1. ЧИТАТЕЛИ
-- ==========================================

CREATE TABLE LibraryReaders (
    reader_id INT PRIMARY KEY IDENTITY(1,1),
    surname VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    patronymic VARCHAR(50),
    address VARCHAR(150) NOT NULL,
    phone_work VARCHAR(20),
    phone_home VARCHAR(20),
    date_of_birth DATE NOT NULL,
    library_card INT NOT NULL UNIQUE,

    CHECK (phone_work IS NOT NULL OR phone_home IS NOT NULL)
);

-- 3 записи
INSERT INTO LibraryReaders
(surname, first_name, patronymic, address, phone_work, phone_home, date_of_birth, library_card)
VALUES
('Смирнов', 'Алексей', 'Игоревич', 'ул. Молодежная, 12', '89001234567', NULL, '2001-04-12', 2001),

('Кузнецова', 'Мария', 'Андреевна', 'ул. Центральная, 25', NULL, '89007654321', '1999-08-21', 2002),

('Орлов', 'Дмитрий', 'Сергеевич', 'ул. Садовая, 8', '89005554433', '89001113322', '2003-01-17', 2003);


-- ==========================================
-- 2. ОБЛАСТИ ЗНАНИЙ
-- ==========================================

CREATE TABLE Categories (
    category_id INT PRIMARY KEY IDENTITY(1,1),
    category_number INT NOT NULL UNIQUE,
    category_name VARCHAR(100) NOT NULL
);

-- 3 записи
INSERT INTO Categories
(category_number, category_name)
VALUES
(201, 'Литература'),
(202, 'Программирование'),
(203, 'История');


-- ==========================================
-- 3. ИЗДАТЕЛЬСТВА
-- ==========================================

CREATE TABLE PublishingHouses (
    publisher_id INT PRIMARY KEY IDENTITY(1,1),
    publisher_name VARCHAR(100) NOT NULL,
    publisher_city VARCHAR(100) NOT NULL
);

-- 3 записи
INSERT INTO PublishingHouses
(publisher_name, publisher_city)
VALUES
('Просвещение', 'Москва'),
('Питер', 'Санкт-Петербург'),
('Альфа-Книга', 'Москва');


-- ==========================================
-- 4. КНИГИ
-- ==========================================

CREATE TABLE LibraryBooks (
    book_id INT PRIMARY KEY IDENTITY(1,1),
    book_code VARCHAR(50) NOT NULL UNIQUE,
    book_title VARCHAR(150) NOT NULL,
    main_author VARCHAR(100),
    other_authors VARCHAR(255),
    publisher_id INT NOT NULL,
    year_published INT NOT NULL,
    pages INT NOT NULL,

    FOREIGN KEY (publisher_id)
        REFERENCES PublishingHouses(publisher_id),

    CHECK (year_published >= 1960),
    CHECK (pages > 0),

    CHECK (
        main_author IS NOT NULL
        OR other_authors IS NULL
    )
);

-- 3 записи
INSERT INTO LibraryBooks
(book_code, book_title, main_author, other_authors, publisher_id, year_published, pages)
VALUES
('LB001', 'Мастер и Маргарита', 'Булгаков М.А.', NULL, 1, 1988, 480),

('LB002', 'Основы C++', 'Иванов А.В.', 'Петров И.С.', 2, 2021, 520),

('LB003', 'История России', 'Соколов Н.П.', NULL, 3, 2019, 640);


-- ==========================================
-- 5. СВЯЗЬ КНИГ И ОБЛАСТЕЙ ЗНАНИЙ
-- ==========================================

CREATE TABLE BookCategories (
    book_id INT NOT NULL,
    category_id INT NOT NULL,

    PRIMARY KEY (book_id, category_id),

    FOREIGN KEY (book_id)
        REFERENCES LibraryBooks(book_id),

    FOREIGN KEY (category_id)
        REFERENCES Categories(category_id)
);

-- 3 записи
INSERT INTO BookCategories
(book_id, category_id)
VALUES
(1, 1),
(2, 2),
(3, 3);


-- ==========================================
-- 6. ЭКЗЕМПЛЯРЫ КНИГ
-- ==========================================

CREATE TABLE Copies (
    copy_id INT PRIMARY KEY IDENTITY(1,1),
    inventory_code INT NOT NULL UNIQUE,
    book_id INT NOT NULL,
    available BIT NOT NULL DEFAULT 1,

    FOREIGN KEY (book_id)
        REFERENCES LibraryBooks(book_id)
);

-- 3 записи
INSERT INTO Copies
(inventory_code, book_id, available)
VALUES
(7001, 1, 1),
(7002, 2, 1),
(7003, 3, 1);


-- ==========================================
-- 7. ВЫДАЧА КНИГ
-- ==========================================

CREATE TABLE Loans (
    loan_id INT PRIMARY KEY IDENTITY(1,1),
    reader_id INT NOT NULL,
    copy_id INT NOT NULL,
    date_issued DATE NOT NULL,
    date_returned DATE,

    FOREIGN KEY (reader_id)
        REFERENCES LibraryReaders(reader_id),

    FOREIGN KEY (copy_id)
        REFERENCES Copies(copy_id),

    CHECK (
        date_returned IS NULL
        OR date_returned >= date_issued
    )
);

-- 3 записи
INSERT INTO Loans
(reader_id, copy_id, date_issued, date_returned)
VALUES
(1, 1, '2026-09-01', NULL),
(2, 2, '2026-08-20', '2026-08-30'),
(3, 3, '2026-09-02', NULL);


-- ==========================================
-- ПРОВЕРКА
-- ==========================================

SELECT * FROM LibraryReaders;
SELECT * FROM Categories;
SELECT * FROM PublishingHouses;
SELECT * FROM LibraryBooks;
SELECT * FROM BookCategories;
SELECT * FROM Copies;
SELECT * FROM Loans;