---

CREATE TABLE Publishers (
    PublisherID COUNTER PRIMARY KEY,
    PublisherName TEXT(255) UNIQUE NOT NULL,
    Address TEXT(255)
);

CREATE TABLE Authors (
    AuthorID COUNTER PRIMARY KEY,
    FirstName TEXT(50) NOT NULL,
    LastName TEXT(50) NOT NULL
);

CREATE TABLE Books (
    BookID COUNTER PRIMARY KEY,
    Title TEXT(255) NOT NULL,
    AuthorID LONG NOT NULL,
    PublisherID LONG,
    YearPublished INTEGER,
    ISBN TEXT(20),
    CONSTRAINT fk_Books_Authors FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID),
    CONSTRAINT fk_Books_Publishers FOREIGN KEY (PublisherID) REFERENCES Publishers(PublisherID),
    CONSTRAINT chk_YearPublished CHECK (YearPublished > 1500)
);

CREATE TABLE Readers (
    ReaderID COUNTER PRIMARY KEY,
    LastName TEXT(50) NOT NULL,
    FirstName TEXT(50) NOT NULL,
    BirthDate DATETIME,
    Phone TEXT(20)
);

CREATE TABLE Loans (
    LoanID COUNTER 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)
);

CREATE INDEX idx_Readers_LastName ON Readers (LastName);
CREATE INDEX idx_Books_Title ON Books (Title);