DDL, DML and Views: planora_dml.sql

File planora_dml.sql, 41.4 KB (added by 231035, 4 weeks ago)
Line 
1SET work_mem = '512MB';
2SET maintenance_work_mem = '1GB';
3SET synchronous_commit = OFF;
4--SET checkpoint_completion_target = 0.9;
5
6INSERT INTO countries (country_name, country_code)
7SELECT c2, c1
8FROM country_name
9offset 1
10ON CONFLICT DO NOTHING;
11
12
13DO $$
14DECLARE
15 c_total CONSTANT INT := 100000;
16 c_batch CONSTANT INT := 10000;
17 v_inserted INT := 0;
18
19 v_streets TEXT[] := ARRAY[
20 'Main Street','Oak Street','Maple Avenue','Pine Road',
21 'Cedar Lane','River Road','Lake View','Park Avenue'
22 ];
23 v_generic_cities TEXT[] := ARRAY[
24 'Capital City','Old Town','Metro City','Riverside',
25 'Hillview','Lakeside','Central City'
26 ];
27
28 v_country_ids BIGINT[];
29 v_country_codes TEXT[];
30 v_n_countries INT;
31 v_idx INT;
32 v_city TEXT;
33 v_country_code TEXT;
34 v_country_id BIGINT;
35BEGIN
36 SELECT ARRAY(SELECT country_id FROM countries ORDER BY country_id),
37 ARRAY(SELECT country_code FROM countries ORDER BY country_id)
38 INTO v_country_ids, v_country_codes;
39
40 v_n_countries := array_length(v_country_ids, 1);
41 IF v_n_countries IS NULL THEN RAISE EXCEPTION 'countries table is empty'; END IF;
42
43 WHILE v_inserted < c_total LOOP
44 INSERT INTO addresses (country_id, zip_code, street, city)
45 SELECT
46 cid,
47 cid::text || lpad(rn::text, 6, '0'),
48 v_streets[1 + floor(random() * array_length(v_streets,1))::int],
49 city
50 FROM (
51 SELECT
52 gs AS rn,
53 v_country_ids [1 + floor(random()*v_n_countries)::int] AS cid,
54 v_country_codes[1 + floor(random()*v_n_countries)::int] AS cc
55 FROM generate_series(v_inserted + 1,
56 LEAST(v_inserted + c_batch, c_total)) gs
57 ) base
58 CROSS JOIN LATERAL (
59 SELECT CASE cc
60 WHEN 'MK' THEN (ARRAY['Skopje','Ohrid','Bitola','Tetovo']) [1 + floor(random()*4)::int]
61 WHEN 'DE' THEN (ARRAY['Berlin','Munich','Hamburg','Frankfurt']) [1 + floor(random()*4)::int]
62 WHEN 'US' THEN (ARRAY['New York','Los Angeles','Chicago','Miami'])[1 + floor(random()*4)::int]
63 ELSE v_generic_cities[1 + floor(random()*array_length(v_generic_cities,1))::int]
64 END AS city
65 ) c;
66
67 v_inserted := v_inserted + c_batch;
68 RAISE NOTICE 'Addresses: % / %', LEAST(v_inserted, c_total), c_total;
69 END LOOP;
70END $$;
71
72ANALYZE addresses;
73
74
75DO $$
76DECLARE
77 batch_size INT := 50000;
78 start_id INT := 1;
79 end_id INT := 2000000;
80 fn_count BIGINT;
81 sn_count BIGINT;
82BEGIN
83 SELECT COUNT(*) INTO fn_count FROM (SELECT name FROM girls_names UNION ALL SELECT name FROM boys_names) t;
84 SELECT COUNT(*) INTO sn_count FROM surnames_200;
85
86 WHILE start_id <= end_id LOOP
87 INSERT INTO users (email, password_hash, first_name, last_name, phone, updated_at)
88 WITH all_first_names AS (
89 SELECT name, row_number() OVER (ORDER BY name) AS id FROM girls_names
90 UNION ALL
91 SELECT name, (SELECT COUNT(*) FROM girls_names) + row_number() OVER (ORDER BY name) FROM boys_names
92 ),
93 sn AS (SELECT surname, row_number() OVER (ORDER BY surname) AS id FROM surnames_200),
94 batch AS (SELECT generate_series(start_id, LEAST(start_id + batch_size - 1, end_id)) AS rn)
95 SELECT
96 lower(fn.name || '.' || sn.surname || b.rn || '@example.com'),
97 'test123',
98 fn.name,
99 sn.surname,
100 '+389' || lpad(b.rn::text, 8, '0'),
101 CURRENT_TIMESTAMP
102 FROM batch b
103 JOIN all_first_names fn ON fn.id = ((b.rn * 17) % fn_count) + 1
104 JOIN sn ON sn.id = ((b.rn * 31) % sn_count) + 1;
105
106 RAISE NOTICE 'Users batch % – %', start_id, LEAST(start_id + batch_size - 1, end_id);
107 start_id := start_id + batch_size;
108 END LOOP;
109END $$;
110
111ANALYZE users;
112
113
114INSERT INTO admins (user_id)
115SELECT user_id FROM users ORDER BY random() LIMIT 300
116ON CONFLICT (user_id) DO NOTHING;
117
118
119INSERT INTO guests (user_id)
120SELECT u.user_id
121FROM users u
122WHERE NOT EXISTS (
123 SELECT 1
124 FROM admins a
125 WHERE a.user_id = u.user_id
126)
127AND NOT EXISTS (
128 SELECT 1
129 FROM hosts h
130 WHERE h.user_id = u.user_id
131)
132ON CONFLICT (user_id) DO NOTHING;
133
134ANALYZE guests;
135
136
137WITH selected_users AS (
138 SELECT user_id
139 FROM users u
140 WHERE NOT EXISTS (SELECT 1 FROM host_applications ha WHERE ha.user_id = u.user_id)
141 ORDER BY random()
142 LIMIT 20000
143),
144randomized AS (
145 SELECT user_id,
146 (ARRAY['PENDING','UNDER_REVIEW','APPROVED','REJECTED'])[floor(random()*4+1)::int] AS status,
147 (CURRENT_DATE - floor(random()*365)::int)::date AS application_date
148 FROM selected_users
149),
150prepared AS (
151 SELECT r.user_id,
152 r.application_date,
153 r.status,
154 CASE WHEN r.status IN ('APPROVED','REJECTED')
155 THEN (SELECT admin_id FROM admins ORDER BY random() LIMIT 1) END AS reviewed_by_admin_id,
156 CASE WHEN r.status IN ('APPROVED','REJECTED')
157 THEN r.application_date + floor(random()*30+1)::int END AS review_date,
158 CASE WHEN r.status = 'REJECTED'
159 THEN (ARRAY[
160 'Incomplete profile information',
161 'Missing required verification documents',
162 'Does not meet hosting criteria',
163 'Policy compliance issue',
164 'Previous account concerns',
165 'Application details could not be verified'
166 ])[floor(random()*6+1)::int] END AS rejection_reason
167 FROM randomized r
168)
169INSERT INTO host_applications
170 (user_id, application_date, status, reviewed_by_admin_id, review_date, rejection_reason)
171SELECT user_id, application_date, status, reviewed_by_admin_id, review_date, rejection_reason
172FROM prepared;
173
174
175
176INSERT INTO hosts (user_id, application_id)
177SELECT ha.user_id, ha.application_id
178FROM host_applications ha
179WHERE ha.status = 'APPROVED'
180ORDER BY random()
181LIMIT 30000
182ON CONFLICT (user_id) DO NOTHING;
183
184ANALYZE hosts;
185
186
187
188INSERT INTO listing_types (type_name, description) VALUES
189('Apartment', 'Self-contained residential unit in a building or complex'),
190('House', 'Standalone residential property with private entrance'),
191('Studio', 'Small open-layout apartment with combined living space'),
192('Villa', 'Luxury standalone property, often in scenic or coastal areas'),
193('Cabin', 'Small house located in rural or nature areas'),
194('Hotel Room', 'Private room inside a hotel with standard hotel services'),
195('Hostel Bed', 'Shared accommodation bed in a dormitory-style room'),
196('Guesthouse', 'Private home or small property offering guest accommodation')
197ON CONFLICT (type_name) DO NOTHING;
198
199
200INSERT INTO payment_methods (method_name, description) VALUES
201('Credit Card', 'Payment via Visa, Mastercard, or other major credit cards.'),
202('Debit Card', 'Payment directly from a linked bank account via debit card.'),
203('PayPal', 'Online payment through a PayPal account.'),
204('Bank Transfer', 'Direct bank-to-bank wire or ACH transfer.'),
205('Apple Pay', 'Contactless payment using Apple Pay on supported devices.'),
206('Google Pay', 'Contactless payment using Google Pay on supported devices.'),
207('Stripe', 'Online card processing via the Stripe payment gateway.'),
208('Cash', 'Payment made in cash upon arrival or check-in.'),
209('Cryptocurrency', 'Payment made using Bitcoin, Ethereum, or other cryptocurrencies.'),
210('Gift Card', 'Payment using a prepaid gift card or voucher code.')
211ON CONFLICT (method_name) DO NOTHING;
212
213
214DO $$
215DECLARE
216 c_total CONSTANT INT := 50000;
217 c_batch CONSTANT INT := 5000;
218 v_inserted INT := 0;
219 v_names TEXT[] := ARRAY[
220 'Wi-Fi','Air Conditioning','Heating','TV','Smart TV','Netflix Access','Mini Fridge',
221 'Coffee Machine','Electric Kettle','Microwave','Hair Dryer','Iron','Ironing Board',
222 'Wardrobe','Desk','Work Chair','Safe','Balcony','Sea View','Mountain View','City View',
223 'Private Bathroom','Bathtub','Shower Cabin','King Bed','Queen Bed','Single Bed',
224 'Sofa Bed','Blackout Curtains','Soundproofing','Towels','Bed Linen','Toiletries',
225 'Slippers','Bathrobe','Room Service','Daily Housekeeping','Private Entrance','Fireplace',
226 'Jacuzzi','Swimming Pool','Private Pool','Shared Pool','Garden','Terrace','Patio',
227 'BBQ Area','Parking','Free Parking','Valet Parking','EV Charging Station','Elevator',
228 'Gym','Spa','Sauna','Steam Room','Restaurant','Bar','Breakfast','Airport Shuttle',
229 'Laundry Service','Dry Cleaning','Pet Friendly','24/7 Reception','Security Cameras',
230 'Wheelchair Access','Conference Room','Meeting Room','Playground','Kids Area',
231 'Bicycle Rental','Car Rental Desk','Luggage Storage','Concierge Service','Smoking Area',
232 'Non-Smoking Rooms','Shared Kitchen','Full Kitchen','Dishwasher','Washing Machine',
233 'Dryer','Hot Tub','Library','Business Center','Game Room','Tennis Court',
234 'Basketball Court','Beach Access','Private Beach','Ski Storage','Ski-in/Ski-out',
235 'Rooftop Access','Picnic Area','Outdoor Furniture','CCTV','First Aid Kit',
236 'Smoke Detector','Carbon Monoxide Detector','Baby Crib','High Chair'
237 ];
238 v_room_descs TEXT[] := ARRAY[
239 'Comfort feature available inside the room.',
240 'Designed to improve guest convenience and stay quality.',
241 'Common room-level feature for added comfort.',
242 'Useful in-room amenity frequently requested by guests.',
243 'Enhances privacy, relaxation, or functionality of the room.',
244 'Suitable for short and extended stays.',
245 'Standard room service or equipment option.',
246 'Added for convenience and better guest experience.'
247 ];
248 v_prop_descs TEXT[] := ARRAY[
249 'Shared or property-wide feature available to guests.',
250 'Amenity provided at the property level.',
251 'Designed to improve the overall guest experience.',
252 'Useful facility available within the property.',
253 'Common property feature for comfort, access, or recreation.',
254 'Supports a more convenient and enjoyable stay.',
255 'General facility or service offered by the property.',
256 'Available to some or all guests depending on booking terms.'
257 ];
258BEGIN
259 WHILE v_inserted < c_total LOOP
260 INSERT INTO amenities (amenity_name, amenity_type, description, is_included, price)
261 SELECT
262 v_names[1 + floor(random() * array_length(v_names,1))::int] || ' ' || gs,
263 x.amenity_type,
264 CASE WHEN x.amenity_type = 'ROOM'
265 THEN v_room_descs[1 + floor(random() * array_length(v_room_descs,1))::int]
266 ELSE v_prop_descs[1 + floor(random() * array_length(v_prop_descs,1))::int]
267 END,
268 x.is_included,
269 CASE WHEN x.is_included THEN NULL ELSE round((5 + random()*195)::numeric, 2) END
270 FROM (
271 SELECT gs,
272 CASE WHEN random() < 0.5 THEN 'ROOM' ELSE 'PROPERTY' END AS amenity_type,
273 random() < 0.7 AS is_included
274 FROM generate_series(1, LEAST(c_batch, c_total - v_inserted)) gs
275 ) x;
276
277 v_inserted := v_inserted + LEAST(c_batch, c_total - v_inserted);
278 RAISE NOTICE 'Amenities: % / %', v_inserted, c_total;
279 END LOOP;
280END $$;
281
282ANALYZE amenities;
283
284
285DO $$
286DECLARE
287 c_total CONSTANT INT := 250000;
288 c_batch CONSTANT INT := 10000;
289 v_inserted INT := 0;
290BEGIN
291 WHILE v_inserted < c_total LOOP
292 INSERT INTO properties
293 (host_id, listing_type_id, address_id, title, description,
294 base_price, max_guests, status, created_at)
295 SELECT
296 h.user_id,
297 lt.listing_type_id,
298 a.address_id,
299 lt.type_name || ' in ' || a.city,
300 'Modern ' || lt.type_name || ' located in ' || a.city,
301 round((30 + random()*400)::numeric, 2),
302 1 + floor(random()*10)::int,
303 (ARRAY['ACTIVE','ACTIVE','ACTIVE','INACTIVE'])[floor(random()*4+1)::int],
304 now() - (random() * interval '5 years')
305 FROM generate_series(1, LEAST(c_batch, c_total - v_inserted))
306 JOIN LATERAL (SELECT user_id FROM hosts ORDER BY random() LIMIT 1) h ON true
307 JOIN LATERAL (SELECT listing_type_id, type_name
308 FROM listing_types ORDER BY random() LIMIT 1) lt ON true
309 JOIN LATERAL (SELECT address_id, city FROM addresses ORDER BY random() LIMIT 1) a ON true;
310
311 v_inserted := v_inserted + LEAST(c_batch, c_total - v_inserted);
312 RAISE NOTICE 'Properties: % / %', v_inserted, c_total;
313 END LOOP;
314END $$;
315
316ANALYZE properties;
317
318
319INSERT INTO room_types (type_name, description) VALUES
320('Single Room', 'A room with one single bed, suitable for one guest.'),
321('Double Room', 'A room with one double or queen bed, for one or two guests.'),
322('Twin Room', 'A room with two single beds, ideal for two guests.'),
323('Triple Room', 'A room for three guests with three singles or a double and a single.'),
324('Quad Room', 'A room for four guests with multiple bed configurations.'),
325('Suite', 'A luxury room with separate living and sleeping areas.'),
326('Junior Suite', 'A larger-than-standard room with a partial sitting area.'),
327('Studio', 'An open-plan room with a kitchenette, suitable for longer stays.'),
328('Family Room', 'A spacious room designed for families with multiple sleeping areas.'),
329('Dormitory Room', 'A shared room with multiple beds, common in hostels.'),
330('Villa', 'A standalone or semi-standalone private unit with premium amenities.'),
331('Bungalow', 'A ground-floor private unit, often found in resort settings.'),
332('Penthouse', 'A top-floor luxury unit with premium views and amenities.'),
333('Accessible Room', 'A room designed for guests with mobility or accessibility needs.'),
334('Connecting Room', 'A room with a door connecting to an adjacent room, ideal for groups.')
335ON CONFLICT (type_name) DO NOTHING;
336
337
338DO $$
339DECLARE
340 c_prop_batch CONSTANT INT := 10000;
341 v_offset INT := 0;
342 v_total_props INT;
343 v_prop_ids BIGINT[];
344 v_rooms_inserted BIGINT := 0;
345 v_rows_done INT;
346
347 v_room_type_ids BIGINT[];
348
349 v_prefixes TEXT[] := ARRAY[
350 'Standard Room','Deluxe Room','Superior Room','Suite',
351 'Junior Suite','Family Room','Twin Room','Double Room',
352 'Single Room','Studio'
353 ];
354 v_descs TEXT[] := ARRAY[
355 'Bright and airy with city views.',
356 'Cozy room with garden view.',
357 'Spacious room with private bathroom.',
358 'Quiet room on upper floor.',
359 'Well-appointed room with all amenities.',
360 'Charming room with vintage decor.',
361 'Modern room with smart TV and fast Wi-Fi.',
362 NULL
363 ];
364 v_statuses TEXT[] := ARRAY[
365 'ACTIVE','ACTIVE','ACTIVE','ACTIVE','ACTIVE','ACTIVE','ACTIVE',
366 'INACTIVE','MAINTENANCE','UNAVAILABLE'
367 ];
368BEGIN
369 SELECT ARRAY(SELECT room_type_id FROM room_types) INTO v_room_type_ids;
370 SELECT COUNT(*) INTO v_total_props FROM properties;
371
372 WHILE v_offset < v_total_props LOOP
373 SELECT ARRAY(
374 SELECT property_id FROM properties
375 ORDER BY property_id
376 LIMIT c_prop_batch
377 OFFSET v_offset
378 ) INTO v_prop_ids;
379
380 EXIT WHEN array_length(v_prop_ids, 1) IS NULL;
381
382 INSERT INTO rooms
383 (property_id, room_type_id, room_name, capacity, price_per_night,
384 description, status, extra_capacity, extra_guest_price)
385 SELECT
386 pid,
387 v_room_type_ids[1 + floor(random() * array_length(v_room_type_ids,1))::int],
388 v_prefixes[1 + floor(random() * array_length(v_prefixes,1))::int] || ' ' || rn,
389 1 + floor(random() * 6)::int,
390 round((30 + random() * 470)::numeric, 2),
391 v_descs[1 + floor(random() * array_length(v_descs,1))::int],
392 v_statuses[1 + floor(random() * array_length(v_statuses,1))::int],
393 floor(random() * 4)::int,
394 CASE WHEN random() < 0.4 THEN NULL
395 ELSE round((10 + random()*90)::numeric, 2) END
396 FROM (
397 SELECT pid, generate_series(1, 1 + floor(random()*4)::int) AS rn
398 FROM unnest(v_prop_ids) AS pid
399 ) expanded;
400
401 GET DIAGNOSTICS v_rows_done = ROW_COUNT;
402 v_rooms_inserted := v_rooms_inserted + v_rows_done;
403
404 v_offset := v_offset + c_prop_batch;
405 RAISE NOTICE 'Rooms — properties offset %, rooms so far %',
406 v_offset, v_rooms_inserted;
407 END LOOP;
408
409 RAISE NOTICE '=== Done. % rooms inserted. ===', v_rooms_inserted;
410END $$;
411
412ANALYZE rooms;
413
414
415DO $$
416DECLARE
417 c_target CONSTANT INT := 500000;
418 c_batch CONSTANT INT := 10000;
419 v_inserted INT := 0;
420 v_room_ids BIGINT[];
421BEGIN
422 SELECT ARRAY(SELECT room_id FROM rooms ORDER BY random() LIMIT 200000)
423 INTO v_room_ids;
424
425 WHILE v_inserted < c_target LOOP
426 INSERT INTO availability_blocks
427 (room_id, blocked_from_date, blocked_from_time,
428 blocked_to_date, blocked_to_time, description)
429 SELECT
430 v_room_ids[1 + floor(random() * array_length(v_room_ids,1))::int],
431 fd,
432 fd::timestamp + make_interval(hours => floor(random()*23)::int),
433 fd + dur,
434 (fd + dur)::timestamp
435 + make_interval(hours => floor(random()*23)::int,
436 mins => floor(random()*59)::int),
437 (ARRAY[
438 'Owner block','Maintenance window','Private event',
439 'Renovation period','Seasonal closure', NULL
440 ])[1 + floor(random()*6)::int]
441 FROM (
442 SELECT gs,
443 (CURRENT_DATE + floor(random()*365)::int - 180)::date AS fd,
444 (1 + floor(random()*13)::int) AS dur
445 FROM generate_series(1, LEAST(c_batch, c_target - v_inserted)) gs
446 ) x;
447
448 v_inserted := v_inserted + LEAST(c_batch, c_target - v_inserted);
449 RAISE NOTICE 'Availability blocks: % / %', v_inserted, c_target;
450 END LOOP;
451END $$;
452
453
454
455DO $$
456DECLARE
457 c_total CONSTANT INT := 50000;
458 c_batch CONSTANT INT := 5000;
459 v_inserted INT := 0;
460 v_host_ids BIGINT[];
461 v_n_hosts INT;
462 v_titles TEXT[] := ARRAY[
463 'Summer Sale','Weekend Special','Early Bird Offer','Last Minute Deal',
464 'Holiday Discount','Loyalty Reward','New Guest Promo','Extended Stay Offer',
465 'Flash Sale','Family Package'
466 ];
467 v_descriptions TEXT[] := ARRAY[
468 'Special limited-time discount for selected bookings.',
469 'Save more on qualifying reservations during the campaign period.',
470 'Promotional offer created to boost bookings and occupancy.',
471 'Exclusive deal available for guests who meet the listed conditions.',
472 'Temporary host discount for higher visibility and conversions.',
473 'Discount valid only during the specified booking window.',
474 'Seasonal promotion for selected accommodation units.',
475 'Incentive for direct and repeat bookings.',
476 'Marketing offer intended to increase reservations.',
477 'Special campaign created by the host for targeted guests.'
478 ];
479BEGIN
480 SELECT ARRAY(SELECT user_id FROM hosts ORDER BY user_id) INTO v_host_ids;
481 v_n_hosts := array_length(v_host_ids, 1);
482 IF v_n_hosts IS NULL THEN RAISE EXCEPTION 'hosts table is empty.'; END IF;
483
484 WHILE v_inserted < c_total LOOP
485 INSERT INTO discounts
486 (host_id, code, title, description, discount_type, discount_value,
487 valid_from, valid_to, is_active)
488 SELECT
489 v_host_ids[1 + floor(random()*v_n_hosts)::int],
490 'DISC-' || upper(substr(md5(random()::text || clock_timestamp()::text || gs::text), 1, 12)),
491 v_titles [1 + floor(random()*array_length(v_titles,1))::int],
492 v_descriptions[1 + floor(random()*array_length(v_descriptions,1))::int],
493 x.discount_type,
494 CASE WHEN x.discount_type = 'PERCENTAGE'
495 THEN round((5 + random()*45 )::numeric, 2)
496 ELSE round((5 + random()*195)::numeric, 2) END,
497 x.valid_from,
498 x.valid_to,
499 CASE WHEN x.valid_to < CURRENT_DATE THEN FALSE
500 WHEN random() < 0.85 THEN TRUE
501 ELSE FALSE END
502 FROM (
503 SELECT gs,
504 CASE WHEN random() < 0.5 THEN 'PERCENTAGE' ELSE 'FIXED' END AS discount_type,
505 vf AS valid_from,
506 vf + (10 + floor(random()*180)::int) AS valid_to
507 FROM generate_series(1, LEAST(c_batch, c_total - v_inserted)) gs
508 CROSS JOIN LATERAL (
509 SELECT (CURRENT_DATE - floor(random()*180)::int)::date AS vf
510 ) d
511 ) x;
512
513 v_inserted := v_inserted + LEAST(c_batch, c_total - v_inserted);
514 RAISE NOTICE 'Discounts: % / %', v_inserted, c_total;
515 END LOOP;
516END $$;
517
518ANALYZE discounts;
519
520
521DO $$
522DECLARE
523 c_total CONSTANT BIGINT := 10000000;
524 c_batch CONSTANT INT := 500000;
525 v_inserted BIGINT := 0;
526 v_guest_ids BIGINT[];
527 v_room_ids BIGINT[];
528 v_n_guests INT;
529 v_n_rooms INT;
530BEGIN
531 SELECT ARRAY(SELECT guest_id FROM guests ORDER BY random() LIMIT 500000)
532 INTO v_guest_ids;
533 SELECT ARRAY(SELECT room_id FROM rooms ORDER BY random())
534 INTO v_room_ids;
535
536 v_n_guests := array_length(v_guest_ids, 1);
537 v_n_rooms := array_length(v_room_ids, 1);
538 IF v_n_guests IS NULL THEN RAISE EXCEPTION 'No guests found.'; END IF;
539 IF v_n_rooms IS NULL THEN RAISE EXCEPTION 'No rooms found.'; END IF;
540
541 WHILE v_inserted < c_total LOOP
542 INSERT INTO bookings
543 (guest_id, room_id, check_in_date, check_out_date,
544 guests_count, total_price, booking_status, booked_at)
545 SELECT
546 v_guest_ids[1 + floor(random()*v_n_guests)::int],
547 v_room_ids [1 + floor(random()*v_n_rooms )::int],
548 ci,
549 ci + (1 + floor(random()*13)::int),
550 1 + floor(random()*6)::int,
551 round((20 + random()*980)::numeric, 2),
552 (ARRAY[
553 'CONFIRMED','CONFIRMED','CONFIRMED','CONFIRMED',
554 'COMPLETED','COMPLETED','COMPLETED',
555 'PENDING','PENDING',
556 'CANCELLED','NO_SHOW'
557 ])[1 + floor(random()*11)::int],
558 now() - (random() * interval '3 years')
559 FROM (
560 SELECT gs,
561 (CURRENT_DATE - floor(random()*1000)::int)::date AS ci
562 FROM generate_series(1, LEAST(c_batch, c_total - v_inserted)) gs
563 ) x;
564
565 v_inserted := v_inserted + LEAST(c_batch, c_total - v_inserted);
566 RAISE NOTICE 'Bookings: % / %', v_inserted, c_total;
567 END LOOP;
568END $$;
569
570ANALYZE bookings;
571
572
573DO $$
574DECLARE
575 c_batch CONSTANT INT := 50000;
576 v_inserted BIGINT := 0;
577 v_rows_done INT;
578 v_method_ids BIGINT[];
579 v_n_methods INT;
580 v_statuses TEXT[] := ARRAY[
581 'PAID','PAID','PAID','PAID','PAID','PAID',
582 'PENDING','PENDING','PENDING',
583 'FAILED','FAILED',
584 'REFUNDED','REFUNDED',
585 'PARTIALLY_REFUNDED'
586 ];
587 v_last_id BIGINT := 0;
588BEGIN
589 SELECT ARRAY(SELECT payment_method_id FROM payment_methods ORDER BY payment_method_id)
590 INTO v_method_ids;
591 v_n_methods := array_length(v_method_ids, 1);
592
593 LOOP
594 INSERT INTO payments
595 (booking_id, payment_method_id, amount, payment_status, paid_at)
596 SELECT
597 b.booking_id,
598 v_method_ids[1 + floor(random()*v_n_methods)::int],
599 round((20 + random()*4980)::numeric, 2),
600 s.stat,
601 CASE WHEN s.stat = 'PAID'
602 THEN now() - (random() * interval '2 years')
603 ELSE NULL END
604 FROM (
605 SELECT b2.booking_id,
606 v_statuses[1 + floor(random()*array_length(v_statuses,1))::int] AS stat
607 FROM bookings b2
608 LEFT JOIN payments p ON p.booking_id = b2.booking_id
609 WHERE b2.booking_id > v_last_id
610 AND p.booking_id IS NULL
611 ORDER BY b2.booking_id
612 LIMIT c_batch
613 ) s
614 JOIN bookings b ON b.booking_id = s.booking_id;
615
616 GET DIAGNOSTICS v_rows_done = ROW_COUNT;
617 EXIT WHEN v_rows_done = 0;
618
619 SELECT MAX(p.booking_id) INTO v_last_id
620 FROM payments p
621 WHERE p.booking_id > v_last_id;
622
623 v_inserted := v_inserted + v_rows_done;
624 RAISE NOTICE 'Payments inserted: %', v_inserted;
625 END LOOP;
626
627 RAISE NOTICE '=== Done. % payments inserted. ===', v_inserted;
628END $$;
629
630ANALYZE payments;
631
632
633DO $$
634DECLARE
635 c_batch CONSTANT INT := 50000;
636 v_inserted BIGINT := 0;
637 v_rows_done INT;
638 v_last_id BIGINT := 0;
639
640 v_positive_comments TEXT[] := ARRAY[
641 'Absolutely wonderful stay, highly recommend!',
642 'Great location and very clean property.',
643 'The host was super responsive and helpful.',
644 'Amazing views and comfortable beds.',
645 'Good value for money, would return.',
646 'Everything as described, no surprises.',
647 'Lovely property, perfect for a family trip.',
648 'Very modern and well-equipped kitchen.',
649 'Peaceful retreat, exactly what we needed.'
650 ];
651
652 v_neutral_comments TEXT[] := ARRAY[
653 'Nice place but a bit noisy at night.',
654 'The stay was okay, but nothing special.',
655 'Property was acceptable for a short stay.',
656 'Decent location, but some things could be improved.',
657 'Average experience overall.',
658 NULL
659 ];
660
661 v_negative_comments TEXT[] := ARRAY[
662 'The property was not as clean as expected.',
663 'Very noisy at night and difficult to sleep.',
664 'The host was slow to respond.',
665 'The room was smaller than described.',
666 'Not good value for the price.',
667 'Several amenities were missing or not working.',
668 'The stay was disappointing overall.',
669 'Would not choose this property again.',
670 'The property needs better maintenance.',
671 'Uncomfortable beds and poor cleanliness.'
672 ];
673BEGIN
674 LOOP
675 WITH batch_bookings AS (
676 SELECT
677 b.booking_id,
678 b.guest_id,
679 rm.property_id,
680 b.booked_at,
681 1 + floor(random() * 5)::int AS rating
682 FROM bookings b
683 JOIN rooms rm ON rm.room_id = b.room_id
684 WHERE b.booking_status = 'COMPLETED'
685 AND b.booking_id > v_last_id
686 AND NOT EXISTS (
687 SELECT 1
688 FROM reviews r
689 WHERE r.booking_id = b.booking_id
690 )
691 ORDER BY b.booking_id
692 LIMIT c_batch
693 )
694 INSERT INTO reviews
695 (booking_id, guest_id, property_id, rating, comment, created_at)
696 SELECT
697 bb.booking_id,
698 bb.guest_id,
699 bb.property_id,
700 bb.rating,
701 CASE
702 WHEN bb.rating IN (1, 2) THEN
703 v_negative_comments[
704 1 + floor(random() * array_length(v_negative_comments, 1))::int
705 ]
706
707 WHEN bb.rating = 3 THEN
708 v_neutral_comments[
709 1 + floor(random() * array_length(v_neutral_comments, 1))::int
710 ]
711
712 ELSE
713 v_positive_comments[
714 1 + floor(random() * array_length(v_positive_comments, 1))::int
715 ]
716 END AS comment,
717 bb.booked_at + (random() * interval '30 days') AS created_at
718 FROM batch_bookings bb;
719
720 GET DIAGNOSTICS v_rows_done = ROW_COUNT;
721
722 EXIT WHEN v_rows_done = 0;
723
724 SELECT MAX(r.booking_id)
725 INTO v_last_id
726 FROM reviews r
727 WHERE r.booking_id > v_last_id;
728
729 v_inserted := v_inserted + v_rows_done;
730
731 RAISE NOTICE 'Reviews inserted so far: %', v_inserted;
732 END LOOP;
733
734 RAISE NOTICE '=== Done. Total reviews inserted: % ===', v_inserted;
735END $$;
736
737ANALYZE reviews;
738
739
740WITH counts AS (
741 SELECT (SELECT COUNT(*) FROM users) AS u_count,
742 (SELECT COUNT(*) FROM properties) AS p_count
743),
744numbered_users AS (
745 SELECT user_id, row_number() OVER (ORDER BY user_id) AS rn FROM users
746),
747numbered_properties AS (
748 SELECT property_id, row_number() OVER (ORDER BY property_id) AS rn FROM properties
749)
750INSERT INTO favorite_listings (user_id, property_id, created_at)
751SELECT
752 u.user_id,
753 p.property_id,
754 now() - (random() * interval '365 days')
755FROM generate_series(1, 10000000) g
756CROSS JOIN counts
757JOIN numbered_users u ON u.rn = (g % counts.u_count) + 1
758JOIN numbered_properties p ON p.rn = ((g * 17) % counts.p_count) + 1
759ON CONFLICT (user_id, property_id) DO NOTHING;
760
761DELETE FROM favorite_listings
762WHERE user_id = -1;
763
764
765
766DO $$
767DECLARE
768 c_total CONSTANT INT := 5000000;
769 c_batch CONSTANT INT := 50000;
770 v_inserted INT := 0;
771 v_user_ids BIGINT[];
772 v_n_users INT;
773 v_messages TEXT[] := ARRAY[
774 'Your booking has been confirmed.',
775 'Your payment was successfully processed.',
776 'A new review has been posted for your property.',
777 'Your reservation has been cancelled.',
778 'Reminder: your check-in is tomorrow.',
779 'Your refund has been initiated.',
780 'You have a new message from your host.',
781 'You have a new message from your guest.',
782 'Your listing has been approved and is now active.',
783 'A guest has requested to book your property.',
784 'Your booking request has been declined.',
785 'Payment failed. Please update your payment method.',
786 'Your account password was changed successfully.',
787 'A special offer is available for your upcoming stay.',
788 'Your loyalty points have been updated.',
789 'Check-out reminder: please leave the property by 11:00 AM.',
790 'Your host has left you a review.',
791 'Your guest has left you a review.',
792 'Maintenance scheduled for your property on the selected dates.',
793 'Your support ticket has been resolved.'
794 ];
795BEGIN
796 SELECT ARRAY(SELECT user_id FROM users ORDER BY random() LIMIT 100000)
797 INTO v_user_ids;
798 v_n_users := array_length(v_user_ids, 1);
799
800 WHILE v_inserted < c_total LOOP
801 INSERT INTO notifications (user_id, message, sent_at, is_read)
802 SELECT
803 v_user_ids[1 + floor(random()*v_n_users)::int],
804 v_messages[1 + floor(random()*array_length(v_messages,1))::int],
805 now() - (random() * interval '3 years'),
806 random() < 0.6
807 FROM generate_series(1, LEAST(c_batch, c_total - v_inserted));
808
809 v_inserted := v_inserted + LEAST(c_batch, c_total - v_inserted);
810 RAISE NOTICE 'Notifications: % / %', v_inserted, c_total;
811 END LOOP;
812END $$;
813
814
815INSERT INTO cancellation_policies
816 (property_id, policy_name, description, refund_percentage, days_before_checking)
817SELECT
818 p.property_id,
819 (ARRAY['Flexible','Moderate','Strict','Non-refundable'])[floor(random()*4+1)::int],
820 CASE floor(random()*4)::int
821 WHEN 0 THEN 'Full refund if cancelled early'
822 WHEN 1 THEN 'Partial refund depending on timing'
823 WHEN 2 THEN 'Limited refund window applies'
824 ELSE 'No refunds allowed'
825 END,
826 CASE floor(random()*4)::int WHEN 0 THEN 100 WHEN 1 THEN 50 WHEN 2 THEN 25 ELSE 0 END,
827 CASE floor(random()*4)::int WHEN 0 THEN 1 WHEN 1 THEN 3 WHEN 2 THEN 7 ELSE 14 END
828FROM properties p
829LEFT JOIN cancellation_policies cp ON cp.property_id = p.property_id
830WHERE cp.property_id IS NULL;
831
832
833
834DO $$
835DECLARE
836 c_prop_batch CONSTANT INT := 5000;
837 v_offset INT := 0;
838 v_total_props INT;
839 v_prop_ids BIGINT[];
840 v_amenity_ids BIGINT[];
841 v_n_amenities INT;
842BEGIN
843 SELECT ARRAY(SELECT amenity_id FROM amenities WHERE amenity_type = 'PROPERTY')
844 INTO v_amenity_ids;
845 v_n_amenities := array_length(v_amenity_ids, 1);
846 SELECT COUNT(*) INTO v_total_props FROM properties;
847
848 WHILE v_offset < v_total_props LOOP
849 SELECT ARRAY(
850 SELECT property_id FROM properties
851 ORDER BY property_id
852 LIMIT c_prop_batch OFFSET v_offset
853 ) INTO v_prop_ids;
854 EXIT WHEN array_length(v_prop_ids, 1) IS NULL;
855
856 INSERT INTO property_amenities (property_id, amenity_id)
857 SELECT DISTINCT pid,
858 v_amenity_ids[1 + floor(random()*v_n_amenities)::int]
859 FROM (
860 SELECT pid, generate_series(1, 2 + floor(random()*3)::int) AS n
861 FROM unnest(v_prop_ids) AS pid
862 ) x
863 ON CONFLICT (property_id, amenity_id) DO NOTHING;
864
865 v_offset := v_offset + c_prop_batch;
866 RAISE NOTICE 'Property amenities — properties offset %', v_offset;
867 END LOOP;
868END $$;
869
870
871DO $$
872DECLARE
873 c_room_batch CONSTANT INT := 5000;
874 v_offset INT := 0;
875 v_total_rooms INT;
876 v_room_ids BIGINT[];
877 v_amenity_ids BIGINT[];
878 v_n_amenities INT;
879BEGIN
880 SELECT ARRAY(SELECT amenity_id FROM amenities WHERE amenity_type = 'ROOM')
881 INTO v_amenity_ids;
882 v_n_amenities := array_length(v_amenity_ids, 1);
883 SELECT COUNT(*) INTO v_total_rooms FROM rooms;
884
885 WHILE v_offset < v_total_rooms LOOP
886 SELECT ARRAY(
887 SELECT room_id FROM rooms
888 ORDER BY room_id
889 LIMIT c_room_batch OFFSET v_offset
890 ) INTO v_room_ids;
891 EXIT WHEN array_length(v_room_ids, 1) IS NULL;
892
893 INSERT INTO room_amenities (room_id, amenity_id)
894 SELECT DISTINCT rid,
895 v_amenity_ids[1 + floor(random()*v_n_amenities)::int]
896 FROM (
897 SELECT rid, generate_series(1, 2 + floor(random()*3)::int) AS n
898 FROM unnest(v_room_ids) AS rid
899 ) x
900 ON CONFLICT (room_id, amenity_id) DO NOTHING;
901
902 v_offset := v_offset + c_room_batch;
903 RAISE NOTICE 'Room amenities — rooms offset %', v_offset;
904 END LOOP;
905END $$;
906
907
908DO $$
909DECLARE
910 c_batch CONSTANT INT := 50000;
911 v_last_id BIGINT := 0;
912 v_rows INT;
913 v_total BIGINT := 0;
914BEGIN
915 LOOP
916 INSERT INTO booking_discounts (booking_id, discount_id)
917 SELECT b.booking_id, d.discount_id
918 FROM bookings b
919 JOIN LATERAL (
920 SELECT discount_id
921 FROM discounts
922 ORDER BY random()
923 LIMIT 1
924 ) d ON true
925 WHERE b.booking_id > v_last_id
926 AND random() < CASE
927 WHEN b.booking_status = 'CONFIRMED' THEN 0.30
928 WHEN b.booking_status = 'COMPLETED' THEN 0.20
929 WHEN b.booking_status = 'CANCELLED' THEN 0.10
930 ELSE 0.15
931 END
932 ORDER BY b.booking_id
933 LIMIT c_batch
934 ON CONFLICT (booking_id, discount_id) DO NOTHING;
935
936 GET DIAGNOSTICS v_rows = ROW_COUNT;
937 EXIT WHEN v_rows = 0;
938
939 SELECT MAX(booking_id) INTO v_last_id
940 FROM booking_discounts
941 WHERE booking_id > v_last_id;
942
943 v_total := v_total + v_rows;
944 RAISE NOTICE 'Booking discounts inserted so far: %', v_total;
945 END LOOP;
946
947 RAISE NOTICE '=== Done. % booking_discounts inserted. ===', v_total;
948END $$;
949
950
951DO $$
952DECLARE
953 c_batch CONSTANT INT := 50000;
954 v_last_id BIGINT := 0;
955 v_rows INT;
956 v_total BIGINT := 0;
957BEGIN
958 LOOP
959 INSERT INTO booking_amenities (booking_id, amenity_id, quantity)
960 SELECT DISTINCT b.booking_id,
961 a.amenity_id,
962 (floor(random()*3)+1)::int
963 FROM bookings b
964 JOIN LATERAL (
965 SELECT amenity_id FROM amenities
966 ORDER BY random()
967 LIMIT 1 + floor(random()*3)::int
968 ) a ON true
969 WHERE b.booking_id > v_last_id
970 AND random() < 0.20
971 ORDER BY b.booking_id
972 LIMIT c_batch
973 ON CONFLICT (booking_id, amenity_id) DO NOTHING;
974
975 GET DIAGNOSTICS v_rows = ROW_COUNT;
976 EXIT WHEN v_rows = 0;
977
978 SELECT MAX(booking_id) INTO v_last_id
979 FROM booking_amenities
980 WHERE booking_id > v_last_id;
981
982 v_total := v_total + v_rows;
983 RAISE NOTICE 'Booking amenities inserted so far: %', v_total;
984 END LOOP;
985 RAISE NOTICE '=== Done. % booking_amenities inserted. ===', v_total;
986END $$;
987
988
989DO $$
990DECLARE
991 c_prop_imgs CONSTANT INT := 300000;
992 c_room_imgs CONSTANT INT := 200000;
993 c_batch CONSTANT INT := 10000;
994 v_inserted INT := 0;
995 v_prop_ids BIGINT[];
996 v_room_ids BIGINT[];
997 v_n_props INT;
998 v_n_rooms INT;
999 v_alts TEXT[] := ARRAY[
1000 'Front view of the property','Living room interior',
1001 'Bedroom with king bed','Bathroom overview',
1002 'Kitchen with modern appliances','Balcony with city view',
1003 'Pool area','Garden and outdoor space',
1004 'Entrance and lobby','Dining area', NULL
1005 ];
1006BEGIN
1007 SELECT ARRAY(SELECT property_id FROM properties ORDER BY random() LIMIT 100000)
1008 INTO v_prop_ids;
1009 v_n_props := array_length(v_prop_ids, 1);
1010
1011 v_inserted := 0;
1012 WHILE v_inserted < c_prop_imgs LOOP
1013 INSERT INTO images
1014 (entity_type, entity_id, property_id, room_id,
1015 url, alt_text, is_cover, sort_order, uploaded_at)
1016 SELECT
1017 'PROPERTY',
1018 pid,
1019 pid,
1020 NULL,
1021 'https://cdn.example.com/properties/' || pid || '/' || gs || '.jpg',
1022 v_alts[1 + floor(random()*array_length(v_alts,1))::int],
1023 gs = 1,
1024 gs,
1025 now() - (random() * interval '5 years')
1026 FROM (
1027 SELECT gs,
1028 v_prop_ids[1 + floor(random()*v_n_props)::int] AS pid
1029 FROM generate_series(1, LEAST(c_batch, c_prop_imgs - v_inserted)) gs
1030 ) x;
1031
1032 v_inserted := v_inserted + LEAST(c_batch, c_prop_imgs - v_inserted);
1033 RAISE NOTICE 'Property images: % / %', v_inserted, c_prop_imgs;
1034 END LOOP;
1035
1036 SELECT ARRAY(SELECT room_id FROM rooms ORDER BY random() LIMIT 100000)
1037 INTO v_room_ids;
1038 v_n_rooms := array_length(v_room_ids, 1);
1039
1040 v_inserted := 0;
1041 WHILE v_inserted < c_room_imgs LOOP
1042 INSERT INTO images
1043 (entity_type, entity_id, property_id, room_id,
1044 url, alt_text, is_cover, sort_order, uploaded_at)
1045 SELECT
1046 'ROOM',
1047 rid,
1048 NULL,
1049 rid,
1050 'https://cdn.example.com/rooms/' || rid || '/' || gs || '.jpg',
1051 v_alts[1 + floor(random()*array_length(v_alts,1))::int],
1052 gs = 1,
1053 gs,
1054 now() - (random() * interval '5 years')
1055 FROM (
1056 SELECT gs,
1057 v_room_ids[1 + floor(random()*v_n_rooms)::int] AS rid
1058 FROM generate_series(1, LEAST(c_batch, c_room_imgs - v_inserted)) gs
1059 ) x;
1060
1061 v_inserted := v_inserted + LEAST(c_batch, c_room_imgs - v_inserted);
1062 RAISE NOTICE 'Room images: % / %', v_inserted, c_room_imgs;
1063 END LOOP;
1064
1065 RAISE NOTICE '=== Images done ===';
1066END $$;
1067
1068DO $$
1069DECLARE
1070 c_total CONSTANT BIGINT := 10000000;
1071 c_batch CONSTANT INT := 50000;
1072
1073 v_inserted BIGINT := 0;
1074 v_rows_done INT;
1075
1076 v_room_ids BIGINT[];
1077 v_n_rooms INT;
1078BEGIN
1079 SELECT ARRAY(
1080 SELECT room_id
1081 FROM rooms
1082 ORDER BY room_id
1083 )
1084 INTO v_room_ids;
1085
1086 v_n_rooms := array_length(v_room_ids, 1);
1087
1088 IF v_n_rooms IS NULL OR v_n_rooms = 0 THEN
1089 RAISE EXCEPTION 'No rooms found. Insert rooms first.';
1090 END IF;
1091
1092 WHILE v_inserted < c_total LOOP
1093
1094 WITH random_data AS (
1095 SELECT
1096 v_room_ids[1 + floor(random() * v_n_rooms)::int] AS room_id,
1097
1098 (
1099 CURRENT_DATE
1100 + 1
1101 + floor(random() * 730)::int
1102 )::date AS available_date,
1103
1104 'AVAILABLE'::varchar AS status
1105 FROM generate_series(
1106 1,
1107 LEAST(c_batch, c_total - v_inserted)
1108 )
1109 )
1110 INSERT INTO availability_windows
1111 (room_id, available_date, status)
1112 SELECT
1113 room_id,
1114 available_date,
1115 status
1116 FROM random_data;
1117
1118 GET DIAGNOSTICS v_rows_done = ROW_COUNT;
1119
1120 v_inserted := v_inserted + v_rows_done;
1121
1122 RAISE NOTICE 'Availability windows inserted: %', v_inserted;
1123
1124 END LOOP;
1125
1126 RAISE NOTICE '=== Done. % availability windows inserted. ===', v_inserted;
1127END $$;
1128
1129ANALYZE availability_windows;
1130
1131
1132ANALYZE;
1133