CinemaDB/Faza2B: cinemaDB-pogledi.sql

--VIEW 1 – vw_film_available_showtimes
--Го прикажува за секој филм во кои сали и кино се одржуваат проекции со слободни места.
-- Може да се користи доколку сакаме за конкретен филм да провериме во која сала и кино се прикажува.
CREATE VIEW vw_film_available_showtimes AS
SELECT
    m.movie_id,
    m.title                             AS film,
    m.duration                          AS trajanje_min,
    c.name                              AS kino,
    c.city                              AS grad,
    h.hall_id,
    h.name                              AS sala,
    s.showtime_id,
    s.start_time,
    s.end_time,
    s.base_price,
    h.capacity                          AS vkupni_mesta,
    COUNT(t.ticket_id)                  AS zafateni_mesta,
    h.capacity - COUNT(t.ticket_id)     AS slobodni_mesta
FROM Movie m
JOIN Showtime s   ON s.movie_id  = m.movie_id
JOIN Hall h       ON h.hall_id   = s.hall_id
JOIN Cinema c     ON c.cinema_id = h.cinema_id
LEFT JOIN Ticket t ON t.showtime_id = s.showtime_id
GROUP BY
    m.movie_id, m.title, m.duration,
    c.name, c.city,
    h.hall_id, h.name, h.capacity,
    s.showtime_id, s.start_time, s.end_time, s.base_price
HAVING h.capacity - COUNT(t.ticket_id) > 0
ORDER BY m.title, s.start_time;

--употреба доколку сакаме за конкретен филм да провериме
SELECT * FROM vw_film_available_showtimes WHERE film = '3 Idiots';


--VIEW 2 – vw_user_reservations
--Ги прикажува сите резервации на одреден корисник со детали за филмот, салата, седиштето и плаќањето(историјата на резервации).
-- Може да се користи доколку сакаме да ги видиме сите резервации на конкретен корисник по неговиот user_id или емаил
CREATE VIEW vw_user_reservations AS
SELECT
    u.user_id,
    u.first_name  || ' ' ||
    u.last_name  AS korisnik,
    u.email,
    r.reservation_id,
    r.reservation_date,
    r.status                             AS status_rezervacija,
    m.title                              AS film,
    c.name                               AS kino,
    h.name                               AS sala,
    s.start_time,
    s.end_time,
    se.seat_row                          AS red,
    se.seat_number                       AS broj_sediste,
    st.type                              AS tip_sediste,
    t.price                              AS cena_bilet,
    rp.amount                            AS plateno,
    rp.payment_method,
    rp.payment_date
FROM CinemaUser u
JOIN Reservation r       ON r.user_id       = u.user_id
JOIN Showtime s          ON s.showtime_id   = r.showtime_id
JOIN Movie m             ON m.movie_id      = s.movie_id
JOIN Hall h              ON h.hall_id       = s.hall_id
JOIN Cinema c            ON c.cinema_id     = h.cinema_id
LEFT JOIN Ticket t       ON t.reservation_id = r.reservation_id
LEFT JOIN Seat se        ON se.seat_id       = t.seat_id
LEFT JOIN Seat_Type st   ON st.seat_type_id  = se.seat_type_id
LEFT JOIN ReservationPayment rp ON rp.reservation_id = r.reservation_id
ORDER BY u.user_id, s.start_time;
--конкретна употреба на ова view според user_id
SELECT * FROM vw_user_reservations WHERE user_id = 42;
--или по email:
SELECT * FROM vw_user_reservations WHERE email = 'sonja_popova_99996@mail.com';


--VIEW 3 – vw_order_details
--Ги прикажува деталите на секоја нарачка, вклучувајќи купувач, касиер, производи, количини и цени.
-- Може да се користи доколку сакаме да ги видиме сите производи и вкупната цена за конкретна нарачка
CREATE VIEW vw_order_details AS
SELECT
    co.order_id,
    co.order_date,
    u.user_id,
    u.first_name || ' ' || u.last_name AS korisnik,
    u.email,
    u.phone,
    e.first_name || ' ' || e.last_name AS kasijer,
    p.product_id,
    p.name AS proizvod,
    op.quantity AS kolicina,
    op.price_at_order AS cena,
    (op.quantity * op.price_at_order) AS vkupna_cena_proizvod,
    co.total_price AS vkupna_cena_naracka
FROM CinemaOrder co
JOIN CinemaUser u ON u.user_id = co.user_id
JOIN Employee e ON e.employee_id = co.employee_id
JOIN Order_Product op ON op.order_id = co.order_id
JOIN Product p ON p.product_id = op.product_id
ORDER BY co.order_id;

--конкретна употреба според order_id
SELECT * FROM vw_order_details WHERE order_id = 432;


--VIEW 4 – vw_cinema_schedule_by_period
--Го прикажува распоредот на проекции во одредено кино за зададен временски период со жанрови.
--Може да се користи доколку сакаме да видиме кои филмови се прикажуваат во конкретно кино во одреден период
CREATE VIEW vw_cinema_schedule_by_period AS
SELECT
    c.cinema_id,
    c.name AS kino,
    c.city AS grad,
    s.start_time::DATE AS datum,
    TO_CHAR(s.start_time, 'Day') AS den_naziv,
    m.title AS film,
    m.duration AS traenje,
    h.name AS sala,
    s.start_time,
    s.end_time,
    s.base_price AS cena,
    STRING_AGG(DISTINCT g.name, ', ') AS zanrovi
FROM Cinema c
JOIN Hall h ON h.cinema_id = c.cinema_id
JOIN Showtime s ON s.hall_id = h.hall_id
JOIN Movie m ON m.movie_id = s.movie_id
LEFT JOIN Genre_Movie gm ON gm.movie_id = m.movie_id
LEFT JOIN Genre g ON g.genre_id = gm.genre_id
GROUP BY c.cinema_id, c.name, c.city, s.showtime_id, s.start_time, s.end_time,
         m.movie_id, m.title, m.duration, h.hall_id, h.name, s.base_price
ORDER BY c.cinema_id, s.start_time;



--VIEW 5 – vw_movie_reviews_summary
--Може да се користи доколку сакаме да ја видиме просечната,минималната и максималната  оцена и вкупниот број рецензии за конкретен филм.
--Исто така од кои жанрови е тој филм
CREATE MATERIALIZED VIEW vw_movie_reviews_summary AS
SELECT
    m.movie_id,
    m.title,
    m.release_year,
    m.language,
    ROUND(AVG(r.rating), 2)     AS avg_rating,
    COUNT(r.review_id)          AS total_reviews,
    MAX(r.rating)               AS max_rating,
    MIN(r.rating)               AS min_rating,
    STRING_AGG(DISTINCT g.name, ', ') AS genres
FROM Movie m
LEFT JOIN Review r      ON r.movie_id = m.movie_id
LEFT JOIN Genre_Movie gm ON gm.movie_id = m.movie_id
LEFT JOIN Genre g        ON g.genre_id = gm.genre_id
GROUP BY m.movie_id, m.title, m.release_year, m.language;
----конкретна употреба според movie_id или пак според име на филм
SELECT * FROM vw_movie_reviews_summary WHERE movie_id = 1;
SELECT * FROM vw_movie_reviews_summary WHERE title = 'Inception';


--VIEW 6 – vw_showtime_schedule
--Прикажува основни информации за секоја проекција — филм, кино, сала, време, цена и капацитет.
--Може да се користи доколку сакаме брзо да ги видиме деталите за конкретна проекција
CREATE VIEW vw_showtime_schedule AS
SELECT
    s.showtime_id,
    m.title AS movie_title,
    m.duration,
    c.name AS cinema_name,
    h.name AS hall_name,
    s.start_time,
    s.end_time,
    s.base_price,
    h.capacity AS total_seats
FROM Showtime s
JOIN Movie m ON m.movie_id = s.movie_id
JOIN Hall h ON h.hall_id = s.hall_id
JOIN Cinema c ON c.cinema_id = h.cinema_id;
--конкретна употреба со id на некоја проекција
SELECT * FROM vw_showtime_schedule WHERE showtime_id = 3;


--VIEW 7 – vw_popular_movies
--Ги рангира филмовите според вкупниот број на резервации со додадена просечна оцена од рецензиите.
-- Може да се користи доколку сакаме да видиме кои филмови се најпопуларни и најгледани
CREATE VIEW vw_popular_movies AS
SELECT
    m.movie_id,
    m.title,
    m.release_year,
    COUNT(r.reservation_id) AS total_reservations,
    mrs.avg_rating
FROM Movie m
JOIN Showtime s ON s.movie_id = m.movie_id
JOIN Reservation r ON r.showtime_id = s.showtime_id
LEFT JOIN vw_movie_reviews_summary mrs ON mrs.movie_id = m.movie_id
GROUP BY m.movie_id, m.title, m.release_year, mrs.avg_rating
ORDER BY total_reservations DESC;



--VIEW 8 – vw_presentation_rights
--Прикажува кои кина имаат право да прикажуваат одреден филм и во кој временски период.
-- Може да се користи доколку сакаме да провериме кои кина имаат дозвола за прикажување на конкретен филм
CREATE VIEW vw_presentation_rights AS
SELECT
    pr.presentationRights_id,
    m.title AS film,
    m.release_year,
    c.name AS kino,
    c.city AS grad,
    pr.start_date,
    pr.end_date
FROM PresentationRights pr
JOIN Movie m ON m.movie_id = pr.movie_id
JOIN Cinema c ON c.cinema_id = pr.cinema_id;
-- за конкретен филм кои кина имаат дозвола да го прикажуваат
SELECT * FROM vw_presentation_rights WHERE film = 'The Godfather';
-- за конкретно кино кои филмови има дозвола да ги прикажува
SELECT * FROM vw_presentation_rights WHERE kino = 'CinemaHouse 1';


--VIEW 9 – vw_monthly_revenue
--Ги прикажува месечните приходи по кино врз основа на извршените плаќања за резервации.
--Може да се користи доколку сакаме да ги видиме приходите за конкретно кино во одреден месец:
CREATE VIEW vw_monthly_revenue AS
SELECT
    DATE_TRUNC('month', rp.payment_date) AS month,
    c.name AS cinema_name,
    SUM(rp.amount) AS revenue,
    COUNT(rp.payment_id) AS num_payments
FROM ReservationPayment rp
JOIN Reservation r ON r.reservation_id = rp.reservation_id
JOIN Showtime s ON s.showtime_id = r.showtime_id
JOIN Hall h ON h.hall_id = s.hall_id
JOIN Cinema c ON c.cinema_id = h.cinema_id
GROUP BY DATE_TRUNC('month', rp.payment_date), c.name
ORDER BY month;