DatabaseProgramming: MaterializedViews.sql

-- Потрошувачка на гориво
CREATE MATERIALIZED VIEW MV_FUEL_CONSUMPTION AS
SELECT
    v.VEHICLE_ID,
    v.REGISTRATION_NUMBER,
    vt.TYPE_NAME                         AS VEHICLE_TYPE,
    v.STATUS                             AS VEHICLE_STATUS,
    COUNT(fl.FUEL_LOG_ID)                AS TOTAL_REFUELS,
    SUM(fl.FUEL_AMOUNT)                  AS TOTAL_FUEL_L,
    AVG(fl.FUEL_AMOUNT)                  AS AVG_FUEL_PER_REFUEL_L,
    SUM(fl.FUEL_COST)                    AS TOTAL_FUEL_COST,
    AVG(fl.FUEL_COST)                    AS AVG_FUEL_COST,
    MAX(fl.FUEL_DATE)                    AS LAST_REFUEL_DATE,
    SUM(CASE
        WHEN fl.FUEL_DATE >= CURRENT_DATE - INTERVAL '30 days'
        THEN fl.FUEL_AMOUNT ELSE 0
    END)                                 AS FUEL_L_LAST_30_DAYS,
    SUM(CASE
        WHEN fl.FUEL_DATE >= CURRENT_DATE - INTERVAL '30 days'
        THEN fl.FUEL_COST ELSE 0
    END)                                 AS COST_LAST_30_DAYS
FROM VEHICLE    v
LEFT JOIN FUEL_LOG      fl ON v.VEHICLE_ID      = fl.VEHICLE_ID
LEFT JOIN VEHICLE_TYPE  vt ON v.VEHICLE_TYPE_ID = vt.VEHICLE_TYPE_ID
GROUP BY v.VEHICLE_ID, v.REGISTRATION_NUMBER,
         vt.TYPE_NAME, v.STATUS;


-- Complaints по патник
CREATE MATERIALIZED VIEW MV_PASSENGER_COMPLAINTS AS
SELECT
    p.PASSENGER_ID,
    p.FIRST_NAME || ' ' || p.LAST_NAME AS PASSENGER_NAME,
    COUNT(c.COMPLAINT_ID) AS TOTAL_COMPLAINTS,
    MAX(c.COMPLAINT_DATE) AS LAST_COMPLAINT_DATE
FROM PASSENGER p
LEFT JOIN COMPLAINT c ON p.PASSENGER_ID = c.PASSENGER_ID
GROUP BY p.PASSENGER_ID, p.FIRST_NAME, p.LAST_NAME;


-- Финансиски извештај по линија
CREATE MATERIALIZED VIEW MV_REVENUE_BY_ROUTE AS
SELECT
    r.ROUTE_ID,
    r.ROUTE_NAME,
    a.AGENCY_NAME,
    COUNT(DISTINCT t.TRIP_ID)            AS TOTAL_TRIPS,
    COUNT(tk.TICKET_ID)                  AS TICKETS_SOLD,
    SUM(tk.PRICE)                        AS GROSS_REVENUE,
    SUM(CASE WHEN pay.PAYMENT_STATUS = 'COMPLETED'
             THEN pay.AMOUNT ELSE 0 END) AS COLLECTED_REVENUE,
    AVG(tk.PRICE)                        AS AVG_TICKET_PRICE
FROM ROUTE      r
JOIN AGENCY     a  ON r.AGENCY_ID  = a.AGENCY_ID
JOIN TRIP       t  ON r.ROUTE_ID   = t.ROUTE_ID
JOIN TICKET     tk ON t.TRIP_ID    = tk.TRIP_ID
LEFT JOIN PAYMENT pay ON tk.TICKET_ID = pay.TICKET_ID
WHERE tk.STATUS NOT IN ('CANCELLED', 'REFUNDED')
GROUP BY r.ROUTE_ID, r.ROUTE_NAME, a.AGENCY_NAME;


-- Доцнења по trip
CREATE MATERIALIZED VIEW MV_TRIP_DELAYS AS
SELECT
    t.TRIP_ID,
    t.TRIP_DATE,
    r.ROUTE_NAME,
    v.REGISTRATION_NUMBER,
    e.FIRST_NAME || ' ' || e.LAST_NAME  AS DRIVER_NAME,
    COUNT(dl.DELAY_ID)                  AS TOTAL_DELAY_RECORDS,
    SUM(dl.DELAY_MINUTES)               AS TOTAL_DELAY_MINUTES,
    AVG(dl.DELAY_MINUTES)               AS AVG_DELAY_MINUTES,
    MAX(dl.DELAY_MINUTES)               AS MAX_DELAY_MINUTES,
    STRING_AGG(DISTINCT dl.DELAY_REASON, '; ') AS DELAY_REASONS
FROM TRIP       t
JOIN ROUTE      r  ON t.ROUTE_ID    = r.ROUTE_ID
JOIN VEHICLE    v  ON t.VEHICLE_ID  = v.VEHICLE_ID
JOIN DRIVER     dr ON t.DRIVER_ID   = dr.DRIVER_ID
JOIN EMPLOYEE   e  ON dr.EMPLOYEE_ID = e.EMPLOYEE_ID
JOIN DELAY_LOG  dl ON t.TRIP_ID     = dl.TRIP_ID
GROUP BY t.TRIP_ID, t.TRIP_DATE, r.ROUTE_NAME,
         v.REGISTRATION_NUMBER, e.FIRST_NAME, e.LAST_NAME;


-- Патници по возило
CREATE MATERIALIZED VIEW MV_VEHICLE_OCCUPANCY_STATS AS
SELECT
    v.VEHICLE_ID,
    v.REGISTRATION_NUMBER,
    AVG(cl.PASSENGER_COUNT) AS AVG_PASSENGERS,
    MAX(cl.PASSENGER_COUNT) AS MAX_PASSENGERS,
    COUNT(cl.CAPACITY_LOG_ID) AS RECORDS
FROM VEHICLE v
JOIN CAPACITY_LOG cl ON v.VEHICLE_ID = cl.VEHICLE_ID
GROUP BY v.VEHICLE_ID, v.REGISTRATION_NUMBER;