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