RelationalDesign: 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_darko', 'Darko', 'Trajkovski', '$2a$10$hashed_pw_darko', 'darko.admin@petsitter.mk'),
('owner_bojan', 'Bojan', 'Jovanovski', '$2a$10$hashed_pw_bojan', 'bojan.jovanovski@email.com'),
('owner_vesna', 'Vesna', 'Petkovska', '$2a$10$hashed_pw_vesna', 'vesna.petkovska@email.com'),
('sitter_filip', 'Filip', 'Nikolovski', '$2a$10$hashed_pw_filip', 'filip.sitter@email.com'),
('sitter_lile', 'Liljana', 'Ilievska', '$2a$10$hashed_pw_lile', 'lile.sitter@email.com'),
('owner_marko', 'Marko', 'Markovski', '$2a$10$hashed_pw_marko', 'marko.markovski@email.com'),
('owner_elena', 'Elena', 'Ristevska', '$2a$10$hashed_pw_elena', 'elena.ristevska@email.com'),
('owner_nikola', 'Nikola', 'Stojanov', '$2a$10$hashed_pw_nikola', 'nikola.stojanov@email.com'),
('sitter_ana', 'Ana', 'Trajkovska', '$2a$10$hashed_pw_ana', 'ana.trajkovska@email.com'),
('sitter_stefan', 'Stefan', 'Ilioski', '$2a$10$hashed_pw_stefan', 'stefan.ilioski@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_darko';

INSERT INTO pet_owners (user_id) 
SELECT user_id FROM users WHERE username IN ('owner_bojan', 'owner_vesna', 'owner_marko', 'owner_elena', 'owner_nikola');

INSERT INTO pet_sitters (user_id) 
SELECT user_id FROM users WHERE username IN ('sitter_filip', 'sitter_lile', 'sitter_ana', 'sitter_stefan');
 
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_bojan'), 
    (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_vesna'), 
    (SELECT pettype_id FROM pet_types WHERE species = 'Cat')
),
(
    'Rex', 'https://smartcdn.gprod.postmedia.digital/ocanada/wp-content/uploads/2019/10/rex_d04_ep102_ddy_0251.jpg', 4, NULL, 'Very energetic and loves fetch', 
    (SELECT user_id FROM users WHERE username = 'owner_marko'), 
    (SELECT pettype_id FROM pet_types WHERE species = 'Dog')
),
(
    'Bela', 'https://www.noina-arka.hr/wp-content/uploads/2020/11/Bella_maltezer_1-800x600.jpg', 2, 'Needs anxiety meds during storms', 'Quiet and shy dog', 
    (SELECT user_id FROM users WHERE username = 'owner_marko'), 
    (SELECT pettype_id FROM pet_types WHERE species = 'Dog')
),
(
    'Luna', 'https://www.shutterstock.com/image-photo/domestic-cat-on-white-background-260nw-2502238361.jpg', 3, 'Picky eater', 'Lazy and cuddly cat', 
    (SELECT user_id FROM users WHERE username = 'owner_elena'), 
    (SELECT pettype_id FROM pet_types WHERE species = 'Cat')
),
(
    'Kiko', 'https://media.istockphoto.com/id/879402334/photo/cute-bunny-on-the-sofa.jpg?s=612x612&w=0&k=20&c=zVo7bFe3k6W-jkxTRiVWN21vrsuwWwOj4ZZnpT9eQ20=', 1, NULL, 'Fast and curious rabbit', 
    (SELECT user_id FROM users WHERE username = 'owner_elena'), 
    (SELECT pettype_id FROM pet_types WHERE species = 'Rabbit')
),
(
    'Max', 'https://thumbs.dreamstime.com/b/wolfhound-dog-turkish-shepherd-man-s-friend-kangal-236669444.jpg', 6, NULL, 'Strong and protective dog', 
    (SELECT user_id FROM users WHERE username = 'owner_nikola'), 
    (SELECT pettype_id FROM pet_types WHERE species = 'Dog')
);

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_bojan'), 
    (SELECT user_id FROM users WHERE username = 'sitter_filip')
),
(
    'Confirmed', '2026-06-10', '2026-06-15', 'ul. Leninova br. 25, Skopje', 
    (SELECT user_id FROM users WHERE username = 'owner_vesna'), 
    (SELECT user_id FROM users WHERE username = 'sitter_lile')
),
(
    'Completed', '2026-07-01', '2026-07-02', 'ul. Borka Taleski br. 1, Skopje', 
    (SELECT user_id FROM users WHERE username = 'owner_marko'), 
    (SELECT user_id FROM users WHERE username = 'sitter_ana')
),
(
    'Confirmed', '2026-07-10', '2026-07-12', 'ul. Ruger Boskovik br. 16, Skopje', 
    (SELECT user_id FROM users WHERE username = 'owner_elena'), 
    (SELECT user_id FROM users WHERE username = 'sitter_stefan')
),
(
    'Completed', '2026-08-05', '2026-08-06', 'ul. Orce Nikolov br. 136, Skopje', 
    (SELECT user_id FROM users WHERE username = 'owner_nikola'), 
    (SELECT user_id FROM users WHERE username = 'sitter_ana')
),
(
    'Completed', '2026-08-15', '2026-08-16', 'ul. Jurij Gagarin br. 15, Skopje', 
    (SELECT user_id FROM users WHERE username = 'owner_marko'), 
    (SELECT user_id FROM users WHERE username = 'sitter_filip')
),
(
    'Confirmed', '2026-09-01', '2026-09-05', 'bul. ASNOM br. 47, Skopje', 
    (SELECT user_id FROM users WHERE username = 'owner_elena'), 
    (SELECT user_id FROM users WHERE username = 'sitter_lile')
);

INSERT INTO reviews (rating, comment, booking_id) VALUES
(
    5, 'Filip was great with Leo! Highly recommend.', 
    (SELECT booking_id FROM bookings WHERE address = 'bul. Partizanski Odredi br. 50, Skopje')
),
(
    4, 'Ana was wonderful with Rex, but arrived a bit late.', 
    (SELECT booking_id FROM bookings WHERE address = 'ul. Borka Taleski br. 1, Skopje')
),
(
    5, 'Max loved his time with Ana. Will book again!', 
    (SELECT booking_id FROM bookings WHERE address = 'ul. Orce Nikolov br. 136, Skopje')
),
(
    5, 'Filip is a true professional. Bela was very calm with him.', 
    (SELECT booking_id FROM bookings WHERE address = 'ul. Jurij Gagarin br. 15, 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')
),
(
    1000, 'Cash', 
    (SELECT booking_id FROM bookings WHERE address = 'ul. Borka Taleski br. 1, Skopje')
),
(
    3000, 'Credit Card', 
    (SELECT booking_id FROM bookings WHERE address = 'ul. Ruger Boskovik br. 16, Skopje')
),
(
    1500, 'Debit Card', 
    (SELECT booking_id FROM bookings WHERE address = 'ul. Orce Nikolov br. 136, Skopje')
),
(
    1200, 'Cash', 
    (SELECT booking_id FROM bookings WHERE address = 'ul. Jurij Gagarin br. 15, Skopje')
),
(
    5000, 'Credit Card', 
    (SELECT booking_id FROM bookings WHERE address = 'bul. ASNOM br. 47, Skopje')
);

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

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')
),
(
    (SELECT booking_id FROM bookings WHERE address = 'ul. Borka Taleski br. 1, Skopje'), 
    (SELECT pet_id FROM pets WHERE name = 'Rex')
),
(
    (SELECT booking_id FROM bookings WHERE address = 'ul. Ruger Boskovik br. 16, Skopje'), 
    (SELECT pet_id FROM pets WHERE name = 'Luna')
),
(
    (SELECT booking_id FROM bookings WHERE address = 'ul. Ruger Boskovik br. 16, Skopje'), 
    (SELECT pet_id FROM pets WHERE name = 'Kiko')
),
(
    (SELECT booking_id FROM bookings WHERE address = 'ul. Orce Nikolov br. 136, Skopje'), 
    (SELECT pet_id FROM pets WHERE name = 'Max')
),
(
    (SELECT booking_id FROM bookings WHERE address = 'ul. Jurij Gagarin br. 15, Skopje'), 
    (SELECT pet_id FROM pets WHERE name = 'Bela')
),
(
    (SELECT booking_id FROM bookings WHERE address = 'bul. ASNOM br. 47, Skopje'), 
    (SELECT pet_id FROM pets WHERE name = 'Luna')
);

INSERT INTO sitter_services (sitter_id, service_id) VALUES
(
    (SELECT user_id FROM users WHERE username = 'sitter_filip'), 
    (SELECT service_id FROM services WHERE type = 'Dog Walking')
),
(
    (SELECT user_id FROM users WHERE username = 'sitter_filip'), 
    (SELECT service_id FROM services WHERE type = 'Short Visit')
),
(
    (SELECT user_id FROM users WHERE username = 'sitter_lile'), 
    (SELECT service_id FROM services WHERE type = 'Overnight Stay')
),
(
    (SELECT user_id FROM users WHERE username = 'sitter_ana'), 
    (SELECT service_id FROM services WHERE type = 'Dog Walking')
),
(
    (SELECT user_id FROM users WHERE username = 'sitter_ana'), 
    (SELECT service_id FROM services WHERE type = 'Short Visit')
),
(
    (SELECT user_id FROM users WHERE username = 'sitter_stefan'), 
    (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')
),
(
    (SELECT booking_id FROM bookings WHERE address = 'ul. Borka Taleski br. 1, Skopje'), 
    (SELECT service_id FROM services WHERE type = 'Dog Walking')
),
(
    (SELECT booking_id FROM bookings WHERE address = 'ul. Ruger Boskovik br. 16, Skopje'), 
    (SELECT service_id FROM services WHERE type = 'Overnight Stay')
),
(
    (SELECT booking_id FROM bookings WHERE address = 'ul. Orce Nikolov br. 136, Skopje'), 
    (SELECT service_id FROM services WHERE type = 'Short Visit')
),
(
    (SELECT booking_id FROM bookings WHERE address = 'ul. Jurij Gagarin br. 15, Skopje'), 
    (SELECT service_id FROM services WHERE type = 'Dog Walking')
),
(
    (SELECT booking_id FROM bookings WHERE address = 'bul. ASNOM br. 47, Skopje'), 
    (SELECT service_id FROM services WHERE type = 'Overnight Stay')
);

COMMIT;