RelationlDesign: vnesuvanje_podatoci_verz2.sql

INSERT INTO Address (id, street, number, municipality, city, country) VALUES
(1, 'Makedonija', '15',  'Karposh', 'Skopje', 'North Macedonia'),
(2, 'Partizanska', '42', 'Karposh', 'Skopje', 'North Macedonia'),
(3, 'Dame Gruev', '8', 'Aerodrom', 'Skopje', 'North Macedonia'),
(4, 'Marsal Tito', '123', 'Centar','Skopje', 'North Macedonia'),
(5, 'Kej 13 Noemvri', '67', 'Centar','Skopje', 'North Macedonia'),
(6, 'Boris Kidric', '29', 'Karposh','Skopje', 'North Macedonia'),
(7, 'Ilindenska', '88', 'Karposh',  'Skopje', 'North Macedonia'),
(8, 'Goce Delcev', '156', 'Centar','Skopje', 'North Macedonia'),
(9, 'Jane Sandanski', '34', 'Centar','Skopje', 'North Macedonia'),
(10, 'Krste Misirkov', '71','Karposh', 'Skopje', 'North Macedonia'),
(11, 'Cvetan Dimov', '45', 'Aerodrom','Skopje', 'North Macedonia'),
(12, 'Vasil Glavinov', '92', 'Aerodrom','Skopje', 'North Macedonia'),
(13, 'Dimce Mircev', '18','Aerodrom', 'Skopje', 'North Macedonia'),
(14, 'Maksim Gorki', '203', 'Karposh', 'Skopje', 'North Macedonia'),
(15, 'Londska', '56','Karposh', 'Skopje', 'North Macedonia');

INSERT INTO PropertyType (id, name) VALUES
(1, 'Apartment Building'),
(2, 'Single Family House'),
(3, 'Townhouse'),
(4, 'Condominium'),
(5, 'Studio Complex');

INSERT INTO ServiceCategory (id, name) VALUES
(1, 'Plumbing'),
(2, 'Electrical'),
(3, 'HVAC'),
(4, 'Cleaning'),
(5, 'General Maintenance'),
(6, 'Pest Control'),
(7, 'Appliance Repair'),
(8, 'Painting');

INSERT INTO PaymentMethod (id, name) VALUES
(1, 'Bank Transfer'),
(2, 'Credit Card'),
(3, 'Cash'),
(4, 'PayPal'),
(5, 'Check');

INSERT INTO UserD (id, first_name, last_name, email, password_hash, date_of_birth, rating, bio, address_id) VALUES
(1, 'Marko', 'Petrovski', 'marko.petrovski@email.com', '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/lewQ5uXeBt/S.Z7r2', '1985-03-15', 4.75, 'Experienced landlord with 10+ years in property management', 1),
(2, 'Ana', 'Dimitrova', 'ana.dimitrova@email.com', '$2b$12$EIqnQwuDUrF6w3RPuDnD4u0J8nOxOJsYxm2S0eCp4TfU2FgWTVmEq', '1990-07-22', 4.50, 'Professional property manager and real estate agent', 2),
(3, 'Stefan', 'Nikolov', 'stefan.nikolov@email.com', '$2b$12$Gb7aRFtQzBKFE8FzP5vVyuKO3cA7cFe6YQ3eS8lMjR4T2sNyU5qC8', '1992-11-08', 4.20, 'Young professional looking for quality housing', 3),
(4, 'Elena', 'Stojanovic', 'elena.stojanovic@email.com', '$2b$12$Mc9pTnVuEbR7JhK2L3dF5eGtA8hNx6QyW1vS9rOlP3mZ4cB5fT7nU', '1988-05-14', 4.85, 'Reliable tenant with excellent references', 4),
(5, 'Aleksandar', 'Trajkovski', 'aleksandar.trajkovski@email.com', '$2b$12$Nf8qUwXvYcS3FjL4M5gH7dHuB9kPy8RzX2wT0pQmN6nZ5dC7eU9oV', '1983-09-30', 4.60, 'Property investor and landlord', 5),
(6, 'Milica', 'Georgievska', 'milica.georgievska@email.com', '$2b$12$Of9rVxYzZdT4GkM6N7hI8eIvC0lQz9SaY3xU1qRnO7oA6eD8fV0pW', '1994-12-03', 4.30, 'Graduate student seeking affordable accommodation', 6),
(7, 'Vladimir', 'Kostovski', 'vladimir.kostovski@email.com', '$2b$12$Pg0sWyZaAfU5HlN7O8jJ9fJwD1mRa0TbZ4yV2rSoP8pB7fE9gW1qX', '1987-04-18', 4.70, 'Family man looking for spacious rental', 7),
(8, 'Jovana', 'Mitreva', 'jovana.mitreva@email.com', '$2b$12$Qh1tXzAbBgV6ImO8P9kK0gKxE2nSb1UcA5zW3sTpQ9qC8gF0hX2rY', '1991-08-25', 4.40, 'Working professional with stable income', 8),
(9, 'Bojan', 'Stankovski', 'bojan.stankovski@email.com', '$2b$12$Ri2uYaBcChW7JnP9Q0lL1hLyF3gTc2VdB6aX4uTqR0rD9hG1iY3sZ', '1986-01-12', 4.55, 'Real estate developer and property owner', 9),
(10, 'Tamara', 'Veljanovska', 'tamara.veljanovska@email.com', '$2b$12$Sj3vZbCdDiX8KoQ0R1mM2iMyG4hUd3WeC7bY5vUrS1sE0iH2jZ4tA', '1993-06-07', 4.25, 'Young couple seeking modern apartment', 10);

INSERT INTO TenantProfile (id) VALUES
(3), (4), (6), (7), (8), (10);

INSERT INTO LandlordProfile (id, managed_properties_count, is_agent) VALUES
(1, 2, false),
(2, 1, true),
(5, 1, false),
(9, 1, false);

INSERT INTO Property (id, title, description, owner_id, property_type_id, address_id) VALUES
(1, 'Modern City Center Apartment Building', 'Luxury apartment building in the heart of Skopje with modern amenities and great city views', 1, 1, 11),
(2, 'Cozy Family Townhouse', 'Beautiful townhouse perfect for families with garden and parking space', 2, 3, 12),
(3, 'Downtown Studio Complex', 'Modern studio apartments ideal for young professionals and students', 5, 5, 13),
(4, 'Riverside Condominium', 'Premium condominium with river views and high-end finishes', 9, 4, 14),
(5, 'Suburban Family House', 'Spacious single-family house in quiet residential area', 1, 2, 15);

INSERT INTO Unit (id, unit_number, floor, bedrooms, bathrooms, area_sq_m, rent_amount, property_id) VALUES
(1, '101', 1, 2, 1, 65.50, 350.00, 1),
(2, '102', 1, 1, 1, 45.00, 280.00, 1),
(3, '201', 2, 3, 2, 85.75, 450.00, 1),
(4, '202', 2, 2, 1, 70.25, 380.00, 1),
(5, '301', 3, 1, 1, 50.00, 300.00, 1),
(6, 'A', 1, 3, 2, 120.00, 600.00, 2),
(7, 'B', 1, 2, 1, 95.50, 500.00, 2),
(8, 'S01', 1, 0, 1, 35.00, 220.00, 3),
(9, 'S02', 1, 0, 1, 30.00, 200.00, 3),
(10, 'S03', 2, 0, 1, 40.00, 250.00, 3),
(11, '1A', 1, 2, 2, 75.00, 550.00, 4),
(12, '2A', 2, 3, 2, 90.00, 650.00, 4),
(13, 'Main', 1, 4, 3, 150.00, 800.00, 5);

INSERT INTO PropertyImage (id, image, property_id) VALUES
(1, '/uploads/properties/prop_1_img_001.jpg', 1),
(2, '/uploads/properties/prop_1_img_002.jpg', 1),
(3, '/uploads/properties/prop_1_img_003.jpg', 1),
(4, '/uploads/properties/prop_2_img_001.jpg', 2),
(5, '/uploads/properties/prop_2_img_002.jpg', 2),
(6, '/uploads/properties/prop_3_img_001.jpg', 3),
(7, '/uploads/properties/prop_3_img_002.jpg', 3),
(8, '/uploads/properties/prop_4_img_001.jpg', 4),
(9, '/uploads/properties/prop_4_img_002.jpg', 4),
(10, '/uploads/properties/prop_5_img_001.jpg', 5);

INSERT INTO Listing (id, title, available_from, available_to, status, description, unit_id) VALUES
(1, 'Trosoben stan so pogled kon Vodno', '2025-07-01', '2026-06-30', true, 'Beautiful 2-bedroom apartment with city view', 1),
(2, 'Ednosoben stan', '2025-06-15', '2026-06-14', true, 'Cozy 1-bedroom apartment perfect for single professional', 2),
(3, 'Karpos cetirisoben stan', '2025-08-01', '2026-07-31', true, 'Spacious 3-bedroom apartment ideal for families', 3),
(4, 'Moderen trosoben stan', '2025-07-15', '2026-07-14', true, 'Modern 2-bedroom with updated fixtures', 4),
(5, 'Kompakten ednosoben stan', '2025-09-01', '2026-08-31', true, 'Compact 1-bedroom in prime location', 5),
(6, 'Golema kukja so dvor', '2025-06-01', '2026-05-31', true, 'Large townhouse with garden and parking', 6),
(7, 'Trosoben stan vo mirna okolina', '2025-08-15', '2026-08-14', true, '2-bedroom townhouse in family neighborhood', 7),
(8, 'Studio za studenti', '2025-07-01', '2025-12-31', true, 'Studio apartment perfect for students', 8),
(9, 'Studio za odlicna cena', '2025-06-01', '2025-11-30', true, 'Affordable studio in downtown area', 9),
(10, 'Deloven objekt vo Centar', '2025-09-01', '2026-08-31', true, 'Modern studio with great amenities', 10);

INSERT INTO Lease (id, start_date, end_date, rent_amount, deposit_amount, listing_id, tenant_id, landlord_id) VALUES
(1, '2025-01-01', '2025-12-31', 280.00, 560.00, 2, 3, 1),
(2, '2025-02-01', '2026-01-31', 600.00, 1200.00, 6, 4, 2),
(3, '2025-03-01', '2025-08-31', 200.00, 400.00, 9, 6, 5),
(4, '2025-01-15', '2026-01-14', 550.00, 1100.00, 10, 7, 9),
(5, '2025-04-01', '2026-03-31', 300.00, 600.00, 5, 8, 1);

INSERT INTO Payment (id, amount, status, payment_date, lease_id, payment_method_id) VALUES
(1, 280.00, 'completed', '2025-01-01', 1, 1),
(2, 280.00, 'completed', '2025-02-01', 1, 1),
(3, 280.00, 'completed', '2025-03-01', 1, 1),
(4, 280.00, 'completed', '2025-04-01', 1, 1),
(5, 280.00, 'completed', '2025-05-01', 1, 1),
(6, 280.00, 'completed', '2025-06-01', 1, 1),
(7, 600.00, 'completed', '2025-02-01', 2, 2),
(8, 600.00, 'completed', '2025-03-01', 2, 2),
(9, 600.00, 'completed', '2025-04-01', 2, 2),
(10, 600.00, 'completed', '2025-05-01', 2, 2),
(11, 600.00, 'pending', '2025-06-01', 2, 2),
(12, 200.00, 'completed', '2025-03-01', 3, 3),
(13, 200.00, 'completed', '2025-04-01', 3, 3),
(14, 200.00, 'completed', '2025-05-01', 3, 3),
(15, 200.00, 'overdue', '2025-06-01', 3, 3);

INSERT INTO ServiceRequest (id, description, request_date, status, lease_id, service_category_id) VALUES
(1, 'Kitchen sink is leaking and needs immediate repair', '2025-03-15', 'completed', 1, 1),
(2, 'Electrical outlet in bedroom not working', '2025-04-10', 'in_progress', 2, 2),
(3, 'Air conditioning unit making strange noises', '2025-05-20', 'pending', 4, 3),
(4, 'Regular deep cleaning service needed', '2025-04-01', 'completed', 2, 4),
(5, 'Front door lock needs replacement', '2025-06-05', 'pending', 1, 5),
(6, 'Refrigerator not cooling properly', '2025-05-28', 'in_progress', 4, 7),
(7, 'Bathroom needs repainting due to water damage', '2025-06-10', 'pending', 5, 8);

INSERT INTO MaintenanceLog (id, description, maintenance_date, service_request_id, user_id) VALUES
(1, 'Replaced kitchen sink faucet and fixed pipe connections. Issue resolved.', '2025-03-17', 1, 1),
(2, 'Professional cleaning service completed for entire unit. All surfaces sanitized.', '2025-04-03', 4,2),
(3, 'Diagnosed electrical issue - outlet needs rewiring. Parts ordered.', '2025-04-12', 2, 3),
(4, 'Inspected refrigerator - compressor issue identified. Technician scheduled.', '2025-05-30', 6, 4);

INSERT INTO Inspection (id, inspection_date, notes, lease_id, landlord_id) VALUES
(1, '2025-02-15', 'Property in excellent condition. Tenant maintaining unit very well. No issues found.', 1, 1),
(2, '2025-03-20', 'Minor wear and tear noted in kitchen area. Overall satisfactory condition. Scheduled maintenance for next month.', 2, 2),
(3, '2025-04-25', 'Unit well-maintained. Small scuff marks on walls, within normal wear limits. Tenant very cooperative.', 4, 9),
(4, '2025-05-10', 'Good overall condition. Noted water stain in bathroom ceiling - maintenance required.', 5, 1),
(5, '2025-06-01', 'Routine inspection completed. Property in good condition with normal wear and tear.', 3, 5);

INSERT INTO DocumentD (id, file_type, file_url, user_id, lease_id) VALUES
(1, 'PDF', '/uploads/documents/lease_agreements/lease_001.pdf', NULL, 1),
(2, 'PDF', '/uploads/documents/lease_agreements/lease_002.pdf', NULL, 2),
(3, 'PDF', '/uploads/documents/lease_agreements/lease_003.pdf', NULL, 3),
(4, 'PDF', '/uploads/documents/lease_agreements/lease_004.pdf', NULL, 4),
(5, 'PDF', '/uploads/documents/lease_agreements/lease_005.pdf', NULL, 5),
(6, 'JPG', '/uploads/documents/identification/id_stefan_nikolov.jpg', 3, NULL),
(7, 'PDF', '/uploads/documents/income_verification/income_elena_stojanovic.pdf', 4, NULL),
(8, 'PDF', '/uploads/documents/income_verification/income_milica_georgievska.pdf', 6, NULL),
(9, 'JPG', '/uploads/documents/identification/id_vladimir_kostovski.jpg', 7, NULL),
(10, 'PDF', '/uploads/documents/insurance/property_insurance_001.pdf', 1, NULL),
(11, 'PDF', '/uploads/documents/maintenance/maintenance_receipt_001.pdf', NULL, 1),
(12, 'PDF', '/uploads/documents/maintenance/maintenance_receipt_002.pdf', NULL, 2),
(13, 'JPG', '/uploads/documents/inspections/inspection_photos_001.jpg', 1, NULL),
(14, 'PDF', '/uploads/documents/contracts/property_management_contract.pdf', 2, NULL);

INSERT INTO MessageD (id, content, sent_at, from_user_id, to_user_id, lease_id) VALUES
(1, 'Hello Stefan, welcome to your new apartment! If you have any questions or concerns, please don''t hesitate to reach out.', '2025-01-01 10:00:00', 1, 3, 1),
(2, 'Thank you, Marko! The apartment is perfect. I really appreciate your quick response to the lease application.', '2025-01-01 15:30:00', 3, 1, 1),
(3, 'Hi Ana, I wanted to let you know that the rent payment for this month has been processed successfully.', '2025-02-01 09:15:00', 4, 2, 2),
(4, 'Thank you for the confirmation, Elena. Everything looks good on our end as well.', '2025-02-01 11:45:00', 2, 4, 2),
(5, 'Stefan, I noticed you submitted a maintenance request for the kitchen sink. I''ll have our plumber come by tomorrow morning.', '2025-03-15 14:20:00', 1, 3, 1),
(6, 'That''s great, thank you for the quick response! I''ll make sure to be available tomorrow morning.', '2025-03-15 16:10:00', 3, 1, 1),
(7, 'The sink has been repaired and is working perfectly now. Thank you for sending the plumber so quickly!', '2025-03-17 12:00:00', 3, 1, 1),
(8, 'Hi Vladimir, just a friendly reminder that your rent payment is due in 3 days. Please let me know if you need any assistance.', '2025-06-12 10:30:00', 9, 7, 4),
(9, 'Thanks for the reminder, Bojan. I''ll process the payment today.', '2025-06-12 13:45:00', 7, 9, 4),
(10, 'Milica, I hope you''re settling in well at the studio. Please remember that quiet hours are from 10 PM to 7 AM.', '2025-03-05 16:00:00', 5, 6, 3);

INSERT INTO UnitImage (id, image, unit_id) VALUES
(1, '/uploads/units/unit_1_img_001.jpg', 1),
(2, '/uploads/units/unit_1_img_002.jpg', 1),
(3, '/uploads/units/unit_1_img_003.jpg', 1),
(4, '/uploads/units/unit_2_img_001.jpg', 2),
(5, '/uploads/units/unit_2_img_002.jpg', 2),
(6, '/uploads/units/unit_3_img_001.jpg', 3),
(7, '/uploads/units/unit_3_img_002.jpg', 3),
(8, '/uploads/units/unit_4_img_001.jpg', 4),
(9, '/uploads/units/unit_4_img_002.jpg', 4),
(10, '/uploads/units/unit_5_img_001.jpg', 5);

INSERT INTO Interested (listing_id, tenant_profile_id) VALUES
(1, 4),
(2, 3),
(3, 6),
(4, 3),
(5, 4);