-- Five additional fake hotels, generic names (no real chain branding), spread
-- across all four market tiers and several classes, for homepage/testing variety.
-- Safe to re-run: each block only inserts if the slug doesn't already exist.

-- 1. Harborview Lodge — Boston, MA — Tier 1, Upscale
INSERT INTO hotels (name, slug, address, city, state, zip, distance_note, market_tier, hotel_class, contract_rate, star_rating, review_count, average_review_score, description, accept_rate, network_status, posted_checkout_time, breakfast_policy)
SELECT * FROM (SELECT
    'Harborview Lodge' AS name, 'harborview-lodge-boston' AS slug, '210 Seaport Boulevard' AS address, 'Boston' AS city, 'MA' AS state, '02210' AS zip,
    '1.8 mi from Logan Airport' AS distance_note, 1 AS market_tier, 'upscale' AS hotel_class, 140.00 AS contract_rate, 4.5 AS star_rating, 178 AS review_count, 4.5 AS average_review_score,
    'A quiet, well-run property near the harbor. Front desk is fast at any hour, and the walk to the room is short and simple.' AS description,
    88.0 AS accept_rate, 'active' AS network_status, '11:00:00' AS posted_checkout_time, 'paid' AS breakfast_policy
) AS tmp WHERE NOT EXISTS (SELECT 1 FROM hotels WHERE slug = 'harborview-lodge-boston');

-- 2. Cedar Point Inn & Suites — Austin, TX — Tier 2, Midscale
INSERT INTO hotels (name, slug, address, city, state, zip, distance_note, market_tier, hotel_class, contract_rate, star_rating, review_count, average_review_score, description, accept_rate, network_status, posted_checkout_time, breakfast_policy)
SELECT * FROM (SELECT
    'Cedar Point Inn and Suites' AS name, 'cedar-point-inn-suites-austin' AS slug, '4400 South Interstate 35' AS address, 'Austin' AS city, 'TX' AS state, '78745' AS zip,
    '3.2 mi from downtown' AS distance_note, 2 AS market_tier, 'midscale' AS hotel_class, 70.00 AS contract_rate, 4.3 AS star_rating, 96 AS review_count, 4.3 AS average_review_score,
    'Straightforward and clean, right off the interstate. Good for a late arrival or an early landing before a meeting.' AS description,
    91.0 AS accept_rate, 'active' AS network_status, '11:00:00' AS posted_checkout_time, 'free' AS breakfast_policy
) AS tmp WHERE NOT EXISTS (SELECT 1 FROM hotels WHERE slug = 'cedar-point-inn-suites-austin');

-- 3. Founders Crossing Hotel — Chattanooga, TN — Tier 3, Economy
INSERT INTO hotels (name, slug, address, city, state, zip, distance_note, market_tier, hotel_class, contract_rate, star_rating, review_count, average_review_score, description, accept_rate, network_status, posted_checkout_time, breakfast_policy)
SELECT * FROM (SELECT
    'Founders Crossing Hotel' AS name, 'founders-crossing-hotel-chattanooga' AS slug, '1120 Market Street' AS address, 'Chattanooga' AS city, 'TN' AS state, '37402' AS zip,
    '0.9 mi from downtown' AS distance_note, 3 AS market_tier, 'economy' AS hotel_class, 40.00 AS contract_rate, 4.0 AS star_rating, 54 AS review_count, 4.0 AS average_review_score,
    'No-frills and reliable. A basic, clean room and a front desk that keeps things simple.' AS description,
    82.0 AS accept_rate, 'active' AS network_status, '11:00:00' AS posted_checkout_time, 'none' AS breakfast_policy
) AS tmp WHERE NOT EXISTS (SELECT 1 FROM hotels WHERE slug = 'founders-crossing-hotel-chattanooga');

-- 4. Blue Ridge Suites — Holland, MI — Tier 4, Midscale
INSERT INTO hotels (name, slug, address, city, state, zip, distance_note, market_tier, hotel_class, contract_rate, star_rating, review_count, average_review_score, description, accept_rate, network_status, posted_checkout_time, breakfast_policy)
SELECT * FROM (SELECT
    'Blue Ridge Suites' AS name, 'blue-ridge-suites-holland' AS slug, '780 Riley Street' AS address, 'Holland' AS city, 'MI' AS state, '49423' AS zip,
    'Just off the highway' AS distance_note, 4 AS market_tier, 'midscale' AS hotel_class, 50.00 AS contract_rate, 4.2 AS star_rating, 41 AS review_count, 4.2 AS average_review_score,
    'A small-town property with a genuinely helpful front desk. Quiet at night, easy in and out.' AS description,
    95.0 AS accept_rate, 'active' AS network_status, '11:00:00' AS posted_checkout_time, 'free' AS breakfast_policy
) AS tmp WHERE NOT EXISTS (SELECT 1 FROM hotels WHERE slug = 'blue-ridge-suites-holland');

-- 5. Grand Meridian Hotel — Chicago, IL — Tier 1, Luxury
INSERT INTO hotels (name, slug, address, city, state, zip, distance_note, market_tier, hotel_class, contract_rate, star_rating, review_count, average_review_score, description, accept_rate, network_status, posted_checkout_time, breakfast_policy)
SELECT * FROM (SELECT
    'Grand Meridian Hotel' AS name, 'grand-meridian-hotel-chicago' AS slug, '88 North Wacker Drive' AS address, 'Chicago' AS city, 'IL' AS state, '60606' AS zip,
    '0.5 mi from the Loop' AS distance_note, 1 AS market_tier, 'luxury' AS hotel_class, 200.00 AS contract_rate, 4.8 AS star_rating, 312 AS review_count, 4.8 AS average_review_score,
    'A genuinely upscale property in the middle of the city. Full-service front desk, handled with real discretion at any hour.' AS description,
    93.0 AS accept_rate, 'active' AS network_status, '12:00:00' AS posted_checkout_time, 'paid' AS breakfast_policy
) AS tmp WHERE NOT EXISTS (SELECT 1 FROM hotels WHERE slug = 'grand-meridian-hotel-chicago');

-- Photos, amenities, and a review for each new hotel
INSERT INTO hotel_photos (hotel_id, url, caption, category, sort_order)
SELECT h.id, p.url, p.caption, p.category, p.sort_order FROM hotels h
JOIN (
    SELECT 'harborview-lodge-boston' AS slug, '/assets/img/sample/exterior.jpg' AS url, 'Exterior' AS caption, 'exterior' AS category, 1 AS sort_order
    UNION ALL SELECT 'harborview-lodge-boston', '/assets/img/sample/room.jpg', 'Room', 'room', 2
    UNION ALL SELECT 'harborview-lodge-boston', '/assets/img/sample/bathroom.jpg', 'Bathroom', 'bathroom', 3
    UNION ALL SELECT 'cedar-point-inn-suites-austin', '/assets/img/sample/exterior.jpg', 'Exterior', 'exterior', 1
    UNION ALL SELECT 'cedar-point-inn-suites-austin', '/assets/img/sample/room.jpg', 'Room', 'room', 2
    UNION ALL SELECT 'founders-crossing-hotel-chattanooga', '/assets/img/sample/exterior.jpg', 'Exterior', 'exterior', 1
    UNION ALL SELECT 'founders-crossing-hotel-chattanooga', '/assets/img/sample/room.jpg', 'Room', 'room', 2
    UNION ALL SELECT 'blue-ridge-suites-holland', '/assets/img/sample/exterior.jpg', 'Exterior', 'exterior', 1
    UNION ALL SELECT 'blue-ridge-suites-holland', '/assets/img/sample/room.jpg', 'Room', 'room', 2
    UNION ALL SELECT 'grand-meridian-hotel-chicago', '/assets/img/sample/exterior.jpg', 'Exterior', 'exterior', 1
    UNION ALL SELECT 'grand-meridian-hotel-chicago', '/assets/img/sample/room.jpg', 'Room', 'room', 2
    UNION ALL SELECT 'grand-meridian-hotel-chicago', '/assets/img/sample/bathroom.jpg', 'Bathroom', 'bathroom', 3
) p ON p.slug = h.slug
WHERE NOT EXISTS (SELECT 1 FROM hotel_photos WHERE hotel_id = h.id);

-- Standard in-room amenities, included free for both brands, for every new hotel
INSERT INTO hotel_amenities (hotel_id, amenity_id, slice_status, redeye_status)
SELECT h.id, a.id, 'included_free', 'included_free'
FROM hotels h
JOIN amenities a ON a.code IN ('free_wifi','air_conditioning','hot_shower','work_desk','coffee_maker','free_parking')
WHERE h.slug IN ('harborview-lodge-boston','cedar-point-inn-suites-austin','founders-crossing-hotel-chattanooga','blue-ridge-suites-holland','grand-meridian-hotel-chicago')
AND NOT EXISTS (SELECT 1 FROM hotel_amenities WHERE hotel_id = h.id AND amenity_id = a.id);

-- Pool and fitness center for the luxury property, fee-based
INSERT INTO hotel_amenities (hotel_id, amenity_id, slice_status, redeye_status, fee_note)
SELECT h.id, a.id, 'available_for_fee', 'available_for_fee', '$20/day, ask front desk'
FROM hotels h
JOIN amenities a ON a.code IN ('pool','fitness_center')
WHERE h.slug = 'grand-meridian-hotel-chicago'
AND NOT EXISTS (SELECT 1 FROM hotel_amenities WHERE hotel_id = h.id AND amenity_id = a.id);

-- One review per new hotel
INSERT INTO hotel_reviews (hotel_id, guest_type, stay_date, rating, body)
SELECT h.id, r.guest_type, r.stay_date, r.rating, r.body FROM hotels h
JOIN (
    SELECT 'harborview-lodge-boston' AS slug, 'business trip' AS guest_type, '2026-06-20' AS stay_date, 4.5 AS rating, 'Landed late, was in and out fast. Room was quiet.' AS body
    UNION ALL SELECT 'cedar-point-inn-suites-austin', 'overnight driver', '2026-06-18', 4.5, 'Easy off the highway, easy back on. Exactly what I needed.'
    UNION ALL SELECT 'founders-crossing-hotel-chattanooga', 'family in transit', '2026-06-15', 4.0, 'Basic but clean, and the front desk was kind about the late hour.'
    UNION ALL SELECT 'blue-ridge-suites-holland', 'overnight driver', '2026-06-22', 4.5, 'Small town, big help. Front desk actually cared.'
    UNION ALL SELECT 'grand-meridian-hotel-chicago', 'business trip', '2026-06-25', 5.0, 'Didn''t expect this level of service for a day-use booking. Impressive.'
) r ON r.slug = h.slug
WHERE NOT EXISTS (SELECT 1 FROM hotel_reviews WHERE hotel_id = h.id);