DatabaseCreation: inserts.sql

-- SUPPLIERS (static, 5 suppliers)

INSERT INTO Supplier (name, contact_name, phone, email, address) VALUES
                                                                     ('PetCo Supply', 'James Turner',  '+12125550101', 'james@petcosupply.com',  '12 Commerce St, New York, NY 10001'),
                                                                     ('FurFresh Co',  'Anna Schmidt',  '+13105550202', 'anna@furfresh.com',       '88 Sunset Blvd, Los Angeles, CA 90028'),
                                                                     ('VetDirect',    'Carlos Reyes',  '+13125550303', 'carlos@vetdirect.com',    '5 Lake Ave, Chicago, IL 60601'),
                                                                     ('AnimalPlus',   'Sara Novak',    '+17135550404', 'sara@animalplus.com',     '20 River Rd, Houston, TX 77001'),
                                                                     ('PawMart',      'David Chen',    '+16025550505', 'david@pawmart.com',       '99 Desert Way, Phoenix, AZ 85001');


-- CATEGORIES (static, 5 categories)

INSERT INTO Category (name, description) VALUES
                                             ('Food',        'Pet food and treats'),
                                             ('Toys',        'Toys and entertainment items'),
                                             ('Medicine',    'Health and medical supplies'),
                                             ('Accessories', 'Collars, leashes and gear'),
                                             ('Bedding',     'Beds, mats and sleeping items');


-- ROOM TYPES (static,  5 room types)

INSERT INTO Room_Type (name, description, price_per_night) VALUES
                                                               ('Economy',         'Basic room with essentials',            25.00),
                                                               ('Standard Single', 'Comfortable single-pet room',           45.00),
                                                               ('Standard Double', 'Room for two pets',                     65.00),
                                                               ('Deluxe Suite',    'Spacious suite with premium amenities', 100.00),
                                                               ('VIP Penthouse',   'Top-tier suite with luxury comfort',    180.00);


-- SERVICES (static, 8 services)

INSERT INTO Service (name, description, price, duration_minutes) VALUES
                                                                     ('Grooming',         'Full coat grooming session',          35.00,  60),
                                                                     ('Vet Check',        'Basic health examination',            50.00,  30),
                                                                     ('Training Session', 'One-on-one obedience training',       40.00,  45),
                                                                     ('Daycare',          'Full day supervised care',            30.00, 480),
                                                                     ('Bath & Dry',       'Wash and blow dry',                   25.00,  45),
                                                                     ('Nail Trim',        'Nail clipping and filing',            15.00,  20),
                                                                     ('Dental Clean',     'Teeth brushing and cleaning',         20.00,  30),
                                                                     ('Massage',          'Relaxation massage for pets',         30.00,  40);


-- HOTELS (static, 10 hotels)

INSERT INTO Hotel (name, location) VALUES
                                       ('Paw Palace',      'New York'),
                                       ('Cozy Paws',       'Los Angeles'),
                                       ('Fur Haven',       'Chicago'),
                                       ('Happy Tails',     'Houston'),
                                       ('PetStay Plus',    'Phoenix'),
                                       ('Bark and Relax',  'Seattle'),
                                       ('The Woof Inn',    'Miami'),
                                       ('Feline Retreat',  'Denver'),
                                       ('Pawsome Lodge',   'Boston'),
                                       ('Critter Comfort', 'Austin');


-- EMPLOYEES (300, ~30 per hotel)

INSERT INTO Employee (first_name, last_name, role, phone, email, hire_date, hotel_id)
SELECT
    fn.name,
    ln.name,
    (ARRAY['Vet','Groomer','Receptionist','Manager','Caretaker'])[floor(random()*5+1)::int],
    '+1555' || (1000000000 + floor(random() * 9000000000)::bigint)::text,
    lower(
            regexp_replace(fn.name, '[^a-zA-Z0-9]', '', 'g') || '.' ||
            regexp_replace(ln.name, '[^a-zA-Z0-9]', '', 'g') ||
            gs || '@' || replace(lower(h.name), ' ', '') || '.com'
    ),
    CURRENT_DATE - (random() * 2000)::int,
    h.hotel_id
FROM generate_series(1, 300) gs
         JOIN (
    SELECT name, row_number() OVER (ORDER BY random()) AS fn_rn FROM first_names_male
    UNION ALL
    SELECT name, row_number() OVER (ORDER BY random()) + (SELECT COUNT(*) FROM first_names_male) FROM first_names_female
) fn ON fn.fn_rn = (gs % ((SELECT COUNT(*) FROM first_names_male) + (SELECT COUNT(*) FROM first_names_female))::int) + 1
         JOIN (
    SELECT name, row_number() OVER (ORDER BY random()) AS ln_rn FROM last_names
) ln ON ln.ln_rn = (gs % (SELECT COUNT(*) FROM last_names)::int) + 1
         JOIN (
    SELECT hotel_id, name, row_number() OVER (ORDER BY random()) AS h_rn FROM Hotel
) h ON h.h_rn = (gs % (SELECT COUNT(*) FROM Hotel)::int) + 1;


-- ROOMS (2,500 total, 250 per hotel)

INSERT INTO Room (room_number, capacity, room_type_id, hotel_id)
SELECT
    h.hotel_id || '-' ||
    CASE
        WHEN gs <= 83  THEN 'A'
        WHEN gs <= 166 THEN 'B'
        ELSE                'C'
        END || (100 + gs)::text,
    floor(random() * 3 + 1)::int,
    ((gs - 1) % (SELECT COUNT(*) FROM Room_Type)::int) + 1,
    h.hotel_id
FROM Hotel h
         CROSS JOIN generate_series(1, 250) gs;


-- CUSTOMERS (150,000)

INSERT INTO Customer (first_name, last_name, email, phone, address, registration_date)
SELECT
    fn.name,
    ln.name,
    lower(
            regexp_replace(fn.name, '[^a-zA-Z0-9]', '', 'g') || '.' ||
            regexp_replace(ln.name, '[^a-zA-Z0-9]', '', 'g') ||
            gs || '@customer.com'
    ),
    '+1555' || (1000000000 + floor(random() * 9000000000)::bigint)::text,
    a.address,
    CURRENT_DATE - (random() * 1500)::int
FROM generate_series(1, 150000) gs
         JOIN (
    SELECT name, row_number() OVER (ORDER BY random()) AS fn_rn FROM first_names_male
    UNION ALL
    SELECT name, row_number() OVER (ORDER BY random()) + (SELECT COUNT(*) FROM first_names_male) FROM first_names_female
) fn ON fn.fn_rn = (gs % ((SELECT COUNT(*) FROM first_names_male) + (SELECT COUNT(*) FROM first_names_female))::int) + 1
         JOIN (
    SELECT name, row_number() OVER (ORDER BY random()) AS ln_rn FROM last_names
) ln ON ln.ln_rn = (gs % (SELECT COUNT(*) FROM last_names)::int) + 1
         JOIN (
    SELECT address, row_number() OVER (ORDER BY random()) AS rn FROM addresses
) a ON a.rn = (gs % (SELECT COUNT(*) FROM addresses)::int) + 1;


-- PRODUCTS (500)

INSERT INTO Product (name, description, price, category_id, supplier_id)
SELECT
    p.product_name || ' #' || gs,
    'Quality pet product - ' || p.product_name,
    round((random() * 95 + 5)::numeric, 2),
    (SELECT category_id FROM Category WHERE name = p.category_name),
    supplier_id
FROM generate_series(1, 500) gs
         JOIN (
    VALUES
        ('Premium Kibble',  'Food'),
        ('Chew Toy',        'Toys'),
        ('Flea Collar',     'Medicine'),
        ('Plush Bed',       'Bedding'),
        ('Catnip',          'Toys'),
        ('Harness',         'Accessories'),
        ('Bowl Set',        'Accessories'),
        ('Vitamin Drops',   'Medicine'),
        ('Leash',           'Accessories'),
        ('Shampoo',         'Medicine')
) AS p(product_name, category_name)
              ON p.product_name = (
                  ARRAY[
                      'Premium Kibble','Chew Toy','Flea Collar','Plush Bed','Catnip',
                      'Harness','Bowl Set','Vitamin Drops','Leash','Shampoo'
                      ]
                  )[(gs % 10) + 1]
         CROSS JOIN LATERAL (
    SELECT supplier_id
    FROM Supplier
    ORDER BY md5(gs::text || 'sup' || random()::text)
    LIMIT 1
    ) c2(supplier_id);


-- PETS (15,000)

INSERT INTO Pet (name, date_of_birth, gender, customer_id, species_id, breed_id)
SELECT
    pet_name,
    CURRENT_DATE - (random() * 5000)::int,
    (ARRAY['Male','Female'])[floor(random() * 2 + 1)::int],
    cust.customer_id,
    s.species_id,
    b.breed_id
FROM generate_series(15001, 200000) gs
         CROSS JOIN LATERAL (
    SELECT val AS pet_name FROM (
                                    SELECT val, row_number() OVER (ORDER BY random()) AS rn
                                    FROM unnest(ARRAY[
                                        'Buddy','Max','Bella','Lucy','Charlie','Daisy',
                                        'Milo','Luna','Rocky','Coco','Oscar','Lily',
                                        'Teddy','Zoe','Loki','Nala','Bear','Stella',
                                        'Archie','Molly','Bailey','Cooper','Sadie','Duke',
                                        'Rosie','Tucker','Maggie','Zeus','Penny','Bruno'
                                        ]::text[]) AS t(val)
                                ) sub WHERE rn = (gs % 30) + 1
    ) names
         CROSS JOIN LATERAL (
    SELECT species_id FROM (
                               SELECT species_id, row_number() OVER (ORDER BY random()) AS rn FROM Species
                           ) sub WHERE rn = (gs % (SELECT COUNT(*) FROM Species)::int) + 1
    ) s
         CROSS JOIN LATERAL (
    SELECT breed_id FROM (
                             SELECT breed_id, row_number() OVER (ORDER BY random()) AS rn
                             FROM Breed WHERE species_id = s.species_id
                         ) sub WHERE rn = (gs % (SELECT COUNT(*) FROM Breed WHERE species_id = s.species_id)::int) + 1
    ) b
         CROSS JOIN LATERAL (
    SELECT customer_id FROM (
                                SELECT customer_id, row_number() OVER (ORDER BY random()) AS rn FROM Customer
                            ) sub WHERE rn = (gs % (SELECT COUNT(*) FROM Customer)::int) + 1
    ) cust;


-- ORDERS (45,000)

INSERT INTO "Order" (order_date, status, total_amount, customer_id, hotel_id)
SELECT
            CURRENT_DATE - (random() * 1000)::int,
            (ARRAY['pending','confirmed','completed','cancelled'])[floor(random() * 4 + 1)::int],
            round((random() * 500 + 10)::numeric, 2),
            c.customer_id,
            h.hotel_id
FROM generate_series(1, 45000) gs
         CROSS JOIN LATERAL (
    SELECT customer_id FROM (
                                SELECT customer_id, row_number() OVER (ORDER BY random()) AS rn FROM Customer
                            ) sub WHERE rn = (gs % (SELECT COUNT(*) FROM Customer)::int) + 1
    ) c
         CROSS JOIN LATERAL (
    SELECT hotel_id FROM (
                             SELECT hotel_id, row_number() OVER (ORDER BY random()) AS rn FROM Hotel
                         ) sub WHERE rn = (gs % (SELECT COUNT(*) FROM Hotel)::int) + 1
    ) h;


-- ORDER PRODUCTS (~23,000)

INSERT INTO OrderProduct (quantity, unit_price, order_id, product_id)
SELECT
    (random() * 9 + 1)::int,
    (random() * 100 + 1)::numeric(10,2),
    o.order_id,
    p.product_id
FROM (
         SELECT order_id, row_number() OVER (ORDER BY random()) AS rn
         FROM "Order"
         LIMIT 23000
     ) o
         JOIN (
    SELECT product_id, row_number() OVER (ORDER BY random()) AS rn,
           COUNT(*) OVER () AS total
    FROM Product
) p ON p.rn = (
    ('x' || substr(md5(o.order_id::text || o.rn::text), 1, 8))::bit(32)::int % p.total + 1
    )
ON CONFLICT (order_id, product_id) DO NOTHING;


-- RESERVATIONS (7,000,000)
-- reservation_date is set to the check-in date (2015–2027)
-- so it stays consistent with RoomReservation.check_in_date.
DO $$
    BEGIN
        FOR iter IN 0..99 LOOP
                INSERT INTO Reservation (
                    reservation_date,
                    status,
                    notes,
                    total_cost,
                    pet_id,
                    employee_id
                )
                WITH
                    pets AS (
                        SELECT
                            pet_id,
                            row_number() OVER (ORDER BY pet_id) - 1 AS rn,
                            count(*) OVER () AS total
                        FROM Pet
                    ),
                    emps AS (
                        SELECT
                            employee_id,
                            row_number() OVER (ORDER BY employee_id) - 1 AS rn,
                            count(*) OVER () AS total
                        FROM Employee
                    ),
                    statuses(idx, val) AS (
                        VALUES
                            (1,'confirmed'),
                            (2,'confirmed'),
                            (3,'confirmed'),
                            (4,'confirmed'),
                            (5,'confirmed'),
                            (6,'confirmed'),
                            (7,'completed'),
                            (8,'completed'),
                            (9,'completed'),
                            (10,'completed'),
                            (11,'pending'),
                            (12,'pending'),
                            (13,'cancelled'),
                            (14,'cancelled'),
                            (15,'no-show')
                    ),
                    notes(idx, val) AS (
                        VALUES
                            (0,'Owner requests daily photo updates.'),
                            (1,'Pet is on a special diet - dry food only.'),
                            (2,'Nervous around loud noises, please keep calm.'),
                            (3,'Very friendly with other animals.'),
                            (4,'Requires medication twice daily.'),
                            (5,NULL),
                            (6,NULL),
                            (7,NULL)
                    )
                SELECT
                    DATE '2015-01-01'
                        + floor(random() * 4747)::int,
                    s.val,
                    n.val,
                    floor(random() * 800 + 50)::int,
                    p.pet_id,
                    e.employee_id
                FROM generate_series(1, 100000) gs(n)
                         JOIN statuses s
                              ON s.idx = (gs.n % 15) + 1
                         JOIN notes n
                              ON n.idx = (gs.n * 3 + 5) % 8
                         JOIN pets p
                              ON p.rn = (gs.n * 5 + 2)
                                  % (SELECT total FROM pets LIMIT 1)
                         JOIN emps e
                              ON e.rn = (gs.n * 7 + 11)
                                  % (SELECT total FROM emps LIMIT 1);
            END LOOP;
    END;
$$;


DROP TABLE RoomReservation;

CREATE TABLE RoomReservation (
                                 room_reservation_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
                                 check_in_date       DATE    NOT NULL,
                                 check_out_date      DATE    NOT NULL,
                                 reservation_id      INTEGER NOT NULL,
                                 room_id             INTEGER NOT NULL,
                                 PRIMARY KEY (room_reservation_id),
                                 CONSTRAINT uq_roomreservation_reservation
                                     UNIQUE (reservation_id),
                                 CONSTRAINT chk_roomres_dates
                                     CHECK (check_out_date > check_in_date),
                                 CONSTRAINT no_overlapping_room_reservations
                                     EXCLUDE USING GIST (
                                     room_id WITH =,
                                     daterange(check_in_date, check_out_date, '[)') WITH &&
                                     ),
                                 CONSTRAINT fk_roomres_room
                                     FOREIGN KEY (room_id) REFERENCES Room(room_id)
                                         ON DELETE RESTRICT ON UPDATE CASCADE,
                                 CONSTRAINT fk_roomres_reservation
                                     FOREIGN KEY (reservation_id) REFERENCES Reservation(reservation_id)
                                         ON DELETE CASCADE ON UPDATE CASCADE
);

-- Step 2: Insert
DO $$
    BEGIN
        FOR iter IN 0..99 LOOP
                INSERT INTO RoomReservation (
                    check_in_date,
                    check_out_date,
                    reservation_id,
                    room_id
                )
                WITH rooms AS (
                    SELECT
                        room_id,
                        (row_number() OVER (ORDER BY room_id) - 1)::int AS room_index,
                        count(*) OVER ()::int AS total_rooms
                    FROM Room
                ),
                     reservations AS (
                         SELECT
                             reservation_id,
                             reservation_date,
                             (iter * 100000) + (row_number() OVER (ORDER BY reservation_id) - 1)::int AS global_index
                         FROM (
                                  SELECT reservation_id, reservation_date
                                  FROM Reservation
                                  ORDER BY reservation_id
                                  LIMIT 100000
                                      OFFSET (iter * 100000)
                              ) x
                     ),
                     assigned AS (
                         SELECT
                             r.reservation_id,
                             r.reservation_date,
                             rm.room_id,
                             rm.total_rooms,
                             r.global_index,
                             floor(random() * 6 + 1)::int AS stay_length
                         FROM reservations r
                                  JOIN rooms rm
                                       ON rm.room_index = r.global_index % rm.total_rooms
                     ),
                     scheduled AS (
                         SELECT
                             reservation_id,
                             room_id,
                             DATE '2015-01-01'
                                 + ((global_index / total_rooms) * 8)::int AS check_in_date,
                             DATE '2015-01-01'
                                 + ((global_index / total_rooms) * 8)::int
                                 + stay_length AS check_out_date
                         FROM assigned
                     )
                SELECT
                    check_in_date,
                    check_out_date,
                    reservation_id,
                    room_id
                FROM scheduled
                ORDER BY room_id, check_in_date
                ON CONFLICT (reservation_id) DO NOTHING;

                RAISE NOTICE 'Completed iteration % of 99 (offset %)', iter, iter * 100000;
            END LOOP;
    END;
$$;

-- Step 3: Verify
SELECT COUNT(*) FROM RoomReservation; -- should be ~10,000,000





SET work_mem = '512MB';
SET synchronous_commit = OFF;

DO $$
    BEGIN
        FOR iter IN 0..99 LOOP
                INSERT INTO RoomReservation (
                    check_in_date,
                    check_out_date,
                    reservation_id,
                    room_id
                )
                WITH rooms AS (
                    SELECT
                        room_id,
                        (row_number() OVER (ORDER BY room_id) - 1)::int AS room_index,
                        count(*) OVER ()::int AS total_rooms
                    FROM Room
                ),
                     reservations AS (
                         SELECT
                             reservation_id,
                             reservation_date,
                             (row_number() OVER (ORDER BY reservation_id) - 1)::int AS res_index
                         FROM (
                                  SELECT reservation_id, reservation_date
                                  FROM Reservation
                                  ORDER BY reservation_id
                                  LIMIT 100000
                                      OFFSET (iter * 100000)
                              ) x
                     ),
                     assigned AS (
                         SELECT
                             r.reservation_id,
                             r.reservation_date,
                             rm.room_id,
                             floor(random() * 6 + 1)::int AS stay_length
                         FROM reservations r
                                  JOIN rooms rm
                                       ON rm.room_index = r.res_index % rm.total_rooms
                     ),
                     scheduled AS (
                         SELECT
                             reservation_id,
                             room_id,
                             reservation_date AS check_in_date,
                             reservation_date + stay_length AS check_out_date
                         FROM assigned
                     )
                SELECT
                    check_in_date,
                    check_out_date,
                    reservation_id,
                    room_id
                FROM scheduled
                ON CONFLICT (reservation_id) DO NOTHING;

                RAISE NOTICE 'Completed iteration % of 99 (offset %)', iter, iter * 100000;
            END LOOP;
    END;
$$;

-- Step 7: Reset settings
SET synchronous_commit = ON;
SET work_mem = '4MB';

-- Reset settings
SET synchronous_commit = ON;
SET work_mem = '4MB';

-- Verify
SELECT COUNT(*) FROM Date;

-- SERVICE RESERVATIONS
-- ~60 % of RoomReservations get at least one service.
-- Each selected reservation gets 1–3 services at random,
-- scheduled on a random day within the actual room stay.
WITH reservation_sample AS (
    SELECT
        rr.reservation_id,
        rr.check_in_date,
        rr.check_out_date,
        -- Assign number of services: 0, 1, 2, or 3
        -- 40% = 0, 35% = 1, 18% = 2, 7% = 3
        CASE
            WHEN random() < 0.40 THEN 0
            WHEN random() < 0.58 THEN 1  -- 35% of remaining
            WHEN random() < 0.75 THEN 2  -- 18% of remaining
            ELSE 3
            END AS num_services
    FROM RoomReservation rr
    WHERE rr.check_out_date > rr.check_in_date
),
     expanded AS (
         SELECT
             rs.reservation_id,
             rs.check_in_date,
             rs.check_out_date,
             gs.service_num
         FROM reservation_sample rs
                  CROSS JOIN generate_series(1, rs.num_services) gs(service_num)
         WHERE rs.num_services > 0
     ),
     services_numbered AS (
         SELECT
             service_id,
             row_number() OVER (ORDER BY service_id) - 1 AS svc_index,
             count(*) OVER () AS total_services
         FROM Service
     )
INSERT INTO ServiceReservation (
    scheduled_date,
    scheduled_time,
    status,
    reservation_id,
    service_id
)
SELECT
    e.check_in_date
        + floor(random() * GREATEST(e.check_out_date - e.check_in_date, 1))::int
                                               AS scheduled_date,
    (ARRAY[
        '09:00','10:00','11:00','12:00','13:00',
        '14:00','15:00','16:00','17:00'
        ])[floor(random() * 9 + 1)::int]::time AS scheduled_time,
    (ARRAY['scheduled','completed','cancelled','no-show'])[floor(random()*4 + 1)::int],
    e.reservation_id,
    s.service_id
FROM expanded e
         JOIN services_numbered s
              ON s.svc_index = (e.reservation_id * e.service_num * 7 + e.service_num * 13)
                  % (SELECT total_services FROM services_numbered LIMIT 1)
ON CONFLICT DO NOTHING;

-- EMPLOYEE–SERVICE (up to 240 unique pairs)

INSERT INTO Employee_Service (employee_id, service_id)
SELECT DISTINCT e.employee_id, s.service_id
FROM generate_series(1, 1000) gs
         JOIN (SELECT employee_id, row_number() OVER (ORDER BY random()) AS rn FROM Employee) e
              ON e.rn = (gs % (SELECT COUNT(*) FROM Employee)::int) + 1
         JOIN (SELECT service_id, row_number() OVER (ORDER BY random()) AS rn FROM Service) s
              ON s.rn = (gs % (SELECT COUNT(*) FROM Service)::int) + 1
ON CONFLICT DO NOTHING;


-- PRODUCT–SERVICE (up to 120 unique pairs)

INSERT INTO Product_Service (product_id, service_id)
SELECT DISTINCT p.product_id, s.service_id
FROM generate_series(1, 400) gs
         JOIN (SELECT product_id, row_number() OVER (ORDER BY random()) AS rn FROM Product) p
              ON p.rn = (gs % (SELECT COUNT(*) FROM Product)::int) + 1
         JOIN (SELECT service_id, row_number() OVER (ORDER BY random()) AS rn FROM Service) s
              ON s.rn = (gs % (SELECT COUNT(*) FROM Service)::int) + 1
ON CONFLICT DO NOTHING;


-- REVIEWS (150,000)

INSERT INTO Review (review_date, rating, comment, customer_id, hotel_id)
SELECT
            CURRENT_DATE - (random() * 365)::int,
            rating,
            CASE
                WHEN rating >= 9 THEN (ARRAY[
                    'Absolutely incredible experience — our dog was treated like royalty!',
                    'Best pet hotel we have ever used. The staff went above and beyond.',
                    'Our cat came home happy, clean, and clearly well-loved. 10 out of 10!',
                    'Exceptional service from check-in to check-out. Will never go anywhere else.',
                    'The VIP suite was worth every penny. Our pup was in heaven!',
                    'Flawless experience. Staff remembered our pet by name on the second visit.',
                    'I was nervous leaving my rabbit for the first time, but the team was amazing.',
                    'Five stars is not enough. Truly the gold standard of pet care.',
                    'Our senior dog received such gentle and attentive care. We are so grateful.',
                    'Immaculate facilities, caring staff, and our pets came home thriving.'
                    ])[floor(random() * 10 + 1)::int]

                WHEN rating >= 7 THEN (ARRAY[
                    'Really great stay overall. The staff were friendly and clearly love animals.',
                    'Our dog seemed very happy when we picked him up. Clean rooms, good service.',
                    'Solid experience. Would definitely recommend to other pet owners.',
                    'Great grooming service included — our cat looked fabulous afterwards.',
                    'Very professional team. Our pet was well cared for throughout the stay.',
                    'Good facilities and attentive staff. Only minor issue was parking.',
                    'Our pets had a wonderful time. The play area is a great touch.',
                    'Really impressed with the daily photo updates. Gave us great peace of mind.',
                    'Friendly reception, clean rooms, and our dog ate well the whole time.',
                    'Above average in every way. We have already booked our next stay.'
                    ])[floor(random() * 10 + 1)::int]

                WHEN rating >= 5 THEN (ARRAY[
                    'Decent place overall. Nothing spectacular but the pets were looked after.',
                    'Average experience. Staff were polite but not particularly attentive.',
                    'Rooms were clean enough. Our dog seemed fine but not especially happy.',
                    'It does the job. Would consider returning if prices stay reasonable.',
                    'Fairly standard pet hotel. No complaints but no real highlights either.',
                    'Middle of the road. Communication could be better but the care was okay.',
                    'Our cat was safe and fed, which is what matters. Facilities felt a bit dated.',
                    'Okay for the price. Do not expect luxury but basics are covered.',
                    'Mixed feelings. Some staff were great, others less engaged.',
                    'Not bad, not great. We might try somewhere else next time just to compare.'
                    ])[floor(random() * 10 + 1)::int]

                WHEN rating >= 3 THEN (ARRAY[
                    'Disappointing visit. Our dog seemed stressed when we picked him up.',
                    'Rooms were smaller than advertised and not very clean.',
                    'Staff seemed overwhelmed and communication was poor throughout.',
                    'Expected much more for the price. Facilities felt neglected.',
                    'Our pet came home with a minor scratch — no explanation was given.',
                    'Booking was easy but the actual experience did not match the website.',
                    'Not enough staff on duty. Our pet did not get the attention promised.',
                    'Unimpressive. The grooming service was rushed and below standard.',
                    'Several issues during the stay that were not handled well by management.',
                    'Would not rush back. There are better options in the area for less money.'
                    ])[floor(random() * 10 + 1)::int]

                ELSE (ARRAY[
                    'Terrible experience. Our pet came home visibly distressed.',
                    'Absolutely awful. Filthy rooms, rude staff, zero accountability.',
                    'I would not leave a goldfish here. Deeply disappointed and angry.',
                    'Our dog lost weight during the stay. Clear sign of neglect.',
                    'Staff were dismissive when we raised concerns. Totally unacceptable.',
                    'The worst pet hotel we have ever used. Avoid at all costs.',
                    'Disgusting conditions. Reported to local animal welfare authorities.',
                    'No care, no communication, no apology. A complete waste of money.',
                    'Our cat was returned to us dehydrated. Shocking level of negligence.',
                    'One star is too generous. This place should not be operating.'
                    ])[floor(random() * 10 + 1)::int]
                END,
            c.customer_id,
            h.hotel_id
FROM (
         SELECT
             gs,
             floor(random() * 10 + 1)::int AS rating
         FROM generate_series(1, 150000) gs
     ) rated
         CROSS JOIN LATERAL (
    SELECT customer_id FROM (
                                SELECT customer_id, row_number() OVER (ORDER BY random()) AS rn FROM Customer
                            ) sub WHERE rn = (rated.gs % (SELECT COUNT(*) FROM Customer)::int) + 1
    ) c
         CROSS JOIN LATERAL (
    SELECT hotel_id FROM (
                             SELECT hotel_id, row_number() OVER (ORDER BY random()) AS rn FROM Hotel
                         ) sub WHERE rn = (rated.gs % (SELECT COUNT(*) FROM Hotel)::int) + 1
    ) h;


-- MEDICAL RECORDS (300,000)

INSERT INTO MedicalRecord (record_date, status, vet_name, alergies, medications, note, diagnosis, pet_id)
SELECT
            CURRENT_DATE - (random() * 730)::int,
            (ARRAY['healthy','healthy','sick','recovering'])[floor(random() * 4 + 1)::int],
            'Dr. ' || ln.name,
            (ARRAY[NULL, 'Pollen', 'Dust mites', 'Certain proteins', 'Flea bites'])[floor(random() * 5 + 1)::int],
            (ARRAY[NULL, 'Antibiotic', 'Anti-inflammatory', 'Antiparasitic', 'Vitamin supplement'])[floor(random() * 5 + 1)::int],
            (ARRAY[NULL, 'Monitor weight weekly.', 'Avoid stressful environments.', 'Follow-up in 2 weeks.'])[floor(random() * 4 + 1)::int],
            (ARRAY['Routine checkup', 'Mild infection', 'Skin irritation', 'Dental issue', 'Ear infection', 'Overweight'])[floor(random() * 6 + 1)::int],
            pet.pet_id
FROM generate_series(1, 300000) gs
         JOIN (
    SELECT name, row_number() OVER (ORDER BY random()) AS ln_rn FROM last_names
) ln ON ln.ln_rn = (gs % (SELECT COUNT(*) FROM last_names)::int) + 1
         JOIN (
    SELECT pet_id, row_number() OVER (ORDER BY random()) AS rn FROM Pet
) pet ON pet.rn = (gs % (SELECT COUNT(*) FROM Pet)::int) + 1;


-- PET DELIVERIES (80,000)

INSERT INTO PetDelivery (delivery_time, status, destination, reservation_id, employee_id)
SELECT
    NOW() - (random() * 365 * INTERVAL '1 day'),
    st.val,
    dest.val,
    r.reservation_id,
    e.employee_id
FROM (
         SELECT reservation_id, row_number() OVER (ORDER BY random()) AS rn
         FROM Reservation
         LIMIT 80000
     ) r
         CROSS JOIN LATERAL (
    SELECT val FROM (
                        SELECT val, row_number() OVER (ORDER BY random()) AS rn
                        FROM unnest(ARRAY['scheduled','delivered','failed','confirmed']::text[]) AS t(val)
                    ) sub WHERE rn = (r.rn % 4) + 1
    ) st
         CROSS JOIN LATERAL (
    SELECT val FROM (
                        SELECT val, row_number() OVER (ORDER BY random()) AS rn
                        FROM unnest(ARRAY['home','hotel','vet clinic','airport']::text[]) AS t(val)
                    ) sub WHERE rn = (r.rn % 4) + 1
    ) dest
         CROSS JOIN LATERAL (
    SELECT employee_id FROM (
                                SELECT employee_id, row_number() OVER (ORDER BY random()) AS rn FROM Employee
                            ) sub WHERE rn = (r.rn % (SELECT COUNT(*) FROM Employee)::int) + 1
    ) e;


-- PAYMENTS (~30,000)

SET work_mem = '512MB';
SET synchronous_commit = OFF;

DO $$
    BEGIN
        FOR iter IN 0..99 LOOP
                INSERT INTO Payment (payment_date, amount, payment_method, status, reservation_id)
                SELECT
                    r.reservation_date + floor(random() * 3)::int,
                    r.total_cost,
                    (ARRAY['credit card','debit card','cash','bank transfer','PayPal'])[floor(random() * 5 + 1)::int],
                    (ARRAY['completed','completed','completed','completed','refunded','pending'])[floor(random() * 6 + 1)::int],
                    r.reservation_id
                FROM (
                         SELECT reservation_id, reservation_date, total_cost
                         FROM Reservation
                         WHERE status IN ('confirmed', 'completed')
                         ORDER BY reservation_id
                         LIMIT 100000
                             OFFSET (iter * 100000)
                     ) r;

                RAISE NOTICE 'Completed iteration % of 99 (offset %)', iter, iter * 100000;
            END LOOP;
    END;
$$;

SET synchronous_commit = ON;
SET work_mem = '4MB';

-- Verify
SELECT COUNT(*) FROM Payment;

-- DELIVERIES (¬23,000)

INSERT INTO Delivery (delivered_at, status, hotel_id, purchase_id)
SELECT
    (ARRAY['morning','afternoon','evening','overnight'])[floor(random() * 4 + 1)::int],
    (ARRAY['pending','in_transit','delivered','failed'])[floor(random() * 4 + 1)::int],
    o.hotel_id,
    op.orderproduct_id
FROM OrderProduct op
         JOIN "Order" o ON o.order_id = op.order_id;


-- VIEW: v_room_availability

CREATE OR REPLACE VIEW v_room_availability AS
SELECT
    h.hotel_id,
    h.name                  AS hotel_name,
    h.location,
    r.room_id,
    r.room_number,
    r.capacity,
    rt.name                 AS room_type,
    rt.price_per_night,
    d.date,
    d.status                AS availability_status,
    CASE WHEN d.status = 'occupied'    THEN TRUE ELSE FALSE END AS is_occupied,
    CASE WHEN d.status = 'available'   THEN TRUE ELSE FALSE END AS is_available,
    CASE WHEN d.status = 'maintenance' THEN TRUE ELSE FALSE END AS in_maintenance
FROM Room r
         JOIN Hotel     h  ON h.hotel_id      = r.hotel_id
         JOIN Room_Type rt ON rt.room_type_id = r.room_type_id
         LEFT JOIN Date d  ON d.room_id       = r.room_id;






-- TOCNITE ROOM RESERVATION I DATE INSERTS




-- Step 2: Recreate RoomReservation without exclusion constraint
CREATE TABLE RoomReservation (
                                 room_reservation_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
                                 check_in_date       DATE    NOT NULL,
                                 check_out_date      DATE    NOT NULL,
                                 reservation_id      INTEGER NOT NULL,
                                 room_id             INTEGER NOT NULL,
                                 PRIMARY KEY (room_reservation_id),
                                 CONSTRAINT uq_roomreservation_reservation
                                     UNIQUE (reservation_id),
                                 CONSTRAINT chk_roomres_dates
                                     CHECK (check_out_date > check_in_date),
                                 CONSTRAINT fk_roomres_room
                                     FOREIGN KEY (room_id) REFERENCES Room(room_id)
                                         ON DELETE RESTRICT ON UPDATE CASCADE,
                                 CONSTRAINT fk_roomres_reservation
                                     FOREIGN KEY (reservation_id) REFERENCES Reservation(reservation_id)
                                         ON DELETE CASCADE ON UPDATE CASCADE
);

-- Step 3: Recreate Date table
CREATE TABLE Date (
                      date    DATE        NOT NULL,
                      room_id INTEGER     NOT NULL,
                      status  VARCHAR(20),
                      PRIMARY KEY (date, room_id),
                      CONSTRAINT fk_dates_room
                          FOREIGN KEY (room_id) REFERENCES Room(room_id)
                              ON DELETE RESTRICT ON UPDATE CASCADE
);

-- Step 4: Insert RoomReservations
SET work_mem = '512MB';
SET synchronous_commit = OFF;

DO $$
    BEGIN
        FOR iter IN 0..99 LOOP
                INSERT INTO RoomReservation (
                    check_in_date,
                    check_out_date,
                    reservation_id,
                    room_id
                )
                WITH rooms AS (
                    SELECT
                        room_id,
                        (row_number() OVER (ORDER BY room_id) - 1)::int AS room_index,
                        count(*) OVER ()::int AS total_rooms
                    FROM Room
                ),
                     reservations AS (
                         SELECT
                             reservation_id,
                             reservation_date,
                             (row_number() OVER (ORDER BY reservation_id) - 1)::int AS res_index
                         FROM (
                                  SELECT reservation_id, reservation_date
                                  FROM Reservation
                                  ORDER BY reservation_id
                                  LIMIT 100000
                                      OFFSET (iter * 100000)
                              ) x
                     ),
                     assigned AS (
                         SELECT
                             r.reservation_id,
                             r.reservation_date,
                             rm.room_id,
                             floor(random() * 6 + 1)::int AS stay_length
                         FROM reservations r
                                  JOIN rooms rm
                                       ON rm.room_index = r.res_index % rm.total_rooms
                     ),
                     scheduled AS (
                         SELECT
                             reservation_id,
                             room_id,
                             reservation_date AS check_in_date,
                             reservation_date + stay_length AS check_out_date
                         FROM assigned
                     )
                SELECT
                    check_in_date,
                    check_out_date,
                    reservation_id,
                    room_id
                FROM scheduled
                ON CONFLICT (reservation_id) DO NOTHING;

                RAISE NOTICE 'Completed iteration % of 99 (offset %)', iter, iter * 100000;
            END LOOP;
    END;
$$;

-- Step 5: Verify RoomReservation
SELECT COUNT(*) FROM RoomReservation; -- should be ~10,000,000
SELECT MIN(check_in_date), MAX(check_out_date) FROM RoomReservation; -- should be within 2015-2027

-- Step 6: Insert Date table
DO $$
    DECLARE
        r_min INTEGER;
        r_max INTEGER;
        batch_size INTEGER := 100;
        cur INTEGER;
    BEGIN
        SELECT MIN(room_id), MAX(room_id) INTO r_min, r_max FROM Room;
        cur := r_min;

        WHILE cur <= r_max LOOP

                -- Part A: occupied nights from RoomReservation
                INSERT INTO Date (date, room_id, status)
                SELECT DISTINCT ON (gs::date, rr.room_id)
                    gs::date,
                    rr.room_id,
                    'occupied'
                FROM RoomReservation rr
                         CROSS JOIN LATERAL generate_series(
                        rr.check_in_date,
                        rr.check_out_date - 1,
                        interval '1 day'
                                            ) gs
                WHERE rr.room_id BETWEEN cur AND cur + batch_size - 1
                ORDER BY gs::date, rr.room_id
                ON CONFLICT (date, room_id) DO UPDATE SET status = 'occupied';

                -- Part B: fill remaining dates as available/maintenance
                INSERT INTO Date (date, room_id, status)
                SELECT
                    d::date,
                    r.room_id,
                    (ARRAY['available','available','available','maintenance'])[floor(random()*4+1)::int]
                FROM Room r
                         CROSS JOIN generate_series(
                        DATE '2015-01-01',
                        DATE '2027-12-31',
                        interval '1 day'
                                    ) AS d
                WHERE r.room_id BETWEEN cur AND cur + batch_size - 1
                ON CONFLICT (date, room_id) DO NOTHING;

                RAISE NOTICE 'Processed rooms % to %', cur, cur + batch_size - 1;
                cur := cur + batch_size;

            END LOOP;
    END;
$$;

-- Step 7: Reset settings
SET synchronous_commit = ON;
SET work_mem = '4MB';

-- Step 8: Verify Date table
SELECT COUNT(*) FROM Date; -- should be ~11,870,000
SELECT MIN(date), MAX(date) FROM Date; -- should be 2015-01-01 to 2027-12-31





SELECT 'Species' AS table_name, COUNT(*) FROM Species UNION ALL
SELECT 'Breed', COUNT(*) FROM Breed UNION ALL
SELECT 'Category', COUNT(*) FROM Category UNION ALL
SELECT 'Room_Type', COUNT(*) FROM Room_Type UNION ALL
SELECT 'Service', COUNT(*) FROM Service UNION ALL
SELECT 'Hotel', COUNT(*) FROM Hotel UNION ALL
SELECT 'Employee', COUNT(*) FROM Employee UNION ALL
SELECT 'Room', COUNT(*) FROM Room UNION ALL
SELECT 'Customer', COUNT(*) FROM Customer UNION ALL
SELECT 'Pet', COUNT(*) FROM Pet UNION ALL
SELECT 'Product', COUNT(*) FROM Product UNION ALL
SELECT 'Supplier', COUNT(*) FROM Supplier UNION ALL
SELECT 'Order', COUNT(*) FROM "Order" UNION ALL
SELECT 'OrderProduct', COUNT(*) FROM OrderProduct UNION ALL
SELECT 'Delivery', COUNT(*) FROM Delivery UNION ALL
SELECT 'Reservation', COUNT(*) FROM Reservation UNION ALL
SELECT 'RoomReservation', COUNT(*) FROM RoomReservation UNION ALL
SELECT 'ServiceReservation', COUNT(*) FROM ServiceReservation UNION ALL
SELECT 'Payment', COUNT(*) FROM Payment UNION ALL
SELECT 'Review', COUNT(*) FROM Review UNION ALL
SELECT 'MedicalRecord', COUNT(*) FROM MedicalRecord UNION ALL
SELECT 'PetDelivery', COUNT(*) FROM PetDelivery UNION ALL
SELECT 'Date', COUNT(*) FROM Date
ORDER BY table_name;