Changes between Version 51 and Version 52 of DatabaseCreation

Timestamp:

06/29/26 21:38:36 (6 days ago)

Author:

231027

Comment:

--

DatabaseCreation

@@ -331 +331 @@
 == DML скрипти - Погледи (Views)
 
-=== `Performer_Events`
-
-Овој поглед дава преглед на сите изведувачи и настаните на кои тие учествуваат, заедно со прецизниот термин на нивниот настап.
-
-{{{
-
-CREATE VIEW "Performer_Events" AS
-SELECT p.performer_id,
-       p.name AS performer_name,
-       e.event_id,
-       e.name AS event_name,
-       eh.event_time
-FROM "Performer" p
-JOIN "Event_Happening_Performer" ehp ON p.performer_id = ehp.performer_id
-JOIN "Event_Happening" eh ON ehp.event_happening_id = eh.event_happening_id
-JOIN "Event" e ON eh.event_id = e.event_id;
-
-}}}
-
 === `Venue_Layout`
 
-Овој поглед ја прикажува целосната структура на секој објект (сала), поврзувајќи ги поединечните седишта со соодветните сектори и името на објектот.
-
-{{{
-
-CREATE VIEW "Venue_Layout" AS
+Овој поглед ја прикажува деталната физичка структура на секој објект (сала), поврзувајќи ги поединечните седишта со нивните сектори и самите локации. Патеката на релациите е поставена линеарно, овозможувајќи брза проверка на точната позиција на седиштето преку неговиот ред и број.
+
+{{{
+
+CREATE OR REPLACE VIEW "Venue_Layout" AS
 SELECT v.venue_id,
        v.name AS venue_name,
@@ -362 +343 @@
        s.name AS section_name,
        st.seat_id,
+       st.row_number,
        st.seat_number
 FROM "Venue" v
@@ -371 +353 @@
 === `User_Tickets`
 
-Овој поглед дава детален преглед на сите купени билети по корисник, вклучувајќи го соодветниот настан, QR-кодот за влез и информациите за евентуална рефундација.
-
-{{{
-
-CREATE VIEW "User_Tickets" AS
+Овој поглед дава детален хронолошки преглед на сите купени поединечни билети по корисник, вклучувајќи ја точната платена цена, QR-кодот за влез и терминот на настанот. Преку релацијата со ставките за рефундација, погледот нуди и инстантна информација за тоа кои карти се откажани и кога се вратени парите.
+
+{{{
+
+CREATE OR REPLACE VIEW "User_Tickets" AS
 SELECT u.user_id,
        u.username,
+       toi.order_item_id,
        t.ticket_id,
        e.event_id,
        e.name AS event_name,
-       tp.purchase_id,
-       tp.qr_code,
-       tr.refund_id,
+       eh.event_time,
+       toi.qr_code,
+       toi.item_price AS price_paid,
+       tri.refund_item_id,
        tr.refund_time
 FROM "User" u
-JOIN "Ticket_Purchase" tp ON u.user_id = tp.user_id
-JOIN "Ticket" t ON tp.ticket_id = t.ticket_id
+JOIN "Regular_User" ru ON u.user_id = ru.user_id
+JOIN "Ticket_Order" o ON ru.user_id = o.user_id
+JOIN "Ticket_Order_Item" toi ON o.order_id = toi.order_id
+JOIN "Ticket" t ON toi.ticket_id = t.ticket_id
 JOIN "Event_Happening" eh ON t.event_happening_id = eh.event_happening_id
 JOIN "Event" e ON eh.event_id = e.event_id
-LEFT JOIN "Ticket_Refund" tr ON tp.purchase_id = tr.purchase_id;
-
-}}}
-
-=== `Event_User_Ratings`
-
-Овој поглед овозможува детален пристап до поединечните коментари и оценки што секој корисник ги оставил за одреден термин на настан.
-
-{{{
-
-CREATE VIEW "Event_User_Ratings" AS
-SELECT eh.event_happening_id,
-       e.event_id,
-       e.name AS event_name,
-       u.user_id,
-       u.username,
-       ehr.rating_id,
-       ehr.rating,
-       ehr.comment
-FROM "Event" e
-JOIN "Event_Happening" eh ON e.event_id = eh.event_id
-JOIN "Event_Happening_Rating" ehr ON eh.event_happening_id = ehr.event_happening_id
-JOIN "User" u ON ehr.user_id = u.user_id;
-
-}}}
-
-=== `Event_Overall_Ratings`
-
-Овој поглед врши статистичка анализа на задоволството на публиката преку пресметување на просечната оцена и вкупниот број на рецензии за секој поединечен настан.
-
-{{{
-
-CREATE VIEW "Event_Overall_Ratings" AS
-SELECT 
-    e.event_id,
-    e.name AS event_name,
-    eh.event_happening_id,
-    eh.event_time,
-    COUNT(ehr.rating_id) AS total_reviews,
-    AVG(ehr.rating) AS average_rating
-FROM "Event" e
-JOIN "Event_Happening" eh ON e.event_id = eh.event_id
-JOIN "Event_Happening_Rating" ehr ON eh.event_happening_id = ehr.event_happening_id
-GROUP BY e.event_id, e.name, eh.event_happening_id, eh.event_time;
-
-}}}
-
-=== `Event_Financial_Summary`
-
-Овој поглед ги сумира финансиските резултати за секој настан, прикажувајќи го вкупниот број на продадени билети, нето приходот по одбивање на рефундациите и посебно издвоената заработка од административните такси при враќање на влезниците.
-
-{{{
-
-CREATE VIEW "Event_Financial_Summary" AS
-SELECT 
-    e.event_id,
-    e.name AS event_name,
-    eh.event_happening_id,
-    eh.event_time,
-    COUNT(tp.purchase_id) AS total_tickets_sold,
-    
-    -- total revenue
-    SUM(tp.purchase_amount) - SUM(CASE WHEN tr.refund_amount IS NOT NULL THEN tr.refund_amount ELSE 0 END) AS net_revenue,
-    
-    -- refund taxes
-    SUM(CASE WHEN tr.refund_id IS NOT NULL THEN tp.purchase_amount - tr.refund_amount ELSE 0 END) AS refund_tax_profit
-
-FROM "Event" e
-JOIN "Event_Happening" eh ON e.event_id = eh.event_id
-JOIN "Ticket" t ON eh.event_happening_id = t.event_happening_id
-JOIN "Ticket_Purchase" tp ON t.ticket_id = tp.ticket_id
-LEFT JOIN "Ticket_Refund" tr ON tp.purchase_id = tr.purchase_id
-GROUP BY e.event_id, e.name, eh.event_happening_id, eh.event_time;
+LEFT JOIN "Ticket_Refund_Item" tri ON toi.order_item_id = tri.order_item_id
+LEFT JOIN "Ticket_Refund" tr ON tri.refund_id = tr.refund_id;
 
 }}}
@@ -468 +383 @@
 === `Future_Events`
 
-Овој поглед служи за динамично генерирање на репертоарот, прикажувајќи ги исклучиво претстојните настани во реално време преку филтрирање на изминатите термини.
-
-{{{
-
-CREATE VIEW "Future_Events" AS
+Овој поглед служи за динамично генерирање на репертоарот, прикажувајќи ги исклучиво претстојните настани преку филтрирање на изминатите термини во однос на моменталното време на системот. Дополнително, тој ја прикажува комплетната географска адреса и локација на објектот каде ќе се одржи настанот.
+
+{{{
+
+CREATE OR REPLACE VIEW "Future_Events" AS
 SELECT
     e.event_id,
@@ -480 +395 @@
     v.venue_id,
     v.name AS venue_name,
-    v.address_city AS city
+    v.address_street AS street,
+    v.address_city AS city,
+    v.address_country AS country
 FROM "Event" e
 JOIN "Event_Happening" eh ON e.event_id = eh.event_id
@@ -490 +407 @@
 === `Available_Tickets`
 
-Овој поглед овозможува моментален увид во инвентарот на достапни седишта и автоматско враќање на рефундираните билети во понуда.
-
-{{{
-
-CREATE VIEW "Available_Tickets" AS
+Овој поглед овозможува моментален увид во инвентарот на достапни слободни седишта за активните настани. Во него е имплементирана комплексна логика која ја калкулира крајната продажна цена во реално време, земајќи го предвид актуелниот процент на попуст за тековниот временски период во кој се наоѓа купувачот.
+
+{{{
+
+CREATE OR REPLACE VIEW "Available_Tickets" AS
 SELECT
     t.ticket_id,
-    t.ticket_type,
-    t.base_price,
+    ROUND(
+        t.base_price * (1 - COALESCE(ep.price_discount_percent, 0) / 100.0),
+        2
+    ) AS price,
     e.event_id,
     e.name AS event_name,
     eh.event_happening_id,
     eh.event_time,
+    v.name AS venue_name,
     s.name AS section_name,
+    st.row_number,
     st.seat_number
 FROM "Ticket" t
 JOIN "Event_Happening" eh ON t.event_happening_id = eh.event_happening_id
 JOIN "Event" e ON eh.event_id = e.event_id
+JOIN "Venue" v ON eh.venue_id = v.venue_id
 JOIN "Seat" st ON t.seat_id = st.seat_id
 JOIN "Section" s ON st.section_id = s.section_id
+LEFT JOIN "Event_Period" ep ON eh.event_happening_id = ep.event_happening_id
+                           AND CURRENT_DATE BETWEEN ep.start_date AND ep.end_date
 WHERE t.is_available = TRUE;
 
 }}}
+
+=== `Event_Overall_Ratings`
+
+Овој материјализиран поглед врши брза статистичка анализа на задоволството на публиката преку пресметување на просечната оцена за секој настан поединечно. Со оглед на тоа што користи агрегациски функции (COUNT и AVG), тој е зачуван како материјализиран поглед со цел да се избегне постојано пресметување и да се зачуваат перформансите на базата.
+
+{{{
+
+CREATE MATERIALIZED VIEW "Event_Overall_Ratings" AS
+SELECT
+    e.event_id,
+    e.name AS event_name,
+    eh.event_happening_id,
+    eh.event_time,
+    COUNT(ehr.rating_id) AS total_reviews,
+    ROUND(AVG(ehr.rating), 2) AS average_rating
+FROM "Event" e
+JOIN "Event_Happening" eh ON e.event_id = eh.event_id
+JOIN "Event_Happening_Rating" ehr ON eh.event_happening_id = ehr.event_happening_id
+GROUP BY e.event_id, e.name, eh.event_happening_id, eh.event_time;
+
+}}}
+
+=== `User_Order_History`
+
+Овој материјализиран поглед генерира финансиски профил и историја на нарачки за секој корисник, сумирајќи ги направените трошоци на ниво на главна нарачка. Тој нуди брз увид во точниот број на купени ставки, бројот на рефундирани билети како и вкупната сума на вратени пари по нарачка без оптоварување на трансакциските табели.
+
+{{{
+
+CREATE MATERIALIZED VIEW "User_Order_History" AS
+SELECT u.user_id,
+       u.username,
+       o.order_id,
+       o.order_time,
+       o.order_amount,
+       COALESCE(items.total_items_ordered, 0) AS total_items_ordered,
+       COALESCE(items.total_items_refunded, 0) AS total_items_refunded,
+       COALESCE(items.total_amount_refunded, 0.00) AS total_amount_refunded
+FROM "User" u
+JOIN "Regular_User" ru ON u.user_id = ru.user_id
+JOIN "Ticket_Order" o ON ru.user_id = o.user_id
+LEFT JOIN (
+    SELECT
+        toi.order_id,
+        COUNT(toi.order_item_id) AS total_items_ordered,
+        COUNT(tri.refund_item_id) AS total_items_refunded,
+        SUM(COALESCE(tri.item_price, 0.00)) AS total_amount_refunded
+    FROM "Ticket_Order_Item" toi
+    LEFT JOIN "Ticket_Refund_Item" tri ON toi.order_item_id = tri.order_item_id
+    GROUP BY toi.order_id
+) items ON o.order_id = items.order_id;
+
+}}}
+
+=== `Venue_Occupancy_Report`
+
+Овој комплексен аналитички поглед ја мери успешноста на продажбата преку споредба на бројот на продадени карти со максималниот капацитет на седишта во салата. Крајниот резултат дава прецизен процент на пополнетост за секој термин на настан, што е клучен бизнис индикатор за менаџерите и организаторите.
+
+{{{
+
+CREATE MATERIALIZED VIEW "Venue_Occupancy_Report" AS
+SELECT e.event_id,
+       e.name AS event_name,
+       eh.event_happening_id,
+       eh.event_time,
+       v.name AS venue_name,
+       v.number_of_seats AS total_venue_capacity,
+       COALESCE(stats.sold_count, 0) AS tickets_sold,
+       ROUND((COALESCE(stats.sold_count, 0)::NUMERIC / v.number_of_seats::NUMERIC) * 100, 2) AS occupancy_percentage
+FROM "Event" e
+JOIN "Event_Happening" eh ON e.event_id = eh.event_id
+JOIN "Venue" v ON eh.venue_id = v.venue_id
+LEFT JOIN (
+    SELECT t.event_happening_id, COUNT(toi.order_item_id) AS sold_count
+    FROM "Ticket_Order_Item" toi
+    JOIN "Ticket" t ON toi.ticket_id = t.ticket_id
+    GROUP BY t.event_happening_id
+) stats ON eh.event_happening_id = stats.event_happening_id
+GROUP BY e.event_id, e.name, eh.event_happening_id, eh.event_time, v.name, v.number_of_seats, stats.sold_count;
+
+}}}