SET search_path TO project;

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

INSERT INTO admin (name, email, password) VALUES
('Nikola', 'nikola@example.com', '$2a$10$hashed_password_nikola'),
('Kiril', 'kiril@example.com', '$2a$10$hashed_password_kiril');

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

INSERT INTO building (name, address, description, admin_id) VALUES
('Golden Tower', 'Prashka 15', 'Luxury living in the heart of the city', (SELECT admin_id FROM admin WHERE name = 'Nikola')),
('Azure Block', 'Rimska 23', 'Modern housing in a quiet neighbourhood',  (SELECT admin_id FROM admin WHERE name = 'Kiril')),
('City Wall', 'Hristo Tatarchev 18', 'All you need housing for city living', (SELECT admin_id FROM admin WHERE name = 'Nikola'));

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 agent (name, email, password) VALUES
('Teodora', 'teodora@example.com', '$2a$10$hashed_password_teodora'),
('Bojana',  'bojana@example.com',  '$2a$10$hashed_password_bojana'),
('Vasil',   'vasil@example.com',   '$2a$10$hashed_password_vasil');

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 timeslot (date, time_start, time_end, status, agent_id) VALUES
('2025-01-10', '10:00:00', '11:00:00', 'Available', (SELECT agent_id FROM agent WHERE name = 'Teodora')),
('2025-01-10', '14:00:00', '15:00:00', 'Booked',    (SELECT agent_id FROM agent WHERE name = 'Teodora')),
('2025-01-11', '09:00:00', '10:00:00', 'Available', (SELECT agent_id FROM agent WHERE name = 'Bojana')),
('2025-01-11', '11:00:00', '12:00:00', 'Booked',    (SELECT agent_id FROM agent WHERE name = 'Bojana')),
('2025-01-12', '15:00:00', '16:00:00', 'Available', (SELECT agent_id FROM agent WHERE name = 'Vasil')),
('2025-01-13', '10:00:00', '11:00:00', 'Booked',    (SELECT agent_id FROM agent WHERE name = 'Vasil'));

INSERT INTO inquiry (message, status, created_at, unit_id, client_id, agent_id) VALUES
('Hi, is unit 101 still available?', 'Replied', '2025-01-05 10:30:00',
    (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 client_id FROM client WHERE name = 'Marko'),
    (SELECT agent_id FROM agent WHERE name = 'Teodora')),
('Unit 202 more images.', 'New', '2025-01-06 14:20:00',
    (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)),
    (SELECT client_id FROM client WHERE name = 'Ana'),
    (SELECT agent_id FROM agent WHERE name = 'Teodora')),
('What is included with this unit?', 'Replied', '2025-01-07 09:15:00',
    (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 client_id FROM client WHERE name = 'Ivan'),
    (SELECT agent_id FROM agent WHERE name = 'Bojana')),
('Is parking included?', 'Replied', '2025-01-07 13:30:00',
    (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 client_id FROM client WHERE name = 'Elena'),
    (SELECT agent_id FROM agent WHERE name = 'Vasil')),
('Interested in unit 301, please call me', 'New', '2025-01-08 16:45:00',
    (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)),
    (SELECT client_id FROM client WHERE name = 'Darko'),
    (SELECT agent_id FROM agent WHERE name = 'Teodora'));

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'));