| 1 | SET search_path TO project;
|
|---|
| 2 |
|
|---|
| 3 | TRUNCATE TABLE appointment CASCADE;
|
|---|
| 4 | TRUNCATE TABLE inquiry CASCADE;
|
|---|
| 5 | TRUNCATE TABLE timeslot CASCADE;
|
|---|
| 6 | TRUNCATE TABLE client CASCADE;
|
|---|
| 7 | TRUNCATE TABLE agent CASCADE;
|
|---|
| 8 | TRUNCATE TABLE unit CASCADE;
|
|---|
| 9 | TRUNCATE TABLE floor CASCADE;
|
|---|
| 10 | TRUNCATE TABLE designs CASCADE;
|
|---|
| 11 | TRUNCATE TABLE building CASCADE;
|
|---|
| 12 | TRUNCATE TABLE architect CASCADE;
|
|---|
| 13 | TRUNCATE TABLE admin CASCADE;
|
|---|
| 14 |
|
|---|
| 15 | INSERT INTO admin (name, email, password) VALUES
|
|---|
| 16 | ('Nikola', 'nikola@example.com', '$2a$10$hashed_password_nikola'),
|
|---|
| 17 | ('Kiril', 'kiril@example.com', '$2a$10$hashed_password_kiril');
|
|---|
| 18 |
|
|---|
| 19 | INSERT INTO architect (full_name) VALUES
|
|---|
| 20 | ('Stefan'),
|
|---|
| 21 | ('Aleksandar');
|
|---|
| 22 |
|
|---|
| 23 | INSERT INTO building (name, address, description, admin_id) VALUES
|
|---|
| 24 | ('Golden Tower', 'Prashka 15', 'Luxury living in the heart of the city', (SELECT admin_id FROM admin WHERE name = 'Nikola')),
|
|---|
| 25 | ('Azure Block', 'Rimska 23', 'Modern housing in a quiet neighbourhood', (SELECT admin_id FROM admin WHERE name = 'Kiril')),
|
|---|
| 26 | ('City Wall', 'Hristo Tatarchev 18', 'All you need housing for city living', (SELECT admin_id FROM admin WHERE name = 'Nikola'));
|
|---|
| 27 |
|
|---|
| 28 | INSERT INTO designs (architect_id, building_id) VALUES
|
|---|
| 29 | ((SELECT architect_id FROM architect WHERE full_name = 'Stefan'), (SELECT building_id FROM building WHERE name = 'Golden Tower')),
|
|---|
| 30 | ((SELECT architect_id FROM architect WHERE full_name = 'Aleksandar'), (SELECT building_id FROM building WHERE name = 'Azure Block')),
|
|---|
| 31 | ((SELECT architect_id FROM architect WHERE full_name = 'Stefan'), (SELECT building_id FROM building WHERE name = 'City Wall'));
|
|---|
| 32 |
|
|---|
| 33 | INSERT INTO floor (floor_number, layout_image, building_id) VALUES
|
|---|
| 34 | (1, 'golden_tower_floor1.jpg', (SELECT building_id FROM building WHERE name = 'Golden Tower')),
|
|---|
| 35 | (2, 'golden_tower_floor2.jpg', (SELECT building_id FROM building WHERE name = 'Golden Tower')),
|
|---|
| 36 | (3, 'golden_tower_floor3.jpg', (SELECT building_id FROM building WHERE name = 'Golden Tower')),
|
|---|
| 37 | (1, 'azure_block_floor1.jpg', (SELECT building_id FROM building WHERE name = 'Azure Block')),
|
|---|
| 38 | (2, 'azure_block_floor2.jpg', (SELECT building_id FROM building WHERE name = 'Azure Block')),
|
|---|
| 39 | (1, 'city_wall_floor1.jpg', (SELECT building_id FROM building WHERE name = 'City Wall')),
|
|---|
| 40 | (2, 'city_wall_floor2.jpg', (SELECT building_id FROM building WHERE name = 'City Wall'));
|
|---|
| 41 |
|
|---|
| 42 | INSERT INTO unit (unit_number, room_number, floor_area, status, price, image, floorplan, vector_image, floor_id) VALUES
|
|---|
| 43 | ('101', 2, 75.00, 'Available', 85000.00, 'unit_101.jpg', 'plan_101.pdf', 'vector_101.svg',
|
|---|
| 44 | (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Golden Tower') AND floor_number = 1)),
|
|---|
| 45 | ('102', 3, 95.00, 'Available', 110000.00, 'unit_102.jpg', 'plan_102.pdf', 'vector_102.svg',
|
|---|
| 46 | (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Golden Tower') AND floor_number = 1)),
|
|---|
| 47 | ('201', 2, 75.00, 'Reserved', 87000.00, 'unit_201.jpg', 'plan_201.pdf', 'vector_201.svg',
|
|---|
| 48 | (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Golden Tower') AND floor_number = 2)),
|
|---|
| 49 | ('202', 3, 95.00, 'Available', 112000.00, 'unit_202.jpg', 'plan_202.pdf', 'vector_202.svg',
|
|---|
| 50 | (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Golden Tower') AND floor_number = 2)),
|
|---|
| 51 | ('301', 2, 75.00, 'Available', 89000.00, 'unit_301.jpg', 'plan_301.pdf', 'vector_301.svg',
|
|---|
| 52 | (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Golden Tower') AND floor_number = 3)),
|
|---|
| 53 | ('302', 3, 95.00, 'Sold', 114000.00, 'unit_302.jpg', 'plan_302.pdf', 'vector_302.svg',
|
|---|
| 54 | (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Golden Tower') AND floor_number = 3)),
|
|---|
| 55 | ('101', 2, 80.00, 'Available', 92000.00, 'unit_101.jpg', 'plan_101.pdf', 'vector_101.svg',
|
|---|
| 56 | (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Azure Block') AND floor_number = 1)),
|
|---|
| 57 | ('102', 1, 55.00, 'Available', 68000.00, 'unit_102.jpg', 'plan_102.pdf', 'vector_102.svg',
|
|---|
| 58 | (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Azure Block') AND floor_number = 1)),
|
|---|
| 59 | ('201', 2, 80.00, 'Available', 94000.00, 'unit_201.jpg', 'plan_201.pdf', 'vector_201.svg',
|
|---|
| 60 | (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Azure Block') AND floor_number = 2)),
|
|---|
| 61 | ('202', 1, 55.00, 'Reserved', 70000.00, 'unit_202.jpg', 'plan_202.pdf', 'vector_202.svg',
|
|---|
| 62 | (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Azure Block') AND floor_number = 2)),
|
|---|
| 63 | ('101', 2, 70.00, 'Available', 78000.00, 'unit_101.jpg', 'plan_101.pdf', 'vector_101.svg',
|
|---|
| 64 | (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'City Wall') AND floor_number = 1)),
|
|---|
| 65 | ('102', 3, 90.00, 'Available', 98000.00, 'unit_102.jpg', 'plan_102.pdf', 'vector_102.svg',
|
|---|
| 66 | (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'City Wall') AND floor_number = 1)),
|
|---|
| 67 | ('201', 2, 70.00, 'Available', 80000.00, 'unit_201.jpg', 'plan_201.pdf', 'vector_201.svg',
|
|---|
| 68 | (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'City Wall') AND floor_number = 2)),
|
|---|
| 69 | ('202', 3, 90.00, 'Available', 100000.00, 'unit_202.jpg', 'plan_202.pdf', 'vector_202.svg',
|
|---|
| 70 | (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'City Wall') AND floor_number = 2));
|
|---|
| 71 |
|
|---|
| 72 | INSERT INTO agent (name, email, password) VALUES
|
|---|
| 73 | ('Teodora', 'teodora@example.com', '$2a$10$hashed_password_teodora'),
|
|---|
| 74 | ('Bojana', 'bojana@example.com', '$2a$10$hashed_password_bojana'),
|
|---|
| 75 | ('Vasil', 'vasil@example.com', '$2a$10$hashed_password_vasil');
|
|---|
| 76 |
|
|---|
| 77 | INSERT INTO client (name, email, phone) VALUES
|
|---|
| 78 | ('Marko', 'marko@gmail.com', '+389 73 123 456'),
|
|---|
| 79 | ('Ana', 'ana@yahoo.com', '+389 73 234 567'),
|
|---|
| 80 | ('Ivan', 'ivan@hotmail.com', '+389 73 345 678'),
|
|---|
| 81 | ('Elena', 'elena@gmail.com', '+389 73 456 789'),
|
|---|
| 82 | ('Darko', 'darko@gmail.com', '+389 73 567 890');
|
|---|
| 83 |
|
|---|
| 84 | INSERT INTO timeslot (date, time_start, time_end, status, agent_id) VALUES
|
|---|
| 85 | ('2025-01-10', '10:00:00', '11:00:00', 'Available', (SELECT agent_id FROM agent WHERE name = 'Teodora')),
|
|---|
| 86 | ('2025-01-10', '14:00:00', '15:00:00', 'Booked', (SELECT agent_id FROM agent WHERE name = 'Teodora')),
|
|---|
| 87 | ('2025-01-11', '09:00:00', '10:00:00', 'Available', (SELECT agent_id FROM agent WHERE name = 'Bojana')),
|
|---|
| 88 | ('2025-01-11', '11:00:00', '12:00:00', 'Booked', (SELECT agent_id FROM agent WHERE name = 'Bojana')),
|
|---|
| 89 | ('2025-01-12', '15:00:00', '16:00:00', 'Available', (SELECT agent_id FROM agent WHERE name = 'Vasil')),
|
|---|
| 90 | ('2025-01-13', '10:00:00', '11:00:00', 'Booked', (SELECT agent_id FROM agent WHERE name = 'Vasil'));
|
|---|
| 91 |
|
|---|
| 92 | INSERT INTO inquiry (message, status, created_at, unit_id, client_id, agent_id) VALUES
|
|---|
| 93 | ('Hi, is unit 101 still available?', 'Replied', '2025-01-05 10:30:00',
|
|---|
| 94 | (SELECT unit_id FROM unit WHERE unit_number = '101' AND floor_id = (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Golden Tower') AND floor_number = 1)),
|
|---|
| 95 | (SELECT client_id FROM client WHERE name = 'Marko'),
|
|---|
| 96 | (SELECT agent_id FROM agent WHERE name = 'Teodora')),
|
|---|
| 97 | ('Unit 202 more images.', 'New', '2025-01-06 14:20:00',
|
|---|
| 98 | (SELECT unit_id FROM unit WHERE unit_number = '202' AND floor_id = (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Golden Tower') AND floor_number = 2)),
|
|---|
| 99 | (SELECT client_id FROM client WHERE name = 'Ana'),
|
|---|
| 100 | (SELECT agent_id FROM agent WHERE name = 'Teodora')),
|
|---|
| 101 | ('What is included with this unit?', 'Replied', '2025-01-07 09:15:00',
|
|---|
| 102 | (SELECT unit_id FROM unit WHERE unit_number = '101' AND floor_id = (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Azure Block') AND floor_number = 1)),
|
|---|
| 103 | (SELECT client_id FROM client WHERE name = 'Ivan'),
|
|---|
| 104 | (SELECT agent_id FROM agent WHERE name = 'Bojana')),
|
|---|
| 105 | ('Is parking included?', 'Replied', '2025-01-07 13:30:00',
|
|---|
| 106 | (SELECT unit_id FROM unit WHERE unit_number = '101' AND floor_id = (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'City Wall') AND floor_number = 1)),
|
|---|
| 107 | (SELECT client_id FROM client WHERE name = 'Elena'),
|
|---|
| 108 | (SELECT agent_id FROM agent WHERE name = 'Vasil')),
|
|---|
| 109 | ('Interested in unit 301, please call me', 'New', '2025-01-08 16:45:00',
|
|---|
| 110 | (SELECT unit_id FROM unit WHERE unit_number = '301' AND floor_id = (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Golden Tower') AND floor_number = 3)),
|
|---|
| 111 | (SELECT client_id FROM client WHERE name = 'Darko'),
|
|---|
| 112 | (SELECT agent_id FROM agent WHERE name = 'Teodora'));
|
|---|
| 113 |
|
|---|
| 114 | INSERT INTO appointment (status, client_id, unit_id, timeslot_id) VALUES
|
|---|
| 115 | ('Scheduled',
|
|---|
| 116 | (SELECT client_id FROM client WHERE name = 'Marko'),
|
|---|
| 117 | (SELECT unit_id FROM unit WHERE unit_number = '101' AND floor_id = (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Golden Tower') AND floor_number = 1)),
|
|---|
| 118 | (SELECT timeslot_id FROM timeslot WHERE date = '2025-01-10' AND time_start = '14:00:00')),
|
|---|
| 119 | ('Scheduled',
|
|---|
| 120 | (SELECT client_id FROM client WHERE name = 'Ivan'),
|
|---|
| 121 | (SELECT unit_id FROM unit WHERE unit_number = '101' AND floor_id = (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Azure Block') AND floor_number = 1)),
|
|---|
| 122 | (SELECT timeslot_id FROM timeslot WHERE date = '2025-01-11' AND time_start = '11:00:00')),
|
|---|
| 123 | ('Completed',
|
|---|
| 124 | (SELECT client_id FROM client WHERE name = 'Elena'),
|
|---|
| 125 | (SELECT unit_id FROM unit WHERE unit_number = '101' AND floor_id = (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'City Wall') AND floor_number = 1)),
|
|---|
| 126 | (SELECT timeslot_id FROM timeslot WHERE date = '2025-01-13' AND time_start = '10:00:00'));
|
|---|
