-- Optional manual installer. The PHP package also self-creates these tables.
CREATE TABLE IF NOT EXISTS guest_stays (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,hotel_id INT UNSIGNED NOT NULL,booking_ref VARCHAR(120) NOT NULL,guest_name VARCHAR(190) NOT NULL,guest_email VARCHAR(190) NULL,room_number VARCHAR(80) NULL,arrival_date DATE NULL,departure_date DATE NULL,status ENUM('active','checked_out','cancelled') NOT NULL DEFAULT 'active',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE KEY uniq_hotel_booking (hotel_id,booking_ref)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS daily_program (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,hotel_id INT UNSIGNED NOT NULL,title VARCHAR(190) NOT NULL,description TEXT NULL,event_date DATE NULL,start_time TIME NULL,end_time TIME NULL,location VARCHAR(190) NULL,category VARCHAR(100) NULL,repeat_rule ENUM('none','daily','weekdays','weekends') NOT NULL DEFAULT 'none',button_text VARCHAR(100) NULL,button_link VARCHAR(500) NULL,status ENUM('active','inactive') NOT NULL DEFAULT 'active',sort_order INT NOT NULL DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS guest_service_requests (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,hotel_id INT UNSIGNED NOT NULL,stay_id INT UNSIGNED NULL,request_code VARCHAR(40) NOT NULL,request_type VARCHAR(100) NOT NULL,request_item VARCHAR(190) NOT NULL,notes TEXT NULL,room_number VARCHAR(80) NULL,guest_name VARCHAR(190) NULL,status ENUM('pending','accepted','in_progress','completed','cancelled') NOT NULL DEFAULT 'pending',priority ENUM('normal','high','urgent') NOT NULL DEFAULT 'normal',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS guest_itinerary (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,hotel_id INT UNSIGNED NOT NULL,stay_id INT UNSIGNED NOT NULL,item_type VARCHAR(80) NOT NULL,item_id INT UNSIGNED NULL,title VARCHAR(190) NOT NULL,itinerary_date DATE NOT NULL,itinerary_time TIME NULL,link VARCHAR(500) NULL,notes TEXT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS concierge_logs (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,hotel_id INT UNSIGNED NOT NULL,stay_id INT UNSIGNED NULL,question TEXT NOT NULL,answer MEDIUMTEXT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;