LogicalAndPhysicalDesign: data_load.sql

begin;

TRUNCATE table
    booking_services, 
    sitter_services, 
    booking_pets, 
    admin_management, 
    payments, 
    reviews, 
    bookings, 
    services, 
    pets, 
    pet_types, 
    pet_sitters, 
    pet_owners, 
    admins, 
    users 
CASCADE;

INSERT INTO users (username, first_name, last_name, password, email) VALUES
('admin_jana', 'Jana', 'Fedorova', '$2a$10$hashed_pw_jana', 'jana.admin@petsitter.mk'),
('owner_bogdan', 'Bogdan', 'Lavrov', '$2a$10$hashed_pw_bogdan', 'bogdan.lavrov@email.com'),
('owner_valentina', 'Valentina', 'Petrova', '$2a$10$hashed_pw_valentina', 'valentina.petrova@email.com'),
('sitter_felix', 'Felix', 'Kovacevic', '$2a$10$hashed_pw_felix', 'felix.sitter@email.com'),
('sitter_lily', 'Lily', 'Aksyonova', '$2a$10$hashed_pw_lily', 'lily.sitter@email.com');

INSERT INTO pet_types (species, average_lifespan, needs_outdoor_walk) VALUES
('Dog', 13, TRUE),
('Cat', 15, FALSE),
('Rabbit', 9, FALSE);

INSERT INTO services (type, description) VALUES
('Dog Walking', 'A 30 minute walk around the neighborhood.'),
('Overnight Stay', 'Pet sitting at the owners house and staying overnight.'),
('Short Visit', 'A 30 minute visit for feeding and playtime.');

INSERT INTO admins (user_id) 
SELECT user_id FROM users WHERE username = 'admin_jana';

INSERT INTO pet_owners (user_id) 
SELECT user_id FROM users WHERE username IN ('owner_bogdan', 'owner_valentina');

INSERT INTO pet_sitters (user_id) 
SELECT user_id FROM users WHERE username IN ('sitter_felix', 'sitter_lily');

INSERT INTO pets (name, photo, age, special_needs, description, owner_id, pettype_id) VALUES
(
    'Leo', 'https://t3.ftcdn.net/jpg/01/72/07/44/360_F_172074409_hADHhLMTjELdx0cMkycnzYFZZZz4OYbv.jpg', 3, 'Allergic to chicken', 'Very friendly golden retriever', 
    (SELECT user_id FROM users WHERE username = 'owner_bogdan'), 
    (SELECT pettype_id FROM pet_types WHERE species = 'Dog')
),
(
    'Dante', 'https://thumbs.dreamstime.com/b/beautiful-cat-isolated-white-background-44369507.jpg', 5, NULL, 'Loves to sleep in the sun', 
    (SELECT user_id FROM users WHERE username = 'owner_valentina'), 
    (SELECT pettype_id FROM pet_types WHERE species = 'Cat')
);

INSERT INTO bookings (status, date_from, date_to, address, owner_id, sitter_id) VALUES
(
    'Completed', '2026-05-01', '2026-05-02', 'bul. Partizanski Odredi br. 50, Skopje', 
    (SELECT user_id FROM users WHERE username = 'owner_bogdan'), 
    (SELECT user_id FROM users WHERE username = 'sitter_felix')
),
(
    'Confirmed', '2026-06-10', '2026-06-15', 'ul. Leninova br. 25, Skopje', 
    (SELECT user_id FROM users WHERE username = 'owner_valentina'), 
    (SELECT user_id FROM users WHERE username = 'sitter_lily')
);

INSERT INTO reviews (rating, comment, booking_id) VALUES
(
    5, 'Felix was great with Leo! Highly recommend.', 
    (SELECT booking_id FROM bookings WHERE address = 'bul. Partizanski Odredi br. 50, Skopje')
);

INSERT INTO payments (amount, payment_type, booking_id) VALUES
(
    1500, 'Credit Card', 
    (SELECT booking_id FROM bookings WHERE address = 'bul. Partizanski Odredi br. 50, Skopje')
),
(
    4000, 'Cash', 
    (SELECT booking_id FROM bookings WHERE address = 'ul. Leninova br. 25, Skopje')
);

INSERT INTO admin_management (admin_id, user_id) VALUES
(
    (SELECT user_id FROM users WHERE username = 'admin_jana'), 
    (SELECT user_id FROM users WHERE username = 'owner_bogdan')
);

INSERT INTO booking_pets (booking_id, pet_id) VALUES
(
    (SELECT booking_id FROM bookings WHERE address = 'bul. Partizanski Odredi br. 50, Skopje'), 
    (SELECT pet_id FROM pets WHERE name = 'Leo')
),
(
    (SELECT booking_id FROM bookings WHERE address = 'ul. Leninova br. 25, Skopje'), 
    (SELECT pet_id FROM pets WHERE name = 'Dante')
);

INSERT INTO sitter_services (sitter_id, service_id) VALUES
(
    (SELECT user_id FROM users WHERE username = 'sitter_felix'), 
    (SELECT service_id FROM services WHERE type = 'Dog Walking')
),
(
    (SELECT user_id FROM users WHERE username = 'sitter_felix'), 
    (SELECT service_id FROM services WHERE type = 'Short Visit')
),
(
    (SELECT user_id FROM users WHERE username = 'sitter_lily'), 
    (SELECT service_id FROM services WHERE type = 'Overnight Stay')
);

INSERT INTO booking_services (booking_id, service_id) VALUES
(
    (SELECT booking_id FROM bookings WHERE address = 'bul. Partizanski Odredi br. 50, Skopje'), 
    (SELECT service_id FROM services WHERE type = 'Dog Walking')
),
(
    (SELECT booking_id FROM bookings WHERE address = 'ul. Leninova br. 25, Skopje'), 
    (SELECT service_id FROM services WHERE type = 'Overnight Stay')
);

commit;