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

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

CREATE VIEW MostPopularFlightsReport AS
SELECT
    f.FlightID,
    f.Flight_Number,
    f.Departure_Airport,
    f.Arrival_Airport,
    COUNT(bf.BookingID) AS NumberOfBookings
FROM
    Flight f
JOIN BookingFlight bf ON f.FlightID = bf.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 Booking b ON r.BookingID = b.BookingID
JOIN BookingFlight bf ON b.BookingID = bf.BookingID
JOIN Flight f ON bf.FlightID = f.FlightID
JOIN Airport a ON f.Arrival_Airport = a.AirportID
JOIN Destination d ON a.DestinationID = d.DestinationID
GROUP BY
    d.DestinationID, d.Name
ORDER BY
    AverageRating DESC;

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

CREATE VIEW UserBookingExpenditureReport AS
SELECT
    u.UserID,
    u.Name,
    u.Surname,
    COUNT(DISTINCT b.BookingID) AS NumberOfBookings,
    SUM(b.Total_Cost) 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,
    STRING_AGG(f.Flight_Number, ', ') AS Flights,
    b.Booking_Date,
    b.Total_Cost
FROM
    Booking b
JOIN ApplicationUser u ON b.UserID = u.UserID
JOIN BookingFlight bf ON b.BookingID = bf.BookingID
JOIN Flight f ON bf.FlightID = f.FlightID
WHERE
    b.Payment_Status = 'PENDING'
GROUP BY
    b.BookingID, u.Name, u.Surname, b.Booking_Date, b.Total_Cost;

• Извештај за топ 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(DISTINCT 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 Booking b ON r.BookingID = b.BookingID
JOIN ApplicationUser u ON b.UserID = u.UserID
JOIN BookingFlight bf ON b.BookingID = bf.BookingID
JOIN Flight f ON bf.FlightID = f.FlightID
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 FlightBookingByDepartureDestination AS
SELECT
    depDest.DestinationID,
    depDest.Name AS DepartureDestinationName,
    COUNT(b.BookingID) AS NumberOfBookings
FROM
    Booking b
JOIN BookingFlight bf ON b.BookingID = bf.BookingID
JOIN Flight f ON bf.FlightID = f.FlightID
JOIN Airport depA ON f.Departure_Airport = depA.AirportID
JOIN Destination depDest ON depA.DestinationID = depDest.DestinationID
GROUP BY
    depDest.DestinationID, depDest.Name
ORDER BY
    NumberOfBookings DESC;

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

CREATE VIEW TopMonthlyReport AS
WITH MonthlyBookings AS (
    SELECT
        DATE_TRUNC('month', b.Booking_Date) AS Month,
        d.Name AS DestinationName,
        COUNT(b.BookingID) AS BookingsCount,
        RANK() OVER (PARTITION BY DATE_TRUNC('month', b.Booking_Date)
                     ORDER BY COUNT(b.BookingID) DESC) AS Rank
    FROM Booking b
    JOIN BookingFlight bf ON b.BookingID = bf.BookingID
    JOIN Flight f ON bf.FlightID = f.FlightID
    JOIN Airport a ON f.Arrival_Airport = a.AirportID
    JOIN Destination d ON a.DestinationID = d.DestinationID
    GROUP BY Month, d.Name
),
MonthlyFlights AS (
    SELECT
        DATE_TRUNC('month', b.Booking_Date) AS Month,
        f.Flight_Number,
        COUNT(b.BookingID) AS BookingsCount,
        RANK() OVER (PARTITION BY DATE_TRUNC('month', b.Booking_Date)
                     ORDER BY COUNT(b.BookingID) DESC) AS Rank
    FROM Booking b
    JOIN BookingFlight bf ON b.BookingID = bf.BookingID
    JOIN Flight f ON bf.FlightID = f.FlightID
    GROUP BY Month, f.Flight_Number
),
MonthlyAirports AS (
    SELECT
        DATE_TRUNC('month', b.Booking_Date) AS Month,
        a.Name AS AirportName,
        COUNT(b.BookingID) AS DeparturesCount,
        RANK() OVER (PARTITION BY DATE_TRUNC('month', b.Booking_Date)
                     ORDER BY COUNT(b.BookingID) DESC) AS Rank
    FROM Booking b
    JOIN BookingFlight bf ON b.BookingID = bf.BookingID
    JOIN Flight f ON bf.FlightID = f.FlightID
    JOIN Airport a ON f.Departure_Airport = a.AirportID
    GROUP BY Month, a.Name
)
SELECT Month, 'Destination' AS Category, DestinationName AS Name, BookingsCount AS Count
FROM MonthlyBookings WHERE Rank <= 3
UNION ALL
SELECT Month, 'Flight' AS Category, Flight_Number AS Name, BookingsCount AS Count
FROM MonthlyFlights WHERE Rank <= 3
UNION ALL
SELECT Month, 'Airport' AS Category, AirportName AS Name, DeparturesCount AS Count
FROM MonthlyAirports WHERE Rank <= 3
ORDER BY Month, Category, Count DESC;