Напредни извештаи од базата

• Извештај за најпопуларни летови по резервација

CREATE VIEW MostPopularFlightsReport AS
SELECT
    f.flightid,
    f.flight_number,
    f.departure_airport,
    f.arrival_airport,
    COUNT(b.BookingID) AS NumberOfBookings
FROM
    Flight f
JOIN
    Booking b ON f.flightid = b.flightid
GROUP BY
    f.flightid, f.flight_number, f.departure_airport, f.arrival_airport
ORDER BY
    NumberOfBookings DESC;

• Извештај за просечен рејтинг по дестинација

CREATE VIEW AverageDestinationRatingReport AS
SELECT
    d.destinationid,
    d.name AS DestinationName,
    AVG(r.rating) AS AverageRating
FROM
    Review r
JOIN
    Destination d ON r.target_id = d.DestinationID
GROUP BY
    d.destinationid, d.name
ORDER BY
    AverageRating DESC;

• Извештај за историја на резервации на корисници со вкупни трошоци

CREATE VIEW UserBookingExpenditureReport AS
SELECT
    u.userid,
    u.name,
    u.surname,
    COUNT(b.bookingid) AS NumberOfBookings,
    SUM(b.total_cost) AS TotalExpenditure
FROM
    application_user u
JOIN
    Booking b ON u.userid = b.userid
GROUP BY
    u.userid, u.name, u.surname
ORDER BY
    TotalExpenditure DESC;

• Извештај за резервации со статус 'Pending'

CREATE VIEW PendingPaymentBookingsReport AS
SELECT
    b.bookingid,
    u.name AS UserName,
    u.surname AS UserSurname,
    f.flight_number,
    f.departure_airport,
    f.arrival_airport,
    b.booking_date,
    b.total_cost
FROM
    Booking b
JOIN
    application_user u ON b.userid = u.userid
JOIN
    Flight f ON b.flightid = f.flightid
WHERE
    b.payment_status = 'PENDING';

• Извештај за топ 5 дестинации според Wishlist

CREATE VIEW TopDestinationsWishlistReport AS
SELECT
    d.destinationid,
    d.name AS DestinationName,
    d.country,
    COUNT(w.wishlistid) AS NumberOfWishlistEntries
FROM
    Wishlist w
JOIN
    Destination d ON w.targetid = d.destinationid
GROUP BY
    d.destinationid, d.name, d.country
ORDER BY
    NumberOfWishlistEntries DESC
LIMIT 5;

• Извештај за корисници кои имаат највеќе резервации

CREATE VIEW TopUsersByBookings AS
SELECT
    u.userid,
    u.name,
    u.surname,
    COUNT(b.bookingid) AS NumberOfBookings
FROM
    application_user u
JOIN
    Booking b ON u.userid = b.userid
GROUP BY
    u.userid, u.name, u.surname
ORDER BY
    NumberOfBookings DESC;

• Извештај за корисници со највеќе рецензии

CREATE VIEW FrequentFlightReviewers AS
SELECT 
    u.userid, 
    u.name, 
    u.surname, 
    COUNT(r.review_id) AS NumberOfReviews
FROM 
    Review r
JOIN 
    application_user u ON r.userid = u.userid
WHERE 
    r.target_id IN (SELECT FlightID FROM Flight)
GROUP BY 
    u.userid, u.name, u.surname
ORDER BY 
    NumberOfReviews DESC;

• Извештај за најдобри начини на плаќање според вкупната потрошувачка

CREATE VIEW PaymentMethodSpendReport AS
SELECT 
    p.payment_method, 
    SUM(p.Amount) AS TotalAmountSpent
FROM 
    Payment p
GROUP BY 
    p.payment_method
ORDER BY 
    TotalAmountSpent DESC;

• Извештај за резервација на летови по град на поаѓање

CREATE VIEW FlightBookingByCity AS
SELECT
    a.airportid,
    c.name AS CityName,
    COUNT(b.bookingid) AS NumberOfBookings
FROM
    Booking b
JOIN
    Flight f ON b.FlightID = f.FlightID
JOIN
    Airport a ON f.departure_airport = a.airportid
JOIN
    Destination c ON a.city = c.destinationid
GROUP BY
    a.airportid, c.name
ORDER BY
    NumberOfBookings DESC;

• Извештај за топ 3 најпопуларни дестинации, аеродроми и летови за секој месец во годината

CREATE VIEW TopMonhtlyReport AS
WITH MonthlyBookings AS (
    SELECT
        DATE_TRUNC('month', b.booking_date) AS month,
        d.name AS destination_name,
        COUNT(b.bookingid) AS bookings_count,
        RANK() OVER (PARTITION BY DATE_TRUNC('month', b.booking_date) ORDER BY COUNT(b.bookingid) DESC) AS rank
    FROM Booking b
    JOIN Flight f ON b.flightid = f.flightid
    JOIN Airport a ON f.arrival_airport = a.airportid
    JOIN Destination d ON a.city = d.destinationid
    GROUP BY month, destination_name
),

MonthlyFlights AS (
    SELECT
        DATE_TRUNC('month', b.booking_date) AS month,
        f.flight_number,
        COUNT(b.bookingid) AS bookings_count,
        RANK() OVER (PARTITION BY DATE_TRUNC('month', b.booking_date) ORDER BY COUNT(b.bookingid) DESC) AS rank
    FROM Booking b
    JOIN Flight f ON b.flightid = f.flightid
    GROUP BY month, f.flight_number
),

MonthlyAirports AS (
    SELECT
        DATE_TRUNC('month', b.booking_date) AS month,
        a.name AS airport_name,
        COUNT(b.bookingid) AS departures_count,
        RANK() OVER (PARTITION BY DATE_TRUNC('month', b.booking_date) ORDER BY COUNT(b.bookingid) DESC) AS rank
    FROM Booking b
    JOIN Flight f ON b.flightid = f.flightid
    JOIN Airport a ON f.departure_airport = a.airportid
    GROUP BY month, a.name
)

SELECT month, 'Destination' AS category, destination_name AS name, bookings_count AS count FROM MonthlyBookings WHERE rank <= 3
UNION ALL
SELECT month, 'Flight' AS category, flight_number AS name, bookings_count AS count FROM MonthlyFlights WHERE rank <= 3
UNION ALL
SELECT month, 'Airport' AS category, airport_name AS name, departures_count AS count FROM MonthlyAirports WHERE rank <= 3
ORDER BY month, category, count DESC;