-- Каталог (иерархический)
CREATE TABLE Catalog (
    ID AUTOINCREMENT PRIMARY KEY,
    PARENT_ID LONG,
    NAME TEXT(100),
    CONSTRAINT fk_parent FOREIGN KEY (PARENT_ID) REFERENCES Catalog(ID)
);

-- Книги
CREATE TABLE Books (
    ISBN TEXT(20) PRIMARY KEY,
    TITLE TEXT(200),
    AUTHOR TEXT(100),
    PUBLISHER TEXT(100),
    PUBLISH_PLACE TEXT(100),
    EDITION TEXT(20),
    PAGES LONG
);

-- Таблица связи "Книга–Каталог" (многие-ко-многим)
CREATE TABLE BookCatalog (
    BOOK_ISBN TEXT(20),
    CATALOG_ID LONG,
    CONSTRAINT pk_BookCatalog PRIMARY KEY (BOOK_ISBN, CATALOG_ID),
    CONSTRAINT fk_book FOREIGN KEY (BOOK_ISBN) REFERENCES Books(ISBN),
    CONSTRAINT fk_catalog FOREIGN KEY (CATALOG_ID) REFERENCES Catalog(ID)
);

-- Экземпляры
CREATE TABLE Copies (
    INVENTORY_NO AUTOINCREMENT PRIMARY KEY,
    ISBN TEXT(20),
    IN_LIBRARY YESNO,
    CONSTRAINT fk_copy_book FOREIGN KEY (ISBN) REFERENCES Books(ISBN)
);

-- Читатели
CREATE TABLE Readers (
    READER_NO AUTOINCREMENT PRIMARY KEY,
    FULLNAME TEXT(100),
    BIRTHDATE DATETIME,
    PHONE_HOME TEXT(20),
    PHONE_WORK TEXT(20)
);

-- История выдачи экземпляров
CREATE TABLE BorrowHistory (
    ID AUTOINCREMENT PRIMARY KEY,
    INVENTORY_NO LONG,
    READER_NO LONG,
    DATE_TAKEN DATETIME,
    DATE_RETURN DATETIME,
    CONSTRAINT fk_copy FOREIGN KEY (INVENTORY_NO) REFERENCES Copies(INVENTORY_NO),
    CONSTRAINT fk_reader FOREIGN KEY (READER_NO) REFERENCES Readers(READER_NO)
);

-- Каталог
INSERT INTO Catalog (NAME, PARENT_ID) VALUES ('Книги', NULL);
INSERT INTO Catalog (NAME, PARENT_ID) VALUES ('Научная литература', 1);
INSERT INTO Catalog (NAME, PARENT_ID) VALUES ('Художественная литература', 1);
INSERT INTO Catalog (NAME, PARENT_ID) VALUES ('Физика', 2);
INSERT INTO Catalog (NAME, PARENT_ID) VALUES ('История', 2);

-- Книги
INSERT INTO Books (ISBN, TITLE, AUTHOR, PUBLISHER, PUBLISH_PLACE, EDITION, PAGES)
VALUES ('978-5-389-07426-6', 'Краткая история времени', 'Стивен Хокинг', 'АМФОРА', 'СПб', '1-е', 256);

INSERT INTO Books (ISBN, TITLE, AUTHOR, PUBLISHER, PUBLISH_PLACE, EDITION, PAGES)
VALUES ('978-5-17-118366-4', 'Война и мир', 'Л. Н. Толстой', 'АСТ', 'Москва', '2-е', 1300);

-- Связи книги с каталогом (многие ко многим)
INSERT INTO BookCatalog (BOOK_ISBN, CATALOG_ID) VALUES ('978-5-389-07426-6', 4); -- Физика
INSERT INTO BookCatalog (BOOK_ISBN, CATALOG_ID) VALUES ('978-5-389-07426-6', 2); -- Научная литература
INSERT INTO BookCatalog (BOOK_ISBN, CATALOG_ID) VALUES ('978-5-17-118366-4', 3); -- Художественная литература
INSERT INTO BookCatalog (BOOK_ISBN, CATALOG_ID) VALUES ('978-5-17-118366-4', 5); -- История

-- Экземпляры
INSERT INTO Copies (ISBN, IN_LIBRARY) VALUES ('978-5-389-07426-6', TRUE);
INSERT INTO Copies (ISBN, IN_LIBRARY) VALUES ('978-5-17-118366-4', TRUE);

-- Читатели
INSERT INTO Readers (FULLNAME, BIRTHDATE, PHONE_HOME, PHONE_WORK)
VALUES ('Иванов Иван Иванович', #1990-05-12#, '123-45-67', '765-43-21');

-- История выдачи
INSERT INTO BorrowHistory (INVENTORY_NO, READER_NO, DATE_TAKEN, DATE_RETURN)
VALUES (1, 1, #2025-10-10#, #2025-10-20#);