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

INSERT INTO application_user (userid, name, surname, email, password, phone_number, date_joined)
VALUES
(6, 'Alice', 'Johnson', 'alice.johnson@email.com', 'pass123', '1234567890', '2023-01-15'),
(7, 'Bob', 'Smith', 'bob.smith@email.com', 'securepass', '0987654321', '2023-02-20');


INSERT INTO destination (destinationid, best_time_to_visit, country, description, name, popular_attraction)
VALUES
(1, 'Spring (March to May)', 'France', 'Capital city of France, known for its art, fashion, and landmarks.', 'Paris', 'Eiffel Tower'),
(2, 'Fall (September to November)', 'USA', 'A major cultural, financial, and media capital, known for its skyline and landmarks.', 'New York', 'Statue of Liberty'),
(3, 'Spring (March to May)', 'Japan', 'A bustling city with a unique mix of traditional and modern attractions.', 'Tokyo', 'Shibuya Crossing'),
(4, 'Summer (June to August)', 'United Kingdom', 'The capital of England, famous for its history, culture, and landmarks.', 'London', 'Big Ben'),
(5, 'Fall (September to November)', 'Spain', 'A vibrant city known for its architecture, beaches, and culture.', 'Barcelona', 'Sagrada Família');


INSERT INTO airport (airportid, city, code, country, name)
VALUES
(1, 1, 'CDG', 'France', 'Charles de Gaulle Airport'),
(2, 2, 'JFK', 'USA', 'John F. Kennedy International Airport'),
(3, 4, 'LHR', 'United Kingdom', 'London Heathrow Airport'),
(4, 3, 'JAP', 'Japan', 'Tokyo International Airport'),
(5, 5, 'SPN', 'Spain','Barselona Airport');

INSERT INTO flight (arrival_airport, arrival_time, available_seats, departure_airport, departure_time, price, flightid,
                    flight_number, departure_date, return_date)
VALUES
(2, '12:30:00', 100, 1, '10:00:00', 250.00, 1, 'FL1001', '2025-03-01'::date, '2025-03-10'::date),
(3, '15:45:00', 80, 1, '13:15:00', 300.00, 2, 'FL1002', '2025-03-02'::date, NULL),
(1, '18:00:00', 90, 2, '16:00:00', 280.00, 3, 'FL1003', '2025-03-03'::date, '2025-03-12'::date),
(4, '20:30:00', 120, 3, '18:00:00', 400.00, 4, 'FL1004', '2025-03-04'::date, NULL),
(5, '22:15:00', 75, 4, '20:45:00', 350.00, 5, 'FL1005', '2025-03-05'::date, '2025-03-14'::date),
(3, '11:00:00', 110, 5, '09:00:00', 290.00, 6, 'FL1006', '2025-03-06'::date, NULL),
(1, '14:00:00', 95, 4, '12:30:00', 320.00, 7, 'FL1007', '2025-03-07'::date, '2025-03-16'::date),
(5, '17:30:00', 85, 2, '15:45:00', 275.00, 8, 'FL1008', '2025-03-08'::date, NULL),
(4, '19:45:00', 105, 3, '18:15:00', 390.00, 9, 'FL1009', '2025-03-09'::date, '2025-03-18'::date),
(2, '23:00:00', 70, 5, '21:30:00', 260.00, 10, 'FL1010', '2025-03-10'::date, NULL);

INSERT INTO booking (booking_date, bookingid, flightid, seat_number, total_cost, userid, payment_status)
VALUES
('2025-02-11', 35, 2, 45, 320.00, 6, 'PENDING'),
('2025-02-11', 34, 1, 36, 320.00, 6, 'PENDING'),
('2025-02-11', 37, 4, 25, 280.00, 7, 'PENDING'),
('2025-02-11', 36, 3, 18, 300.00, 7, 'PENDING'),
('2025-02-11', 38, 5, 25, 400.00, 6, 'PENDING');

INSERT INTO review ("date", rating, reviewid, targetid, userid, review_comment)
VALUES
('2025-02-11', 5, 2, 2, 6, 'Review1'),
('2025-02-11', 5, 3, 3, 7, 'Review2'),
('2025-02-11', 5, 1, 1, 6, 'Review3');

INSERT INTO payment (amount, bookingid, paymentid, transaction_date, userid, payment_method, payment_status)
VALUES
(280.00, 36, 9, '2025-02-11', 6, 'CREDIT', 'COMPLETED'),
(280.00, 35, 8, '2025-02-11', 6, 'CREDIT', 'COMPLETED'),
(280.00, 34, 6, '2025-02-11', 6, 'CREDIT', 'COMPLETED'),
(400.00, 37, 10, '2025-02-11', 7, 'CREDIT', 'COMPLETED'),
(280.00, 36, 5, '2025-02-11', 7, NULL, 'PENDING'),
(280.00, 35, 7, '2025-02-11', 7, 'CREDIT', 'COMPLETED'),
(280.00, 38, 4, '2025-02-13', 6, NULL, 'PENDING');

insert into notification (date_sent, notificationid, userid, message, type)
values
('2025-02-12', 1, 6, 'Your flight booking has been confirmed.', 'BOOKING_CONFIRMATION'),
('2025-02-12', 2, 6, 'Your flight departure has been delayed by 2 hours.', 'FLIGHT_DELAY'),
('2025-02-12', 3, 6, 'A new destination has been added to your wishlist.', 'GENERAL_UPDATE'),
('2025-02-12', 4, 7, 'Your support ticket has been updated.', 'GENERAL_UPDATE'),
('2025-02-12', 5, 7, 'Your flight departure time has changed.', 'FLIGHT_DELAY'),
('2025-02-12', 6, 6, 'Your booking has been successfully canceled.', 'BOOKING_CONFIRMATION'),
('2025-02-12', 7, 7, 'A special discount is available for your next trip!', 'GENERAL_UPDATE'),
('2025-02-12', 8, 7, 'Your refund request has been approved.', 'GENERAL_UPDATE'),
('2025-02-12', 9, 6, 'A new review has been posted on your favorite destination.', 'GENERAL_UPDATE'),
('2025-02-12', 10, 7, 'Check-in for your flight is now open.', 'BOOKING_CONFIRMATION');

INSERT INTO wishlist (date_added, targetid, userid, wishlistid)
VALUES
('2025-02-12', 1, 6, 1),
('2025-02-12', 2, 6, 2),
('2025-02-12', 3, 6, 3),
('2025-02-12', 4, 7, 4),
('2025-02-12', 5, 7, 5),
('2025-02-12', 5, 7, 6),
('2025-02-12', 1, 6, 7),
('2025-02-12', 2, 7, 8),
('2025-02-12', 3, 6, 9),
('2025-02-12', 4, 7, 10);

INSERT INTO  administrator (adminid, email)
VALUES
(1, 'admin');

INSERT INTO supportticket (assigned_to, date_created, date_resolved, ticketid, userid, description, status)
VALUES
(1, '2024-02-11', NULL, 3, 7, 'I need a refund for my flight.', 'OPEN'),
(1, '2025-02-11', '2025-02-12', 1, 6, 'Test', 'RESOLVED'),
(1, '2024-02-14', NULL, 2, 7, 'My luggage was lost during transit.', 'OPEN');