create table application_user (
	    userid INT PRIMARY KEY,
	    name varchar(255),
	    surname varchar(255),
	    email varchar(255),
	    password varchar(255),
	    phone_number varchar(20),
	    date_joined date
	);

	create table destination(
	    destinationid int primary key,
	    name varchar(255),
	    country varchar(255),
	    description text,
	    popular_attraction text,
	    best_time_to_visit varchar(255)
	);

	create table airport(
	    airportid int primary key,
	    name varchar(255),
	    city int,
	    country varchar(255),
	    code varchar(3),
	    foreign key (city) references destination(destinationid)
	);

	create table flight (
	    flightid int primary key,
	    flight_number varchar(50),
	    departure_airport int,
	    arrival_airport int,
	    departure_time time,
	    arrival_time time,
	    price decimal(10,2),
	    available_seats int,
	    foreign key (arrival_airport) references airport(airportid),
	    foreign key (departure_airport) references airport(airportid)
	);

	create table booking(
	    bookingid int primary key ,
	    userid int,
	    flightid int,
	    booking_date date,
	    payment_status varchar(20) check(booking.payment_status in ('PENDING','COMPLETED','CANCELLED')),
	    total_cost decimal(10,2),
	    seat_number int,
	    foreign key (userid) references application_user(userid),
	    foreign key (flightid) references flight(flightid)
	);

	create table review (
	    reviewid int primary key ,
	    userid int,
	    targetid int,
	    review_comment text,
	    rating int,
	    date date,
	    foreign key (userid) references application_user(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,
	    payment_method varchar(20) check (payment.payment_method in ('PAYPAL', 'DEBIT', 'CREDIT')),
	    amount decimal(10,2),
	    transaction_date date,
	    payment_status varchar(20) check (payment.payment_status in ('PENDING', 'COMPLETED', 'CANCELLED')),
	    foreign key (bookingid) references booking(bookingid),
	    foreign key (userid) references application_user(userid)
	);

	create table notification(
	    notificationid int primary key,
	    userid int,
	    message text,
	    type varchar(20) check ( Type in ('BOOKING_CONFIRMATION','FLIGHT_DELAY','GENERAL_UPDATE')),
	    date_sent date,
	    foreign key (userid) references application_user(userid)
	);

	create table wishlist(
	    wishlistid int primary key ,
	    userid int,
	    targetid int,
	    date_added date,
	    foreign key (userid) references application_user(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) unique

	);

	create table supportticket(
	    ticketid int primary key ,
	    userid int,
	    subjectid varchar(255),
	    description text,
	    status varchar(20) check ( Status in ('OPEN','IN_PROGRESS','RESOLVED')),
	    date_created date,
	    date_resolved date,
	    assigned_to int,
	    foreign key (userid) references application_user(userid),
	    foreign key (assigned_to) references administrator(adminid)
	);