оптимизација: Optimizirinani views.sql

CREATE INDEX IF NOT EXISTS idx_ticket_screening ON TICKET(screening_id);
CREATE INDEX IF NOT EXISTS idx_ticket_user ON TICKET(user_id);
CREATE INDEX IF NOT EXISTS idx_ticket_seat ON TICKET(seat_id);
CREATE INDEX IF NOT EXISTS idx_screening_movie ON SCREENING(movie_id);
CREATE INDEX IF NOT EXISTS idx_screening_hall ON SCREENING(cinemahall_id);
CREATE INDEX IF NOT EXISTS idx_screening_date ON SCREENING("date");
CREATE INDEX IF NOT EXISTS idx_screening_time ON SCREENING("time");
CREATE INDEX IF NOT EXISTS idx_review_movie ON REVIEW(movie_id);
CREATE INDEX IF NOT EXISTS idx_reservation_status ON RESERVATION(status);
CREATE INDEX IF NOT EXISTS idx_purchased_product_ticket ON PURCHASED_PRODUCT(ticket_id);
CREATE INDEX IF NOT EXISTS idx_purchased_product_product ON PURCHASED_PRODUCT(product_id);
CREATE INDEX IF NOT EXISTS idx_movie_genre_movie ON MOVIE_GENRE(movie_id);
CREATE INDEX IF NOT EXISTS idx_movie_genre_genre ON MOVIE_GENRE(genre_id);

DROP MATERIALIZED VIEW IF EXISTS mat_movie_revenue CASCADE;
DROP MATERIALIZED VIEW IF EXISTS mat_most_watched_genre CASCADE;
DROP MATERIALIZED VIEW IF EXISTS mat_busiest_months CASCADE;
DROP MATERIALIZED VIEW IF EXISTS mat_cinema_revenue CASCADE;
DROP MATERIALIZED VIEW IF EXISTS mat_hall_occupancy CASCADE;
DROP MATERIALIZED VIEW IF EXISTS mat_top_products CASCADE;
DROP MATERIALIZED VIEW IF EXISTS mat_promotion_impact CASCADE;
DROP MATERIALIZED VIEW IF EXISTS mat_reservation_status CASCADE;
DROP MATERIALIZED VIEW IF EXISTS mat_popular_time_slots CASCADE;
DROP MATERIALIZED VIEW IF EXISTS mat_product_vs_ticket_revenue CASCADE;
DROP MATERIALIZED VIEW IF EXISTS mat_morning_vs_evening CASCADE;
DROP MATERIALIZED VIEW IF EXISTS mat_movie_review_stats CASCADE;
DROP MATERIALIZED VIEW IF EXISTS mat_genre_review_stats CASCADE;

-- 1. Priod po film
CREATE MATERIALIZED VIEW mat_movie_revenue AS
SELECT m.title,
       COUNT(t.ticket_id)              AS total_tickets,
       SUM(t.price)                    AS total_revenue,
       ROUND(AVG(t.price)::numeric, 2) AS avg_ticket_price
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. Najgledan zanr (bez DISTINCT i bez LEFT JOIN)
CREATE MATERIALIZED VIEW mat_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. Najaktivni periodi po mesec
CREATE MATERIALIZED VIEW mat_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. Priod po kino
CREATE MATERIALIZED VIEW mat_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,
       ROUND(AVG(t.price)::numeric, 2) AS avg_ticket_price
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. Popolnetost na sali (bez COUNT DISTINCT)
CREATE MATERIALIZED VIEW mat_hall_occupancy AS
SELECT ch.description               AS hall,
       c.name                       AS cinema,
       ch.capacity,
       COUNT(t.ticket_id)           AS total_tickets_sold
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 total_tickets_sold DESC;

-- 6. Najprodavani proizvodi
CREATE MATERIALIZED VIEW mat_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. Promocii vs priod
CREATE MATERIALIZED VIEW mat_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. Rezervacii po status
CREATE MATERIALIZED VIEW mat_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. Popularni termini
CREATE MATERIALIZED VIEW mat_popular_time_slots AS
SELECT s.time                           AS time_slot,
       CASE
           WHEN s.time < '13:00:00' THEN 'Morning'
           WHEN s.time < '18:00:00' THEN 'Afternoon'
           ELSE 'Evening'
       END                              AS period,
       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;

-- 10. Proizvodi vs tiketi priod
CREATE MATERIALIZED VIEW mat_product_vs_ticket_revenue AS
SELECT 'Tickets' AS revenue_source, SUM(t.price) AS total_revenue
FROM TICKET t
UNION ALL
SELECT 'Products', SUM(pp.numbers * p.price)
FROM PURCHASED_PRODUCT pp
JOIN PRODUCT p ON p.product_id = pp.product_id;

-- 11. Utro vs Vecer priod
CREATE MATERIALIZED VIEW mat_morning_vs_evening AS
SELECT
    CASE
        WHEN s.time < '13:00:00' THEN 'Morning'
        WHEN s.time < '18:00:00' THEN 'Afternoon'
        ELSE 'Evening'
    END                              AS period,
    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 period
ORDER BY total_revenue DESC;

-- 12. Review statistika po film
CREATE MATERIALIZED VIEW mat_movie_review_stats AS
SELECT m.title,
       COUNT(r.review_id)              AS total_reviews,
       ROUND(AVG(r.rating)::numeric,2) AS avg_rating,
       MAX(r.rating)                   AS max_rating,
       MIN(r.rating)                   AS min_rating
FROM MOVIE m
JOIN REVIEW r ON r.movie_id = m.movie_id
GROUP BY m.title
ORDER BY avg_rating DESC;

-- 13. Review statistika po zanr
CREATE MATERIALIZED VIEW mat_genre_review_stats AS
SELECT g.name                          AS genre,
       COUNT(r.review_id)              AS total_reviews,
       ROUND(AVG(r.rating)::numeric,2) AS avg_rating
FROM GENRE g
JOIN MOVIE_GENRE mg ON mg.genre_id = g.genre_id
JOIN REVIEW      r  ON r.movie_id  = mg.movie_id
GROUP BY g.name
ORDER BY avg_rating DESC;

--------------------------------

--------------------------------
DO $$
DECLARE
    start_time TIMESTAMPTZ;
    end_time TIMESTAMPTZ;
BEGIN
    -- 1
    start_time := clock_timestamp();
    PERFORM * FROM mat_movie_revenue;
    RAISE NOTICE 'mat_movie_revenue: % ms', EXTRACT(MILLISECONDS FROM clock_timestamp()-start_time);

    -- 2
    start_time := clock_timestamp();
    PERFORM * FROM mat_most_watched_genre;
    RAISE NOTICE 'mat_most_watched_genre: % ms', EXTRACT(MILLISECONDS FROM clock_timestamp()-start_time);

    -- 3
    start_time := clock_timestamp();
    PERFORM * FROM mat_busiest_months;
    RAISE NOTICE 'mat_busiest_months: % ms', EXTRACT(MILLISECONDS FROM clock_timestamp()-start_time);

    -- 4
    start_time := clock_timestamp();
    PERFORM * FROM mat_cinema_revenue;
    RAISE NOTICE 'mat_cinema_revenue: % ms', EXTRACT(MILLISECONDS FROM clock_timestamp()-start_time);

    -- 5
    start_time := clock_timestamp();
    PERFORM * FROM mat_hall_occupancy;
    RAISE NOTICE 'mat_hall_occupancy: % ms', EXTRACT(MILLISECONDS FROM clock_timestamp()-start_time);

    -- 6
    start_time := clock_timestamp();
    PERFORM * FROM mat_top_products;
    RAISE NOTICE 'mat_top_products: % ms', EXTRACT(MILLISECONDS FROM clock_timestamp()-start_time);

    -- 7
    start_time := clock_timestamp();
    PERFORM * FROM mat_promotion_impact;
    RAISE NOTICE 'mat_promotion_impact: % ms', EXTRACT(MILLISECONDS FROM clock_timestamp()-start_time);

    -- 8
    start_time := clock_timestamp();
    PERFORM * FROM mat_reservation_status;
    RAISE NOTICE 'mat_reservation_status: % ms', EXTRACT(MILLISECONDS FROM clock_timestamp()-start_time);

    -- 9
    start_time := clock_timestamp();
    PERFORM * FROM mat_popular_time_slots;
    RAISE NOTICE 'mat_popular_time_slots: % ms', EXTRACT(MILLISECONDS FROM clock_timestamp()-start_time);

    -- 10
    start_time := clock_timestamp();
    PERFORM * FROM mat_product_vs_ticket_revenue;
    RAISE NOTICE 'mat_product_vs_ticket_revenue: % ms', EXTRACT(MILLISECONDS FROM clock_timestamp()-start_time);

    -- 11
    start_time := clock_timestamp();
    PERFORM * FROM mat_morning_vs_evening;
    RAISE NOTICE 'mat_morning_vs_evening: % ms', EXTRACT(MILLISECONDS FROM clock_timestamp()-start_time);

    -- 12
    start_time := clock_timestamp();
    PERFORM * FROM mat_movie_review_stats;
    RAISE NOTICE 'mat_movie_review_stats: % ms', EXTRACT(MILLISECONDS FROM clock_timestamp()-start_time);

    -- 13
    start_time := clock_timestamp();
    PERFORM * FROM mat_genre_review_stats;
    RAISE NOTICE 'mat_genre_review_stats: % ms', EXTRACT(MILLISECONDS FROM clock_timestamp()-start_time);
END $$;