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

-- CREATE SCHEMA
CREATE SCHEMA IF NOT EXISTS project;

-- TABLE: ApplicationUser
CREATE TABLE project.ApplicationUser (
    UserID SERIAL PRIMARY KEY,
    Name VARCHAR(255),
    Surname VARCHAR(255),
    Email VARCHAR(255) UNIQUE NOT NULL,
    Password VARCHAR(255) NOT NULL,
    PhoneNumber VARCHAR(20),
    DateJoined TIMESTAMP NOT NULL DEFAULT NOW()
);

-- TABLE: Destination
CREATE TABLE project.Destination (
    DestinationID SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Country VARCHAR(255),
    Description TEXT,
    PopularAttraction TEXT,
    BestTimeToVisit VARCHAR(255)
);

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

-- TABLE: Flight
CREATE TABLE project.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,
    CONSTRAINT fk_flight_departure FOREIGN KEY (DepartureAirportID) REFERENCES project.Airport(AirportID),
    CONSTRAINT fk_flight_arrival FOREIGN KEY (ArrivalAirportID) REFERENCES project.Airport(AirportID)
);

-- TABLE: Booking
CREATE TABLE project.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 project.ApplicationUser(UserID)
);

-- TABLE: BookingFlight (many-to-many)
CREATE TABLE project.BookingFlight (
    BookingID INT NOT NULL,
    FlightID INT NOT NULL,
    SeatNumber INT,
    PRIMARY KEY (BookingID, FlightID),
    CONSTRAINT fk_bookingflight_booking FOREIGN KEY (BookingID) REFERENCES project.Booking(BookingID),
    CONSTRAINT fk_bookingflight_flight FOREIGN KEY (FlightID) REFERENCES project.Flight(FlightID)
);

-- TABLE: Review
CREATE TABLE project.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 CHECK (Rating >= 1 AND Rating <= 5),
    ReviewDate TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT fk_review_user FOREIGN KEY (UserID) REFERENCES project.ApplicationUser(UserID),
    CONSTRAINT fk_review_booking FOREIGN KEY (BookingID) REFERENCES project.Booking(BookingID)
);

-- TABLE: Payment
CREATE TABLE project.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 project.Booking(BookingID),
    CONSTRAINT fk_payment_user FOREIGN KEY (UserID) REFERENCES project.ApplicationUser(UserID)
);

-- TABLE: Notification
CREATE TABLE project.Notification (
    NotificationID SERIAL PRIMARY KEY,
    UserID INT NOT NULL,
    Message TEXT,
    Type VARCHAR(30) CHECK (Type IN ('Booking Confirmation','Flight Delay','General Update')),
    DateSent TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT fk_notification_user FOREIGN KEY (UserID) REFERENCES project.ApplicationUser(UserID)
);

-- TABLE: Wishlist
CREATE TABLE project.Wishlist (
    WishlistID SERIAL PRIMARY KEY,
    UserID INT NOT NULL,
    TargetID INT NOT NULL,
    DateAdded TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT fk_wishlist_user FOREIGN KEY (UserID) REFERENCES project.ApplicationUser(UserID)
);

-- TABLE: Administrator
CREATE TABLE project.Administrator (
    AdminID SERIAL PRIMARY KEY,
    Email VARCHAR(255) UNIQUE NOT NULL
);

-- TABLE: SupportTicket
CREATE TABLE project.SupportTicket (
    TicketID SERIAL PRIMARY KEY,
    UserID INT NOT NULL,
    Subject VARCHAR(255),
    Description TEXT,
    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 project.ApplicationUser(UserID),
    CONSTRAINT fk_ticket_admin FOREIGN KEY (AssignedTo) REFERENCES project.Administrator(AdminID)
);