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

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

CREATE OR REPLACE VIEW v_user_bookings AS
SELECT
    u.UserID,
    u.Name || ' ' || u.Surname AS FullName,
    b.BookingID,
    b.BookingDate,
    b.PaymentStatus,
    b.TotalCost,
    f.FlightID,
    f.FlightNumber,
    f.DepartureDate,
    f.ReturnDate,
    dep.Name AS DepartureAirport,
    arr.Name AS ArrivalAirport,
    f.Price
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
JOIN Airport dep ON f.DepartureAirportID = dep.AirportID
JOIN Airport arr ON f.ArrivalAirportID = arr.AirportID;

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

CREATE OR REPLACE VIEW v_flight_summary AS
SELECT
    f.FlightID,
    f.FlightNumber,
    dep.Name AS DepartureAirport,
    arr.Name AS ArrivalAirport,
    f.DepartureDate,
    f.ReturnDate,
    f.Price,
    f.AvailableSeats,
    COUNT(bf.BookingID) AS TotalBookings
FROM Flight f
JOIN Airport dep ON f.DepartureAirportID = dep.AirportID
JOIN Airport arr ON f.ArrivalAirportID = arr.AirportID
LEFT JOIN BookingFlight bf ON f.FlightID = bf.FlightID
GROUP BY f.FlightID, f.FlightNumber, dep.Name, arr.Name, f.DepartureDate, f.ReturnDate, f.Price, f.AvailableSeats;

• Извештај за рецензии

CREATE OR REPLACE VIEW v_reviews AS
SELECT
    r.ReviewID,
    u.Name || ' ' || u.Surname AS Reviewer,
    r.TargetID,
    r.TargetType,
    r.ReviewComment,
    r.Rating,
    r.Date
FROM Review r
JOIN ApplicationUser u ON r.UserID = u.UserID;

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

CREATE OR REPLACE VIEW v_user_payments AS
SELECT
    u.UserID,
    u.Name || ' ' || u.Surname AS FullName,
    COUNT(p.PaymentID) AS NumPayments,
    SUM(p.Amount) AS TotalPaid,
    AVG(p.Amount) AS AvgPayment
FROM Payment p
JOIN ApplicationUser u ON p.UserID = u.UserID
GROUP BY u.UserID, u.Name, u.Surname;

• Извештај за Wishlist

CREATE OR REPLACE VIEW v_user_wishlist AS
SELECT
    u.UserID,
    u.Name || ' ' || u.Surname AS FullName,
    w.WishlistID,
    w.TargetID,
    w.TargetType,
    w.DateAdded
FROM Wishlist w
JOIN ApplicationUser u ON w.UserID = u.UserID;

• Извештај за најдобро рангирани летови

CREATE OR REPLACE VIEW v_best_value_flights AS
SELECT
    f.FlightID,
    f.FlightNumber,
    f.Price,
    COALESCE(AVG(r.Rating), 0) AS AvgRating
FROM Flight f
LEFT JOIN Review r ON r.TargetType = 'Flight' AND r.TargetID = f.FlightID
GROUP BY f.FlightID, f.FlightNumber, f.Price
HAVING COALESCE(AVG(r.Rating),0) > 4 AND f.Price < 200;

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

CREATE OR REPLACE VIEW TopMonthlyReport AS
WITH MonthlyBookings AS (
    SELECT
        DATE_TRUNC('month', b.BookingDate) AS Month,
        d.Name AS DestinationName,
        COUNT(b.BookingID) AS BookingsCount,
        RANK() OVER (PARTITION BY DATE_TRUNC('month', b.BookingDate)
                     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.ArrivalAirportID = a.AirportID
    JOIN Destination d ON a.DestinationID = d.DestinationID
    GROUP BY Month, d.Name
),
MonthlyFlights AS (
    SELECT
        DATE_TRUNC('month', b.BookingDate) AS Month,
        f.FlightNumber,
        COUNT(b.BookingID) AS BookingsCount,
        RANK() OVER (PARTITION BY DATE_TRUNC('month', b.BookingDate)
                     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.FlightNumber
),
MonthlyAirports AS (
    SELECT
        DATE_TRUNC('month', b.BookingDate) AS Month,
        a.Name AS AirportName,
        COUNT(b.BookingID) AS DeparturesCount,
        RANK() OVER (PARTITION BY DATE_TRUNC('month', b.BookingDate)
                     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.DepartureAirportID = 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, FlightNumber 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;