database-group-4/insert_mock_data.sql at main · captaincobbs/database-group-4 · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
USE airdandd;

INSERT INTO user (user_id, payment_method_id, name, email, phone_number, identity_verified) VALUES
(1, NULL, 'Alice Smith', 'alice@example.com', '8015551111', 1),
(2, NULL, 'Bob Jones', 'bob@example.com', '8015552222', 0),
(3, NULL, 'Carol White', 'carol@example.com', '8015553333', 1);

ALTER TABLE Payment_Method DROP FOREIGN KEY fk_payment_details;

INSERT INTO payment_detail (payment_details_id, payment_method_id, card_number, cvv, expration_date, billing_address) VALUES
(1, NULL, '4111111111111111', 123, '2025-12-01 00:00:00', '123 Maple St'),
(2, NULL, '4222222222222', 456, '2026-06-01 00:00:00', '456 Oak Ave'),
(3, NULL, '4333333333333333', 789, '2027-01-01 00:00:00', '789 Pine Blvd');


INSERT INTO payment_method (payment_method_id, payment_details_id, user_id, payment_type, is_payout) VALUES
(1, 1, 1, 'debit', 0),
(2, 2, 2, 'credit', 0),
(3, 3, 3, 'credit', 1);

UPDATE payment_detail SET payment_method_id = 1 WHERE payment_details_id = 1;
UPDATE payment_detail SET payment_method_id = 2 WHERE payment_details_id = 2;
UPDATE payment_detail SET payment_method_id = 3 WHERE payment_details_id = 3;

ALTER TABLE Payment_Method
ADD CONSTRAINT fk_payment_details
FOREIGN KEY (payment_details_id) REFERENCES Payment_Detail(payment_details_id);


UPDATE user SET payment_method_id = 1 WHERE user_id = 1;
UPDATE user SET payment_method_id = 2 WHERE user_id = 2;
UPDATE user SET payment_method_id = 3 WHERE user_id = 3;

ALTER TABLE Room DROP FOREIGN KEY fk_room_host;

INSERT INTO host (host_id, user_id, room_id) VALUES
(1, 1, NULL),
(2, 3, NULL);

INSERT INTO room (room_id, host_id, title, description, theme, photos, price) VALUES
(1, 1, 'Dragon''s Lair', 'Cool cave vibes', 'Dungeon', 'dragon1.jpg', 99.99),
(2, 2, 'Fairy Forest', 'Magical forest stay', 'Fairy', 'fairy1.jpg', 129.5);

UPDATE host SET room_id = 1 WHERE host_id = 1;
UPDATE host SET room_id = 2 WHERE host_id = 2;

ALTER TABLE Room
ADD CONSTRAINT fk_room_host
FOREIGN KEY (host_id) REFERENCES Host(host_id);

INSERT INTO guest (guest_id, user_id, room_id) VALUES
(1, 2, 1);

INSERT INTO service (service_id, name, price) VALUES
(1, 'Cleaning', 20.0),
(2, 'Breakfast', 15.0);

INSERT INTO room_service (room_service_id, room_id, service_id) VALUES
(1, 1, 1),
(2, 2, 1),
(3, 2, 2);

INSERT INTO room_availability (availability_id, room_id, start_date, end_date) VALUES
(1, 1, '2025-05-01 00:00:00', '2025-05-07 00:00:00'),
(2, 2, '2025-06-01 00:00:00', '2025-06-15 00:00:00');

ALTER TABLE Conversation DROP FOREIGN KEY fk_convo_message;

INSERT INTO message (message_id, sender_user_id, send_date, message) VALUES
(1, 2, '2025-04-10 10:00:00', 'Hi, excited to stay at your place!'),
(2, 1, '2025-04-12 10:00:00', 'Follow-up message 2 from user 1'),
(3, 3, '2025-04-13 10:00:00', 'Follow-up message 3 from user 3'),
(4, 1, '2025-04-14 10:00:00', 'Follow-up message 4 from user 1'),
(5, 2, '2025-04-15 10:00:00', 'Follow-up message 5 from user 2');

INSERT INTO conversation (conversation_id, message_id, reseervation_id, participants) VALUES
(1, 1, 1, 2),
(2, 2, 1, 2),
(3, 3, 1, 2),
(4, 4, 1, 2),
(5, 5, 1, 2);

ALTER TABLE Conversation
ADD CONSTRAINT fk_convo_message
FOREIGN KEY (message_id) REFERENCES Message(message_id);

INSERT INTO reservation (reservation_id, room_id, guest_id, conversation_id, check_in_date, check_out_date, status, total_cost, payment_status) VALUES
(1, 1, 1, 1, '2025-05-01 15:00:00', '2025-05-05 11:00:00', 'available', 399.96, 'completed'),
(2, 1, 1, 2, '2025-05-12 15:00:00', '2025-05-15 15:00:00', 'available', 360.49, 'completed'),
(3, 1, 1, 3, '2025-05-13 15:00:00', '2025-05-16 15:00:00', 'available', 386.69, 'completed'),
(4, 1, 1, 4, '2025-05-14 15:00:00', '2025-05-17 15:00:00', 'available', 452.86, 'completed'),
(5, 2, 1, 5, '2025-05-15 15:00:00', '2025-05-18 15:00:00', 'available', 492.72, 'completed');

INSERT INTO transaction (transaction_id, reservation_id, guest_payment_method_id, host_payment_method_id, guest_id, host_id, amount, status, occurance_date) VALUES
(1, 1, 2, 3, 1, 1, 399.96, 'completed', '2025-04-09 12:00:00'),
(2, 2, 2, 3, 1, 1, 445.49, 'completed', '2025-04-011 12:00:00'),
(3, 3, 2, 3, 1, 1, 372.62, 'completed', '2025-04-012 12:00:00'),
(4, 4, 2, 3, 1, 1, 418.73, 'completed', '2025-04-013 12:00:00'),
(5, 5, 2, 3, 1, 1, 390.1, 'completed', '2025-04-014 12:00:00');