DatabaseCreation: VIEWS.sql

CREATE VIEW view_admin AS
SELECT
    u.user_id,
    u.username,
    u.email,
    m.member_number,
    COUNT(lh.loan_id) AS vkupno_pozajmuvanja,
    COUNT(f.fine_id) AS neplateni_kazni,
    SUM(f.amount) AS vkupen_dolg
FROM app_user u
JOIN member m ON m.user_id = u.user_id
LEFT JOIN loan_history lh ON lh.member_user_id = u.user_id
LEFT JOIN fines f ON f.loan_id = lh.loan_id
    AND (f.status = 'unpaid' OR f.status = 'overdue')
GROUP BY
    u.user_id,
    u.username,
    u.email,
    m.member_number;


CREATE OR REPLACE VIEW view_myreservations AS
SELECT
    r.reservation_id,
    r.member_user_id as member_user_id,
    b.title,
    r.reservation_date,
    r.expiration_date,
    r.status
FROM reservation r
JOIN book b ON b.barcode = r.barcode;


CREATE VIEW view_currentloans AS
SELECT
    lh.member_user_id AS korisnik_id,
    b.title,
    lh.borrow_date,
    lh.due_date,
    lh.due_date::date - CURRENT_DATE AS ostanati_denovi
FROM loan_history lh
JOIN book_copy bc ON bc.copy_id = lh.copy_id
JOIN book b ON b.barcode = bc.barcode
WHERE lh.return_date IS NULL
  AND lh.status = 'borrowed'
  AND lh.due_date::date >= CURRENT_DATE;

CREATE VIEW view_bookcatalog AS
SELECT DISTINCT
    b.barcode,
    b.title AS book_title,
    a.first_name || ' ' || a.last_name AS avtor,
    c.name AS kategorija,
    g.name AS zhanr,
    p.name AS izdavach,
    b.publication_year
FROM book b
LEFT JOIN book_author ba ON ba.barcode = b.barcode
LEFT JOIN author a ON a.author_id = ba.author_id
LEFT JOIN category_book cb ON cb.barcode = b.barcode
LEFT JOIN category c ON c.category_id = cb.category_id
LEFT JOIN book_genre bg ON bg.barcode = b.barcode
LEFT JOIN genre g ON g.genre_id = bg.genre_id
LEFT JOIN publisher p ON p.publisher_id = b.publisher_id;


CREATE VIEW View_MyNotifications AS
SELECT
    n.notification_id,
    n.member_user_id,
    n.notification_type,
    n.status,
    n.created_at
FROM notification n;


CREATE OR REPLACE VIEW view_userborrowhistory AS
SELECT
    lh.loan_id,
    lh.member_user_id,

    bc.barcode,
    b.title,

    lh.borrow_date,
    lh.due_date,
    lh.return_date,

    lh.status AS loan_status,

    CASE
        WHEN lh.return_date IS NOT NULL
            THEN lh.return_date - lh.borrow_date
        ELSE CURRENT_DATE - lh.borrow_date
    END AS denovi_traenje,

    CASE
        WHEN lh.return_date IS NOT NULL THEN
            CASE
                WHEN lh.return_date > lh.due_date
                    THEN lh.return_date - lh.due_date
                ELSE 0
            END
        ELSE
            CASE
                WHEN CURRENT_DATE > lh.due_date
                    THEN CURRENT_DATE - lh.due_date
                ELSE 0
            END
    END AS denovi_kasni

FROM loan_history lh
JOIN book_copy bc ON lh.copy_id = bc.copy_id
JOIN book b ON bc.barcode = b.barcode;


CREATE OR REPLACE VIEW view_bookmonthly AS
WITH counts AS (
    SELECT
        EXTRACT(YEAR FROM bvl.view_timestamp) AS year,
        EXTRACT(MONTH FROM bvl.view_timestamp) AS month,
        bvl.barcode,
        b.title,
        COUNT(*) AS view_count
    FROM book_view_log bvl
    JOIN book b ON b.barcode = bvl.barcode
    GROUP BY year, month, bvl.barcode, b.title
),
ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY year, month
            ORDER BY view_count DESC
        ) AS max
    FROM counts
)
SELECT
    year,
    month,
    barcode,
    title,
    view_count
FROM ranked
WHERE max = 1
ORDER BY year, month;



CREATE VIEW view_upcomingevents AS
SELECT
    event_id,
    title,
    event_date,
    start_time,
    end_time,
    location,
    max_seats,
    available_seats,
    CASE
        WHEN available_seats > 0 THEN 'AVAILABLE'
        ELSE 'FULL'
    END AS availability_status
FROM event
WHERE event_date >= CURRENT_DATE;


CREATE VIEW view_myfinesandfees AS
SELECT
    lh.member_user_id AS korisnik_id,
    b.title AS naslov,
    f.amount,
    f.status AS status_kazna,
    f.payment_due_date
FROM fines f
JOIN loan_history lh ON lh.loan_id = f.loan_id
JOIN book_copy bc ON bc.copy_id = lh.copy_id
JOIN book b ON b.barcode = bc.barcode
WHERE f.status IN ('unpaid', 'overdue');