-- ==========================================
-- Drop tables if they exist
-- ==========================================

-- ==========================================
-- 1. Users
-- ==========================================
CREATE TABLE ApplicationUser (
                                 UserID SERIAL PRIMARY KEY,
                                 Name VARCHAR(100) NOT NULL,
                                 Surname VARCHAR(100) NOT NULL,
                                 Email VARCHAR(255) NOT NULL UNIQUE,
                                 Password VARCHAR(255) NOT NULL,
                                 PhoneNumber VARCHAR(50) UNIQUE,
                                 DateJoined TIMESTAMP NOT NULL DEFAULT NOW()
);

-- ==========================================
-- 2. Destinations
-- ==========================================
CREATE TABLE Destination (
                             DestinationID SERIAL PRIMARY KEY,
                             Name VARCHAR(255) NOT NULL,
                             Country VARCHAR(100) NOT NULL,
                             Description TEXT,
                             PopularAttraction TEXT,
                             BestTimeToVisit VARCHAR(50),
                             IsActive BOOLEAN NOT NULL DEFAULT TRUE
);

-- ==========================================
-- 3. Airports
-- ==========================================
CREATE TABLE Airport (
                         AirportID SERIAL PRIMARY KEY,
                         Name VARCHAR(255) NOT NULL,
                         Country VARCHAR(100) NOT NULL,
                         Code VARCHAR(10) UNIQUE NOT NULL,
                         DestinationID INT NOT NULL,
                         CONSTRAINT fk_airport_destination FOREIGN KEY (DestinationID)
                             REFERENCES Destination(DestinationID)
);

-- ==========================================
-- 4. Flights
-- ==========================================
CREATE TABLE Flight (
                        FlightID SERIAL PRIMARY KEY,
                        FlightNumber VARCHAR(50) NOT NULL,
                        DepartureAirportID INT NOT NULL,
                        ArrivalAirportID INT NOT NULL,
                        DepartureDate DATE NOT NULL,
                        ReturnDate DATE NOT NULL,
                        Price DECIMAL(10,2),
                        AvailableSeats INT NOT NULL,
                        CONSTRAINT fk_flight_dep FOREIGN KEY (DepartureAirportID) REFERENCES Airport(AirportID),
                        CONSTRAINT fk_flight_arr FOREIGN KEY (ArrivalAirportID) REFERENCES Airport(AirportID)
);

-- ==========================================
-- 5. Bookings
-- ==========================================
CREATE TABLE Booking (
                         BookingID SERIAL PRIMARY KEY,
                         UserID INT NOT NULL,
                         BookingDate TIMESTAMP NOT NULL DEFAULT NOW(),
                         PaymentStatus VARCHAR(20) CHECK (PaymentStatus IN ('Processing','Failed','Success')),
                         TotalCost DECIMAL(10,2),
                         CONSTRAINT fk_booking_user FOREIGN KEY (UserID) REFERENCES ApplicationUser(UserID)
);

-- ==========================================
-- 6. BookingFlight (M:N)
-- ==========================================
CREATE TABLE BookingFlight (
                               BookingID INT NOT NULL,
                               FlightID INT NOT NULL,
                               SeatNumber INT,
                               PRIMARY KEY (BookingID, FlightID),
                               CONSTRAINT fk_bf_booking FOREIGN KEY (BookingID) REFERENCES Booking(BookingID),
                               CONSTRAINT fk_bf_flight FOREIGN KEY (FlightID) REFERENCES Flight(FlightID)
);

-- ==========================================
-- 7. Reviews
-- ==========================================
CREATE TABLE Review (
                        ReviewID SERIAL PRIMARY KEY,
                        UserID INT NOT NULL,
                        BookingID INT NOT NULL,
                        TargetID INT NOT NULL,
                        TargetType VARCHAR(20) CHECK (TargetType IN ('Flight','Destination','Airport')),
                        ReviewComment TEXT,
                        Rating INT,
                        Date TIMESTAMP NOT NULL DEFAULT NOW(),
                        CONSTRAINT fk_review_user FOREIGN KEY (UserID) REFERENCES ApplicationUser(UserID),
                        CONSTRAINT fk_review_booking FOREIGN KEY (BookingID) REFERENCES Booking(BookingID)
);

-- ==========================================
-- 8. Payments
-- ==========================================
CREATE TABLE Payment (
                         PaymentID SERIAL PRIMARY KEY,
                         BookingID INT NOT NULL,
                         UserID INT NOT NULL,
                         PaymentMethod VARCHAR(20) CHECK (PaymentMethod IN ('Credit','Debit','PayPal')),
                         Amount DECIMAL(10,2),
                         TransactionDate TIMESTAMP NOT NULL DEFAULT NOW(),
                         PaymentStatus VARCHAR(20) CHECK (PaymentStatus IN ('Success','Failed','Processing')),
                         CONSTRAINT fk_payment_booking FOREIGN KEY (BookingID) REFERENCES Booking(BookingID),
                         CONSTRAINT fk_payment_user FOREIGN KEY (UserID) REFERENCES ApplicationUser(UserID)
);

-- ==========================================
-- 9. Notifications
-- ==========================================
CREATE TABLE Notification (
                              NotificationID SERIAL PRIMARY KEY,
                              UserID INT NOT NULL,
                              Message TEXT NOT NULL,
                              Type VARCHAR(50) CHECK (Type IN ('BookingConfirmation','FlightDelay','GeneralUpdate')),
                              DateSent TIMESTAMP NOT NULL DEFAULT NOW(),
                              CONSTRAINT fk_notification_user FOREIGN KEY (UserID) REFERENCES ApplicationUser(UserID)
);

-- ==========================================
-- 10. Wishlist
-- ==========================================
CREATE TABLE Wishlist (
                          WishlistID SERIAL PRIMARY KEY,
                          UserID INT NOT NULL,
                          TargetID INT NOT NULL,
                          TargetType VARCHAR(20) CHECK (TargetType IN ('Flight','Destination')),
                          DateAdded TIMESTAMP NOT NULL DEFAULT NOW(),
                          CONSTRAINT fk_wishlist_user FOREIGN KEY (UserID) REFERENCES ApplicationUser(UserID),
                          CONSTRAINT uq_wishlist UNIQUE (UserID, TargetID)
);

-- ==========================================
-- 11. Administrators
-- ==========================================
CREATE TABLE Administrator (
                               AdminID SERIAL PRIMARY KEY,
                               Email VARCHAR(255) NOT NULL UNIQUE
);

-- ==========================================
-- 12. Support Tickets
-- ==========================================
CREATE TABLE SupportTicket (
                               TicketID SERIAL PRIMARY KEY,
                               UserID INT NOT NULL,
                               Subject VARCHAR(255) NOT NULL,
                               Description TEXT NOT NULL,
                               Status VARCHAR(20) CHECK (Status IN ('Open','InProgress','Resolved')),
                               DateCreated TIMESTAMP NOT NULL DEFAULT NOW(),
                               DateResolved TIMESTAMP,
                               AssignedTo INT,
                               CONSTRAINT fk_ticket_user FOREIGN KEY (UserID) REFERENCES ApplicationUser(UserID),
                               CONSTRAINT fk_ticket_admin FOREIGN KEY (AssignedTo) REFERENCES Administrator(AdminID)
);


-- Indexes
CREATE INDEX idx_booking_user ON Booking(UserID);
CREATE INDEX idx_flight_departuredate ON Flight(DepartureDate);
CREATE INDEX idx_payment_booking ON Payment(BookingID);
CREATE INDEX idx_review_user ON Review(UserID);
CREATE INDEX idx_review_target_type_id ON Review(TargetType, TargetID);

-- Trigger: Deactivate destination on negative review
CREATE OR REPLACE FUNCTION trg_after_insert_review_negative_dest()
    RETURNS TRIGGER AS $$
BEGIN
    IF NEW.TargetType = 'Destination' AND NEW.Rating IS NOT NULL AND NEW.Rating < 3 THEN
        UPDATE Destination
        SET IsActive = FALSE
        WHERE DestinationID = NEW.TargetID;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_review_after_insert ON Review;
CREATE TRIGGER trg_review_after_insert
    AFTER INSERT ON Review
    FOR EACH ROW
EXECUTE FUNCTION trg_after_insert_review_negative_dest();

-- Trigger: Restore seats on deletion of BookingFlight
CREATE OR REPLACE FUNCTION trg_bookingflight_after_delete_restore_seat()
    RETURNS TRIGGER AS $$
BEGIN
    UPDATE Flight
    SET AvailableSeats = AvailableSeats + 1
    WHERE FlightID = OLD.FlightID;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_bf_after_delete ON BookingFlight;
CREATE TRIGGER trg_bf_after_delete
    AFTER DELETE ON BookingFlight
    FOR EACH ROW
EXECUTE FUNCTION trg_bookingflight_after_delete_restore_seat();

-- Function: average rating for destination
CREATE OR REPLACE FUNCTION avg_rating_for_destination(dest_id INT)
    RETURNS NUMERIC AS $$
DECLARE
    res NUMERIC;
BEGIN
    SELECT AVG(r.Rating)::NUMERIC INTO res
    FROM Review r
    WHERE r.TargetType = 'Destination' AND r.TargetID = dest_id;
    RETURN COALESCE(res, 0);
END;
$$ LANGUAGE plpgsql;

-- Procedure: create booking transactionally
CREATE OR REPLACE PROCEDURE create_booking(
    p_user_id INT,
    p_flight_ids INT[],
    p_seat_numbers INT[],
    p_total_cost DECIMAL(10,2),
    p_payment_method VARCHAR
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_booking_id INT;
    v_flight_id INT;
    v_seat_number INT;
    i INT;
    v_method VARCHAR(20);
BEGIN
    BEGIN
        -- Validate payment method
        v_method := INITCAP(p_payment_method); -- Capitalizes first letter (Credit, Debit, PayPal)
        IF v_method NOT IN ('Credit','Debit','PayPal') THEN
            RAISE EXCEPTION 'Invalid payment method: %', p_payment_method;
        END IF;

        -- Create Booking
        INSERT INTO Booking(UserID, BookingDate, PaymentStatus, TotalCost)
        VALUES (p_user_id, NOW(), 'Processing', p_total_cost)
        RETURNING BookingID INTO v_booking_id;

        -- Reserve seats
        FOR i IN array_lower(p_flight_ids,1)..array_upper(p_flight_ids,1) LOOP
            v_flight_id := p_flight_ids[i];
            v_seat_number := p_seat_numbers[i];

            IF (SELECT AvailableSeats FROM Flight WHERE FlightID = v_flight_id FOR UPDATE) <= 0 THEN
                RAISE EXCEPTION 'No seats available for flight %', v_flight_id;
            END IF;

            INSERT INTO BookingFlight(BookingID, FlightID, SeatNumber)
            VALUES (v_booking_id, v_flight_id, v_seat_number);

            UPDATE Flight
            SET AvailableSeats = AvailableSeats - 1
            WHERE FlightID = v_flight_id;
        END LOOP;

        -- Create Payment
        INSERT INTO Payment(BookingID, UserID, PaymentMethod, Amount, TransactionDate, PaymentStatus)
        VALUES (v_booking_id, p_user_id, v_method, p_total_cost, NOW(), 'Processing');

        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE;
    END;
END;
$$;


-- Trigger: Update Booking.PaymentStatus after Payment
CREATE OR REPLACE FUNCTION trg_update_booking_payment_status()
    RETURNS TRIGGER AS $$
BEGIN
    IF NEW.PaymentStatus = 'Success' THEN
        UPDATE Booking
        SET PaymentStatus = 'Completed'
        WHERE BookingID = NEW.BookingID;
    ELSIF NEW.PaymentStatus = 'Failed' THEN
        UPDATE Booking
        SET PaymentStatus = 'Cancelled'
        WHERE BookingID = NEW.BookingID;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_payment_after_update ON Payment;
CREATE TRIGGER trg_payment_after_update
    AFTER UPDATE OF PaymentStatus ON Payment
    FOR EACH ROW
EXECUTE FUNCTION trg_update_booking_payment_status();

-- Views
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.FlightNumber, dep.Name, arr.Name, f.DepartureDate, f.FlightID, f.Price, f.AvailableSeats, 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;

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;

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;

