-- Slice Hotels / Redeye Rooms — database schema
-- Run this directly against the MySQL database on the shared host
-- (e.g. via phpMyAdmin or the hosting control panel's SQL runner).
-- Matches the data model in HOTEL-PAGE-SPEC.md.

CREATE TABLE IF NOT EXISTS hotels (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(160) NOT NULL UNIQUE,
    address VARCHAR(255) NOT NULL,
    city VARCHAR(100) NOT NULL,
    state CHAR(2) NOT NULL,
    zip VARCHAR(10) NOT NULL,
    latitude DECIMAL(9,6) NULL,
    longitude DECIMAL(9,6) NULL,
    distance_note VARCHAR(120) NULL COMMENT 'e.g. "2.1 mi from BNA"',
    market_tier TINYINT UNSIGNED NOT NULL COMMENT '1-4, see BUSINESS-MODEL.md section 3.2',
    hotel_class ENUM('economy','midscale','upscale','luxury') NOT NULL,
    contract_rate DECIMAL(6,2) NOT NULL COMMENT 'flat rate from the rate chart, section 3.4',
    star_rating DECIMAL(2,1) NOT NULL DEFAULT 0.0,
    review_count INT UNSIGNED NOT NULL DEFAULT 0,
    average_review_score DECIMAL(2,1) NOT NULL DEFAULT 0.0,
    description TEXT NULL,
    accept_rate DECIMAL(5,2) NULL COMMENT 'internal only — never shown to guests, section 2.3',
    network_status ENUM('active','probation','removed') NOT NULL DEFAULT 'active' COMMENT 'Slice-controlled: network standing, tied to behavior/billing (BUSINESS-MODEL.md 2.3, PROCESS-FLOW.md section 7)',
    accepting_new_requests TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Hotel-controlled: self-service pause. Does not affect network_status or already-made reservations — see PROCESS-FLOW.md section 9',
    posted_checkout_time TIME NOT NULL DEFAULT '11:00:00' COMMENT 'This hotel''s normal overnight-guest checkout time. Feeds the mandatory Redeye "Stay Until Checkout" add-on, BUSINESS-MODEL.md section 2.1.3',
    breakfast_policy ENUM('none','free','paid') NOT NULL DEFAULT 'none' COMMENT 'Hotel-wide breakfast offering, independent of the per-brand hotel_amenities toggle — governs whether Stay Until Checkout can unlock breakfast at all',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS hotel_photos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    hotel_id INT UNSIGNED NOT NULL,
    url VARCHAR(500) NOT NULL,
    caption VARCHAR(150) NULL,
    category ENUM('exterior','room','bathroom','desk','other') NOT NULL DEFAULT 'other',
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    CONSTRAINT fk_hotel_photos_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Curated amenity taxonomy, subset of the OTA Hotel Amenity Codes (HAC) standard,
-- plus room for hotels to add their own. See HOTEL-PAGE-SPEC.md section 4 and
-- PROCESS-FLOW.md section 8 for the full list and rationale.
-- hotel_id NULL = standard menu item, offered to every hotel to toggle.
-- hotel_id NOT NULL = a custom amenity a specific hotel added themselves,
-- for something at their property the standard menu doesn't cover.
CREATE TABLE IF NOT EXISTS amenities (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    hotel_id INT UNSIGNED NULL COMMENT 'NULL = standard menu item; set = custom, added by that hotel only',
    code VARCHAR(40) NULL COMMENT 'stable key for standard items, e.g. free_wifi; NULL for custom amenities',
    label VARCHAR(80) NOT NULL COMMENT 'guest-facing text, e.g. Free wifi',
    icon VARCHAR(40) NOT NULL DEFAULT 'sparkles' COMMENT 'Tabler icon name; custom amenities default to a generic icon',
    scope ENUM('in_room','property') NOT NULL COMMENT 'in_room = always day-use relevant, property = hours-gated by default',
    UNIQUE KEY uq_amenities_code (code),
    CONSTRAINT fk_amenities_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-hotel amenity availability, split by brand and by fee status. A hotel can
-- include an amenity free, offer it for a fee (collected by the hotel directly,
-- never through Slice — consistent with never touching guest payment), or not
-- offer it at all — independently for Slice and for Redeye guests (e.g. a hotel
-- may offer breakfast free to Redeye guests, whose stay often overlaps morning
-- breakfast service, while excluding it entirely for Slice guests). A row's
-- absence means "not applicable to this property." This is a hotel-level policy
-- choice made once at onboarding, not a real-time calculation against amenity
-- operating hours — kept simple on purpose. See PROCESS-FLOW.md section 8.
CREATE TABLE IF NOT EXISTS hotel_amenities (
    hotel_id INT UNSIGNED NOT NULL,
    amenity_id INT UNSIGNED NOT NULL,
    slice_status ENUM('not_included','included_free','available_for_fee') NOT NULL DEFAULT 'not_included',
    redeye_status ENUM('not_included','included_free','available_for_fee') NOT NULL DEFAULT 'not_included',
    fee_note VARCHAR(100) NULL COMMENT 'shown only when either status is available_for_fee, e.g. "$15/day, ask front desk"',
    PRIMARY KEY (hotel_id, amenity_id),
    CONSTRAINT fk_hotel_amenities_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE,
    CONSTRAINT fk_hotel_amenities_amenity FOREIGN KEY (amenity_id) REFERENCES amenities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS hotel_reviews (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    hotel_id INT UNSIGNED NOT NULL,
    guest_type VARCHAR(60) NULL COMMENT 'e.g. "overnight driver", "business trip"',
    stay_date DATE NOT NULL,
    rating DECIMAL(2,1) NOT NULL,
    body TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_hotel_reviews_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed the curated, standard amenity menu (hotel_id left NULL — see HOTEL-PAGE-SPEC.md
-- section 4). Every hotel picks from this list; PROCESS-FLOW.md section 8 covers how a
-- hotel adds something of their own beyond it.
INSERT INTO amenities (code, label, icon, scope) VALUES
    ('free_wifi', 'Free wifi', 'wifi', 'in_room'),
    ('air_conditioning', 'Air conditioning', 'snowflake', 'in_room'),
    ('hot_shower', 'Hot shower', 'bath', 'in_room'),
    ('work_desk', 'In-room work desk', 'device-desktop', 'in_room'),
    ('coffee_maker', 'Coffee maker', 'coffee', 'in_room'),
    ('tv_streaming', 'TV and streaming', 'device-tv', 'in_room'),
    ('blackout_curtains', 'Blackout curtains', 'moon', 'in_room'),
    ('free_parking', 'Free parking', 'parking', 'in_room'),
    ('accessible_room', 'Accessible room option', 'wheelchair', 'in_room'),
    ('microwave_fridge', 'Microwave and mini-fridge', 'fridge', 'in_room'),
    ('pool', 'Pool', 'swimming', 'property'),
    ('fitness_center', 'Fitness center', 'barbell', 'property'),
    ('breakfast', 'Breakfast service', 'bread', 'property'),
    ('restaurant_bar', 'Restaurant and bar service', 'glass', 'property'),
    ('bell_luggage', 'Bell and luggage service', 'luggage', 'property'),
    ('business_center', 'Business center', 'briefcase', 'property'),
    ('spa', 'Spa', 'flower', 'property'),
    ('concierge', 'Concierge desk', 'user-star', 'property'),
    ('airport_shuttle', 'Airport shuttle', 'car', 'property'),
    ('pet_friendly', 'Pet friendly', 'paw', 'property')
ON DUPLICATE KEY UPDATE label = VALUES(label);

-- Sample hotel matching the mockup shown during design (Section 3, HOTEL-PAGE-SPEC.md)
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)
VALUES (
    'Hampton Inn and Suites Nashville Airport',
    'hampton-inn-suites-nashville-airport',
    '1 Hospitality Drive',
    'Nashville',
    'TN',
    '37214',
    '2.1 mi from BNA',
    2,
    'midscale',
    70.00,
    4.6,
    212,
    4.6,
    'A straightforward, well-kept property two miles from BNA. Rooms are quiet, the desk is fast, and the walk from the lot to your room takes under a minute — built for landing, resetting, and getting back on the road.',
    92.50,
    'active',
    '11:00:00',
    'free'
);

SET @hotel_id = LAST_INSERT_ID();

INSERT INTO hotel_photos (hotel_id, url, caption, category, sort_order) VALUES
    (@hotel_id, '/assets/img/sample/exterior.jpg', 'Exterior and lobby', 'exterior', 1),
    (@hotel_id, '/assets/img/sample/room.jpg', 'King room', 'room', 2),
    (@hotel_id, '/assets/img/sample/bathroom.jpg', 'Bathroom', 'bathroom', 3),
    (@hotel_id, '/assets/img/sample/desk.jpg', 'Work desk', 'desk', 4);

-- In-room amenities: included free for both brands, no reason for a hotel to distinguish.
INSERT INTO hotel_amenities (hotel_id, amenity_id, slice_status, redeye_status)
SELECT @hotel_id, id, 'included_free', 'included_free' FROM amenities WHERE code IN
    ('free_wifi','air_conditioning','hot_shower','work_desk','coffee_maker','free_parking');

-- Breakfast: this hotel includes it free for Redeye (whose stay often overlaps morning
-- breakfast service) but not at all for Slice (daytime-only stays) — a real example of
-- the brand-specific choice PROCESS-FLOW.md section 8 describes, not a default.
INSERT INTO hotel_amenities (hotel_id, amenity_id, slice_status, redeye_status)
SELECT @hotel_id, id, 'not_included', 'included_free' FROM amenities WHERE code = 'breakfast';

-- Fitness center: available to both brands, but for a fee at this property — a real
-- example of the available_for_fee tier, collected by the hotel directly at the desk.
INSERT INTO hotel_amenities (hotel_id, amenity_id, slice_status, redeye_status, fee_note)
SELECT @hotel_id, id, 'available_for_fee', 'available_for_fee', '$10/day, pay at front desk' FROM amenities WHERE code = 'fitness_center';

-- Excluded entirely for both brands at this property.
INSERT INTO hotel_amenities (hotel_id, amenity_id, slice_status, redeye_status)
SELECT @hotel_id, id, 'not_included', 'not_included' FROM amenities WHERE code IN
    ('pool','bell_luggage');

-- A custom amenity this hotel added themselves, beyond the standard menu.
INSERT INTO amenities (hotel_id, code, label, icon, scope) VALUES
    (@hotel_id, NULL, 'Truck and RV parking', 'truck', 'property');
SET @custom_amenity_id = LAST_INSERT_ID();
INSERT INTO hotel_amenities (hotel_id, amenity_id, slice_status, redeye_status)
VALUES (@hotel_id, @custom_amenity_id, 'included_free', 'included_free');

INSERT INTO hotel_reviews (hotel_id, guest_type, stay_date, rating, body) VALUES
    (@hotel_id, 'overnight driver', '2026-06-26', 5.0, 'Checked in at 2am, front desk was ready for me, slept until my alarm. Exactly what I needed.'),
    (@hotel_id, 'business trip', '2026-06-12', 4.5, 'Booked between two meetings for a shower and a change of clothes. Desk knew exactly why I was there.');