DatabaseCreation: Views.sql.txt

-- 1. Приход по филм
CREATE VIEW view_movie_revenue AS
SELECT m.title,
       COUNT(t.ticket_id) AS total_tickets,
       SUM(t.price) AS total_revenue
FROM MOVIE m
JOIN SCREENING s ON s.movie_id = m.movie_id
JOIN TICKET t ON t.screening_id = s.screening_id
GROUP BY m.title
ORDER BY total_revenue DESC;

-- 2. Најгледан жанр
CREATE VIEW view_most_watched_genre AS
SELECT g.name AS genre,
       COUNT(t.ticket_id) AS total_tickets,
       SUM(t.price) AS total_revenue
FROM GENRE g
JOIN MOVIE_GENRE mg ON mg.genre_id = g.genre_id
JOIN SCREENING s ON s.movie_id = mg.movie_id
JOIN TICKET t ON t.screening_id = s.screening_id
GROUP BY g.name
ORDER BY total_tickets DESC;

-- 3. Најактивни периоди по месец
CREATE VIEW view_busiest_months AS
SELECT EXTRACT(YEAR FROM s.date)::int AS year,
       EXTRACT(MONTH FROM s.date)::int AS month,
       COUNT(t.ticket_id) AS total_tickets,
       SUM(t.price) AS total_revenue
FROM SCREENING s
JOIN TICKET t ON t.screening_id = s.screening_id
GROUP BY year, month
ORDER BY year, month;

-- 4. Приход по кино
CREATE VIEW view_cinema_revenue AS
SELECT c.name AS cinema,
       ci.name AS city,
       COUNT(t.ticket_id) AS total_tickets,
       SUM(t.price) AS total_revenue
FROM CINEMA c
JOIN CITY ci ON ci.city_id = c.city_id
JOIN CINEMA_HALL ch ON ch.cinema_id = c.cinema_id
JOIN SCREENING s ON s.cinemahall_id = ch.cinemahall_id
JOIN TICKET t ON t.screening_id = s.screening_id
GROUP BY c.name, ci.name
ORDER BY total_revenue DESC;

-- 5. Попуненост на сали
CREATE VIEW view_hall_occupancy AS
SELECT ch.description AS hall,
       c.name AS cinema,
       ch.capacity,
       COUNT(t.ticket_id) AS total_tickets_sold,
       ROUND((COUNT(t.ticket_id)::numeric / (ch.capacity * COUNT(DISTINCT s.screening_id)) * 100), 2) AS occupancy_percent
FROM CINEMA_HALL ch
JOIN CINEMA c ON c.cinema_id = ch.cinema_id
JOIN SCREENING s ON s.cinemahall_id = ch.cinemahall_id
JOIN TICKET t ON t.screening_id = s.screening_id
GROUP BY ch.description, c.name, ch.capacity
ORDER BY occupancy_percent DESC;

-- 6. Најпродавани производи
CREATE VIEW view_top_products AS
SELECT p.name AS product,
       p.unit,
       p.price AS unit_price,
       SUM(pp.numbers) AS total_quantity_sold,
       SUM(pp.numbers * p.price) AS total_revenue
FROM PRODUCT p
JOIN PURCHASED_PRODUCT pp ON pp.product_id = p.product_id
GROUP BY p.name, p.unit, p.price
ORDER BY total_quantity_sold DESC;

-- 7. Промоции vs приход
CREATE VIEW view_promotion_impact AS
SELECT pr.name AS promotion,
       pr.discount AS discount_percent,
       COUNT(t.ticket_id) AS total_tickets,
       SUM(t.price) AS revenue_with_promotion,
       ROUND(AVG(t.price)::numeric, 2) AS avg_ticket_price
FROM PROMOTION pr
JOIN SCREENING s ON s.screening_id = pr.screening_id
JOIN TICKET t ON t.screening_id = s.screening_id
GROUP BY pr.name, pr.discount
ORDER BY total_tickets DESC;

-- 8. Резервации по статус
CREATE VIEW view_reservation_status AS
SELECT status,
       COUNT(*) AS total_reservations,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM RESERVATION
GROUP BY status
ORDER BY total_reservations DESC;

-- 9. Популарни термини
CREATE VIEW view_popular_time_slots AS
SELECT s.time AS time_slot,
       COUNT(t.ticket_id) AS total_tickets,
       SUM(t.price) AS total_revenue,
       ROUND(AVG(t.price)::numeric, 2) AS avg_ticket_price
FROM SCREENING s
JOIN TICKET t ON t.screening_id = s.screening_id
GROUP BY s.time
ORDER BY total_tickets DESC;