-- SEED SCRIPT

SET work_mem                  = '512MB';
SET maintenance_work_mem      = '1GB';
SET synchronous_commit        = OFF;
--SET checkpoint_completion_target = 0.9;



-- 1. COUNTRIES

INSERT INTO countries (country_name, country_code)
SELECT c2, c1
FROM   country_name
offset 1
ON CONFLICT DO NOTHING;



-- 2. ADDRESSES  (100 000 rows)

DO $$
DECLARE
    c_total    CONSTANT INT := 100000;
    c_batch    CONSTANT INT := 10000;
    v_inserted INT := 0;

    v_streets       TEXT[] := ARRAY[
        'Main Street','Oak Street','Maple Avenue','Pine Road',
        'Cedar Lane','River Road','Lake View','Park Avenue'
    ];
    v_generic_cities TEXT[] := ARRAY[
        'Capital City','Old Town','Metro City','Riverside',
        'Hillview','Lakeside','Central City'
    ];

    v_country_ids   BIGINT[];
    v_country_codes TEXT[];
    v_n_countries   INT;
    v_idx           INT;
    v_city          TEXT;
    v_country_code  TEXT;
    v_country_id    BIGINT;
BEGIN
    SELECT ARRAY(SELECT country_id FROM countries ORDER BY country_id),
           ARRAY(SELECT country_code FROM countries ORDER BY country_id)
      INTO v_country_ids, v_country_codes;

    v_n_countries := array_length(v_country_ids, 1);
    IF v_n_countries IS NULL THEN RAISE EXCEPTION 'countries table is empty'; END IF;

    WHILE v_inserted < c_total LOOP
        INSERT INTO addresses (country_id, zip_code, street, city)
        SELECT
            cid,
            cid::text || lpad(rn::text, 6, '0'),
            v_streets[1 + floor(random() * array_length(v_streets,1))::int],
            city
        FROM (
            SELECT
                gs AS rn,
                v_country_ids [1 + floor(random()*v_n_countries)::int] AS cid,
                v_country_codes[1 + floor(random()*v_n_countries)::int] AS cc
            FROM generate_series(v_inserted + 1,
                                 LEAST(v_inserted + c_batch, c_total)) gs
        ) base
        CROSS JOIN LATERAL (
            SELECT CASE cc
                WHEN 'MK' THEN (ARRAY['Skopje','Ohrid','Bitola','Tetovo'])     [1 + floor(random()*4)::int]
                WHEN 'DE' THEN (ARRAY['Berlin','Munich','Hamburg','Frankfurt']) [1 + floor(random()*4)::int]
                WHEN 'US' THEN (ARRAY['New York','Los Angeles','Chicago','Miami'])[1 + floor(random()*4)::int]
                ELSE            v_generic_cities[1 + floor(random()*array_length(v_generic_cities,1))::int]
            END AS city
        ) c;

        v_inserted := v_inserted + c_batch;
        RAISE NOTICE 'Addresses: % / %', LEAST(v_inserted, c_total), c_total;
    END LOOP;
END $$;

ANALYZE addresses;



-- 3. USERS  (2 000 000 rows)

DO $$
DECLARE
    batch_size INT := 50000;
    start_id   INT := 1;
    end_id     INT := 2000000;
    fn_count   BIGINT;
    sn_count   BIGINT;
BEGIN
    SELECT COUNT(*) INTO fn_count FROM (SELECT name FROM girls_names UNION ALL SELECT name FROM boys_names) t;
    SELECT COUNT(*) INTO sn_count FROM surnames_200;

    WHILE start_id <= end_id LOOP
        INSERT INTO users (email, password_hash, first_name, last_name, phone, updated_at)
        WITH all_first_names AS (
            SELECT name, row_number() OVER (ORDER BY name) AS id FROM girls_names
            UNION ALL
            SELECT name, (SELECT COUNT(*) FROM girls_names) + row_number() OVER (ORDER BY name) FROM boys_names
        ),
        sn AS (SELECT surname, row_number() OVER (ORDER BY surname) AS id FROM surnames_200),
        batch AS (SELECT generate_series(start_id, LEAST(start_id + batch_size - 1, end_id)) AS rn)
        SELECT
            lower(fn.name || '.' || sn.surname || b.rn || '@example.com'),
            'test123',
            fn.name,
            sn.surname,
            '+389' || lpad(b.rn::text, 8, '0'),
            CURRENT_TIMESTAMP
        FROM batch b
        JOIN all_first_names fn ON fn.id = ((b.rn * 17) % fn_count) + 1
        JOIN sn              ON sn.id = ((b.rn * 31) % sn_count) + 1;

        RAISE NOTICE 'Users batch % – %', start_id, LEAST(start_id + batch_size - 1, end_id);
        start_id := start_id + batch_size;
    END LOOP;
END $$;

ANALYZE users;



-- 4. ADMINS  (300 random users)

INSERT INTO admins (user_id)
SELECT user_id FROM users ORDER BY random() LIMIT 300
ON CONFLICT (user_id) DO NOTHING;



-- 5. GUESTS  (all users)
--    ON CONFLICT replaces the expensive LEFT JOIN anti-pattern.

INSERT INTO guests (user_id)
SELECT u.user_id
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM admins a
    WHERE a.user_id = u.user_id
)
AND NOT EXISTS (
    SELECT 1
    FROM hosts h
    WHERE h.user_id = u.user_id
)
ON CONFLICT (user_id) DO NOTHING;

ANALYZE guests;


-- 6. HOST_APPLICATIONS  (20 000 rows)

WITH selected_users AS (
    SELECT user_id
    FROM   users u
    WHERE  NOT EXISTS (SELECT 1 FROM host_applications ha WHERE ha.user_id = u.user_id)
    ORDER BY random()
    LIMIT  20000
),
randomized AS (
    SELECT user_id,
           (ARRAY['PENDING','UNDER_REVIEW','APPROVED','REJECTED'])[floor(random()*4+1)::int] AS status,
           (CURRENT_DATE - floor(random()*365)::int)::date AS application_date
    FROM selected_users
),
prepared AS (
    SELECT r.user_id,
           r.application_date,
           r.status,
           CASE WHEN r.status IN ('APPROVED','REJECTED')
                THEN (SELECT admin_id FROM admins ORDER BY random() LIMIT 1) END AS reviewed_by_admin_id,
           CASE WHEN r.status IN ('APPROVED','REJECTED')
                THEN r.application_date + floor(random()*30+1)::int END AS review_date,
           CASE WHEN r.status = 'REJECTED'
                THEN (ARRAY[
                    'Incomplete profile information',
                    'Missing required verification documents',
                    'Does not meet hosting criteria',
                    'Policy compliance issue',
                    'Previous account concerns',
                    'Application details could not be verified'
                ])[floor(random()*6+1)::int] END AS rejection_reason
    FROM randomized r
)
INSERT INTO host_applications
    (user_id, application_date, status, reviewed_by_admin_id, review_date, rejection_reason)
SELECT user_id, application_date, status, reviewed_by_admin_id, review_date, rejection_reason
FROM prepared;



-- 7. HOSTS  (approved subset, up to 30 000)

INSERT INTO hosts (user_id, application_id)
SELECT ha.user_id, ha.application_id
FROM   host_applications ha
WHERE  ha.status = 'APPROVED'
ORDER BY random()
LIMIT  30000
ON CONFLICT (user_id) DO NOTHING;

ANALYZE hosts;



-- 8. LISTING_TYPES  (static)

INSERT INTO listing_types (type_name, description) VALUES
('Apartment',  'Self-contained residential unit in a building or complex'),
('House',      'Standalone residential property with private entrance'),
('Studio',     'Small open-layout apartment with combined living space'),
('Villa',      'Luxury standalone property, often in scenic or coastal areas'),
('Cabin',      'Small house located in rural or nature areas'),
('Hotel Room', 'Private room inside a hotel with standard hotel services'),
('Hostel Bed', 'Shared accommodation bed in a dormitory-style room'),
('Guesthouse', 'Private home or small property offering guest accommodation')
ON CONFLICT (type_name) DO NOTHING;



-- 9. PAYMENT_METHODS  (static)

INSERT INTO payment_methods (method_name, description) VALUES
('Credit Card',    'Payment via Visa, Mastercard, or other major credit cards.'),
('Debit Card',     'Payment directly from a linked bank account via debit card.'),
('PayPal',         'Online payment through a PayPal account.'),
('Bank Transfer',  'Direct bank-to-bank wire or ACH transfer.'),
('Apple Pay',      'Contactless payment using Apple Pay on supported devices.'),
('Google Pay',     'Contactless payment using Google Pay on supported devices.'),
('Stripe',         'Online card processing via the Stripe payment gateway.'),
('Cash',           'Payment made in cash upon arrival or check-in.'),
('Cryptocurrency', 'Payment made using Bitcoin, Ethereum, or other cryptocurrencies.'),
('Gift Card',      'Payment using a prepaid gift card or voucher code.')
ON CONFLICT (method_name) DO NOTHING;



-- 10. AMENITIES  (50 000 rows)

DO $$
DECLARE
    c_total   CONSTANT INT := 50000;
    c_batch   CONSTANT INT := 5000;
    v_inserted INT := 0;
    v_names TEXT[] := ARRAY[
        'Wi-Fi','Air Conditioning','Heating','TV','Smart TV','Netflix Access','Mini Fridge',
        'Coffee Machine','Electric Kettle','Microwave','Hair Dryer','Iron','Ironing Board',
        'Wardrobe','Desk','Work Chair','Safe','Balcony','Sea View','Mountain View','City View',
        'Private Bathroom','Bathtub','Shower Cabin','King Bed','Queen Bed','Single Bed',
        'Sofa Bed','Blackout Curtains','Soundproofing','Towels','Bed Linen','Toiletries',
        'Slippers','Bathrobe','Room Service','Daily Housekeeping','Private Entrance','Fireplace',
        'Jacuzzi','Swimming Pool','Private Pool','Shared Pool','Garden','Terrace','Patio',
        'BBQ Area','Parking','Free Parking','Valet Parking','EV Charging Station','Elevator',
        'Gym','Spa','Sauna','Steam Room','Restaurant','Bar','Breakfast','Airport Shuttle',
        'Laundry Service','Dry Cleaning','Pet Friendly','24/7 Reception','Security Cameras',
        'Wheelchair Access','Conference Room','Meeting Room','Playground','Kids Area',
        'Bicycle Rental','Car Rental Desk','Luggage Storage','Concierge Service','Smoking Area',
        'Non-Smoking Rooms','Shared Kitchen','Full Kitchen','Dishwasher','Washing Machine',
        'Dryer','Hot Tub','Library','Business Center','Game Room','Tennis Court',
        'Basketball Court','Beach Access','Private Beach','Ski Storage','Ski-in/Ski-out',
        'Rooftop Access','Picnic Area','Outdoor Furniture','CCTV','First Aid Kit',
        'Smoke Detector','Carbon Monoxide Detector','Baby Crib','High Chair'
    ];
    v_room_descs TEXT[] := ARRAY[
        'Comfort feature available inside the room.',
        'Designed to improve guest convenience and stay quality.',
        'Common room-level feature for added comfort.',
        'Useful in-room amenity frequently requested by guests.',
        'Enhances privacy, relaxation, or functionality of the room.',
        'Suitable for short and extended stays.',
        'Standard room service or equipment option.',
        'Added for convenience and better guest experience.'
    ];
    v_prop_descs TEXT[] := ARRAY[
        'Shared or property-wide feature available to guests.',
        'Amenity provided at the property level.',
        'Designed to improve the overall guest experience.',
        'Useful facility available within the property.',
        'Common property feature for comfort, access, or recreation.',
        'Supports a more convenient and enjoyable stay.',
        'General facility or service offered by the property.',
        'Available to some or all guests depending on booking terms.'
    ];
BEGIN
    WHILE v_inserted < c_total LOOP
        INSERT INTO amenities (amenity_name, amenity_type, description, is_included, price)
        SELECT
            v_names[1 + floor(random() * array_length(v_names,1))::int] || ' ' || gs,
            x.amenity_type,
            CASE WHEN x.amenity_type = 'ROOM'
                 THEN v_room_descs[1 + floor(random() * array_length(v_room_descs,1))::int]
                 ELSE v_prop_descs[1 + floor(random() * array_length(v_prop_descs,1))::int]
            END,
            x.is_included,
            CASE WHEN x.is_included THEN NULL ELSE round((5 + random()*195)::numeric, 2) END
        FROM (
            SELECT gs,
                   CASE WHEN random() < 0.5 THEN 'ROOM' ELSE 'PROPERTY' END AS amenity_type,
                   random() < 0.7                                             AS is_included
            FROM generate_series(1, LEAST(c_batch, c_total - v_inserted)) gs
        ) x;

        v_inserted := v_inserted + LEAST(c_batch, c_total - v_inserted);
        RAISE NOTICE 'Amenities: % / %', v_inserted, c_total;
    END LOOP;
END $$;

ANALYZE amenities;



-- 11. PROPERTIES  (250 000 rows)

DO $$
DECLARE
    c_total  CONSTANT INT := 250000;
    c_batch  CONSTANT INT := 10000;
    v_inserted INT := 0;
BEGIN
    WHILE v_inserted < c_total LOOP
        INSERT INTO properties
            (host_id, listing_type_id, address_id, title, description,
             base_price, max_guests, status, created_at)
        SELECT
            h.user_id,
            lt.listing_type_id,
            a.address_id,
            lt.type_name || ' in ' || a.city,
            'Modern ' || lt.type_name || ' located in ' || a.city,
            round((30 + random()*400)::numeric, 2),
            1 + floor(random()*10)::int,
            (ARRAY['ACTIVE','ACTIVE','ACTIVE','INACTIVE'])[floor(random()*4+1)::int],
            now() - (random() * interval '5 years')
        FROM generate_series(1, LEAST(c_batch, c_total - v_inserted))
        JOIN LATERAL (SELECT user_id    FROM hosts         ORDER BY random() LIMIT 1) h  ON true
        JOIN LATERAL (SELECT listing_type_id, type_name
                        FROM listing_types ORDER BY random() LIMIT 1) lt ON true
        JOIN LATERAL (SELECT address_id, city FROM addresses ORDER BY random() LIMIT 1) a  ON true;

        v_inserted := v_inserted + LEAST(c_batch, c_total - v_inserted);
        RAISE NOTICE 'Properties: % / %', v_inserted, c_total;
    END LOOP;
END $$;

ANALYZE properties;



-- 12. ROOM_TYPES  (static)

INSERT INTO room_types (type_name, description) VALUES
('Single Room',     'A room with one single bed, suitable for one guest.'),
('Double Room',     'A room with one double or queen bed, for one or two guests.'),
('Twin Room',       'A room with two single beds, ideal for two guests.'),
('Triple Room',     'A room for three guests with three singles or a double and a single.'),
('Quad Room',       'A room for four guests with multiple bed configurations.'),
('Suite',           'A luxury room with separate living and sleeping areas.'),
('Junior Suite',    'A larger-than-standard room with a partial sitting area.'),
('Studio',          'An open-plan room with a kitchenette, suitable for longer stays.'),
('Family Room',     'A spacious room designed for families with multiple sleeping areas.'),
('Dormitory Room',  'A shared room with multiple beds, common in hostels.'),
('Villa',           'A standalone or semi-standalone private unit with premium amenities.'),
('Bungalow',        'A ground-floor private unit, often found in resort settings.'),
('Penthouse',       'A top-floor luxury unit with premium views and amenities.'),
('Accessible Room', 'A room designed for guests with mobility or accessibility needs.'),
('Connecting Room', 'A room with a door connecting to an adjacent room, ideal for groups.')
ON CONFLICT (type_name) DO NOTHING;



-- 13. ROOMS  (~625 000 rows, 1-5 per property, unique names)
DO $$
DECLARE
    c_prop_batch     CONSTANT INT := 10000;
    v_offset         INT    := 0;
    v_total_props    INT;
    v_prop_ids       BIGINT[];
    v_rooms_inserted BIGINT := 0;
    v_rows_done      INT;

    v_room_type_ids BIGINT[];

    v_prefixes TEXT[] := ARRAY[
        'Standard Room','Deluxe Room','Superior Room','Suite',
        'Junior Suite','Family Room','Twin Room','Double Room',
        'Single Room','Studio'
    ];
    v_descs TEXT[] := ARRAY[
        'Bright and airy with city views.',
        'Cozy room with garden view.',
        'Spacious room with private bathroom.',
        'Quiet room on upper floor.',
        'Well-appointed room with all amenities.',
        'Charming room with vintage decor.',
        'Modern room with smart TV and fast Wi-Fi.',
        NULL
    ];
    v_statuses TEXT[] := ARRAY[
        'ACTIVE','ACTIVE','ACTIVE','ACTIVE','ACTIVE','ACTIVE','ACTIVE',
        'INACTIVE','MAINTENANCE','UNAVAILABLE'
    ];
BEGIN
    SELECT ARRAY(SELECT room_type_id FROM room_types) INTO v_room_type_ids;
    SELECT COUNT(*)                                    INTO v_total_props FROM properties;

    WHILE v_offset < v_total_props LOOP
        SELECT ARRAY(
            SELECT property_id FROM properties
            ORDER  BY property_id
            LIMIT  c_prop_batch
            OFFSET v_offset
        ) INTO v_prop_ids;

        EXIT WHEN array_length(v_prop_ids, 1) IS NULL;

        INSERT INTO rooms
            (property_id, room_type_id, room_name, capacity, price_per_night,
             description, status, extra_capacity, extra_guest_price)
        SELECT
            pid,
            v_room_type_ids[1 + floor(random() * array_length(v_room_type_ids,1))::int],
            v_prefixes[1 + floor(random() * array_length(v_prefixes,1))::int] || ' ' || rn,
            1 + floor(random() * 6)::int,
            round((30 + random() * 470)::numeric, 2),
            v_descs[1 + floor(random() * array_length(v_descs,1))::int],
            v_statuses[1 + floor(random() * array_length(v_statuses,1))::int],
            floor(random() * 4)::int,
            CASE WHEN random() < 0.4 THEN NULL
                 ELSE round((10 + random()*90)::numeric, 2) END
        FROM (
            SELECT pid, generate_series(1, 1 + floor(random()*4)::int) AS rn
            FROM   unnest(v_prop_ids) AS pid
        ) expanded;

        GET DIAGNOSTICS v_rows_done = ROW_COUNT;
        v_rooms_inserted := v_rooms_inserted + v_rows_done;

        v_offset := v_offset + c_prop_batch;
        RAISE NOTICE 'Rooms — properties offset %, rooms so far %',
                     v_offset, v_rooms_inserted;
    END LOOP;

    RAISE NOTICE '=== Done. % rooms inserted. ===', v_rooms_inserted;
END $$;

ANALYZE rooms;



-- 14. AVAILABILITY_BLOCKS  (~500 000 rows)

DO $$
DECLARE
    c_target  CONSTANT INT := 500000;
    c_batch   CONSTANT INT := 10000;
    v_inserted INT := 0;
    v_room_ids BIGINT[];
BEGIN
    SELECT ARRAY(SELECT room_id FROM rooms ORDER BY random() LIMIT 200000)
      INTO v_room_ids;

    WHILE v_inserted < c_target LOOP
        INSERT INTO availability_blocks
            (room_id, blocked_from_date, blocked_from_time,
             blocked_to_date, blocked_to_time, description)
        SELECT
            v_room_ids[1 + floor(random() * array_length(v_room_ids,1))::int],
            fd,
            fd::timestamp + make_interval(hours => floor(random()*23)::int),
            fd + dur,
            (fd + dur)::timestamp
                + make_interval(hours   => floor(random()*23)::int,
                                mins    => floor(random()*59)::int),
            (ARRAY[
                'Owner block','Maintenance window','Private event',
                'Renovation period','Seasonal closure', NULL
            ])[1 + floor(random()*6)::int]
        FROM (
            SELECT gs,
                   (CURRENT_DATE + floor(random()*365)::int - 180)::date AS fd,
                   (1 + floor(random()*13)::int)                          AS dur
            FROM generate_series(1, LEAST(c_batch, c_target - v_inserted)) gs
        ) x;

        v_inserted := v_inserted + LEAST(c_batch, c_target - v_inserted);
        RAISE NOTICE 'Availability blocks: % / %', v_inserted, c_target;
    END LOOP;
END $$;



-- 15. DISCOUNTS  (50 000 rows)

DO $$
DECLARE
    c_total   CONSTANT INT := 50000;
    c_batch   CONSTANT INT := 5000;
    v_inserted INT := 0;
    v_host_ids BIGINT[];
    v_n_hosts  INT;
    v_titles TEXT[] := ARRAY[
        'Summer Sale','Weekend Special','Early Bird Offer','Last Minute Deal',
        'Holiday Discount','Loyalty Reward','New Guest Promo','Extended Stay Offer',
        'Flash Sale','Family Package'
    ];
    v_descriptions TEXT[] := ARRAY[
        'Special limited-time discount for selected bookings.',
        'Save more on qualifying reservations during the campaign period.',
        'Promotional offer created to boost bookings and occupancy.',
        'Exclusive deal available for guests who meet the listed conditions.',
        'Temporary host discount for higher visibility and conversions.',
        'Discount valid only during the specified booking window.',
        'Seasonal promotion for selected accommodation units.',
        'Incentive for direct and repeat bookings.',
        'Marketing offer intended to increase reservations.',
        'Special campaign created by the host for targeted guests.'
    ];
BEGIN
    SELECT ARRAY(SELECT user_id FROM hosts ORDER BY user_id) INTO v_host_ids;
    v_n_hosts := array_length(v_host_ids, 1);
    IF v_n_hosts IS NULL THEN RAISE EXCEPTION 'hosts table is empty.'; END IF;

    WHILE v_inserted < c_total LOOP
        INSERT INTO discounts
            (host_id, code, title, description, discount_type, discount_value,
             valid_from, valid_to, is_active)
        SELECT
            v_host_ids[1 + floor(random()*v_n_hosts)::int],
            'DISC-' || upper(substr(md5(random()::text || clock_timestamp()::text || gs::text), 1, 12)),
            v_titles      [1 + floor(random()*array_length(v_titles,1))::int],
            v_descriptions[1 + floor(random()*array_length(v_descriptions,1))::int],
            x.discount_type,
            CASE WHEN x.discount_type = 'PERCENTAGE'
                 THEN round((5  + random()*45 )::numeric, 2)
                 ELSE round((5  + random()*195)::numeric, 2) END,
            x.valid_from,
            x.valid_to,
            CASE WHEN x.valid_to < CURRENT_DATE THEN FALSE
                 WHEN random() < 0.85            THEN TRUE
                 ELSE                                  FALSE END
        FROM (
            SELECT gs,
                   CASE WHEN random() < 0.5 THEN 'PERCENTAGE' ELSE 'FIXED' END AS discount_type,
                   vf                                                            AS valid_from,
                   vf + (10 + floor(random()*180)::int)                         AS valid_to
            FROM generate_series(1, LEAST(c_batch, c_total - v_inserted)) gs
            CROSS JOIN LATERAL (
                SELECT (CURRENT_DATE - floor(random()*180)::int)::date AS vf
            ) d
        ) x;

        v_inserted := v_inserted + LEAST(c_batch, c_total - v_inserted);
        RAISE NOTICE 'Discounts: % / %', v_inserted, c_total;
    END LOOP;
END $$;

ANALYZE discounts;



-- 16. BOOKINGS  (10 000 000 rows )
--     check_out > check_in

DO $$
DECLARE
    c_total    CONSTANT BIGINT := 10000000;
    c_batch    CONSTANT INT    := 500000;
    v_inserted BIGINT := 0;
    v_guest_ids BIGINT[];
    v_room_ids  BIGINT[];
    v_n_guests  INT;
    v_n_rooms   INT;
BEGIN
    SELECT ARRAY(SELECT guest_id FROM guests ORDER BY random() LIMIT 500000)
      INTO v_guest_ids;
    SELECT ARRAY(SELECT room_id  FROM rooms  ORDER BY random())
      INTO v_room_ids;

    v_n_guests := array_length(v_guest_ids, 1);
    v_n_rooms  := array_length(v_room_ids,  1);
    IF v_n_guests IS NULL THEN RAISE EXCEPTION 'No guests found.'; END IF;
    IF v_n_rooms  IS NULL THEN RAISE EXCEPTION 'No rooms found.';  END IF;

    WHILE v_inserted < c_total LOOP
        INSERT INTO bookings
            (guest_id, room_id, check_in_date, check_out_date,
             guests_count, total_price, booking_status, booked_at)
        SELECT
            v_guest_ids[1 + floor(random()*v_n_guests)::int],
            v_room_ids [1 + floor(random()*v_n_rooms )::int],
            ci,
            ci + (1 + floor(random()*13)::int),
            1 + floor(random()*6)::int,
            round((20 + random()*980)::numeric, 2),
            (ARRAY[
                'CONFIRMED','CONFIRMED','CONFIRMED','CONFIRMED',
                'COMPLETED','COMPLETED','COMPLETED',
                'PENDING','PENDING',
                'CANCELLED','NO_SHOW'
            ])[1 + floor(random()*11)::int],
            now() - (random() * interval '3 years')
        FROM (
            SELECT gs,
                   (CURRENT_DATE - floor(random()*1000)::int)::date AS ci
            FROM generate_series(1, LEAST(c_batch, c_total - v_inserted)) gs
        ) x;

        v_inserted := v_inserted + LEAST(c_batch, c_total - v_inserted);
        RAISE NOTICE 'Bookings: % / %', v_inserted, c_total;
    END LOOP;
END $$;

ANALYZE bookings;



-- 17. PAYMENTS  (one per booking)

DO $$
DECLARE
    c_batch      CONSTANT INT := 50000;
    v_inserted   BIGINT := 0;
    v_rows_done  INT;
    v_method_ids BIGINT[];
    v_n_methods  INT;
    v_statuses   TEXT[] := ARRAY[
        'PAID','PAID','PAID','PAID','PAID','PAID',
        'PENDING','PENDING','PENDING',
        'FAILED','FAILED',
        'REFUNDED','REFUNDED',
        'PARTIALLY_REFUNDED'
    ];
    v_last_id    BIGINT := 0;
BEGIN
    SELECT ARRAY(SELECT payment_method_id FROM payment_methods ORDER BY payment_method_id)
      INTO v_method_ids;
    v_n_methods := array_length(v_method_ids, 1);

    LOOP
        INSERT INTO payments
            (booking_id, payment_method_id, amount, payment_status, paid_at)
        SELECT
            b.booking_id,
            v_method_ids[1 + floor(random()*v_n_methods)::int],
            round((20 + random()*4980)::numeric, 2),
            s.stat,
            CASE WHEN s.stat = 'PAID'
                 THEN now() - (random() * interval '2 years')
                 ELSE NULL END
        FROM (
            SELECT b2.booking_id,
                   v_statuses[1 + floor(random()*array_length(v_statuses,1))::int] AS stat
            FROM   bookings b2
            LEFT   JOIN payments p ON p.booking_id = b2.booking_id
            WHERE  b2.booking_id > v_last_id
              AND  p.booking_id IS NULL
            ORDER  BY b2.booking_id
            LIMIT  c_batch
        ) s
        JOIN bookings b ON b.booking_id = s.booking_id;

        GET DIAGNOSTICS v_rows_done = ROW_COUNT;
        EXIT WHEN v_rows_done = 0;

        SELECT MAX(p.booking_id) INTO v_last_id
        FROM   payments p
        WHERE  p.booking_id > v_last_id;

        v_inserted := v_inserted + v_rows_done;
        RAISE NOTICE 'Payments inserted: %', v_inserted;
    END LOOP;

    RAISE NOTICE '=== Done. % payments inserted. ===', v_inserted;
END $$;

ANALYZE payments;



-- 18. REVIEWS  (one per COMPLETED booking)

DO $$
DECLARE
    c_batch     CONSTANT INT := 50000;
    v_inserted  BIGINT := 0;
    v_rows_done INT;
    v_last_id   BIGINT := 0;

    v_positive_comments TEXT[] := ARRAY[
        'Absolutely wonderful stay, highly recommend!',
        'Great location and very clean property.',
        'The host was super responsive and helpful.',
        'Amazing views and comfortable beds.',
        'Good value for money, would return.',
        'Everything as described, no surprises.',
        'Lovely property, perfect for a family trip.',
        'Very modern and well-equipped kitchen.',
        'Peaceful retreat, exactly what we needed.'
    ];

    v_neutral_comments TEXT[] := ARRAY[
        'Nice place but a bit noisy at night.',
        'The stay was okay, but nothing special.',
        'Property was acceptable for a short stay.',
        'Decent location, but some things could be improved.',
        'Average experience overall.',
        NULL
    ];

    v_negative_comments TEXT[] := ARRAY[
        'The property was not as clean as expected.',
        'Very noisy at night and difficult to sleep.',
        'The host was slow to respond.',
        'The room was smaller than described.',
        'Not good value for the price.',
        'Several amenities were missing or not working.',
        'The stay was disappointing overall.',
        'Would not choose this property again.',
        'The property needs better maintenance.',
        'Uncomfortable beds and poor cleanliness.'
    ];
BEGIN
    LOOP
        WITH batch_bookings AS (
            SELECT
                b.booking_id,
                b.guest_id,
                rm.property_id,
                b.booked_at,
                1 + floor(random() * 5)::int AS rating
            FROM bookings b
            JOIN rooms rm ON rm.room_id = b.room_id
            WHERE b.booking_status = 'COMPLETED'
              AND b.booking_id > v_last_id
              AND NOT EXISTS (
                  SELECT 1
                  FROM reviews r
                  WHERE r.booking_id = b.booking_id
              )
            ORDER BY b.booking_id
            LIMIT c_batch
        )
        INSERT INTO reviews
            (booking_id, guest_id, property_id, rating, comment, created_at)
        SELECT
            bb.booking_id,
            bb.guest_id,
            bb.property_id,
            bb.rating,
            CASE
                WHEN bb.rating IN (1, 2) THEN
                    v_negative_comments[
                        1 + floor(random() * array_length(v_negative_comments, 1))::int
                    ]

                WHEN bb.rating = 3 THEN
                    v_neutral_comments[
                        1 + floor(random() * array_length(v_neutral_comments, 1))::int
                    ]

                ELSE
                    v_positive_comments[
                        1 + floor(random() * array_length(v_positive_comments, 1))::int
                    ]
            END AS comment,
            bb.booked_at + (random() * interval '30 days') AS created_at
        FROM batch_bookings bb;

        GET DIAGNOSTICS v_rows_done = ROW_COUNT;

        EXIT WHEN v_rows_done = 0;

        SELECT MAX(r.booking_id)
        INTO v_last_id
        FROM reviews r
        WHERE r.booking_id > v_last_id;

        v_inserted := v_inserted + v_rows_done;

        RAISE NOTICE 'Reviews inserted so far: %', v_inserted;
    END LOOP;

    RAISE NOTICE '=== Done. Total reviews inserted: % ===', v_inserted;
END $$;

ANALYZE reviews;




-- 19. FAVORITE_LISTINGS  (10 000 000 rows)

WITH counts AS (
    SELECT (SELECT COUNT(*) FROM users)      AS u_count,
           (SELECT COUNT(*) FROM properties) AS p_count
),
numbered_users AS (
    SELECT user_id, row_number() OVER (ORDER BY user_id) AS rn FROM users
),
numbered_properties AS (
    SELECT property_id, row_number() OVER (ORDER BY property_id) AS rn FROM properties
)
INSERT INTO favorite_listings (user_id, property_id, created_at)
SELECT
    u.user_id,
    p.property_id,
    now() - (random() * interval '365 days')
FROM generate_series(1, 10000000) g
CROSS JOIN counts
JOIN numbered_users       u ON u.rn = (g % counts.u_count) + 1
JOIN numbered_properties  p ON p.rn = ((g * 17) % counts.p_count) + 1
ON CONFLICT (user_id, property_id) DO NOTHING;

DELETE FROM favorite_listings
WHERE user_id = -1;



-- 20. NOTIFICATIONS  (5 000 000 rows)

DO $$
DECLARE
    c_total  CONSTANT INT := 5000000;
    c_batch  CONSTANT INT := 50000;
    v_inserted INT := 0;
    v_user_ids BIGINT[];
    v_n_users  INT;
    v_messages TEXT[] := ARRAY[
        'Your booking has been confirmed.',
        'Your payment was successfully processed.',
        'A new review has been posted for your property.',
        'Your reservation has been cancelled.',
        'Reminder: your check-in is tomorrow.',
        'Your refund has been initiated.',
        'You have a new message from your host.',
        'You have a new message from your guest.',
        'Your listing has been approved and is now active.',
        'A guest has requested to book your property.',
        'Your booking request has been declined.',
        'Payment failed. Please update your payment method.',
        'Your account password was changed successfully.',
        'A special offer is available for your upcoming stay.',
        'Your loyalty points have been updated.',
        'Check-out reminder: please leave the property by 11:00 AM.',
        'Your host has left you a review.',
        'Your guest has left you a review.',
        'Maintenance scheduled for your property on the selected dates.',
        'Your support ticket has been resolved.'
    ];
BEGIN
    SELECT ARRAY(SELECT user_id FROM users ORDER BY random() LIMIT 100000)
      INTO v_user_ids;
    v_n_users := array_length(v_user_ids, 1);

    WHILE v_inserted < c_total LOOP
        INSERT INTO notifications (user_id, message, sent_at, is_read)
        SELECT
            v_user_ids[1 + floor(random()*v_n_users)::int],
            v_messages[1 + floor(random()*array_length(v_messages,1))::int],
            now() - (random() * interval '3 years'),
            random() < 0.6
        FROM generate_series(1, LEAST(c_batch, c_total - v_inserted));

        v_inserted := v_inserted + LEAST(c_batch, c_total - v_inserted);
        RAISE NOTICE 'Notifications: % / %', v_inserted, c_total;
    END LOOP;
END $$;



-- 21. CANCELLATION_POLICIES  (one per property)

INSERT INTO cancellation_policies
    (property_id, policy_name, description, refund_percentage, days_before_checking)
SELECT
    p.property_id,
    (ARRAY['Flexible','Moderate','Strict','Non-refundable'])[floor(random()*4+1)::int],
    CASE floor(random()*4)::int
        WHEN 0 THEN 'Full refund if cancelled early'
        WHEN 1 THEN 'Partial refund depending on timing'
        WHEN 2 THEN 'Limited refund window applies'
        ELSE        'No refunds allowed'
    END,
    CASE floor(random()*4)::int WHEN 0 THEN 100 WHEN 1 THEN 50 WHEN 2 THEN 25 ELSE 0 END,
    CASE floor(random()*4)::int WHEN 0 THEN 1   WHEN 1 THEN 3  WHEN 2 THEN 7  ELSE 14 END
FROM properties p
LEFT JOIN cancellation_policies cp ON cp.property_id = p.property_id
WHERE cp.property_id IS NULL;



-- 22. PROPERTY_AMENITIES  (~750 000 rows, 2-5 per property)

DO $$
DECLARE
    c_prop_batch  CONSTANT INT := 5000;
    v_offset      INT := 0;
    v_total_props INT;
    v_prop_ids    BIGINT[];
    v_amenity_ids BIGINT[];
    v_n_amenities INT;
BEGIN
    SELECT ARRAY(SELECT amenity_id FROM amenities WHERE amenity_type = 'PROPERTY')
      INTO v_amenity_ids;
    v_n_amenities := array_length(v_amenity_ids, 1);
    SELECT COUNT(*) INTO v_total_props FROM properties;

    WHILE v_offset < v_total_props LOOP
        SELECT ARRAY(
            SELECT property_id FROM properties
            ORDER  BY property_id
            LIMIT  c_prop_batch OFFSET v_offset
        ) INTO v_prop_ids;
        EXIT WHEN array_length(v_prop_ids, 1) IS NULL;

        INSERT INTO property_amenities (property_id, amenity_id)
        SELECT DISTINCT pid,
               v_amenity_ids[1 + floor(random()*v_n_amenities)::int]
        FROM (
            SELECT pid, generate_series(1, 2 + floor(random()*3)::int) AS n
            FROM   unnest(v_prop_ids) AS pid
        ) x
        ON CONFLICT (property_id, amenity_id) DO NOTHING;

        v_offset := v_offset + c_prop_batch;
        RAISE NOTICE 'Property amenities — properties offset %', v_offset;
    END LOOP;
END $$;



-- 23. ROOM_AMENITIES  (~1 250 000 rows, 2-5 per room)

DO $$
DECLARE
    c_room_batch  CONSTANT INT := 5000;
    v_offset      INT := 0;
    v_total_rooms INT;
    v_room_ids    BIGINT[];
    v_amenity_ids BIGINT[];
    v_n_amenities INT;
BEGIN
    SELECT ARRAY(SELECT amenity_id FROM amenities WHERE amenity_type = 'ROOM')
      INTO v_amenity_ids;
    v_n_amenities := array_length(v_amenity_ids, 1);
    SELECT COUNT(*) INTO v_total_rooms FROM rooms;

    WHILE v_offset < v_total_rooms LOOP
        SELECT ARRAY(
            SELECT room_id FROM rooms
            ORDER  BY room_id
            LIMIT  c_room_batch OFFSET v_offset
        ) INTO v_room_ids;
        EXIT WHEN array_length(v_room_ids, 1) IS NULL;

        INSERT INTO room_amenities (room_id, amenity_id)
        SELECT DISTINCT rid,
               v_amenity_ids[1 + floor(random()*v_n_amenities)::int]
        FROM (
            SELECT rid, generate_series(1, 2 + floor(random()*3)::int) AS n
            FROM   unnest(v_room_ids) AS rid
        ) x
        ON CONFLICT (room_id, amenity_id) DO NOTHING;

        v_offset := v_offset + c_room_batch;
        RAISE NOTICE 'Room amenities — rooms offset %', v_offset;
    END LOOP;
END $$;



-- 24. BOOKING_DISCOUNTS  (~20-30% of bookings)
--     Batched to avoid a single enormous lateral join over all bookings.

DO $$
DECLARE
    c_batch   CONSTANT INT := 50000;
    v_last_id BIGINT := 0;
    v_rows    INT;
    v_total   BIGINT := 0;
BEGIN
    LOOP
        INSERT INTO booking_discounts (booking_id, discount_id)
        SELECT b.booking_id, d.discount_id
        FROM bookings b
        JOIN LATERAL (
            SELECT discount_id
            FROM discounts
            WHERE b.booking_id IS NOT NULL
            ORDER BY random()
            LIMIT 1
        ) d ON true
        WHERE b.booking_id > v_last_id
          AND random() < CASE
                WHEN b.booking_status = 'CONFIRMED' THEN 0.30
                WHEN b.booking_status = 'COMPLETED' THEN 0.20
                WHEN b.booking_status = 'CANCELLED' THEN 0.10
                ELSE 0.15
              END
        ORDER BY b.booking_id
        LIMIT c_batch
        ON CONFLICT (booking_id, discount_id) DO NOTHING;

        GET DIAGNOSTICS v_rows = ROW_COUNT;
        EXIT WHEN v_rows = 0;

        SELECT MAX(booking_id) INTO v_last_id
        FROM booking_discounts
        WHERE booking_id > v_last_id;

        v_total := v_total + v_rows;
        RAISE NOTICE 'Booking discounts inserted so far: %', v_total;
    END LOOP;

    RAISE NOTICE '=== Done. % booking_discounts inserted. ===', v_total;
END $$;



-- 25. BOOKING_AMENITIES  (~20% of bookings get add-on amenities)
--     Batched for the same reason as booking_discounts

DO $$
DECLARE
    c_batch   CONSTANT INT := 50000;
    v_last_id BIGINT := 0;
    v_rows    INT;
    v_total   BIGINT := 0;
BEGIN
    LOOP
        INSERT INTO booking_amenities (booking_id, amenity_id, quantity)
        SELECT DISTINCT b.booking_id,
               a.amenity_id,
               (floor(random()*3)+1)::int
        FROM   bookings b
        JOIN LATERAL (
            SELECT amenity_id FROM amenities
            ORDER  BY random()
            LIMIT  1 + floor(random()*3)::int
        ) a ON true
        WHERE  b.booking_id > v_last_id
          AND  random() < 0.20
        ORDER  BY b.booking_id
        LIMIT  c_batch
        ON CONFLICT (booking_id, amenity_id) DO NOTHING;

        GET DIAGNOSTICS v_rows = ROW_COUNT;
        EXIT WHEN v_rows = 0;

        SELECT MAX(booking_id) INTO v_last_id
        FROM   booking_amenities
        WHERE  booking_id > v_last_id;

        v_total := v_total + v_rows;
        RAISE NOTICE 'Booking amenities inserted so far: %', v_total;
    END LOOP;
    RAISE NOTICE '=== Done. % booking_amenities inserted. ===', v_total;
END $$;



-- 26. IMAGES  (~500 000 rows)
--     PROPERTY: entity_type='PROPERTY', entity_id=property_id, room_id=NULL
--     ROOM:     entity_type='ROOM',     entity_id=room_id,     property_id=NULL

DO $$
DECLARE
    c_prop_imgs CONSTANT INT := 300000;
    c_room_imgs CONSTANT INT := 200000;
    c_batch     CONSTANT INT := 10000;
    v_inserted  INT := 0;
    v_prop_ids  BIGINT[];
    v_room_ids  BIGINT[];
    v_n_props   INT;
    v_n_rooms   INT;
    v_alts TEXT[] := ARRAY[
        'Front view of the property','Living room interior',
        'Bedroom with king bed','Bathroom overview',
        'Kitchen with modern appliances','Balcony with city view',
        'Pool area','Garden and outdoor space',
        'Entrance and lobby','Dining area', NULL
    ];
BEGIN
    SELECT ARRAY(SELECT property_id FROM properties ORDER BY random() LIMIT 100000)
      INTO v_prop_ids;
    v_n_props := array_length(v_prop_ids, 1);

    v_inserted := 0;
    WHILE v_inserted < c_prop_imgs LOOP
        INSERT INTO images
            (entity_type, entity_id, property_id, room_id,
             url, alt_text, is_cover, sort_order, uploaded_at)
        SELECT
            'PROPERTY',
            pid,
            pid,
            NULL,
            'https://cdn.example.com/properties/' || pid || '/' || gs || '.jpg',
            v_alts[1 + floor(random()*array_length(v_alts,1))::int],
            gs = 1,
            gs,
            now() - (random() * interval '5 years')
        FROM (
            SELECT gs,
                   v_prop_ids[1 + floor(random()*v_n_props)::int] AS pid
            FROM generate_series(1, LEAST(c_batch, c_prop_imgs - v_inserted)) gs
        ) x;

        v_inserted := v_inserted + LEAST(c_batch, c_prop_imgs - v_inserted);
        RAISE NOTICE 'Property images: % / %', v_inserted, c_prop_imgs;
    END LOOP;

    SELECT ARRAY(SELECT room_id FROM rooms ORDER BY random() LIMIT 100000)
      INTO v_room_ids;
    v_n_rooms := array_length(v_room_ids, 1);

    v_inserted := 0;
    WHILE v_inserted < c_room_imgs LOOP
        INSERT INTO images
            (entity_type, entity_id, property_id, room_id,
             url, alt_text, is_cover, sort_order, uploaded_at)
        SELECT
            'ROOM',
            rid,
            NULL,
            rid,
            'https://cdn.example.com/rooms/' || rid || '/' || gs || '.jpg',
            v_alts[1 + floor(random()*array_length(v_alts,1))::int],
            gs = 1,
            gs,
            now() - (random() * interval '5 years')
        FROM (
            SELECT gs,
                   v_room_ids[1 + floor(random()*v_n_rooms)::int] AS rid
            FROM generate_series(1, LEAST(c_batch, c_room_imgs - v_inserted)) gs
        ) x;

        v_inserted := v_inserted + LEAST(c_batch, c_room_imgs - v_inserted);
        RAISE NOTICE 'Room images: % / %', v_inserted, c_room_imgs;
    END LOOP;

    RAISE NOTICE '=== Images done ===';
END $$;

DO $$
DECLARE
    c_total     CONSTANT INT := 10000000;
    c_batch     CONSTANT INT := 100000;
    v_inserted  INT := 0;

    v_room_ids  BIGINT[];
    v_n_rooms   INT;
BEGIN

    SELECT ARRAY(
        SELECT room_id
        FROM rooms
        WHERE status = 'ACTIVE'
        ORDER BY random()
        LIMIT 200000
    )
    INTO v_room_ids;

    v_n_rooms := array_length(v_room_ids, 1);

    IF v_n_rooms IS NULL THEN
        RAISE EXCEPTION 'No rooms found';
    END IF;

    WHILE v_inserted < c_total LOOP

        INSERT INTO availability_windows (
            room_id,
            available_from_date,
            available_from_time,
            available_to_date,
            available_to_time,
            description
        )
        SELECT
            v_room_ids[1 + floor(random() * v_n_rooms)::int],

            start_date,
            start_date::timestamp + make_interval(hours => floor(random()*24)::int),

            (start_date + duration_days),
            (start_date + duration_days)::timestamp + make_interval(hours => floor(random()*24)::int),

            (ARRAY[
                'Season open for booking',
                'Property available',
                'Standard availability window',
                'Peak season availability',
                'Long-term rental availability',
                NULL
            ])[1 + floor(random()*6)::int]

        FROM (
            SELECT
                gs,
                (CURRENT_DATE + floor(random()*365)::int - 30)::date AS start_date,
                (1 + floor(random()*30)::int) AS duration_days
            FROM generate_series(1, LEAST(c_batch, c_total - v_inserted)) gs
        ) x;

        v_inserted := v_inserted + LEAST(c_batch, c_total - v_inserted);

        RAISE NOTICE 'Availability windows inserted: % / %', v_inserted, c_total;

    END LOOP;

END $$;


ANALYZE;


-- END OF SEED SCRIPT