Логички и физички дизајн - Креирање база податоци: ddlscript-drop-create.sql

DROP TABLE IF EXISTS SupportTicket CASCADE;
DROP TABLE IF EXISTS Wishlist CASCADE;
DROP TABLE IF EXISTS Notification CASCADE;
DROP TABLE IF EXISTS Payment CASCADE;
DROP TABLE IF EXISTS Review CASCADE;
DROP TABLE IF EXISTS BookingFlight CASCADE;
DROP TABLE IF EXISTS Booking CASCADE;
DROP TABLE IF EXISTS Flight CASCADE;
DROP TABLE IF EXISTS Airport CASCADE;
DROP TABLE IF EXISTS Destination CASCADE;
DROP TABLE IF EXISTS ApplicationUser CASCADE;
DROP TABLE IF EXISTS Administrator CASCADE;

-- 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)
);

-- 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,
    DepartureTime TIMESTAMP NOT NULL,
    ArrivalTime TIMESTAMP 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 ('Pending','Completed','Cancelled')),
    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 Card','Debit Card','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 ('Booking Confirmation','Flight Delay','General Update')),
    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)
);

-- 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','In Progress','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)
);


CREATE INDEX idx_booking_user ON Booking(UserID);
CREATE INDEX idx_flight_dep_time ON Flight(DepartureTime);
CREATE INDEX idx_payment_booking ON Payment(BookingID);
CREATE INDEX idx_review_user ON Review(UserID);


-- Prevent duplicate wishlist entries
CREATE OR REPLACE FUNCTION validate_wishlist_entry()
RETURNS TRIGGER AS $$
BEGIN
   IF EXISTS (
       SELECT 1 FROM Wishlist
       WHERE UserID = NEW.UserID AND TargetID = NEW.TargetID
   ) THEN
       RAISE EXCEPTION 'Item already exists in wishlist!';
   END IF;
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_wishlist_validate
BEFORE INSERT ON Wishlist
FOR EACH ROW
EXECUTE FUNCTION validate_wishlist_entry();

-- Validate new user email
CREATE OR REPLACE FUNCTION validate_new_user()
RETURNS TRIGGER AS $$
BEGIN
   IF EXISTS (SELECT 1 FROM ApplicationUser WHERE Email = NEW.Email) THEN
       RAISE EXCEPTION 'Email already registered!';
   END IF;
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_user_validate
BEFORE INSERT ON ApplicationUser
FOR EACH ROW
EXECUTE FUNCTION validate_new_user();


-- User Bookings with Flight Details
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.DepartureTime,
    f.ArrivalTime,
    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;

-- Flight Summary
CREATE OR REPLACE VIEW v_flight_summary AS
SELECT
    f.FlightID,
    f.FlightNumber,
    dep.Name AS DepartureAirport,
    arr.Name AS ArrivalAirport,
    f.DepartureTime,
    f.ArrivalTime,
    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.DepartureTime, f.ArrivalTime, f.Price, f.AvailableSeats;

-- Reviews View
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;

-- Payments Summary
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 Summary
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;


EXPLAIN ANALYZE
SELECT * FROM Flight
WHERE DepartureTime >= '2025-05-01' AND DepartureTime < '2025-06-01';

EXPLAIN ANALYZE
SELECT * FROM Booking
WHERE BookingDate >= '2025-05-01' AND BookingDate < '2025-06-01';

EXPLAIN ANALYZE
SELECT * FROM Payment
WHERE BookingID = 1;