Логички: ddlscript-create.sql

create table ApplicationUser(
    UserID INT PRIMARY KEY,
    Name varchar(255),
    Surname varchar(255),
    Email varchar(255),
    Password varchar(255),
    PhoneNumber varchar(20),
    DateJoined date
);

create table Destination(
    DestinationID int primary key,
    Name varchar(255),
    Country varchar(255),
    Description text,
    PopularAttraction text,
    BestTimeToVisit varchar(255)
);

create table Airport(
    AirportID int primary key,
    Name varchar(255),
    CityID int,
    Country varchar(255),
    Code varchar(3),
    foreign key (CityID) references Destination(DestinationID)
);

create table Flight(
    FlightID int primary key,
    FlightNumber varchar(50),
    DepartureAirport int,
    ArrivalAirport int,
    DepartureTime time,
    ArrivalTime time,
    Price decimal(10,2),
    AvailableSeats int,
    foreign key (ArrivalAirport) references Airport(AirportID),
    foreign key (DepartureAirport) references Airport(AirportID)
);

create table Booking(
    BookingID int primary key ,
    UserID int,
    FlightID int,
    BookingDate date,
    PaymentStatus varchar(20) check(PaymentStatus in ('Pending','Completed','Cancelled')),
    TotalCost decimal(10,2),
    SeatNumber int,
    foreign key (UserID) references ApplicationUser(UserID),
    foreign key (FlightID) references Flight(FlightID)
);

create table Review(
    ReviewID int primary key ,
    UserID int,
    TargetID int,
    ReviewComment text,
    Rating int,
    Date date,
    foreign key (UserID) references ApplicationUser(UserID),
    constraint FK_TargetID_Flight foreign key (TargetID) references Flight(FlightID) on delete cascade ,
    constraint FK_TargetID_Destination foreign key (TargetID) references Destination(DestinationID) on delete cascade
);

create table Payment(
    PaymentID int primary key,
    BookingID int,
    UserID int,
    PaymentMethod varchar(20) check (PaymentMethod in ('Credit card', 'Debit card', 'PayPal')),
    Amount decimal(10,2),
    TransactionDate date,
    PaymentStatus varchar(20) check (PaymentStatus in ('Success', 'Failed', 'Processing')),
    foreign key (BookingID) references Booking(BookingID),
    foreign key (UserID) references ApplicationUser(UserID)
);

create table Notification(
    NotificationID int primary key,
    UserID int,
    Message text,
    Type varchar(20) check ( Type in ('Booking confirmation','Flight delay','General update')),
    DateSent date,
    foreign key (UserID) references ApplicationUser(UserID)
);

create table Wishlist(
    WishlistID int primary key ,
    UserID int,
    TargetID int,
    DateAdded date,
    foreign key (UserID) references ApplicationUser(UserID),
    constraint FK_TargetID_Flight foreign key (TargetID) references Flight(FlightID) on delete cascade ,
    constraint FK_TargetID_Destination foreign key (TargetID) references Destination(DestinationID) on delete cascade
);

create table Administrator
(
    AdminID int primary key,
    Email   varchar(255)

);

create table SupportTicket(
    TicketID int primary key ,
    UserID int,
    Subject varchar(255),
    Description text,
    Status varchar(20) check ( Status in ('Open','In progress','Resolved')),
    DateCreated date,
    DateResolved date,
    AssignedTo int,
    foreign key (UserID) references ApplicationUser(UserID),
    foreign key (AssignedTo) references Administrator(AdminID)
);