schema.sql

-- TABLES 

CREATE TABLE "authors" (
    "id" INTEGER,
    "first_name" TEXT NOT NULL,
    "last_name" TEXT NOT NULL,
    "nationality" TEXT,
    "date_of_birth" DATE,
    PRIMARY KEY("id")
);

CREATE TABLE "translators" (
    "id" INTEGER,
    "first_name" TEXT NOT NULL,
    "last_name" TEXT NOT NULL,
    "nationality" TEXT,
    "date_of_birth" DATE,
    PRIMARY KEY("id")
);

CREATE TABLE "publishers" (
    "id" INTEGER,
    "name" TEXT NOT NULL,
    "founded_year" INTEGER,
    "phone_number" TEXT,
    "email" TEXT,
    "website" TEXT,
    PRIMARY KEY("id")
);

CREATE TABLE "books" (
    "id" INTEGER,
    "title" TEXT NOT NULL,
    "language" TEXT NOT NULL,
    "original_language" TEXT NOT NULL,
    "year" INTEGER,
    "edition" INTEGER,
    "edition_year" INTEGER,
    "category" TEXT NOT NULL,
    "genre" TEXT NOT NULL,
    "rating" NUMERIC CHECK("rating" BETWEEN 0 AND 5),
    "location" TEXT, -- shelf, kindle, etc...
    "isbn" TEXT, -- international standart book number
    "is_read" BOOLEAN DEFAULT FALSE,
    "sold" BOOLEAN DEFAULT FALSE,
    "lent" BOOLEAN DEFAULT FALSE,
    "borrowed" BOOLEAN DEFAULT FALSE,
    "translator_id" INTEGER,
    "publisher_id" INTEGER,
    FOREIGN KEY("translator_id") REFERENCES "translators"("id"),
    FOREIGN KEY("publisher_id") REFERENCES "publishers"("id"),
    PRIMARY KEY("id")
);

-- Association table authors-book
CREATE TABLE "authored" (
    "author_id" INTEGER NOT NULL, 
    "book_id" INTEGER NOT NULL,
    PRIMARY KEY("author_id", "book_id"),
    FOREIGN KEY("author_id") REFERENCES "authors"("id"),
    FOREIGN KEY("book_id") REFERENCES "books"("id")
);

CREATE TABLE "transactions" (
    "id" INTEGER,
    "type" TEXT NOT NULL CHECK("type" IN ('purchase', 'sale')),
    "value" NUMERIC NOT NULL,
    "timestamp" DATETIME DEFAULT CURRENT_TIMESTAMP,
    "entity_type" TEXT NOT NULL CHECK("entity_type" IN ('person', 'store')),
    "entity_name" TEXT NOT NULL,
    "contact" TEXT, -- phone number or email
    PRIMARY KEY("id")
);

-- Association table books-transaction
CREATE TABLE "books_in_transaction" (
    "transaction_id" INTEGER NOT NULL,
    "book_id" INTEGER NOT NULL,
    FOREIGN KEY ("transaction_id") REFERENCES "transactions"("id"),
    FOREIGN KEY ("book_id") REFERENCES "books"("id"),
    PRIMARY KEY ("transaction_id", "book_id")
);

CREATE TABLE "loans" (
    "id" INTEGER,
    "loan_date" DATE NOT NULL DEFAULT CURRENT_DATE,
    "type" TEXT NOT NULL CHECK("type" IN ('borrow', 'lend')),
    "loaner_type" TEXT NOT NULL CHECK("loaner_type" IN ('person', 'library')),
    "loaner_name" TEXT NOT NULL,
    "fine_per_day" NUMERIC CHECK("fine_per_day" >= 0 AND "fine_per_day" = ROUND("fine_per_day", 2)) DEFAULT 0,
    "total_fine" NUMERIC CHECK("total_fine" >= 0 AND "total_fine" = ROUND("total_fine", 2)) DEFAULT 0,
    PRIMARY KEY("id")
);

-- Association table books-loans
CREATE TABLE "books_on_loan" (
    "loan_id" INTEGER,
    "book_id" INTEGER,
    "due_date" DATE, -- Each book can have its own due and return date.
    "return_date" DATE,
    FOREIGN KEY ("loan_id") REFERENCES "loans"("id"),
    FOREIGN KEY ("book_id") REFERENCES "books"("id"),
    PRIMARY KEY ("loan_id", "book_id")
);

-- VIEWS

-- To view all books in the library
CREATE VIEW "available_books" AS
SELECT 
    "id", "title", "year",
    (SELECT "first_name" || ' ' || "last_name"
    FROM "authors"
    JOIN "authored" ON "authored"."author_id" = "authors"."id"
    WHERE "authored"."book_id" = "books"."id"
    ORDER BY "authors"."last_name" LIMIT 1) AS "author",
    "language", "rating", "location", "category", "genre"       
FROM "books"
WHERE "location" = 'shelf' OR "location" = 'kindle'
ORDER BY "location", "author", "year";

-- To view all books on the shelf
CREATE VIEW "books_on_shelf" AS
SELECT * FROM "available_books" WHERE "location" = 'shelf';

-- To view all books on the kindle
CREATE VIEW "books_on_kindle" AS
SELECT * FROM "available_books" WHERE "location" = 'kindle';

-- To view all books that have already been read
CREATE VIEW "been_read" AS
SELECT 
    "id", "title", "year",
    (SELECT "first_name" || ' ' || "last_name"
    FROM "authors"
    JOIN "authored" ON "authored"."author_id" = "authors"."id"
    WHERE "authored"."book_id" = "books"."id"
    ORDER BY "authors"."last_name" LIMIT 1) AS "author",
    "language", "rating", "location", "category", "genre"       
FROM "books"
WHERE "is_read" = TRUE
ORDER BY "location", "author", "year";

-- To view all books that have been sold (soft deletion)
CREATE VIEW "sold_books" AS
SELECT 
    "id", "title", "year",
    (SELECT "first_name" || ' ' || "last_name"
    FROM "authors"
    JOIN "authored" ON "authored"."author_id" = "authors"."id"
    WHERE "authored"."book_id" = "books"."id"
    ORDER BY "authors"."last_name" LIMIT 1) AS "author",
    "language", "transaction_id", "entity_name" AS "buyer", "value", "timestamp"   
FROM "books"
JOIN "books_in_transaction" ON "books_in_transaction"."book_id" = "books"."id"
JOIN "transactions" ON "transactions"."id" = "books_in_transaction"."transaction_id"
WHERE "sold" = TRUE
ORDER BY "timestamp";

-- To view all books that have been loaned
CREATE VIEW "loaned_books" AS
SELECT
    "id", "type", "title", "year",
    (SELECT "first_name" || ' ' || "last_name"
    FROM "authors"
    JOIN "authored" ON "authored"."author_id" = "authors"."id"
    WHERE "authored"."book_id" = "books"."id"
    ORDER BY "authors"."last_name" LIMIT 1) AS "author",
    "language", "loan_id", "loaner_name" AS "loaner", "loan_date", "due_date",
    "return_date", "fine_per_day", "total_fine"       
FROM "books"
JOIN "books_on_loan" ON "books_on_loan"."book_id" = "books"."id"
JOIN "loans" ON "loans"."id" = "books_on_loan"."loan_id"
ORDER BY "due_date", "loan_date";

-- To view all books that have been lent
CREATE VIEW "lent_books" AS
SELECT * FROM "loaned_books" 
WHERE "lent" = TRUE;

-- To view all books that have been borrowed
CREATE VIEW "borrowed_books" AS
SELECT * FROM "loaned_books" 
WHERE "borrowed" = TRUE;

-- To view all books that have been borrowed and not yet returned
CREATE VIEW "current_borrowed_books" AS
SELECT * FROM "borrowed_books" 
WHERE "return_date" IS NULL;

-- TRIGGERS

-- Trigger to update books.sold to TRUE when a new sale transaction is inserted
CREATE TRIGGER "sold"
AFTER INSERT ON "books_in_transaction"
FOR EACH ROW
WHEN (SELECT "type" FROM "transactions" WHERE "id" = NEW."transaction_id") = 'sale'
BEGIN
    UPDATE "books"
    SET "sold" = TRUE, "location" = 'sold'
    WHERE "id" = NEW."book_id";
END;

-- Trigger to update books.sold to FALSE when a new purchase transaction is inserted for a book that was already sold
CREATE TRIGGER "bought"
AFTER INSERT ON "books_in_transaction"
FOR EACH ROW
WHEN (SELECT "type" FROM "transactions" WHERE "id" = NEW."transaction_id") = 'purchase'
BEGIN
    UPDATE "books"
    SET "sold" = FALSE, "location" = 'shelf'
    WHERE "id" = NEW."book_id";
END;

-- Trigger to update books.lent to TRUE when a new lend is inserted
CREATE TRIGGER "lent"
AFTER INSERT ON "books_on_loan"
FOR EACH ROW
WHEN (SELECT "type" FROM "loans" WHERE "id" = NEW."loan_id") = 'lend'
BEGIN
    UPDATE "books"
    SET "lent" = TRUE, "location" = 'lent'
    WHERE "id" = NEW."book_id";
END;

-- Trigger to update books.lent to FALSE when a lent book is returned to the user
CREATE TRIGGER "lend_returned"
AFTER UPDATE OF "return_date" ON "books_on_loan"
FOR EACH ROW
WHEN (SELECT "type" FROM "loans" WHERE "id" = NEW."loan_id") = 'lend'
BEGIN
    UPDATE "books"
    SET "lent" = FALSE, "location" = 'shelf'
    WHERE "id" = NEW."book_id";
END;
    
-- Trigger to update books.borrowed to TRUE when a new borrow is inserted
CREATE TRIGGER "borrowed"
AFTER INSERT ON "books_on_loan"
FOR EACH ROW
WHEN (SELECT "type" FROM "loans" WHERE "id" = NEW."loan_id") = 'borrow'
BEGIN
    UPDATE "books"
    SET "borrowed" = TRUE, "location" = 'shelf'
    WHERE "id" = NEW."book_id";
END;

-- Trigger to update the location of books to 'returned' when they are returned
CREATE TRIGGER "borrow_returned"
AFTER UPDATE OF "return_date" ON "books_on_loan"
FOR EACH ROW
WHEN (SELECT "type" FROM "loans" WHERE "id" = NEW."loan_id") = 'borrow'
BEGIN
    UPDATE "books"
    SET "location" = 'returned'
    WHERE "id" = NEW."book_id";
END;

-- INDEXES

-- To optimize this database, indexes are created to order the data, allowing for binary search, 
-- which is much faster than linear search in unordered data. The queries will then bring the answers faster

CREATE INDEX "books_index" ON "books"("id");

CREATE INDEX "books_title_index" ON "books"("title");

CREATE INDEX "authors_index" ON "authors"("id");

CREATE INDEX "authors_name_index" ON "authors"("first_name", "last_name");