DatabaseCreation: views.sql

-- 1. За даден возач: автобус, линија и распоред
CREATE OR REPLACE VIEW driver_shift_info AS
SELECT
    au.name,
    au.surname,
    d.verification_code,
    bi.chassis_number,
    bi.registration_number,
    la.start_time,
    la.end_time,
    l.line_number
FROM ApplicationUser au
JOIN Driver d
    ON au.user_id = d.user_id
JOIN Line_assignment la
    ON d.user_id = la.driver_id
JOIN Bus_instance bi
    ON la.chassis_number = bi.chassis_number
JOIN Schedule s
    ON la.schedule_id = s.schedule_id
JOIN Line l
    ON s.line_id = l.line_id;


-- 2. Активни линии со почетна и крајна станица
CREATE OR REPLACE VIEW line_info AS
SELECT
    l.line_number,
    s_start.station_name AS start_station,
    s_start.address AS start_address,
    s_end.station_name AS end_station,
    s_end.address AS end_address
FROM Line l
JOIN Station s_start
    ON l.start_station_id = s_start.station_id
JOIN Station s_end
    ON l.end_station_id = s_end.station_id
WHERE l.is_active = true;


-- 3. Плаќања по статус и тип
CREATE OR REPLACE VIEW payments_info AS
SELECT
    p.payment_id,
    p.status AS payment_status,
    p.payment_date,
    pt.type AS payment_type
FROM Payment p
JOIN Payment_type pt
    ON p.type_id = pt.type_id;


-- 4. Станици за дадена линија, со редослед на станиците
CREATE OR REPLACE VIEW line_stations AS
SELECT
    l.line_number,
    p.num_of_station,
    s.station_id,
    s.station_name,
    s.address
FROM Line l
JOIN Position p
    ON l.line_id = p.line_id
JOIN Station s
    ON p.station_id = s.station_id
ORDER BY l.line_number, p.num_of_station;


-- 5. Распоред на линии за дадена станица
CREATE OR REPLACE VIEW station_info AS
SELECT
    s.station_name,
    s.address,
    l.line_number,
    sc.arrival_time
FROM Station s
JOIN Position p
    ON s.station_id = p.station_id
JOIN Line l
    ON p.line_id = l.line_id
JOIN Schedule sc
    ON l.line_id = sc.line_id
WHERE l.is_active = true;


-- 6. Преглед на работа на админ
CREATE OR REPLACE VIEW admin_info AS
SELECT
    u.name,
    u.surname,
    u.email,
    a.verification_code,
    a.status,
    d.user_id AS driver_id,
    bi.chassis_number,
    bi.model,
    la.start_time,
    la.end_time,
    sc.departure_time,
    sc.arrival_time,
    sc.day_of_week
FROM ApplicationUser u
JOIN Admin a
    ON u.user_id = a.user_id
JOIN Line_assignment la
    ON a.user_id = la.admin_id
JOIN Driver d
    ON la.driver_id = d.user_id
JOIN Bus_instance bi
    ON la.chassis_number = bi.chassis_number
JOIN Schedule sc
    ON la.schedule_id = sc.schedule_id
WHERE a.status = 'Active';


-- 7. Информации за единечни билети според тип на купувач
CREATE OR REPLACE VIEW ticket_info AS
SELECT
    c.type,
    t.ticket_id,
    st.amount,
    st.assignment_id,
    la.start_time,
    la.end_time,
    bi.model,
    bi.capacity,
    b.brand
FROM Customer c
JOIN Ticket t
    ON c.user_id = t.user_id
JOIN Single_ticket st
    ON t.ticket_id = st.ticket_id
JOIN Line_assignment la
    ON st.assignment_id = la.assignment_id
JOIN Bus_instance bi
    ON la.chassis_number = bi.chassis_number
JOIN Bus b
    ON bi.bus_id = b.bus_id;


-- 8. Историја на сервисирање за автобус
CREATE OR REPLACE VIEW bus_service_history AS
SELECT
    bi.chassis_number,
    bi.registration_number,
    bi.model,
    m.maintenance_date,
    m.description,
    m.cost,
    m.maintenance_name
FROM Bus_instance bi
LEFT JOIN Maintenance m
    ON bi.chassis_number = m.chassis_number;


-- 9. Месечна продажба на билети и приход според начин на плаќање
CREATE OR REPLACE VIEW monthly_ticket_sales AS
SELECT
    date_trunc('month', p.payment_date)::date AS payment_month,
    pt.type AS payment_method,
    COUNT(*) AS sold_tickets,
    SUM(COALESCE(st.amount, pass.amount, 0)) AS total_amount
FROM Customer_Payment_Ticket cpt
JOIN Payment p
    ON cpt.payment_id = p.payment_id
JOIN Payment_type pt
    ON p.type_id = pt.type_id
JOIN Ticket t
    ON cpt.ticket_id = t.ticket_id
LEFT JOIN Single_ticket st
    ON t.ticket_id = st.ticket_id
LEFT JOIN Pass_ticket ppt
    ON t.ticket_id = ppt.ticket_id
LEFT JOIN Pass_type pass
    ON ppt.type_id = pass.id
WHERE p.status = 'Completed'
GROUP BY
    date_trunc('month', p.payment_date)::date,
    pt.type;