DatabaseCreation: views.sql

-- активни возења
CREATE VIEW VW_ACTIVE_TRIPS AS
SELECT
    t.TRIP_ID,
    t.TRIP_DATE,
    t.START_TIME,
    t.END_TIME,
    t.STATUS,
    t.TRIP_HEADSIGN,
    r.ROUTE_ID,
    r.ROUTE_NAME,
    v.VEHICLE_ID,
    v.REGISTRATION_NUMBER,
    v.MODEL                         AS VEHICLE_MODEL,
    vt.TYPE_NAME                    AS VEHICLE_TYPE,
    e.FIRST_NAME || ' ' || e.LAST_NAME AS DRIVER_NAME,
    dr.LICENSE_NUMBER
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
LEFT JOIN VEHICLE_TYPE vt ON t.VEHICLE_TYPE_ID = vt.VEHICLE_TYPE_ID
WHERE t.STATUS IN ('SCHEDULED', 'IN_PROGRESS');

-- распоред на постојќи по линија
CREATE VIEW VW_ROUTE_TIMETABLE AS
SELECT
    r.ROUTE_ID,
    r.ROUTE_NAME,
    t.TRIP_ID,
    t.TRIP_DATE,
    t.DIRECTION_ID,
    s.STOP_ID,
    s.STOP_NAME,
    s.LATITUDE,
    s.LONGITUDE,
    st.STOP_SEQUENCE,
    st.ARRIVAL_TIME,
    st.DEPARTURE_TIME,
    z.ZONE_NAME
FROM STOP_TIME  st
JOIN TRIP       t  ON st.TRIP_ID  = t.TRIP_ID
JOIN ROUTE      r  ON t.ROUTE_ID  = r.ROUTE_ID
JOIN STOPS      s  ON st.STOP_ID  = s.STOP_ID
LEFT JOIN ZONE  z  ON s.ZONE_ID   = z.ZONE_ID
ORDER BY r.ROUTE_ID, t.TRIP_ID, st.STOP_SEQUENCE;

-- доцнења по trip
CREATE VIEW VW_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 VIEW VW_VEHICLE_CURRENT_LOCATION AS
SELECT DISTINCT ON (vl.VEHICLE_ID)
    vl.VEHICLE_ID,
    v.REGISTRATION_NUMBER,
    vt.TYPE_NAME                        AS VEHICLE_TYPE,
    v.STATUS                            AS VEHICLE_STATUS,
    vl.TRIP_ID,
    r.ROUTE_NAME,
    vl.LATITUDE,
    vl.LONGITUDE,
    vl.SPEED_KMH,
    vl.OCCUPANCY_STATUS,
    vl.CONGESTION_LEVEL,
    cs.STOP_NAME                        AS CURRENT_STOP,
    ns.STOP_NAME                        AS NEXT_STOP,
    vl.DISTANCE_TO_NEXT_STOP_M,
    vl.RECORDED_AT
FROM VEHICLE_LOG    vl
JOIN VEHICLE        v   ON vl.VEHICLE_ID = v.VEHICLE_ID
JOIN TRIP           t   ON vl.TRIP_ID    = t.TRIP_ID
JOIN ROUTE          r   ON t.ROUTE_ID    = r.ROUTE_ID
LEFT JOIN VEHICLE_TYPE vt ON v.VEHICLE_TYPE_ID  = vt.VEHICLE_TYPE_ID
LEFT JOIN STOPS     cs  ON vl.CURRENT_STOP_ID   = cs.STOP_ID
LEFT JOIN STOPS     ns  ON vl.NEXT_STOP_ID      = ns.STOP_ID
ORDER BY vl.VEHICLE_ID, vl.RECORDED_AT DESC;

-- детален тикет 
CREATE VIEW VW_TICKET_DETAILS AS
SELECT
    t.TICKET_ID,
    p.FIRST_NAME || ' ' || p.LAST_NAME AS PASSENGER_NAME,
    tt.NAME AS TICKET_TYPE,
    t.PRICE,
    d.DISCOUNT_NAME,
    d.DISCOUNT_PERCENTAGE,
    r.ROUTE_NAME,
    s1.STOP_NAME AS FROM_STOP,
    s2.STOP_NAME AS TO_STOP,
    t.PURCHASE_DATE,
    t.STATUS
FROM TICKET t
JOIN PASSENGER p ON t.PASSENGER_ID = p.PASSENGER_ID
JOIN TICKET_TYPE tt ON t.TICKET_TYPE_ID = tt.TICKET_TYPE_ID
LEFT JOIN DISCOUNT d ON t.DISCOUNT_ID = d.DISCOUNT_ID
LEFT JOIN TRIP tr ON t.TRIP_ID = tr.TRIP_ID
LEFT JOIN ROUTE r ON tr.ROUTE_ID = r.ROUTE_ID
LEFT JOIN STOPS s1 ON t.START_STOP_ID = s1.STOP_ID AND t.START_STOP_VT = s1.VEHICLE_TYPE_ID
LEFT JOIN STOPS s2 ON t.END_STOP_ID   = s2.STOP_ID AND t.END_STOP_VT   = s2.VEHICLE_TYPE_ID;

-- распоред на возачи и кондуктори 
CREATE VIEW VW_STAFF_SCHEDULE AS
SELECT
    'DRIVER'                            AS ROLE,
    ds.SCHEDULE_DATE,
    e.FIRST_NAME || ' ' || e.LAST_NAME  AS STAFF_NAME,
    e.PHONE,
    sh.SHIFT_NAME,
    sh.START_TIME                       AS SHIFT_FROM,
    sh.END_TIME                         AS SHIFT_TO,
    t.TRIP_ID,
    r.ROUTE_NAME,
    v.REGISTRATION_NUMBER
FROM DRIVER_SCHEDULE ds
JOIN DRIVER      dr ON ds.DRIVER_ID  = dr.DRIVER_ID
JOIN EMPLOYEE    e  ON dr.EMPLOYEE_ID = e.EMPLOYEE_ID
JOIN SHIFT       sh ON ds.SHIFT_ID   = sh.SHIFT_ID
JOIN TRIP        t  ON ds.TRIP_ID    = t.TRIP_ID
JOIN ROUTE       r  ON t.ROUTE_ID    = r.ROUTE_ID
JOIN VEHICLE     v  ON t.VEHICLE_ID  = v.VEHICLE_ID

UNION ALL

SELECT
    'CONDUCTOR'                         AS ROLE,
    cs.SCHEDULE_DATE,
    e.FIRST_NAME || ' ' || e.LAST_NAME  AS STAFF_NAME,
    e.PHONE,
    sh.SHIFT_NAME,
    sh.START_TIME                       AS SHIFT_FROM,
    sh.END_TIME                         AS SHIFT_TO,
    t.TRIP_ID,
    r.ROUTE_NAME,
    v.REGISTRATION_NUMBER
FROM CONDUCTOR_SCHEDULE cs
JOIN CONDUCTOR   c  ON cs.CONDUCTOR_ID = c.CONDUCTOR_ID
JOIN EMPLOYEE    e  ON c.EMPLOYEE_ID   = e.EMPLOYEE_ID
JOIN SHIFT       sh ON cs.SHIFT_ID     = sh.SHIFT_ID
JOIN TRIP        t  ON cs.TRIP_ID      = t.TRIP_ID
JOIN ROUTE       r  ON t.ROUTE_ID      = r.ROUTE_ID
JOIN VEHICLE     v  ON t.VEHICLE_ID    = v.VEHICLE_ID;

-- билети
CREATE VIEW VW_TICKETS_BASIC AS
SELECT
    t.TICKET_ID,
    t.PASSENGER_ID,
    t.TRIP_ID,
    tt.NAME AS TICKET_TYPE,
    t.PRICE,
    t.STATUS,
    t.PURCHASE_DATE
FROM TICKET t
JOIN TICKET_TYPE tt ON t.TICKET_TYPE_ID = tt.TICKET_TYPE_ID;

-- финансиски извештај по линија 
CREATE VIEW VW_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;

-- complaints по патник
CREATE VIEW VW_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 VIEW VW_PASSENGER_NOTIFICATIONS AS
SELECT
    p.PASSENGER_ID,
    p.FIRST_NAME || ' ' || p.LAST_NAME AS PASSENGER_NAME,
    n.NOTIFICATION_TYPE,
    n.MESSAGE,
    n.STATUS,
    n.SENT_TIME
FROM NOTIFICATION n
JOIN PASSENGER p ON n.PASSENGER_ID = p.PASSENGER_ID;

-- патници по возилo
CREATE VIEW VW_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;

-- Активни subscription pass картички по патник
CREATE VIEW VW_PASSENGER_ACTIVE_PASSES AS
SELECT
    sp.PASS_ID,
    sp.PASSENGER_ID,
    p.FIRST_NAME || ' ' || p.LAST_NAME AS PASSENGER_NAME,
    sp.PASS_TYPE,
    z.ZONE_NAME,
    sp.START_DATE,
    sp.END_DATE,
    (sp.END_DATE - CURRENT_DATE) AS DAYS_REMAINING,
    sp.STATUS,
    sp.PRICE
FROM SUBSCRIPTION_PASS sp
JOIN PASSENGER p ON sp.PASSENGER_ID = p.PASSENGER_ID
LEFT JOIN ZONE z ON sp.ZONE_ID = z.ZONE_ID
WHERE sp.STATUS = 'ACTIVE'
  AND sp.END_DATE >= CURRENT_DATE;

-- Следни возења по постојка (за Next Departure и Journey Planner)
CREATE VIEW VW_UPCOMING_TRIPS_BY_STOP AS
SELECT
    s.STOP_ID,
    s.STOP_NAME,
    s.LATITUDE,
    s.LONGITUDE,
    z.ZONE_NAME,
    r.ROUTE_ID,
    r.ROUTE_NAME,
    t.TRIP_ID,
    t.TRIP_DATE,
    t.TRIP_HEADSIGN,
    t.DIRECTION_ID,
    st.STOP_SEQUENCE,
    st.ARRIVAL_TIME,
    st.DEPARTURE_TIME,
    vt.TYPE_NAME AS VEHICLE_TYPE,
    v.REGISTRATION_NUMBER,
    t.STATUS AS TRIP_STATUS,
    CASE
        WHEN t.TRIP_DATE = CURRENT_DATE
        THEN EXTRACT(EPOCH FROM (st.DEPARTURE_TIME - LOCALTIME)) / 60
        ELSE NULL
    END AS MINUTES_UNTIL_DEPARTURE
FROM STOP_TIME st
JOIN STOPS s ON st.STOP_ID = s.STOP_ID
JOIN TRIP t ON st.TRIP_ID = t.TRIP_ID
JOIN ROUTE r ON t.ROUTE_ID = r.ROUTE_ID
JOIN VEHICLE v ON t.VEHICLE_ID = v.VEHICLE_ID
LEFT JOIN ZONE z ON s.ZONE_ID = z.ZONE_ID
LEFT JOIN VEHICLE_TYPE vt ON t.VEHICLE_TYPE_ID = vt.VEHICLE_TYPE_ID
WHERE t.STATUS IN ('SCHEDULED', 'IN_PROGRESS')
  AND t.TRIP_DATE = CURRENT_DATE
  AND st.DEPARTURE_TIME >= LOCALTIME
ORDER BY s.STOP_ID, st.DEPARTURE_TIME;

-- Потрошувачка на гориво по возило
CREATE VIEW VW_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;