DatabaseCreation: views.sql

----------------------------- View 1: view_events_by_date ----------------------------- 
-- Home page со идни настани 
CREATE VIEW view_events_by_date AS
SELECT
    e.id,
    e.title,
    e.start_date,
    e.end_date,
    MIN(ei.image_url) AS image
FROM EVENT e
LEFT JOIN EVENT_IMAGE ei ON ei.EVENTid = e.id
WHERE e.start_date >= CURRENT_DATE
GROUP BY e.id, e.title, e.start_date, e.end_date
ORDER BY e.start_date ASC;

SELECT * 
FROM view_events_by_date 
WHERE id = 12455; 

-- Погледот ги прикажува сите идни настани подредени по датум со по една слика за секој настан. 
-- Се користи на почетната страница на апликацијата и 
-- ја имплементира бизнис логиката за приказ на листа на достапни настани.


----------------------------- View 2: view_event_details ----------------------------- 
-- Детали за избран конкретен настан
CREATE VIEW view_event_details AS
SELECT
    e.id,
    e.title,
    e.description,
    e.start_date,
    e.end_date,
    e.end_date - e.start_date AS duration_days,
    c.category_name  AS category,
    ARRAY_AGG(DISTINCT ei.image_url) FILTER (WHERE ei.image_url IS NOT NULL) AS images
FROM EVENT e
LEFT JOIN CATEGORIZATION c ON e.CATEGORIZATIONid = c.id
LEFT JOIN EVENT_IMAGE ei ON e.id = ei.EVENTid
GROUP BY e.id, e.title, e.start_date, e.end_date, c.category_name ;

SELECT * 
FROM view_event_details 
WHERE id=56984;

-- Погледот ги прикажува деталите за конкретен настан — 
-- наслов, опис, датуми, траење, категорија и сите слики. 
-- Се користи на страницата за детали на настан кога корисникот ќе кликне на конкретен настан и 
-- ја имплементира бизнис логиката за приказ на комплетни информации за настанот.


----------------------------- View 3: view_event_halls ----------------------------- 
-- Пред да се купи тикет, преглед на сала и venues за настанот
CREATE VIEW view_event_halls AS
SELECT
    e.id AS event_id,
    e.title,
    h.id AS hall_id,
    h.hall_name AS hall_name,
    h.capacity,
    eh.allowed_access,
    v.venue_name  AS venue_name,
    v.city
FROM EVENT e
JOIN EVENT_HALL eh ON eh.EVENTid = e.id
JOIN HALL h ON h.id = eh.HALLid
JOIN VENUE v ON v.id = h.VENUEid;

SELECT * 
FROM view_event_halls 
WHERE event_id = 12356;

-- Погледот ги прикажува сите сали и venues за даден настан заедно со 
-- нивниот капацитет и дозволен пристап.
-- Се користи при процесот на купување тикет кога корисникот треба да избере сала и 
-- ја имплементира бизнис логиката за приказ на достапни локации за настанот.


----------------------------- View 4: view_event_ticket_availability -----------------------------
-- Преглед на типови на тикети, цени и количини за веќе избрана сала
CREATE VIEW view_event_ticket_availability AS
SELECT
    e.id AS event_id,
    tt.id AS ticket_type_id,
    tt.type_name  AS ticket_type,
    ett.price,
    ett.quantity_available,
    COUNT(DISTINCT t.id) FILTER (WHERE t.status = 'ACTIVE') AS sold,
    ett.quantity_available - COUNT(DISTINCT t.id) FILTER (WHERE t.status = 'ACTIVE') AS remaining
FROM EVENT e
JOIN EVENT_TICKET_TYPE ett ON ett.EVENTid = e.id
JOIN TICKET_TYPE tt ON tt.id = ett.TICKET_TYPEid
LEFT JOIN TICKET t ON t.EVENTid = e.id AND t.TICKET_TYPEid = tt.id
GROUP BY e.id, tt.id, tt.type_name , ett.price, ett.quantity_available;

SELECT * 
FROM view_event_ticket_availability 
WHERE event_id = 12455;

-- Погледот ги прикажува достапните типови на тикети за даден настан заедно со 
-- нивните цени, вкупен број, продадени и преостанати количини. 
-- Се користи при купување тикет по избор на сала и 
-- ја имплементира бизнис логиката за приказ на достапност на тикети во реално време.


----------------------------- View 5: view_ticket_status -----------------------------
-- Преглед на детали за тикет
CREATE VIEW view_ticket_status AS
SELECT
    t.id AS ticket_id,
    t.code,
    t.status,
    e.title AS event_title,
    e.start_date,
    h.hall_name AS hall_name,
    CASE 
        WHEN s.seat_number IS NULL THEN 'N/A'
        ELSE s.seat_number::TEXT
    END AS seat_number,
    tt.type_name AS ticket_type,
    u.first_name || ' ' || u.last_name AS holder_name
FROM TICKET t
JOIN EVENT e ON t.EVENTid = e.id
JOIN HALL h ON t.HALLid = h.id
JOIN TICKET_TYPE tt ON t.TICKET_TYPEid = tt.id
JOIN APP_USER u ON t.APP_USERid = u.id
LEFT JOIN SEAT s ON t.SEATid = s.id;

SELECT * 
FROM view_ticket_status 
WHERE ticket_id = 3;

-- Погледот ги прикажува деталите за конкретен тикет — 
-- статус, настан, сала, седиште, тип и корисник. Се користи при преглед на тикет 
-- од страна на корисникот и при скенирање на тикет при влез на настан и 
-- ја имплементира бизнис логиката за верификација и приказ на тикети.


----------------------------- View 6: view_event_reviews ----------------------------- 
-- Приказ на рецензии и оценки за минат настан 
CREATE OR REPLACE VIEW view_event_reviews AS
SELECT
    e.id AS event_id,
    e.title AS event_title,
    e.start_date AS start_date,
    r.id AS review_id,
    CASE
        WHEN u.first_name IS NULL THEN 'Anonymous'
        ELSE u.first_name || ' ' || u.last_name
    END                                 
    AS user_name,
    r.rating,
    r.review_comment
FROM EVENT e
LEFT JOIN REVIEW r ON r.EVENTid = e.id
LEFT JOIN APP_USER u ON u.id = r.APP_USERid;

SELECT * 
FROM view_event_reviews 
WHERE event_id = 12345;

-- Погледот ги прикажува сите рецензии и оценки за даден минат настан 
-- со прикажување на Anonymous за анонимни рецензии. 
-- Се користи на страницата на настанот по неговото завршување и 
-- ја имплементира бизнис логиката за систем на оценување и рецензии.


----------------------------- View 7: view_user_order_summary ----------------------------- 
-- Преглед на историја на нарачки по корисник
CREATE VIEW view_user_order_summary AS
SELECT
    uo.id AS order_id,
    u.id AS user_id,
    u.first_name || ' ' || u.last_name AS customer_name,
    uo.order_date,
    uo.total_amount,
    s.status_name AS order_status,
    pc.code AS promo_code,
    pc.discount_percent,
    ps.paid_amount,
    ps.payment_status,
    ps.payment_method,
    ts.ticket_count
FROM USER_ORDER uo
JOIN APP_USER u ON u.id  = uo.APP_USERid
JOIN STATUS s ON s.id  = uo.STATUSid
LEFT JOIN PROMO_CODE pc  ON pc.id = uo.PROMO_CODEid
LEFT JOIN (
    SELECT user_orderid, COUNT(*) AS ticket_count
    FROM ticket
    GROUP BY user_orderid
) ts ON ts.user_orderid = uo.id
LEFT JOIN (
    SELECT
        p.user_orderid,
        SUM(p.amount) FILTER (WHERE p.status = 'COMPLETED') AS paid_amount,
        MAX(p.status)       AS payment_status,
        MAX(pm.method_name) AS payment_method
    FROM payment p
    LEFT JOIN payment_method pm ON pm.id = p.payment_methodid
    GROUP BY p.user_orderid
) ps ON ps.user_orderid = uo.id;

SELECT * FROM view_user_order_summary WHERE user_id = 25679; 


-- Оригиналната верзија на погледот користеше subqueries кои ги 
-- агрегираа сите редови од табелите TICKET и PAYMENT пред да ги филтрираат, 
-- што резултираше со бавно извршување. Поради тоа прашалникот беше преуреден.

----------------------------- Преуреден View 7 ----------------------------- 
CREATE OR REPLACE VIEW view_user_order_summary AS
SELECT
    uo.id AS order_id,
    u.id AS user_id,
    u.first_name || ' ' || u.last_name  AS customer_name,
    uo.order_date,
    uo.total_amount,
    s.status_name AS order_status,
    pc.code AS promo_code,
    pc.discount_percent,
    SUM(p.amount) FILTER (WHERE p.status = 'COMPLETED') AS paid_amount,
    MAX(p.status) AS payment_status,
    MAX(pm.method_name) AS payment_method,
    COUNT(DISTINCT t.id) AS ticket_count
FROM USER_ORDER uo
JOIN APP_USER u ON u.id = uo.APP_USERid
JOIN STATUS s ON s.id = uo.STATUSid
LEFT JOIN PROMO_CODE pc ON pc.id = uo.PROMO_CODEid
LEFT JOIN PAYMENT p ON p.user_orderid = uo.id
LEFT JOIN PAYMENT_METHOD pm ON pm.id = p.payment_methodid
LEFT JOIN TICKET t ON t.user_orderid = uo.id
GROUP BY uo.id, u.id, u.first_name, u.last_name,
         uo.order_date, uo.total_amount, s.status_name,
         pc.code, pc.discount_percent;

SELECT * FROM view_user_order_summary WHERE user_id = 25679; 

-- Погледот ја прикажува целосната историја на нарачки за даден корисник — нарачки, 
-- плаќања, тикети и промо кодови. Се користи на страницата на профилот на корисникот и 
-- ја имплементира бизнис логиката за преглед на историја на купувања.


----------------------------- View 8: view_user_subscriptions_feed ----------------------------- 
-- Приказ на настани само категориите на кои е претплатен корисникот
CREATE VIEW view_user_subscriptions_feed AS
SELECT
    u.id AS user_id,
    u.email,
    e.id AS event_id,
    e.title AS event_title,
    e.start_date,
    c.category_name  AS category,
    sub.subcategory_name  AS subcategory,
    'CATEGORY' AS subscription_type
FROM APP_USER u
JOIN USER_CATEGORY_SUBSCRIPTION ucs ON ucs.APP_USERid = u.id
JOIN CATEGORIZATION c ON c.id = ucs.CATEGORIZATIONid
JOIN EVENT e ON e.CATEGORIZATIONid = c.id
LEFT JOIN SUBCATEGORY sub ON sub.id = e.SUBCATEGORYid
WHERE e.start_date >= CURRENT_DATE

UNION

SELECT
    u.id AS user_id,
    u.email,
    e.id AS event_id,
    e.title AS event_title,
    e.start_date,
    c.category_name  AS category,
    sub.subcategory_name  AS subcategory,
    'SUBCATEGORY' AS subscription_type
FROM APP_USER u
JOIN USER_SUBCATEGORY_SUBSCRIPTION uss ON uss.APP_USERid = u.id
JOIN SUBCATEGORY sub ON sub.id = uss.SUBCATEGORYid
JOIN CATEGORIZATION c ON c.id = sub.CATEGORIZATIONid
JOIN EVENT e ON e.SUBCATEGORYid = sub.id  
WHERE e.start_date >= CURRENT_DATE;

SELECT *
FROM view_user_subscriptions_feed
WHERE user_id=24589;

-- Погледот ги прикажува само идните настани од категориите и подкатегориите 
-- на кои е претплатен корисникот, комбинирајќи ги двата типа на претплати преку UNION. 
-- Се користи на персонализираниот feed на корисникот и 
-- ја имплементира бизнис логиката за приказ на релевантни настани според интересите на корисникот.


----------------------------- View 9: view_event_sales_report ----------------------------- 
-- Финансиски извештај по настан (приходи, рефундации, пополнетост)
CREATE VIEW view_event_sales_report AS
SELECT
    e.id AS event_id,
    e.title,
    e.start_date,
    c.category_name AS category,
    COUNT(t.id) FILTER (WHERE t.status = 'ACTIVE') AS active_tickets,
    COUNT(t.id) FILTER (WHERE t.status = 'CANCELLED') AS cancelled_tickets,
    SUM(p.amount) FILTER (WHERE p.status = 'COMPLETED') AS total_revenue,
    SUM(rf.amount) FILTER (WHERE rf.status = 'APPROVED') AS total_refunded,
    SUM(p.amount) FILTER (WHERE p.status = 'COMPLETED') -
    SUM(rf.amount) FILTER (WHERE rf.status = 'APPROVED') AS net_revenue,
    SUM(h.capacity) AS total_capacity,
    ROUND(COUNT(t.id) FILTER (WHERE t.status = 'ACTIVE') * 100.0 /
          NULLIF(SUM(h.capacity), 0), 2) AS occupancy_percent
FROM EVENT e
LEFT JOIN CATEGORIZATION c ON c.id = e.CATEGORIZATIONid
LEFT JOIN TICKET t ON t.EVENTid  = e.id
LEFT JOIN USER_ORDER uo ON uo.id = t.USER_ORDERid
LEFT JOIN PAYMENT p ON p.USER_ORDERid = uo.id
LEFT JOIN REFUND rf ON rf.PAYMENTid = p.id
LEFT JOIN EVENT_HALL eh ON eh.EVENTid = e.id
LEFT JOIN HALL h ON h.id = eh.HALLid
GROUP BY e.id, e.title, e.start_date, c.category_name;

SELECT * 
FROM view_event_sales_report 
WHERE event_id = 12455;

-- Погледот прикажува комплетен финансиски извештај за даден настан — активни 
-- и откажани тикети, вкупен приход, рефундации, нето приход и процент на пополнетост. 
-- Се користи од страна на организаторите и администраторите за анализа на успешноста на настанот и 
-- ја имплементира бизнис логиката за финансиско известување.