
TRUNCATE TABLE appointment CASCADE;
TRUNCATE TABLE inquiry CASCADE;
TRUNCATE TABLE timeslot CASCADE;
TRUNCATE TABLE client CASCADE;
TRUNCATE TABLE unit CASCADE;
TRUNCATE TABLE floor CASCADE;
TRUNCATE TABLE designs CASCADE;
TRUNCATE TABLE building CASCADE;
TRUNCATE TABLE architect CASCADE;

INSERT INTO architect (full_name) VALUES
('Stefan'),
('Aleksandar');

INSERT INTO designs (architect_id, building_id) VALUES
((SELECT architect_id FROM architect WHERE full_name = 'Stefan'),      (SELECT building_id FROM building WHERE name = 'Golden Tower')),
((SELECT architect_id FROM architect WHERE full_name = 'Aleksandar'),  (SELECT building_id FROM building WHERE name = 'Azure Block')),
((SELECT architect_id FROM architect WHERE full_name = 'Stefan'),      (SELECT building_id FROM building WHERE name = 'City Wall'));

INSERT INTO floor (floor_number, layout_image, building_id) VALUES
(1, 'golden_tower_floor1.jpg', (SELECT building_id FROM building WHERE name = 'Golden Tower')),
(2, 'golden_tower_floor2.jpg', (SELECT building_id FROM building WHERE name = 'Golden Tower')),
(3, 'golden_tower_floor3.jpg', (SELECT building_id FROM building WHERE name = 'Golden Tower')),
(1, 'azure_block_floor1.jpg',  (SELECT building_id FROM building WHERE name = 'Azure Block')),
(2, 'azure_block_floor2.jpg',  (SELECT building_id FROM building WHERE name = 'Azure Block')),
(1, 'city_wall_floor1.jpg',    (SELECT building_id FROM building WHERE name = 'City Wall')),
(2, 'city_wall_floor2.jpg',    (SELECT building_id FROM building WHERE name = 'City Wall'));

INSERT INTO unit (unit_number, room_number, floor_area, status, price, image, floorplan, vector_image, floor_id) VALUES
('101', 2, 75.00,  'Available', 85000.00,  'unit_101.jpg', 'plan_101.pdf', 'vector_101.svg',
    (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Golden Tower') AND floor_number = 1)),
('102', 3, 95.00,  'Available', 110000.00, 'unit_102.jpg', 'plan_102.pdf', 'vector_102.svg',
    (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Golden Tower') AND floor_number = 1)),
('201', 2, 75.00,  'Reserved',  87000.00,  'unit_201.jpg', 'plan_201.pdf', 'vector_201.svg',
    (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Golden Tower') AND floor_number = 2)),
('202', 3, 95.00,  'Available', 112000.00, 'unit_202.jpg', 'plan_202.pdf', 'vector_202.svg',
    (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Golden Tower') AND floor_number = 2)),
('301', 2, 75.00,  'Available', 89000.00,  'unit_301.jpg', 'plan_301.pdf', 'vector_301.svg',
    (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Golden Tower') AND floor_number = 3)),
('302', 3, 95.00,  'Sold',      114000.00, 'unit_302.jpg', 'plan_302.pdf', 'vector_302.svg',
    (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Golden Tower') AND floor_number = 3)),
('101', 2, 80.00,  'Available', 92000.00,  'unit_101.jpg', 'plan_101.pdf', 'vector_101.svg',
    (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Azure Block') AND floor_number = 1)),
('102', 1, 55.00,  'Available', 68000.00,  'unit_102.jpg', 'plan_102.pdf', 'vector_102.svg',
    (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Azure Block') AND floor_number = 1)),
('201', 2, 80.00,  'Available', 94000.00,  'unit_201.jpg', 'plan_201.pdf', 'vector_201.svg',
    (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Azure Block') AND floor_number = 2)),
('202', 1, 55.00,  'Reserved',  70000.00,  'unit_202.jpg', 'plan_202.pdf', 'vector_202.svg',
    (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'Azure Block') AND floor_number = 2)),
('101', 2, 70.00,  'Available', 78000.00,  'unit_101.jpg', 'plan_101.pdf', 'vector_101.svg',
    (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'City Wall') AND floor_number = 1)),
('102', 3, 90.00,  'Available', 98000.00,  'unit_102.jpg', 'plan_102.pdf', 'vector_102.svg',
    (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'City Wall') AND floor_number = 1)),
('201', 2, 70.00,  'Available', 80000.00,  'unit_201.jpg', 'plan_201.pdf', 'vector_201.svg',
    (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'City Wall') AND floor_number = 2)),
('202', 3, 90.00,  'Available', 100000.00, 'unit_202.jpg', 'plan_202.pdf', 'vector_202.svg',
    (SELECT floor_id FROM floor WHERE building_id = (SELECT building_id FROM building WHERE name = 'City Wall') AND floor_number = 2));

INSERT INTO client (name, email, phone) VALUES
('Marko', 'marko@gmail.com',   '+389 73 123 456'),
('Ana',   'ana@yahoo.com',     '+389 73 234 567'),
('Ivan',  'ivan@hotmail.com',  '+389 73 345 678'),
('Elena', 'elena@gmail.com',   '+389 73 456 789'),
('Darko', 'darko@gmail.com',   '+389 73 567 890');

INSERT INTO appointment (status, client_id, unit_id, timeslot_id) VALUES
('Scheduled',
    (SELECT client_id FROM client WHERE name = 'Marko'),
    (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)),
    (SELECT timeslot_id FROM timeslot WHERE date = '2025-01-10' AND time_start = '14:00:00')),
('Scheduled',
    (SELECT client_id FROM client WHERE name = 'Ivan'),
    (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)),
    (SELECT timeslot_id FROM timeslot WHERE date = '2025-01-11' AND time_start = '11:00:00')),
('Completed',
    (SELECT client_id FROM client WHERE name = 'Elena'),
    (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)),
    (SELECT timeslot_id FROM timeslot WHERE date = '2025-01-13' AND time_start = '10:00:00'));
