Логички и физички дизајн - Креирање база податоци (со SQL DDL): DDL-Script-Book-Tracker.sql

-- Drop procedures if they exist
DROP PROCEDURE IF EXISTS register_user;
DROP PROCEDURE IF EXISTS delete_book;
DROP PROCEDURE IF EXISTS add_book;
DROP PROCEDURE IF EXISTS update_book;

-- Drop tables if they exist
DROP TABLE IF EXISTS Users CASCADE;
DROP TABLE IF EXISTS Member CASCADE;
DROP TABLE IF EXISTS Book CASCADE;
DROP TABLE IF EXISTS Book_Details CASCADE;
DROP TABLE IF EXISTS Book_Copies CASCADE;
DROP TABLE IF EXISTS Book_Author CASCADE;
DROP TABLE IF EXISTS Author CASCADE;
DROP TABLE IF EXISTS Loan CASCADE;
DROP TABLE IF EXISTS Fine CASCADE;
DROP TABLE IF EXISTS FinePayment CASCADE;
DROP TABLE IF EXISTS Cart CASCADE;

-- Create procedures first
CREATE OR REPLACE PROCEDURE register_user(
        p_username TEXT,
        p_email TEXT,
        p_password TEXT,
        p_role TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
        IF EXISTS (SELECT 1 FROM users WHERE username = p_username OR email = p_email) THEN
        RAISE EXCEPTION 'Username or Email already exists';
    END IF;

    -- Insert new user
    INSERT INTO users (username, email, password, role)
    VALUES (p_username, p_email, p_password, p_role);

END;
$$;

CREATE OR REPLACE PROCEDURE delete_book(
        p_book_id INT
)
LANGUAGE plpgsql
AS $$
BEGIN
        IF EXISTS (SELECT 1 FROM Book WHERE bookid = p_book_id) THEN
        DELETE FROM Book WHERE bookid = p_book_id;
    END IF;
END;
$$;

CREATE OR REPLACE PROCEDURE add_book(
        p_book_isbn TEXT,
        p_book_title TEXT,
        p_book_genre TEXT,
        p_book_published_year INT,
        p_book_description TEXT,
        p_book_total_copies INT,
        p_book_format TEXT,
        p_book_language TEXT,
        p_book_publisher TEXT,
        p_book_pages INT,
        p_book_author_id INT,
        p_book_image TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
        new_book_id INT;
BEGIN
        INSERT INTO BOOK (ISBN, Title, Genre, PublishedYear, Description, CoverImage, TotalCopies) VALUES (
                p_book_isbn, p_book_title, p_book_genre, p_book_published_year, p_book_description, p_book_image, p_book_total_copies
        ) RETURNING BookID INTO new_book_id;

        INSERT INTO BOOK_DETAILS (BookID, Format, Language, Publisher, Pages) VALUES (
                new_book_id, p_book_format, p_book_language, p_book_publisher, p_book_pages
        );

        INSERT INTO Book_Author (BookID, AuthorID) VALUES (
                new_book_id, p_book_author_id
        );
END;
$$;

CREATE OR REPLACE PROCEDURE update_book(
        p_book_id INT,
        p_book_title TEXT,
        p_book_isbn TEXT,
        p_book_genre TEXT,
        p_book_published_year INT,
        p_book_description TEXT,
        p_book_total_copies INT,
        p_book_image TEXT,
        p_book_language TEXT,
        p_book_publisher TEXT,
        p_book_pages INT,
        p_book_format TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
        UPDATE BOOK SET isbn = p_book_isbn, title = p_book_title, genre = p_book_genre, publishedyear = p_book_published_year,
                description = p_book_description, totalcopies = p_book_total_copies, coverimage = p_book_image
        WHERE bookid = p_book_id;

        UPDATE BOOK_DETAILS SET format = p_book_format, language = p_book_language, publisher = p_book_publisher, pages = p_book_pages
        WHERE bookid = p_book_id;
END;
$$;

-- Now create tables

CREATE TABLE Users (
    UserID SERIAL PRIMARY KEY,
    Username TEXT NOT NULL,
    FirstName TEXT,
    LastName TEXT,
    Address TEXT,
    Phone NUMERIC,
    Password TEXT NOT NULL,
    Email TEXT NOT NULL,
    Role TEXT NOT NULL
);

CREATE TABLE Member (
    MemberID SERIAL PRIMARY KEY,
    Expired_Date DATE NOT NULL,
    Membership_Status TEXT NOT NULL CHECK (Membership_Status IN ('Active', 'Inactive', 'Suspended')),
    UserID INT REFERENCES Users(UserID) ON DELETE CASCADE
);

CREATE TABLE Book (
    BookID SERIAL PRIMARY KEY,
    ISBN TEXT NOT NULL,
    Title TEXT NOT NULL,
    Genre TEXT NOT NULL,
    PublishedYear NUMERIC NOT NULL,
    Description TEXT, 
    CoverImage TEXT,
    TotalCopies NUMERIC DEFAULT 0
);

CREATE TABLE Book_Details (
    DetailsID SERIAL PRIMARY KEY,
    BookID INT REFERENCES Book(BookID) ON DELETE CASCADE,
    Format TEXT NOT NULL CHECK (Format IN ('Hardcover', 'Paperback')),
    Language TEXT NOT NULL,
    Publisher TEXT NOT NULL,
    Pages TEXT NOT NULL
);

CREATE TABLE Book_Copies (
    CopyID SERIAL PRIMARY KEY,
    BookID INT REFERENCES Book(BookID) ON DELETE CASCADE,
    Condition TEXT NOT NULL CHECK (Condition IN ('New', 'Good', 'Damaged'))
);

CREATE TABLE Author (
    AuthorID SERIAL PRIMARY KEY,
    FirstName TEXT NOT NULL,
    LastName TEXT NOT NULL,
    Nationality TEXT NOT NULL,
    DateOfBirth DATE NOT NULL,
    Author_description TEXT,
    Author_image TEXT
);

CREATE TABLE Book_Author (
    BookID INT REFERENCES Book(BookID) ON DELETE CASCADE,
    AuthorID INT REFERENCES Author(AuthorID) ON DELETE CASCADE,
    PRIMARY KEY (BookID, AuthorID)
);

CREATE TABLE Loan (
    LoanID SERIAL PRIMARY KEY,
    LoanDate DATE NOT NULL,
    ReturnDate DATE,
    Status TEXT NOT NULL,
    BookCopyID INT REFERENCES Book_Copies(CopyID) ON DELETE CASCADE,
    MemberID INT REFERENCES Member(MemberID) ON DELETE CASCADE
);

CREATE TABLE Fine (
    FineID SERIAL PRIMARY KEY,
    FineAmount NUMERIC NOT NULL,
    FineDate DATE NOT NULL,
    Status TEXT NOT NULL,
    LoanID INT REFERENCES Loan(LoanID) ON DELETE CASCADE
);

CREATE TABLE FinePayment (
    FinePaymentID SERIAL PRIMARY KEY,
    PaymentDate DATE NOT NULL,
    PaymentAmount NUMERIC NOT NULL,
    FineID INT REFERENCES Fine(FineID) ON DELETE CASCADE
);

CREATE TABLE Cart (
    CartID SERIAL PRIMARY KEY,       
    BookID INT REFERENCES Book(BookID) ON DELETE CASCADE,
    MemberID INT REFERENCES Member(MemberID) ON DELETE CASCADE
);

-- Create the function and trigger for total copies
CREATE OR REPLACE FUNCTION update_total_copies() 
RETURNS TRIGGER AS $$
BEGIN
    -- If a new copy is inserted, increment the total copies count
    IF TG_OP = 'INSERT' THEN
        UPDATE Book 
        SET TotalCopies = TotalCopies + 1
        WHERE BookID = NEW.BookID;
    -- If a copy is deleted, decrement the total copies count
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE Book 
        SET TotalCopies = TotalCopies - 1
        WHERE BookID = OLD.BookID;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_total_copies
AFTER INSERT OR DELETE ON Book_Copies
FOR EACH ROW EXECUTE FUNCTION update_total_copies();

CREATE OR REPLACE FUNCTION validate_login(
    p_username TEXT
) RETURNS users -- Returns a full row from the users table
LANGUAGE plpgsql
AS $$
DECLARE
    user_record users; -- Variable to store the row
BEGIN
    -- Check if the user exists
    IF NOT EXISTS (SELECT 1 FROM users WHERE username = p_username) THEN
        RAISE EXCEPTION 'Username does not exist!';
    END IF;

    -- Fetch the user row
    SELECT * INTO user_record FROM users WHERE username = p_username;

    RETURN user_record;
END;
$$;

CREATE INDEX idx_author_firstname ON author(LOWER(firstname));
CREATE INDEX idx_author_lastname ON author(LOWER(lastname));
CREATE INDEX idx_member_userid ON member(userid);

CREATE OR REPLACE FUNCTION get_genre_loan_stats()
RETURNS TABLE (
    Genre text,
    Period date,
    LoanCount integer,
    MovingAverage numeric
)
LANGUAGE SQL
STABLE
AS $$
WITH TimePeriodLoans AS (
    SELECT 
        b.Genre,
        l.LoanDate,
        DATE_TRUNC('quarter', l.LoanDate) as QuarterPeriod
    FROM 
        Book b
        JOIN Book_Copies bc ON b.BookID = bc.BookID
        JOIN Loan l ON bc.CopyID = l.BookCopyID
    WHERE 
        l.LoanDate >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '6 months'
)
SELECT 
    Genre::text,
    Period::date,
    LoanCount::integer,
    ROUND(MovingAverage, 2)::numeric
FROM (
    SELECT 
        Genre,
        QuarterPeriod as Period,
        COUNT(*) as LoanCount,
        AVG(COUNT(*)) OVER (
            PARTITION BY Genre
            ORDER BY QuarterPeriod 
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) as MovingAverage
    FROM TimePeriodLoans
    GROUP BY Genre, QuarterPeriod
    HAVING COUNT(*) > 3  -- Filter for genres with more than 3 loans per period
) subquery
ORDER BY 
    Period DESC, LoanCount DESC;
$$;

CREATE OR REPLACE FUNCTION get_book_loan_stats()
RETURNS TABLE (
    Title text,
    AvgDaysToReturn numeric(10,2),
    MinDaysToReturn integer,
    MaxDaysToReturn integer,
    TotalLoans integer,
    LoansWithFines integer
)
LANGUAGE SQL
STABLE
AS $$
SELECT 
    b.Title::text,
    AVG(l.ReturnDate - l.LoanDate)::numeric(10,2) AS AvgDaysToReturn,
    MIN(l.ReturnDate - l.LoanDate)::integer AS MinDaysToReturn,
    MAX(l.ReturnDate - l.LoanDate)::integer AS MaxDaysToReturn,
    COUNT(*)::integer AS TotalLoans,
    COUNT(CASE WHEN f.FineID IS NOT NULL THEN 1 END)::integer AS LoansWithFines
FROM 
    Loan l
    JOIN Book_Copies bc ON l.BookCopyID = bc.CopyID
    JOIN Book b ON bc.BookID = b.BookID
    LEFT JOIN Fine f ON l.LoanID = f.LoanID
WHERE 
    l.ReturnDate IS NOT NULL
    AND l.ReturnDate >= l.LoanDate -- Prevent negative days
GROUP BY 
    b.BookID, b.Title
HAVING 
    COUNT(*) >= 5
ORDER BY 
    AvgDaysToReturn DESC;
$$;

CREATE OR REPLACE FUNCTION get_member_history()
RETURNS TABLE (
    MemberName text,
    MembershipStatus text,
    StartDate date,
    EndDate date,
    TotalLoans integer,
    CurrentLoans integer,
    TotalFines integer,
    TotalFineAmount numeric,
    TotalPayments numeric
)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    RETURN QUERY
    WITH DateRanges AS (
        SELECT
            generate_series(
                date_trunc('year', CURRENT_DATE),
                date_trunc('month', CURRENT_DATE),
                interval '1 month'
            ) as start_date,
            generate_series(
                date_trunc('year', CURRENT_DATE) + interval '1 month',
                date_trunc('month', CURRENT_DATE) + interval '1 month',
                interval '1 month'
            ) as end_date
    )
    SELECT 
        u.FirstName || ' ' || u.LastName::text AS MemberName,
        m.Membership_Status::text AS MembershipStatus,
        d.start_date::date AS StartDate,
        d.end_date::date AS EndDate,
        COUNT(DISTINCT l.LoanID)::integer AS TotalLoans,
        COUNT(DISTINCT CASE WHEN l.ReturnDate IS NULL THEN l.LoanID END)::integer AS CurrentLoans,
        COUNT(DISTINCT f.FineID)::integer AS TotalFines,
        COALESCE(SUM(f.FineAmount), 0)::numeric AS TotalFineAmount,
        COALESCE(SUM(fp.PaymentAmount), 0)::numeric AS TotalPayments
    FROM 
        DateRanges d
        CROSS JOIN Member m
        JOIN Users u ON m.UserID = u.UserID
        LEFT JOIN Loan l ON m.MemberID = l.MemberID 
            AND l.LoanDate >= d.start_date 
            AND l.LoanDate < d.end_date
        LEFT JOIN Fine f ON l.LoanID = f.LoanID
        LEFT JOIN FinePayment fp ON f.FineID = fp.FineID
    GROUP BY 
        u.FirstName, u.LastName, m.Membership_Status, d.start_date, d.end_date
    ORDER BY 
        d.start_date DESC, TotalLoans DESC;
END;
$$;