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

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

CREATE VIEW MostPopularFlightsReport AS
SELECT 
    f.FlightID, 
    f.FlightNumber, 
    f.DepartureAirport, 
    f.ArrivalAirport, 
    COUNT(b.BookingID) AS NumberOfBookings
FROM 
    Flight f
JOIN 
    Booking b ON f.FlightID = b.FlightID
GROUP BY 
    f.FlightID, f.FlightNumber, f.DepartureAirport, f.ArrivalAirport
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.TargetID = 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.TotalCost) AS TotalExpenditure
FROM 
    ApplicationUser 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.FlightNumber, 
    f.DepartureAirport, 
    f.ArrivalAirport, 
    b.BookingDate, 
    b.TotalCost
FROM 
    Booking b
JOIN 
    ApplicationUser u ON b.UserID = u.UserID
JOIN 
    Flight f ON b.FlightID = f.FlightID
WHERE 
    b.PaymentStatus = '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 
    ApplicationUser 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.ReviewID) AS NumberOfReviews
FROM 
    Review r
JOIN 
    ApplicationUser u ON r.UserID = u.UserID
WHERE 
    r.TargetID IN (SELECT FlightID FROM Flight)
GROUP BY 
    u.UserID, u.Name, u.Surname
ORDER BY 
    NumberOfReviews DESC;

• Извештај за следни летови

CREATE VIEW UpcomingFlightsWithAvailability AS
SELECT 
    f.FlightID, 
    f.FlightNumber, 
    f.DepartureAirport, 
    f.ArrivalAirport, 
    f.DepartureTime, 
    f.ArrivalTime, 
    f.AvailableSeats
FROM 
    Flight f
WHERE 
    f.DepartureTime > NOW() AND f.AvailableSeats > 0
ORDER BY 
    f.DepartureTime;

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

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

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

CREATE VIEW FlightBookingByCity AS
SELECT 
    a.CityID, 
    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.DepartureAirport = a.AirportID
JOIN 
    City c ON a.CityID = c.CityID
GROUP BY 
    a.CityID, c.Name
ORDER BY 
    NumberOfBookings DESC;

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

WITH MonthlyBookings AS (
    -- Count bookings for each destination per month
    SELECT 
        DATE_TRUNC('month', b.bookingDate) AS month,
        d.Name AS destination_name,
        COUNT(b.BookingID) AS bookings_count,
        RANK() OVER (PARTITION BY DATE_TRUNC('month', b.bookingDate) ORDER BY COUNT(b.BookingID) DESC) AS rank
    FROM Booking b
    JOIN Flight f ON b.FlightID = f.FlightID
    JOIN Airport a ON f.ArrivalAirport = a.AirportID
    JOIN Destination d ON a.City = d.DestinationID
    GROUP BY month, destination_name
), 

MonthlyFlights AS (
    -- Count bookings for each flight per month
    SELECT 
        DATE_TRUNC('month', b.bookingDate) AS month,
        f.FlightNumber,
        COUNT(b.BookingID) AS bookings_count,
        RANK() OVER (PARTITION BY DATE_TRUNC('month', b.bookingDate) ORDER BY COUNT(b.BookingID) DESC) AS rank
    FROM Booking b
    JOIN Flight f ON b.FlightID = f.FlightID
    GROUP BY month, f.FlightNumber
), 

MonthlyAirports AS (
    -- Count departures from each airport per month
    SELECT 
        DATE_TRUNC('month', b.bookingDate) AS month,
        a.Name AS airport_name,
        COUNT(b.BookingID) AS departures_count,
        RANK() OVER (PARTITION BY DATE_TRUNC('month', b.bookingDate) ORDER BY COUNT(b.BookingID) DESC) AS rank
    FROM Booking b
    JOIN Flight f ON b.FlightID = f.FlightID
    JOIN Airport a ON f.DepartureAirport = a.AirportID
    GROUP BY month, a.Name
)

-- Retrieve the top 3 destinations, flights, and airports for each month
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, FlightNumber 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;