---

-- 1. Издательства
CREATE TABLE Publishers (
    PublisherID AUTOINCREMENT PRIMARY KEY,
    PublisherName TEXT(255) UNIQUE NOT NULL,
    Address TEXT(255)
);

-- 2. Авторы
CREATE TABLE Authors (
    AuthorID AUTOINCREMENT PRIMARY KEY,
    FirstName TEXT(50) NOT NULL,
    LastName TEXT(50) NOT NULL
);

-- 3. Книги (CHECK для года)
CREATE TABLE Books (
    BookID AUTOINCREMENT PRIMARY KEY,
    Title TEXT(255) NOT NULL,
    AuthorID LONG NOT NULL,
    PublisherID LONG,
    YearPublished INTEGER CHECK (YearPublished > 1500),
    ISBN TEXT(20)
);

-- 4. Читатели
CREATE TABLE Readers (
    ReaderID AUTOINCREMENT PRIMARY KEY,
    LastName TEXT(50) NOT NULL,
    FirstName TEXT(50) NOT NULL,
    BirthDate DATETIME,
    Phone TEXT(20)
);

-- 5. Выдачи (с каскадным удалением и CHECK на уровне таблицы)
CREATE TABLE Loans (
    LoanID AUTOINCREMENT PRIMARY KEY,
    BookID LONG NOT NULL,
    ReaderID LONG NOT NULL,
    IssueDate DATETIME NOT NULL,
    ReturnDate DATETIME,
    CONSTRAINT fk_Loans_Books FOREIGN KEY (BookID) REFERENCES Books(BookID) ON DELETE CASCADE,
    CONSTRAINT fk_Loans_Readers FOREIGN KEY (ReaderID) REFERENCES Readers(ReaderID),
    CONSTRAINT chk_ReturnDate CHECK (ReturnDate >= IssueDate OR ReturnDate IS NULL)
);

-- 6. Индексы
CREATE INDEX idx_Readers_LastName ON Readers (LastName);
CREATE INDEX idx_Books_Title ON Books (Title);