---

-- Создание таблицы Authors
CREATE TABLE Authors (
    AuthorID AUTOINCREMENT PRIMARY KEY,
    FirstName TEXT(50) NOT NULL,
    LastName TEXT(50) NOT NULL,
    BirthYear INTEGER CHECK (BirthYear > 1500)
);

-- Создание таблицы Publishers
CREATE TABLE Publishers (
    PublisherID AUTOINCREMENT PRIMARY KEY,
    Name TEXT(100) NOT NULL UNIQUE,
    City TEXT(50)
);

-- Создание таблицы Books
CREATE TABLE Books (
    BookID AUTOINCREMENT PRIMARY KEY,
    Title TEXT(200) NOT NULL,
    AuthorID INTEGER NOT NULL,
    PublisherID INTEGER NOT NULL,
    YearPublished INTEGER NOT NULL CHECK (YearPublished > 1500),
    ISBN TEXT(20) UNIQUE,
    CONSTRAINT fk_Books_Author FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID),
    CONSTRAINT fk_Books_Publisher FOREIGN KEY (PublisherID) REFERENCES Publishers(PublisherID)
);

-- Создание таблицы Readers
CREATE TABLE Readers (
    ReaderID AUTOINCREMENT PRIMARY KEY,
    LastName TEXT(50) NOT NULL,
    FirstName TEXT(50) NOT NULL,
    BirthDate DATE,
    Phone TEXT(15)
);

-- Создание таблицы Loans
CREATE TABLE Loans (
    LoanID AUTOINCREMENT PRIMARY KEY,
    BookID INTEGER NOT NULL,
    ReaderID INTEGER NOT NULL,
    LoanDate DATE NOT NULL,
    ReturnDate DATE,
    CONSTRAINT chk_dates CHECK (ReturnDate >= LoanDate OR ReturnDate IS NULL),
    CONSTRAINT fk_Loans_Book FOREIGN KEY (BookID) REFERENCES Books(BookID) ON DELETE CASCADE,
    CONSTRAINT fk_Loans_Reader FOREIGN KEY (ReaderID) REFERENCES Readers(ReaderID)
);

-- Создание индексов
CREATE INDEX idx_Readers_LastName ON Readers (LastName);
CREATE INDEX idx_Books_Title ON Books (Title);