QueryOptimization: views.sql

-------------------------------------------------------------------------------
--station_traffic_and_revenue Marija

CREATE VIEW station_traffic_and_revenue AS
SELECT
    s.station_id,
    s.station_name,
    s.city,
    COUNT(tk.ticket_id) AS passengers_departed,
    SUM(tk.price) AS revenue_generated,
    COUNT(DISTINCT tt.Traintrain_id) AS unique_trains_passed
FROM Station s
JOIN Ticket tk ON s.station_id = tk.Stationstation_id
JOIN "Train Trip" tt ON tk."Train Triptrip_id" = tt.trip_id
GROUP BY s.station_id, s.station_name, s.city;

select * from station_traffic_and_revenue where station_id=14;

DROP VIEW IF EXISTS station_traffic_and_revenue;

CREATE MATERIALIZED VIEW station_traffic_and_revenue AS
SELECT
    s.station_id,
    s.station_name,
    s.city,
    COUNT(tk.ticket_id) AS passengers_departed,
    SUM(tk.price) AS revenue_generated,
    COUNT(DISTINCT tt.Traintrain_id) AS unique_trains_passed
FROM Station s
JOIN Ticket tk ON s.station_id = tk.Stationstation_id
JOIN "Train Trip" tt ON tk."Train Triptrip_id" = tt.trip_id
GROUP BY s.station_id, s.station_name, s.city;

select * from station_traffic_and_revenue where station_id=14;

CREATE INDEX idx_station_traffic_sid ON station_traffic_and_revenue (station_id);

EXPLAIN (ANALYZE, COSTS OFF, TIMING ON, SUMMARY ON)
SELECT * FROM station_traffic_and_revenue WHERE station_id=14;

----------------------------------------------------------------------
--view_train_utilization Marija

CREATE VIEW view_train_utilization AS
SELECT
    t.train_id,
    t.train_number,
    COUNT(tt.trip_id) AS total_trips,
    AVG(tt.delay_minutes) AS avg_delay_minutes
FROM Train t
LEFT JOIN "Train Trip" tt ON t.train_id = tt.Traintrain_id
GROUP BY t.train_id, t.train_number;

SELECT * FROM view_train_utilization WHERE train_id = 91;

EXPLAIN (ANALYZE, COSTS OFF, TIMING ON, SUMMARY ON)
SELECT * FROM view_train_utilization WHERE train_id = 91;

----------------------------------------------------------------------
--view_payment_audit Marija

CREATE VIEW view_payment_audit AS
SELECT
    pm.payment_id,
    p.first_name || ' ' || p.last_name AS passenger_name,
    pm.amount AS total_paid,
    SUM(tk.price) AS total_ticket_value,
    (pm.amount - SUM(tk.price)) AS discrepancy,
    CASE
        WHEN pm.amount = SUM(tk.price) THEN 'Balanced'
        WHEN pm.amount > SUM(tk.price) THEN 'Overpaid'
        ELSE 'Underpaid'
    END AS audit_status
FROM Payment pm
JOIN Person p ON pm.PassengerPersonEMBG2 = p.EMBG
JOIN Ticket tk ON pm.payment_id = tk.Paymentpayment_id
GROUP BY pm.payment_id, p.first_name, p.last_name, pm.amount;

SELECT * FROM view_payment_audit WHERE payment_id = 212;

DROP VIEW IF EXISTS view_payment_audit;

CREATE MATERIALIZED VIEW view_payment_audit AS
SELECT
    pm.payment_id,
    p.first_name || ' ' || p.last_name AS passenger_name,
    pm.amount AS total_paid,
    SUM(tk.price) AS total_ticket_value,
    (pm.amount - SUM(tk.price)) AS discrepancy,
    CASE
        WHEN pm.amount = SUM(tk.price) THEN 'Balanced'
        WHEN pm.amount > SUM(tk.price) THEN 'Overpaid'
        ELSE 'Underpaid'
    END AS audit_status
FROM Payment pm
JOIN Person p ON pm.PassengerPersonEMBG2 = p.EMBG
JOIN Ticket tk ON pm.payment_id = tk.Paymentpayment_id
GROUP BY pm.payment_id, p.first_name, p.last_name, pm.amount;

SELECT * FROM view_payment_audit WHERE payment_id = 212;

CREATE INDEX idx_audit_payment_id ON view_payment_audit (payment_id);

EXPLAIN (ANALYZE, COSTS OFF, TIMING ON, SUMMARY ON)
SELECT * FROM view_payment_audit WHERE payment_id = 212;
----------------------------------------------------------------------
--view_route_passenger_summary Marija

CREATE VIEW view_route_passenger_summary AS
SELECT
    r.route_id,
    r.route_name,
    COUNT(tk.ticket_id) AS total_tickets_sold,
    SUM(tk.price) AS total_revenue
FROM Route r
LEFT JOIN "Train Trip" tt ON r.route_id = tt.Routeroute_id
LEFT JOIN Ticket tk ON tt.trip_id = tk."Train Triptrip_id"
GROUP BY r.route_id, r.route_name;

SELECT * FROM view_route_passenger_summary WHERE route_id = 517;

DROP VIEW IF EXISTS view_route_passenger_summary;

CREATE MATERIALIZED VIEW view_route_passenger_summary AS
SELECT
    r.route_id,
    r.route_name,
    COUNT(tk.ticket_id) AS total_tickets_sold,
    SUM(tk.price) AS total_revenue
FROM Route r
LEFT JOIN "Train Trip" tt ON r.route_id = tt.Routeroute_id
LEFT JOIN Ticket tk ON tt.trip_id = tk."Train Triptrip_id"
GROUP BY r.route_id, r.route_name;

SELECT * FROM view_route_passenger_summary WHERE route_id = 517;

CREATE INDEX idx_traintrip_route_id ON "Train Trip" (Routeroute_id);
CREATE INDEX idx_ticket_trip_id_1 ON Ticket ("Train Triptrip_id");

EXPLAIN (ANALYZE, COSTS OFF, TIMING ON, SUMMARY ON)
SELECT * FROM view_route_passenger_summary WHERE route_id = 517;
---------------------------------------------------------------------------
--------------functionalView1 Nina--------------

DROP VIEW IF EXISTS view_conductor_list CASCADE;

CREATE VIEW view_conductor_list AS
SELECT
    tt.trip_id,
    t.train_number,
    tk.ticket_id,
    tk.seat_number,
    tk.carriage_number,
    p.first_name || ' ' || p.last_name AS passenger,
    s1.station_name AS departure_station,
    s2.station_name AS arrival_station,
    tt.departure_time
FROM Ticket tk
JOIN "Train Trip" tt ON tk."Train Triptrip_id" = tt.trip_id
JOIN Train t ON tt.Traintrain_id = t.train_id
JOIN Payment pay ON tk.Paymentpayment_id = pay.payment_id
JOIN Passenger pass ON pay.Passengerpassenger_id = pass.passenger_id
     AND pay.PassengerPersonEMBG2 = pass.PersonEMBG
JOIN Person p ON pass.PersonEMBG = p.EMBG
JOIN Station s1 ON tk.Stationstation_id = s1.station_id
JOIN Station s2 ON tk.Stationstation_id2 = s2.station_id;

select * from view_conductor_list where trip_id=345895;

CREATE INDEX idx_ticket_trip_id ON Ticket ("Train Triptrip_id");

EXPLAIN (ANALYZE, COSTS OFF, TIMING ON, SUMMARY ON)
SELECT * FROM view_conductor_list WHERE trip_id = 345895;

SELECT * FROM view_conductor_list LIMIT 10;
----------------------------------------------------------------

----------functionalView2 Nina----------------

CREATE VIEW view_user_ticket_history AS
SELECT
    pass.passenger_id,
    p.first_name || ' ' || p.last_name AS passenger_name,
    tk.ticket_id,
    tt.departure_time,
    r.route_name,
    tk.ticket_status,
    CASE
        WHEN tt.departure_time >= CURRENT_DATE THEN 'Upcoming'
        ELSE 'Past'
    END AS travel_period
FROM Ticket tk
JOIN "Train Trip" tt ON tk."Train Triptrip_id" = tt.trip_id
JOIN Route r ON tt.Routeroute_id = r.route_id
JOIN Payment pay ON tk.Paymentpayment_id = pay.payment_id
JOIN Passenger pass ON pay.Passengerpassenger_id = pass.passenger_id
JOIN Person p ON pass.PersonEMBG = p.EMBG;


select * from view_user_ticket_history where passenger_id=689371;

CREATE INDEX idx_ticket_payment_id ON Ticket (Paymentpayment_id);
CREATE INDEX idx_payment_passenger_id ON Payment (Passengerpassenger_id);


EXPLAIN (ANALYZE, COSTS OFF, TIMING ON, SUMMARY ON)
select * from view_user_ticket_history where passenger_id=689371;

-------------------------------------------------------------------------
------Functional view 3 Nina----

CREATE VIEW view_station_departures AS
SELECT s.station_id, s.station_name,tt.trip_id,r.route_name, tt.departure_time, tt.trip_status, tt.delay_minutes
FROM "Train Trip" tt
JOIN Route r ON tt.Routeroute_id = r.route_id
JOIN Platform pl ON tt.PlatformStationstation_id = pl.Stationstation_id AND tt.Platformplatform_id = pl.platform_id
JOIN Station s ON pl.Stationstation_id = s.station_id
WHERE tt.departure_time >= CURRENT_DATE
ORDER BY tt.departure_time ASC;

select * from view_station_departures where trip_id=47138;

EXPLAIN (ANALYZE, COSTS OFF, TIMING ON, SUMMARY ON)
select * from view_user_ticket_history where passenger_id=689371;

---------------------------------------------------------------------------
------Functional view 4 Nina----------------

CREATE VIEW view_station_arrivals AS
SELECT s.station_id, s.station_name, tt.trip_id,r.route_name, tt.arrival_time, tt.trip_status, tt.delay_minutes
FROM "Train Trip" tt
JOIN Route r ON tt.Routeroute_id = r.route_id
JOIN Platform pl ON tt.PlatformStationstation_id = pl.Stationstation_id AND tt.Platformplatform_id = pl.platform_id
JOIN Station s ON pl.Stationstation_id = s.station_id
WHERE tt.arrival_time >= CURRENT_DATE
ORDER BY tt.arrival_time ASC;

select * from view_station_arrivals where trip_id=146474;

EXPLAIN (ANALYZE, COSTS OFF, TIMING ON, SUMMARY ON)
select * from view_station_arrivals where trip_id=146474;

-----------------------------------------------------------------------
--view_route_infrastructure_stats Nina

CREATE VIEW view_route_infrastructure_stats AS
SELECT
    r.route_name,
    COUNT(rs.Segmentsegment_id) AS total_segments,
    SUM(seg.length_km) AS total_route_length_km,
    MIN(seg.max_speed) AS bottleneck_speed,
    AVG(seg.max_speed) AS average_speed_limit
FROM Route r
JOIN Route_Segment rs ON r.route_id = rs.Routeroute_id
JOIN Segment seg ON rs.Segmentsegment_id = seg.segment_id
GROUP BY r.route_id, r.route_name;

SELECT
    route_name,
    total_segments,
    total_route_length_km
FROM view_route_infrastructure_stats
ORDER BY total_route_length_km DESC
LIMIT 10;

select * from view_route_infrastructure_stats where route_name='Venice - Bari';

---------------------------------------------------
--view_trip_performance Ana

CREATE VIEW view_trip_performance AS
SELECT
    tt.trip_id,
    r.route_name,
    t.train_number,
    p.first_name || ' ' || p.last_name AS driver_name,
    tt.departure_time,
    tt.arrival_time,
    tt.delay_minutes,
    CASE
        WHEN tt.delay_minutes = 0 THEN 'On Time'
        WHEN tt.delay_minutes <= 15 THEN 'Slight Delay'
        ELSE 'Significant Delay'
    END AS delay_status
FROM "Train Trip" tt
JOIN Route r ON tt.Routeroute_id = r.route_id
JOIN Train t ON tt.Traintrain_id = t.train_id
JOIN Employee e ON tt.EmployeePersonEMBG2 = e.PersonEMBG AND tt.Employeeemployee_id = e.employee_id
JOIN Person p ON e.PersonEMBG = p.EMBG;

SELECT trip_id,
       delay_minutes,
       delay_status
FROM view_trip_performance
WHERE delay_minutes > 0
LIMIT 10;

SELECT
    v.trip_id,
    v.route_name,
    v.delay_status,
    COUNT(tk.ticket_id) AS total_passengers
FROM view_trip_performance v
LEFT JOIN Ticket tk ON v.trip_id = tk."Train Triptrip_id"
GROUP BY v.trip_id, v.route_name, v.delay_status
ORDER BY total_passengers DESC
limit 10;

select * from view_trip_performance where trip_id=6789;
EXPLAIN (ANALYZE , COSTS OFF , TIMING On, SUMMARY  ON)
select * from view_trip_performance where trip_id=6789;

-----------------------------------------------------------------
--active_delays Ana

CREATE VIEW active_delays AS
SELECT
    tt.trip_id,
    r.route_name,
    t.train_number,
    tt.departure_time,
    tt.delay_minutes,
    s.station_name AS current_stop
FROM "Train Trip" tt
JOIN Route r ON tt.Routeroute_id = r.route_id
JOIN Train t ON tt.Traintrain_id = t.train_id
JOIN Station s ON tt.PlatformStationstation_id = s.station_id
WHERE tt.delay_minutes > 0 AND tt.trip_status != 'Completed';

select * from active_delays where trip_id=30275;
EXPLAIN (ANALYZE , COSTS OFF , TIMING On, SUMMARY  ON)
select * from active_delays where trip_id=30275;

----------------------------------------------------------
--train_maintenance_stats Ana

CREATE VIEW train_maintenance_stats AS
SELECT
    t.train_id,
    t.train_number,
    COUNT(DISTINCT tum.maintenancemaintenance_id) AS total_services,
    COUNT(DISTINCT epm.employeeemployee_id) AS total_technicians_involved
FROM Train t
LEFT JOIN train_undergoes_maintenance tum ON t.train_id = tum.traintrain_id
LEFT JOIN employee_performs_maintenance epm ON tum.maintenancemaintenance_id = epm.maintenancemaintenance_id
GROUP BY t.train_id, t.train_number;

select * from train_maintenance_stats where train_id=940;
CREATE INDEX ind_train_maintenance
ON employee_performs_maintenance(maintenancemaintenance_id);
EXPLAIN (ANALYZE , COSTS OFF , TIMING On, SUMMARY  ON)
select * from train_maintenance_stats where train_id=940;
-------------------------------------------------------------
--route_trip_stats Ana

CREATE VIEW route_trip_stats AS
SELECT
    r.route_id,
    r.route_name,
    COUNT(tt.trip_id) AS total_trips,
    AVG(tt.delay_minutes) AS avg_delay
FROM Route r
LEFT JOIN "Train Trip" tt ON tt.Routeroute_id = r.route_id
GROUP BY r.route_id, r.route_name;

select * from route_trip_stats where route_id=489;
EXPLAIN (ANALYZE , COSTS OFF , TIMING On, SUMMARY  ON)
select * from route_trip_stats where route_id=489;
----------------------------------------------------------------------
CREATE OR REPLACE VIEW vw_free_seats_per_trip AS
SELECT
    tt.trip_id,
    COALESCE(t.capacity, 0) AS total_capacity,
    COUNT(tk.ticket_id) AS sold_tickets, 
    (COALESCE(t.capacity, 0) - COUNT(tk.ticket_id)) AS free_seats
FROM "Train Trip" tt
JOIN Train t
    ON tt.Traintrain_id = t.train_id
LEFT JOIN Ticket tk
    ON tt.trip_id = tk."Train Triptrip_id"
GROUP BY tt.trip_id, t.capacity;

SELECT * FROM vw_free_seats_per_trip;

SELECT free_seats FROM vw_free_seats_per_trip WHERE trip_id = 155;


EXPLAIN (ANALYZE, COSTS OFF, TIMING ON, SUMMARY ON)
SELECT free_seats FROM vw_free_seats_per_trip WHERE trip_id = 155;

-----------------------------------------------------------
INDEX DROP 

drop index idx_station_traffic_sid;
drop index idx_traintrip_route_id;
drop index idx_ticket_trip_id_1;
drop index idx_audit_payment_id;